1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
|
//remplir la table cp_reliq
with datamodule3 do
begin
//vider la table
query2.SQL.Clear;
query2.SQL.Add('delete from tot_Ap_tot_Cp');
query2.ExecSQL;
query2.SQL.Clear;
query2.SQL.Add('insert into tot_Ap_tot_Cp(NOM_WILAYA,Nom_commune,ANN,INTITULE_SEC,sous_secteur,TRIMESTRE,NB_operation,RAR_N_1,Autorisations_programme_AP,total_ap,Consommation_CP');
query2.SQL.Add(',Taux_consommation_CP,RAR,total_cp_N_1,Cedits_paiements_CP,total_cp,reliq_cp,rar_1_p,total_ap_p,total_cp_1_p,total_cp_p,reliq_cp_p) select');
query2.SQL.add(' NOM_WILAYA,null,ANN,null,null,null,sum(NB_operation),0,sum(Autorisations_programme_AP),0,sum(Consommation_CP),0,0,0,sum(Cedits_paiements_CP),0,0,0,0,0,0,0 from Etat_f_PSD_n_final where ANN='+'''' +RzComboBox40.Text+''' group by NOM_WILAYA,ann');
query2.ExecSQL;
ann := 1999;
while ann <= strtoint(rzcombobox40.Text) do
begin
query2.SQL.Clear;
//calculer le rarn-1
query2.SQL.Add('update tot_Ap_tot_Cp set RAR_N_1 = RAR,total_cp_N_1 = total_cp');
query2.ExecSQL;
query2.SQL.Clear;
//calculer le reliq_cp
adoquery1.SQL.Clear;
adoquery1.SQL.Add('UPDATE tot_Ap_tot_Cp SET reliq_cp = total_cp_N_1 - IIf(dsum("CONSM_CP","Etat_f_PSD","');
adoquery1.SQL.add(' NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann-1)+'''") is null,0,');
adoquery1.SQL.add('dsum("CONSM_CP","Etat_f_PSD","NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann-1)+'''"))');
adoquery1.ExecSQL;
/ecalculer le total_cp total_ap
adoquery1.SQL.Clear;
adoquery1.SQL.Add('UPDATE tot_Ap_tot_Cp SET total_cp = reliq_cp + IIf(dsum("CRE_PAY","Etat_f_PSD","');
adoquery1.SQL.add('NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann)+'''") is null,0,');
adoquery1.SQL.add('dsum("CRE_PAY","Etat_f_PSD","NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann)+'''")),');
adoquery1.SQL.Add('total_ap = RAR_N_1 + IIf(dsum("AUT_PGRM","Etat_f_PSD","');
adoquery1.SQL.add('NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann)+'''") is null,0,');
adoquery1.SQL.add('dsum("AUT_PGRM","Etat_f_PSD","NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann)+'''"))');
adoquery1.ExecSQL;
//calculer le rar
adoquery1.SQL.Clear;
adoquery1.SQL.Add('UPDATE tot_Ap_tot_Cp set rar = total_ap - IIf(dsum("CONSM_CP","Etat_f_PSD","');
adoquery1.SQL.add('NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann)+'''") is null,0,dsum("CONSM_CP","Etat_f_PSD","');
adoquery1.SQL.add('NOM_WILAYA = ''" & NOM_WILAYA & "'' and An = '+''''+inttostr(ann)+'''"))');
adoquery1.ExecSQL;
query2.SQL.Clear;
query2.SQL.add ('select * from tot_Ap_tot_Cp');
query2.Active := true;
query2.Active := false;
query2.SQL.Clear;
sleep(500);
ann := ann+1;
end;
query2.SQL.Add('SELECT NOM_WILAYA, NOM_WILAYA, NOM_WILAYA, NOM_WILAYA, Sum(NB_operation) AS NB_op, Sum(RAR_N_1) AS RAR_1, Sum(Autorisations_programme_AP) AS AP,Sum(TOTAL_AP)AS TOTALAP, sum(reliq_cp) as Reliquat_CP, ');
query2.SQL.Add('Sum(Cedits_paiements_CP) AS CP, sum(total_cp) as [Total CP], Sum(Consommation_CP) AS Con_CP, Round(Con_CP *100 /[Total CP],2) AS T_Consom, Sum(RAR) AS RAR1');
query2.SQL.Add('FROM tot_Ap_tot_Cp');
query2.SQL.Add('GROUP BY NOM_WILAYA');
query2.open();
end;
//affichage
.... |
Partager