/**
* Imports data, creating a table and inserting rows
* @param string $tablename The destination table's name
* @param array $head The column headers
* @param array $data The data to import
* @param string $mismatchtable Optional table's name to store type mismatch alerts
* @return boolean TRUE on success or FALSE on failure
*/
public function importRequest($tablename = null, $head = null, $data = null, $mismatchtable = null)
{
if ($tablename === null || $head === null || $data === null) {
// mandatory params
return false;
}
try {
$logInstance = LogTool::getInstance();
$coltype = array();
// tells int, float or varchar for each column
$nadata = array();
// stores type mismatch alerts in a second table
$nb_lines_to_check = 5;
$sql = 'CREATE TABLE IF NOT EXISTS ' . $tablename . ' (';
if ($mismatchtable) {
$sql2 = 'CREATE TABLE IF NOT EXISTS ' . $mismatchtable . ' (';
}
$nb_col_to_check = array_key_exists(0, $head) ? count($head) - 1 : count($head);
// if columns begin at #1, there's no id and we won't create one ;
// if they begin at #0, it probably means there's an id, so let's handle it
if (array_key_exists(0, $head)) {
// " && $head[0] == 'id' " unnecessary
$coltype[$head[0]] = 'int(11)';
$sql .= '`' . $head[0] . '` ' . $coltype[$head[0]] . ', ';
}
for ($i = 1; $i <= $nb_col_to_check; $i++) {
$colindex = StringTool::cleanVarName($head[$i]);
if ($colindex == SGBD_SDF_MOLSTRUCCOL) {
$coltype[$colindex] = 'text';
} else {
$nb_flt = 0;
$nb_int = 0;
$nb_str = 0;
// try the first $nb_lines_to_check lines of data
for ($j = 0; $j < $nb_lines_to_check; $j++) {
//$logInstance->logDebug("j : ".$j." - colindex : ".$colindex);
if (array_key_exists($j, $data)) {
// "false" if less than 5 lines in data
$temp = $data[$j][$colindex];
if (StringTool::isFloat($temp)) {
$nb_flt++;
} elseif (StringTool::isInt($temp)) {
$nb_int++;
} else {
$nb_str++;
}
}
}
if ($nb_flt > 0 && $nb_flt + $nb_int >= $nb_lines_to_check - 1) {
// we tolerate 1 line with wrong type
$coltype[$colindex] = 'float';
} elseif ($nb_int >= $nb_lines_to_check - 1) {
$coltype[$colindex] = 'int(11)';
} else {
$coltype[$colindex] = 'text';
}
// varchar too short sometimes
if ($mismatchtable) {
$sql2 .= '`' . $head[$i] . '` varchar(50), ';
}
// store mismatches directly in this table (not just 0/1/null)
}
$sql .= '`' . $head[$i] . '` ' . $coltype[$colindex] . ', ';
}
// the line below gets rid of the comma
$sql = substr($sql, 0, strlen($sql) - 2);
$sql .= ') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$ok = $this->createRequest($sql);
if (!$ok) {
return false;
}
// ensure it's empty
$sql = 'TRUNCATE TABLE ' . $tablename . ';';
$ok = $this->deleteRequest($sql);
if ($mismatchtable) {
$sql2 = substr($sql2, 0, strlen($sql2) - 2);
$sql2 .= ') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
$ok = $this->createRequest($sql2);
$sql2 = 'TRUNCATE TABLE ' . $mismatchtable . ';';
$ok = $this->deleteRequest($sql2);
}
// now insert data
$logInstance->setSilentMode();
$entry = array();
if ($mismatchtable) {
$entry2 = array();
}
foreach ($data as $key => $row) {
$sql = 'INSERT INTO ' . $tablename . ' VALUES (\'';
foreach ($row as $field => $value) {
if ($coltype[$field] == 'float' && !StringTool::isFloat($value) && !StringTool::isInt($value) || $coltype[$field] == 'int(11)' && !StringTool::isInt($value) && !StringTool::isFloat($value)) {
if ($mismatchtable) {
$entry2[] = $value == "" ? "NULL" : "1:" . $value;
// store mismatches directly in this table, with "1:" prefix
}
$value = "NULL";
} elseif ($value !== "" && !is_null($value)) {
if ($mismatchtable && $field != SGBD_SDF_MOLSTRUCCOL) {
$entry2[] = 0;
}
} else {
$value = "NULL";
if ($mismatchtable) {
$entry2[] = "NULL";
}
}
$entry[] = $value;
}
$sql .= implode("','", $entry);
$sql .= '\');';
$sql = str_replace('\'NULL\'', 'NULL', $sql);
$entry = array();
$ok = $this->insertRequest($sql);
if ($mismatchtable) {
$sql2 = 'INSERT INTO ' . $mismatchtable . ' VALUES (\'';
$sql2 .= implode("','", $entry2);
$sql2 .= '\');';
$sql2 = str_replace('\'NULL\'', 'NULL', $sql2);
$ok = $this->insertRequest($sql2);
$entry2 = array();
}
}
$logInstance->unsetSilentMode();
return true;
} catch (ParameterException $e) {
return false;
}
}
