Ejemplo n.º 1
0
     // 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;
         }
Ejemplo n.º 2
0
     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;
Ejemplo n.º 3
0
 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;
 }