public function actionResults($id)
{
$Campaign = $this->loadModel($id);
// Has this campaign been run?
if (!in_array((int) $Campaign->status, array(Campaign::STATUS_HAS_BEEN_RUN, Campaign::STATUS_QUEUED))) {
$this->redirect(array('campaign/createUpdate', 'id' => $Campaign->id));
}
$mailgunApi = new MailgunCampaign(Yii::app()->params['insiderEmailDomain'], Yii::app()->params['mailgun']['key']);
$outcomeStasCommand = Yii::app()->db->createCommand("\n\n\n\t\t\tSELECT co.id AS campaign_outcome_id, co.name, sum(CASE WHEN outcome IS NOT NULL THEN 1 ELSE 0 END) AS positive_outcomes_count\n\t\t\tFROM campaign_outcome co\n\t\t\tLEFT JOIN campaign_contact2outcome cc2o ON cc2o.campaign_outcome_id = co.id\n\t\t\tWHERE co.campaign_id = :campaign_id\n\t\t\tGROUP BY co.id\n\t\t");
$outcomeStasCommand->bindParam(':campaign_id', $Campaign->id);
$stats = array("opencount" => 0, "total_clickcount" => 0, "outcomes" => $outcomeStasCommand->queryAll(), "sent" => null);
// Check if the campaign ID exists
try {
$mailgunCampaign = $mailgunApi->getCampaign(Yii::app()->params['insiderEmailDomain'], $Campaign->id);
$stats['sent'] = $mailgunCampaign['created_at'];
try {
$mailgunContactOpens = $mailgunApi->getCampaignOpensByRecipientCount(Yii::app()->params['insiderEmailDomain'], $Campaign->id);
$stats['opencount'] = $mailgunContactOpens['count'];
} catch (Exception $e) {
$mailgunContactOpens = array('error' => 'No open data available');
}
} catch (Exception $e) {
$mailgunCampaign = array('error' => 'No campaign data available');
}
$QueryQuestions = QueryQuestion::model()->findAll(array('order' => 'type,id'));
$this->render('results', array('Campaign' => $Campaign, 'QueryQuestions' => $QueryQuestions, 'mailgunCampaign' => $mailgunCampaign, 'mailgunContactOpens' => $mailgunContactOpens, 'stats' => $stats));
}
break;
case QueryQuestion::OPTION_CS:
if (is_null($CultureSegments)) {
$CultureSegments = CultureSegment::model()->findAll(array('index' => 'id'));
}
print ' ' . $CultureSegments[$row->query_option]->name;
break;
case QueryQuestion::OPTION_ARTFORM:
if (is_null($Artforms)) {
$Artforms = Artform::model()->findAll(array('index' => 'id', 'order' => 'title ASC'));
}
print ' ' . $CultureSegments[$row->query_option]->title;
break;
case QueryQuestion::OPTION_LOE:
if (is_null($LevelsOfEngagement)) {
$LevelsOfEngagement = QueryQuestion::model()->levelsOfEngagement();
}
print ' ' . $LevelsOfEngagement[$row->query_option];
break;
case QueryQuestion::OPTION_CAMPAIGN:
if (is_null($Campaigns)) {
$Campaigns = Campaign::model()->with('query')->findAll(array('condition' => 'invite = 0', 'index' => 'id'));
}
print ' ' . $Campaigns[$row->query_option];
break;
default:
print ' ' . $row->query_number;
break;
}
?>
</td>
public function actionInvite()
{
$Query = new Query();
//hard code email and commonwealth
$Query->JSON = CJSON::encode(array('rows' => array()));
$queryResults = $Query->runInviteCountQuery();
$QueryQuestions = QueryQuestion::model()->findAll(array('order' => 'type,id'));
$this->pageTitle = 'Invite | ' . Yii::app()->name;
$this->breadcrumbs = array('Invites' => array('invite/index'), 'Create Invite Query');
$this->render('create', array('Query' => $Query, 'QueryQuestions' => $QueryQuestions, 'invite' => true, 'queryResults' => $queryResults));
}
//condition so old campaigns pre-json store don't kick off
if (!is_null($Campaign->json)) {
?>
<div class="row">
<div class="col-xs-12">
<div class="page-header">
<h2>Query Criteria</h2>
</div>
<?php
//for each row in the query builder create the correct choice
$json = json_decode($Campaign->json);
$textFirstRow = true;
$listItems = array();
foreach ($json->rows as $k => $row) {
$Question = QueryQuestion::model()->findByPk($row->query_choice);
//render partial
$listItems[] = $this->renderPartial('_textRow', array('Question' => $Question, 'QueryQuestions' => $QueryQuestions, 'and_choice' => $row->and_choice, 'bool_choice' => $row->bool_choice, 'query_choice' => $row->query_choice, 'query_number' => $row->query_number, 'query_option' => $row->query_option, 'disabled' => $row->disabled, 'textFirstRow' => $textFirstRow, 'rowNumber' => $k), true);
$textFirstRow = false;
}
if (sizeof($listItems)) {
echo '<ul><li>' . implode('</li><li>', $listItems) . '</li></ul>';
}
?>
</div>
</div>
<div class="row">
<div class="col-xs-12">
<div class="page-header">
public function run($selectFields = array('contact_warehouse_id'), $filterByOrganisation = null, $invite = false, $random = false, $count = false, $accessionOnly = true)
{
$start = microtime(true);
// Sort out select fields
foreach ($selectFields as $k => $selectField) {
$selectFields[$k] = 'clean_warehouse.' . $selectField;
}
// Make them into a string
$selectFields = implode(',', $selectFields);
// Set up the command
$command = Yii::app()->db->createCommand();
$params = array();
$conditions = json_decode($this->JSON, true);
$whereSql = '';
$joinSQL = '';
// Var to identify if we've already joined the invite table into the query
$invitesJoined = false;
$i = 0;
if (sizeof($conditions['rows'])) {
foreach ($conditions['rows'] as $key => $row) {
//print_r($row);
// $key is used for $params array.
//get question from the database
$QueryQuestion = QueryQuestion::model()->findByPk($row['query_choice']);
// after the first item we need an AND / OR to link them
if ($i > 0) {
if ($row['and_choice']) {
$whereSql .= "\n AND";
} else {
$whereSql .= "\n OR";
}
}
// not all questions make use of a field name, check if we have one
if ($QueryQuestion->field_name) {
// we start with the field name
$whereSql .= ' `' . $QueryQuestion->field_name . '`';
}
//if there is no comparison option we check for null or empty
if (!$QueryQuestion->has_value) {
switch ($QueryQuestion->id) {
case 20:
// Each contact has any previous invites
if ($row['bool_choice']) {
$joinSQL .= "\n LEFT JOIN `invite` AS pendingInvites ON pendingInvites.`contact_warehouse_id` = `clean_warehouse`.`contact_warehouse_id`" . "\n ";
$whereSql .= " pendingInvites.`id` IS NOT NULL";
$invitesJoined = true;
} else {
$joinSQL .= "\n LEFT JOIN `invite` AS pendingInvites ON pendingInvites.`contact_warehouse_id` = `clean_warehouse`.`contact_warehouse_id`" . "\n ";
$whereSql .= " pendingInvites.id IS NULL";
$invitesJoined = true;
}
break;
case 21:
if ($row['bool_choice']) {
// 1+
$whereSql .= ' > 0';
} else {
// 0 OR NULL due to error with initial value being set to 0
$whereSql .= ' < 1 OR ' . $QueryQuestion->field_name . ' IS NULL';
}
break;
case 26:
// has completed all assession forms
if ($row['bool_choice']) {
$whereSql .= ' = 1';
} else {
$whereSql .= ' != 1';
}
break;
default:
//TRUE
if ($row['bool_choice']) {
$whereSql .= ' IS NOT NULL';
// Add empty string check for email
if ($QueryQuestion->id == 6) {
$whereSql .= ' AND email != "" ';
}
} else {
$whereSql .= ' IS NULL';
}
break;
}
//$whereSql .= "--\n\nOption query\n\n";
} else {
switch ($QueryQuestion->id) {
//older than
case 4:
if ($row['bool_choice']) {
$whereSql .= ' < :date' . $key;
} else {
$whereSql .= ' > :date' . $key;
}
$birth = strtotime('today - ' . (int) $row['query_number'] . ' years');
$params[':date' . $key] = "'" . date('Y-m-d', $birth) . "'";
break;
//younger than
//younger than
case 3:
if ($row['bool_choice']) {
$whereSql .= ' > :date' . $key;
} else {
$whereSql .= ' < :date' . $key;
}
$birth = strtotime('today - ' . (int) $row['query_number'] . ' years');
$params[':date' . $key] = "'" . date('Y-m-d', $birth) . "'";
break;
//culture segment
//culture segment
case 5:
if ($row['bool_choice']) {
$whereSql .= ' = :text' . $key;
} else {
$whereSql .= ' != :text' . $key . ' OR ' . $QueryQuestion->field_name . ' IS NULL';
}
$CS = CultureSegment::model()->findByPk($row['query_option']);
$params[':text' . $key] = "'" . $CS->name . "'";
break;
//level of engagement of
//level of engagement of
case 10:
if ($row['bool_choice']) {
$whereSql .= ' = :engage' . $key;
} else {
$whereSql .= ' != :engage' . $key;
}
$params[':engage' . $key] = '"' . QueryQuestion::model()->levelOfEngagement($row['query_option']) . '"';
break;
// Visited a venue
// Visited a venue
case 11:
case 12:
case 13:
case 14:
/*
1 = I've visited
2 = I've visited in last 3 years
3 = Never been but I would
4 = Never been and don't plan to
*/
switch ($QueryQuestion->id) {
case 11:
$venueVisitedValue = 1;
break;
case 12:
$venueVisitedValue = 2;
break;
case 13:
$venueVisitedValue = 3;
break;
case 14:
$venueVisitedValue = 4;
break;
default:
$venueVisitedValue = 1;
break;
}
$joinSQL .= "\n\nLEFT JOIN contact2venue join" . $key . " ON (\n\n\tjoin" . $key . ".accession_id = clean_warehouse.accession_id\n\n\tAND\n\n\tjoin" . $key . ".venue_id = " . (int) $row['query_option'] . "\n\n)";
if ($row['bool_choice']) {
$whereSql .= " join" . $key . ".visited = " . (int) $venueVisitedValue;
} else {
$whereSql .= " join" . $key . ".visited != " . (int) $venueVisitedValue;
}
break;
case 15:
$joinSQL .= "\n\nLEFT JOIN campaign_contact join" . $key . " ON contact_warehouse_id = join" . $key . ".warehouse_id\n\n";
if ($row['bool_choice']) {
$whereSql = " campaign_id = :campaign_id" . $key;
} else {
$whereSql = " campaign_id != :campaign_id" . $key . " OR campaign_id IS NULL ";
}
$params[":campaign_id" . $key] = (int) $row['query_option'];
break;
//outcomes
//outcomes
case 27:
if ($row['bool_choice']) {
$whereSql .= " clean_warehouse.contact_warehouse_id IN (SELECT warehouse_id FROM campaign_contact2outcome JOIN campaign_contact ON campaign_contact2outcome.campaign_contact_id = campaign_contact.id AND campaign_outcome_id = :campaign_outcome_id AND outcome IS NOT NULL)";
} else {
$whereSql .= " clean_warehouse.contact_warehouse_id NOT IN (SELECT warehouse_id FROM campaign_contact2outcome JOIN campaign_contact ON campaign_contact2outcome.campaign_contact_id = campaign_contact.id AND campaign_outcome_id = :campaign_outcome_id AND outcome IS NOT NULL)";
}
$params[":campaign_outcome_id"] = (int) $row['query_option'];
break;
//origin id
//origin id
case 16:
if ($row['bool_choice']) {
$whereSql .= ' = :origin' . $key;
} else {
$whereSql .= ' != :origin' . $key;
}
$orgId = preg_replace("/[^0-9]/", "", $row['query_option']);
$params[':origin' . $key] = (int) $orgId;
break;
// invited date
// invited date
case 18:
break;
// was / not invited before in a particular invite query
// was / not invited before in a particular invite query
case 19:
break;
// Visited an artform
// Visited an artform
case 22:
case 23:
case 24:
case 25:
/*
1 = I've visited
2 = I've visited in last 3 years
3 = Never been but I would
4 = Never been and don't plan to
*/
switch ($QueryQuestion->id) {
case 22:
$artformVisitedValue = 1;
break;
case 23:
$artformVisitedValue = 2;
break;
case 24:
$artformVisitedValue = 3;
break;
case 25:
$artformVisitedValue = 4;
break;
default:
$artformVisitedValue = 1;
break;
}
$joinSQL .= " \n\t\t\t\t\t\t\t\tLEFT JOIN contact2artform join" . $key . " ON join" . $key . ".accession_id = clean_warehouse.accession_id\n\t\t\t\t\t\t\t\t\tAND join" . $key . ".artform_id = " . (int) $row['query_option'] . "\n\t\t\t\t\t\t\t";
if ($row['bool_choice'] == 1) {
$whereSql .= " join" . $key . ".visited = " . (int) $artformVisitedValue;
} else {
$whereSql .= " (join" . $key . ".visited IS NULL OR join" . $key . ".visited != " . (int) $artformVisitedValue . ') ';
}
break;
}
}
$i++;
}
// foreach
}
if (strlen($whereSql)) {
$whereSql = '(' . $whereSql . ')';
}
if ((int) $filterByOrganisation) {
if (strlen($whereSql)) {
$whereSql .= ' AND ';
}
// filter down to a single organisation outside the saved query.
// Used when an organisation downloads query results which aren't filtered to only their own organisation already.
$whereSql .= ' (origin_organisation_id = :originOrganisation)';
$params[':originOrganisation'] = (int) $filterByOrganisation;
}
// Are we querying people in the list, or not?
// Regular campaign query = Contacts in the List
// Invite query = Contacts not in the List
if (!$invite) {
// All records, no restrictions
$joinSQL .= "\n\t\t\t\tLEFT JOIN `suppression_list` sup1 ON sup1.`store2contact_id` = `clean_warehouse`.`store2contact_id`\n\t\t\t\tLEFT JOIN `suppression_list` sup2 ON (sup2.`warehouse_id` = `clean_warehouse`.`contact_warehouse_id`)\n\t\t\t" . " \n";
if (strlen($whereSql)) {
$whereSql .= ' AND ';
}
$whereSql .= ' ( sup1.`id` IS NULL) AND ( sup2.`id` IS NULL)';
} elseif ($invite) {
if (strlen($whereSql)) {
$whereSql .= ' AND ';
}
// standard invite exclusions - not agreed terms, not insider row, has got an email
$whereSql .= "\n (terms_agreed IS NULL AND email IS NOT NULL AND origin_organisation_id != :inviteOrgOrganisation)" . " \n";
$params[':inviteOrgOrganisation'] = 10;
// suppression list exclusions. We do two joins here for speed
//$joinSQL .= "\n LEFT JOIN `suppression_list` ON `suppression_list`.`store_id` = `clean_warehouse`.`store_id`" . " \n";
$joinSQL .= "\n\t\t\t\tLEFT JOIN `suppression_list` sup1 ON sup1.`store2contact_id` = `clean_warehouse`.`store2contact_id`\n\t\t\t\tLEFT JOIN `suppression_list` sup2 ON (sup2.`warehouse_id` = `clean_warehouse`.`contact_warehouse_id`)\n\t\t\t" . " \n";
$whereSql .= ' AND ( sup1.`id` IS NULL) AND ( sup2.`id` IS NULL)';
//exclusion - prevent pending invite contacts being included
if (!$invitesJoined) {
$joinSQL .= "\n LEFT JOIN ( SELECT * FROM `invite` WHERE `invite`.`status` = 0 ) AS pendingInvites ON pendingInvites.`contact_warehouse_id` = `clean_warehouse`.`contact_warehouse_id`" . " \n";
$whereSql .= " AND pendingInvites.`status` IS NULL ";
}
// End invite exclusion
}
// Order randomly
if ($random) {
$command->order("RAND()");
} elseif ($count !== true) {
$command->order('clean_warehouse.contact_warehouse_id ASC');
}
if ($count) {
$command->select('clean_warehouse.contact_warehouse_id');
} else {
// all the defined fields
$command->select($selectFields);
}
if ($accessionOnly) {
if (strlen($whereSql)) {
$whereSql .= ' AND ';
}
$whereSql .= 'terms_agreed IS NOT NULL';
}
$command->from('clean_warehouse');
$command->where($whereSql, $params);
if (strlen($joinSQL)) {
$command->join = $joinSQL;
}
$sql_string = $command->text;
$query = str_replace(array_keys($command->params), array_values($command->params), $command->text);
if ($count) {
// only return the total
$command = Yii::app()->db->createCommand("SELECT COUNT(*) AS rowCount FROM (\n\t\t\t\t\tSELECT contact_warehouse_id FROM (" . $query . ") AS shuffleStore2Contact GROUP BY contact_warehouse_id\n\t\t\t\t\t" . (isset($conditions['limit']) && $conditions['limit'] > 0 ? ' limit ' . (int) $conditions['limit'] : '') . "\n\t\t\t\t) AS t\n\t\t\t\t");
//exit($command->text);
$result = $command->queryRow();
$contactCount = $result['rowCount'];
} else {
// return the full row. Group by contact_warehouse_id to remove dupe rows
$command = Yii::app()->db->createCommand("SELECT * FROM (" . $query . ") AS shuffleStore2Contact GROUP BY contact_warehouse_id" . ($random ? " ORDER BY RAND()" : "") . (isset($conditions['limit']) && $conditions['limit'] > 0 ? ' limit ' . (int) $conditions['limit'] : ''));
//print $command->text;exit();
$contacts = $command->queryAll();
$contactCount = count($contacts);
}
$sql_string = $command->text;
//exit($sql_string);
unset($whereSql, $command);
$returnArray = array('rows' => $contacts, 'count' => $contactCount);
if (ENVIRONMENT === 'LOCAL') {
$returnArray['sql'] = $sql_string;
$returnArray['params'] = $params;
}
$end = microtime(true);
$returnArray['queryTime'] = round($end - $start, 4);
$returnArray['ip'] = $_SERVER['REMOTE_ADDR'];
return $returnArray;
}
