unit frm_LotesCreator;

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.Buttons,
  Vcl.ComCtrls, Data.DB, FireDAC.Comp.Client, FireDAC.Stan.Param, System.Generics.Collections, uBaseForm,
  Vcl.Grids, Vcl.DBGrids;

type
  TfrmLotesCreator = class(TfrmBase)
    lblCodigo: TLabel;
    edtCodigo: TEdit;
    lblCodigoAntiguo: TLabel;
    edtCodigoAntiguo: TEdit;
    lblFecha: TLabel;
    dtpFecha: TDateTimePicker;
    lblArticulo: TLabel;
    edtArticulo: TEdit;
    btnBuscarProd: TButton;
    lblObrador: TLabel;
    cbObrador: TComboBox;
    lblObservaciones: TLabel;
    memObservaciones: TMemo;
    tsTrazabilidadVentas: TTabSheet;
    pnlTrazabilidadTop: TPanel;
    lblResumenTrazabilidad: TLabel;
    btnVerDocumento: TButton;
    btnActualizarTraz: TButton;
    dbgTrazabilidadVentas: TDBGrid;
    qTrazabilidadVentas: TFDQuery;
    dsTrazabilidadVentas: TDataSource;

    procedure FormShow(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnBuscarProdClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnSalirClick(Sender: TObject);
    procedure btnEliminarClick(Sender: TObject);
    procedure btnVerDocumentoClick(Sender: TObject);
    procedure btnActualizarTrazClick(Sender: TObject);
    procedure dbgTrazabilidadVentasDblClick(Sender: TObject);
    procedure dbgTrazabilidadVentasTitleClick(Column: TColumn);
    procedure pgcDetailsChange(Sender: TObject);
  protected
    procedure DoAnadir; override;
    procedure DoModificar; override;
    procedure DoPrimero; override;
    procedure DoAnterior; override;
    procedure DoSiguiente; override;
    procedure DoUltimo; override;
    function IsNewRecord: Boolean; override;
  private
    FLoteId: Integer;
    FSelectedArticuloId: Integer;
    FObradorIds: TList<Integer>;
    FOpenTraceabilityTab: Boolean;
    FTrazabilidadLoaded: Boolean;
    FParentForm: TForm;
    procedure LoadObradores;
    procedure LoadData;
    procedure SetupTrazabilidadGrid;
    procedure CargarTrazabilidadVentas;
  public
    property LoteId: Integer read FLoteId write FLoteId;
    property OpenTraceabilityTab: Boolean read FOpenTraceabilityTab write FOpenTraceabilityTab;
    property ParentForm: TForm read FParentForm write FParentForm;
  end;

var
  frmLotesCreator: TfrmLotesCreator;

implementation

uses
  dmg_Main, uDbErrorHandler, frm_SelectProduct, uAppTheme, frm_AlbaranesEditor, frm_FacturasEditor, frm_Lotes;

{$R *.dfm}

procedure TfrmLotesCreator.FormShow(Sender: TObject);
begin
  inherited;
  
  TAppTheme.ApplyToForm(Self);

  btnGuardar.OnClick := btnSaveClick;
  btnSalir.OnClick := btnSalirClick;
  btnEliminar.OnClick := btnEliminarClick;

  // Configuración de visibilidad de contenedores heredados
  pgcDetails.Visible := True;
  TsListado.TabVisible := False;
  if FOpenTraceabilityTab then
    pgcDetails.ActivePage := tsTrazabilidadVentas
  else
    pgcDetails.ActivePage := tsDatosEnvio;
  pgcDetails.OnChange := pgcDetailsChange;
  pnlSideToolbar.Visible := False;
  dbgItems.Visible := False;
  pnlHeader.Visible := False;
  pnlFooter.Visible := False;

  if not Assigned(FObradorIds) then
    FObradorIds := TList<Integer>.Create
  else
    FObradorIds.Clear;

  LoadObradores;

  if FLoteId > 0 then
    LoadData
  else
    DoAnadir;
end;

procedure TfrmLotesCreator.FormDestroy(Sender: TObject);
begin
  if Assigned(FObradorIds) then
    FreeAndNil(FObradorIds);
  if frmLotesCreator = Self then
    frmLotesCreator := nil;
end;

procedure TfrmLotesCreator.LoadObradores;
var
  LQry: TFDQuery;
begin
  cbObrador.Items.Clear;
  FObradorIds.Clear;

  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    LQry.SQL.Text := 'SELECT id, nombre FROM ge_obradores WHERE activo = 1 ORDER BY nombre';
    LQry.Open;
    while not LQry.Eof do
    begin
      cbObrador.Items.Add(LQry.FieldByName('nombre').AsString);
      FObradorIds.Add(LQry.FieldByName('id').AsInteger);
      LQry.Next;
    end;
  finally
    LQry.Free;
  end;

  if cbObrador.Items.Count > 0 then
    cbObrador.ItemIndex := 0;
end;

procedure TfrmLotesCreator.LoadData;
var
  LQry: TFDQuery;
  LIdObrador: Integer;
  LIdx: Integer;
begin
  if FLoteId <= 0 then Exit;

  Caption := 'Editar Lote de Producción';

  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := dmgMain.dbConn;
    LQry.SQL.Text := 
      'SELECT l.id, l.codigo, l.codigo_antiguo, l.id_articulo, l.id_obrador, l.fecha, l.observaciones, ' +
      '       a.descripcion as articulo_desc ' +
      'FROM ge_lotes l ' +
      'LEFT JOIN ge_articulos a ON l.id_articulo = a.id ' +
      'WHERE l.id = :id';
    LQry.ParamByName('id').AsInteger := FLoteId;
    LQry.Open;

    if not LQry.IsEmpty then
    begin
      edtCodigo.Text := LQry.FieldByName('codigo').AsString;
      if (LQry.FindField('codigo_antiguo') <> nil) and not LQry.FieldByName('codigo_antiguo').IsNull and (Trim(LQry.FieldByName('codigo_antiguo').AsString) <> '') then
        edtCodigoAntiguo.Text := LQry.FieldByName('codigo_antiguo').AsString
      else
        edtCodigoAntiguo.Text := edtCodigo.Text;

      if not LQry.FieldByName('fecha').IsNull then
        dtpFecha.Date := LQry.FieldByName('fecha').AsDateTime
      else
        dtpFecha.Date := Date;

      FSelectedArticuloId := LQry.FieldByName('id_articulo').AsInteger;
      edtArticulo.Text := LQry.FieldByName('articulo_desc').AsString;
      memObservaciones.Text := LQry.FieldByName('observaciones').AsString;

      LIdObrador := LQry.FieldByName('id_obrador').AsInteger;
      LIdx := FObradorIds.IndexOf(LIdObrador);
      if LIdx >= 0 then
        cbObrador.ItemIndex := LIdx;

      ResetChangeTracking;
      ResetModifiedState;
    end;
  finally
    LQry.Free;
  end;

  FTrazabilidadLoaded := False;
  if pgcDetails.ActivePage = tsTrazabilidadVentas then
    CargarTrazabilidadVentas;
end;

function TfrmLotesCreator.IsNewRecord: Boolean;
begin
  Result := (FLoteId = 0);
end;

procedure TfrmLotesCreator.DoAnadir;
begin
  FLoteId := 0;
  Caption := 'Nuevo Lote de Producción';
  FSelectedArticuloId := 0;
  edtArticulo.Clear;
  dtpFecha.Date := Date;
  memObservaciones.Clear;

  if pgcDetails.ActivePage = tsTrazabilidadVentas then
  begin
    if Assigned(qTrazabilidadVentas) then qTrazabilidadVentas.Close;
    lblResumenTrazabilidad.Caption := 'Nuevo lote: No hay documentos de venta asociados.';
  end;

  // Sugerir el siguiente código numérico de lote automáticamente
  try
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'SELECT COALESCE(MAX(codigo), 0) + 1 AS next_codigo FROM ge_lotes';
    dmgMain.qryExec.Open;
    edtCodigo.Text := dmgMain.qryExec.FieldByName('next_codigo').AsString;
    edtCodigoAntiguo.Text := edtCodigo.Text;
    dmgMain.qryExec.Close;
  except
    edtCodigo.Text := '1';
    edtCodigoAntiguo.Text := '1';
  end;

  if cbObrador.Items.Count > 0 then
    cbObrador.ItemIndex := 0;

  ResetChangeTracking;
  ResetModifiedState;
  edtCodigoAntiguo.SetFocus;
end;

procedure TfrmLotesCreator.DoModificar;
begin
  edtCodigoAntiguo.SetFocus;
end;

procedure TfrmLotesCreator.DoPrimero;
var
  LParent: TfrmLotes;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmLotes) then
    LParent := TfrmLotes(FParentForm)
  else if Assigned(frmLotes) then
    LParent := frmLotes;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.First;
    FLoteId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
    ResetChangeTracking;
    ResetModifiedState;
  end;
