You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
496 lines
14 KiB
496 lines
14 KiB
unit ufrmPesquisa;
|
|
|
|
interface
|
|
|
|
uses
|
|
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
|
|
System.Classes, Vcl.Graphics, System.DateUtils,
|
|
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, umstMaster, Data.DB, Vcl.Grids,
|
|
Vcl.DBGrids, Vcl.StdCtrls, Vcl.Buttons, Vcl.ComCtrls, Vcl.ExtCtrls,
|
|
ZAbstractRODataset, ZAbstractDataset, ZDataset, StrUtils, Vcl.Menus, ClipBrd,
|
|
Datasnap.DBClient;
|
|
|
|
type
|
|
TfrmPesquisa = class(TmstMaster)
|
|
grpb_pesquisa: TGroupBox;
|
|
edtSelecionar: TEdit;
|
|
SpeedButton1: TSpeedButton;
|
|
grpb_grid: TGroupBox;
|
|
DBGrid1: TDBGrid;
|
|
dtsDB: TDataSource;
|
|
ZQuery1: TZQuery;
|
|
ccb_campo: TComboBox;
|
|
ppmn_pesquisa: TPopupMenu;
|
|
Copiar1: TMenuItem;
|
|
edt_copytoclipboard: TEdit;
|
|
gb_hist: TGroupBox;
|
|
dtsHist: TDataSource;
|
|
dbgr_hist: TDBGrid;
|
|
procedure SpeedButton1Click(Sender: TObject);
|
|
procedure DBGrid1DblClick(Sender: TObject);
|
|
procedure FormCreate(Sender: TObject);
|
|
procedure edtSelecionarKeyPress(Sender: TObject; var Key: Char);
|
|
procedure Copiar1Click(Sender: TObject);
|
|
procedure FormClose(Sender: TObject; var Action: TCloseAction);
|
|
procedure dbgr_histDblClick(Sender: TObject);
|
|
procedure DBGrid1TitleClick(Column: TColumn);
|
|
private
|
|
procedure WMMoving(var Msg: TWMMoving); message WM_MOVING;
|
|
// procedure WMGetMinMaxInfo(var M: TWMGetMinMaxInfo);
|
|
// message WM_GETMINMAXINFO;
|
|
{ Private declarations }
|
|
public
|
|
veio_cobranca: boolean;
|
|
{ Public declarations }
|
|
end;
|
|
|
|
var
|
|
frmPesquisa: TfrmPesquisa;
|
|
|
|
implementation
|
|
|
|
{$R *.dfm}
|
|
|
|
uses udtmSystem, ucadDevedores, ufrmCobranca, System.UITypes,
|
|
ufrmEfetuarCobranca;
|
|
|
|
// aqui que as pesquisas são feitas conforme o tipo de campo que deseja ser pesquisado
|
|
|
|
procedure TfrmPesquisa.Copiar1Click(Sender: TObject);
|
|
begin
|
|
Clipboard.AsText := DBGrid1.SelectedField.Text; // copia pra clipboard
|
|
end;
|
|
|
|
procedure TfrmPesquisa.DBGrid1DblClick(Sender: TObject);
|
|
begin
|
|
if not dtmSystem.tblDevedores.Active then
|
|
begin
|
|
dtmSystem.tblDevedores.Open;
|
|
end
|
|
else
|
|
begin
|
|
dtmSystem.tblDevedores.Refresh;
|
|
end;
|
|
// if ZQuery1.FieldByName('ID_DEVEDOR')
|
|
// .AsString = dtmSystem.tblDevedoresID_DEVEDOR.AsString then
|
|
// begin
|
|
//
|
|
// end;
|
|
|
|
if dtmSystem.tblDevedores.Locate('ID_DEVEDOR',
|
|
ZQuery1.FieldByName('ID_DEVEDOR').AsString, []) then
|
|
begin
|
|
|
|
dtmSystem.cdsHist.DisableControls;
|
|
//
|
|
if dtmSystem.cdsHist.RecordCount > 10 then
|
|
begin
|
|
dtmSystem.cdsHist.First;
|
|
dtmSystem.cdsHist.Delete;
|
|
end;
|
|
//
|
|
if not(dtmSystem.cdsHist.Locate('TX_NOME', ZQuery1.FieldByName('TX_NOME')
|
|
.AsString, [loCaseInsensitive, loPartialKey])) then
|
|
begin
|
|
dtmSystem.cdsHist.Append;
|
|
dtmSystem.cdsHisttx_nome.AsString :=
|
|
ZQuery1.FieldByName('TX_NOME').AsString;
|
|
dtmSystem.cdsHistid_devedor.AsInteger := ZQuery1.FieldByName('ID_DEVEDOR')
|
|
.AsInteger;
|
|
dtmSystem.cdsHist.Post;
|
|
end;
|
|
//
|
|
dtmSystem.cdsHist.EnableControls;
|
|
with dtmSystem do
|
|
begin
|
|
if not tblEnderecos.Active then
|
|
begin
|
|
tblEnderecos.Open;
|
|
tblEnderecos.First;
|
|
end
|
|
else
|
|
begin
|
|
tblEnderecos.Refresh;
|
|
end;
|
|
if not tblTelefones.Active then
|
|
begin
|
|
tblTelefones.Open;
|
|
tblTelefones.First;
|
|
end
|
|
else
|
|
begin
|
|
tblTelefones.Refresh;
|
|
end;
|
|
if not tblEmail.Active then
|
|
begin
|
|
tblEmail.Open;
|
|
tblEmail.First;
|
|
end
|
|
else
|
|
begin
|
|
tblEmail.Refresh;
|
|
end;
|
|
if not tblTitulos.Active then
|
|
begin
|
|
tblTitulos.Open;
|
|
tblTitulos.First;
|
|
end
|
|
else
|
|
begin
|
|
tblTitulos.Refresh;
|
|
end;
|
|
if not tblHistorico.Active then
|
|
begin
|
|
tblHistorico.Open;
|
|
tblHistorico.First;
|
|
end
|
|
else
|
|
begin
|
|
tblHistorico.Refresh;
|
|
end;
|
|
end;
|
|
if veio_cobranca then
|
|
begin
|
|
frmEfetuarCobranca.achou := true;
|
|
Close;
|
|
end
|
|
else
|
|
begin
|
|
cadDevedores := TcadDevedores.Create(Self);
|
|
// cadDevedores.pesquisa := helper;
|
|
cadDevedores.ShowModal;
|
|
end;
|
|
end;
|
|
|
|
end;
|
|
|
|
procedure TfrmPesquisa.DBGrid1TitleClick(Column: TColumn);
|
|
begin
|
|
dtmSystem.OrganizaPorColuna(DBGrid1.DataSource.DataSet, Column);
|
|
end;
|
|
|
|
procedure TfrmPesquisa.dbgr_histDblClick(Sender: TObject);
|
|
var
|
|
helper: integer;
|
|
begin
|
|
if not dtmSystem.tblDevedores.Active then
|
|
begin
|
|
dtmSystem.tblDevedores.Open;
|
|
end
|
|
else
|
|
begin
|
|
dtmSystem.tblDevedores.Refresh;
|
|
end;
|
|
if dbgr_hist.DataSource.DataSet.FieldByName('id_devedor')
|
|
.AsInteger = dtmSystem.tblDevedoresID_DEVEDOR.AsInteger then
|
|
begin
|
|
dtmSystem.tblEnderecos.Refresh;
|
|
dtmSystem.tblEnderecos.First;
|
|
dtmSystem.tblTelefones.Refresh;
|
|
dtmSystem.tblTelefones.First;
|
|
dtmSystem.tblEmail.Refresh;
|
|
dtmSystem.tblEmail.First;
|
|
dtmSystem.tblTitulos.Refresh;
|
|
dtmSystem.tblTitulos.First;
|
|
dtmSystem.tblHistorico.Refresh;
|
|
dtmSystem.tblHistorico.First;
|
|
end;
|
|
if dtmSystem.tblDevedores.Locate('ID_DEVEDOR',
|
|
dbgr_hist.DataSource.DataSet.FieldByName('id_devedor').AsInteger, []) then
|
|
begin
|
|
helper := dtmSystem.tblDevedores.recno;
|
|
if veio_cobranca then
|
|
begin
|
|
frmEfetuarCobranca.achou := true;
|
|
Close;
|
|
end
|
|
else
|
|
begin
|
|
cadDevedores := TcadDevedores.Create(Self);
|
|
cadDevedores.pesquisa := helper;
|
|
cadDevedores.ShowModal;
|
|
end;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmPesquisa.edtSelecionarKeyPress(Sender: TObject; var Key: Char);
|
|
begin
|
|
inherited;
|
|
if (Key = #13) then
|
|
begin
|
|
SpeedButton1Click(Self); // enter pra pesquisar
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmPesquisa.FormClose(Sender: TObject; var Action: TCloseAction);
|
|
begin
|
|
// inherited;
|
|
// dtmSystem.tblDevedores.close;
|
|
frmPesquisa := nil;
|
|
end;
|
|
|
|
procedure TfrmPesquisa.FormCreate(Sender: TObject);
|
|
begin
|
|
inherited;
|
|
// cbb_seletor.ItemIndex := 0;
|
|
ccb_campo.ItemIndex := 0;
|
|
if not(dtmSystem.tblDevedores.Active) then
|
|
begin
|
|
dtmSystem.tblDevedores.Open;
|
|
end
|
|
else
|
|
begin
|
|
dtmSystem.tblDevedores.Refresh;
|
|
end;
|
|
DBGrid1.Enabled := false;
|
|
veio_cobranca := false;
|
|
if not(dtmSystem.cdsHist.Active) then
|
|
begin
|
|
dtmSystem.cdsHist.Open;
|
|
end;
|
|
if not(dtmSystem.tblEnderecos.Active) then
|
|
begin
|
|
dtmSystem.tblEnderecos.Open;
|
|
end;
|
|
if veio_cobranca then
|
|
begin
|
|
frmEfetuarCobranca.achou := false;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmPesquisa.SpeedButton1Click(Sender: TObject);
|
|
var
|
|
tx_pesquisa, string_query, help_cpfcnpj, tx_tel, query2: string;
|
|
i, tam1: integer;
|
|
begin
|
|
inherited;
|
|
// se vazio, sai
|
|
if edtSelecionar.Text = '' then
|
|
begin
|
|
// exit;
|
|
abort;
|
|
end;
|
|
// coloca o caracter coringa
|
|
tx_pesquisa := Ansiuppercase(stringreplace(edtSelecionar.Text, ' ', '%',
|
|
[rfReplaceAll]));
|
|
|
|
string_query := 'select ID_DEVEDOR, TX_NOME, case when TP_CLIENTE = ''F'' ' +
|
|
'then CD_CPF when TP_CLIENTE = ''J'' then CD_CNPJ end as TX_DOCUMENTO from '
|
|
+ 'CHG_DEVEDORES where ';
|
|
query2 := string_query;
|
|
Screen.Cursor := crHourglass;
|
|
case ccb_campo.ItemIndex of
|
|
0: // Nome/Razão
|
|
begin
|
|
string_query := string_query + 'upper(TX_NOME COLLATE WIN_PTBR) LIKE ' +
|
|
QuotedStr('%' + tx_pesquisa + '%');
|
|
end;
|
|
|
|
1: // Telefone sem DDD
|
|
begin
|
|
// if copy(tx_pesquisa, 1, 1) <> '(' then
|
|
// begin
|
|
// tx_pesquisa := '(' + tx_pesquisa;
|
|
// end;
|
|
// if copy(tx_pesquisa, 4, 1) <> ')' then
|
|
// begin
|
|
// tx_pesquisa := copy(tx_pesquisa, 1, 3) + ')' +
|
|
// copy(tx_pesquisa, 4, 15);
|
|
// end;
|
|
// if copy(tx_pesquisa, 9, 1) <> '-' then
|
|
// begin
|
|
// tx_pesquisa := copy(tx_pesquisa, 1, 8) + '-' +
|
|
// copy(tx_pesquisa, 9, 15);
|
|
// end;
|
|
// if (length(tx_pesquisa) < 13) or (length(tx_pesquisa) > 14) then
|
|
// begin
|
|
// MessageDlg('Número de telefone inválido.', mtWarning, [mbOK], 0);
|
|
// exit;
|
|
// end;
|
|
tx_tel := stringreplace(tx_pesquisa, '-', '',
|
|
[rfReplaceAll, rfIgnoreCase]);
|
|
if (length(tx_pesquisa) < 8) or (length(tx_pesquisa) > 9) then
|
|
begin
|
|
MessageDlg
|
|
('Número de telefone inválido. Ele deve conter ao menos 8 dígitos e no máximo 9 para que a pesquisa possa ser realizada.',
|
|
mtWarning, [mbOK], 0);
|
|
abort;
|
|
end;
|
|
if length(tx_pesquisa) = 8 then
|
|
begin
|
|
tx_pesquisa := copy(tx_pesquisa, 1, 4) + '-' +
|
|
copy(tx_pesquisa, 5, 4);
|
|
end
|
|
else
|
|
begin
|
|
tx_pesquisa := copy(tx_pesquisa, 1, 5) + '-' +
|
|
copy(tx_pesquisa, 6, 4);
|
|
end;
|
|
string_query := string_query +
|
|
'ID_DEVEDOR in (SELECT ID_DEVEDOR FROM CHG_TELEFONES WHERE NR_NUMERO = '
|
|
+ QuotedStr(tx_pesquisa) + ' or NR_NUMERO = '+ QuotedStr(tx_tel) +')';
|
|
end;
|
|
|
|
2: // CPF/CNPJ
|
|
begin
|
|
tx_pesquisa := stringreplace(tx_pesquisa, '.', '', [rfReplaceAll]);
|
|
tx_pesquisa := stringreplace(tx_pesquisa, '-', '', [rfReplaceAll]);
|
|
tx_pesquisa := stringreplace(tx_pesquisa, '/', '', [rfReplaceAll]);
|
|
if length(tx_pesquisa) < 14 then
|
|
begin
|
|
tx_pesquisa := stringreplace(tx_pesquisa, 'TX_DOCUMENTO', 'CD_CPF',
|
|
[rfReplaceAll]);
|
|
help_cpfcnpj := copy(tx_pesquisa, 1, 3) + '.' +
|
|
copy(tx_pesquisa, 4, 3) + '.' + copy(tx_pesquisa, 7, 3) + '-' +
|
|
RightStr(tx_pesquisa, 2);
|
|
string_query := string_query + 'CD_CPF like ' +
|
|
QuotedStr(help_cpfcnpj);
|
|
end
|
|
else if length(tx_pesquisa) >= 14 then
|
|
begin
|
|
tx_pesquisa := stringreplace(tx_pesquisa, 'TX_DOCUMENTO', 'CD_CNPJ',
|
|
[rfReplaceAll]);
|
|
help_cpfcnpj := copy(tx_pesquisa, 1, 2) + '.' +
|
|
copy(tx_pesquisa, 3, 3) + '.' + copy(tx_pesquisa, 6, 3) + '/' +
|
|
copy(tx_pesquisa, 9, 4) + '-' + RightStr(tx_pesquisa, 2);
|
|
string_query := string_query + 'CD_CNPJ like ' +
|
|
QuotedStr(help_cpfcnpj);
|
|
string_query := stringreplace(string_query, 'CD_CPF', 'CD_CNPJ',
|
|
[rfReplaceAll]);
|
|
end;
|
|
end;
|
|
|
|
3: // Nº do documento
|
|
begin
|
|
// select id_devedor from chg_devedores where id_devedor = (select id_devedor from chg_titulos where cd_devedor = '1')
|
|
string_query := string_query +
|
|
'ID_DEVEDOR in (select id_devedor from chg_titulos where (cd_devedor like '
|
|
+ QuotedStr('%' + tx_pesquisa + '%') + ') or (tx_produto like' +
|
|
QuotedStr('%' + tx_pesquisa + '%') + '))';
|
|
end;
|
|
4:
|
|
begin
|
|
string_query := string_query +
|
|
'id_devedor in ( select id_devedor from chg_emails where upper(TX_EMAIL) LIKE '
|
|
+ QuotedStr('%' + tx_pesquisa + '%') + ')';
|
|
end;
|
|
5:
|
|
begin
|
|
// string_query := string_query +
|
|
// 'ID_DEVEDOR in (select distinct id_devedor from chg_titulos where ((cd_devedor like '
|
|
// + QuotedStr('%' + tx_pesquisa + '%') + ') or (tx_produto like' +
|
|
// QuotedStr('%' + tx_pesquisa + '%') +
|
|
// ')) and id_empresa in (5, 7, 9, 73, 74, 242, 258))';
|
|
string_query := string_query +
|
|
'ID_DEVEDOR in (select id_devedor from chg_titulos where tx_produto containing ' +
|
|
QuotedStr(tx_pesquisa) +
|
|
' and id_empresa in (5, 7, 9, 73, 74, 242, 258))';
|
|
end;
|
|
6:
|
|
begin
|
|
string_query := string_query +
|
|
'CD_DEVEDOR LIKE ' + QuotedStr('%' + tx_pesquisa + '%')
|
|
end;
|
|
end;
|
|
string_query := string_query + ' order by tx_nome';
|
|
|
|
DBGrid1.Enabled := true;
|
|
ZQuery1.Close;
|
|
ZQuery1.SQL.Text := string_query;
|
|
|
|
//ZQuery1.ExecSQL;
|
|
|
|
ZQuery1.Open;
|
|
Application.ProcessMessages;
|
|
//ZQuery1.Active := true;
|
|
|
|
if (ZQuery1.IsEmpty)then
|
|
// se vazio, avisa que não achou nada
|
|
begin
|
|
//ZQuery1.Active := false;
|
|
ZQuery1.Close;
|
|
MessageDlg('Nenhuma entrada foi encontrada.', mtWarning, [mbOK], 0);
|
|
end;
|
|
|
|
for i := 0 to ZQuery1.FieldCount - 1 do
|
|
// arruma o tamanho das colunas conforme os dados
|
|
begin
|
|
|
|
if AnsiContainsText(DBGrid1.Columns[i].Title.Caption, 'ID_') then
|
|
begin
|
|
DBGrid1.Columns[i].Title.Caption := 'Id';
|
|
end;
|
|
|
|
if DBGrid1.Columns[i].Title.Caption = 'TX_NOME' then
|
|
begin
|
|
DBGrid1.Columns[i].Title.Caption := 'Nome';
|
|
end;
|
|
|
|
if DBGrid1.Columns[i].Title.Caption = 'TX_DOCUMENTO' then
|
|
begin
|
|
DBGrid1.Columns[i].Title.Caption := 'Nº do documento';
|
|
tam1 := 543;
|
|
end;
|
|
|
|
if DBGrid1.Columns[i].Title.Caption = 'CD_CPF' then
|
|
begin
|
|
DBGrid1.Columns[i].Title.Caption := 'CPF';
|
|
tam1 := 584;
|
|
end;
|
|
|
|
if DBGrid1.Columns[i].Title.Caption = 'CD_CNPJ' then
|
|
begin
|
|
DBGrid1.Columns[i].Title.Caption := 'CNPJ';
|
|
tam1 := 567;
|
|
end;
|
|
|
|
if DBGrid1.Columns[i].Title.Caption = 'TX_CNPJ' then
|
|
begin
|
|
DBGrid1.Columns[i].Title.Caption := 'CNPJ';
|
|
tam1 := 543;
|
|
end;
|
|
|
|
end;
|
|
|
|
for i := 0 to ZQuery1.FieldCount - 1 do
|
|
begin
|
|
if AnsiContainsText(DBGrid1.Columns[i].Title.Caption, 'Id') then
|
|
begin
|
|
DBGrid1.Columns[i].Width := 40;
|
|
end;
|
|
|
|
if DBGrid1.Columns[i].Title.Caption = 'Nome' then
|
|
begin
|
|
DBGrid1.Columns[i].Width := tam1;
|
|
end;
|
|
end;
|
|
Screen.Cursor := crDefault;
|
|
end;
|
|
|
|
procedure TfrmPesquisa.WMMoving(var Msg: TWMMoving);
|
|
var
|
|
workArea: TRect;
|
|
begin
|
|
workArea := Screen.WorkareaRect;
|
|
|
|
with Msg.DragRect^ do
|
|
begin
|
|
if Left < workArea.Left then
|
|
OffsetRect(Msg.DragRect^, workArea.Left - Left, 0);
|
|
|
|
if Top < workArea.Top then
|
|
OffsetRect(Msg.DragRect^, 0, workArea.Top - Top);
|
|
|
|
if Right > workArea.Right then
|
|
OffsetRect(Msg.DragRect^, workArea.Right - Right, 0);
|
|
|
|
if Bottom > workArea.Bottom then
|
|
OffsetRect(Msg.DragRect^, 0, workArea.Bottom - Bottom);
|
|
end;
|
|
|
|
end;
|
|
|
|
end.
|
|
|
|
|
|
|