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