Problème d'affichage de données calculées sql (dépendant de dates)

par **Samsss** » 12 juin 2019, 14:20

Salut,
pour une meilleure compréhension, voici le resultat avec l'affichage while :

Voici l'affichage d'un extrait de la requete qui est passée, pour montrer que les dates sont bien prises en compte :

par **Samsss** » 12 juin 2019, 14:15

Salut,
pour une meilleure compréhension, voici le resultat avec l'affichage while :

Voici l'affichage d'un extrait de la requete qui est passée, pour montrer que les dates sont bien prises en compte :

par **Samsss** » 12 juin 2019, 14:09

Bonjour,

Dans le cadre d'un projet, je dois pouvoir afficher des rapports de vente journaliers complets (ventes par j/m, stocks, etc...) pour un client dans l’immobilier. Jusque là rien de bien méchant..
Cependant les données que je dois afficher sont pour la plupart calculées, et dans ma requête sql qui interroge la BDD j'ai inséré des dates (obligatoire pour ventes par j/m, etc..). Mon problème est que les dates passent bien dans la requête (comme parametres), Mais je n'obtiens pas les voulues, j'obtiens les premières mais le reste est vide.

J'aimerais avoir, si possible, quelques pistes de réflexions pour pouvoir régler ce pb.

Voici mon code :

<?php


