/** To get the events of the specified user and shared events
* @param $userid -- The user Id:: Type integer
* @param $from_datetime -- The start date Obj :: Type Array
* @param $to_datetime -- The end date Obj :: Type Array
* @param $view -- The calendar view :: Type String
* @returns $list :: Type Array
*/
function readAppointment($userid, &$from_datetime, &$to_datetime, $view)
{
global $current_user, $adb;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$and = "AND (\n\t\t\t\t\t(\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) >= ? AND CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ? )\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t\t)\n\t\t\t\t\t\tAND vtiger_recurringevents.activityid is NULL\n\t\t\t\t\t)\n\t\t\t\tOR (\n\t\t\t\t\t\t(CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) >= ?\n\t\t\t\t\t\t\tAND CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ?)\n\t\t\t\t\t\tOR\t(CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) <= ?\n\t\t\t\t\t\t\tAND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t)\n\t\t\t\t)";
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$q = "select vtiger_activity.*, vtiger_crmentity.*,\n\t\t\t\t\tcase when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name\n\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tinner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid\n\t\t\t\t\t\tleft join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid\n\t\t\t\t\t\tleft join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') {$and} ";
// User Select Customization: Changes should made also in (calendayLaout getEventList) and one more BELOW
$query_filter_prefix = calendarview_getSelectedUserFilterQuerySuffix();
$q .= $query_filter_prefix;
// END
$h = $from_datetime->z_hour;
$m = $from_datetime->min;
if (empty($m)) {
$m = '00';
}
$startDate = new DateTimeField($from_datetime->year . "-" . $from_datetime->z_month . "-" . $from_datetime->z_day . " {$h}:{$m}");
$h = '23';
$m = '59';
$endDate = new DateTimeField($to_datetime->year . "-" . $to_datetime->z_month . "-" . $to_datetime->z_day . " {$h}:{$m}");
$params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue());
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
//Added for User Based Custom View for Calendar
$sec_parameter = getCalendarViewSecurityParameter();
$q .= $sec_parameter;
}
$q .= " AND vtiger_recurringevents.activityid is NULL ";
$q .= " group by vtiger_activity.activityid ORDER by vtiger_activity.date_start,vtiger_activity.time_start";
$r = $adb->pquery($q, $params);
$n = $adb->getRowCount($r);
$a = 0;
$list = array();
while ($a < $n) {
$result = $adb->fetchByAssoc($r);
$from = strtotime($result['date_start']);
$to = strtotime($result['due_date'] . ' ' . $result["time_end"]);
$windowTo = strtotime($endDate->getDBInsertDateTimeValue());
for ($j = $from; $j <= $to; $j = $j + 60 * 60 * 24) {
$obj = new Appointment();
$temp_start = date("Y-m-d", $j);
$endTime = strtotime($temp_start . ' ' . $result['time_start']);
if ($endTime > $windowTo) {
break;
}
$result["date_start"] = $temp_start;
list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]);
if ($start_timestamp != $end_timestamp && $view == 'day') {
if ($j == $start_timestamp) {
$result["duration_hours"] = 24 - $obj->temphour;
} elseif ($j > $start_timestamp && $j < $end_timestamp) {
list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00");
$result["duration_hours"] = 24 - $obj->temphour;
} elseif ($j == $end_timestamp) {
list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00");
list($ehr, $emin) = explode(":", $result["time_end"]);
$result["duration_hours"] = $ehr - $obj->temphour;
}
}
$obj->readResult($result, $view);
$list[] = $obj;
unset($obj);
}
$a++;
}
//Get Recurring events
$q = "SELECT vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name , vtiger_recurringevents.recurringid, vtiger_recurringevents.recurringdate as date_start ,vtiger_recurringevents.recurringtype,vtiger_groups.groupname from vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid inner join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid";
$q .= getNonAdminAccessControlQuery('Calendar', $current_user);
$q .= " where vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') AND (cast(concat(recurringdate, ' ', time_start) as datetime) between ? and ?) ";
// User Select Customization
$q .= $query_filter_prefix;
// END
$params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue());
$q .= " ORDER by vtiger_recurringevents.recurringid";
$r = $adb->pquery($q, $params);
$n = $adb->getRowCount($r);
$a = 0;
while ($a < $n) {
$obj = new Appointment();
$result = $adb->fetchByAssoc($r);
list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]);
$obj->readResult($result, $view);
$a++;
$list[] = $obj;
unset($obj);
}
usort($list, 'compare');
return $list;
}
