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.