unit frm_ClientesGrupos;

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, Data.DB, Vcl.Buttons, uAppTheme, FireDAC.Stan.Param, FireDAC.Comp.Client,
  frm_BaseList, Vcl.ComCtrls, System.Win.ComObj, frxClass, frxDBSet, System.Math, Vcl.Printers, System.UITypes;

type
  TfrmClientesGrupos = class(TfrmBaseList)
    splDetail: TSplitter;
    pnlDetail: TPanel;
    pgcSubDetails: TPageControl;
    tsSubClientes: TTabSheet;
    tsSubPrecios: TTabSheet;
    dbGridClientes: TDBGrid;
    dbGridPrecios: TDBGrid;
    qryClientes: TFDQuery;
    dsClientes: TDataSource;
    qryPrecios: TFDQuery;
    dsPrecios: TDataSource;

    pnlPreciosToolbar: TPanel;
    lblFiltroAno: TLabel;
    cbbFiltroAno: TComboBox;
    btnClonarTarifa: TBitBtn;

    procedure FormShow(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject); override;
    procedure btnNewClick(Sender: TObject); override;
    procedure btnEditClick(Sender: TObject); override;
    procedure btnDeleteClick(Sender: TObject); override;
    procedure btnPrintClick(Sender: TObject); override;
    procedure dbGridClientesDblClick(Sender: TObject);
    procedure dbGridClientesTitleClick(Column: TColumn);
    procedure dbGridPreciosTitleClick(Column: TColumn);
    procedure cbbFiltroAnoChange(Sender: TObject);
    procedure btnClonarTarifaClick(Sender: TObject);
  private
    FLastGrupoId: string;

    procedure dbGridMainCellClick(Column: TColumn);
    procedure dbGridMainKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure dsMainDataChange(Sender: TObject; Field: TField);
    procedure UpdateSubDetails;
    procedure ActualizarAnosFiltro(const AGrupoId: string);
    procedure PrepararReporteTarifaGrupo;
    function IsFieldTrue(AField: TField): Boolean;
    procedure ToggleFieldActive(AField: TField);
  public
    { Public declarations }
    procedure ExportClientesGruposToExcel;
  end;

var
  frmClientesGrupos: TfrmClientesGrupos;

implementation

uses
  dmg_Main, frm_ClientesGruposEditor, frm_ClientesEditor, uDbErrorHandler,
  frm_PrintOptions, FireDAC.Comp.DataSet, dlg_ClonarTarifa, dlg_SelectClientesEmail, uEnviarDocumento;

{$R *.dfm}

procedure TfrmClientesGrupos.FormShow(Sender: TObject);
begin
  AlphaFilterField := 'nombre';
  StatusFilterField := 'activo';
  StatusValues.Clear;
  StatusValues.Add('Activo');
  StatusValues.Add('Inactivo');
  DateFilterField := 'created_at';

  inherited;
  frmClientesGrupos := Self;
  OnDestroy := FormDestroy;
  pnlSearch.Visible := True;
  lblSearch.Caption := 'Buscar Grupo de Clientes:';

  dbGridMain.Options := dbGridMain.Options + [dgEditing] - [dgRowSelect];
  dbGridMain.OnCellClick := dbGridMainCellClick;
  dbGridMain.OnKeyDown := dbGridMainKeyDown;

  dbGridClientes.OnDrawColumnCell := dbGridMainDrawColumnCell;
  dbGridClientes.OnTitleClick := dbGridClientesTitleClick;

  dbGridPrecios.OnDrawColumnCell := dbGridMainDrawColumnCell;
  dbGridPrecios.OnTitleClick := dbGridPreciosTitleClick;

  dsMain.OnDataChange := dsMainDataChange;
  UpdateSubDetails;
end;

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

procedure TfrmClientesGrupos.dsMainDataChange(Sender: TObject; Field: TField);
begin
  if (Field = nil) then
    UpdateSubDetails;
end;

procedure TfrmClientesGrupos.dbGridClientesDblClick(Sender: TObject);
begin
  if qryClientes.Active and not qryClientes.IsEmpty then
  begin
    if not Assigned(frmClientesEditor) then
      Application.CreateForm(TfrmClientesEditor, frmClientesEditor);
    frmClientesEditor.ClienteId := qryClientes.FieldByName('id').AsString;
    frmClientesEditor.ShowModal;
    UpdateSubDetails;
  end;
end;

procedure TfrmClientesGrupos.dbGridClientesTitleClick(Column: TColumn);
begin
  if Assigned(qryClientes) and qryClientes.Active and Assigned(Column.Field) then
  begin
    if qryClientes.IndexFieldNames = Column.Field.FieldName then
      qryClientes.IndexFieldNames := Column.Field.FieldName + ':D'
    else
      qryClientes.IndexFieldNames := Column.Field.FieldName;
  end;
end;

procedure TfrmClientesGrupos.dbGridPreciosTitleClick(Column: TColumn);
begin
  if Assigned(qryPrecios) and qryPrecios.Active and Assigned(Column.Field) then
  begin
    if qryPrecios.IndexFieldNames = Column.Field.FieldName then
      qryPrecios.IndexFieldNames := Column.Field.FieldName + ':D'
    else
      qryPrecios.IndexFieldNames := Column.Field.FieldName;
  end;
end;

procedure TfrmClientesGrupos.UpdateSubDetails;
var
  LGrupoId: string;
  LFiltroAno: Integer;
