Esempio n. 1
0
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;
}
Esempio n. 3
0
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;
}
Esempio n. 5
0
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));
}
Esempio n. 7
0
 /**
  * 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;	
} 
Esempio n. 10
0
 /**	
  * 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;
	}
}