Fórum Rotina ruim!!!!! #183987

Ola colegas boa tarde

a rotina abaixo serve para gerar relatorios diversos
do tipo:

clientexproduto
produtoxcliente
classificação clientexproduto
distritoxproduto

entre outras


qo que, quando peço para listar toda a base ela demora uma eternidade(mais de 30 minutos).

Sei que diram para usar SQL mas a base é em dbf

o sistema ja esta sendo migrado para FB/IB mas até lá!?

bom se alguem tiver paciencia de ver e puder me ajudar,

em como melhorar a rotina eu agradeço

talvez algo se deva ser analisado primeiro.. para evitar voltas a toa.

obrigado pela atenção



Var iCounter: LongInt;
sEstado, sMunicipio, sCodZona, sDesZona,sCla, sClaNsg, sCSG, sNsg1, sNsg2, sNsg3, sNsg4, sNsg5: String;
{fDescIcms, fIrenda,} fEncPro, fEncIpi, fIpi: Double;
sCODEST,sNOMEEST:string;
pegames:integer;
fQTE,fVLR : double;
begin
btnConfirma.Enabled := False;

DesabilitaComp( self );

dbgrid1.enabled := True;
{* FECHA/ZERA/INDEXA/ABRE TABELA TEMPORARIA*}
tblTemp.Close;
tblTemp.EmptyTable;
tblTemp.IndexName := ´IECPRO´;
tblTemp.Open;

{*FORMATA CAMPOS COM VALORES*}
TFloatField( tblTemp.FieldByName( ´PERCENTUAL´ ) ).EditFormat := ´#,0.00´;
TFloatField( tblTemp.FieldByName( ´PERCENTUAL´ ) ).Precision := 5;
TFloatField( tblTemp.FieldByName( ´PERCENTUAL´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´PERCVLR´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´PERCVLR´ ) ).Precision := 5;
TFloatField( tblTemp.FieldByName( ´PERCVLR´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´VLRTOT´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´VLRTOT´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´TOTPRO´ ) ).EditFormat := ´#,0.000´;
TFloatField( tblTemp.FieldByName( ´TOTPRO´ ) ).DisplayFormat := ´,0.000´;
if rdgOpcao.ItemIndex = 0 then
begin
TFloatField( tblTemp.FieldByName( ´1´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´1´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´2´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´2´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´3´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´3´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´4´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´4´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´5´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´5´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´6´ ) ).EditFormat := ´#,0.00´;
TFloatField( tblTemp.FieldByName( ´6´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´7´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´7´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´8´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´8´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´9´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´9´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´10´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´10´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´11´ ) ).EditFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´11´ ) ).DisplayFormat := ´,0.00´;
TFloatField( tblTemp.FieldByName( ´12´ ) ).EditFormat := ´#,0.00´;
TFloatField( tblTemp.FieldByName( ´12´ ) ).DisplayFormat := ´,0.00´;
end;


{* FECHA/ZERA/INDEXA/ABRE TABELA TEMPORARIA*}
tblTempRes.Close;
tblTempRes.EmptyTable;
tblTempRes.IndexName := ´IECPRO´;

{* DESABILITA CONTROLES DA TABELA TEMPORARIA*}
tblTemp.DisableControls;
Dm.TArqNfis.DisableControls;
Dm.TArqPrnf.DisableControls;

{* CONTA REGISTROS*}
iTotRec := dm.TarqNfis.RecordCount;

Dm.TArqClie.IndexName := ´ICODV1C´;
Dm.TArqMer.IndexName := ´ICODME´;
Dm.TArqNfis.IndexName := ´INFSDAT´;

Dm.TArqNfis.SetKey;
Dm.TArqNfis.FieldByName( ´EMP´ ).AsString := sEmpresa;
Dm.TArqNfis.fieldByName( ´DAT´ ).AsString := mdtPerIni.Text;
Dm.TArqNfis.GotoNearest;


