unit frm_CodigosBarrasCompleto;

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.Grids, Vcl.DBGrids,
  Vcl.Buttons, Vcl.DBCtrls, Data.DB, uAppTheme, frm_BaseList,
  FireDAC.Comp.Client, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param,
  FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf,
  FireDAC.Stan.Async, FireDAC.DApt, FireDAC.Comp.DataSet, Vcl.ComCtrls;

type
  TfrmCodigosBarrasCompleto = class(TfrmBaseList)
    dblcArticulo: TDBLookupComboBox;
    dsArticulosLookup: TDataSource;
    qryArticulosLookup: TFDQuery;
    procedure FormShow(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject); override;
    procedure dbGridMainDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure dbGridMainColEnter(Sender: TObject);
    procedure dblcArticuloCloseUp(Sender: TObject);
    procedure dblcArticuloKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure dbGridMainKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
  private
    { Private declarations }
    procedure qryMainUpdateRecord(ASender: TDataSet; ARequest: TFDUpdateRequest;
      var AAction: TFDErrorAction; AOptions: TFDUpdateRowOptions);
  public
    { Public declarations }
  end;

var
  frmCodigosBarrasCompleto: TfrmCodigosBarrasCompleto;

implementation

uses
  dmg_Main, uDbErrorHandler;

{$R *.dfm}

procedure TfrmCodigosBarrasCompleto.FormShow(Sender: TObject);
begin
  inherited;
  frmCodigosBarrasCompleto := Self;
  OnDestroy := FormDestroy;
  
  // Ocultar botones de CRUD que no aplican a esta consulta directa de Excel
  btnNuevo.Visible := False;
  btnEditar.Visible := False;
  btnBorrar.Visible := False;
  
  pnlSearch.Visible := True;
  lblSearch.Caption := 'Buscar Producto:';
  
  // Configurar desplegable flotante de artículos
  qryArticulosLookup.Connection := dmgMain.dbConn;
  try
    qryArticulosLookup.Open;
  except
    on E: Exception do
      TDbErrorHandler.HandleException(E, 'Error al cargar lista de artículos');
  end;
  
  // Habilitar edición en la rejilla y desactivar selección de fila
  dbGridMain.Options := dbGridMain.Options + [dgEditing] - [dgRowSelect];

  // Vincular dinámicamente los eventos de la rejilla (ya que no están en el DFM)
  dbGridMain.OnDrawColumnCell := dbGridMainDrawColumnCell;
  dbGridMain.OnColEnter := dbGridMainColEnter;
  dbGridMain.OnKeyDown := dbGridMainKeyDown;
  dblcArticulo.OnKeyDown := dblcArticuloKeyDown;

  dblcArticulo.Parent := dbGridMain;
  dblcArticulo.Visible := False;
end;

procedure TfrmCodigosBarrasCompleto.FormDestroy(Sender: TObject);
begin
  if frmCodigosBarrasCompleto = Self then
    frmCodigosBarrasCompleto := nil;
end;

procedure TfrmCodigosBarrasCompleto.btnRefreshClick(Sender: TObject);
var
  LSql: string;
