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.