/modules/Campaigns/utils.php

https://bitbucket.org/cviolette/sugarcrm · PHP · 978 lines · 588 code · 150 blank · 240 comment · 116 complexity · ede9d5b23276473f4855b2ff0abbc5fc MD5 · raw file

  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-2012 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. * Description: Defines the English language pack for the base application.
  39. * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
  40. * All Rights Reserved.
  41. * Contributor(s): ______________________________________..
  42. ********************************************************************************/
  43. /*
  44. *returns a list of objects a message can be scoped by, the list contacts the current campaign
  45. *name and list of all prospects associated with this campaign..
  46. *
  47. */
  48. function get_message_scope_dom($campaign_id, $campaign_name,$db=null, $mod_strings=array()) {
  49. //find prospect list attached to this campaign..
  50. $query = "SELECT prospect_list_id, prospect_lists.name ";
  51. $query .= "FROM prospect_list_campaigns ";
  52. $query .= "INNER join prospect_lists on prospect_lists.id = prospect_list_campaigns.prospect_list_id ";
  53. $query .= "WHERE prospect_lists.deleted = 0 ";
  54. $query .= "AND prospect_list_campaigns.deleted=0 ";
  55. $query .= "AND campaign_id='".$campaign_id."'";
  56. $query.=" and prospect_lists.list_type not like 'exempt%'";
  57. if (empty($db)) {
  58. $db = DBManagerFactory::getInstance();
  59. }
  60. if (empty($mod_strings) or !isset($mod_strings['LBL_DEFAULT'])) {
  61. global $current_language;
  62. $mod_strings = return_module_language($current_language, 'Campaigns');
  63. }
  64. //add campaign to the result array.
  65. //$return_array[$campaign_id]= $campaign_name . ' (' . $mod_strings['LBL_DEFAULT'] . ')';
  66. $result=$db->query($query);
  67. while(($row=$db->fetchByAssoc($result))!= null) {
  68. $return_array[$row['prospect_list_id']]=$row['name'];
  69. }
  70. if (empty($return_array)) $return_array=array();
  71. else return $return_array;
  72. }
  73. /**
  74. * Return bounce handling mailboxes for campaign.
  75. *
  76. * @param unknown_type $emails
  77. * @param unknown_type $get_box_name, Set it to false if want to get "From Name" other than the InboundEmail Name.
  78. * @return $get_name=true, bounce handling mailboxes' name; $get_name=false, bounce handling mailboxes' from name.
  79. */
  80. function get_campaign_mailboxes(&$emails, $get_name=true) {
  81. if (!class_exists('InboundEmail')) {
  82. require('modules/InboundEmail/InboundEmail.php');
  83. }
  84. $query = "select id,name,stored_options from inbound_email where mailbox_type='bounce' and status='Active' and deleted='0'";
  85. $db = DBManagerFactory::getInstance();
  86. $result=$db->query($query);
  87. while(($row=$db->fetchByAssoc($result))!= null) {
  88. if($get_name) {
  89. $return_array[$row['id']] = $row['name'];
  90. } else {
  91. $return_array[$row['id']]= InboundEmail::get_stored_options('from_name',$row['name'],$row['stored_options']);
  92. }
  93. $emails[$row['id']]=InboundEmail::get_stored_options('from_addr','nobody@example.com',$row['stored_options']);
  94. }
  95. if (empty($return_array)) $return_array=array(''=>'');
  96. return $return_array;
  97. }
  98. function get_campaign_mailboxes_with_stored_options() {
  99. $ret = array();
  100. if(!class_exists('InboundEmail')) {
  101. require('modules/InboundEmail/InboundEmail.php');
  102. }
  103. $q = "SELECT id, name, stored_options FROM inbound_email WHERE mailbox_type='bounce' AND status='Active' AND deleted='0'";
  104. $db = DBManagerFactory::getInstance();
  105. $r = $db->query($q);
  106. while($a = $db->fetchByAssoc($r)) {
  107. $ret[$a['id']] = unserialize(base64_decode($a['stored_options']));
  108. }
  109. return $ret;
  110. }
  111. function log_campaign_activity($identifier, $activity, $update=true, $clicked_url_key=null) {
  112. $return_array = array();
  113. $db = DBManagerFactory::getInstance();
  114. //check to see if the identifier has been replaced with Banner string
  115. if($identifier == 'BANNER' && isset($clicked_url_key) && !empty($clicked_url_key))
  116. {
  117. // create md5 encrypted string using the client ip, this will be used for tracker id purposes
  118. $enc_id = 'BNR'.md5($_SERVER['REMOTE_ADDR']);
  119. //default the identifier to ip address
  120. $identifier = $enc_id;
  121. //if user has chosen to not use this mode of id generation, then replace identifier with plain guid.
  122. //difference is that guid will generate a new campaign log for EACH CLICK!!
  123. //encrypted generation will generate 1 campaign log and update the hit counter for each click
  124. if(isset($sugar_config['campaign_banner_id_generation']) && $sugar_config['campaign_banner_id_generation'] != 'md5'){
  125. $identifier = create_guid();
  126. }
  127. //retrieve campaign log.
  128. $trkr_query = "select * from campaign_log where target_tracker_key='$identifier' and related_id = '$clicked_url_key'";
  129. $current_trkr=$db->query($trkr_query);
  130. $row=$db->fetchByAssoc($current_trkr);
  131. //if campaign log is not retrieved (this is a new ip address or we have chosen to create
  132. //unique entries for each click
  133. if($row==null || empty($row)){
  134. //retrieve campaign id
  135. $trkr_query = "select ct.campaign_id from campaign_trkrs ct, campaigns c where c.id = ct.campaign_id and ct.id = '$clicked_url_key'";
  136. $current_trkr=$db->query($trkr_query);
  137. $row=$db->fetchByAssoc($current_trkr);
  138. //create new campaign log with minimal info. Note that we are creating new unique id
  139. //as target id, since we do not link banner/web campaigns to any users
  140. $data['target_id']="'" . create_guid() . "'";
  141. $data['target_type']= "'Prospects'";
  142. $data['id']="'" . create_guid() . "'";
  143. $data['campaign_id']="'" . $row['campaign_id'] . "'";
  144. $data['target_tracker_key']="'" . $identifier . "'";
  145. $data['activity_type']="'" . $activity . "'";
  146. $data['activity_date']="'" . TimeDate::getInstance()->nowDb() . "'";
  147. $data['hits']=1;
  148. $data['deleted']=0;
  149. if (!empty($clicked_url_key)) {
  150. $data['related_id']="'".$clicked_url_key."'";
  151. $data['related_type']="'".'CampaignTrackers'."'";
  152. }
  153. //values for return array..
  154. $return_array['target_id']=$data['target_id'];
  155. $return_array['target_type']=$data['target_type'];
  156. //create insert query for new campaign log
  157. $insert_query="INSERT into campaign_log (" . implode(",",array_keys($data)) . ")";
  158. $insert_query.=" VALUES (" . implode(",",array_values($data)) . ")";
  159. $db->query($insert_query);
  160. }else{
  161. //campaign log already exists, so just set the return array and update hits column
  162. $return_array['target_id']= $row['target_id'];
  163. $return_array['target_type']= $row['target_type'];
  164. $query1="update campaign_log set hits=hits+1 where id='{$row['id']}'";
  165. $current=$db->query($query1);
  166. }
  167. //return array and exit
  168. return $return_array;
  169. }
  170. $query1="select * from campaign_log where target_tracker_key='$identifier' and activity_type='$activity'";
  171. if (!empty($clicked_url_key)) {
  172. $query1.=" AND related_id='$clicked_url_key'";
  173. }
  174. $current=$db->query($query1);
  175. $row=$db->fetchByAssoc($current);
  176. if ($row==null) {
  177. $query="select * from campaign_log where target_tracker_key='$identifier' and activity_type='targeted'";
  178. $targeted=$db->query($query);
  179. $row=$db->fetchByAssoc($targeted);
  180. //if activity is removed and target type is users, then a user is trying to opt out
  181. //of emails. This is not possible as Users Table does not have opt out column.
  182. if ($row && (strtolower($row['target_type']) == 'users' && $activity == 'removed' )) {
  183. $return_array['target_id']= $row['target_id'];
  184. $return_array['target_type']= $row['target_type'];
  185. return $return_array;
  186. }
  187. elseif ($row){
  188. $data['id']="'" . create_guid() . "'";
  189. $data['campaign_id']="'" . $row['campaign_id'] . "'";
  190. $data['target_tracker_key']="'" . $identifier . "'";
  191. $data['target_id']="'" . $row['target_id'] . "'";
  192. $data['target_type']="'" . $row['target_type'] . "'";
  193. $data['activity_type']="'" . $activity . "'";
  194. $data['activity_date']="'" . TimeDate::getInstance()->nowDb() . "'";
  195. $data['list_id']="'" . $row['list_id'] . "'";
  196. $data['marketing_id']="'" . $row['marketing_id'] . "'";
  197. $data['hits']=1;
  198. $data['deleted']=0;
  199. if (!empty($clicked_url_key)) {
  200. $data['related_id']="'".$clicked_url_key."'";
  201. $data['related_type']="'".'CampaignTrackers'."'";
  202. }
  203. //values for return array..
  204. $return_array['target_id']=$row['target_id'];
  205. $return_array['target_type']=$row['target_type'];
  206. $insert_query="INSERT into campaign_log (" . implode(",",array_keys($data)) . ")";
  207. $insert_query.=" VALUES (" . implode(",",array_values($data)) . ")";
  208. $db->query($insert_query);
  209. }
  210. } else {
  211. $return_array['target_id']= $row['target_id'];
  212. $return_array['target_type']= $row['target_type'];
  213. $query1="update campaign_log set hits=hits+1 where id='{$row['id']}'";
  214. $current=$db->query($query1);
  215. }
  216. //check to see if this is a removal action
  217. if ($row && $activity == 'removed' ) {
  218. //retrieve campaign and check it's type, we are looking for newsletter Campaigns
  219. $query = "SELECT campaigns.* FROM campaigns WHERE campaigns.id = '".$row['campaign_id']."' ";
  220. $result = $db->query($query);
  221. if(!empty($result))
  222. {
  223. $c_row = $db->fetchByAssoc($result);
  224. //if type is newsletter, then add campaign id to return_array for further processing.
  225. if(isset($c_row['campaign_type']) && $c_row['campaign_type'] == 'NewsLetter'){
  226. $return_array['campaign_id']=$c_row['id'];
  227. }
  228. }
  229. }
  230. return $return_array;
  231. }
  232. /**
  233. *
  234. * This method is deprecated
  235. * @deprecated 62_Joneses - June 24, 2011
  236. * @see campaign_log_lead_or_contact_entry()
  237. */
  238. function campaign_log_lead_entry($campaign_id, $parent_bean,$child_bean,$activity_type){
  239. campaign_log_lead_or_contact_entry($campaign_id, $parent_bean,$child_bean,$activity_type);
  240. }
  241. function campaign_log_lead_or_contact_entry($campaign_id, $parent_bean,$child_bean,$activity_type){
  242. global $timedate;
  243. //create campaign tracker id and retrieve related bio bean
  244. $tracker_id = create_guid();
  245. //create new campaign log record.
  246. $campaign_log = new CampaignLog();
  247. $campaign_log->campaign_id = $campaign_id;
  248. $campaign_log->target_tracker_key = $tracker_id;
  249. $campaign_log->related_id = $parent_bean->id;
  250. $campaign_log->related_type = $parent_bean->module_dir;
  251. $campaign_log->target_id = $child_bean->id;
  252. $campaign_log->target_type = $child_bean->module_dir;
  253. $campaign_log->activity_date = $timedate->now();
  254. $campaign_log->activity_type = $activity_type;
  255. //save the campaign log entry
  256. $campaign_log->save();
  257. }
  258. function get_campaign_urls($campaign_id) {
  259. $return_array=array();
  260. if (!empty($campaign_id)) {
  261. $db = DBManagerFactory::getInstance();
  262. $query1="select * from campaign_trkrs where campaign_id='$campaign_id' and deleted=0";
  263. $current=$db->query($query1);
  264. while (($row=$db->fetchByAssoc($current)) != null) {
  265. $return_array['{'.$row['tracker_name'].'}']=$row['tracker_name'] . ' : ' . $row['tracker_url'];
  266. }
  267. }
  268. return $return_array;
  269. }
  270. /**
  271. * Queries for the list
  272. */
  273. function get_subscription_lists_query($focus, $additional_fields = null) {
  274. //get all prospect lists belonging to Campaigns of type newsletter
  275. $all_news_type_pl_query = "select c.name, pl.list_type, plc.campaign_id, plc.prospect_list_id";
  276. if(is_array($additional_fields) && !empty($additional_fields)) $all_news_type_pl_query .= ', ' . implode(', ', $additional_fields);
  277. $all_news_type_pl_query .= " from prospect_list_campaigns plc , prospect_lists pl, campaigns c ";
  278. $all_news_type_pl_query .= "where plc.campaign_id = c.id ";
  279. $all_news_type_pl_query .= "and plc.prospect_list_id = pl.id ";
  280. $all_news_type_pl_query .= "and c.campaign_type = 'NewsLetter' and pl.deleted = 0 and c.deleted=0 and plc.deleted=0 ";
  281. $all_news_type_pl_query .= "and (pl.list_type like 'exempt%' or pl.list_type ='default') ";
  282. $all_news_type_list =$focus->db->query($all_news_type_pl_query);
  283. //build array of all newsletter campaigns
  284. $news_type_list_arr = array();
  285. while ($row = $focus->db->fetchByAssoc($all_news_type_list)){$news_type_list_arr[] = $row;}
  286. //now get all the campaigns that the current user is assigned to
  287. $all_plp_current = "select prospect_list_id from prospect_lists_prospects where related_id = '$focus->id' and deleted = 0 ";
  288. //build array of prospect lists that this user belongs to
  289. $current_plp =$focus->db->query($all_plp_current );
  290. $current_plp_arr = array();
  291. while ($row = $focus->db->fetchByAssoc($current_plp)){$current_plp_arr[] = $row;}
  292. return array('current_plp_arr' => $current_plp_arr, 'news_type_list_arr' => $news_type_list_arr);
  293. }
  294. /*
  295. * This function takes in a bean from a lead, prospect, or contact and returns an array containing
  296. * all subscription lists that the bean is a part of, and all the subscriptions that the bean is not
  297. * a part of. The array elements have the key names of "subscribed" and "unsusbscribed". These elements contain an array
  298. * of the corresponding list. In other words, the "subscribed" element holds another array that holds the subscription information.
  299. *
  300. * The subscription information is a concatenated string that holds the prospect list id and the campaign id, separated by at "@" character.
  301. * To parse these information string into something more usable, use the "process subscriptions()" function
  302. *
  303. * */
  304. function get_subscription_lists($focus, $descriptions = false) {
  305. $subs_arr = array();
  306. $unsubs_arr = array();
  307. $results = get_subscription_lists_query($focus, $descriptions);
  308. $news_type_list_arr = $results['news_type_list_arr'];
  309. $current_plp_arr = $results['current_plp_arr'];
  310. //For each prospect list of type 'NewsLetter', check to see if current user is already in list,
  311. foreach($news_type_list_arr as $news_list){
  312. $match = 'false';
  313. //perform this check against each prospect list this user belongs to
  314. foreach($current_plp_arr as $current_list_key => $current_list){//echo " new entry from current lists user is subscribed to-------------";
  315. //compare current user list id against newsletter id
  316. if ($news_list['prospect_list_id'] == $current_list['prospect_list_id']){
  317. //if id's match, user is subscribed to this list, check to see if this is an exempt list,
  318. if(strpos($news_list['list_type'], 'exempt')!== false){
  319. //this is an exempt list, so process
  320. if(array_key_exists($news_list['name'],$subs_arr)){
  321. //first, add to unsubscribed array
  322. $unsubs_arr[$news_list['name']] = $subs_arr[$news_list['name']];
  323. //now remove from exempt subscription list
  324. unset($subs_arr[$news_list['name']]);
  325. }else{
  326. //we know this is an exempt list the user belongs to, but the
  327. //non exempt list has not been processed yet, so just add to exempt array
  328. $unsubs_arr[$news_list['name']] = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
  329. }
  330. $match = 'false';//although match is false, this is an exempt array, so
  331. //it will not be added a second time down below
  332. }else{
  333. //this list is not exempt, and user is subscribed, so add to subscribed array, and unset from the unsubs_arr
  334. //as long as this list is not in exempt array
  335. $temp = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
  336. if(!array_search($temp,$unsubs_arr)){
  337. $subs_arr[$news_list['name']] = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
  338. $match = 'true';
  339. //unset($unsubs_arr[$news_list['name']]);
  340. }
  341. }
  342. }else{
  343. //do nothing, there is no match
  344. }
  345. }
  346. //if this newsletter id never matched a user subscription..
  347. //..then add to available(unsubscribed) NewsLetters if list is not of type exempt
  348. if(($match == 'false') && (strpos($news_list['list_type'], 'exempt') === false)){
  349. $unsubs_arr[$news_list['name']] = "prospect_list@".$news_list['prospect_list_id']."@campaign@".$news_list['campaign_id'];
  350. }
  351. }
  352. $return_array['unsubscribed'] = $unsubs_arr;
  353. $return_array['subscribed'] = $subs_arr;
  354. return $return_array;
  355. }
  356. /**
  357. * same function as get_subscription_lists, but with the data separated in an associated array
  358. */
  359. function get_subscription_lists_keyed($focus) {
  360. $subs_arr = array();
  361. $unsubs_arr = array();
  362. $results = get_subscription_lists_query($focus, array('c.content', 'c.frequency'));
  363. $news_type_list_arr = $results['news_type_list_arr'];
  364. $current_plp_arr = $results['current_plp_arr'];
  365. //For each prospect list of type 'NewsLetter', check to see if current user is already in list,
  366. foreach($news_type_list_arr as $news_list){
  367. $match = false;
  368. $news_list_data = array('prospect_list_id' => $news_list['prospect_list_id'],
  369. 'campaign_id' => $news_list['campaign_id'],
  370. 'description' => $news_list['content'],
  371. 'frequency' => $news_list['frequency']);
  372. //perform this check against each prospect list this user belongs to
  373. foreach($current_plp_arr as $current_list_key => $current_list){//echo " new entry from current lists user is subscribed to-------------";
  374. //compare current user list id against newsletter id
  375. if ($news_list['prospect_list_id'] == $current_list['prospect_list_id']){
  376. //if id's match, user is subscribed to this list, check to see if this is an exempt list,
  377. if($news_list['list_type'] == 'exempt'){
  378. //this is an exempt list, so process
  379. if(array_key_exists($news_list['name'],$subs_arr)){
  380. //first, add to unsubscribed array
  381. $unsubs_arr[$news_list['name']] = $subs_arr[$news_list['name']];
  382. //now remove from exempt subscription list
  383. unset($subs_arr[$news_list['name']]);
  384. }else{
  385. //we know this is an exempt list the user belongs to, but the
  386. //non exempt list has not been processed yet, so just add to exempt array
  387. $unsubs_arr[$news_list['name']] = $news_list_data;
  388. }
  389. $match = false;//although match is false, this is an exempt array, so
  390. //it will not be added a second time down below
  391. }else{
  392. //this list is not exempt, and user is subscribed, so add to subscribed array
  393. //as long as this list is not in exempt array
  394. if(!array_key_exists($news_list['name'],$unsubs_arr)){
  395. $subs_arr[$news_list['name']] = $news_list_data;
  396. $match = 'true';
  397. }
  398. }
  399. }else{
  400. //do nothing, there is no match
  401. }
  402. }
  403. //if this newsletter id never matched a user subscription..
  404. //..then add to available(unsubscribed) NewsLetters if list is not of type exempt
  405. if(($match == false) && ($news_list['list_type'] != 'exempt')){
  406. $unsubs_arr[$news_list['name']] = $news_list_data;
  407. }
  408. }
  409. $return_array['unsubscribed'] = $unsubs_arr;
  410. $return_array['subscribed'] = $subs_arr;
  411. return $return_array;
  412. }
  413. /*
  414. * This function will take an array of strings that have been created by the "get_subscription_lists()" method
  415. * and parses it into an array. The returned array has it's key's labeled in a specific fashion.
  416. *
  417. * Each string produces a campaign and a prospect id. The keys are appended with a number specifying the order
  418. * it was process in. So an input array containing 3 strings will have the following key values:
  419. * "prospect_list0", "campaign0"
  420. * "prospect_list1", "campaign1"
  421. * "prospect_list2", "campaign2"
  422. *
  423. * */
  424. function process_subscriptions($subscription_string_to_parse) {
  425. $subs_change = array();
  426. //parse through and build list of id's'. We are retrieving the campaign_id and
  427. //the prospect_list id from the selected subscriptions
  428. $i = 0;
  429. foreach($subscription_string_to_parse as $subs_changes){
  430. $subs_changes = trim($subs_changes);
  431. if(!empty($subs_changes)){
  432. $ids_arr = explode("@", $subs_changes);
  433. $subs_change[$ids_arr[0].$i] = $ids_arr[1];
  434. $subs_change[$ids_arr[2].$i] = $ids_arr[3];
  435. $i = $i+1;
  436. }
  437. }
  438. return $subs_change;
  439. }
  440. /*This function is used by the Manage Subscriptions page in order to add the user
  441. * to the default prospect lists of the passed in campaign
  442. * Takes in campaign and prospect list id's we are subscribing to.
  443. * It also takes in a bean of the user (lead,target,prospect) we are subscribing
  444. * */
  445. function subscribe($campaign, $prospect_list, $focus, $default_list = false) {
  446. $relationship = strtolower($focus->getObjectName()).'s';
  447. //--grab all the lists for the passed in campaign id
  448. $pl_qry ="select id, list_type from prospect_lists where id in (select prospect_list_id from prospect_list_campaigns ";
  449. $pl_qry .= "where campaign_id = '$campaign') and deleted = 0 ";
  450. $GLOBALS['log']->debug("In Campaigns Util: subscribe function, about to run query: ".$pl_qry );
  451. $pl_qry_result = $focus->db->query($pl_qry);
  452. //build the array of all prospect_lists
  453. $pl_arr = array();
  454. while ($row = $focus->db->fetchByAssoc($pl_qry_result)){$pl_arr[] = $row;}
  455. //--grab all the prospect_lists this user belongs to
  456. $curr_pl_qry ="select prospect_list_id, related_id from prospect_lists_prospects ";
  457. $curr_pl_qry .="where related_id = '$focus->id' and deleted = 0 ";
  458. $GLOBALS['log']->debug("In Campaigns Util: subscribe function, about to run query: ".$curr_pl_qry );
  459. $curr_pl_qry_result = $focus->db->query($curr_pl_qry);
  460. //build the array of all prospect lists that this current user belongs to
  461. $curr_pl_arr = array();
  462. while ($row = $focus->db->fetchByAssoc($curr_pl_qry_result)){$curr_pl_arr[] = $row;}
  463. //search through prospect lists for this campaign and identifiy the "unsubscription list"
  464. $exempt_id = '';
  465. foreach($pl_arr as $subscription_list){
  466. if(strpos($subscription_list['list_type'], 'exempt')!== false){
  467. $exempt_id = $subscription_list['id'];
  468. }
  469. if($subscription_list['list_type'] == 'default' && $default_list) {
  470. $prospect_list = $subscription_list['id'];
  471. }
  472. }
  473. //now that we have exempt (unsubscription) list id, compare against user list id's
  474. if(!empty($exempt_id)){
  475. $exempt_array['exempt_id'] = $exempt_id;
  476. foreach($curr_pl_arr as $curr_subscription_list){
  477. if($curr_subscription_list['prospect_list_id'] == $exempt_id){
  478. //--if we are in here then user is subscribing to a list in which they are exempt.
  479. // we need to remove the user from this unsubscription list.
  480. //Begin by retrieving unsubscription prospect list
  481. $exempt_subscription_list = new ProspectList();
  482. $exempt_result = $exempt_subscription_list->retrieve($exempt_id);
  483. if($exempt_result == null)
  484. {//error happened while retrieving this list
  485. return;
  486. }
  487. //load realationships and delete user from unsubscription list
  488. $exempt_subscription_list->load_relationship($relationship);
  489. $exempt_subscription_list->$relationship->delete($exempt_id,$focus->id);
  490. }
  491. }
  492. }
  493. //Now we need to check if user is already in subscription list
  494. $already_here = 'false';
  495. //for each list user is subscribed to, compare id's with current list id'
  496. foreach($curr_pl_arr as $user_list){
  497. if(in_array($prospect_list, $user_list)){
  498. //if user already exists, then set flag to true
  499. $already_here = 'true';
  500. }
  501. }
  502. if($already_here ==='true'){
  503. //do nothing, user is already subscribed
  504. }else{
  505. //user is not subscribed already, so add to subscription list
  506. $subscription_list = new ProspectList();
  507. $subs_result = $subscription_list->retrieve($prospect_list);
  508. if($subs_result == null)
  509. {//error happened while retrieving this list, iterate and continue
  510. return;
  511. }
  512. //load subscription list and add this user
  513. $GLOBALS['log']->debug("In Campaigns Util, loading relationship: ".$relationship);
  514. $subscription_list->load_relationship($relationship);
  515. $subscription_list->$relationship->add($focus->id);
  516. }
  517. }
  518. /*This function is used by the Manage Subscriptions page in order to add the user
  519. * to the exempt prospect lists of the passed in campaign
  520. * Takes in campaign and focus parameters.
  521. * */
  522. function unsubscribe($campaign, $focus) {
  523. $relationship = strtolower($focus->getObjectName()).'s';
  524. //--grab all the list for this campaign id
  525. $pl_qry ="select id, list_type from prospect_lists where id in (select prospect_list_id from prospect_list_campaigns ";
  526. $pl_qry .= "where campaign_id = '$campaign') and deleted = 0 ";
  527. $pl_qry_result = $focus->db->query($pl_qry);
  528. //build the array with list information
  529. $pl_arr = array();
  530. $GLOBALS['log']->debug("In Campaigns Util, about to run query: ".$pl_qry);
  531. while ($row = $focus->db->fetchByAssoc($pl_qry_result)){$pl_arr[] = $row;}
  532. //retrieve lists that this user belongs to
  533. $curr_pl_qry ="select prospect_list_id, related_id from prospect_lists_prospects ";
  534. $curr_pl_qry .="where related_id = '$focus->id' and deleted = 0 ";
  535. $GLOBALS['log']->debug("In Campaigns Util, unsubscribe function about to run query: ".$curr_pl_qry );
  536. $curr_pl_qry_result = $focus->db->query($curr_pl_qry);
  537. //build the array with current user list information
  538. $curr_pl_arr = array();
  539. while ($row = $focus->db->fetchByAssoc($curr_pl_qry_result)){$curr_pl_arr[] = $row;}
  540. //check to see if user is already there in prospect list
  541. $already_here = 'false';
  542. $exempt_id = '';
  543. foreach($curr_pl_arr as $user_list){
  544. foreach($pl_arr as $v){
  545. //if list is exempt list
  546. if($v['list_type'] == 'exempt'){
  547. //save the exempt list id for later use
  548. $exempt_id = $v['id'];
  549. //check to see if user is already in this exempt list
  550. if(in_array($v['id'], $user_list)){
  551. $already_here = 'true';
  552. }
  553. break 2;
  554. }
  555. }
  556. }
  557. //unsubscribe subscripted newsletter
  558. foreach($pl_arr as $subscription_list){
  559. //create a new instance of the prospect list
  560. $exempt_list = new ProspectList();
  561. $exempt_list->retrieve($subscription_list['id']);
  562. $exempt_list->load_relationship($relationship);
  563. //if list type is default, then delete the relationship
  564. //if list type is exempt, then add the relationship to unsubscription list
  565. if($subscription_list['list_type'] == 'exempt') {
  566. $exempt_list->$relationship->add($focus->id);
  567. }elseif($subscription_list['list_type'] == 'default' || $subscription_list['list_type'] == 'test'){
  568. //if list type is default or test, then delete the relationship
  569. //$exempt_list->$relationship->delete($subscription_list['id'],$focus->id);
  570. }
  571. }
  572. if($already_here =='true'){
  573. //do nothing, user is already exempted
  574. }else{
  575. //user is not exempted yet , so add to unsubscription list
  576. $exempt_result = $exempt_list->retrieve($exempt_id);
  577. if($exempt_result == null)
  578. {//error happened while retrieving this list
  579. return;
  580. }
  581. $GLOBALS['log']->debug("In Campaigns Util, loading relationship: ".$relationship);
  582. $exempt_list->load_relationship($relationship);
  583. $exempt_list->$relationship->add($focus->id);
  584. }
  585. }
  586. /*
  587. *This function will return a string to the newsletter wizard if campaign check
  588. *does not return 100% healthy.
  589. */
  590. function diagnose()
  591. {
  592. global $mod_strings;
  593. global $current_user;
  594. $msg = " <table class='detail view small' width='100%'><tr><td> ".$mod_strings['LNK_CAMPAIGN_DIGNOSTIC_LINK']."</td></tr>";
  595. //Start with email components
  596. //monitored mailbox section
  597. $focus = new Administration();
  598. $focus->retrieveSettings(); //retrieve all admin settings.
  599. //run query for mail boxes of type 'bounce'
  600. $email_health = 0;
  601. $email_components = 2;
  602. $mbox_qry = "select * from inbound_email where deleted ='0' and mailbox_type = 'bounce'";
  603. $mbox_res = $focus->db->query($mbox_qry);
  604. $mbox = array();
  605. while ($mbox_row = $focus->db->fetchByAssoc($mbox_res)){$mbox[] = $mbox_row;}
  606. //if the array is not empty, then set "good" message
  607. if(isset($mbox) && count($mbox)>0){
  608. //everything is ok, do nothing
  609. }else{
  610. //if array is empty, then increment health counter
  611. $email_health =$email_health +1;
  612. $msg .= "<tr><td ><font color='red'><b>". $mod_strings['LBL_MAILBOX_CHECK1_BAD']."</b></font></td></tr>";
  613. }
  614. if (strstr($focus->settings['notify_fromaddress'], 'example.com')){
  615. //if "from_address" is the default, then set "bad" message and increment health counter
  616. $email_health =$email_health +1;
  617. $msg .= "<tr><td ><font color='red'><b> ".$mod_strings['LBL_MAILBOX_CHECK2_BAD']." </b></font></td></tr>";
  618. }else{
  619. //do nothing, address has been changed
  620. }
  621. //if health counter is above 1, then show admin link
  622. if($email_health>0){
  623. if (is_admin($current_user)){
  624. $msg.="<tr><td ><a href='index.php?module=Campaigns&action=WizardEmailSetup";
  625. if(isset($_REQUEST['return_module'])){
  626. $msg.="&return_module=".$_REQUEST['return_module'];
  627. }
  628. if(isset($_REQUEST['return_action'])){
  629. $msg.="&return_action=".$_REQUEST['return_action'];
  630. }
  631. $msg.="'>".$mod_strings['LBL_EMAIL_SETUP_WIZ']."</a></td></tr>";
  632. }else{
  633. $msg.="<tr><td >".$mod_strings['LBL_NON_ADMIN_ERROR_MSG']."</td></tr>";
  634. }
  635. }
  636. // proceed with scheduler components
  637. //create and run the scheduler queries
  638. $sched_qry = "select job, name, status from schedulers where deleted = 0 and status = 'Active'";
  639. $sched_res = $focus->db->query($sched_qry);
  640. $sched_health = 0;
  641. $sched = array();
  642. $check_sched1 = 'function::runMassEmailCampaign';
  643. $check_sched2 = 'function::pollMonitoredInboxesForBouncedCampaignEmails';
  644. $sched_mes = '';
  645. $sched_mes_body = '';
  646. $scheds = array();
  647. while ($sched_row = $focus->db->fetchByAssoc($sched_res)){$scheds[] = $sched_row;}
  648. //iterate through and see which jobs were found
  649. foreach ($scheds as $funct){
  650. if( ($funct['job']==$check_sched1) || ($funct['job']==$check_sched2)){
  651. if($funct['job']==$check_sched1){
  652. $check_sched1 ="found";
  653. }else{
  654. $check_sched2 ="found";
  655. }
  656. }
  657. }
  658. //determine if error messages need to be displayed for schedulers
  659. if($check_sched2 != 'found'){
  660. $sched_health =$sched_health +1;
  661. $msg.= "<tr><td><font color='red'><b>".$mod_strings['LBL_SCHEDULER_CHECK1_BAD']."</b></font></td></tr>";
  662. }
  663. if($check_sched1 != 'found'){
  664. $sched_health =$sched_health +1;
  665. $msg.= "<tr><td><font color='red'><b>".$mod_strings['LBL_SCHEDULER_CHECK2_BAD']."</b></font></td></tr>";
  666. }
  667. //if health counter is above 1, then show admin link
  668. if($sched_health>0){
  669. global $current_user;
  670. if (is_admin($current_user)){
  671. $msg.="<tr><td ><a href='index.php?module=Schedulers&action=index'>".$mod_strings['LBL_SCHEDULER_LINK']."</a></td></tr>";
  672. }else{
  673. $msg.="<tr><td >".$mod_strings['LBL_NON_ADMIN_ERROR_MSG']."</td></tr>";
  674. }
  675. }
  676. //determine whether message should be returned
  677. if(($sched_health + $email_health)>0){
  678. $msg .= "</table> ";
  679. }else{
  680. $msg = '';
  681. }
  682. return $msg;
  683. }
  684. /**
  685. * Handle campaign log entry creation for mail-merge activity. The function will be called by the soap component.
  686. *
  687. * @param String campaign_id Primary key of the campaign
  688. * @param array targets List of keys for entries from prospect_lists_prosects table
  689. */
  690. function campaign_log_mail_merge($campaign_id, $targets) {
  691. $campaign= new Campaign();
  692. $campaign->retrieve($campaign_id);
  693. if (empty($campaign->id)) {
  694. $GLOBALS['log']->debug('set_campaign_merge: Invalid campaign id'. $campaign_id);
  695. } else {
  696. foreach ($targets as $target_list_id) {
  697. $pl_query = "select * from prospect_lists_prospects where id='".$GLOBALS['db']->quote($target_list_id)."'";
  698. $result=$GLOBALS['db']->query($pl_query);
  699. $row=$GLOBALS['db']->fetchByAssoc($result);
  700. if (!empty($row)) {
  701. write_mail_merge_log_entry($campaign_id,$row);
  702. }
  703. }
  704. }
  705. }
  706. /**
  707. * Function creates a campaign_log entry for campaigns processesed using the mail-merge feature. If any entry
  708. * exist the hit counter is updated. target_tracker_key is used to locate duplicate entries.
  709. * @param string campaign_id Primary key of the campaign
  710. * @param array $pl_row A row of data from prospect_lists_prospects table.
  711. */
  712. function write_mail_merge_log_entry($campaign_id,$pl_row) {
  713. //Update the log entry if it exists.
  714. $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']) . "'";
  715. $result=$GLOBALS['db']->query($update);
  716. //get affected row count...
  717. $count=$GLOBALS['db']->getAffectedRowCount();
  718. if ($count==0) {
  719. $data=array();
  720. $data['id']="'" . create_guid() . "'";
  721. $data['campaign_id']="'" . $GLOBALS['db']->quote($campaign_id) . "'";
  722. $data['target_tracker_key']="'" . $GLOBALS['db']->quote($pl_row['id']) . "'";
  723. $data['target_id']="'" . $GLOBALS['db']->quote($pl_row['related_id']) . "'";
  724. $data['target_type']="'" . $GLOBALS['db']->quote($pl_row['related_type']) . "'";
  725. $data['activity_type']="'targeted'";
  726. $data['activity_date']="'" . TimeDate::getInstance()->nowDb() . "'";
  727. $data['list_id']="'" . $GLOBALS['db']->quote($pl_row['prospect_list_id']) . "'";
  728. $data['hits']=1;
  729. $data['deleted']=0;
  730. $insert_query="INSERT into campaign_log (" . implode(",",array_keys($data)) . ")";
  731. $insert_query.=" VALUES (" . implode(",",array_values($data)) . ")";
  732. $GLOBALS['db']->query($insert_query);
  733. }
  734. }
  735. function track_campaign_prospects($focus){
  736. $campaign_id = $GLOBALS['db']->quote($focus->id);
  737. $delete_query="delete from campaign_log where campaign_id='".$campaign_id."' and activity_type='targeted'";
  738. $focus->db->query($delete_query);
  739. $current_date = $focus->db->now();
  740. $guidSQL = $focus->db->getGuidSQL();
  741. $insert_query= "INSERT INTO campaign_log (id,activity_date, campaign_id, target_tracker_key,list_id, target_id, target_type, activity_type, deleted";
  742. $insert_query.=')';
  743. $insert_query.="SELECT {$guidSQL}, $current_date, plc.campaign_id,{$guidSQL},plp.prospect_list_id, plp.related_id, plp.related_type,'targeted',0 ";
  744. $insert_query.="FROM prospect_lists INNER JOIN prospect_lists_prospects plp ON plp.prospect_list_id = prospect_lists.id";
  745. $insert_query.=" INNER JOIN prospect_list_campaigns plc ON plc.prospect_list_id = prospect_lists.id";
  746. $insert_query.=" WHERE plc.campaign_id='".$GLOBALS['db']->quote($focus->id)."'";
  747. $insert_query.=" AND prospect_lists.deleted=0";
  748. $insert_query.=" AND plc.deleted=0";
  749. $insert_query.=" AND plp.deleted=0";
  750. $insert_query.=" AND prospect_lists.list_type!='test' AND prospect_lists.list_type not like 'exempt%'";
  751. $focus->db->query($insert_query);
  752. global $mod_strings;
  753. //return success message
  754. return $mod_strings['LBL_DEFAULT_LIST_ENTRIES_WERE_PROCESSED'];
  755. }
  756. function create_campaign_log_entry($campaign_id, $focus, $rel_name, $rel_bean, $target_id = ''){
  757. global $timedate;
  758. $target_ids = array();
  759. //check if this is specified for one target/contact/prospect/lead (from contact/lead detail subpanel)
  760. if(!empty($target_id)){
  761. $target_ids[] = $target_id;
  762. }else{
  763. //this is specified for all, so load target/prospect relationships (mark as sent button)
  764. $focus->load_relationship($rel_name);
  765. $target_ids = $focus->$rel_name->get();
  766. }
  767. if(count($target_ids)>0){
  768. //retrieve the target beans and create campaign log entry
  769. foreach($target_ids as $id){
  770. //perform duplicate check
  771. $dup_query = "select id from campaign_log where campaign_id = '$campaign_id' and target_id = '$id'";
  772. $dup_result = $focus->db->query($dup_query);
  773. $row = $focus->db->fetchByAssoc($dup_result);
  774. //process if this is not a duplicate campaign log entry
  775. if(empty($row)){
  776. //create campaign tracker id and retrieve related bio bean
  777. $tracker_id = create_guid();
  778. $rel_bean->retrieve($id);
  779. //create new campaign log record.
  780. $campaign_log = new CampaignLog();
  781. $campaign_log->campaign_id = $campaign_id;
  782. $campaign_log->target_tracker_key = $tracker_id;
  783. $campaign_log->target_id = $rel_bean->id;
  784. $campaign_log->target_type = $rel_bean->module_dir;
  785. $campaign_log->activity_type = 'targeted';
  786. $campaign_log->activity_date=$timedate->now();
  787. //save the campaign log entry
  788. $campaign_log->save();
  789. }
  790. }
  791. }
  792. }
  793. /*
  794. * This function will return an array that has been formatted to work as a Quick Search Object for prospect lists
  795. */
  796. function getProspectListQSObjects($source = '', $return_field_name='name', $return_field_id='id' ) {
  797. global $app_strings;
  798. //if source has not been specified, then search across all prospect lists
  799. if(empty($source)){
  800. $qsProspectList = array('method' => 'query',
  801. 'modules'=> array('ProspectLists'),
  802. 'group' => 'and',
  803. 'field_list' => array('name', 'id'),
  804. 'populate_list' => array('prospect_list_name', 'prospect_list_id'),
  805. 'conditions' => array( array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>'') ),
  806. 'order' => 'name',
  807. 'limit' => '30',
  808. 'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']);
  809. }else{
  810. //source has been specified use it to tell quicksearch.js which html input to use to get filter value
  811. $qsProspectList = array('method' => 'query',
  812. 'modules'=> array('ProspectLists'),
  813. 'group' => 'and',
  814. 'field_list' => array('name', 'id'),
  815. 'populate_list' => array($return_field_name, $return_field_id),
  816. 'conditions' => array(
  817. array('name'=>'name','op'=>'like_custom','end'=>'%','value'=>''),
  818. //this condition has the source parameter defined, meaning the query will take the value specified below
  819. array('name'=>'list_type', 'op'=>'like_custom', 'end'=>'%','value'=>'', 'source' => $source)
  820. ),
  821. 'order' => 'name',
  822. 'limit' => '30',
  823. 'no_match_text' => $app_strings['ERR_SQS_NO_MATCH']);
  824. }
  825. return $qsProspectList;
  826. }
  827. ?>