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