modules/Campaigns/utils.php

   1 <?php
   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.
   6  *
   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.
  13  *
  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
  17  * details.
  18  *
  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
  22  * 02110-1301 USA.
  23  *
  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.
  26  *
  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.
  30  *
  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  ********************************************************************************/
  37
  38 /*********************************************************************************
  39
  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  ********************************************************************************/
  45
  46 /*
  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..
  49  *
  50  */
  51 function get_message_scope_dom($campaign_id, $campaign_name,$db=null, $mod_strings=array()) {
  52
  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%'";
  61
  62     if (empty($db)) {
  63         $db = DBManagerFactory::getInstance();
  64     }
  65     if (empty($mod_strings) or !isset($mod_strings['LBL_DEFAULT'])) {
  66         global $current_language;
  67         $mod_strings = return_module_language($current_language, 'Campaigns');
  68     }
  69
  70     //add campaign to the result array.
  71     //$return_array[$campaign_id]= $campaign_name . ' (' . $mod_strings['LBL_DEFAULT'] . ')';
  72
  73     $result=$db->query($query);
  74     while(($row=$db->fetchByAssoc($result))!= null) {
  75         $return_array[$row['prospect_list_id']]=$row['name'];
  76     }
  77     if (empty($return_array)) $return_array=array();
  78     else return $return_array;
  79 }
  80 /**
  81  * Return bounce handling mailboxes for campaign.
  82  *
  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.
  86  */
  87 function get_campaign_mailboxes(&$emails, $get_name=true) {
  88     if (!class_exists('InboundEmail')) {
  89         require('modules/InboundEmail/InboundEmail.php');
  90     }
  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) {
  95         if($get_name) {
  96                 $return_array[$row['id']] = $row['name'];
  97         } else {
  98                 $return_array[$row['id']]= InboundEmail::get_stored_options('from_name',$row['name'],$row['stored_options']);
  99         }
 100         $emails[$row['id']]=InboundEmail::get_stored_options('from_addr','nobody@example.com',$row['stored_options']);
 101     }
 102
 103     if (empty($return_array)) $return_array=array(''=>'');
 104     return $return_array;
 105
 106 }
 107
 108 function get_campaign_mailboxes_with_stored_options() {
 109         $ret = array();
 110
 111     if(!class_exists('InboundEmail')) {
 112         require('modules/InboundEmail/InboundEmail.php');
 113     }
 114
 115     $q = "SELECT id, name, stored_options FROM inbound_email WHERE mailbox_type='bounce' AND status='Active' AND deleted='0'";
 116
 117     $db = DBManagerFactory::getInstance();
 118
 119     $r = $db->query($q);
 120
 121     while($a = $db->fetchByAssoc($r)) {
 122         $ret[$a['id']] = unserialize(base64_decode($a['stored_options']));
 123     }
 124         return $ret;
 125 }
 126
 127 function log_campaign_activity($identifier, $activity, $update=true, $clicked_url_key=null) {
 128
 129     $return_array = array();
 130
 131     $db = DBManagerFactory::getInstance();
 132
 133
 134
 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))
 137     {
 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']);
 140
 141         //default the identifier to ip address
 142         $identifier = $enc_id;
 143
 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();
 149         }
 150
 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);
 155
 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)){
 159
 160
 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);
 165
 166
 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
 169
 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() . "'";
 177                 $data['hits']=1;
 178                 if (!empty($clicked_url_key)) {
 179                     $data['related_id']="'".$clicked_url_key."'";
 180                     $data['related_type']="'".'CampaignTrackers'."'";
 181                 }
 182
 183                 //values for return array..
 184                 $return_array['target_id']=$data['target_id'];
 185                 $return_array['target_type']=$data['target_type'];
 186
 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);
 191             }else{
 192
 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);
 198
 199
 200            }
 201
 202         //return array and exit
 203         return $return_array;
 204
 205     }
 206
 207
 208
 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'";
 212     }
 213     $current=$db->query($query1);
 214     $row=$db->fetchByAssoc($current);
 215
 216         if ($row==null) {
 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);
 220
 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;
 227             }
 228             elseif ($row){
 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'] . "'";
 238                 $data['hits']=1;
 239                 if (!empty($clicked_url_key)) {
 240                     $data['related_id']="'".$clicked_url_key."'";
 241                     $data['related_type']="'".'CampaignTrackers'."'";
 242                 }
 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);
 249             }
 250         } else {
 251
 252             $return_array['target_id']= $row['target_id'];
 253             $return_array['target_type']= $row['target_type'];
 254
 255             $query1="update campaign_log set hits=hits+1 where id='{$row['id']}'";
 256             $current=$db->query($query1);
 257
 258         }
 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);
 264             if(!empty($result))
 265             {
 266                 $c_row = $db->fetchByAssoc($result);
 267
 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'];
 271                 }
 272             }
 273         }
 274         return $return_array;
 275     }
 276
 277
 278  /**
 279      *
 280      * This method is deprecated
 281      * @deprecated 62_Joneses - June 24, 2011
 282      * @see campaign_log_lead_or_contact_entry()
 283      */
 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);
 286 }
 287
 288
 289 function campaign_log_lead_or_contact_entry($campaign_id, $parent_bean,$child_bean,$activity_type){
 290     global $timedate;
 291
 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();
 306 }
 307
 308
 309 function get_campaign_urls($campaign_id) {
 310     $return_array=array();
 311
 312     if (!empty($campaign_id)) {
 313
 314         $db = DBManagerFactory::getInstance();
 315
 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'];
 320         }
 321     }
 322     return $return_array;
 323 }
 324
 325 /**
 326  * Queries for the list
 327  */
 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 ";
 333
 334
 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') ";
 339
 340     $all_news_type_list =$focus->db->query($all_news_type_pl_query);
 341
 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;}
 345
 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 ";
 348
 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;}
 353
 354     return array('current_plp_arr' => $current_plp_arr, 'news_type_list_arr' => $news_type_list_arr);
 355 }
 356 /*
 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.
 361  *
 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
 364  *
 365  * */
 366 function get_subscription_lists($focus, $descriptions = false) {
 367     $subs_arr = array();
 368     $unsubs_arr = array();
 369
 370     $results = get_subscription_lists_query($focus, $descriptions);
 371
 372     $news_type_list_arr = $results['news_type_list_arr'];
 373     $current_plp_arr = $results['current_plp_arr'];
 374
 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){
 377         $match = 'false';
 378
 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']]);
 391                     }else{
 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'];
 395                     }
 396                     $match = 'false';//although match is false, this is an exempt array, so
 397                     //it will not be added a second time down below
 398                 }else{
 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'];
 404                         $match = 'true';
 405                         //unset($unsubs_arr[$news_list['name']]);
 406                     }
 407                 }
 408             }else{
 409                 //do nothing, there is no match
 410                 }
 411         }
 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'];
 416         }
 417
 418     }
 419     $return_array['unsubscribed'] = $unsubs_arr;
 420     $return_array['subscribed'] = $subs_arr;
 421     return $return_array;
 422 }
 423
 424 /**
 425  * same function as get_subscription_lists, but with the data seperated in an associated array
 426  */
 427 function get_subscription_lists_keyed($focus) {
 428     $subs_arr = array();
 429     $unsubs_arr = array();
 430
 431     $results = get_subscription_lists_query($focus, array('c.content', 'c.frequency'));
 432
 433     $news_type_list_arr = $results['news_type_list_arr'];
 434     $current_plp_arr = $results['current_plp_arr'];
 435
 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){
 438         $match = false;
 439
 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']);
 444
 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,
 450
 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']]);
 458                     }else{
 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;
 462                     }
 463                     $match = false;//although match is false, this is an exempt array, so
 464                     //it will not be added a second time down below
 465                 }else{
 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;
 470                         $match = 'true';
 471                     }
 472                 }
 473             }else{
 474                 //do nothing, there is no match
 475                 }
 476         }
 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;
 481         }
 482
 483     }
 484
 485     $return_array['unsubscribed'] = $unsubs_arr;
 486     $return_array['subscribed'] = $subs_arr;
 487     return $return_array;
 488 }
 489
 490
 491
 492 /*
 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.
 495  *
 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"
 501  *
 502  * */
 503 function process_subscriptions($subscription_string_to_parse) {
 504     $subs_change = array();
 505
 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
 508     $i = 0;
 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];
 515             $i = $i+1;
 516         }
 517     }
 518     return $subs_change;
 519 }
 520
 521
 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
 526      * */
 527     function subscribe($campaign, $prospect_list, $focus, $default_list = false) {
 528             $relationship = strtolower($focus->getObjectName()).'s';
 529
 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);
 535
 536             //build the array of all prospect_lists
 537             $pl_arr = array();
 538             while ($row = $focus->db->fetchByAssoc($pl_qry_result)){$pl_arr[] = $row;}
 539
 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);
 545
 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;}
 549
 550             //search through prospect lists for this campaign and identifiy the "unsubscription list"
 551             $exempt_id = '';
 552            foreach($pl_arr as $subscription_list){
 553                 if(strpos($subscription_list['list_type'],  'exempt')!== false){
 554                     $exempt_id = $subscription_list['id'];
 555                 }
 556
 557                 if($subscription_list['list_type'] == 'default' && $default_list) {
 558                     $prospect_list = $subscription_list['id'];
 559                 }
 560            }
 561
 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;
 565
 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();
 572
 573
 574                         $exempt_result = $exempt_subscription_list->retrieve($exempt_id);
 575                         if($exempt_result == null)
 576                         {//error happened while retrieving this list
 577                             return;
 578                         }
 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);
 582
 583                     }
 584                }
 585            }
 586
 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';
 594                 }
 595             }
 596             if($already_here ==='true'){
 597                 //do nothing, user is already subscribed
 598             }else{
 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
 604                     return;
 605                 }
 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);
 610             }
 611 }
 612
 613
 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.
 617      * */
 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
 625         $pl_arr = array();
 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;}
 628
 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);
 634
 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';
 640         $exempt_id = '';
 641
 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';
 651                         }
 652
 653                         break 2;
 654                 }
 655                 }
 656         }
 657
 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);
 671             }
 672
 673         }
 674
 675         if($already_here =='true'){
 676             //do nothing, user is already exempted
 677
 678         }else{
 679             //user is not exempted yet , so add to unsubscription list
 680
 681
 682             $exempt_result = $exempt_list->retrieve($exempt_id);
 683             if($exempt_result == null)
 684             {//error happened while retrieving this list
 685                 return;
 686             }
 687             $GLOBALS['log']->debug("In Campaigns Util, loading relationship: ".$relationship);
 688             $exempt_list->load_relationship($relationship);
 689             $exempt_list->$relationship->add($focus->id);
 690         }
 691
 692     }
 693
 694
 695     /*
 696      *This function will return a string to the newsletter wizard if campaign check
 697      *does not return 100% healthy.
 698      */
 699     function diagnose()
 700     {
 701         global $mod_strings;
 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.
 708
 709
 710         //run query for mail boxes of type 'bounce'
 711         $email_health = 0;
 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);
 715
 716         $mbox = array();
 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
 721
 722         }else{
 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>";
 726         }
 727
 728
 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>";
 733         }else{
 734             //do nothing, address has been changed
 735         }
 736         //if health counter is above 1, then show admin link
 737         if($email_health>0){
 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'];
 742                 }
 743                 if(isset($_REQUEST['return_action'])){
 744                     $msg.="&return_action=".$_REQUEST['return_action'];
 745                 }
 746                 $msg.="'>".$mod_strings['LBL_EMAIL_SETUP_WIZ']."</a></td></tr>";
 747             }else{
 748                 $msg.="<tr><td >".$mod_strings['LBL_NON_ADMIN_ERROR_MSG']."</td></tr>";
 749
 750             }
 751
 752         }
 753
 754
 755         // proceed with scheduler components
 756
 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);
 760         $sched_health = 0;
 761         $sched = array();
 762         $check_sched1 = 'function::runMassEmailCampaign';
 763         $check_sched2 = 'function::pollMonitoredInboxesForBouncedCampaignEmails';
 764         $sched_mes = '';
 765         $sched_mes_body = '';
 766         $scheds = array();
 767
 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";
 774                 }else{
 775                     $check_sched2 ="found";
 776                 }
 777
 778           }
 779         }
 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>";
 784         }
 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>";
 788         }
 789         //if health counter is above 1, then show admin link
 790         if($sched_health>0){
 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>";
 794             }else{
 795                 $msg.="<tr><td >".$mod_strings['LBL_NON_ADMIN_ERROR_MSG']."</td></tr>";
 796             }
 797
 798         }
 799
 800         //determine whether message should be returned
 801         if(($sched_health + $email_health)>0){
 802             $msg  .= "</table> ";
 803         }else{
 804             $msg = '';
 805         }
 806         return $msg;
 807     }
 808
 809
 810 /**
 811  * Handle campaign log entry creation for mail-merge activity. The function will be called by the soap component.
 812  *
 813  * @param String campaign_id Primary key of the campaign
 814  * @param array targets List of keys for entries from prospect_lists_prosects table
 815  */
 816  function campaign_log_mail_merge($campaign_id, $targets) {
 817
 818     $campaign= new Campaign();
 819     $campaign->retrieve($campaign_id);
 820
 821     if (empty($campaign->id)) {
 822         $GLOBALS['log']->debug('set_campaign_merge: Invalid campaign id'. $campaign_id);
 823     } else {
 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);
 828             if (!empty($row)) {
 829                 write_mail_merge_log_entry($campaign_id,$row);
 830             }
 831         }
 832     }
 833
 834  }
 835 /**
 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.
 840  */
 841 function write_mail_merge_log_entry($campaign_id,$pl_row) {
 842
 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);
 846
 847     //get affected row count...
 848     $count=$GLOBALS['db']->getAffectedRowCount();
 849     if ($count==0) {
 850         $data=array();
 851
 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']) . "'";
 860         $data['hits']=1;
 861
 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);
 865     }
 866 }
 867
 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);
 871
 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();
 883
 884                         $insert_query= "INSERT INTO campaign_log (id,activity_date, campaign_id, target_tracker_key,list_id, target_id, target_type, activity_type";
 885                         $insert_query.=')';
 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);
 894                 }
 895         global $mod_strings;
 896         //return success message
 897         return $mod_strings['LBL_DEFAULT_LIST_ENTRIES_WERE_PROCESSED'];
 898     }
 899
 900     function create_campaign_log_entry($campaign_id, $focus, $rel_name, $rel_bean, $target_id = ''){
 901         global $timedate;
 902
 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;
 907         }else{
 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();
 911
 912         }
 913         if(count($target_ids)>0){
 914
 915
 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);
 922
 923                 //process if this is not a duplicate campaign log entry
 924                 if(empty($row)){
 925                     //create campaign tracker id and retrieve related bio bean
 926                      $tracker_id = create_guid();
 927                      $rel_bean->retrieve($id);
 928
 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();
 939                 }
 940             }
 941         }
 942
 943     }
 944
 945     /*
 946      * This function will return an array that has been formatted to work as a Quick Search Object for prospect lists
 947      */
 948     function getProspectListQSObjects($source = '', $return_field_name='name', $return_field_id='id' ) {
 949         global $app_strings;
 950         //if source has not been specified, then search across all prospect lists
 951         if(empty($source)){
 952             $qsProspectList = array('method' => 'query',
 953                                 'modules'=> array('ProspectLists'),
 954                                 'group' => 'and',
 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'=>'') ),
 958                                 'order' => 'name',
 959                                 'limit' => '30',
 960                                 'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']);
 961         }else{
 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'),
 965                                 'group' => 'and',
 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)
 972                                 ),
 973                                 'order' => 'name',
 974                                 'limit' => '30',
 975                                 'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']);
 976
 977         }
 978
 979         return $qsProspectList;
 980     }
 981
 982
 983 ?>