/**
* 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;
}
