include/SugarFolders/SugarFolders.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-2013 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 require_once("include/ytree/Tree.php");
  46 require_once("include/ytree/ExtNode.php");
  47
  48 /**
  49  * Polymorphic buckets - place any item in a folder
  50  */
  51 class SugarFolder {
  52
  53         // public attributes
  54         var $id;
  55         var $name;
  56         var $parent_folder;
  57         var $has_child = 0; // flag node has child
  58         var $is_group = 0;
  59         var $is_dynamic = 0;
  60         var $dynamic_query = '';
  61         var $assign_to_id;
  62         var $created_by;
  63         var $modified_by;
  64         var $date_created;
  65         var $date_modified;
  66         var $deleted;
  67         var $folder_type;
  68
  69         var $db;
  70         var $new_with_id = false;
  71
  72         // core queries
  73         var $core = "SELECT f.id, f.name, f.has_child, f.is_group, f.is_dynamic, f.dynamic_query, f.folder_type, f.created_by, i.deleted FROM folders f left join inbound_email i on f.id = i.groupfolder_id ";
  74         var $coreSubscribed = "SELECT f.id, f.name, f.has_child, f.is_group, f.is_dynamic, f.dynamic_query, f.folder_type, f.created_by, i.deleted FROM folders f LEFT JOIN folders_subscriptions fs ON f.id = fs.folder_id LEFT JOIN inbound_email i on  i.groupfolder_id = f.id ";
  75         var $coreWhere = "WHERE f.deleted = 0 ";
  76         var $coreWhereSubscribed = "WHERE f.deleted = 0 AND fs.assigned_user_id = ";
  77         var $coreOrderBy = " ORDER BY f.is_dynamic, f.is_group, f.name ASC ";
  78
  79         var $hrSortLocal = array(
  80             'flagged' => 'type',
  81             'status'  => 'reply_to_status',
  82             'from'    => 'emails_text.from_addr',
  83             'subject' => 'name',
  84             'date'    => 'date_sent',
  85             'AssignedTo' => 'assigned_user_id',
  86             'flagged' => 'flagged'
  87         );
  88     var $defaultSort = 'date';
  89     var $defaultDirection = "DESC";
  90
  91         // private attributes
  92         var $_depth;
  93
  94         /**
  95          * Sole constructor
  96          */
  97         function SugarFolder() {
  98                 $this->db = DBManagerFactory::getInstance();
  99         }
 100
 101         function deleteEmailFromAllFolder($id) {
 102                 $q = "delete from folders_rel where polymorphic_module = 'Emails' AND polymorphic_id = '{$id}' ";
 103                 $r = $this->db->query($q);
 104         }
 105
 106         function deleteEmailFromFolder($id) {
 107                 $q = "delete from folders_rel where polymorphic_module = 'Emails' AND polymorphic_id = '{$id}' AND folder_id = '{$this->id}'";
 108                 $r = $this->db->query($q);
 109         }
 110
 111         function checkEmailExistForFolder($id) {
 112                 $q = "SELECT COUNT(*) c from folders_rel where polymorphic_module = 'Emails' AND polymorphic_id = '{$id}' AND folder_id = '{$this->id}'";
 113                 $r = $this->db->query($q);
 114                 $a = $this->db->fetchByAssoc($r);
 115                 if ($a['c'] > 0) {
 116                         return true;
 117                 } else {
 118                         return false;
 119                 } // else
 120         }
 121         /**
 122          * Moves beans from one folder to another folder
 123          * @param string fromFolder GUID of source folder
 124          * @param string toFolder GUID of destination folder
 125          * @param string beanId GUID of SugarBean being moved
 126          */
 127         function move($fromFolder, $toFolder, $beanId) {
 128                 $q = "UPDATE folders_rel SET folder_id = '{$toFolder}' WHERE folder_id = '{$fromFolder}' AND polymorphic_id = '{$beanId}' AND deleted = 0";
 129                 $r = $this->db->query($q);
 130         }
 131
 132         /**
 133          * Copies one bean from one folder to another
 134          */
 135         function copyBean($fromFolder, $toFolder, $beanId, $module) {
 136                 $q = "INSERT INTO folders_rel (id, folder_id, polymorphic_module, polymorphic_id, deleted)
 137                                 VALUES('{$guid}', '{$toFolder}', '{$module}', '{$beanId}', 0)";
 138                 $r = $this->db->query($q);
 139         }
 140
 141         /**
 142          * Creates a new group Folder from the passed fields
 143          * @param array fields
 144          */
 145         function setFolder($fields) {
 146
 147                 global $current_user;
 148                 if(empty($fields['groupFoldersUser'])) {
 149                         $fields['groupFoldersUser'] = $current_user->id;
 150                 }
 151
 152                 $this->name = $fields['name'];
 153                 $this->parent_folder = $fields['parent_folder'];
 154                 $this->has_child = 0;
 155                 $this->is_group = 1;
 156                 $this->assign_to_id = $fields['groupFoldersUser'];
 157
 158                 $this->save();
 159         }
 160
 161         /**
 162          * Returns GUIDs of folders that the user in focus is subscribed to
 163          * @param object user User object in focus
 164          * @return array
 165          */
 166         function getSubscriptions($user) {
 167                 if(empty($user)) {
 168                         global $current_user;
 169                         $user = $current_user;
 170                 }
 171
 172                 $q = "SELECT folder_id FROM folders_subscriptions WHERE assigned_user_id = '{$user->id}'";
 173                 $r = $this->db->query($q);
 174                 $ret = array();
 175                 while($a = $this->db->fetchByAssoc($r)) {
 176                         $ret[] = $a['folder_id'];
 177                 }
 178                 return $ret;
 179         }
 180
 181         /**
 182          * Sets a user's preferences for subscribe folders (Sugar only)
 183          * @param array subs Array of IDs for subscribed folders
 184          */
 185         function setSubscriptions($subs) {
 186                 global $current_user;
 187
 188                 if(empty($current_user->id)) {
 189                         $GLOBALS['log']->fatal("*** FOLDERS: tried to update folder subscriptions for a user with no ID");
 190                         return false;
 191                 }
 192
 193                 $cleanSubscriptions = array();
 194
 195                 // ensure parent folders are selected, regardless.
 196                 foreach($subs as $id) {
 197                         $id = trim($id);
 198                         if(!empty($id)) {
 199                                 $cleanSubscriptions[] = $id;
 200                                 $qChk = "SELECT parent_folder FROM folders WHERE id = '{$id}'";
 201                                 $rChk = $this->db->query($qChk);
 202                                 $aChk = $this->db->fetchByAssoc($rChk);
 203
 204                                 if(!empty($aChk['parent_folder'])) {
 205                                         $cleanSubscriptions = $this->getParentIDRecursive($aChk['parent_folder'], $cleanSubscriptions);
 206                                 }
 207                         }
 208                 }
 209
 210                 $this->clearSubscriptions();
 211
 212                 foreach($cleanSubscriptions as $id) {
 213                     $this->insertFolderSubscription($id, $current_user->id);
 214                 }
 215         }
 216
 217         /**
 218          * Given a folder id and user id, create a folder subscription entry.
 219          *
 220          * @param String $folderId
 221          * @param String $userID
 222          * @return String The id of the newly created folder subscription.
 223          */
 224         function insertFolderSubscription($folderId, $userID)
 225         {
 226             $guid = create_guid();
 227                 $query = "INSERT INTO folders_subscriptions (id, folder_id, assigned_user_id) VALUES ('{$guid}', '{$folderId}', '{$userID}')";
 228                 $r = $this->db->query($query);
 229                 return $guid;
 230         }
 231         /**
 232          * Recursively finds parent node until it hits root
 233          * @param string id Starting id to follow up
 234          * @param array ret collected ids
 235          * @return array of IDs
 236          */
 237         function getParentIDRecursive($id, $ret=array()) {
 238                 $q = "SELECT * FROM folders WHERE id = '{$id}' AND deleted = 0";
 239                 $r = $this->db->query($q);
 240                 $a = $this->db->fetchByAssoc($r);
 241
 242                 if(!in_array($id, $ret)) {
 243                         $ret[] = $id;
 244                 }
 245
 246                 if($a['parent_folder'] != '') {
 247                         $qChk = "SELECT parent_folder FROM folders WHERE id = '{$id}'";
 248                         $rChk = $this->db->query($qChk);
 249                         $aChk = $this->db->fetchByAssoc($rChk);
 250
 251                         if(!empty($aChk['parent_folder'])) {
 252                                 $ret = $this->getParentIDRecursive($aChk['parent_folder'], $ret);
 253                         }
 254                 }
 255
 256                 return $ret;
 257         }
 258
 259         /**
 260          * Deletes subscriptions to folders in preparation for reset
 261          */
 262         function clearSubscriptions() {
 263                 global $current_user;
 264
 265                 if(!empty($current_user->id)) {
 266                         $q = "DELETE FROM folders_subscriptions WHERE assigned_user_id = '{$current_user->id}'";
 267                         $r = $this->db->query($q);
 268                 }
 269         }
 270
 271
 272         /**
 273          * Deletes all subscriptions for a particular folder id
 274          *
 275          * @return unknown
 276          */
 277         function clearSubscriptionsForFolder($folderID)
 278         {
 279             $query = "DELETE FROM folders_subscriptions WHERE folder_id = '$folderID'";
 280             $r = $this->db->query($query);
 281         }
 282
 283         protected function generateArchiveFolderQuery()
 284         {
 285                 global $current_user;
 286             $q = <<<ENDQ
 287 SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails
 288 JOIN emails_text on emails.id = emails_text.email_id
 289 WHERE emails.deleted=0 AND emails.type NOT IN ('out', 'draft') AND emails.status NOT IN ('sent', 'draft') AND emails.id IN (
 290 SELECT eear.email_id FROM emails_email_addr_rel eear
 291 JOIN email_addr_bean_rel eabr ON eabr.email_address_id=eear.email_address_id AND eabr.bean_id = '{$current_user->id}' AND eabr.bean_module = 'Users'
 292 WHERE eear.deleted=0
 293 )
 294 ENDQ;
 295         return $q;
 296         }
 297
 298         function generateSugarsDynamicFolderQuery()
 299         {
 300                 global $current_user;
 301                 $type = $this->folder_type;
 302                 if($type == 'archived') {
 303                     return $this->generateArchiveFolderQuery();
 304                 }
 305                 $status = $type;
 306                 if($type == "sent") {
 307                         $type = "out";
 308                 }
 309                 if($type == 'inbound') {
 310                         $ret = " AND emails.status NOT IN ('sent', 'archived', 'draft') AND emails.type NOT IN ('out', 'archived', 'draft')";
 311                 } else {
 312                         $ret = " AND emails.status NOT IN ('archived') AND emails.type NOT IN ('archived')";
 313                 }
 314                 $q = "SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails" .
 315                                                                    " JOIN emails_text on emails.id = emails_text.email_id
 316                                    WHERE (type = '{$type}' OR status = '{$status}') AND assigned_user_id = '{$current_user->id}' AND emails.deleted=0";
 317                 return $q . $ret;
 318         } // fn
 319
 320
 321         /**
 322          * returns array of items for listView display in yui-ext Grid
 323          */
 324         function getListItemsForEmailXML($folderId, $page = 1, $pageSize = 10, $sort = '', $direction='') {
 325                 require_once('include/TimeDate.php');
 326                 global $timedate;
 327                 global $current_user;
 328                 global $beanList;
 329                 global $sugar_config;
 330                 global $app_strings;
 331
 332                 $this->retrieve($folderId);
 333                 $start = ($page - 1) * $pageSize;
 334
 335                 $sort = (empty($sort)) ? $this->defaultSort : $sort;
 336         if (!in_array(strtolower($direction), array('asc', 'desc'))) {
 337             $direction = $this->defaultDirection;
 338         }
 339
 340         if (!empty($this->hrSortLocal[$sort])) {
 341             $order = " ORDER BY {$this->hrSortLocal[$sort]} {$direction}";
 342         } else {
 343             $order = "";
 344         }
 345
 346                 if($this->is_dynamic) {
 347                         $r = $this->db->limitQuery(from_html($this->generateSugarsDynamicFolderQuery() . $order), $start, $pageSize);
 348                 } else {
 349                         // get items and iterate through them
 350                         $q = "SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails JOIN folders_rel ON emails.id = folders_rel.polymorphic_id" .
 351                                   " JOIN emails_text on emails.id = emails_text.email_id
 352                   WHERE folders_rel.folder_id = '{$folderId}' AND folders_rel.deleted = 0 AND emails.deleted = 0";
 353                         if ($this->is_group) {
 354                                 $q = $q . " AND (emails.assigned_user_id is null or emails.assigned_user_id = '')";
 355                         }
 356                         $r = $this->db->limitQuery($q . $order, $start, $pageSize);
 357                 }
 358
 359                 $return = array();
 360
 361                 $email = new Email(); //Needed for email specific functions.
 362
 363                 while($a = $this->db->fetchByAssoc($r)) {
 364
 365                         $temp = array();
 366                         $temp['flagged'] = (is_null($a['flagged']) || $a['flagged'] == '0') ? '' : 1;
 367                         $temp['status'] = (is_null($a['reply_to_status']) || $a['reply_to_status'] == '0') ? '' : 1;
 368                         $temp['from']   = preg_replace('/[\x00-\x08\x0B-\x1F]/', '', $a['from_addr']);
 369                         $temp['subject'] = $a['name'];
 370                         $temp['date']   = $timedate->to_display_date_time($this->db->fromConvert($a['date_sent'], 'datetime'));
 371                         $temp['uid'] = $a['id'];
 372                         $temp['mbox'] = 'sugar::'.$a['polymorphic_module'];
 373                         $temp['ieId'] = $folderId;
 374                         $temp['site_url'] = $sugar_config['site_url'];
 375                         $temp['seen'] = ($a['status'] == 'unread') ? 0 : 1;
 376                         $temp['type'] = $a['type'];
 377                         $temp['hasAttach'] = $email->doesImportedEmailHaveAttachment($a['id']);
 378                         $temp['to_addrs'] = preg_replace('/[\x00-\x08\x0B-\x1F]/', '', $a['to_addrs']);
 379                         $return[] = $temp;
 380                 }
 381
 382
 383                 $metadata = array();
 384                 $metadata['mbox'] = $app_strings['LBL_EMAIL_SUGAR_FOLDER'].': '.$this->name;
 385                 $metadata['ieId'] = $folderId;
 386                 $metadata['name'] = $this->name;
 387                 $metadata['unreadChecked'] = ($current_user->getPreference('showUnreadOnly', 'Emails') == 1) ? 'CHECKED' : '';
 388                 $metadata['out'] = $return;
 389
 390                 return $metadata;
 391         }
 392
 393         function getCountItems ( $folderId ) {
 394                 global $current_user ;
 395                 global $beanList ;
 396                 global $sugar_config ;
 397                 global $app_strings ;
 398
 399                 $this->retrieve ( $folderId ) ;
 400                 if ($this->is_dynamic) {
 401                 $pattern = '/SELECT(.*?)(\s){1}FROM(\s){1}/is';  // ignores the case
 402                 $replacement = 'SELECT count(*) c FROM ';
 403                 $modified_select_query = preg_replace($pattern, $replacement, $this->generateSugarsDynamicFolderQuery(), 1);
 404                 $r = $this->db->query ( from_html ( $modified_select_query )) ;
 405                 } else {
 406                         // get items and iterate through them
 407                         $q = "SELECT count(*) c FROM folders_rel JOIN emails ON emails.id = folders_rel.polymorphic_id" .
 408                         " WHERE folder_id = '{$folderId}' AND folders_rel.deleted = 0 AND emails.deleted = 0" ;
 409                         if ($this->is_group) {
 410                                 $q .= " AND (emails.assigned_user_id is null or emails.assigned_user_id = '')";
 411                         }
 412                         $r = $this->db->query ( $q ) ;
 413                 }
 414
 415                 $a = $this->db->fetchByAssoc($r);
 416                 return $a['c'];
 417         }
 418
 419     function getCountUnread ( $folderId ) {
 420         global $current_user ;
 421         global $beanList ;
 422         global $sugar_config ;
 423         global $app_strings ;
 424
 425         $this->retrieve ( $folderId ) ;
 426         if ($this->is_dynamic) {
 427                 $pattern = '/SELECT(.*?)(\s){1}FROM(\s){1}/is';  // ignores the case
 428                 $replacement = 'SELECT count(*) c FROM ';
 429                 $modified_select_query = preg_replace($pattern, $replacement, $this->generateSugarsDynamicFolderQuery(), 1);
 430                 $r = $this->db->query (from_html($modified_select_query) . " AND emails.status = 'unread'") ;
 431         } else {
 432             // get items and iterate through them
 433             $q = "SELECT count(*) c FROM folders_rel fr JOIN emails on fr.folder_id = '{$folderId}' AND fr.deleted = 0 " .
 434                "AND fr.polymorphic_id = emails.id AND emails.status = 'unread' AND emails.deleted = 0" ;
 435             if ($this->is_group) {
 436                 $q .= " AND (emails.assigned_user_id is null or emails.assigned_user_id = '')";
 437             }
 438             $r = $this->db->query ( $q ) ;
 439         }
 440
 441                 $a = $this->db->fetchByAssoc($r);
 442         return $a['c'];
 443     }
 444
 445
 446         /**
 447          * Convenience method, pass a SugarBean and User to this to add anything to a given folder
 448          */
 449         function addBean($bean, $user=null) {
 450                 if(empty($bean->id) || empty($bean->module_dir)) {
 451                         $GLOBALS['log']->fatal("*** FOLDERS: addBean() got empty bean - not saving");
 452                         return false;
 453                 } elseif(empty($this->id)) {
 454                         $GLOBALS['log']->fatal("*** FOLDERS: addBean() is trying to save to a non-saved or non-existent folder");
 455                         return false;
 456                 }
 457
 458                 global $current_user;
 459                 if($user == null) {
 460                         $user = $current_user;
 461                 }
 462
 463                 $guid = create_guid();
 464
 465                 $q = "INSERT INTO folders_rel (id, folder_id, polymorphic_module, polymorphic_id, deleted)
 466                                 VALUES('{$guid}', '{$this->id}', '{$bean->module_dir}', '{$bean->id}', 0)";
 467                 $r = $this->db->query($q);
 468                 return true;
 469         }
 470
 471         /**
 472          * Builds up a metacollection of user/group folders to be passed to processor methods
 473          * @param object User object, defaults to $current_user
 474          * @return array Array of abstract folder objects
 475          */
 476         function retrieveFoldersForProcessing($user, $subscribed=true) {
 477                 global $sugar_config;
 478                 global $current_language, $current_user;
 479
 480                 $emails_mod_strings = return_module_language($current_language, "Emails");
 481                 $myEmailTypeString = 'inbound';
 482                 $myDraftsTypeString = 'draft';
 483                 $mySentEmailTypeString = 'sent';
 484
 485                 if(empty($user)) {
 486                         global $current_user;
 487                         $user = $current_user;
 488                 }
 489                 $rootWhere = '';
 490         $teamSecurityClause = '';
 491
 492
 493
 494         $rootWhere .= "AND (f.parent_folder IS NULL OR f.parent_folder = '')";
 495
 496                 if($subscribed) {
 497                         $q = $this->coreSubscribed.$teamSecurityClause.$this->coreWhereSubscribed."'{$user->id}' ".$rootWhere.$this->coreOrderBy;
 498                 } else {
 499                         $q = $this->core.$teamSecurityClause.$this->coreWhere.$rootWhere.$this->coreOrderBy;
 500                 }
 501                 $r = $this->db->query($q);
 502                 $return = array();
 503
 504                 $found = array();
 505                 while($a = $this->db->fetchByAssoc($r)) {
 506                         if ((($a['folder_type'] == $myEmailTypeString) ||
 507                                 ($a['folder_type'] == $myDraftsTypeString) ||
 508                                 ($a['folder_type'] == $mySentEmailTypeString)) &&
 509                                 ($a['created_by'] != $current_user->id)) {
 510
 511                                 continue;
 512                         } // if
 513                         if (!isset($found[$a['id']])) {
 514                 $found[$a['id']] = true;
 515                             $return[] = $a;
 516                         }
 517                 }
 518                 return $return;
 519         }
 520     /**
 521          * Preps object array for async call from user's Settings->Folders
 522          */
 523         function getGroupFoldersForSettings($focusUser=null) {
 524                 global $app_strings;
 525
 526                 $grp = array();
 527
 528                 $folders = $this->retrieveFoldersForProcessing($focusUser, false);
 529                 $subscriptions = $this->getSubscriptions($focusUser);
 530
 531                 foreach($folders as $a) {
 532                         $a['selected'] = (in_array($a['id'], $subscriptions)) ? true : false;
 533             $a['origName'] = $a['name'];
 534
 535                         if($a['is_group'] == 1)
 536                                 if ($a['deleted'] != 1)
 537                                         $grp[] = $a;
 538                 }
 539
 540                 return $grp;
 541         }
 542         /**
 543          * Preps object array for async call from user's Settings->Folders
 544          */
 545         function getFoldersForSettings($focusUser=null) {
 546                 global $app_strings;
 547
 548                 $user = array();
 549                 $grp = array();
 550                 $user[] = array('id' => '', 'name' => $app_strings['LBL_NONE'], 'has_child' => 0, 'is_group' => 0, 'selected' => false);
 551                 $grp[] = array('id' => '', 'name' => $app_strings['LBL_NONE'], 'has_child' => 0, 'is_group' => 1, 'selected' => false, 'origName' => "");
 552
 553                 $folders = $this->retrieveFoldersForProcessing($focusUser, false);
 554                 $subscriptions = $this->getSubscriptions($focusUser);
 555
 556                 foreach($folders as $a) {
 557                         $a['selected'] = (in_array($a['id'], $subscriptions)) ? true : false;
 558             $a['origName'] = $a['name'];
 559             if( isset($a['dynamic_query']) )
 560                 unset($a['dynamic_query']);
 561                         if($a['is_group'] == 1) {
 562                                 $grp[] = $a;
 563                         } else {
 564                                 $user[] = $a;
 565                         }
 566
 567                         if($a['has_child'] == 1) {
 568                                 $qGetChildren = $this->core.$this->coreWhere."AND parent_folder = '{$a['id']}'";
 569                                 $rGetChildren = $this->db->query($qGetChildren);
 570
 571                                 while($aGetChildren = $this->db->fetchByAssoc($rGetChildren)) {
 572                                         if($a['is_group']) {
 573                                                 $this->_depth = 1;
 574                                                 $grp = $this->getFoldersChildForSettings($aGetChildren, $grp, $subscriptions);
 575                                         } else {
 576                                                 $this->_depth = 1;
 577                                                 $user = $this->getFoldersChildForSettings($aGetChildren, $user, $subscriptions);
 578                                         }
 579                                 }
 580                         }
 581                 }
 582
 583                 $ret = array(
 584                         'userFolders'   => $user,
 585                         'groupFolders'  => $grp,
 586                 );
 587                 return $ret;
 588         }
 589
 590         function getFoldersChildForSettings($a, $collection, $subscriptions) {
 591                 $a['selected'] = (in_array($a['id'], $subscriptions)) ? true : false;
 592                 $a['origName'] = $a['name'];
 593
 594                 if(isset($a['dynamic_query']))
 595                 {
 596                    unset($a['dynamic_query']);
 597                 }
 598
 599                 for($i=0; $i<$this->_depth; $i++)
 600                 {
 601                         $a['name'] = ".".$a['name'];
 602                 }
 603
 604                 $collection[] = $a;
 605
 606                 if($a['has_child'] == 1) {
 607                         $this->_depth++;
 608                         $qGetChildren = $this->core.$this->coreWhere."AND parent_folder = '{$a['id']}'";
 609                         $rGetChildren = $this->db->query($qGetChildren);
 610                         while($aGetChildren = $this->db->fetchByAssoc($rGetChildren)) {
 611                                 $collection = $this->getFoldersChildForSettings($aGetChildren, $collection, $subscriptions);
 612                         }
 613                 }
 614
 615                 return $collection;
 616         }
 617
 618         /**
 619          * Returns the number of "new" items (based on passed criteria)
 620          * @param string id ID of folder
 621          * @param array criteria
 622          *              expected:
 623          *              array('field' => 'status',
 624          *                              'value' => 'unread');
 625          * @param array
 626          * @return int
 627          */
 628         function getCountNewItems($id, $criteria, $folder) {
 629                 global $current_user;
 630
 631                 $sugarFolder = new SugarFolder();
 632                 return $sugarFolder->getCountUnread($id);
 633         }
 634
 635         /**
 636          * Collects, sorts, and builds tree of user's folders
 637          * @param objec $rootNode Reference to tree root node
 638          * @param array $folderStates User pref folder open/closed states
 639          * @param object $user Optional User in focus, default current_user
 640          * @return array
 641          */
 642         function getUserFolders(&$rootNode, $folderStates, $user=null, $forRefresh=false) {
 643                 if(empty($user)) {
 644                         global $current_user;
 645                         $user = $current_user;
 646                 }
 647                 global $mod_strings;
 648                 $folders = $this->retrieveFoldersForProcessing($user, true);
 649                 $subscriptions = $this->getSubscriptions($user);
 650
 651                 $refresh = ($forRefresh) ? array() : null;
 652
 653                 if(!is_array($folderStates)) {
 654                         $folderStates = array();
 655                 }
 656
 657                 foreach($folders as $a) {
 658                         if ($a['deleted'] == 1)
 659                                 continue;
 660                         $label = ($a['name'] == 'My Email' ? $mod_strings['LNK_MY_INBOX'] : $a['name']);
 661
 662                         $unseen = $this->getCountNewItems($a['id'], array('field' => 'status', 'value' => 'unread'), $a);
 663
 664                         $folderNode = new ExtNode($a['id'], $label);
 665                         $folderNode->dynamicloadfunction = '';
 666                         $folderNode->expanded = false;
 667
 668                         if(array_key_exists('Home::'.$a['id'], $folderStates)) {
 669                                 if($folderStates['Home::'.$a['id']] == 'open') {
 670                                         $folderNode->expanded = true;
 671                                 }
 672                         }
 673                         $nodePath = "Home::".$folderNode->_properties['id'];
 674
 675                         $folderNode->dynamic_load = true;
 676                 //$folderNode->set_property('click', " SUGAR.email2.listView.populateListFrameSugarFolder(YAHOO.namespace('frameFolders').selectednode, '{$a['id']}', 'false');");
 677                 $folderNode->set_property('ieId', 'folder');
 678                 $folderNode->set_property('is_group', ($a['is_group'] == 1) ? 'true' : 'false');
 679                 $folderNode->set_property('is_dynamic', ($a['is_dynamic'] == 1) ? 'true' : 'false');
 680                 $folderNode->set_property('mbox', $folderNode->_properties['id']);
 681                 $folderNode->set_property('unseen', $unseen);
 682                 $folderNode->set_property('id', $a['id']);
 683                 $folderNode->set_property('folder_type', $a['folder_type']);
 684                 $folderNode->set_property('children', array());
 685
 686                         if(in_array($a['id'], $subscriptions) && $a['has_child'] == 1) {
 687                                 $qGetChildren = $this->core.$this->coreWhere."AND parent_folder = '{$a['id']}'";
 688                                 $rGetChildren = $this->db->query($qGetChildren);
 689
 690                                 while($aGetChildren = $this->db->fetchByAssoc($rGetChildren)) {
 691                                         if(in_array($aGetChildren['id'], $subscriptions)) {
 692                                                 $folderNode->add_node($this->buildTreeNodeFolders($aGetChildren, $nodePath, $folderStates, $subscriptions));
 693                                         }
 694                                 }
 695                         }
 696                         $rootNode->add_node($folderNode);
 697                 }
 698
 699                 /* the code below is called only by Settings->Folders when selecting folders to subscribe to */
 700                 if($forRefresh) {
 701                         $metaNode = array();
 702
 703                         if(!empty($rootNode->nodes)) {
 704                                 foreach($rootNode->nodes as $node) {
 705                                         $metaNode[] = $this->buildTreeNodeRefresh($node, $subscriptions);
 706                                 }
 707                         }
 708                         return $metaNode;
 709                 }
 710         }
 711
 712         /**
 713          * Builds up a metanode for folder refresh (Sugar folders only)
 714          */
 715         function buildTreeNodeRefresh($folderNode, $subscriptions) {
 716                 $metaNode = $folderNode->_properties;
 717                 $metaNode['expanded'] = $folderNode->expanded;
 718                 $metaNode['text'] = $folderNode->_label;
 719                 if($metaNode['is_group'] == 'true') {
 720                         $metaNode['cls'] = 'groupFolder';
 721                 } else {
 722                     $metaNode['cls'] = 'sugarFolder';
 723                 }
 724                 $metaNode['id'] = $folderNode->_properties['id'];
 725                 $metaNode['children'] = array();
 726                 $metaNode['type'] = 1;
 727                 $metaNode['leaf'] = false;
 728                 $metaNode['isTarget'] = true;
 729                 $metaNode['allowChildren'] = true;
 730
 731                 if(!empty($folderNode->nodes)) {
 732                         foreach($folderNode->nodes as $node) {
 733                                 if(in_array($node->_properties['id'], $subscriptions))
 734                                         $metaNode['children'][] = $this->buildTreeNodeRefresh($node, $subscriptions);
 735                         }
 736                 }
 737                 return $metaNode;
 738         }
 739
 740         /**
 741          * Builds children nodes for folders for TreeView
 742          * @return $folderNode TreeView node
 743          */
 744         function buildTreeNodeFolders($a, $nodePath, $folderStates, $subscriptions) {
 745                 $label = $a['name'];
 746                 global $mod_strings;
 747                 if($a['name'] == 'My Drafts') {
 748                         $label = $mod_strings['LBL_LIST_TITLE_MY_DRAFTS'];
 749                 }
 750                 if($a['name'] == 'Sent Emails') {
 751                         $label = $mod_strings['LBL_LIST_TITLE_MY_SENT'];
 752                 }
 753                 $unseen = $this->getCountNewItems($a['id'], array('field' => 'status', 'value' => 'unread'), $a);
 754
 755                 $folderNode = new ExtNode($a['id'], $label);
 756                 $folderNode->dynamicloadfunction = '';
 757                 $folderNode->expanded = false;
 758
 759                 $nodePath .= "::{$a['id']}";
 760
 761                 if(array_key_exists($nodePath, $folderStates)) {
 762                         if($folderStates[$nodePath] == 'open') {
 763                                 $folderNode->expanded = true;
 764                         }
 765                 }
 766
 767                 $folderNode->dynamic_load = true;
 768         $folderNode->set_property('click', "SUGAR.email2.listView.populateListFrameSugarFolder(YAHOO.namespace('frameFolders').selectednode, '{$a['id']}', 'false');");
 769         $folderNode->set_property('ieId', 'folder');
 770         $folderNode->set_property('mbox', $a['id']);
 771                 $folderNode->set_property('is_group', ($a['is_group'] == 1) ? 'true' : 'false');
 772         $folderNode->set_property('is_dynamic', ($a['is_dynamic'] == 1) ? 'true' : 'false');
 773         $folderNode->set_property('unseen', $unseen);
 774             $folderNode->set_property('folder_type', $a['folder_type']);
 775
 776                 if(in_array($a['id'], $subscriptions) && $a['has_child'] == 1) {
 777                         $qGetChildren = $this->core.$this->coreWhere."AND parent_folder = '{$a['id']}' ".$this->coreOrderBy;
 778                         $rGetChildren = $this->db->query($qGetChildren);
 779
 780                         while($aGetChildren = $this->db->fetchByAssoc($rGetChildren)) {
 781                                 $folderNode->add_node($this->buildTreeNodeFolders($aGetChildren, $nodePath, $folderStates, $subscriptions));
 782                         }
 783                 }
 784                 return $folderNode;
 785         }
 786
 787         /**
 788          * Flags a folder as deleted
 789          * @return bool True on success
 790          */
 791         function delete() {
 792                 global $current_user;
 793
 794                 if(!empty($this->id)) {
 795                         if($this->has_child) {
 796                                 $this->deleteChildrenCascade($this->id);
 797                         }
 798
 799                         $ownerCheck = ($current_user->is_admin == 0) ? " AND created_by = '{$current_user->id}'" : "";
 800                         $q = "UPDATE folders SET deleted = 1 WHERE id = '{$this->id}'{$ownerCheck}";
 801                         $r = $this->db->query($q);
 802                         return true;
 803                 }
 804                 return false;
 805         }
 806
 807         /**
 808          * Deletes all children in a cascade
 809          * @param string $id ID of parent
 810          * @return bool True on success
 811          */
 812         function deleteChildrenCascade($id) {
 813                 global $current_user;
 814
 815                 $canContinue = true;
 816                 $checkInboundQuery = "SELECT count(*) c FROM inbound_email WHERE groupfolder_id = '{$id}' and deleted = 0";
 817                 $resultSet = $this->db->query($checkInboundQuery);
 818                 $a = $this->db->fetchByAssoc($resultSet);
 819                 if ($a['c'] > 0) {
 820                         return false;
 821                 } // if
 822
 823                 $q = "SELECT COUNT(*) c from folders_rel where polymorphic_module = 'Emails' AND polymorphic_id = '{$id}' AND folder_id = '{$this->id}'";
 824
 825                 $checkEmailQuery = "SELECT count(*) c FROM folders_rel where polymorphic_module = 'Emails' and folder_id = '{$id}' and deleted = 0";
 826                 $resultSet = $this->db->query($checkEmailQuery);
 827                 $a = $this->db->fetchByAssoc($resultSet);
 828                 if ($a['c'] > 0) {
 829                         return false;
 830                 } // if
 831
 832                 $q = "SELECT * FROM folders WHERE id = '{$id}'";
 833                 $r = $this->db->query($q);
 834                 $a = $this->db->fetchByAssoc($r);
 835
 836                 if($a['has_child'] == 1) {
 837                         $q2 = "SELECT id FROM folders WHERE parent_folder = '{$id}'";
 838                         $r2 = $this->db->query($q2);
 839
 840                         while($a2 = $this->db->fetchByAssoc($r2)) {
 841                                 $canContinue = $this->deleteChildrenCascade($a2['id']);
 842                         }
 843                 }
 844
 845                 if ($canContinue) {
 846                         // flag deleted
 847                         $ownerCheck = ($current_user->is_admin == 0) ? " AND created_by = '{$current_user->id}'" : "";
 848                         $q3 = "UPDATE folders SET deleted = 1 WHERE id = '{$id}'{$ownerCheck}";
 849                         $r3 = $this->db->query($q3);
 850
 851                         // flag rels
 852                         $qRel = "UPDATE folders_rel SET deleted = 1 WHERE folder_id = '{$id}'";
 853                         $rRel = $this->db->query($qRel);
 854
 855                         // delete subscriptions
 856                         $qSub = "DELETE FROM folders_subscriptions WHERE folder_id = '{$id}'";
 857                         $rSub = $this->db->query($qSub);
 858                 }
 859                 return $canContinue;
 860                 //_pp($q3);_pp($qRel);_pp($qSub);
 861         }
 862
 863
 864         /**
 865          * Saves folder
 866          * @return bool
 867          */
 868         function save($addSubscriptions = TRUE) {
 869                 global $current_user;
 870
 871                 $this->dynamic_query = $this->db->quote($this->dynamic_query);
 872
 873                 if((empty($this->id) && $this->new_with_id == false) || (!empty($this->id) && $this->new_with_id == true))
 874                 {
 875
 876                     if( empty($this->id) )
 877                     {
 878                             $guid = create_guid();
 879                             $this->id = $guid;
 880                     }
 881
 882                         $q = "INSERT INTO folders(id, name, folder_type, parent_folder, has_child, is_group, is_dynamic, dynamic_query, assign_to_id, ".
 883                                 "created_by, modified_by, deleted)".
 884
 885                                 " VALUES('{$this->id}', '{$this->name}', '{$this->folder_type}', '{$this->parent_folder}', {$this->has_child}, {$this->is_group}, {$this->is_dynamic}, '{$this->dynamic_query}', '{$this->assign_to_id}', " .
 886                                 "'{$current_user->id}', '{$current_user->id}', 0)";
 887
 888
 889                         if($addSubscriptions)
 890                         {
 891                             // create default subscription
 892                             $this->addSubscriptionsToGroupFolder();
 893                         }
 894
 895                         // if parent_id is set, update parent's has_child flag
 896                         $q3 = "UPDATE folders SET has_child = 1 WHERE id = '{$this->parent_folder}'";
 897                         $r3 = $this->db->query($q3);
 898                 }
 899                 else {
 900                         $q = "UPDATE folders SET name = '{$this->name}', parent_folder = '{$this->parent_folder}', dynamic_query = '{$this->dynamic_query}', assign_to_id = '{$this->assign_to_id}', " .
 901                                 "modified_by = '{$current_user->id}' WHERE id = '{$this->id}'";
 902                 }
 903
 904                 $this->db->query($q, true);
 905
 906                 return true;
 907         }
 908
 909         /**
 910          * Add subscriptions to this group folder.
 911          *
 912          */
 913         function addSubscriptionsToGroupFolder()
 914         {
 915             global $current_user;
 916
 917             $this->createSubscriptionForUser($current_user->id);
 918
 919         }
 920
 921
 922
 923     /**
 924          * Add subscriptions to this group folder.
 925          *
 926          */
 927         function createSubscriptionForUser($user_id)
 928         {
 929            $guid2 = create_guid();
 930            $query = "INSERT INTO folders_subscriptions VALUES('{$guid2}', '{$this->id}', '{$user_id}')";
 931            $this->db->query($query);
 932         }
 933
 934
 935         function updateFolder($fields) {
 936                 global $current_user;
 937
 938                 $this->dynamic_query = $this->db->quote($this->dynamic_query);
 939                 $id = $fields['record'];
 940                 $name = $fields['name'];
 941                 $parent_folder = $fields['parent_folder'];
 942                 // first do the retrieve
 943                 $this->retrieve($id);
 944                 if ($this->has_child) {
 945                         $childrenArray = array();
 946                         $this->findAllChildren($id, $childrenArray);
 947                         if (in_array($parent_folder, $childrenArray)) {
 948                                 return array('status' => "failed", 'message' => "Can not add this folder to its children");
 949                         }
 950                 }
 951                 // update has_child to 0 for this parent folder if this is the only child it has
 952                 $q1 = "select count(*) count from folders where deleted = 0 AND parent_folder = '{$this->parent_folder}'";
 953                 $r1 = $this->db->query($q1);
 954                 $a1 = $this->db->fetchByAssoc($r1);
 955                 if ($a1['count'] == 1) {
 956                         $q1 = "UPDATE folders SET has_child = 0 WHERE id = '{$this->parent_folder}'";
 957                         $r1 = $this->db->query($q1);
 958                 } // if
 959
 960
 961                 $this->name = $name;
 962                 $this->parent_folder = $parent_folder;
 963                 $q2 = "UPDATE folders SET name = '{$this->name}', parent_folder = '{$this->parent_folder}',                     dynamic_query = '{$this->dynamic_query}', " .
 964                         "modified_by = '{$current_user->id}' WHERE id = '{$this->id}'";
 965                 $r2 = $this->db->query($q2);
 966                 if (!empty($this->parent_folder)) {
 967                         $q3 = "UPDATE folders SET has_child = 1 WHERE id = '{$this->parent_folder}'";
 968                         $r3 = $this->db->query($q3);
 969                 } // if
 970                 return array('status' => "done");
 971
 972         } // fn
 973
 974         function findAllChildren($folderId, &$childrenArray) {
 975                 $q = "SELECT * FROM folders WHERE id = '{$folderId}'";
 976                 $r = $this->db->query($q);
 977                 $a = $this->db->fetchByAssoc($r);
 978
 979                 if($a['has_child'] == 1) {
 980                         $q2 = "SELECT id FROM folders WHERE deleted = 0 AND parent_folder = '{$folderId}'";
 981                         $r2 = $this->db->query($q2);
 982
 983                         while($a2 = $this->db->fetchByAssoc($r2)) {
 984                                 $childrenArray[] = $a2['id'];
 985                                 $this->findAllChildren($a2['id'], $childrenArray);
 986                         } // while
 987                 } // if
 988
 989         } // fn
 990
 991         /**
 992          * Retrieves and populates object
 993          * @param string $id ID of folder
 994          * @return bool True on success
 995          */
 996         function retrieve($id) {
 997                 $q = "SELECT * FROM folders WHERE id = '{$id}' AND deleted = 0";
 998                 $r = $this->db->query($q);
 999                 $a = $this->db->fetchByAssoc($r);
1000
1001                 if(!empty($a)) {
1002                         foreach($a as $k => $v) {
1003                                 if($k == 'dynamic_query') {
1004                                         $v = from_html($v);
1005                                 }
1006                                 $this->$k = $v;
1007                         }
1008                         return true;
1009                 }
1010
1011                 return false;
1012         }
1013 } // end class def