end;

procedure TfrmLotesCreator.DoAnterior;
var
  LParent: TfrmLotes;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmLotes) then
    LParent := TfrmLotes(FParentForm)
  else if Assigned(frmLotes) then
    LParent := frmLotes;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Prior;
    FLoteId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
    ResetChangeTracking;
    ResetModifiedState;
  end;
end;

procedure TfrmLotesCreator.DoSiguiente;
var
  LParent: TfrmLotes;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmLotes) then
    LParent := TfrmLotes(FParentForm)
  else if Assigned(frmLotes) then
    LParent := frmLotes;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Next;
    FLoteId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
    ResetChangeTracking;
    ResetModifiedState;
  end;
end;

procedure TfrmLotesCreator.DoUltimo;
var
  LParent: TfrmLotes;
begin
  LParent := nil;
  if Assigned(FParentForm) and (FParentForm is TfrmLotes) then
    LParent := TfrmLotes(FParentForm)
  else if Assigned(frmLotes) then
    LParent := frmLotes;

  if Assigned(LParent) and Assigned(LParent.QryMain) and not LParent.QryMain.IsEmpty then
  begin
    LParent.QryMain.Last;
    FLoteId := LParent.QryMain.FieldByName('id').AsInteger;
    LoadData;
    ResetChangeTracking;
    ResetModifiedState;
  end;
