2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
38 /*********************************************************************************
40 * Description: Defines the English language pack for the base application.
41 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
42 * All Rights Reserved.
43 * Contributor(s): ______________________________________..
44 ********************************************************************************/
47 *returns a list of objects a message can be scoped by, the list contacts the current campaign
48 *name and list of all prospects associated with this campaign..
51 function get_message_scope_dom($campaign_id, $campaign_name,$db=null, $mod_strings=array()) {
53 //find prospect list attached to this campaign..
54 $query = "SELECT prospect_list_id, prospect_lists.name ";
55 $query .= "FROM prospect_list_campaigns ";
56 $query .= "INNER join prospect_lists on prospect_lists.id = prospect_list_campaigns.prospect_list_id ";
57 $query .= "WHERE prospect_lists.deleted = 0 ";
58 $query .= "AND prospect_list_campaigns.deleted=0 ";
59 $query .= "AND campaign_id='".$campaign_id."'";
60 $query.=" and prospect_lists.list_type not like 'exempt%'";
63 $db = DBManagerFactory::getInstance();
65 if (empty($mod_strings) or !isset($mod_strings['LBL_DEFAULT'])) {
66 global $current_language;
67 $mod_strings = return_module_language($current_language, 'Campaigns');
70 //add campaign to the result array.
71 //$return_array[$campaign_id]= $campaign_name . ' (' . $mod_strings['LBL_DEFAULT'] . ')';
73 $result=$db->query($query);
74 while(($row=$db->fetchByAssoc($result))!= null) {
75 $return_array[$row['prospect_list_id']]=$row['name'];
77 if (empty($return_array)) $return_array=array();
78 else return $return_array;
81 * Return bounce handling mailboxes for campaign.
83 * @param unknown_type $emails
84 * @param unknown_type $get_box_name, Set it to false if want to get "From Name" other than the InboundEmail Name.
85 * @return $get_name=true, bounce handling mailboxes' name; $get_name=false, bounce handling mailboxes' from name.
87 function get_campaign_mailboxes(&$emails, $get_name=true) {
88 if (!class_exists('InboundEmail')) {
89 require('modules/InboundEmail/InboundEmail.php');
91 $query = "select id,name,stored_options from inbound_email where mailbox_type='bounce' and status='Active' and deleted='0'";
92 $db = DBManagerFactory::getInstance();
93 $result=$db->query($query);
94 while(($row=$db->fetchByAssoc($result))!= null) {
96 $return_array[$row['id']] = $row['name'];
98 $return_array[$row['id']]= InboundEmail::get_stored_options('from_name',$row['name'],$row['stored_options']);
100 $emails[$row['id']]=InboundEmail::get_stored_options('from_addr','nobody@example.com',$row['stored_options']);
103 if (empty($return_array)) $return_array=array(''=>'');
104 return $return_array;
108 function get_campaign_mailboxes_with_stored_options() {
111 if(!class_exists('InboundEmail')) {
112 require('modules/InboundEmail/InboundEmail.php');
115 $q = "SELECT id, name, stored_options FROM inbound_email WHERE mailbox_type='bounce' AND status='Active' AND deleted='0'";
117 $db = DBManagerFactory::getInstance();
121 while($a = $db->fetchByAssoc($r)) {
122 $ret[$a['id']] = unserialize(base64_decode($a['stored_options']));
127 function log_campaign_activity($identifier, $activity, $update=true, $clicked_url_key=null) {
129 $return_array = array();
131 $db = DBManagerFactory::getInstance();
135 //check to see if the identifier has been replaced with Banner string
136 if($identifier == 'BANNER' && isset($clicked_url_key) && !empty($clicked_url_key))
138 // create md5 encrypted string using the client ip, this will be used for tracker id purposes
139 $enc_id = 'BNR'.md5($_SERVER['REMOTE_ADDR']);
141 //default the identifier to ip address
142 $identifier = $enc_id;
144 //if user has chosen to not use this mode of id generation, then replace identifier with plain guid.
145 //difference is that guid will generate a new campaign log for EACH CLICK!!
146 //encrypted generation will generate 1 campaign log and update the hit counter for each click
147 if(isset($sugar_config['campaign_banner_id_generation']) && $sugar_config['campaign_banner_id_generation'] != 'md5'){
148 $identifier = create_guid();
151 //retrieve campaign log.
152 $trkr_query = "select * from campaign_log where target_tracker_key='$identifier' and related_id = '$clicked_url_key'";
153 $current_trkr=$db->query($trkr_query);
154 $row=$db->fetchByAssoc($current_trkr);
156 //if campaign log is not retrieved (this is a new ip address or we have chosen to create
157 //unique entries for each click
158 if($row==null || empty($row)){
161 //retrieve campaign id
162 $trkr_query = "select ct.campaign_id from campaign_trkrs ct, campaigns c where c.id = ct.campaign_id and ct.id = '$clicked_url_key'";
163 $current_trkr=$db->query($trkr_query);
164 $row=$db->fetchByAssoc($current_trkr);
167 //create new campaign log with minimal info. Note that we are creating new unique id
168 //as target id, since we do not link banner/web campaigns to any users
170 $data['target_id']="'" . create_guid() . "'";
171 $data['target_type']= "'Prospects'";
172 $data['id']="'" . create_guid() . "'";
173 $data['campaign_id']="'" . $row['campaign_id'] . "'";
174 $data['target_tracker_key']="'" . $identifier . "'";
175 $data['activity_type']="'" . $activity . "'";
176 $data['activity_date']="'" . TimeDate::getInstance()->nowDb() . "'";
178 if (!empty($clicked_url_key)) {
179 $data['related_id']="'".$clicked_url_key."'";
180 $data['related_type']="'".'CampaignTrackers'."'";
183 //values for return array..
184 $return_array['target_id']=$data['target_id'];
185 $return_array['target_type']=$data['target_type'];
187 //create insert query for new campaign log
188 $insert_query="INSERT into campaign_log (" . implode(",",array_keys($data)) . ")";
189 $insert_query.=" VALUES (" . implode(",",array_values($data)) . ")";
190 $db->query($insert_query);
193 //campaign log already exists, so just set the return array and update hits column
194 $return_array['target_id']= $row['target_id'];
195 $return_array['target_type']= $row['target_type'];
196 $query1="update campaign_log set hits=hits+1 where id='{$row['id']}'";
197 $current=$db->query($query1);
202 //return array and exit
203 return $return_array;
209 $query1="select * from campaign_log where target_tracker_key='$identifier' and activity_type='$activity'";
210 if (!empty($clicked_url_key)) {
211 $query1.=" AND related_id='$clicked_url_key'";
213 $current=$db->query($query1);
214 $row=$db->fetchByAssoc($current);
217 $query="select * from campaign_log where target_tracker_key='$identifier' and activity_type='targeted'";
218 $targeted=$db->query($query);
219 $row=$db->fetchByAssoc($targeted);
221 //if activity is removed and target type is users, then a user is trying to opt out
222 //of emails. This is not possible as Users Table does not have opt out column.
223 if ($row && (strtolower($row['target_type']) == 'users' && $activity == 'removed' )) {
224 $return_array['target_id']= $row['target_id'];
225 $return_array['target_type']= $row['target_type'];
226 return $return_array;
229 $data['id']="'" . create_guid() . "'";
230 $data['campaign_id']="'" . $row['campaign_id'] . "'";
231 $data['target_tracker_key']="'" . $identifier . "'";
232 $data['target_id']="'" . $row['target_id'] . "'";
233 $data['target_type']="'" . $row['target_type'] . "'";
234 $data['activity_type']="'" . $activity . "'";
235 $data['activity_date']="'" . TimeDate::getInstance()->nowDb() . "'";
236 $data['list_id']="'" . $row['list_id'] . "'";
237 $data['marketing_id']="'" . $row['marketing_id'] . "'";
239 if (!empty($clicked_url_key)) {
240 $data['related_id']="'".$clicked_url_key."'";
241 $data['related_type']="'".'CampaignTrackers'."'";
243 //values for return array..
244 $return_array['target_id']=$row['target_id'];
245 $return_array['target_type']=$row['target_type'];
246 $insert_query="INSERT into campaign_log (" . implode(",",array_keys($data)) . ")";
247 $insert_query.=" VALUES (" . implode(",",array_values($data)) . ")";
248 $db->query($insert_query);
252 $return_array['target_id']= $row['target_id'];
253 $return_array['target_type']= $row['target_type'];
255 $query1="update campaign_log set hits=hits+1 where id='{$row['id']}'";
256 $current=$db->query($query1);
259 //check to see if this is a removal action
260 if ($row && $activity == 'removed' ) {
261 //retrieve campaign and check it's type, we are looking for newsletter Campaigns
262 $query = "SELECT campaigns.* FROM campaigns WHERE campaigns.id = '".$row['campaign_id']."' ";
263 $result = $db->query($query);
266 $c_row = $db->fetchByAssoc($result);
268 //if type is newsletter, then add campaign id to return_array for further processing.
269 if(isset($c_row['campaign_type']) && $c_row['campaign_type'] == 'NewsLetter'){
270 $return_array['campaign_id']=$c_row['id'];
274 return $return_array;
280 * This method is deprecated
281 * @deprecated 62_Joneses - June 24, 2011
282 * @see campaign_log_lead_or_contact_entry()
284 function campaign_log_lead_entry($campaign_id, $parent_bean,$child_bean,$activity_type){
285 campaign_log_lead_or_contact_entry($campaign_id, $parent_bean,$child_bean,$activity_type);
289 function campaign_log_lead_or_contact_entry($campaign_id, $parent_bean,$child_bean,$activity_type){
292 //create campaign tracker id and retrieve related bio bean
293 $tracker_id = create_guid();
294 //create new campaign log record.
295 $campaign_log = new CampaignLog();
296 $campaign_log->campaign_id = $campaign_id;
297 $campaign_log->target_tracker_key = $tracker_id;
298 $campaign_log->related_id = $parent_bean->id;
299 $campaign_log->related_type = $parent_bean->module_dir;
300 $campaign_log->target_id = $child_bean->id;
301 $campaign_log->target_type = $child_bean->module_dir;
302 $campaign_log->activity_date = $timedate->now();
303 $campaign_log->activity_type = $activity_type;
304 //save the campaign log entry
305 $campaign_log->save();
309 function get_campaign_urls($campaign_id) {
310 $return_array=array();
312 if (!empty($campaign_id)) {
314 $db = DBManagerFactory::getInstance();
316 $query1="select * from campaign_trkrs where campaign_id='$campaign_id' and deleted=0";
317 $current=$db->query($query1);
318 while (($row=$db->fetchByAssoc($current)) != null) {
319 $return_array['{'.$row['tracker_name'].'}']=$row['tracker_name'] . ' : ' . $row['tracker_url'];
322 return $return_array;
326 * Queries for the list
328 function get_subscription_lists_query($focus, $additional_fields = null) {
329 //get all prospect lists belonging to Campaigns of type newsletter
330 $all_news_type_pl_query = "select c.name, pl.list_type, plc.campaign_id, plc.prospect_list_id";
331 if(is_array($additional_fields) && !empty($additional_fields)) $all_news_type_pl_query .= ', ' . implode(', ', $additional_fields);
332 $all_news_type_pl_query .= " from prospect_list_campaigns plc , prospect_lists pl, campaigns c ";
335 $all_news_type_pl_query .= "where plc.campaign_id = c.id ";
336 $all_news_type_pl_query .= "and plc.prospect_list_id = pl.id ";
337 $all_news_type_pl_query .= "and c.campaign_type = 'NewsLetter' and pl.deleted = 0 and c.deleted=0 and plc.deleted=0 ";
338 $all_news_type_pl_query .= "and (pl.list_type like 'exempt%' or pl.list_type ='default') ";
340 $all_news_type_list =$focus->db->query($all_news_type_pl_query);
342 //build array of all newsletter campaigns
343 $news_type_list_arr = array();
344 while ($row = $focus->db->fetchByAssoc($all_news_type_list)){$news_type_list_arr[] = $row;}
346 //now get all the campaigns that the current user is assigned to
347 $all_plp_current = "select prospect_list_id from prospect_lists_prospects where related_id = '$focus->id' and deleted = 0 ";
349 //build array of prospect lists that this user belongs to
350 $current_plp =$focus->db->query($all_plp_current );
351 $current_plp_arr = array();
352 while ($row = $focus->db->fetchByAssoc($current_plp)){$current_plp_arr[] = $row;}
354 return array('current_plp_arr' => $current_plp_arr, 'news_type_list_arr' => $news_type_list_arr);
357 * This function takes in a bean from a lead, propsect, or contact and returns an array containing
358 * all subscription lists that the bean is a part of, and all the subscriptions that the bean is not
359 * a part of. The array elements have the key names of "subscribed" and "unsusbscribed". These elements contain an array
360 * of the corresponding list. In other words, the "subscribed" element holds another array that holds the subscription information.
362 * The subscription information is a concatenated string that holds the prospect list id and the campaign id, seperated by at "@" character.
363 * To parse these information string into something more usable, use the "process subscriptions()" function
366 function get_subscription_lists($focus, $descriptions = false) {
368 $unsubs_arr = array();
370 $results = get_subscription_lists_query($focus, $descriptions);
372 $news_type_list_arr = $results['news_type_list_arr'];
373 $current_plp_arr = $results['current_plp_arr'];
375 //For each prospect list of type 'NewsLetter', check to see if current user is already in list,
376 foreach($news_type_list_arr as $news_list){
379 //perform this check against each prospect list this user belongs to
380 foreach($current_plp_arr as $current_list_key => $current_list){//echo " new entry from current lists user is subscribed to-------------";
381 //compare current user list id against newsletter id
382 if ($news_list['prospect_list_id'] == $current_list['prospect_list_id']){
383 //if id's match, user is subscribed to this list, check to see if this is an exempt list,
384 if(strpos($news_list['list_type'], 'exempt')!== false){
385 //this is an exempt list, so process
386 if(array_key_exists($news_list['name'],$subs_arr)){
387 //first, add to unsubscribed array
388 $unsubs_arr[$news_list['name']] = $subs_arr[$news_list['name']];
389 //now remove from exempt subscription list
390 unset($subs_arr[$news_list['name']]);
392 //we know this is an exempt list the user belongs to, but the
393 //non exempt list has not been processed yet, so just add to exempt array
394 $unsubs_arr[$news_list['name']] = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
396 $match = 'false';//although match is false, this is an exempt array, so
397 //it will not be added a second time down below
399 //this list is not exempt, and user is subscribed, so add to subscribed array, and unset from the unsubs_arr
400 //as long as this list is not in exempt array
401 $temp = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
402 if(!array_search($temp,$unsubs_arr)){
403 $subs_arr[$news_list['name']] = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
405 //unset($unsubs_arr[$news_list['name']]);
409 //do nothing, there is no match
412 //if this newsletter id never matched a user subscription..
413 //..then add to available(unsubscribed) NewsLetters if list is not of type exempt
414 if(($match == 'false') && (strpos($news_list['list_type'], 'exempt') === false)){
415 $unsubs_arr[$news_list['name']] = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
419 $return_array['unsubscribed'] = $unsubs_arr;
420 $return_array['subscribed'] = $subs_arr;
421 return $return_array;
425 * same function as get_subscription_lists, but with the data seperated in an associated array
427 function get_subscription_lists_keyed($focus) {
429 $unsubs_arr = array();
431 $results = get_subscription_lists_query($focus, array('c.content', 'c.frequency'));
433 $news_type_list_arr = $results['news_type_list_arr'];
434 $current_plp_arr = $results['current_plp_arr'];
436 //For each prospect list of type 'NewsLetter', check to see if current user is already in list,
437 foreach($news_type_list_arr as $news_list){
440 $news_list_data = array('prospect_list_id' => $news_list['prospect_list_id'],
441 'campaign_id' => $news_list['campaign_id'],
442 'description' => $news_list['content'],
443 'frequency' => $news_list['frequency']);
445 //perform this check against each prospect list this user belongs to
446 foreach($current_plp_arr as $current_list_key => $current_list){//echo " new entry from current lists user is subscribed to-------------";
447 //compare current user list id against newsletter id
448 if ($news_list['prospect_list_id'] == $current_list['prospect_list_id']){
449 //if id's match, user is subscribed to this list, check to see if this is an exempt list,
451 if($news_list['list_type'] == 'exempt'){
452 //this is an exempt list, so process
453 if(array_key_exists($news_list['name'],$subs_arr)){
454 //first, add to unsubscribed array
455 $unsubs_arr[$news_list['name']] = $subs_arr[$news_list['name']];
456 //now remove from exempt subscription list
457 unset($subs_arr[$news_list['name']]);
459 //we know this is an exempt list the user belongs to, but the
460 //non exempt list has not been processed yet, so just add to exempt array
461 $unsubs_arr[$news_list['name']] = $news_list_data;
463 $match = false;//although match is false, this is an exempt array, so
464 //it will not be added a second time down below
466 //this list is not exempt, and user is subscribed, so add to subscribed array
467 //as long as this list is not in exempt array
468 if(!array_key_exists($news_list['name'],$unsubs_arr)){
469 $subs_arr[$news_list['name']] = $news_list_data;
474 //do nothing, there is no match
477 //if this newsletter id never matched a user subscription..
478 //..then add to available(unsubscribed) NewsLetters if list is not of type exempt
479 if(($match == false) && ($news_list['list_type'] != 'exempt')){
480 $unsubs_arr[$news_list['name']] = $news_list_data;
485 $return_array['unsubscribed'] = $unsubs_arr;
486 $return_array['subscribed'] = $subs_arr;
487 return $return_array;
493 * This function will take an array of strings that have been created by the "get_subscription_lists()" method
494 * and parses it into an array. The returned array has it's key's labeled in a specific fashion.
496 * Each string produces a campaign and a prospect id. The keys are appended with a number specifying the order
497 * it was process in. So an input array containing 3 strings will have the following key values:
498 * "prospect_list0", "campaign0"
499 * "prospect_list1", "campaign1"
500 * "prospect_list2", "campaign2"
503 function process_subscriptions($subscription_string_to_parse) {
504 $subs_change = array();
506 //parse through and build list of id's'. We are retrieving the campaign_id and
507 //the prospect_list id from the selected subscriptions
509 foreach($subscription_string_to_parse as $subs_changes){
510 $subs_changes = trim($subs_changes);
511 if(!empty($subs_changes)){
512 $ids_arr = explode("@", $subs_changes);
513 $subs_change[$ids_arr[0].$i] = $ids_arr[1];
514 $subs_change[$ids_arr[2].$i] = $ids_arr[3];
522 /*This function is used by the Manage Subscriptions page in order to add the user
523 * to the default prospect lists of the passed in campaign
524 * Takes in campaign and prospect list id's we are subscribing to.
525 * It also takes in a bean of the user (lead,target,prospect) we are subscribing
527 function subscribe($campaign, $prospect_list, $focus, $default_list = false) {
528 $relationship = strtolower($focus->getObjectName()).'s';
530 //--grab all the lists for the passed in campaign id
531 $pl_qry ="select id, list_type from prospect_lists where id in (select prospect_list_id from prospect_list_campaigns ";
532 $pl_qry .= "where campaign_id = '$campaign') and deleted = 0 ";
533 $GLOBALS['log']->debug("In Campaigns Util: subscribe function, about to run query: ".$pl_qry );
534 $pl_qry_result = $focus->db->query($pl_qry);
536 //build the array of all prospect_lists
538 while ($row = $focus->db->fetchByAssoc($pl_qry_result)){$pl_arr[] = $row;}
540 //--grab all the prospect_lists this user belongs to
541 $curr_pl_qry ="select prospect_list_id, related_id from prospect_lists_prospects ";
542 $curr_pl_qry .="where related_id = '$focus->id' and deleted = 0 ";
543 $GLOBALS['log']->debug("In Campaigns Util: subscribe function, about to run query: ".$curr_pl_qry );
544 $curr_pl_qry_result = $focus->db->query($curr_pl_qry);
546 //build the array of all prospect lists that this current user belongs to
547 $curr_pl_arr = array();
548 while ($row = $focus->db->fetchByAssoc($curr_pl_qry_result)){$curr_pl_arr[] = $row;}
550 //search through prospect lists for this campaign and identifiy the "unsubscription list"
552 foreach($pl_arr as $subscription_list){
553 if(strpos($subscription_list['list_type'], 'exempt')!== false){
554 $exempt_id = $subscription_list['id'];
557 if($subscription_list['list_type'] == 'default' && $default_list) {
558 $prospect_list = $subscription_list['id'];
562 //now that we have exempt (unsubscription) list id, compare against user list id's
563 if(!empty($exempt_id)){
564 $exempt_array['exempt_id'] = $exempt_id;
566 foreach($curr_pl_arr as $curr_subscription_list){
567 if($curr_subscription_list['prospect_list_id'] == $exempt_id){
568 //--if we are in here then user is subscribing to a list in which they are exempt.
569 // we need to remove the user from this unsubscription list.
570 //Begin by retrieving unsubscription prospect list
571 $exempt_subscription_list = new ProspectList();
574 $exempt_result = $exempt_subscription_list->retrieve($exempt_id);
575 if($exempt_result == null)
576 {//error happened while retrieving this list
579 //load realationships and delete user from unsubscription list
580 $exempt_subscription_list->load_relationship($relationship);
581 $exempt_subscription_list->$relationship->delete($exempt_id,$focus->id);
587 //Now we need to check if user is already in subscription list
588 $already_here = 'false';
589 //for each list user is subscribed to, compare id's with current list id'
590 foreach($curr_pl_arr as $user_list){
591 if(in_array($prospect_list, $user_list)){
592 //if user already exists, then set flag to true
593 $already_here = 'true';
596 if($already_here ==='true'){
597 //do nothing, user is already subscribed
599 //user is not subscribed already, so add to subscription list
600 $subscription_list = new ProspectList();
601 $subs_result = $subscription_list->retrieve($prospect_list);
602 if($subs_result == null)
603 {//error happened while retrieving this list, iterate and continue
606 //load subscription list and add this user
607 $GLOBALS['log']->debug("In Campaigns Util, loading relationship: ".$relationship);
608 $subscription_list->load_relationship($relationship);
609 $subscription_list->$relationship->add($focus->id);
614 /*This function is used by the Manage Subscriptions page in order to add the user
615 * to the exempt prospect lists of the passed in campaign
616 * Takes in campaign and focus parameters.
618 function unsubscribe($campaign, $focus) {
619 $relationship = strtolower($focus->getObjectName()).'s';
620 //--grab all the list for this campaign id
621 $pl_qry ="select id, list_type from prospect_lists where id in (select prospect_list_id from prospect_list_campaigns ";
622 $pl_qry .= "where campaign_id = '$campaign') and deleted = 0 ";
623 $pl_qry_result = $focus->db->query($pl_qry);
624 //build the array with list information
626 $GLOBALS['log']->debug("In Campaigns Util, about to run query: ".$pl_qry);
627 while ($row = $focus->db->fetchByAssoc($pl_qry_result)){$pl_arr[] = $row;}
629 //retrieve lists that this user belongs to
630 $curr_pl_qry ="select prospect_list_id, related_id from prospect_lists_prospects ";
631 $curr_pl_qry .="where related_id = '$focus->id' and deleted = 0 ";
632 $GLOBALS['log']->debug("In Campaigns Util, unsubscribe function about to run query: ".$curr_pl_qry );
633 $curr_pl_qry_result = $focus->db->query($curr_pl_qry);
635 //build the array with current user list information
636 $curr_pl_arr = array();
637 while ($row = $focus->db->fetchByAssoc($curr_pl_qry_result)){$curr_pl_arr[] = $row;}
638 //check to see if user is already there in prospect list
639 $already_here = 'false';
642 foreach($curr_pl_arr as $user_list){
643 foreach($pl_arr as $v){
644 //if list is exempt list
645 if($v['list_type'] == 'exempt'){
646 //save the exempt list id for later use
647 $exempt_id = $v['id'];
648 //check to see if user is already in this exempt list
649 if(in_array($v['id'], $user_list)){
650 $already_here = 'true';
658 //unsubscribe subscripted newsletter
659 foreach($pl_arr as $subscription_list){
660 //create a new instance of the prospect list
661 $exempt_list = new ProspectList();
662 $exempt_list->retrieve($subscription_list['id']);
663 $exempt_list->load_relationship($relationship);
664 //if list type is default, then delete the relationship
665 //if list type is exempt, then add the relationship to unsubscription list
666 if($subscription_list['list_type'] == 'exempt') {
667 $exempt_list->$relationship->add($focus->id);
668 }elseif($subscription_list['list_type'] == 'default' || $subscription_list['list_type'] == 'test'){
669 //if list type is default or test, then delete the relationship
670 //$exempt_list->$relationship->delete($subscription_list['id'],$focus->id);
675 if($already_here =='true'){
676 //do nothing, user is already exempted
679 //user is not exempted yet , so add to unsubscription list
682 $exempt_result = $exempt_list->retrieve($exempt_id);
683 if($exempt_result == null)
684 {//error happened while retrieving this list
687 $GLOBALS['log']->debug("In Campaigns Util, loading relationship: ".$relationship);
688 $exempt_list->load_relationship($relationship);
689 $exempt_list->$relationship->add($focus->id);
696 *This function will return a string to the newsletter wizard if campaign check
697 *does not return 100% healthy.
702 global $current_user;
703 $msg = " <table class='detail view small' width='100%'><tr><td> ".$mod_strings['LNK_CAMPAIGN_DIGNOSTIC_LINK']."</td></tr>";
704 //Start with email components
705 //monitored mailbox section
706 $focus = new Administration();
707 $focus->retrieveSettings(); //retrieve all admin settings.
710 //run query for mail boxes of type 'bounce'
712 $email_components = 2;
713 $mbox_qry = "select * from inbound_email where deleted ='0' and mailbox_type = 'bounce'";
714 $mbox_res = $focus->db->query($mbox_qry);
717 while ($mbox_row = $focus->db->fetchByAssoc($mbox_res)){$mbox[] = $mbox_row;}
718 //if the array is not empty, then set "good" message
719 if(isset($mbox) && count($mbox)>0){
720 //everything is ok, do nothing
723 //if array is empty, then increment health counter
724 $email_health =$email_health +1;
725 $msg .= "<tr><td ><font color='red'><b>". $mod_strings['LBL_MAILBOX_CHECK1_BAD']."</b></font></td></tr>";
729 if (strstr($focus->settings['notify_fromaddress'], 'example.com')){
730 //if "from_address" is the default, then set "bad" message and increment health counter
731 $email_health =$email_health +1;
732 $msg .= "<tr><td ><font color='red'><b> ".$mod_strings['LBL_MAILBOX_CHECK2_BAD']." </b></font></td></tr>";
734 //do nothing, address has been changed
736 //if health counter is above 1, then show admin link
738 if (is_admin($current_user)){
739 $msg.="<tr><td ><a href='index.php?module=Campaigns&action=WizardEmailSetup";
740 if(isset($_REQUEST['return_module'])){
741 $msg.="&return_module=".$_REQUEST['return_module'];
743 if(isset($_REQUEST['return_action'])){
744 $msg.="&return_action=".$_REQUEST['return_action'];
746 $msg.="'>".$mod_strings['LBL_EMAIL_SETUP_WIZ']."</a></td></tr>";
748 $msg.="<tr><td >".$mod_strings['LBL_NON_ADMIN_ERROR_MSG']."</td></tr>";
755 // proceed with scheduler components
757 //create and run the scheduler queries
758 $sched_qry = "select job, name, status from schedulers where deleted = 0 and status = 'Active'";
759 $sched_res = $focus->db->query($sched_qry);
762 $check_sched1 = 'function::runMassEmailCampaign';
763 $check_sched2 = 'function::pollMonitoredInboxesForBouncedCampaignEmails';
765 $sched_mes_body = '';
768 while ($sched_row = $focus->db->fetchByAssoc($sched_res)){$scheds[] = $sched_row;}
769 //iterate through and see which jobs were found
770 foreach ($scheds as $funct){
771 if( ($funct['job']==$check_sched1) || ($funct['job']==$check_sched2)){
772 if($funct['job']==$check_sched1){
773 $check_sched1 ="found";
775 $check_sched2 ="found";
780 //determine if error messages need to be displayed for schedulers
781 if($check_sched2 != 'found'){
782 $sched_health =$sched_health +1;
783 $msg.= "<tr><td><font color='red'><b>".$mod_strings['LBL_SCHEDULER_CHECK1_BAD']."</b></font></td></tr>";
785 if($check_sched1 != 'found'){
786 $sched_health =$sched_health +1;
787 $msg.= "<tr><td><font color='red'><b>".$mod_strings['LBL_SCHEDULER_CHECK2_BAD']."</b></font></td></tr>";
789 //if health counter is above 1, then show admin link
791 global $current_user;
792 if (is_admin($current_user)){
793 $msg.="<tr><td ><a href='index.php?module=Schedulers&action=index'>".$mod_strings['LBL_SCHEDULER_LINK']."</a></td></tr>";
795 $msg.="<tr><td >".$mod_strings['LBL_NON_ADMIN_ERROR_MSG']."</td></tr>";
800 //determine whether message should be returned
801 if(($sched_health + $email_health)>0){
811 * Handle campaign log entry creation for mail-merge activity. The function will be called by the soap component.
813 * @param String campaign_id Primary key of the campaign
814 * @param array targets List of keys for entries from prospect_lists_prosects table
816 function campaign_log_mail_merge($campaign_id, $targets) {
818 $campaign= new Campaign();
819 $campaign->retrieve($campaign_id);
821 if (empty($campaign->id)) {
822 $GLOBALS['log']->debug('set_campaign_merge: Invalid campaign id'. $campaign_id);
824 foreach ($targets as $target_list_id) {
825 $pl_query = "select * from prospect_lists_prospects where id='".$GLOBALS['db']->quote($target_list_id)."'";
826 $result=$GLOBALS['db']->query($pl_query);
827 $row=$GLOBALS['db']->fetchByAssoc($result);
829 write_mail_merge_log_entry($campaign_id,$row);
836 * Function creates a campaign_log entry for campaigns processesed using the mail-merge feature. If any entry
837 * exist the hit counter is updated. target_tracker_key is used to locate duplicate entries.
838 * @param string campaign_id Primary key of the campaign
839 * @param array $pl_row A row of data from prospect_lists_prospects table.
841 function write_mail_merge_log_entry($campaign_id,$pl_row) {
843 //Update the log entry if it exists.
844 $update="update campaign_log set hits=hits+1 where campaign_id='".$GLOBALS['db']->quote($campaign_id)."' and target_tracker_key='" . $GLOBALS['db']->quote($pl_row['id']) . "'";
845 $result=$GLOBALS['db']->query($update);
847 //get affected row count...
848 $count=$GLOBALS['db']->getAffectedRowCount();
852 $data['id']="'" . create_guid() . "'";
853 $data['campaign_id']="'" . $GLOBALS['db']->quote($campaign_id) . "'";
854 $data['target_tracker_key']="'" . $GLOBALS['db']->quote($pl_row['id']) . "'";
855 $data['target_id']="'" . $GLOBALS['db']->quote($pl_row['related_id']) . "'";
856 $data['target_type']="'" . $GLOBALS['db']->quote($pl_row['related_type']) . "'";
857 $data['activity_type']="'targeted'";
858 $data['activity_date']="'" . TimeDate::getInstance()->nowDb() . "'";
859 $data['list_id']="'" . $GLOBALS['db']->quote($pl_row['prospect_list_id']) . "'";
862 $insert_query="INSERT into campaign_log (" . implode(",",array_keys($data)) . ")";
863 $insert_query.=" VALUES (" . implode(",",array_values($data)) . ")";
864 $GLOBALS['db']->query($insert_query);
868 function track_campaign_prospects($focus){
869 $delete_query="delete from campaign_log where campaign_id='".$GLOBALS['db']->quote($focus->id)."' and activity_type='targeted'";
870 $focus->db->query($delete_query);
872 $query="SELECT prospect_lists.id prospect_list_id from prospect_lists ";
873 $query.=" INNER JOIN prospect_list_campaigns plc ON plc.prospect_list_id = prospect_lists.id";
874 $query.=" WHERE plc.campaign_id='".$GLOBALS['db']->quote($focus->id)."'";
875 $query.=" AND prospect_lists.deleted=0";
876 $query.=" AND plc.deleted=0";
877 $query.=" AND prospect_lists.list_type!='test' AND prospect_lists.list_type not like 'exempt%'";
878 $result=$focus->db->query($query);
879 while (($row=$focus->db->fetchByAssoc($result))!=null ) {
880 $prospect_list_id=$row['prospect_list_id'];
881 $guid = create_guid();
882 $current_date = $focus->db->now();
884 $insert_query= "INSERT INTO campaign_log (id,activity_date, campaign_id, target_tracker_key,list_id, target_id, target_type, activity_type";
886 $insert_query.= " SELECT '{$guid}',$current_date,plc.campaign_id,'{$guid}',plp.prospect_list_id, plp.related_id, plp.related_type,'targeted' ";
887 $insert_query.= "FROM prospect_lists_prospects plp ";
888 $insert_query.= "INNER JOIN prospect_list_campaigns plc ON plc.prospect_list_id = plp.prospect_list_id ";
889 $insert_query.= "WHERE plp.prospect_list_id = '{$prospect_list_id}' ";
890 $insert_query.= "AND plp.deleted=0 ";
891 $insert_query.= "AND plc.deleted=0 ";
892 $insert_query.= "AND plc.campaign_id='{$focus->id}'";
893 $focus->db->query($insert_query);
896 //return success message
897 return $mod_strings['LBL_DEFAULT_LIST_ENTRIES_WERE_PROCESSED'];
900 function create_campaign_log_entry($campaign_id, $focus, $rel_name, $rel_bean, $target_id = ''){
903 $target_ids = array();
904 //check if this is specified for one target/contact/prospect/lead (from contact/lead detail subpanel)
905 if(!empty($target_id)){
906 $target_ids[] = $target_id;
908 //this is specified for all, so load target/prospect relationships (mark as sent button)
909 $focus->load_relationship($rel_name);
910 $target_ids = $focus->$rel_name->get();
913 if(count($target_ids)>0){
916 //retrieve the target beans and create campaign log entry
917 foreach($target_ids as $id){
918 //perform duplicate check
919 $dup_query = "select id from campaign_log where campaign_id = '$campaign_id' and target_id = '$id'";
920 $dup_result = $focus->db->query($dup_query);
921 $row = $focus->db->fetchByAssoc($dup_result);
923 //process if this is not a duplicate campaign log entry
925 //create campaign tracker id and retrieve related bio bean
926 $tracker_id = create_guid();
927 $rel_bean->retrieve($id);
929 //create new campaign log record.
930 $campaign_log = new CampaignLog();
931 $campaign_log->campaign_id = $campaign_id;
932 $campaign_log->target_tracker_key = $tracker_id;
933 $campaign_log->target_id = $rel_bean->id;
934 $campaign_log->target_type = $rel_bean->module_dir;
935 $campaign_log->activity_type = 'targeted';
936 $campaign_log->activity_date=$timedate->now();
937 //save the campaign log entry
938 $campaign_log->save();
946 * This function will return an array that has been formatted to work as a Quick Search Object for prospect lists
948 function getProspectListQSObjects($source = '', $return_field_name='name', $return_field_id='id' ) {
950 //if source has not been specified, then search across all prospect lists
952 $qsProspectList = array('method' => 'query',
953 'modules'=> array('ProspectLists'),
955 'field_list' => array('name', 'id'),
956 'populate_list' => array('prospect_list_name', 'prospect_list_id'),
957 'conditions' => array( array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>'') ),
960 'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']);
962 //source has been specified use it to tell quicksearch.js which html input to use to get filter value
963 $qsProspectList = array('method' => 'query',
964 'modules'=> array('ProspectLists'),
966 'field_list' => array('name', 'id'),
967 'populate_list' => array($return_field_name, $return_field_id),
968 'conditions' => array(
969 array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>''),
970 //this condition has the source parameter defined, meaning the query will take the value specified below
971 array('name'=>'list_type', 'op'=>'like_custom', 'end'=>'%','value'=>'', 'source' => $source)
975 'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']);
979 return $qsProspectList;