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