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);
}
}