begin
  FQryMain.Close;
  
  // Asociar evento de actualización personalizado para guardar e insertar transaccionalmente
  FQryMain.OnUpdateRecord := qryMainUpdateRecord;
  
  LSql := 'SELECT c.id, c.descripcion_producto, c.ean_13, c.articulo_id, a.descripcion AS articulo_local, ' +
          'c.ean_14_caja_1, c.unidades_caja_1, ' +
          'c.ean_14_caja_2, c.unidades_caja_2, ' +
          'c.ean_14_caja_4, c.unidades_caja_4, ' +
          'c.ean_14_caja_6, c.unidades_caja_6, ' +
          'c.ean_14_caja_8, c.unidades_caja_8, ' +
          'c.ean_14_caja_12, c.unidades_caja_12, ' +
          'c.ean_14_caja_16, c.unidades_caja_16, ' +
          'c.ean_14_caja_28, c.unidades_caja_28 ' +
          'FROM ge_codigos_barras_completo c ' +
          'LEFT JOIN ge_articulos a ON c.articulo_id = a.id ' +
          'WHERE 1=1 ';
  
  if Trim(edtSearch.Text) <> '' then
  begin
    LSql := LSql + 'AND (c.descripcion_producto LIKE :search OR c.ean_13 LIKE :search OR a.descripcion LIKE :search ' +
                   'OR c.ean_14_caja_1 LIKE :search OR c.ean_14_caja_2 LIKE :search ' +
                   'OR c.ean_14_caja_4 LIKE :search OR c.ean_14_caja_6 LIKE :search ' +
                   'OR c.ean_14_caja_8 LIKE :search OR c.ean_14_caja_12 LIKE :search ' +
                   'OR c.ean_14_caja_16 LIKE :search OR c.ean_14_caja_28 LIKE :search) ';
  end;
  
  LSql := LSql + 'ORDER BY c.descripcion_producto';
  
  FQryMain.SQL.Text := LSql;
  if Trim(edtSearch.Text) <> '' then
    FQryMain.ParamByName('search').AsString := '%' + Trim(edtSearch.Text) + '%';
    
  FQryMain.Open;
  
  // Habilitar edición en campos de FireDAC desactivando ReadOnly por defecto del LEFT JOIN
  FQryMain.UpdateOptions.UpdateTableName := 'ge_codigos_barras_completo';
  FQryMain.FieldByName('articulo_id').ReadOnly := False;
  FQryMain.FieldByName('articulo_local').ReadOnly := False;
  
  // Configurar las columnas de la rejilla
  dbGridMain.Columns.Clear;
  
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'articulo_local';
    Title.Caption := 'Artículo Local';
    Width := 280;
    ReadOnly := True; // Editado mediante DBLookupComboBox superpuesto
  end;

  with dbGridMain.Columns.Add do
  begin
    FieldName := 'descripcion_producto';
    Title.Caption := 'Producto (Excel)';
    Width := 200;
    ReadOnly := false;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_13';
    Title.Caption := 'EAN 13';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_1';
    Title.Caption := 'EAN Caja 1';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_1';
    Title.Caption := 'Uds 1';
    Width := 50;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_2';
    Title.Caption := 'EAN Caja 2';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_2';
    Title.Caption := 'Uds 2';
    Width := 50;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_4';
    Title.Caption := 'EAN Caja 4';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_4';
    Title.Caption := 'Uds 4';
    Width := 50;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_6';
    Title.Caption := 'EAN Caja 6';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_6';
    Title.Caption := 'Uds 6';
    Width := 50;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_8';
    Title.Caption := 'EAN Caja 8';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_8';
    Title.Caption := 'Uds 8';
    Width := 50;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_12';
    Title.Caption := 'EAN Caja 12';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_12';
    Title.Caption := 'Uds 12';
    Width := 55;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_16';
    Title.Caption := 'EAN Caja 16';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_16';
    Title.Caption := 'Uds 16';
    Width := 55;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'ean_14_caja_28';
    Title.Caption := 'EAN Caja 28';
    Width := 100;
    ReadOnly := False;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'unidades_caja_28';
    Title.Caption := 'Uds 28';
    Width := 55;
    ReadOnly := False;
  end;
end;

procedure TfrmCodigosBarrasCompleto.dbGridMainDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  inherited;
  // Posicionar DBLookupComboBox sobre la celda activa cuando esté en la columna de Artículo Local
  if (gdFocused in State) and (Column.FieldName = 'articulo_local') then
  begin
    dblcArticulo.Left := Rect.Left;
    dblcArticulo.Top := Rect.Top;
    dblcArticulo.Width := Rect.Width;
    dblcArticulo.Height := Rect.Height;
    dblcArticulo.Visible := True;
    dblcArticulo.BringToFront;
  end;
end;

