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; }
/** * Prepare fields' values of numeric and time types for db * The fields of other types have been already db-prepared * @param Array fieldsValuesData * @return Array */ protected function prepareFiledsValuesData($fieldsValuesData) { global $locale_info; $refinedFieldsValuesData = array(); foreach ($fieldsValuesData as $field => $val) { $type = $this->pSet->getFieldType($field); if (IsTimeType($type)) { $value = prepare_for_db($field, $val, "time", "", $this->tName); if (strlen($value) > 0) { $refinedFieldsValuesData[$field] = $value; } else { $refinedFieldsValuesData[$field] = NULL; } continue; } if (!IsNumberType($type)) { $refinedFieldsValuesData[$field] = $val; continue; } $value = str_replace(",", ".", (string) $val); if (strlen($value) > 0) { if (strpos($value, $locale_info["LOCALE_SCURRENCY"]) !== FALSE) { // try to process the currency format $value = str_replace(array($locale_info["LOCALE_SCURRENCY"], " "), array("", ""), $value); $matches = array(); if (preg_match('/^\\((.*)\\)$/', $value, $matches)) { $value = -1 * $matches[1]; } } $refinedFieldsValuesData[$field] = 0 + $value; } else { $refinedFieldsValuesData[$field] = NULL; } } return $refinedFieldsValuesData; }
/** * @param String field * @param Mixed value * @param String controltype * @param String postfilename * @param String table The datasource table name * @intellisense */ function prepare_for_db($field, $value, $controltype = "", $postfilename = "", $table = "") { global $strTableName, $cman; if ($table == "") { $table = $strTableName; } $pSet = new ProjectSettings($table); $connection = $cman->byTable($table); $filename = ""; $type = $pSet->getFieldType($field); if ((!$controltype || $controltype == "multiselect") && !IsTimeType($type)) { if (is_array($value)) { $value = combinevalues($value); } if (($value === "" || $value === FALSE) && !IsCharType($type)) { return ""; } if (IsGuid($type)) { if (!IsGuidString($value)) { return ""; } } if (IsFloatType($type)) { return makeFloat($value); } if (IsNumberType($type) && !is_int($value)) { $value = trim($value); if (!is_numeric(str_replace(",", ".", $value))) { $value = ""; } } return $value; } else { if ($controltype == "time" || IsTimeType($type)) { if (!strlen($value)) { return ""; } $time = localtime2db($value); if ($connection->dbType == nDATABASE_PostgreSQL) { $timeArr = explode(":", $time); if ($timeArr[0] > 24 || $timeArr[1] > 59 || $timeArr[2] > 59) { return ""; } } if (IsDateFieldType($type)) { $time = "2000-01-01 " . $time; } return $time; } else { if (substr($controltype, 0, 4) == "date") { $dformat = substr($controltype, 4); if ($dformat == EDIT_DATE_SIMPLE || $dformat == EDIT_DATE_SIMPLE_INLINE || $dformat == EDIT_DATE_SIMPLE_DP) { $time = localdatetime2db($value); if ($time == "null") { return ""; } return $time; } else { if ($dformat == EDIT_DATE_DD || $dformat == EDIT_DATE_DD_INLINE || $dformat == EDIT_DATE_DD_DP) { $a = explode("-", $value); if (count($a) < 3) { return ""; } else { $y = $a[0]; $m = $a[1]; $d = $a[2]; } if ($y < 100) { if ($y < 70) { $y += 2000; } else { $y += 1900; } } return mysprintf("%04d-%02d-%02d", array($y, $m, $d)); } else { return ""; } } } else { if (substr($controltype, 0, 8) == "checkbox") { if ($value == "on") { $ret = 1; } else { if ($value == "none") { return ""; } else { $ret = 0; } } return $ret; } else { return false; } } } } }
/** * 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 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; }
/** * 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); } }