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
| _db.CreateTables(new Table("VaffairesEtat", "CREATE VIEW VaffairesEtat AS SELECT"
+ " affaires.pkaffaires, affaires.numAffaire AS 'N° Dossier', affaires.designation AS 'Nom dossier' , societes.nomSociete AS 'Société' , affaires.fkaffaireStatuts AS 'Statut affaire'"
+ " , devis.pkdevis, devis.numDevis AS 'N° Devis', devis.designation as 'Nom devis', devis.ptHT AS 'Valeur Ht', devis.fkdevisStatuts AS 'Statut devis'"
+ " , factures.pkfactures, factures.numFacture AS 'N° Facture', factures.nom AS 'Désignation facture', factures.ptHt AS 'Montant Ht', factures.dateFacture AS 'Facturé le :', factures.dateReglement AS 'régler le :', factures.fkfactureStatuts AS 'Statut'"
+ " FROM"
//+ " affaires LEFT OUTER JOIN devis ON affaires.pkaffaires = devis.fkaffaires"
+ " affaires JOIN devis ON affaires.pkaffaires = devis.fkaffaires"
+ " JOIN societes ON societes.pksocietes = affaires.idSociete"
+ " LEFT OUTER JOIN factures ON devis.pkdevis = factures.fkdevis"
+ " UNION ALL"
+ " SELECT affaires.pkaffaires, affaires.numAffaire AS 'N° Dossier', affaires.designation AS 'Nom dossier' , societes.nomSociete AS 'Société' , affaires.fkaffaireStatuts AS 'Statut affaire'"
+ " , null, null AS 'N° Devis', null as 'Nom devis', null AS 'Valeur Ht', null AS 'Statut devis'"
+ " , factures.pkfactures, factures.numFacture AS 'N° Facture', factures.nom AS 'Désignation facture', factures.ptHt AS 'Montant Ht', factures.dateFacture AS 'Facturé le :', factures.dateReglement AS 'régler le :', factures.fkfactureStatuts AS 'Statut'"
+ " FROM"
+ " affaires JOIN societes ON societes.pksocietes = affaires.idSociete"
+ " JOIN factures ON affaires.pkaffaires = factures.fkaffaires"
+ " where factures.fkdevis < 1"
// + " UNION ALL"
// + " SELECT affaires.pkaffaires, affaires.numAffaire AS 'N° Dossier', affaires.designation AS 'Nom dossier' , societes.nomSociete AS 'Société' , affaires.fkaffaireStatuts AS 'Statut affaire'"
// + " , null, null AS 'N° Devis', null as 'Nom devis', null AS 'Valeur Ht', null AS 'Statut devis'"
// + " , null, null AS 'N° Facture', null AS 'Désignation facture', null AS 'Montant Ht', null AS 'Facturé le :', null AS 'régler le :', null AS 'Statut'"
// + " FROM"
// + " affaires JOIN societes ON societes.pksocietes = affaires.idSociete"
//+ " JOIN factures JOIN devis"
// + " where factures.fkdevis is null AND devis.fkaffaires is null"
+ " ORDER BY affaires.numAffaire DESC"
)); |
Partager