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.
384 lines
14 KiB
384 lines
14 KiB
unit uconfrelRelatorioTCC;
|
|
|
|
interface
|
|
|
|
uses
|
|
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
|
|
System.Classes, Vcl.Graphics, System.win.ComObj,
|
|
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, umstMaster, Vcl.StdCtrls, Vcl.DBCtrls,
|
|
Vcl.ComCtrls, Data.DB, ZAbstractRODataset, ZDataset;
|
|
|
|
type
|
|
TconfrelRelatorioTCC = class(TmstMaster)
|
|
gb_data: TGroupBox;
|
|
Label1: TLabel;
|
|
dtp_inicio: TDateTimePicker;
|
|
dtp_fim: TDateTimePicker;
|
|
gb_beneficiario: TGroupBox;
|
|
dblucb_beneficiario: TDBLookupComboBox;
|
|
btn_viewreport: TButton;
|
|
dtsRelatorio: TDataSource;
|
|
roqryRelatorio: TZReadOnlyQuery;
|
|
roqryRelatorioTX_NOME: TWideStringField;
|
|
roqryRelatorioTX_DOCUMENTO: TWideStringField;
|
|
roqryRelatorioTX_PRODUTO: TWideStringField;
|
|
roqryRelatorioDT_VENCIMENTO: TDateTimeField;
|
|
roqryRelatorioVL_TITULO: TFloatField;
|
|
roqryRelatorioDT_PAGAMENTO: TDateTimeField;
|
|
roqryRelatorioVL_CORRIGIDO: TFloatField;
|
|
procedure FormCreate(Sender: TObject);
|
|
procedure btn_viewreportClick(Sender: TObject);
|
|
procedure FormClose(Sender: TObject; var Action: TCloseAction);
|
|
procedure AbreROQuery(tipo: string);
|
|
procedure GerarXLS(tipo: string);
|
|
function PixtoCM(pix: Integer): double;
|
|
private
|
|
{ Private declarations }
|
|
public
|
|
{ Public declarations }
|
|
end;
|
|
|
|
var
|
|
confrelRelatorioTCC: TconfrelRelatorioTCC;
|
|
|
|
meses: array [1 .. 12] of string = (
|
|
'JANEIRO',
|
|
'FEVEREIRO',
|
|
'MARCO',
|
|
'ABRIL',
|
|
'MAIO',
|
|
'JUNHO',
|
|
'JULHO',
|
|
'AGOSTO',
|
|
'SETEMBRO',
|
|
'OUTUBRO',
|
|
'NOVEMBRO',
|
|
'DEZEMBRO'
|
|
);
|
|
|
|
implementation
|
|
|
|
{$R *.dfm}
|
|
|
|
uses udtmSystem;
|
|
|
|
procedure TconfrelRelatorioTCC.AbreROQuery(tipo: string);
|
|
begin
|
|
if tipo = 'liq' then
|
|
begin
|
|
roqryRelatorio.Close;
|
|
roqryRelatorio.SQL.Clear;
|
|
roqryRelatorio.SQL.Add('select');
|
|
roqryRelatorio.SQL.Add('d.tx_nome,');
|
|
roqryRelatorio.SQL.Add('case when d.tp_cliente = ' + QuotedStr('F') +
|
|
' then d.cd_cpf');
|
|
roqryRelatorio.SQL.Add('when d.tp_cliente = ' + QuotedStr('J') +
|
|
' then d.cd_cnpj');
|
|
roqryRelatorio.SQL.Add('end as tx_documento,');
|
|
roqryRelatorio.SQL.Add('t.tx_produto,');
|
|
roqryRelatorio.SQL.Add('t.dt_vencimento,');
|
|
roqryRelatorio.SQL.Add('t.vl_titulo,');
|
|
roqryRelatorio.SQL.Add('t.dt_pagamento,');
|
|
roqryRelatorio.SQL.Add('t.vl_corrigido');
|
|
roqryRelatorio.SQL.Add('from chg_titulos t');
|
|
roqryRelatorio.SQL.Add
|
|
('left join chg_devedores d on d.id_devedor = t.id_devedor');
|
|
roqryRelatorio.SQL.Add('where');
|
|
roqryRelatorio.SQL.Add('t.id_empresa = ' +
|
|
inttostr(dblucb_beneficiario.KeyValue));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) >= ' +
|
|
QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) <= ' +
|
|
QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('t.tp_situacao = ' + QuotedStr('QB'));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('t.tx_produto not similar to ' + QuotedStr('99_*'));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('Upper(t.tx_produto) not similar to ' +
|
|
QuotedStr('ENTRADA_*'));
|
|
roqryRelatorio.Open;
|
|
roqryRelatorio.First;
|
|
end;
|
|
if tipo = 'ac' then
|
|
begin
|
|
roqryRelatorio.Close;
|
|
roqryRelatorio.SQL.Clear;
|
|
roqryRelatorio.SQL.Add('select');
|
|
roqryRelatorio.SQL.Add('d.tx_nome,');
|
|
roqryRelatorio.SQL.Add('case when d.tp_cliente = ' + QuotedStr('F') +
|
|
' then d.cd_cpf');
|
|
roqryRelatorio.SQL.Add('when d.tp_cliente = ' + QuotedStr('J') +
|
|
' then d.cd_cnpj');
|
|
roqryRelatorio.SQL.Add('end as tx_documento,');
|
|
roqryRelatorio.SQL.Add('t.tx_produto,');
|
|
roqryRelatorio.SQL.Add('t.dt_vencimento,');
|
|
roqryRelatorio.SQL.Add('t.vl_titulo,');
|
|
roqryRelatorio.SQL.Add('t.dt_pagamento,');
|
|
roqryRelatorio.SQL.Add('t.vl_corrigido');
|
|
roqryRelatorio.SQL.Add('from chg_titulos t');
|
|
roqryRelatorio.SQL.Add
|
|
('left join chg_devedores d on d.id_devedor = t.id_devedor');
|
|
roqryRelatorio.SQL.Add('where');
|
|
roqryRelatorio.SQL.Add('t.id_empresa = ' +
|
|
inttostr(dblucb_beneficiario.KeyValue));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) >= ' +
|
|
QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) <= ' +
|
|
QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('t.tp_situacao = ' + QuotedStr('QB'));
|
|
// Add('t.tp_situacao = ' + QuotedStr('QC'));
|
|
roqryRelatorio.SQL.Add('and');
|
|
roqryRelatorio.SQL.Add('(t.tx_produto similar to ' + QuotedStr('99_*'));
|
|
roqryRelatorio.SQL.Add('or');
|
|
roqryRelatorio.SQL.Add('Upper(t.tx_produto) similar to ' +
|
|
QuotedStr('ENTRADA_*') + ')');
|
|
roqryRelatorio.Open;
|
|
roqryRelatorio.First;
|
|
end;
|
|
|
|
end;
|
|
|
|
procedure TconfrelRelatorioTCC.btn_viewreportClick(Sender: TObject);
|
|
begin
|
|
// relTCC := TrelTCC.Create(self);
|
|
// with relTCC do
|
|
// begin
|
|
// roqryRelatorio.SQL.Clear;
|
|
// roqryRelatorio.SQL.Add('select');
|
|
// roqryRelatorio.SQL.Add('d.tx_nome,');
|
|
// roqryRelatorio.SQL.Add('case when d.tp_cliente = ' + QuotedStr('F') +
|
|
// ' then d.cd_cpf');
|
|
// roqryRelatorio.SQL.Add('when d.tp_cliente = ' + QuotedStr('J') +
|
|
// ' then d.cd_cnpj');
|
|
// roqryRelatorio.SQL.Add('end as tx_documento,');
|
|
// roqryRelatorio.SQL.Add('t.tx_produto,');
|
|
// roqryRelatorio.SQL.Add('t.dt_vencimento,');
|
|
// roqryRelatorio.SQL.Add('t.vl_titulo,');
|
|
// roqryRelatorio.SQL.Add('t.dt_pagamento,');
|
|
// roqryRelatorio.SQL.Add('t.vl_corrigido');
|
|
// roqryRelatorio.SQL.Add('from chg_titulos t');
|
|
// roqryRelatorio.SQL.Add
|
|
// ('left join chg_devedores d on d.id_devedor = t.id_devedor');
|
|
// roqryRelatorio.SQL.Add('where');
|
|
// roqryRelatorio.SQL.Add('t.id_empresa = ' +
|
|
// inttostr(dblucb_beneficiario.KeyValue));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) >= ' +
|
|
// QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) <= ' +
|
|
// QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('t.tp_situacao = ' + QuotedStr('QB'));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('t.tx_produto not similar to ' + QuotedStr('99_*'));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('Upper(t.tx_produto) not similar to ' +
|
|
// QuotedStr('ENTRADA_*'));
|
|
// // Add('t.tp_situacao = ' + QuotedStr('QC'));
|
|
// tipo := 'liq';
|
|
// frrelTCC.PreviewModal;
|
|
// roqryRelatorio.Close;
|
|
// roqryRelatorio.SQL.Clear;
|
|
// roqryRelatorio.SQL.Add('select');
|
|
// roqryRelatorio.SQL.Add('d.tx_nome,');
|
|
// roqryRelatorio.SQL.Add('case when d.tp_cliente = ' + QuotedStr('F') +
|
|
// ' then d.cd_cpf');
|
|
// roqryRelatorio.SQL.Add('when d.tp_cliente = ' + QuotedStr('J') +
|
|
// ' then d.cd_cnpj');
|
|
// roqryRelatorio.SQL.Add('end as tx_documento,');
|
|
// roqryRelatorio.SQL.Add('t.tx_produto,');
|
|
// roqryRelatorio.SQL.Add('t.dt_vencimento,');
|
|
// roqryRelatorio.SQL.Add('t.vl_titulo,');
|
|
// roqryRelatorio.SQL.Add('t.dt_pagamento,');
|
|
// roqryRelatorio.SQL.Add('t.vl_corrigido');
|
|
// roqryRelatorio.SQL.Add('from chg_titulos t');
|
|
// roqryRelatorio.SQL.Add
|
|
// ('left join chg_devedores d on d.id_devedor = t.id_devedor');
|
|
// roqryRelatorio.SQL.Add('where');
|
|
// roqryRelatorio.SQL.Add('t.id_empresa = ' +
|
|
// inttostr(dblucb_beneficiario.KeyValue));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) >= ' +
|
|
// QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_inicio.Date)));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('cast(t.dt_entrada as date) <= ' +
|
|
// QuotedStr(FormatDateTime('yyyy-mm-dd', dtp_fim.Date)));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('t.tp_situacao = ' + QuotedStr('QB'));
|
|
// // Add('t.tp_situacao = ' + QuotedStr('QC'));
|
|
// roqryRelatorio.SQL.Add('and');
|
|
// roqryRelatorio.SQL.Add('(t.tx_produto similar to ' + QuotedStr('99_*'));
|
|
// roqryRelatorio.SQL.Add('or');
|
|
// roqryRelatorio.SQL.Add('Upper(t.tx_produto) similar to ' +
|
|
// QuotedStr('ENTRADA_*') + ')');
|
|
// tipo := 'ac';
|
|
// frrelTCC.PreviewModal;
|
|
// end;
|
|
AbreROQuery('liq');
|
|
GerarXLS('liq');
|
|
AbreROQuery('ac');
|
|
GerarXLS('ac');
|
|
Showmessage('Processo terminado!');
|
|
end;
|
|
|
|
procedure TconfrelRelatorioTCC.FormClose(Sender: TObject;
|
|
var Action: TCloseAction);
|
|
begin
|
|
dtmSystem.tblCHGEmpresa.Filtered := False;
|
|
end;
|
|
|
|
procedure TconfrelRelatorioTCC.FormCreate(Sender: TObject);
|
|
begin
|
|
with dtmSystem do
|
|
begin
|
|
if not tblCHGEmpresa.Active then
|
|
begin
|
|
tblCHGEmpresa.Open;
|
|
end;
|
|
tblCHGEmpresa.Filtered := False;
|
|
tblCHGEmpresa.Filter := 'TX_NOME LIKE ' + QuotedStr('*TECH*');
|
|
tblCHGEmpresa.Filtered := true;
|
|
end;
|
|
end;
|
|
|
|
procedure TconfrelRelatorioTCC.GerarXLS(tipo: string);
|
|
var
|
|
OpenOffice, OpenDesktop, OOCalc, OOExec, Planilha, LoadParams, Param,
|
|
FilterParams, Param2: Variant;
|
|
i, j: Integer;
|
|
pathFile: string;
|
|
Fmt: TFormatSettings;
|
|
begin
|
|
Screen.Cursor := crHourGlass;
|
|
|
|
// gera uma planilha conforme o string grid do layout
|
|
if VarIsEmpty(OpenOffice) then
|
|
begin
|
|
OpenOffice := CreateOleObject('com.sun.star.ServiceManager');
|
|
end;
|
|
OpenDesktop := OpenOffice.CreateInstance('com.sun.star.frame.Desktop');
|
|
|
|
LoadParams := VarArrayCreate([0, 0], varVariant);
|
|
Param := OpenOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
|
|
Param.Name := 'Hidden';
|
|
Param.Value := false;
|
|
LoadParams[0] := Param;
|
|
|
|
OOExec := OpenDesktop.LoadComponentFromURL('private:factory/scalc', '_blank',
|
|
0, LoadParams);
|
|
OOCalc := OOExec.Sheets;
|
|
Planilha := OOCalc.getbyIndex(0);
|
|
Planilha.getCellByPosition(0, 0).string := 'DOCUMENTO';
|
|
Planilha.getCellByPosition(0, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(0, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
Planilha.getCellByPosition(1, 0).string := 'NOME';
|
|
Planilha.getCellByPosition(1, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(1, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
Planilha.getCellByPosition(2, 0).string := 'TÍTULO';
|
|
Planilha.getCellByPosition(2, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(2, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
Planilha.getCellByPosition(3, 0).string := 'VENCIMENTO';
|
|
Planilha.getCellByPosition(3, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(3, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
Planilha.getCellByPosition(4, 0).string := 'VALOR ORIGINAL';
|
|
Planilha.getCellByPosition(4, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(4, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
Planilha.getCellByPosition(5, 0).string := 'RECEBIMENTO';
|
|
Planilha.getCellByPosition(5, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(5, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
Planilha.getCellByPosition(6, 0).string := 'VALOR RECEBIDO';
|
|
Planilha.getCellByPosition(6, 0).getText.createTextCursor.CharWeight := 150;
|
|
Planilha.getCellByPosition(6, 0).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
j := 1;
|
|
while not roqryRelatorio.Eof do
|
|
begin
|
|
for i := 0 to roqryRelatorio.FieldCount - 1 do
|
|
begin
|
|
Planilha.getCellByPosition(i, j).string :=
|
|
roqryRelatorio.FieldByName(roqryRelatorio.Fields[i].FieldName).AsString;
|
|
Planilha.getCellByPosition(i, j).getColumns.getbyIndex(0).OptimalWidth := true;
|
|
end;
|
|
Inc(j, 1);
|
|
roqryRelatorio.Next;
|
|
end;
|
|
|
|
FilterParams := VarArrayCreate([0, 1], varVariant);
|
|
|
|
Param2 := OpenDesktop.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
|
|
Param2.Name := 'FilterName';
|
|
Param2.Value := 'MS Excel 97';
|
|
FilterParams[0] := Param2;
|
|
Param2.Name := 'Overwrite';
|
|
Param2.Value := true;
|
|
FilterParams[1] := Param2;
|
|
|
|
for i := Low(meses) to High(meses) do
|
|
begin
|
|
Fmt.LongMonthNames[i] := meses[i];
|
|
end;
|
|
// pathFile := ExtractFilePath(Application.ExeName) + 'RELATORIOS';
|
|
pathFile := dtmSystem.path_executavel + '\RELATORIOS';
|
|
if not DirectoryExists(pathFile) then
|
|
begin
|
|
CreateDir(pathFile);
|
|
end;
|
|
pathFile := pathFile + '\PRESTACAO_CONTAS_TCC';
|
|
if not DirectoryExists(pathFile) then
|
|
begin
|
|
CreateDir(pathFile);
|
|
end;
|
|
pathFile := pathFile + '\' + FormatDateTime('YYYY', Date);
|
|
if not DirectoryExists(pathFile) then
|
|
begin
|
|
CreateDir(pathFile);
|
|
end;
|
|
pathFile := pathFile + '\' + FormatDateTime('mmmm', Date, Fmt);
|
|
if not DirectoryExists(pathFile) then
|
|
begin
|
|
CreateDir(pathFile);
|
|
end;
|
|
pathFile := pathFile + '\' + FormatDateTime('dd', Date);
|
|
if not DirectoryExists(pathFile) then
|
|
begin
|
|
CreateDir(pathFile);
|
|
end;
|
|
if tipo = 'liq' then
|
|
begin
|
|
pathFile := pathFile + '\RELATORIO_PRESTACAO_CONTAS-LIQ-' +
|
|
FormatDateTime('HH-mm', Time);
|
|
end;
|
|
if tipo = 'ac' then
|
|
begin
|
|
pathFile := pathFile + '\RELATORIO_PRESTACAO_CONTAS-ACORDOS-' +
|
|
FormatDateTime('HH-mm', Time);
|
|
end;
|
|
|
|
// pathFile := GetCurrentDir;
|
|
pathFile := 'file:///' + pathFile;
|
|
pathFile := StringReplace(pathFile, '\', '/', [rfReplaceAll]);
|
|
|
|
try
|
|
OOExec.StoreAsURL(pathFile + '.xls', FilterParams);
|
|
// OOExec.Close(False);
|
|
finally
|
|
// OpenOffice := Unassigned;
|
|
end;
|
|
Screen.Cursor := crDefault;
|
|
end;
|
|
|
|
function TconfrelRelatorioTCC.PixtoCM(pix: Integer): double;
|
|
var
|
|
ppcm: double;
|
|
begin
|
|
ppcm := Screen.PixelsPerInch / 3000;
|
|
result := pix / ppcm;
|
|
end;
|
|
|
|
end.
|