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; }
/** * @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; } } } } }
function prepare_for_db($field, $value, $controltype = "", $postfilename = "", $table = "") { global $strTableName; if ($table == "") { $table = $strTableName; } $pSet = new ProjectSettings($table); $filename = ""; $type = $pSet->getFieldType($field); if (!$controltype || $controltype == "multiselect") { if (is_array($value)) { $value = combinevalues($value); } if (($value === "" || $value === FALSE) && !IsCharType($type)) { return ""; } if (IsGuid($type)) { if (!IsGuidString($value)) { return ""; } } return $value; } else { if ($controltype == "time") { if (!strlen($value)) { return ""; } $time = localtime2db($value); if (IsDateFieldType($pSet->getFieldType($field))) { $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_DP) { $time = localdatetime2db($value); if ($time == "null") { return ""; } return $time; } else { if ($dformat == EDIT_DATE_DD || $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; } } } } }
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; }