include/SugarEmailAddress/SugarEmailAddress.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:
  41  * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc. All Rights
  42  * Reserved. Contributor(s): ______________________________________..
  43  *********************************************************************************/
  44
  45
  46 require_once("include/JSON.php");
  47
  48
  49 class SugarEmailAddress extends SugarBean {
  50     var $table_name = 'email_addresses';
  51     var $module_name = "EmailAddresses";
  52     var $module_dir = 'EmailAddresses';
  53     var $object_name = 'EmailAddress';
  54
  55     //bug 40068, According to rules in page 6 of http://www.apps.ietf.org/rfc/rfc3696.html#sec-3,
  56         //allowed special characters ! # $ % & ' * + - / = ?  ^ _ ` . { | } ~ in local part
  57     var $regex = "/^(?:['\.\-\+&#!\$\*=\?\^_`\{\}~\/\w]+)@(?:(?:\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})|\w+(?:[\.-]*\w+)*(?:\.[\w-]{2,})+)\$/";
  58     var $disable_custom_fields = true;
  59     var $db;
  60     var $smarty;
  61     var $addresses = array(); // array of emails
  62     var $view = '';
  63
  64     static $count = 0;
  65
  66     /**
  67      * Sole constructor
  68      */
  69     function SugarEmailAddress() {
  70         parent::SugarBean();
  71         $this->index = self::$count;
  72         self::$count++;
  73     }
  74
  75     /**
  76      * Legacy email address handling.  This is to allow support for SOAP or customizations
  77      * @param string $id
  78      * @param string $module
  79      */
  80     function handleLegacySave($bean, $prefix = "") {
  81         if(!isset($_REQUEST) || !isset($_REQUEST['useEmailWidget'])) {
  82             if (empty($this->addresses) || !isset($_REQUEST['massupdate'])) {
  83                 $this->addresses = array();
  84                 $optOut = (isset($bean->email_opt_out) && $bean->email_opt_out == "1") ? true : false;
  85                 $invalid = (isset($bean->invalid_email) && $bean->invalid_email == "1") ? true : false;
  86
  87                 $isPrimary = true;
  88                 for($i = 1; $i <= 10; $i++){
  89                     $email = 'email'.$i;
  90                     if(isset($bean->$email) && !empty($bean->$email)){
  91                         $opt_out_field = $email.'_opt_out';
  92                         $invalid_field = $email.'_invalid';
  93                         $field_optOut = (isset($bean->$opt_out_field)) ? $bean->$opt_out_field : $optOut;
  94                         $field_invalid = (isset($bean->$invalid_field)) ? $bean->$invalid_field : $invalid;
  95                         $this->addAddress($bean->$email, $isPrimary, false, $field_invalid, $field_optOut);
  96                         $isPrimary = false;
  97                     }
  98                 }
  99             }
 100         }
 101         $this->populateAddresses($bean->id, $bean->module_dir, array(),'');
 102         if(isset($_REQUEST) && isset($_REQUEST['useEmailWidget'])) {
 103             $this->populateLegacyFields($bean);
 104         }
 105     }
 106
 107     /**
 108      * Fills standard email1 legacy fields
 109      * @param string id
 110      * @param string module
 111      * @return object
 112      */
 113     function handleLegacyRetrieve(&$bean) {
 114         $module_dir = $this->getCorrectedModule($bean->module_dir);
 115         $this->addresses = $this->getAddressesByGUID($bean->id, $module_dir);
 116         $this->populateLegacyFields($bean);
 117         if (isset($bean->email1) && !isset($bean->fetched_row['email1'])) {
 118             $bean->fetched_row['email1'] = $bean->email1;
 119         }
 120
 121         return;
 122     }
 123
 124     function populateLegacyFields(&$bean){
 125         $primary_found = false;
 126         $alternate_found = false;
 127         $alternate2_found = false;
 128         foreach($this->addresses as $k=>$address) {
 129             if ($primary_found && $alternate_found)
 130                 break;
 131             if ($address['primary_address'] == 1 && !$primary_found) {
 132                 $primary_index = $k;
 133                 $primary_found = true;
 134             } elseif (!$alternate_found) {
 135                 $alternate_index = $k;
 136                 $alternate_found = true;
 137             } elseif (!$alternate2_found){
 138                 $alternate2_index = $k;
 139                 $alternate2_found = true;
 140             }
 141         }
 142
 143         if ($primary_found) {
 144             $bean->email1 = $this->addresses[$primary_index]['email_address'];
 145             $bean->email_opt_out = $this->addresses[$primary_index]['opt_out'];
 146             $bean->invalid_email = $this->addresses[$primary_index]['invalid_email'];
 147             if ($alternate_found) {
 148                 $bean->email2 = $this->addresses[$alternate_index]['email_address'];
 149             }
 150         } elseif ($alternate_found) {
 151             // Use the first found alternate as email1.
 152             $bean->email1 = $this->addresses[$alternate_index]['email_address'];
 153             $bean->email_opt_out = $this->addresses[$alternate_index]['opt_out'];
 154             $bean->invalid_email = $this->addresses[$alternate_index]['invalid_email'];
 155             if ($alternate2_found) {
 156                 $bean->email2 = $this->addresses[$alternate2_index]['email_address'];
 157             }
 158         }
 159     }
 160
 161     /**
 162      * Saves email addresses for a parent bean
 163      * @param string $id Parent bean ID
 164      * @param string $module Parent bean's module
 165      * @param array $addresses Override of $_REQUEST vars, used to handle non-standard bean saves
 166      * @param string $primary GUID of primary address
 167      * @param string $replyTo GUID of reply-to address
 168      * @param string $invalid GUID of invalid address
 169      */
 170     function save($id, $module, $new_addrs=array(), $primary='', $replyTo='', $invalid='', $optOut='', $in_workflow=false) {
 171         if(empty($this->addresses) || $in_workflow){
 172             $this->populateAddresses($id, $module, $new_addrs,$primary);
 173         }
 174
 175         //find all email addresses..
 176         $current_links=array();
 177         // Need to correct this to handle the Employee/User split
 178         $module = $this->getCorrectedModule($module);
 179         $q2="select *  from email_addr_bean_rel eabr WHERE eabr.bean_id = '{$id}' AND eabr.bean_module = '{$module}' and eabr.deleted=0";
 180         $r2 = $this->db->query($q2);
 181         while(($row2=$this->db->fetchByAssoc($r2)) != null ) {
 182             $current_links[$row2['email_address_id']]=$row2;
 183         }
 184
 185         $isConversion = (isset($_REQUEST) && isset($_REQUEST['action']) && $_REQUEST['action'] == 'ConvertLead') ? true : false;
 186
 187         if (!empty($this->addresses)) {
 188             // insert new relationships and create email address record, if they don't exist
 189             foreach($this->addresses as $address) {
 190                 if(!empty($address['email_address'])) {
 191                     $guid = create_guid();
 192                     $emailId = $this->AddUpdateEmailAddress($address['email_address'],$address['invalid_email'],$address['opt_out']);// this will save the email address if not found
 193
 194                     //verify linkage and flags.
 195                     $upd_eabr="";
 196                     if (isset($current_links[$emailId])) {
 197                         if (!$isConversion) { // do not update anything if this is for lead conversion
 198                             if ($address['primary_address'] != $current_links[$emailId]['primary_address'] or $address['reply_to_address'] != $current_links[$emailId]['reply_to_address'] ) {
 199                                 $upd_eabr="UPDATE email_addr_bean_rel SET primary_address='{$address['primary_address']}', reply_to_address='{$address['reply_to_address']}' WHERE id='{$current_links[$emailId]['id']}'";
 200                             }
 201
 202                             unset($current_links[$emailId]);
 203                         }
 204                     } else {
 205                         $primary = $address['primary_address'];
 206                         if (!empty($current_links) && $isConversion) {
 207                             foreach ($current_links as $eabr) {
 208                                 if ($eabr['primary_address'] == 1) {
 209                                     // for lead conversion, if there is already a primary email, do not insert another primary email
 210                                     $primary = 0;
 211                                     break;
 212                                 }
 213                             }
 214                         }
 215                         $now = TimeDate::getInstance()->nowDb();
 216                         $upd_eabr = "INSERT INTO email_addr_bean_rel (id, email_address_id,bean_id, bean_module,primary_address,reply_to_address,date_created,date_modified,deleted) VALUES('{$guid}', '{$emailId}', '{$id}', '{$module}', {$primary}, {$address['reply_to_address']}, '$now', '$now', 0)";
 217                     }
 218
 219                     if (!empty($upd_eabr)) {
 220                         $r2 = $this->db->query($upd_eabr);
 221                     }
 222                 }
 223             }
 224         }
 225
 226         //delete link to dropped email address.
 227         // for lead conversion, do not delete email addresses
 228         if (!empty($current_links) && !$isConversion) {
 229
 230             $delete="";
 231             foreach ($current_links as $eabr) {
 232
 233                 $delete.=empty($delete) ? "'".$eabr['id'] . "' " : ",'" . $eabr['id'] . "'";
 234             }
 235
 236             $eabr_unlink="update email_addr_bean_rel set deleted=1 where id in ({$delete})";
 237             $this->db->query($eabr_unlink);
 238         }
 239         return;
 240     }
 241
 242     /**
 243      * returns the number of email addresses found for a specifed bean
 244      *
 245      * @param  string $email       Address to match
 246      * @param  object $bean        Bean to query against
 247      * @param  string $addresstype Optional, pass a 1 to query against the primary address, 0 for the other addresses
 248      * @return int                 Count of records found
 249      */
 250     function getCountEmailAddressByBean(
 251         $email,
 252         $bean,
 253         $addresstype
 254         )
 255     {
 256         $emailCaps = strtoupper(trim($email));
 257         if(empty($emailCaps))
 258             return 0;
 259
 260         $q = "SELECT *
 261                 FROM email_addr_bean_rel eabl JOIN email_addresses ea
 262                         ON (ea.id = eabl.email_address_id)
 263                     JOIN {$bean->table_name} bean
 264                         ON (eabl.bean_id = bean.id)
 265                 WHERE ea.email_address_caps = '{$emailCaps}'
 266                     and eabl.bean_module = '{$bean->module_dir}'
 267                     and eabl.primary_address = '{$addresstype}'
 268                     and eabl.deleted=0 ";
 269
 270         $r = $this->db->query($q);
 271
 272         // do it this way to make the count accurate in oracle
 273         $i = 0;
 274         while ($this->db->fetchByAssoc($r)) ++$i;
 275
 276         return $i;
 277     }
 278
 279     /**
 280      * This function returns a contact or user ID if a matching email is found
 281      * @param   $email      the email address to match
 282      * @param   $table      which table to query
 283      */
 284     function getRelatedId($email, $module) {
 285         $email = trim(strtoupper($email));
 286         $module = ucfirst($module);
 287
 288         $q = "SELECT bean_id FROM email_addr_bean_rel eabr
 289                 JOIN email_addresses ea ON (eabr.email_address_id = ea.id)
 290                 WHERE bean_module = '{$module}' AND ea.email_address_caps = '{$email}' AND eabr.deleted=0";
 291
 292         $r = $this->db->query($q, true);
 293
 294         $retArr = array();
 295         while($a = $this->db->fetchByAssoc($r)) {
 296             $retArr[] = $a['bean_id'];
 297         }
 298         if(count($retArr) > 0) {
 299             return $retArr;
 300         } else {
 301             return false;
 302         }
 303     }
 304
 305     /**
 306      * returns a collection of beans matching the email address
 307      * @param string $email Address to match
 308      * @return array
 309      */
 310     function getBeansByEmailAddress($email) {
 311         global $beanList;
 312         global $beanFiles;
 313
 314         $ret = array();
 315
 316         $email = trim($email);
 317
 318         if(empty($email)) {
 319             return array();
 320         }
 321
 322         $emailCaps = strtoupper($email);
 323         $q = "SELECT * FROM email_addr_bean_rel eabl JOIN email_addresses ea ON (ea.id = eabl.email_address_id)
 324                 WHERE ea.email_address_caps = '{$emailCaps}' and eabl.deleted=0 ";
 325         $r = $this->db->query($q);
 326
 327         while($a = $this->db->fetchByAssoc($r)) {
 328             if(isset($beanList[$a['bean_module']]) && !empty($beanList[$a['bean_module']])) {
 329                 $className = $beanList[$a['bean_module']];
 330
 331                 if(isset($beanFiles[$className]) && !empty($beanFiles[$className])) {
 332                     if(!class_exists($className)) {
 333                         require_once($beanFiles[$className]);
 334                     }
 335
 336                     $bean = new $className();
 337                     $bean->retrieve($a['bean_id']);
 338
 339                     $ret[] = $bean;
 340                 } else {
 341                     $GLOBALS['log']->fatal("SUGAREMAILADDRESS: could not find valid class file for [ {$className} ]");
 342                 }
 343             } else {
 344                 $GLOBALS['log']->fatal("SUGAREMAILADDRESS: could not find valid class [ {$a['bean_module']} ]");
 345             }
 346         }
 347
 348         return $ret;
 349     }
 350
 351     /**
 352      * Saves email addresses for a parent bean
 353      * @param string $id Parent bean ID
 354      * @param string $module Parent bean's module
 355      * @param array $addresses Override of $_REQUEST vars, used to handle non-standard bean saves
 356      * @param string $primary GUID of primary address
 357      * @param string $replyTo GUID of reply-to address
 358      * @param string $invalid GUID of invalid address
 359      */
 360     function populateAddresses($id, $module, $new_addrs=array(), $primary='', $replyTo='', $invalid='', $optOut='') {
 361         $module = $this->getCorrectedModule($module);
 362         //One last check for the ConvertLead action in which case we need to change $module to 'Leads'
 363         $module = (isset($_REQUEST) && isset($_REQUEST['action']) && $_REQUEST['action'] == 'ConvertLead') ? 'Leads' : $module;
 364
 365         $post_from_email_address_widget = (isset($_REQUEST) && isset($_REQUEST['emailAddressWidget'])) ? true : false;
 366         $primaryValue = $primary;
 367         $widgetCount = 0;
 368         $hasEmailValue = false;
 369
 370         if (isset($_REQUEST) && isset($_REQUEST[$module .'_email_widget_id'])) {
 371
 372             $fromRequest = false;
 373             // determine which array to process
 374             foreach($_REQUEST as $k => $v) {
 375                 if(strpos($k, 'emailAddress') !== false) {
 376                    $fromRequest = true;
 377                    break;
 378                 }
 379             }
 380             $widget_id = $_REQUEST[$module .'_email_widget_id'];
 381
 382
 383             //Iterate over the widgets for this module, in case there are multiple email widgets for this module
 384             while(isset($_REQUEST[$module . $widget_id . "emailAddress" . $widgetCount]))
 385             {
 386                 if (empty($_REQUEST[$module . $widget_id . "emailAddress" . $widgetCount])) {
 387                     $widgetCount++;
 388                     continue;
 389                 }
 390
 391                 $hasEmailValue = true;
 392
 393                 $eId = $module . $widget_id;
 394                 if(isset($_REQUEST[$eId . 'emailAddressPrimaryFlag'])) {
 395                    $primaryValue = $_REQUEST[$eId . 'emailAddressPrimaryFlag'];
 396                 } else if(isset($_REQUEST[$module . 'emailAddressPrimaryFlag'])) {
 397                    $primaryValue = $_REQUEST[$module . 'emailAddressPrimaryFlag'];
 398                 }
 399
 400                 $optOutValues = array();
 401                 if(isset($_REQUEST[$eId .'emailAddressOptOutFlag'])) {
 402                    $optOutValues = $_REQUEST[$eId .'emailAddressOptOutFlag'];
 403                 } else if(isset($_REQUEST[$module . 'emailAddressOptOutFlag'])) {
 404                    $optOutValues = $_REQUEST[$module . 'emailAddressOptOutFlag'];
 405                 }
 406
 407                 $invalidValues = array();
 408                 if(isset($_REQUEST[$eId .'emailAddressInvalidFlag'])) {
 409                    $invalidValues = $_REQUEST[$eId .'emailAddressInvalidFlag'];
 410                 } else if(isset($_REQUEST[$module . 'emailAddressInvalidFlag'])) {
 411                    $invalidValues = $_REQUEST[$module . 'emailAddressInvalidFlag'];
 412                 }
 413
 414                 $deleteValues = array();
 415                 if(isset($_REQUEST[$eId .'emailAddressDeleteFlag'])) {
 416                    $deleteValues = $_REQUEST[$eId .'emailAddressDeleteFlag'];
 417                 } else if(isset($_REQUEST[$module . 'emailAddressDeleteFlag'])) {
 418                    $deleteValues = $_REQUEST[$module . 'emailAddressDeleteFlag'];
 419                 }
 420
 421                 // prep from form save
 422                 $primaryField = $primary;
 423                 $replyToField = '';
 424                 $invalidField = '';
 425                 $optOutField = '';
 426                 if($fromRequest && empty($primary) && isset($primaryValue)) {
 427                     $primaryField = $primaryValue;
 428                 }
 429
 430                 if($fromRequest && empty($replyTo)) {
 431                     if(isset($_REQUEST[$eId .'emailAddressReplyToFlag'])) {
 432                        $replyToField = $_REQUEST[$eId .'emailAddressReplyToFlag'];
 433                     } else if(isset($_REQUEST[$module . 'emailAddressReplyToFlag'])) {
 434                        $replyToField = $_REQUEST[$module . 'emailAddressReplyToFlag'];
 435                     }
 436                 }
 437                 if($fromRequest && empty($new_addrs)) {
 438                     foreach($_REQUEST as $k => $v) {
 439                         if(preg_match('/'.$eId.'emailAddress[0-9]+$/i', $k) && !empty($v)) {
 440                             $new_addrs[$k] = $v;
 441                         }
 442                     }
 443                 }
 444
 445                 if($fromRequest && empty($new_addrs)) {
 446                     foreach($_REQUEST as $k => $v) {
 447                         if(preg_match('/'.$eId.'emailAddressVerifiedValue[0-9]+$/i', $k) && !empty($v)) {
 448                             $validateFlag = str_replace("Value", "Flag", $k);
 449                             if (isset($_REQUEST[$validateFlag]) && $_REQUEST[$validateFlag] == "true")
 450                               $new_addrs[$k] = $v;
 451                         }
 452                     }
 453                 }
 454
 455                 //empty the addresses array if the post happened from email address widget.
 456                 if($post_from_email_address_widget) {
 457                     $this->addresses=array();  //this gets populated during retrieve of the contact bean.
 458                 } else {
 459                     $optOutValues = array();
 460                     $invalidValues = array();
 461                     foreach($new_addrs as $k=>$email) {
 462                        preg_match('/emailAddress([0-9])+$/', $k, $matches);
 463                        $count = $matches[1];
 464                        $result = $this->db->query("SELECT opt_out, invalid_email from email_addresses where email_address_caps = '" . strtoupper($email) . "'");
 465                        if(!empty($result)) {
 466                           $row=$this->db->fetchByAssoc($result);
 467                           if(!empty($row['opt_out'])) {
 468                              $optOutValues[$k] = "emailAddress$count";
 469                           }
 470                           if(!empty($row['invalid_email'])) {
 471                              $invalidValues[$k] = "emailAddress$count";
 472                           }
 473                        }
 474                     }
 475                 }
 476                 // Re-populate the addresses class variable if we have new address(es).
 477                 if (!empty($new_addrs)) {
 478                     foreach($new_addrs as $k => $reqVar) {
 479                         //$key = preg_match("/^$eId/s", $k) ? substr($k, strlen($eId)) : $k;
 480                         $reqVar = trim($reqVar);
 481                         if(strpos($k, 'emailAddress') !== false) {
 482                             if(!empty($reqVar) && !in_array($k, $deleteValues)) {
 483                                 $primary    = ($k == $primaryValue) ? true : false;
 484                                 $replyTo    = ($k == $replyToField) ? true : false;
 485                                 $invalid    = (in_array($k, $invalidValues)) ? true : false;
 486                                 $optOut     = (in_array($k, $optOutValues)) ? true : false;
 487                                 $this->addAddress(trim($new_addrs[$k]), $primary, $replyTo, $invalid, $optOut);
 488                             }
 489                         }
 490                     } //foreach
 491                 }
 492
 493                 $widgetCount++;
 494             }//End of Widget for loop
 495         }
 496
 497         //If no widgets, set addresses array to empty
 498         if($post_from_email_address_widget && !$hasEmailValue) {
 499            $this->addresses = array();
 500         }
 501     }
 502
 503     /**
 504      * Preps internal array structure for email addresses
 505      * @param string $addr Email address
 506      * @param bool $primary Default false
 507      * @param bool $replyTo Default false
 508      */
 509     function addAddress($addr, $primary=false, $replyTo=false, $invalid=false, $optOut=false) {
 510         $addr = html_entity_decode($addr, ENT_QUOTES);
 511         if(preg_match($this->regex, $addr)) {
 512             $primaryFlag = ($primary) ? '1' : '0';
 513             $replyToFlag = ($replyTo) ? '1' : '0';
 514             $invalidFlag = ($invalid) ? '1' : '0';
 515             $optOutFlag = ($optOut) ? '1' : '0';
 516
 517             $addr = trim($addr);
 518
 519             // If we have such address already, remove it and add new one in.
 520             foreach ($this->addresses as $k=>$address) {
 521                 if ($address['email_address'] == $addr) {
 522                     unset($this->addresses[$k]);
 523                 } elseif ($primary && $address['primary_address'] == '1') {
 524                     // We should only have one primary. If we are adding a primary but
 525                     // we find an existing primary, reset this one's primary flag.
 526                     $address['primary_address'] = '0';
 527                 }
 528             }
 529
 530             $this->addresses[] = array(
 531                 'email_address' => $addr,
 532                 'primary_address' => $primaryFlag,
 533                 'reply_to_address' => $replyToFlag,
 534                 'invalid_email' => $invalidFlag,
 535                 'opt_out' => $optOutFlag,
 536             );
 537         } else {
 538             $GLOBALS['log']->fatal("SUGAREMAILADDRESS: address did not validate [ {$addr} ]");
 539         }
 540     }
 541
 542     /**
 543      * Updates invalid_email and opt_out flags for each address
 544      */
 545     function updateFlags() {
 546         if(!empty($this->addresses)) {
 547             foreach($this->addresses as $addressMeta) {
 548                 if(isset($addressMeta['email_address']) && !empty($addressMeta['email_address'])) {
 549                     $address = $this->_cleanAddress($addressMeta['email_address']);
 550
 551                     $q = "SELECT * FROM email_addresses WHERE email_address = '{$address}'";
 552                     $r = $this->db->query($q);
 553                     $a = $this->db->fetchByAssoc($r);
 554
 555                     if(!empty($a)) {
 556                         if(isset($a['invalid_email']) && isset($addressMeta['invalid_email']) && isset($addressMeta['opt_out']) && $a['invalid_email'] != $addressMeta['invalid_email'] || $a['opt_out'] != $addressMeta['opt_out']) {
 557                             $qUpdate = "UPDATE email_addresses SET invalid_email = {$addressMeta['invalid_email']}, opt_out = {$addressMeta['opt_out']}, date_modified = '".TimeDate::getInstance()->nowDb()."' WHERE id = '{$a['id']}'";
 558                             $rUpdate = $this->db->query($qUpdate);
 559                         }
 560                     }
 561                 }
 562             }
 563         }
 564     }
 565
 566     public function splitEmailAddress($addr)
 567     {
 568         $email = $this->_cleanAddress($addr);
 569         if(!preg_match($this->regex, $email)) {
 570             $email = ''; // remove bad email addr
 571         }
 572         $name = trim(str_replace(array($email, '<', '>', '"', "'"), '', $addr));
 573         return array("name" => $name, "email" => strtolower($email));
 574     }
 575
 576     /**
 577      * PRIVATE UTIL
 578      * Normalizes an RFC-clean email address, returns a string that is the email address only
 579      * @param string $addr Dirty email address
 580      * @return string clean email address
 581      */
 582     function _cleanAddress($addr) {
 583         $addr = trim(from_html($addr));
 584
 585         if(strpos($addr, "<") !== false && strpos($addr, ">") !== false) {
 586             $address = trim(substr($addr, strrpos($addr, "<") +1, strrpos($addr, ">") - strrpos($addr, "<") -1));
 587         } else {
 588             $address = trim($addr);
 589         }
 590
 591         return $address;
 592     }
 593
 594     /**
 595      * preps a passed email address for email address storage
 596      * @param array $addr Address in focus, must be RFC compliant
 597      * @return string $id email_addresses ID
 598      */
 599     function getEmailGUID($addr) {
 600         $address = $this->_cleanAddress($addr);
 601         $addressCaps = strtoupper($address);
 602
 603         $q = "SELECT id FROM email_addresses WHERE email_address_caps = '{$addressCaps}'";
 604         $r = $this->db->query($q);
 605         $a = $this->db->fetchByAssoc($r);
 606
 607         if(!empty($a) && !empty($a['id'])) {
 608             return $a['id'];
 609         } else {
 610             $guid = '';
 611             if(!empty($address)){
 612                 $guid = create_guid();
 613                 $address = $GLOBALS['db']->quote($address);
 614                 $addressCaps = $GLOBALS['db']->quote($addressCaps);
 615                 $now = TimeDate::getInstance()->nowDb();
 616                 $qa = "INSERT INTO email_addresses (id, email_address, email_address_caps, date_created, date_modified, deleted)
 617                         VALUES('{$guid}', '{$address}', '{$addressCaps}', '$now', '$now', 0)";
 618                 $ra = $this->db->query($qa);
 619             }
 620             return $guid;
 621         }
 622     }
 623
 624     function AddUpdateEmailAddress($addr,$invalid=0,$opt_out=0) {
 625
 626         $address = $this->_cleanAddress($addr);
 627         $addressCaps = strtoupper($this->db->quoteForEmail($address));
 628
 629         $q = "SELECT * FROM email_addresses WHERE email_address_caps = '{$addressCaps}' and deleted=0";
 630         $r = $this->db->query($q);
 631         $a = $this->db->fetchByAssoc($r);
 632         if(!empty($a) && !empty($a['id'])) {
 633             //verify the opt out and invalid flags.
 634            //bug# 39378- did not allow change of case of an email address
 635             if ($a['invalid_email'] != $invalid or $a['opt_out'] != $opt_out or strcasecmp(trim($a['email_address']), trim($address))==0) {
 636                 $upd_q="update email_addresses set email_address='{$address}', invalid_email={$invalid}, opt_out={$opt_out},date_modified = '".gmdate($GLOBALS['timedate']->get_db_date_time_format())."' where id='{$a['id']}'";
 637                 $upd_r= $this->db->query($upd_q);
 638             }
 639             return $a['id'];
 640         } else {
 641             $guid = '';
 642             if(!empty($address)){
 643                 $guid = create_guid();
 644                 $address = $GLOBALS['db']->quote($address);
 645                 $addressCaps = $GLOBALS['db']->quote($addressCaps);
 646                 $now = TimeDate::getInstance()->nowDb();
 647                 $qa = "INSERT INTO email_addresses (id, email_address, email_address_caps, date_created, date_modified, deleted, invalid_email, opt_out)
 648                         VALUES('{$guid}', '{$address}', '{$addressCaps}', '$now', '$now', 0 , $invalid, $opt_out)";
 649                 $this->db->query($qa);
 650             }
 651             return $guid;
 652         }
 653     }
 654
 655     /**
 656      * Returns Primary or newest email address
 657      * @param object $focus Object in focus
 658      * @return string email
 659      */
 660     function getPrimaryAddress($focus,$parent_id=null,$parent_type=null) {
 661
 662         $parent_type=empty($parent_type) ? $focus->module_dir : $parent_type;
 663         $parent_id=empty($parent_id) ? $focus->id : $parent_id;
 664
 665         $q = "SELECT ea.email_address FROM email_addresses ea
 666                 LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
 667                 WHERE ear.bean_module = '{$parent_type}'
 668                 AND ear.bean_id = '{$parent_id}'
 669                 AND ear.deleted = 0
 670                 AND ea.invalid_email = 0
 671                 ORDER BY ear.primary_address DESC";
 672         $r = $this->db->limitQuery($q, 0, 1);
 673         $a = $this->db->fetchByAssoc($r);
 674
 675         if(isset($a['email_address'])) {
 676             return $a['email_address'];
 677         }
 678         return '';
 679     }
 680
 681     function getReplyToAddress($focus) {
 682         $q = "SELECT ea.email_address FROM email_addresses ea
 683                 LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
 684                 WHERE ear.bean_module = '{$focus->module_dir}'
 685                 AND ear.bean_id = '{$focus->id}'
 686                 AND ear.deleted = 0
 687                 AND ea.invalid_email = 0
 688                 ORDER BY ear.reply_to_address DESC";
 689         $r = $this->db->query($q);
 690         $a = $this->db->fetchByAssoc($r);
 691
 692         if(isset($a['email_address'])) {
 693             return $a['email_address'];
 694         }
 695         return '';
 696     }
 697
 698     /**
 699      * Returns all email addresses by parent's GUID
 700      * @param string $id Parent's GUID
 701      * @param string $module Parent's module
 702      * @return array
 703      */
 704     function getAddressesByGUID($id, $module) {
 705         $return = array();
 706         $module = $this->getCorrectedModule($module);
 707
 708         $q = "SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.date_created, ea.date_modified,
 709                 ear.id, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted
 710                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
 711                 WHERE ear.bean_module = '{$module}'
 712                 AND ear.bean_id = '{$id}'
 713                 AND ear.deleted = 0
 714                 ORDER BY ear.reply_to_address, ear.primary_address DESC";
 715         $r = $this->db->query($q);
 716
 717         while($a = $this->db->fetchByAssoc($r)) {
 718             $return[] = $a;
 719         }
 720
 721         return $return;
 722     }
 723
 724     /**
 725      * Returns the HTML/JS for the EmailAddress widget
 726      * @param string $parent_id ID of parent bean, generally $focus
 727      * @param string $module $focus' module
 728      * @param bool asMetadata Default false
 729      * @return string HTML/JS for widget
 730      */
 731     function getEmailAddressWidgetEditView($id, $module, $asMetadata=false, $tpl='',$tabindex='')
 732     {
 733         if ( !($this->smarty instanceOf Sugar_Smarty ) )
 734             $this->smarty = new Sugar_Smarty();
 735
 736         global $app_strings, $dictionary, $beanList;
 737
 738         $prefill = 'false';
 739
 740         $prefillData = 'new Object()';
 741         $passedModule = $module;
 742         $module = $this->getCorrectedModule($module);
 743         $saveModule = $module;
 744         if(isset($_POST['is_converted']) && $_POST['is_converted']==true){
 745             $id=$_POST['return_id'];
 746             $module=$_POST['return_module'];
 747         }
 748         $prefillDataArr = array();
 749         if(!empty($id)) {
 750             $prefillDataArr = $this->getAddressesByGUID($id, $module);
 751             //When coming from convert leads, sometimes module is Contacts while the id is for a lead.
 752             if (empty($prefillDataArr) && $module == "Contacts")
 753                 $prefillDataArr = $this->getAddressesByGUID($id, "Leads");
 754         } else if(isset($_REQUEST['full_form']) && !empty($_REQUEST['emailAddressWidget'])){
 755             $widget_id = isset($_REQUEST[$module . '_email_widget_id']) ? $_REQUEST[$module . '_email_widget_id'] : '0';
 756             $count = 0;
 757             $key = $module . $widget_id . 'emailAddress'.$count;
 758             while(isset($_REQUEST[$key])) {
 759                    $email = $_REQUEST[$key];
 760                    $prefillDataArr[] =  array('email_address'=>$email,
 761                                              'primary_address'=>isset($_REQUEST['emailAddressPrimaryFlag']) && $_REQUEST['emailAddressPrimaryFlag'] == $key,
 762                                              'invalid_email'=>isset($_REQUEST['emailAddressInvalidFlag']) && in_array($key, $_REQUEST['emailAddressInvalidFlag']),
 763                                              'opt_out'=>isset($_REQUEST['emailAddressOptOutFlag']) && in_array($key, $_REQUEST['emailAddressOptOutFlag']),
 764                                              'reply_to_address'=>false
 765                                         );
 766                    $key = $module . $widget_id . 'emailAddress' . ++$count;
 767             } //while
 768         }
 769
 770         if(!empty($prefillDataArr)) {
 771             $json = new JSON(JSON_LOOSE_TYPE);
 772             $prefillData = $json->encode($prefillDataArr);
 773             $prefill = !empty($prefillDataArr) ? 'true' : 'false';
 774         }
 775
 776         $required = false;
 777         $vardefs = $dictionary[$beanList[$passedModule]]['fields'];
 778         if (!empty($vardefs['email1']) && isset($vardefs['email1']['required']) && $vardefs['email1']['required'])
 779             $required = true;
 780         $this->smarty->assign('required', $required);
 781
 782         $this->smarty->assign('module', $saveModule);
 783         $this->smarty->assign('index', $this->index);
 784         $this->smarty->assign('app_strings', $app_strings);
 785         $this->smarty->assign('prefillEmailAddresses', $prefill);
 786         $this->smarty->assign('prefillData', $prefillData);
 787         $this->smarty->assign('tabindex', $tabindex);
 788         //Set addDefaultAddress flag (do not add if it's from the Email module)
 789         $this->smarty->assign('addDefaultAddress', (isset($_REQUEST['module']) && $_REQUEST['module'] == 'Emails') ? 'false' : 'true');
 790         $form = $this->view;
 791
 792         //determine if this should be a quickcreate form, or a quick create form under subpanels
 793         if ($this->view == "QuickCreate"){
 794             $form = 'form_DC'.$this->view .'_'.$module;
 795             if(isset($_REQUEST['action']) && $_REQUEST['action']=='SubpanelCreates' ||  $_REQUEST['action']=='SubpanelEdits'){
 796                 $form = 'form_Subpanel'.$this->view .'_'.$module;
 797             }
 798         }
 799
 800         $this->smarty->assign('emailView', $form);
 801
 802         if($module == 'Users') {
 803             $this->smarty->assign('useReplyTo', true);
 804         } else {
 805             $this->smarty->assign('useOptOut', true);
 806             $this->smarty->assign('useInvalid', true);
 807         }
 808
 809         $template = empty($tpl) ? "include/SugarEmailAddress/templates/forEditView.tpl" : $tpl;
 810         $newEmail = $this->smarty->fetch($template);
 811
 812
 813         if($asMetadata) {
 814             // used by Email 2.0
 815             $ret = array();
 816             $ret['prefillData'] = $prefillDataArr;
 817             $ret['html'] = $newEmail;
 818
 819             return $ret;
 820         }
 821
 822         return $newEmail;
 823     }
 824
 825
 826     /**
 827      * Returns the HTML/JS for the EmailAddress widget
 828      * @param object $focus Bean in focus
 829      * @return string HTML/JS for widget
 830      */
 831     function getEmailAddressWidgetDetailView($focus, $tpl='')
 832     {
 833         if ( !($this->smarty instanceOf Sugar_Smarty ) )
 834             $this->smarty = new Sugar_Smarty();
 835
 836         global $app_strings;
 837         global $current_user;
 838         $assign = array();
 839         if(empty($focus->id))return '';
 840         $prefillData = $this->getAddressesByGUID($focus->id, $focus->module_dir);
 841
 842         foreach($prefillData as $addressItem) {
 843             $key = ($addressItem['primary_address'] == 1) ? 'primary' : "";
 844             $key = ($addressItem['reply_to_address'] == 1) ? 'reply_to' : $key;
 845             $key = ($addressItem['opt_out'] == 1) ? 'opt_out' : $key;
 846             $key = ($addressItem['invalid_email'] == 1) ? 'invalid' : $key;
 847             $key = ($addressItem['opt_out'] == 1) && ($addressItem['invalid_email'] == 1) ? 'opt_out_invalid' : $key;
 848
 849             $assign[] = array('key' => $key, 'address' => $current_user->getEmailLink2($addressItem['email_address'], $focus).$addressItem['email_address']."</a>");
 850         }
 851
 852
 853         $this->smarty->assign('app_strings', $app_strings);
 854         $this->smarty->assign('emailAddresses', $assign);
 855         $templateFile = empty($tpl) ? "include/SugarEmailAddress/templates/forDetailView.tpl" : $tpl;
 856         $return = $this->smarty->fetch($templateFile);
 857         return $return;
 858     }
 859
 860
 861     /**
 862      * getEmailAddressWidgetDuplicatesView($focus)
 863      * @param object $focus Bean in focus
 864      * @return string HTML that contains hidden input values based off of HTML request
 865      */
 866     function getEmailAddressWidgetDuplicatesView($focus)
 867     {
 868         if ( !($this->smarty instanceOf Sugar_Smarty ) )
 869             $this->smarty = new Sugar_Smarty();
 870
 871         $count = 0;
 872         $emails = array();
 873         $primary = null;
 874         $optOut = array();
 875         $invalid = array();
 876         $mod = isset($focus) ? $focus->module_dir : "";
 877
 878         $widget_id = $_POST[$mod .'_email_widget_id'];
 879         $this->smarty->assign('email_widget_id',$widget_id);
 880         $this->smarty->assign('emailAddressWidget',$_POST['emailAddressWidget']);
 881
 882         if(isset($_POST[$mod . $widget_id . 'emailAddressPrimaryFlag'])) {
 883            $primary = $_POST[$mod . $widget_id . 'emailAddressPrimaryFlag'];
 884         }
 885
 886         while(isset($_POST[$mod . $widget_id . "emailAddress" . $count])) {
 887             $emails[] = $_POST[$mod . $widget_id . 'emailAddress' . $count];
 888             $count++;
 889         }
 890
 891         if($count == 0) {
 892            return "";
 893         }
 894
 895         if(isset($_POST[$mod . $widget_id . 'emailAddressOptOutFlag'])) {
 896            foreach($_POST[$mod . $widget_id . 'emailAddressOptOutFlag'] as $v) {
 897               $optOut[] = $v;
 898            }
 899         }
 900
 901         if(isset($_POST[$mod . $widget_id . 'emailAddressInvalidFlag'])) {
 902            foreach($_POST[$mod . $widget_id . 'emailAddressInvalidFlag'] as $v) {
 903               $invalid[] = $v;
 904            }
 905         }
 906
 907         if(isset($_POST[$mod . $widget_id . 'emailAddressReplyToFlag'])) {
 908            foreach($_POST[$mod . $widget_id . 'emailAddressReplyToFlag'] as $v) {
 909               $replyTo[] = $v;
 910            }
 911         }
 912
 913         if(isset($_POST[$mod . $widget_id . 'emailAddressDeleteFlag'])) {
 914            foreach($_POST[$mod . $widget_id . 'emailAddressDeleteFlag'] as $v) {
 915               $delete[] = $v;
 916            }
 917         }
 918
 919         while(isset($_POST[$mod . $widget_id . "emailAddressVerifiedValue" . $count])) {
 920             $verified[] = $_POST[$mod . $widget_id . 'emailAddressVerifiedValue' . $count];
 921             $count++;
 922         }
 923
 924         $this->smarty->assign('emails', $emails);
 925         $this->smarty->assign('primary', $primary);
 926         $this->smarty->assign('optOut', $optOut);
 927         $this->smarty->assign('invalid', $invalid);
 928         $this->smarty->assign('replyTo', $invalid);
 929         $this->smarty->assign('delete', $invalid);
 930         $this->smarty->assign('verified', $invalid);
 931         $this->smarty->assign('moduleDir', $mod);
 932
 933         return $this->smarty->fetch("include/SugarEmailAddress/templates/forDuplicatesView.tpl");
 934     }
 935
 936     /**
 937      * getFormBaseURL
 938      *
 939      */
 940     function getFormBaseURL($focus) {
 941         $get = "";
 942         $count = 0;
 943         $mod = isset($focus) ? $focus->module_dir : "";
 944
 945         $widget_id = $_POST[$mod .'_email_widget_id'];
 946         $get .= '&' . $mod . '_email_widget_id='. $widget_id;
 947         $get .= '&emailAddressWidget='.$_POST['emailAddressWidget'];
 948
 949         while(isset($_REQUEST[$mod . $widget_id . 'emailAddress' . $count])) {
 950               $get .= "&" . $mod . $widget_id . "emailAddress" . $count . "=" . urlencode($_REQUEST[$mod . $widget_id . 'emailAddress' . $count]);
 951               $count++;
 952         } //while
 953
 954         while(isset($_REQUEST[$mod . $widget_id . 'emailAddressVerifiedValue' . $count])) {
 955               $get .= "&" . $mod . $widget_id . "emailAddressVerifiedValue" . $count . "=" . urlencode($_REQUEST[$mod . $widget_id . 'emailAddressVerifiedValue' . $count]);
 956               $count++;
 957         } //while
 958
 959         $options = array('emailAddressPrimaryFlag', 'emailAddressOptOutFlag', 'emailAddressInvalidFlag', 'emailAddressDeleteFlag', 'emailAddressReplyToFlag');
 960
 961         foreach($options as $option) {
 962             $count = 0;
 963             $optionIdentifier = $mod.$widget_id.$option;
 964             if(isset($_REQUEST[$optionIdentifier])) {
 965                if(is_array($_REQUEST[$optionIdentifier])) {
 966                    foreach($_REQUEST[$optionIdentifier] as $optOut) {
 967                       $get .= "&" . $optionIdentifier . "[" . $count . "]=" . $optOut;
 968                       $count++;
 969                    } //foreach
 970                } else {
 971                    $get .= "&" . $optionIdentifier . "=" . $_REQUEST[$optionIdentifier];
 972                }
 973             } //if
 974         } //foreach
 975         return $get;
 976
 977     }
 978
 979     function setView($view) {
 980        $this->view = $view;
 981     }
 982
 983 /**
 984  * This function is here so the Employees/Users division can be handled cleanly in one place
 985  * @param object $focus SugarBean
 986  * @return string The value for the bean_module column in the email_addr_bean_rel table
 987  */
 988     function getCorrectedModule(&$module) {
 989         return ($module == "Employees")? "Users" : $module;
 990     }
 991 } // end class def
 992
 993
 994 /**
 995  * Convenience function for MVC (Mystique)
 996  * @param object $focus SugarBean
 997  * @param string $field unused
 998  * @param string $value unused
 999  * @param string $view DetailView or EditView
1000  * @return string
1001  */
1002 function getEmailAddressWidget($focus, $field, $value, $view, $tabindex='') {
1003     $sea = new SugarEmailAddress();
1004     $sea->setView($view);
1005
1006         if($view == 'EditView' || $view == 'QuickCreate' || $view == 'ConvertLead') {
1007             $module = $focus->module_dir;
1008             if ($view == 'ConvertLead' && $module == "Contacts")  $module = "Leads";
1009
1010             return $sea->getEmailAddressWidgetEditView($focus->id, $module, false,'',$tabindex);
1011         }
1012
1013     return $sea->getEmailAddressWidgetDetailView($focus);
1014 }