public function getEntity()
{
$db = PearDatabase::getInstance();
$module = 'Calendar';
$currentUser = Users_Record_Model::getCurrentUserModel();
$query = getListQuery($module);
$params = array();
if ($this->get('start') && $this->get('end')) {
$dbStartDateOject = DateTimeField::convertToDBTimeZone($this->get('start'));
$dbStartDateTime = $dbStartDateOject->format('Y-m-d H:i:s');
$dbEndDateObject = DateTimeField::convertToDBTimeZone($this->get('end'));
$dbEndDateTime = $dbEndDateObject->format('Y-m-d H:i:s');
$query .= " AND (concat(date_start, ' ', time_start) >= ? AND concat(due_date, ' ', time_end) <= ?) ";
$params[] = $dbStartDateTime;
$params[] = $dbEndDateTime;
}
if ($this->get('types')) {
$query .= " AND vtiger_activity.activitytype IN ('" . implode("','", $this->get('types')) . "')";
}
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;
$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;
$result[] = $item;
}
return $result;
}
public function getEntity()
{
$db = PearDatabase::getInstance();
$module = 'Reservations';
$currentUser = Users_Record_Model::getCurrentUserModel();
$query = getListQuery($module);
$params = array();
if ($this->get('start') && $this->get('end')) {
$query .= ' AND vtiger_reservations.date_start >= ? AND vtiger_reservations.due_date <= ?';
$params[] = $this->get('start');
$params[] = $this->get('end');
}
if ($this->get('types')) {
$query .= " AND vtiger_reservations.type IN ('" . implode("','", $this->get('types')) . "')";
}
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') . ')';
}
}
$instance = CRMEntity::getInstance($module);
$securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser);
if ($securityParameter != '') {
$query .= ' AND ' . $securityParameter;
}
$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['reservationsid'];
$item['id'] = $crmid;
$item['title'] = vtranslate($record['name'], $module);
$item['url'] = 'index.php?module=Reservations&view=Detail&record=' . $crmid;
$dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$item['start'] = $dataBaseDateFormatedString . ' ' . $dateTimeComponents[1];
$dateTimeFieldInstance = new DateTimeField($record['due_date'] . ' ' . $record['time_end']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$item['end'] = $dataBaseDateFormatedString . ' ' . $dateTimeComponents[1];
$item['className'] = ' userCol_' . $record['smownerid'] . ' calCol_' . $record['type'];
$result[] = $item;
}
return $result;
}
public function process(Vtiger_Request $request)
{
$user = Users_Record_Model::getCurrentUserModel();
$allDay = $request->get('allday');
if ('on' == $allDay) {
$request->set('time_start', NULL);
$request->set('time_end', NULL);
}
$recordModel = $this->saveRecord($request);
$fieldModelList = $recordModel->getModule()->getFields();
$result = array();
foreach ($fieldModelList as $fieldName => $fieldModel) {
$fieldValue = Vtiger_Util_Helper::toSafeHTML($recordModel->get($fieldName));
$result[$fieldName] = array();
if ($fieldName == 'date_start') {
$timeStart = $recordModel->get('time_start');
$dateTimeFieldInstance = new DateTimeField($fieldValue . ' ' . $timeStart);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $user->get('date_format'));
$result[$fieldName]['value'] = $fieldValue;
$result[$fieldName]['display_value'] = $dataBaseDateFormatedString;
} else {
if ($fieldName == 'due_date') {
$timeEnd = $recordModel->get('time_end');
$dateTimeFieldInstance = new DateTimeField($fieldValue . ' ' . $timeEnd);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $user->get('date_format'));
$result[$fieldName]['value'] = $fieldValue;
$result[$fieldName]['display_value'] = $dataBaseDateFormatedString;
} else {
if ($fieldName == 'time_end') {
$dueDate = $recordModel->get('due_date');
$dateTimeFieldInstance = new DateTimeField($dueDate . ' ' . $fieldValue);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
$dateTimeComponents = explode(' ', $userDateTimeString);
$result[$fieldName]['value'] = $fieldValue;
$result[$fieldName]['display_value'] = $dateTimeComponents[1];
} else {
if ($fieldName == 'time_start') {
$startDate = $recordModel->get('date_start');
$dateTimeFieldInstance = new DateTimeField($startDate . ' ' . $fieldValue);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
$dateTimeComponents = explode(' ', $userDateTimeString);
$result[$fieldName]['value'] = $fieldValue;
$result[$fieldName]['display_value'] = $dateTimeComponents[1];
} else {
if ('time_start' != $fieldName && 'time_end' != $fieldName && 'duration_hours' != $fieldName) {
$result[$fieldName]['value'] = $fieldValue;
$result[$fieldName]['display_value'] = decode_html($fieldModel->getDisplayValue($fieldValue));
} else {
$result[$fieldName]['value'] = $result[$fieldName]['display_value'] = $fieldValue;
}
}
}
}
}
}
$result['_recordLabel'] = $recordModel->getName();
$result['_recordId'] = $recordModel->getId();
// Handled to save follow up event
$followupMode = $request->get('followup');
if ($followupMode == 'on') {
//Start Date and Time values
$startTime = Vtiger_Time_UIType::getTimeValueWithSeconds($request->get('followup_time_start'));
$startDateTime = Vtiger_Datetime_UIType::getDBDateTimeValue($request->get('followup_date_start') . " " . $startTime);
list($startDate, $startTime) = explode(' ', $startDateTime);
$subject = $request->get('subject');
if ($startTime != '' && $startDate != '') {
$recordModel->set('eventstatus', 'Planned');
$recordModel->set('subject', '[Followup] ' . $subject);
$recordModel->set('date_start', $startDate);
$recordModel->set('time_start', $startTime);
$currentUser = Users_Record_Model::getCurrentUserModel();
$activityType = $recordModel->get('activitytype');
if ($activityType == 'Call') {
$minutes = $currentUser->get('callduration');
} else {
$minutes = $currentUser->get('othereventduration');
}
$dueDateTime = date('Y-m-d H:i:s', strtotime("{$startDateTime}+{$minutes} minutes"));
list($endDate, $endTime) = explode(' ', $dueDateTime);
$recordModel->set('due_date', $endDate);
$recordModel->set('time_end', $endTime);
$recordModel->set('mode', 'create');
$recordModel->save();
}
}
$response = new Vtiger_Response();
$response->setEmitType(Vtiger_Response::$EMIT_JSON);
$response->setResult($result);
$response->emit();
}
function writeReportToExcelFile($fileName, $filterlist = '')
{
global $currentModule, $current_language, $current_user;
$mod_strings = return_module_language($current_language, $currentModule);
require_once 'include/PHPExcel/PHPExcel.php';
$xlsrowheight = GlobalVariable::getVariable('Report.Excel.Export.RowHeight', 20);
$workbook = new PHPExcel();
$worksheet = $workbook->setActiveSheetIndex(0);
$fieldinfo = array();
$arr_val = $this->GenerateReport('PDF', $filterlist, false, $fieldinfo);
$totalxls = $this->GenerateReport('TOTALXLS', $filterlist);
$header_styles = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E1E0F7')), 'font' => array('bold' => true));
if (isset($arr_val)) {
$FieldDataTypes = array();
foreach ($arr_val[0] as $hdr => $value) {
$FieldDataTypes[$hdr] = $fieldinfo[$hdr]->getFieldDataType();
if ($fieldinfo[$hdr]->getColumnName() == 'totaltime') {
$FieldDataTypes[$hdr] = 'time';
}
if ($fieldinfo[$hdr]->getColumnName() == 'totaldaytime') {
$FieldDataTypes[$hdr] = 'time';
}
}
$BoolTrue = getTranslatedString('LBL_YES');
//$BoolFalse = getTranslatedString('LBL_NO');
$count = 0;
$rowcount = 1;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
//copy the first value details
$arrayFirstRowValues = $arr_val[0];
foreach ($arrayFirstRowValues as $key => $value) {
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $key, true);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
// NOTE Performance overhead: http://stackoverflow.com/questions/9965476/phpexcel-column-size-issues
$worksheet->getColumnDimensionByColumn($count)->setAutoSize(true);
$count = $count + 1;
if ($FieldDataTypes[$key] == 'currency') {
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, getTranslatedString('LBL_CURRENCY'), true);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$worksheet->getColumnDimensionByColumn($count)->setAutoSize(true);
$count = $count + 1;
}
}
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
foreach ($arr_val as $key => $array_value) {
$count = 0;
foreach ($array_value as $hdr => $value) {
$value = decode_html($value);
switch ($FieldDataTypes[$hdr]) {
case 'boolean':
$celltype = PHPExcel_Cell_DataType::TYPE_BOOL;
$value = $value == $BoolTrue ? 1 : 0;
break;
case 'integer':
case 'double':
case 'currency':
$celltype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
case 'date':
$value = DateTimeField::__convertToDBFormat($value, $current_user->date_format);
$dt = new DateTime($value);
$value = PHPExcel_Shared_Date::PHPToExcel($dt);
$celltype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
case 'time':
$dt = new DateTime("1970/01/01 {$value}");
$value = PHPExcel_Shared_Date::PHPToExcel($dt);
$celltype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
default:
$celltype = PHPExcel_Cell_DataType::TYPE_STRING;
break;
}
if ($FieldDataTypes[$hdr] == 'currency') {
$csym = preg_replace('/[0-9,.-]/', '', $value);
$value = preg_replace('/[^0-9,.-]/', '', $value);
$value = str_replace($current_user->currency_grouping_separator, '', $value);
if ($current_user->currency_decimal_separator != '.') {
$value = str_replace($current_user->currency_decimal_separator, '.', $value);
}
}
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $value, $celltype);
if ($FieldDataTypes[$hdr] == 'date') {
$worksheet->getStyleByColumnAndRow($count, $rowcount)->getNumberFormat()->setFormatCode($current_user->date_format);
} elseif ($FieldDataTypes[$hdr] == 'time') {
$worksheet->getStyleByColumnAndRow($count, $rowcount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
}
if ($FieldDataTypes[$hdr] == 'currency') {
$count = $count + 1;
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $csym, PHPExcel_Cell_DataType::TYPE_STRING);
}
$count = $count + 1;
}
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
}
// Summary Total
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
$count = 0;
if (isset($totalxls) and is_array($totalxls) and count($totalxls) > 0) {
if (is_array($totalxls[0])) {
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, getTranslatedString('Totals', 'Reports'), PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$count = $count + 1;
foreach ($totalxls[0] as $key => $value) {
$chdr = substr($key, -3, 3);
$translated_str = in_array($chdr, array_keys($mod_strings)) ? $mod_strings[$chdr] : $key;
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, decode_html($translated_str), PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$count = $count + 1;
}
}
$rowcount++;
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
foreach ($totalxls as $key => $array_value) {
$count = 0;
foreach ($array_value as $hdr => $value) {
if ($count == 0) {
$lbl = substr($hdr, 0, strrpos($hdr, '_'));
$mname = substr($lbl, 0, strpos($lbl, '_'));
$lbl = substr($lbl, strpos($lbl, '_') + 1);
$lbl = str_replace('_', ' ', $lbl);
$lbl = getTranslatedString($lbl, $mname);
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, decode_html($lbl), PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyleByColumnAndRow($count, $rowcount)->applyFromArray($header_styles);
$workbook->getActiveSheet()->getRowDimension($rowcount)->setRowHeight($xlsrowheight);
$count = $count + 1;
}
$value = str_replace($current_user->currency_grouping_separator, '', $value);
if ($current_user->currency_decimal_separator != '.') {
$value = str_replace($current_user->currency_decimal_separator, '.', $value);
}
$worksheet->setCellValueExplicitByColumnAndRow($count, $rowcount, $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
$count = $count + 1;
}
$rowcount++;
}
}
}
$workbookWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel5');
$workbookWriter->save($fileName);
}
public function getStdComparator($comparator, $value, $datatype = "")
{
$return = "";
global $default_charset;
if ($comparator != "custom") {
$date_array = GenerateObj::getStandarFiltersStartAndEndDate($comparator);
} else {
$date_array = explode("<;@STDV@;>", html_entity_decode($value, ENT_QUOTES, $default_charset));
}
$s_date = $date_array[0];
$e_date = $date_array[1];
switch ($comparator) {
case "today":
case "yesterday":
case "tomorrow":
$return = " = '{$s_date}' ";
break;
case "thisweek":
case "lastweek":
case "nextweek":
case "thismonth":
case "lastmonth":
case "nextmonth":
case "next7days":
case "next15days":
case "next30days":
case "next60days":
case "next90days":
case "next120days":
case "last7days":
case "last15days":
case "last30days":
case "last60days":
case "last90days":
case "last120days":
case "thisfy":
case "prevfy":
case "nextfy":
case "nextfq":
case "prevfq":
case "thisfq":
$return = " BETWEEN '{$s_date}' AND '{$e_date}' ";
break;
case "todaymore":
$return = " > '{$s_date}' ";
break;
case "todayless":
case "older1days":
case "older7days":
case "older15days":
case "older30days":
case "older60days":
case "older90days":
case "older120days":
case "todayless":
$return = " < '{$s_date}' ";
break;
default:
$currentUser = Users_Record_Model::getCurrentUserModel();
$js_cal_dateformat = $currentUser->get('date_format');
//$js_cal_dateformat = "dd-mm-yyyy";
if ($s_date != "") {
$s_date = DateTimeField::__convertToDBFormat(trim($s_date), $js_cal_dateformat);
}
if ($e_date != "") {
$e_date = DateTimeField::__convertToDBFormat(trim($e_date), $js_cal_dateformat);
}
if ($s_date != "" && $e_date != "") {
$return = " BETWEEN '{$s_date}' AND '{$e_date}' ";
} elseif ($s_date != "") {
$return = " = '{$s_date}' ";
} elseif ($e_date != "") {
$return = " = '{$e_date}' ";
} else {
$return = "";
}
break;
}
return $return;
}
public function getEntityCount()
{
$currentUser = Users_Record_Model::getCurrentUserModel();
$db = PearDatabase::getInstance();
$startDate = DateTimeField::convertToDBTimeZone($this->get('start'));
$startDate = strtotime($startDate->format('Y-m-d H:i:s'));
$endDate = DateTimeField::convertToDBTimeZone($this->get('end'));
$endDate = strtotime($endDate->format('Y-m-d H:i:s'));
$data = $this->getQuery();
$result = $db->pquery($data['query'], $data['params']);
$return = [];
while ($record = $db->fetch_array($result)) {
$crmid = $record['activityid'];
$activitytype = $record['activitytype'];
$dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
$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];
$endDateFormated = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$begin = new DateTime($startDateFormated);
$end = new DateTime($endDateFormated);
$end->modify('+1 day');
$interval = DateInterval::createFromDateString('1 day');
foreach (new DatePeriod($begin, $interval, $end) as $dt) {
$date = strtotime($dt->format('Y-m-d'));
if ($date >= $startDate && $date <= $endDate) {
$date = date('Y-m-d', $date);
$return[$date]['start'] = $date;
$return[$date]['date'] = $date;
$return[$date]['event'][$activitytype]['count'] += 1;
$return[$date]['event'][$activitytype]['className'] = ' fc-draggable calCol_' . $activitytype;
$return[$date]['event'][$activitytype]['label'] = vtranslate($activitytype, $this->getModuleName());
$return[$date]['type'] = 'widget';
}
}
}
return array_values($return);
}
protected function pullTasks($start, $end, &$result, $color = null, $textColor = 'white')
{
$user = Users_Record_Model::getCurrentUserModel();
$db = PearDatabase::getInstance();
$moduleModel = Vtiger_Module_Model::getInstance('Calendar');
$userAndGroupIds = array_merge(array($user->getId()), $this->getGroupsIdsForUsers($user->getId()));
$queryGenerator = new QueryGenerator($moduleModel->get('name'), $user);
$queryGenerator->setFields(array('activityid', 'subject', 'taskstatus', 'activitytype', 'date_start', 'time_start', 'due_date', 'time_end', 'id'));
$query = $queryGenerator->getQuery();
$query .= " AND vtiger_activity.activitytype = 'Task' AND ";
$currentUser = Users_Record_Model::getCurrentUserModel();
$hideCompleted = $currentUser->get('hidecompletedevents');
if ($hideCompleted) {
$query .= "vtiger_activity.status != 'Completed' AND ";
}
$query .= " ((date_start >= ? AND due_date < ?) OR ( due_date >= ?))";
$params = array($start, $end, $start);
$params = array_merge($params, $userAndGroupIds);
$query .= " AND vtiger_crmentity.smownerid IN (" . generateQuestionMarks($userAndGroupIds) . ")";
$queryResult = $db->pquery($query, $params);
while ($record = $db->fetchByAssoc($queryResult)) {
$item = array();
$crmid = $record['activityid'];
$item['title'] = decode_html($record['subject']) . ' - (' . decode_html(vtranslate($record['status'], 'Calendar')) . ')';
$item['status'] = $record['status'];
$item['activitytype'] = $record['activitytype'];
$item['id'] = $crmid;
$dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
$userDateTimeString = $dateTimeFieldInstance->getFullcalenderDateTimevalue();
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $user->get('date_format'));
$item['start'] = $dataBaseDateFormatedString . ' ' . $dateTimeComponents[1];
$item['end'] = $record['due_date'];
$item['url'] = sprintf('index.php?module=Calendar&view=Detail&record=%s', $crmid);
$item['color'] = $color;
$item['textColor'] = $textColor;
$item['module'] = $moduleModel->getName();
$result[] = $item;
}
}
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') {
$query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status not in ('Completed','Deferred'))\n\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus not in ('','Held')))";
}
if ($this->get('time') == 'history') {
$query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status in ('Completed','Deferred'))\n\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus in ('','Held')))";
}
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'] == '' ? $record['eventstatus'] : $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) {
$firstDate = strtotime($startDateFormated);
$endDate = strtotime($endDateFormated);
$diffDate = intval(($endDate - $firstDate) / (60 * 60 * 24));
for ($q = 0; $q <= $diffDate; $q++) {
if ($q == 0) {
$date = $startDateFormated;
} else {
$date = strtotime(date("Y-m-d", strtotime($date)) . " +1 day");
$date = date('Y-m-d', $date);
}
if ($activitytype == 'Task') {
$widgetElements[$date]['start'] = $date;
$widgetElements[$date]['event']['Task']['ids'][] = $crmid;
$crmids = Zend_Json::encode($widgetElements[$date]['event']['Task']['ids']);
$widgetElements[$date]['event']['Task']['url'] = "index.php?module=Calendar&view=List&searchResult=" . $crmids;
$widgetElements[$date]['event']['Task']['className'] = ' col-md-5 fc-draggable calCol_' . $activitytype;
$widgetElements[$date]['type'] = 'widget';
} else {
$widgetElements[$date]['start'] = $date;
$widgetElements[$date]['event']['Meeting']['ids'][] = $crmid;
$crmids = Zend_Json::encode($widgetElements[$date]['event']['Meeting']['ids']);
$widgetElements[$date]['event']['Meeting']['url'] = "index.php?module=Calendar&view=List&searchResult=" . $crmids;
$widgetElements[$date]['event']['Meeting']['className'] = ' col-md-5 fc-draggable calCol_' . $activitytype;
$widgetElements[$date]['type'] = 'widget';
}
}
$result = array_values($widgetElements);
} else {
$result[] = $item;
}
}
return $result;
}
public function process($feed, $request, $start, $end, &$result, $userid = false, $color = null, $textColor = 'white')
{
$dbStartDateOject = DateTimeField::convertToDBTimeZone($start);
$dbStartDateTime = $dbStartDateOject->format('Y-m-d H:i:s');
$dbStartDateTimeComponents = explode(' ', $dbStartDateTime);
$dbStartDate = $dbStartDateTimeComponents[0];
$dbEndDateObject = DateTimeField::convertToDBTimeZone($end);
$dbEndDateTime = $dbEndDateObject->format('Y-m-d H:i:s');
$currentUser = Users_Record_Model::getCurrentUserModel();
$db = PearDatabase::getInstance();
$moduleModel = Vtiger_Module_Model::getInstance('Events');
if ($userid) {
$focus = new Users();
$focus->id = $userid;
$focus->retrieve_entity_info($userid, 'Users');
$user = Users_Record_Model::getInstanceFromUserObject($focus);
$userName = $user->getName();
}
$params = array();
if (empty($userid)) {
$eventUserId = $currentUser->getId();
} else {
$eventUserId = $userid;
}
$params = array_merge(array($eventUserId), $feed->getGroupsIdsForUsers($eventUserId));
$query = 'SELECT vtiger_activity.subject, vtiger_activity.eventstatus, vtiger_activity.visibility, vtiger_activity.date_start, vtiger_activity.time_start, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.activityid, vtiger_activity.activitytype, vtiger_seactivityrel.crmid as parent_id FROM vtiger_activity LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid WHERE';
$query .= " vtiger_activity.activitytype NOT IN ('Emails','Task') AND ";
$hideCompleted = $currentUser->get('hidecompletedevents');
if ($hideCompleted) {
$query .= "vtiger_activity.eventstatus != 'HELD' AND ";
}
$query .= " ((concat(date_start, '', time_start) >= '{$dbStartDateTime}' AND concat(due_date, '', time_end) < '{$dbEndDateTime}') ) AND vtiger_activity.smownerid IN (" . generateQuestionMarks($params) . ") AND vtiger_activity.deleted=0";
$queryResult = $db->pquery($query, $params);
while ($record = $db->fetchByAssoc($queryResult)) {
$item = array();
$crmid = $record['activityid'];
$visibility = $record['visibility'];
$activitytype = $record['activitytype'];
$status = $record['eventstatus'];
$item['id'] = $crmid;
$item['visibility'] = $visibility;
$item['activitytype'] = $activitytype;
$item['status'] = $status;
//dodanie powiazan albo z kontaktow albo z powiazanych
$title_add = $record['parent_id'] ? Vtiger_Functions::getCRMRecordLabel($record['parent_id']) : implode(', ', getActivityRelatedContacts($crmid));
if (!$currentUser->isAdminUser() && $visibility == 'Private' && $userid && $userid != $currentUser->getId()) {
$item['title'] = decode_html($userName);
$item['url'] = '';
} else {
$item['title'] = decode_html($record['subject']);
$item['url'] = sprintf('index.php?module=Calendar&view=Detail&record=%s', $crmid);
}
if ($title_add != '') {
$item['title'] .= ' [' . decode_html($title_add) . ']';
}
$dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$item['start'] = $dataBaseDateFormatedString . ' ' . $dateTimeComponents[1];
$dateTimeFieldInstance = new DateTimeField($record['due_date'] . ' ' . $record['time_end']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue($currentUser);
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $currentUser->get('date_format'));
$item['end'] = $dataBaseDateFormatedString . ' ' . $dateTimeComponents[1];
$item['className'] = $cssClass;
$item['allDay'] = false;
$item['color'] = $color;
$item['textColor'] = $textColor;
$item['module'] = $moduleModel->getName();
$result[] = $item;
}
return $widget;
}
public function process($feed, $request, $start, $end, &$result, $userid = false, $color = null, $textColor = 'white')
{
$user = Users_Record_Model::getCurrentUserModel();
$db = PearDatabase::getInstance();
$moduleModel = Vtiger_Module_Model::getInstance('Calendar');
$userAndGroupIds = array_merge(array($user->getId()), $feed->getGroupsIdsForUsers($user->getId()));
$queryGenerator = new QueryGenerator($moduleModel->get('name'), $user);
$queryGenerator->setFields(array('activityid', 'subject', 'taskstatus', 'activitytype', 'date_start', 'time_start', 'due_date', 'time_end', 'id'));
$query = $queryGenerator->getQuery();
$newQuery = spliti('FROM', $query);
foreach ($newQuery as $key => $val) {
if ($key == 0) {
$query = $newQuery[0] . ' ,vtiger_seactivityrel.crmid as parent_id';
} else {
$query .= ' FROM ' . $val;
}
}
$joinQuery = ' LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid';
$newQuery = spliti('WHERE ', $query);
$query = $newQuery[0] . $joinQuery;
foreach ($newQuery as $key => $val) {
if ($key != 0) {
$query .= ' WHERE ' . $val;
}
}
$query .= " AND vtiger_activity.activitytype = 'Task' AND ";
$currentUser = Users_Record_Model::getCurrentUserModel();
$hideCompleted = $currentUser->get('hidecompletedevents');
if ($hideCompleted) {
$query .= "vtiger_activity.status != 'Completed' AND ";
}
//$query.= " ((date_start >= '$start' AND due_date < '$end') OR ( due_date >= '$start'))";
// opensaas
$query .= " ((date_start >= '{$start}' AND due_date < '{$end}') )";
// opensaas
$params = $userAndGroupIds;
$query .= " AND vtiger_crmentity.smownerid IN (" . generateQuestionMarks($params) . ")";
$queryResult = $db->pquery($query, $params);
while ($record = $db->fetchByAssoc($queryResult)) {
$item = array();
$crmid = $record['activityid'];
//dodanie infromacji z wyciagnietych
$title_add = $record['parent_id'] ? Vtiger_Functions::getCRMRecordLabel($record['parent_id']) : implode(', ', getActivityRelatedContacts($crmid));
$item['title'] = decode_html($record['subject']);
if ($title_add != '') {
$item['title'] .= ' [' . decode_html($title_add) . ']';
}
$item['status'] = $record['status'];
$item['activitytype'] = $record['activitytype'];
$item['id'] = $crmid;
$dateTimeFieldInstance = new DateTimeField($record['date_start'] . ' ' . $record['time_start']);
$userDateTimeString = $dateTimeFieldInstance->getDisplayDateTimeValue();
$dateTimeComponents = explode(' ', $userDateTimeString);
$dateComponent = $dateTimeComponents[0];
//Conveting the date format in to Y-m-d . since full calendar expects in the same format
$dataBaseDateFormatedString = DateTimeField::__convertToDBFormat($dateComponent, $user->get('date_format'));
$item['start'] = $dataBaseDateFormatedString . ' ' . $dateTimeComponents[1];
$item['end'] = $record['due_date'];
$item['url'] = sprintf('index.php?module=Calendar&view=Detail&record=%s', $crmid);
$item['color'] = $color;
$item['textColor'] = $textColor;
$item['module'] = $moduleModel->getName();
$result[] = $item;
}
}
