function db_field2time($value,$type) { // is called when searhing in View as:Time fields if(IsDateFieldType($type)) return "time(".$value.")"; return $value; }
/** * Import data from an Excel file * @param PHPExcel fileHandle * @param Array fieldsData * @param Array keys * @param ImportPage importPageObject * @param Boolean autoinc * @param Boolean useFirstLine * @param String dateFormat * @return Array */ function ImportDataFromExcel($fileHandle, $fieldsData, $keys, $importPageObject, $autoinc, $useFirstLine, $dateFormat) { global $cCharset; $metaData = array(); $metaData["totalRecords"] = 0; $errorMessages = array(); $unprocessedData = array(); $updatedRecords = 0; $addedRecords = 0; $startRow = $useFirstLine ? 1 : 2; foreach ($fileHandle->getWorksheetIterator() as $worksheet) { $highestRow = $worksheet->getHighestRow(); // get a litteral index of the 'highest' column (e.g. 'K') $highestColumn = $worksheet->getHighestColumn(); // get an index number of the 'highest' column (e.g. 11) $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = $startRow; $row <= $highestRow; $row++) { $fieldValuesData = array(); for ($col = 0; $col < $highestColumnIndex; $col++) { if (!isset($fieldsData[$col])) { continue; } $importFieldName = $fieldsData[$col]["fName"]; $cell = $worksheet->getCellByColumnAndRow($col, $row); $cellValue = $cell->getValue(); if (PHPExcel_Shared_Date::isDateTime($cell)) { $cellDateFormat = $fileHandle->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(); $cellTextValue = PHPExcel_Style_NumberFormat::ToFormattedString($cellValue, $cellDateFormat); $cellValue = getDBDateValue($cellTextValue, $cellDateFormat); } else { if (is_a($cellValue, 'PHPExcel_RichText')) { $cellValue = $cellValue->getPlainText(); } if (IsDateFieldType($fieldsData[$col]["type"])) { $cellValue = getDBDateValue($cellValue, $dateFormat); } $error_handler = set_error_handler("empty_error_handler"); $cellValue = PHPExcel_Shared_String::ConvertEncoding($cellValue, $cCharset, 'UTF-8'); if ($error_handler) { set_error_handler($error_handler); } $matches = array(); preg_match('/^="(=.*)"$/i', $cellValue, $matches); if (array_key_exists(1, $matches)) { $cellValue = $matches[1]; } } $fieldValuesData[$importFieldName] = $cellValue; } $importPageObject->importRecord($fieldValuesData, $keys, $autoinc, $addedRecords, $updatedRecords, $errorMessages, $unprocessedData); $metaData["totalRecords"] = $metaData["totalRecords"] + 1; } } $metaData["addedRecords"] = $addedRecords; $metaData["updatedRecords"] = $updatedRecords; $metaData["errorMessages"] = $errorMessages; $metaData["unprocessedData"] = $unprocessedData; return $metaData; }
function getControl($field, $id = "") { // if conrol does not created previously if (!array_key_exists($field, $this->controls)) { include_once getabspath("classes/controls/Control.php"); $isUserControl = false; $cTypes = new ControlTypes(); $editFormat = $this->pSetEdit->getEditFormat($field); if ($editFormat == EDIT_FORMAT_TEXT_FIELD && IsDateFieldType($this->pSetEdit->getFieldType($field))) { $editFormat = EDIT_FORMAT_DATE; } if ($this->pageType == PAGE_SEARCH || $this->pageType == PAGE_LIST) { // Text field may be Lookup field on some page if ($editFormat == EDIT_FORMAT_TEXT_FIELD && $this->pSetEdit->getPageTypeByFieldEditFormat($field, EDIT_FORMAT_LOOKUP_WIZARD) != "") { $localPSet = new ProjectSettings($this->pSetEdit->_table, $this->pSetEdit->getPageTypeByFieldEditFormat($field, EDIT_FORMAT_LOOKUP_WIZARD)); if ($localPSet->getLWLinkField($field) != $localPSet->getLWDisplayField($field)) { $className = "LookupTextField"; } else { $className = $cTypes->forSearch[$editFormat]; } } else { $className = $cTypes->forSearch[$editFormat]; } } else { $className = $cTypes->forEdit[$editFormat]; } if (!$className) { if ($editFormat != "") { $className = "Edit" . $editFormat; $isUserControl = true; include_once getabspath("classes/controls/UserControl.php"); if (!is_null($this->pageObject)) { $this->pageObject->AddJSFile("include/runnerJS/controls/" . $className . ".js", "include/runnerJS/Control.js"); } } else { $className = $cTypes->forEdit[EDIT_FORMAT_TEXT_FIELD]; } } else { if ($className == $cTypes->forEdit[EDIT_FORMAT_FILE] && $this->pSetEdit->isCompatibilityMode($field)) { $className = "FileFieldSingle"; } } $this->controls[$field] = createControlClass($className, $field, $this->pageObject != null ? $this->pageObject : $this, $id); $this->controls[$field]->container = $this; if ($isUserControl) { $this->controls[$field]->format = $className; $this->controls[$field]->initUserControl(); } } if ($id != "") { $this->controls[$field]->setID($id); } return $this->controls[$field]; }
public function showDBValue(&$data, $keylink) { $result = ""; if (IsDateFieldType($this->fieldType)) { $result = str_format_time(db2time($data[$this->field])); } else { $numbers = parsenumbers($data[$this->field]); if (!count($numbers)) { return ""; } while (count($numbers) < 3) { $numbers[] = 0; } $result = str_format_time(array(0, 0, 0, $numbers[0], $numbers[1], $numbers[2])); } return $this->checkForEncoding($result, $keylink); }
/** * @param &Array data * @return String */ public function getTextValue(&$data) { $result = ""; if (IsDateFieldType($this->fieldType)) { return str_format_time(db2time($data[$this->field])); } $numbers = parsenumbers($data[$this->field]); if (!count($numbers)) { return ""; } while (count($numbers) < 3) { $numbers[] = 0; } if (count($numbers) == 6) { return str_format_time(array(0, 0, 0, $numbers[3], $numbers[4], $numbers[5])); } // sometimes data is datetime return str_format_time(array(0, 0, 0, $numbers[0], $numbers[1], $numbers[2])); }
/** * Get axes displyed values * @param Number index * @param String value * @return String */ protected function getAxisDisplayValue($index, $value) { global $locale_info; if ($value == "" || is_null($value)) { return ""; } $groupFieldsData = $this->xml_array["group_fields"]; $field = $groupFieldsData[$index]["name"]; $int_type = $groupFieldsData[$index]["int_type"]; if ($this->fromWizard) { $control = $this->viewControls->getControl($field); } if ($int_type == 0) { // The 'Normal' interval is set if ($this->fromWizard) { $data = array($field => $value); return $control->showDBValue($data, ""); } if ($this->table_type != "db") { $fieldIdentifier = $this->xml_array["tables"][0] . "_" . $field; } else { $fieldIdentifier = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$fieldIdentifier]['curr'] == true) { return str_format_currency($value); } return xmlencode($value); } $ftype = $this->getFieldType($field); if (IsNumberType($ftype)) { $start = $value - $value % $int_type; $end = $start + $int_type; if ($this->fromWizard) { $dataStart = array($field => $start); $dataEnd = array($field => $end); return $control->showDBValue($dataStart, "") . " - " . $control->showDBValue($dataEnd, ""); } if ($this->table_type != "db") { $fieldIdentifier = $this->xml_array["tables"][0] . "_" . $field; } else { $fieldIdentifier = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$fieldIdentifier]['curr'] == true) { return str_format_currency($start) . " - " . str_format_currency($end); } return $start . " - " . $end; } if (IsCharType($ftype)) { return xmlencode(substr($value, 0, $int_type)); } if (IsDateFieldType($ftype)) { $dvalue = substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2); if (strlen($value) == 10) { $dvalue .= " " . substr($value, 8, 2) . "00:00"; } elseif (strlen($value) == 12) { $dvalue .= " " . substr($value, 8, 2) . ":" . substr($value, 10, 2) . ":00"; } $tm = db2time($dvalue); if (!count($tm)) { return ""; } switch ($int_type) { case 1: // DATE_INTERVAL_YEAR return $tm[0]; case 2: // DATE_INTERVAL_QUARTER return $tm[0] . "/Q" . $tm[1]; case 3: // DATE_INTERVAL_MONTH return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0]; case 4: // DATE_INTERVAL_WEEK $dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]); return format_shortdate(db2time($dates[0])) . ' - ' . format_shortdate(db2time($dates[1])); case 5: // DATE_INTERVAL_DAY return format_shortdate($tm); case 6: // DATE_INTERVAL_HOUR $tm[4] = 0; $tm[5] = 0; return str_format_datetime($tm); case 7: // DATE_INTERVAL_MINUTE $tm[5] = 0; return str_format_datetime($tm); default: return str_format_datetime($tm); } } return ""; }
function GetGroupDisplay($field, $value) { global $locale_info, $rpt_array, $tbl, $fields_type; for ($i = 0; $i < count($rpt_array['group_fields']) - 1; $i++){ $arr = $rpt_array['group_fields'][$i]; foreach ($rpt_array['totals'] as $fld){ if ($arr['name'] == fldname($fld)) { if (!is_wr_custom()) $ftype = WRGetFieldType($fld['table'] . "." . $fld['name']); else $ftype = $fields_type[$fld['name']]; if ($field == $arr['name']) { if ($arr['int_type'] == 0) { $prefix = ""; if (!is_wr_db()) $prefix = $rpt_array["tables"][0] . "_"; else $field = GoodFieldName($field); if ($rpt_array['totals'][$prefix . $field]['curr'] == "true") return str_format_currency($value); else return $value; } elseif (IsNumberType($ftype)) { $start = $value - ($value % $arr['int_type']); $end = $start + $arr['int_type']; $prefix = ""; if (!is_wr_db()) $prefix = $rpt_array["tables"][0] . "_"; else $field = GoodFieldName($field); if ($rpt_array['totals'][$prefix . $field]['curr'] == "true") return str_format_currency($start) . " - " . str_format_currency($end); else return $start . " - " . $end; } elseif (IsCharType($ftype)) { return substr($value, 0, $arr['int_type']); } elseif (IsDateFieldType($ftype)) { $tm = db2time($value); if (!count($tm)) return ""; if ($arr['int_type'] == 1) { // DATE_INTERVAL_YEAR return $tm[0]; } elseif ($arr['int_type'] == 2) { // DATE_INTERVAL_QUARTER return $tm[0] . "/Q" . floor(($tm[1] - 1) / 4 + 1); } elseif ($arr['int_type'] == 3) { // DATE_INTERVAL_MONTH return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0]; } elseif ($arr['int_type'] == 4) { // DATE_INTERVAL_WEEK $start = getweekstart($tm); $end = adddays($start, 6); return format_shortdate($start) . " - " . format_shortdate($end); } elseif ($arr['int_type'] == 5) { // DATE_INTERVAL_DAY return format_shortdate($tm); } elseif ($arr['int_type'] == 6) { // DATE_INTERVAL_HOUR $tm[4] = 0; $tm[5] = 0; return str_format_datetime($tm); } elseif ($arr['int_type'] == 7) { // DATE_INTERVAL_MINUTE $tm[5] = 0; return str_format_datetime($tm); } else { return str_format_datetime($tm); } } } } } } }
/** * Get the WHERE clause conditions string for the search or suggest SQL query * @param String SearchFor * @param String strSearchOption * @param String SearchFor2 * @param String etype * @param Boolean isSuggest */ function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest) { $baseResult = $this->baseSQLWhere($strSearchOption); if ($baseResult === false) { return ""; } if ($baseResult != "") { return $baseResult; } if (!strlen($SearchFor)) { return ""; } $value1 = $this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor, $etype, true); $value2 = false; $cleanvalue2 = false; if ($strSearchOption == "Between") { $cleanvalue2 = prepare_for_db($this->field, $SearchFor2, $etype); $value2 = make_db_value($this->field, $SearchFor2, $etype); } if ($strSearchOption != "Contains" && $strSearchOption != "Starts with" && ($value1 === "null" || $value2 === "null") && !$this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) { return ""; } if (($strSearchOption == "Contains" || $strSearchOption == "Starts with") && !$this->isStringValidForLike($SearchFor)) { return ""; } $searchIsCaseInsensitive = $this->pageObject->pSetEdit->getNCSearch(); if (IsCharType($this->type) && !$this->btexttype) { $gstrField = $this->getFieldSQLDecrypt(); if (!$this->pageObject->cipherer->isFieldPHPEncrypted($this->field) && $searchIsCaseInsensitive) { $value1 = $this->connection->upper($value1); $value2 = $this->connection->upper($value2); $gstrField = $this->connection->upper($gstrField); } } elseif ($strSearchOption == "Contains" || $strSearchOption == "Starts with") { $gstrField = $this->connection->field2char($this->getFieldSQLDecrypt(), $this->type); } elseif ($this->pageObject->pSetEdit->getViewFormat($this->field) == FORMAT_TIME) { $gstrField = $this->connection->field2time($this->getFieldSQLDecrypt(), $this->type); } else { $gstrField = $this->getFieldSQLDecrypt(); } if ($strSearchOption == "Contains") { if ($this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) { return $gstrField . "=" . $this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor); } $SearchFor = $this->connection->escapeLIKEpattern($SearchFor); if (IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) { return $gstrField . " " . $this->like . " " . $this->connection->upper($this->connection->prepareString("%" . $SearchFor . "%")); } return $gstrField . " " . $this->like . " " . $this->connection->prepareString("%" . $SearchFor . "%"); } if ($strSearchOption == "Equals") { return $gstrField . "=" . $value1; } if ($strSearchOption == "Starts with") { $SearchFor = $this->connection->escapeLIKEpattern($SearchFor); if (IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) { return $gstrField . " " . $this->like . " " . $this->connection->upper($this->connection->prepareString($SearchFor . "%")); } return $gstrField . " " . $this->like . " " . $this->connection->prepareString($SearchFor . "%"); } if ($strSearchOption == "More than") { return $gstrField . ">" . $value1; } if ($strSearchOption == "Less than") { return $gstrField . "<" . $value1; } if ($strSearchOption == "Equal or more than") { return $gstrField . ">=" . $value1; } if ($strSearchOption == "Equal or less than") { return $gstrField . "<=" . $value1; } if ($strSearchOption == "Between") { $ret = $gstrField . ">=" . $value1 . " and "; if (IsDateFieldType($this->type)) { $timeArr = db2time($cleanvalue2); // for dates without time, add one day if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) { $timeArr = adddays($timeArr, 1); $value2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2]; $value2 = add_db_quotes($this->field, $value2, $this->pageObject->tName); $ret .= $gstrField . "<" . $value2; } else { $ret .= $gstrField . "<=" . $value2; } } else { $ret .= $gstrField . "<=" . $value2; } return $ret; } return ""; }
/** * @param String field * @param String id (optional) * @param Array extraParmas (optional) * @return Control */ function getControl($field, $id = "", $extraParmas = array()) { if (count($extraParmas) && $extraParmas["getDetKeyReadOnlyCtrl"]) { include_once getabspath("classes/controls/Control.php"); $cTypes = new ControlTypes(); $className = $cTypes->forEdit[EDIT_FORMAT_READONLY]; $ctrl = createControlClass($className, $field, $this->pageObject != null ? $this->pageObject : $this, $id, $this->connection); $ctrl->container = $this; return $ctrl; } if (count($extraParmas) && $extraParmas["getConrirmFieldCtrl"]) { include_once getabspath("classes/controls/Control.php"); $cTypes = new ControlTypes(); $className = $cTypes->forEdit[EDIT_FORMAT_PASSWORD]; $ctrl = createControlClass($className, $field, $this->pageObject != null ? $this->pageObject : $this, $id, $this->connection); if ($extraParmas['isConfirm']) { $ctrl->field = GetPasswordField(); } $ctrl->container = $this; return $ctrl; } // if conrol does not created previously if (!array_key_exists($field, $this->controls)) { include_once getabspath("classes/controls/Control.php"); $userControl = false; $cTypes = new ControlTypes(); $editFormat = $this->pSetEdit->getEditFormat($field); if ($editFormat == EDIT_FORMAT_TEXT_FIELD && IsDateFieldType($this->pSetEdit->getFieldType($field))) { $editFormat = EDIT_FORMAT_DATE; } if ($this->pageType == PAGE_SEARCH || $this->pageType == PAGE_LIST) { // Text field may be Lookup field on some page $pageTypebyLookupFormat = $this->pSetEdit->getPageTypeByFieldEditFormat($field, EDIT_FORMAT_LOOKUP_WIZARD); if ($editFormat == EDIT_FORMAT_TEXT_FIELD && $pageTypebyLookupFormat != "") { $localPSet = new ProjectSettings($this->pSetEdit->_table, $pageTypebyLookupFormat); if ($localPSet->getLinkField($field) != $localPSet->getDisplayField($field)) { $className = "LookupTextField"; } else { $className = $cTypes->forSearch[$editFormat]; } } else { $className = $cTypes->forSearch[$editFormat]; } } else { $className = $cTypes->forEdit[$editFormat]; } if ($className == $cTypes->forEdit[EDIT_FORMAT_FILE] && $this->pSetEdit->isBasicUploadUsed($field)) { $className = "FileFieldSingle"; } if (!$className) { if ($editFormat != "") { $className = "Edit" . $editFormat; $userControl = true; include_once getabspath("classes/controls/UserControl.php"); if (!is_null($this->pageObject)) { $this->pageObject->AddJSFile("include/runnerJS/controls/" . $className . ".js", "include/runnerJS/Control.js"); } } else { $className = $cTypes->forEdit[EDIT_FORMAT_TEXT_FIELD]; } } $this->controls[$field] = createControlClass($className, $field, $this->pageObject != null ? $this->pageObject : $this, $id, $this->connection); $this->controls[$field]->container = $this; if ($userControl) { $this->controls[$field]->format = $className; $this->controls[$field]->initUserControl(); } } if ($id !== "") { $this->controls[$field]->setID($id); } return $this->controls[$field]; }
/** * Get value for field edit as time and get dpTime settings * @param integer $convention - 24 or 12 hours format for timePicker * @param string $type - type of field * @param string $value - value of field * @param boolean $useTimePicker - use timePicker or not * @return array */ function getValForTimePicker($type, $value, $locale) { $val = ""; $dbtime = array(); if (IsDateFieldType($type)) { $dbtime = db2time($value); if (count($dbtime)) { $val = format_datetime_custom($dbtime, $locale); } } else { $arr = parsenumbers($value); if (count($arr)) { while (count($arr) < 3) { $arr[] = 0; } $dbtime = array(0, 0, 0, $arr[0], $arr[1], $arr[2]); $val = format_datetime_custom($dbtime, $locale); } } return array('val' => $val, 'dbTime' => $dbtime); }
function PrepareValue($value, $type) { if (IsDateFieldType($type)) { return db_datequotes($value); } if (NeedQuotes($type)) { return db_prepare_string($value); } else { return 0 + $value; } }
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest) { if ($this->lookupType == LT_LISTOFVALUES) { return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest); } $baseResult = $this->baseSQLWhere($strSearchOption); if ($baseResult === false) { return ""; } if ($baseResult != "") { return $baseResult; } $displayFieldType = $this->type; if ($this->lookupType == LT_QUERY) { $displayFieldType = $this->lookupPSet->getFieldType($this->field); $this->btexttype = IsTextType($displayFieldType); } if ($this->multiselect) { $SearchFor = splitvalues($SearchFor); } else { $SearchFor = array($SearchFor); } $ret = ""; if ($this->linkAndDisplaySame) { $gstrField = GetFullFieldName($this->field, "", false); } else { $gstrField = GetFullFieldName($this->displayFieldName, $this->lookupTable, false); } if ($this->customDisplay) { $gstrField = $this->lwDisplayFieldWrapped; } else { if (!$this->linkAndDisplaySame && $this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype && !$this->ciphererDisplay->isFieldPHPEncrypted($this->displayFieldName)) { $gstrField = $this->lookupPSet->isEnableUpper(GetFullFieldName($this->displayFieldName, $this->lookupTable, false)); } } foreach ($SearchFor as $value) { if (!($value == "null" || $value == "Null" || $value == "")) { if (strlen(trim($ret))) { $ret .= " or "; } if (!$this->multiselect) { if ($strSearchOption == "Starts with") { $value .= '%'; } if ($isSuggest || $strSearchOption == "Contains") { $value = '%' . $value . '%'; } if ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between" || $strSearchOption == "Equals" && $this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) { $value = $this->escapeSearchValForMySQL($value); if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) { $value = $this->lookupPSet->isEnableUpper(db_prepare_string($value)); } else { $value = db_prepare_string($value); } } else { if ($strSearchOption == "Equals") { $value = make_db_value($this->field, $value); } } } if ($strSearchOption == "Equals") { if (!($value == "null" || $value == "Null")) { if ($this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) { $condition = $gstrField . '=' . $value; } else { $condition = GetFullFieldName($this->field, "", false) . '=' . $value; } } } else { if ($strSearchOption == "Starts with" || $strSearchOption == "Contains" && !$this->multiselect) { $condition = $gstrField . " " . $this->like . " " . $value; } else { if ($strSearchOption == "More than") { $condition = $gstrField . " > " . $value; } else { if ($strSearchOption == "Less than") { $condition = $gstrField . "<" . $value; } else { if ($strSearchOption == "Equal or more than") { $condition = $gstrField . ">=" . $value1; } else { if ($strSearchOption == "Equal or less than") { $condition = $gstrField . "<=" . $value1; } else { if ($strSearchOption == "Between") { if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) { $value2 = $this->lookupPSet->isEnableUpper(db_prepare_string($SearchFor2)); } else { $value2 = db_prepare_string($SearchFor2); } $condition = $gstrField . ">=" . $value . " and "; if (IsDateFieldType($this->type)) { $timeArr = db2time($SearchFor2); // for dates without time, add one day if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) { $timeArr = adddays($timeArr, 1); $SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2]; $SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->pageObject->tName); $condition .= $gstrField . "<" . $SearchFor2; } else { $condition .= $gstrField . "<=" . $value2; } } else { $condition .= $gstrField . "<=" . $value2; } } else { if (strpos($value, ",") !== false || strpos($value, '"') !== false) { $value = '"' . str_replace('"', '""', $value) . '"'; } $value = $this->escapeSearchValForMySQL($value); //for search by multiply Lookup wizard field $ret .= GetFullFieldName($this->field, "", false) . " = " . db_prepare_string($value); $ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value . ",%"); $ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value); $ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string($value . ",%"); } } } } } } } if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) { if ($this->linkAndDisplaySame || $strSearchOption == "Equals" && $this->LCType != LCT_AJAX) { $ret .= " " . $condition; } else { if ($this->lookupType == LT_QUERY) { $lookupQueryObj = $this->lookupPSet->getSQLQuery(); $ret .= " EXISTS (" . $lookupQueryObj->toSql($condition . " and " . GetFullFieldName($this->linkFieldName, $this->lookupTable, false) . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field), '', null, false) . ")"; } else { $ret .= " EXISTS (SELECT 1 as fld from " . AddTableWrappers($this->lookupTable) . " where " . $condition . " and " . $this->lwLinkField . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field) . ")"; } } } } } if (strlen(trim($ret))) { $ret = "(" . $ret . ")"; } else { $ret = trim($ret); } return $ret; }
function ImportFromCSV($uploadfile, $strOriginalTableName, $ext, $keys, &$keys_present, &$total_records, &$error_message, &$goodlines, $pageObject, $cipherer) { global $conn, $gSettings; $ret = 1; $fields = array(); $fields = getImportCVSFields($uploadfile); // populate field names array for ($j=0;$j<count($fields);$j++) { $fields[$j] = $fields[$j]; if(substr($fields[$j],0,1)=="\"" && substr($fields[$j],-1)=="\"") $fields[$j]=substr($fields[$j],1,-1); } $fields = getFieldNamesByHeaders($fields, $strOriginalTableName, $ext); if($fields == null) // if error happened return; $keys_present=1; for($k=0; $k<count($keys); $k++) { if (!in_array(RemoveFieldWrappers($keys[$k]),$fields)) { $keys_present=0; break; } } $autoinc = false; if(in_array("id",$fields)) $autoinc=true; if(GetDatabaseType() == 2 && $autoinc) { $sql="SET IDENTITY_INSERT ".AddTableWrappers($strOriginalTableName)." ON"; db_exec($sql,$conn); } $total_records = 0; $line = ""; $row = 0; // parse records from file if (($handle = OpenCSVFile($uploadfile)) !== FALSE) { while (($data = GetCSVLine($handle, 1000000, ",")) !== FALSE) { // first rec contain only fields names if ($row === 0) { $row++; continue; } $arr = array(); foreach($data as $key=>$val) { $type = $gSettings->getFieldType($fields[$key]); if(IsDateFieldType($type)) { $value = localdatetime2db($val); if ( $value !== FALSE && strlen($value) && $value != 'null' ) $arr[$fields[$key]] = $value; else $arr[$fields[$key]] = NULL; } elseif(IsTimeType($type)) { $value = localtime2db($val); if ( $value !== FALSE && strlen($value) && !is_null($val) && strlen($val) ) $arr[$fields[$key]] = $value; else $arr[$fields[$key]] = NULL; } else $arr[$fields[$key]] = $val; } $ret = InsertRecord($arr, $row, $error_message, $goodlines, $keys, $keys_present, $strOriginalTableName, $pageObject, $cipherer, $autoinc); $row++; } CloseCSVFile($handle); } $total_records = $row-1; if(GetDatabaseType() == 2 && $autoinc) { $sql="SET IDENTITY_INSERT ".AddTableWrappers($strOriginalTableName)." OFF"; db_exec($sql,$conn); } return $ret; }
/** * @param Mixed value * @param Number type * @return String */ public function field2time($value, $type) { if (IsDateFieldType($type)) { return "time(" . $value . ")"; } return $value; }
/** * The static function creating the Filter control basing on the control's type * @param String fName * @param Object pageObj * @param Number id * @param Object viewControls * @return Object */ static function getFilterControl($fName, $pageObj, $id, $viewControls) { $contorlType = $pageObj->pSet->getFilterFieldFormat($fName); switch($contorlType) { case FF_VALUE_LIST: include_once getabspath("classes/controls/FilterValuesList.php"); return new FilterValuesList($fName, $pageObj, $id, $viewControls); case FF_BOOLEAN: include_once getabspath("classes/controls/Control.php"); include_once getabspath("classes/controls/CheckboxField.php"); include_once getabspath("classes/controls/FilterBoolean.php"); return new FilterBoolean($fName, $pageObj, $id, $viewControls); case FF_INTERVAL_LIST: include_once getabspath("classes/controls/FilterIntervalList.php"); return new FilterIntervalList($fName, $pageObj, $id, $viewControls); case FF_INTERVAL_SLIDER: include_once getabspath("classes/controls/FilterIntervalSlider.php"); $fieldType = $pageObj->pSet->getFieldType($fName); if( IsDateFieldType($fieldType) ) { include_once getabspath("classes/controls/FilterIntervalDateSlider.php"); return new FilterIntervalDateSlider($fName, $pageObj, $id, $viewControls); } if( IsTimeType($fieldType) ) { include_once getabspath("classes/controls/FilterIntervalDateSlider.php"); include_once getabspath("classes/controls/FilterIntervalTimeSlider.php"); return new FilterIntervalTimeSlider($fName, $pageObj, $id, $viewControls); } return new FilterIntervalSlider($fName, $pageObj, $id, $viewControls); default: include_once getabspath("classes/controls/FilterValuesList.php"); return new FilterValuesList($fName, $pageObj, $id, $viewControls); } }
function db_field2char($value,$type = 3) { // is called for Contains and Starts with searches if(IsCharType($type)) return $value; if(!IsDateFieldType($type)) return "convert(varchar(250),".$value.")"; return "convert(varchar(50),".$value.", 120)"; }
/** * Import data form a CSV file * @param String filePath * @param Array fieldsData * @param Boolean useFirstLine * @param String delimiter * @param String dateFormat * @return Array */ protected function importFromCSV($filePath, $fieldsData, $useFirstLine, $delimiter, $dateFormat) { $metaData = array(); $metaData["totalRecords"] = 0; $fileHandle = OpenCSVFile($filePath, $delimiter); if ($fileHandle === FALSE) { return $metaData; } $keys = $this->pSet->getTableKeys(); $autoinc = $this->hasAutoincImportFields($fieldsData); $errorMessages = array(); $unprocessedData = array(); $addedRecords = 0; $updatedRecords = 0; if ($this->connection->dbType == nDATABASE_MSSQLServer && $autoinc) { $sql = "SET IDENTITY_INSERT " . $this->connection->addTableWrappers($this->strOriginalTableName) . " ON"; $this->connection->exec($sql); } $row = 0; // parse records from file while (($elems = GetCSVLine($fileHandle, 1000000, $delimiter)) !== FALSE) { if ($row === 0 && (!$useFirstLine || $useFirstLine === "false")) { $row++; continue; } $fieldsValuesData = array(); foreach ($elems as $idx => $elem) { if (!isset($fieldsData[$idx])) { continue; } $importFieldName = $fieldsData[$idx]["fName"]; $fType = $fieldsData[$idx]["type"]; if (IsDateFieldType($fType)) { $value = localdatetime2db($elem, $dateFormat); if ($value !== FALSE && strlen($value) && $value != 'null') { $fieldsValuesData[$importFieldName] = $value; } else { $fieldsValuesData[$importFieldName] = NULL; } } else { $fieldsValuesData[$importFieldName] = $elem; } } $this->importRecord($fieldsValuesData, $keys, $autoinc, $addedRecords, $updatedRecords, $errorMessages, $unprocessedData); $metaData["totalRecords"] = $metaData["totalRecords"] + 1; $row++; } CloseCSVFile($fileHandle); if ($this->connection->dbType == nDATABASE_MSSQLServer && $autoinc) { $sql = "SET IDENTITY_INSERT " . $this->connection->addTableWrappers($this->strOriginalTableName) . " OFF"; $this->connection->exec($sql); } $metaData["addedRecords"] = $addedRecords; $metaData["updatedRecords"] = $updatedRecords; $metaData["errorMessages"] = $errorMessages; $metaData["unprocessedData"] = $unprocessedData; return $metaData; }
function BuildEditControl($field, $value, $format, $edit, $fieldNum = 0, $id = "", $validate, $additionalCtrlParams, &$pageObj) { global $rs, $data, $strTableName, $filenamelist, $keys, $locale_info, $jscode; $inputStyle = 'style="'; $inputStyle .= $additionalCtrlParams['style'] ? $additionalCtrlParams['style'] : ''; //$inputStyle .= ($additionalCtrlParams['hidden'] ? 'display: none;' : ''); $inputStyle .= '"'; $cfieldname = GoodFieldName($field) . "_" . $id; $cfield = "value_" . GoodFieldName($field) . "_" . $id; $ctype = "type_" . GoodFieldName($field) . "_" . $id; $is508 = isEnableSection508(); $strLabel = Label($field); if ($fieldNum) { $cfield = "value" . $fieldNum . "_" . GoodFieldName($field) . "_" . $id; $ctype = "type" . $fieldNum . "_" . GoodFieldName($field) . "_" . $id; } $type = GetFieldType($field); $arr = ""; $iquery = "field=" . rawurlencode($field); $keylink = ""; $arrKeys = GetTableKeys($strTableName); for ($j = 0; $j < count($arrKeys); $j++) { $keylink .= "&key" . ($j + 1) . "=" . rawurlencode($data[$arrKeys[$j]]); } $iquery .= $keylink; $isHidden = isset($additionalCtrlParams['hidden']) && $additionalCtrlParams['hidden']; echo '<span id="edit' . $id . '_' . GoodFieldName($field) . '_' . $fieldNum . '" class="runner-nowrap"' . ($isHidden ? ' style="display:none"' : '') . '">'; if ($format == EDIT_FORMAT_FILE && $edit == MODE_SEARCH) { $format = ""; } if ($format == EDIT_FORMAT_TEXT_FIELD) { if (IsDateFieldType($type)) { echo '<input id="' . $ctype . '" type="hidden" name="' . $ctype . '" value="date' . EDIT_DATE_SIMPLE . '">' . GetDateEdit($field, $value, 0, $fieldNum, $edit, $id, $pageObj); } else { if ($edit == MODE_SEARCH) { echo '<input id="' . $cfield . '" ' . $inputStyle . ' type="text" autocomplete="off" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '" ' . GetEditParams($field) . ' value="' . htmlspecialchars($value) . '">'; } else { echo '<input id="' . $cfield . '" ' . $inputStyle . ' type="text" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '" ' . GetEditParams($field) . ' value="' . htmlspecialchars($value) . '">'; } } } else { if ($format == EDIT_FORMAT_TIME) { echo '<input id="' . $ctype . '" ' . $inputStyle . ' type="hidden" name="' . $ctype . '" value="time">'; $arr_number = parsenumbers((string) $value); if (count($arr_number) == 6) { $value = mysprintf("%d:%02d:%02d", array($arr_number[3], $arr_number[4], $arr_number[5])); } $timeAttrs = GetFieldData($strTableName, $field, "FormatTimeAttrs", array()); if (count($timeAttrs)) { if ($timeAttrs["useTimePicker"]) { $convention = $timeAttrs["hours"]; $loc = getLacaleAmPmForTimePicker($convention, true); $tpVal = getValForTimePicker($type, $value, $loc['locale']); echo '<input type="text" ' . $inputStyle . ' name="' . $cfield . '" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'id="' . $cfield . '" ' . GetEditParams($field) . ' value="' . htmlspecialchars($tpVal['val']) . '">'; echo ' '; echo '<img class="runner-imgclock" src="images/clock.gif" alt="Time" border="0" style="margin:4px 0 0 6px; visibility: hidden;" id="trigger-test-' . $cfield . '" />'; } else { echo '<input id="' . $cfield . '" ' . $inputStyle . ' type="text" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '" ' . GetEditParams($field) . ' value="' . htmlspecialchars($value) . '">'; } } } else { if ($format == EDIT_FORMAT_TEXT_AREA) { $nWidth = GetNCols($field); $nHeight = GetNRows($field); if (UseRTE($field)) { $value = RTESafe($value); } else { echo '<textarea id="' . $cfield . '" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '" style="'; if (!isMobile()) { echo "width: " . $nWidth . "px;"; } echo 'height: ' . $nHeight . 'px;">' . htmlspecialchars($value) . '</textarea>'; } } else { if ($format == EDIT_FORMAT_PASSWORD) { echo '<input ' . $inputStyle . ' id="' . $cfield . '" type="Password" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '" ' . GetEditParams($field) . ' value="' . htmlspecialchars($value) . '">'; } else { if ($format == EDIT_FORMAT_DATE) { echo '<input id="' . $ctype . '" type="hidden" name="' . $ctype . '" value="date' . DateEditType($field) . '">' . GetDateEdit($field, $value, DateEditType($field), $fieldNum, $edit, $id, $pageObj); } else { if ($format == EDIT_FORMAT_RADIO) { BuildRadioControl($field, $value, $fieldNum, $id, $edit); } else { if ($format == EDIT_FORMAT_CHECKBOX) { if ($edit == MODE_ADD || $edit == MODE_INLINE_ADD || $edit == MODE_EDIT || $edit == MODE_INLINE_EDIT) { $checked = ""; if ($value && $value != 0) { $checked = " checked"; } echo '<input id="' . $ctype . '" type="hidden" name="' . $ctype . '" value="checkbox">'; echo '<input id="' . $cfield . '" type="Checkbox" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '" ' . $checked . '>'; } else { echo '<input id="' . $ctype . '" type="hidden" name="' . $ctype . '" value="checkbox">'; echo '<select id="' . $cfield . '" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . 'name="' . $cfield . '">'; $val = array("", "on", "off"); $show = array("", "True", "False"); foreach ($val as $i => $v) { $sel = ""; if ($value === $v) { $sel = " selected"; } echo '<option value="' . $v . '"' . $sel . '>' . $show[$i] . '</option>'; } echo "</select>"; } } else { if ($format == EDIT_FORMAT_DATABASE_IMAGE || $format == EDIT_FORMAT_DATABASE_FILE) { $disp = ""; $strfilename = ""; //$onchangefile=""; if ($edit == MODE_EDIT || $edit == MODE_INLINE_EDIT) { $value = db_stripslashesbinary($value); $itype = SupposeImageType($value); $thumbnailed = false; $thumbfield = ""; if ($itype) { if ($thumbnailed) { $disp = "<a "; if (IsUseiBox($field, $strTableName)) { $disp .= " rel='ibox'"; } else { $disp .= " target=_blank"; } $disp .= " href=\"imager.php?table=" . GetTableURL($strTableName) . "&" . $iquery . "&rndVal=" . rand(0, 32768) . "\">"; $disp .= "<img id=\"image_" . GoodFieldName($field) . "_" . $id . "\" name=\"" . $cfield . "\" border=0"; if (isEnableSection508()) { $disp .= " alt=\"Image from DB\""; } $disp .= " src=\"imager.php?table=" . GetTableURL($strTableName) . "&field=" . rawurlencode($thumbfield) . "&alt=" . rawurlencode($field) . $keylink . "&rndVal=" . rand(0, 32768) . "\">"; $disp .= "</a>"; } else { $disp = '<img id="image_' . GoodFieldName($field) . '_' . $id . '" name="' . $cfield . '"'; if (isEnableSection508()) { $disp .= ' alt="Image from DB"'; } $disp .= ' border=0 src="imager.php?table=' . GetTableURL($strTableName) . '&' . $iquery . "&rndVal=" . rand(0, 32768) . '">'; } } else { if (strlen($value)) { $disp = '<img id="image_' . GoodFieldName($field) . '_' . $id . '" name="' . $cfield . '" border=0 '; if (isEnableSection508()) { $disp .= ' alt="file"'; } $disp .= ' src="images/file.gif">'; } else { $disp = '<img id="image_' . GoodFieldName($field) . '_' . $id . '" name="' . $cfield . '" border="0"'; if (isEnableSection508()) { $disp .= ' alt=" "'; } $disp .= ' src="images/no_image.gif">'; } } // filename if ($format == EDIT_FORMAT_DATABASE_FILE && !$itype && strlen($value)) { if (!($filename = @$data[GetFilenameField($field)])) { $filename = "file.bin"; } $disp = '<a href="getfile.php?table=' . GetTableURL($strTableName) . '&filename=' . htmlspecialchars($filename) . '&' . $iquery . '".>' . $disp . '</a>'; } // filename edit if ($format == EDIT_FORMAT_DATABASE_FILE && GetFilenameField($field)) { if (!($filename = @$data[GetFilenameField($field)])) { $filename = ""; } if ($edit == MODE_INLINE_EDIT) { $strfilename = '<br><label for="filename_' . $cfieldname . '">' . mlang_message("FILENAME") . '</label> <input type="text" ' . $inputStyle . ' id="filename_' . $cfieldname . '" name="filename_' . $cfieldname . '" size="20" maxlength="50" value="' . htmlspecialchars($filename) . '">'; } else { $strfilename = '<br><label for="filename_' . $cfieldname . '">' . mlang_message("FILENAME") . '</label> <input type="text" ' . $inputStyle . ' id="filename_' . $cfieldname . '" name="filename_' . $cfieldname . '" size="20" maxlength="50" value="' . htmlspecialchars($filename) . '">'; } } $strtype = '<br><input id="' . $ctype . '_keep" type="Radio" name="' . $ctype . '" value="file0" checked>' . mlang_message("KEEP"); if ((strlen($value) || $edit == MODE_INLINE_EDIT) && !IsRequired($field)) { $strtype .= '<input id="' . $ctype . '_delete" type="Radio" name="' . $ctype . '" value="file1">' . mlang_message("DELETE"); } $strtype .= '<input id="' . $ctype . '_update" type="Radio" name="' . $ctype . '" value="file2">' . mlang_message("UPDATE"); } else { // if Add mode $strtype = '<input id="' . $ctype . '" type="hidden" name="' . $ctype . '" value="file2">'; if ($format == EDIT_FORMAT_DATABASE_FILE && GetFilenameField($field)) { $strfilename = '<br><label for="filename_' . $cfieldname . '">' . mlang_message("FILENAME") . '</label> <input type="text" ' . $inputStyle . ' id="filename_' . $cfieldname . '" name="filename_' . $cfieldname . '" size="20" maxlength="50">'; } } if ($edit == MODE_INLINE_EDIT && $format == EDIT_FORMAT_DATABASE_FILE) { $disp = ""; } echo $disp . $strtype; if ($edit == MODE_EDIT || $edit == MODE_INLINE_EDIT) { echo '<br>'; } echo '<input type="File" ' . $inputStyle . ' id="' . $cfield . '" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . ' name="' . $cfield . '" >' . $strfilename; echo '<input type="Hidden" id="notempty_' . $cfieldname . '" value="' . (strlen($value) ? 1 : 0) . '">'; } else { if ($format == EDIT_FORMAT_LOOKUP_WIZARD) { BuildSelectControl($field, $value, $fieldNum, $edit, $id, $additionalCtrlParams, $pageObj); } else { if ($format == EDIT_FORMAT_HIDDEN) { echo '<input id="' . $cfield . '" type="Hidden" name="' . $cfield . '" value="' . htmlspecialchars($value) . '">'; } else { if ($format == EDIT_FORMAT_READONLY) { echo '<input id="' . $cfield . '" type="Hidden" name="' . $cfield . '" value="' . htmlspecialchars($value) . '">'; } else { if ($format == EDIT_FORMAT_FILE) { $disp = ""; $strfilename = ""; $function = ""; if ($edit == MODE_EDIT || $edit == MODE_INLINE_EDIT) { // show current file if (ViewFormat($field) == FORMAT_FILE || ViewFormat($field) == FORMAT_FILE_IMAGE) { $disp = GetData($data, $field, ViewFormat($field)) . "<br>"; } $filename = $value; // filename edit $filename_size = 30; if (UseTimestamp($field)) { $filename_size = 50; } $strfilename = '<input type=hidden name="filenameHidden_' . $cfieldname . '" value="' . htmlspecialchars($filename) . '"><br>' . mlang_message("FILENAME") . ' <input type="text" style="background-color:gainsboro" disabled id="filename_' . $cfieldname . '" name="filename_' . $cfieldname . '" size="' . $filename_size . '" maxlength="100" value="' . htmlspecialchars($filename) . '">'; if ($edit == MODE_INLINE_EDIT) { $strtype = '<br><input id="' . $ctype . '_keep" type="Radio" name="' . $ctype . '" value="upload0" checked class="runner-uploadtype">' . mlang_message("KEEP"); } else { $strtype = '<br><input id="' . $ctype . '_keep" type="Radio" name="' . $ctype . '" value="upload0" checked class="runner-uploadtype">' . mlang_message("KEEP"); } if ((strlen($value) || $edit == MODE_INLINE_EDIT) && !IsRequired($field)) { $strtype .= '<input id="' . $ctype . '_delete" type="Radio" name="' . $ctype . '" value="upload1" class="runner-uploadtype">' . mlang_message("DELETE"); } $strtype .= '<input id="' . $ctype . '_update" type="Radio" name="' . $ctype . '" value="upload2" class="runner-uploadtype">' . mlang_message("UPDATE"); } else { // if Adding record $filename_size = 30; if (UseTimestamp($field)) { $filename_size = 50; } $strtype = '<input id="' . $ctype . '" type="hidden" name="' . $ctype . '" value="upload2">'; $strfilename = '<br>' . mlang_message("FILENAME") . ' <input type="text" id="filename_' . $cfieldname . '" name="filename_' . $cfieldname . '" size="' . $filename_size . '" maxlength="100">'; } echo $disp . $strtype . $function; if ($edit == MODE_EDIT || $edit == MODE_INLINE_EDIT) { echo '<br>'; } echo '<input type="File" id="' . $cfield . '" ' . (($edit == MODE_INLINE_EDIT || $edit == MODE_INLINE_ADD) && $is508 == true ? 'alt="' . $strLabel . '" ' : '') . ' name="' . $cfield . '" >' . $strfilename; echo '<input type="Hidden" id="notempty_' . $cfieldname . '" value="' . (strlen($value) ? 1 : 0) . '">'; } } } } } } } } } } } } if (count($validate['basicValidate']) && array_search('IsRequired', $validate['basicValidate']) !== false) { echo ' <font color="red">*</font></span>'; } else { echo '</span>'; } }
function SQLStatement($sql, $order, $pagesize, $connection, &$searchClauseObj, &$params) { // copy properties to object RunnerApply($this, $params); $this->searchClauseObj = $searchClauseObj; $this->pSet = new ProjectSettings($this->tName, PAGE_REPORT); if (!is_array($sql)) { die('Invalid sql parameter'); } global $reportCaseSensitiveGroupFields; $this->_originalSql = $sql; $start = 0; $fields = array(); for ($i = 0; $i < count($this->repGroupFields); $i++) { for ($j = 0; $j < count($this->fieldsArr); $j++) { if ($this->repGroupFields[$i]['strGroupField'] == $this->fieldsArr[$j]['name']) { $add = array(); $add['name'] = $this->fieldsArr[$j]['name']; if (IsNumberType($this->pSet->getFieldType($this->fieldsArr[$j]['name']))) { $add['type'] = 'numeric'; } elseif (IsCharType($this->pSet->getFieldType($this->fieldsArr[$j]['name']))) { $add['type'] = 'char'; $add['case_sensitive'] = $reportCaseSensitiveGroupFields; } elseif (IsDateFieldType($this->pSet->getFieldType($this->fieldsArr[$j]['name']))) { $add['type'] = 'date'; } else { $add['type'] = 'char'; } $add['interval'] = $this->repGroupFields[$i]['groupInterval']; $add['viewformat'] = $this->fieldsArr[$j]['viewFormat']; $add['rowsinsummary'] = 1; if ($this->fieldsArr[$j]['totalMax'] || $this->fieldsArr[$j]['totalMin'] || $this->fieldsArr[$j]['totalAvg'] || $this->fieldsArr[$j]['totalSum']) { $add['rowsinsummary']++; } if ($this->repLayout == REPORT_STEPPED) { $add['rowsinheader'] = 1; } elseif ($this->repLayout == REPORT_BLOCK) { $add['rowsinheader'] = 0; } elseif ($this->repLayout == REPORT_OUTLINE || $this->repLayout == REPORT_ALIGN) { if ($j == count($this->fieldsArr) - 1) { $add['rowsinheader'] = 2; } else { $add['rowsinheader'] = 1; } } elseif ($this->repLayout == REPORT_TABULAR) { $add['rowsinheader'] = 0; } $fields[] = $add; } } } $this->_hasGroups = count($fields) > 0; foreach ($fields as $field) { $f = create_reportfield($field['name'], $field['type'], $field['interval'], 'grp', $this->tName); $start = $f->setStart($start); if (isset($field['case_sensitive'])) { $f->setCaseSensitive($field['case_sensitive']); } if (isset($field['rowsinsummary'])) { $f->_rowsInSummary = $field['rowsinsummary']; } if (isset($field['rowsinheader'])) { $f->_rowsInHeader = $field['rowsinheader']; } $f->_viewFormat = $field['viewformat']; $this->_fields[] = $f; } // order if ($order) { $order_in = array(); $order_out = array(); $order_old = array(); foreach ($order as $o) { $order_in[] = $o[2] . ' as ' . cached_ffn('originalorder' . $o[0]); $order_out[] = cached_ffn('originalorder' . $o[0]) . ' ' . $o[1]; $groupField = false; for ($i = 0; $i < count($this->repGroupFields); $i++) { for ($j = 0; $j < count($this->fieldsArr); $j++) { if ($this->repGroupFields[$i]['strGroupField'] == $this->fieldsArr[$j]['name']) { $fieldIndex = $this->pSet->GetFieldIndex($this->repGroupFields[$i]['strGroupField']); if ($fieldIndex == $o[0]) { $n = $this->repGroupFields[$i]['groupOrder'] - 1; $this->_fields[$n]->_orderBy = $o[1]; $groupField = true; } } } } // don't add group fields to the $order_old if (!$groupField) { $order_old[] = $o[2] . ' ' . $o[1]; } } $this->_order_in = join(', ', $order_in); $this->_order_out = join(', ', $order_out); $this->_order_old = join(', ', $order_old); } for ($i = 0; $i < count($this->fieldsArr); $i++) { if ($this->fieldsArr[$i]['totalMax']) { $this->_aggregates[] = 'MAX(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "MAX"); } if ($this->fieldsArr[$i]['totalMin']) { $this->_aggregates[] = 'MIN(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "MIN"); } if ($this->fieldsArr[$i]['totalAvg']) { if (!IsDateFieldType($this->pSet->getFieldType($this->fieldsArr[$i]['name']))) { $this->_aggregates[] = 'AVG(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "AVG"); $this->_aggregates[] = 'COUNT(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "NAVG"); } } if ($this->fieldsArr[$i]['totalSum']) { if (!IsDateFieldType($this->pSet->getFieldType($this->fieldsArr[$i]['name']))) { $this->_aggregates[] = 'SUM(' . cached_ffn($this->fieldsArr[$i]['name'], true) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "SUM"); } } } $this->_reportSummary = $this->repPageSummary || $this->repGlobalSummary; $this->_pagesize = $pagesize; }
/** * Get the WHERE clause conditions string for the search or suggest SQL query * @param String SearchFor * @param String strSearchOption * @param String SearchFor2 * @param String etype * @param Boolean isSuggest * @return String */ function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest) { if ($this->lookupType == LT_LISTOFVALUES) { return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest); } $baseResult = $this->baseSQLWhere($strSearchOption); if ($baseResult === false) { return ""; } if ($baseResult !== "") { return $baseResult; } if ($this->connection->dbType != nDATABASE_MySQL) { $this->btexttype = IsTextType($this->type); } if ($this->multiselect && $strSearchOption != "Equals") { $SearchFor = splitvalues($SearchFor); } else { $SearchFor = array($SearchFor); } $gstrField = $this->getFieldSQLDecrypt(); $gstrField = $this->getFieldSQLDecrypt(); if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && (!IsCharType($this->type) || $this->btexttype)) { $gstrField = $this->connection->field2char($gstrField, $this->type); } $ret = ""; foreach ($SearchFor as $searchItem) { $value = $searchItem; if ($value == "null" || $value == "Null" || $value == "") { continue; } if (strlen(trim($ret))) { $ret .= " or "; } if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && !$this->multiselect) { $value = $this->connection->escapeLIKEpattern($value); if ($strSearchOption == "Starts with") { $value .= '%'; } if ($strSearchOption == "Contains") { $value = '%' . $value . '%'; } } if ($strSearchOption != "Starts with" && $strSearchOption != "Contains") { $value = make_db_value($this->field, $value); } $searchIsCaseInsensitive = $this->pageObject->pSetEdit->getNCSearch(); if ($strSearchOption == "Equals" && !($value == "null" || $value == "Null")) { $condition = $gstrField . '=' . $value; } else { if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && !$this->multiselect) { $condition = $gstrField . " " . $this->like . " " . $this->connection->prepareString($value); } else { if ($strSearchOption == "More than") { $condition = $gstrField . " > " . $value; } else { if ($strSearchOption == "Less than") { $condition = $gstrField . "<" . $value; } else { if ($strSearchOption == "Equal or more than") { $condition = $gstrField . ">=" . $value1; } else { if ($strSearchOption == "Equal or less than") { $condition = $gstrField . "<=" . $value1; } else { if ($strSearchOption == "Between") { $value2 = $this->connection->prepareString($SearchFor2); if ($this->lookupType == LT_QUERY && IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) { $value2 = $this->connection->upper($value2); } $condition = $gstrField . ">=" . $value . " and "; if (IsDateFieldType($this->type)) { $timeArr = db2time($SearchFor2); // for dates without time, add one day if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) { $timeArr = adddays($timeArr, 1); $SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2]; $SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->tName); $condition .= $gstrField . "<" . $SearchFor2; } else { $condition .= $gstrField . "<=" . $value2; } } else { $condition .= $gstrField . "<=" . $value2; } } else { if ($this->multiselect) { if (strpos($value, ",") !== false || strpos($value, '"') !== false) { $value = '"' . str_replace('"', '""', $value) . '"'; } $fullFieldName = $this->getFieldSQLDecrypt(); $value = $this->connection->escapeLIKEpattern($value); //for search by multiply Lookup wizard field $ret .= $fullFieldName . " = " . $this->connection->prepareString($value); $ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString("%," . $value . ",%"); $ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString("%," . $value); $ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString($value . ",%"); } } } } } } } } if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) { if ($this->linkAndDisplaySame || $strSearchOption != "Contains" && $strSearchOption != "Starts with") { $ret .= " " . $condition; } else { return ""; } } } $ret = trim($ret); if (strlen($ret)) { $ret = "(" . $ret . ")"; } return $ret; }
/** * */ function PrepareValue($value, $type) { if ($this->_connection->dbType == nDATABASE_Oracle || $this->_connection->dbType == nDATABASE_DB2 || $this->_connection->dbType == nDATABASE_Informix) { if (IsBinaryType($type)) { if ($this->_connection->dbType == nDATABASE_Oracle) { return "EMPTY_BLOB()"; } return "?"; } if ($this->_connection->dbType == nDATABASE_Informix && IsTextType($type)) { return "?"; } } if (IsDateFieldType($type)) { if (!$value) { return "null"; } else { $this->_connection->addDateQuotes($value); } } if (NeedQuotes($type)) { return $this->_connection->prepareString($value); } return 0 + $value; }
function getDisplayValue($index, $value) { global $locale_info; $field = $this->xml_array["group_fields"][$index]["name"]; if ($value == "" || is_null($value)) { return ""; } $ftype = $this->getFieldType($field); $arr = array(); $arr = $this->xml_array["group_fields"]; for ($i = 0; $i < count($arr) - 1; $i++) { if ($field == $arr[$i]["name"] && $index == $i) { $int_type = $arr[$i]["int_type"]; break; } } if ($int_type == 0) { $prefix = ""; if ($this->table_type != "db") { if (!$this->fromWizard) { $prefix = $this->xml_array["tables"][0] . "_"; } } else { $field = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$prefix . $field]['curr'] == true) { return str_format_currency($value); } else { return $value; } } elseif (IsNumberType($ftype)) { $start = $value - $value % $int_type; $end = $start + $int_type; $prefix = ""; if ($this->table_type != "db") { if (!$this->fromWizard) { $prefix = $this->xml_array["tables"][0] . "_"; } } else { $field = $this->CrossGoodFieldName($field); } if ($this->xml_array['totals'][$prefix . $field]['curr'] == true) { return str_format_currency($start) . " - " . str_format_currency($end); } else { return $start . " - " . $end; } } elseif (IsCharType($ftype)) { return substr($value, 0, $int_type); } elseif (IsDateFieldType($ftype)) { $dvalue = substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2); if (strlen($value) == 10) { $dvalue .= " " . substr($value, 8, 2) . "00:00"; } elseif (strlen($value) == 12) { $dvalue .= " " . substr($value, 8, 2) . ":" . substr($value, 10, 2) . ":00"; } $tm = db2time($dvalue); if (!count($tm)) { return ""; } if ($int_type == 1) { // DATE_INTERVAL_YEAR return $tm[0]; } elseif ($int_type == 2) { // DATE_INTERVAL_QUARTER return $tm[0] . "/Q" . $tm[1]; } elseif ($int_type == 3) { // DATE_INTERVAL_MONTH return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0]; } elseif ($int_type == 4) { // DATE_INTERVAL_WEEK $dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]); return format_shortdate(db2time($dates[0])) . ' - ' . format_shortdate(db2time($dates[1])); } elseif ($int_type == 5) { // DATE_INTERVAL_DAY return format_shortdate($tm); } elseif ($int_type == 6) { // DATE_INTERVAL_HOUR $tm[4] = 0; $tm[5] = 0; return str_format_datetime($tm); } elseif ($int_type == 7) { // DATE_INTERVAL_MINUTE $tm[5] = 0; return str_format_datetime($tm); } else { return str_format_datetime($tm); } } }
function LogEdit($str_table, $newvalues, $oldvalues, $keys) { global $globalEvents; $retval = true; $table = $str_table; $pSet = new ProjectSettings($str_table); if ($globalEvents->exists("OnAuditLog")) { $retval = $globalEvents->OnAuditLog($this->strEdit, $this->params, $table, $keys, $newvalues, $oldvalues); } if ($retval) { if (count($keys) > 0) { $key = ""; foreach ($keys as $idx => $val) { if ($key != "") { $key .= ","; } $key .= $val; } } $fp = $this->CreateLogFile(); $str = format_datetime_custom(db2time(now()), "MMM dd,yyyy") . chr(9) . format_datetime_custom(db2time(now()), "HH:mm:ss") . chr(9) . $this->params[0] . chr(9) . $this->params[1] . chr(9) . $table . chr(9) . $this->strEdit . chr(9) . $key; $putsValue = true; $str_add = ""; if ($this->logValueEnable($str_table)) { foreach ($newvalues as $idx => $val) { $type = $pSet->getFieldType($idx); if (IsBinaryType($type)) { continue; } if (IsDateFieldType($type)) { $newvalue = format_datetime_custom(db2time($newvalues[$idx]), "yyyy-MM-dd HH:mm:ss"); $oldvalue = format_datetime_custom(db2time($oldvalues[$idx]), "yyyy-MM-dd HH:mm:ss"); } else { $newvalue = $newvalues[$idx]; $oldvalue = $oldvalues[$idx]; } if ($newvalue != $oldvalue) { $v1 = ""; if (IsBinaryType($type)) { $v1 = "<binary value>"; } else { $v1 = str_replace(array("\r\n", "\n", "\t"), " ", $oldvalue); if (strlen($v1) > $this->maxFieldLength) { $v1 = substr($v1, 0, $this->maxFieldLength); } } $v2 = ""; if (IsBinaryType($type)) { $v2 = "<binary value>"; } else { $v2 = str_replace(array("\r\n", "\n", "\t"), " ", $newvalue); if (strlen($v2) > $this->maxFieldLength) { $v2 = substr($v2, 0, $this->maxFieldLength); } } $str_add .= $str . chr(9) . $idx . chr(9) . $v1 . chr(9) . $v2 . "\r\n"; } } } else { $str_add .= $str . "\r\n"; } if ($fp) { fputs($fp, $str_add); fclose($fp); } } return $retval; }
/** * Get filter's WHERE clause condition basing on the filter's type * * @param String filterType A string representing the filter's type * @param String fName * @param String fValue * @param String dbType * @return String */ function getFilterWhereByType($filterType, $fName, $fValue, $sValue, $parentValues, $connection) { $pSet = new ProjectSettings($this->tName, PAGE_SEARCH); $fullFieldName = RunnerPage::_getFieldSQLDecrypt($fName, $connection, $pSet, $this->cipherer); $fieldType = $pSet->getFieldType($fName); $dateField = IsDateFieldType($fieldType); $timeField = IsTimeType($fieldType); if ($dateField || $timeField) { include_once getabspath("classes/controls/FilterControl.php"); include_once getabspath("classes/controls/FilterIntervalSlider.php"); include_once getabspath("classes/controls/FilterIntervalDateSlider.php"); } switch ($filterType) { case 'interval': $intervalData = $pSet->getFilterIntervalDatabyIndex($fName, $fValue); if (!count($intervalData)) { return ""; } include_once getabspath("classes/controls/FilterControl.php"); include_once getabspath("classes/controls/FilterIntervalList.php"); return FilterIntervalList::getIntervalFilterWhere($fName, $intervalData, $pSet, $this->cipherer, $this->tName, $connection); case 'equals': if (!count($parentValues)) { return $fullFieldName . "=" . $this->cipherer->MakeDBValue($fName, $fValue, "", true); } $wheres = array(); $wheres[] = $fullFieldName . "=" . $this->cipherer->MakeDBValue($fName, $fValue, "", true); $parentFiltersNames = $pSet->getParentFiltersNames($fName); foreach ($parentFiltersNames as $key => $parentName) { $wheres[] = RunnerPage::_getFieldSQLDecrypt($parentName, $connection, $pSet, $this->cipherer) . "=" . $this->cipherer->MakeDBValue($parentName, $parentValues[$key], "", true); } return "(" . implode(" AND ", $wheres) . ")"; case 'checked': if ($fValue != "on" && $fValue != "off") { return ""; } $bNeedQuotes = NeedQuotes($fieldType); include_once getabspath("classes/controls/Control.php"); include_once getabspath("classes/controls/CheckboxField.php"); return CheckboxField::constructFieldWhere($fullFieldName, $bNeedQuotes, $fValue == "on", $pSet->getFieldType($fName), $connection->dbType); case 'slider': if ($dateField) { return FilterIntervalDateSlider::getDateSliderWhere($fName, $pSet, $this->cipherer, $this->tName, $fValue, $sValue, $filterType, $fullFieldName); } if ($timeField) { include_once getabspath("classes/controls/FilterIntervalTimeSlider.php"); return FilterIntervalTimeSlider::getTimeSliderWhere($fName, $pSet, $this->cipherer, $this->tName, $fValue, $sValue, $filterType, $fullFieldName); } return $this->cipherer->MakeDBValue($fName, $fValue, "", true) . "<=" . $fullFieldName . " AND " . $fullFieldName . "<=" . $this->cipherer->MakeDBValue($fName, $sValue, "", true); case 'moreequal': if ($dateField) { return FilterIntervalDateSlider::getDateSliderWhere($fName, $pSet, $this->cipherer, $this->tName, $fValue, $sValue, $filterType, $fullFieldName); } if ($timeField) { include_once getabspath("classes/controls/FilterIntervalTimeSlider.php"); return FilterIntervalTimeSlider::getTimeSliderWhere($fName, $pSet, $this->cipherer, $this->tName, $fValue, $sValue, $filterType, $fullFieldName); } return $this->cipherer->MakeDBValue($fName, $fValue, "", true) . "<=" . $fullFieldName; case 'lessequal': if ($dateField) { return FilterIntervalDateSlider::getDateSliderWhere($fName, $pSet, $this->cipherer, $this->tName, $fValue, $sValue, $filterType, $fullFieldName); } if ($timeField) { include_once getabspath("classes/controls/FilterIntervalTimeSlider.php"); return FilterIntervalTimeSlider::getTimeSliderWhere($fName, $pSet, $this->cipherer, $this->tName, $fValue, $sValue, $filterType, $fullFieldName); } return $fullFieldName . "<=" . $this->cipherer->MakeDBValue($fName, $fValue, "", true); default: return ""; } }
function WRprepare_for_db($field, $value, $table = "") { $type = WRGetFieldType($table . "." . $field); if (is_array($value)) { $value = combinevalues($value); } if (($value === "" || $value === FALSE) && !IsCharType($type)) { return ""; } if (IsDateFieldType($type)) { $value = localdatetime2db($value); } return $value; }
{ $fields_type=array(); $fields_type=WRGetAllCustomFieldType(); } foreach ($group_fields as $fld) { if(!is_wr_custom()) $type = WRGetFieldType($fld); else $type = $fields_type[$fld]; if ( IsNumberType( $type ) ) { $b_includes .= "fld_types['" . jsreplace($fld) . "'] = \"number\";"."\r\n"; } elseif ( IsCharType( $type ) ) { $b_includes .= "fld_types['" . jsreplace($fld) . "'] = \"string\";"."\r\n"; } elseif ( IsDateFieldType( $type ) ) { $b_includes .= "fld_types['" . jsreplace($fld) . "'] = \"date\";"."\r\n"; } } if (is_wr_db()) { $b_includes .= ' var NEXT_PAGE_URL = "'.GetTableLink("webreport4").'", PREV_PAGE_URL = "'.GetTableLink("webreport2").'"; '."\r\n"; } else { $b_includes .= ' var NEXT_PAGE_URL = "'.GetTableLink("webreport4").'", PREV_PAGE_URL = "'.GetTableLink("webreport0").'"; '."\r\n"; }
function ImportFromCSV($uploadfile) { $ret = 1; global $error_message, $keys, $goodlines, $total_records, $conn, $strOriginalTableName, $keys_present, $auditObj; $fields = array(); $fields = getImportCVSFields($uploadfile); // populate field names array for ($j = 0; $j < count($fields); $j++) { $fields[$j] = trim($fields[$j]); if (substr($fields[$j], 0, 1) == "\"" && substr($fields[$j], -1) == "\"") { $fields[$j] = substr($fields[$j], 1, -1); } } $fields = getFieldNamesByHeaders($fields); $keys_present = 1; for ($k = 0; $k < count($keys); $k++) { if (!in_array(RemoveFieldWrappers($keys[$k]), $fields)) { $keys_present = 0; break; } } $autoinc = false; if (in_array("Record ID", $fields)) { $autoinc = true; } if ($autoinc) { $sql = "SET IDENTITY_INSERT " . AddTableWrappers($strOriginalTableName) . " ON"; db_exec($sql, $conn); } $total_records = 0; $line = ""; $row = 0; // parse records from file if (($handle = fopen($uploadfile, "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000000, ",")) !== FALSE) { // first rec contain only fields names if ($row === 0) { $row++; continue; } $arr = array(); foreach ($data as $key => $val) { $type = GetFieldType($fields[$key]); if (IsDateFieldType($type)) { $value = localdatetime2db($val); if ($value !== -1 && $value !== FALSE && strlen($value)) { $arr[$fields[$key]] = $value; } else { $arr[$fields[$key]] = NULL; } } elseif (IsTimeType($type)) { $value = localtime2db($val); if ($value !== -1 && $value !== FALSE && strlen($value) && strlen($val) && !is_null($val)) { $arr[$fields[$key]] = $value; } else { $arr[$fields[$key]] = NULL; } } else { $arr[$fields[$key]] = $val; } } $ret = InsertRecord($arr, $row); $row++; } fclose($handle); } $total_records = $row - 1; if ($autoinc) { $sql = "SET IDENTITY_INSERT " . AddTableWrappers($strOriginalTableName) . " OFF"; db_exec($sql, $conn); } return $ret; }
/** * It's called for Contains and Starts with searches * @param Mixed value * @param Number type (optional) * @return String */ public function field2char($value, $type = 3) { if (IsCharType($type)) { return $value; } if (!IsDateFieldType($type)) { return "convert(varchar(250)," . $value . ")"; } return "convert(varchar(50)," . $value . ", 120)"; }