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