function read_excel_and_insert_into_database($target_file, $userid)
{
// return {"status":, error:[{"line":"1", "message":"xxx error"},{"line":"", "message":""}, ...]}
$users = array();
global $file_status;
// load file
try {
$input_file_type = PHPExcel_IOFactory::identify($target_file);
$reader = PHPExcel_IOFactory::createReader($input_file_type);
$excel = $reader->load($target_file);
} catch (Exception $e) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => 0, "lines" => 0, "message" => $e->getMessage()));
return $file_status->status;
}
// parse file
$sheet_count = $excel->getSheetCount();
for ($cur_sheet = 0; $cur_sheet < $sheet_count; $cur_sheet++) {
$sheet = $excel->getSheet($cur_sheet);
$sheet_title = $sheet->getTitle();
//print_r($sheet_title);
if ($sheet_title == "上传名单说明") {
continue;
}
// if sheet name is xxxx, skip it
$highest_row = $sheet->getHighestRow();
$highest_col = count($file_status->upload_user_syntax);
$tmp = array();
for ($col = 0; $col <= $highest_col; $col++) {
array_push($tmp, trim($sheet->getCellByColumnAndRow($col, 1)->getValue()));
}
if (!is_valid_syntax_import_file($tmp)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => 0, "message" => MSG_ERR_FILE_CONTENT_SYNTAX));
return $file_status->status;
}
for ($row = 2; $row <= $highest_row; $row++) {
$tmp = array();
$functions = array();
for ($col = 0; $col <= $highest_col; $col++) {
array_push($tmp, trim($sheet->getCellByColumnAndRow($col, $row)->getValue()));
}
if (is_empty_row($tmp)) {
continue;
}
$cur_user = new UploadUser($tmp);
if (!is_correct_user_eid_format($cur_user->EmployeeId)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_USER_EID_FORMAT));
}
if (!is_correct_user_name_format($cur_user->UserName)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_USER_NAME_FORMAT));
}
if (!is_correct_user_email_format($cur_user->Email)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_USER_EMAIL_FORMAT));
}
if (!is_correct_user_dept_format($cur_user->DeptCode)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_USER_DEPT_FORMAT));
}
$detp_id = get_dept_id_from_database($cur_user->DeptCode);
if ($detp_id == ERR_USER_DEPT_NOT_EXIST) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => "{$product_name} 不存在"));
} else {
$cur_user->DeptId = $detp_id;
}
$canapprove = get_canapprove_from_tf($cur_user->CanApprovestr);
$cur_user->CanApprove = $canapprove;
if (is_user_exist($cur_user)) {
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "UserName" => $cur_user->UserName, "EmployeeId" => $cur_user->EmployeeId, "message" => "此用户系统中已存在！"));
} else {
array_push($users, $cur_user);
}
}
}
if ($file_status->status == UPLOAD_SUCCESS) {
return write_into_database($users, $userid);
} else {
return $file_status->status;
}
}
function read_excel_and_insert_into_database($target_file)
{
// return {"status":, error:[{"line":"1", "message":"xxx error"},{"line":"", "message":""}, ...]}
$problems = array();
global $file_status;
// load file
try {
$input_file_type = PHPExcel_IOFactory::identify($target_file);
$reader = PHPExcel_IOFactory::createReader($input_file_type);
$excel = $reader->load($target_file);
} catch (Exception $e) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => 0, "lines" => 0, "message" => $e->getMessage()));
return $file_status->status;
}
// parse file
$sheet_count = $excel->getSheetCount();
for ($cur_sheet = 0; $cur_sheet < $sheet_count; $cur_sheet++) {
$sheet = $excel->getSheet($cur_sheet);
$highest_row = $sheet->getHighestRow();
$highest_col = count($file_status->upload_problem_syntax);
$tmp = array();
for ($col = 0; $col <= $highest_col; $col++) {
array_push($tmp, trim($sheet->getCellByColumnAndRow($col, 1)->getValue()));
}
if (!is_valid_syntax_import_file($tmp)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => 0, "message" => MSG_ERR_FILE_CONTENT_SYNTAX));
return $file_status->status;
}
for ($row = 2; $row <= $highest_row; $row++) {
$tmp = array();
$functions = array();
for ($col = 0; $col <= $highest_col; $col++) {
array_push($tmp, trim($sheet->getCellByColumnAndRow($col, $row)->getValue()));
}
if (is_empty_row($tmp)) {
continue;
}
$cur_problem = new UploadProblem($tmp);
if (!is_correct_prob_type_format($cur_problem->type)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_PROB_TYPE_FORMAT));
}
if (!is_correct_prob_desc_format($cur_problem->desc)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_PROB_DESC_FORMAT));
}
if (!is_correct_prob_level_format($cur_problem->level)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_PROB_LEVEL_FORMAT));
}
if (!is_correct_prob_answer_format($cur_problem->answer, $cur_problem->selections, $cur_problem->type)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_PROB_ANSWER_FORMAT));
}
if (!is_correct_prob_selection_format($cur_problem->selections, $cur_problem->type)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_PROB_SELECTOR_FORMAT));
}
foreach ($cur_problem->category_product as $product_name) {
$func_id = get_function_id_from_database($product_name);
if ($func_id == ERR_PROB_FUNC_NOT_EXIST) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => "{$product_name} 不存在"));
} else {
array_push($functions, $func_id);
}
}
foreach ($cur_problem->category_adaptaion as $adaptation_name) {
$func_id = get_function_id_from_database($adaptation_name);
if ($func_id == ERR_PROB_FUNC_NOT_EXIST) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => "{$adaptation_name} 不存在"));
} else {
array_push($functions, $func_id);
}
}
foreach ($cur_problem->problem_category as $category_name) {
$func_id = get_function_id_from_database($category_name);
if ($func_id == ERR_PROB_FUNC_NOT_EXIST) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => "{$category_name} 不存在"));
} else {
array_push($functions, $func_id);
}
}
if (is_no_any_functions($functions)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => "至少需要有一个分类"));
}
$cur_problem->functions_str = output_category_str_from_func_array($functions);
array_push($problems, $cur_problem);
}
}
if ($file_status->status == UPLOAD_SUCCESS) {
return write_into_database($problems);
} else {
return $file_status->status;
}
}
$id = $_SESSION['id'];
/*========== Einschränkungen ==========*/
if (empty($_FILES['file'])) {
echo "Error: " . $_FILES["file"]["error"] . "<br>";
} else {
$filename = $_FILES['file']['name'];
$tmp = $_FILES['file']['tmp_name'];
/**
TODO: Einbau von Funktion zur Überprüfung auf Tags explode name -> for each word -> SELECT tags where word -> if 1 -> Vorschlag
**/
$type = $_FILES['file']['type'];
$size = $_FILES['file']['size'];
if (!file_exists("C:\\xampp\\htdocs\\IBBExtender\\data\\" . $username . "\\" . $filename)) {
move_uploaded_file($_FILES['file']['tmp_name'], "C:\\xampp\\htdocs\\IBBExtender\\data\\" . $username . "\\" . $filename);
chmod("C:\\xampp\\htdocs\\IBBExtender\\data\\" . $username . "\\" . $filename, 0777);
write_into_database($filename, $id, $type, $size);
} else {
unlink($tmp);
echo "File wurde nicht verschoben weil es bereits existiert";
}
}
/*----- End of Upload Skript ------*/
/**
* [write_into_database description]
* Schreibt die Werte in die Datenbank
* TODO:Auslagern und per include
* @return [type] [description]
*/
function write_into_database($filename, $id, $type, $size)
{
mysql_connect("localhost", "admin", "admin") or die("Datenbank Verbindung konnte nicht hergestellt werden");
function read_excel_and_insert_into_database($target_file, $qtid)
{
// return {"status":, error:[{"line":"1", "message":"xxx error"},{"line":"", "message":""}, ...]}
$qts = array();
global $file_status;
// load file
try {
$input_file_type = PHPExcel_IOFactory::identify($target_file);
$reader = PHPExcel_IOFactory::createReader($input_file_type);
$excel = $reader->load($target_file);
} catch (Exception $e) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => 0, "lines" => 0, "message" => $e->getMessage()));
return $file_status->status;
}
// parse file
$sheet_count = $excel->getSheetCount();
for ($cur_sheet = 0; $cur_sheet < $sheet_count; $cur_sheet++) {
$sheet = $excel->getSheet($cur_sheet);
$sheet_title = $sheet->getTitle();
// print_r($sheet_title);
if ($sheet_title == "上传题库说明") {
continue;
}
// if sheet name is xxxx, skip it
$highest_row = $sheet->getHighestRow();
$highest_col = count($file_status->upload_qd_syntax);
$tmp = array();
for ($col = 0; $col <= $highest_col; $col++) {
array_push($tmp, trim($sheet->getCellByColumnAndRow($col, 1)->getValue()));
}
if (!is_valid_syntax_import_file($tmp)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => 0, "message" => MSG_ERR_FILE_CONTENT_SYNTAX));
$resultStr = MSG_ERR_FILE_CONTENT_SYNTAX;
return $file_status->status;
}
for ($row = 2; $row <= $highest_row; $row++) {
$tmp = array();
$functions = array();
for ($col = 0; $col <= $highest_col; $col++) {
array_push($tmp, trim($sheet->getCellByColumnAndRow($col, $row)->getValue()));
}
if (is_empty_row($tmp)) {
continue;
}
//$qtid = 1;
$cur_qt = new UploadQT($tmp, $qtid);
// echo "<br />";
// print_r($cur_qt);
if (!is_correct_qt_type_format($cur_qt->type)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_QT_TYPE_FORMAT));
$resultStr = MSG_ERR_QT_TYPE_FORMAT;
}
if (!is_correct_qt_desc_format($cur_qt->desc)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_QT_DESC_FORMAT));
$resultStr = MSG_ERR_QT_DESC_FORMAT;
}
// if (!is_correct_prob_level_format($cur_problem->level))
// {
// $file_status->status = ERR_FILE_LOAD;
// array_push($file_status->errors, array("sheet"=>$cur_sheet, "lines"=>$row, "message"=>MSG_ERR_PROB_LEVEL_FORMAT));
// }
// if (!is_correct_prob_answer_format($cur_problem->answer, $cur_problem->selections, $cur_problem->type))
// {
// $file_status->status = ERR_FILE_LOAD;
// array_push($file_status->errors, array("sheet"=>$cur_sheet, "lines"=>$row, "message"=>MSG_ERR_PROB_ANSWER_FORMAT));
// }
if (!is_correct_qt_selection_format($cur_qt->selections, $cur_qt->type)) {
$file_status->status = ERR_FILE_LOAD;
array_push($file_status->errors, array("sheet" => $cur_sheet, "lines" => $row, "message" => MSG_ERR_QT_SELECTOR_FORMAT));
$resultStr = MSG_ERR_QT_SELECTOR_FORMAT;
}
// if this sheet is OBL, insert OBL function name to $cur_problem->category_product
// if ($sheet_title == "OBL")
// {
// print_r("OBL");
// array_push($cur_problem->category_product, "OBL");
// }
// foreach ($cur_problem->category_product as $product_name)
// {
// $func_id = get_function_id_from_database($product_name);
// if ($func_id == ERR_PROB_FUNC_NOT_EXIST)
// {
// $file_status->status = ERR_FILE_LOAD;
// array_push($file_status->errors, array("sheet"=>$cur_sheet, "lines"=>$row, "message"=> "$product_name 不存在"));
// }
// else
// {
// array_push($functions, $func_id);
// }
// }
// foreach ($cur_problem->category_adaptaion as $adaptation_name)
// {
// $func_id = get_function_id_from_database($adaptation_name);
// if ($func_id == ERR_PROB_FUNC_NOT_EXIST)
// {
// $file_status->status = ERR_FILE_LOAD;
// array_push($file_status->errors, array("sheet"=>$cur_sheet, "lines"=>$row, "message"=> "$adaptation_name 不存在"));
// }
// else
// {
// array_push($functions, $func_id);
// }
// }
// foreach ($cur_problem->problem_category as $category_name)
// {
// $func_id = get_function_id_from_database($category_name);
// if ($func_id == ERR_PROB_FUNC_NOT_EXIST)
// {
// $file_status->status = ERR_FILE_LOAD;
// array_push($file_status->errors, array("sheet"=>$cur_sheet, "lines"=>$row, "message"=> "$category_name 不存在"));
// }
// else
// {
// array_push($functions, $func_id);
// }
// }
//
// if (is_no_any_functions($functions))
// {
// $file_status->status = ERR_FILE_LOAD;
// array_push($file_status->errors, array("sheet"=>$cur_sheet, "lines"=>$row, "message"=> "至少需要有一个分类"));
// }
// $cur_problem->functions_str = output_category_str_from_func_array($functions);
array_push($qts, $cur_qt);
}
}
if ($file_status->status == UPLOAD_SUCCESS) {
return write_into_database($qts);
} else {
return $file_status->status;
}
}
