Comparison works, but data listing does not

Asked

Viewed 245 times

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 DB. I managed to make the comparison with this code, remembering, the code works for comparison, but is not working for the data listing in Other Dbgrid.

It is using Dbgrid only for comparison and not for listing. Any idea?

delphi
  • 2

    The ping @usuario only works within the Comments for people involved in the post (question author, commentators, editors). Each Question should be self-sufficient by allowing anyone to answer. Please add details that better explain your problem.

  • 2

    @Ramonruan If this question is based on another, please put the link to understand the context.

  • @Andrey, Follow the link and a brief explanation of what I did, Excuse the mistake. Thank you, I hope you can help me.

  • @Crood, I know yes dear... kkk, is why the following, the problem is that the selection was coming with the WHERE clause and with parameters, consequently was just comparing the data, had no pure selection function. I’ll post the code so you’ll understand better

  • @Crood, haha, really, I was only able to finish after allmoçar, but I’m down there. Please take a look. ;]

1 answer

1


Follow the code, gentlemen:

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 stick to what I wanted in the code comments. The dbgrids are connected to their respective datasources.

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

If you have any improvement, please feel free.

  • @Crood, for sure!

  • @Crood, OK buddy, you got it, I’ll work on my questions. Thanks!!

  • I ask you to please accept your answer if it solved the problem. So we will have fewer questions answered with the "no answer" flag. Thanks!

Browser other questions tagged delphi

You are not signed in. Login or sign up in order to post.