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; }