iCounter := 1;
{* INICIA LAÇO CHECANDO EMPRESA;DATAINICIAL/DATAFINAL*}
While ( Dm.TArqNfis.FieldByName( ´EMP´ ).AsString = sEmpresa ) and
( StrToDate( dm.TArqNfisDAT.AsString ) >= StrToDate( mdtPerIni.Text ) ) and
( StrToDate( dm.TArqNfisDAT.AsString ) <= StrToDate( mdtPerFim.Text ) ) and
( not Dm.TArqNfis.Eof ) do
Begin
Dm.Indicator( itotRec, iCounter, ´Aguarde - Processando´ );
{* CHECA CLIENTE*}
If ( Dm.TArqNfis.fieldByName( ´CLI´ ).AsString < mdtCliIni.Text ) or
( Dm.TArqNfis.fieldByName( ´CLI´ ).AsString > mdtCliFim.Text ) Then
Begin
Dm.TArqNfis.Next;
Inc( iCounter );
Continue;
End;

//Verifica abaixo se (S)oma ou (D)eduz, caso não for salta pra próxima nota.
dm.TArqNops.FindKey([ Dm.TArqNfisNOP.AsString ]);
If Pos( dm.tArqNopsEFEITOVEN.AsString, ´SD´ ) = 0 Then
Begin
Dm.TArqNfis.Next;
Inc( iCounter );
Continue;
End;

//Se Soma fator positivo para somar notas abaixo, se não subtrai valor da nota.
If ( dm.tArqNopsEFEITOVEN.AsString = ´S´ ) Then
fFatorVen := 1
Else fFaTorVen := -1;


// Início Código Master - Venda Direta
if chkLiga.Checked Then
Begin
sVen_Direta := Dm.VenDireta(Dm.TArqNfis, ´EMP´, ´CLI´);
End Else
Begin
sVen_Direta := Dm.TArqnfis.FieldByName(´CLI´).AsString;;
End;
Dm.TArqClie.SetKey;
Dm.TArqClieV0.AsSTring := sEmpresa;
Dm.TArqClieV1.AsString := sVen_Direta;
If not Dm.TARqClie.GotoKey Then
Begin
sCSG := ´´;
sCla := ´´;
sDesCli := ´Código não Cadastrado: ´ + sVen_Direta;
sEstado := ´´;
sMunicipio := ´´;
End
Else
Begin
sCSG := Dm.TArqClieTCL.AsSTring;
sCla := Dm.TArqClieTCL2.AsSTring;
sDesCli := Dm.TArqClieV2.AsString;
sEstado := Dm.TArqClieV5.AsString;
sMunicipio := Dm.TArqClieV4.AsString;
End;
// Fim Código Master - Venda Direta


If ( Dm.TArqClieTCL.AsSTring < mdtSegIni.Text ) or
( Dm.TArqClieTCL.AsSTring > mdtSegFim.Text ) Then
Begin
dm.TArqNfis.Next;
Inc( iCounter );
Continue;
End;

If ( Dm.TArqClieTCL2.AsSTring < mdtClaIni.Text ) or
( Dm.TArqClieTCL2.AsSTring > mdtClaFiN.Text ) Then
Begin
dm.TArqNfis.Next;
Inc( iCounter );
Continue;
End;

If ( Dm.TArqClieZONA.AsSTring < mdtDistritoIni.Text ) or
( Dm.TArqClieZONA.AsSTring > mdtDistritoFin.Text ) Then
Begin
dm.TArqNfis.Next;
Inc( iCounter );
Continue;
End;

sNsg1 := ´´;
sNsg2 := ´´;
sNsg3 := ´´;
sNsg4 := ´´;
sNsg5 := ´´;
If Dm.TarqTcli.findkey( [sCsg] ) Then
Begin
sNsg1 := Dm.TarqTcliNSG.AsString;
sNsg2 := Dm.TarqTcliNSG2.AsString;
sNsg3 := Dm.TarqTcliNSG3.AsString;
sNsg4 := Dm.TarqTcliNSG4.AsString;
sNsg5 := Dm.TarqTcliNSG5.AsString;
End;

