/**
* Function to get Date and Time value for Display
* @param <type> $date
* @return <String>
*/
public static function getDBDateTimeValue($date)
{
global $log;
$log->debug("Entering ./uitypes/Datetime.php::staticgetDBDateTimeValue");
$date = new DateTimeField($date);
return $date->getDBInsertDateTimeValue();
}
/**
*
* @param mixed $value
* @param String $operator
* @param WebserviceField $field
*/
private function getConditionValue($value, $operator, $field)
{
$operator = strtolower($operator);
$db = PearDatabase::getInstance();
$inEqualityFieldTypes = ['currency', 'percentage', 'double', 'integer', 'number'];
if (is_string($value) && $this->ignoreComma == false) {
$commaSeparatedFieldTypes = ['picklist', 'multipicklist', 'owner', 'date', 'datetime', 'time', 'tree', 'sharedOwner', 'sharedOwner'];
if (in_array($field->getFieldDataType(), $commaSeparatedFieldTypes)) {
$valueArray = explode(',', $value);
if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, ['e', 'n'])) {
$valueArray = getCombinations($valueArray);
foreach ($valueArray as $key => $value) {
$valueArray[$key] = ltrim($value, ' |##| ');
}
}
} else {
if ($field->getFieldDataType() == 'multiReferenceValue') {
$valueArray = explode(',', $value);
foreach ($valueArray as $key => $value) {
$valueArray[$key] = '|#|' . $value . '|#|';
}
} else {
$valueArray = [$value];
}
}
} elseif (is_array($value)) {
$valueArray = $value;
} else {
$valueArray = [$value];
}
$sql = array();
if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') {
if ($field->getFieldName() == 'birthday') {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
$sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')";
} else {
if ($this->isDateType($field->getFieldDataType())) {
$start = explode(' ', $valueArray[0]);
$end = explode(' ', $valueArray[1]);
if ($operator == 'between' && count($start) == 2 && count($end) == 2) {
$valueArray[0] = getValidDBInsertDateTimeValue($start[0] . ' ' . $start[1]);
if ($start[0] == $end[0]) {
$dateTime = new DateTime($valueArray[0]);
$nextDay = $dateTime->modify('+1 days');
$nextDay = strtotime($nextDay->format('Y-m-d H:i:s')) - 1;
$nextDay = date('Y-m-d H:i:s', $nextDay);
$values = explode(' ', $nextDay);
$valueArray[1] = getValidDBInsertDateTimeValue($values[0]) . ' ' . $values[1];
} else {
$end = $valueArray[1];
$dateObject = new DateTimeField($end);
$valueArray[1] = $dateObject->getDBInsertDateTimeValue();
}
} else {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$dateTimeStart = explode(' ', $valueArray[0]);
if ($dateTimeStart[1] == '00:00:00' && $operator != 'between') {
$valueArray[0] = $dateTimeStart[0];
}
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
$dateTimeEnd = explode(' ', $valueArray[1]);
if ($dateTimeEnd[1] == '00:00:00' || $dateTimeEnd[1] == '23:59:59') {
$valueArray[1] = $dateTimeEnd[0];
}
}
}
if ($operator == 'notequal') {
$sql[] = "NOT BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]);
} else {
$sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]);
}
}
return $sql;
}
foreach ($valueArray as $value) {
if (!$this->isStringType($field->getFieldDataType())) {
$value = trim($value);
}
if ($operator == 'empty' || $operator == 'y') {
$sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName()));
continue;
}
if ($operator == 'ny') {
$sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName()));
continue;
}
if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) {
if ($operator == 'e') {
$sql[] = "IS NULL";
continue;
}
$sql[] = "IS NOT NULL";
continue;
} elseif ($field->getFieldDataType() == 'boolean') {
$value = strtolower($value);
if ($value == 'yes') {
$value = 1;
} elseif ($value == 'no') {
$value = 0;
}
} elseif ($this->isDateType($field->getFieldDataType())) {
// For "after" and "before" conditions
$values = explode(' ', $value);
if (($operator == 'a' || $operator == 'b') && count($values) == 2) {
if ($operator == 'a') {
// for after comparator we should check the date after the given
$dateTime = new DateTime($value);
$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);
} else {
$dateTime = new DateTime($value);
$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);
}
} else {
$value = getValidDBInsertDateTimeValue($value);
$dateTime = explode(' ', $value);
if ($dateTime[1] == '00:00:00') {
$value = $dateTime[0];
}
}
} else {
if (in_array($field->getFieldDataType(), $inEqualityFieldTypes)) {
$table = get_html_translation_table(HTML_ENTITIES, ENT_COMPAT, vglobal('default_charset'));
$chars = implode('', array_keys($table));
if (preg_match("/[{$chars}]+/", $value) === 1) {
if ($operator == 'g' || $operator == 'l') {
$value = substr($value, 4);
} else {
if ($operator == 'h' || $operator == 'm') {
$value = substr($value, 5);
}
}
}
} else {
if ($field->getFieldDataType() === 'currency') {
$uiType = $field->getUIType();
if ($uiType == 72) {
$value = CurrencyField::convertToDBFormat($value, null, true);
} elseif ($uiType == 71) {
$value = CurrencyField::convertToDBFormat($value);
}
}
}
}
if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) {
$value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')";
} else {
$value = $db->sql_escape_string($value, true);
}
if ($field->getFieldDataType() == 'multiReferenceValue' && in_array($operator, ['e', 's', 'ew', 'c'])) {
$sql[] = "LIKE '%{$value}%'";
continue;
} else {
if ($field->getFieldDataType() == 'multiReferenceValue' && in_array($operator, ['n', 'k'])) {
$sql[] = "NOT LIKE '%{$value}%'";
continue;
}
}
if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) {
$sql[] = "LIKE ''";
continue;
}
if (trim($value) == '' && $operator == 'om' && in_array($field->getFieldName(), $this->ownerFields)) {
$sql[] = " = '" . Users_Record_Model::getCurrentUserModel()->get('id') . "'";
continue;
}
if ($field->getUIType() == 120) {
$shownersTable = Vtiger_SharedOwner_UIType::getShownerTable($this->getModule());
if ($operator == 'om') {
$sql[] = 'vtiger_crmentity.crmid IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ' . Users_Record_Model::getCurrentUserModel()->get('id') . ')';
} else {
if (in_array($operator, ['e', 's', 'ew', 'c'])) {
$sql[] = 'vtiger_crmentity.crmid IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ' . $value . ')';
} else {
if (in_array($operator, ['n', 'k'])) {
$sql[] = 'vtiger_crmentity.crmid NOT IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ' . $value . ')';
}
}
}
continue;
}
if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) {
$sql[] = "NOT LIKE ''";
continue;
}
$sqlOperatorData = $this->getSqlOperator($operator, $value);
$sqlOperator = $sqlOperatorData[0];
$value = $sqlOperatorData[1];
if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) {
$value = "'{$value}'";
}
if ($this->isNumericType($field->getFieldDataType()) && empty($value)) {
$value = '0';
}
$sql[] = "{$sqlOperator} {$value}";
}
return $sql;
}
function getActivityMailInfo($return_id, $status, $activity_type)
{
$mail_data = array();
global $adb;
$qry = "select * from vtiger_activity where activityid=?";
$ary_res = $adb->pquery($qry, array($return_id));
$send_notification = $adb->query_result($ary_res, 0, "sendnotification");
$subject = $adb->query_result($ary_res, 0, "subject");
$priority = $adb->query_result($ary_res, 0, "priority");
$st_date = $adb->query_result($ary_res, 0, "date_start");
$st_time = $adb->query_result($ary_res, 0, "time_start");
$end_date = $adb->query_result($ary_res, 0, "due_date");
$end_time = $adb->query_result($ary_res, 0, "time_end");
$location = $adb->query_result($ary_res, 0, "location");
if (!empty($st_time)) {
$date = new DateTimeField($st_date . ' ' . $st_time);
$st_date = $date->getDisplayDate();
$st_time = $date->getDisplayTime();
}
if (!empty($end_time)) {
$date = new DateTimeField($end_date . ' ' . $end_time);
$end_date = $date->getDisplayDate();
$end_time = $date->getDisplayTime();
}
$owner_qry = "select smownerid from vtiger_crmentity where crmid=?";
$res = $adb->pquery($owner_qry, array($return_id));
$owner_id = $adb->query_result($res, 0, "smownerid");
$usr_res = $adb->pquery("select count(*) as count from vtiger_users where id=?", array($owner_id));
if ($adb->query_result($usr_res, 0, 'count') > 0) {
$assignType = "U";
$usr_id = $owner_id;
} else {
$assignType = "T";
$group_qry = "select groupname from vtiger_groups where groupid=?";
$grp_res = $adb->pquery($group_qry, array($owner_id));
$grp_name = $adb->query_result($grp_res, 0, "groupname");
}
$desc_qry = "select description from vtiger_crmentity where crmid=?";
$des_res = $adb->pquery($desc_qry, array($return_id));
$description = $adb->query_result($des_res, 0, "description");
$rel_qry = "select case vtiger_crmentity.setype when 'Leads' then vtiger_leaddetails.lastname when 'Accounts' then vtiger_account.accountname when 'Potentials' then vtiger_potential.potentialname when 'Quotes' then vtiger_quotes.subject when 'PurchaseOrder' then vtiger_purchaseorder.subject when 'SalesOrder' then vtiger_salesorder.subject when 'Invoice' then vtiger_invoice.subject when 'Campaigns' then vtiger_campaign.campaignname when 'HelpDesk' then vtiger_troubletickets.title end as relname from vtiger_seactivityrel inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_seactivityrel.crmid left join vtiger_leaddetails on vtiger_leaddetails.leadid = vtiger_seactivityrel.crmid left join vtiger_account on vtiger_account.accountid=vtiger_seactivityrel.crmid left join vtiger_potential on vtiger_potential.potentialid=vtiger_seactivityrel.crmid left join vtiger_quotes on vtiger_quotes.quoteid= vtiger_seactivityrel.crmid left join vtiger_purchaseorder on vtiger_purchaseorder.purchaseorderid = vtiger_seactivityrel.crmid left join vtiger_salesorder on vtiger_salesorder.salesorderid = vtiger_seactivityrel.crmid left join vtiger_invoice on vtiger_invoice.invoiceid = vtiger_seactivityrel.crmid left join vtiger_campaign on vtiger_campaign.campaignid = vtiger_seactivityrel.crmid left join vtiger_troubletickets on vtiger_troubletickets.ticketid = vtiger_seactivityrel.crmid where vtiger_seactivityrel.activityid=?";
$rel_res = $adb->pquery($rel_qry, array($return_id));
$rel_name = $adb->query_result($rel_res, 0, "relname");
$relatedContacts = getActivityRelatedContacts($return_id);
$mail_data['mode'] = "edit";
$mail_data['activity_mode'] = $activity_type;
$mail_data['sendnotification'] = $send_notification;
$mail_data['user_id'] = $usr_id;
$mail_data['subject'] = $subject;
$mail_data['status'] = $status;
$mail_data['taskpriority'] = $priority;
$mail_data['relatedto'] = $rel_name;
$mail_data['contact_name'] = implode(',', $relatedContacts);
$mail_data['description'] = $description;
$mail_data['assign_type'] = $assignType;
$mail_data['group_name'] = $grp_name;
$value = getaddEventPopupTime($st_time, $end_time, '24');
$start_hour = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt'];
if ($activity_type != 'Task') {
$end_hour = $value['endhour'] . ':' . $value['endmin'] . '' . $value['endfmt'];
}
$date = new DateTimeField($st_date . " " . $start_hour);
$endDate = new DateTimeField($end_date . " " . $end_hour);
$mail_data['st_date_time'] = $date->getDBInsertDateTimeValue();
$mail_data['end_date_time'] = $endDate->getDBInsertDateValue() . ' ' . $endDate->getDBInsertTimeValue();
$mail_data['location'] = $location;
return $mail_data;
}
/**
* Function to get Pending/Upcoming activities
* @param integer $mode - number to differentiate upcoming and pending activities
* return array $values - activities record in array format
*/
function getPendingActivities($mode, $view = '')
{
global $log;
$log->debug("Entering getPendingActivities() method ...");
require_once 'data/Tracker.php';
require_once 'include/utils/utils.php';
require_once 'user_privileges/default_module_view.php';
global $currentModule;
global $singlepane_view;
global $theme;
global $focus;
global $action;
global $adb;
global $app_strings;
global $current_language;
global $current_user;
$current_module_strings = return_module_language($current_language, 'Calendar');
$theme_path = "themes/" . $theme . "/";
$image_path = $theme_path . "images/";
if ($_REQUEST['activity_view'] == '') {
$activity_view = 'today';
} else {
$activity_view = vtlib_purify($_REQUEST['activity_view']);
}
$dbStartDateTime = new DateTimeField(date('Y-m-d H:i:s'));
$userStartDate = $dbStartDateTime->getDisplayDate();
$userStartDateTime = new DateTimeField($userStartDate . ' 00:00:00');
$startDateTime = $userStartDateTime->getDBInsertDateTimeValue();
$userEndDateTime = new DateTimeField($userStartDate . ' 23:59:00');
$endDateTime = $userEndDateTime->getDBInsertDateTimeValue();
if ($view == 'today') {
$upcoming_condition = " AND (CAST((CONCAT(date_start,' ',time_start)) AS DATETIME) BETWEEN '{$startDateTime}' AND '{$endDateTime}'\n\t\t\t\t\t\t\t\t\tOR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) BETWEEN '{$startDateTime}' AND '{$endDateTime}')";
$pending_condition = " AND (CAST((CONCAT(date_start,' ',time_start)) AS DATETIME) BETWEEN '{$startDateTime}' AND '{$endDateTime}'\n\t\t\t\t\t\t\t\t\tOR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) BETWEEN '{$startDateTime}' AND '{$endDateTime}')";
} else {
if ($view == 'all') {
$upcoming_condition = " AND (CAST((CONCAT(date_start,' ',time_start)) AS DATETIME) >= '{$startDateTime}'\n\t\t\t\t\t\t\t\t\tOR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) >= '{$startDateTime}')";
$pending_condition = " AND (CAST((CONCAT(date_start,' ',time_start)) AS DATETIME) <= '{$startDateTime}'\n\t\t\t\t\t\t\t\t\tOR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) <= '{$startDateTime}')";
}
}
if ($mode != 1) {
$list_query = " select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity." . "setype, vtiger_recurringevents.recurringdate, vtiger_activity.activityid, " . "vtiger_activity.activitytype, vtiger_activity.date_start, vtiger_activity.due_date," . "from vtiger_activity inner join vtiger_crmentity on vtiger_crmentity.crmid=" . "vtiger_activity.activityid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = " . "vtiger_crmentity.smownerid left outer join vtiger_recurringevents on " . "vtiger_recurringevents.activityid=vtiger_activity.activityid";
$list_query .= getNonAdminAccessControlQuery('Calendar', $current_user);
$list_query .= " WHERE vtiger_crmentity.deleted=0 and vtiger_activity.activitytype not in " . "('Emails') AND ( vtiger_activity.status is NULL OR vtiger_activity.status not in" . "('Completed','Deferred')) and ( vtiger_activity.eventstatus is NULL OR vtiger_activity." . "eventstatus not in ('Held','Not Held') )" . $upcoming_condition;
} else {
$list_query = "select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity" . "setype, vtiger_recurringevents.recurringdate, vtiger_activity.activityid, vtiger_activity" . ".activitytype, vtiger_activity.date_start, vtiger_activity.due_date, from vtiger_activity" . "inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid " . "LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid " . "left outer join vtiger_recurringevents on vtiger_recurringevents.activityid=" . "vtiger_activity.activityid";
$list_query .= getNonAdminAccessControlQuery('Calendar', $current_user);
$list_query .= "WHERE vtiger_crmentity.deleted=0 and (vtiger_activity." . "activitytype not in ('Emails')) AND (vtiger_activity.status is NULL OR vtiger_activity." . "status not in ('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR " . "vtiger_activity.eventstatus not in ('Held','Not Held')) " . $pending_condition;
$list_query .= " GROUP BY vtiger_activity.activityid";
$list_query .= " ORDER BY date_start,time_start ASC";
$res = $adb->query($list_query);
$noofrecords = $adb->num_rows($res);
$open_activity_list = array();
$noofrows = $adb->num_rows($res);
if (count($res) > 0) {
for ($i = 0; $i < $noofrows; $i++) {
$dateValue = $adb->query_result($res, $i, 'date_start') . ' ' . $adb->query_result($res, $i, 'time_start');
$endDateValue = $adb->query_result($res, $i, 'due_date') . ' ' . $adb->query_result($res, $i, 'time_end');
$recurringDateValue = $adb->query_result($res, $i, 'due_date') . ' ' . $adb->query_result($res, $i, 'time_start');
$date = new DateTimeField($dateValue);
$endDate = new DateTimeField($endDateValue);
$recurringDate = new DateTimeField($recurringDateValue);
$open_activity_list[] = array('name' => $adb->query_result($res, $i, 'subject'), 'id' => $adb->query_result($res, $i, 'activityid'), 'type' => $adb->query_result($res, $i, 'activitytype'), 'module' => $adb->query_result($res, $i, 'setype'), 'date_start' => $date->getDisplayDate(), 'due_date' => $endDate->getDisplayDate(), 'recurringdate' => $recurringDate->getDisplayDate(), 'priority' => $adb->query_result($res, $i, 'priority'));
}
}
$title = array();
$title[] = $view;
$title[] = 'myUpcoPendAct.gif';
$title[] = 'home_myact';
$title[] = 'showActivityView';
$title[] = 'MyUpcumingFrm';
$title[] = 'activity_view';
$header = array();
$header[] = $current_module_strings['LBL_LIST_SUBJECT'];
$header[] = 'Type';
$return_url = "&return_module={$currentModule}&return_action=DetailView&return_id=" . (is_object($focus) ? $focus->id : "");
$oddRow = true;
$entries = array();
foreach ($open_activity_list as $event) {
$recur_date = preg_replace('/--/', '', $event['recurringdate']);
if ($recur_date != "") {
$event['date_start'] = $event['recurringdate'];
}
$font_color_high = "color:#00DD00;";
$font_color_medium = "color:#DD00DD;";
switch ($event['priority']) {
case 'High':
$font_color = $font_color_high;
break;
case 'Medium':
$font_color = $font_color_medium;
break;
default:
$font_color = '';
}
if ($event['type'] != 'Task' && $event['type'] != 'Emails' && $event['type'] != '') {
$activity_type = 'Events';
} else {
$activity_type = 'Task';
}
}
$entries[$event['id']] = array('0' => '<a href="index.php?action=DetailView&module=' . $event["module"] . '&activity_mode=' . $activity_type . '&record=' . $event["id"] . '' . $return_url . '" style="' . $font_color . ';">' . $event["name"] . '</a>', 'IMAGE' => '<IMG src="' . $image_path . $event["type"] . 's.gif">');
}
$values = array('noofactivities' => $noofrecords, 'Title' => $title, 'Header' => $header, 'Entries' => $entries);
$log->debug("Exiting getPendingActivities method ...");
return $values;
}
function getValidDBInsertDateTimeValue($value)
{
$value = trim($value);
$valueList = explode(' ', $value);
if (count($valueList) == 2) {
$dbDateValue = getValidDBInsertDateValue($valueList[0]);
$dbTimeValue = $valueList[1];
if (!empty($dbTimeValue) && strpos($dbTimeValue, ':') === false) {
$dbTimeValue = $dbTimeValue . ':';
}
$timeValueLength = strlen($dbTimeValue);
if (!empty($dbTimeValue) && strrpos($dbTimeValue, ':') == $timeValueLength - 1) {
$dbTimeValue = $dbTimeValue . '00';
}
try {
$dateTime = new DateTimeField($dbDateValue . ' ' . $dbTimeValue);
return $dateTime->getDBInsertDateTimeValue();
} catch (Exception $ex) {
return '';
}
} elseif (count($valueList == 1)) {
return getValidDBInsertDateValue($value);
}
}
/** Function to get the RunTime Advanced filter conditions
* @ param $advft_criteria : Type Array
* @ param $advft_criteria_groups : Type Array
* This function returns $advfiltersql
*
*/
function RunTimeAdvFilter($advft_criteria, $advft_criteria_groups)
{
$adb = PearDatabase::getInstance();
$advfilterlist = array();
$advfiltersql = '';
if (!empty($advft_criteria)) {
foreach ($advft_criteria as $column_index => $column_condition) {
if (empty($column_condition)) {
continue;
}
$adv_filter_column = $column_condition["columnname"];
$adv_filter_comparator = $column_condition["comparator"];
$adv_filter_value = $column_condition["value"];
$adv_filter_column_condition = $column_condition["columncondition"];
$adv_filter_groupid = $column_condition["groupid"];
$column_info = explode(":", $adv_filter_column);
$moduleFieldLabel = $column_info[2];
$fieldName = $column_info[3];
list($module, $fieldLabel) = explode('__', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if ($fieldType == 'currency') {
// Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion
if ($field->getUIType() == '72') {
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true);
} else {
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value);
}
}
$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 ($column_info[4] == 'D') {
$date = new DateTimeField(trim($temp_val[$x]));
$val[$x] = $date->getDBInsertDateValue();
} elseif ($column_info[4] == 'DT') {
$date = new DateTimeField(trim($temp_val[$x]));
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$date = new DateTimeField(trim($temp_val[$x]));
$val[$x] = $date->getDBInsertTimeValue();
}
}
$adv_filter_value = implode(",", $val);
}
$criteria = array();
$criteria['columnname'] = $adv_filter_column;
$criteria['comparator'] = $adv_filter_comparator;
$criteria['value'] = $adv_filter_value;
$criteria['column_condition'] = $adv_filter_column_condition;
$advfilterlist[$adv_filter_groupid]['columns'][] = $criteria;
}
foreach ($advft_criteria_groups as $group_index => $group_condition_info) {
if (empty($group_condition_info)) {
continue;
}
if (empty($advfilterlist[$group_index])) {
continue;
}
$advfilterlist[$group_index]['condition'] = $group_condition_info["groupcondition"];
$noOfGroupColumns = count($advfilterlist[$group_index]['columns']);
if (!empty($advfilterlist[$group_index]['columns'][$noOfGroupColumns - 1]['column_condition'])) {
$advfilterlist[$group_index]['columns'][$noOfGroupColumns - 1]['column_condition'] = '';
}
}
$noOfGroups = count($advfilterlist);
if (!empty($advfilterlist[$noOfGroups]['condition'])) {
$advfilterlist[$noOfGroups]['condition'] = '';
}
$advfiltersql = $this->generateAdvFilterSql($advfilterlist);
}
return $advfiltersql;
}
/**
*
* @param mixed $value
* @param String $operator
* @param WebserviceField $field
*/
private function getConditionValue($value, $operator, $field)
{
$operator = strtolower($operator);
$db = PearDatabase::getInstance();
$inEqualityFieldTypes = ['currency', 'percentage', 'double', 'integer', 'number'];
if (is_string($value) && $this->ignoreComma == false) {
$commaSeparatedFieldTypes = array('picklist', 'multipicklist', 'owner', 'date', 'datetime', 'time', 'tree');
if (in_array($field->getFieldDataType(), $commaSeparatedFieldTypes)) {
$valueArray = explode(',', $value);
if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) {
$valueArray = getCombinations($valueArray);
foreach ($valueArray as $key => $value) {
$valueArray[$key] = ltrim($value, ' |##| ');
}
}
} else {
$valueArray = array($value);
}
} elseif (is_array($value)) {
$valueArray = $value;
} else {
$valueArray = array($value);
}
$sql = array();
if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') {
if ($field->getFieldName() == 'birthday') {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
$sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')";
} else {
if ($this->isDateType($field->getFieldDataType())) {
$start = explode(' ', $valueArray[0]);
$end = explode(' ', $valueArray[1]);
if ($operator == 'between' && count($start) == 2 && count($end) == 2) {
$valueArray[0] = getValidDBInsertDateTimeValue($start[0] . ' ' . $start[1]);
if ($start[0] == $end[0]) {
$dateTime = new DateTime($valueArray[0]);
$nextDay = $dateTime->modify('+1 days');
$nextDay = strtotime($nextDay->format('Y-m-d H:i:s')) - 1;
$nextDay = date('Y-m-d H:i:s', $nextDay);
$values = explode(' ', $nextDay);
$valueArray[1] = getValidDBInsertDateTimeValue($values[0]) . ' ' . $values[1];
} else {
$end = $valueArray[1];
$dateObject = new DateTimeField($end);
$valueArray[1] = $dateObject->getDBInsertDateTimeValue();
}
} else {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$dateTimeStart = explode(' ', $valueArray[0]);
if ($dateTimeStart[1] == '00:00:00' && $operator != 'between') {
$valueArray[0] = $dateTimeStart[0];
}
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
$dateTimeEnd = explode(' ', $valueArray[1]);
if ($dateTimeEnd[1] == '00:00:00' || $dateTimeEnd[1] == '23:59:59') {
$valueArray[1] = $dateTimeEnd[0];
}
}
}
if ($operator == 'notequal') {
$sql[] = "NOT BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]);
} else {
$sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]);
}
}
return $sql;
}
foreach ($valueArray as $value) {
if (!$this->isStringType($field->getFieldDataType())) {
$value = trim($value);
}
if ($operator == 'empty' || $operator == 'y') {
$sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName()));
continue;
}
if ($operator == 'ny') {
$sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName()));
continue;
}
if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) {
if ($operator == 'e') {
$sql[] = "IS NULL";
continue;
}
$sql[] = "IS NOT NULL";
continue;
} elseif ($field->getFieldDataType() == 'boolean') {
$value = strtolower($value);
if ($value == 'yes') {
$value = 1;
} elseif ($value == 'no') {
$value = 0;
}
} elseif ($this->isDateType($field->getFieldDataType())) {
// For "after" and "before" conditions
$values = explode(' ', $value);
if (($operator == 'a' || $operator == 'b') && count($values) == 2) {
if ($operator == 'a') {
// for after comparator we should check the date after the given
$dateTime = new DateTime($value);
$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);
} else {
$dateTime = new DateTime($value);
$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);
}
} else {
$value = getValidDBInsertDateTimeValue($value);
$dateTime = explode(' ', $value);
if ($dateTime[1] == '00:00:00') {
$value = $dateTime[0];
}
}
} else {
if (in_array($field->getFieldDataType(), $inEqualityFieldTypes)) {
if ($operator == 'g' || $operator == 'l') {
$value = substr($value, 4);
} else {
if ($operator == 'h' || $operator == 'm') {
$value = substr($value, 5);
}
}
} else {
if ($field->getFieldDataType() === 'currency') {
$uiType = $field->getUIType();
if ($uiType == 72) {
$value = CurrencyField::convertToDBFormat($value, null, true);
} elseif ($uiType == 71) {
$value = CurrencyField::convertToDBFormat($value);
}
}
}
}
if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) {
$value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')";
} else {
$value = $db->sql_escape_string($value);
}
if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) {
$sql[] = "LIKE ''";
continue;
}
if (trim($value) == '' && $operator == 'om' && in_array($field->getFieldName(), $this->ownerFields)) {
$sql[] = " = '" . Users_Record_Model::getCurrentUserModel()->get('id') . "'";
continue;
}
if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) {
$sql[] = "NOT LIKE ''";
continue;
}
switch ($operator) {
case 'e':
$sqlOperator = "=";
break;
case 'n':
$sqlOperator = "<>";
break;
case 's':
$sqlOperator = "LIKE";
$value = "{$value}%";
break;
case 'ew':
$sqlOperator = "LIKE";
$value = "%{$value}";
break;
case 'c':
$sqlOperator = "LIKE";
$value = "%{$value}%";
break;
case 'k':
$sqlOperator = "NOT LIKE";
$value = "%{$value}%";
break;
case 'l':
$sqlOperator = "<";
break;
case 'g':
$sqlOperator = ">";
break;
case 'm':
$sqlOperator = "<=";
break;
case 'h':
$sqlOperator = ">=";
break;
case 'a':
$sqlOperator = ">";
break;
case 'b':
$sqlOperator = "<";
break;
}
if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) {
$value = "'{$value}'";
}
if ($this->isNumericType($field->getFieldDataType()) && empty($value)) {
$value = '0';
}
$sql[] = "{$sqlOperator} {$value}";
}
return $sql;
}
/** Function to get the RunTime Advanced filter conditions
* @ param $advft_criteria : Type Array
* @ param $advft_criteria_groups : Type Array
* This function returns $advfiltersql
*/
function RunTimeAdvFilter($advft_criteria, $advft_criteria_groups)
{
$adb = PearDatabase::getInstance();
$advfilterlist = array();
if (!empty($advft_criteria)) {
foreach ($advft_criteria as $column_index => $column_condition) {
if (empty($column_condition)) {
continue;
}
$adv_filter_column = $column_condition["columnname"];
$adv_filter_comparator = $column_condition["comparator"];
$adv_filter_value = $column_condition["value"];
$adv_filter_column_condition = $column_condition["columncondition"];
$adv_filter_groupid = $column_condition["groupid"];
$column_info = explode(":", $adv_filter_column);
$moduleFieldLabel = $column_info[2];
$fieldName = $column_info[3];
list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if ($fieldType == 'currency' or $fieldType == 'double') {
$flduitype = $fieldInfo['uitype'];
if ($flduitype == '72' or $flduitype == 9 or $flduitype == 7) {
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true);
} else {
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value);
}
}
$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 ($column_info[4] == 'D') {
$date = new DateTimeField(trim($temp_val[$x]));
$val[$x] = $date->getDBInsertDateValue();
} elseif ($column_info[4] == 'DT') {
$date = new DateTimeField(trim($temp_val[$x]));
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$date = new DateTimeField(trim($temp_val[$x]));
$val[$x] = $date->getDBInsertTimeValue();
}
}
$adv_filter_value = implode(",", $val);
}
if ($fieldType == 'picklist' || $fieldType == 'multipicklist') {
if (!isValueInPicklist($adv_filter_value, $fieldName)) {
$adv_filter_value = getTranslationKeyFromTranslatedValue($module, $adv_filter_value);
}
}
$criteria = array();
$criteria['columnname'] = $adv_filter_column;
$criteria['comparator'] = $adv_filter_comparator;
$criteria['value'] = $adv_filter_value;
$criteria['column_condition'] = $adv_filter_column_condition;
$advfilterlist[$adv_filter_groupid]['columns'][] = $criteria;
}
foreach ($advft_criteria_groups as $group_index => $group_condition_info) {
if (empty($group_condition_info)) {
continue;
}
if (empty($advfilterlist[$group_index])) {
continue;
}
$advfilterlist[$group_index]['condition'] = $group_condition_info["groupcondition"];
$noOfGroupColumns = count($advfilterlist[$group_index]['columns']);
if (!empty($advfilterlist[$group_index]['columns'][$noOfGroupColumns - 1]['column_condition'])) {
$advfilterlist[$group_index]['columns'][$noOfGroupColumns - 1]['column_condition'] = '';
}
}
$noOfGroups = count($advfilterlist);
if (!empty($advfilterlist[$noOfGroups]['condition'])) {
$advfilterlist[$noOfGroups]['condition'] = '';
}
$advfiltersql = $this->generateAdvFilterSql($advfilterlist);
}
return $advfiltersql;
}
function getRequestData($return_id)
{
global $adb;
$cont_qry = "select * from vtiger_cntactivityrel where activityid=?";
$cont_res = $adb->pquery($cont_qry, array($return_id));
$noofrows = $adb->num_rows($cont_res);
$cont_id = array();
if ($noofrows > 0) {
for ($i = 0; $i < $noofrows; $i++) {
$cont_id[] = $adb->query_result($cont_res, $i, "contactid");
}
}
$cont_name = '';
foreach ($cont_id as $key => $id) {
if ($id != '') {
$displayValueArray = getEntityName('Contacts', $id);
if (!empty($displayValueArray)) {
foreach ($displayValueArray as $key => $field_value) {
$contact_name = $field_value;
}
}
$cont_name .= $contact_name . ', ';
}
}
$cont_name = trim($cont_name, ', ');
$mail_data = array();
$mail_data['user_id'] = $_REQUEST['assigned_user_id'];
$mail_data['subject'] = $_REQUEST['subject'];
$mail_data['status'] = $_REQUEST['activity_mode'] == 'Task' ? $_REQUEST['taskstatus'] : $_REQUEST['eventstatus'];
$mail_data['activity_mode'] = $_REQUEST['activity_mode'];
$mail_data['taskpriority'] = $_REQUEST['taskpriority'];
$mail_data['relatedto'] = $_REQUEST['parent_name'];
$mail_data['contact_name'] = $cont_name;
$mail_data['description'] = $_REQUEST['description'];
$mail_data['assign_type'] = $_REQUEST['assigntype'];
$mail_data['group_name'] = getGroupName($_REQUEST['assigned_group_id']);
$mail_data['mode'] = $_REQUEST['mode'];
$value = getaddEventPopupTime($_REQUEST['time_start'], $_REQUEST['time_end'], '24');
$start_hour = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt'];
if ($_REQUEST['activity_mode'] != 'Task') {
$end_hour = $value['endhour'] . ':' . $value['endmin'] . '' . $value['endfmt'];
}
$startDate = new DateTimeField($_REQUEST['date_start'] . " " . $start_hour);
$endDate = new DateTimeField($_REQUEST['due_date'] . " " . $end_hour);
$mail_data['st_date_time'] = $startDate->getDBInsertDateTimeValue();
$mail_data['end_date_time'] = $endDate->getDBInsertDateTimeValue();
$mail_data['location'] = vtlib_purify($_REQUEST['location']);
return $mail_data;
}
/**
* function to get pending activities for today
* @param integer $maxval - the maximum number of records to display
* @param integer $calCnt - returns the count query if this is set
* return array $values - activities record in array format
*/
function homepage_getPendingActivities($maxval, $calCnt)
{
require_once "data/Tracker.php";
require_once "include/utils/utils.php";
require_once 'include/utils/CommonUtils.php';
global $adb;
global $current_user;
$dbStartDateTime = new DateTimeField(date('Y-m-d H:i:s'));
$userStartDate = $dbStartDateTime->getDisplayDate();
$userStartDateTime = new DateTimeField($userStartDate . ' 00:00:00');
$startDateTime = $userStartDateTime->getDBInsertDateTimeValue();
$userEndDateTime = new DateTimeField($userStartDate . ' 23:59:00');
$endDateTime = $userEndDateTime->getDBInsertDateTimeValue();
$pending_condition = " AND (CAST((CONCAT(date_start,' ',time_start)) AS DATETIME) BETWEEN '{$startDateTime}' AND '{$endDateTime}'\n\t\t\t\t\t\t\t\t\tOR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) BETWEEN '{$startDateTime}' AND '{$endDateTime}')";
$list_query = "select vtiger_crmentity.crmid,vtiger_crmentity.smownerid,vtiger_crmentity." . "setype, vtiger_recurringevents.recurringdate, vtiger_activity.* from vtiger_activity " . "inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid LEFT " . "JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid left outer join " . "vtiger_recurringevents on vtiger_recurringevents.activityid=vtiger_activity.activityid" . ($list_query .= getNonAdminAccessControlQuery('Calendar', $current_user));
$list_query .= "WHERE vtiger_crmentity.deleted=0 and (vtiger_activity.activitytype not in " . "('Emails')) AND (vtiger_activity.status is NULL OR vtiger_activity.status not in " . "('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR vtiger_activity." . "eventstatus not in ('Held','Not Held')) " . $pending_condition;
$list_query .= " GROUP BY vtiger_activity.activityid";
$list_query .= " ORDER BY date_start,time_start ASC";
$list_query .= " limit {$maxval}";
$res = $adb->query($list_query);
$noofrecords = $adb->num_rows($res);
if ($calCnt == 'calculateCnt') {
return $noofrecords;
}
$open_activity_list = array();
$noofrows = $adb->num_rows($res);
if (count($res) > 0) {
for ($i = 0; $i < $noofrows; $i++) {
$dateValue = $adb->query_result($res, $i, 'date_start') . ' ' . $adb->query_result($res, $i, 'time_start');
$endDateValue = $adb->query_result($res, $i, 'due_date') . ' ' . $adb->query_result($res, $i, 'time_end');
$recurringDateValue = $adb->query_result($res, $i, 'due_date') . ' ' . $adb->query_result($res, $i, 'time_start');
$date = new DateTimeField($dateValue);
$endDate = new DateTimeField($endDateValue);
$recurringDate = new DateTimeField($recurringDateValue);
$open_activity_list[] = array('name' => $adb->query_result($res, $i, 'subject'), 'id' => $adb->query_result($res, $i, 'activityid'), 'type' => $adb->query_result($res, $i, 'activitytype'), 'module' => $adb->query_result($res, $i, 'setype'), 'date_start' => $date->getDisplayDate(), 'due_date' => $endDate->getDisplayDate(), 'recurringdate' => $recurringDate->getDisplayDate(), 'priority' => $adb->query_result($res, $i, 'priority'));
}
}
$values = getActivityEntries($open_activity_list);
$values['ModuleName'] = 'Calendar';
$values['search_qry'] = "&action=ListView&from_homepage=pending_activities";
return $values;
}
/**
* 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() == '71') {
$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') {
$val[$i] = $date->getDBInsertDateTimeValue();
} 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));
}
}
}
function getValidDBInsertDateTimeValue($value)
{
$valueList = explode(' ', $value);
$date = new DateTimeField($value);
if (count($valueList) == 2) {
$value = $date->getDBInsertDateTimeValue();
} elseif (count($valueList == 1)) {
$value = $date->getDBInsertDateValue();
}
return $value;
}
private function convertRecordToNativeFormat($module, $record)
{
if ($module == 'Events') {
$record['start_time'] = $record['date_start'] . " " . $record['time_start'];
$record['end_time'] = $record['due_date'] . " " . $record['time_end'];
} else {
if ($module == 'Calendar') {
$dformat = "Y-m-d H:i:s";
$record['start_time'] = date($dformat, strtotime($record['date_start']));
$record['end_time'] = date($dformat, strtotime($record['due_date']));
// convert the start time and end time to user time zone as outlook does not take the datetime in utc
$oldDateFormat = $this->user->date_format;
$this->user->date_format = 'yyyy-mm-dd';
$startDateTimeField = new DateTimeField($record['start_time']);
$record['start_time'] = $startDateTimeField->getDBInsertDateTimeValue($this->user);
$endDateTimeField = new DateTimeField($record['end_time']);
$record['end_time'] = $endDateTimeField->getDBInsertDateTimeValue($this->user);
$this->user->date_format = $oldDateFormat;
}
}
return $record;
}
$list_query = $oCustomView->getModifiedCvListQuery($viewid, $list_query, "Calendar");
}
if (isset($where) && $where != '') {
if (isset($_REQUEST['from_homepagedb']) && $_REQUEST['from_homepagedb'] == 'true') {
$list_query .= " and ((vtiger_activity.status!='Completed' and vtiger_activity.status!='Deferred') or vtiger_activity.status is null) and ((vtiger_activity.eventstatus!='Held' and vtiger_activity.eventstatus!='Not Held') or vtiger_activity.eventstatus is null) AND " . $where;
} else {
$list_query .= " AND " . $where;
}
}
if (isset($_REQUEST['from_homepage'])) {
$dbStartDateTime = new DateTimeField(date('Y-m-d H:i:s'));
$userStartDate = $dbStartDateTime->getDisplayDate();
$userStartDateTime = new DateTimeField($userStartDate . ' 00:00:00');
$startDateTime = $userStartDateTime->getDBInsertDateTimeValue();
$userEndDateTime = new DateTimeField($userStartDate . ' 23:59:00');
$endDateTime = $userEndDateTime->getDBInsertDateTimeValue();
if ($_REQUEST['from_homepage'] == 'upcoming_activities') {
$list_query .= " AND (vtiger_activity.status is NULL OR vtiger_activity.status not in ('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus not in ('Held','Not Held')) AND (CAST((CONCAT(date_start,' ',time_start)) AS DATETIME) >= '{$startDateTime}' OR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) >= '{$startDateTime}')";
} elseif ($_REQUEST['from_homepage'] == 'pending_activities') {
$list_query .= " AND (vtiger_activity.status is NULL OR vtiger_activity.status not in ('Completed','Deferred')) and (vtiger_activity.eventstatus is NULL OR vtiger_activity.eventstatus not in ('Held','Not Held')) AND (CAST((CONCAT(due_date,' ',time_end)) AS DATETIME) <= '{$endDateTime}' OR CAST((CONCAT(vtiger_recurringevents.recurringdate,' ',time_start)) AS DATETIME) <= '{$endDateTime}')";
}
}
if (isset($order_by) && $order_by != '') {
if ($order_by == 'smownerid') {
$list_query .= ' ORDER BY user_name ' . $sorder;
} else {
$tablename = getTableNameForField('Calendar', $order_by);
$tablename = $tablename != '' ? $tablename . "." : '';
if ($order_by == 'lastname') {
$list_query .= ' ORDER BY vtiger_contactdetails.lastname ' . $sorder;
} else {
/** To get the events of the specified user and shared events
* @param $userid -- The user Id:: Type integer
* @param $from_datetime -- The start date Obj :: Type Array
* @param $to_datetime -- The end date Obj :: Type Array
* @param $view -- The calendar view :: Type String
* @returns $list :: Type Array
*/
function readAppointment($userid, &$from_datetime, &$to_datetime, $view)
{
global $current_user, $adb;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$and = "AND (\n\t\t\t\t\t(\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) >= ? AND CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ? )\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t\t)\n\t\t\t\t\t\tAND vtiger_recurringevents.activityid is NULL\n\t\t\t\t\t)\n\t\t\t\tOR (\n\t\t\t\t\t\t(CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) >= ?\n\t\t\t\t\t\t\tAND CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ?)\n\t\t\t\t\t\tOR\t(CAST(CONCAT(vtiger_recurringevents.recurringdate,' ',time_start) AS DATETIME) <= ?\n\t\t\t\t\t\t\tAND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t)\n\t\t\t\t)";
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$q = "select vtiger_activity.*, vtiger_crmentity.*,\n\t\t\t\t\tcase when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name\n\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tinner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid\n\t\t\t\t\t\tleft join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid\n\t\t\t\t\t\tleft join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') {$and} ";
// User Select Customization: Changes should made also in (calendayLaout getEventList) and one more BELOW
$query_filter_prefix = calendarview_getSelectedUserFilterQuerySuffix();
$q .= $query_filter_prefix;
// END
$h = $from_datetime->z_hour;
$m = $from_datetime->min;
if (empty($m)) {
$m = '00';
}
$startDate = new DateTimeField($from_datetime->year . "-" . $from_datetime->z_month . "-" . $from_datetime->z_day . " {$h}:{$m}");
$h = '23';
$m = '59';
$endDate = new DateTimeField($to_datetime->year . "-" . $to_datetime->z_month . "-" . $to_datetime->z_day . " {$h}:{$m}");
$params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue());
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[16] == 3) {
//Added for User Based Custom View for Calendar
$sec_parameter = getCalendarViewSecurityParameter();
$q .= $sec_parameter;
}
$q .= " AND vtiger_recurringevents.activityid is NULL ";
$q .= " group by vtiger_activity.activityid ORDER by vtiger_activity.date_start,vtiger_activity.time_start";
$r = $adb->pquery($q, $params);
$n = $adb->getRowCount($r);
$a = 0;
$list = array();
while ($a < $n) {
$result = $adb->fetchByAssoc($r);
$from = strtotime($result['date_start']);
$to = strtotime($result['due_date'] . ' ' . $result["time_end"]);
$windowTo = strtotime($endDate->getDBInsertDateTimeValue());
for ($j = $from; $j <= $to; $j = $j + 60 * 60 * 24) {
$obj =& new Appointment();
$temp_start = date("Y-m-d", $j);
$endTime = strtotime($temp_start . ' ' . $result['time_start']);
if ($endTime > $windowTo) {
break;
}
$result["date_start"] = $temp_start;
list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]);
if ($start_timestamp != $end_timestamp && $view == 'day') {
if ($j == $start_timestamp) {
$result["duration_hours"] = 24 - $obj->temphour;
} elseif ($j > $start_timestamp && $j < $end_timestamp) {
list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00");
$result["duration_hours"] = 24 - $obj->temphour;
} elseif ($j == $end_timestamp) {
list($obj->temphour, $obj->tempmin) = $current_user->start_hour != '' ? explode(":", $current_user->start_hour) : explode(":", "08:00");
list($ehr, $emin) = explode(":", $result["time_end"]);
$result["duration_hours"] = $ehr - $obj->temphour;
}
}
$obj->readResult($result, $view);
$list[] = $obj;
unset($obj);
}
$a++;
}
//Get Recurring events
$q = "SELECT vtiger_activity.*, vtiger_crmentity.*, case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end as user_name , vtiger_recurringevents.recurringid, vtiger_recurringevents.recurringdate as date_start ,vtiger_recurringevents.recurringtype,vtiger_groups.groupname from vtiger_activity inner join vtiger_crmentity on vtiger_activity.activityid = vtiger_crmentity.crmid inner join vtiger_recurringevents on vtiger_activity.activityid=vtiger_recurringevents.activityid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid";
$q .= getNonAdminAccessControlQuery('Calendar', $current_user);
$q .= " where vtiger_crmentity.deleted = 0 and vtiger_activity.activitytype not in ('Emails','Task') AND (cast(concat(recurringdate, ' ', time_start) as datetime) between ? and ?) ";
// User Select Customization
$q .= $query_filter_prefix;
// END
$params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue());
$q .= " ORDER by vtiger_recurringevents.recurringid";
$r = $adb->pquery($q, $params);
$n = $adb->getRowCount($r);
$a = 0;
while ($a < $n) {
$obj =& new Appointment();
$result = $adb->fetchByAssoc($r);
list($obj->temphour, $obj->tempmin) = explode(":", $result["time_start"]);
$obj->readResult($result, $view);
$a++;
$list[] = $obj;
unset($obj);
}
usort($list, 'compare');
return $list;
}
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value);
}
}
$temp_val = explode(",", $adv_filter_value);
if (($fieldType == 'date' || $fieldType == 'time' && $fieldName != 'time_start' && $fieldName != 'time_end' || $fieldType == 'datetime') && ($fieldType != '' && $adv_filter_value != '')) {
$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::convertToUserFormat(trim($temp_val[$x]));
} elseif ($fieldType == 'datetime') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$adv_filter_value = implode(",", $val);
}
$irelcriteriasql = "INSERT INTO vtiger_cvadvfilter(cvid,columnindex,columnname,comparator,value,groupid,column_condition) values (?,?,?,?,?,?,?)";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($genCVid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
// 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;
public function getInviteUserMailData()
{
$adb = PearDatabase::getInstance();
$return_id = $this->getId();
$cont_qry = "select * from vtiger_cntactivityrel where activityid=?";
$cont_res = $adb->pquery($cont_qry, array($return_id));
$noofrows = $adb->num_rows($cont_res);
$cont_id = array();
if ($noofrows > 0) {
for ($i = 0; $i < $noofrows; $i++) {
$cont_id[] = $adb->query_result($cont_res, $i, "contactid");
}
}
$cont_name = '';
foreach ($cont_id as $key => $id) {
if ($id != '') {
$contact_name = Vtiger_Util_Helper::getRecordName($id);
$cont_name .= $contact_name . ', ';
}
}
$parentId = $this->get('parent_id');
$parentName = '';
if ($parentId != '') {
$parentName = Vtiger_Util_Helper::getRecordName($parentId);
}
$cont_name = trim($cont_name, ', ');
$mail_data = array();
$mail_data['user_id'] = $this->get('assigned_user_id');
$mail_data['subject'] = $this->get('subject');
$moduleName = $this->getModuleName();
$mail_data['status'] = $moduleName == 'Calendar' ? $this->get('taskstatus') : $this->get('eventstatus');
$mail_data['activity_mode'] = $moduleName == 'Calendar' ? 'Task' : 'Events';
$mail_data['taskpriority'] = $this->get('taskpriority');
$mail_data['relatedto'] = $parentName;
$mail_data['contact_name'] = $cont_name;
$mail_data['description'] = $this->get('description');
$mail_data['assign_type'] = $this->get('assigntype');
$mail_data['group_name'] = getGroupName($this->get('assigned_user_id'));
$mail_data['mode'] = $this->get('mode');
//TODO : remove dependency on request;
$value = getaddEventPopupTime($_REQUEST['time_start'], $_REQUEST['time_end'], '24');
$start_hour = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt'];
if ($_REQUEST['activity_mode'] != 'Task') {
$end_hour = $value['endhour'] . ':' . $value['endmin'] . '' . $value['endfmt'];
}
$startDate = new DateTimeField($_REQUEST['date_start'] . " " . $start_hour);
$endDate = new DateTimeField($_REQUEST['due_date'] . " " . $end_hour);
$mail_data['st_date_time'] = $startDate->getDBInsertDateTimeValue();
$mail_data['end_date_time'] = $endDate->getDBInsertDateTimeValue();
$mail_data['location'] = $this->get('location');
return $mail_data;
}
/** to get the customview stdFilter Query for the given customview Id
* @param $cvid :: Type Integer
* @returns $stdfiltersql as a string
* This function will return the standard filter criteria for the given customfield
*
*/
function getCVStdFilterSQL($cvid)
{
global $adb;
$stdfiltersql = '';
$stdfilterlist = array();
$sSQL = "select vtiger_cvstdfilter.* from vtiger_cvstdfilter inner join vtiger_customview on vtiger_customview.cvid = vtiger_cvstdfilter.cvid";
$sSQL .= " where vtiger_cvstdfilter.cvid=?";
$result = $adb->pquery($sSQL, array($cvid));
$stdfilterrow = $adb->fetch_array($result);
$stdfilterlist = array();
$stdfilterlist["columnname"] = $stdfilterrow["columnname"];
$stdfilterlist["stdfilter"] = $stdfilterrow["stdfilter"];
if ($stdfilterrow["stdfilter"] == "custom" || $stdfilterrow["stdfilter"] == "") {
if ($stdfilterrow["startdate"] != "0000-00-00" && $stdfilterrow["startdate"] != "") {
$stdfilterlist["startdate"] = $stdfilterrow["startdate"];
}
if ($stdfilterrow["enddate"] != "0000-00-00" && $stdfilterrow["enddate"] != "") {
$stdfilterlist["enddate"] = $stdfilterrow["enddate"];
}
} else {
//if it is not custom get the date according to the selected duration
$datefilter = $this->getDateforStdFilterBytype($stdfilterrow["stdfilter"]);
$stdfilterlist["startdate"] = $datefilter[0];
$stdfilterlist["enddate"] = $datefilter[1];
}
if (isset($stdfilterlist)) {
foreach ($stdfilterlist as $columnname => $value) {
if ($columnname == "columnname") {
$filtercolumn = $value;
} elseif ($columnname == "stdfilter") {
$filtertype = $value;
} elseif ($columnname == "startdate") {
$startDateTime = new DateTimeField($value . ' ' . date('H:i:s'));
$userStartDate = $startDateTime->getDisplayDate();
$userStartDateTime = new DateTimeField($userStartDate . ' 00:00:00');
$startDateTime = $userStartDateTime->getDBInsertDateTimeValue();
} elseif ($columnname == "enddate") {
$endDateTime = new DateTimeField($value . ' ' . date('H:i:s'));
$userEndDate = $endDateTime->getDisplayDate();
$userEndDateTime = new DateTimeField($userEndDate . ' 23:59:00');
$endDateTime = $userEndDateTime->getDBInsertDateTimeValue();
}
if ($startDateTime != "" && $endDateTime != "") {
$columns = explode(":", $filtercolumn);
// Fix for http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/5423
if ($columns[1] == 'birthday') {
$tableColumnSql = "DATE_FORMAT(" . $columns[0] . "." . $columns[1] . ", '%m%d')";
$startDateTime = "DATE_FORMAT('{$startDate}', '%m%d')";
$endDateTime = "DATE_FORMAT('{$endDate}', '%m%d')";
$stdfiltersql = $tableColumnSql . " BETWEEN " . $startDateTime . " and " . $endDateTime;
} else {
if ($this->customviewmodule == 'Calendar' && ($columns[1] == 'date_start' || $columns[1] == 'due_date')) {
$tableColumnSql = '';
if ($columns[1] == 'date_start') {
$tableColumnSql = "CAST((CONCAT(date_start,' ',time_start)) AS DATETIME)";
} else {
$tableColumnSql = "CAST((CONCAT(due_date,' ',time_end)) AS DATETIME)";
}
} else {
$tableColumnSql = $columns[0] . "." . $columns[1];
}
$stdfiltersql = $tableColumnSql . " BETWEEN '" . $startDateTime . "' and '" . $endDateTime . "'";
}
}
}
}
return $stdfiltersql;
}
/**
* 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 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;
}
function updateAdvancedCriteria($reportid, $advft_criteria, $advft_criteria_groups)
{
global $adb, $log;
$idelrelcriteriasql = "delete from vtiger_relcriteria where queryid=?";
$idelrelcriteriasqlresult = $adb->pquery($idelrelcriteriasql, array($reportid));
$idelrelcriteriagroupsql = "delete from vtiger_relcriteria_grouping where queryid=?";
$idelrelcriteriagroupsqlresult = $adb->pquery($idelrelcriteriagroupsql, array($reportid));
if (empty($advft_criteria)) {
return;
}
foreach ($advft_criteria as $column_index => $column_condition) {
if (empty($column_condition)) {
continue;
}
$adv_filter_column = $column_condition["columnname"];
$adv_filter_comparator = $column_condition["comparator"];
$adv_filter_value = $column_condition["value"];
$adv_filter_column_condition = $column_condition["columncondition"];
$adv_filter_groupid = $column_condition["groupid"];
$column_info = explode(":", $adv_filter_column);
$moduleFieldLabel = $column_info[2];
$fieldName = $column_info[3];
list($module, $fieldLabel) = explode('__', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if ($fieldType == 'currency') {
// Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion
if ($field->getUIType() == '72') {
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true);
} else {
$adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value);
}
}
$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::convertToUserFormat(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 vtiger_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) values (?,?,?,?,?,?,?)";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($reportid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
// 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;
}
foreach ($advft_criteria_groups as $group_index => $group_condition_info) {
if (empty($group_condition_info)) {
continue;
}
if (empty($group_condition_info["conditionexpression"])) {
continue;
}
// Case when the group doesn't have any column criteria
$irelcriteriagroupsql = "insert into vtiger_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) values (?,?,?,?)";
$irelcriteriagroupresult = $adb->pquery($irelcriteriagroupsql, array($group_index, $reportid, $group_condition_info["groupcondition"], $group_condition_info["conditionexpression"]));
}
}
/**
* Function to get todos list scheduled between specified dates
* @param array $calendar - collection of objects and strings
* @param string $start_date - date string
* @param string $end_date - date string
* @param string $info - string 'listcnt' or empty string. if 'listcnt' means it returns no. of todos and no. of pending todos in array format else it returns todos list in array format
* return array $Entries - todolists in array format
*/
function getTodoList(&$calendar, $start_date, $end_date, $info = '')
{
global $log, $app_strings, $theme;
$Entries = array();
$category = getParentTab();
global $adb, $current_user, $mod_strings, $cal_log, $list_max_entries_per_page;
$cal_log->debug("Entering getTodoList() method...");
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT vtiger_groups.groupname, {$userNameSql} as user_name, vtiger_crmentity.crmid, vtiger_cntactivityrel.contactid,\n\t\t\t\tvtiger_activity.* FROM vtiger_activity\n INNER JOIN vtiger_crmentity\n\t\t\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n LEFT JOIN vtiger_cntactivityrel\n\t\t\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_users\n\t\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid";
$query .= getNonAdminAccessControlQuery('Calendar', $current_user);
$query .= "WHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype = 'Task'" . " AND ((CAST(CONCAT(date_start,' ',time_start) AS DATETIME) >= ? AND CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ?)\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) <= ? )\n\t\t\t\t\t\t\tOR\t(CAST(CONCAT(date_start,' ',time_start) AS DATETIME) <= ? AND CAST(CONCAT(due_date,' ',time_end) AS DATETIME) >= ?)\n\t\t\t\t\t\t)";
$list_query = $query . " AND vtiger_crmentity.smownerid = " . $current_user->id;
$startDate = new DateTimeField($start_date . ' 00:00');
$endDate = new DateTimeField($end_date . ' 23:59');
$params = $info_params = array($startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue(), $startDate->getDBInsertDateTimeValue(), $endDate->getDBInsertDateTimeValue());
if ($info != '') {
//added to fix #4816
$groupids = explode(",", fetchUserGroupids($current_user->id));
if (count($groupids) > 0 && !is_admin($current_user)) {
$com_q = " AND (vtiger_crmentity.smownerid = ?\n\t\t\t\t\tOR vtiger_groups.groupid in (" . generateQuestionMarks($groupids) . "))";
array_push($info_params, $current_user->id);
array_push($info_params, $groupids);
} elseif (!is_admin($current_user)) {
$com_q = " AND vtiger_crmentity.smownerid = ?";
array_push($info_params, $current_user->id);
}
//end
$pending_query = $query . " AND (vtiger_activity.status != 'Completed')" . $com_q;
$total_q = $query . "" . $com_q;
$total_res = $adb->pquery($total_q, $info_params);
$total = $adb->num_rows($total_res);
$res = $adb->pquery($pending_query, $info_params);
$pending_rows = $adb->num_rows($res);
$cal_log->debug("Exiting getTodoList() method...");
return array('totaltodo' => $total, 'pendingtodo' => $pending_rows);
}
$group_cond = '';
$group_cond .= " ORDER BY vtiger_activity.date_start,vtiger_activity.time_start ASC";
if (isset($_REQUEST['start']) && $_REQUEST['start'] != '') {
$start = vtlib_purify($_REQUEST['start']);
} else {
$start = 1;
}
//T6477 changes
if (PerformancePrefs::getBoolean('LISTVIEW_COMPUTE_PAGE_COUNT', false) === true) {
$count_res = $adb->pquery(mkCountQuery($query), $params);
$total_rec_count = $adb->query_result($count_res, 0, 'count');
} else {
$total_rec_count = null;
}
$navigation_array = VT_getSimpleNavigationValues($start, $list_max_entries_per_page, $total_rec_count);
$start_rec = ($start - 1) * $list_max_entries_per_page;
$end_rec = $navigation_array['end_val'];
$list_query = $adb->convert2Sql($query, $params);
$_SESSION['Calendar_listquery'] = $list_query;
if ($start_rec < 0) {
$start_rec = 0;
}
//ends
$query .= $group_cond . " limit {$start_rec},{$list_max_entries_per_page}";
$result = $adb->pquery($query, $params);
$rows = $adb->num_rows($result);
$c = 0;
if ($start > 1) {
$c = ($start - 1) * $list_max_entries_per_page;
}
for ($i = 0; $i < $rows; $i++) {
$element = array();
$contact_name = '';
$element['no'] = $c + 1;
$more_link = "";
$start_time = $adb->query_result($result, $i, "time_start");
$date_start = $adb->query_result($result, $i, "date_start");
$due_date = $adb->query_result($result, $i, "due_date");
$date = new DateTimeField($date_start . ' ' . $start_time);
$endDate = new DateTimeField($due_date);
if (!empty($start_time)) {
$start_time = $date->getDisplayTime();
}
$format = $calendar['calendar']->hour_format;
$value = getaddEventPopupTime($start_time, $start_time, $format);
$element['starttime'] = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt'];
$element['startdate'] = $date->getDisplayDate();
$element['duedate'] = $endDate->getDisplayDate();
$id = $adb->query_result($result, $i, "activityid");
$subject = $adb->query_result($result, $i, "subject");
$more_link = "<a href='index.php?action=DetailView&module=Calendar&record=" . $id . "&activity_mode=Task&viewtype=calendar&parenttab=" . $category . "' class='webMnu'>" . $subject . "</a>";
$element['tododetail'] = $more_link;
if (getFieldVisibilityPermission('Calendar', $current_user->id, 'taskstatus') == '0') {
$taskstatus = $adb->query_result($result, $i, "status");
if (!$is_admin && $taskstatus != '') {
$roleid = $current_user->roleid;
$roleids = array();
$subrole = getRoleSubordinates($roleid);
if (count($subrole) > 0) {
$roleids = $subrole;
}
array_push($roleids, $roleid);
//here we are checking wheather the table contains the sortorder column .If sortorder is present in the main picklist table, then the role2picklist will be applicable for this table...
$sql = "select * from vtiger_taskstatus where taskstatus=?";
$res = $adb->pquery($sql, array(decode_html($taskstatus)));
$picklistvalueid = $adb->query_result($res, 0, 'picklist_valueid');
if ($picklistvalueid != null) {
$pick_query = "select * from vtiger_role2picklist where picklistvalueid={$picklistvalueid} and roleid in (" . generateQuestionMarks($roleids) . ")";
$res_val = $adb->pquery($pick_query, array($roleids));
$num_val = $adb->num_rows($res_val);
}
if ($num_val > 0) {
$element['status'] = getTranslatedString(decode_html($taskstatus));
} else {
$element['status'] = "<font color='red'>" . $app_strings['LBL_NOT_ACCESSIBLE'] . "</font>";
}
} else {
$element['status'] = getTranslatedString(decode_html($taskstatus));
}
}
if (isPermitted("Calendar", "EditView") == "yes" || isPermitted("Calendar", "Delete") == "yes") {
$element['action'] = "<img onClick='getcalAction(this,\"taskcalAction\"," . $id . ",\"" . $calendar['view'] . "\",\"" . $calendar['calendar']->date_time->hour . "\",\"" . $calendar['calendar']->date_time->get_DB_formatted_date() . "\",\"todo\");' src='" . vtiger_imageurl('cal_event.jpg', $theme) . "' border='0'>";
}
$assignedto = $adb->query_result($result, $i, "user_name");
if (!empty($assignedto)) {
$element['assignedto'] = $assignedto;
} else {
$element['assignedto'] = $adb->query_result($result, $i, "groupname");
}
$c++;
$Entries[] = $element;
}
$ret_arr[0] = $Entries;
$ret_arr[1] = $navigation_array;
$cal_log->debug("Exiting getTodoList() method...");
return $ret_arr;
}
/**
* Function to get Date and Time value for Display
* @param <type> $date
* @return <String>
*/
public static function getDBDateTimeValue($date)
{
$date = new DateTimeField($date);
return $date->getDBInsertDateTimeValue();
}
/** Function to get the 5 New Leads
*return array $values - array with the title, header and entries like Array('Title'=>$title,'Header'=>$listview_header,'Entries'=>$listview_entries) where as listview_header and listview_entries are arrays of header and entity values which are returned from function getListViewHeader and getListViewEntries
*/
function getNewLeads($maxval, $calCnt)
{
global $log;
$log->debug("Entering getNewLeads() method ...");
require_once "data/Tracker.php";
require_once "include/utils/utils.php";
global $adb, $current_language, $current_user;
$current_module_strings = return_module_language($current_language, 'Leads');
if ($_REQUEST['lead_view'] == '') {
$query = "select lead_view from vtiger_users where id =?";
$result = $adb->pquery($query, array($current_user->id));
$lead_view = $adb->query_result($result, 0, 'lead_view');
} else {
$lead_view = $_REQUEST['lead_view'];
}
$today = date("Y-m-d", time());
if ($lead_view == 'Last 2 Days') {
$dbStartDateTime = new DateTimeField(date("Y-m-d H:i:s", strtotime("-2 days")));
} else {
if ($lead_view == 'Last Week') {
$dbStartDateTime = new DateTimeField(date("Y-m-d H:i:s", strtotime("-1 week")));
} else {
$dbStartDateTime = new DateTimeField(date("Y-m-d H:i:s", strtotime("{$today}")));
}
}
$userStartDate = $dbStartDateTime->getDisplayDate();
$userStartDateTime = new DateTimeField($userStartDate . ' 00:00:00');
$startDateTime = $userStartDateTime->getDBInsertDateTimeValue();
$list_query = 'select vtiger_leaddetails.firstname, vtiger_leaddetails.lastname, vtiger_leaddetails.leadid, vtiger_leaddetails.company
from vtiger_leaddetails inner join vtiger_crmentity on vtiger_leaddetails.leadid = vtiger_crmentity.crmid
where vtiger_crmentity.deleted =0 AND vtiger_leaddetails.converted =0 AND vtiger_leaddetails.leadid > 0 AND
vtiger_leaddetails.leadstatus not in ("Lost Lead", "Junk Lead","' . $current_module_strings['Lost Lead'] . '","' . $current_module_strings['Junk Lead'] . '")
AND vtiger_crmentity.createdtime >=? AND vtiger_crmentity.smownerid = ?';
$list_query .= " LIMIT 0," . $adb->sql_escape_string($maxval);
if ($calCnt == 'calculateCnt') {
$list_result_rows = $adb->pquery(mkCountQuery($list_query), array($startDateTime, $current_user->id));
return $adb->query_result($list_result_rows, 0, 'count');
}
$list_result = $adb->pquery($list_query, array($startDateTime, $current_user->id));
$noofrows = $adb->num_rows($list_result);
$open_lead_list = array();
if ($noofrows > 0) {
for ($i = 0; $i < $noofrows && $i < $maxval; $i++) {
$open_lead_list[] = array('leadname' => $adb->query_result($list_result, $i, 'firstname') . ' ' . $adb->query_result($list_result, $i, 'lastname'), 'company' => $adb->query_result($list_result, $i, 'company'), 'id' => $adb->query_result($list_result, $i, 'leadid'));
}
}
$header = array();
$header[] = $current_module_strings['LBL_LIST_LEAD_NAME'];
$header[] = $current_module_strings['Company'];
$entries = array();
foreach ($open_lead_list as $lead) {
$value = array();
$lead_fields = array('LEAD_NAME' => $lead['leadname'], 'COMPANY' => $lead['company'], 'LEAD_ID' => $lead['id']);
$Top_Leads = strlen($lead['leadname']) > 20 ? substr($lead['leadname'], 0, 20) . '...' : $lead['leadname'];
$value[] = '<a href="index.php?action=DetailView&module=Leads&record=' . $lead_fields['LEAD_ID'] . '">' . $Top_Leads . '</a>';
$value[] = $lead_fields['COMPANY'];
$entries[$lead_fields['LEAD_ID']] = $value;
}
$advft_criteria_groups = array('1' => array('groupcondition' => null));
$advft_criteria = array(array('groupid' => 1, 'columnname' => 'vtiger_leaddetails:leadstatus:leadstatus:Leads_Lead_Status:V', 'comparator' => 'n', 'value' => 'Lost Lead', 'columncondition' => 'and'), array('groupid' => 1, 'columnname' => 'vtiger_leaddetails:leadstatus:leadstatus:Leads_Lead_Status:V', 'comparator' => 'n', 'value' => 'Junk Lead', 'columncondition' => 'and'), array('groupid' => 1, 'columnname' => 'vtiger_crmentity:smownerid:assigned_user_id:Leads_Assigned_To:V', 'comparator' => 'e', 'value' => getFullNameFromArray('Users', $current_user->column_fields), 'columncondition' => 'and'), array('groupid' => 1, 'columnname' => 'vtiger_crmentity:createdtime:createdtime:Leads_Created_Time:DT', 'comparator' => 'h', 'value' => $userStartDate . ' 00:00:00', 'columncondition' => null));
$search_qry = '&advft_criteria=' . Zend_Json::encode($advft_criteria) . '&advft_criteria_groups=' . Zend_Json::encode($advft_criteria_groups) . '&searchtype=advance&query=true';
$values = array('ModuleName' => 'Leads', 'Header' => $header, 'Entries' => $entries, 'search_qry' => $search_qry);
$log->debug("Exiting getNewLeads method ...");
if (count($entries) == 0 || count($entries) > 0) {
return $values;
}
}