procedure TfrmCodigosBarrasCompleto.dbGridMainColEnter(Sender: TObject);
begin
  if (dbGridMain.SelectedField <> nil) and (dbGridMain.SelectedField.FieldName <> 'articulo_local') then
    dblcArticulo.Visible := False;
end;

procedure TfrmCodigosBarrasCompleto.dblcArticuloCloseUp(Sender: TObject);
begin
  if dblcArticulo.Visible then
  begin
    dblcArticulo.Visible := False;
    if FQryMain.State in [dsEdit, dsInsert] then
    begin
      FQryMain.Post;
      btnRefreshClick(nil);
    end;
  end;
end;

procedure TfrmCodigosBarrasCompleto.dblcArticuloKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  if (Key = VK_DELETE) or (Key = VK_BACK) then
  begin
    if not (FQryMain.State in [dsEdit, dsInsert]) then
      FQryMain.Edit;
    
    FQryMain.FieldByName('articulo_id').Clear;
    dblcArticulo.KeyValue := Null;
    
    // Forzar el Post inmediato al presionar borrar
    FQryMain.Post;
    btnRefreshClick(nil);
  end;
end;

procedure TfrmCodigosBarrasCompleto.dbGridMainKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  if (dbGridMain.SelectedField <> nil) and (dbGridMain.SelectedField.FieldName = 'articulo_local') then
  begin
    if (Key = VK_DELETE) or (Key = VK_BACK) then
    begin
      if not (FQryMain.State in [dsEdit, dsInsert]) then
        FQryMain.Edit;
      
      FQryMain.FieldByName('articulo_id').Clear;
      dblcArticulo.KeyValue := Null;
      
      // Forzar el Post inmediato al presionar borrar
      FQryMain.Post;
      btnRefreshClick(nil);
    end;
  end;
end;

procedure TfrmCodigosBarrasCompleto.qryMainUpdateRecord(ASender: TDataSet;
  ARequest: TFDUpdateRequest; var AAction: TFDErrorAction; AOptions: TFDUpdateRowOptions);
