unit gruposfamilias;

interface

uses
  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, FireDAC.Comp.Client, uFireDacHelper,
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs,Vcl.StdCtrls, Vcl.DBCtrls, Vcl.Mask,
  Vcl.ComCtrls, Vcl.Grids, Vcl.DBGrids, Vcl.ExtCtrls, datos,
    Data.DB,  Excel2000,
  ComObj;

type
  Tfgruposfamilias = class(TForm)
    Panel13: TPanel;
    Panel17: TPanel;
    DBGrid3: TDBGrid;
    Panel7: TPanel;
    btGcancelar: TButton;
    btGmodificar: TButton;
    btGborrar: TButton;
    btGnuevo: TButton;
    btGguardar: TButton;
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    DBGrid1: TDBGrid;
    Panel6: TPanel;
    btfcancelar: TButton;
    btFmodificar: TButton;
    btFborrar: TButton;
    btFnuevo: TButton;
    btFguardar: TButton;
    Panel8: TPanel;
    Panel9: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    DBGrid2: TDBGrid;
    dsgrupos: TDataSource;
    qgrupos: TFDQuery;
    qgruposidempresa: TIntegerField;
    qgruposid: TIntegerField;
    qgruposdescripcion: TWideStringField;
    qgruposactivo: TIntegerField;
    qfamilias: TFDQuery;
    dsfamilias: TDataSource;
    qarticulos: TFDQuery;
    dsarticulos: TDataSource;
    qfamiliasid: TIntegerField;
    qfamiliasdescripcion: TWideStringField;
    qfamiliasactivo: TIntegerField;
    qfamiliasCOMUN: TIntegerField;
    qfamiliasABONOS: TIntegerField;
    qfamiliasFITOSANITARIOS: TIntegerField;
    qfamiliasSUMINISTROS: TIntegerField;
    qfamiliasGENERADORES: TIntegerField;
    qarticulosid: TIntegerField;
    qarticuloscodigo: TWideStringField;
    qarticulosdescripcion: TWideStringField;
    qarticulospreciocoste: TFloatField;
    qarticulosstock: TFloatField;
    qarticulosult_preciocompra: TFloatField;
    qarticulosactivo: TIntegerField;
    qarticulosnombre_corto: TWideStringField;
    qfamiliasidempresa: TIntegerField;
    qfamiliasgrupo: TIntegerField;
    qarticulosidempresa: TIntegerField;
    qarticulosfamilia: TIntegerField;
    gcrpagado: TDBCheckBox;
    qaux: TFDQuery;
    btJexcel: TButton;
    btJimprimir: TButton;
    SaveDialog1: TSaveDialog;
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure FormKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure FormShow(Sender: TObject);
    procedure dsgruposStateChange(Sender: TObject);
    procedure dsfamiliasStateChange(Sender: TObject);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure FormCreate(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure DBGrid3DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid3CellClick(Column: TColumn);
    procedure DBGrid2DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure btGnuevoClick(Sender: TObject);
    procedure btGguardarClick(Sender: TObject);
    procedure btGcancelarClick(Sender: TObject);
    procedure btGmodificarClick(Sender: TObject);
    procedure btGborrarClick(Sender: TObject);
    procedure btFborrarClick(Sender: TObject);
    procedure btFmodificarClick(Sender: TObject);
    procedure btFguardarClick(Sender: TObject);
    procedure btfcancelarClick(Sender: TObject);
    procedure btFnuevoClick(Sender: TObject);
    procedure btJexcelClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure AppMessage(var Msg: Tmsg; var Handled:Boolean);
    procedure ExportaraExcel(FileNameXLS, SheetName: String; DBGrid: TDBGrid; ProgressBarXls: TProgressBar = nil);

  end;

var
  fgruposfamilias: Tfgruposfamilias;

implementation

{$R *.dfm}
procedure Tfgruposfamilias.AppMessage(var Msg: TMSg; Var Handled:Boolean);
 var
  actual : TWincontrol;
 begin
 if Msg.message = WM_KEYDOWN then
    begin

    // esto es para controlar que con la flecha abajo se desplegen las
    // listas

    if Msg.wParam = VK_DOWN then
      begin
      Actual := Screen.ActiveControl;
      if Actual is TDBLookupComboBox then
            if not TDBLookupComboBox(Screen.ActiveForm.ActiveControl).listVisible then
               TDBLookupComboBox(Screen.ActiveForm.ActiveControl).DropDown;

      // si utilizas las RX
   {   if Actual is TRxDBLookupCombo then
            if not TRxDBLookupCombo(Screen.ActiveForm.ActiveControl).listVisible then
               TRxDBLookupCombo(Screen.ActiveForm.ActiveControl).DropDown;

    }
      if Actual is TComboBox then
         SendMessage(TComboBox(Screen.ActiveForm.ActiveControl).Handle,CB_SHOWDROPDOWN,-1,0);

      if (Actual is TCustomEdit) then
          Msg.wParam := VK_TAB;
      // se pueden aadir mas controles....

      end;

    // y aqui es el control del intro.
    if Msg.wParam = VK_RETURN THEN
       begin
       Actual := Screen.ActiveControl;
       // para los edit.
       if (Actual is TCustomEdit) and
          not (Actual is TCustomMemo) then
          Msg.wParam := VK_TAB;

       // para los memo. Con ctrl + enter se salta de linea dentro del
       // memo, si solo es intro salta al siguiente control
       if (Actual is TCustomMemo) and
          (HiWord(GetKeyState(VK_CONTROL)) = 0)then
          Msg.wParam := VK_TAB;

       // para los lookup
       if Actual is TDBLookupComboBox then
          if not TDBLookupComboBox(Actual).listVisible then
             Msg.wParam := VK_TAB;

       // Si utilizas las RX
{       if Actual is TRxDBLookupCombo then
          if not TRxDBLookupCombo(Actual).listVisible then
             Msg.wParam := VK_TAB;
 }
       // Los combobox
       if Actual is TCustomComboBox then
          if not TCustomComboBox(Actual).DroppedDown then
             Msg.wParam := VK_TAB;

       // el radiobutton
       if Actual is TRadioButton then
          Msg.wParam := VK_TAB;

       // en el grid
       if Actual is TDBGrid then
          if (TDBGrid(Actual).ReadOnly) or
             (HiWord(GetKeyState(VK_CONTROL)) = 1)then
             Msg.wParam := VK_TAB;

       // o esto otro Con Ctrl + intro siguiente celda con
       // ctrl + shift + intro celda anterior
       // es complicado pero es por no liar con el intro solo
       // que siempre pasa de un control a otro.
       if Actual is TDBGrid then
          begin
          if (TDBGrid(Actual).ReadOnly) or
             (HiWord(GetKeyState(VK_CONTROL)) = 0) then
             Msg.wParam := VK_TAB
          else
             if not(HiWord(GetKeyState(VK_SHIFT)) = 0) then
                begin
                if TDBGrid(Actual).selectedindex > 0 then             { increment the field }
                   TDBGrid(Actual).selectedindex := TDBGrid(Actual).selectedindex -1
                else
                   TDBGrid(Actual).selectedindex := TDBGrid(Actual).fieldcount -1;
                end
             else
                begin
                if TDBGrid(Actual).selectedindex < (TDBGrid(Actual).fieldcount -1) then             { increment the field }
                   TDBGrid(Actual).selectedindex := TDBGrid(Actual).selectedindex +1
                else
                   TDBGrid(Actual).selectedindex := 0;
                end;
          end;


       // ListBox
       if Actual is TCustomListBox then
          Msg.wParam := VK_TAB;

       // TabControl
       if Actual is TCustomTabControl then
          Msg.wParam := VK_TAB;

       // Aqui se pondran todos los controles que usamos en la
       // aplicacin y necesitamos cambiar el tab por intro.
       // de esta forma te despreocupas de cuantos aades y es ms
       // limpio el cdigo

       end;
    end;
 end;


procedure Tfgruposfamilias.btFborrarClick(Sender: TObject);
begin
   qfamilias.delete;
end;

procedure Tfgruposfamilias.btfcancelarClick(Sender: TObject);
begin
   qfamilias.Cancel;
   dbgrid1.ReadOnly:=true;
end;

procedure Tfgruposfamilias.btFguardarClick(Sender: TObject);
begin
    qfamilias.Post;
    dbgrid1.ReadOnly:=true;
end;

procedure Tfgruposfamilias.btFmodificarClick(Sender: TObject);
begin
   dbgrid1.ReadOnly:=false;
   qfamilias.edit;
end;

procedure Tfgruposfamilias.btFnuevoClick(Sender: TObject);
begin
   dbgrid1.ReadOnly:=false;
   qaux.sql.clear;
   qaux.SQL.Add('select max(id)+1 from familias');
   qaux.Open;
   qfamilias.append;
   dbgrid1.setfocus;
   qfamiliasid.Value:=qaux.Fields[0].AsInteger;
end;

procedure Tfgruposfamilias.btGborrarClick(Sender: TObject);
begin
   qgrupos.delete;
end;

procedure Tfgruposfamilias.btGcancelarClick(Sender: TObject);
begin
   qgrupos.Cancel;
   dbgrid3.ReadOnly:=true;
end;

procedure Tfgruposfamilias.btGguardarClick(Sender: TObject);
begin
   qgrupos.post;
   dbgrid3.ReadOnly:=true;
end;

procedure Tfgruposfamilias.btGmodificarClick(Sender: TObject);
begin
   dbgrid3.ReadOnly:=false;
   qgrupos.edit;
end;

procedure Tfgruposfamilias.btGnuevoClick(Sender: TObject);
begin
   dbgrid3.ReadOnly:=false;
   qaux.sql.clear;
   qaux.SQL.Add('select max(id)+1 from grupos');
   qaux.Open;
   qgrupos.append;
   dbgrid3.setfocus;
   qgruposid.Value:=qaux.Fields[0].AsInteger;
end;

procedure Tfgruposfamilias.btJexcelClick(Sender: TObject);
var  archivo:string;
begin
    savedialog1.FileName:='ARTICULOS.xls';
    savedialog1.Execute();
    archivo:=savedialog1.FileName;

    try
    Exportaraexcel(archivo,'ARTICULOS',DBGRID2);
    finally
      showmessage('Los datos han sido exportados correctamente a '+archivo);
    end;
end;

procedure Tfgruposfamilias.DBGrid1CellClick(Column: TColumn);
begin
    if column.Field.FieldName ='COMUN' then
    begin
        if qfamiliascomun.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasCOMUN.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasCOMUN.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    if column.Field.FieldName ='ABONOS' then
    begin
        if qfamiliasABONOS.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasABONOS.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasABONOS.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    if column.Field.FieldName ='FITOSANITARIOS' then
    begin
        if qfamiliasFITOSANITARIOS.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasFITOSANITARIOS.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasFITOSANITARIOS.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    if column.Field.FieldName ='SUMINISTROS' then
    begin
        if qfamiliasSUMINISTROS.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasSUMINISTROS.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasSUMINISTROS.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    if column.Field.FieldName ='GENERADORES' then
    begin
        if qfamiliasGENERADORES.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasGENERADORES.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasGENERADORES.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    {if column.Field.FieldName ='comisiones' then
    begin
        if qfamiliascomisiones.asinteger=1 then
         begin
           qfamilias.Edit;
           //qfamiliascomisiones.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 //qfamiliascomisiones.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    if column.Field.FieldName ='transporte' then
    begin
        if qfamiliastransporte.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliastransporte.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliastransporte.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;
    if column.Field.FieldName ='otrosgastos' then
    begin
        if qfamiliasotrosgastos.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasotrosgastos.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasotrosgastos.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end; }
    if column.Field.FieldName ='activo' then
    begin
        if qfamiliasactivo.asinteger=1 then
         begin
           qfamilias.Edit;
           qfamiliasactivo.Value:=0;
           qfamilias.Post;
           dbgrid1.selectedIndex:=1;
         end else
               begin
                 qfamilias.Edit;
                 qfamiliasactivo.Value:=1;
                 qfamilias.Post;
                 qfamilias.Refresh;
                 dbgrid1.SelectedIndex:=1;
               end;
    end;

end;

procedure Tfgruposfamilias.DBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
const IsChecked : array[Boolean] of Integer =
 (DFCS_BUTTONCHECK, DFCS_BUTTONCHECK or DFCS_CHECKED);
var
  DrawState: Integer;
  DrawRect: TRect;
begin
if (gdFocused in State) then
begin

    if (Column.Field.FieldName = 'COMUN') then
     begin
       gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
       gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
       gcrpagado.Width := Rect.Right - Rect.Left;
       gcrpagado.Height := Rect.Bottom - Rect.Top;
       gcrpagado.Visible := True;
     end;

    if (Column.Field.FieldName = 'ABONOS') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'FITOSANITARIOS') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'SUMINISTROS') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'GENERADORES') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'comisiones') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'transporte') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'otrosgastos') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;
    if (Column.Field.FieldName = 'activo') then
    begin
      gcrpagado.Left := Rect.Left + DBgrid1.Left + 2;
      gcrpagado.Top := Rect.Top + DBgrid1.top + 2;
      gcrpagado.Width := Rect.Right - Rect.Left;
      gcrpagado.Height := Rect.Bottom - Rect.Top;
      gcrpagado.Visible := True;
    end;