begin
  if not Assigned(FQryMain) or not FQryMain.Active or FQryMain.IsEmpty or not Assigned(qryClientes) then
  begin
    if Assigned(qryClientes) and qryClientes.Active then qryClientes.Close;
    if Assigned(qryPrecios) and qryPrecios.Active then qryPrecios.Close;
    if Assigned(tsSubClientes) then tsSubClientes.Caption := 'Clientes del Grupo (0)';
    if Assigned(tsSubPrecios) then tsSubPrecios.Caption := 'Artículos y Precios Asignados (0)';
    Exit;
  end;

  LGrupoId := FQryMain.FieldByName('id').AsString;

  // 1. Cargar Clientes pertenecientes a este Grupo
  qryClientes.Close;
  qryClientes.SQL.Text :=
    'SELECT c.id, c.nombre_fiscal, c.nombre_comercial, c.nif, c.telefono, c.poblacion, c.email, c.activo ' +
    'FROM ge_clientes c ' +
    'WHERE c.clientesgrupo_id = :id_grupo ' +
    'ORDER BY c.nombre_fiscal';
  qryClientes.ParamByName('id_grupo').AsString := LGrupoId;
  qryClientes.Open;

  tsSubClientes.Caption := Format('Clientes del Grupo (%d)', [qryClientes.RecordCount]);

  dbGridClientes.Columns.Clear;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'id';
    Title.Caption := 'Código';
    Width := 60;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'nombre_fiscal';
    Title.Caption := 'Nombre Fiscal / Razón Social';
    Width := 220;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'nombre_comercial';
    Title.Caption := 'Nombre Comercial';
    Width := 180;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'nif';
    Title.Caption := 'NIF / CIF';
    Width := 95;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'telefono';
    Title.Caption := 'Teléfono';
    Width := 95;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'poblacion';
    Title.Caption := 'Población';
    Width := 120;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'email';
    Title.Caption := 'Email';
    Width := 180;
  end;
  with dbGridClientes.Columns.Add do
  begin
    FieldName := 'activo';
    Title.Caption := 'Activo';
    Width := 60;
  end;

  // 2. Cargar Artículos con precios asignados a este Grupo
  if FLastGrupoId <> LGrupoId then
  begin
    ActualizarAnosFiltro(LGrupoId);
    FLastGrupoId := LGrupoId;
  end;

  qryPrecios.Close;
  if TryStrToInt(Trim(cbbFiltroAno.Text), LFiltroAno) then
  begin
    qryPrecios.SQL.Text :=
      'SELECT a.id as codigo, a.descripcion as articulo_desc, cgp.precio, ' +
      '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
      '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
      'FROM ge_clientes_grupos_precios cgp ' +
      'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
      'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
      'WHERE cgp.id_cliente_grupo = :id_grupo ' +
      '  AND ((YEAR(cgp.valido_desde) = ' + IntToStr(LFiltroAno) + ') ' +
      '       OR (cgp.valido_desde IS NULL AND YEAR(cgp.valido_hasta) = ' + IntToStr(LFiltroAno) + ')) ' +
      'ORDER BY a.descripcion, cgp.und_min, cgp.valido_desde';
  end
  else if Pos('Sin fecha', cbbFiltroAno.Text) > 0 then
  begin
    qryPrecios.SQL.Text :=
      'SELECT a.id as codigo, a.descripcion as articulo_desc, cgp.precio, ' +
      '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
      '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
      'FROM ge_clientes_grupos_precios cgp ' +
      'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
      'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
      'WHERE cgp.id_cliente_grupo = :id_grupo ' +
      '  AND (cgp.valido_desde IS NULL AND cgp.valido_hasta IS NULL) ' +
      'ORDER BY a.descripcion, cgp.und_min, cgp.valido_desde';
  end
  else
  begin
    qryPrecios.SQL.Text :=
      'SELECT a.id as codigo, a.descripcion as articulo_desc, cgp.precio, ' +
      '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
      '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
      'FROM ge_clientes_grupos_precios cgp ' +
      'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
      'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
      'WHERE cgp.id_cliente_grupo = :id_grupo ' +
      'ORDER BY a.descripcion, cgp.und_min, cgp.valido_desde';
  end;

  qryPrecios.ParamByName('id_grupo').AsString := LGrupoId;
  qryPrecios.Open;

  if qryPrecios.FindField('precio') is TNumericField then
    TNumericField(qryPrecios.FieldByName('precio')).DisplayFormat := '#,##0.00';
  if qryPrecios.FindField('precio_con_iva') is TNumericField then
    TNumericField(qryPrecios.FieldByName('precio_con_iva')).DisplayFormat := '#,##0.00';

  tsSubPrecios.Caption := Format('Artículos y Precios Asignados (%d)', [qryPrecios.RecordCount]);

  dbGridPrecios.Columns.Clear;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'codigo';
    Title.Caption := 'Ref. Art.';
    Width := 60;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'articulo_desc';
    Title.Caption := 'Descripción Artículo';
    Width := 240;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'precio';
    Title.Caption := 'Precio Sin IVA';
    Width := 90;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'precio_con_iva';
    Title.Caption := 'Precio Con IVA';
    Width := 95;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'und_min';
    Title.Caption := 'Ud. Mín.';
    Width := 65;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'und_max';
    Title.Caption := 'Ud. Máx.';
    Width := 65;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'valido_desde';
    Title.Caption := 'Válido Desde';
    Width := 85;
  end;
  with dbGridPrecios.Columns.Add do
  begin
    FieldName := 'valido_hasta';
    Title.Caption := 'Válido Hasta';
    Width := 85;
  end;
end;

procedure TfrmClientesGrupos.btnRefreshClick(Sender: TObject);
var
  LSql: string;
begin
  FQryMain.Close;
  LSql := 'SELECT id, nombre, descuento, activo, created_at FROM ge_clientes_grupos ';
  
  if Trim(edtSearch.Text) <> '' then
  begin
    LSql := LSql + 'WHERE nombre LIKE :search ';
  end;
  LSql := LSql + 'ORDER BY nombre';
  
  FQryMain.SQL.Text := LSql;
  
  if Trim(edtSearch.Text) <> '' then
    FQryMain.ParamByName('search').AsString := '%' + Trim(edtSearch.Text) + '%';
    
  FQryMain.Open;
  
  // Configurar FireDAC para permitir edición in-situ del campo activo
  FQryMain.UpdateOptions.UpdateTableName := 'ge_clientes_grupos';
  FQryMain.UpdateOptions.KeyFields := 'id';
  FQryMain.UpdateOptions.ReadOnly := False;
  
  if FQryMain.FindField('activo') <> nil then
    FQryMain.FieldByName('activo').ReadOnly := False;

  if FQryMain.FindField('descuento') <> nil then
    TNumericField(FQryMain.FieldByName('descuento')).DisplayFormat := '#,##0.00 %';

  // Configurar columnas de la rejilla
  dbGridMain.Columns.Clear;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'id';
    Title.Caption := 'ID';
    Width := 60;
    ReadOnly := True;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'nombre';
    Title.Caption := 'Nombre del Grupo';
    Width := 450;
    ReadOnly := True;
  end;
  with dbGridMain.Columns.Add do
  begin
    FieldName := 'activo';
    Title.Caption := 'Activo';
    Width := 80;
    ReadOnly := False;
  end;

  UpdateSubDetails;
