function setFields(array $fieldNames, $required, $value)
{
require_once 'include/fields/DateTimeField.php';
foreach ($fieldNames as $ind => $fieldname) {
$fieldInfo = Webforms::getFieldInfo($this->getTargetModule(), $fieldname);
$fieldModel = new Webforms_Field_Model();
$fieldModel->setFieldName($fieldname);
$fieldModel->setNeutralizedField($fieldname, $fieldInfo['label']);
$field = Webforms::getFieldInfo('Leads', $fieldname);
if ($field['type']['name'] == 'date') {
$defaultvalue = DateTimeField::convertToDBFormat($value[$fieldname]);
} else {
if ($field['type']['name'] == 'boolean') {
if (in_array($fieldname, $required)) {
if (empty($value[$fieldname])) {
$defaultvalue = 'off';
} else {
$defaultvalue = 'on';
}
} else {
$defaultvalue = $value[$fieldname];
}
} else {
$defaultvalue = vtlib_purify($value[$fieldname]);
}
}
$fieldModel->setDefaultValue($defaultvalue);
if (!empty($required) && in_array($fieldname, $required)) {
$fieldModel->setRequired(1);
} else {
$fieldModel->setRequired(0);
}
$this->addField($fieldModel);
}
}
/**
* Save date
* @param <Object> $request
* @return true if saved, false otherwise
*/
public function save(Vtiger_Request $request)
{
$response = new Vtiger_Response();
$moduleName = 'Settings:' . $request->getModule();
try {
$id = $request->get('holidayId');
$date = DateTimeField::convertToDBFormat($request->get('holidayDate'));
$name = $request->get('holidayName');
$type = $request->get('holidayType');
if (empty($name) || empty($date)) {
$response->setResult(array('success' => false, 'message' => vtranslate('LBL_FILL_FORM_ERROR', $moduleName)));
} else {
if (!empty($id)) {
if (Settings_PublicHoliday_Module_Model::edit($id, $date, $name, $type)) {
$response->setResult(array('success' => true, 'message' => vtranslate('LBL_EDIT_DATE_OK', $moduleName)));
} else {
$response->setResult(array('success' => false, 'message' => vtranslate('LBL_EDIT_DATE_ERROR', $moduleName)));
}
} else {
if (Settings_PublicHoliday_Module_Model::save($date, $name, $type)) {
$response->setResult(array('success' => true, 'message' => vtranslate('LBL_NEW_DATE_OK', $moduleName)));
} else {
$response->setResult(array('success' => false, 'message' => vtranslate('LBL_NEW_DATE_ERROR', $moduleName)));
}
}
}
} catch (Exception $e) {
$response->setError($e->getCode(), $e->getMessage());
}
$response->emit();
}
public function process(Vtiger_Request $request)
{
$recordId = $request->get('record');
$modules = $request->get('modules');
$assignId = $request->get('assigned_user_id');
$currentUser = Users_Record_Model::getCurrentUserModel();
$entityValues = array();
$entityValues['transferRelatedRecordsTo'] = $request->get('transferModule');
//■権限なぜか、エラーになるので権限を固定するinouchi
//$entityValues['assignedTo'] = vtws_getWebserviceEntityId(vtws_getOwnerType($assignId), $assignId);
$entityValues['assignedTo'] = vtws_getWebserviceEntityId(vtws_getOwnerType(1), 1);
$entityValues['leadId'] = vtws_getWebserviceEntityId($request->getModule(), $recordId);
$recordModel = Vtiger_Record_Model::getInstanceById($recordId, $request->getModule());
$convertLeadFields = $recordModel->getConvertLeadFields();
$availableModules = array('Accounts', 'Contacts', 'Potentials');
foreach ($availableModules as $module) {
if (vtlib_isModuleActive($module) && in_array($module, $modules)) {
$entityValues['entities'][$module]['create'] = true;
$entityValues['entities'][$module]['name'] = $module;
foreach ($convertLeadFields[$module] as $fieldModel) {
$fieldName = $fieldModel->getName();
$fieldValue = $request->get($fieldName);
//Potential Amount Field value converting into DB format
if ($fieldModel->getFieldDataType() === 'currency') {
$fieldValue = Vtiger_Currency_UIType::convertToDBFormat($fieldValue);
} elseif ($fieldModel->getFieldDataType() === 'date') {
$fieldValue = DateTimeField::convertToDBFormat($fieldValue);
} elseif ($fieldModel->getFieldDataType() === 'reference' && $fieldValue) {
$ids = vtws_getIdComponents($fieldValue);
if (count($ids) === 1) {
$fieldValue = vtws_getWebserviceEntityId(getSalesEntityType($fieldValue), $fieldValue);
}
}
$entityValues['entities'][$module][$fieldName] = $fieldValue;
}
}
}
try {
$result = vtws_convertlead($entityValues, $currentUser);
} catch (Exception $e) {
$this->showError($request, $e);
exit;
}
if (!empty($result['Accounts'])) {
$accountIdComponents = vtws_getIdComponents($result['Accounts']);
$accountId = $accountIdComponents[1];
}
if (!empty($result['Contacts'])) {
$contactIdComponents = vtws_getIdComponents($result['Contacts']);
$contactId = $contactIdComponents[1];
}
if (!empty($accountId)) {
header("Location: index.php?view=Detail&module=Accounts&record={$accountId}");
} elseif (!empty($contactId)) {
header("Location: index.php?view=Detail&module=Contacts&record={$contactId}");
} else {
$this->showError($request);
exit;
}
}
function vtSaveTask($adb, $request)
{
$util = new VTWorkflowUtils();
$module = new VTWorkflowApplication("savetask");
$mod = return_module_language($current_language, $module->name);
if (!$util->checkAdminAccess()) {
$errorUrl = $module->errorPageUrl($mod['LBL_ERROR_NOT_ADMIN']);
$util->redirectTo($errorUrl, $mod['LBL_ERROR_NOT_ADMIN']);
return;
}
$tm = new VTTaskManager($adb);
if (isset($request["task_id"])) {
$task = $tm->retrieveTask($request["task_id"]);
} else {
$taskType = vtlib_purifyForSql($request["task_type"]);
$workflowId = $request["workflow_id"];
$task = $tm->createTask($taskType, $workflowId);
}
$task->summary = $request["summary"];
if ($request["active"] == "true") {
$task->active = true;
} else {
if ($request["active"] == "false") {
$task->active = false;
}
}
if (isset($request['check_select_date'])) {
$trigger = array('days' => ($request['select_date_direction'] == 'after' ? 1 : -1) * (int) $request['select_date_days'], 'field' => $request['select_date_field']);
$task->trigger = $trigger;
} else {
$task->trigger = null;
}
$fieldNames = $task->getFieldNames();
foreach ($fieldNames as $fieldName) {
$task->{$fieldName} = $request[$fieldName];
if ($fieldName == 'calendar_repeat_limit_date') {
$task->{$fieldName} = DateTimeField::convertToDBFormat($request[$fieldName]);
}
}
$tm->saveTask($task);
if (isset(vtlib_purify($request["return_url"]))) {
$returnUrl = vtlib_purify($request["return_url"]);
} else {
$returnUrl = $module->editTaskUrl($task->id);
}
?>
<script type="text/javascript" charset="utf-8">
window.location="<?php
echo $returnUrl;
?>
";
</script>
<a href="<?php
echo $returnUrl;
?>
">Return</a>
<?php
}
/**
* Function to save the Scheduled Reports data
*/
public function saveScheduleReport() {
$adb = PearDatabase::getInstance();
$reportid = $this->get('reportid');
$scheduleid = $this->get('scheduleid');
$schtime = $this->get('schtime');
if(!preg_match('/^[0-2]\d(:[0-5]\d){1,2}$/', $schtime) or substr($schtime,0,2)>23) { // invalid time format
$schtime='00:00';
}
$schtime .=':00';
$schdate = null; $schdayoftheweek = null; $schdayofthemonth = null; $schannualdates = null;
if ($scheduleid == self::$SCHEDULED_ON_SPECIFIC_DATE) {
$date = $this->get('schdate');
$dateDBFormat = DateTimeField::convertToDBFormat($date);
$nextTriggerTime = $dateDBFormat.' '.$schtime;
$currentTime = Vtiger_Util_Helper::getActiveAdminCurrentDateTime();
if($nextTriggerTime > $currentTime) {
$this->set('next_trigger_time', $nextTriggerTime);
} else {
$this->set('next_trigger_time', date('Y-m-d H:i:s', strtotime('+10 year')));
}
$schdate = Zend_Json::encode(array($dateDBFormat));
} else if ($scheduleid == self::$SCHEDULED_WEEKLY) {
$schdayoftheweek = Zend_Json::encode($this->get('schdayoftheweek'));
$this->set('schdayoftheweek', $schdayoftheweek);
} else if ($scheduleid == self::$SCHEDULED_MONTHLY_BY_DATE) {
$schdayofthemonth = Zend_Json::encode($this->get('schdayofthemonth'));
$this->set('schdayofthemonth', $schdayofthemonth);
} else if ($scheduleid == self::$SCHEDULED_ANNUALLY) {
$schannualdates = Zend_Json::encode($this->get('schannualdates'));
$this->set('schannualdates', $schannualdates);
}
$recipients = Zend_Json::encode($this->get('recipients'));
$specificemails = Zend_Json::encode($this->get('specificemails'));
$isReportScheduled = $this->get('isReportScheduled');
if($scheduleid != self::$SCHEDULED_ON_SPECIFIC_DATE) {
$nextTriggerTime = $this->getNextTriggerTime();
}
if ($isReportScheduled == '0' || $isReportScheduled == '' || $isReportScheduled == false) {
$deleteScheduledReportSql = "DELETE FROM vtiger_schedulereports WHERE reportid=?";
$adb->pquery($deleteScheduledReportSql, array($reportid));
} else {
$checkScheduledResult = $adb->pquery('SELECT 1 FROM vtiger_schedulereports WHERE reportid=?', array($reportid));
if ($adb->num_rows($checkScheduledResult) > 0) {
$scheduledReportSql = 'UPDATE vtiger_schedulereports SET scheduleid=?, recipients=?, schdate=?, schtime=?, schdayoftheweek=?, schdayofthemonth=?, schannualdates=?, specificemails=?, next_trigger_time=? WHERE reportid=?';
$adb->pquery($scheduledReportSql, array($scheduleid, $recipients, $schdate, $schtime, $schdayoftheweek, $schdayofthemonth, $schannualdates, $specificemails, $nextTriggerTime, $reportid));
} else {
$scheduleReportSql = 'INSERT INTO vtiger_schedulereports (reportid,scheduleid,recipients,schdate,schtime,schdayoftheweek,schdayofthemonth,schannualdates,next_trigger_time,specificemails) VALUES (?,?,?,?,?,?,?,?,?,?)';
$adb->pquery($scheduleReportSql, array($reportid, $scheduleid, $recipients, $schdate, $schtime, $schdayoftheweek, $schdayofthemonth, $schannualdates, $nextTriggerTime, $specificemails));
}
}
}
/**
* @param $value
* @param $name
* @param $type
* @param \Workflow\VTEntity $context
* @return \type
*/
public function getValue($value, $name, $type, $context)
{
if ($type == 'date') {
$value = \DateTimeField::convertToDBFormat($value);
}
if ($type == 'file') {
// var_dump($value, $name, $type, $_FILES, $this);
$context->addTempFile($_FILES['fileUpload']['tmp_name'][$name], $value, $_FILES['fileUpload']['name'][$name]);
return '1';
}
return $value;
}
function track_email($user_name, $password, $contact_ids, $date_sent, $email_subject, $email_body)
{
if (authentication($user_name, $password)) {
global $current_user;
global $adb;
global $log;
require_once 'modules/Users/Users.php';
require_once 'modules/Emails/Emails.php';
$current_user = new Users();
$user_id = $current_user->retrieve_user_id($user_name);
$query = "select email1 from vtiger_users where id =?";
$result = $adb->pquery($query, array($user_id));
$user_emailid = $adb->query_result($result, 0, "email1");
$current_user = $current_user->retrieveCurrentUserInfoFromFile($user_id);
$email = new Emails();
//$log->debug($msgdtls['contactid']);
$emailbody = str_replace("'", "''", $email_body);
$emailsubject = str_replace("'", "''", $email_subject);
$datesent = substr($date_sent, 1, 10);
$mydate = date('Y-m-d', $datesent);
$mydate = DateTimeField::convertToDBFormat($mydate);
$email->column_fields[subject] = $emailsubject;
$email->column_fields[assigned_user_id] = $user_id;
$email->column_fields[date_start] = $mydate;
$email->column_fields[description] = $emailbody;
$email->column_fields[activitytype] = 'Emails';
$email->plugin_save = true;
$email->save("Emails");
$query = "select fieldid from vtiger_field where fieldname = 'email' and tabid = 4 and vtiger_field.presence in (0,2)";
$result = $adb->pquery($query, array());
$field_id = $adb->query_result($result, 0, "fieldid");
$email->set_emails_contact_invitee_relationship($email->id, $contact_ids);
$email->set_emails_se_invitee_relationship($email->id, $contact_ids);
$email->set_emails_user_invitee_relationship($email->id, $user_id);
$sql = "select email from vtiger_contactdetails inner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid where vtiger_crmentity.deleted =0 and vtiger_contactdetails.contactid=?";
$result = $adb->pquery($sql, array($contact_ids));
$camodulerow = $adb->fetch_array($result);
if (isset($camodulerow)) {
$emailid = $camodulerow["email"];
//added to save < as $lt; and > as > in the database so as to retrive the emailID
$user_emailid = str_replace('<', '<', $user_emailid);
$user_emailid = str_replace('>', '>', $user_emailid);
$query = 'insert into vtiger_emaildetails values (?,?,?,?,?,?,?,?)';
$params = array($email->id, $emailid, $user_emailid, "", "", "", $user_id . '@-1|' . $contact_ids . '@' . $field_id . '|', "THUNDERBIRD");
$adb->pquery($query, $params);
}
return $email->id;
}
}
public function process($ModuleName, $ID, $record_form, $config)
{
$projectmilestoneid = $record_form['projectmilestoneid'];
if (!isset($projectmilestoneid) || $projectmilestoneid == 0 || $projectmilestoneid == '') {
return array('save_record' => true);
}
$moduleModel = Vtiger_Record_Model::getInstanceById($projectmilestoneid, 'ProjectMilestone');
$projectMilestoneDate = $moduleModel->get('projectmilestonedate');
if (!isset($projectMilestoneDate) || $projectMilestoneDate == 0 || $projectMilestoneDate == '') {
return array('save_record' => true);
}
$dateField = new DateTimeField($projectMilestoneDate);
$projectMilestoneDateUserFormat = $dateField->convertToUserFormat($projectMilestoneDate);
$dateField = new DateTimeField($record_form['targetenddate']);
$targetEndDateUserFormat = $dateField->convertToDBFormat($record_form['targetenddate']);
if (strtotime($targetEndDateUserFormat) > strtotime($projectMilestoneDate)) {
return array('save_record' => false, 'type' => 0, 'info' => array('text' => vtranslate('Date can not be greater', 'DataAccess') . ' ( ' . $record_form['targetenddate'] . ' < ' . $projectMilestoneDateUserFormat . ')', 'type' => 'error'));
} else {
return array('save_record' => true);
}
}
$focus->retrieve_entity_info($record, 'Invoice');
$focus->name = $focus->column_fields['subject'];
}
} else {
if (isset($_REQUEST['convertmode']) && $_REQUEST['convertmode'] == 'update_so_val') {
//Updating the Selected SO Value in Create Mode
foreach ($focus->column_fields as $fieldname => $val) {
if (isset($_REQUEST[$fieldname])) {
$value = $_REQUEST[$fieldname];
$focus->column_fields[$fieldname] = $value;
}
}
//Handling for dateformat in invoicedate vtiger_field
if ($focus->column_fields['invoicedate'] != '') {
$curr_due_date = $focus->column_fields['invoicedate'];
$focus->column_fields['invoicedate'] = DateTimeField::convertToDBFormat($curr_due_date);
}
$soid = $focus->column_fields['salesorder_id'];
$so_focus = new SalesOrder();
$so_focus->id = $soid;
$so_focus->retrieve_entity_info($soid, "SalesOrder");
$focus = getConvertSoToInvoice($focus, $so_focus, $soid);
// Reset the value w.r.t SalesOrder Selected
$currencyid = $so_focus->column_fields['currency_id'];
$rate = $so_focus->column_fields['conversion_rate'];
//Added to display the SO's associated products -- when we select SO in New Invoice page
if (isset($_REQUEST['salesorder_id']) && $_REQUEST['salesorder_id'] != '') {
$associated_prod = getAssociatedProducts("SalesOrder", $so_focus, $focus->column_fields['salesorder_id']);
}
$smarty->assign("SALESORDER_ID", $focus->column_fields['salesorder_id']);
$smarty->assign("ASSOCIATEDPRODUCTS", $associated_prod);
if ($_REQUEST[$allKeys[$i]] != "") {
$columnslist[] = $_REQUEST[$allKeys[$i]];
}
}
}
//<<<<<<<columns>>>>>>>>>
//<<<<<<<standardfilters>>>>>>>>>
$stdfiltercolumn = $_REQUEST["stdDateFilterField"];
$std_filter_list["columnname"] = $stdfiltercolumn;
$stdcriteria = $_REQUEST["stdDateFilter"];
$std_filter_list["stdfilter"] = $stdcriteria;
$startdate = $_REQUEST["startdate"];
$enddate = $_REQUEST["enddate"];
if ($stdcriteria == "custom") {
$startdate = DateTimeField::convertToDBFormat($startdate);
$enddate = DateTimeField::convertToDBFormat($enddate);
}
$std_filter_list["startdate"] = $startdate;
$std_filter_list["enddate"] = $enddate;
if (empty($startdate) && empty($enddate)) {
unset($std_filter_list);
}
//<<<<<<<standardfilters>>>>>>>>>
//<<<<<<<advancedfilter>>>>>>>>>
$json = new Zend_Json();
$advft_criteria = $_REQUEST['advft_criteria'];
$advft_criteria = $json->decode($advft_criteria);
$advft_criteria_groups = $_REQUEST['advft_criteria_groups'];
$advft_criteria_groups = $json->decode($advft_criteria_groups);
//<<<<<<<advancedfilter>>>>>>>>
$moduleHandler = vtws_getModuleHandlerFromName($cvmodule, $current_user);
public function process(Vtiger_Request $request)
{
$recordId = $request->get('record');
$summary = $request->get('summary');
$moduleName = $request->get('module_name');
$conditions = $request->get('conditions');
$filterSavedInNew = $request->get('filtersavedinnew');
$executionCondition = $request->get('execution_condition');
if ($recordId) {
$workflowModel = Settings_Workflows_Record_Model::getInstance($recordId);
} else {
$workflowModel = Settings_Workflows_Record_Model::getCleanInstance($moduleName);
}
$response = new Vtiger_Response();
$workflowModel->set('summary', $summary);
$workflowModel->set('module_name', $moduleName);
$workflowModel->set('conditions', $conditions);
$workflowModel->set('execution_condition', $executionCondition);
if ($executionCondition == '6') {
$schtime = $request->get("schtime");
if (!preg_match('/^[0-2]\\d(:[0-5]\\d){1,2}$/', $schtime) or substr($schtime, 0, 2) > 23) {
// invalid time format
$schtime = '00:00';
}
$schtime .= ':00';
$workflowModel->set('schtime', $schtime);
$workflowScheduleType = $request->get('schtypeid');
$workflowModel->set('schtypeid', $workflowScheduleType);
$dayOfMonth = null;
$dayOfWeek = null;
$month = null;
$annualDates = null;
if ($workflowScheduleType == Workflow::$SCHEDULED_WEEKLY) {
$dayOfWeek = Zend_Json::encode($request->get('schdayofweek'));
} else {
if ($workflowScheduleType == Workflow::$SCHEDULED_MONTHLY_BY_DATE) {
$dayOfMonth = Zend_Json::encode($request->get('schdayofmonth'));
} else {
if ($workflowScheduleType == Workflow::$SCHEDULED_ON_SPECIFIC_DATE) {
$date = $request->get('schdate');
$dateDBFormat = DateTimeField::convertToDBFormat($date);
$nextTriggerTime = $dateDBFormat . ' ' . $schtime;
$currentTime = Vtiger_Util_Helper::getActiveAdminCurrentDateTime();
if ($nextTriggerTime > $currentTime) {
$workflowModel->set('nexttrigger_time', $nextTriggerTime);
} else {
$workflowModel->set('nexttrigger_time', date('Y-m-d H:i:s', strtotime('+10 year')));
}
$annualDates = Zend_Json::encode(array($dateDBFormat));
} else {
if ($workflowScheduleType == Workflow::$SCHEDULED_ANNUALLY) {
$annualDates = Zend_Json::encode($request->get('schannualdates'));
}
}
}
}
$workflowModel->set('schdayofmonth', $dayOfMonth);
$workflowModel->set('schdayofweek', $dayOfWeek);
$workflowModel->set('schannualdates', $annualDates);
}
// Added to save the condition only when its changed from vtiger6
if ($filterSavedInNew == '6') {
//Added to change advanced filter condition to workflow
$workflowModel->transformAdvanceFilterToWorkFlowFilter();
}
$workflowModel->set('filtersavedinnew', $filterSavedInNew);
$workflowModel->save();
//Update only for scheduled workflows other than specific date
if ($workflowScheduleType != Workflow::$SCHEDULED_ON_SPECIFIC_DATE && $executionCondition == '6') {
$workflowModel->updateNextTriggerTime();
}
$response->setResult(array('id' => $workflowModel->get('workflow_id')));
$response->emit();
}
/**
* This function is used to get the days in between the current time and the modified time of an entity .
* Takes the input parameter as $id - crmid it will calculate the number of days in between the
* the current time and the modified time from the vtiger_crmentity vtiger_table and return the result as a string.
* The return format is updated <No of Days> day ago <(date when updated)>
*/
function updateInfo($id)
{
global $log;
$log->debug("Entering updateInfo(" . $id . ") method ...");
global $adb;
global $app_strings;
$query = 'SELECT modifiedtime, modifiedby FROM vtiger_crmentity WHERE crmid = ?';
$result = $adb->pquery($query, array($id));
$modifiedtime = $adb->query_result($result, 0, 'modifiedtime');
$modifiedby_id = $adb->query_result($result, 0, 'modifiedby');
$modifiedby = $app_strings['LBL_BY'] . getOwnerName($modifiedby_id);
$date = new DateTimeField($modifiedtime);
$modifiedtime = DateTimeField::convertToDBFormat($date->getDisplayDate());
$current_time = date('Y-m-d H:i:s');
$values = explode(' ', $modifiedtime);
$date_info = explode('-', $values[0]);
$time_info = explode(':', $values[1]);
$date = $date_info[2] . ' ' . $app_strings[date("M", mktime(0, 0, 0, $date_info[1], $date_info[2], $date_info[0]))] . ' ' . $date_info[0];
$time_modified = strtotime($modifiedtime);
$time_now = strtotime($current_time);
$days_diff = (int) (($time_now - $time_modified) / (60 * 60 * 24));
if ($days_diff == 0) {
$update_info = $app_strings['LBL_UPDATED_TODAY'] . " (" . $date . ")" . ' ' . $modifiedby;
} elseif ($days_diff == 1) {
$update_info = $app_strings['LBL_UPDATED'] . " " . $days_diff . " " . $app_strings['LBL_DAY_AGO'] . " (" . $date . ")" . ' ' . $modifiedby;
} else {
$update_info = $app_strings['LBL_UPDATED'] . " " . $days_diff . " " . $app_strings['LBL_DAYS_AGO'] . " (" . $date . ")" . ' ' . $modifiedby;
}
$log->debug("Exiting updateInfo method ...");
return $update_info;
}
if (isset($_REQUEST['month']) && is_numeric(vtlib_purify($_REQUEST['month']))) {
$date_data['month'] = vtlib_purify($_REQUEST['month']);
}
if (isset($_REQUEST['week']) && is_numeric(vtlib_purify($_REQUEST['week']))) {
$date_data['week'] = vtlib_purify($_REQUEST['week']);
}
if (isset($_REQUEST['year']) && is_numeric(vtlib_purify($_REQUEST['year']))) {
if ($_REQUEST['year'] > 2037 || $_REQUEST['year'] < 1970) {
print "<font color='red'>" . $app_strings['LBL_CAL_LIMIT_MSG'] . "</font>";
exit;
}
$date_data['year'] = vtlib_purify($_REQUEST['year']);
}
if (empty($date_data)) {
$dateTimeField = new DateTimeField('');
$dateValue = $dateTimeField->getDisplayDate();
$timeValue = $dateTimeField->getDisplayTime();
$dbDateValue = DateTimeField::convertToDBFormat($dateValue);
$dateValueArray = explode('-', $dbDateValue);
$timeValueArray = explode(':', $timeValue);
$date_data = array('day' => $dateValueArray[2], 'month' => $dateValueArray[1], 'year' => $dateValueArray[0], 'hour' => $timeValueArray[0], 'min' => $timeValueArray[1]);
}
$calendar_arr['calendar'] = new Calendar($mysel, $date_data);
if ($current_user->hour_format != '') {
$calendar_arr['calendar']->hour_format = $current_user->hour_format;
}
if ($viewBox == 'hourview' && ($mysel == 'day' || $mysel == 'week' || $mysel == 'month' || $mysel == 'year')) {
$calendar_arr['calendar']->add_Activities($current_user);
}
$calendar_arr['view'] = $mysel;
calendar_layout($calendar_arr, $viewBox, $subtab);
function generateAdvFilterSql($advfilterlist)
{
$adb = PearDatabase::getInstance();
$advfiltersql = "";
$customView = new CustomView();
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advfilterlist as $groupindex => $groupinfo) {
$groupcondition = $groupinfo['condition'];
$groupcolumns = $groupinfo['columns'];
if (count($groupcolumns) > 0) {
$advfiltergroupsql = "";
foreach ($groupcolumns as $columnindex => $columninfo) {
$fieldcolname = $columninfo["columnname"];
$comparator = $columninfo["comparator"];
$value = $columninfo["value"];
$columncondition = $columninfo["column_condition"];
$advcolsql = array();
if ($fieldcolname != "" && $comparator != "") {
if (in_array($comparator, $dateSpecificConditions)) {
if ($fieldcolname != 'none') {
$selectedFields = explode(':', $fieldcolname);
if ($selectedFields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$selectedFields[0] = 'vtiger_crmentity';
}
if ($comparator != 'custom') {
list($startDate, $endDate) = $this->getStandarFiltersStartAndEndDate($comparator);
} else {
list($startDateTime, $endDateTime) = explode(',', $value);
list($startDate, $startTime) = explode(' ', $startDateTime);
list($endDate, $endTime) = explode(' ', $endDateTime);
}
$type = $selectedFields[4];
if ($startDate != '0000-00-00' && $endDate != '0000-00-00' && $startDate != '' && $endDate != '') {
$startDateTime = new DateTimeField($startDate . ' ' . date('H:i:s'));
$userStartDate = $startDateTime->getDisplayDate();
if ($type == 'DT') {
$userStartDate = $userStartDate . ' 00:00:00';
}
$startDateTime = getValidDBInsertDateTimeValue($userStartDate);
$endDateTime = new DateTimeField($endDate . ' ' . date('H:i:s'));
$userEndDate = $endDateTime->getDisplayDate();
if ($type == 'DT') {
$userEndDate = $userEndDate . ' 23:59:59';
}
$endDateTime = getValidDBInsertDateTimeValue($userEndDate);
if ($selectedFields[1] == 'birthday') {
$tableColumnSql = 'DATE_FORMAT(' . $selectedFields[0] . '.' . $selectedFields[1] . ', "%m%d")';
$startDateTime = "DATE_FORMAT('{$startDateTime}', '%m%d')";
$endDateTime = "DATE_FORMAT('{$endDateTime}', '%m%d')";
} else {
if ($selectedFields[0] == 'vtiger_activity' && $selectedFields[1] == 'date_start') {
$tableColumnSql = 'CAST((CONCAT(date_start, " ", time_start)) AS DATETIME)';
} else {
$tableColumnSql = $selectedFields[0] . '.' . $selectedFields[1];
}
$startDateTime = "'{$startDateTime}'";
$endDateTime = "'{$endDateTime}'";
}
$advfiltergroupsql .= "{$tableColumnSql} BETWEEN {$startDateTime} AND {$endDateTime}";
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
$this->queryPlanner->addTable($selectedFields[0]);
}
}
continue;
}
$selectedFields = explode(":", $fieldcolname);
$tempComparators = array('e', 'n', 'bw', 'a', 'b');
if ($selectedFields[4] == 'DT' && in_array($comparator, $tempComparators)) {
if ($selectedFields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$selectedFields[0] = 'vtiger_crmentity';
}
if ($selectedFields[0] == 'vtiger_activity' && $selectedFields[1] == 'date_start') {
$tableColumnSql = 'CAST((CONCAT(date_start, " ", time_start)) AS DATETIME)';
} else {
$tableColumnSql = $selectedFields[0] . '.' . $selectedFields[1];
}
if ($value != null && $value != '') {
if ($comparator == 'e' || $comparator == 'n') {
$dateTimeComponents = explode(' ', $value);
$dateTime = new DateTime($dateTimeComponents[0] . ' ' . '00:00:00');
$date1 = $dateTime->format('Y-m-d H:i:s');
$dateTime->modify("+1 days");
$date2 = $dateTime->format('Y-m-d H:i:s');
$tempDate = strtotime($date2) - 1;
$date2 = date('Y-m-d H:i:s', $tempDate);
$start = getValidDBInsertDateTimeValue($date1);
$end = getValidDBInsertDateTimeValue($date2);
$start = "'{$start}'";
$end = "'{$end}'";
if ($comparator == 'e') {
$advfiltergroupsql .= "{$tableColumnSql} BETWEEN {$start} AND {$end}";
} else {
$advfiltergroupsql .= "{$tableColumnSql} NOT BETWEEN {$start} AND {$end}";
}
} else {
if ($comparator == 'bw') {
$values = explode(',', $value);
$startDateTime = explode(' ', $values[0]);
$endDateTime = explode(' ', $values[1]);
$startDateTime = new DateTimeField($startDateTime[0] . ' ' . date('H:i:s'));
$userStartDate = $startDateTime->getDisplayDate();
$userStartDate = $userStartDate . ' 00:00:00';
$start = getValidDBInsertDateTimeValue($userStartDate);
$endDateTime = new DateTimeField($endDateTime[0] . ' ' . date('H:i:s'));
$userEndDate = $endDateTime->getDisplayDate();
$userEndDate = $userEndDate . ' 23:59:59';
$end = getValidDBInsertDateTimeValue($userEndDate);
$advfiltergroupsql .= "{$tableColumnSql} BETWEEN '{$start}' AND '{$end}'";
} else {
if ($comparator == 'a' || $comparator == 'b') {
$value = explode(' ', $value);
$dateTime = new DateTime($value[0]);
if ($comparator == 'a') {
$modifiedDate = $dateTime->modify('+1 days');
$nextday = $modifiedDate->format('Y-m-d H:i:s');
$temp = strtotime($nextday) - 1;
$date = date('Y-m-d H:i:s', $temp);
$value = getValidDBInsertDateTimeValue($date);
$advfiltergroupsql .= "{$tableColumnSql} > '{$value}'";
} else {
$prevday = $dateTime->format('Y-m-d H:i:s');
$temp = strtotime($prevday) - 1;
$date = date('Y-m-d H:i:s', $temp);
$value = getValidDBInsertDateTimeValue($date);
$advfiltergroupsql .= "{$tableColumnSql} < '{$value}'";
}
}
}
}
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
$this->queryPlanner->addTable($selectedFields[0]);
}
continue;
}
$selectedfields = explode(":", $fieldcolname);
$moduleFieldLabel = $selectedfields[2];
list($moduleName, $fieldLabel) = explode('__', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
$concatSql = getSqlForNameInDisplayFormat(array('first_name' => $selectedfields[0] . ".first_name", 'last_name' => $selectedfields[0] . ".last_name"), 'Users');
// Added to handle the crmentity table name for Primary module
if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$selectedfields[0] = "vtiger_crmentity";
}
//Added to handle yes or no for checkbox field in reports advance filters. -shahul
if ($selectedfields[4] == 'C') {
if (strcasecmp(trim($value), "yes") == 0) {
$value = "1";
}
if (strcasecmp(trim($value), "no") == 0) {
$value = "0";
}
}
if (in_array($comparator, $dateSpecificConditions)) {
$customView = new CustomView($moduleName);
$columninfo['stdfilter'] = $columninfo['comparator'];
$valueComponents = explode(',', $columninfo['value']);
if ($comparator == 'custom') {
if ($selectedfields[4] == 'DT') {
$startDateTimeComponents = explode(' ', $valueComponents[0]);
$endDateTimeComponents = explode(' ', $valueComponents[1]);
$columninfo['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]);
$columninfo['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]);
} else {
$columninfo['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$columninfo['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($columninfo);
$startDate = DateTimeField::convertToDBFormat($dateFilterResolvedList['startdate']);
$endDate = DateTimeField::convertToDBFormat($dateFilterResolvedList['enddate']);
$columninfo['value'] = $value = implode(',', array($startDate, $endDate));
$comparator = 'bw';
}
$valuearray = explode(",", trim($value));
$datatype = isset($selectedfields[4]) ? $selectedfields[4] : "";
if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') {
$advcolumnsql = "";
for ($n = 0; $n < count($valuearray); $n++) {
if (($selectedfields[0] == "vtiger_users" . $this->primarymodule || $selectedfields[0] == "vtiger_users" . $this->secondarymodule) && $selectedfields[1] == 'user_name') {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$advcolsql[] = " (trim({$concatSql})" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype) . " or vtiger_groups" . $module_from_tablename . ".groupname " . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype) . ")";
$this->queryPlanner->addTable("vtiger_groups" . $module_from_tablename);
} elseif ($selectedfields[1] == 'status') {
//when you use comma seperated values.
if ($selectedfields[2] == 'Calendar_Status') {
$advcolsql[] = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
if ($selectedfields[2] == 'HelpDesk_Status') {
$advcolsql[] = "vtiger_troubletickets.status" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
if ($selectedfields[2] == 'Faq_Status') {
$advcolsql[] = "vtiger_faq.status" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
}
}
} elseif ($selectedfields[1] == 'description') {
//when you use comma seperated values.
if ($selectedfields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$advcolsql[] = "vtiger_crmentity.description" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
} elseif ($selectedfields[2] == 'Quotes_Inventory_Manager') {
$advcolsql[] = "trim({$concatSql})" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} elseif ($selectedfields[1] == 'modifiedby') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_lastModifiedBy' . $module_from_tablename;
} else {
$tableName = 'vtiger_lastModifiedBy' . $this->primarymodule;
}
$advcolsql[] = 'trim(' . getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . ')' . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
}
//If negative logic filter ('not equal to', 'does not contain') is used, 'and' condition should be applied instead of 'or'
if ($comparator == 'n' || $comparator == 'k') {
$advcolumnsql = implode(" and ", $advcolsql);
} else {
$advcolumnsql = implode(" or ", $advcolsql);
}
$fieldvalue = " (" . $advcolumnsql . ") ";
} elseif ($selectedfields[1] == 'user_name') {
if ($selectedfields[0] == "vtiger_users" . $this->primarymodule) {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$fieldvalue = " trim(case when (" . $selectedfields[0] . ".last_name NOT LIKE '') then " . $concatSql . " else vtiger_groups" . $module_from_tablename . ".groupname end) " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_groups" . $module_from_tablename);
} else {
$secondaryModules = explode(':', $this->secondarymodule);
$firstSecondaryModule = "vtiger_users" . $secondaryModules[0];
$secondSecondaryModule = "vtiger_users" . $secondaryModules[1];
if ($firstSecondaryModule && $firstSecondaryModule == $selectedfields[0] || $secondSecondaryModule && $secondSecondaryModule == $selectedfields[0]) {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$moduleInstance = CRMEntity::getInstance($module_from_tablename);
$fieldvalue = " trim(case when (" . $selectedfields[0] . ".last_name NOT LIKE '') then " . $concatSql . " else vtiger_groups" . $module_from_tablename . ".groupname end) " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_groups" . $module_from_tablename);
$this->queryPlanner->addTable($moduleInstance->table_name);
}
}
} elseif ($comparator == 'bw' && count($valuearray) == 2) {
if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$fieldvalue = "(" . "vtiger_crmentity." . $selectedfields[1] . " between '" . trim($valuearray[0]) . "' and '" . trim($valuearray[1]) . "')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " between '" . trim($valuearray[0]) . "' and '" . trim($valuearray[1]) . "')";
}
} elseif ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$fieldvalue = "vtiger_crmentity." . $selectedfields[1] . " " . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[2] == 'Quotes_Inventory_Manager') {
$fieldvalue = "trim({$concatSql})" . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[1] == 'modifiedby') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_lastModifiedBy' . $module_from_tablename;
} else {
$tableName = 'vtiger_lastModifiedBy' . $this->primarymodule;
}
$this->queryPlanner->addTable($tableName);
$fieldvalue = 'trim(' . getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . ')' . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[1] == 'smcreatorid') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_createdby' . $module_from_tablename;
} else {
$tableName = 'vtiger_createdby' . $this->primarymodule;
}
if ($moduleName == 'ModComments') {
$tableName = 'vtiger_users' . $moduleName;
}
$this->queryPlanner->addTable($tableName);
$fieldvalue = 'trim(' . getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . ')' . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[0] == "vtiger_activity" && ($selectedfields[1] == 'status' || $selectedfields[1] == 'eventstatus')) {
// for "Is Empty" condition we need to check with "value NOT NULL" OR "value = ''" conditions
if ($comparator == 'y') {
$fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status\n else vtiger_activity.eventstatus end) IS NULL OR (case when (vtiger_activity.status not like '')\n then vtiger_activity.status else vtiger_activity.eventstatus end) = ''";
} else {
$fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status\n else vtiger_activity.eventstatus end)" . $this->getAdvComparator($comparator, trim($value), $datatype);
}
} else {
if ($comparator == 'ny') {
if ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NOT NULL AND " . $selectedfields[0] . "." . $selectedfields[1] . " != '' AND " . $selectedfields[0] . "." . $selectedfields[1] . " != '0')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NOT NULL AND " . $selectedfields[0] . "." . $selectedfields[1] . " != '')";
}
} elseif ($comparator == 'y' || $comparator == 'e' && (trim($value) == "NULL" || trim($value) == '')) {
if ($selectedfields[0] == 'vtiger_inventoryproductrel') {
$selectedfields[0] = 'vtiger_inventoryproductrel' . $moduleName;
}
if ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NULL OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '' OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '0')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NULL OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '')";
}
} elseif ($selectedfields[0] == 'vtiger_inventoryproductrel') {
if ($selectedfields[1] == 'productid') {
$fieldvalue = "vtiger_products{$moduleName}.productname " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_products{$moduleName}");
} else {
if ($selectedfields[1] == 'serviceid') {
$fieldvalue = "vtiger_service{$moduleName}.servicename " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_service{$moduleName}");
} else {
//for inventory module table should be follwed by the module name
$selectedfields[0] = 'vtiger_inventoryproductrel' . $moduleName;
$fieldvalue = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, $value, $datatype);
}
}
} elseif ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo);
$comparatorValue = $this->getAdvComparator($comparator, trim($value), $datatype, $fieldSqlColumns[0]);
$fieldSqls = array();
foreach ($fieldSqlColumns as $columnSql) {
$fieldSqls[] = $columnSql . $comparatorValue;
}
$fieldvalue = ' (' . implode(' OR ', $fieldSqls) . ') ';
} else {
$fieldvalue = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($value), $datatype);
}
}
$advfiltergroupsql .= $fieldvalue;
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
$this->queryPlanner->addTable($selectedfields[0]);
}
}
if (trim($advfiltergroupsql) != "") {
$advfiltergroupsql = "( {$advfiltergroupsql} ) ";
if (!empty($groupcondition)) {
$advfiltergroupsql .= ' ' . $groupcondition . ' ';
}
$advfiltersql .= $advfiltergroupsql;
}
}
}
if (trim($advfiltersql) != "") {
$advfiltersql = '(' . $advfiltersql . ')';
}
return $advfiltersql;
}
public function parseAdvFilterList($advFilterList, $glue = '')
{
if (!empty($glue)) {
$this->addConditionGlue($glue);
}
$customView = new CustomView($this->module);
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advFilterList as $groupindex => $groupcolumns) {
$filtercolumns = $groupcolumns['columns'];
if (count($filtercolumns) > 0) {
$this->startGroup('');
foreach ($filtercolumns as $index => $filter) {
$nameComponents = explode(':', $filter['columnname']);
// For Events "End Date & Time" field datatype should be DT. But, db will give D for due_date field
if ($nameComponents[2] == 'due_date' && $nameComponents[3] == 'Events_End_Date_&_Time') {
$nameComponents[4] = 'DT';
}
if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
$name = $this->getSQLColumn('id');
} else {
$name = $nameComponents[2];
}
if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
$filter['stdfilter'] = $filter['comparator'];
$valueComponents = explode(',', $filter['value']);
if ($filter['comparator'] == 'custom') {
if ($nameComponents[4] == 'DT') {
$startDateTimeComponents = explode(' ', $valueComponents[0]);
$endDateTimeComponents = explode(' ', $valueComponents[1]);
$filter['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]);
} else {
$filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
// If datatype is DT then we should append time also
if ($nameComponents[4] == 'DT') {
$startdate = explode(' ', $dateFilterResolvedList['startdate']);
if ($startdate[1] == '') {
$startdate[1] = '00:00:00';
}
$dateFilterResolvedList['startdate'] = $startdate[0] . ' ' . $startdate[1];
$enddate = explode(' ', $dateFilterResolvedList['enddate']);
if ($enddate[1] == '') {
$enddate[1] = '23:59:59';
}
$dateFilterResolvedList['enddate'] = $enddate[0] . ' ' . $enddate[1];
}
$value = array();
$value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
$value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
$this->addCondition($name, $value, 'BETWEEN');
} else {
if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'e' || $filter['comparator'] == 'n')) {
$filter['stdfilter'] = $filter['comparator'];
$dateTimeComponents = explode(' ', $filter['value']);
$filter['startdate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]);
$startDate = $this->fixDateTimeValue($name, $filter['startdate']);
$endDate = $this->fixDateTimeValue($name, $filter['enddate'], false);
$value = array();
$start = explode(' ', $startDate);
if ($start[1] == "") {
$startDate = $start[0] . ' ' . '00:00:00';
}
$end = explode(' ', $endDate);
if ($end[1] == "") {
$endDate = $end[0] . ' ' . '23:59:59';
}
$value[] = $startDate;
$value[] = $endDate;
if ($filter['comparator'] == 'n') {
$this->addCondition($name, $value, 'NOTEQUAL');
} else {
$this->addCondition($name, $value, 'BETWEEN');
}
} else {
if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'a' || $filter['comparator'] == 'b')) {
$dateTime = explode(' ', $filter['value']);
$date = DateTimeField::convertToDBFormat($dateTime[0]);
$value = array();
$value[] = $this->fixDateTimeValue($name, $date, false);
// Still fixDateTimeValue returns only date value, we need to append time because it is DT type
for ($i = 0; $i < count($value); $i++) {
$values = explode(' ', $value[$i]);
if ($values[1] == '') {
$values[1] = '00:00:00';
}
$value[$i] = $values[0] . ' ' . $values[1];
}
$this->addCondition($name, $value, $filter['comparator']);
} else {
$this->addCondition($name, $filter['value'], $filter['comparator']);
}
}
}
$columncondition = $filter['column_condition'];
if (!empty($columncondition)) {
$this->addConditionGlue($columncondition);
}
}
$this->endGroup();
$groupConditionGlue = $groupcolumns['condition'];
if (!empty($groupConditionGlue)) {
$this->addConditionGlue($groupConditionGlue);
}
}
}
}
function vtWorkflowSave($adb, $request)
{
$util = new VTWorkflowUtils();
$module = new VTWorkflowApplication("saveworkflow");
$mod = return_module_language($current_language, $module->name);
$request = vtlib_purify($request);
// this cleans all values of the array
if (!$util->checkAdminAccess()) {
$errorUrl = $module->errorPageUrl($mod['LBL_ERROR_NOT_ADMIN']);
$util->redirectTo($errorUrl, $mod['LBL_ERROR_NOT_ADMIN']);
return;
}
$description = from_html($request["description"]);
$moduleName = $request["module_name"];
$conditions = $request["conditions"];
$taskId = $request["task_id"];
$saveType = $request["save_type"];
$executionCondition = $request['execution_condition'];
$schdayofweek = array();
if (isset($request['sun_flag']) && $_REQUEST['sun_flag'] != null) {
$schdayofweek[] = 1;
}
if (isset($request['mon_flag']) && $_REQUEST['mon_flag'] != null) {
$schdayofweek[] = 2;
}
if (isset($request['tue_flag']) && $_REQUEST['tue_flag'] != null) {
$schdayofweek[] = 3;
}
if (isset($request['wed_flag']) && $_REQUEST['wed_flag'] != null) {
$schdayofweek[] = 4;
}
if (isset($request['thu_flag']) && $_REQUEST['thu_flag'] != null) {
$schdayofweek[] = 5;
}
if (isset($request['fri_flag']) && $_REQUEST['fri_flag'] != null) {
$schdayofweek[] = 6;
}
if (isset($request['sat_flag']) && $_REQUEST['sat_flag'] != null) {
$schdayofweek[] = 7;
}
// internally the code is prepared to launch the same workflow on many dates
// but the interface only sends one in
// TODO: change interface to send in many dates for annual scheduling
$schannualdates = DateTimeField::convertToDBFormat($request['schdate']);
$schannualdates = json_encode(array($schannualdates));
$schminuteinterval = $request['schminuteinterval'];
$wm = new VTWorkflowManager($adb);
if ($saveType == 'new') {
$wf = $wm->newWorkflow($moduleName);
$wf->description = $description;
$wf->test = $conditions;
$wf->taskId = $taskId;
$wf->executionConditionAsLabel($executionCondition);
$wf->schtypeid = $request['schtypeid'];
$wf->schtime = VTCreateEventTask::conv12to24hour($request['schtime']);
$wf->schdayofmonth = json_encode($request['schdayofmonth']);
$wf->schdayofweek = json_encode($schdayofweek);
$wf->schannualdates = $schannualdates;
$wf->schminuteinterval = $schminuteinterval;
$wm->save($wf);
} else {
if ($saveType == 'edit') {
$wf = $wm->retrieve($request["workflow_id"]);
$wf->description = $description;
$wf->test = $conditions;
$wf->taskId = $taskId;
$wf->executionConditionAsLabel($executionCondition);
$wf->schtypeid = $request['schtypeid'];
$wf->schtime = VTCreateEventTask::conv12to24hour($request['schtime']);
$wf->schdayofmonth = json_encode($request['schdayofmonth']);
$wf->schdayofweek = json_encode($schdayofweek);
$wf->schannualdates = $schannualdates;
$wf->schminuteinterval = $schminuteinterval;
$wm->save($wf);
} else {
throw new Exception();
}
}
if (isset($request["return_url"])) {
$returnUrl = $request["return_url"];
} else {
$returnUrl = $module->editWorkflowUrl($wf->id);
}
?>
<script type="text/javascript" charset="utf-8">
window.location="<?php
echo $returnUrl;
?>
";
</script>
<a href="<?php
echo $returnUrl;
?>
">Return</a>
<?php
}
if (in_array($adv_filter_column, $std_filter_columns)) {
$temp_val = explode("<;@STDV@;>", html_entity_decode($adv_filter_value, ENT_QUOTES, $default_charset));
$val[0] = DateTimeField::convertToDBFormat(trim($temp_val[0]));
$val[1] = DateTimeField::convertToDBFormat(trim($temp_val[1]));
$adv_filter_value = implode("<;@STDV@;>", $val);
// $adv_filter_value = html_entity_decode($adv_filter_value, ENT_QUOTES, $default_charset);
} else {
$column_info = explode(":", $adv_filter_column);
$temp_val = explode(",", $adv_filter_value);
if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $adv_filter_value != '')) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($column_info[4] == 'D') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($column_info[4] == 'DT') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$adv_filter_value = implode(",", $val);
}
}
$irelcriteriasql = "INSERT INTO its4you_reports4you_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) VALUES (?,?,?,?,?,?,?)";
$export_sql === true ? $adb->setDebug(true) : "";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($genQueryId, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
$export_sql === true ? $adb->setDebug(false) : "";
// Update the condition expression for the group to which the condition column belongs
<select name="task_assigned_group_id" class=small>';
} else {
$htmlStr .= '<span id="assign_team" style="' . $style_group . '">
<select name="assigned_group_id" class=small>';
}
$htmlStr .= getGroupslist();
$htmlStr .= '</select>
</span>';
}
return $htmlStr;
}
$date = new DateTimeField(null);
$endDate = new DateTimeField(date("Y-m-d H:i:s", time() + 1 * 24 * 60 * 60));
list($startYear, $startMonth, $startDay) = DateTimeField::convertToDBFormat($date->getDisplayDate());
list($startHour, $startMin) = explode(':', $date->getDisplayTime());
list($startYear, $startMonth, $startDay) = DateTimeField::convertToDBFormat($date->getDisplayDate());
list($startHour, $startMin) = explode(':', $date->getDisplayTime());
?>
<!-- Add Event DIV starts-->
<link rel="stylesheet" type="text/css" media="all" href="jscalendar/calendar-win2k-cold-1.css">
<script type="text/javascript" src="jscalendar/calendar.js"></script>
<script type="text/javascript" src="jscalendar/lang/calendar-<?php
echo $app_strings['LBL_JSCALENDAR_LANG'];
?>
.js"></script>
<script type="text/javascript" src="jscalendar/calendar-setup.js"></script>
<script type="text/javascript" src="include/js/ListView.js"></script>
<div class="calAddEvent layerPopup" style="display:none;width:700px;left:200px;" id="addEvent" align=center>
<form name="EditView" onSubmit="if(check_form()) { VtigerJS_DialogBox.block(); } else { return false; }" method="POST" action="index.php">
<input type="hidden" name="return_action" value="index">
/**
* Function creates HTML to display Calendar YearView
* @param array $cal - collections of objects and strings.
* return string $yearview_layout - html tags in string format
*/
function getYearViewLayout(&$cal)
{
global $mod_strings, $cal_log;
$category = getParentTab();
$cal_log->debug("Entering getYearViewLayout() method...");
$yearview_layout = '';
$yearview_layout .= '<table border="0" cellpadding="5" cellspacing="0" width="100%">';
$count = 0;
//year view divided as 4 rows and 3 columns
for ($i = 0; $i < 4; $i++) {
$yearview_layout .= '<tr>';
for ($j = 0; $j < 3; $j++) {
$cal['slice'] = $cal['calendar']->year_array[$cal['calendar']->slices[$count]];
$yearview_layout .= '<td width="33%">
<table class="mailClient " border="0" cellpadding="2" cellspacing="0" width="98%">
<tr>
<td colspan="7" class="calHdr" style="padding:5px">
<a style="text-decoration: none;" href="index.php?module=Calendar&action=index&view=month&hour=0&day=1&month=' . ($count + 1) . '&year=' . $cal['calendar']->date_time->year . '&parenttab=' . $category . '"><b>
' . $cal['slice']->start_time->month_inlong . '
</b></a>
</td>
</tr><tr class="hdrNameBg">';
for ($w = 0; $w < 7; $w++) {
$yearview_layout .= '<th width="14%">' . $mod_strings['cal_weekdays_short'][$w] . '</th>';
}
$yearview_layout .= '</tr>';
$date = DateTimeField::convertToDBFormat($cal['calendar']->month_day_slices[$count][35]);
list($_3rdyear, $_3rdmonth, $_3rddate) = explode("-", $date);
$date = DateTimeField::convertToDBFormat($cal['calendar']->month_day_slices[$count][6]);
list($_2ndyear, $_2ndmonth, $_2nddate) = explode("-", $date);
//to get no. of rows(weeks) in month
if ($_3rdmonth != $_2ndmonth) {
$rows = 5;
} else {
$rows = 6;
}
$cnt = 0;
$date_stack = array();
for ($k = 0; $k < 5; $k++) {
$yearview_layout .= '<tr>';
for ($mr = 0; $mr < 7; $mr++) {
$date = DateTimeField::convertToDBFormat($cal['calendar']->month_day_slices[$count][$cnt]);
list($_1styear, $_1stmonth, $_1stdate) = explode("-", $date);
if (count($cal['slice']->activities) != 0) {
for ($act_count = 0; $act_count < count($cal['slice']->activities); $act_count++) {
array_push($date_stack, $cal['slice']->activities[$act_count]->start_time->get_formatted_date());
}
}
if (in_array($cal['calendar']->month_day_slices[$count][$cnt], $date_stack)) {
$event_class = 'class="eventDay"';
} else {
$event_class = '';
}
if ($_1stmonth == $_2ndmonth) {
$curclass = dateCheck($cal['calendar']->month_day_slices[$count][$cnt]);
}
if ($curclass != '') {
$class = 'class="' . $curclass . '"';
$curclass = '';
} else {
$class = $event_class;
$event_class = '';
}
$date = $_1stdate + 0;
$month = $_1stmonth + 0;
$yearview_layout .= '<td ' . $class . ' style="text-align:center">';
if ($rows == 6 && $k == 0) {
$tdate = DateTimeField::convertToDBFormat($cal['calendar']->month_day_slices[$count][35 + $mr]);
list($tempyear, $tempmonth, $tempdate) = explode("-", $tdate);
if ($tempmonth == $_2ndmonth) {
$yearview_layout .= '<a href="index.php?module=Calendar&action=index&view=day&hour=0&day=' . $tempdate . '&month=' . $tempmonth . '&year=' . $tempyear . '&parenttab=' . $category . '">' . $tempdate;
}
}
if ($_1stmonth == $_2ndmonth) {
$yearview_layout .= '<a href="index.php?module=Calendar&action=index&view=day&hour=0&day=' . $date . '&month=' . $month . '&year=' . $_1styear . '&parenttab=' . $category . '">' . $date;
}
$yearview_layout .= '</a></td>';
$cnt++;
}
$yearview_layout .= '</tr>';
}
$yearview_layout .= '
</table>
';
$count++;
}
$yearview_layout .= '</tr>';
}
$yearview_layout .= '</table>';
return $yearview_layout;
$cal_log->debug("Exiting getYearViewLayout() method...");
}
/**
* This function is used to get the days in between the current time and the modified time of an entity .
* Takes the input parameter as $id - crmid it will calculate the number of days in between the
* the current time and the modified time from the vtiger_crmentity vtiger_table and return the result as a string.
* The return format is updated <No of Days> day ago <(date when updated)>
*/
function updateInfo($id)
{
global $log;
$log->debug("Entering updateInfo(" . $id . ") method ...");
global $adb;
global $app_strings;
$query = 'select modifiedtime from vtiger_crmentity where crmid = ?';
$result = $adb->pquery($query, array($id));
$modifiedtime = $adb->query_result($result, 0, 'modifiedtime');
$date = new DateTimeField($modifiedtime);
$modifiedtime = DateTimeField::convertToDBFormat($date->getDisplayDate());
$current_time = date('Y-m-d H:i:s');
$values = explode(' ', $modifiedtime);
$date_info = explode('-', $values[0]);
$time_info = explode(':', $values[1]);
// JFV - updateinfo date format
global $lang_crm;
if ($lang_crm == 'ja') {
$date = $values[0];
} else {
$date = $date_info[2] . ' ' . $app_strings[date("M", mktime(0, 0, 0, $date_info[1], $date_info[2], $date_info[0]))] . ' ' . $date_info[0];
// JFV
}
// JFV END
$time_modified = strtotime($modifiedtime);
$time_now = strtotime($current_time);
$days_diff = (int) (($time_now - $time_modified) / (60 * 60 * 24));
if ($days_diff == 0) {
$update_info = $app_strings['LBL_UPDATED_TODAY'] . " (" . $date . ")";
} elseif ($days_diff == 1) {
$update_info = $app_strings['LBL_UPDATED'] . " " . $days_diff . " " . $app_strings['LBL_DAY_AGO'] . " (" . $date . ")";
} else {
$update_info = $app_strings['LBL_UPDATED'] . " " . $days_diff . " " . $app_strings['LBL_DAYS_AGO'] . " (" . $date . ")";
}
$log->debug("Exiting updateInfo method ...");
return $update_info;
}
function getReqAdvFilterSql($reportid, $join_array)
{
$adb = PEARDatabase::getInstance();
$conditions = array();
$advfiltersql = "";
// ITS4YOU-CR SlOl 28. 3. 2014 10:34:19 to get select options array
// $ITS4YouReports = new ITS4YouReports($record);
$ITS4YouReports = $this->report_obj;
// ADV FILTER START
$ITS4YouReports->getPriModuleColumnsList($ITS4YouReports->primarymodule);
if (!empty($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule])) {
foreach ($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule] as $key => $value) {
$secondarymodules[] = $value["id"];
}
$secondary_modules_str = implode(":", $secondarymodules);
}
$ITS4YouReports->getSecModuleColumnsList($secondary_modules_str);
$sel_fields = $ITS4YouReports->adv_sel_fields;
$conditions_rc = $this->getReqConditions($sel_fields);
// ITS4YOU-END 28. 3. 2014 10:34:37
$last_gid = $l_groupcondition = "";
$std_filter_columns = $this->report_obj->getStdFilterColumns();
foreach ($conditions_rc as $groupid => $conditions) {
$d_i = 1;
$d_num_rows = count($conditions);
foreach ($conditions as $key => $d_conditions) {
$advfiltergroupsql = "";
$groupid = $d_conditions["groupid"];
if ($last_gid == "") {
$last_gid = $groupid;
}
$dkey = $d_conditions["dkey"];
$from_req = $d_conditions["from_req"];
$column_condition = "";
$fieldcolname = $d_conditions["fcol{$dkey}"];
$comparator = $d_conditions["fop{$dkey}"];
$value = $d_conditions["fval{$dkey}"];
// ? fval_
if (isset($d_conditions["fcon{$dkey}"])) {
$column_condition = $d_conditions["fcon{$dkey}"];
}
if (isset($d_conditions["gpcon{$dkey}"])) {
$groupcondition = $d_conditions["gpcon{$dkey}"];
}
if (isset($d_conditions["f_type{$dkey}"])) {
$f_type = $d_conditions["f_type{$dkey}"];
}
if ($fieldcolname != "" && ($comparator == "isn" || $comparator == "isnn")) {
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$fld_cond = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$fld_arr = explode(":", $fieldcolname);
$fld_cond = $fld_arr[0] . "." . $fld_arr[1];
}
if ($comparator == "isn") {
$advfiltergroupsql .= " (" . $fld_cond . " IS NULL OR " . $fld_cond . " = '') ";
} else {
$advfiltergroupsql .= " (" . $fld_cond . " IS NOT NULL AND " . $fld_cond . " != '') ";
}
} elseif ($fieldcolname != "" && $comparator != "" && $f_type != "S" && !in_array($fieldcolname, $std_filter_columns)) {
$selectedfields = explode(":", $fieldcolname);
//Added to handle yes or no for checkbox field in reports advance filters. -shahul
if ($selectedfields[4] == 'C') {
if (strcasecmp(trim($value), "yes") == 0) {
$value = "1";
}
if (strcasecmp(trim($value), "no") == 0) {
$value = "0";
}
}
$valuearray = explode(", ", trim($value));
$datatype = isset($selectedfields[4]) ? $selectedfields[4] : "";
// ADV FILTER CONDITIONS START
if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') {
$advcolumnsql = "";
for ($n = 0; $n < count($valuearray); $n++) {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
//If negative logic filter ('not equal to', 'does not contain') is used, 'and' condition should be applied instead of 'or'
if ($comparator == 'n' || $comparator == 'k') {
$advcolumnsql = implode(" and ", $advcolsql);
} else {
$advcolumnsql = implode(" or ", $advcolsql);
}
$fieldvalue = " (" . $advcolumnsql . ") ";
} else {
// ITS4YOU-UP SlOl 9. 1. 2014 13:44:18
$last_key = count($selectedfields) - 1;
$exploded_name = explode("_", $selectedfields[2]);
$field_module_id = getTabid($exploded_name[0]);
if (is_numeric($selectedfields[$last_key])) {
$tablename = $selectedfields[0];
$fieldname = $selectedfields[3];
$field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0);
$selected_field_col_table = trim($selectedfields[0], $selectedfields[$last_key]);
if (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $field_row["fieldid"] . " ", $join_array)) {
$selected_field_col = $selected_field_col_table . "_" . $field_row["fieldid"] . "." . $selectedfields[1];
} elseif (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $selectedfields[$last_key] . " ", $join_array)) {
$selected_field_col = $selected_field_col_table . "_" . $selectedfields[$last_key] . "." . $selectedfields[1];
}
} elseif (isset($this->columns_array[$fieldcolname]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"];
} elseif ($selectedfields[$last_key] == "MIF") {
$alias = "mif_" . $field_module_id;
$selected_field_col = trim($selectedfields[0], "MIF") . $alias . "." . $selectedfields[1];
} else {
$tablename = $selectedfields[0];
$fieldname = $selectedfields[3];
$field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0);
if ($tablename == "vtiger_inventoryproductrel") {
$field_uitype = "INV";
$params = array("fieldid" => $selectedfields[$last_key]);
$uifactory = new UIFactory($field_row);
$selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields);
} elseif (!empty($field_row)) {
$field_uitype = $field_row["uitype"];
//$uifactory = new UIFactory($field_row);
//$selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields);
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$table_alias = $tablename . "_{$field_uitype}";
$column_alias = $fieldname;
$selected_field_col = $table_alias . "." . $column_alias;
}
} elseif (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$selected_field_col = $selectedfields[0] . "." . $selectedfields[1];
}
}
// ITS4YOU-END 9. 1. 2014 13:44:22
$fieldvalue = $selected_field_col . $this->getAdvComparator($comparator, trim($value), $datatype);
}
// ADV FILTER CONDITIONS END
$advfiltergroupsql .= $fieldvalue;
} elseif ($f_type == "S") {
$fld_arr = explode(":", $fieldcolname);
if ($fld_arr[3] == "assigned_user_id" && strpos($value, "Current User") !== false) {
$value = trim($value, "('");
$value = trim($value, "')");
$valuearray = explode("', '", $value);
$valuearray = $this->getConditionCurrentUserName($valuearray);
$value = "('" . implode("', '", $valuearray) . "')";
}
$advfiltergroupsql .= $this->getSelFieldsWhereSQL($fieldcolname, $comparator, $value, $fld_arr);
/*if ($comparator == "n") {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " NOT IN " . $value;
} else {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " IN " . $value;
}*/
} elseif (in_array($fieldcolname, $std_filter_columns)) {
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$fld_cond = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$fld_arr = explode(":", $fieldcolname);
$fld_cond = $fld_arr[0] . "." . $fld_arr[1];
}
$advfiltergroupsql .= $fld_cond . " " . $this->getStdComparator($comparator, trim($value));
} else {
$column_info = explode(":", $fieldcolname);
$temp_val = explode(", ", $value);
if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $value != '') && !in_array($fieldcolname, $std_filter_columns)) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($column_info[4] == 'D') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($column_info[4] == 'DT') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$value = implode(", ", $val);
}
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
if (in_array($fieldcolname, $std_filter_columns)) {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getStdComparator($comparator, trim($value));
} else {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getAdvComparator($comparator, trim($value));
}
} else {
$this->adv_fcol[] = $fieldcolname;
// $selectedfields = explode(":", $fieldcolname);
if (in_array($fieldcolname, $std_filter_columns)) {
$advfiltergroupsql .= $fieldcolname . " " . $this->getStdComparator($comparator, trim($value));
} else {
$advfiltergroupsql .= $fieldcolname . " " . $this->getAdvComparator($comparator, trim($value));
}
}
}
if (trim($advfiltergroupsql) != "") {
$advfiltergroupsql = "( {$advfiltergroupsql} ) ";
if ($column_condition != NULL && $column_condition != '' && $d_i < $d_num_rows) {
$advfiltergroupsql .= ' ' . $column_condition . ' ';
}
if ($l_groupcondition != NULL && $l_groupcondition != '' && $groupid != $last_gid) {
$advfiltergroupsql = ' ' . $l_groupcondition . ' ' . $advfiltergroupsql;
$l_groupcondition = $groupcondition;
$last_gid = $groupid;
} else {
$l_groupcondition = $groupcondition;
$last_gid = $groupid;
}
$advfiltersql .= $advfiltergroupsql;
}
$d_i++;
}
if (trim($advfiltersql) != "") {
$advfiltersql = '(' . $advfiltersql . ')';
}
}
// Save the information
return $advfiltersql;
}
* The Original Code is: vtiger CRM Open Source
* The Initial Developer of the Original Code is vtiger.
* Portions created by vtiger are Copyright (C) vtiger.
* All Rights Reserved.
*
********************************************************************************/
require_once 'Smarty_setup.php';
require_once "modules/Reports/ReportRun.php";
require_once "modules/Reports/Reports.php";
global $app_strings;
global $mod_strings;
$oPrint_smarty = new vtigerCRM_Smarty();
$reportid = vtlib_purify($_REQUEST["record"]);
$oReport = new Reports($reportid);
$filtercolumn = $_REQUEST["stdDateFilterField"];
$filter = $_REQUEST["stdDateFilter"];
$oReportRun = ReportRun::getInstance($reportid);
$startdate = DateTimeField::convertToDBFormat($_REQUEST["startdate"]);
//Convert the user date format to DB date format
$enddate = DateTimeField::convertToDBFormat($_REQUEST["enddate"]);
//Convert the user date format to DB date format
$filterlist = $oReportRun->RunTimeFilter($filtercolumn, $filter, $startdate, $enddate);
$arr_values = $oReportRun->GenerateReport("PRINT", $filterlist);
$total_report = $oReportRun->GenerateReport("PRINT_TOTAL", $filterlist);
$oPrint_smarty->assign("COUNT", $arr_values[1]);
$oPrint_smarty->assign("APP", $app_strings);
$oPrint_smarty->assign("MOD", $mod_strings);
$oPrint_smarty->assign("REPORT_NAME", $oReport->reportname);
$oPrint_smarty->assign("PRINT_CONTENTS", $arr_values[0]);
$oPrint_smarty->assign("TOTAL_HTML", $total_report);
$oPrint_smarty->display("PrintReport.tpl");
public function Save(Vtiger_Request $request)
{
$workflowId = $request->get('for_workflow');
if (!empty($workflowId)) {
$record = $request->get('task_id');
if ($record) {
$taskRecordModel = Settings_Workflows_TaskRecord_Model::getInstance($record);
} else {
$workflowModel = Settings_Workflows_Record_Model::getInstance($workflowId);
$taskRecordModel = Settings_Workflows_TaskRecord_Model::getCleanInstance($workflowModel, $request->get('taskType'));
}
$taskObject = $taskRecordModel->getTaskObject();
$taskObject->summary = $request->get("summary");
$active = $request->get("active");
if ($active == "true") {
$taskObject->active = true;
} else {
if ($active == "false") {
$taskObject->active = false;
}
}
$checkSelectDate = $request->get('check_select_date');
if (!empty($checkSelectDate)) {
$trigger = array('days' => ($request->get('select_date_direction') == 'after' ? 1 : -1) * (int) $request->get('select_date_days'), 'field' => $request->get('select_date_field'));
$taskObject->trigger = $trigger;
} else {
$taskObject->trigger = null;
}
$fieldNames = $taskObject->getFieldNames();
foreach ($fieldNames as $fieldName) {
if ($fieldName == 'field_value_mapping') {
$taskObject->{$fieldName} = $request->getRaw($fieldName);
} else {
$taskObject->{$fieldName} = $request->get($fieldName);
}
if ($fieldName == 'calendar_repeat_limit_date') {
$taskObject->{$fieldName} = DateTimeField::convertToDBFormat($request->get($fieldName));
}
}
$taskType = get_class($taskObject);
if ($taskType === 'VTCreateEntityTask') {
$relationModuleModel = Vtiger_Module_Model::getInstance($taskObject->entity_type);
$ownerFieldModels = $relationModuleModel->getFieldsByType('owner');
$fieldMapping = Zend_Json::decode($taskObject->field_value_mapping);
foreach ($fieldMapping as $key => $mappingInfo) {
if (array_key_exists($mappingInfo['fieldname'], $ownerFieldModels)) {
$userRecordModel = Users_Record_Model::getInstanceById($mappingInfo['value'], 'Users');
$ownerName = $userRecordModel->get('user_name');
if (!$ownerName) {
$groupRecordModel = Settings_Groups_Record_Model::getInstance($mappingInfo['value']);
$ownerName = $groupRecordModel->getName();
}
$fieldMapping[$key]['value'] = $ownerName;
}
}
$taskObject->field_value_mapping = Zend_Json::encode($fieldMapping);
}
$taskRecordModel->save();
$response = new Vtiger_Response();
$response->setResult(array('for_workflow' => $workflowId));
$response->emit();
}
}
/**
* Function saves Reports Filter information
*/
function saveAdvancedFilters($advft_criteria = array(), $advft_criteria_groups = array(), $std_filter_columns = array(), $export_sql = false)
{
$adb = PearDatabase::getInstance();
$reportid = $this->getId();
$d_adv_criteria = "DELETE FROM its4you_reports4you_relcriteria WHERE queryid = ?";
$d_adv_criteria_result = $adb->pquery($d_adv_criteria, array($reportid));
if (!empty($std_filter_columns)) {
global $default_charset;
foreach ($std_filter_columns as $std_key => $std_value) {
$std_filter_columns[$std_key] = html_entity_decode($std_value, ENT_QUOTES, $default_charset);
}
}
if (!empty($advft_criteria) && !empty($advft_criteria_groups)) {
$default_charset = vglobal("default_charset");
foreach ($advft_criteria as $column_index => $column_condition) {
if (empty($column_condition)) {
continue;
}
$adv_filter_comparator = $column_condition["comparator"];
if (in_array($column_condition["columnname"], $std_filter_columns)) {
$adv_filter_column = $column_condition["columnname"];
} else {
$adv_filter_column = $column_condition["columnname"];
}
$adv_filter_value = $column_condition["value"];
$adv_filter_column_condition = $column_condition["column_condition"];
$adv_filter_groupid = $column_condition["groupid"];
if (in_array($adv_filter_column, $std_filter_columns)) {
$temp_val = explode("<;@STDV@;>", html_entity_decode($adv_filter_value, ENT_QUOTES, $default_charset));
$val[0] = DateTimeField::convertToDBFormat(trim($temp_val[0]));
$val[1] = DateTimeField::convertToDBFormat(trim($temp_val[1]));
$adv_filter_value = implode("<;@STDV@;>", $val);
// $adv_filter_value = html_entity_decode($adv_filter_value, ENT_QUOTES, $default_charset);
} else {
$column_info = explode(":", $adv_filter_column);
/// $temp_val = explode(",",$adv_filter_value);
$temp_val = $adv_filter_value;
if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $adv_filter_value != '')) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($column_info[4] == 'D') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($column_info[4] == 'DT') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$adv_filter_value = implode(",", $val);
}
}
if (is_array($adv_filter_value)) {
$adv_filter_value = implode(",", $adv_filter_value);
}
$irelcriteriasql = "INSERT INTO its4you_reports4you_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) VALUES (?,?,?,?,?,?,?)";
$export_sql === true ? $adb->setDebug(true) : "";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($reportid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
$export_sql === true ? $adb->setDebug(false) : "";
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advft_criteria_groups[$adv_filter_groupid]["conditionexpression"])) {
$groupConditionExpression = $advft_criteria_groups[$adv_filter_groupid]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $column_index . ' ' . $adv_filter_column_condition;
$advft_criteria_groups[$adv_filter_groupid]["conditionexpression"] = $groupConditionExpression;
}
$d_adv_criteria_grouping = "DELETE FROM its4you_reports4you_relcriteria_grouping WHERE queryid = ?";
$export_sql === true ? $adb->setDebug(true) : "";
$d_adv_criteria_grouping_result = $adb->pquery($d_adv_criteria_grouping, array($reportid));
$export_sql === true ? $adb->setDebug(false) : "";
foreach ($advft_criteria_groups as $group_index => $group_condition_info) {
if (!isset($group_condition_info) || empty($group_condition_info)) {
continue;
}
$irelcriteriagroupsql = "INSERT INTO its4you_reports4you_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) VALUES (?,?,?,?)";
$export_sql === true ? $adb->setDebug(true) : "";
$irelcriteriagroupresult = $adb->pquery($irelcriteriagroupsql, array($group_index, $reportid, $group_condition_info["groupcondition"], $group_condition_info["conditionexpression"]));
$export_sql === true ? $adb->setDebug(false) : "";
}
}
}
/**
* Function to save the custom view record
*/
public function save()
{
$db = PearDatabase::getInstance();
$currentUserModel = Users_Record_Model::getCurrentUserModel();
$cvId = $this->getId();
$moduleModel = $this->getModule();
$moduleName = $moduleModel->get('name');
$viewName = $this->get('viewname');
$setDefault = $this->get('setdefault');
$setMetrics = $this->get('setmetrics');
$status = $this->get('status');
if ($status == self::CV_STATUS_PENDING) {
if ($currentUserModel->isAdminUser()) {
$status = self::CV_STATUS_PUBLIC;
}
}
if (!$cvId) {
$cvId = $db->getUniqueID("vtiger_customview");
$this->set('cvid', $cvId);
$sql = 'INSERT INTO vtiger_customview(cvid, viewname, setdefault, setmetrics, entitytype, status, userid) VALUES (?,?,?,?,?,?,?)';
$params = array($cvId, $viewName, $setDefault, $setMetrics, $moduleName, $status, $currentUserModel->getId());
$db->pquery($sql, $params);
} else {
$sql = 'UPDATE vtiger_customview SET viewname=?, setdefault=?, setmetrics=?, status=? WHERE cvid=?';
$params = array($viewName, $setDefault, $setMetrics, $status, $cvId);
$db->pquery($sql, $params);
$db->pquery('DELETE FROM vtiger_cvcolumnlist WHERE cvid = ?', array($cvId));
$db->pquery('DELETE FROM vtiger_cvstdfilter WHERE cvid = ?', array($cvId));
$db->pquery('DELETE FROM vtiger_cvadvfilter WHERE cvid = ?', array($cvId));
$db->pquery('DELETE FROM vtiger_cvadvfilter_grouping WHERE cvid = ?', array($cvId));
}
if ($setDefault == 1) {
$query = 'SELECT 1 FROM vtiger_user_module_preferences WHERE userid = ? AND tabid = ?';
$queryParams = array($currentUserModel->getId(), $moduleModel->getId());
$queryResult = $db->pquery($query, $queryParams);
if ($db->num_rows($queryResult) > 0) {
$updateSql = 'UPDATE vtiger_user_module_preferences SET default_cvid = ? WHERE userid = ? AND tabid = ?';
$updateParams = array($cvId, $currentUserModel->getId(), $moduleModel->getId());
$db->pquery($updateSql, $updateParams);
} else {
$insertSql = 'INSERT INTO vtiger_user_module_preferences(userid, tabid, default_cvid) VALUES (?,?,?)';
$insertParams = array($currentUserModel->getId(), $moduleModel->getId(), $cvId);
$db->pquery($insertSql, $insertParams);
}
} else {
$deleteSql = 'DELETE FROM vtiger_user_module_preferences WHERE userid = ? AND tabid = ? AND default_cvid = ?';
$deleteParams = array($currentUserModel->getId(), $moduleModel->getId(), $cvId);
$db->pquery($deleteSql, $deleteParams);
}
$selectedColumnsList = $this->get('columnslist');
if (!empty($selectedColumnsList)) {
$noOfColumns = count($selectedColumnsList);
for ($i = 0; $i < $noOfColumns; $i++) {
$columnSql = 'INSERT INTO vtiger_cvcolumnlist (cvid, columnindex, columnname) VALUES (?,?,?)';
$columnParams = array($cvId, $i, $selectedColumnsList[$i]);
$db->pquery($columnSql, $columnParams);
}
} else {
//no fields were sent so add default All filter columns
$defaultModuleFilter = $db->pquery('SELECT cvid FROM vtiger_customview WHERE setdefault = 1 AND entitytype = ?', array($moduleName));
$defaultViewId = $db->query_result($defaultModuleFilter, 0, 'cvid');
//User Specific filterId
if (empty($defaultViewId)) {
$userDefaultModuleFilter = $db->pquery('SELECT default_cvid FROM vtiger_user_module_preferences WHERE
userid = ? AND tabid = ?', array($currentUserModel->id, $moduleModel->getId()));
$defaultViewId = $db->query_result($userDefaultModuleFilter, 0, 'default_cvid');
}
//First filterid of module
if (empty($defaultViewId)) {
$firstDefaultFilter = $db->pquery('SELECT cvid FROM vtiger_customview WHERE entitytype = ?', array($moduleName));
$defaultViewId = $db->query_result($firstDefaultFilter, 0, 'cvid');
}
// Get the defaults filters columnlist
$columnSql = "INSERT INTO vtiger_cvcolumnlist (cvid, columnindex, columnname)\n\t\t\t\t\t\t\tSELECT ?, columnindex, columnname FROM vtiger_cvcolumnlist WHERE cvid = ?";
$db->pquery($columnSql, array($cvId, $defaultViewId));
}
$stdFilterList = $this->get('stdfilterlist');
if (!empty($stdFilterList) && !empty($stdFilterList['columnname'])) {
$stdFilterSql = 'INSERT INTO vtiger_cvstdfilter(cvid,columnname,stdfilter,startdate,enddate) VALUES (?,?,?,?,?)';
$stdFilterParams = array($cvId, $stdFilterList['columnname'], $stdFilterList['stdfilter'], $db->formatDate($stdFilterList['startdate'], true), $db->formatDate($stdFilterList['enddate'], true));
$db->pquery($stdFilterSql, $stdFilterParams);
}
$advFilterList = $this->get('advfilterlist');
if (!empty($advFilterList)) {
foreach ($advFilterList as $groupIndex => $groupInfo) {
if (empty($groupInfo)) {
continue;
}
$groupColumns = $groupInfo['columns'];
$groupCondition = $groupInfo['condition'];
foreach ($groupColumns as $columnIndex => $columnCondition) {
if (empty($columnCondition)) {
continue;
}
$advFilterColumn = $columnCondition['columnname'];
$advFilterComparator = $columnCondition['comparator'];
$advFitlerValue = $columnCondition['value'];
$advFilterColumnCondition = $columnCondition['column_condition'];
$columnInfo = explode(":", $advFilterColumn);
$fieldName = $columnInfo[2];
$fieldModel = $moduleModel->getField($fieldName);
//Required if Events module fields are selected for the condition
if (!$fieldModel) {
$modulename = $moduleModel->get('name');
if ($modulename == 'Calendar') {
$eventModuleModel = Vtiger_Module_model::getInstance('Events');
$fieldModel = $eventModuleModel->getField($fieldName);
}
}
$fieldType = $fieldModel->getFieldDataType();
if ($fieldType == 'currency') {
if ($fieldModel->get('uitype') == '72') {
// Some of the currency fields like Unit Price, Totoal , Sub-total - doesn't need currency conversion during save
$advFitlerValue = CurrencyField::convertToDBFormat($advFitlerValue, null, true);
} else {
$advFitlerValue = CurrencyField::convertToDBFormat($advFitlerValue);
}
}
$temp_val = explode(",", $advFitlerValue);
if (($fieldType == 'date' || $fieldType == 'time' && $fieldName != 'time_start' && $fieldName != 'time_end' || $fieldType == 'datetime') && ($fieldType != '' && $advFitlerValue != '')) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
//if date and time given then we have to convert the date and
//leave the time as it is, if date only given then temp_time
//value will be empty
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($fieldType == 'date') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($fieldType == 'datetime') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$advFitlerValue = implode(",", $val);
}
$advCriteriaSql = 'INSERT INTO vtiger_cvadvfilter(cvid,columnindex,columnname,comparator,value,groupid,column_condition)
values (?,?,?,?,?,?,?)';
$advCriteriaParams = array($cvId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFitlerValue, $groupIndex, $advFilterColumnCondition);
$db->pquery($advCriteriaSql, $advCriteriaParams);
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advFilterList[$groupIndex]["conditionexpression"])) {
$groupConditionExpression = $advFilterList[$groupIndex]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition;
$advFilterList[$groupIndex]["conditionexpression"] = $groupConditionExpression;
}
$groupConditionExpression = $advFilterList[$groupIndex]["conditionexpression"];
if (empty($groupConditionExpression)) {
continue;
}
// Case when the group doesn't have any column criteria
$advGroupSql = 'INSERT INTO vtiger_cvadvfilter_grouping(groupid,cvid,group_condition,condition_expression) VALUES (?,?,?,?)';
$advGroupParams = array($groupIndex, $cvId, $groupCondition, $groupConditionExpression);
$db->pquery($advGroupSql, $advGroupParams);
}
}
}
/**
* Function to display the Search Results
* @param Vtiger_Request $request
*/
function showSearchResults(Vtiger_Request $request)
{
$db = PearDatabase::getInstance();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$advFilterList = $request->get('advfilterlist');
//used to show the save modify filter option
$isAdvanceSearch = false;
$matchingRecords = array();
if (is_array($advFilterList) && count($advFilterList) > 0) {
$isAdvanceSearch = true;
$user = Users_Record_Model::getCurrentUserModel();
$queryGenerator = new QueryGenerator($moduleName, $user);
$queryGenerator->setFields(array('id'));
vimport('~~/modules/CustomView/CustomView.php');
$customView = new CustomView($moduleName);
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advFilterList as $groupindex => $groupcolumns) {
$filtercolumns = $groupcolumns['columns'];
if (count($filtercolumns) > 0) {
$queryGenerator->startGroup('');
foreach ($filtercolumns as $index => $filter) {
$nameComponents = explode(':', $filter['columnname']);
if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
$name = $queryGenerator->getSQLColumn('id');
} else {
$name = $nameComponents[2];
}
if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
$filter['stdfilter'] = $filter['comparator'];
$valueComponents = explode(',', $filter['value']);
if ($filter['comparator'] == 'custom') {
$filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
$queryGenerator->addCondition($name, $value, 'BETWEEN');
} else {
$queryGenerator->addCondition($name, $filter['value'], $filter['comparator']);
}
$columncondition = $filter['column_condition'];
if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) {
$queryGenerator->addConditionGlue($columncondition);
}
}
$queryGenerator->endGroup();
$groupConditionGlue = $groupcolumns['condition'];
if (!empty($groupConditionGlue)) {
$queryGenerator->addConditionGlue($groupConditionGlue);
}
}
}
$query = $queryGenerator->getQuery();
//Remove the ordering for now to improve the speed
//$query .= ' ORDER BY createdtime DESC';
$result = $db->pquery($query, array());
$rows = $db->num_rows($result);
for ($i = 0; $i < $rows; ++$i) {
$row = $db->query_result_rowdata($result, $i);
$recordInstance = Vtiger_Record_Model::getInstanceById($row[0]);
$moduleName = $recordInstance->getModuleName();
$matchingRecords[$moduleName][$row[0]] = $recordInstance;
}
$viewer->assign('SEARCH_MODULE', $moduleName);
} else {
$searchKey = $request->get('value');
$searchModule = false;
if ($request->get('searchModule')) {
$searchModule = $request->get('searchModule');
}
$viewer->assign('SEARCH_KEY', $searchKey);
$viewer->assign('SEARCH_MODULE', $searchModule);
$matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule);
}
$matchingRecordsList = array();
if ($matchingRecords[$moduleName]) {
$matchingRecordsList[$moduleName] = $matchingRecords[$moduleName];
}
foreach ($matchingRecords as $module => $recordModelsList) {
$matchingRecordsList[$module] = $recordModelsList;
}
$viewer->assign('MODULE', $moduleName);
$viewer->assign('MATCHING_RECORDS', $matchingRecordsList);
$viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch);
echo $viewer->view('UnifiedSearchResults.tpl', '', true);
}
/** Function to set date values compatible to database (YY_MM_DD)
* @param $value -- value :: Type string
* @returns $insert_date -- insert_date :: Type string
*/
function getValidDBInsertDateValue($value)
{
global $log;
$log->debug("Entering getValidDBInsertDateValue(" . $value . ") method ...");
$value = trim($value);
$delim = array('/', '.');
foreach ($delim as $delimiter) {
$x = strpos($value, $delimiter);
if ($x === false) {
continue;
} else {
$value = str_replace($delimiter, '-', $value);
break;
}
}
list($y, $m, $d) = explode('-', $value);
if (strlen($y) == 1) {
$y = '0' . $y;
}
if (strlen($m) == 1) {
$m = '0' . $m;
}
if (strlen($d) == 1) {
$d = '0' . $d;
}
$value = implode('-', array($y, $m, $d));
if (strlen($y) < 4) {
$insert_date = DateTimeField::convertToDBFormat($value);
} else {
$insert_date = $value;
}
if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2}\$/", $insert_date) == 0) {
return '';
}
$log->debug("Exiting getValidDBInsertDateValue method ...");
return $insert_date;
}
/**
* Function saves Reports Filter information
*/
function saveAdvancedFilters()
{
$db = PearDatabase::getInstance();
$reportId = $this->getId();
$advancedFilter = $this->get('advancedFilter');
if (!empty($advancedFilter)) {
$db->pquery('DELETE FROM vtiger_relcriteria WHERE queryid = ?', array($reportId));
$db->pquery('DELETE FROM vtiger_relcriteria_grouping WHERE queryid = ?', array($reportId));
foreach ($advancedFilter as $groupIndex => $groupInfo) {
if (empty($groupInfo)) {
continue;
}
$groupColumns = $groupInfo['columns'];
$groupCondition = $groupInfo['condition'];
foreach ($groupColumns as $columnIndex => $columnCondition) {
if (empty($columnCondition)) {
continue;
}
$advFilterColumn = $columnCondition["columnname"];
$advFilterComparator = $columnCondition["comparator"];
$advFilterValue = $columnCondition["value"];
$advFilterColumnCondition = $columnCondition["column_condition"];
$columnInfo = explode(":", $advFilterColumn);
$moduleFieldLabel = $columnInfo[2];
list($module, $fieldLabel) = explode('__', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($db, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if ($fieldType == 'currency') {
if ($field->getUIType() == '72') {
// Some of the currency fields like Unit Price, Totoal , Sub-total - doesn't need currency conversion during save
$advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue, null, true);
} else {
$advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue);
}
}
$tempVal = explode(",", $advFilterValue);
if (($columnInfo[4] == 'D' || $columnInfo[4] == 'T' && $columnInfo[1] != 'time_start' && $columnInfo[1] != 'time_end' || $columnInfo[4] == 'DT') && ($columnInfo[4] != '' && $advFilterValue != '')) {
$val = array();
for ($i = 0; $i < count($tempVal); $i++) {
if (trim($tempVal[$i]) != '') {
$date = new DateTimeField(trim($tempVal[$i]));
if ($columnInfo[4] == 'D') {
$val[$i] = DateTimeField::convertToDBFormat(trim($tempVal[$i]));
} elseif ($columnInfo[4] == 'DT') {
/**
* While generating query to retrieve report, for date time fields we are only taking
* date field and appending '00:00:00' for correct results depending on time zone.
* If you save the time also here by converting to db format, while showing in edit
* view it was changing the date selected.
*/
$values = explode(' ', $tempVal[$i]);
$date = new DateTimeField($values[0]);
$val[$i] = $date->getDBInsertDateValue();
} else {
$val[$i] = $date->getDBInsertTimeValue();
}
}
}
$advFilterValue = implode(",", $val);
}
$db->pquery('INSERT INTO vtiger_relcriteria (queryid, columnindex, columnname, comparator, value,
groupid, column_condition) VALUES (?,?,?,?,?,?,?)', array($reportId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFilterValue, $groupIndex, $advFilterColumnCondition));
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advancedFilter[$groupIndex]["conditionexpression"])) {
$groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition;
$advancedFilter[$groupIndex]["conditionexpression"] = $groupConditionExpression;
}
$groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
if (empty($groupConditionExpression)) {
continue;
}
// Case when the group doesn't have any column criteria
$db->pquery("INSERT INTO vtiger_relcriteria_grouping(groupid, queryid, group_condition, condition_expression) VALUES (?,?,?,?)", array($groupIndex, $reportId, $groupCondition, $groupConditionExpression));
}
}
}
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;
}
/**
* Function to display the Search Results
* @param Vtiger_Request $request
*/
function showSearchResults(Vtiger_Request $request)
{
$db = PearDatabase::getInstance();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$advFilterList = $request->get('advfilterlist');
//used to show the save modify filter option
$isAdvanceSearch = false;
$matchingRecords = array();
if (is_array($advFilterList) && count($advFilterList) > 0) {
$isAdvanceSearch = true;
$user = Users_Record_Model::getCurrentUserModel();
$queryGenerator = new QueryGenerator($moduleName, $user);
$queryGenerator->setFields(['id']);
vimport('~modules/CustomView/CustomView.php');
$customView = new CustomView($moduleName);
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advFilterList as $groupindex => $groupcolumns) {
$filtercolumns = $groupcolumns['columns'];
if (count($filtercolumns) > 0) {
$queryGenerator->startGroup('');
foreach ($filtercolumns as $index => $filter) {
$nameComponents = explode(':', $filter['columnname']);
if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
$name = $queryGenerator->getSQLColumn('id');
} else {
$name = $nameComponents[2];
}
if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
$filter['stdfilter'] = $filter['comparator'];
$valueComponents = explode(',', $filter['value']);
if ($filter['comparator'] == 'custom') {
$filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
$queryGenerator->addCondition($name, $value, 'BETWEEN');
} else {
$queryGenerator->addCondition($name, $filter['value'], $filter['comparator']);
}
$columncondition = $filter['column_condition'];
if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) {
$queryGenerator->addConditionGlue($columncondition);
}
}
$queryGenerator->endGroup();
$groupConditionGlue = $groupcolumns['condition'];
if (!empty($groupConditionGlue)) {
$queryGenerator->addConditionGlue($groupConditionGlue);
}
}
}
$query = $queryGenerator->getQuery();
//Remove the ordering for now to improve the speed
//$query .= ' ORDER BY createdtime DESC';
$result = $db->query($query);
while ($row = $db->fetch_array($result)) {
$recordInstance = Vtiger_Record_Model::getInstanceById(current($row));
$moduleName = $recordInstance->getModuleName();
$recordInstance->set('permitted', true);
$matchingRecords[$moduleName][current($row)] = $recordInstance;
}
$viewer->assign('SEARCH_MODULE', $moduleName);
} else {
$searchKey = $request->get('value');
$searchModule = false;
if ($request->get('searchModule')) {
$searchModule = $request->get('searchModule');
}
$viewer->assign('SEARCH_KEY', $searchKey);
$viewer->assign('SEARCH_MODULE', $searchModule);
$matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule, $request->get('limit'));
}
$recordsList = $matchingRecordsList = [];
if ($matchingRecords[$moduleName]) {
$matchingRecordsList[$moduleName] = $matchingRecords[$moduleName];
}
foreach ($matchingRecords as $module => $recordModelsList) {
$matchingRecordsList[$module] = $recordModelsList;
}
if ($request->get('html') == 'true') {
$viewer->assign('MODULE', $moduleName);
$viewer->assign('MATCHING_RECORDS', $matchingRecordsList);
$viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch);
echo $viewer->view('UnifiedSearchResults.tpl', '', true);
} else {
foreach ($matchingRecordsList as $module => $modules) {
foreach ($modules as $recordID => $recordModel) {
$label = decode_html($recordModel->getName());
$label .= ' (' . Vtiger_Functions::getOwnerRecordLabel($recordModel->get('smownerid')) . ')';
if (!$recordModel->get('permitted')) {
$label .= ' <span class="glyphicon glyphicon-warning-sign" aria-hidden="true"></span>';
}
$recordsList[] = ['id' => $recordID, 'module' => $module, 'category' => vtranslate($module, $module), 'label' => $label, 'permitted' => $recordModel->get('permitted')];
}
}
$response = new Vtiger_Response();
$response->setResult($recordsList);
$response->emit();
}
}