end else
     begin

       if (Column.Field.FieldName = 'COMUN') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;

       if (Column.Field.FieldName = 'ABONOS') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
       if (Column.Field.FieldName = 'FITOSANITARIOS') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
       if (Column.Field.FieldName = 'SUMINISTROS') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
       if (Column.Field.FieldName = 'GENERADORES') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
       if (Column.Field.FieldName = 'activo') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid1.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid1.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;

     end;
end;

procedure Tfgruposfamilias.DBGrid2DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
const IsChecked : array[Boolean] of Integer =
 (DFCS_BUTTONCHECK, DFCS_BUTTONCHECK or DFCS_CHECKED);
var
  DrawState: Integer;
  DrawRect: TRect;
begin
if (gdFocused in State) then
begin

    if (Column.Field.FieldName = 'activo') then
     begin
       gcrpagado.Left := Rect.Left + DBgrid2.Left + 2;
       gcrpagado.Top := Rect.Top + DBgrid2.top + 2;
       gcrpagado.Width := Rect.Right - Rect.Left;
       gcrpagado.Height := Rect.Bottom - Rect.Top;
       gcrpagado.Visible := True;
     end;
end else
     begin

       if (Column.Field.FieldName = 'activo') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid2.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid2.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
    end;
end;

procedure Tfgruposfamilias.DBGrid3CellClick(Column: TColumn);
begin
    if column.Field.FieldName ='activo' then
    begin
        if qgruposactivo.asinteger=1 then
         begin
           qgrupos.Edit;
           qgruposactivo.Value:=0;
           qgrupos.Post;
           dbgrid3.selectedIndex:=1;
         end else
               begin
                 qgrupos.Edit;
                 qgruposactivo.Value:=1;
                 qgrupos.Post;
                 qgrupos.Refresh;
                 dbgrid3.SelectedIndex:=1;
               end;
    end;
