function getImportExcelData($data, $fields)
{
global $total_records, $cCharset, $columnIndex;
foreach ($data->getWorksheetIterator() as $worksheet) {
$highestRow = $worksheet->getHighestRow();
for ($row = 2; $row <= $highestRow; ++$row) {
for ($col = 0; $col < $columnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$date_format = $cell->getParent()->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode();
$value = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $date_format);
if (is_a($value, 'PHPExcel_RichText')) {
$value = $value->getPlainText();
}
if ($value) {
$time = array();
if (strtotime($value)) {
$value = strtotime($value);
} else {
$d_format = "";
for ($i = 0; $i < strlen($date_format); $i++) {
$letter = substr(strtolower($date_format), $i, 1);
if ($letter == "d" || $letter == "m" || $letter == "y") {
if (strpos($d_format, $letter) === false) {
$d_format .= $letter;
}
}
}
$value = strtotime(localdatetime2db($value, $d_format));
}
// $value = PHPExcel_Shared_Date::ExcelToPHP($value);
$time = localtime($value, true);
$val = $time["tm_year"] + 1900 . "-" . ($time["tm_mon"] + 1) . "-" . $time["tm_mday"] . " " . $time["tm_hour"] . ":" . $time["tm_min"] . ":" . $time["tm_sec"];
} else {
$val = NULL;
}
} else {
$error_handler = set_error_handler("empty_error_handler");
$val = PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8');
if (is_a($val, 'PHPExcel_RichText')) {
$val = $val->getPlainText();
}
if ($error_handler) {
set_error_handler($error_handler);
}
}
$arr[$fields[$col]] = $val;
}
$ret = InsertRecord($arr, $row - 2);
$total_records++;
}
break;
}
}
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;
}
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;
}
function db_fetch_array($rs, $assoc = 1)
{
global $mssql_dmy;
if ($rs->EOF()) {
return false;
}
try {
$ret = array();
for ($i = 0; $i < db_numfields($rs); $i++) {
if (IsBinaryType($rs->Fields[$i]->Type) && $rs->Fields[$i]->Type != 128) {
$str = "";
if ($rs->Fields[$i]->ActualSize) {
$val = $rs->Fields[$i]->GetChunk($rs->Fields[$i]->ActualSize);
$str = str_pad("", count($val));
$j = 0;
foreach ($val as $byte) {
$str[$j++] = chr($byte);
}
}
if ($assoc) {
$ret[$rs->Fields[$i]->Name] = $str;
} else {
$ret[$i] = $str;
}
} else {
$value = $rs->Fields[$i]->Value;
if (is_null($value)) {
$val = NULL;
} else {
if (isdatefieldtype($rs->Fields[$i]->Type)) {
$value = localdatetime2db((string) $rs->Fields[$i]->Value, $mssql_dmy);
}
if (IsNumberType($rs->Fields[$i]->Type)) {
$val = floatval($value);
} else {
$val = strval($value);
}
}
if ($assoc) {
$ret[$rs->Fields[$i]->Name] = $val;
} else {
$ret[$i] = $val;
}
}
}
$rs->MoveNext();
} catch (com_exception $e) {
trigger_error($e->getMessage(), E_USER_ERROR);
}
return $ret;
}
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;
}
}
}
}
}
/**
* Get timestamp
* @param String value
* @param String dateFormat
* @return String
*/
function getTimeStamp($value, $dateFormat)
{
$refinedDateFormat = getRefinedDateFormat($dateFormat);
return strtotime(localdatetime2db($value, $refinedDateFormat));
}
/**
* 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;
}
/**
* @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 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;
}
/**
* Fetch a result row as an array
* @param Mixed qHanle The query handle
* @param Number assoc (optional)
* @return Array
*/
protected function _fetch_array($qHanle, $assoc = 1)
{
$ret = array();
if ($qHanle->EOF()) {
return false;
}
try {
for ($i = 0; $i < $this->num_fields($qHanle); $i++) {
if (IsBinaryType($qHanle->Fields[$i]->Type) && $qHanle->Fields[$i]->Type != 128 || $qHanle->Fields[$i]->Type == 203) {
$str = "";
if ($qHanle->Fields[$i]->ActualSize > 0) {
$size = $qHanle->Fields[$i]->ActualSize;
$val = $qHanle->Fields[$i]->GetChunk($size);
if (is_array($val) || is_object($val)) {
$str = str_pad("", $size);
$j = 0;
foreach ($val as $byte) {
$str[$j++] = chr($byte);
}
} else {
$str = $val;
}
}
if ($assoc) {
$ret[$qHanle->Fields[$i]->Name] = $str;
} else {
$ret[$i] = $str;
}
} else {
$value = $qHanle->Fields[$i]->Value;
if (is_null($value)) {
$val = NULL;
} else {
if (isdatefieldtype($qHanle->Fields[$i]->Type)) {
$value = localdatetime2db((string) $qHanle->Fields[$i]->Value, $this->access_dmy);
}
if (IsNumberType($qHanle->Fields[$i]->Type)) {
$val = floatval($value);
} else {
$val = strval($value);
}
}
if ($assoc) {
$ret[$qHanle->Fields[$i]->Name] = $val;
} else {
$ret[$i] = $val;
}
}
}
$qHanle->MoveNext();
} catch (com_exception $e) {
trigger_error($e->getMessage(), E_USER_ERROR);
}
return $ret;
}
function getImportExcelData($data, $fields, &$error_message, &$goodlines, $keys, $keys_present, $strOriginalTableName, $pageObject, $cipherer, $autoinc, &$total_records)
{
global $cCharset;
foreach ($data->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for ($row = 2; $row <= $highestRow; ++ $row)
{
for ($col = 0; $col < count($fields); ++ $col)
{
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell))
{
$date_format=$cell->getParent()->getParent()->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode();
$value=PHPExcel_Style_NumberFormat::ToFormattedString( $cell->getValue(),$date_format);
if(is_a($value, 'PHPExcel_RichText'))
$value = $value->getPlainText();
if($value)
{
$time=array();
if(strtotime($value))
$value=strtotime($value);
else
{
$d_format="";
for($i=0;$i<strlen($date_format);$i++)
{
$letter=substr(strtolower($date_format),$i,1);
if($letter=="d" || $letter=="m" || $letter=="y")
{
if(strpos($d_format,$letter)===false)
$d_format.=$letter;
}
}
$value=strtotime(localdatetime2db($value,$d_format));
}
// $value = PHPExcel_Shared_Date::ExcelToPHP($value);
$time=localtime($value,true);
$val=($time["tm_year"]+1900)."-".($time["tm_mon"]+1)."-".$time["tm_mday"]." ".$time["tm_hour"].":".$time["tm_min"].":".$time["tm_sec"];
}
else
$val=NULL;
}
else
{
$error_handler=set_error_handler("empty_error_handler");
$val=PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8');
if(is_a($val, 'PHPExcel_RichText'))
$val = $val->getPlainText();
if($error_handler)
set_error_handler($error_handler);
}
preg_match('/^="=(.*|n*)"$/i', $val, $matches);
if (array_key_exists(1, $matches)) {
$val = '='.$matches[1];
}
$arr[$fields[$col]]= $val;
}
$ret = InsertRecord($arr, $row-2, $error_message, $goodlines, $keys, $keys_present,
$strOriginalTableName, $pageObject, $cipherer, $autoinc);
$total_records++;
}
break;
}
}