Comparison works, but data listing does not

0
procedure TfrmGrid.btnLoadClick(Sender: TObject);
var
 txt: TextFile;
 treg, lreg, dc: integer;
 valortxt, valorbd : double;
 dt1, dt2,lTemp, valor, dtcompratxt, numnf, coo,ccf: String;
 dtcompra: TDateTime;
begin
lreg:= 1;
treg:= 0;
dc := 0;
StatusBar1.Panels[0].Text:= 'Abrindo arquivo de txt';
AssignFile(txt, frmSelection.FileListBox1.FileName);
Reset(txt);

 while not eof(txt) do
 begin
   Readln(txt, lTemp);
   if  (copy(lTemp, 1, 3) = 'E14') then
   begin
     StatusBar1.Panels[0].Text:= 'Executando a query';
     dtcompra:=StrToDate(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+copy(lTemp,59,4));
     dtcompratxt:= FormatDateTime('dd/MM/yyyy', dtcompra);

     DModuleGrid.ZQuery1.Close;
     DModuleGrid.ZQuery1.SQL.Clear;
     DModuleGrid.ZQuery1.SQL.Add('SELECT dtcompra, impcaixa, numcupom, ccf, valor FROM tdcupant');
     DModuleGrid.ZQuery1.SQL.Add('WHERE numcupom = :co2 AND ccf = :cc3 AND dtcompra = :dtc4 AND impcaixa = :ip5');
     DModuleGrid.ZQuery1.ParamByName('co2').AsString := copy(lTemp,53,6);
     DModuleGrid.ZQuery1.ParamByName('cc3').AsString := copy(lTemp,47,6);
     DModuleGrid.ZQuery1.ParamByName('ip5').AsString := copy(lTemp,4,20);
     DModuleGrid.ZQuery1.ParamByName('dtc4').AsDate := StrToDate(dtcompratxt);
     DModuleGrid.ZQuery1.Open;

     if not (DModuleGrid.ZQuery1.IsEmpty) then
     begin
       StatusBar1.Panels[0].Text:= 'Executando o loop de consulta/comparação';
       DModuleGrid.ZQuery1.First;
         if  (DModuleGrid.ZQuery1.FieldByName('numcupom').AsString = DModuleGrid.ZQuery1.ParamByName('co2').AsString)
         and (DModuleGrid.ZQuery1.FieldByName('ccf').AsString = DModuleGrid.ZQuery1.ParamByName('cc3').AsString)
         and (DModuleGrid.ZQuery1.FieldByName('dtcompra').AsDateTime = DModuleGrid.ZQuery1.ParamByName('dtc4').AsDate)
         and (DModuleGrid.ZQuery1.FieldByName('impcaixa').AsString = DModuleGrid.ZQuery1.ParamByName('ip5').AsString)
         then
         begin
          StatusBar1.Panels[0].Text:= 'Incrementando registros';
          inc(lreg);
          //Valor no BD
          valorbd := DModuleGrid.ZQuery1.FieldByName('valor').AsFloat;
          //Valor no TXT
          valortxt := StrToFloat(copy(lTemp, 109, 14))/100;
          //Diferença nos valores
          if (valorbd <> valortxt) then
           begin
            inc(treg);
            if (valor = '') then
             begin
              valor := IntToStr(0);
             end
             else
             valor := IntToStr(treg);
           end;
         end
         else
         begin
         inc(dc);

         StatusBar1.Panels[0].Text:= 'Próximo registro';
         end;
         DModuleGrid.ZQuery1.Next;
     end;
   end;
 end;
  Label1.Caption := 'Divergências '+'Valor'+' Cupons: '+valor;
  Label7.Caption := 'Divergência de cupons: '+ IntToStr(dc);
  StatusBar1.Panels[0].Text:= 'Existem '+ IntToStr(lreg) + ' linhas de Cupons(E14)';
  CloseFile(txt);
end;

Optimize Database Query and Compress Code

The context of the question is in the comments, but I will make it clear.

What I tried to do was a comparison of a .txt file with the data contained in the DB. I've been able to do the comparison with this code, remembering, the code works for the comparison, but it is not working for listing data in another DBGrid.

He is using the DBGrid just for comparison, not for listing. Any ideas?

    
asked by anonymous 15.05.2014 / 03:24

1 answer

1

Follow the code:

procedure TfrmGrid.btnLoadCupClick(Sender: TObject);
var
 txt: TextFile;
 treg, lreg, dc: integer;
 valortxt, valorbd : double;
 lTemp, valor, dtcompratxt, ccf, coo, ecf: String;
 dtcompra, dtc: TDateTime;
begin
lreg:= 1;
treg:= 0;
dc := 0;