end;

procedure Tfgruposfamilias.DBGrid3DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
const IsChecked : array[Boolean] of Integer =
 (DFCS_BUTTONCHECK, DFCS_BUTTONCHECK or DFCS_CHECKED);
var
  DrawState: Integer;
  DrawRect: TRect;
begin
if (gdFocused in State) then
begin

    if (Column.Field.FieldName = 'activo') then
     begin
       gcrpagado.Left := Rect.Left + DBgrid3.Left + 2;
       gcrpagado.Top := Rect.Top + DBgrid3.top + 2;
       gcrpagado.Width := Rect.Right - Rect.Left;
       gcrpagado.Height := Rect.Bottom - Rect.Top;
       gcrpagado.Visible := True;
     end;
end else
     begin

       if (Column.Field.FieldName = 'activo') then
       begin
         DrawRect:=Rect;
         InflateRect(DrawRect,-1,-1);
         if column.Field.Asinteger=0  then DrawState := ischecked[false];
         if column.Field.Asinteger=1  then DrawState := ischecked[true];
      //   DrawState := ISChecked[Column.Field.Asboolean];
         DBgrid3.Canvas.FillRect(Rect);
         DrawFrameControl(DBgrid3.Canvas.Handle, DrawRect,
         DFC_BUTTON, DrawState);
       end;
    end;
       gcrpagado.Visible := false;
