PHP CRM_Core_Dao::query方法代码示例

 /**
  * Get rows for the event browser.
  *
  * @param int $mailing_id
  *   ID of the mailing.
  * @param int $job_id
  *   Optional ID of the job.
  * @param bool $is_distinct
  *   Group by queue id?.
  * @param int $offset
  *   Offset.
  * @param int $rowCount
  *   Number of rows.
  * @param array $sort
  *   Sort array.
  *
  * @param null $org_unsubscribe
  * @return array
  *   Result set
  */
 public static function &getRows($mailing_id, $job_id = NULL, $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $org_unsubscribe = NULL)
 {
     $dao = new CRM_Core_Dao();
     $unsub = self::$_tableName;
     $queueObject = new CRM_Mailing_Event_BAO_Queue();
     $queue = $queueObject->getTableName();
     $mailingObject = new CRM_Mailing_BAO_Mailing();
     $mailing = $mailingObject->getTableName();
     $jobObject = new CRM_Mailing_BAO_MailingJob();
     $job = $jobObject->getTableName();
     $contactObject = new CRM_Contact_BAO_Contact();
     $contact = $contactObject->getTableName();
     $emailObject = new CRM_Core_BAO_Email();
     $email = $emailObject->getTableName();
     $query = "\n            SELECT      {$contact}.display_name as display_name,\n                        {$contact}.id as contact_id,\n                        {$email}.email as email,\n                        {$unsub}.time_stamp as date,\n                        {$unsub}.org_unsubscribe as org_unsubscribe\n            FROM        {$contact}\n            INNER JOIN  {$queue}\n                    ON  {$queue}.contact_id = {$contact}.id\n            INNER JOIN  {$email}\n                    ON  {$queue}.email_id = {$email}.id\n            INNER JOIN  {$unsub}\n                    ON  {$unsub}.event_queue_id = {$queue}.id\n            INNER JOIN  {$job}\n                    ON  {$queue}.job_id = {$job}.id\n            INNER JOIN  {$mailing}\n                    ON  {$job}.mailing_id = {$mailing}.id\n                    AND {$job}.is_test = 0\n            WHERE       {$mailing}.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
     if (!empty($job_id)) {
         $query .= " AND {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
     }
     if ($org_unsubscribe !== NULL) {
         $query .= " AND {$unsub}.org_unsubscribe = " . ($org_unsubscribe ? 0 : 1);
     }
     if ($is_distinct) {
         $query .= " GROUP BY {$queue}.id ";
     }
     $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC";
     if ($sort) {
         if (is_string($sort)) {
             $sort = CRM_Utils_Type::escape($sort, 'String');
             $orderBy = $sort;
         } else {
             $orderBy = trim($sort->orderBy());
         }
     }
     $query .= " ORDER BY {$orderBy} ";
     if ($offset || $rowCount) {
         //Added "||$rowCount" to avoid displaying all records on first page
         $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
     }
     $dao->query($query);
     $results = array();
     while ($dao->fetch()) {
         $url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$dao->contact_id}");
         $results[] = array('name' => "<a href=\"{$url}\">{$dao->display_name}</a>", 'email' => $dao->email, 'unsubOrOptout' => ts('Yes'), 'date' => CRM_Utils_Date::customFormat($dao->date));
     }
     return $results;
 }

 /**
  * Get rows for the event browser
  *
  * @param int $mailing_id       ID of the mailing
  * @param int $job_id           optional ID of the job
  * @param boolean $is_distinct  Group by queue id?
  * @param int $offset           Offset
  * @param int $rowCount         Number of rows
  * @param array $sort           sort array
  *
  * @return array                Result set
  * @access public
  * @static
  */
 public static function &getRows($mailing_id, $job_id = NULL, $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL)
 {
     $dao = new CRM_Core_Dao();
     $bounce = self::getTableName();
     $bounceType = CRM_Mailing_DAO_BounceType::getTableName();
     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
     $job = CRM_Mailing_BAO_MailingJob::getTableName();
     $contact = CRM_Contact_BAO_Contact::getTableName();
     $email = CRM_Core_BAO_Email::getTableName();
     $query = "\n            SELECT      {$contact}.display_name as display_name,\n                        {$contact}.id as contact_id,\n                        {$email}.email as email,\n                        {$bounce}.time_stamp as date,\n                        {$bounce}.bounce_reason as reason,\n                        {$bounceType}.name as bounce_type\n            FROM        {$contact}\n            INNER JOIN  {$queue}\n                    ON  {$queue}.contact_id = {$contact}.id\n            INNER JOIN  {$email}\n                    ON  {$queue}.email_id = {$email}.id\n            INNER JOIN  {$bounce}\n                    ON  {$bounce}.event_queue_id = {$queue}.id\n            LEFT JOIN   {$bounceType}\n                    ON  {$bounce}.bounce_type_id = {$bounceType}.id\n            INNER JOIN  {$job}\n                    ON  {$queue}.job_id = {$job}.id\n                    AND {$job}.is_test = 0\n            INNER JOIN  {$mailing}\n                    ON  {$job}.mailing_id = {$mailing}.id\n            WHERE       {$mailing}.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
     if (!empty($job_id)) {
         $query .= " AND {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
     }
     if ($is_distinct) {
         $query .= " GROUP BY {$queue}.id ";
     }
     $orderBy = "sort_name ASC, {$bounce}.time_stamp DESC";
     if ($sort) {
         if (is_string($sort)) {
             $sort = CRM_Utils_Type::escape($sort, 'String');
             $orderBy = $sort;
         } else {
             $orderBy = trim($sort->orderBy());
         }
     }
     $query .= " ORDER BY {$orderBy} ";
     if ($offset || $rowCount) {
         //Added "||$rowCount" to avoid displaying all records on first page
         $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
     }
     $dao->query($query);
     $results = array();
     while ($dao->fetch()) {
         $url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$dao->contact_id}");
         $results[] = array('name' => "<a href=\"{$url}\">{$dao->display_name}</a>", 'email' => $dao->email, 'type' => empty($dao->bounce_type) ? ts('Unknown') : $dao->bounce_type, 'reason' => $dao->reason, 'date' => CRM_Utils_Date::customFormat($dao->date));
     }
     return $results;
 }

 /**
  * Get rows for the event browser
  *
  * @param int $mailing_id       ID of the mailing
  * @param int $job_id           optional ID of the job
  * @param int $offset           Offset
  * @param int $rowCount         Number of rows
  * @param array $sort           sort array
  * @return array                Result set
  * @access public
  * @static
  */
 public static function &getRows($mailing_id, $job_id = null, $offset = null, $rowCount = null, $sort = null)
 {
     $dao = new CRM_Core_Dao();
     $queue = self::getTableName();
     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
     $job = CRM_Mailing_BAO_Job::getTableName();
     $contact = CRM_Contact_BAO_Contact::getTableName();
     $email = CRM_Core_BAO_Email::getTableName();
     $orderBy = "sort_name ASC, {$job}.start_date DESC";
     if ($sort) {
         if (is_string($sort)) {
             $orderBy = $sort;
         } else {
             $orderBy = trim($sort->orderBy());
         }
     }
     $query = "\n            SELECT      {$contact}.display_name as display_name,\n                        {$contact}.id as contact_id,\n                        {$email}.email as email,\n                        {$job}.start_date as date\n            FROM        {$contact}\n            INNER JOIN  {$queue}\n                    ON  {$queue}.contact_id = {$contact}.id\n            INNER JOIN  {$email}\n                    ON  {$queue}.email_id = {$email}.id\n            INNER JOIN  {$job}\n                    ON  {$queue}.job_id = {$job}.id\n            INNER JOIN  {$mailing}\n                    ON  {$job}.mailing_id = {$mailing}.id\n                    AND {$job}.is_test = 0\n            WHERE       {$mailing}.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
     if (!empty($job_id)) {
         $query .= " AND {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
     }
     $query .= " ORDER BY {$orderBy} ";
     if ($offset || $rowCount) {
         //Added "||$rowCount" to avoid displaying all records on first page
         $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
     }
     $dao->query($query);
     $results = array();
     while ($dao->fetch()) {
         $url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$dao->contact_id}");
         $results[] = array('name' => "<a href=\"{$url}\">{$dao->display_name}</a>", 'email' => $dao->email, 'date' => CRM_Utils_Date::customFormat($dao->date));
     }
     return $results;
 }