public function process(Vtiger_Request $request)
{
$currentUser = Users_Record_Model::getCurrentUserModel();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel();
$page = $request->get('page');
$linkId = $request->get('linkid');
$sortOrder = $request->get('sortorder');
$orderBy = $request->get('orderby');
$params = ['status' => $stateActivityLabels['in_realization']];
if ($request->get('switchParams')) {
$params = ['status' => $request->get('switchParams')];
}
$widget = Vtiger_Widget_Model::getInstance($linkId, $currentUser->getId());
if (!$request->has('owner'))
$owner = Settings_WidgetsManagement_Module_Model::getDefaultUserId($widget);
else
$owner = $request->get('owner');
$pagingModel = new Vtiger_Paging_Model();
$pagingModel->set('page', $page);
$pagingModel->set('limit', (int) $widget->get('limit'));
$pagingModel->set('orderby', $orderBy);
$pagingModel->set('sortorder', $sortOrder);
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$calendarActivities = ($owner === false) ? array() : $moduleModel->getCalendarActivities('upcoming', $pagingModel, $owner, false, $params);
$switchLabels = [];
$switchLabels[] = ['label' => vtranslate($stateActivityLabels['in_realization'], 'Calendar'), 'name' => $stateActivityLabels['in_realization']];
$switchLabels[] = ['label' => vtranslate($stateActivityLabels['not_started'], 'Calendar'), 'name' => $stateActivityLabels['not_started']];
$msgLabel = 'LBL_NO_SCHEDULED_ACTIVITIES';
if ($params['status'] == $stateActivityLabels['in_realization']) {
$msgLabel = 'LBL_NO_CURRENT_ACTIVITIES';
}
$viewer->assign('WIDGET', $widget);
$viewer->assign('MODULE_NAME', $moduleName);
$viewer->assign('ACTIVITIES', $calendarActivities);
$viewer->assign('PAGING', $pagingModel);
$viewer->assign('CURRENTUSER', $currentUser);
$title_max_length = vglobal('title_max_length');
$href_max_length = vglobal('href_max_length');
$viewer->assign('NAMELENGHT', $title_max_length);
$viewer->assign('OWNER', $owner);
$viewer->assign('HREFNAMELENGHT', $href_max_length);
$viewer->assign('NODATAMSGLABLE', $msgLabel);
$viewer->assign('SWITCH', $switchLabels);
$viewer->assign('LISTVIEWLINKS', true);
$content = $request->get('content');
if (!empty($content)) {
$viewer->view('dashboards/CalendarActivitiesContents.tpl', $moduleName);
} else {
$viewer->view('dashboards/CalendarActivities.tpl', $moduleName);
}
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_activities') {
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility,\n\t\t\t\t\t\tvtiger_activity.status AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.process = " . $recordId;
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
if ($time == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$instance = CRMEntity::getInstance($relatedModuleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId);
if ($securityParameter != '') {
$query .= $securityParameter;
}
} elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
public function getQuery()
{
$db = PearDatabase::getInstance();
$currentUser = Users_Record_Model::getCurrentUserModel();
$query = "SELECT vtiger_activity.activityid as act_id,vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype,\n\t\tvtiger_activity.*, relcrm.setype AS linkmod, relcrm.label AS linklabel, procrm.label AS processlabel, procrm.setype AS processmod\n\t\tFROM vtiger_activity\n\t\tLEFT JOIN vtiger_activitycf\n\t\t\tON vtiger_activitycf.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_crmentity\n\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_crmentity relcrm\n\t\t\tON relcrm.crmid = vtiger_activity.link\n\t\tLEFT JOIN vtiger_crmentity procrm\n\t\t\tON procrm.crmid = vtiger_activity.process\n\t\tWHERE vtiger_crmentity.deleted = 0 AND activitytype != 'Emails' ";
$instance = CRMEntity::getInstance($this->getModuleName());
$securityParameter = $instance->getUserAccessConditionsQuerySR($this->getModuleName(), $currentUser);
if ($securityParameter != '') {
$query .= $securityParameter;
}
$params = [];
if ($this->get('start') && $this->get('end')) {
$dbStartDateOject = DateTimeField::convertToDBTimeZone($this->get('start'));
$dbStartDateTime = $dbStartDateOject->format('Y-m-d H:i:s');
$dbStartDate = $dbStartDateOject->format('Y-m-d');
$dbEndDateObject = DateTimeField::convertToDBTimeZone($this->get('end'));
$dbEndDateTime = $dbEndDateObject->format('Y-m-d H:i:s');
$dbEndDate = $dbEndDateObject->format('Y-m-d');
$query .= " AND ( (concat(date_start, ' ', time_start) >= ? AND concat(date_start, ' ', time_start) <= ?) OR (concat(due_date, ' ', time_end) >= ? AND concat(due_date, ' ', time_end) <= ?) OR (date_start < ? AND due_date > ?) ) ";
$params[] = $dbStartDateTime;
$params[] = $dbEndDateTime;
$params[] = $dbStartDateTime;
$params[] = $dbEndDateTime;
$params[] = $dbStartDate;
$params[] = $dbEndDate;
}
if ($this->get('types')) {
$query .= " AND vtiger_activity.activitytype IN ('" . implode("','", $this->get('types')) . "')";
}
if ($this->get('time') == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "')";
}
if ($this->get('time') == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "')";
}
if ($this->get('activitystatus')) {
$query .= " AND vtiger_activity.status IN ('" . $this->get('activitystatus') . "')";
}
if ($this->get('user')) {
if (is_array($this->get('user'))) {
$query .= ' AND vtiger_crmentity.smownerid IN (' . implode(",", $this->get('user')) . ')';
} else {
$query .= ' AND vtiger_crmentity.smownerid IN (' . $this->get('user') . ')';
}
}
$query .= ' ORDER BY date_start,time_start ASC';
return ['query' => $query, 'params' => $params];
}
/**
* Function to get the detail view links (links and widgets)
* @param <array> $linkParams - parameters which will be used to calicaulate the params
* @return <array> - array of link models in the format as below
* array('linktype'=>list of link models);
*/
public function getDetailViewLinks($linkParams)
{
$currentUserModel = Users_Privileges_Model::getCurrentUserPrivilegesModel();
$linkModelList = parent::getDetailViewLinks($linkParams);
$recordModel = $this->getRecord();
$moduleName = $recordModel->getModuleName();
$recordId = $recordModel->getId();
$status = $recordModel->get('activitystatus');
$statusActivity = Calendar_Module_Model::getComponentActivityStateLabel('current');
$lockEdit = Users_Privileges_Model::checkLockEdit($moduleName, $recordId);
if (Users_Privileges_Model::isPermitted($moduleName, 'EditView', $recordId) && $currentUserModel->hasModuleActionPermission($this->getModule()->getId(), 'DetailView') && !$lockEdit && isPermitted($moduleName, 'ActivityComplete', $recordId) == 'yes' && isPermitted($moduleName, 'ActivityCancel', $recordId) == 'yes' && isPermitted($moduleName, 'ActivityPostponed', $recordId) == 'yes' && in_array($status, $statusActivity)) {
$basicActionLink = ['linktype' => 'DETAILVIEW', 'linklabel' => 'LBL_SET_RECORD_STATUS', 'linkurl' => '#', 'linkdata' => ['url' => $recordModel->getActivityStateModalUrl()], 'linkicon' => 'glyphicon glyphicon-ok', 'linkclass' => 'showModal closeCalendarRekord'];
$linkModelList['DETAILVIEW'][] = Vtiger_Link_Model::getInstanceFromValues($basicActionLink);
}
return $linkModelList;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_activities') {
$focus = CRMEntity::getInstance($this->getName());
$focus->id = $recordId;
$entityIds = $focus->getRelatedContactsIds();
$entityIds[] = $recordId;
$entityIds = implode(',', $entityIds);
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, \n\t\t\t\t\t\tvtiger_activity.status AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.link IN (" . $entityIds . ')';
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
if ($time == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$instance = CRMEntity::getInstance($relatedModuleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId);
if ($securityParameter != '') {
$query .= $securityParameter;
}
// There could be more than one contact for an activity.
$query .= ' GROUP BY vtiger_activity.activityid';
} elseif ($functionName === 'get_dependents_list' && $relatedModule->getName() == 'OutsourcedProducts') {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
$query .= " OR potential IN (SELECT potentialid FROM vtiger_potential INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_potential.potentialid WHERE vtiger_crmentity.deleted = 0 AND related_to = '{$recordId}')";
} elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
/**
* 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();
}
$currentActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$nowInUserFormat = Vtiger_Datetime_UIType::getDisplayDateValue(date('Y-m-d H:i:s'));
$nowInDBFormat = Vtiger_Datetime_UIType::getDBDateTimeValue($nowInUserFormat);
list($currentDate, $currentTime) = explode(' ', $nowInDBFormat);
if (in_array($this->getName(), ['Accounts', 'Leads', 'Contacts', 'Vendors', 'OSSEmployees'])) {
$relationField = 'link';
}
if (in_array($this->getName(), ['Campaigns', 'HelpDesk', 'Potentials', 'Project', 'ServiceContracts'])) {
$relationField = 'process';
}
$query = "SELECT vtiger_crmentity.crmid, crmentity2.crmid AS parent_id, vtiger_crmentity.description as description, vtiger_crmentity.smownerid, vtiger_crmentity.smcreatorid, vtiger_crmentity.setype, vtiger_activity.* FROM vtiger_activity\n\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\tINNER JOIN vtiger_crmentity AS crmentity2 ON vtiger_activity." . $relationField . " = crmentity2.crmid AND crmentity2.deleted = 0 AND crmentity2.setype = ?\n\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted=0";
$params = [$this->getName()];
if ($recordId) {
$query .= ' AND vtiger_activity.' . $relationField . ' = ?';
array_push($params, $recordId);
}
if ($mode === 'current') {
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN (" . generateQuestionMarks($currentActivityLabels) . "))";
$params = array_merge($params, $currentActivityLabels);
} elseif ($mode === 'history') {
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status NOT IN (" . generateQuestionMarks($currentActivityLabels) . "))";
$params = array_merge($params, $currentActivityLabels);
} elseif ($mode === 'upcoming') {
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))";
$query .= " AND due_date >= '{$currentDate}'";
} elseif ($mode === 'overdue') {
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails'))\n\t\t\t\t\tAND (vtiger_activity.status is NULL OR vtiger_activity.status NOT IN ('Completed', 'Deferred'))";
$query .= " AND due_date < '{$currentDate}'";
}
if ($user != 'all' && $user != '') {
if ($user === $currentUser->id) {
$query .= " AND vtiger_crmentity.smownerid = ?";
array_push($params, $user);
}
}
$moduleName = 'Calendar';
$instance = CRMEntity::getInstance($moduleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($moduleName, $currentUser, $recordId);
if ($securityParameter != '') {
$query .= $securityParameter;
}
$query .= " ORDER BY date_start, time_start LIMIT " . $pagingModel->getStartIndex() . ", " . ($pagingModel->getPageLimit() + 1);
$result = $db->pquery($query, $params);
$numOfRows = $db->num_rows($result);
$groupsIds = Vtiger_Util_Helper::getGroupsIdsForUsers($currentUser->getId());
$activities = array();
for ($i = 0; $i < $numOfRows; $i++) {
$newRow = $db->query_result_rowdata($result, $i);
$model = Vtiger_Record_Model::getCleanInstance('Calendar');
$ownerId = $newRow['smownerid'];
$currentUser = Users_Record_Model::getCurrentUserModel();
$visibleFields = array('activitytype', 'date_start', 'time_start', 'due_date', 'time_end', 'assigned_user_id', 'visibility', 'smownerid', 'crmid');
$visibility = true;
if (in_array($ownerId, $groupsIds)) {
$visibility = false;
} else {
if ($ownerId == $currentUser->getId()) {
$visibility = false;
}
}
if (!$currentUser->isAdminUser() && $newRow['activitytype'] != 'Task' && $newRow['visibility'] == 'Private' && $ownerId && $visibility) {
foreach ($newRow as $data => $value) {
if (in_array($data, $visibleFields) != -1) {
unset($newRow[$data]);
}
}
$newRow['subject'] = vtranslate('Busy', 'Events') . '*';
}
if ($newRow['activitytype'] == 'Task') {
unset($newRow['visibility']);
}
$sql = "SELECT * FROM vtiger_invitees WHERE activityid = '" . $newRow['crmid'] . "'";
$result_invitees = $db->query($sql);
while ($recordinfo = $db->fetch_array($result_invitees)) {
$newRow['selectedusers'][] = $recordinfo['inviteeid'];
}
$model->setData($newRow);
$model->setId($newRow['crmid']);
$activities[] = $model;
}
$pagingModel->calculatePageRange($activities);
if ($numOfRows > $pagingModel->getPageLimit()) {
array_pop($activities);
$pagingModel->set('nextPageExists', true);
} else {
$pagingModel->set('nextPageExists', false);
}
return $activities;
}
public static function getCalendarState($data = [])
{
if ($data) {
$activityStatus = $data['activitystatus'];
if (in_array($activityStatus, Calendar_Module_Model::getComponentActivityStateLabel('history'))) {
return false;
}
$dueDateTime = $data['due_date'] . ' ' . $data['time_end'];
$startDateTime = $data['date_start'] . ' ' . $data['time_start'];
$dates = ['start' => $startDateTime, 'end' => $dueDateTime, 'current' => null];
foreach ($dates as $key => $date) {
$date = new DateTimeField($date);
$userFormatedString = $date->getDisplayDate();
$timeFormatedString = $date->getDisplayTime();
$dBFomatedDate = DateTimeField::convertToDBFormat($userFormatedString);
$dates[$key] = strtotime($dBFomatedDate . " " . $timeFormatedString);
}
$activityStatusLabels = Calendar_Module_Model::getComponentActivityStateLabel();
$state = $activityStatusLabels['not_started'];
if ($dates['end'] > $dates['current'] && $dates['start'] < $dates['current']) {
$state = $activityStatusLabels['in_realization'];
} elseif ($dates['end'] > $dates['current']) {
$state = $activityStatusLabels['not_started'];
} elseif ($dates['end'] < $dates['current']) {
$state = $activityStatusLabels['overdue'];
}
return $state;
}
return false;
}
<?php
/* {[The file is published on the basis of YetiForce Public License that can be found in the following directory: licenses/License.html]} */
require_once 'include/main/WebUI.php';
$user = new Users();
$current_user = $user->retrieveCurrentUserInfoFromFile("1");
$adb = PearDatabase::getInstance();
$statusActivity = Calendar_Module_Model::getComponentActivityStateLabel();
$query = 'SELECT vtiger_activity.`activityid`, vtiger_activity.`due_date`,vtiger_activity.`time_end`,vtiger_activity.`date_start`,vtiger_activity.`time_start`,vtiger_activity.`state` FROM vtiger_activity INNER JOIN vtiger_crmentity AS crm ON crm.crmid = vtiger_activity.`activityid` WHERE vtiger_activity.`status` IN (?,?) AND crm.`deleted` = ?';
$result = $adb->pquery($query, [$statusActivity['not_started'], $statusActivity['in_realization'], 0]);
while ($row = $adb->fetch_array($result)) {
$state = Calendar_Module_Model::getCalendarState($row);
if ($state) {
$recordModel = Vtiger_Record_Model::getInstanceById($row['activityid']);
$recordModel->set('id', $row['activityid']);
$recordModel->set('activitystatus', $state);
$recordModel->set('mode', 'edit');
$recordModel->save();
}
}
/** Function to insert values in vtiger_activity_reminder_popup table for the specified module
* @param $cbmodule -- module:: Type varchar
*/
function insertIntoActivityReminderPopup($cbmodule)
{
$adb = PearDatabase::getInstance();
$cbrecord = $this->id;
unset($_SESSION['next_reminder_time']);
if (isset($cbmodule) && isset($cbrecord)) {
$cbdate = getValidDBInsertDateValue($this->column_fields['date_start']);
$cbtime = $this->column_fields['time_start'];
$reminderQuery = 'SELECT reminderid FROM vtiger_activity_reminder_popup WHERE semodule = ? and recordid = ?';
$reminderParams = [$cbmodule, $cbrecord];
$reminderidres = $adb->pquery($reminderQuery, $reminderParams);
$reminderid = null;
if ($reminderidres->rowCount() > 0) {
$reminderid = $adb->query_result($reminderidres, 0, 'reminderid');
}
$currentStates = Calendar_Module_Model::getComponentActivityStateLabel('current');
$state = Calendar_Module_Model::getCalendarState($this->column_fields);
if (in_array($state, $currentStates)) {
$status = 0;
} else {
$status = 1;
}
if (isset($reminderid)) {
$adb->update('vtiger_activity_reminder_popup', ['date_start' => $cbdate, 'time_start' => $cbtime, 'status' => $status], 'reminderid = ?', [$reminderid]);
} else {
$adb->insert('vtiger_activity_reminder_popup', ['recordid' => $cbrecord, 'semodule' => $cbmodule, 'date_start' => $cbdate, 'time_start' => $cbtime, 'status' => $status]);
}
}
}
public function getRelationQueryForActivities($recordId, $relatedModule, $relationModel)
{
$query = 'SELECT vtiger_crmentity.*,vtiger_activity.* FROM vtiger_activity ' . ' INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid' . ' WHERE vtiger_activity.deleted = 0';
$referenceLinkClass = Vtiger_Loader::getComponentClassName('UIType', 'ReferenceLink', $relatedModule->getName());
$referenceLinkInstance = new $referenceLinkClass();
if (in_array($this->getName(), $referenceLinkInstance->getReferenceList())) {
$query .= ' AND vtiger_activity.`link` = ';
} else {
$referenceProcessClass = Vtiger_Loader::getComponentClassName('UIType', 'ReferenceProcess', $relatedModule->getName());
$referenceProcessInstance = new $referenceProcessClass();
if (in_array($this->getName(), $referenceProcessInstance->getReferenceList())) {
$query .= ' AND vtiger_activity.`process` = ';
} else {
$referenceSubProcessClass = Vtiger_Loader::getComponentClassName('UIType', 'ReferenceSubProcess', $relatedModule->getName());
$referenceSubProcessInstance = new $referenceSubProcessClass();
if (in_array($this->getName(), $referenceSubProcessInstance->getReferenceList())) {
$query .= ' AND vtiger_activity.`subprocess` = ';
} else {
throw new AppException('LBL_HANDLER_NOT_FOUND');
}
}
}
$query .= $recordId;
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
if ($time == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
return $query;
}
/**
* 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;
}
public function getEntity()
{
$db = PearDatabase::getInstance();
$module = 'Calendar';
$currentUser = Users_Record_Model::getCurrentUserModel();
$query = "SELECT vtiger_activity.activityid as act_id,vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype,\n\t\tvtiger_activity.*, relcrm.setype AS linkmod, relcrm.label AS linklabel, procrm.label AS processlabel, procrm.setype AS processmod\n\t\tFROM vtiger_activity\n\t\tLEFT JOIN vtiger_activitycf\n\t\t\tON vtiger_activitycf.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_crmentity\n\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_crmentity relcrm\n\t\t\tON relcrm.crmid = vtiger_activity.link\n\t\tLEFT JOIN vtiger_crmentity procrm\n\t\t\tON procrm.crmid = vtiger_activity.process\n\t\tWHERE vtiger_crmentity.deleted = 0 AND activitytype != 'Emails' ";
$instance = CRMEntity::getInstance($module);
$securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser);
if ($securityParameter != '') {
$query .= $securityParameter;
}
$params = array();
if ($this->get('start') && $this->get('end')) {
$dbStartDateOject = DateTimeField::convertToDBTimeZone($this->get('start'));
$dbStartDateTime = $dbStartDateOject->format('Y-m-d H:i:s');
$dbStartDate = $dbStartDateOject->format('Y-m-d');
$dbEndDateObject = DateTimeField::convertToDBTimeZone($this->get('end'));
$dbEndDateTime = $dbEndDateObject->format('Y-m-d H:i:s');
$dbEndDate = $dbEndDateObject->format('Y-m-d');
$query .= " AND ( (concat(date_start, ' ', time_start) >= ? AND concat(date_start, ' ', time_start) <= ?) OR (concat(due_date, ' ', time_end) >= ? AND concat(due_date, ' ', time_end) <= ?) OR (date_start < ? AND due_date > ?) ) ";
$params[] = $dbStartDateTime;
$params[] = $dbEndDateTime;
$params[] = $dbStartDateTime;
$params[] = $dbEndDateTime;
$params[] = $dbStartDate;
$params[] = $dbEndDate;
}
if ($this->get('types')) {
$query .= " AND vtiger_activity.activitytype IN ('" . implode("','", $this->get('types')) . "')";
}
if ($this->get('time') == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "')";
}
if ($this->get('time') == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "')";
}
if ($this->get('activitystatus')) {
$query .= " AND vtiger_activity.status IN ('" . $this->get('activitystatus') . "')";
}
if ($this->get('user')) {
if (is_array($this->get('user'))) {
$query .= ' AND vtiger_crmentity.smownerid IN (' . implode(",", $this->get('user')) . ')';
} else {
$query .= ' AND vtiger_crmentity.smownerid IN (' . $this->get('user') . ')';
}
}
$query .= ' ORDER BY date_start,time_start ASC';
$queryResult = $db->pquery($query, $params);
$result = array();
for ($i = 0; $i < $db->num_rows($queryResult); $i++) {
$record = $db->raw_query_result_rowdata($queryResult, $i);
$item = array();
$crmid = $record['activityid'];
$activitytype = $record['activitytype'];
$item['id'] = $crmid;
$item['title'] = $record['subject'];
$item['url'] = 'index.php?module=' . $module . '&view=Detail&record=' . $crmid;
$item['set'] = $record['activitytype'] == 'Task' ? 'Task' : 'Event';
$item['lok'] = $record['location'];
$item['pri'] = $record['priority'];
$item['sta'] = $record['status'];
$item['vis'] = $record['visibility'];
$item['state'] = $record['state'];
//Relation
$item['link'] = $record['link'];
$item['linkl'] = $record['linklabel'];
$item['linkm'] = $record['linkmod'];
//Process
$item['process'] = $record['process'];
$item['procl'] = $record['processlabel'];
$item['procm'] = $record['processmod'];
if ($record['linkmod'] != 'Accounts' && (!empty($record['link']) || !empty($record['process']))) {
$findId = 0;
$findMod = '';
if (!empty($record['link'])) {
$findId = $record['link'];
$findMod = $record['linkmod'];
}
if (!empty($record['process'])) {
$findId = $record['process'];
$findMod = $record['processmod'];
}
$tabInfo = $this->relationAcounts[$findMod];
if ($tabInfo) {
$findResult = $db->pquery('SELECT accountid, accountname FROM vtiger_account ' . 'INNER JOIN ' . $tabInfo[0] . ' ON vtiger_account.accountid = ' . $tabInfo[0] . '.' . $tabInfo[2] . ' WHERE ' . $tabInfo[1] . ' = ?;', [$findId]);
if ($db->num_rows($findResult) > 0) {
$item['accid'] = $db->query_result_raw($findResult, 0, 'accountid');
$item['accname'] = $db->query_result_raw($findResult, 0, 'accountname');
}
}
}
$dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
$startTimeFormated = $dateTimeComponents[1];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$startDateFormated = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$dateTimeFieldInstance = new DateTimeField($record['due_date'] . ' ' . $record['time_end']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
$endTimeFormated = $dateTimeComponents[1];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$endDateFormated = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$item['start'] = $startDateFormated . ' ' . $startTimeFormated;
$item['end'] = $endDateFormated . ' ' . $endTimeFormated;
$item['allDay'] = $record['allday'] == 1 ? true : false;
$item['className'] = ' userCol_' . $record['smownerid'] . ' calCol_' . $activitytype;
if ($this->has('widget') && $this->get('widget') == true) {
$widgetElements = $this->getWidgetElements($widgetElements, $startDateFormated, $endDateFormated, $activitytype);
$result = array_values($widgetElements);
} else {
$result[] = $item;
}
}
return $result;
}
