unit frm_CrearAlbaranStock;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, Vcl.StdCtrls, Vcl.Buttons, Vcl.ComCtrls,
  Vcl.Grids, Vcl.DBGrids, Data.DB, FireDAC.Comp.Client, FireDAC.Comp.DataSet,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, System.UITypes;

type
  TfrmCrearAlbaranStock = class(TForm)
    pnlHeader: TPanel;
    lblCliente: TLabel;
    lblDestino: TLabel;
    lblSerie: TLabel;
    lblFecha: TLabel;
    lblAlmacenInfo: TLabel;
    btnBuscarCliente: TSpeedButton;
    cbCliente: TComboBox;
    cbDestino: TComboBox;
    cbSerie: TComboBox;
    dtpFecha: TDateTimePicker;
    pnlGrid: TPanel;
    dbgLineas: TDBGrid;
    pnlBultosEDI: TPanel;
    grpBultosEDI: TGroupBox;
    chkGenerarBultosEDI: TCheckBox;
    rbBultoPorLote: TRadioButton;
    rbPaletConCajas: TRadioButton;
    pnlFooter: TPanel;
    lblTotalesResumen: TLabel;
    lblAvisoStock: TLabel;
    btnCrearAlbaran: TBitBtn;
    btnCancelar: TBitBtn;
    mtLineas: TFDMemTable;
    dsLineas: TDataSource;

    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btnBuscarClienteClick(Sender: TObject);
    procedure cbClienteChange(Sender: TObject);
    procedure dbgLineasColExit(Sender: TObject);
    procedure dbgLineasDrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure chkGenerarBultosEDIClick(Sender: TObject);
    procedure btnCrearAlbaranClick(Sender: TObject);
  private
    FClientIds: TStringList;
    FDestinationIds: TStringList;
    FAlbaranCreadoId: Integer;
    FAlmacenIdComun: Integer;
    FAlmacenNombreComun: string;

    procedure ConfigurarMemTable;
    procedure ConfigurarColumnasGrid;
    procedure LoadClientes;
    procedure LoadDestinos(const AClienteId: string);
    procedure LoadSeries;
    procedure RecalcularPreciosCliente;
    procedure RecalcularTotales;
    procedure mtLineasAfterPost(DataSet: TDataSet);
    procedure mtLineasBeforePost(DataSet: TDataSet);
    function GenerarCodigoSSCC18(const AAlbaranId: Integer): string;
  public
    procedure CargarLineasStock(const AStockIds: TArray<Integer>);
    property AlbaranCreadoId: Integer read FAlbaranCreadoId;
  end;

var
  frmCrearAlbaranStock: TfrmCrearAlbaranStock;

implementation

uses
  dmg_Main, frm_SelectCliente, frm_AlbaranesEditor, uValidacionFiscal, System.Math;

{$R *.dfm}

procedure TfrmCrearAlbaranStock.FormCreate(Sender: TObject);
begin
  FClientIds := TStringList.Create;
  FDestinationIds := TStringList.Create;
  FAlbaranCreadoId := 0;
  FAlmacenIdComun := 0;
  FAlmacenNombreComun := '';

  dtpFecha.Date := Date;
  ConfigurarMemTable;
  ConfigurarColumnasGrid;
  LoadClientes;
  LoadSeries;
end;

procedure TfrmCrearAlbaranStock.FormDestroy(Sender: TObject);
begin
  FreeAndNil(FClientIds);
  FreeAndNil(FDestinationIds);
end;

procedure TfrmCrearAlbaranStock.FormShow(Sender: TObject);
begin
  RecalcularTotales;
end;

procedure TfrmCrearAlbaranStock.ConfigurarMemTable;
begin
  mtLineas.Close;
  mtLineas.FieldDefs.Clear;

  mtLineas.FieldDefs.Add('stock_id', ftInteger, 0, False);
  mtLineas.FieldDefs.Add('almacen_id', ftInteger, 0, False);
  mtLineas.FieldDefs.Add('almacen_nombre', ftString, 100, False);
  mtLineas.FieldDefs.Add('articulo_id', ftInteger, 0, False);
  mtLineas.FieldDefs.Add('articulo_codigo', ftString, 50, False);
  mtLineas.FieldDefs.Add('descripcion', ftString, 200, False);
  mtLineas.FieldDefs.Add('id_lote', ftInteger, 0, False);
  mtLineas.FieldDefs.Add('lote', ftString, 50, False);
  mtLineas.FieldDefs.Add('fecha_caducidad', ftDate, 0, False);
  mtLineas.FieldDefs.Add('stock_disponible', ftFloat, 0, False);
  mtLineas.FieldDefs.Add('cantidad', ftFloat, 0, False);
  mtLineas.FieldDefs.Add('precio', ftFloat, 0, False);
  mtLineas.FieldDefs.Add('tipo_iva', ftFloat, 0, False);
  mtLineas.FieldDefs.Add('importe', ftFloat, 0, False);

  mtLineas.CreateDataSet;
  mtLineas.BeforePost := mtLineasBeforePost;
  mtLineas.AfterPost := mtLineasAfterPost;

  // Marcar de sólo lectura los campos descriptivos
  mtLineas.FieldByName('stock_id').ReadOnly := True;
  mtLineas.FieldByName('almacen_id').ReadOnly := True;
  mtLineas.FieldByName('almacen_nombre').ReadOnly := True;
  mtLineas.FieldByName('articulo_id').ReadOnly := True;
  mtLineas.FieldByName('articulo_codigo').ReadOnly := True;
  mtLineas.FieldByName('descripcion').ReadOnly := True;
  mtLineas.FieldByName('id_lote').ReadOnly := True;
  mtLineas.FieldByName('lote').ReadOnly := True;
  mtLineas.FieldByName('fecha_caducidad').ReadOnly := True;
  mtLineas.FieldByName('stock_disponible').ReadOnly := True;
  mtLineas.FieldByName('tipo_iva').ReadOnly := True;
  mtLineas.FieldByName('importe').ReadOnly := True;

  TNumericField(mtLineas.FieldByName('stock_disponible')).DisplayFormat := '#,##0.##';
  TNumericField(mtLineas.FieldByName('cantidad')).DisplayFormat := '#,##0.##';
  TNumericField(mtLineas.FieldByName('precio')).DisplayFormat := '#,##0.00';
  TNumericField(mtLineas.FieldByName('tipo_iva')).DisplayFormat := '#,##0.#';
  TNumericField(mtLineas.FieldByName('importe')).DisplayFormat := '#,##0.00';