end;

procedure TfrmClientesGrupos.btnEditClick(Sender: TObject);
begin
  if FQryMain.IsEmpty then Exit;
  
  if not Assigned(frmClientesGruposEditor) then
    Application.CreateForm(TfrmClientesGruposEditor, frmClientesGruposEditor);
  
  frmClientesGruposEditor.ParentForm := Self;
  frmClientesGruposEditor.GrupoId := FQryMain.FieldByName('id').AsString;
  if frmClientesGruposEditor.ShowModal = mrOk then
    btnRefreshClick(nil);
end;

procedure TfrmClientesGrupos.btnNewClick(Sender: TObject);
begin
  if not Assigned(frmClientesGruposEditor) then
    Application.CreateForm(TfrmClientesGruposEditor, frmClientesGruposEditor);
  
  frmClientesGruposEditor.ParentForm := Self;
  frmClientesGruposEditor.GrupoId := '';
  if frmClientesGruposEditor.ShowModal = mrOk then
    btnRefreshClick(nil);
end;

procedure TfrmClientesGrupos.btnDeleteClick(Sender: TObject);
var
  LGrupoId: string;
begin
  if FQryMain.IsEmpty then Exit;
  
  if MessageDlg('¿Está seguro de que desea eliminar este grupo de clientes?', 
                mtWarning, [mbYes, mbNo], 0) = mrYes then
  begin
    LGrupoId := FQryMain.FieldByName('id').AsString;
    try
      dmgMain.qryExec.Close;
      dmgMain.qryExec.SQL.Text := 'DELETE FROM ge_clientes_grupos WHERE id = :id';
      dmgMain.qryExec.ParamByName('id').AsString := LGrupoId;
      dmgMain.qryExec.ExecSQL;
      
      btnRefreshClick(nil);
    except
      on E: Exception do
        TDbErrorHandler.HandleException(E, 'Error al eliminar el grupo de clientes');
    end;
  end;
end;

function TfrmClientesGrupos.IsFieldTrue(AField: TField): Boolean;
var
  S: string;
begin
  if (AField = nil) or AField.IsNull then
    Exit(False);
  if AField is TBooleanField then
    Exit(AField.AsBoolean);

  S := Trim(AField.AsString);
  Result := (S = '1') or SameText(S, 'T') or SameText(S, 'TRUE') or SameText(S, 'S') or SameText(S, 'SI');
end;

procedure TfrmClientesGrupos.ToggleFieldActive(AField: TField);
begin
  if (AField = nil) then Exit;
  if not (AField.DataSet.State in [dsEdit, dsInsert]) then
    AField.DataSet.Edit;
    
  try
    if AField is TBooleanField then
      AField.AsBoolean := not AField.AsBoolean
    else
    begin
      if IsFieldTrue(AField) then
        AField.AsString := '0'
      else
        AField.AsString := '1';
    end;
    AField.DataSet.Post;
  except
    AField.DataSet.Cancel;
  end;
end;

procedure TfrmClientesGrupos.dbGridMainCellClick(Column: TColumn);
begin
  if (Column.Field <> nil) and SameText(Column.FieldName, 'activo') then
  begin
    ToggleFieldActive(Column.Field);
  end;
end;

procedure TfrmClientesGrupos.dbGridMainKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  if (Key = VK_SPACE) and (dbGridMain.SelectedField <> nil) and SameText(dbGridMain.SelectedField.FieldName, 'activo') then
  begin
    ToggleFieldActive(dbGridMain.SelectedField);
    Key := 0;
  end;
end;

procedure TfrmClientesGrupos.ActualizarAnosFiltro(const AGrupoId: string);
var
  Qry: TFDQuery;
  LSelectedText: string;
  LAno: Integer;
  LHasNullDates: Boolean;
begin
  if not Assigned(cbbFiltroAno) then Exit;

  LSelectedText := cbbFiltroAno.Text;
  cbbFiltroAno.Items.BeginUpdate;
  try
    cbbFiltroAno.Items.Clear;
    cbbFiltroAno.Items.Add('(Todos los ejercicios)');

    LHasNullDates := False;
    Qry := TFDQuery.Create(nil);
    try
      Qry.Connection := dmgMain.dbConn;
      Qry.SQL.Text :=
        'SELECT DISTINCT YEAR(valido_desde) as ano ' +
        'FROM ge_clientes_grupos_precios ' +
        'WHERE id_cliente_grupo = :grp AND valido_desde IS NOT NULL ' +
        'ORDER BY ano DESC';
      Qry.ParamByName('grp').AsString := AGrupoId;
      Qry.Open;

      while not Qry.Eof do
      begin
        LAno := Qry.FieldByName('ano').AsInteger;
        if LAno > 1900 then
          cbbFiltroAno.Items.Add(IntToStr(LAno));
        Qry.Next;
      end;

      Qry.Close;
      Qry.SQL.Text :=
        'SELECT COUNT(*) FROM ge_clientes_grupos_precios ' +
        'WHERE id_cliente_grupo = :grp AND (valido_desde IS NULL OR valido_hasta IS NULL)';
      Qry.ParamByName('grp').AsString := AGrupoId;
      Qry.Open;
      if (not Qry.IsEmpty) and (Qry.Fields[0].AsInteger > 0) then
        LHasNullDates := True;

      if LHasNullDates then
        cbbFiltroAno.Items.Add('(Sin fechas definidas)');
    finally
      Qry.Free;
    end;

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

procedure TfrmClientesGrupos.cbbFiltroAnoChange(Sender: TObject);
begin
  UpdateSubDetails;
end;

procedure TfrmClientesGrupos.btnClonarTarifaClick(Sender: TObject);
var
  LGrupoId, LGrupoNom: string;
  LNewYear: Integer;
