unit frm_ProvFacturasEditor;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes,
  Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, uBaseForm, Data.DB,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.DApt,
  FireDAC.Comp.Client, Vcl.Buttons, Vcl.Grids, Vcl.DBGrids, Vcl.ExtCtrls,
  Vcl.ComCtrls, Vcl.StdCtrls, Vcl.Mask, Vcl.DBCtrls, dmg_Main, Winapi.ShellAPI, System.IOUtils, System.Generics.Collections,
  FireDAC.Stan.Async, FireDAC.Comp.DataSet;

type
  TfrmProvFacturasEditor = class(TfrmBase)
    qMaster: TFDQuery;
    dsMaster: TDataSource;
    qDetail: TFDQuery;
    dsDetail: TDataSource;
    qProvList: TFDQuery;
    dsProvList: TDataSource;
    qSelectedProv: TFDQuery;
    dsSelectedProv: TDataSource;

    // Totals in Footer
    Label4: TLabel;
    dbBase: TDBEdit;
    Label6: TLabel;
    dbTotal: TDBEdit;
    sgIvaDesglose: TStringGrid;

    // Header controls
    lblProveedor: TLabel;
    dbProv: TDBLookupComboBox;
    lblFecha: TLabel;
    dbFecha: TDBEdit;
    lblDoc: TLabel;
    dbDoc: TDBEdit;
    
    // Supplier info panel
    pnlDatosProveedor: TPanel;
    lblDatoNombre: TLabel;
    lblDatoDireccion: TLabel;
    lblDatoTelefono: TLabel;
    lblDatoEmail: TLabel;
    dbTextNombre: TDBText;
    dbTextDireccion: TDBText;
    dbTextTelefono: TDBText;
    dbTextEmail: TDBText;
    lbid: TLabel;
    DBText1: TDBText;
    lblCodigoPostal: TLabel;
    dbTextCP: TDBText;
    lblPoblacion: TLabel;
    DBTextPoblacion: TDBText;
    lblProvincia: TLabel;
    DBTextProvincia: TDBText;
    qSelectedProvDireccionCompleta: TWideStringField;
    qSelectedProvnombre: TWideStringField;
    qSelectedProvdireccion: TWideStringField;
    qSelectedProvpoblacion: TWideStringField;
    qSelectedProvcodpost: TWideStringField;
    qSelectedProvprovincia: TWideStringField;
    qSelectedProvtelefono: TWideStringField;
    qSelectedProvemail: TWideStringField;
    dbgListado: TDBGrid;

    procedure FormShow(Sender: TObject);
    procedure btnAnadirClick(Sender: TObject);
    procedure btnGuardarClick(Sender: TObject);
    procedure btnEliminarClick(Sender: TObject);
    procedure qDetailAfterPost(DataSet: TDataSet);
    procedure qDetailBeforePost(DataSet: TDataSet);
    procedure qMasterAfterScroll(DataSet: TDataSet);
    procedure qSelectedProvCalcFields(DataSet: TDataSet);
    procedure btnPrimeroClick(Sender: TObject);
    procedure btnAnteriorClick(Sender: TObject);
    procedure btnSiguienteClick(Sender: TObject);
    procedure btnUltimoClick(Sender: TObject);
    procedure btnImprimirClick(Sender: TObject);
  protected
    procedure Loaded; override;
    function IsNewRecord: Boolean; override;
    procedure DoPrimero; override;
    procedure DoAnterior; override;
    procedure DoSiguiente; override;
    procedure DoUltimo; override;
  private
    FIsNewRecord: Boolean;
    procedure CalcularTotales;
    procedure qDetailBultosChange(Sender: TField);
    procedure qDetailCantidadBultoChange(Sender: TField);
    procedure qDetailDescuentoPorcentajeChange(Sender: TField);
    procedure qDetailDescuentoPrecioChange(Sender: TField);
    procedure qDetailPrecioChange(Sender: TField);
    procedure FormatNumericFields(DataSet: TDataSet);
  public
    { Public declarations }
  end;

var
  frmProvFacturasEditor: TfrmProvFacturasEditor;

implementation

uses
  uProvPreciosService;

{$R *.dfm}

procedure TfrmProvFacturasEditor.Loaded;
begin
  inherited Loaded;
  qDetail.SQL.Text := 'SELECT * FROM ge_prov_facturas_detalle WHERE idfactura = :id AND idempresa = :idempresa';
  qSelectedProv.SQL.Text := 'SELECT id, nombre, direccion, poblacion, codigo_postal AS codpost, provincia, telefono_1 AS telefono, email FROM ge_proveedores WHERE id = :idproveedor';
end;

procedure TfrmProvFacturasEditor.FormShow(Sender: TObject);
var
  LEmpresa: string;
begin
  LEmpresa := dmgMain.CurrentCompanyId;

  qProvList.Close;
  qProvList.Open;

  btnImprimir.OnClick := btnImprimirClick;

  if not qMaster.Active then
  begin
    qMaster.Close;
    qMaster.ParamByName('Idempresa').AsString := LEmpresa;
    qMaster.Open;
  end;

  FIsNewRecord := qMaster.Active and (qMaster.State = dsInsert);
  
  // Configurar sgIvaDesglose
  sgIvaDesglose.ColCount := 3;
  sgIvaDesglose.RowCount := 2;
  sgIvaDesglose.FixedRows := 1;
  sgIvaDesglose.FixedCols := 0;
  sgIvaDesglose.Cells[0, 0] := '% IVA';
  sgIvaDesglose.Cells[1, 0] := 'Base Imponible';
  sgIvaDesglose.Cells[2, 0] := 'Cuota IVA';
  sgIvaDesglose.ColWidths[0] := 60;
  sgIvaDesglose.ColWidths[1] := 140;
  sgIvaDesglose.ColWidths[2] := 140;

  qDetail.Close;
  qDetail.AfterDelete := qDetailAfterPost;
  qSelectedProv.Close;
  qMasterAfterScroll(qMaster);
end;

function TfrmProvFacturasEditor.IsNewRecord: Boolean;
begin
  Result := FIsNewRecord;
end;

procedure TfrmProvFacturasEditor.qMasterAfterScroll(DataSet: TDataSet);
begin
  if (qMaster.State = dsBrowse) and (not qMaster.FieldByName('id').IsNull) then
  begin
    qDetail.Close;
    qDetail.ParamByName('id').AsString := qMaster.FieldByName('id').AsString;
    qDetail.ParamByName('Idempresa').AsString := qMaster.FieldByName('idempresa').AsString;
    qDetail.Open;
    FormatNumericFields(qDetail);
    AdjustFormWidthToGrid(dbgItems);
    
    qDetail.FieldByName('bultos').OnChange := qDetailBultosChange;
    qDetail.FieldByName('cantidad_bulto').OnChange := qDetailCantidadBultoChange;
    qDetail.FieldByName('descuento_porcentaje').OnChange := qDetailDescuentoPorcentajeChange;
    qDetail.FieldByName('descuento_precio').OnChange := qDetailDescuentoPrecioChange;
    qDetail.FieldByName('precio').OnChange := qDetailPrecioChange;
    
    CalcularTotales;
    
    qSelectedProv.Close;
    qSelectedProv.ParamByName('idproveedor').AsString := qMaster.FieldByName('idproveedor').AsString;
    qSelectedProv.Open;
  end
  else
  begin
    qDetail.Close;
    qSelectedProv.Close;
  end;
end;

procedure TfrmProvFacturasEditor.btnAnadirClick(Sender: TObject);
begin
  FIsNewRecord := True;
  qMaster.Append;
  qMaster.FieldByName('idempresa').AsString := dmgMain.CurrentCompanyId;
  qMaster.FieldByName('fecha').AsDateTime := Date;
  dbProv.SetFocus;
end;