var
  LArticuloId: Integer;
  LOldArticuloId: Integer;
  LEan13: string;
  LId: Integer;
  
  procedure InsertarEanAdicional(AEan: string; AUnidades: Integer);
  var
    LUni: Integer;
  begin
    if Trim(AEan) = '' then Exit;
    LUni := AUnidades;
    if LUni <= 0 then
      LUni := 1;
      
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'INSERT INTO ge_articulos_ean (articulo_id, ean, unidades, id_user_creator, id_user_update) ' +
                                'VALUES (:art, :ean, :uni, :user, :user) ' +
                                'ON DUPLICATE KEY UPDATE articulo_id = VALUES(articulo_id), ' +
                                'unidades = VALUES(unidades), id_user_update = VALUES(id_user_update)';
    dmgMain.qryExec.ParamByName('art').AsInteger := LArticuloId;
    dmgMain.qryExec.ParamByName('ean').AsString := Trim(AEan);
    dmgMain.qryExec.ParamByName('uni').AsInteger := LUni;
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ExecSQL;
  end;

  procedure GuardarFilaCompleta(AArtId: Variant);
  begin
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 
      'UPDATE ge_codigos_barras_completo SET ' +
      '  articulo_id = :art, ' +
      '  descripcion_producto = :desc_prod, ' +
      '  ean_13 = :ean_13, ' +
      '  ean_14_caja_1 = :ean1, unidades_caja_1 = :uni1, ' +
      '  ean_14_caja_2 = :ean2, unidades_caja_2 = :uni2, ' +
      '  ean_14_caja_4 = :ean4, unidades_caja_4 = :uni4, ' +
      '  ean_14_caja_6 = :ean6, unidades_caja_6 = :uni6, ' +
      '  ean_14_caja_8 = :ean8, unidades_caja_8 = :uni8, ' +
      '  ean_14_caja_12 = :ean12, unidades_caja_12 = :uni12, ' +
      '  ean_14_caja_16 = :ean16, unidades_caja_16 = :uni16, ' +
      '  ean_14_caja_28 = :ean28, unidades_caja_28 = :uni28, ' +
      '  id_user_update = :user ' +
      'WHERE id = :id';
      
    if VarIsNull(AArtId) or (AArtId = 0) then
    begin
      dmgMain.qryExec.ParamByName('art').DataType := ftInteger;
      dmgMain.qryExec.ParamByName('art').Value := Null;
    end
    else
      dmgMain.qryExec.ParamByName('art').AsInteger := AArtId;
      
    dmgMain.qryExec.ParamByName('desc_prod').AsString := ASender.FieldByName('descripcion_producto').AsString;
    dmgMain.qryExec.ParamByName('ean_13').AsString := ASender.FieldByName('ean_13').AsString;
    
    dmgMain.qryExec.ParamByName('ean1').AsString := ASender.FieldByName('ean_14_caja_1').AsString;
    dmgMain.qryExec.ParamByName('uni1').AsInteger := ASender.FieldByName('unidades_caja_1').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean2').AsString := ASender.FieldByName('ean_14_caja_2').AsString;
    dmgMain.qryExec.ParamByName('uni2').AsInteger := ASender.FieldByName('unidades_caja_2').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean4').AsString := ASender.FieldByName('ean_14_caja_4').AsString;
    dmgMain.qryExec.ParamByName('uni4').AsInteger := ASender.FieldByName('unidades_caja_4').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean6').AsString := ASender.FieldByName('ean_14_caja_6').AsString;
    dmgMain.qryExec.ParamByName('uni6').AsInteger := ASender.FieldByName('unidades_caja_6').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean8').AsString := ASender.FieldByName('ean_14_caja_8').AsString;
    dmgMain.qryExec.ParamByName('uni8').AsInteger := ASender.FieldByName('unidades_caja_8').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean12').AsString := ASender.FieldByName('ean_14_caja_12').AsString;
    dmgMain.qryExec.ParamByName('uni12').AsInteger := ASender.FieldByName('unidades_caja_12').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean16').AsString := ASender.FieldByName('ean_14_caja_16').AsString;
    dmgMain.qryExec.ParamByName('uni16').AsInteger := ASender.FieldByName('unidades_caja_16').AsInteger;
    
    dmgMain.qryExec.ParamByName('ean28').AsString := ASender.FieldByName('ean_14_caja_28').AsString;
    dmgMain.qryExec.ParamByName('uni28').AsInteger := ASender.FieldByName('unidades_caja_28').AsInteger;
    
    dmgMain.qryExec.ParamByName('user').AsString := dmgMain.CurrentUserId;
    dmgMain.qryExec.ParamByName('id').AsInteger := LId;
    
    dmgMain.qryExec.ExecSQL;
  end;

