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 |
Partager