programa_cobranca/uconfrelAcordos.pas

unit uconfrelAcordos;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
  System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, umstMaster, Vcl.ComCtrls, Data.DB,
  Vcl.DBCtrls, Vcl.StdCtrls;

type
  TconfrelAcordos = class(TmstMaster)
    gb_data: TGroupBox;
    Label1: TLabel;
    dtp_inicio: TDateTimePicker;
    dtp_fim: TDateTimePicker;
    Button1: TButton;
    gb_beneficiario: TGroupBox;
    dblucb_beneficiario: TDBLookupComboBox;
    dtsBeneficiarios: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    id_emp: integer;
    procedure WMMoving(var Msg: TWMMoving); message WM_MOVING;

    { Private declarations }
  public
    str_titulos: string;
    { Public declarations }
  end;

var
  confrelAcordos: TconfrelAcordos;

implementation

{$R *.dfm}

uses udtmSystem, urelRelacaoAcordo;

procedure TconfrelAcordos.Button1Click(Sender: TObject);
var
  str_ids: string;
begin
  inherited;
  Screen.Cursor := crHourGlass;
  relRelacaoAcordo := TrelRelacaoAcordo.Create(self);
  with relRelacaoAcordo do
  begin
    rel_acordo := true;
    with zroqryAcordos.SQL do
    begin
      Clear;
      Add('select distinct');
      //Add('D.tx_nome,');
      //Add('T.id_titulo,');
      Add('MAX(T.id_titulo),');
      //Add('T.tx_produto,');
      //Add('T.dt_vencimento,');
      //Add('T.vl_titulo,');
      //Add('T.qtde_parcelas,');
      Add('AC.id_acordo');
      Add('from chg_titulos T');
      Add('left join sys_acordos AC on (AC.id_original = T.id_titulo)');
      //Add('left join chg_devedores D on (D.id_devedor = T.id_devedor)');
      Add('where (T.tp_situacao = ' + QuotedStr('QC') + ' or T.tp_situacao = ' +
        QuotedStr('B') + ') and');
      Add('T.id_empresa = ' + inttostr(dblucb_beneficiario.KeyValue) + ' and');
      Add('((CAST(T.dt_pagamento AS DATE) >= ' +
        QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)) + ' and');
      Add('CAST(T.dt_pagamento AS DATE) <= ' +
        QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)) + ' and');
      Add('T.dt_emissao_recibo is NULL) or');
      Add('(CAST(T.dt_emissao_recibo AS DATE) >= ' +
        QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)) + ' and');
      Add('CAST(T.dt_emissao_recibo AS DATE) <= ' +
        QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)) + ')) and');
      Add('T.id_titulo in (select id_original from sys_acordos)');
      Add('group by AC.id_acordo');
      //Add('order by T.id_titulo');
      Add('order by AC.id_acordo');
    end;
    zroqryAcordos.Open;
    zroqryAcordos.First;
    str_ids := '';
    while not zroqryAcordos.Eof do
    begin
      str_ids := str_ids + zroqryAcordos.FieldByName('ID_ACORDO')
        .AsString + ',';
      zroqryAcordos.Next;
    end;
    setlength(str_ids, length(str_ids) - 1);
    if length(str_ids) > 0 then
    begin
      with zroqry1.SQL do
      begin
        Clear;
        Add('select distinct A.id_acordo, B.tx_nome from sys_acordos A');
        Add('left join chg_devedores B on (A.id_devedor = B.id_devedor)');
        Add('where id_acordo in (' + str_ids + ')');
        Add('order by B.tx_nome');
      end;
      with zroqry2.SQL do
      begin
        Clear;
        Add('select distinct A.id_acordo, A.id_original, B.tx_produto, B.dt_vencimento, B.vl_titulo, count(a.id_acordo) from sys_acordos A');
        Add('left join chg_titulos B on (A.id_original = B.id_titulo)');
        Add('where id_acordo in (' + str_ids + ')');
        Add('group by A.id_acordo, A.id_original, B.tx_produto, B.dt_vencimento, B.vl_titulo');
      end;
      with zroqry3.SQL do
      begin
        Clear;
        Add('select distinct A.id_acordo, A.id_parcela, B.tx_produto, B.dt_vencimento, B.vl_titulo, count(a.id_acordo) from sys_acordos A');
        Add('left join chg_titulos B on (A.id_parcela = B.id_titulo)');
        Add('where id_acordo in (' + str_ids + ')');
        Add('group by A.id_acordo, A.id_parcela, B.tx_produto, B.dt_vencimento, B.vl_titulo');
      end;
      dtmSystem.tblEmpresa.Locate('ID_EMPRESA', id_emp, []);
      RLMemo1.Lines[0] := StringReplace(RLMemo1.Lines[0], 'BENEF',
        UpperCase(dtmSystem.tblCHGEmpresaTX_NOME.AsString),
        [rfReplaceAll, rfIgnoreCase]);
      RLMemo1.Lines[0] := StringReplace(RLMemo1.Lines[0], 'DT1',
        FormatDateTime('dd/mm/yyyy', dtp_inicio.Date),
        [rfReplaceAll, rfIgnoreCase]);
      RLMemo1.Lines[0] := StringReplace(RLMemo1.Lines[0], 'DT2',
        FormatDateTime('dd/mm/yyyy', dtp_fim.Date),
        [rfReplaceAll, rfIgnoreCase]);
      zroqry1.Open;
      zroqry2.Open;
      zroqry3.Open;

      Screen.Cursor := crDefault;
      frrelRelacaoAcordo.PreviewModal;
      zroqryAcordos.Close;
      zroqry1.Close;
      zroqry2.Close;
      zroqry3.Close;
    end
    else
    begin
      Screen.Cursor := crDefault;
      Showmessage('Não foi encontrado nenhum acordo no período escolhido para o credor selecionado.');
    end;
  end;
end;

procedure TconfrelAcordos.FormCreate(Sender: TObject);
begin
  inherited;
  dtp_inicio.Date := Date;
  dtp_fim.Date := Date;
  if not dtmSystem.tblCHGEmpresa.Active then
  begin
    dtmSystem.tblCHGEmpresa.Open;
  end;
  id_emp := dtmSystem.tblCHGEmpresaID_EMPRESA.AsInteger;
  dblucb_beneficiario.KeyValue := 1;
end;

procedure TconfrelAcordos.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.