begin
  if not Assigned(FQryMain) or not FQryMain.Active or FQryMain.IsEmpty then
  begin
    ShowMessage('Seleccione un grupo de clientes primero.');
    Exit;
  end;

  LGrupoId := FQryMain.FieldByName('id').AsString;
  LGrupoNom := FQryMain.FieldByName('nombre').AsString;

  if TdlgClonarTarifa.Execute(Self, LGrupoId, LGrupoNom, LNewYear) then
  begin
    FLastGrupoId := ''; // Forzar recarga de combo de años
    ActualizarAnosFiltro(LGrupoId);
    if cbbFiltroAno.Items.IndexOf(IntToStr(LNewYear)) >= 0 then
      cbbFiltroAno.ItemIndex := cbbFiltroAno.Items.IndexOf(IntToStr(LNewYear));
    UpdateSubDetails;
  end;
end;

procedure TfrmClientesGrupos.btnPrintClick(Sender: TObject);
begin
  PrepararReporteTarifaGrupo;
end;

procedure TfrmClientesGrupos.PrepararReporteTarifaGrupo;
var
  LGrupoId, LGrupoNom: string;
  LReportName, LReportPath: string;
  QryReport: TFDQuery;
  LMemTable: TFDMemTable;
  LObjTitulo, LObjEmp, LImgObj: TfrxComponent;
  LDesc, LBaseName, LTier, LNormKey: string;
  LPrecio, LTipoIva, LPrecioConIva, LPvp: Double;
  QryEmp: TFDQuery;
  LEmpId, LEmpTexto, LLogoPath: string;
  LStream: TMemoryStream;
  LFiltroAno: Integer;
  LTituloSufijo: string;
  LMultiEmailCallback: TMultiEmailCallback;

  procedure ParseArticleDesc(const ADesc: string; out ABaseName, ATier, ANormKey: string);
  var
    S, SUpper: string;
    P, J: Integer;
  begin
    S := Trim(ADesc);
    SUpper := UpperCase(S);
    ATier := '';

    // 1. Detectar XL o L
    if (Pos(' XL', SUpper) > 0) or (Pos('XL ', SUpper) > 0) or (Pos('(XL', SUpper) > 0) then
      ATier := 'XL'
    else if (Pos(' L', SUpper) > 0) or (Pos('L ', SUpper) > 0) or (Pos('(L', SUpper) > 0) then
      ATier := 'L';

    if ATier = '' then
    begin
      if (Pos('1100', SUpper) > 0) or (Pos('1200', SUpper) > 0) then
        ATier := 'XL'
      else if (Pos('550', SUpper) > 0) or (Pos('600', SUpper) > 0) then
        ATier := 'L'
      else
        ATier := 'XL';
    end;

    // 2. Limpiar expresiones de peso
    ABaseName := S;
    while True do
    begin
      P := Pos('(', ABaseName);
      if P > 0 then
      begin
        J := Pos(')', ABaseName);
        if J > P then
        begin
          if (Pos('GR', UpperCase(Copy(ABaseName, P, J - P + 1))) > 0) or
             (Pos('G', UpperCase(Copy(ABaseName, P, J - P + 1))) > 0) then
          begin
            Delete(ABaseName, P, J - P + 1);
            Continue;
          end;
        end;
      end;
      Break;
    end;

    ABaseName := StringReplace(ABaseName, '1100-1200 GR', '', [rfIgnoreCase, rfReplaceAll]);
    ABaseName := StringReplace(ABaseName, '1100/1200GR', '', [rfIgnoreCase, rfReplaceAll]);
    ABaseName := StringReplace(ABaseName, '550/600GR', '', [rfIgnoreCase, rfReplaceAll]);
    ABaseName := StringReplace(ABaseName, '550-600GR', '', [rfIgnoreCase, rfReplaceAll]);

    SUpper := UpperCase(ABaseName);
    if (Length(SUpper) >= 3) and (Copy(SUpper, Length(SUpper) - 2, 3) = ' XL') then
      Delete(ABaseName, Length(ABaseName) - 2, 3)
    else if (Length(SUpper) >= 2) and (Copy(SUpper, Length(SUpper) - 1, 2) = ' L') then
      Delete(ABaseName, Length(ABaseName) - 1, 2);

    ABaseName := StringReplace(ABaseName, ' XL ', ' ', [rfIgnoreCase, rfReplaceAll]);
    ABaseName := StringReplace(ABaseName, ' L ', ' ', [rfIgnoreCase, rfReplaceAll]);
    ABaseName := Trim(ABaseName);

    ANormKey := UpperCase(ABaseName);
    ANormKey := StringReplace(ANormKey, 'Á', 'A', [rfReplaceAll]);
    ANormKey := StringReplace(ANormKey, 'É', 'E', [rfReplaceAll]);
    ANormKey := StringReplace(ANormKey, 'Í', 'I', [rfReplaceAll]);
    ANormKey := StringReplace(ANormKey, 'Ó', 'O', [rfReplaceAll]);
    ANormKey := StringReplace(ANormKey, 'Ú', 'U', [rfReplaceAll]);
  end;
