unit frm_SelectLote;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls, Vcl.Grids, Vcl.DBGrids,
  Data.DB, FireDAC.Comp.Client, uAppTheme, frm_BaseSelect;

type
  TfrmSelectLote = class(TfrmBaseSelect)
  private
    FArticuloId: Integer;
    FSelectedLoteCode: string;
    FSelectedFechaCaducidad: TDateTime;
    FHasFechaCaducidad: Boolean;
  protected
    procedure DoSearch; override;
    procedure DoSelect; override;
  public
    constructor Create(AOwner: TComponent); override;
    property ArticuloId: Integer read FArticuloId write FArticuloId;
    property SelectedLoteCode: string read FSelectedLoteCode;
    property SelectedFechaCaducidad: TDateTime read FSelectedFechaCaducidad;
    property HasFechaCaducidad: Boolean read FHasFechaCaducidad;
  end;

var
  frmSelectLote: TfrmSelectLote;

implementation

uses
  dmg_Main;

{$R *.dfm}

constructor TfrmSelectLote.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FArticuloId := 0;
  FSelectedLoteCode := '';
  FHasFechaCaducidad := False;
end;

procedure TfrmSelectLote.DoSearch;
var
  LTargetArtId: Integer;
begin
  if FArticuloId <= 0 then Exit;

  // Si el artículo tiene un id_maestro configurado, buscar los lotes y el stock del maestro
  LTargetArtId := FArticuloId;
  dmgMain.qryExec.Close;
  dmgMain.qryExec.SQL.Text := 'SELECT COALESCE(NULLIF(id_maestro, 0), id) AS target_id FROM ge_articulos WHERE id = :id';
  dmgMain.qryExec.ParamByName('id').AsInteger := FArticuloId;
  dmgMain.qryExec.Open;
  if not dmgMain.qryExec.IsEmpty then
    LTargetArtId := dmgMain.qryExec.FieldByName('target_id').AsInteger;
  dmgMain.qryExec.Close;

  dmgMain.qryExec.SQL.Text := 
    'SELECT ' +
    '  l.id, ' +
    '  COALESCE(NULLIF(l.codigo_antiguo, ''''), l.codigo) AS codigo_str, ' +
    '  COALESCE(NULLIF(l.codigo_antiguo, ''''), l.codigo) AS lote, ' +
    '  COALESCE(SUM(se.cantidad_unidades), 0) AS stock, ' +
    '  l.fecha AS fecha_elaboracion, ' +
    '  (CASE ' +
    '     WHEN l.fecha IS NOT NULL AND MAX(art.dias_vida) > 0 THEN DATE_ADD(l.fecha, INTERVAL MAX(art.dias_vida) DAY) ' +
    '     ELSE NULL ' +
    '   END) AS fecha_caducidad, ' +
    '  (CASE WHEN COALESCE(SUM(se.cantidad_unidades), 0) > 0 THEN 0 ELSE 1 END) AS grupo_stock ' +
    'FROM ge_lotes l ' +
    'LEFT JOIN ge_stock_envasado se ON se.id_lote = l.id AND se.id_articulo = :art ' +
    'LEFT JOIN ge_articulos art ON art.id = :art ' +
    'WHERE (se.id_articulo = :art OR EXISTS ( ' +
    '  SELECT 1 FROM ge_albaranes_lineas_bultos lb ' +
    '  INNER JOIN ge_albaranes_lineas al ON lb.linea_albaran_id = al.id ' +
    '  WHERE (al.articulo_id = :art OR al.articulo_id = :orig_art) ' +
    '    AND (lb.lote = l.codigo OR lb.lote = l.codigo_antiguo))) ';

  dmgMain.qryExec.ParamByName('art').AsInteger := LTargetArtId;
  dmgMain.qryExec.ParamByName('orig_art').AsInteger := FArticuloId;

  if Trim(edtSearch.Text) <> '' then
  begin
    dmgMain.qryExec.SQL.Add('AND (UPPER(l.codigo) LIKE UPPER(:search) OR UPPER(COALESCE(l.codigo_antiguo, '''')) LIKE UPPER(:search)) ');
    dmgMain.qryExec.ParamByName('search').AsString := '%' + Trim(edtSearch.Text) + '%';
  end;

  dmgMain.qryExec.SQL.Add(
    'GROUP BY l.id, l.codigo, l.codigo_antiguo, l.fecha ' +
    'ORDER BY ' +
    '  (CASE WHEN COALESCE(SUM(se.cantidad_unidades), 0) > 0 THEN 0 ELSE 1 END) ASC, ' +
    '  COALESCE(l.fecha, l.created_at) DESC, ' +
    '  l.id DESC'
  );

  dmgMain.qryExec.Open;

  if dmgMain.qryExec.FindField('id') <> nil then
    dmgMain.qryExec.FieldByName('id').Visible := False;

  if dmgMain.qryExec.FindField('codigo_str') <> nil then
    dmgMain.qryExec.FieldByName('codigo_str').Visible := False;

  if dmgMain.qryExec.FindField('grupo_stock') <> nil then
    dmgMain.qryExec.FieldByName('grupo_stock').Visible := False;

  if dmgMain.qryExec.FindField('lote') <> nil then
  begin
    dmgMain.qryExec.FieldByName('lote').DisplayLabel := 'Código de Lote';
    dmgMain.qryExec.FieldByName('lote').DisplayWidth := 20;
  end;

  if dmgMain.qryExec.FindField('stock') <> nil then
  begin
    dmgMain.qryExec.FieldByName('stock').DisplayLabel := 'Stock Disponible';
    dmgMain.qryExec.FieldByName('stock').DisplayWidth := 15;
    if dmgMain.qryExec.FieldByName('stock') is TNumericField then
      TNumericField(dmgMain.qryExec.FieldByName('stock')).DisplayFormat := '#,##0.00';
  end;

  if dmgMain.qryExec.FindField('fecha_elaboracion') <> nil then
  begin
    dmgMain.qryExec.FieldByName('fecha_elaboracion').DisplayLabel := 'F. Elaboración';
    dmgMain.qryExec.FieldByName('fecha_elaboracion').DisplayWidth := 15;
  end;

  if dmgMain.qryExec.FindField('fecha_caducidad') <> nil then
  begin
    dmgMain.qryExec.FieldByName('fecha_caducidad').DisplayLabel := 'F. Caducidad';
    dmgMain.qryExec.FieldByName('fecha_caducidad').DisplayWidth := 15;
  end;
end;

procedure TfrmSelectLote.DoSelect;
begin
  if not dmgMain.qryExec.IsEmpty then
  begin
    FSelectedLoteCode := dmgMain.qryExec.FieldByName('lote').AsString;
    
    FHasFechaCaducidad := not dmgMain.qryExec.FieldByName('fecha_caducidad').IsNull;
    if FHasFechaCaducidad then
      FSelectedFechaCaducidad := dmgMain.qryExec.FieldByName('fecha_caducidad').AsDateTime;
      
    ModalResult := mrOk;
  end;
end;

end.