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 122 123 124 125 126 127 128 129 130 131 132 133
| CREATE ALGORITHM=UNDEFINED DEFINER=root@`appian.solypse.com` SQL SECURITY DEFINER VIEW zz_projetaffich AS
select a.id AS id,a.nom AS nom,a.comm AS comm,
a.datecreation AS datecreation,a.idunique AS idunique,a.idrepertoire AS idrepertoire,a.versionoffre AS versionoffre,
(select if(isnull(sum(zz_patrimoinegenerique.quantitegenerique)),0,sum(zz_patrimoinegenerique.quantitegenerique))
from zz_patrimoinegenerique
where ((zz_patrimoinegenerique.idprojet = a.id) and (zz_patrimoinegenerique.codeobjet = 1)
and (zz_patrimoinegenerique.versionoffre = a.versionoffre)))
AS quantiteassociesup,
(select if(isnull(sum(zz_patrimoinegenerique.quantite)),0,sum(zz_patrimoinegenerique.quantite))
from zz_patrimoinegenerique
where ((zz_patrimoinegenerique.idprojet = a.id) and
(zz_patrimoinegenerique.codeobjet = 1) and (zz_patrimoinegenerique.idgenerique = '-1')))
AS quantitenonassociesup,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine
where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 1)
and (zz_patrimoine.codeobjet = 1))) AS
quantitetotvalsup,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine
where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 0)
and (zz_patrimoine.codeobjet = 1))) AS
quantitetotnonvalsup,
(select if(isnull(sum(zz_patrimoinegenerique.quantitegenerique)),0,sum(zz_patrimoinegenerique.quantitegenerique))
from zz_patrimoinegenerique where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 2)
and (zz_patrimoinegenerique.versionoffre = a.versionoffre)))
AS quantiteassociecon,
(select if(isnull(sum(zz_patrimoinegenerique.quantite)),0,sum(zz_patrimoinegenerique.quantite))
from zz_patrimoinegenerique where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 2)
and (zz_patrimoinegenerique.idgenerique = '-1')))
AS quantitenonassociecon,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 1)
and (zz_patrimoine.codeobjet = 2)))
AS quantitetotvalcon,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 0)
and (zz_patrimoine.codeobjet = 2)))
AS quantitetotnonvalcon,
(select if(isnull(sum(zz_patrimoinegenerique.quantitegenerique)),0,sum(zz_patrimoinegenerique.quantitegenerique))
from zz_patrimoinegenerique
where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 3)
and (zz_patrimoinegenerique.versionoffre = a.versionoffre)))
AS quantiteassocielum,
(select if(isnull(sum(zz_patrimoinegenerique.quantite)),0,sum(zz_patrimoinegenerique.quantite))
from zz_patrimoinegenerique where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 3)
and (zz_patrimoinegenerique.idgenerique = '-1'))) AS quantitenonassocielum,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 1)
and (zz_patrimoine.codeobjet = 3)))
AS quantitetotvallum,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id) and (zz_patrimoine.valide = 0)
and (zz_patrimoine.codeobjet = 3)))
AS quantitetotnonvallum,
(select if(isnull(sum(zz_patrimoinegenerique.quantitegenerique)),0,sum(zz_patrimoinegenerique.quantitegenerique))
from zz_patrimoinegenerique where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 4)
and (zz_patrimoinegenerique.versionoffre = a.versionoffre)))
AS quantiteassociesou,
(select if(isnull(sum(zz_patrimoinegenerique.quantite)),0,sum(zz_patrimoinegenerique.quantite))
from zz_patrimoinegenerique
where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 4)
and (zz_patrimoinegenerique.idgenerique = '-1'))) AS
quantitenonassociesou,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine
where ((zz_patrimoine.idprojet = a.id) and (zz_patrimoine.valide = 1)
and (zz_patrimoine.codeobjet = 4))) AS quantitetotvalsou,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id) and (zz_patrimoine.valide = 0)
and (zz_patrimoine.codeobjet = 4)))
AS quantitetotnonvalsou,
(select if(isnull(sum(zz_patrimoinegenerique.quantitegenerique)),0,sum(zz_patrimoinegenerique.quantitegenerique))
from zz_patrimoinegenerique
where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 5)
and (zz_patrimoinegenerique.versionoffre = a.versionoffre)))
AS quantiteassocieapp,
(select if(isnull(sum(zz_patrimoinegenerique.quantite)),0,sum(zz_patrimoinegenerique.quantite))
from zz_patrimoinegenerique
where ((zz_patrimoinegenerique.idprojet = a.id)
and (zz_patrimoinegenerique.codeobjet = 5)
and (zz_patrimoinegenerique.idgenerique = '-1')))
AS quantitenonassocieapp,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 1)
and (zz_patrimoine.codeobjet = 5)))
AS quantitetotvalapp,
(select if(isnull(sum(zz_patrimoine.quantite)),0,sum(zz_patrimoine.quantite))
from zz_patrimoine where ((zz_patrimoine.idprojet = a.id)
and (zz_patrimoine.valide = 0)
and (zz_patrimoine.codeobjet = 5)))
AS quantitetotnonvalapp
from projet a; |
Partager