begin
  if not Assigned(FQryMain) or not FQryMain.Active or FQryMain.IsEmpty then
  begin
    ShowMessage('Seleccione un grupo de clientes.');
    Exit;
  end;

  LGrupoId := FQryMain.FieldByName('id').AsString;
  LGrupoNom := FQryMain.FieldByName('nombre').AsString;
  LReportName := 'Tarifa_Grupo_' + StringReplace(LGrupoNom, ' ', '_', [rfReplaceAll]);

  // Localizar la plantilla FastReport
  LReportPath := ExtractFilePath(ParamStr(0)) + 'reportes\TarifaPreciosGrupo.fr3';
  if not FileExists(LReportPath) then
    LReportPath := ExtractFilePath(ParamStr(0)) + '..\reportes\TarifaPreciosGrupo.fr3';
  if not FileExists(LReportPath) then
    LReportPath := ExtractFilePath(ParamStr(0)) + '..\..\reportes\TarifaPreciosGrupo.fr3';
  if not FileExists(LReportPath) then
    LReportPath := 'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\reportes\TarifaPreciosGrupo.fr3';
  if not FileExists(LReportPath) then
    LReportPath := 'reportes\TarifaPreciosGrupo.fr3';

  if not FileExists(LReportPath) then
  begin
    ShowMessage('No se ha encontrado la plantilla de informe:' + sLineBreak + LReportPath);
    TfrmPrintOptions.ShowOptions(Self, nil, dbGridPrecios, LReportName);
    Exit;
  end;

  QryReport := TFDQuery.Create(nil);
  LMemTable := TFDMemTable.Create(nil);
  try
    QryReport.Connection := dmgMain.dbConn;
    if TryStrToInt(Trim(cbbFiltroAno.Text), LFiltroAno) then
    begin
      QryReport.SQL.Text :=
        'SELECT a.id as codigo, a.descripcion as articulo_desc, ' +
        '       cgp.precio, ' +
        '       COALESCE(t.tipo, 10.00) as tipo_iva, ' +
        '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
        '       ROUND(a.pvp1 * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as pvp, ' +
        '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
        'FROM ge_clientes_grupos_precios cgp ' +
        'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
        'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
        'WHERE cgp.id_cliente_grupo = :id_grupo ' +
        '  AND ((YEAR(cgp.valido_desde) = ' + IntToStr(LFiltroAno) + ') ' +
        '       OR (cgp.valido_desde IS NULL AND YEAR(cgp.valido_hasta) = ' + IntToStr(LFiltroAno) + ')) ' +
        'ORDER BY a.descripcion';
    end
    else if Pos('Sin fecha', cbbFiltroAno.Text) > 0 then
    begin
      QryReport.SQL.Text :=
        'SELECT a.id as codigo, a.descripcion as articulo_desc, ' +
        '       cgp.precio, ' +
        '       COALESCE(t.tipo, 10.00) as tipo_iva, ' +
        '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
        '       ROUND(a.pvp1 * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as pvp, ' +
        '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
        'FROM ge_clientes_grupos_precios cgp ' +
        'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
        'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
        'WHERE cgp.id_cliente_grupo = :id_grupo ' +
        '  AND (cgp.valido_desde IS NULL AND cgp.valido_hasta IS NULL) ' +
        'ORDER BY a.descripcion';
    end
    else
    begin
      QryReport.SQL.Text :=
        'SELECT a.id as codigo, a.descripcion as articulo_desc, ' +
        '       cgp.precio, ' +
        '       COALESCE(t.tipo, 10.00) as tipo_iva, ' +
        '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
        '       ROUND(a.pvp1 * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as pvp, ' +
        '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
        'FROM ge_clientes_grupos_precios cgp ' +
        'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
        'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
        'WHERE cgp.id_cliente_grupo = :id_grupo ' +
        'ORDER BY a.descripcion';
    end;
    QryReport.ParamByName('id_grupo').AsString := LGrupoId;
    QryReport.Open;

    if QryReport.IsEmpty then
    begin
      ShowMessage('El grupo seleccionado no tiene artículos ni precios asignados para el filtro actual.');
      TfrmPrintOptions.ShowOptions(Self, nil, dbGridPrecios, LReportName);
      Exit;
    end;

    // Crear estructura del MemTable para la cuadrícula pivotada
    LMemTable.FieldDefs.Clear;
    LMemTable.FieldDefs.Add('norm_key', ftString, 120);
    LMemTable.FieldDefs.Add('codigo', ftWideString, 50);
    LMemTable.FieldDefs.Add('concepto', ftWideString, 250);
    LMemTable.FieldDefs.Add('xl_sin_iva', ftWideString, 30);
    LMemTable.FieldDefs.Add('xl_con_iva', ftWideString, 30);
    LMemTable.FieldDefs.Add('xl_pvp', ftWideString, 30);
    LMemTable.FieldDefs.Add('l_sin_iva', ftWideString, 30);
    LMemTable.FieldDefs.Add('l_con_iva', ftWideString, 30);
    LMemTable.FieldDefs.Add('l_pvp', ftWideString, 30);
    LMemTable.CreateDataSet;
    LMemTable.IndexFieldNames := 'norm_key';

    while not QryReport.Eof do
    begin
      LDesc := QryReport.FieldByName('articulo_desc').AsString;
      ParseArticleDesc(LDesc, LBaseName, LTier, LNormKey);

      LPrecio := QryReport.FieldByName('precio').AsFloat;
      LTipoIva := QryReport.FieldByName('tipo_iva').AsFloat;
      LPrecioConIva := Round(LPrecio * (1 + (LTipoIva / 100)) * 100) / 100;
      LPvp := Round(QryReport.FieldByName('pvp').AsFloat * 100) / 100;

      if LMemTable.Locate('norm_key', LNormKey, []) then
        LMemTable.Edit
      else
      begin
        LMemTable.Append;
        LMemTable.FieldByName('norm_key').AsString := LNormKey;
        LMemTable.FieldByName('codigo').AsString := '';
        LMemTable.FieldByName('concepto').AsString := LBaseName;
        LMemTable.FieldByName('xl_sin_iva').AsString := '';
        LMemTable.FieldByName('xl_con_iva').AsString := '';
        LMemTable.FieldByName('xl_pvp').AsString := '';
        LMemTable.FieldByName('l_sin_iva').AsString := '';
        LMemTable.FieldByName('l_con_iva').AsString := '';
        LMemTable.FieldByName('l_pvp').AsString := '';
      end;

      if LMemTable.FieldByName('codigo').AsString = '' then
        LMemTable.FieldByName('codigo').AsString := QryReport.FieldByName('codigo').AsString
      else if Pos(QryReport.FieldByName('codigo').AsString, LMemTable.FieldByName('codigo').AsString) = 0 then
        LMemTable.FieldByName('codigo').AsString := LMemTable.FieldByName('codigo').AsString + ' / ' + QryReport.FieldByName('codigo').AsString;

      if LTier = 'L' then
      begin
        LMemTable.FieldByName('l_sin_iva').AsString := FormatFloat('#,##0.00 €', LPrecio);
        LMemTable.FieldByName('l_con_iva').AsString := FormatFloat('#,##0.00 €', LPrecioConIva);
        if LPvp > 0 then
          LMemTable.FieldByName('l_pvp').AsString := FormatFloat('#,##0.00 €', LPvp);
      end
      else
      begin
        LMemTable.FieldByName('xl_sin_iva').AsString := FormatFloat('#,##0.00 €', LPrecio);
        LMemTable.FieldByName('xl_con_iva').AsString := FormatFloat('#,##0.00 €', LPrecioConIva);
        if LPvp > 0 then
          LMemTable.FieldByName('xl_pvp').AsString := FormatFloat('#,##0.00 €', LPvp);
      end;

      LMemTable.Post;
      QryReport.Next;
    end;

    LMemTable.IndexFieldNames := 'concepto';
    LMemTable.First;

    // Vincular frxDBMaster al dataset pivotado en memoria
    dmgMain.frxDBMaster.UserName := 'frxDBmaster';
    dmgMain.frxDBMaster.DataSet := LMemTable;

    dmgMain.frxInforme.Clear;
    dmgMain.frxInforme.LoadFromFile(LReportPath);
    dmgMain.frxInforme.DataSets.Clear;
    dmgMain.frxInforme.DataSets.Add(dmgMain.frxDBMaster);
    dmgMain.frxInforme.EnabledDataSets.Clear;
    dmgMain.frxInforme.EnabledDataSets.Add(dmgMain.frxDBMaster);
    dmgMain.frxInforme.ReportOptions.Name := LReportName;

    // Actualizar cabecera del informe con datos y logo de la empresa activa
    LEmpId := dmgMain.CurrentCompanyId;
    if LEmpId = '' then
      LEmpId := '1';

    QryEmp := TFDQuery.Create(nil);
    try
      QryEmp.Connection := dmgMain.dbConn;
      QryEmp.SQL.Text := 'SELECT id, nombre, cif_nif, direccion, cp, poblacion, provincia, telefono1, email, logo FROM ge_empresas WHERE id = :id LIMIT 1';
      QryEmp.ParamByName('id').AsString := LEmpId;
      QryEmp.Open;

      if not QryEmp.IsEmpty then
      begin
        LEmpTexto := Trim(QryEmp.FieldByName('nombre').AsString);
        if Trim(QryEmp.FieldByName('cif_nif').AsString) <> '' then
          LEmpTexto := LEmpTexto + sLineBreak + 'CIF: ' + Trim(QryEmp.FieldByName('cif_nif').AsString);
        if Trim(QryEmp.FieldByName('direccion').AsString) <> '' then
          LEmpTexto := LEmpTexto + sLineBreak + Trim(QryEmp.FieldByName('direccion').AsString);
        if Trim(QryEmp.FieldByName('cp').AsString + QryEmp.FieldByName('poblacion').AsString) <> '' then
        begin
          if Trim(QryEmp.FieldByName('provincia').AsString) <> '' then
            LEmpTexto := LEmpTexto + sLineBreak + Trim(QryEmp.FieldByName('cp').AsString + ' ' + QryEmp.FieldByName('poblacion').AsString + ' (' + QryEmp.FieldByName('provincia').AsString + ')')
          else
            LEmpTexto := LEmpTexto + sLineBreak + Trim(QryEmp.FieldByName('cp').AsString + ' ' + QryEmp.FieldByName('poblacion').AsString);
        end;
        if Trim(QryEmp.FieldByName('telefono1').AsString) <> '' then
          LEmpTexto := LEmpTexto + sLineBreak + 'Tel: ' + Trim(QryEmp.FieldByName('telefono1').AsString);

        LObjEmp := dmgMain.frxInforme.FindObject('memEmpresaInfo');
        if Assigned(LObjEmp) and (LObjEmp is TfrxMemoView) then
        begin
          TfrxMemoView(LObjEmp).AllowExpressions := False;
          TfrxMemoView(LObjEmp).Text := LEmpTexto;
        end;

        LImgObj := dmgMain.frxInforme.FindObject('imgLogo');
        if Assigned(LImgObj) and (LImgObj is TfrxPictureView) then
        begin
          if not TBlobField(QryEmp.FieldByName('logo')).IsNull then
          begin
            LStream := TMemoryStream.Create;
            try
              TBlobField(QryEmp.FieldByName('logo')).SaveToStream(LStream);
              LStream.Position := 0;
              if LStream.Size > 0 then
              begin
                TfrxPictureView(LImgObj).Picture.LoadFromStream(LStream);
                TfrxPictureView(LImgObj).Visible := True;
              end
              else
                TfrxPictureView(LImgObj).Visible := False;
            finally
              LStream.Free;
            end;
          end
          else
          begin
            LLogoPath := ExtractFilePath(ParamStr(0)) + 'logo_raul.png';
            if not FileExists(LLogoPath) then
              LLogoPath := ExtractFilePath(ParamStr(0)) + 'logo.png';
            if not FileExists(LLogoPath) then
              LLogoPath := 'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\logo_raul.png';

            if FileExists(LLogoPath) then
            begin
              TfrxPictureView(LImgObj).Picture.LoadFromFile(LLogoPath);
              TfrxPictureView(LImgObj).Visible := True;
            end
            else
              TfrxPictureView(LImgObj).Visible := False;
          end;
        end;
      end;
    finally
      QryEmp.Free;
    end;

    // Actualizar título del informe
    LObjTitulo := dmgMain.frxInforme.FindObject('lblTitulo');
    if Assigned(LObjTitulo) and (LObjTitulo is TfrxMemoView) then
    begin
      TfrxMemoView(LObjTitulo).AllowExpressions := False;
      if TryStrToInt(Trim(cbbFiltroAno.Text), LFiltroAno) then
        LTituloSufijo := ' (TARIFA ' + IntToStr(LFiltroAno) + ')'
      else
        LTituloSufijo := '';
      TfrxMemoView(LObjTitulo).Text := 'PRECIO VENTA: ' + UpperCase(LGrupoNom) + LTituloSufijo;
    end;

    dmgMain.frxInforme.PrepareReport(True);

    // Configurar callback para envío de correo individualizado si el grupo tiene más de un cliente
    LMultiEmailCallback :=
      function(const APdfPath: string): Boolean
      var
        QryCount: TFDQuery;
        LTotalClientes: Integer;
        LClienteEmail, LClienteTel: string;
      begin
        Result := False;
        QryCount := TFDQuery.Create(nil);
        try
          QryCount.Connection := dmgMain.dbConn;
          QryCount.SQL.Text :=
            'SELECT id, nombre_fiscal, email, telefono ' +
            'FROM ge_clientes ' +
            'WHERE clientesgrupo_id = :grp AND activo = 1 ' +
            'ORDER BY nombre_fiscal';
          QryCount.ParamByName('grp').AsString := LGrupoId;
          QryCount.Open;
          LTotalClientes := QryCount.RecordCount;

          if LTotalClientes = 0 then
          begin
            ShowMessage('El grupo de clientes seleccionado no tiene ningún cliente asociado activo.');
            Exit;
          end;

          if LTotalClientes > 1 then
          begin
            // Más de un cliente: abrir diálogo con checkboxes para seleccionar destinatarios
            Result := TdlgSelectClientesEmail.Execute(Self, LGrupoId, LGrupoNom, APdfPath, LReportName);
          end
          else
          begin
            // Solo 1 cliente: abrir ventana estándar de envío con datos precargados de ese cliente
            LClienteEmail := QryCount.FieldByName('email').AsString;
            LClienteTel := QryCount.FieldByName('telefono').AsString;

            if not Assigned(fEnviarDocumento) then
              Application.CreateForm(TfEnviarDocumento, fEnviarDocumento);

            fEnviarDocumento.RutasPDF := [APdfPath];
            fEnviarDocumento.InitData(LClienteTel, LClienteEmail, '', 'Tarifa de Precios - ' + LGrupoNom, LReportName);
            Result := (fEnviarDocumento.ShowModal = mrOk);
          end;
        finally
          QryCount.Free;
        end;
      end;

    TfrmPrintOptions.ShowOptions(Self, dmgMain.frxInforme, dbGridPrecios, LReportName, '', '', 1, nil, LMultiEmailCallback);
  finally
    dmgMain.frxDBMaster.DataSet := nil;
    LMemTable.Free;
    QryReport.Free;
  end;