function DailyReport($entityData) {
    global $log, $adb;
    $moduleName = $entityData->getModuleName();
    $wsId = $entityData->getId();
    $data = $entityData->data;
    $parts = explode('x', $wsId);
    $entityId = $parts[1];
    $myvar2 = $_REQUEST['action'];
    $myvar = $_REQUEST['ajxaction'];

    $todays_date = date('Y-m-d');

//requete
 $sql3 = 'SELECT 
 
 stock.project as PROJET,
 stock.type as TYPE,
 STOCK,
 TRESERVE,
 MVENTES,
 MAVANCES,
 JVENTES,
 JAVANCES,
 SYNDIC,
 VISITES,
 APPELS,
 RELANCES
 
 from
 
 (select
 crm212_assetscf.cf_1256 as project,
 crm212_assetscf.cf_1257 as type,
 count(crm212_assets.assetsid) as STOCK
 
 
  from crm212_assets
  join crm212_assetscf on crm212_assets.assetsid=crm212_assetscf.assetsid
  join crm212_crmentity on crm212_assets.assetsid=crm212_crmentity.crmid
  where crm212_crmentity.deleted=0
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
 
  Group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) AS stock
 
 LEFT JOIN
  (select
  crm212_assetscf.cf_1256 as project,
  crm212_assetscf.cf_1257 as type,
  count(crm212_salesorder.salesorderid) as TVENTES
  
  from crm212_salesorder
  left join crm212_salesordercf on crm212_salesorder.salesorderid=crm212_salesordercf.salesorderid
  left join crm212_assetscf on crm212_salesorder.rf_1290=crm212_assetscf.assetsid
  left join crm212_crmentity on crm212_salesorder.salesorderid=crm212_crmentity.crmid
  where (crm212_salesorder.sostatus not like "%DESISTEMENT%" and crm212_salesorder.sostatus not like "%Cancelled%")
  and crm212_crmentity.deleted=0
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  and cf_1279=crm212_assetscf.cf_1256
  
  Group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) AS totalventes
  on stock.type=totalventes.type and stock.project=totalventes.project
 
 LEFT JOIN
  (select
  crm212_assetscf.cf_1256 as project,
  crm212_assetscf.cf_1257 as type,
  count(crm212_assets.assetsid) as TRESERVE
  
  from crm212_assets
  left join crm212_assetscf on crm212_assets.assetsid=crm212_assetscf.assetsid
  left join crm212_crmentity on crm212_assets.assetsid=crm212_crmentity.crmid
  where (assetstatus = "RESERVE" OR assetstatus ="LIVRAISON EN COURS" OR assetstatus ="LIVRE")
  and crm212_crmentity.deleted=0
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  
  Group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) AS totalreserve
  on stock.type=totalreserve.type and stock.project=totalreserve.project
 
 LEFT JOIN
  (select
  crm212_assetscf.cf_1256 as project,
  crm212_assetscf.cf_1257 as type,
  count(crm212_salesorder.salesorderid) as MVENTES
  
  from crm212_salesorder
  left join crm212_salesordercf on crm212_salesorder.salesorderid=crm212_salesordercf.salesorderid
  left join crm212_assetscf on crm212_salesorder.rf_1290=crm212_assetscf.assetsid
  left join crm212_crmentity on crm212_salesorder.salesorderid=crm212_crmentity.crmid
  where DATE_FORMAT(crm212_salesorder.duedate,"%m")=EXTRACT(MONTH FROM ?)
  and DATE_FORMAT(crm212_salesorder.duedate,"%Y")=EXTRACT(YEAR FROM ?)
 and (crm212_salesorder.sostatus not like "%DESISTEMENT%" and crm212_salesorder.sostatus not like "%Cancelled%")
  and crm212_crmentity.deleted=0
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  and cf_1279=crm212_assetscf.cf_1256
  Group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) AS mventes
  on stock.type=mventes.type and stock.project=mventes.project
 
 LEFT JOIN 
 (SELECT 
 
 crm212_assetscf.cf_1256 as project,
 crm212_assetscf.cf_1257 as type,
 sum(payment_amount) as MAVANCES
 
 FROM 
 crm212_payment 
 left join crm212_assetscf on crm212_payment.rf_1401=crm212_assetscf.assetsid
 left join crm212_salesorder on crm212_payment.related_to=crm212_salesorder.salesorderid
 join crm212_crmentity on crm212_payment.paymentid=crm212_crmentity.crmid
 where DATE_FORMAT(crm212_salesorder.duedate,"%m")=EXTRACT(MONTH FROM ?)
  and DATE_FORMAT(crm212_salesorder.duedate,"%Y")=EXTRACT(YEAR FROM ?)
 and crm212_crmentity.deleted=0
 and paymenttype="AVANCE"
 and sostatus!="Cancelled"
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
 group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) as mavances
 on mavances.type=stock.type and stock.project=mavances.project
 
 LEFT JOIN 
 (select
 crm212_assetscf.cf_1256 as project,
 crm212_assetscf.cf_1257 as type,
 count(crm212_salesorder.salesorderid) as JVENTES
 
  from crm212_salesorder
  left join crm212_salesordercf on crm212_salesorder.salesorderid=crm212_salesordercf.salesorderid
  left join crm212_assetscf on crm212_salesorder.rf_1290=crm212_assetscf.assetsid
  left join crm212_crmentity on crm212_salesorder.salesorderid=crm212_crmentity.crmid
  where crm212_salesorder.duedate=?
  and (crm212_salesorder.sostatus not like "%DESISTEMENT%" and crm212_salesorder.sostatus not like "%Cancelled%")
  and crm212_crmentity.deleted=0
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  and cf_1279=crm212_assetscf.cf_1256 
  Group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) AS jventes
 on stock.type=jventes.type and stock.project=jventes.project
 
 LEFT JOIN 
 (SELECT 
 
 crm212_assetscf.cf_1256 as project,
 crm212_assetscf.cf_1257 as type,
 sum(payment_amount) as JAVANCES
 
 FROM 
 crm212_payment 
 left join crm212_assetscf on crm212_payment.rf_1401=crm212_assetscf.assetsid
 left join crm212_salesorder on crm212_payment.related_to=crm212_salesorder.salesorderid
 join crm212_crmentity on crm212_payment.paymentid=crm212_crmentity.crmid
 where crm212_salesorder.duedate=?
 and crm212_crmentity.deleted=0
 and paymenttype="AVANCE"
 and sostatus!="Cancelled"
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
 group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) as javances
 on javances.type=stock.type and stock.project=javances.project
 
 LEFT JOIN 
 (SELECT 
 
 crm212_assetscf.cf_1256 as project,
 crm212_assetscf.cf_1257 as type,
 sum(payment_amount) as SYNDIC
 
 FROM 
 crm212_payment 
 left join crm212_assetscf on crm212_payment.rf_1401=crm212_assetscf.assetsid
 left join crm212_salesorder on crm212_payment.related_to=crm212_salesorder.salesorderid
 join crm212_crmentity on crm212_payment.paymentid=crm212_crmentity.crmid
 where crm212_salesorder.duedate=?
 and crm212_crmentity.deleted=0
 and paymenttype="SYNDIC"
 and crm212_assetscf.cf_1256 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
 group by crm212_assetscf.cf_1256,crm212_assetscf.cf_1257) as syndic
 on syndic.type=stock.type and stock.project=syndic.project
 
 
 LEFT join (
  select 
  cf_1240 as project, 
  cf_1237 as type, 
  count(*) as VISITES 
  from crm212_crmentity 
  
  join crm212_users on crm212_crmentity.smownerid=crm212_users.id
  left JOIN crm212_leadscf on crm212_crmentity.crmid=crm212_leadscf.leadid
  where (DATE_FORMAT(createdtime, "%Y-%m-%d") =?)
  and cf_1402="BDV"
  and crm212_crmentity.deleted="0"
 and cf_1240 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  group by cf_1240,cf_1237) as visites 
  on visites.type=stock.type and stock.project=visites.project
 
 LEFT join (
  select 
  cf_1240 as project, 
  cf_1237 as type, 
  count(*) as APPELS
  from crm212_crmentity 
  
  join crm212_users on crm212_crmentity.smownerid=crm212_users.id
  left JOIN crm212_leadscf on crm212_crmentity.crmid=crm212_leadscf.leadid
  where (DATE_FORMAT(createdtime, "%Y-%m-%d") =?)
  and cf_1402="CALL CENTER"
  and crm212_crmentity.deleted="0"
 and cf_1240 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  group by cf_1240,cf_1237) as appels 
 on appels.type=stock.type and stock.project=appels.project
 
 LEFT join (
 select 
  crm212_leadscf.cf_1240 as project, 
  crm212_leadscf.cf_1237 as type,
 count(*) as RELANCES 
 from crm212_crmentity 
  
 join crm212_modcomments on crm212_crmentity.crmid=crm212_modcomments.modcommentsid
 join crm212_leadscf on crm212_modcomments.related_to=crm212_leadscf.leadid
 where setype="Modcomments"
  and (DATE_FORMAT(createdtime, "%Y-%m-%d") =?)
  and crm212_crmentity.deleted="0"
  and cf_1240 in ("TAMARIS 1","JNAN AHL LOGHLAM","SIDI MOUMEN","LISSASFA","TAMARIS 2","JNAN AL MOHIT") 
  group by cf_1240,cf_1237) as relances
  on relances.type=stock.type and stock.project=relances.project';


 
 // execution 
 $res1 = $adb->pquery($sql3, array($todays_date,$todays_date,$todays_date,$todays_date,$todays_date,$todays_date,$todays_date,$todays_date,$todays_date,$todays_date));  
 $resinfo = $adb->fetch_array($res1);

 //parcourir et afficher l'array des resultats

while ($resinfo = $adb->fetch_array($res1)) {
  echo '<pre>';
  echo $res1;
  echo '</pre>';
  die;
}


// afficher le contenu dans $res1 pour voir si les dates passent en para, dans ce cas mettre en com le while ci dessus
/*echo '<pre>';
print_r($res1);
echo '<pre>';*/
   

    

}
 ?>

Merci d'avance