sClaNsg := ´´;
If Dm.TarqCcli.findkey( [sCla] ) Then
Begin
sClaNsg := Dm.TarqCcliNSG.AsString;
End;

//**************** INCLUIDO : ALEXANDRE ******************************
If ( Dm.TArqClieREP.AsSTring < mdtRepIni.Text ) or
( Dm.TArqClieREP.AsSTring > mdtRepFim.Text ) Then
Begin
dm.TArqNfis.Next;
Inc( iCounter );
Continue;
End;
//*********************************************************************

sCodZona := FrmPrincip.BuscaGer(DM.TArqZona, DM.DsArqZona, ´ICODZON´, ´CODZONA´, ´´, ´DESZONA´,
Dm.TArqClieZONA.AsString, ´´, ´CODZONA´, False);
sDesZona := IIF( sCodZona <> ´´ , Dm.TArqZonaDESZONA.AsString, ´Não Encontrado´ );


sCODEST := FrmPrincip.BuscaGer(DM.TArqEsta, DM.DsArqEsta, ´IESTADO´, ´CODEST´, ´´, ´´,
Dm.TArqClieV5.AsString, ´´, ´CODEST´, False);
// sNOMEEST := IIF( sCODEST <> ´´ , Dm.TArqEstaNomeEst.AsString, ´´);


If ( Dm.TArqnfisSOMAFRE.AsString = ´S´ ) Then
fFrete := Dm.TArqNfisFRE.AsFloat
Else
fFrete := 0;

If ( Dm.TArqnfisINCIDEFRE.AsString = ´S´) and ( fFrete > 0 ) and
( Dm.TarqnfisICMSFRE.AsInteger > 0 ) Then
Begin
fIcmsFre := fFrete * ( Dm.TarqnfisICMSFRE.AsInteger / 100 );
fFreteBas := fFrete;
End Else
Begin
fIcmsFre := 0;
fFreteBas := 0;
End;

fDescIcms := dm.TArqNfis.FieldByName(´DESCICMS´).AsFloat;
fIrenda := dm.TArqNfis.FieldByName(´IRENDA´).AsFloat;
fServico := dm.TArqNfisSERVICOS.asFloat;

Dm.TArqPrnf.IndexName := ´IECNF´;
Dm.TArqPrnf.SetKey;
Dm.TArqPrnf.FieldByName( ´NEM´ ).AsString := Dm.TArqNfis.fieldByName( ´EMP´ ).AsString;
Dm.TArqPrnf.FieldByName( ´NCL´ ).AsString := Dm.TArqNfis.fieldByName( ´CLI´ ).AsString;
Dm.TArqPrnf.FieldByName( ´NNF´ ).AsString := Dm.TArqNfis.fieldByName( ´NFI´ ).AsString;
Dm.TArqPrnf.GotoKey;

While ( Dm.TArqPrnf.FieldByName( ´NEM´ ).AsString +
Dm.TArqPrnf.FieldByName( ´NCL´ ).AsString +
Dm.TArqPrnf.FieldByName( ´NNF´ ).AsString =
Dm.TArqNfis.fieldByName( ´EMP´ ).AsString +
Dm.TArqNfis.fieldByName( ´CLI´ ).AsString+
Dm.TArqNfis.fieldByName( ´NFI´ ).AsString ) and
( not Dm.TArqPrnf.eof ) do
Begin



if ( Dm.TArqPrnfPRO.AsString < mdtProIni.Text ) or
( Dm.TArqPrnfPRO.AsString > mdtProFim.Text ) Then
Begin
Dm.TArqPrnf.Next;
Continue;
End;

if ( Dm.TArqPrnfNRE.AsString < mdtRepIni.Text ) or
( Dm.TArqPrnfNRE.AsString > mdtRepFim.Text ) Then
Begin
Dm.TArqPrnf.Next;
Continue;
End;

fValInd := FrmPrincip.BuscaValInd( mdtIndice.Text );

sProAna := Copy( Dm.TArqPrnfPRO.AsString, 1, StrtoInt( mdtAgrupa.Text ) );

