function whereClause(&$params, $sortBy = TRUE) { $values = array(); $clauses = array(); $title = $this->get('mailing_name'); //echo " name=$title "; if ($title) { $clauses[] = 'name LIKE %1'; if (strpos($title, '%') !== FALSE) { $params[1] = array($title, 'String', FALSE); } else { $params[1] = array($title, 'String', TRUE); } } if ($sortBy && $this->_sortByCharacter !== NULL) { $clauses[] = "name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_sortByCharacter)) . "%'"; } $campainIds = $this->get('campaign_id'); if (!CRM_Utils_System::isNull($campainIds)) { if (!is_array($campainIds)) { $campaignIds = array($campaignIds); } $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campainIds)) . ' ) )'; } return implode(' AND ', $clauses); }
/** * where / qill clause for sorting by character * * @return void * @access public */ function sortByCharacter(&$values) { list($name, $op, $value, $grouping, $wildcard) = $values; $name = trim($value); $cond = " contact_a.sort_name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($name)) . "%'"; $this->_where[$grouping][] = $cond; $this->_qill[$grouping][] = ts('Showing only Contacts starting with: \'%1\'', array(1 => $name)); }
/** * @param array $params * @param bool $sortBy * @param $force * * @return string */ public function whereClause(&$params, $sortBy = TRUE, $force) { $values = array(); $clauses = array(); $title = $this->get('title'); $createdId = $this->get('cid'); if ($createdId) { $clauses[] = "(created_id = {$createdId})"; } if ($title) { $clauses[] = "title LIKE %1"; if (strpos($title, '%') !== FALSE) { $params[1] = array(trim($title), 'String', FALSE); } else { $params[1] = array(trim($title), 'String', TRUE); } } $value = $this->get('event_type_id'); if ($value) { if (is_array($value)) { $type = implode(',', $value); } $clauses[] = "event_type_id IN ({$type})"; } $eventsByDates = $this->get('eventsByDates'); if ($this->_searchResult) { if ($eventsByDates) { $from = $this->get('start_date'); if (!CRM_Utils_System::isNull($from)) { $clauses[] = '( start_date >= %3 OR start_date IS NULL )'; $params[3] = array($from, 'String'); } $to = $this->get('end_date'); if (!CRM_Utils_System::isNull($to)) { $clauses[] = '( end_date <= %4 OR end_date IS NULL )'; $params[4] = array($to, 'String'); } } else { $curDate = date('YmdHis'); $clauses[5] = "(end_date >= {$curDate} OR end_date IS NULL)"; } } else { $curDate = date('YmdHis'); $clauses[] = "(end_date >= {$curDate} OR end_date IS NULL)"; } if ($sortBy && $this->_sortByCharacter !== NULL) { $clauses[] = "title LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_sortByCharacter)) . "%'"; } $campaignIds = $this->get('campaign_id'); if (!CRM_Utils_System::isNull($campaignIds)) { if (!is_array($campaignIds)) { $campaignIds = array($campaignIds); } $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campaignIds)) . ' ) )'; } // don't do a the below assignment when doing a // AtoZ pager clause if ($sortBy) { if (count($clauses) > 1 || $eventsByDates) { $this->assign('isSearch', 1); } else { $this->assign('isSearch', 0); } } return !empty($clauses) ? implode(' AND ', $clauses) : '(1)'; }
/** * @param array $params * @param bool $sortBy * * @return int|string */ public function whereClause(&$params, $sortBy = TRUE) { // @todo Unused local variable can be safely removed. $values = $clauses = array(); $title = $this->get('title'); $createdId = $this->get('cid'); if ($createdId) { $clauses[] = "(created_id = {$createdId})"; } if ($title) { $clauses[] = "title LIKE %1"; if (strpos($title, '%') !== FALSE) { $params[1] = array(trim($title), 'String', FALSE); } else { $params[1] = array(trim($title), 'String', TRUE); } } $value = $this->get('financial_type_id'); $val = array(); if ($value) { if (is_array($value)) { foreach ($value as $k => $v) { if ($v) { $val[$k] = $k; } } $type = implode(',', $val); } // @todo Variable 'type' might not have been defined. $clauses[] = "financial_type_id IN ({$type})"; } if ($sortBy && $this->_sortByCharacter !== NULL) { $clauses[] = "title LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_sortByCharacter)) . "%'"; } $campaignIds = $this->getCampaignIds(); if (count($campaignIds) >= 1) { $clauses[] = '( campaign_id IN ( ' . implode(' , ', $campaignIds) . ' ) )'; } if (empty($clauses)) { // Let template know if user has run a search or not $this->assign('isSearch', 0); return 1; } else { $this->assign('isSearch', 1); } return implode(' AND ', $clauses); }
/** * @param array $params * @param bool $sortBy * * @return int|string */ public function whereClause(&$params, $sortBy = TRUE) { $values = $clauses = array(); $isFormSubmitted = $this->_parent->get('hidden_find_mailings'); $title = $this->_parent->get('mailing_name'); if ($title) { $clauses[] = 'name LIKE %1'; if (strpos($title, '%') !== FALSE) { $params[1] = array($title, 'String', FALSE); } else { $params[1] = array($title, 'String', TRUE); } } $dateClause1 = $dateClause2 = array(); $from = $this->_parent->get('mailing_from'); if (!CRM_Utils_System::isNull($from)) { if ($this->_parent->get('unscheduled')) { $dateClause1[] = 'civicrm_mailing.created_date >= %2'; } else { $dateClause1[] = 'civicrm_mailing_job.start_date >= %2'; $dateClause2[] = 'civicrm_mailing_job.scheduled_date >= %2'; } $params[2] = array($from, 'String'); } $to = $this->_parent->get('mailing_to'); if (!CRM_Utils_System::isNull($to)) { if ($this->_parent->get('unscheduled')) { $dateClause1[] = ' civicrm_mailing.created_date <= %3 '; } else { $dateClause1[] = 'civicrm_mailing_job.start_date <= %3'; $dateClause2[] = 'civicrm_mailing_job.scheduled_date <= %3'; } $params[3] = array($to, 'String'); } $dateClauses = array(); if (!empty($dateClause1)) { $dateClauses[] = implode(' AND ', $dateClause1); } if (!empty($dateClause2)) { $dateClauses[] = implode(' AND ', $dateClause2); } $dateClauses = implode(' OR ', $dateClauses); if (!empty($dateClauses)) { $clauses[] = "({$dateClauses})"; } if ($this->_parent->get('sms')) { $clauses[] = "civicrm_mailing.sms_provider_id IS NOT NULL"; } else { $clauses[] = "civicrm_mailing.sms_provider_id IS NULL"; } // get values submitted by form $isDraft = $this->_parent->get('status_unscheduled'); $isArchived = $this->_parent->get('is_archived'); $mailingStatus = $this->_parent->get('mailing_status'); if (!$isFormSubmitted && $this->_parent->get('scheduled')) { // mimic default behavior for scheduled screen $isArchived = 0; $mailingStatus = array('Scheduled' => 1, 'Complete' => 1, 'Running' => 1, 'Canceled' => 1); } if (!$isFormSubmitted && $this->_parent->get('archived')) { // mimic default behavior for archived screen $isArchived = 1; } if (!$isFormSubmitted && $this->_parent->get('unscheduled')) { // mimic default behavior for draft screen $isDraft = 1; } $statusClauses = array(); if ($isDraft) { $statusClauses[] = "civicrm_mailing.scheduled_id IS NULL"; } if (!empty($mailingStatus)) { $statusClauses[] = "civicrm_mailing_job.status IN ('" . implode("', '", array_keys($mailingStatus)) . "')"; } if (!empty($statusClauses)) { $clauses[] = "(" . implode(' OR ', $statusClauses) . ")"; } if (isset($isArchived)) { if ($isArchived) { $clauses[] = "civicrm_mailing.is_archived = 1"; } else { $clauses[] = "(civicrm_mailing.is_archived IS NULL OR civicrm_mailing.is_archived = 0)"; } } if ($sortBy && $this->_parent->_sortByCharacter !== NULL) { $clauses[] = "name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_parent->_sortByCharacter)) . "%'"; } // dont do a the below assignement when doing a // AtoZ pager clause if ($sortBy) { if (count($clauses) > 1) { $this->_parent->assign('isSearch', 1); } else { $this->_parent->assign('isSearch', 0); } } $createOrSentBy = $this->_parent->get('sort_name'); if (!CRM_Utils_System::isNull($createOrSentBy)) { $clauses[] = '(createdContact.sort_name LIKE %4 OR scheduledContact.sort_name LIKE %4)'; $params[4] = array('%' . $createOrSentBy . '%', 'String'); } $createdId = $this->_parent->get('createdId'); if ($createdId) { $clauses[] = "(created_id = {$createdId})"; $params[5] = array($createdId, 'Integer'); } $campainIds = $this->_parent->get('campaign_id'); if (!CRM_Utils_System::isNull($campainIds)) { if (!is_array($campainIds)) { $campaignIds = array($campaignIds); } $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campainIds)) . ' ) )'; } if (empty($clauses)) { return 1; } return implode(' AND ', $clauses); }
/** * Browse all custom data groups. * * * @param null $action * * @return void * @access public * @static */ function browse($action = NULL) { $this->_sortByCharacter = CRM_Utils_Request::retrieve('sortByCharacter', 'String', $this); if ($this->_sortByCharacter == 1 || !empty($_POST)) { $this->_sortByCharacter = ''; } $status = CRM_PCP_BAO_PCP::buildOptions('status_id', 'create'); $pcpSummary = $params = array(); $whereClause = NULL; if (!empty($_POST) || !empty($_GET['page_type'])) { if (!empty($_POST['status_id'])) { $whereClause = ' AND cp.status_id = %1'; $params['1'] = array($_POST['status_id'], 'Integer'); } if (!empty($_POST['page_type'])) { $whereClause .= ' AND cp.page_type = %2'; $params['2'] = array($_POST['page_type'], 'String'); } elseif (!empty($_GET['page_type'])) { $whereClause .= ' AND cp.page_type = %2'; $params['2'] = array($_GET['page_type'], 'String'); } if (!empty($_POST['page_id'])) { $whereClause .= ' AND cp.page_id = %4 AND cp.page_type = "contribute"'; $params['4'] = array($_POST['page_id'], 'Integer'); } if (!empty($_POST['event_id'])) { $whereClause .= ' AND cp.page_id = %5 AND cp.page_type = "event"'; $params['5'] = array($_POST['event_id'], 'Integer'); } if ($whereClause) { $this->set('whereClause', $whereClause); $this->set('params', $params); } else { $this->set('whereClause', NULL); $this->set('params', NULL); } } $approvedId = CRM_Core_OptionGroup::getValue('pcp_status', 'Approved', 'name'); //check for delete CRM-4418 $allowToDelete = CRM_Core_Permission::check('delete in CiviContribute'); // get all contribution pages $query = "SELECT id, title, start_date, end_date FROM civicrm_contribution_page WHERE (1)"; $cpages = CRM_Core_DAO::executeQuery($query); while ($cpages->fetch()) { $pages['contribute'][$cpages->id]['id'] = $cpages->id; $pages['contribute'][$cpages->id]['title'] = $cpages->title; $pages['contribute'][$cpages->id]['start_date'] = $cpages->start_date; $pages['contribute'][$cpages->id]['end_date'] = $cpages->end_date; } // get all event pages. pcp campaign start and end dates for event related pcp's use the online registration start and end dates, // altho if target is contribution page this might not be correct. fixme? dgg $query = "SELECT id, title, start_date, end_date, registration_start_date, registration_end_date\n FROM civicrm_event\n WHERE is_template IS NULL OR is_template != 1"; $epages = CRM_Core_DAO::executeQuery($query); while ($epages->fetch()) { $pages['event'][$epages->id]['id'] = $epages->id; $pages['event'][$epages->id]['title'] = $epages->title; $pages['event'][$epages->id]['start_date'] = $epages->registration_start_date; $pages['event'][$epages->id]['end_date'] = $epages->registration_end_date; } $params = $this->get('params') ? $this->get('params') : array(); $title = '1'; if ($this->_sortByCharacter !== NULL) { $clauses[] = "cp.title LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_sortByCharacter)) . "%'"; } $query = "\n SELECT cp.id, cp.contact_id , cp.status_id, cp.title, cp.is_active, cp.page_type, cp.page_id\n FROM civicrm_pcp cp\n WHERE {$title}" . $this->get('whereClause') . " ORDER BY cp.status_id"; $pcp = CRM_Core_DAO::executeQuery($query, $params); while ($pcp->fetch()) { $action = array_sum(array_keys($this->links())); $contact = CRM_Contact_BAO_Contact::getDisplayAndImage($pcp->contact_id); $class = ''; if ($pcp->status_id != $approvedId || $pcp->is_active != 1) { $class = 'disabled'; } switch ($pcp->status_id) { case 2: $action -= CRM_Core_Action::RENEW; break; case 3: $action -= CRM_Core_Action::REVERT; break; } switch ($pcp->is_active) { case 1: $action -= CRM_Core_Action::ENABLE; break; case 0: $action -= CRM_Core_Action::DISABLE; break; } if (!$allowToDelete) { $action -= CRM_Core_Action::DELETE; } $page_type = $pcp->page_type; $page_id = (int) $pcp->page_id; if ($pages[$page_type][$page_id]['title'] == '' || $pages[$page_type][$page_id]['title'] == NULL) { $title = '(no title found for ' . $page_type . ' id ' . $page_id . ')'; } else { $title = $pages[$page_type][$page_id]['title']; } if ($pcp->page_type == 'contribute') { $pageUrl = CRM_Utils_System::url('civicrm/' . $page_type . '/transact', 'reset=1&id=' . $pcp->page_id); } else { $pageUrl = CRM_Utils_System::url('civicrm/' . $page_type . '/register', 'reset=1&id=' . $pcp->page_id); } $pcpSummary[$pcp->id] = array('id' => $pcp->id, 'start_date' => $pages[$page_type][$page_id]['start_date'], 'end_date' => $pages[$page_type][$page_id]['end_date'], 'supporter' => $contact['0'], 'supporter_id' => $pcp->contact_id, 'status_id' => $status[$pcp->status_id], 'page_id' => $page_id, 'page_title' => $title, 'page_url' => $pageUrl, 'page_type' => $page_type, 'action' => CRM_Core_Action::formLink(self::links(), $action, array('id' => $pcp->id), ts('more'), FALSE, 'contributionpage.pcp.list', 'PCP', $pcp->id), 'title' => $pcp->title, 'class' => $class); } $this->search(); $this->pagerAToZ($this->get('whereClause'), $params); $this->assign('rows', $pcpSummary); // Let template know if user has run a search or not if ($this->get('whereClause')) { $this->assign('isSearch', 1); } else { $this->assign('isSearch', 0); } }
/** * @param $params * @param bool $sortBy * * @return int|string */ function whereClause(&$params, $sortBy = TRUE) { $values = $clauses = array(); $title = $this->get('title'); $createdId = $this->get('cid'); if ($createdId) { $clauses[] = "(created_id = {$createdId})"; } if ($title) { $clauses[] = "title LIKE %1"; if (strpos($title, '%') !== FALSE) { $params[1] = array(trim($title), 'String', FALSE); } else { $params[1] = array(trim($title), 'String', TRUE); } } $value = $this->get('financial_type_id'); $val = array(); if ($value) { if (is_array($value)) { foreach ($value as $k => $v) { if ($v) { $val[$k] = $k; } } $type = implode(',', $val); } $clauses[] = "financial_type_id IN ({$type})"; } if ($sortBy && $this->_sortByCharacter !== NULL) { $clauses[] = "title LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_sortByCharacter)) . "%'"; } $campainIds = $this->get('campaign_id'); if (!CRM_Utils_System::isNull($campainIds)) { if (!is_array($campainIds)) { $campaignIds = array($campaignIds); } $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campainIds)) . ' ) )'; } if (empty($clauses)) { // Let template know if user has run a search or not $this->assign('isSearch', 0); return 1; } else { $this->assign('isSearch', 1); } return implode(' AND ', $clauses); }
/** * @param array $params * @param bool $sortBy * @param $force * * @return string */ public function whereClause(&$params, $sortBy = TRUE, $force) { $values = array(); $clauses = array(); $title = $this->get('title'); $createdId = $this->get('cid'); //Customization for customFields $sql = "SELECT f.column_name\n FROM civicrm_custom_field f\n LEFT JOIN civicrm_custom_group g ON g.id = f.custom_group_id\n WHERE g.name = 'Courses'"; $dao = CRM_Core_DAO::executeQuery($sql); while ($dao->fetch()) { if ($customName = $this->get($dao->column_name)) { $clauses[] = "{$dao->column_name} LIKE '%{$customName}%'"; } } if ($createdId) { $clauses[] = "(created_id = {$createdId})"; } if ($title) { $clauses[] = "title LIKE %1"; if (strpos($title, '%') !== FALSE) { $params[1] = array(trim($title), 'String', FALSE); } else { $params[1] = array(trim($title), 'String', TRUE); } } $value = $this->get('event_type_id'); if ($value) { if (is_array($value)) { $type = implode(',', $value); } $clauses[] = "event_type_id IN ({$type})"; } $eventsByDates = $this->get('eventsByDates'); if ($this->_searchResult) { if ($eventsByDates) { $from = $this->get('start_date'); if (!CRM_Utils_System::isNull($from)) { $clauses[] = '( start_date >= %3 OR start_date IS NULL )'; $params[3] = array($from, 'String'); } $to = $this->get('end_date'); if (!CRM_Utils_System::isNull($to)) { $clauses[] = '( end_date <= %4 OR end_date IS NULL )'; $params[4] = array($to, 'String'); } } else { $curDate = date('YmdHis'); $clauses[5] = "(end_date >= {$curDate} OR end_date IS NULL)"; } } else { $curDate = date('YmdHis'); $clauses[] = "(end_date >= {$curDate} OR end_date IS NULL)"; } if ($sortBy && $this->_sortByCharacter !== NULL) { $clauses[] = "title LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_sortByCharacter)) . "%'"; } $campaignIds = $this->get('campaign_id'); if (!CRM_Utils_System::isNull($campaignIds)) { if (!is_array($campaignIds)) { $campaignIds = array($campaignIds); } $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campaignIds)) . ' ) )'; } // don't do a the below assignment when doing a // AtoZ pager clause if ($sortBy) { if (count($clauses) > 1 || $eventsByDates) { $this->assign('isSearch', 1); } else { $this->assign('isSearch', 0); } } return !empty($clauses) ? implode(' AND ', $clauses) : '(1)'; }
function whereClause(&$params, $sortBy = TRUE) { $values = $clauses = array(); $title = $this->_parent->get('mailing_name'); if ($title) { $clauses[] = 'name LIKE %1'; if (strpos($title, '%') !== FALSE) { $params[1] = array($title, 'String', FALSE); } else { $params[1] = array($title, 'String', TRUE); } } $from = $this->_parent->get('mailing_from'); if (!CRM_Utils_System::isNull($from)) { $dateClause1[] = 'civicrm_mailing_job.start_date >= %2'; $dateClause2[] = 'civicrm_mailing_job.scheduled_date >= %2'; $params[2] = array($from, 'String'); } $to = $this->_parent->get('mailing_to'); if (!CRM_Utils_System::isNull($to)) { $dateClause1[] = 'civicrm_mailing_job.start_date <= %3'; $dateClause2[] = 'civicrm_mailing_job.scheduled_date <= %3'; $params[3] = array($to, 'String'); } if (!empty($dateClause1)) { $dateClause1[] = "civicrm_mailing_job.status IN ('Complete', 'Running')"; $dateClause2[] = "civicrm_mailing_job.status IN ('Scheduled')"; $dateClause1 = implode(' AND ', $dateClause1); $dateClause2 = implode(' AND ', $dateClause2); $clauses[] = "( ({$dateClause1}) OR ({$dateClause2}) )"; } if ($this->_parent->get('unscheduled')) { $clauses[] = "civicrm_mailing_job.status is null"; $clauses[] = "civicrm_mailing.scheduled_id IS NULL"; } if ($this->_parent->get('archived')) { // CRM-6446: archived view should also show cancelled mailings $clauses[] = "(civicrm_mailing.is_archived = 1 OR civicrm_mailing_job.status = 'Canceled')"; } if ($this->_parent->get('sms')) { $clauses[] = "(civicrm_mailing.sms_provider_id IS NOT NULL)"; } else { $clauses[] = "(civicrm_mailing.sms_provider_id IS NULL)"; } // CRM-4290, do not show archived or unscheduled mails // on 'Scheduled and Sent Mailing' page selector if ($this->_parent->get('scheduled')) { $clauses[] = "civicrm_mailing.scheduled_id IS NOT NULL"; $clauses[] = "( civicrm_mailing.is_archived IS NULL OR civicrm_mailing.is_archived = 0 )"; $status = $this->_parent->get('mailing_status'); if (!empty($status)) { $status = array_keys($status); $status = implode("','", $status); $clauses[] = "civicrm_mailing_job.status IN ('{$status}')"; } else { $clauses[] = "civicrm_mailing_job.status IN ('Scheduled', 'Complete', 'Running')"; } } if ($sortBy && $this->_parent->_sortByCharacter !== NULL) { $clauses[] = "name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_parent->_sortByCharacter)) . "%'"; } // dont do a the below assignement when doing a // AtoZ pager clause if ($sortBy) { if (count($clauses) > 1) { $this->_parent->assign('isSearch', 1); } else { $this->_parent->assign('isSearch', 0); } } $createOrSentBy = $this->_parent->get('sort_name'); if (!CRM_Utils_System::isNull($createOrSentBy)) { $clauses[] = '(createdContact.sort_name LIKE %4 OR scheduledContact.sort_name LIKE %4)'; $params[4] = array('%' . $createOrSentBy . '%', 'String'); } $createdId = $this->_parent->get('createdId'); if ($createdId) { $clauses[] = "(created_id = {$createdId})"; $params[5] = array($createdId, 'Integer'); } $campainIds = $this->_parent->get('campaign_id'); if (!CRM_Utils_System::isNull($campainIds)) { if (!is_array($campainIds)) { $campaignIds = array($campaignIds); } $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campainIds)) . ' ) )'; } if (empty($clauses)) { return 1; } return implode(' AND ', $clauses); }