end;

procedure TfrmCrearAlbaranStock.ConfigurarColumnasGrid;
begin
  dbgLineas.Columns.Clear;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'articulo_codigo';
    Title.Caption := 'Cód. Art.';
    Width := 80;
    ReadOnly := True;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'descripcion';
    Title.Caption := 'Descripción del Artículo';
    Width := 250;
    ReadOnly := True;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'lote';
    Title.Caption := 'Lote Envasado';
    Width := 110;
    ReadOnly := True;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'fecha_caducidad';
    Title.Caption := 'Caducidad';
    Width := 90;
    ReadOnly := True;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'stock_disponible';
    Title.Caption := 'Stock Disp.';
    Width := 95;
    Title.Alignment := taRightJustify;
    Alignment := taRightJustify;
    ReadOnly := True;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'cantidad';
    Title.Caption := 'Cant. Albarán';
    Width := 100;
    Title.Alignment := taRightJustify;
    Alignment := taRightJustify;
    ReadOnly := False;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'precio';
    Title.Caption := 'Precio Unit. €';
    Width := 90;
    Title.Alignment := taRightJustify;
    Alignment := taRightJustify;
    ReadOnly := False;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'tipo_iva';
    Title.Caption := '% IVA';
    Width := 55;
    Title.Alignment := taRightJustify;
    Alignment := taRightJustify;
    ReadOnly := True;
  end;

  with dbgLineas.Columns.Add do
  begin
    FieldName := 'importe';
    Title.Caption := 'Importe €';
    Width := 95;
    Title.Alignment := taRightJustify;
    Alignment := taRightJustify;
    ReadOnly := True;
  end;
end;

procedure TfrmCrearAlbaranStock.LoadClientes;
var
  Qry: TFDQuery;
begin
  cbCliente.Items.BeginUpdate;
  try
    cbCliente.Items.Clear;
    FClientIds.Clear;

    Qry := TFDQuery.Create(nil);
    try
      Qry.Connection := dmgMain.dbConn;
      Qry.SQL.Text := 'SELECT id, nombre_fiscal FROM ge_clientes WHERE activo = 1 ORDER BY nombre_fiscal ASC';
      Qry.Open;
      while not Qry.Eof do
      begin
        cbCliente.Items.Add(Qry.FieldByName('nombre_fiscal').AsString);
        FClientIds.Add(TdmgMain.CleanUUID(Qry.FieldByName('id').AsString));
        Qry.Next;
      end;
    finally
      Qry.Free;
    end;
  finally
    cbCliente.Items.EndUpdate;
  end;
end;

procedure TfrmCrearAlbaranStock.LoadDestinos(const AClienteId: string);
var
  Qry: TFDQuery;
begin
  cbDestino.Items.BeginUpdate;
  try
    cbDestino.Items.Clear;
    FDestinationIds.Clear;

    if Trim(AClienteId) = '' then Exit;

    Qry := TFDQuery.Create(nil);
    try
      Qry.Connection := dmgMain.dbConn;
      Qry.SQL.Text := 'SELECT id, descripcion, centro FROM ge_clientes_destinos WHERE id_cliente = :cli AND activo = 1 ORDER BY descripcion ASC';
      Qry.ParamByName('cli').AsString := AClienteId;
      Qry.Open;
      while not Qry.Eof do
      begin
        if Trim(Qry.FieldByName('centro').AsString) <> '' then
          cbDestino.Items.Add(Trim(Qry.FieldByName('centro').AsString) + ' - ' + Qry.FieldByName('descripcion').AsString)
        else
          cbDestino.Items.Add(Qry.FieldByName('descripcion').AsString);

        FDestinationIds.Add(TdmgMain.CleanUUID(Qry.FieldByName('id').AsString));
        Qry.Next;
      end;
    finally
      Qry.Free;
    end;

    if cbDestino.Items.Count > 0 then
      cbDestino.ItemIndex := 0;
  finally
    cbDestino.Items.EndUpdate;
  end;
