unit fDirectasCampo;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes,
  Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Vcl.StdCtrls,
  ZAbstractRODataset, ZDataset, Vcl.ExtCtrls, Vcl.DBCtrls, Vcl.Grids,
  Vcl.DBGrids, Vcl.ComCtrls, Vcl.Buttons, ComObj, Excel2000, dateutils;

type
  TfrmDirectasCampo = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    grDatos: TDBGrid;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    listacampanyas: TDBLookupComboBox;
    dtpDesde: TDateTimePicker;
    dtpHasta: TDateTimePicker;
    cbAgrupacion: TComboBox;
    cbVariedad: TComboBox;
    btnFiltrar: TButton;
    btnExportar: TButton;
    btnCerrar: TButton;
    qcampanyas: TZReadOnlyQuery;
    qPesadas: TZQuery;
    dsqcampanyas: TDataSource;
    dsqPesadas: TDataSource;
    SaveDialog1: TSaveDialog;
    qcampanyasid: TIntegerField;
    qcampanyasidempresa: TIntegerField;
    qcampanyasdescripcion: TWideStringField;
    qcampanyasfechainicio: TDateField;
    qcampanyasfechafin: TDateField;
    procedure FormShow(Sender: TObject);
    procedure listacampanyasCloseUp(Sender: TObject);
    procedure btnFiltrarClick(Sender: TObject);
    procedure btnExportarClick(Sender: TObject);
    procedure btnCerrarClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure ExportaraExcel(FileNameXLS, SheetName: String; DBGrid: TDBGrid);
  end;

var
  frmDirectasCampo: TfrmDirectasCampo;

implementation

{$R *.dfm}

uses datos;

