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
