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
|
SELECT cm.id_campaign_marketing as id,
cm.name_campaign as nameCampaign,
cm.cpid as cpid,
cm.media asmedia, cm.identification_place as identificationPlace,
cm.mode as mode,
cm.share as share,
cm.embed as embed,
cm.full_screen as full_screen,
cm.id_project as idProject,
campaign.description as description,
com.compagny_name as advertiser,
sum(if((id_evaluation IS NOT NULL), 1, 0)) AS `nbClic`,
sum(if(`eva`.`ID_USER`!=0, 1, 0)) AS `nbClicIdent`,
sum(if(`eva`.`EVALUATIONACTION_PROGRESS`=2, 1, 0)) AS `nbAudit`,
sum(if(`eva`.`EVALUATIONACTION_PROGRESS`=2 and `eva`.`ID_USER`!=0, 1, 0)) AS `nbAuditIdent`,
sum(if(`inf`.`CONV_VALID_ACTION`=1, 1, 0)) AS `nbDiagnostic`, avg(if(`inf`.`QUESTIONNAIRE_TIME`=0, null, `inf`.`QUESTIONNAIRE_TIME`)) AS `averageTimeQuestionnaire`,
avg(if(`inf`.`PROFILE_TIME`=0, null, `inf`.`PROFILE_TIME`)) AS `averageTimeResults`
FROM (campaign_marketing cm
LEFT JOIN evaluation_action AS eva ON eva.ID_PROJECT = cm.id_project )
LEFT JOIN evaluation_informations AS inf ON inf.ID_EVALUATION = eva.id_EVALUATIONACTION AND cm.cpid = inf.CPID
JOIN campaign ON cm.id_project = campaign.ID_PROJECT
JOIN compagny AS com ON campaign.id_company_customer = com.id_compagny
JOIN global_rights AS gr ON gr.ID_SLAVE_OBJECT=campaign.ID_PROJECT
AND gr.ID_MASTER_OBJECT='200'
AND gr.MASTER_OBJECT_TYPE='14'
AND gr.SLAVE_OBJECT_TYPE ='11'
WHERE 1
GROUP BY cm.cpid, cm.id_project
ORDER BY cm.id_campaign_marketing desc |
Partager