end;

procedure Tfgruposfamilias.dsfamiliasStateChange(Sender: TObject);
begin
if qfamilias.State in [dsedit,dsinsert] then
begin
    btfnuevo.Enabled:=false;
    btfmodificar.Enabled:=false;
    btfborrar.Enabled:=false;
    btfcancelar.Enabled:=true;
    btfguardar.Enabled:=true;
end else
begin
    btfnuevo.Enabled:=true;
    btfmodificar.Enabled:=true;
    btfborrar.Enabled:=true;
    btfcancelar.Enabled:=false;
    btfguardar.Enabled:=false;
end;
end;

procedure Tfgruposfamilias.dsgruposStateChange(Sender: TObject);
begin
if qgrupos.State in [dsedit,dsinsert] then
begin
    btGnuevo.Enabled:=false;
    btgmodificar.Enabled:=false;
    btgborrar.Enabled:=false;
    btgcancelar.Enabled:=true;
    btgguardar.Enabled:=true;
    btfnuevo.Enabled:=false;
    btfmodificar.Enabled:=false;
    btfborrar.Enabled:=false;
    btfcancelar.Enabled:=false;
    btfguardar.Enabled:=false;
end else
begin
    btgnuevo.Enabled:=true;
    btgmodificar.Enabled:=true;
    btgborrar.Enabled:=true;
    btgcancelar.Enabled:=false;
    btgguardar.Enabled:=false;
    btfnuevo.Enabled:=true;
    btfmodificar.Enabled:=true;
    btfborrar.Enabled:=true;
    btfcancelar.Enabled:=false;
    btfguardar.Enabled:=false;