end;

procedure TfrmClientesGrupos.ExportClientesGruposToExcel;
var
  SaveDialog: TSaveDialog;
  ExcelApp, Workbook, Sheet: OleVariant;
  Row: Integer;
  Bookmark: TBookmark;
  LGrupoId, LGrupoNom: string;
  LActivoStr: string;
  QryC, QryP: TFDQuery;
  LClientCount, LPriceCount: Integer;
begin
  if not Assigned(FQryMain) or not FQryMain.Active or FQryMain.IsEmpty then
  begin
    ShowMessage('No hay grupos de clientes para exportar.');
    Exit;
  end;

  SaveDialog := TSaveDialog.Create(Self);
  try
    SaveDialog.Filter := 'Archivos de Excel (*.xlsx)|*.xlsx|Todos los archivos (*.*)|*.*';
    SaveDialog.DefaultExt := 'xlsx';
    SaveDialog.Title := 'Exportar Grupos, Clientes y Precios a Excel';
    SaveDialog.FileName := 'Grupos_Clientes_Precios_' + FormatDateTime('yyyyMMdd_hhmmss', Now) + '.xlsx';

    if not SaveDialog.Execute then Exit;

    try
      ExcelApp := CreateOleObject('Excel.Application');
    except
      on E: Exception do
      begin
        ShowMessage('No se pudo iniciar Microsoft Excel. Verifique que Excel esté instalado.' + sLineBreak + E.Message);
        Exit;
      end;
    end;

    QryC := TFDQuery.Create(nil);
    QryP := TFDQuery.Create(nil);
    try
      QryC.Connection := dmgMain.dbConn;
      QryP.Connection := dmgMain.dbConn;

      ExcelApp.Visible := False;
      ExcelApp.DisplayAlerts := False;
      Workbook := ExcelApp.Workbooks.Add;
      Sheet := Workbook.ActiveSheet;
      Sheet.Name := 'Grupos Clientes y Precios';

      Row := 1;

      // Encabezado del documento Excel
      Sheet.Cells[Row, 1] := 'INFORME DE GRUPOS DE CLIENTES, CLIENTES ASIGNADOS Y TARIFAS ESPECÍFICAS';
      Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Merge;
      Sheet.Cells[Row, 1].Font.Bold := True;
      Sheet.Cells[Row, 1].Font.Size := 14;
      Sheet.Cells[Row, 1].Font.Color := RGB(0, 51, 102);
      Inc(Row, 2);

      FQryMain.DisableControls;
      Bookmark := FQryMain.GetBookmark;
      try
        FQryMain.First;
        while not FQryMain.Eof do
        begin
          LGrupoId := FQryMain.FieldByName('id').AsString;
          LGrupoNom := FQryMain.FieldByName('nombre').AsString;
          if IsFieldTrue(FQryMain.FieldByName('activo')) then
            LActivoStr := 'ACTIVO'
          else
            LActivoStr := 'INACTIVO';

          // --- ENCABEZADO DE GRUPO ---
          Sheet.Cells[Row, 1] := Format('GRUPO #%s: %s [%s]', [LGrupoId, UpperCase(LGrupoNom), LActivoStr]);
          Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Merge;
          Sheet.Cells[Row, 1].Font.Bold := True;
          Sheet.Cells[Row, 1].Font.Size := 11;
          Sheet.Cells[Row, 1].Font.Color := RGB(255, 255, 255);
          Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Interior.Color := RGB(41, 128, 185); // Azul
          Inc(Row);

          // --- SECCIÓN 1: CLIENTES DEL GRUPO ---
          QryC.Close;
          QryC.SQL.Text :=
            'SELECT c.id, c.nombre_fiscal, c.nombre_comercial, c.nif, c.telefono, c.poblacion, c.email, c.activo ' +
            'FROM ge_clientes c WHERE c.clientesgrupo_id = :id ORDER BY c.nombre_fiscal';
          QryC.ParamByName('id').AsString := LGrupoId;
          QryC.Open;

          LClientCount := QryC.RecordCount;
          Sheet.Cells[Row, 1] := Format('  ► CLIENTES ASIGNADOS AL GRUPO (%d)', [LClientCount]);
          Sheet.Cells[Row, 1].Font.Bold := True;
          Sheet.Cells[Row, 1].Font.Color := RGB(44, 62, 80);
          Inc(Row);

          if LClientCount > 0 then
          begin
            // Cabeceras tabla clientes
            Sheet.Cells[Row, 1] := 'Código';
            Sheet.Cells[Row, 2] := 'Razón Social / Nombre Fiscal';
            Sheet.Cells[Row, 3] := 'Nombre Comercial';
            Sheet.Cells[Row, 4] := 'CIF / NIF';
            Sheet.Cells[Row, 5] := 'Teléfono';
            Sheet.Cells[Row, 6] := 'Población';
            Sheet.Cells[Row, 7] := 'Email';
            Sheet.Cells[Row, 8] := 'Estado';

            Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Font.Bold := True;
            Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Interior.Color := RGB(235, 245, 251); // Azul claro

            Inc(Row);

            while not QryC.Eof do
            begin
              Sheet.Cells[Row, 1] := QryC.FieldByName('id').AsString;
              Sheet.Cells[Row, 2] := QryC.FieldByName('nombre_fiscal').AsString;
              Sheet.Cells[Row, 3] := QryC.FieldByName('nombre_comercial').AsString;
              Sheet.Cells[Row, 4] := QryC.FieldByName('nif').AsString;
              Sheet.Cells[Row, 5] := QryC.FieldByName('telefono').AsString;
              Sheet.Cells[Row, 6] := QryC.FieldByName('poblacion').AsString;
              Sheet.Cells[Row, 7] := QryC.FieldByName('email').AsString;
              if IsFieldTrue(QryC.FieldByName('activo')) then
                Sheet.Cells[Row, 8] := 'Activo'
              else
                Sheet.Cells[Row, 8] := 'Inactivo';

              Inc(Row);
              QryC.Next;
            end;
          end
          else
          begin
            Sheet.Cells[Row, 1] := '  (Sin clientes asignados a este grupo)';
            Sheet.Cells[Row, 1].Font.Italic := True;
            Inc(Row);
          end;

          Inc(Row); // Separación

          // --- SECCIÓN 2: PRECIOS Y TARIFAS ESPECÍFICAS ---
          QryP.Close;
          QryP.SQL.Text :=
            'SELECT a.id as codigo, a.descripcion as articulo_desc, cgp.precio, ' +
            '       ROUND(cgp.precio * (1 + (COALESCE(t.tipo, 10.00) / 100)), 2) as precio_con_iva, ' +
            '       cgp.und_min, cgp.und_max, cgp.valido_desde, cgp.valido_hasta ' +
            'FROM ge_clientes_grupos_precios cgp ' +
            'INNER JOIN ge_articulos a ON cgp.id_articulo = a.id ' +
            'LEFT JOIN ge_tipos_iva t ON a.id_tipo_iva = t.id ' +
            'WHERE cgp.id_cliente_grupo = :id ' +
            'ORDER BY a.descripcion, cgp.und_min, cgp.valido_desde';
          QryP.ParamByName('id').AsString := LGrupoId;
          QryP.Open;

          LPriceCount := QryP.RecordCount;
          Sheet.Cells[Row, 1] := Format('  ► PRECIOS Y TARIFAS ESPECÍFICAS (%d)', [LPriceCount]);
          Sheet.Cells[Row, 1].Font.Bold := True;
          Sheet.Cells[Row, 1].Font.Color := RGB(44, 62, 80);
          Inc(Row);

          if LPriceCount > 0 then
          begin
            // Cabeceras tabla precios
            Sheet.Cells[Row, 1] := 'Ref. Art.';
            Sheet.Cells[Row, 2] := 'Descripción Artículo';
            Sheet.Cells[Row, 3] := 'Precio Sin IVA (€)';
            Sheet.Cells[Row, 4] := 'Precio Con IVA (€)';
            Sheet.Cells[Row, 5] := 'Ud. Mínima';
            Sheet.Cells[Row, 6] := 'Ud. Máxima';
            Sheet.Cells[Row, 7] := 'Válido Desde';
            Sheet.Cells[Row, 8] := 'Válido Hasta';

            Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Font.Bold := True;
            Sheet.Range[Sheet.Cells[Row, 1], Sheet.Cells[Row, 8]].Interior.Color := RGB(234, 250, 234); // Verde claro

            Inc(Row);

            while not QryP.Eof do
            begin
              Sheet.Cells[Row, 1] := QryP.FieldByName('codigo').AsString;
              Sheet.Cells[Row, 2] := QryP.FieldByName('articulo_desc').AsString;
              Sheet.Cells[Row, 3] := QryP.FieldByName('precio').AsFloat;
              Sheet.Cells[Row, 4] := QryP.FieldByName('precio_con_iva').AsFloat;

              if not QryP.FieldByName('und_min').IsNull then
                Sheet.Cells[Row, 5] := QryP.FieldByName('und_min').AsInteger
              else
                Sheet.Cells[Row, 5] := '';

              if not QryP.FieldByName('und_max').IsNull then
                Sheet.Cells[Row, 6] := QryP.FieldByName('und_max').AsInteger
              else
                Sheet.Cells[Row, 6] := '';

              if not QryP.FieldByName('valido_desde').IsNull then
                Sheet.Cells[Row, 7] := FormatDateTime('dd/mm/yyyy', QryP.FieldByName('valido_desde').AsDateTime)
              else
                Sheet.Cells[Row, 7] := '';

              if not QryP.FieldByName('valido_hasta').IsNull then
                Sheet.Cells[Row, 8] := FormatDateTime('dd/mm/yyyy', QryP.FieldByName('valido_hasta').AsDateTime)
              else
                Sheet.Cells[Row, 8] := '';

              // Formato numérico para precios
              Sheet.Cells[Row, 3].NumberFormat := '#,##0.00 €';
              Sheet.Cells[Row, 4].NumberFormat := '#,##0.00 €';

              Inc(Row);
              QryP.Next;
            end;
          end;

          Inc(Row, 2); // Espacio entre grupos
          FQryMain.Next;
        end;
      finally
        if FQryMain.BookmarkValid(Bookmark) then
          FQryMain.GotoBookmark(Bookmark);
        FQryMain.FreeBookmark(Bookmark);
        FQryMain.EnableControls;
      end;

      Sheet.Columns.AutoFit;
      Workbook.SaveAs(SaveDialog.FileName, 51); // xlOpenXMLWorkbook (.xlsx)
      Workbook.Close(True);

      ShowMessage('Los grupos de clientes con sus clientes asignados y tarifas se han exportado correctamente a Excel.');
    finally
      QryC.Free;
      QryP.Free;
      ExcelApp.Quit;
      ExcelApp := Unassigned;
    end;
  finally
    SaveDialog.Free;
  end;
end;

end.