If Not TblTemp.FindKey([ Dm.TArqNfis.fieldByName( ´EMP´ ).AsString,
sVen_Direta, sProAna ]) Then
Begin
ManReg( ´I´, tblTemp );
tblTemp.FieldByName( ´CODEMP´ ).AsString := Dm.TArqNfis.fieldByName( ´EMP´ ).AsString;
tblTemp.FieldByName( ´NOP´ ).AsString := Dm.TArqNfisNOP.AsString;
tblTemp.FieldByName( ´CODCLI´ ).AsString := sVen_Direta;
tblTemp.FieldByName( ´DESCLI´ ).AsString := sDesCli;
tblTemp.FieldByName( ´ESTADO´ ).AsString := sEstado;
tblTemp.FieldByName( ´MUNICIPIO´).AsString := sMunicipio;
tblTemp.FieldByName( ´CODPRO´ ).AsString := sProAna;

tblTemp.FieldByName( ´DNF´ ).AsString := Dm.TArqPrnfDNF.AsString;
tblTemp.FieldByName( ´PED´ ).AsString := Dm.TArqPrnfPED.AsString;
tblTemp.FieldByName( ´NFI´ ).AsString := Dm.TArqPrnfNNF.AsString;

tblTemp.FieldByName( ´CODZONA´ ).AsString := sCodZona;
tblTemp.FieldByName( ´DESZONA´ ).AsString := sDesZona;
tblTemp.FieldByName( ´CSG´ ).AsString := sCsg;
tblTemp.FieldByName( ´NSG1´ ).AsString := sNsg1;
tblTemp.FieldByName( ´NSG2´ ).AsString := sNsg2;
tblTemp.FieldByName( ´NSG3´ ).AsString := sNsg3;
tblTemp.FieldByName( ´NSG4´ ).AsString := sNsg4;
tblTemp.FieldByName( ´NSG5´ ).AsString := sNsg5;
tblTemp.FieldByName( ´CLA´ ).AsString := sCla;
tblTemp.FieldByName( ´CLANSG´ ).AsString := sClaNsg;
//****************INCLUIDO:ALEANDRE*******************************
tblTemp.FieldByName( ´V1´ ).AsString := DM.TArqClieREP.AsString;
tblTemp.FieldByName( ´v2´ ).AsString := DM.TArqClieRepresentante.AsString;
tblTemp.FieldByName( ´CODEST´ ).AsString := sCODEST;
tblTemp.FieldByName( ´NOMEEST´ ).AsString := sNOMEEST;

//***********************************************************************

Dm.TArqMer.FindKey([Dm.TArqPrnfPRO.AsString]);

tblTemp.FieldByName( ´UNIDADE´ ).AsString:= Unidade( StrToIntDef( Dm.TArqMerA3.AsString, 0 ) );

tblTemp.FieldByName( ´DESPRO´ ).AsString := Dm.Agrupa( StrtoInt( mdtAgrupa.Text ),
Dm.TArqPrnfPRO.AsString,
sEmpresa );

End Else ManReg(´A´, tblTemp );

pegames := StrToInt( Copy( Dm.TArqPrnfDNF.AsString , 4, 2) );


tblTemp.FieldByName( ´TOTPRO´ ).AsFloat := tblTemp.FieldByName( ´TOTPRO´ ).AsFloat +
Dm.SomaQteCe(sSomaCe, fFatorVen);
//Retorna Valor ja somado com Fator - ou +.

if rdgOpcao.ItemIndex = 1 then
tblTemp.FieldByName( IntToStr(pegames) ).Asfloat := tblTemp.FieldByName( IntToStr(pegames) ).Asfloat+
Dm.SomaQteCe(sSomaCe, fFatorVen);

fIPI := 0;
fEncIpi := 0;
fEncPro := 0;

If chkSomaIPI.Checked Then
Begin
fIpi := Dm.TArqPrnfIPI.AsFloat;
fEncIPI := Dm.TArqPrnfENCIPI.AsFloat;
End;

