From ca51c18d83a8e4d7dd85e68a4ccf8d7976c98502 Mon Sep 17 00:00:00 2001 From: "vincent@cubedesigners.com" Date: Tue, 29 Mar 2011 14:10:57 +0000 Subject: [PATCH] --- inc/commons/class.common.core.php | 41 ++++++++++++++----------------- 1 file changed, 19 insertions(+), 22 deletions(-) diff --git a/inc/commons/class.common.core.php b/inc/commons/class.common.core.php index 37490f5d1..726c31cfe 100644 --- a/inc/commons/class.common.core.php +++ b/inc/commons/class.common.core.php @@ -164,7 +164,6 @@ class commonCore extends cubeCore { $db->entreprises->primary('pk_entreprises', 'entreprise_id'); $db->entreprises->index('index_entreprises_nom', 'BTREE', 'nom'); $db->entreprises->index('index_entreprises_ws_grade', 'BTREE', 'ws_grade'); - // . // Table projets $db->projets->projet_id('integer', 0, false); @@ -183,6 +182,8 @@ class commonCore extends cubeCore { $db->projets->index('index_projets_nom', 'BTREE', 'nom'); $db->projets->index('index_projets_client', 'BTREE', 'client'); $db->projets->index('index_projets_chef', 'BTREE', 'chef'); + $db->projets->index('index_projets_debut', 'BTREE', 'date_debut'); + $db->projets->index('index_projets_fin', 'BTREE', 'date_fin'); // . // Table taches $db->taches->tache_id('integer', 0, false); @@ -258,9 +259,9 @@ class commonCore extends cubeCore { $db->dashboard->site('varchar', 32, false, '\'extranet\''); // Clés $db->dashboard->primary('pk_dashboard', 'dashboard_id'); - $db->dashboard->index('index_dashboard_equipier','BTREE', 'equipier'); - $db->dashboard->index('index_dashboard_sort','BTREE', 'sort'); - $db->dashboard->index('index_dashboard_site','BTREE', 'site'); + $db->dashboard->index('index_dashboard_equipier', 'BTREE', 'equipier'); + $db->dashboard->index('index_dashboard_sort', 'BTREE', 'sort'); + $db->dashboard->index('index_dashboard_site', 'BTREE', 'site'); // . // Table Signatures $db->signatures->signature_id('integer', 0, false); @@ -459,26 +460,22 @@ class commonCore extends cubeCore { $this->views->createView('factures_valides', 'SELECT * FROM factures WHERE status IN(1,2)'); $this->views->createView('projets_inter', 'SELECT projets.projet_id,projets.nom,projets.status,projets.date_debut,projets.date_fin,projets.date_creation,projets.deadline,projets.devis,' . 'YEAR(FROM_UNIXTIME(projets.date_debut)) AS annee_debut,' - . 'IF(YEAR(FROM_UNIXTIME(projets.date_fin))=1970,YEAR(NOW()),YEAR(FROM_UNIXTIME(projets.date_fin))) AS annee_fin,' + . 'IF(date_fin=0,YEAR(NOW()),YEAR(FROM_UNIXTIME(projets.date_fin))) AS annee_fin,' . 'clients.rs AS client,equipiers.prenom AS chef,equipiers.utilisateur_id AS chef_id,clients.utilisateur_id AS client_id ' - . 'FROM projets,clients_entreprise clients,equipiers ' - . 'WHERE clients.utilisateur_id=projets.client AND equipiers.utilisateur_id=projets.chef', 'TEMPTABLE'); - $this->views->createView('projets_inter1', 'SELECT projets.*,taches.jours_prevus,taches.jours_consommes,taches.progression,taches.budget AS budget, ' - . 'IF(taches.budget>0,SUM(factures_valides.total_ht)/taches.budget,1) AS rapport_facturation, ' - . 'SUM(factures_valides.total_ht) AS montant_facture ' - . 'FROM projets_inter projets ' - . 'LEFT JOIN taches_projet taches ON taches.projet=projets.projet_id ' - . 'LEFT JOIN factures_valides ON factures_valides.projet=projets.projet_id ' - . 'GROUP BY projets.projet_id,factures_valides.projet', 'TEMPTABLE'); - $this->views->createView('projets_vue', 'SELECT p.*,SUM(f.total_ht) AS montant_paye, ' - . 'IF(p.budget>0,SUM(f.total_ht)/p.budget,1) AS rapport_paiement ' - . 'FROM projets_inter1 p ' - . 'LEFT JOIN factures_payees f ON f.projet=p.projet_id ' - . 'GROUP BY p.projet_id', 'TEMPTABLE'); + . 'FROM projets JOIN clients_entreprise clients ON projets.client=clients.utilisateur_id ' + . 'JOIN equipiers ON projets.chef=equipiers.utilisateur_id'); + $this->views->createView('projets_facturation', 'SELECT projet,SUM(IF(status IN(1,2),total_ht,0)) AS montant_facture, SUM(IF(status=2,total_ht,0)) AS montant_paye FROM factures GROUP BY projet'); + $this->views->createView('projets_vue', 'SELECT p.*,f.montant_facture,f.montant_paye, ' + . 't.jours_prevus,t.jours_consommes,t.progression,t.budget AS budget, ' + . 'IF(t.budget>0,f.montant_paye/t.budget,1) AS rapport_paiement, ' + . 'IF(t.budget>0,f.montant_facture/t.budget,1) AS rapport_facturation ' + . 'FROM projets_inter p ' + . 'LEFT JOIN projets_facturation f ON f.projet=p.projet_id ' + . 'LEFT JOIN taches_projet t ON p.projet_id=t.projet'); $this->views->createView('factures_vue', 'SELECT factures.facture_id,factures.nom,factures.lignes,factures.status,factures.total_ht,factures.date_creation,factures.texte_complementaire,factures.informations_paiement,factures.date_paiement,factures.adresse,factures.avoir,' - . 'projets.projet_id AS projet_id,projets.nom AS projet,projets.client_id AS client_id,projets.client as client,equipiers.prenom AS createur,equipiers.utilisateur_id AS createur_id ' - . 'FROM factures,projets_vue projets,equipiers ' - . 'WHERE factures.projet=projets.projet_id AND factures.createur=equipiers.utilisateur_id AND (factures.facture_id>=0 OR factures.status=1) '); + . 'projets.projet_id AS projet_id,projets.nom AS projet,clients.utilisateur_id AS client_id,clients.rs as client,equipiers.prenom AS createur,equipiers.utilisateur_id AS createur_id ' + . 'FROM factures,projets,equipiers,clients ' + . 'WHERE factures.projet=projets.projet_id AND clients.utilisateur_id=projets.client AND factures.createur=equipiers.utilisateur_id AND (factures.facture_id>=0 OR factures.status=1) '); $this->views->createView('projets_timereport', 'SELECT * FROM projets_vue WHERE status=0'); $this->views->createView('order_timereport', 'SELECT utilisateur_id,projet AS projet_id,MAX(date) AS derniere_activite FROM timereport,taches,projets WHERE taches.tache_id=timereport.tache_id AND taches.projet=projets.projet_id AND projets.status=0 GROUP BY utilisateur_id,taches.projet ORDER BY utilisateur_id ASC,MAX(date) DESC'); $this->views->createView('ca', 'SELECT SUM(total_ht) AS ca, YEAR(FROM_UNIXTIME(date_creation)) annee FROM factures WHERE status IN(1,2) GROUP BY annee'); -- 2.39.5