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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
| select * from(
SELECT A.CT_Num,A.CT_Intitule,a.CO_Nom,a.CT_CodeRegion,a.ct_pays,(tiersD+tiersAND-tiersC-tiersANC) as SOLDE,montant as REGLEMENT,
MontantTTC,JM_DATE,RG_DATE,dates as DateBL,tiersD,tiersC,tiersAND,tiersANC,
--/*(CT_Intitule + '('+space(2)+CT_NUM + ')'+ '-->>'+CO_Nom) as ct_num*/,
(tiersD-tiersC) as SOLDEMVT,(tiersAND-tiersANC) as SOLDEAN,
CASE
WHEN MONTH(JM_DATE)=1 THEN '01-JANVIER'
WHEN MONTH(JM_DATE)=2 THEN '02-FEVRIER'
WHEN MONTH(JM_DATE)=3 THEN '03-MARS'
WHEN MONTH(JM_DATE)=4 THEN '04-AVRIL'
WHEN MONTH(JM_DATE)=5 THEN '05-MAI'
WHEN MONTH(JM_DATE)=6 THEN '06-JUIN'
WHEN MONTH(JM_DATE)=7 THEN '07-JUILLET'
WHEN MONTH(JM_DATE)=8 THEN '08-AOÛT'
WHEN MONTH(JM_DATE)=9 THEN '09-SEPTEMBRE'
WHEN MONTH(JM_DATE)=10 THEN '10-OCTOBRE'
WHEN MONTH(JM_DATE)=11 THEN '11-NOVEMBRE'
WHEN MONTH(JM_DATE)=12 THEN '12-DECEMBRE'
ELSE '0'
END as MOIS,YEAR(JM_DATE) as Annee,
CASE
WHEN a.CT_Num='410VEO' THEN 1
WHEN a.CT_Num='410SNS' THEN 2
WHEN a.CT_Num='410MAX' THEN 3
WHEN a.CT_Num='410GJO' THEN 4
WHEN a.CT_Num='410BUC' THEN 5
WHEN a.CT_Num='410WOL' THEN 6
WHEN a.CT_Num='410GLD' THEN 7
WHEN a.CT_Num='410TRY' THEN 8
WHEN a.CT_Num='410TMK' THEN 9
WHEN a.CT_Num='410SMY' THEN 10
WHEN a.CT_Num='410KOM' THEN 11
WHEN a.CT_Num='410CHA' THEN 12
WHEN a.CT_Num='410GTS' THEN 13
WHEN a.CT_Num='410SAU' THEN 14
WHEN a.CT_Num='410LGD' THEN 15
WHEN a.CT_Num='410ALB' THEN 16
WHEN a.CT_Num='410ABK' THEN 17
WHEN a.CT_Num='410MDD' THEN 18
WHEN a.CT_Num='410FTJ' THEN 19
WHEN a.CT_Num='410GKO' THEN 20
WHEN a.CT_Num='410FAE' THEN 21
WHEN a.CT_Num='410YMD' THEN 22
WHEN a.CT_Num='410NEW' THEN 23
WHEN a.CT_Num='410TMM' THEN 24
WHEN a.CT_Num='410GET' THEN 25
WHEN a.CT_Num='410ATC' THEN 26
WHEN a.CT_Num='410DDA' THEN 27
ELSE '0'
END as NUM
FROM
(SELECT fCptT.CT_Num,fCptT.CT_Intitule,JM_DATE,CO_Nom,ct_pays,ct_coderegion,
SUM(case when /*year(JM_DATE)= '2021' AND*/ fEcg.EC_ANType = 0 AND fEcg.EC_Sens = 0 then fEcg.EC_Montant else 0 end) tiersD,
SUM(case when /*year(JM_DATE)= '2021' AND*/ fEcg.EC_ANType = 0 AND fEcg.EC_Sens = 1 then fEcg.EC_Montant else 0 end) tiersC,
SUM(case when /*year(JM_DATE)= '2021' AND*/ fEcg.EC_ANType > 0 AND fEcg.EC_Sens = 0 then fEcg.EC_Montant else 0 end) tiersAND,
SUM(case when /*year(JM_DATE)= '2021' AND*/ fEcg.EC_ANType > 0 AND fEcg.EC_Sens = 1 then fEcg.EC_Montant else 0 end) tiersANC
FROM F_COMPTET fCptT
INNER JOIN F_ECRITUREC fEcg
ON fCptT.cbCT_Num = fEcg.cbCT_Num
INNER JOIN F_JOURNAUX fCjr
ON fEcg.cbJO_Num = fCjr.cbJO_Num
LEFT JOIN F_COLLABORATEUR CO
ON CO.CO_NO=fCptT.CO_NO
WHERE --year(JM_DATE)= '2021'AND
fCptT.CT_Type = 0
AND ISNULL(EC_Norme,0) = 0
AND JO_Type BETWEEN 0 AND 3
and (fEcg.CT_Num='410VEO' or fEcg.CT_Num='410SNS' OR fEcg.CT_Num='410MAX' OR fEcg.CT_Num='410GJO' OR fEcg.CT_Num='410BUC'
OR fEcg.CT_Num='410WOL' OR fEcg.CT_Num='410GLD' OR fEcg.CT_Num='410TRY' OR fEcg.CT_Num='410TMK' OR fEcg.CT_Num='410SMY'
OR fEcg.CT_Num='410KOM' OR fEcg.CT_Num='410CHA' OR fEcg.CT_Num='410GTS' OR fEcg.CT_Num='410SAU' OR fEcg.CT_Num='410LGD'
OR fEcg.CT_Num='410ALB' OR fEcg.CT_Num='410ABK' OR fEcg.CT_Num='410MDD' OR fEcg.CT_Num='410FTJ' OR fEcg.CT_Num='410GKO'
OR fEcg.CT_Num='410FAE' OR fEcg.CT_Num='410YMD'OR fEcg.CT_Num='410NEW' OR fEcg.CT_Num='410TMM' OR fEcg.CT_Num='410GET'
OR fEcg.CT_Num='410ATC' OR fEcg.CT_Num='410DDA')
GROUP BY fCptT.CT_Num,fCptT.CT_Intitule,fCptT.CT_Saut,JM_DATE,CO_Nom,ct_pays,fCptT.CT_CodeRegion
)a
left join(
select * from(
select isnull (sum (dl.DL_MontantTTC),0 ) as MontantTTC, te.ct_num as dotiers,isnull(de.DO_Date,'') Dates
from F_DOCENTETE de
inner join F_DOCLIGNE dl on de.DO_Tiers= dl.CT_Num
INNER join f_comptet te on te.CT_Num = de.DO_Tiers
where (de.DO_Type='3' and de.DO_Statut='0' /*and dl.DO_Date >'30-08-2022'*/)group by te.ct_num ,de.DO_Date
)c
left join (
SELECT CO_Nom,YEAR(RG_Date) as Annee,CT_Num, CT_Intitule AS CLIENT,RG_Libelle AS LIBELLE,
CAST (RG_Montant as int) as Montant,RG_Date,JO_Num as JOURNAL,CASE
WHEN MONTH(RG_Date)=1 THEN '01-JANVIER'
WHEN MONTH(RG_Date)=2 THEN '02-FEVRIER'
WHEN MONTH(RG_Date)=3 THEN '03-MARS'
WHEN MONTH(RG_Date)=4 THEN '04-AVRIL'
WHEN MONTH(RG_Date)=5 THEN '05-MAI'
WHEN MONTH(RG_Date)=6 THEN '06-JUIN'
WHEN MONTH(RG_Date)=7 THEN '07-JUILLET'
WHEN MONTH(RG_Date)=8 THEN '08-AOÛT'
WHEN MONTH(RG_Date)=9 THEN '09-SEPTEMBRE'
WHEN MONTH(RG_Date)=10 THEN '10-OCTOBRE'
WHEN MONTH(RG_Date)=11 THEN '11-NOVEMBRE'
WHEN MONTH(RG_Date)=12 THEN '12-DECEMBRE'
ELSE '0'
END as MOIS FROM F_CREGLEMENT cr
inner join F_comptet ct on ct.CT_NumPayeur= cr.CT_NumPayeur
left join F_COLLABORATEUR col on col.CO_No= ct.CO_No where RG_Compta=0 /*and rg_Date >'30-01-2021'*/
)b on b.CT_Num=c.dotiers
)G on G.CT_NUM=a.CT_Num )b |
J'aimerais savoir � quoi sert ce code
R�pondre avec citation
Partager