If chkSomaEnc.Checked Then
Begin
fEncPro := Dm.TArqPrnfENCPRO.AsFloat;
End;

If ( chkSomaEnc.Checked ) and ( sSomaCe = ´S´ ) Then
fEncPro := fEncPro + Dm.TArqPrnfENCPROCE.AsFloat;


tblTemp.FieldByName( ´VLRTOT´ ).AsFloat := tblTemp.FieldByName( ´VLRTOT´ ).AsFloat +
Dm.SomaVlrCe(sSomaCe, fFatorVen, fValInd)+
fIpi + fEncIPI + fEncPro + fFrete +
fServico - fDescIcms - fIrenda;
if rdgOpcao.ItemIndex = 0 then
tblTemp.FieldByName( IntToStr(pegames) ).Asfloat := tblTemp.FieldByName( IntToStr(pegames) ).Asfloat+
Dm.SomaVlrCe(sSomaCe, fFatorVen, fValInd)+
fIpi + fEncIPI + fEncPro + fFrete +
fServico - fDescIcms - fIrenda;



tblTemp.FieldByName( ´IPI´ ).AsFloat := Dm.TArqPrnfIPI.AsFloat;

tblTemp.FieldByName( ´ENCFIN´ ).AsFloat := Dm.TArqPrnfENCFIN.AsFloat;

tblTEmp.Post;

fFrete := 0;
fDescIcms := 0;
fiRenda := 0;
fServico := 0;

Dm.tArqPrnf.Next;
End;
Inc( iCounter );
Dm.TarqNfis.Next;
End;
(* indicador não totaliza por estar indexado, colocado o comando a abaixo para
fechar finalizar a barra de progressão e fechar o indicador. *)
If Assigned( FrmProgres ) Then FrmProgres.Close;

fTGeralPro := 0;
fTGeralVlr := 0;

Manreg( ´F´, tblTemp );
While not tblTemp.Eof do
Begin
If FrmAAnualCp.tblTemp.FieldByName( ´TOTPRO´ ).AsFloat = 0 Then
Begin
FrmAAnualCp.tblTemp.Delete;
Continue;
End;
fTGeralPro := fTGeralPro + FrmAAnualCp.tblTemp.FieldByName( ´TOTPRO´ ).AsFloat;
fTGeralVlr := fTGeralVlr + FrmAAnualCp.tblTemp.FieldByName( ´VLRTOT´ ).AsFloat;
Manreg( ´N´, tblTemp );
End;

Manreg( ´F´, tblTemp );
While not tblTemp.Eof do
Begin
Manreg( ´A´, tblTemp );
FrmAAnualCp.tblTemp.FieldByName( ´PERCENTUAL´ ).AsFloat := stf( FloatToStrF( (( FrmAAnualCp.tblTemp.FieldByName( ´TOTPRO´ ).AsFloat/
fTGeralPro ) * 100), fffixed, 6, 2 ) );
Try
FrmAAnualCp.tblTemp.FieldByName( ´PERCVLR´ ).AsFloat := stf( FloatToStrF( (( FrmAAnualCp.tblTemp.FieldByName( ´VLRTOT´ ).AsFloat/
fTGeralVlr ) * 100), fffixed, 6, 2 ) );
Except
FrmAAnualCp.tblTemp.FieldByName( ´PERCVLR´ ).AsFloat := 0;
End;
Manreg( ´G´, tblTemp );
Manreg( ´N´, tblTemp );
End;

tblTemp.EnableControls;
Dm.TArqNfis.EnableControls;
Dm.TArqPrnf.EnableControls;

If ( not tblTemp.Isempty ) Then
Begin
bMudaPagina := True;
pgcGeral.ActivePageIndex := 1;
bMudaPagina := False;
btnConfirma.Enabled := True;
cbxZebra.Enabled:= true;
End
Else
Begin
bMudaPagina := False;
HabilitaComp( self );
btnConfirma.Enabled := True;
Mensagem( ´Não há dados a serem Exibidos.´, ´Aviso´, 48 );
End;