end;
end;

procedure Tfgruposfamilias.FormCreate(Sender: TObject);
var
  I: Integer;
begin
    Application.OnMessage :=  AppMessage;
   gcrpagado.DataField := 'pagado';
   gcrpagado.Visible := False;
   gcrpagado.SendToBack;
   gcrpagado.Color := DBGrid1.Color;
   gcrpagado.Caption := '';
   gcrpagado.ValueChecked := '0';
   gcrpagado.ValueUnChecked := '1';



end;

procedure Tfgruposfamilias.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
    if (activecontrol is tdbgrid) then
    begin
        if Key = 13 then Key :=9;

    end;
end;

procedure Tfgruposfamilias.FormKeyPress(Sender: TObject; var Key: Char);
begin
   key:=upcase(key);
    if (key='.') and (activecontrol is tdbedit) then
    begin
       if  (activecontrol as tdbedit).Field.DataType in [ftfloat,ftCurrency,ftbcd,ftfmtbcd] then key:=','
    end;
    if (key='.') and (activecontrol is tdbgrid) then
    begin
        key:=','
    end;
    if (activecontrol is tdbgrid) then
    begin
        if Key = #13 then Key :=#9;
    end;

    if (key=#13) then
    begin
        Perform(WM_NextDLGCtl,0,0);
        key:=#0;
    end;
end;

procedure Tfgruposfamilias.FormShow(Sender: TObject);
begin
   qgrupos.Close;
   qfamilias.Close;
   qarticulos.Close;
   qgrupos.open;
   qfamilias.open;
   qarticulos.open;

end;
procedure Tfgruposfamilias.ExportaraExcel(FileNameXLS, SheetName: String; DBGrid: TDBGrid; ProgressBarXls: TProgressBar);

  procedure ProgressBarInit;
  begin
    ProgressBarXls.Max := DBGrid.DataSource.DataSet.RecordCount;
    ProgressBarXls.Position := 0;
    ProgressBarXls.Visible := True;
  end;

const
  xlWBATworksheet = -4167;
var
  Excel, WorkBook, WorkSheet: OleVariant;
  I, J: Integer;
  PBookmark: TBookmark;
begin
  // Guardar la posicin en la DB y desactivar que se mueva el registro
  PBookmark := DBGrid.DataSource.DataSet.GetBookmark;
  DBGrid.DataSource.DataSet.DisableControls;
  DBGrid.DataSource.DataSet.First;

  // Comprobar si existe el component TProgressBar
  if (ProgressBarXls <> nil) then
    ProgressBarInit;

  // Crear instancia de la aplicacin Excel
  Excel := CreateOleObject('Excel.Application');

  // Evitar que nos pregunte si deseamos sobreescribir el archivo
  Excel.DisplayAlerts := False;

  // Agregar libro de trabajo
  WorkBook := Excel.Workbooks.Add(xlWBATWorksheet);

  // Tomar una referencia a la hoja creada
  WorkSheet := WorkBook.WorkSheets[1];

  // Se  describe el nombre de la hoja
  WorkSheet.Name := SheetName;

  // Llenamos las celdas
  //    Toma en cuenta que las columnas y filas empiezan en 1, y que en el
  // WorkSheet.Cells[I, J], I es la fila y J es la columna.

  //   Extrae los nombres de los campos del DBGrid y los coloca en el primer registro,
  // excluyendo los campos que estn ocultos
  for J := 0 to DBGrid.FieldCount -1 do
    if DBGrid.Fields[J].Visible then
    begin
      // Poner la fuente de letra en Negrita
      WorkSheet.Cells[1, J +1].Font.Bold := True;
      // Centrar la celda
//      WorkSheet.Cells[1, J +1].HorizontalAlignment := xlCenter;
      // Asignamos Valor del titulo del DBrid
      WorkSheet.Cells[1, J +1] := DBGrid.Columns[J].Title.Caption;
    end;

  // Guardar los registros
  for I := 0 to DBGrid.DataSource.DataSet.RecordCount -1 do
  begin
    // Identificamos los campos y lo grabamos segn su tipo
    for J := 0 to DBGrid.FieldCount -1 do
      case DBGrid.Fields[J].DataType of
        ftAutoInc, ftBytes, ftInteger, ftSmallint, ftWord: // Auto o Numrico
          WorkSheet.Cells[I +2, J +1] := DBGrid.Fields[J].AsInteger;
        ftBCD, ftFloat, ftCurrency: // Numrico con decimales
          WorkSheet.Cells[I +2, J +1] := DBGrid.Fields[J].AsFloat;
        ftDateTime, ftDate, ftTime: // Fecha y Hora
          WorkSheet.Cells[I +2, J +1] := DBGrid.Fields[J].AsDateTime;
        else // Todo lo demas caracteres
          WorkSheet.Cells[I +2, J +1] := DBGrid.Fields[J].AsString;
      end;

    // Saltamos de registro
    DBGrid.DataSource.DataSet.Next;

    // Comprobar si existe el component TProgressBar
    if (ProgressBarXls <> nil) then
      ProgressBarXls.Position := ProgressBarXls.Position +1;
  end;

  // Comprobar si existe el component TProgressBar
  if (ProgressBarXls <> nil) then
    ProgressBarXls.Visible := False;

  // Redimensionar todas las celdas para que esten segn su tamao
  WorkSheet.Cells.Columns.AutoFit;

  // Guardar el archivo
  WorkBook.SaveAs(FileNameXLS);

  // Cierra el archivo
  WorkBook.Close(FileNameXLS);

  // Salir de Excel
  Excel.Quit;

  // posicionar el registro donde estaba
  DBGrid.DataSource.DataSet.GotoBookmark(PBookmark);
  DBGrid.DataSource.DataSet.FreeBookmark(PBookmark);
  DBGrid.DataSource.DataSet.EnableControls;
end;

end.