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
| SELECT
DosStrCompta As Ccompta,
DosLngId As Dossier,
DosStrNom As Nom_Client,
PrjLngId As Projet,
PrjStrLibelle As Nom_Projet,
PrjDat2 As Validation,
PrjMonFacture As Facturé,
((PrjMonFacture) - (leproduit.IteMon2)) as Diff,
--MontantHTRemise
Sum(case when sousRemise.IteLngId is not null and remise.IteLngId is not null then
-- sous remise + remise //
((Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) - ((Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) * (sousRemise.IteMon2/100))) - (((Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) - ((Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) * (Cast( cast( sousRemise.IteMon2 as float)/100 as decimal (10,5))))) * (Cast( cast( remise.IteMon2 as float)/100 as decimal (10,5))))
when sousRemise.IteLngId is not null and remise.IteLngId is null then
-- sous remise //
(Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) - ((Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) * (Cast( cast( sousRemise.IteMon2 as float)/100 as decimal (10,5))))
when sousRemise.IteLngId is null and remise.IteLngId is not null then
-- remise//
(Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) - ((Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) * (Cast( cast( remise.IteMon2 as float)/100 as decimal (10,5))))
else (Cast( cast( leproduit.IteLng2 as float)/100 as decimal (10,2))*leproduit.IteMon2) end) as Montant
FROM
T_Projet INNER JOIN T_Item leproduit ON 'PJ' + convert(varchar,PrjLngId) = leproduit.IteStrIndex
Inner JOIN db_GENAPI.dbo.T_Produit ON leproduit.IteLng1=ProLngId
Inner JOIN db_GENAPI.dbo.T_Param ON ProLngSFamId=ParLngId
-- MontantRemisé
Left JOIN T_Item sousRemise ON 'PJ' + convert(varchar,PrjLngId) +'R' = sousRemise.IteStrIndex and ProLngSFamId=sousRemise.IteLng1 and sousRemise.IteMon2 is not null
Left JOIN T_Item remise ON 'PJ' + convert(varchar,PrjLngId) +'R' = remise.IteStrIndex and ParLng4=remise.IteLng1 and remise.IteMon2 is not null
-- Famille + SousFamille
Inner JOIN db_GENAPI.dbo.T_Param P2 ON db_GENAPI.dbo.T_Produit.ProLngSFamId = P2.ParLngId
Inner JOIN db_GENAPI.dbo.T_Param P3 ON P2.ParLng4 = P3.ParLngId
-- Etat du projet
Inner JOIN T_Param Etat on T_Projet.PrjLngEtatId = Etat.ParLngId
--Le Dossier
Inner join T_Dossier on T_Projet.PrjLngDosId = DosLngId
WHERE
Etat.ParStrNom = ?
And Year(PrjDat2) = ?
And Month(PrjDat2) = ?
And PrjDat2 is not null
And P3.ParStrNom not in ('MAINTENANCE','SERVICES COMPLEMENTAIRES')
And P3.ParStrNom not like '%ACCOMPAGNEMENT%'
And PrjStrLibelle not like 'Echange%'
And PrjStrLibelle not like 'Surcoût%'
And ProStrNom not like 'Frais de rejet bancaire'
And DosStrNom not like '%COMMANDES INTERNES%'
And DosStrNom not like 'Icare Test'
And ProStrNom not like 'Participation financière'
And (PrjMonFacture) - (leproduit.IteMon2) not between -1000 AND 1000
group by
DosStrCompta,
DosLngId,
DosStrNom,
PrjLngId,
PrjStrLibelle,
PrjDat2,
leproduit.IteMon2,
PrjMonFacture |
