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
| VIEW `vbudgets` AS
select `b`.`IDBUDGET` AS `IDBUDGET`,
`b`.`MTBREVISE` AS `MTBREVISE`,
concat_ws(' ',( case when (`b`.`TB` = 'F') then concat_ws(' ',
'Fct.',
( select `r`.`LIBELLE`
from `rubriquesbf` `r`
where (`r`.`CODERUBBF` = `b`.`LIGNE`)
),
`b`.`LIBELLE`
)
when (`b`.`TB` = 'I') then concat_ws(' ',
'Inv.',
`b`.`LIBELLE`,
( case when (`b`.`POSTE` = '001') then 'Materiel'
when (`b`.`POSTE` = '002') then 'Logiciel'
when (`b`.`POSTE` = '003') then 'Prestations'
else '???'
end
)
)
else '???'
end
),
( case when (`b`.`CHAPITRE` = 'C') then '(CTB)'
when (`b`.`CHAPITRE` = 'R') then '(RTB)'
else '???'
end
)
) AS `LONGID`,
`b`.`TB` AS `TB`,
( select ifnull(sum(`v`.`MONTANT`),0)
from `dedvlb` `v`
where ((`b`.`IDBUDGET` = `v`.`IDBUDGET`)
and exists ( select `s`.`NOORDRE`
from (`ded` `d` join `dedsignatures` `s` )
where ((`v`.`IDDED` = `d`.`IDDED` )
and (`d`.`STATUT` not in ('S','A') )
and (`v`.`ANNEE` = `d`.`ANNEE` )
and (`d`.`IDDED` = `s`.`IDDED` )
and (`d`.`ANNEE` = `s`.`ANNEE` )
and (`d`.`UTIC` = `s`.`USERSIGNATAIRE`)
and (`s`.`SIGNATURE` = 'V')
)
)
)
) AS `MTENGAGE`,
( select ifnull(sum(`bcvlb`.`MONTANT`),0)
from `bcvlb`
where ((`b`.`IDBUDGET` = `bcvlb`.`IDBUDGET`)
and exists ( select `s`.`NOORDRE`
from ( `ded` `d` join `dedsignatures` `s` )
where ((`bcvlb`.`IDDED` = `d`.`IDDED` )
and ( `d`.`STATUT` not in ('S','A') )
and (`bcvlb`.`ANNEE` = `d`.`ANNEE` )
and ( `d`.`IDDED` = `s`.`IDDED` )
and ( `d`.`ANNEE` = `s`.`ANNEE` )
and ( `d`.`UTIC` = `s`.`USERSIGNATAIRE`)
and ( `s`.`SIGNATURE` = 'V' )
)
)
)
) AS `MTCOMMANDE`,
( select ifnull(sum(`facturesvlb`.`MONTANT`),0)
from `facturesvlb`
where ( (`b`.`IDBUDGET` = `facturesvlb`.`IDBUDGET`)
and exists ( select `s`.`NOORDRE`
from (`ded` `d` join `dedsignatures` `s`)
where ((`facturesvlb`.`IDDED` = `d`.`IDDED` )
and ( `d`.`STATUT` not in ('S','A') )
and (`facturesvlb`.`ANNEE` = `d`.`ANNEE` )
and ( `d`.`IDDED` = `s`.`IDDED` )
and ( `d`.`ANNEE` = `s`.`ANNEE` )
and ( `d`.`UTIC` = `s`.`USERSIGNATAIRE`)
and ( `s`.`SIGNATURE` = 'V' )
)
)
)
) AS `MTFACTURE`
from `tbudgets` `b`; |
Partager