// Organisation
// Organisation
case QueryQuestion::OPTION_ORGANISATION:
$Organisation = Organisation::model()->findByPk($query_option);
print $Organisation->title;
break;
// Invite
// Invite
case QueryQuestion::OPTION_INVITE:
$InviteQuery = Query::model()->findByPk($query_option);
print $InviteQuery->name;
break;
// Culture Segment
// Culture Segment
case QueryQuestion::OPTION_CS:
$CultureSegment = CultureSegment::model()->findByPk($query_option);
print $CultureSegment->name;
break;
// Artforms
// Artforms
case QueryQuestion::OPTION_ARTFORM:
$Artforms = Artform::model()->findByPk($query_option);
print $Artform->title;
// Level of engagement
// Level of engagement
case QueryQuestion::OPTION_LOE:
$LevelsOfEngagement = QueryQuestion::model()->levelsOfEngagement();
foreach ($LevelsOfEngagement as $LOEid => $LOEName) {
if ($query_option == $LOEid) {
print $LOEName;
}
break;
case QueryQuestion::OPTION_ORGANISATION:
if (is_null($Organisations)) {
$Organisations = Organisation::model()->findAll(array('condition' => 'active = 1', 'index' => 'id'));
}
print ' ' . $Organisations[$row->query_option]->title;
break;
case QueryQuestion::OPTION_INVITE:
if (is_null($InviteQueries)) {
$InviteQueries = Query::model()->findAll(array('condition' => 'invite = 1', 'index' => 'id'));
}
print ' ' . $InviteQueries[$row->query_option]->name;
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;
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;
}