3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Campaign_BAO_Petition
extends CRM_Campaign_BAO_Survey
{
22 public function __construct() {
23 parent
::__construct();
24 // expire cookie in one day
25 $this->cookieExpire
= (1 * 60 * 60 * 24);
29 * Get Petition Details for dashboard.
31 * @param array $params
32 * @param bool $onlyCount
36 public static function getPetitionSummary($params = [], $onlyCount = FALSE) {
37 //build the limit and order clause.
38 $limitClause = $orderByClause = $lookupTableJoins = NULL;
41 'sort' => 'created_date',
44 'sortOrder' => 'desc',
46 foreach ($sortParams as $name => $default) {
47 if (!empty($params[$name])) {
48 $sortParams[$name] = $params[$name];
52 //need to lookup tables.
53 $orderOnPetitionTable = TRUE;
54 if ($sortParams['sort'] == 'campaign') {
55 $orderOnPetitionTable = FALSE;
57 LEFT JOIN civicrm_campaign campaign ON ( campaign.id = petition.campaign_id )';
58 $orderByClause = "ORDER BY campaign.title {$sortParams['sortOrder']}";
60 elseif ($sortParams['sort'] == 'activity_type') {
61 $orderOnPetitionTable = FALSE;
63 LEFT JOIN civicrm_option_value activity_type ON ( activity_type.value = petition.activity_type_id
64 OR petition.activity_type_id IS NULL )
65 INNER JOIN civicrm_option_group grp ON ( activity_type.option_group_id = grp.id AND grp.name = 'activity_type' )";
66 $orderByClause = "ORDER BY activity_type.label {$sortParams['sortOrder']}";
68 elseif ($sortParams['sort'] == 'isActive') {
69 $sortParams['sort'] = 'is_active';
71 if ($orderOnPetitionTable) {
72 $orderByClause = "ORDER BY petition.{$sortParams['sort']} {$sortParams['sortOrder']}";
74 $limitClause = "LIMIT {$sortParams['offset']}, {$sortParams['rowCount']}";
77 //build the where clause.
78 $queryParams = $where = [];
80 //we only have activity type as a
81 //difference between survey and petition.
82 $petitionTypeID = CRM_Core_PseudoConstant
::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Petition');
83 if ($petitionTypeID) {
84 $where[] = "( petition.activity_type_id = %1 )";
85 $queryParams[1] = [$petitionTypeID, 'Positive'];
87 if (!empty($params['title'])) {
88 $where[] = "( petition.title LIKE %2 )";
89 $queryParams[2] = ['%' . trim($params['title']) . '%', 'String'];
91 if (!empty($params['campaign_id'])) {
92 $where[] = '( petition.campaign_id = %3 )';
93 $queryParams[3] = [$params['campaign_id'], 'Positive'];
97 $whereClause = ' WHERE ' . implode(" \nAND ", $where);
101 SELECT petition.id as id,
102 petition.title as title,
103 petition.is_active as is_active,
104 petition.result_id as result_id,
105 petition.is_default as is_default,
106 petition.campaign_id as campaign_id,
107 petition.activity_type_id as activity_type_id';
110 $selectClause = 'SELECT COUNT(*)';
112 $fromClause = 'FROM civicrm_survey petition';
114 $query = "{$selectClause} {$fromClause} {$whereClause} {$orderByClause} {$limitClause}";
117 return (int) CRM_Core_DAO
::singleValueQuery($query, $queryParams);
131 $petition = CRM_Core_DAO
::executeQuery($query, $queryParams);
132 while ($petition->fetch()) {
133 foreach ($properties as $property) {
134 $petitions[$petition->id
][$property] = $petition->$property;
142 * Get the petition count.
145 public static function getPetitionCount() {
146 $whereClause = 'WHERE ( 1 )';
148 $petitionTypeID = CRM_Core_PseudoConstant
::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Petition');
149 if ($petitionTypeID) {
150 $whereClause = "WHERE ( petition.activity_type_id = %1 )";
151 $queryParams[1] = [$petitionTypeID, 'Positive'];
153 $query = "SELECT COUNT(*) FROM civicrm_survey petition {$whereClause}";
155 return (int) CRM_Core_DAO
::singleValueQuery($query, $queryParams);
159 * Takes an associative array and creates a petition signature activity.
161 * @param array $params
162 * (reference ) an assoc array of name/value pairs.
165 * CRM_Campaign_BAO_Petition or NULl or void
167 public function createSignature(&$params) {
168 if (empty($params)) {
172 if (!isset($params['sid'])) {
173 $statusMsg = ts('No survey sid parameter. Cannot process signature.');
174 CRM_Core_Session
::setStatus($statusMsg, ts('Sorry'), 'error');
178 if (isset($params['contactId'])) {
180 // add signature as activity with survey id as source id
181 // get the activity type id associated with this survey
182 $surveyInfo = CRM_Campaign_BAO_Petition
::getSurveyInfo($params['sid']);
185 // 1-Schedule, 2-Completed
188 'source_contact_id' => $params['contactId'],
189 'target_contact_id' => $params['contactId'],
190 'source_record_id' => $params['sid'],
191 'subject' => $surveyInfo['title'],
192 'activity_type_id' => $surveyInfo['activity_type_id'],
193 'activity_date_time' => date("YmdHis"),
194 'status_id' => $params['statusId'],
195 'activity_campaign_id' => $params['activity_campaign_id'],
199 // *** check for activity using source id - if already signed
200 $activity = CRM_Activity_BAO_Activity
::create($activityParams);
202 // save activity custom data
203 if (!empty($params['custom']) &&
204 is_array($params['custom'])
206 CRM_Core_BAO_CustomValueTable
::store($params['custom'], 'civicrm_activity', $activity->id
);
209 // Set browser cookie to indicate this petition was already signed.
210 $config = CRM_Core_Config
::singleton();
211 $url_parts = parse_url($config->userFrameworkBaseURL
);
212 setcookie('signed_' . $params['sid'], $activity->id
, time() +
$this->cookieExpire
, $url_parts['path'], $url_parts['host'], CRM_Utils_System
::isSSL());
219 * @param int $activity_id
220 * @param int $contact_id
221 * @param int $petition_id
225 public function confirmSignature($activity_id, $contact_id, $petition_id) {
226 // change activity status to completed (status_id = 2)
227 // I wonder why do we need contact_id when we have activity_id anyway? [chastell]
228 $sql = 'UPDATE civicrm_activity SET status_id = 2 WHERE id = %1';
229 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
230 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
232 1 => [$activity_id, 'Integer'],
233 2 => [$contact_id, 'Integer'],
234 3 => [$sourceID, 'Integer'],
236 CRM_Core_DAO
::executeQuery($sql, $params);
238 $sql = 'UPDATE civicrm_activity_contact SET contact_id = %2 WHERE activity_id = %1 AND record_type_id = %3';
239 CRM_Core_DAO
::executeQuery($sql, $params);
240 // remove 'Unconfirmed' tag for this contact
241 $tag_name = Civi
::settings()->get('tag_unconfirmed');
244 DELETE FROM civicrm_entity_tag
245 WHERE entity_table = 'civicrm_contact'
247 AND tag_id = ( SELECT id FROM civicrm_tag WHERE name = %2 )";
249 1 => [$contact_id, 'Integer'],
250 2 => [$tag_name, 'String'],
252 CRM_Core_DAO
::executeQuery($sql, $params);
253 // validate arguments to setcookie are numeric to prevent header manipulation
254 if (isset($petition_id) && is_numeric($petition_id)
255 && isset($activity_id) && is_numeric($activity_id)) {
256 // set permanent cookie to indicate this users email address now confirmed
257 $config = CRM_Core_Config
::singleton();
258 $url_parts = parse_url($config->userFrameworkBaseURL
);
259 setcookie("confirmed_{$petition_id}",
261 time() +
$this->cookieExpire
,
264 CRM_Utils_System
::isSSL()
269 throw new CRM_Core_Exception(ts('Petition Id and/or Activity Id is not of the type Positive.'));
274 * Get Petition Signature Total.
276 * @param int $surveyId
280 public static function getPetitionSignatureTotalbyCountry($surveyId) {
283 SELECT count(civicrm_address.country_id) as total,
284 IFNULL(country_id,'') as country_id,IFNULL(iso_code,'') as country_iso, IFNULL(civicrm_country.name,'') as country
285 FROM ( civicrm_activity a, civicrm_survey, civicrm_contact )
286 LEFT JOIN civicrm_address ON civicrm_address.contact_id = civicrm_contact.id AND civicrm_address.is_primary = 1
287 LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id
288 LEFT JOIN civicrm_activity_contact ac ON ( ac.activity_id = a.id AND ac.record_type_id = %2 )
290 ac.contact_id = civicrm_contact.id AND
291 a.activity_type_id = civicrm_survey.activity_type_id AND
292 civicrm_survey.id = %1 AND
293 a.source_record_id = %1 ";
295 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
296 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
298 1 => [$surveyId, 'Integer'],
299 2 => [$sourceID, 'Integer'],
301 $sql .= " GROUP BY civicrm_address.country_id";
302 $fields = ['total', 'country_id', 'country_iso', 'country'];
304 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
305 while ($dao->fetch()) {
307 foreach ($fields as $field) {
308 $row[$field] = $dao->$field;
316 * Get Petition Signature Total.
318 * @param int $surveyId
322 public static function getPetitionSignatureTotal($surveyId) {
323 $surveyInfo = CRM_Campaign_BAO_Petition
::getSurveyInfo((int) $surveyId);
324 //$activityTypeID = $surveyInfo['activity_type_id'];
327 status_id,count(id) as total
328 FROM civicrm_activity
330 source_record_id = " . (int) $surveyId . " AND activity_type_id = " . (int) $surveyInfo['activity_type_id'] . " GROUP BY status_id";
334 $dao = CRM_Core_DAO
::executeQuery($sql);
335 while ($dao->fetch()) {
336 $total +
= $dao->total
;
337 $statusTotal['status'][$dao->status_id
] = $dao->total
;
339 $statusTotal['count'] = $total;
344 * @param int $surveyId
348 public static function getSurveyInfo($surveyId = NULL) {
352 SELECT activity_type_id,
355 ov.label AS activity_type
356 FROM civicrm_survey s, civicrm_option_value ov, civicrm_option_group og
357 WHERE s.id = " . (int) $surveyId . "
358 AND s.activity_type_id = ov.value
359 AND ov.option_group_id = og.id
360 AND og.name = 'activity_type'";
362 $dao = CRM_Core_DAO
::executeQuery($sql);
363 while ($dao->fetch()) {
364 //$survey['campaign_id'] = $dao->campaign_id;
365 //$survey['campaign_name'] = $dao->campaign_name;
366 $surveyInfo['activity_type'] = $dao->activity_type
;
367 $surveyInfo['activity_type_id'] = $dao->activity_type_id
;
368 $surveyInfo['title'] = $dao->title
;
375 * Get Petition Signature Details.
377 * @param int $surveyId
378 * @param int $status_id
382 public static function getPetitionSignature($surveyId, $status_id = NULL) {
384 // sql injection protection
385 $surveyId = (int) $surveyId;
390 a.source_record_id as survey_id,
391 a.activity_date_time,
393 civicrm_contact.id as contact_id,
394 civicrm_contact.contact_type,civicrm_contact.contact_sub_type,image_URL,
395 first_name,last_name,sort_name,
396 employer_id,organization_name,
398 IFNULL(gender_id,'') AS gender_id,
399 IFNULL(state_province_id,'') AS state_province_id,
400 IFNULL(country_id,'') as country_id,IFNULL(iso_code,'') as country_iso, IFNULL(civicrm_country.name,'') as country
401 FROM (civicrm_activity a, civicrm_survey, civicrm_contact )
402 LEFT JOIN civicrm_activity_contact ac ON ( ac.activity_id = a.id AND ac.record_type_id = %3 )
403 LEFT JOIN civicrm_address ON civicrm_address.contact_id = civicrm_contact.id AND civicrm_address.is_primary = 1
404 LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id
406 ac.contact_id = civicrm_contact.id AND
407 a.activity_type_id = civicrm_survey.activity_type_id AND
408 civicrm_survey.id = %1 AND
409 a.source_record_id = %1 ";
411 $params = [1 => [$surveyId, 'Integer']];
414 $sql .= " AND status_id = %2";
415 $params[2] = [$status_id, 'Integer'];
417 $sql .= " ORDER BY a.activity_date_time";
419 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
420 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
421 $params[3] = [$sourceID, 'Integer'];
427 'activity_date_time',
440 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
441 while ($dao->fetch()) {
443 foreach ($fields as $field) {
444 $row[$field] = $dao->$field;
452 * Check if contact has signed this petition.
454 * @param int $surveyId
455 * @param int $contactId
459 public static function checkSignature($surveyId, $contactId) {
461 $surveyInfo = CRM_Campaign_BAO_Petition
::getSurveyInfo($surveyId);
463 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
464 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
468 a.source_record_id AS source_record_id,
469 ac.contact_id AS source_contact_id,
470 a.activity_date_time AS activity_date_time,
471 a.activity_type_id AS activity_type_id,
472 a.status_id AS status_id,
474 FROM civicrm_activity a
475 INNER JOIN civicrm_activity_contact ac ON (ac.activity_id = a.id AND ac.record_type_id = %5)
476 WHERE a.source_record_id = %2
477 AND a.activity_type_id = %3
478 AND ac.contact_id = %4
481 1 => [$surveyInfo['title'], 'String'],
482 2 => [$surveyId, 'Integer'],
483 3 => [$surveyInfo['activity_type_id'], 'Integer'],
484 4 => [$contactId, 'Integer'],
485 5 => [$sourceID, 'Integer'],
488 $dao = CRM_Core_DAO
::executeQuery($sql, $params);
489 while ($dao->fetch()) {
490 $signature[$dao->id
]['id'] = $dao->id
;
491 $signature[$dao->id
]['source_record_id'] = $dao->source_record_id
;
492 $signature[$dao->id
]['source_contact_id'] = CRM_Contact_BAO_Contact
::displayName($dao->source_contact_id
);
493 $signature[$dao->id
]['activity_date_time'] = $dao->activity_date_time
;
494 $signature[$dao->id
]['activity_type_id'] = $dao->activity_type_id
;
495 $signature[$dao->id
]['status_id'] = $dao->status_id
;
496 $signature[$dao->id
]['survey_title'] = $dao->survey_title
;
497 $signature[$dao->id
]['contactId'] = $dao->source_contact_id
;
504 * Takes an associative array and sends a thank you or email verification email.
506 * @param array $params
507 * (reference ) an assoc array of name/value pairs.
509 * @param int $sendEmailMode
513 public static function sendEmail($params, $sendEmailMode) {
516 * CRM_Campaign_Form_Petition_Signature::EMAIL_THANK
517 * connected user via login/pwd - thank you
518 * or dedupe contact matched who doesn't have a tag CIVICRM_TAG_UNCONFIRMED - thank you
519 * or login using fb connect - thank you + click to add msg to fb wall
521 * CRM_Campaign_Form_Petition_Signature::EMAIL_CONFIRM
522 * send a confirmation request email
525 // check if the group defined by CIVICRM_PETITION_CONTACTS exists, else create it
526 $petitionGroupName = Civi
::settings()->get('petition_contacts');
528 $dao = new CRM_Contact_DAO_Group();
529 $dao->title
= $petitionGroupName;
530 if (!$dao->find(TRUE)) {
532 $dao->visibility
= 'User and User Admin Only';
535 $group_id = $dao->id
;
538 $petitionParams['id'] = $params['sid'];
540 CRM_Campaign_BAO_Survey
::retrieve($petitionParams, $petitionInfo);
541 if (empty($petitionInfo)) {
542 throw new CRM_Core_Exception('Petition doesn\'t exist.');
545 //get the default domain email address.
546 list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain
::getNameAndEmail();
548 $emailDomain = CRM_Core_BAO_MailSettings
::defaultDomain();
550 $toName = CRM_Contact_BAO_Contact
::displayName($params['contactId']);
552 $replyTo = CRM_Core_BAO_Domain
::getNoReplyEmailAddress();
554 // set additional general message template params (custom tokens to use in email msg templates)
555 // tokens then available in msg template as {$petition.title}, etc
556 $petitionTokens['title'] = $petitionInfo['title'];
557 $petitionTokens['petitionId'] = $params['sid'];
558 $tplParams['survey_id'] = $params['sid'];
559 $tplParams['petitionTitle'] = $petitionInfo['title'];
560 $tplParams['petition'] = $petitionTokens;
562 switch ($sendEmailMode) {
563 case CRM_Campaign_Form_Petition_Signature
::EMAIL_THANK
:
565 // add this contact to the CIVICRM_PETITION_CONTACTS group
566 // Cannot pass parameter 1 by reference
567 $p = [$params['contactId']];
568 CRM_Contact_BAO_GroupContact
::addContactsToGroup($p, $group_id, 'API');
570 if ($params['email-Primary']) {
571 CRM_Core_BAO_MessageTemplate
::sendTemplate(
573 'groupName' => 'msg_tpl_workflow_petition',
574 'valueName' => 'petition_sign',
575 'contactId' => $params['contactId'],
576 'tplParams' => $tplParams,
577 'from' => "\"{$domainEmailName}\" <{$domainEmailAddress}>",
579 'toEmail' => $params['email-Primary'],
580 'replyTo' => $replyTo,
581 'petitionId' => $params['sid'],
582 'petitionTitle' => $petitionInfo['title'],
588 case CRM_Campaign_Form_Petition_Signature
::EMAIL_CONFIRM
:
589 // create mailing event subscription record for this contact
590 // this will allow using a hash key to confirm email address by sending a url link
591 $se = CRM_Mailing_Event_BAO_Subscribe
::subscribe($group_id,
592 $params['email-Primary'],
593 $params['contactId'],
597 // require_once 'CRM/Core/BAO/Domain.php';
598 // $domain = CRM_Core_BAO_Domain::getDomain();
599 $config = CRM_Core_Config
::singleton();
600 $localpart = CRM_Core_BAO_MailSettings
::defaultLocalpart();
602 $replyTo = implode($config->verpSeparator
,
611 $confirmUrl = CRM_Utils_System
::url('civicrm/petition/confirm',
612 "reset=1&cid={$se->contact_id}&sid={$se->id}&h={$se->hash}&a={$params['activityId']}&pid={$params['sid']}",
615 $confirmUrlPlainText = CRM_Utils_System
::url('civicrm/petition/confirm',
616 "reset=1&cid={$se->contact_id}&sid={$se->id}&h={$se->hash}&a={$params['activityId']}&pid={$params['sid']}",
622 // set email specific message template params and assign to tplParams
623 $petitionTokens['confirmUrl'] = $confirmUrl;
624 $petitionTokens['confirmUrlPlainText'] = $confirmUrlPlainText;
625 $tplParams['petition'] = $petitionTokens;
627 if ($params['email-Primary']) {
628 CRM_Core_BAO_MessageTemplate
::sendTemplate(
630 'groupName' => 'msg_tpl_workflow_petition',
631 'valueName' => 'petition_confirmation_needed',
632 'contactId' => $params['contactId'],
633 'tplParams' => $tplParams,
634 'from' => "\"{$domainEmailName}\" <{$domainEmailAddress}>",
636 'toEmail' => $params['email-Primary'],
637 'replyTo' => $replyTo,
638 'petitionId' => $params['sid'],
639 'petitionTitle' => $petitionInfo['title'],
640 'confirmUrl' => $confirmUrl,