end;

procedure TfrmCrearAlbaranStock.LoadSeries;
var
  Qry: TFDQuery;
  LSerieDef: string;
begin
  cbSerie.Items.BeginUpdate;
  try
    cbSerie.Items.Clear;
    LSerieDef := '';

    Qry := TFDQuery.Create(nil);
    try
      Qry.Connection := dmgMain.dbConn;
      Qry.SQL.Text := 'SELECT DISTINCT serie, por_defecto FROM ge_series WHERE activo = 1 AND empresa_id = :emp ORDER BY por_defecto DESC, serie ASC';
      Qry.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      Qry.Open;
      while not Qry.Eof do
      begin
        cbSerie.Items.Add(Qry.FieldByName('serie').AsString);
        if (LSerieDef = '') and (Qry.FieldByName('por_defecto').AsInteger = 1) then
          LSerieDef := Qry.FieldByName('serie').AsString;
        Qry.Next;
      end;
    finally
      Qry.Free;
    end;

    if (LSerieDef <> '') and (cbSerie.Items.IndexOf(LSerieDef) >= 0) then
      cbSerie.ItemIndex := cbSerie.Items.IndexOf(LSerieDef)
    else if cbSerie.Items.Count > 0 then
      cbSerie.ItemIndex := 0;
  finally
    cbSerie.Items.EndUpdate;
  end;
end;

procedure TfrmCrearAlbaranStock.btnBuscarClienteClick(Sender: TObject);
var
  LFrm: TfrmSelectCliente;
  LIdx: Integer;
begin
  LFrm := TfrmSelectCliente.Create(Self);
  try
    if LFrm.ShowModal = mrOk then
    begin
      if LFrm.SelectedId <> '' then
      begin
        LIdx := FClientIds.IndexOf(LFrm.SelectedId);
        if LIdx = -1 then
        begin
          LoadClientes;
          LIdx := FClientIds.IndexOf(LFrm.SelectedId);
        end;

        if LIdx <> -1 then
        begin
          cbCliente.ItemIndex := LIdx;
          cbClienteChange(cbCliente);
        end;
      end;
    end;
  finally
    LFrm.Free;
  end;
end;

procedure TfrmCrearAlbaranStock.cbClienteChange(Sender: TObject);
begin
  if (cbCliente.ItemIndex >= 0) and (FClientIds.Count > cbCliente.ItemIndex) then
  begin
    LoadDestinos(FClientIds[cbCliente.ItemIndex]);
    RecalcularPreciosCliente;
  end
  else
  begin
    cbDestino.Items.Clear;
    FDestinationIds.Clear;
  end;
end;

procedure TfrmCrearAlbaranStock.RecalcularPreciosCliente;
var
  LClienteId: string;
  LArtId: Integer;
  LPrecioOriginal, LPrecioCliente: Double;
  LCantidad: Double;
begin
  if (cbCliente.ItemIndex < 0) or (FClientIds.Count <= cbCliente.ItemIndex) then Exit;
  LClienteId := FClientIds[cbCliente.ItemIndex];
  if LClienteId = '' then Exit;

  if mtLineas.IsEmpty then Exit;

  mtLineas.DisableControls;
  try
    mtLineas.First;
    while not mtLineas.Eof do
    begin
      LArtId := mtLineas.FieldByName('articulo_id').AsInteger;
      LPrecioOriginal := mtLineas.FieldByName('precio').AsFloat;
      LCantidad := mtLineas.FieldByName('cantidad').AsFloat;
      if LCantidad <= 0 then LCantidad := 1.0;

      LPrecioCliente := dmgMain.GetPrecioArticuloCliente(LClienteId, IntToStr(LArtId), dtpFecha.Date, LPrecioOriginal, LCantidad);
      if Abs(LPrecioCliente - LPrecioOriginal) > 0.001 then
      begin
        mtLineas.Edit;
        mtLineas.FieldByName('precio').AsFloat := LPrecioCliente;
        mtLineas.FieldByName('importe').AsFloat := RoundTo(LCantidad * LPrecioCliente, -2);
        mtLineas.Post;
      end;

      mtLineas.Next;
    end;
  finally
    mtLineas.EnableControls;
  end;

  RecalcularTotales;
end;

procedure TfrmCrearAlbaranStock.mtLineasBeforePost(DataSet: TDataSet);
var
  LCant, LDisp, LPrecio: Double;
