unit Auditoria;
                                                       
interface

uses
  Windows, system.UItypes, IB_Access, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, TelaComum, ExtCtrls, StdCtrls, Buttons, ComCtrls, Mask,
  IB_Controls, IB_Components, IB_Grid, DB, IBODataset, ppCtrls,
  ppMemo, ppBands, ppClass, ppVar, 
  ppReport, ppDBPipe, ppDB, ppStrtch, ppPrnabl, ppCache, ppComm, ppRelatv,
  ppProd, Grids, ImgList, ppDesignLayer, ppParameter, IB_EditButton,
  System.ImageList;

type
  TfrmAuditoria = class(TfrmTelaComum)
    pagina: TPageControl;
    tabFiltro: TTabSheet;
    tabVisualizacao: TTabSheet;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    edtDataIncio: TIB_Date;
    edtDataFim: TIB_Date;
    edtHoraInicio: TMaskEdit;
    edtHoraFim: TMaskEdit;
    tabPesquisa: TTabSheet;
    edtComentario: TMemo;
    Label1: TLabel;
    edtChaveTabela: TLabeledEdit;
    Panel4: TPanel;
    Label2: TLabel;
    edPesquisa: TEdit;
    btnVoltar: TBitBtn;
    qrAcao: TIB_Query;
    qrTabela: TIB_Query;
    qrUsuario: TIB_Query;
    dsrAcao: TIB_DataSource;
    dsrTabela: TIB_DataSource;
    dsrUsuario: TIB_DataSource;
    btnPesquisaEmpresa: TBitBtn;
    BitBtn1: TBitBtn;
    grdPesquisa: TIB_Grid;
    dtPesquisa: TIB_DataSource;
    BitBtn2: TBitBtn;
    edtAcao: TIB_LookupCombo;
    edtusuario: TIB_LookupCombo;
    edtTabela: TIB_LookupCombo;
    qrAuditoria: TIB_Query;
    dsrAuditoria: TIB_DataSource;
    qrAuditoriaDetalhe: TIB_Query;
    dsrAuditoriaDetalhe: TIB_DataSource;
    IB_Grid1: TIB_Grid;
    Label3: TLabel;
    Label4: TLabel;
    IB_Grid2: TIB_Grid;
    IB_Memo1: TIB_Memo;
    IB_Memo2: TIB_Memo;
    IB_Memo3: TIB_Memo;
    Shape2: TShape;
    qrRelatorio: TIBOQuery;
    DataSource1: TDataSource;
    Relatorio: TppReport;
    ppHeaderBand1: TppHeaderBand;
    ppDetailBand1: TppDetailBand;
    ppFooterBand1: TppFooterBand;
    ppLabel1: TppLabel;
    ppFiltro: TppMemo;
    ppSystemVariable1: TppSystemVariable;
    ppSystemVariable2: TppSystemVariable;
    ppLine1: TppLine;
    ppGroup1: TppGroup;
    ppGroupHeaderBand1: TppGroupHeaderBand;
    ppGroupFooterBand1: TppGroupFooterBand;
    ppLabel2: TppLabel;
    ppLabel3: TppLabel;
    ppLabel4: TppLabel;
    ppLabel5: TppLabel;
    ppLabel6: TppLabel;
    ppLabel7: TppLabel;
    ppLabel8: TppLabel;
    ppDBText1: TppDBText;
    ppDBText2: TppDBText;
    ppDBText3: TppDBText;
    ppDBText4: TppDBText;
    ppDBText5: TppDBText;
    ppDBText6: TppDBText;
    ppDBMemo1: TppDBMemo;
    ppDBText7: TppDBText;
    ppLabel9: TppLabel;
    ppLabel10: TppLabel;
    ppLabel11: TppLabel;
    ppDBText9: TppDBText;
    ppShape1: TppShape;
    ppShape2: TppShape;
    ppDBPipeline1: TppDBPipeline;
    ppDBText8: TppDBText;
    ppLine3: TppLine;
    ppDBText10: TppDBText;
    ppLabel12: TppLabel;
    BitBtn3: TBitBtn;
    btnPrint: TBitBtn;
    procedure FormCreate(Sender: TObject);
    procedure Pesquisar;
    procedure PegaIndice;
    procedure btnPesquisaEmpresaClick(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure grdPesquisaDblClick(Sender: TObject);
    procedure btnVoltarClick(Sender: TObject);
    procedure edPesquisaChange(Sender: TObject);
    procedure MontaSql;
    procedure BtGravarClick(Sender: TObject);
    procedure edPesquisaKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure btnPrintClick(Sender: TObject);
    procedure BtCancelarClick(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure paginaChange(Sender: TObject);
    procedure edPesquisaEnter(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure grdPesquisaKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure dsrAuditoriaDataChange(Sender: TIB_StatementLink;
      Statement: TIB_Statement; Field: TIB_Column);

  private
    { Private declarations }
  public
    { Public declarations }
    Indice : string;
  end;

var
  frmAuditoria: TfrmAuditoria;

implementation



{$R *.dfm}

procedure TfrmAuditoria.FormCreate(Sender: TObject);
begin
  inherited;
   qrAcao.Open;
   qrTabela.Open;
   qrUsuario.Open;

   if not qrAcao.Bof then
   begin
      qrAcao.First;
      qrAcao.Prior;
   end;

   if not qrTabela.Bof then
   begin
      qrTabela.First;
      qrTabela.Prior;
   end;

   if not qrUsuario.Bof then
   begin
      qrUsuario.First;
      qrUsuario.Prior;
   end;

   pagina.ActivePage := tabFiltro;
end;

procedure TfrmAuditoria.PegaIndice;
var i : Integer;
    indiceTemp : string;
begin
   Indice := '';
   indiceTemp := grdPesquisa.DataSource.Dataset.OrderingItems.ValueFromIndex[ abs(grdPesquisa.DataSource.Dataset.OrderingItemNo) -1];
   for i := 0 to Length(indiceTemp) do
   begin
      if indiceTemp[i] = ';' then
         Break;
      Indice := Indice + indiceTemp[i];
   end;
end;

procedure TfrmAuditoria.Pesquisar;
begin
   TIB_Query( grdPesquisa.DataSource.Dataset).Filtered := false;
   TIB_Query( grdPesquisa.DataSource.Dataset).Refresh;
   PegaIndice;
   pagina.ActivePage := tabPesquisa;
   Panel3.Visible := False;
end;

procedure TfrmAuditoria.btnPesquisaEmpresaClick(Sender: TObject);
begin
  inherited;
  dtPesquisa.Dataset:= qrAcao;
  Pesquisar;
end;

procedure TfrmAuditoria.BitBtn1Click(Sender: TObject);
begin
  inherited;
   dtPesquisa.Dataset:= qrTabela;
   Pesquisar;
end;

procedure TfrmAuditoria.BitBtn2Click(Sender: TObject);
begin
  inherited;
   dtPesquisa.Dataset:= qrUsuario;
   Pesquisar;
end;

procedure TfrmAuditoria.grdPesquisaDblClick(Sender: TObject);
begin
  inherited;
   Panel3.Visible   := True;
   pagina.ActivePage:= tabFiltro;
end;

procedure TfrmAuditoria.btnVoltarClick(Sender: TObject);
begin
  inherited;
   Panel3.Visible   := True;
   pagina.ActivePage:= tabFiltro;
end;

procedure TfrmAuditoria.edPesquisaChange(Sender: TObject);
begin
  inherited;
   if edPesquisa.Text<>'' then
   begin
      TIB_Query( grdPesquisa.DataSource.Dataset).Close;
      TIB_Query( grdPesquisa.DataSource.Dataset).Filtered := False ;
      TIB_Query( grdPesquisa.DataSource.Dataset).Filter   := '';
      TIB_Query( grdPesquisa.DataSource.Dataset).Filter   := Indice + ' like ' + QuotedStr('%' + edPesquisa.Text + '%');
      TIB_Query( grdPesquisa.DataSource.Dataset).Filtered := True;
      TIB_Query( grdPesquisa.DataSource.Dataset).Open;
   end
   else
      TIB_Query( grdPesquisa.DataSource.Dataset).Filtered := false;

   if not TIB_Query( grdPesquisa.DataSource.Dataset).IsEmpty then
      TIB_Query( grdPesquisa.DataSource.Dataset).First;
end;

procedure TfrmAuditoria.MontaSql;
var
  horaFim: tdatetime;
  horaInicio: TDateTime;

  filtro : string;
begin
   ppFiltro.Lines.Clear;
   filtro := '';

   qrAuditoria.Close;
   qrAuditoria.SQL.Clear;
   qrAuditoria.SQL.Add(' SELECT AUDITORIA.* ');
   qrAuditoria.SQL.Add(' FROM AUDITORIA     ');
   qrAuditoria.SQL.Add(' WHERE 0 = 0 ');

   if edtDataIncio.Value <> 0 then
   begin
      qrAuditoria.SQL.Add(' and AUDITORIA.DATA BETWEEN ' + QuotedStr( FormatDateTime('dd.mm.yyy',edtDataIncio.Value) )+ ' and ' + QuotedStr( FormatDateTime('dd.mm.yyy',edtDataFim.Value) ) );
      filtro := filtro + ' Data entre ' + edtDataIncio.Text + ' e ' + edtdatafim.Text;
   end;

   if edtHoraInicio.Text <> '  :  ' then
   begin
      horaInicio:=  strtotime(edtHoraInicio.Text) ;
      horaFim :=  strtotime(edtHoraFim.Text) ;
      horaFim := horaFim + StrToTime('00:00:59');
      qrAuditoria.SQL.Add(' and AUDITORIA.hora BETWEEN ' + QuotedStr( FormatDateTime('hh:mm:ss', horaInicio) )+ ' and ' + QuotedStr( FormatDateTime('hh:mm:ss', horaFim)) );
      filtro := filtro + ' Hora entre ' + edtHoraInicio.Text + ' e ' + edtDataFim.Text;
   end;

   if edtusuario.Text <> '' then
   begin
      qrAuditoria.SQL.Add(' and AUDITORIA.USUARIO = ' + QuotedStr(edtusuario.Text) );
      filtro := filtro + ' Usuário: ' + edtusuario.Text;
   end;

   if edtAcao.Text <> '' then
   begin
      qrAuditoria.SQL.Add(' and AUDITORIA.acao = ' + QuotedStr(edtAcao.Text) );
      filtro := filtro  + ' Acão: ' + edtAcao.Text;
   end;

   if edtTabela.Text <> '' then
   begin
      qrAuditoria.SQL.Add(' and AUDITORIA.tabela = ' + QuotedStr(edtTabela.Text) );
      filtro := filtro  + ' Tabela: ' + edtTabela.Text;
   end;

   if edtChaveTabela.Text <> '' then
   begin
      qrAuditoria.SQL.Add(' and AUDITORIA.CHAVE_TABELA = ' + QuotedStr(edtChaveTabela.Text) );
      filtro := filtro  + ' Chave Primária: ' + edtChaveTabela.Text;
   end;

   if edtComentario.Text <> '' then
   begin
      qrAuditoria.SQL.Add(' and AUDITORIA.comentario like ' + QuotedStr( '%' + edtComentario.Text+ '%') );
      filtro := filtro + ' Comentário: ' + edtComentario.Text;
   end;

   ppFiltro.Lines.Add(filtro);
end;

procedure TfrmAuditoria.BtGravarClick(Sender: TObject);
begin
  inherited;
  MontaSql;
  qrAuditoria.Open;
  qrAuditoriaDetalhe.Open;
  pagina.ActivePage :=  tabVisualizacao;
  btnPrint.Visible := True;
end;

procedure TfrmAuditoria.edPesquisaKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key = VK_ESCAPE
  then
    begin
      if edPesquisa.Text = ''
      then  btnVoltar.Click
      else
        begin
          edPesquisa.Clear;
          edPesquisa.SetFocus;
        end;
    end
  else
  if (Key = VK_UP) or (Key = VK_Down)
  then grdPesquisa.SetFocus
  else
  if (Key = VK_RETURN)
  then grdPesquisa.onDblClick(Self);
end;

procedure TfrmAuditoria.btnPrintClick(Sender: TObject);
begin
  inherited;
   if qrAuditoria.IsEmpty then
   begin
      MessageDlg('A consulta não contém resultados', mtWarning, [mbOK], 0);
      Exit;
   end;

   qrRelatorio.Close;
   qrRelatorio.SQLWhere.Clear;
   qrRelatorio.SQLWhere.Add( qrAuditoria.SQLWhere.Text  );
   qrRelatorio.Open;

   Relatorio.Print;
end;

procedure TfrmAuditoria.BtCancelarClick(Sender: TObject);
begin
  inherited;
   Close;
end;

procedure TfrmAuditoria.BitBtn3Click(Sender: TObject);
begin
  inherited;
   pagina.ActivePage := tabFiltro;
   
   edtDataIncio.Clear;
   edtDataFim.Clear;
   edtHoraInicio.Clear;
   edtHoraFim.Clear;
   edtAcao.Clear;
   edtusuario.Clear;
   edtTabela.Clear;
   edtChaveTabela.Clear;
   edtComentario.Clear;

end;

procedure TfrmAuditoria.paginaChange(Sender: TObject);
begin
  inherited;
   if pagina.ActivePage = tabVisualizacao then
       btnPrint.Visible := True
   else
      btnPrint.Visible := False;

   if pagina.ActivePage = tabPesquisa then
   begin
      Panel3.Visible := False;
      edPesquisa.SetFocus;
   end
   else
      Panel3.Visible := True;

end;

procedure TfrmAuditoria.edPesquisaEnter(Sender: TObject);
begin
  inherited;
   frmAuditoria.KeyPreview := False;
end;

procedure TfrmAuditoria.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin

   if (not edPesquisa.Focused) and (not grdPesquisa.Focused) then
   begin
      if Key = vk_Up then
      begin
         Perform(wm_NextDlgCtl,0,0);
         Key := 0;
      end;

      if Key = vk_Down then
      begin
         Perform(wm_NextDlgCtl,0,0);
         Key := 0;
      end;
   end;

  //inherited;

end;

procedure TfrmAuditoria.grdPesquisaKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  inherited;
  if Key = VK_ESCAPE
  then edPesquisa.SetFocus
  else
  if Key = VK_RETURN
  then grdPesquisa.OnDblClick(self)
  else
  if (Key = VK_UP) or (Key = VK_Down)
  then grdPesquisa.SetFocus;

end;

procedure TfrmAuditoria.dsrAuditoriaDataChange(Sender: TIB_StatementLink;
  Statement: TIB_Statement; Field: TIB_Column);
begin
  inherited;
   qrAuditoriaDetalhe.Close;
   qrAuditoriaDetalhe.ParamByName('codAuditoria').Value := qrAuditoria.FieldByName('COD_AUDITORIA').Value;
   qrAuditoriaDetalhe.Open;
end;

end.