begin
  if ARequest = arUpdate then
  begin
    LId := ASender.FieldByName('id').AsInteger;
    LEan13 := ASender.FieldByName('ean_13').AsString;
    
    // 1. Obtener el articulo_id previo (viejo) de este registro
    LOldArticuloId := 0;
    dmgMain.qryExec.Close;
    dmgMain.qryExec.SQL.Text := 'SELECT articulo_id FROM ge_codigos_barras_completo WHERE id = :id';
    dmgMain.qryExec.ParamByName('id').AsInteger := LId;
    dmgMain.qryExec.Open;
    if not dmgMain.qryExec.IsEmpty then
      LOldArticuloId := dmgMain.qryExec.FieldByName('articulo_id').AsInteger;
    dmgMain.qryExec.Close;

    // Iniciar transacción para manejar todo atómicamente
    dmgMain.dbConn.StartTransaction;
    try
      if ASender.FieldByName('articulo_id').IsNull then
      begin
        // CASO A: Se ha dejado vacío (desasociar)
        
        // A.1. Actualizar el registro a NULL y guardar todos los campos editados en ge_codigos_barras_completo
        GuardarFilaCompleta(Null);
        
        // A.2. Si había un artículo asignado, limpiar sus códigos
        if LOldArticuloId > 0 then
        begin
          // Quitar EAN principal
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'UPDATE ge_articulos SET ean = NULL WHERE id = :art';
          dmgMain.qryExec.ParamByName('art').AsInteger := LOldArticuloId;
          dmgMain.qryExec.ExecSQL;
          
          // Eliminar EANs auxiliares
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_articulos_ean WHERE articulo_id = :art';
          dmgMain.qryExec.ParamByName('art').AsInteger := LOldArticuloId;
          dmgMain.qryExec.ExecSQL;
        end;
      end
      else
      begin
        // CASO B: Se ha asignado un artículo (nuevo o cambio)
        LArticuloId := ASender.FieldByName('articulo_id').AsInteger;
        
        // B.1. Si se ha CAMBIADO de artículo, limpiar los códigos del artículo ANTERIOR (viejo)
        if (LOldArticuloId > 0) and (LOldArticuloId <> LArticuloId) then
        begin
          // Quitar EAN principal del artículo viejo
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'UPDATE ge_articulos SET ean = NULL WHERE id = :art';
          dmgMain.qryExec.ParamByName('art').AsInteger := LOldArticuloId;
          dmgMain.qryExec.ExecSQL;
          
          // Eliminar EANs auxiliares del artículo viejo
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_articulos_ean WHERE articulo_id = :art';
          dmgMain.qryExec.ParamByName('art').AsInteger := LOldArticuloId;
          dmgMain.qryExec.ExecSQL;
        end;

        // B.2. Liberar cualquier otro registro que tuviera asignado este nuevo artículo para asegurar exclusividad
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'UPDATE ge_codigos_barras_completo SET articulo_id = NULL WHERE articulo_id = :art AND id <> :id';
        dmgMain.qryExec.ParamByName('art').AsInteger := LArticuloId;
        dmgMain.qryExec.ParamByName('id').AsInteger := LId;
        dmgMain.qryExec.ExecSQL;

        // B.3. Actualizar la asociación y guardar todos los campos editados en ge_codigos_barras_completo
        GuardarFilaCompleta(LArticuloId);
        
        // B.4. Eliminar códigos auxiliares anteriores del nuevo artículo (para sobreescribirlos limpios)
        dmgMain.qryExec.Close;
        dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_articulos_ean WHERE articulo_id = :art';
        dmgMain.qryExec.ParamByName('art').AsInteger := LArticuloId;
        dmgMain.qryExec.ExecSQL;
        
        // B.5. Asignar ean_13 al ean del artículo local nuevo
        if Trim(LEan13) <> '' then
        begin
          dmgMain.qryExec.Close;
          dmgMain.qryExec.SQL.Text := 'UPDATE ge_articulos SET ean = :ean WHERE id = :art';
          dmgMain.qryExec.ParamByName('ean').AsString := Trim(LEan13);
          dmgMain.qryExec.ParamByName('art').AsInteger := LArticuloId;
          dmgMain.qryExec.ExecSQL;
        end;
        
        // B.6. Insertar nuevos empaquetados secundarios en ge_articulos_ean
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_1').AsString, ASender.FieldByName('unidades_caja_1').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_2').AsString, ASender.FieldByName('unidades_caja_2').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_4').AsString, ASender.FieldByName('unidades_caja_4').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_6').AsString, ASender.FieldByName('unidades_caja_6').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_8').AsString, ASender.FieldByName('unidades_caja_8').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_12').AsString, ASender.FieldByName('unidades_caja_12').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_16').AsString, ASender.FieldByName('unidades_caja_16').AsInteger);
        InsertarEanAdicional(ASender.FieldByName('ean_14_caja_28').AsString, ASender.FieldByName('unidades_caja_28').AsInteger);
      end;
      
      dmgMain.dbConn.Commit;
      AAction := eaApplied;
    except
      on E: Exception do
      begin
        dmgMain.dbConn.Rollback;
        AAction := eaFail;
        TDbErrorHandler.HandleException(E, 'Error al actualizar e insertar códigos asociados al artículo');
      end;
    end;
  end;
end;

end.