/**
*
* @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;
}