procedure TfrmDirectasCampo.FormShow(Sender: TObject);
begin
  // Populate Varieties ComboBox dynamically
  cbVariedad.Items.Clear;
  cbVariedad.Items.Add('TODAS');
  qPesadas.Close;
  qPesadas.SQL.Text := 'select distinct mprima from ea_pesadas where mprima is not null and mprima <> '''' order by mprima';
  try
    qPesadas.Open;
    while not qPesadas.Eof do
    begin
      cbVariedad.Items.Add(qPesadas.Fields[0].AsString);
      qPesadas.Next;
    end;
  finally
    qPesadas.Close;
  end;
  cbVariedad.ItemIndex := 0;

  qcampanyas.Close;
  qcampanyas.Open;
  if not qcampanyas.IsEmpty then
  begin
    listacampanyas.KeyValue := qcampanyasid.AsInteger;
    listacampanyasCloseUp(Self);
  end;
  cbAgrupacion.ItemIndex := 0;
  btnFiltrar.Click;
end;

procedure TfrmDirectasCampo.listacampanyasCloseUp(Sender: TObject);
begin
  if listacampanyas.KeyValue <> Null then
  begin
    if qcampanyas.Locate('id', listacampanyas.KeyValue, []) then
    begin
      dtpDesde.Date := qcampanyasfechainicio.AsDateTime;
      dtpHasta.Date := qcampanyasfechafin.AsDateTime;
    end;
  end;
end;

procedure TfrmDirectasCampo.btnFiltrarClick(Sender: TObject);
var
  sqlText: string;
  fDesdeStr, fHastaStr: string;
  anyo, mes, dia: word;
  filtroVariedad: string;
begin
  decodedate(dtpDesde.Date, anyo, mes, dia);
  fDesdeStr := Format('%d-%.2d-%.2d', [anyo, mes, dia]);
  decodedate(dtpHasta.Date, anyo, mes, dia);
  fHastaStr := Format('%d-%.2d-%.2d', [anyo, mes, dia]);

  filtroVariedad := '';
  if (cbVariedad.ItemIndex > 0) and (cbVariedad.Text <> 'TODAS') then
  begin
    filtroVariedad := ' and ea.mprima = ' + QuotedStr(cbVariedad.Text);
  end;

  qPesadas.Close;
  qPesadas.SQL.Clear;

  case cbAgrupacion.ItemIndex of
    0: // Detallado
      begin
        sqlText := 'select ea.id, ea.fecha, ea.fechatrabajo, ea.num_palets, aa.descripcion as envase, ea.num_envases, ' +
                   'ea.destino, ea.peso_bruto, ea.tara, ea.peso_neto, ea.parcela, ea.mprima as variedad, ' +
                   'ea.idalbaran as albaran, ea.idfactura as factura, cfg.descripcion as acabado, ' +
                   '(select alb.nomcliente from albaranes alb where alb.id = ea.idalbaran and alb.idempresa = ea.idempresa limit 1) as cliente ' +
                   'from ea_pesadas ea ' +
                   'left join articulos_almacen aa on aa.codigo = ea.id_envase and aa.ide = ea.idempresa ' +
                   'left join lotes_pesadas lp on lp.ide = ea.idempresa and lp.idea = ea.id and lp.idea2 = ea.idEA ' +
                   'left join configuraciones cfg on cfg.id = lp.id_formato and cfg.ide = lp.ide ' +
                   'where ea.idEA = 0 and ea.borrado = 0 ' +
                   'and ea.fecha >= ' + QuotedStr(fDesdeStr) + ' and ea.fecha <= ' + QuotedStr(fHastaStr) +
                   filtroVariedad + ' ' +
                   'and exists (select 1 from ea_pesadas ea2 where ea2.id = ea.id and ea2.idEA <> 0 and ea2.borrado = 0) ' +
                   'order by ea.fecha asc, ea.id asc';
      end;
    1: // Agrupado por Parcela
      begin
        sqlText := 'select ea.parcela, sum(ea.num_palets) as total_palets, sum(ea.num_envases) as total_envases, ' +
                   'sum(ea.peso_bruto) as total_bruto, sum(ea.tara) as total_tara, sum(ea.peso_neto) as total_neto, ' +
                   'count(ea.id) as total_cargas ' +
                   'from ea_pesadas ea ' +
                   'where ea.idEA = 0 and ea.borrado = 0 ' +
                   'and ea.fecha >= ' + QuotedStr(fDesdeStr) + ' and ea.fecha <= ' + QuotedStr(fHastaStr) +
                   filtroVariedad + ' ' +
                   'and exists (select 1 from ea_pesadas ea2 where ea2.id = ea.id and ea2.idEA <> 0 and ea2.borrado = 0) ' +
                   'group by ea.parcela ' +
                   'order by ea.parcela asc';
      end;
    2: // Agrupado por Variedad
      begin
        sqlText := 'select ea.mprima as variedad, sum(ea.num_palets) as total_palets, sum(ea.num_envases) as total_envases, ' +
                   'sum(ea.peso_bruto) as total_bruto, sum(ea.tara) as total_tara, sum(ea.peso_neto) as total_neto, ' +
                   'count(ea.id) as total_cargas ' +
                   'from ea_pesadas ea ' +
                   'where ea.idEA = 0 and ea.borrado = 0 ' +
                   'and ea.fecha >= ' + QuotedStr(fDesdeStr) + ' and ea.fecha <= ' + QuotedStr(fHastaStr) +
                   filtroVariedad + ' ' +
                   'and exists (select 1 from ea_pesadas ea2 where ea2.id = ea.id and ea2.idEA <> 0 and ea2.borrado = 0) ' +
                   'group by ea.mprima ' +
                   'order by ea.mprima asc';
      end;
    3: // Agrupado por Parcela y Variedad
      begin
        sqlText := 'select ea.parcela, ea.mprima as variedad, sum(ea.num_palets) as total_palets, sum(ea.num_envases) as total_envases, ' +
                   'sum(ea.peso_bruto) as total_bruto, sum(ea.tara) as total_tara, sum(ea.peso_neto) as total_neto, ' +
                   'count(ea.id) as total_cargas ' +
                   'from ea_pesadas ea ' +
                   'where ea.idEA = 0 and ea.borrado = 0 ' +
                   'and ea.fecha >= ' + QuotedStr(fDesdeStr) + ' and ea.fecha <= ' + QuotedStr(fHastaStr) +
                   filtroVariedad + ' ' +
                   'and exists (select 1 from ea_pesadas ea2 where ea2.id = ea.id and ea2.idEA <> 0 and ea2.borrado = 0) ' +
                   'group by ea.parcela, ea.mprima ' +
                   'order by ea.parcela asc, ea.mprima asc';
      end;
  end;

  qPesadas.SQL.Add(sqlText);
  qPesadas.Open;
end;

procedure TfrmDirectasCampo.btnExportarClick(Sender: TObject);
var
  archivo: string;
begin
  SaveDialog1.FileName := 'CARGAS_DIRECTAS_CAMPO.xlsx';
  if SaveDialog1.Execute then
  begin
    archivo := SaveDialog1.FileName;
    try
      ExportaraExcel(archivo, 'Cargas Directas', grDatos);
      ShowMessage('Los datos han sido exportados correctamente a ' + archivo);
    except
      on E: Exception do
        ShowMessage('Error al exportar a Excel: ' + E.Message);
    end;
  end;
end;

procedure TfrmDirectasCampo.btnCerrarClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmDirectasCampo.ExportaraExcel(FileNameXLS, SheetName: String; DBGrid: TDBGrid);
const
  xlWBATworksheet = -4167;
var
  Excel, WorkBook, WorkSheet: OleVariant;
  I, J: Integer;
  PBookmark: TBookmark;
begin
  PBookmark := DBGrid.DataSource.DataSet.GetBookmark;
  DBGrid.DataSource.DataSet.DisableControls;
  try
    DBGrid.DataSource.DataSet.First;

    Excel := CreateOleObject('Excel.Application');
    Excel.DisplayAlerts := False;
    WorkBook := Excel.Workbooks.Add(xlWBATworksheet);
    WorkSheet := WorkBook.WorkSheets[1];
    WorkSheet.Name := SheetName;

    // Headers
    for J := 0 to DBGrid.FieldCount - 1 do
      if DBGrid.Columns[J].Visible then
      begin
        WorkSheet.Cells[1, J + 1].Font.Bold := True;
        WorkSheet.Cells[1, J + 1] := DBGrid.Columns[J].Title.Caption;
      end;

    // Data rows
    for I := 0 to DBGrid.DataSource.DataSet.RecordCount - 1 do
    begin
      for J := 0 to DBGrid.FieldCount - 1 do
        if DBGrid.Columns[J].Visible then
        begin
          case DBGrid.Fields[J].DataType of
            ftAutoInc, ftBytes, ftInteger, ftSmallint, ftWord:
              WorkSheet.Cells[I + 2, J + 1] := DBGrid.Fields[J].AsInteger;
            ftBCD, ftFloat, ftCurrency:
              WorkSheet.Cells[I + 2, J + 1] := DBGrid.Fields[J].AsFloat;
            ftDateTime, ftDate, ftTime:
              WorkSheet.Cells[I + 2, J + 1] := DBGrid.Fields[J].AsDateTime;
            else
              WorkSheet.Cells[I + 2, J + 1] := DBGrid.Fields[J].AsString;
          end;
        end;
      DBGrid.DataSource.DataSet.Next;
    end;

    WorkSheet.Cells.Columns.AutoFit;
    WorkBook.SaveAs(FileNameXLS);
    WorkBook.Close(FileNameXLS);
    Excel.Quit;
  finally
    DBGrid.DataSource.DataSet.GotoBookmark(PBookmark);
    DBGrid.DataSource.DataSet.FreeBookmark(PBookmark);
    DBGrid.DataSource.DataSet.EnableControls;
  end;
end;

end.