begin
  LCant := DataSet.FieldByName('cantidad').AsFloat;
  LDisp := DataSet.FieldByName('stock_disponible').AsFloat;
  LPrecio := DataSet.FieldByName('precio').AsFloat;

  if LCant <= 0 then
    raise Exception.Create('La cantidad a incorporar al albarán debe ser superior a 0.');

  if LCant > LDisp then
  begin
    if MessageDlg(Format('La cantidad introducida (%f) supera el stock disponible (%f). ¿Desea ajustarla automáticamente al máximo disponible (%f)?',
      [LCant, LDisp, LDisp]), mtConfirmation, [mbYes, mbNo], 0) = mrYes then
    begin
      LCant := LDisp;
      DataSet.FieldByName('cantidad').AsFloat := LCant;
    end
    else
      raise Exception.Create(Format('No puede asignar más de %f unidades disponibles de este lote.', [LDisp]));
  end;

  DataSet.FieldByName('importe').AsFloat := RoundTo(LCant * LPrecio, -2);
end;

procedure TfrmCrearAlbaranStock.mtLineasAfterPost(DataSet: TDataSet);
begin
  RecalcularTotales;
end;

procedure TfrmCrearAlbaranStock.dbgLineasColExit(Sender: TObject);
begin
  if mtLineas.State in [dsEdit, dsInsert] then
    mtLineas.Post;
end;

procedure TfrmCrearAlbaranStock.dbgLineasDrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  // Resaltar en fondo azul suave las columnas editables: 'cantidad' y 'precio'
  if (Column.FieldName = 'cantidad') or (Column.FieldName = 'precio') then
  begin
    if not (gdSelected in State) then
    begin
      dbgLineas.Canvas.Brush.Color := $00FDF5E6; // Tinte suave destacable
      dbgLineas.Canvas.FillRect(Rect);
      dbgLineas.Canvas.Font.Style := [fsBold];
      dbgLineas.Canvas.Font.Color := clNavy;
    end;
  end;
  dbgLineas.DefaultDrawColumnCell(Rect, DataCol, Column, State);
end;

procedure TfrmCrearAlbaranStock.chkGenerarBultosEDIClick(Sender: TObject);
begin
  rbBultoPorLote.Enabled := chkGenerarBultosEDI.Checked;
  rbPaletConCajas.Enabled := chkGenerarBultosEDI.Checked;
end;

procedure TfrmCrearAlbaranStock.RecalcularTotales;
var
  LTotalLineas: Integer;
  LTotalUnidades, LTotalImporte: Double;
begin
  LTotalLineas := 0;
  LTotalUnidades := 0;
  LTotalImporte := 0;

  if not mtLineas.IsEmpty then
  begin
    mtLineas.DisableControls;
    try
      mtLineas.First;
      while not mtLineas.Eof do
      begin
        Inc(LTotalLineas);
        LTotalUnidades := LTotalUnidades + mtLineas.FieldByName('cantidad').AsFloat;
        LTotalImporte := LTotalImporte + mtLineas.FieldByName('importe').AsFloat;
        mtLineas.Next;
      end;
    finally
      mtLineas.EnableControls;
    end;
  end;

  lblTotalesResumen.Caption := Format('Líneas: %d  |  Unidades: %.2f  |  Base Imponible: %.2f €',
    [LTotalLineas, LTotalUnidades, LTotalImporte]);
end;

procedure TfrmCrearAlbaranStock.CargarLineasStock(const AStockIds: TArray<Integer>);
var
  Qry: TFDQuery;
  LInList: string;
  i: Integer;
  LDisp: Double;
  LAlmSet: Boolean;
