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.
580 lines
18 KiB
580 lines
18 KiB
unit ufrmImpTCC;
|
|
|
|
interface
|
|
|
|
uses
|
|
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
|
|
System.Classes, Vcl.Graphics, System.DateUtils, System.UITypes,
|
|
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, umstMaster, Vcl.Grids, Vcl.Menus,
|
|
Vcl.ComCtrls, System.win.ComObj, Vcl.StdCtrls, Vcl.DBCtrls, Vcl.CheckLst;
|
|
|
|
type
|
|
TfrmImpTCC = class(TmstMaster)
|
|
mnu_main: TMainMenu;
|
|
Arquivo1: TMenuItem;
|
|
mnu_abrir: TMenuItem;
|
|
od_abrir: TOpenDialog;
|
|
sgrd_import: TStringGrid;
|
|
pb_import: TProgressBar;
|
|
btn_baixatitulos: TButton;
|
|
lbl_estado: TLabel;
|
|
dblucb_tcc: TDBLookupComboBox;
|
|
btn_geraracordo: TButton;
|
|
clb_cobradores: TCheckListBox;
|
|
btn_rel: TButton;
|
|
Label1: TLabel;
|
|
Label2: TLabel;
|
|
lbl_arquivo: TLabel;
|
|
procedure mnu_abrirClick(Sender: TObject);
|
|
procedure FormCreate(Sender: TObject);
|
|
procedure btn_baixatitulosClick(Sender: TObject);
|
|
procedure FormClose(Sender: TObject; var Action: TCloseAction);
|
|
procedure btn_geraracordoClick(Sender: TObject);
|
|
procedure btn_relClick(Sender: TObject);
|
|
private
|
|
function CmtoPix(cm: double): integer;
|
|
{ Private declarations }
|
|
public
|
|
itemList: TStringList;
|
|
{ Public declarations }
|
|
end;
|
|
|
|
var
|
|
frmImpTCC: TfrmImpTCC;
|
|
|
|
implementation
|
|
|
|
{$R *.dfm}
|
|
|
|
uses udtmSystem, uconfrelRelatorioTCC;
|
|
|
|
procedure TfrmImpTCC.btn_baixatitulosClick(Sender: TObject);
|
|
var
|
|
i, counter: integer;
|
|
achou: Boolean;
|
|
dt_venc: string;
|
|
begin
|
|
if dblucb_tcc.KeyValue = Null then
|
|
begin
|
|
MessageDlg
|
|
('Por favor escolha qual Tech Cable o arquivo de importação pertence.',
|
|
mtWarning, [mbOK], 0);
|
|
abort;
|
|
end;
|
|
screen.cursor := crHourGlass;
|
|
lbl_estado.Caption := 'Estado: Baixando títulos.';
|
|
pb_import.Max := sgrd_import.RowCount - 2;
|
|
counter := 0;
|
|
for i := 1 to sgrd_import.RowCount - 1 do
|
|
begin
|
|
achou := false;
|
|
label2.Caption := 'Lendo linha: ' + sgrd_import.Cells[1, i];
|
|
with dtmSystem do
|
|
begin
|
|
if sgrd_import.Cells[0, i] = 'F' then
|
|
begin
|
|
if tblDevedores.Locate('CD_CPF', sgrd_import.Cells[1, i], []) then
|
|
begin
|
|
achou := true;
|
|
end;
|
|
end;
|
|
if sgrd_import.Cells[0, i] = 'J' then
|
|
begin
|
|
if tblDevedores.Locate('CD_CNPJ', sgrd_import.Cells[1, i], []) then
|
|
begin
|
|
achou := true;
|
|
end;
|
|
end;
|
|
if achou then
|
|
begin
|
|
if (sgrd_import.Cells[5, i] = 'Mensalidades') then
|
|
begin
|
|
dt_venc := copy(sgrd_import.Cells[3, i], 7, 4) + '-' +
|
|
copy(sgrd_import.Cells[3, i], 4, 2) + '-' +
|
|
copy(sgrd_import.Cells[3, i], 1, 2);
|
|
if tblTitulos.Locate('DT_VENCIMENTO;VL_TITULO',
|
|
Vararrayof([dt_venc, sgrd_import.Cells[7, i]]), []) then
|
|
begin
|
|
if tblTitulosTP_SITUACAO.AsString = 'A' then
|
|
begin
|
|
tblTitulos.Edit;
|
|
if sgrd_import.Cells[12, i] = 'X' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'QB';
|
|
end;
|
|
if sgrd_import.Cells[12, i] = 'B' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'QB';
|
|
end;
|
|
if sgrd_import.Cells[6, i] <> '' then
|
|
begin
|
|
tblTitulosDT_PAGAMENTO.AsDateTime :=
|
|
StrToDate(sgrd_import.Cells[6, i]);
|
|
end;
|
|
tblTitulosTX_OBS.AsString :=
|
|
'Título baixado pela importação TCC.';
|
|
if sgrd_import.Cells[11, i] <> '' then
|
|
begin
|
|
tblTitulosVL_CORRIGIDO.AsCurrency :=
|
|
StrToCurr(stringreplace(sgrd_import.Cells[11, i], '.', ',',
|
|
[rfReplaceAll]));
|
|
end;
|
|
tblTitulos.Post;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
Inc(counter, 1);
|
|
if counter < sgrd_import.RowCount - 2 then
|
|
begin
|
|
pb_import.Position := counter + 1;
|
|
pb_import.Position := counter;
|
|
end
|
|
else
|
|
begin
|
|
pb_import.Max := counter + 1;
|
|
pb_import.Position := counter + 1;
|
|
pb_import.Max := counter;
|
|
end;
|
|
Application.ProcessMessages;
|
|
end;
|
|
screen.cursor := crDefault;
|
|
lbl_estado.Caption := 'Estado: Terminou Mensalidades.';
|
|
// btn_baixatitulos.Enabled := false;
|
|
// btn_geraracordo.Enabled := false;
|
|
end;
|
|
|
|
procedure TfrmImpTCC.btn_geraracordoClick(Sender: TObject);
|
|
var
|
|
i, counter: integer;
|
|
achou, agendar: Boolean;
|
|
dt_venc, erro, dt_cad, old_id: string;
|
|
cobradores: array of string;
|
|
j, k, loop: integer;
|
|
begin
|
|
if dblucb_tcc.KeyValue = Null then
|
|
begin
|
|
MessageDlg
|
|
('Por favor escolha qual Tech Cable o arquivo de importação pertence.',
|
|
mtWarning, [mbOK], 0);
|
|
abort;
|
|
end;
|
|
agendar := false;
|
|
screen.cursor := crHourGlass;
|
|
lbl_estado.Caption := 'Estado: Gerando acordos.';
|
|
setlength(cobradores, 1);
|
|
k := 0;
|
|
loop := 0;
|
|
for j := 0 to clb_cobradores.Items.Count - 1 do
|
|
begin
|
|
if clb_cobradores.Checked[j] then
|
|
begin
|
|
cobradores[k] := clb_cobradores.Items[j];
|
|
Inc(k, 1);
|
|
setlength(cobradores, k + 1);
|
|
end;
|
|
end;
|
|
setlength(cobradores, length(cobradores) - 1);
|
|
pb_import.Max := sgrd_import.RowCount - 2;
|
|
counter := 0;
|
|
old_id := '';
|
|
for i := 1 to sgrd_import.RowCount - 1 do
|
|
begin
|
|
try
|
|
label2.Caption := 'Lendo linha: ' + sgrd_import.Cells[1, i];
|
|
achou := false;
|
|
with dtmSystem do
|
|
begin
|
|
if sgrd_import.Cells[0, i] = 'F' then
|
|
begin
|
|
if tblDevedores.Locate('CD_CPF', sgrd_import.Cells[1, i], []) then
|
|
begin
|
|
achou := true;
|
|
end;
|
|
end;
|
|
if sgrd_import.Cells[0, i] = 'J' then
|
|
begin
|
|
if tblDevedores.Locate('CD_CNPJ', sgrd_import.Cells[1, i], []) then
|
|
begin
|
|
achou := true;
|
|
end;
|
|
end;
|
|
if achou then
|
|
begin
|
|
if old_id <> tblDevedoresID_DEVEDOR.AsString then
|
|
begin
|
|
old_id := tblDevedoresID_DEVEDOR.AsString;
|
|
agendar := true;
|
|
end;
|
|
if (sgrd_import.Cells[5, i] = 'Mensalidades') and
|
|
(sgrd_import.Cells[12, i] = 'X') then
|
|
begin
|
|
dt_venc := copy(sgrd_import.Cells[3, i], 7, 4) + '-' +
|
|
copy(sgrd_import.Cells[3, i], 4, 2) + '-' +
|
|
copy(sgrd_import.Cells[3, i], 1, 2);
|
|
if tblTitulos.Locate('DT_VENCIMENTO;VL_TITULO',
|
|
Vararrayof([dt_venc, sgrd_import.Cells[7, i]]), []) then
|
|
begin
|
|
dt_cad := sgrd_import.Cells[13, i];
|
|
end;
|
|
end;
|
|
if (sgrd_import.Cells[4, i] = 'ENTRADA ACORDO') or
|
|
(copy(sgrd_import.Cells[4, i], 1, 2) = '99') then
|
|
begin
|
|
if dt_cad = sgrd_import.Cells[2, i] then
|
|
begin
|
|
dt_venc := copy(sgrd_import.Cells[3, i], 7, 4) + '-' +
|
|
copy(sgrd_import.Cells[3, i], 4, 2) + '-' +
|
|
copy(sgrd_import.Cells[3, i], 1, 2);
|
|
if not tblTitulos.Locate('DT_VENCIMENTO;VL_TITULO',
|
|
Vararrayof([dt_venc, sgrd_import.Cells[7, i]]), []) then
|
|
begin
|
|
tblTitulos.Append;
|
|
tblTitulosID_DEVEDOR.AsInteger :=
|
|
tblDevedoresID_DEVEDOR.AsInteger;
|
|
tblTitulosID_EMPRESA.AsInteger := dblucb_tcc.KeyValue;
|
|
tblTitulosTX_PRODUTO.AsString := sgrd_import.Cells[4, i];
|
|
if sgrd_import.Cells[10, i] <> '' then
|
|
begin
|
|
tblTitulosVL_TITULO.AsCurrency :=
|
|
StrToCurr(stringreplace(sgrd_import.Cells[7, i], '.', ',',
|
|
[rfReplaceAll]));
|
|
end;
|
|
tblTitulosDT_ENTRADA.AsDateTime :=
|
|
StrToDate(sgrd_import.Cells[2, i]);
|
|
tblTitulosDT_VENCIMENTO.AsDateTime :=
|
|
StrToDate(sgrd_import.Cells[3, i]);
|
|
if sgrd_import.Cells[12, i] = 'A' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'A';
|
|
if agendar then
|
|
begin
|
|
agendar := false;
|
|
tblUsuariosConsulta.Locate('TX_NOME', cobradores[loop], []);
|
|
tblAgenda.Append;
|
|
tblAgendaID_COBRADOR.AsInteger :=
|
|
tblUsuariosConsultaID_USUARIO.AsInteger;
|
|
tblAgendaID_DEVEDOR.AsInteger :=
|
|
tblDevedoresID_DEVEDOR.AsInteger;
|
|
tblAgendaDT_AGENDADA.AsDateTime :=
|
|
IncDay(StrToDate(sgrd_import.Cells[3, i]), 33);
|
|
tblAgendaHR_AGENDADA.AsDateTime := StrToTime('00:00:01');
|
|
tblAgendaDT_QUE_AGENDOU.AsDateTime := Now;
|
|
tblAgendaQUEM_AGENDOU.AsInteger := id_usuario;
|
|
tblAgenda.Post;
|
|
Inc(loop, 1);
|
|
if loop = length(cobradores) then
|
|
begin
|
|
loop := 0;
|
|
end;
|
|
end;
|
|
end;
|
|
if sgrd_import.Cells[12, i] = 'B' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'QB';
|
|
end;
|
|
if sgrd_import.Cells[12, i] = 'X' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'QB';
|
|
end;
|
|
if (sgrd_import.Cells[11, i] <> '') and
|
|
(sgrd_import.Cells[11, i] <> '0') then
|
|
begin
|
|
tblTitulosVL_CORRIGIDO.AsCurrency :=
|
|
StrToCurr(stringreplace(sgrd_import.Cells[11, i], '.', ',',
|
|
[rfReplaceAll]));;
|
|
end;
|
|
if sgrd_import.Cells[6, i] <> '' then
|
|
begin
|
|
tblTitulosDT_PAGAMENTO.AsDateTime :=
|
|
StrToDate(sgrd_import.Cells[6, i]);
|
|
end;
|
|
tblTitulos.Post;
|
|
end
|
|
else
|
|
begin
|
|
if tblTitulosTP_SITUACAO.AsString = 'A' then
|
|
begin
|
|
tblTitulos.Edit;
|
|
tblTitulosID_DEVEDOR.AsInteger :=
|
|
tblDevedoresID_DEVEDOR.AsInteger;
|
|
erro := 'i';
|
|
if sgrd_import.Cells[12, i] = 'A' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'A';
|
|
tblTitulosTX_OBS.AsString :=
|
|
'Título criado pela importação TCC.';
|
|
end;
|
|
if sgrd_import.Cells[12, i] = 'B' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'QB';
|
|
if sgrd_import.Cells[6, i] <> '' then
|
|
begin
|
|
tblTitulosDT_PAGAMENTO.AsDateTime :=
|
|
StrToDate(sgrd_import.Cells[6, i]);
|
|
erro := 'h';
|
|
end;
|
|
tblTitulosTX_OBS.AsString :=
|
|
'Título criado e baixado pela importação TCC.';
|
|
end;
|
|
if sgrd_import.Cells[12, i] = 'X' then
|
|
begin
|
|
tblTitulosTP_SITUACAO.AsString := 'QB';
|
|
if sgrd_import.Cells[6, i] <> '' then
|
|
begin
|
|
tblTitulosDT_PAGAMENTO.AsDateTime :=
|
|
StrToDate(sgrd_import.Cells[6, i]);
|
|
erro := 'h';
|
|
end;
|
|
tblTitulosTX_OBS.AsString :=
|
|
'Título cancelado pela importação TCC.';
|
|
end;
|
|
if (sgrd_import.Cells[11, i] <> '') and
|
|
(sgrd_import.Cells[11, i] <> '0') then
|
|
begin
|
|
tblTitulosVL_CORRIGIDO.AsCurrency :=
|
|
StrToCurr(stringreplace(sgrd_import.Cells[11, i], '.',
|
|
',', [rfReplaceAll]));
|
|
erro := 'j';
|
|
end;
|
|
tblTitulos.Post;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
end;
|
|
Inc(counter, 1);
|
|
if counter < sgrd_import.RowCount - 2 then
|
|
begin
|
|
pb_import.Position := counter + 1;
|
|
pb_import.Position := counter;
|
|
end
|
|
else
|
|
begin
|
|
pb_import.Max := counter + 1;
|
|
pb_import.Position := counter + 1;
|
|
pb_import.Max := counter;
|
|
end;
|
|
Application.ProcessMessages;
|
|
except
|
|
Showmessage(inttostr(i) + erro);
|
|
end;
|
|
|
|
end;
|
|
lbl_estado.Caption := 'Estado: Terminou Acordos.';
|
|
// btn_baixatitulos.Enabled := false;
|
|
// btn_geraracordo.Enabled := false;
|
|
screen.cursor := crDefault;
|
|
end;
|
|
|
|
procedure TfrmImpTCC.btn_relClick(Sender: TObject);
|
|
begin
|
|
with TconfrelRelatorioTCC.Create(self) do
|
|
begin
|
|
Showmodal;
|
|
end;
|
|
end;
|
|
|
|
function TfrmImpTCC.CmtoPix(cm: double): integer;
|
|
var
|
|
ppcm: double;
|
|
begin
|
|
ppcm := screen.PixelsPerInch / 2540;
|
|
Result := Trunc(ppcm * cm);
|
|
end;
|
|
|
|
procedure TfrmImpTCC.FormClose(Sender: TObject; var Action: TCloseAction);
|
|
begin
|
|
dtmSystem.tblCHGEmpresa.Filtered := false;
|
|
end;
|
|
|
|
procedure TfrmImpTCC.FormCreate(Sender: TObject);
|
|
begin
|
|
with dtmSystem do
|
|
begin
|
|
if not tblDevedores.Active then
|
|
begin
|
|
tblDevedores.Open;
|
|
end;
|
|
if not tblTitulos.Active then
|
|
begin
|
|
tblTitulos.Open;
|
|
end;
|
|
if not tblCHGEmpresa.Active then
|
|
begin
|
|
tblCHGEmpresa.Open;
|
|
end;
|
|
tblCHGEmpresa.Filtered := false;
|
|
tblCHGEmpresa.Filter := 'TX_NOME LIKE ' + quotedstr('*TECH*');
|
|
tblCHGEmpresa.Filtered := true;
|
|
if not tblUsuariosConsulta.Active then
|
|
begin
|
|
tblUsuariosConsulta.Open;
|
|
end;
|
|
tblUsuariosConsulta.First;
|
|
while not tblUsuariosConsulta.Eof do
|
|
begin
|
|
clb_cobradores.Items.Add(tblUsuariosConsultaTX_NOME.AsString);
|
|
tblUsuariosConsulta.Next;
|
|
end;
|
|
if not tblAgenda.Active then
|
|
begin
|
|
tblAgenda.Open;
|
|
end;
|
|
end;
|
|
end;
|
|
|
|
procedure TfrmImpTCC.mnu_abrirClick(Sender: TObject);
|
|
var
|
|
OpenOffice, OpenDesktop, OOCalc, OOExec, Planilha, LoadParams, Param,
|
|
OORCCount: Variant;
|
|
RCount, CCount, TotCell, counter, ColWidth, RowHeight, PColwidth,
|
|
PRowHeight: integer;
|
|
pathFile: string;
|
|
i, j, k, l: integer;
|
|
begin
|
|
if od_abrir.Execute then
|
|
begin
|
|
lbl_estado.Caption := 'Estado: Abrindo arquivo.';
|
|
pathFile := stringreplace(od_abrir.FileName, '\', '/', [rfReplaceAll]);
|
|
pathFile := 'file:///' + pathFile;
|
|
screen.cursor := crHourGlass;
|
|
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 := true;
|
|
LoadParams[0] := Param;
|
|
|
|
OOExec := OpenDesktop.LoadComponentFromURL(pathFile, '_blank', 0,
|
|
LoadParams);
|
|
OOCalc := OOExec.Sheets;
|
|
Planilha := OOCalc.getbyIndex(0);
|
|
|
|
OORCCount := Planilha.createCursor;
|
|
OORCCount.gotoEndOfUsedArea(false);
|
|
RCount := OORCCount.RangeAddress.EndRow;
|
|
label1.Caption := 'Quantidade de linhas: ' + inttostr(Rcount);
|
|
CCount := OORCCount.RangeAddress.EndColumn;
|
|
Inc(RCount, 1);
|
|
Inc(CCount, 1);
|
|
TotCell := RCount * CCount;
|
|
pb_import.Max := TotCell;
|
|
counter := 0;
|
|
|
|
itemList := TStringList.Create;
|
|
with itemList do
|
|
begin
|
|
for k := 0 to CCount - 1 do
|
|
begin
|
|
Add(inttostr(k + 1));
|
|
end;
|
|
end;
|
|
|
|
for l := 0 to sgrd_import.ColCount - 1 do
|
|
begin
|
|
sgrd_import.Cols[l].Clear;
|
|
end;
|
|
|
|
sgrd_import.RowCount := RCount;
|
|
sgrd_import.ColCount := CCount;
|
|
|
|
RowHeight := 0;
|
|
|
|
for j := 0 to RCount - 1 do
|
|
begin
|
|
ColWidth := 0;
|
|
for i := 0 to CCount - 1 do
|
|
begin
|
|
PColwidth := CmtoPix(Planilha.getCellByPosition(i, j)
|
|
.getColumns.getbyIndex(0).Width);
|
|
if ColWidth < PColwidth then
|
|
begin
|
|
ColWidth := PColwidth;
|
|
end;
|
|
PRowHeight := CmtoPix(Planilha.getCellByPosition(i, j).getRows.Height);
|
|
if RowHeight < PRowHeight then
|
|
begin
|
|
RowHeight := PRowHeight;
|
|
end;
|
|
sgrd_import.ColWidths[i] := ColWidth;
|
|
sgrd_import.RowHeights[j] := RowHeight;
|
|
|
|
if ((Planilha.getCellByPosition(i, j).NumberFormat = 36) or
|
|
(Planilha.getCellByPosition(i, j).NumberFormat = 37) or
|
|
(Planilha.getCellByPosition(i, j).NumberFormat = 8110) or
|
|
(Planilha.getCellByPosition(i, j).NumberFormat = 8109)) and
|
|
(Planilha.getCellByPosition(i, j).getFormula <> '') and
|
|
(StrToIntDef(copy(Planilha.getCellByPosition(i, j).getFormula, 1, 1),
|
|
0) <> 0) then
|
|
begin
|
|
try
|
|
sgrd_import.Cells[i, j] :=
|
|
DateToStr(Planilha.getCellByPosition(i, j).getFormula);
|
|
except
|
|
MessageDlg('Data não existente escrita na linha ' + inttostr(j) +
|
|
', coluna ' + inttostr(i + 1) +
|
|
'. Essa data não será carregada para importação, o que irá gerar um erro.',
|
|
mtWarning, [mbOK], 0);
|
|
end;
|
|
end
|
|
else
|
|
begin
|
|
if copy(Planilha.getCellByPosition(i, j).getFormula, 0, 1) = '''' then
|
|
begin
|
|
sgrd_import.Cells[i, j] :=
|
|
stringreplace(Planilha.getCellByPosition(i, j).getFormula, '''',
|
|
'', [rfReplaceAll, rfIgnoreCase]);
|
|
end
|
|
else
|
|
begin
|
|
sgrd_import.Cells[i, j] := Planilha.getCellByPosition(i, j)
|
|
.getFormula;
|
|
end;
|
|
if Planilha.getCellByPosition(i, j).getFormula = '' then
|
|
begin
|
|
sgrd_import.Cells[i, j] := '';
|
|
end;
|
|
end;
|
|
|
|
Inc(counter, 1);
|
|
if counter < TotCell then
|
|
begin
|
|
pb_import.Position := counter + 1;
|
|
pb_import.Position := counter;
|
|
end
|
|
else
|
|
begin
|
|
pb_import.Max := counter + 1;
|
|
pb_import.Position := counter + 1;
|
|
pb_import.Max := counter;
|
|
end;
|
|
end;
|
|
Application.ProcessMessages;
|
|
end;
|
|
|
|
try
|
|
OOExec.Close(false);
|
|
OpenDesktop.Terminate;
|
|
finally
|
|
|
|
OpenOffice := Unassigned;
|
|
|
|
screen.cursor := crDefault;
|
|
SetFocus;
|
|
end;
|
|
btn_baixatitulos.Enabled := true;
|
|
btn_geraracordo.Enabled := true;
|
|
end;
|
|
lbl_estado.Caption := 'Estado: Esperando ação.';
|
|
lbl_arquivo.Caption := 'Arquivo: ' + od_abrir.FileName;
|
|
end;
|
|
|
|
end.
|