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.
412 lines
11 KiB
412 lines
11 KiB
unit ufrmRankings;
|
|
|
|
interface
|
|
|
|
uses
|
|
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
|
|
System.Classes, Vcl.Graphics, System.Math,
|
|
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, umstMaster, Vcl.ComCtrls,
|
|
Data.DB, ZAbstractRODataset, ZDataset, Vcl.StdCtrls, Vcl.ExtCtrls,
|
|
Vcl.Grids, Vcl.DBGrids, Vcl.CheckLst;
|
|
|
|
type
|
|
TfrmRanking = class(TmstMaster)
|
|
dtsHistorico: TDataSource;
|
|
zroqryRanking: TZReadOnlyQuery;
|
|
zroqryRankingID_COBRADOR: TIntegerField;
|
|
zroqryRankingQTDE_ACIONAMENTOS: TIntegerField;
|
|
zroqryRankingNOME: TWideStringField;
|
|
dbgrd_ranking: TDBGrid;
|
|
rg_relacoes: TRadioGroup;
|
|
cb_filtragrupo: TCheckBox;
|
|
clb_grupousuarios: TCheckListBox;
|
|
btn_gerarank: TButton;
|
|
Label1: TLabel;
|
|
edt_locate: TEdit;
|
|
cb_filtroqtde: TCheckBox;
|
|
rg_filtroqtde: TRadioGroup;
|
|
edt_v1: TEdit;
|
|
edt_v2: TEdit;
|
|
lbl_vcorte: TLabel;
|
|
edt_vcorte: TEdit;
|
|
gb_data: TGroupBox;
|
|
Label2: TLabel;
|
|
dtp_inicio: TDateTimePicker;
|
|
dtp_fim: TDateTimePicker;
|
|
cb_periodo: TCheckBox;
|
|
clb_providencias: TCheckListBox;
|
|
cb_prov: TCheckBox;
|
|
procedure FormCreate(Sender: TObject);
|
|
procedure btn_gerarankClick(Sender: TObject);
|
|
procedure edt_locateChange(Sender: TObject);
|
|
procedure cb_filtroqtdeClick(Sender: TObject);
|
|
procedure rg_filtroqtdeClick(Sender: TObject);
|
|
procedure edt_v1Change(Sender: TObject);
|
|
procedure edt_v2Change(Sender: TObject);
|
|
procedure dbgrd_rankingDrawColumnCell(Sender: TObject; const Rect: TRect;
|
|
DataCol: Integer; Column: TColumn; State: TGridDrawState);
|
|
procedure dbgrd_rankingTitleClick(Column: TColumn);
|
|
procedure edt_vcorteChange(Sender: TObject);
|
|
procedure cb_periodoClick(Sender: TObject);
|
|
private
|
|
{ Private declarations }
|
|
function ChecaMarcados(clb: TCheckListBox): Boolean;
|
|
procedure AdjustCheckListBox;
|
|
public
|
|
{ Public declarations }
|
|
end;
|
|
|
|
var
|
|
frmRanking: TfrmRanking;
|
|
|
|
implementation
|
|
|
|
{$R *.dfm}
|
|
|
|
uses udtmSystem;
|
|
|
|
procedure TfrmRanking.AdjustCheckListBox;
|
|
var
|
|
i, nMaxWidth, nItemWidth: Integer;
|
|
begin
|
|
// ajusta o checklistbox pra ter scroll horizontal
|
|
nMaxWidth := clb_providencias.ClientWidth;
|
|
for i := 0 to clb_providencias.Items.Count - 1 do
|
|
begin
|
|
nItemWidth := clb_providencias.Canvas.TextWidth
|
|
(clb_providencias.Items[i]) + 20;
|
|
if (nItemWidth > nMaxWidth) then
|
|
nMaxWidth := nItemWidth;
|
|
end;
|
|
if (nMaxWidth > clb_providencias.ClientWidth) then
|
|
begin
|
|
clb_providencias.ScrollWidth := nMaxWidth;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmRanking.btn_gerarankClick(Sender: TObject);
|
|
var
|
|
str_help, str_prov: string;
|
|
i: Integer;
|
|
begin
|
|
if rg_relacoes.ItemIndex < 0 then
|
|
begin
|
|
Application.MessageBox
|
|
('Por favor selecione um tipo de Ranking antes de tentar visualizar algo.',
|
|
'Falta de parâmetros', MB_OK);
|
|
abort;
|
|
end
|
|
else
|
|
begin
|
|
if cb_filtragrupo.Checked and not ChecaMarcados(clb_grupousuarios) then
|
|
begin
|
|
Application.MessageBox
|
|
('Por favor selecione um grupo para o filtro de grupos antes de tentar visualizar algo.',
|
|
'Falta de parâmetros', MB_OK);
|
|
abort;
|
|
end;
|
|
Screen.Cursor := crHourGlass;
|
|
with zroqryRanking.SQL do
|
|
begin
|
|
Clear;
|
|
Add('select');
|
|
Add('h.id_cobrador,');
|
|
Add('case when u.tx_nome is not NULL then u.tx_nome when u.tx_nome is NULL then '
|
|
+ QuotedStr('Usuário Inexistente') + ' end as NOME,');
|
|
case rg_relacoes.ItemIndex of
|
|
0: // Devedores distintos acionados
|
|
begin
|
|
Add('COUNT (DISTINCT h.ID_DEVEDOR) as qtde_acionamentos');
|
|
end;
|
|
1: // Devedores totais (repetidos) acionados
|
|
begin
|
|
Add('COUNT (h.ID_DEVEDOR) as qtde_acionamentos');
|
|
end;
|
|
2, 3: // Providências distintas positivas & negativas
|
|
begin
|
|
Add('COUNT (DISTINCT h.ID_PROVIDENCIA) as qtde_acionamentos');
|
|
end;
|
|
4, 5: // Providências totais (repetidas) positivas & negativas
|
|
begin
|
|
Add('COUNT (h.ID_PROVIDENCIA) as qtde_acionamentos');
|
|
end;
|
|
end;
|
|
Add('from chg_historico h');
|
|
Add('left join sys_usuarios u on h.id_cobrador = u.id_usuario');
|
|
Add('where h.id_cobrador in (select id_usuario from sys_usuarios where tp_ativo = '
|
|
+ QuotedStr('S') + ')');
|
|
case rg_relacoes.ItemIndex of
|
|
2, 4:
|
|
begin
|
|
Add('and h.id_providencia in (select p.id_providencia from chg_providencias p where p.tp_providencia = '
|
|
+ QuotedStr('Positiva') + ')');
|
|
end;
|
|
3, 5:
|
|
begin
|
|
Add('and h.id_providencia in (select p.id_providencia from chg_providencias p where p.tp_providencia = '
|
|
+ QuotedStr('Negativa') + ')');
|
|
end;
|
|
end;
|
|
if cb_filtragrupo.Checked then
|
|
begin
|
|
str_help := '';
|
|
for i := 0 to clb_grupousuarios.Items.Count - 1 do
|
|
begin
|
|
if clb_grupousuarios.Checked[i] then
|
|
begin
|
|
dtmSystem.tblGrupos.Locate('tx_nome', clb_grupousuarios.Items[i], [loCaseInsensitive]);
|
|
str_help := str_help + dtmSystem.tblGruposID_GRUPO.AsString + ',';
|
|
end;
|
|
end;
|
|
SetLength(str_help, length(str_help) - 1);
|
|
Add('and h.id_cobrador in (select id_usuario from sys_usuarios where id_grupo in ('
|
|
+ str_help + '))');
|
|
end;
|
|
if cb_periodo.Checked then
|
|
begin
|
|
Add('and CAST(h.dt_contato AS DATE) >= ' +
|
|
QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)) + ' and');
|
|
Add('CAST(h.dt_contato AS DATE) <= ' +
|
|
QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)));
|
|
end;
|
|
if cb_prov.Checked then
|
|
begin
|
|
str_prov := '';
|
|
for i := 0 to clb_providencias.Items.Count - 1 do
|
|
begin
|
|
if clb_providencias.Checked[i] then
|
|
begin
|
|
dtmSystem.tblProvidencias.Locate('tx_nome', clb_providencias.Items[i], [loCaseInsensitive]);
|
|
str_prov := str_prov + dtmSystem.tblProvidenciasID_PROVIDENCIA.
|
|
AsString + ',';
|
|
end;
|
|
end;
|
|
SetLength(str_prov, length(str_prov) - 1);
|
|
Add('and h.id_providencia in (' + str_prov + ')');
|
|
end;
|
|
Add('group by h.id_cobrador, u.tx_nome');
|
|
Add('order by NOME, qtde_acionamentos desc');
|
|
end;
|
|
zroqryRanking.Open;
|
|
Screen.Cursor := crDefault;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmRanking.cb_filtroqtdeClick(Sender: TObject);
|
|
begin
|
|
rg_filtroqtde.Enabled := cb_filtroqtde.Checked;
|
|
if cb_filtroqtde.Checked = false then
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
edt_v1.Enabled := false;
|
|
edt_v2.Enabled := false;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmRanking.cb_periodoClick(Sender: TObject);
|
|
begin
|
|
gb_data.Enabled := cb_periodo.Checked;
|
|
end;
|
|
|
|
function TfrmRanking.ChecaMarcados(clb: TCheckListBox): Boolean;
|
|
var
|
|
i: Integer;
|
|
res: Boolean;
|
|
begin
|
|
res := false;
|
|
for i := 0 to clb.Items.Count - 1 do
|
|
begin
|
|
if clb.Checked[i] then
|
|
begin
|
|
res := true;
|
|
break;
|
|
end;
|
|
end;
|
|
Result := res;
|
|
end;
|
|
|
|
procedure TfrmRanking.dbgrd_rankingDrawColumnCell(Sender: TObject;
|
|
const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
|
|
begin
|
|
if zroqryRankingQTDE_ACIONAMENTOS.AsInteger <=
|
|
StrToInt64Def(edt_vcorte.Text, 0) then
|
|
begin
|
|
dbgrd_ranking.Canvas.Brush.Color := clRed;
|
|
dbgrd_ranking.DefaultDrawColumnCell(Rect, DataCol, Column, State);
|
|
end;
|
|
if (gdSelected in State) then
|
|
begin
|
|
dbgrd_ranking.Canvas.Brush.Color := clGray;
|
|
dbgrd_ranking.DefaultDrawColumnCell(Rect, DataCol, Column, State);
|
|
end;
|
|
|
|
end;
|
|
|
|
procedure TfrmRanking.dbgrd_rankingTitleClick(Column: TColumn);
|
|
begin
|
|
dtmSystem.OrganizaPorColuna(zroqryRanking, Column);
|
|
end;
|
|
|
|
procedure TfrmRanking.edt_locateChange(Sender: TObject);
|
|
begin
|
|
if zroqryRanking.Active then
|
|
begin
|
|
zroqryRanking.Locate('NOME', edt_locate.Text,
|
|
[loPartialKey, loCaseInsensitive]);
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmRanking.edt_v1Change(Sender: TObject);
|
|
begin
|
|
case rg_filtroqtde.ItemIndex of
|
|
0:
|
|
begin
|
|
if edt_v1.Text <> '' then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS >= ' + edt_v1.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
1:
|
|
begin
|
|
if edt_v1.Text <> '' then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS <= ' + edt_v1.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
2:
|
|
begin
|
|
if (edt_v1.Text <> '') and (edt_v2.Text <> '') then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS >= ' + edt_v1.Text +
|
|
' and QTDE_ACIONAMENTOS <= ' + edt_v2.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmRanking.edt_v2Change(Sender: TObject);
|
|
begin
|
|
if (edt_v1.Text <> '') and (edt_v2.Text <> '') then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS >= ' + edt_v1.Text +
|
|
' and QTDE_ACIONAMENTOS <= ' + edt_v2.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmRanking.edt_vcorteChange(Sender: TObject);
|
|
begin
|
|
dbgrd_ranking.Repaint;
|
|
end;
|
|
|
|
procedure TfrmRanking.FormCreate(Sender: TObject);
|
|
begin
|
|
inherited;
|
|
with dtmSystem do
|
|
begin
|
|
if not tblGrupos.Active then
|
|
begin
|
|
tblGrupos.Open;
|
|
end
|
|
else
|
|
begin
|
|
tblGrupos.Refresh;
|
|
end;
|
|
tblGrupos.First;
|
|
while not tblGrupos.Eof do
|
|
begin
|
|
clb_grupousuarios.Items.Add(tblGruposTX_NOME.AsString);
|
|
tblGrupos.Next;
|
|
end;
|
|
if not tblProvidencias.Active then
|
|
begin
|
|
tblProvidencias.Open;
|
|
end
|
|
else
|
|
begin
|
|
tblProvidencias.Refresh;
|
|
end;
|
|
tblProvidencias.First;
|
|
while not tblProvidencias.Eof do
|
|
begin
|
|
clb_providencias.Items.Add(tblProvidenciasTX_NOME.AsString);
|
|
tblProvidencias.Next;
|
|
end;
|
|
end;
|
|
dtp_inicio.Date := Date;
|
|
dtp_fim.Date := Date;
|
|
AdjustCheckListBox;
|
|
end;
|
|
|
|
procedure TfrmRanking.rg_filtroqtdeClick(Sender: TObject);
|
|
begin
|
|
case rg_filtroqtde.ItemIndex of
|
|
0:
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
edt_v1.Enabled := true;
|
|
edt_v2.Enabled := false;
|
|
if edt_v1.Text <> '' then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS >= ' + edt_v1.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
1:
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
edt_v1.Enabled := true;
|
|
edt_v2.Enabled := false;
|
|
if edt_v1.Text <> '' then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS <= ' + edt_v1.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
2:
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
edt_v1.Enabled := true;
|
|
edt_v2.Enabled := true;
|
|
if (edt_v1.Text <> '') and (edt_v2.Text <> '') then
|
|
begin
|
|
zroqryRanking.Filter := 'QTDE_ACIONAMENTOS >= ' + edt_v1.Text +
|
|
' and QTDE_ACIONAMENTOS <= ' + edt_v2.Text;
|
|
zroqryRanking.Filtered := true;
|
|
end
|
|
else
|
|
begin
|
|
zroqryRanking.Filtered := false;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
|
|
end.
|