begin
  if Length(AStockIds) = 0 then Exit;

  LInList := '';
  for i := 0 to High(AStockIds) do
  begin
    if LInList <> '' then LInList := LInList + ', ';
    LInList := LInList + IntToStr(AStockIds[i]);
  end;

  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := dmgMain.dbConn;
    Qry.SQL.Text :=
      'SELECT se.id AS stock_id, se.id_almacen, alm.descripcion AS almacen_nombre, ' +
      '       se.id_articulo, art.codigo AS articulo_codigo, art.descripcion AS articulo_desc, ' +
      '       COALESCE(art.pvp1, 0) AS pvp1, COALESCE(t.tipo, 21.0) AS tipo_iva, ' +
      '       se.id_lote, COALESCE(NULLIF(l.codigo_antiguo, ''''), l.codigo) AS lote_codigo, ' +
      '       (CASE WHEN l.fecha IS NOT NULL AND art.dias_vida > 0 THEN DATE_ADD(l.fecha, INTERVAL art.dias_vida DAY) ELSE NULL END) AS fecha_caducidad, ' +
      '       se.cantidad_unidades, COALESCE(se.cantidad_reservada, 0) AS cantidad_reservada, ' +
      '       GREATEST(0, se.cantidad_unidades - COALESCE(se.cantidad_reservada, 0)) AS stock_disponible ' +
      'FROM ge_stock_envasado se ' +
      'JOIN ge_almacenes alm ON se.id_almacen = alm.id ' +
      'JOIN ge_articulos art ON se.id_articulo = art.id ' +
      'JOIN ge_lotes l ON se.id_lote = l.id ' +
      'LEFT JOIN ge_tipos_iva t ON art.id_tipo_iva = t.id ' +
      'WHERE se.id IN (' + LInList + ') ' +
      'ORDER BY art.descripcion ASC, l.codigo ASC';
    Qry.Open;

    mtLineas.DisableControls;
    try
      mtLineas.EmptyDataSet;
      LAlmSet := False;

      while not Qry.Eof do
      begin
        LDisp := Qry.FieldByName('stock_disponible').AsFloat;
        if LDisp <= 0 then
          LDisp := Qry.FieldByName('cantidad_unidades').AsFloat;

        if not LAlmSet then
        begin
          FAlmacenIdComun := Qry.FieldByName('id_almacen').AsInteger;
          FAlmacenNombreComun := Qry.FieldByName('almacen_nombre').AsString;
          lblAlmacenInfo.Caption := 'Almacén Origen: ' + FAlmacenNombreComun;
          LAlmSet := True;
        end;

        mtLineas.Append;
        mtLineas.FieldByName('stock_id').AsInteger := Qry.FieldByName('stock_id').AsInteger;
        mtLineas.FieldByName('almacen_id').AsInteger := Qry.FieldByName('id_almacen').AsInteger;
        mtLineas.FieldByName('almacen_nombre').AsString := Qry.FieldByName('almacen_nombre').AsString;
        mtLineas.FieldByName('articulo_id').AsInteger := Qry.FieldByName('id_articulo').AsInteger;
        mtLineas.FieldByName('articulo_codigo').AsString := Qry.FieldByName('articulo_codigo').AsString;
        mtLineas.FieldByName('descripcion').AsString := Qry.FieldByName('articulo_desc').AsString;
        mtLineas.FieldByName('id_lote').AsInteger := Qry.FieldByName('id_lote').AsInteger;
        mtLineas.FieldByName('lote').AsString := Qry.FieldByName('lote_codigo').AsString;

        if not Qry.FieldByName('fecha_caducidad').IsNull then
          mtLineas.FieldByName('fecha_caducidad').AsDateTime := Qry.FieldByName('fecha_caducidad').AsDateTime
        else
          mtLineas.FieldByName('fecha_caducidad').Clear;

        mtLineas.FieldByName('stock_disponible').AsFloat := LDisp;
        mtLineas.FieldByName('cantidad').AsFloat := LDisp; // Por defecto todo el stock disponible
        mtLineas.FieldByName('precio').AsFloat := Qry.FieldByName('pvp1').AsFloat;
        mtLineas.FieldByName('tipo_iva').AsFloat := Qry.FieldByName('tipo_iva').AsFloat;
        mtLineas.FieldByName('importe').AsFloat := RoundTo(LDisp * Qry.FieldByName('pvp1').AsFloat, -2);
        mtLineas.Post;

        Qry.Next;
      end;
    finally
      mtLineas.EnableControls;
    end;
  finally
    Qry.Free;
  end;

  RecalcularTotales;
end;

function TfrmCrearAlbaranStock.GenerarCodigoSSCC18(const AAlbaranId: Integer): string;
var
  Qry: TFDQuery;
  LGlnEmp, LPrefix9, LSSCC18: string;
  LNextSscc: Int64;
begin
  Result := '';
  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := dmgMain.dbConn;

    // 1. Obtener los 9 primeros dígitos del GLN de la empresa activa
    Qry.SQL.Text := 'SELECT gln FROM ge_empresas WHERE id = :emp';
    Qry.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
    Qry.Open;
    LGlnEmp := Trim(Qry.FieldByName('gln').AsString);
    Qry.Close;

    if Length(LGlnEmp) >= 9 then
      LPrefix9 := Copy(LGlnEmp, 1, 9)
    else
      LPrefix9 := '843700368'; // Prefijo GS1 predeterminado

    // 2. Incrementar contador SSCC en ge_contadores
    Qry.SQL.Text := 'SELECT sscc FROM ge_contadores LIMIT 1';
    Qry.Open;
    if Qry.IsEmpty then
    begin
      LNextSscc := 1;
      Qry.Close;
      Qry.SQL.Text := 'INSERT INTO ge_contadores (id, sscc) VALUES (1, 2)';
      Qry.ExecSQL;
    end
    else
    begin
      LNextSscc := Qry.FieldByName('sscc').AsLargeInt;
      Qry.Close;
      Qry.SQL.Text := 'UPDATE ge_contadores SET sscc = sscc + 1';
      Qry.ExecSQL;
    end;

    // 3. Generar el código SSCC-18 estandarizado
    LSSCC18 := TValidacionFiscal.GenerarSSCC18(LPrefix9, LNextSscc, 3);
    Result := LSSCC18;
  finally
    Qry.Free;
  end;
end;

procedure TfrmCrearAlbaranStock.btnCrearAlbaranClick(Sender: TObject);
var
  Qry, QryIns: TFDQuery;
  LClienteId, LDestinoId, LSerie: string;
  LNextNum: Integer;
  LNewAlbaranId: Integer;
  LPosicion, LNextBultoNum: Integer;
  LPaletBultoId, LBoxBultoId, LLineaAlbaranId: Integer;
  LPaletSSCC, LBoxSSCC, LPrimerSSCC: string;
  LTotalBase, LTotalIva, LTotalDoc: Double;
  LBaseLin, LIvaLin, LTotalLin: Double;
  LCant, LPrecio, LTipoIva: Double;
  LFrmEditor: TfrmAlbaranesEditor;
begin
  if (cbCliente.ItemIndex < 0) or (FClientIds.Count <= cbCliente.ItemIndex) then
  begin
    MessageDlg('Debe seleccionar el Cliente destinatario del albarán.', mtWarning, [mbOK], 0);
    cbCliente.SetFocus;
    Exit;
  end;

  if cbSerie.ItemIndex < 0 then
  begin
    MessageDlg('Debe seleccionar la Serie del albarán.', mtWarning, [mbOK], 0);
    cbSerie.SetFocus;
    Exit;
  end;

  if mtLineas.IsEmpty then
  begin
    MessageDlg('No hay líneas de stock para generar el albarán.', mtWarning, [mbOK], 0);
    Exit;
  end;

  // Asegurar que se guarden cambios si está editando una celda en el grid
  if mtLineas.State in [dsEdit, dsInsert] then
    mtLineas.Post;

  LClienteId := FClientIds[cbCliente.ItemIndex];
  LDestinoId := '';
  if (cbDestino.ItemIndex >= 0) and (FDestinationIds.Count > cbDestino.ItemIndex) then
    LDestinoId := FDestinationIds[cbDestino.ItemIndex];
  LSerie := cbSerie.Text;

  // Validar cantidades positivas
  mtLineas.First;
  while not mtLineas.Eof do
  begin
    if mtLineas.FieldByName('cantidad').AsFloat <= 0 then
    begin
      MessageDlg(Format('El artículo "%s" (Lote %s) tiene una cantidad de 0. Ajuste la cantidad o elimínela.',
        [mtLineas.FieldByName('descripcion').AsString, mtLineas.FieldByName('lote').AsString]), mtWarning, [mbOK], 0);
      Exit;
    end;
    mtLineas.Next;
  end;

  // Confirmación
  if MessageDlg(Format('¿Está seguro de crear el Albarán para %s con %d línea(s) de stock envasado?',
    [cbCliente.Text, mtLineas.RecordCount]), mtConfirmation, [mbYes, mbNo], 0) <> mrYes then
    Exit;

  Qry := TFDQuery.Create(nil);
  QryIns := TFDQuery.Create(nil);
  try
    Qry.Connection := dmgMain.dbConn;
    QryIns.Connection := dmgMain.dbConn;

    dmgMain.dbConn.StartTransaction;
    try
      // 1. Obtener siguiente número correlativo para la serie
      LNextNum := 1;
      Qry.SQL.Text := 'SELECT sig_numero_albaran FROM ge_series WHERE activo = 1 AND empresa_id = :emp AND serie = :ser FOR UPDATE';
      Qry.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      Qry.ParamByName('ser').AsString := LSerie;
      Qry.Open;
      if not Qry.IsEmpty then
        LNextNum := Qry.FieldByName('sig_numero_albaran').AsInteger;
      Qry.Close;

      // 2. Calcular totales globales
      LTotalBase := 0;
      LTotalIva := 0;
      LTotalDoc := 0;

      mtLineas.First;
      while not mtLineas.Eof do
      begin
        LCant := mtLineas.FieldByName('cantidad').AsFloat;
        LPrecio := mtLineas.FieldByName('precio').AsFloat;
        LTipoIva := mtLineas.FieldByName('tipo_iva').AsFloat;

        LBaseLin := RoundTo(LCant * LPrecio, -2);
        LIvaLin := RoundTo(LBaseLin * (LTipoIva / 100.0), -2);
        LTotalLin := LBaseLin + LIvaLin;

        LTotalBase := LTotalBase + LBaseLin;
        LTotalIva := LTotalIva + LIvaLin;
        LTotalDoc := LTotalDoc + LTotalLin;

        mtLineas.Next;
      end;

      // 3. Insertar cabecera en ge_albaranes
      QryIns.SQL.Text :=
        'INSERT INTO ge_albaranes (empresa_id, cliente_id, clientedestino_id, serie, numero, fecha, ' +
        'base_imponible, iva, total, facturado, id_user_creator, id_user_update, edi_estado_envio) ' +
        'VALUES (:emp, :cli, :dest, :ser, :num, :fec, :base, :iva, :tot, 0, :user, :user, ''NO_ENVIADO'')';
      QryIns.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      QryIns.ParamByName('cli').AsString := LClienteId;
      if LDestinoId <> '' then
        QryIns.ParamByName('dest').AsString := LDestinoId
      else
        QryIns.ParamByName('dest').Clear;
      QryIns.ParamByName('ser').AsString := LSerie;
      QryIns.ParamByName('num').AsInteger := LNextNum;
      QryIns.ParamByName('fec').AsDateTime := dtpFecha.Date;
      QryIns.ParamByName('base').AsFloat := LTotalBase;
      QryIns.ParamByName('iva').AsFloat := LTotalIva;
      QryIns.ParamByName('tot').AsFloat := LTotalDoc;
      QryIns.ParamByName('user').AsString := dmgMain.CurrentUserId;
      QryIns.ExecSQL;

      // Obtener el ID del albarán generado
      Qry.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
      Qry.Open;
      LNewAlbaranId := Qry.FieldByName('new_id').AsInteger;
      Qry.Close;

      // 4. Si se seleccionó agrupar en 1 Palet Maestro (Nivel 2)
      LPaletBultoId := 0;
      LPrimerSSCC := '';
      LNextBultoNum := 1;

      if chkGenerarBultosEDI.Checked and rbPaletConCajas.Checked then
      begin
        LPaletSSCC := GenerarCodigoSSCC18(LNewAlbaranId);
        LPrimerSSCC := LPaletSSCC;

        QryIns.SQL.Text :=
          'INSERT INTO ge_albaranes_bultos (albaran_id, numero_bulto, bulto_padre_id, nivel, tipo_embalaje, sscc, id_user_creator) ' +
          'VALUES (:alb, :num, NULL, 2, ''PE'', :sscc, :user)';
        QryIns.ParamByName('alb').AsInteger := LNewAlbaranId;
        QryIns.ParamByName('num').AsInteger := LNextBultoNum;
        QryIns.ParamByName('sscc').AsString := LPaletSSCC;
        QryIns.ParamByName('user').AsString := dmgMain.CurrentUserId;
        QryIns.ExecSQL;

        Qry.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
        Qry.Open;
        LPaletBultoId := Qry.FieldByName('new_id').AsInteger;
        Qry.Close;

        Inc(LNextBultoNum);
      end;

      // 5. Insertar líneas del albarán, bultos y descontar stock físico
      LPosicion := 0;
      mtLineas.First;
      while not mtLineas.Eof do
      begin
        Inc(LPosicion);
        LCant := mtLineas.FieldByName('cantidad').AsFloat;
        LPrecio := mtLineas.FieldByName('precio').AsFloat;
        LTipoIva := mtLineas.FieldByName('tipo_iva').AsFloat;
        LBaseLin := RoundTo(LCant * LPrecio, -2);
        LIvaLin := RoundTo(LBaseLin * (LTipoIva / 100.0), -2);
        LTotalLin := LBaseLin + LIvaLin;

        QryIns.SQL.Text :=
          'INSERT INTO ge_albaranes_lineas (albaran_id, articulo_codigo, articulo_id, posicion, descripcion, ' +
          'lotes, fecha_caducidad, bultos, cantidad_bulto, cantidad, precio, tipo_iva, base, total, iva, ' +
          'descuento_porcentaje, descuento_precio, id_user_creator, id_user_update) ' +
          'VALUES (:alb, :cod, :art, :pos, :desc, :lote, :fcad, 1, :cant, :cant, :precio, :tiva, :base, :tot, :iva, ' +
          '0, 0, :user, :user)';
        QryIns.ParamByName('alb').AsInteger := LNewAlbaranId;
        QryIns.ParamByName('cod').AsString := mtLineas.FieldByName('articulo_codigo').AsString;
        QryIns.ParamByName('art').AsInteger := mtLineas.FieldByName('articulo_id').AsInteger;
        QryIns.ParamByName('pos').AsInteger := LPosicion;
        QryIns.ParamByName('desc').AsString := mtLineas.FieldByName('descripcion').AsString;
        QryIns.ParamByName('lote').AsString := mtLineas.FieldByName('lote').AsString;

        if not mtLineas.FieldByName('fecha_caducidad').IsNull then
          QryIns.ParamByName('fcad').AsDate := mtLineas.FieldByName('fecha_caducidad').AsDateTime
        else
          QryIns.ParamByName('fcad').Clear;

        QryIns.ParamByName('cant').AsFloat := LCant;
        QryIns.ParamByName('precio').AsFloat := LPrecio;
        QryIns.ParamByName('tiva').AsFloat := LTipoIva;
        QryIns.ParamByName('base').AsFloat := LBaseLin;
        QryIns.ParamByName('tot').AsFloat := LTotalLin;
        QryIns.ParamByName('iva').AsFloat := LIvaLin;
        QryIns.ParamByName('user').AsString := dmgMain.CurrentUserId;
        QryIns.ExecSQL;

        Qry.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
        Qry.Open;
        LLineaAlbaranId := Qry.FieldByName('new_id').AsInteger;
        Qry.Close;

        // 5.1 Generación de Bultos EDI
        if chkGenerarBultosEDI.Checked then
        begin
          LBoxSSCC := GenerarCodigoSSCC18(LNewAlbaranId);
          if LPrimerSSCC = '' then LPrimerSSCC := LBoxSSCC;

          if rbPaletConCajas.Checked and (LPaletBultoId > 0) then
          begin
            // Caja nivel 3 asociada al palet padre
            QryIns.SQL.Text :=
              'INSERT INTO ge_albaranes_bultos (albaran_id, numero_bulto, bulto_padre_id, nivel, tipo_embalaje, sscc, id_user_creator) ' +
              'VALUES (:alb, :num, :padre, 3, ''CT'', :sscc, :user)';
            QryIns.ParamByName('alb').AsInteger := LNewAlbaranId;
            QryIns.ParamByName('num').AsInteger := LNextBultoNum;
            QryIns.ParamByName('padre').AsInteger := LPaletBultoId;
            QryIns.ParamByName('sscc').AsString := LBoxSSCC;
            QryIns.ParamByName('user').AsString := dmgMain.CurrentUserId;
            QryIns.ExecSQL;
          end
          else
          begin
            // Bulto independiente por lote (Nivel 2 o 3 directo)
            QryIns.SQL.Text :=
              'INSERT INTO ge_albaranes_bultos (albaran_id, numero_bulto, bulto_padre_id, nivel, tipo_embalaje, sscc, id_user_creator) ' +
              'VALUES (:alb, :num, NULL, 2, ''CT'', :sscc, :user)';
            QryIns.ParamByName('alb').AsInteger := LNewAlbaranId;
            QryIns.ParamByName('num').AsInteger := LNextBultoNum;
            QryIns.ParamByName('sscc').AsString := LBoxSSCC;
            QryIns.ParamByName('user').AsString := dmgMain.CurrentUserId;
            QryIns.ExecSQL;
          end;

          Inc(LNextBultoNum);

          Qry.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
          Qry.Open;
          LBoxBultoId := Qry.FieldByName('new_id').AsInteger;
          Qry.Close;

          // Asignar el contenido a la tabla de trazabilidad de bultos
          QryIns.SQL.Text :=
            'INSERT INTO ge_albaranes_lineas_bultos (bulto_id, linea_albaran_id, cantidad, lote, fecha_caducidad) ' +
            'VALUES (:bid, :lid, :cant, :lote, :fcad)';
          QryIns.ParamByName('bid').AsInteger := LBoxBultoId;
          QryIns.ParamByName('lid').AsInteger := LLineaAlbaranId;
          QryIns.ParamByName('cant').AsFloat := LCant;
          QryIns.ParamByName('lote').AsString := mtLineas.FieldByName('lote').AsString;
          if not mtLineas.FieldByName('fecha_caducidad').IsNull then
            QryIns.ParamByName('fcad').AsDate := mtLineas.FieldByName('fecha_caducidad').AsDateTime
          else
            QryIns.ParamByName('fcad').Clear;
          QryIns.ExecSQL;
        end;

        // 5.2 Descuento directo del Stock Físico en ge_stock_envasado para trazabilidad
        QryIns.SQL.Text :=
          'UPDATE ge_stock_envasado ' +
          'SET cantidad_unidades = GREATEST(0, cantidad_unidades - :cant), ' +
          '    updated_at = NOW() ' +
          'WHERE id = :sid';
        QryIns.ParamByName('cant').AsFloat := LCant;
        QryIns.ParamByName('sid').AsInteger := mtLineas.FieldByName('stock_id').AsInteger;
        QryIns.ExecSQL;

        mtLineas.Next;
      end;

      // 6. Asignar SSCC principal a la cabecera si se generaron bultos
      if LPrimerSSCC <> '' then
      begin
        QryIns.SQL.Text := 'UPDATE ge_albaranes SET edi_numero_sscc = :sscc WHERE id = :id';
        QryIns.ParamByName('sscc').AsString := LPrimerSSCC;
        QryIns.ParamByName('id').AsInteger := LNewAlbaranId;
        QryIns.ExecSQL;
      end;

      // 7. Incrementar el contador de la serie
      QryIns.SQL.Text :=
        'UPDATE ge_series SET sig_numero_albaran = sig_numero_albaran + 1 ' +
        'WHERE activo = 1 AND empresa_id = :emp AND serie = :ser';
      QryIns.ParamByName('emp').AsString := dmgMain.CurrentCompanyId;
      QryIns.ParamByName('ser').AsString := LSerie;
      QryIns.ExecSQL;

      dmgMain.dbConn.Commit;
      FAlbaranCreadoId := LNewAlbaranId;

      ShowMessage(Format('✅ ¡Albarán Nº %s-%d creado exitosamente!' + sLineBreak +
        '• Se ha registrado la trazabilidad y descontado el stock de ge_stock_envasado.' + sLineBreak +
        '• Bultos logísticos SSCC creados y vinculados para EDICOM.', [LSerie, LNextNum]));

      // Preguntar si se desea abrir el editor del albarán
      if MessageDlg('¿Desea abrir el nuevo albarán en pantalla para revisarlo o imprimirlo?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
      begin
        LFrmEditor := TfrmAlbaranesEditor.Create(Application);
        try
          LFrmEditor.AlbaranId := IntToStr(LNewAlbaranId);
          LFrmEditor.ShowModal;
        finally
          LFrmEditor.Free;
        end;
      end;

      ModalResult := mrOk;
    except
      on E: Exception do
      begin
        dmgMain.dbConn.Rollback;
        MessageDlg('Error al generar el albarán: ' + E.Message, mtError, [mbOK], 0);
      end;
    end;
  finally
    Qry.Free;
    QryIns.Free;
  end;
end;

end.
