[Summary view] [Print] [Text view]

   1  <?php
   2  /*+***********************************************************************************
   3   * The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4   * ("License"); You may not use this file except in compliance with the License
   5   * The Original Code is:  vtiger CRM Open Source
   6   * The Initial Developer of the Original Code is vtiger.
   7   * Portions created by vtiger are Copyright (C) vtiger.
   8   * All Rights Reserved.
   9   *************************************************************************************/
  10  
  11  vimport ('~~/include/Webservices/Query.php');
  12  
  13  class Calendar_Feed_Action extends Vtiger_BasicAjax_Action {
  14  
  15  	public function process(Vtiger_Request $request) {
  16          try {
  17              $result = array();
  18  
  19              $start = $request->get('start');
  20              $end   = $request->get('end');
  21              $type = $request->get('type');
  22              $userid = $request->get('userid');
  23              $color = $request->get('color');
  24              $textColor = $request->get('textColor');
  25              
  26              switch ($type) {
  27                  case 'Events': $this->pullEvents($start, $end, $result,$userid,$color,$textColor); break;
  28                  case 'Calendar': $this->pullTasks($start, $end, $result,$color,$textColor); break;
  29                  case 'Potentials': $this->pullPotentials($start, $end, $result, $color, $textColor); break;
  30                  case 'Contacts':
  31                              if($request->get('fieldname') == 'support_end_date') {
  32                                  $this->pullContactsBySupportEndDate($start, $end, $result, $color, $textColor);
  33                              }else{
  34                                  $this->pullContactsByBirthday($start, $end, $result, $color, $textColor);
  35                              }
  36                              break;
  37  
  38                  case 'Invoice': $this->pullInvoice($start, $end, $result, $color, $textColor); break;
  39                  case 'MultipleEvents' : $this->pullMultipleEvents($start,$end, $result,$request->get('mapping'));break;
  40                  case 'Project': $this->pullProjects($start, $end, $result, $color, $textColor); break;
  41                  case 'ProjectTask': $this->pullProjectTasks($start, $end, $result, $color, $textColor); break;
  42              }
  43              echo json_encode($result);
  44          } catch (Exception $ex) {
  45              echo $ex->getMessage();
  46          }
  47      }
  48      
  49      protected function getGroupsIdsForUsers($userId) {
  50          vimport('~~/include/utils/GetUserGroups.php');
  51          
  52          $userGroupInstance = new GetUserGroups();
  53          $userGroupInstance->getAllUserGroups($userId);
  54          return $userGroupInstance->user_groups;
  55      }
  56  
  57  	protected function queryForRecords($query, $onlymine=true) {
  58          $user = Users_Record_Model::getCurrentUserModel();
  59          if ($onlymine) {
  60              $groupIds = $this->getGroupsIdsForUsers($user->getId());
  61              $groupWsIds = array();
  62              foreach($groupIds as $groupId) {
  63                  $groupWsIds[] = vtws_getWebserviceEntityId('Groups', $groupId);
  64              }
  65              $userwsid = vtws_getWebserviceEntityId('Users', $user->getId());
  66              $userAndGroupIds = array_merge(array($userwsid),$groupWsIds);
  67              $query .= " AND assigned_user_id IN ('".implode("','",$userAndGroupIds)."')";
  68          }
  69          // TODO take care of pulling 100+ records
  70          return vtws_query($query.';', $user);
  71      }
  72  
  73  	protected function pullEvents($start, $end, &$result, $userid = false,$color = null,$textColor = 'white') {
  74          $dbStartDateOject = DateTimeField::convertToDBTimeZone($start);
  75          $dbStartDateTime = $dbStartDateOject->format('Y-m-d H:i:s');
  76          $dbStartDateTimeComponents = explode(' ', $dbStartDateTime);
  77          $dbStartDate = $dbStartDateTimeComponents[0];
  78          
  79          $dbEndDateObject = DateTimeField::convertToDBTimeZone($end);
  80          $dbEndDateTime = $dbEndDateObject->format('Y-m-d H:i:s');
  81          
  82          $currentUser = Users_Record_Model::getCurrentUserModel();
  83          $db = PearDatabase::getInstance();
  84  
  85          $moduleModel = Vtiger_Module_Model::getInstance('Events');
  86          if($userid){
  87              $focus = new Users();
  88              $focus->id = $userid;
  89              $focus->retrieve_entity_info($userid, 'Users');
  90              $user = Users_Record_Model::getInstanceFromUserObject($focus);
  91              $userName = $user->getName();
  92              $queryGenerator = new QueryGenerator($moduleModel->get('name'), $user);
  93          }else{
  94              $queryGenerator = new QueryGenerator($moduleModel->get('name'), $currentUser);
  95          }
  96  
  97          $queryGenerator->setFields(array('subject', 'eventstatus', 'visibility','date_start','time_start','due_date','time_end','assigned_user_id','id','activitytype'));
  98          $query = $queryGenerator->getQuery();
  99  
 100          $query.= " AND vtiger_activity.activitytype NOT IN ('Emails','Task') AND ";
 101          $hideCompleted = $currentUser->get('hidecompletedevents');
 102          if($hideCompleted)
 103              $query.= "vtiger_activity.eventstatus != 'HELD' AND ";
 104          $query.= " ((concat(date_start, '', time_start)  >= '$dbStartDateTime' AND concat(due_date, '', time_end) < '$dbEndDateTime') OR ( due_date >= '$dbStartDate'))";
 105          
 106          $params = array();
 107          if(empty($userid)){
 108              $eventUserId  = $currentUser->getId();
 109          }else{
 110              $eventUserId = $userid;
 111          }
 112          $params = array_merge(array($eventUserId), $this->getGroupsIdsForUsers($eventUserId));
 113          $query.= " AND vtiger_crmentity.smownerid IN (".  generateQuestionMarks($params).")";
 114          
 115          $queryResult = $db->pquery($query, $params);
 116  
 117          while($record = $db->fetchByAssoc($queryResult)){
 118              $item = array();
 119              $crmid = $record['activityid'];
 120              $visibility = $record['visibility'];
 121              $activitytype = $record['activitytype'];
 122              $status = $record['eventstatus'];
 123              $item['id'] = $crmid;
 124              $item['visibility'] = $visibility;
 125              $item['activitytype'] = $activitytype;
 126              $item['status'] = $status;
 127              if(!$currentUser->isAdminUser() && $visibility == 'Private' && $userid && $userid != $currentUser->getId()) {
 128                  $item['title'] = decode_html($userName).' - '.decode_html(vtranslate('Busy','Events')).'*';
 129                  $item['url']   = '';
 130              } else {
 131                  $item['title'] = decode_html($record['subject']) . ' - (' . decode_html(vtranslate($record['eventstatus'],'Calendar')) . ')';
 132                  $item['url']   = sprintf('index.php?module=Calendar&view=Detail&record=%s', $crmid);
 133              }
 134  
 135              $dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
 136              $userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
 137              $dateTimeComponents = explode(' ',$userDateTimeString);
 138              $dateComponent = $dateTimeComponents[0];
 139              //Conveting the date format in to Y-m-d . since full calendar expects in the same format
 140              $dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
 141              $item['start'] = $dataBaseDateFormatedString.' '. $dateTimeComponents[1];
 142  
 143              $dateTimeFieldInstance = new DateTimeField($record['due_date'] . ' ' . $record['time_end']);
 144              $userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
 145              $dateTimeComponents = explode(' ',$userDateTimeString);
 146              $dateComponent = $dateTimeComponents[0];
 147              //Conveting the date format in to Y-m-d . since full calendar expects in the same format
 148              $dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
 149              $item['end']   =  $dataBaseDateFormatedString.' '. $dateTimeComponents[1];
 150  
 151  
 152              $item['className'] = $cssClass;
 153              $item['allDay'] = false;
 154              $item['color'] = $color;
 155              $item['textColor'] = $textColor;
 156              $item['module'] = $moduleModel->getName();
 157              $result[] = $item;
 158              }
 159          }
 160  
 161  	protected function pullMultipleEvents($start, $end, &$result, $data) {
 162  
 163          foreach ($data as $id=>$backgroundColorAndTextColor) {
 164              $userEvents = array();
 165              $colorComponents = explode(',',$backgroundColorAndTextColor);
 166              $this->pullEvents($start, $end, $userEvents ,$id, $colorComponents[0], $colorComponents[1]);
 167              $result[$id] = $userEvents;
 168          }
 169      }
 170  
 171  	protected function pullTasks($start, $end, &$result, $color = null,$textColor = 'white') {
 172          $user = Users_Record_Model::getCurrentUserModel();
 173          $db = PearDatabase::getInstance();
 174  
 175          $moduleModel = Vtiger_Module_Model::getInstance('Calendar');
 176          $userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
 177          $queryGenerator = new QueryGenerator($moduleModel->get('name'), $user);
 178  
 179          $queryGenerator->setFields(array('activityid','subject', 'taskstatus','activitytype', 'date_start','time_start','due_date','time_end','id'));
 180          $query = $queryGenerator->getQuery();
 181  
 182          $query.= " AND vtiger_activity.activitytype = 'Task' AND ";
 183          $currentUser = Users_Record_Model::getCurrentUserModel();
 184          $hideCompleted = $currentUser->get('hidecompletedevents');
 185          if($hideCompleted)
 186              $query.= "vtiger_activity.status != 'Completed' AND ";
 187          $query.= " ((date_start >= '$start' AND due_date < '$end') OR ( due_date >= '$start'))";
 188          $params = $userAndGroupIds;
 189          $query.= " AND vtiger_crmentity.smownerid IN (".generateQuestionMarks($params).")";
 190          
 191          $queryResult = $db->pquery($query,$params);
 192          
 193          while($record = $db->fetchByAssoc($queryResult)){
 194              $item = array();
 195              $crmid = $record['activityid'];
 196              $item['title'] = decode_html($record['subject']) . ' - (' . decode_html(vtranslate($record['status'],'Calendar')) . ')';
 197              $item['status'] = $record['status'];
 198              $item['activitytype'] = $record['activitytype'];
 199              $item['id'] = $crmid;
 200              $dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
 201              $userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
 202              $dateTimeComponents = explode(' ',$userDateTimeString);
 203              $dateComponent = $dateTimeComponents[0];
 204              //Conveting the date format in to Y-m-d . since full calendar expects in the same format
 205              $dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $user->get('date_format'));
 206              $item['start'] = $dataBaseDateFormatedString.' '. $dateTimeComponents[1];
 207  
 208              $item['end']   = $record['due_date'];
 209              $item['url']   = sprintf('index.php?module=Calendar&view=Detail&record=%s', $crmid);
 210              $item['color'] = $color;
 211              $item['textColor'] = $textColor;
 212              $item['module'] = $moduleModel->getName();
 213              $result[] = $item;
 214          }
 215      }
 216  
 217  	protected function pullPotentials($start, $end, &$result, $color = null,$textColor = 'white') {
 218          $query = "SELECT potentialname,closingdate FROM Potentials";
 219          $query.= " WHERE closingdate >= '$start' AND closingdate <= '$end'";
 220          $records = $this->queryForRecords($query);
 221          foreach ($records as $record) {
 222              $item = array();
 223              list ($modid, $crmid) = vtws_getIdComponents($record['id']);
 224              $item['id'] = $crmid;
 225              $item['title'] = decode_html($record['potentialname']);
 226              $item['start'] = $record['closingdate'];
 227              $item['url']   = sprintf('index.php?module=Potentials&view=Detail&record=%s', $crmid);
 228              $item['color'] = $color;
 229              $item['textColor'] = $textColor;
 230              $result[] = $item;
 231          }
 232      }
 233  
 234  	protected function pullContacts($start, $end, &$result, $color = null,$textColor = 'white') {
 235          $this->pullContactsBySupportEndDate($start, $end, $result, $color, $textColor);
 236          $this->pullContactsByBirthday($start, $end, $result, $color, $textColor);
 237      }
 238  
 239  	protected function pullContactsBySupportEndDate($start, $end, &$result, $color = null,$textColor = 'white') {
 240          $query = "SELECT firstname,lastname,support_end_date FROM Contacts";
 241          $query.= " WHERE support_end_date >= '$start' AND support_end_date <= '$end'";
 242          $records = $this->queryForRecords($query);
 243          foreach ($records as $record) {
 244              $item = array();
 245              list ($modid, $crmid) = vtws_getIdComponents($record['id']);
 246              $item['id'] = $crmid;
 247              $item['title'] = decode_html(trim($record['firstname'] . ' ' . $record['lastname']));
 248              $item['start'] = $record['support_end_date'];
 249              $item['url']   = sprintf('index.php?module=Contacts&view=Detail&record=%s', $crmid);
 250              $item['color'] = $color;
 251              $item['textColor'] = $textColor;
 252              $result[] = $item;
 253          }
 254      }
 255  
 256  	protected  function pullContactsByBirthday($start, $end, &$result, $color = null,$textColor = 'white') {
 257          $db = PearDatabase::getInstance();
 258          $user = Users_Record_Model::getCurrentUserModel();
 259          $startDateComponents = split('-', $start);
 260          $endDateComponents = split('-', $end);
 261          
 262          $userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
 263          $params = $userAndGroupIds;
 264          
 265          $year = $startDateComponents[0];
 266  
 267          $query = "SELECT firstname,lastname,birthday,crmid FROM vtiger_contactdetails";
 268          $query.= " INNER JOIN vtiger_contactsubdetails ON vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid";
 269          $query.= " INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid";
 270          $query.= " WHERE vtiger_crmentity.deleted=0 AND smownerid IN (".  generateQuestionMarks($params) .") AND";
 271          $query.= " ((CONCAT('$year-', date_format(birthday,'%m-%d')) >= '$start'
 272                          AND CONCAT('$year-', date_format(birthday,'%m-%d')) <= '$end')";
 273  
 274          
 275          $endDateYear = $endDateComponents[0];
 276          if ($year !== $endDateYear) {
 277              $query .= " OR
 278                          (CONCAT('$endDateYear-', date_format(birthday,'%m-%d')) >= '$start'
 279                              AND CONCAT('$endDateYear-', date_format(birthday,'%m-%d')) <= '$end')";
 280          }
 281          $query .= ")";
 282  
 283          $queryResult = $db->pquery($query, $params);
 284  
 285          while($record = $db->fetchByAssoc($queryResult)){
 286              $item = array();
 287              $crmid = $record['crmid'];
 288              $recordDateTime = new DateTime($record['birthday']);
 289  
 290              $calendarYear = $year;
 291              if($recordDateTime->format('m') < $startDateComponents[1]) {
 292                  $calendarYear = $endDateYear;
 293              }
 294              $recordDateTime->setDate($calendarYear, $recordDateTime->format('m'), $recordDateTime->format('d'));
 295              $item['id'] = $crmid;
 296              $item['title'] = decode_html(trim($record['firstname'] . ' ' . $record['lastname']));
 297              $item['start'] = $recordDateTime->format('Y-m-d');
 298              $item['url']   = sprintf('index.php?module=Contacts&view=Detail&record=%s', $crmid);
 299              $item['color'] = $color;
 300              $item['textColor'] = $textColor;
 301              $result[] = $item;
 302          }
 303      }
 304  
 305  	protected function pullInvoice($start, $end, &$result, $color = null,$textColor = 'white') {
 306          $query = "SELECT subject,duedate FROM Invoice";
 307          $query.= " WHERE duedate >= '$start' AND duedate <= '$end'";
 308          $records = $this->queryForRecords($query);
 309          foreach ($records as $record) {
 310              $item = array();
 311              list ($modid, $crmid) = vtws_getIdComponents($record['id']);
 312              $item['id'] = $crmid;
 313              $item['title'] = decode_html($record['subject']);
 314              $item['start'] = $record['duedate'];
 315              $item['url']   = sprintf('index.php?module=Invoice&view=Detail&record=%s', $crmid);
 316              $item['color'] = $color;
 317              $item['textColor'] = $textColor;
 318              $result[] = $item;
 319          }
 320      }
 321  
 322      /**
 323       * Function to pull all the current user projects
 324       * @param type $startdate
 325       * @param type $actualenddate
 326       * @param type $result
 327       * @param type $color
 328       * @param type $textColor
 329       */
 330  	protected function pullProjects($start, $end, &$result, $color = null,$textColor = 'white') {
 331          $db = PearDatabase::getInstance();
 332          $user = Users_Record_Model::getCurrentUserModel();
 333          $userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
 334          $params = $userAndGroupIds;
 335          
 336          $query = "SELECT projectname, startdate, targetenddate, crmid FROM vtiger_project";
 337          $query.= " INNER JOIN vtiger_crmentity ON vtiger_project.projectid = vtiger_crmentity.crmid";
 338          $query.= " WHERE vtiger_crmentity.deleted=0 AND smownerid IN (". generateQuestionMarks($params) .") AND ";
 339          $query.= " ((startdate >= '$start' AND targetenddate < '$end') OR ( targetenddate >= '$start'))";
 340          $queryResult = $db->pquery($query, $params);
 341  
 342          while($record = $db->fetchByAssoc($queryResult)){
 343              $item = array();
 344              $crmid = $record['crmid'];
 345              $item['id'] = $crmid;
 346              $item['title'] = decode_html($record['projectname']);
 347              $item['start'] = $record['startdate'];
 348              $item['end'] = $record['targetenddate'];
 349              $item['url']   = sprintf('index.php?module=Project&view=Detail&record=%s', $crmid);
 350              $item['color'] = $color;
 351              $item['textColor'] = $textColor;
 352              $result[] = $item;
 353          }
 354      }
 355  
 356      /**
 357       * Function to pull all the current user porjecttasks
 358       * @param type $startdate
 359       * @param type $enddate
 360       * @param type $result
 361       * @param type $color
 362       * @param type $textColor
 363       */
 364  	protected function pullProjectTasks($start, $end, &$result, $color = null,$textColor = 'white') {
 365          $db = PearDatabase::getInstance();
 366          $user = Users_Record_Model::getCurrentUserModel();
 367          $userAndGroupIds = array_merge(array($user->getId()),$this->getGroupsIdsForUsers($user->getId()));
 368          $params = $userAndGroupIds;
 369          
 370          $query = "SELECT projecttaskname, startdate, enddate, crmid FROM vtiger_projecttask";
 371          $query.= " INNER JOIN vtiger_crmentity ON vtiger_projecttask.projecttaskid = vtiger_crmentity.crmid";
 372          $query.= " WHERE vtiger_crmentity.deleted=0 AND smownerid IN (". generateQuestionMarks($params) .") AND ";
 373          $query.= " ((startdate >= '$start' AND enddate < '$end') OR ( enddate >= '$start'))";
 374          $queryResult = $db->pquery($query, $params);
 375  
 376          while($record = $db->fetchByAssoc($queryResult)){
 377              $item = array();
 378              $crmid = $record['crmid'];
 379              $item['id'] = $crmid;
 380              $item['title'] = decode_html($record['projecttaskname']);
 381              $item['start'] = $record['startdate'];
 382              $item['end'] = $record['enddate'];
 383              $item['url']   = sprintf('index.php?module=ProjectTask&view=Detail&record=%s', $crmid);
 384              $item['color'] = $color;
 385              $item['textColor'] = $textColor;
 386              $result[] = $item;
 387          }
 388      }
 389  
 390  }