end;

procedure TfrmLotesCreator.btnEliminarClick(Sender: TObject);
begin
  if FLoteId <= 0 then Exit;

  if MessageDlg('¿Está seguro de que desea eliminar este lote?', mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  try
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_lotes WHERE id = :id';
    dmgMain.qryExec.ParamByName('id').AsInteger := FLoteId;
    dmgMain.qryExec.ExecSQL;

    if Assigned(FParentForm) and (FParentForm is TfrmLotes) then
      TfrmLotes(FParentForm).btnRefreshClick(nil)
    else if Assigned(frmLotes) then
      frmLotes.btnRefreshClick(nil);

    ModalResult := mrOk;
    Close;
  except
    on E: Exception do
      TDbErrorHandler.HandleException(E, 'Error al eliminar el lote');
  end;
end;

procedure TfrmLotesCreator.btnSalirClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmLotesCreator.btnBuscarProdClick(Sender: TObject);
var
  LSelectProduct: TfrmSelectProduct;
begin
  LSelectProduct := TfrmSelectProduct.Create(Self);
  try
    if LSelectProduct.ShowModal = mrOk then
    begin
      FSelectedArticuloId := StrToIntDef(LSelectProduct.SelectedId, 0);
      edtArticulo.Text := LSelectProduct.SelectedDesc;

      // Comprobar si el artículo seleccionado tiene un id_maestro configurado
      if FSelectedArticuloId > 0 then
      begin
        var LQryMaestro := TFDQuery.Create(nil);
        try
          LQryMaestro.Connection := dmgMain.dbConn;
          LQryMaestro.SQL.Text :=
            'SELECT a.id_maestro, am.descripcion AS maestro_desc ' +
            'FROM ge_articulos a ' +
            'INNER JOIN ge_articulos am ON a.id_maestro = am.id ' +
            'WHERE a.id = :id AND a.id_maestro > 0 LIMIT 1';
          LQryMaestro.ParamByName('id').AsInteger := FSelectedArticuloId;
          LQryMaestro.Open;
          if not LQryMaestro.IsEmpty then
          begin
            var LMaestroId := LQryMaestro.FieldByName('id_maestro').AsInteger;
            var LMaestroDesc := LQryMaestro.FieldByName('maestro_desc').AsString;
            ShowMessage(Format('Nota: El artículo seleccionado tiene como Maestro a:' + sLineBreak +
                               'Art. #%d - %s' + sLineBreak + sLineBreak +
                               'El lote de producción se vinculará al Artículo Maestro para centralizar la trazabilidad y el stock.',
                               [LMaestroId, LMaestroDesc]));
            FSelectedArticuloId := LMaestroId;
            edtArticulo.Text := LMaestroDesc;
          end;
        finally
          LQryMaestro.Free;
        end;
      end;

      ControlChanged(edtArticulo);
    end;
  finally
    LSelectProduct.Free;
  end;
end;

procedure TfrmLotesCreator.btnSaveClick(Sender: TObject);
var
  LCodigo: Integer;
  LCodigoAntiguo: string;
  LIdObrador: Integer;
begin
  LCodigo := StrToIntDef(Trim(edtCodigo.Text), 0);
  if LCodigo <= 0 then
  begin
    ShowMessage('Debe introducir un código numérico de lote válido.');
    edtCodigo.SetFocus;
    Exit;
  end;

  LCodigoAntiguo := Trim(edtCodigoAntiguo.Text);
  if LCodigoAntiguo = '' then
    LCodigoAntiguo := IntToStr(LCodigo);

  if FSelectedArticuloId <= 0 then
  begin
    ShowMessage('Debe seleccionar un artículo.');
    btnBuscarProd.SetFocus;
    Exit;
  end;

  if cbObrador.ItemIndex < 0 then
  begin
    ShowMessage('Debe seleccionar un obrador.');
    cbObrador.SetFocus;
    Exit;
  end;

  LIdObrador := FObradorIds[cbObrador.ItemIndex];

  try
    dmgMain.qryExec.Close;
    if FLoteId > 0 then
    begin
      dmgMain.qryExec.SQL.Text := 
        'UPDATE ge_lotes SET ' +
        '  codigo = :codigo, codigo_antiguo = :codigo_antiguo, id_articulo = :id_articulo, id_obrador = :id_obrador, ' +
        '  fecha = :fecha, observaciones = :observaciones, id_user_update = :user, ' +
        '  updated_at = CURRENT_TIMESTAMP ' +
        'WHERE id = :id';
      dmgMain.qryExec.ParamByName('id').DataType := ftInteger;
      dmgMain.qryExec.ParamByName('id').AsInteger := FLoteId;
    end
    else
    begin
      dmgMain.qryExec.SQL.Text := 
        'INSERT INTO ge_lotes (codigo, codigo_antiguo, id_articulo, id_obrador, fecha, observaciones, activo, id_user_creator, id_user_update) ' +
        'VALUES (:codigo, :codigo_antiguo, :id_articulo, :id_obrador, :fecha, :observaciones, 1, :user, :user)';
    end;

    dmgMain.qryExec.ParamByName('codigo').DataType := ftInteger;
    dmgMain.qryExec.ParamByName('codigo').AsInteger := LCodigo;

    dmgMain.qryExec.ParamByName('codigo_antiguo').DataType := ftWideString;
    dmgMain.qryExec.ParamByName('codigo_antiguo').AsWideString := LCodigoAntiguo;
    
    dmgMain.qryExec.ParamByName('id_articulo').DataType := ftInteger;
    dmgMain.qryExec.ParamByName('id_articulo').AsInteger := FSelectedArticuloId;

    dmgMain.qryExec.ParamByName('id_obrador').DataType := ftInteger;
    if LIdObrador > 0 then
      dmgMain.qryExec.ParamByName('id_obrador').AsInteger := LIdObrador
    else
      dmgMain.qryExec.ParamByName('id_obrador').Clear;

    dmgMain.qryExec.ParamByName('fecha').DataType := ftDate;
    dmgMain.qryExec.ParamByName('fecha').AsDate := dtpFecha.Date;

    dmgMain.qryExec.ParamByName('observaciones').DataType := ftWideString;
    if Trim(memObservaciones.Text) <> '' then
      dmgMain.qryExec.ParamByName('observaciones').AsWideString := Trim(memObservaciones.Text)
    else
      dmgMain.qryExec.ParamByName('observaciones').Clear;

    dmgMain.qryExec.ParamByName('user').DataType := ftInteger;
    if (dmgMain.CurrentUserId <> '') and (StrToIntDef(dmgMain.CurrentUserId, 0) > 0) then
      dmgMain.qryExec.ParamByName('user').AsInteger := StrToIntDef(dmgMain.CurrentUserId, 1)
    else
      dmgMain.qryExec.ParamByName('user').Clear;

    dmgMain.qryExec.ExecSQL;

    if FLoteId <= 0 then
    begin
      dmgMain.qryExec.Close;
      dmgMain.qryExec.SQL.Text := 'SELECT LAST_INSERT_ID() AS last_id';
      dmgMain.qryExec.Open;
      FLoteId := dmgMain.qryExec.FieldByName('last_id').AsInteger;
      dmgMain.qryExec.Close;
      Caption := 'Editar Lote de Producción';
    end;

    ResetChangeTracking;
    ResetModifiedState;
    if Assigned(FParentForm) and (FParentForm is TfrmLotes) then
      TfrmLotes(FParentForm).btnRefreshClick(nil)
    else if Assigned(frmLotes) then
      frmLotes.btnRefreshClick(nil);

    ShowMessage('Lote guardado correctamente.');
    ModalResult := mrOk;
  except
    on E: Exception do
      TDbErrorHandler.HandleException(E, 'Error al guardar el lote');
  end;
end;

procedure TfrmLotesCreator.pgcDetailsChange(Sender: TObject);
begin
  if (pgcDetails.ActivePage = tsTrazabilidadVentas) and not FTrazabilidadLoaded then
  begin
    CargarTrazabilidadVentas;
  end;
end;

procedure TfrmLotesCreator.btnActualizarTrazClick(Sender: TObject);
begin
  CargarTrazabilidadVentas;
end;

procedure TfrmLotesCreator.btnVerDocumentoClick(Sender: TObject);
var
  LTipoDoc: string;
  LDocId: Integer;
  LAlbaranEditor: TfrmAlbaranesEditor;
  LFacturaEditor: TfrmFacturasEditor;
begin
  if not qTrazabilidadVentas.Active or qTrazabilidadVentas.IsEmpty then Exit;

  LTipoDoc := UpperCase(Trim(qTrazabilidadVentas.FieldByName('tipo_doc').AsString));
  LDocId := qTrazabilidadVentas.FieldByName('doc_id').AsInteger;
  if LDocId <= 0 then Exit;

  if LTipoDoc = 'ALBARÁN' then
  begin
    LAlbaranEditor := TfrmAlbaranesEditor.Create(Self);
    try
      LAlbaranEditor.AlbaranId := IntToStr(LDocId);
      LAlbaranEditor.ShowModal;
    finally
      LAlbaranEditor.Free;
    end;
  end
  else if LTipoDoc = 'FACTURA' then
  begin
    LFacturaEditor := TfrmFacturasEditor.Create(Self);
    try
      LFacturaEditor.FacturaId := IntToStr(LDocId);
      LFacturaEditor.ShowModal;
    finally
      LFacturaEditor.Free;
    end;
  end;
end;

procedure TfrmLotesCreator.dbgTrazabilidadVentasDblClick(Sender: TObject);
begin
  btnVerDocumentoClick(Sender);
end;

procedure TfrmLotesCreator.dbgTrazabilidadVentasTitleClick(Column: TColumn);
begin
  if not Assigned(qTrazabilidadVentas) or not qTrazabilidadVentas.Active then Exit;
  if Column.Field = nil then Exit;

  if qTrazabilidadVentas.IndexFieldNames = Column.FieldName + ':A' then
    qTrazabilidadVentas.IndexFieldNames := Column.FieldName + ':D'
  else
    qTrazabilidadVentas.IndexFieldNames := Column.FieldName + ':A';
end;

procedure TfrmLotesCreator.SetupTrazabilidadGrid;
begin
  dbgTrazabilidadVentas.Columns.Clear;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'tipo_doc';
    Title.Caption := 'Tipo Doc.';
    Width := 90;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'doc_numero_completo';
    Title.Caption := 'Nº Documento';
    Width := 130;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'doc_fecha';
    Title.Caption := 'Fecha';
    Width := 85;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'cliente_nombre';
    Title.Caption := 'Cliente Fiscal';
    Width := 210;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'destino_nombre';
    Title.Caption := 'Destino / Sucursal';
    Width := 160;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'cantidad_vendida';
    Title.Caption := 'Uds. Vendidas';
    Width := 95;
    Alignment := taRightJustify;
    Title.Alignment := taRightJustify;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'bultos';
    Title.Caption := 'Bultos';
    Width := 65;
    Alignment := taRightJustify;
    Title.Alignment := taRightJustify;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'precio';
    Title.Caption := 'Precio Ud.';
    Width := 85;
    Alignment := taRightJustify;
    Title.Alignment := taRightJustify;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'total';
    Title.Caption := 'Total Base';
    Width := 95;
    Alignment := taRightJustify;
    Title.Alignment := taRightJustify;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'lote_codigo';
    Title.Caption := 'Lote Registrado';
    Width := 140;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'fecha_caducidad';
    Title.Caption := 'Caducidad';
    Width := 85;
  end;

  with dbgTrazabilidadVentas.Columns.Add do
  begin
    FieldName := 'sscc_bulto';
    Title.Caption := 'SSCC / Palet';
    Width := 170;
  end;
end;

procedure TfrmLotesCreator.CargarTrazabilidadVentas;
var
  LLoteCode: string;
  LArtId: Integer;
  LTotalUds: Double;
  LTotalDocs: Integer;
  LDocSet: TDictionary<string, Boolean>;
  LDocKey: string;
begin
  if FLoteId <= 0 then
  begin
    lblResumenTrazabilidad.Caption := 'Guarde el lote para consultar sus documentos de trazabilidad.';
    Exit;
  end;

  LLoteCode := Trim(edtCodigoAntiguo.Text);
  if LLoteCode = '' then
    LLoteCode := Trim(edtCodigo.Text);

  LArtId := FSelectedArticuloId;

  qTrazabilidadVentas.DisableControls;
  try
    qTrazabilidadVentas.Close;
    qTrazabilidadVentas.SQL.Text := 
      'SELECT ' +
      '  CONVERT(''ALBARÁN'' USING utf8mb4) COLLATE utf8mb4_general_ci AS tipo_doc, ' +
      '  a.id AS doc_id, ' +
      '  CONVERT(CONCAT(COALESCE(a.serie, ''''), CASE WHEN a.serie IS NOT NULL AND a.serie <> '''' THEN '' / '' ELSE '''' END, a.numero) USING utf8mb4) COLLATE utf8mb4_general_ci AS doc_numero_completo, ' +
      '  CAST(a.fecha AS DATE) AS doc_fecha, ' +
      '  CONVERT(COALESCE(c.nombre_fiscal, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS cliente_nombre, ' +
      '  CONVERT(COALESCE(cd.descripcion, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS destino_nombre, ' +
      '  al.cantidad AS cantidad_vendida, ' +
      '  COALESCE(al.bultos, 0) AS bultos, ' +
      '  al.precio AS precio, ' +
      '  al.total AS total, ' +
      '  CONVERT(COALESCE(NULLIF(al.lotes, ''''), (SELECT GROUP_CONCAT(DISTINCT alb_b.lote SEPARATOR '', '') FROM ge_albaranes_lineas_bultos alb_b WHERE alb_b.linea_albaran_id = al.id)) USING utf8mb4) COLLATE utf8mb4_general_ci AS lote_codigo, ' +
      '  al.fecha_caducidad AS fecha_caducidad, ' +
      '  CONVERT((SELECT GROUP_CONCAT(DISTINCT COALESCE(NULLIF(bp.sscc, ''''), NULLIF(b.sscc, '''')) SEPARATOR '', '') ' +
      '   FROM ge_albaranes_lineas_bultos alb_b ' +
      '   INNER JOIN ge_albaranes_bultos b ON alb_b.bulto_id = b.id ' +
      '   LEFT JOIN ge_albaranes_bultos bp ON b.bulto_padre_id = bp.id ' +
      '   WHERE alb_b.linea_albaran_id = al.id AND (b.sscc IS NOT NULL OR bp.sscc IS NOT NULL)) USING utf8mb4) COLLATE utf8mb4_general_ci AS sscc_bulto ' +
      'FROM ge_albaranes_lineas al ' +
      'INNER JOIN ge_albaranes a ON al.albaran_id = a.id ' +
      'LEFT JOIN ge_clientes c ON a.cliente_id = c.id ' +
      'LEFT JOIN ge_clientes_destinos cd ON a.clientedestino_id = cd.id ' +
      'WHERE ( ' +
      '  (al.articulo_id = :art_id AND (al.lotes = :lote_cod OR al.lotes LIKE CONCAT(''%'', :lote_cod, ''%'') OR EXISTS (SELECT 1 FROM ge_albaranes_lineas_bultos alb_b WHERE alb_b.linea_albaran_id = al.id AND alb_b.lote = :lote_cod))) ' +
      '  OR ' +
      '  (:art_id = 0 AND (al.lotes = :lote_cod OR al.lotes LIKE CONCAT(''%'', :lote_cod, ''%'') OR EXISTS (SELECT 1 FROM ge_albaranes_lineas_bultos alb_b WHERE alb_b.linea_albaran_id = al.id AND alb_b.lote = :lote_cod))) ' +
      ') ' +
      'UNION ALL ' +
      'SELECT ' +
      '  CONVERT(''FACTURA'' USING utf8mb4) COLLATE utf8mb4_general_ci AS tipo_doc, ' +
      '  f.id AS doc_id, ' +
      '  CONVERT(CONCAT(COALESCE(f.serie, ''''), CASE WHEN f.serie IS NOT NULL AND f.serie <> '''' THEN '' / '' ELSE '''' END, f.numero) USING utf8mb4) COLLATE utf8mb4_general_ci AS doc_numero_completo, ' +
      '  CAST(f.fecha AS DATE) AS doc_fecha, ' +
      '  CONVERT(COALESCE(c.nombre_fiscal, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS cliente_nombre, ' +
      '  CONVERT('''' USING utf8mb4) COLLATE utf8mb4_general_ci AS destino_nombre, ' +
      '  fl.cantidad AS cantidad_vendida, ' +
      '  COALESCE(fl.bultos, 0) AS bultos, ' +
      '  fl.precio AS precio, ' +
      '  fl.total AS total, ' +
      '  CONVERT(COALESCE(fl.lotes, '''') USING utf8mb4) COLLATE utf8mb4_general_ci AS lote_codigo, ' +
      '  fl.fecha_caducidad AS fecha_caducidad, ' +
      '  CONVERT('''' USING utf8mb4) COLLATE utf8mb4_general_ci AS sscc_bulto ' +
      'FROM ge_facturas_lineas fl ' +
      'INNER JOIN ge_facturas f ON fl.factura_id = f.id ' +
      'LEFT JOIN ge_clientes c ON f.cliente_id = c.id ' +
      'WHERE f.id NOT IN (SELECT COALESCE(id_factura, 0) FROM ge_albaranes WHERE id_factura IS NOT NULL) ' +
      '  AND ( ' +
      '    (fl.articulo_id = :art_id AND (fl.lotes = :lote_cod OR fl.lotes LIKE CONCAT(''%'', :lote_cod, ''%''))) ' +
      '    OR ' +
      '    (:art_id = 0 AND (fl.lotes = :lote_cod OR fl.lotes LIKE CONCAT(''%'', :lote_cod, ''%''))) ' +
      '  ) ' +
      'ORDER BY doc_fecha DESC, doc_id DESC';

    qTrazabilidadVentas.ParamByName('art_id').AsInteger := LArtId;
    qTrazabilidadVentas.ParamByName('lote_cod').AsString := LLoteCode;
    qTrazabilidadVentas.Open;

    if qTrazabilidadVentas.FindField('cantidad_vendida') is TNumericField then
      TNumericField(qTrazabilidadVentas.FieldByName('cantidad_vendida')).DisplayFormat := '#,##0.00';
    if qTrazabilidadVentas.FindField('bultos') is TNumericField then
      TNumericField(qTrazabilidadVentas.FieldByName('bultos')).DisplayFormat := '#,##0';
    if qTrazabilidadVentas.FindField('precio') is TNumericField then
      TNumericField(qTrazabilidadVentas.FieldByName('precio')).DisplayFormat := '#,##0.00 €';
    if qTrazabilidadVentas.FindField('total') is TNumericField then
      TNumericField(qTrazabilidadVentas.FieldByName('total')).DisplayFormat := '#,##0.00 €';

    SetupTrazabilidadGrid;
  finally
    qTrazabilidadVentas.EnableControls;
  end;

  FTrazabilidadLoaded := True;

  LTotalUds := 0.0;
  LDocSet := TDictionary<string, Boolean>.Create;
  try
    qTrazabilidadVentas.DisableControls;
    try
      qTrazabilidadVentas.First;
      while not qTrazabilidadVentas.Eof do
      begin
        LTotalUds := LTotalUds + qTrazabilidadVentas.FieldByName('cantidad_vendida').AsFloat;
        LDocKey := qTrazabilidadVentas.FieldByName('tipo_doc').AsString + '_' + qTrazabilidadVentas.FieldByName('doc_id').AsString;
        if not LDocSet.ContainsKey(LDocKey) then
          LDocSet.Add(LDocKey, True);
        qTrazabilidadVentas.Next;
      end;
      qTrazabilidadVentas.First;
    finally
      qTrazabilidadVentas.EnableControls;
    end;

    LTotalDocs := LDocSet.Count;
    if qTrazabilidadVentas.IsEmpty then
      lblResumenTrazabilidad.Caption := Format('Lote %s: No se registran ventas para este lote.', [LLoteCode])
    else
      lblResumenTrazabilidad.Caption := Format('Lote %s: %.2f uds. vendidas en %d documento(s) comercial(es).', [LLoteCode, LTotalUds, LTotalDocs]);
  finally
    LDocSet.Free;
  end;
end;

end.