/** * * @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 Standard filter columns for the reportid * This function accepts the $reportid datatype Integer * This function returns $stdfilterlist Array($columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria, * $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria, * ) * */ function getStdFilterList($reportid) { // Have we initialized information already? if ($this->_stdfilterlist !== false) { return $this->_stdfilterlist; } $adb = PearDatabase::getInstance(); $log = vglobal('log'); $stdfilterlist = array(); $stdfiltersql = "select vtiger_reportdatefilter.* from vtiger_report"; $stdfiltersql .= " inner join vtiger_reportdatefilter on vtiger_report.reportid = vtiger_reportdatefilter.datefilterid"; $stdfiltersql .= " where vtiger_report.reportid = ?"; $result = $adb->pquery($stdfiltersql, array($reportid)); $stdfilterrow = $adb->fetch_array($result); if (isset($stdfilterrow)) { $fieldcolname = $stdfilterrow["datecolumnname"]; $datefilter = $stdfilterrow["datefilter"]; $startdate = $stdfilterrow["startdate"]; $enddate = $stdfilterrow["enddate"]; if ($fieldcolname != "none") { $selectedfields = explode(":", $fieldcolname); if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) { $selectedfields[0] = "vtiger_crmentity"; } $moduleFieldLabel = $selectedfields[3]; list($moduleName, $fieldLabel) = explode('__', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel); $typeOfData = $fieldInfo['typeofdata']; list($type, $typeOtherInfo) = explode('~', $typeOfData, 2); if ($datefilter != "custom") { $startenddate = $this->getStandarFiltersStartAndEndDate($datefilter); $startdate = $startenddate[0]; $enddate = $startenddate[1]; } if ($startdate != "0000-00-00" && $enddate != "0000-00-00" && $startdate != "" && $enddate != "" && $selectedfields[0] != "" && $selectedfields[1] != "") { $startDateTime = new DateTimeField($startdate . ' ' . date('H:i:s')); $userStartDate = $startDateTime->getDisplayDate(); if ($type == 'DT') { $userStartDate = $userStartDate . ' 00:00:00'; } $startDateTime = getValidDBInsertDateTimeValue($userStartDate); $endDateTime = new DateTimeField($enddate . ' ' . date('H:i:s')); $userEndDate = $endDateTime->getDisplayDate(); if ($type == 'DT') { $userEndDate = $userEndDate . ' 23:59:00'; } $endDateTime = getValidDBInsertDateTimeValue($userEndDate); if ($selectedfields[1] == 'birthday') { $tableColumnSql = "DATE_FORMAT(" . $selectedfields[0] . "." . $selectedfields[1] . ", '%m%d')"; $startDateTime = "DATE_FORMAT('{$startDateTime}', '%m%d')"; $endDateTime = "DATE_FORMAT('{$endDateTime}', '%m%d')"; } else { if ($selectedfields[0] == 'vtiger_activity' && $selectedfields[1] == 'date_start') { $tableColumnSql = ''; $tableColumnSql = "CAST((CONCAT(date_start,' ',time_start)) AS DATETIME)"; } else { $tableColumnSql = $selectedfields[0] . "." . $selectedfields[1]; } $startDateTime = "'{$startDateTime}'"; $endDateTime = "'{$endDateTime}'"; } $stdfilterlist[$fieldcolname] = $tableColumnSql . " between " . $startDateTime . " and " . $endDateTime; $this->queryPlanner->addTable($selectedfields[0]); } } } // Save the information $this->_stdfilterlist = $stdfilterlist; $log->info("ReportRun :: Successfully returned getStdFilterList" . $reportid); return $stdfilterlist; }
public function transformForImport($fieldData, $moduleMeta, $fillDefault = true, $mergeMode = false) { $moduleFields = $moduleMeta->getModuleFields(); $defaultFieldValues = $this->getDefaultFieldValues($moduleMeta); foreach ($fieldData as $fieldName => $fieldValue) { $fieldInstance = $moduleFields[$fieldName]; if (!is_object($fieldInstance)) { continue; } // specially for Inventory module import which has virtual item line fields if ($fieldInstance->getFieldDataType() == 'owner') { global $adb; if (strpos($fieldValue, '::::') > 0) { $fieldValueDetails = explode('::::', $fieldValue); } else { $fieldValueDetails = explode(':::', $fieldValue); } if (count($fieldValueDetails) == 2) { $fieldValue = $fieldValueDetails[1]; } if (count($fieldValueDetails) == 3) { $user_qry = 'select vtiger_users.id from vtiger_users where ' . $fieldValueDetails[2] . ' = ?'; $res = $adb->pquery($user_qry, array($fieldValueDetails[1])); $ownerId = 0; if ($res and $adb->num_rows($res) > 0) { $ownerId = $adb->query_result($res, 0, 'id'); } } else { $ownerId = getUserId_Ol($fieldValue); if (empty($ownerId)) { $ownerId = getGrpId($fieldValue); } } if (empty($ownerId) && isset($defaultFieldValues[$fieldName])) { $ownerId = $defaultFieldValues[$fieldName]; } if (empty($ownerId) || !Import_Utils::hasAssignPrivilege($moduleMeta->getEntityName(), $ownerId)) { $ownerId = $this->user->id; } $fieldData[$fieldName] = $ownerId; } elseif ($fieldInstance->getFieldDataType() == 'reference') { $entityId = false; if (!empty($fieldValue)) { if (strpos($fieldValue, '::::') > 0) { $fieldValueDetails = explode('::::', $fieldValue); } else { $fieldValueDetails = explode(':::', $fieldValue); } if (count($fieldValueDetails) > 1) { $referenceModuleName = trim($fieldValueDetails[0]); $entityLabel = trim($fieldValueDetails[1]); if (!empty($fieldValueDetails[2])) { $entityId = getEntityId($referenceModuleName, $entityLabel, $fieldValueDetails[2]); } else { $entityId = getEntityId($referenceModuleName, $entityLabel); } } else { $referencedModules = $fieldInstance->getReferenceList(); $entityLabel = $fieldValue; foreach ($referencedModules as $referenceModule) { $referenceModuleName = $referenceModule; if ($referenceModule == 'Users') { $referenceEntityId = getUserId_Ol($entityLabel); if (empty($referenceEntityId) || !Import_Utils::hasAssignPrivilege($moduleMeta->getEntityName(), $referenceEntityId)) { $referenceEntityId = $this->user->id; } } else { $referenceEntityId = getEntityId($referenceModule, $entityLabel); } if ($referenceEntityId != 0) { $entityId = $referenceEntityId; break; } } } if ((empty($entityId) || $entityId == 0) && (!empty($referenceModuleName) and !in_array($referenceModuleName, getInventoryModules()))) { if (isPermitted($referenceModuleName, 'EditView') == 'yes') { $wsEntityIdInfo = $this->createEntityRecord($referenceModuleName, $entityLabel); $wsEntityId = $wsEntityIdInfo['id']; $entityIdComponents = vtws_getIdComponents($wsEntityId); $entityId = $entityIdComponents[1]; } } $fieldData[$fieldName] = $entityId; } else { $referencedModules = $fieldInstance->getReferenceList(); if ($referencedModules[0] == 'Users') { if (isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $defaultFieldValues[$fieldName]; } if (empty($fieldData[$fieldName]) || !Import_Utils::hasAssignPrivilege($moduleMeta->getEntityName(), $fieldData[$fieldName])) { $fieldData[$fieldName] = $this->user->id; } } else { $fieldData[$fieldName] = ''; } } } elseif ($fieldInstance->getFieldDataType() == 'picklist') { global $default_charset; if (empty($fieldValue) && isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $fieldValue = $defaultFieldValues[$fieldName]; } $allPicklistDetails = $fieldInstance->getPicklistDetails(); $allPicklistValues = array(); foreach ($allPicklistDetails as $picklistDetails) { $allPicklistValues[] = $picklistDetails['value']; } $encodePicklistValue = $fieldValue; if (!in_array($encodePicklistValue, $allPicklistValues)) { $moduleObject = Vtiger_Module::getInstance($moduleMeta->getEntityName()); $fieldObject = Vtiger_Field::getInstance($fieldName, $moduleObject); $fieldObject->setPicklistValues(array($fieldValue)); } } else { if ($fieldInstance->getFieldDataType() == 'datetime' && !empty($fieldValue)) { if ($fieldValue == null || $fieldValue == '0000-00-00 00:00:00') { $fieldValue = ''; } $valuesList = explode(' ', $fieldValue); if (count($valuesList) == 1) { $fieldValue = ''; } $fieldValue = getValidDBInsertDateTimeValue($fieldValue); if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2} ([0-1][0-9]|[2][0-3])([:][0-5][0-9]){1,2}\$/", $fieldValue) == 0) { $fieldValue = ''; } $fieldData[$fieldName] = $fieldValue; } if ($fieldInstance->getFieldDataType() == 'date' && !empty($fieldValue)) { if ($fieldValue == null || $fieldValue == '0000-00-00') { $fieldValue = ''; } $fieldValue = getValidDBInsertDateValue($fieldValue); if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2}\$/", $fieldValue) == 0) { $fieldValue = ''; } $fieldData[$fieldName] = $fieldValue; } if (empty($fieldValue) && isset($defaultFieldValues[$fieldName]) && !$mergeMode) { $fieldData[$fieldName] = $fieldValue = $defaultFieldValues[$fieldName]; } } } if ($fillDefault) { foreach ($defaultFieldValues as $fieldName => $fieldValue) { if (!isset($fieldData[$fieldName])) { $fieldData[$fieldName] = $defaultFieldValues[$fieldName]; } } } if (!$mergeMode) { //Do not check mandatory fields on merge ! foreach ($moduleFields as $fieldName => $fieldInstance) { if (empty($fieldData[$fieldName]) && $fieldInstance->isMandatory()) { return null; } } } return DataTransform::sanitizeData($fieldData, $moduleMeta); }
function getCVAdvFilterSQL($cvid) { global $current_user; $advfilter = $this->getAdvFilterByCvid($cvid); $advcvsql = ""; foreach ($advfilter as $groupid => $groupinfo) { $groupcolumns = $groupinfo["columns"]; $groupcondition = $groupinfo["condition"]; $advfiltergroupsql = ""; foreach ($groupcolumns as $columnindex => $columninfo) { $columnname = $columninfo['columnname']; $comparator = $columninfo['comparator']; $value = $columninfo['value']; $columncondition = $columninfo['column_condition']; $columns = explode(":", $columnname); $datatype = isset($columns[4]) ? $columns[4] : ""; if ($columnname != "" && $comparator != "") { $valuearray = explode(",", trim($value)); if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') { $advorsql = ""; for ($n = 0; $n < count($valuearray); $n++) { $advorsql[] = $this->getRealValues($columns[0], $columns[1], $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') { $advorsqls = implode(" and ", $advorsql); } else { $advorsqls = implode(" or ", $advorsql); } $advfiltersql = " (" . $advorsqls . ") "; } elseif ($comparator == 'bw' && count($valuearray) == 2) { $advfiltersql = "(" . $columns[0] . "." . $columns[1] . " between '" . getValidDBInsertDateTimeValue(trim($valuearray[0]), $datatype) . "' and '" . getValidDBInsertDateTimeValue(trim($valuearray[1]), $datatype) . "')"; } else { //Added for getting vtiger_activity Status -Jaguar if ($this->customviewmodule == "Calendar" && ($columns[1] == "status" || $columns[1] == "eventstatus")) { if (getFieldVisibilityPermission("Calendar", $current_user->id, 'taskstatus') == '0') { $advfiltersql = "case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end" . $this->getAdvComparator($comparator, trim($value), $datatype); } else { $advfiltersql = "vtiger_activity.eventstatus" . $this->getAdvComparator($comparator, trim($value), $datatype); } } elseif ($this->customviewmodule == "Documents" && $columns[1] == 'folderid') { $advfiltersql = "vtiger_attachmentsfolder.foldername" . $this->getAdvComparator($comparator, trim($value), $datatype); } elseif ($this->customviewmodule == "Assets") { if ($columns[1] == 'account') { $advfiltersql = "vtiger_account.accountname" . $this->getAdvComparator($comparator, trim($value), $datatype); } if ($columns[1] == 'product') { $advfiltersql = "vtiger_products.productname" . $this->getAdvComparator($comparator, trim($value), $datatype); } if ($columns[1] == 'invoiceid') { $advfiltersql = "vtiger_invoice.subject" . $this->getAdvComparator($comparator, trim($value), $datatype); } } else { $advfiltersql = $this->getRealValues($columns[0], $columns[1], $comparator, trim($value), $datatype); } } $advfiltergroupsql .= $advfiltersql; if ($columncondition != NULL && $columncondition != '' && count($groupcolumns) > $columnindex) { $advfiltergroupsql .= ' ' . $columncondition . ' '; } } } if (trim($advfiltergroupsql) != "") { $advfiltergroupsql = "( {$advfiltergroupsql} ) "; if ($groupcondition != NULL && $groupcondition != '' && $advfilter > $groupid) { $advfiltergroupsql .= ' ' . $groupcondition . ' '; } $advcvsql .= $advfiltergroupsql; } } if (trim($advcvsql) != "") { $advcvsql = '(' . $advcvsql . ')'; } return $advcvsql; }
public function transformForImport($fieldData, $moduleMeta, $fillDefault = true, $checkMandatoryFieldValues = true) { $moduleFields = $moduleMeta->getModuleFields(); $defaultFieldValues = $this->getDefaultFieldValues($moduleMeta); foreach ($fieldData as $fieldName => $fieldValue) { $fieldInstance = $moduleFields[$fieldName]; if ($fieldInstance->getFieldDataType() == 'owner') { $ownerId = getUserId_Ol(trim($fieldValue)); if (empty($ownerId)) { $ownerId = getGrpId($fieldValue); } if (empty($ownerId) && isset($defaultFieldValues[$fieldName])) { $ownerId = $defaultFieldValues[$fieldName]; } if (empty($ownerId) || !Import_Utils_Helper::hasAssignPrivilege($moduleMeta->getEntityName(), $ownerId)) { $ownerId = $this->user->id; } $fieldData[$fieldName] = $ownerId; } elseif ($fieldInstance->getFieldDataType() == 'multipicklist') { $trimmedValue = trim($fieldValue); if (!$trimmedValue && isset($defaultFieldValues[$fieldName])) { $explodedValue = explode(',', $defaultFieldValues[$fieldName]); } else { $explodedValue = explode(' |##| ', $trimmedValue); } foreach ($explodedValue as $key => $value) { $explodedValue[$key] = trim($value); } $implodeValue = implode(' |##| ', $explodedValue); $fieldData[$fieldName] = $implodeValue; } elseif ($fieldInstance->getFieldDataType() == 'reference') { $entityId = false; if (!empty($fieldValue)) { if (strpos($fieldValue, '::::') > 0) { $fieldValueDetails = explode('::::', $fieldValue); } else { if (strpos($fieldValue, ':::') > 0) { $fieldValueDetails = explode(':::', $fieldValue); } else { $fieldValueDetails = $fieldValue; } } if (count($fieldValueDetails) > 1) { $referenceModuleName = trim($fieldValueDetails[0]); $entityLabel = trim($fieldValueDetails[1]); $entityId = getEntityId($referenceModuleName, $entityLabel); } else { $referencedModules = $fieldInstance->getReferenceList(); $entityLabel = $fieldValue; foreach ($referencedModules as $referenceModule) { $referenceModuleName = $referenceModule; if ($referenceModule == 'Users') { $referenceEntityId = getUserId_Ol($entityLabel); if (empty($referenceEntityId) || !Import_Utils_Helper::hasAssignPrivilege($moduleMeta->getEntityName(), $referenceEntityId)) { $referenceEntityId = $this->user->id; } } elseif ($referenceModule == 'Currency') { $referenceEntityId = getCurrencyId($entityLabel); } else { $referenceEntityId = getEntityId($referenceModule, $entityLabel); } if ($referenceEntityId != 0) { $entityId = $referenceEntityId; break; } } } if ((empty($entityId) || $entityId == 0) && !empty($referenceModuleName)) { if (isPermitted($referenceModuleName, 'EditView') == 'yes') { try { $wsEntityIdInfo = $this->createEntityRecord($referenceModuleName, $entityLabel); $wsEntityId = $wsEntityIdInfo['id']; $entityIdComponents = vtws_getIdComponents($wsEntityId); $entityId = $entityIdComponents[1]; } catch (Exception $e) { $entityId = false; } } } $fieldData[$fieldName] = $entityId; } else { $referencedModules = $fieldInstance->getReferenceList(); if ($referencedModules[0] == 'Users') { if (isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $defaultFieldValues[$fieldName]; } if (empty($fieldData[$fieldName]) || !Import_Utils_Helper::hasAssignPrivilege($moduleMeta->getEntityName(), $fieldData[$fieldName])) { $fieldData[$fieldName] = $this->user->id; } } else { $fieldData[$fieldName] = ''; } } } elseif ($fieldInstance->getFieldDataType() == 'picklist') { $fieldValue = trim($fieldValue); global $default_charset; if (empty($fieldValue) && isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $fieldValue = $defaultFieldValues[$fieldName]; } $olderCacheEnable = Vtiger_Cache::$cacheEnable; Vtiger_Cache::$cacheEnable = false; if (!isset($this->allPicklistValues[$fieldName])) { $this->allPicklistValues[$fieldName] = $fieldInstance->getPicklistDetails(); } $allPicklistDetails = $this->allPicklistValues[$fieldName]; $allPicklistValues = array(); foreach ($allPicklistDetails as $picklistDetails) { $allPicklistValues[] = $picklistDetails['value']; } $picklistValueInLowerCase = strtolower(htmlentities($fieldValue, ENT_QUOTES, $default_charset)); $allPicklistValuesInLowerCase = array_map('strtolower', $allPicklistValues); $picklistDetails = array_combine($allPicklistValuesInLowerCase, $allPicklistValues); if (!in_array($picklistValueInLowerCase, $allPicklistValuesInLowerCase)) { $moduleObject = Vtiger_Module::getInstance($moduleMeta->getEntityName()); $fieldObject = Vtiger_Field::getInstance($fieldName, $moduleObject); $fieldObject->setPicklistValues(array($fieldValue)); unset($this->allPicklistValues[$fieldName]); } else { $fieldData[$fieldName] = $picklistDetails[$picklistValueInLowerCase]; } Vtiger_Cache::$cacheEnable = $olderCacheEnable; } else { if ($fieldInstance->getFieldDataType() == 'currency') { // While exporting we are exporting as user format, we should import as db format while importing $fieldData[$fieldName] = CurrencyField::convertToDBFormat($fieldValue, $current_user, false); } else { if ($fieldInstance->getFieldDataType() == 'datetime' && !empty($fieldValue)) { if ($fieldValue == null || $fieldValue == '0000-00-00 00:00:00') { $fieldValue = ''; } $valuesList = explode(' ', $fieldValue); if (count($valuesList) == 1) { $fieldValue = ''; } $fieldValue = getValidDBInsertDateTimeValue($fieldValue); if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2} ([0-1][0-9]|[2][0-3])([:][0-5][0-9]){1,2}\$/", $fieldValue) == 0) { $fieldValue = ''; } $fieldData[$fieldName] = $fieldValue; } if ($fieldInstance->getFieldDataType() == 'date' && !empty($fieldValue)) { if ($fieldValue == null || $fieldValue == '0000-00-00') { $fieldValue = ''; } $fieldValue = getValidDBInsertDateValue($fieldValue); if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2}\$/", $fieldValue) == 0) { $fieldValue = ''; } $fieldData[$fieldName] = $fieldValue; } if (empty($fieldValue) && isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $fieldValue = $defaultFieldValues[$fieldName]; } } } } if ($fillDefault) { foreach ($defaultFieldValues as $fieldName => $fieldValue) { if (!isset($fieldData[$fieldName])) { $fieldData[$fieldName] = $defaultFieldValues[$fieldName]; } } } // We should sanitizeData before doing final mandatory check below. $fieldData = DataTransform::sanitizeData($fieldData, $moduleMeta); if ($fieldData != null && $checkMandatoryFieldValues) { foreach ($moduleFields as $fieldName => $fieldInstance) { if (empty($fieldData[$fieldName]) && $fieldInstance->isMandatory()) { return null; } } } return $fieldData; }
function getAdvancedSearchComparator($comparator, $value, $datatype = '') { global $adb, $default_charset; $value = html_entity_decode(trim($value), ENT_QUOTES, $default_charset); $value = $adb->sql_escape_string($value); if ($datatype == 'DT' || $datatype == 'D') { $value = getValidDBInsertDateTimeValue($value, $datatype); } if ($comparator == "e") { if (trim($value) == "NULL") { $rtvalue = " is NULL"; } elseif (trim($value) != "") { $rtvalue = " = " . $adb->quote($value); } elseif (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " = " . $adb->quote($value); } else { $rtvalue = " is NULL"; } } if ($comparator == "n") { if (trim($value) == "NULL") { $rtvalue = " is NOT NULL"; } elseif (trim($value) != "") { $rtvalue = " <> " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "V") { $rtvalue = " <> " . $adb->quote($value); } elseif (trim($value) == "" && $datatype == "E") { $rtvalue = " <> " . $adb->quote($value); } else { $rtvalue = " is NOT NULL"; } } if ($comparator == "s") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " like '" . formatForSqlLike($value, 3) . "'"; } else { $rtvalue = " like '" . formatForSqlLike($value, 2) . "'"; } } if ($comparator == "ew") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " like '" . formatForSqlLike($value, 3) . "'"; } else { $rtvalue = " like '" . formatForSqlLike($value, 1) . "'"; } } if ($comparator == "c") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " like '" . formatForSqlLike($value, 3) . "'"; } else { $rtvalue = " like '" . formatForSqlLike($value) . "'"; } } if ($comparator == "k") { if (trim($value) == "" && ($datatype == "V" || $datatype == "E")) { $rtvalue = " not like ''"; } else { $rtvalue = " not like '" . formatForSqlLike($value) . "'"; } } if ($comparator == "l") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "g") { $rtvalue = " > " . $adb->quote($value); } if ($comparator == "m") { $rtvalue = " <= " . $adb->quote($value); } if ($comparator == "h") { $rtvalue = " >= " . $adb->quote($value); } if ($comparator == "b") { $rtvalue = " < " . $adb->quote($value); } if ($comparator == "a") { $rtvalue = " > " . $adb->quote($value); } return $rtvalue; }
/** * * @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; }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); if (is_string($value) && $this->ignoreComma == false) { $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, ' |##| '); } } } 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())) { $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') { $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 (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())) { $value = getValidDBInsertDateTimeValue($value); $dateTime = explode(' ', $value); if ($dateTime[1] == '00:00:00') { $value = $dateTime[0]; } } 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 == '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; }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); $noncommaSeparatedFieldTypes = array('currency', 'percentage', 'double', 'integer', 'number'); if (in_array($field->getFieldDataType(), $noncommaSeparatedFieldTypes)) { if (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } // if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) { // $valueArray = getCombinations($valueArray); // foreach ($valueArray as $key => $value) { // $valueArray[$key] = ltrim($value, ' |##| '); // } // } } elseif (is_string($value)) { $valueArray = explode(',', $value); } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'exists') { global $current_user, $adb; $mid = getTabModuleName($field->getTabId()); $qg = new QueryGenerator($mid, $current_user); $qg->addCondition($field->getFieldName(), $value, 'e'); $sql[] = 'SELECT EXISTS(SELECT 1 ' . $qg->getFromClause() . $qg->getWhereClause() . ')'; return $sql; } if ($operator == 'i' or $operator == 'in' or $operator == 'ni' or $operator == 'nin') { $vals = array_map(array($db, 'quote'), $valueArray); $sql[] = (($operator == 'ni' or $operator == 'nin') ? ' NOT ' : '') . 'IN (' . implode(',', $vals) . ')'; return $sql; } 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())) { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); } $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } return $sql; } $yes = strtolower(getTranslatedString('yes')); $no = strtolower(getTranslatedString('no')); 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' or $value == $yes) { $value = 1; } elseif ($value == 'no' or $value == $no) { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { $value = getValidDBInsertDateTimeValue($value); if (empty($value)) { $sql[] = 'IS NULL or ' . $field->getTableName() . '.' . $field->getFieldName() . " = ''"; return $sql; } } elseif ($field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist') { if (!isValueInPicklist($value, $field->getFieldName())) { $value = getTranslationKeyFromTranslatedValue($this->module, $value); } } else { if ($field->getFieldDataType() === 'currency') { $uiType = $field->getUIType(); if ($uiType == 72) { $value = CurrencyField::convertToDBFormat($value, null, true); } elseif ($uiType == 71) { $value = CurrencyField::convertToDBFormat($value, $this->user); } } } 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 == '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; }
public function transformForImport($fieldData, $moduleMeta, $fillDefault=true) { $moduleFields = $moduleMeta->getModuleFields(); $defaultFieldValues = $this->getDefaultFieldValues($moduleMeta); foreach ($fieldData as $fieldName => $fieldValue) { $fieldInstance = $moduleFields[$fieldName]; if ($fieldInstance->getFieldDataType() == 'owner') { $ownerId = getUserId_Ol($fieldValue); if (empty($ownerId)) { $ownerId = getGrpId($fieldValue); } if (empty($ownerId) && isset($defaultFieldValues[$fieldName])) { $ownerId = $defaultFieldValues[$fieldName]; } if(empty($ownerId) || !Import_Utils::hasAssignPrivilege($moduleMeta->getEntityName(), $ownerId)) { $ownerId = $this->user->id; } $fieldData[$fieldName] = $ownerId; } elseif ($fieldInstance->getFieldDataType() == 'reference') { $entityId = false; if (!empty($fieldValue)) { if(strpos($fieldValue, '::::') > 0) { $fieldValueDetails = explode('::::', $fieldValue); } else { $fieldValueDetails = explode(':::', $fieldValue); } if (count($fieldValueDetails) > 1) { $referenceModuleName = trim($fieldValueDetails[0]); $entityLabel = trim($fieldValueDetails[1]); $entityId = getEntityId($referenceModuleName, $entityLabel); } else { $referencedModules = $fieldInstance->getReferenceList(); $entityLabel = $fieldValue; foreach ($referencedModules as $referenceModule) { $referenceModuleName = $referenceModule; if ($referenceModule == 'Users') { $referenceEntityId = getUserId_Ol($entityLabel); if(empty($referenceEntityId) || !Import_Utils::hasAssignPrivilege($moduleMeta->getEntityName(), $referenceEntityId)) { $referenceEntityId = $this->user->id; } } else { $referenceEntityId = getEntityId($referenceModule, $entityLabel); } if ($referenceEntityId != 0) { $entityId = $referenceEntityId; break; } } } if ((empty($entityId) || $entityId == 0) && !empty($referenceModuleName)) { if(isPermitted($referenceModuleName, 'EditView') == 'yes') { $wsEntityIdInfo = $this->createEntityRecord($referenceModuleName, $entityLabel); $wsEntityId = $wsEntityIdInfo['id']; $entityIdComponents = vtws_getIdComponents($wsEntityId); $entityId = $entityIdComponents[1]; } } $fieldData[$fieldName] = $entityId; } else { $referencedModules = $fieldInstance->getReferenceList(); if ($referencedModules[0] == 'Users') { if(isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $defaultFieldValues[$fieldName]; } if(empty($fieldData[$fieldName]) || !Import_Utils::hasAssignPrivilege($moduleMeta->getEntityName(), $fieldData[$fieldName])) { $fieldData[$fieldName] = $this->user->id; } } else { $fieldData[$fieldName] = ''; } } } elseif ($fieldInstance->getFieldDataType() == 'picklist') { global $default_charset; if (empty($fieldValue) && isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $fieldValue = $defaultFieldValues[$fieldName]; } $allPicklistDetails = $fieldInstance->getPicklistDetails(); $allPicklistValues = array(); foreach ($allPicklistDetails as $picklistDetails) { $allPicklistValues[] = $picklistDetails['value']; } $encodePicklistValue = htmlentities($fieldValue,ENT_QUOTES,$default_charset); if (!in_array($encodePicklistValue, $allPicklistValues)) { $moduleObject = Vtiger_Module::getInstance($moduleMeta->getEntityName()); $fieldObject = Vtiger_Field::getInstance($fieldName, $moduleObject); $fieldObject->setPicklistValues(array($fieldValue)); } } else { if ($fieldInstance->getFieldDataType() == 'datetime' && !empty($fieldValue)) { if($fieldValue == null || $fieldValue == '0000-00-00 00:00:00') { $fieldValue = ''; } $valuesList = explode(' ', $fieldValue); if(count($valuesList) == 1) $fieldValue = ''; $fieldValue = getValidDBInsertDateTimeValue($fieldValue); if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2} ([0-1][0-9]|[2][0-3])([:][0-5][0-9]){1,2}$/", $fieldValue) == 0) { $fieldValue = ''; } $fieldData[$fieldName] = $fieldValue; } if ($fieldInstance->getFieldDataType() == 'date' && !empty($fieldValue)) { if($fieldValue == null || $fieldValue == '0000-00-00') { $fieldValue = ''; } $fieldValue = getValidDBInsertDateValue($fieldValue); if (preg_match("/^[0-9]{2,4}[-][0-1]{1,2}?[0-9]{1,2}[-][0-3]{1,2}?[0-9]{1,2}$/", $fieldValue) == 0) { $fieldValue = ''; } $fieldData[$fieldName] = $fieldValue; } if (empty($fieldValue) && isset($defaultFieldValues[$fieldName])) { $fieldData[$fieldName] = $fieldValue = $defaultFieldValues[$fieldName]; } } } if($fillDefault) { foreach($defaultFieldValues as $fieldName => $fieldValue) { if (!isset($fieldData[$fieldName])) { $fieldData[$fieldName] = $defaultFieldValues[$fieldName]; } } } foreach ($moduleFields as $fieldName => $fieldInstance) { if(empty($fieldData[$fieldName]) && $fieldInstance->isMandatory()) { return null; } } return DataTransform::sanitizeData($fieldData, $moduleMeta); }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); if (is_string($value)) { $valueArray = explode(',', $value); } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'between' || $operator == 'bw') { 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())) { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); } $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 (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())) { $value = getValidDBInsertDateTimeValue($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 == '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}'"; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }