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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
|
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
m.id_campaign_marketing,
cm.name_campaign,
cm.cpid,
cm.media ,
cm.identification_place ,
cm.mode,
cm.share,
cm.embed,
cm.full_screen,
cm.id_project,
campaign.description,
com.compagny_name
ORDER BY cm.id_campaign_marketing DESC |
Partager