StatusBar1.Panels[0].Text:= 'Abrindo arquivo de txt';
AssignFile(txt, frmSelection.FileListBox1.FileName);
Reset(txt);

 while not eof(txt) do
 begin
   Readln(txt, lTemp);
   if  (copy(lTemp, 1, 3) = 'E14') then
   begin
     //Query selecionando os campos do arquivo txt
     StatusBar1.Panels[0].Text:= 'Executando a query';
     dtcompra:=StrToDate(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+copy(lTemp,59,4));
     dtcompratxt:= FormatDateTime('dd/MM/yyyy', dtcompra);

     DModuleGrid.ZQuery1.Close;
     DModuleGrid.ZQuery1.SQL.Clear;
     DModuleGrid.ZQuery1.SQL.Add('SELECT * FROM tdcupant');
     DModuleGrid.ZQuery1.SQL.Add('WHERE numcupom = :co2 AND ccf = :cc3 AND dtcompra = :dtc4 AND impcaixa = :ip5');
     DModuleGrid.ZQuery1.ParamByName('co2').AsString := copy(lTemp,53,6);
     DModuleGrid.ZQuery1.ParamByName('cc3').AsString := copy(lTemp,47,6);
     DModuleGrid.ZQuery1.ParamByName('ip5').AsString := copy(lTemp,4,20);
     DModuleGrid.ZQuery1.ParamByName('dtc4').AsDate := StrToDate(dtcompratxt);
     DModuleGrid.ZQuery1.Open;

     if not (DModuleGrid.ZQuery1.IsEmpty) then
     begin
       StatusBar1.Panels[0].Text:= 'Executando o loop de consulta/comparação';
         //condições para pegar o valor
         if  (DModuleGrid.ZQuery1.FieldByName('numcupom').AsString = DModuleGrid.ZQuery1.ParamByName('co2').AsString)
         and (DModuleGrid.ZQuery1.FieldByName('ccf').AsString = DModuleGrid.ZQuery1.ParamByName('cc3').AsString)
         and (DModuleGrid.ZQuery1.FieldByName('dtcompra').AsDateTime = DModuleGrid.ZQuery1.ParamByName('dtc4').AsDate)
         and (DModuleGrid.ZQuery1.FieldByName('impcaixa').AsString = DModuleGrid.ZQuery1.ParamByName('ip5').AsString)
         then
         begin
          StatusBar1.Panels[0].Text:= 'Incrementando registros';
          inc(lreg);
          //Valor no BD
          valorbd := DModuleGrid.ZQuery1.FieldByName('valor').AsFloat;
          //Valor no TXT
          valortxt := StrToFloat(copy(lTemp, 109, 14))/100;

          //divergências nos valores após as condições
          if (valorbd <> valortxt) then
           begin
             with DModuleGrid.ZQuery2 do
             begin
             //comparação de divergências (Identifica se sao mesmo diferentes e grava os valores no banco de dados)
               coo := copy(lTemp,53,6);
               ccf := copy(lTemp,47,6);
               ecf := copy(lTemp,4,20);
               dtc := StrToDate(dtcompratxt);
               valortxt := StrToFloat(copy(lTemp, 109, 14))/100;

               DModuleGrid.ZQuery2.Close;
               DModuleGrid.ZQuery2.SQL.Clear;
               DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES ( "'+DateToStr(dtc)+'", "'+ecf+'", "'+coo+'", "'+ccf+'", "'+FloatToStr(valortxt)+'")');
               DModuleGrid.ZQuery2.ExecSQL;

               DModuleGrid.ZQuery2.Close;
               DModuleGrid.ZQuery2.SQL.Clear;
               DModuleGrid.ZQuery2.SQL.Add('SELECT * FROM tabc460');
               DModuleGrid.ZQuery2.SQL.Add('WHERE numcupom = :tcoo AND ccf = :tccf AND dtcompra = :tdtc AND impcaixa = :tecf AND valor = :tvalor');
               DModuleGrid.ZQuery2.ParamByName('tcoo').AsString := coo;
               DModuleGrid.ZQuery2.ParamByName('tccf').AsString := ccf;
               DModuleGrid.ZQuery2.ParamByName('tecf').AsString := ecf;
               DModuleGrid.ZQuery2.ParamByName('tdtc').AsDate := dtc;
               DModuleGrid.ZQuery2.ParamByName('tvalor').AsFloat := valortxt;
               DModuleGrid.ZQuery2.Open;

               DModuleGrid.ZQuery2.Next;
             end;
             //incremento caso seja divergente
             inc(treg);
             //tratamento de erro
             if (valor = '') then
             begin
              valor := IntToStr(0);
             end
             else
             begin
              valor := IntToStr(treg);
             end;
           end;
         end
         else
         begin
           //caso as condições não sejam aceitas, elas sao incrementadas na variavel dc
           inc(dc);
           StatusBar1.Panels[0].Text:= 'Próximo registro';
         end;
         DModuleGrid.ZQuery1.Next;
     end;
   end;
 end;
  //Seleção da base depois da comparação
  DModuleGrid.ZQuery1.Close;
  DModuleGrid.ZQuery1.SQL.Clear;
  DModuleGrid.ZQuery1.SQL.Add('SELECT * FROM tdcupant');
  DModuleGrid.ZQuery1.Open;

  DModuleGrid.ZQuery2.Close;
  DModuleGrid.ZQuery2.SQL.Clear;
  DModuleGrid.ZQuery2.SQL.Add('SELECT * FROM tabc460 LIMIT 0,'+IntToStr(treg));
  DModuleGrid.ZQuery2.Open;

  //apresenta valores divergentes nas labels
  Label1.Caption := 'Divergências '+'Valor'+' Cupons: '+valor;
  Label7.Caption := 'Divergência de cupons: '+ IntToStr(dc);
  StatusBar1.Panels[0].Text:= 'Existem '+ IntToStr(lreg) + ' linhas de Cupons(E14)';
  CloseFile(txt);
end;

Sorry for the lack of explanation, but follow what I wanted in the code comments. The dbgrids are connected to their respective datasources .

This is just the code made on the arm to show its functions.

If you have any improvement, please feel free.

    
15.05.2014 / 18:12