/** * Function returns the Calendar Events for the module * @param <String> $mode - upcoming/overdue mode * @param <Vtiger_Paging_Model> $pagingModel - $pagingModel * @param <String> $user - all/userid * @param <String> $recordId - record id * @return <Array> */ function getCalendarActivities($mode, $pagingModel, $user, $recordId = false) { $currentUser = Users_Record_Model::getCurrentUserModel(); $db = PearDatabase::getInstance(); if (!$user) { $user = $currentUser->getId(); } $orderBy = $pagingModel->getForSql('orderby'); $sortOrder = $pagingModel->getForSql('sortorder'); if (empty($sortOrder) || !in_array($sortOrder, ['asc', 'desc'])) { $sortOrder = 'ASC'; } if (empty($orderBy)) { $orderBy = "date_start {$sortOrder}, time_start {$sortOrder}"; } else { $orderBy .= ' ' . $sortOrder; } $nowInUserFormat = Vtiger_Datetime_UIType::getDisplayDateTimeValue(date('Y-m-d H:i:s')); $nowInDBFormat = Vtiger_Datetime_UIType::getDBDateTimeValue($nowInUserFormat); list($currentDate, $currentTime) = explode(' ', $nowInDBFormat); $instance = CRMEntity::getInstance('Calendar'); $UserAccessConditions = $instance->getUserAccessConditionsQuerySR('Calendar'); $params = []; $query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype, vtiger_activity.*\n\t\t\tFROM vtiger_activity\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\tWHERE vtiger_crmentity.deleted=0"; $query .= $UserAccessConditions; if ($mode === 'upcoming') { $query .= " AND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))\n\t\t\tAND (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held')) \n\t\t\tAND CASE WHEN vtiger_activity.activitytype='Task' THEN due_date >= '{$currentDate}' ELSE CONCAT(due_date,' ',time_end) >= '{$nowInDBFormat}' END"; } elseif ($mode === 'overdue') { $query .= " AND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))\n\t\t\tAND (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held')) \n\t\t\tAND CASE WHEN vtiger_activity.activitytype='Task' THEN due_date < '{$currentDate}' ELSE CONCAT(due_date,' ',time_end) < '{$nowInDBFormat}' END"; } elseif ($mode === 'assigned_upcoming') { $query .= " AND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))\n\t\t\tAND (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held')) \n\t\t\tAND CASE WHEN vtiger_activity.activitytype='Task' THEN due_date >= '{$currentDate}' ELSE CONCAT(due_date,' ',time_end) >= '{$nowInDBFormat}' END AND vtiger_crmentity.smcreatorid = ?"; $params[] = $currentUser->getId(); } elseif ($mode === 'assigned_over') { $query .= " AND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))\n\t\t\tAND (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus NOT IN ('Held')) \n\t\t\tAND CASE WHEN vtiger_activity.activitytype='Task' THEN due_date < '{$currentDate}' ELSE CONCAT(due_date,' ',time_end) < '{$nowInDBFormat}' END AND vtiger_crmentity.smcreatorid = ?"; $params[] = $currentUser->getId(); } $accessibleUsers = $currentUser->getAccessibleUsers(); $accessibleGroups = $currentUser->getAccessibleGroups(); if ($user != 'all' && $user != '' && (array_key_exists($user, $accessibleUsers) || array_key_exists($user, $accessibleGroups))) { $query .= ' AND vtiger_crmentity.smownerid = ?'; $params[] = $user; } $query .= ' ORDER BY ' . $orderBy . ' LIMIT ?, ?'; $params[] = $pagingModel->getStartIndex(); $params[] = $pagingModel->getPageLimit() + 1; $result = $db->pquery($query, $params); $numOfRows = $db->num_rows($result); $activities = array(); for ($i = 0; $i < $numOfRows; $i++) { $row = $db->query_result_rowdata($result, $i); $model = Vtiger_Record_Model::getCleanInstance('Calendar'); $model->setData($row); if ($row['activitytype'] == 'Task') { $due_date = $row['due_date']; $dayEndTime = '23:59:59'; $endInUserFormat = Vtiger_Datetime_UIType::getDisplayDateTimeValue($due_date . " " . $dayEndTime); $EndDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($endInUserFormat); $dueDateTimeInDbFormat = explode(' ', $EndDateTime); $dueTimeInDbFormat = $dueDateTimeInDbFormat[1]; $model->set('time_end', $dueTimeInDbFormat); } $model->setId($row['crmid']); if ($row['parent_id']) { if (isRecordExists($row['parent_id'])) { $record = Vtiger_Record_Model::getInstanceById($row['parent_id']); if ($record->getModuleName() == 'Accounts') { $model->set('contractor', $record); } else { if ($record->getModuleName() == 'Potentials') { if (isRecordExists($record->get('related_to'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('related_to')); $model->set('contractor', $recordContractor); } } else { if ($record->getModuleName() == 'Project') { if (isRecordExists($record->get('linktoaccountscontacts'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('linktoaccountscontacts')); $model->set('contractor', $recordContractor); } } else { if ($record->getModuleName() == 'ServiceContracts') { if (isRecordExists($record->get('sc_realted_to'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('sc_realted_to')); $model->set('contractor', $recordContractor); } } else { if ($record->getModuleName() == 'HelpDesk') { if (isRecordExists($record->get('parent_id'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('parent_id')); $model->set('contractor', $recordContractor); } } } } } } } } $contactsA = getActivityRelatedContacts($row['activityid']); if (count($contactsA)) { foreach ($contactsA as $j => $rcA2) { $contactsA[$j] = '<a href="index.php?module=Contacts&view=Detail&record=' . $j . '">' . $rcA2 . '</a>'; $model->set('contact_id', $contactsA); } } $activities[] = $model; } $pagingModel->calculatePageRange($activities); if ($numOfRows > $pagingModel->getPageLimit()) { array_pop($activities); $pagingModel->set('nextPageExists', true); } else { $pagingModel->set('nextPageExists', false); } return $activities; }
/** * Function returns the Calendar Events for the module * @param <String> $mode - upcoming/overdue mode * @param <Vtiger_Paging_Model> $pagingModel - $pagingModel * @param <String> $user - all/userid * @param <String> $recordId - record id * @return <Array> */ function getCalendarActivities($mode, $pagingModel, $user, $recordId = false, $paramsMore = []) { $currentUser = Users_Record_Model::getCurrentUserModel(); $db = PearDatabase::getInstance(); if (!$user) { $user = $currentUser->getId(); } $orderBy = $pagingModel->getForSql('orderby'); $sortOrder = $pagingModel->getForSql('sortorder'); if (empty($sortOrder) || !in_array(strtolower($sortOrder), ['asc', 'desc'])) { $sortOrder = 'ASC'; } if (empty($orderBy)) { $orderBy = "due_date {$sortOrder}, time_end {$sortOrder}"; } else { $orderBy .= ' ' . $sortOrder; } $stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel(); $nowInUserFormat = Vtiger_Datetime_UIType::getDisplayDateTimeValue(date('Y-m-d H:i:s')); $nowInDBFormat = Vtiger_Datetime_UIType::getDBDateTimeValue($nowInUserFormat); $instance = CRMEntity::getInstance('Calendar'); $userAccessConditions = $instance->getUserAccessConditionsQuerySR('Calendar'); $params = []; $query = 'SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype, vtiger_activity.* FROM vtiger_activity INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid WHERE vtiger_crmentity.deleted=0 '; $query .= $userAccessConditions; if ($mode === 'upcoming') { $query .= "AND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status IN (?))"; array_push($params, $paramsMore['status']); } elseif ($mode === 'overdue') { $overdueActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('overdue'); $query .= "AND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status IN (?))"; //AND CONCAT(due_date,' ',time_end) < '$nowInDBFormat'"; array_push($params, $overdueActivityLabels); } elseif ($mode === 'assigned_upcoming') { $stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current'); $query .= "AND (vtiger_activity.status is NULL OR vtiger_activity.status IN (" . generateQuestionMarks($stateActivityLabels) . ")) AND vtiger_crmentity.smcreatorid = ?"; $params = array_merge($params, $stateActivityLabels); $params[] = $currentUser->getId(); } elseif ($mode === 'assigned_over') { $overdueActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('overdue'); $query .= "AND (vtiger_activity.status is NULL OR vtiger_activity.status IN (?)) AND vtiger_crmentity.smcreatorid = ?"; array_push($params, $overdueActivityLabels, $currentUser->getId()); } elseif ($mode === 'createdByMeButNotMine') { if ($paramsMore['activitesType'] != 'upcoming') { $status = [$stateActivityLabels['not_started'], $stateActivityLabels['in_realization']]; } else { $status = [$stateActivityLabels['overdue']]; } $query .= "AND (vtiger_activity.status is NULL OR vtiger_activity.status IN (" . generateQuestionMarks($status) . ")) AND vtiger_crmentity.smcreatorid = ? AND vtiger_crmentity.smownerid NOT IN (?) "; $params = array_merge($params, $status); array_push($params, $currentUser->getId(), $currentUser->getId()); } $accessibleUsers = $currentUser->getAccessibleUsers(); $accessibleGroups = $currentUser->getAccessibleGroups(); if ($user != 'all' && $user != '' && (array_key_exists($user, $accessibleUsers) || array_key_exists($user, $accessibleGroups))) { $query .= ' AND vtiger_crmentity.smownerid = ?'; $params[] = $user; } $query .= ' ORDER BY ' . $orderBy . ' LIMIT ?, ?'; $params[] = $pagingModel->getStartIndex(); $params[] = $pagingModel->getPageLimit() + 1; $result = $db->pquery($query, $params); $activities = []; while ($row = $db->fetch_array($result)) { $model = Vtiger_Record_Model::getCleanInstance('Calendar'); $model->setData($row); $model->setId($row['crmid']); if ($row['parent_id']) { if (isRecordExists($row['parent_id'])) { $record = Vtiger_Record_Model::getInstanceById($row['parent_id']); if ($record->getModuleName() == 'Accounts') { $model->set('contractor', $record); } else { if ($record->getModuleName() == 'Potentials') { if (isRecordExists($record->get('related_to'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('related_to')); $model->set('contractor', $recordContractor); } } else { if ($record->getModuleName() == 'Project') { if (isRecordExists($record->get('linktoaccountscontacts'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('linktoaccountscontacts')); $model->set('contractor', $recordContractor); } } else { if ($record->getModuleName() == 'ServiceContracts') { if (isRecordExists($record->get('sc_realted_to'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('sc_realted_to')); $model->set('contractor', $recordContractor); } } else { if ($record->getModuleName() == 'HelpDesk') { if (isRecordExists($record->get('parent_id'))) { $recordContractor = Vtiger_Record_Model::getInstanceById($record->get('parent_id')); $model->set('contractor', $recordContractor); } } } } } } } } $contactsA = getActivityRelatedContacts($row['activityid']); if (count($contactsA)) { foreach ($contactsA as $j => $rcA2) { $contactsA[$j] = '<a href="index.php?module=Contacts&view=Detail&record=' . $j . '">' . $rcA2 . '</a>'; $model->set('contact_id', $contactsA); } } $activities[] = $model; } $pagingModel->calculatePageRange($activities); if ($result->rowCount() > $pagingModel->getPageLimit()) { array_pop($activities); $pagingModel->set('nextPageExists', true); } else { $pagingModel->set('nextPageExists', false); } return $activities; }