procedure TfrmProvFacturasEditor.btnGuardarClick(Sender: TObject);
begin
  if qMaster.State in [dsInsert, dsEdit] then
    qMaster.Post;
  if qDetail.State in [dsInsert, dsEdit] then
    qDetail.Post;
  FIsNewRecord := False;
  ResetChangeTracking;
  ShowMessage('Factura de proveedor guardada correctamente.');
end;

procedure TfrmProvFacturasEditor.btnEliminarClick(Sender: TObject);
begin
  if MessageDlg('¿Desea eliminar esta factura?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
  begin
    qMaster.Delete;
    ModalResult := mrOk;
  end;
end;

procedure TfrmProvFacturasEditor.qDetailBeforePost(DataSet: TDataSet);
begin
  qDetail.FieldByName('idempresa').AsString := qMaster.FieldByName('idempresa').AsString;
  qDetail.FieldByName('idfactura').AsString := qMaster.FieldByName('id').AsString;
  qDetail.FieldByName('idproveedor').AsString := qMaster.FieldByName('idproveedor').AsString;
  
  // Calculate line total
  qDetail.FieldByName('base').AsFloat := qDetail.FieldByName('cantidad').AsFloat * (qDetail.FieldByName('precio').AsFloat - Abs(qDetail.FieldByName('descuento_precio').AsFloat));
  qDetail.FieldByName('total').AsFloat := qDetail.FieldByName('base').AsFloat * (1 + (qDetail.FieldByName('tipo_iva').AsInteger / 100));
end;

procedure TfrmProvFacturasEditor.qDetailAfterPost(DataSet: TDataSet);
var
  LPrecioFinal: Double;
  LVarResult: TVariacionPrecioResult;
  LEmpresaId, LProvId, LDocId, LLineaId, LMatId, LUserId: Integer;
  LQryAux: TFDQuery;
begin
  CalcularTotales;

  if not qDetail.IsEmpty then
  begin
    LEmpresaId := StrToIntDef(qMaster.FieldByName('idempresa').AsString, 1);
    LProvId := qMaster.FieldByName('idproveedor').AsInteger;
    LDocId := qMaster.FieldByName('id').AsInteger;
    LLineaId := qDetail.FieldByName('id').AsInteger;
    LMatId := qDetail.FieldByName('id_propio').AsInteger;
    LUserId := StrToIntDef(dmgMain.CurrentUserId, 1);

    if LLineaId <= 0 then
    begin
      LQryAux := TFDQuery.Create(nil);
      try
        LQryAux.Connection := dmgMain.dbConn;
        LQryAux.SQL.Text := 'SELECT LAST_INSERT_ID() AS last_id';
        LQryAux.Open;
        LLineaId := LQryAux.FieldByName('last_id').AsInteger;
      finally
        LQryAux.Free;
      end;
    end;

    if LMatId <= 0 then
    begin
      LQryAux := TFDQuery.Create(nil);
      try
        LQryAux.Connection := dmgMain.dbConn;
        LQryAux.SQL.Text :=
          'SELECT id_materia_prima FROM ge_proveedores_materias_primas ' +
          'WHERE id_proveedor = :prov AND codigo_articulo_proveedor = :cod LIMIT 1';
        LQryAux.ParamByName('prov').AsInteger := LProvId;
        LQryAux.ParamByName('cod').AsString := Trim(qDetail.FieldByName('codigo').AsString);
        LQryAux.Open;
        if not LQryAux.IsEmpty then
          LMatId := LQryAux.FieldByName('id_materia_prima').AsInteger;
      finally
        LQryAux.Free;
      end;
    end;

    LPrecioFinal := TProvPreciosService.CalcularPrecioFinalUnitario(
      qDetail.FieldByName('precio').AsFloat,
      qDetail.FieldByName('descuento_precio').AsFloat,
      qDetail.FieldByName('descuento_porcentaje').AsFloat,
      qDetail.FieldByName('cantidad').AsFloat
    );

    if (LDocId > 0) and (LLineaId > 0) and (LPrecioFinal > 0) then
    begin
      LVarResult := TProvPreciosService.ComprobarYRegistrarVariacion(
        dmgMain.dbConn,
        LEmpresaId,
        LProvId,
        qDetail.FieldByName('codigo').AsString,
        qDetail.FieldByName('descripcion').AsString,
        LMatId,
        'FACTURA',
        LDocId,
        LLineaId,
        qMaster.FieldByName('documento').AsString,
        qMaster.FieldByName('fecha').AsDateTime,
        LPrecioFinal,
        LUserId
      );
    end;
  end;
end;

procedure TfrmProvFacturasEditor.CalcularTotales;
var
  LBase, LIVA, LTotal: Double;
  Bookmark: TBookmark;
  LVatBases: TDictionary<Double, Double>;
  LVatAmounts: TDictionary<Double, Double>;
  LRate: Double;
  LKey: Double;
  LLineBase, LLineIva: Double;
  LRow: Integer;
begin
  LBase := 0;
  LIVA := 0;
  LTotal := 0;
  
  LVatBases := TDictionary<Double, Double>.Create;
  LVatAmounts := TDictionary<Double, Double>.Create;
  try
    Bookmark := qDetail.GetBookmark;
    qDetail.DisableControls;
    try
      qDetail.First;
      while not qDetail.Eof do
      begin
        LLineBase := qDetail.FieldByName('base').AsFloat;
        LBase := LBase + LLineBase;
        LTotal := LTotal + qDetail.FieldByName('total').AsFloat;
        LLineIva := qDetail.FieldByName('total').AsFloat - LLineBase;
        
        LRate := qDetail.FieldByName('tipo_iva').AsFloat;
        if LVatBases.ContainsKey(LRate) then
        begin
          LVatBases[LRate] := LVatBases[LRate] + LLineBase;
          LVatAmounts[LRate] := LVatAmounts[LRate] + LLineIva;
        end
        else
        begin
          LVatBases.Add(LRate, LLineBase);
          LVatAmounts.Add(LRate, LLineIva);
        end;
        qDetail.Next;
      end;
      LIVA := LTotal - LBase;
      
      if qMaster.State = dsbrowse then qMaster.Edit;
      qMaster.FieldByName('base').AsFloat := LBase;
      qMaster.FieldByName('totaliva').AsFloat := LIVA;
      qMaster.FieldByName('totalentrada').AsFloat := LTotal;
      qMaster.Post;
    finally
      qDetail.GotoBookmark(Bookmark);
      qDetail.FreeBookmark(Bookmark);
      qDetail.EnableControls;
    end;

    // Rellenar sgIvaDesglose
    sgIvaDesglose.RowCount := 2;
    sgIvaDesglose.Cells[0, 1] := '';
    sgIvaDesglose.Cells[1, 1] := '';
    sgIvaDesglose.Cells[2, 1] := '';

    LRow := 1;
    for LKey in LVatAmounts.Keys do
    begin
      if LRow >= sgIvaDesglose.RowCount then
        sgIvaDesglose.RowCount := LRow + 1;
      sgIvaDesglose.Cells[0, LRow] := FormatFloat('0.#', LKey) + '%';
      sgIvaDesglose.Cells[1, LRow] := FormatFloat('#,##0.00', LVatBases[LKey]);
      sgIvaDesglose.Cells[2, LRow] := FormatFloat('#,##0.00', LVatAmounts[LKey]);
      Inc(LRow);
    end;
  finally
    LVatBases.Free;
    LVatAmounts.Free;
  end;
end;

procedure TfrmProvFacturasEditor.qSelectedProvCalcFields(DataSet: TDataSet);
var
  FullAddr: string;
begin
  FullAddr := DataSet.FieldByName('direccion').AsString;
  if DataSet.FieldByName('poblacion').AsString <> '' then
    FullAddr := FullAddr + ', ' + DataSet.FieldByName('poblacion').AsString;
  if DataSet.FieldByName('codpost').AsString <> '' then
    FullAddr := FullAddr + ' (' + DataSet.FieldByName('codpost').AsString + ')';
  if DataSet.FieldByName('provincia').AsString <> '' then
    FullAddr := FullAddr + ' - ' + DataSet.FieldByName('provincia').AsString;
    
  DataSet.FieldByName('DireccionCompleta').AsString := FullAddr;
end;

procedure TfrmProvFacturasEditor.btnPrimeroClick(Sender: TObject);
begin
  DoPrimero;
end;

procedure TfrmProvFacturasEditor.btnAnteriorClick(Sender: TObject);
begin
  DoAnterior;
end;

procedure TfrmProvFacturasEditor.btnSiguienteClick(Sender: TObject);
begin
  DoSiguiente;
end;

procedure TfrmProvFacturasEditor.btnUltimoClick(Sender: TObject);
begin
  DoUltimo;
end;

procedure TfrmProvFacturasEditor.DoPrimero;
begin
  qMaster.First;
end;

procedure TfrmProvFacturasEditor.DoAnterior;
begin
  qMaster.Prior;
end;

procedure TfrmProvFacturasEditor.DoSiguiente;
begin
  qMaster.Next;
end;

procedure TfrmProvFacturasEditor.DoUltimo;
begin
  qMaster.Last;
end;

procedure TfrmProvFacturasEditor.btnImprimirClick(Sender: TObject);
var
  Qry: TFDQuery;
  BlobField: TBlobField;
  TempPath, TempFile: string;
  Stream: TMemoryStream;
  LFacturaId: string;
begin
  LFacturaId := qMaster.FieldByName('id').AsString;
  if LFacturaId = '' then
  begin
    ShowMessage('No hay ningún registro de factura seleccionado para buscar su documento.');
    Exit;
  end;

  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := dmgMain.dbConn;
    Qry.SQL.Text := 'SELECT archivo, nombre_archivo, extension FROM ge_documentos WHERE id_factura_proveedor = :id LIMIT 1';
    Qry.ParamByName('id').AsString := LFacturaId;
    Qry.Open;

    if Qry.IsEmpty then
    begin
      ShowMessage('No se ha encontrado ningún documento digitalizado asociado a esta factura.');
      Exit;
    end;

    BlobField := TBlobField(Qry.FieldByName('archivo'));
    if BlobField.IsNull or (BlobField.BlobSize = 0) then
    begin
      ShowMessage('El documento asociado está vacío.');
      Exit;
    end;

    TempPath := TPath.GetTempPath;
    TempFile := TPath.Combine(TempPath, Qry.FieldByName('nombre_archivo').AsString);
    if TempFile = '' then
      TempFile := TPath.Combine(TempPath, 'factura_' + LFacturaId + '.' + Qry.FieldByName('extension').AsString);

    Stream := TMemoryStream.Create;
    try
      BlobField.SaveToStream(Stream);
      Stream.Position := 0;
      Stream.SaveToFile(TempFile);
    finally
      Stream.Free;
    end;

    ShellExecute(0, 'open', PChar(TempFile), nil, nil, SW_SHOWNORMAL);
  finally
    Qry.Free;
  end;
end;

procedure TfrmProvFacturasEditor.qDetailDescuentoPorcentajeChange(Sender: TField);
var
  LPrecio: Double;
  LPorc: Double;
  LDescPrecio: Double;
begin
  if qDetail.State in [dsEdit, dsInsert] then
  begin
    LPrecio := qDetail.FieldByName('precio').AsFloat;
    LPorc := Abs(Sender.AsFloat);
    LDescPrecio := LPrecio * (LPorc / 100);
    qDetail.FieldByName('descuento_precio').OnChange := nil;
    try
      qDetail.FieldByName('descuento_precio').AsFloat := LDescPrecio;
    finally
      qDetail.FieldByName('descuento_precio').OnChange := qDetailDescuentoPrecioChange;
    end;
  end;
end;

procedure TfrmProvFacturasEditor.qDetailDescuentoPrecioChange(Sender: TField);
var
  LPrecio: Double;
  LDescPrecio: Double;
  LPorc: Double;
begin
  if qDetail.State in [dsEdit, dsInsert] then
  begin
    LPrecio := qDetail.FieldByName('precio').AsFloat;
    LDescPrecio := Abs(Sender.AsFloat);
    if LPrecio <> 0 then
      LPorc := (LDescPrecio / LPrecio) * 100
    else
      LPorc := 0;
    qDetail.FieldByName('descuento_porcentaje').OnChange := nil;
    try
      qDetail.FieldByName('descuento_porcentaje').AsFloat := LPorc;
    finally
      qDetail.FieldByName('descuento_porcentaje').OnChange := qDetailDescuentoPorcentajeChange;
    end;
  end;
end;

procedure TfrmProvFacturasEditor.qDetailPrecioChange(Sender: TField);
var
  LPrecio: Double;
  LPorc: Double;
  LDescPrecio: Double;
begin
  if qDetail.State in [dsEdit, dsInsert] then
  begin
    LPrecio := Sender.AsFloat;
    LPorc := Abs(qDetail.FieldByName('descuento_porcentaje').AsFloat);
    LDescPrecio := LPrecio * (LPorc / 100);
    qDetail.FieldByName('descuento_precio').OnChange := nil;
    try
      qDetail.FieldByName('descuento_precio').AsFloat := LDescPrecio;
    finally
      qDetail.FieldByName('descuento_precio').OnChange := qDetailDescuentoPrecioChange;
    end;
  end;
end;

procedure TfrmProvFacturasEditor.qDetailBultosChange(Sender: TField);
begin
  if qDetail.State in [dsEdit, dsInsert] then
  begin
    qDetail.FieldByName('cantidad').AsFloat := Sender.AsFloat * qDetail.FieldByName('cantidad_bulto').AsFloat;
  end;
end;

procedure TfrmProvFacturasEditor.qDetailCantidadBultoChange(Sender: TField);
var
  LOldCantBulto: Double;
  LNewCantBulto: Double;
  LOldPrecio: Double;
begin
  if qDetail.State in [dsEdit, dsInsert] then
  begin
    qDetail.FieldByName('cantidad').AsFloat := qDetail.FieldByName('bultos').AsFloat * Sender.AsFloat;

    // Recalcular el precio si la cantidad por bulto anterior era exactamente 1
    if not VarIsNull(Sender.OldValue) then
      LOldCantBulto := Sender.OldValue
    else
      LOldCantBulto := 1.0;

    LNewCantBulto := Sender.AsFloat;

    if (LOldCantBulto = 1.0) and (LNewCantBulto > 0) then
    begin
      if not VarIsNull(qDetail.FieldByName('precio').OldValue) then
        LOldPrecio := qDetail.FieldByName('precio').OldValue
      else
        LOldPrecio := qDetail.FieldByName('precio').AsFloat;

      qDetail.FieldByName('precio').AsFloat := LOldPrecio / LNewCantBulto;
    end;
  end;
end;

procedure TfrmProvFacturasEditor.FormatNumericFields(DataSet: TDataSet);
var
  i: Integer;
  Fld: TField;
begin
  for i := 0 to DataSet.FieldCount - 1 do
  begin
    Fld := DataSet.Fields[i];
    if Fld is TNumericField then
    begin
      if SameText(Fld.FieldName, 'cantidad') then
        TNumericField(Fld).DisplayFormat := '0.##'
      else if SameText(Fld.FieldName, 'bultos') or SameText(Fld.FieldName, 'cantidad_bulto') then
        TNumericField(Fld).DisplayFormat := '0.##'
      else if SameText(Fld.FieldName, 'precio') then
        TNumericField(Fld).DisplayFormat := '#,##0.00##'
      else if SameText(Fld.FieldName, 'descuento_porcentaje') or SameText(Fld.FieldName, 'descuento_precio') then
        TNumericField(Fld).DisplayFormat := '0.##'
      else if SameText(Fld.FieldName, 'base') or SameText(Fld.FieldName, 'total') then
        TNumericField(Fld).DisplayFormat := '#,##0.00';
    end;
  end;
end;

end.