From a1466c59a3df797dcc13a379fa3abdfc0b5b469f Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Tue, 28 Jul 2015 01:49:29 -0700
Subject: [PATCH] CRM-13244 - prepareMailingQuery - Use CRM_Utils_SQL_Select

---
 CRM/Core/BAO/ActionSchedule.php | 96 +++++++++++++--------------------
 1 file changed, 38 insertions(+), 58 deletions(-)

diff --git a/CRM/Core/BAO/ActionSchedule.php b/CRM/Core/BAO/ActionSchedule.php
index 283c620bf3..19d5af66b1 100755
--- a/CRM/Core/BAO/ActionSchedule.php
+++ b/CRM/Core/BAO/ActionSchedule.php
@@ -1043,83 +1043,63 @@ WHERE     m.owner_membership_id IS NOT NULL AND
    * @return string
    */
   protected static function prepareMailingQuery($mapping, $actionSchedule) {
-    $extraSelect = $extraJoin = $extraWhere = $extraOn = '';
+    $select = CRM_Utils_SQL_Select::from('civicrm_action_log reminder', array('mode' => 'out'))
+      ->select("reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.*")
+      ->where("reminder.action_schedule_id = #casScheduleId")
+      ->param('casScheduleId', $actionSchedule->id)
+      ->where("reminder.action_date_time IS NULL");
+
+    if ($actionSchedule->limit_to == 0) {
+      $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
+      $select->where("e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact'");
+    }
+    else {
+      $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
+    }
+    if ($mapping->entity == 'civicrm_activity') {
+      $entityJoinClause .= ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
+    }
+    $select->join('a', $entityJoinClause);
 
     if ($mapping->entity == 'civicrm_activity') {
       $compInfo = CRM_Core_Component::getEnabledComponents();
-      $extraSelect = ', ov.label as activity_type, e.id as activity_id';
-      $extraJoin = "
-INNER JOIN civicrm_option_group og ON og.name = 'activity_type'
-INNER JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
-      $extraOn = ' AND e.is_current_revision = 1 AND e.is_deleted = 0 ';
-      if ($actionSchedule->limit_to == 0) {
-        $extraJoin = "
-LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
-LEFT JOIN civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id";
-      }
+      $select->select('ov.label as activity_type, e.id as activity_id');
+
+      $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN';
+      $select->join("og", "$JOIN_TYPE civicrm_option_group og ON og.name = 'activity_type'");
+      $select->join("ov", "$JOIN_TYPE civicrm_option_value ov ON e.activity_type_id = ov.value AND ov.option_group_id = og.id");
 
-      //join for caseId
-      // if CiviCase component is enabled
+      // if CiviCase component is enabled, join for caseId.
       if (array_key_exists('CiviCase', $compInfo)) {
-        $extraSelect .= ", civicrm_case_activity.case_id as case_id";
-        $extraJoin .= "
-            LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`";
+        $select->select("civicrm_case_activity.case_id as case_id");
+        $select->join('civicrm_case_activity', "LEFT JOIN `civicrm_case_activity` ON `e`.`id` = `civicrm_case_activity`.`activity_id`");
       }
     }
 
     if ($mapping->entity == 'civicrm_participant') {
-      $extraSelect = ', ov.label as event_type, ev.title, ev.id as event_id, ev.start_date, ev.end_date, ev.summary, ev.description, address.street_address, address.city, address.state_province_id, address.postal_code, email.email as contact_email, phone.phone as contact_phone ';
-
-      $extraJoin = "
-INNER JOIN civicrm_event ev ON e.event_id = ev.id
-INNER JOIN civicrm_option_group og ON og.name = 'event_type'
-INNER JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
-LEFT  JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
-LEFT  JOIN civicrm_address address ON address.id = lb.address_id
-LEFT  JOIN civicrm_email email ON email.id = lb.email_id
-LEFT  JOIN civicrm_phone phone ON phone.id = lb.phone_id
-";
-      if ($actionSchedule->limit_to == 0) {
-        $extraJoin = "
-LEFT JOIN civicrm_event ev ON e.event_id = ev.id
-LEFT JOIN civicrm_option_group og ON og.name = 'event_type'
-LEFT JOIN civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
+      $select->select('ov.label as event_type, ev.title, ev.id as event_id, ev.start_date, ev.end_date, ev.summary, ev.description, address.street_address, address.city, address.state_province_id, address.postal_code, email.email as contact_email, phone.phone as contact_phone');
+
+      $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN';
+      $select->join('participant_stuff', "
+$JOIN_TYPE civicrm_event ev ON e.event_id = ev.id
+$JOIN_TYPE civicrm_option_group og ON og.name = 'event_type'
+$JOIN_TYPE civicrm_option_value ov ON ev.event_type_id = ov.value AND ov.option_group_id = og.id
 LEFT JOIN civicrm_loc_block lb ON lb.id = ev.loc_block_id
 LEFT JOIN civicrm_address address ON address.id = lb.address_id
 LEFT JOIN civicrm_email email ON email.id = lb.email_id
 LEFT JOIN civicrm_phone phone ON phone.id = lb.phone_id
-";
-      }
+");
     }
 
     if ($mapping->entity == 'civicrm_membership') {
-      $extraSelect = ', mt.minimum_fee as fee, e.id as id , e.join_date, e.start_date, e.end_date, ms.name as status, mt.name as type';
-      $extraJoin = '
- INNER JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
- INNER JOIN civicrm_membership_status ms ON e.status_id = ms.id';
-
-      if ($actionSchedule->limit_to == 0) {
-        $extraJoin = '
- LEFT JOIN civicrm_membership_type mt ON e.membership_type_id = mt.id
- LEFT JOIN civicrm_membership_status ms ON e.status_id = ms.id';
-      }
-    }
+      $select->select('mt.minimum_fee as fee, e.id as id , e.join_date, e.start_date, e.end_date, ms.name as status, mt.name as type');
 
-    $entityJoinClause = "INNER JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
-    if ($actionSchedule->limit_to == 0) {
-      $entityJoinClause = "LEFT JOIN {$mapping->entity} e ON e.id = reminder.entity_id";
-      $extraWhere .= " AND (e.id = reminder.entity_id OR reminder.entity_table = 'civicrm_contact')";
+      $JOIN_TYPE = ($actionSchedule->limit_to == 0) ? 'LEFT JOIN' : 'INNER JOIN';
+      $select->join('mt', "$JOIN_TYPE civicrm_membership_type mt ON e.membership_type_id = mt.id");
+      $select->join('ms', "$JOIN_TYPE civicrm_membership_status ms ON e.status_id = ms.id");
     }
-    $entityJoinClause .= $extraOn;
 
-    $query = "
-SELECT reminder.id as reminderID, reminder.contact_id as contactID, reminder.entity_table as entityTable, reminder.*, e.id as entityID, e.* {$extraSelect}
-FROM  civicrm_action_log reminder
-{$entityJoinClause}
-{$extraJoin}
-WHERE reminder.action_schedule_id = %1 AND reminder.action_date_time IS NULL
-{$extraWhere}";
-    return $query;
+    return $select->toSQL();
   }
 
   /**
-- 
2.25.1