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;
    }
}
Ejemplo n.º 3
0
 function add_details($data)
 {
     $reason = '';
     $data['financialperiod'] = $data['fystart__financialperiods'] . '-all';
     if (empty($data['plan_id'])) {
         $planCount = $this->_query_reader->get_count('get_procurement_plan_by_data', array('pde_id' => $data['pde_id'], 'financial_year_start' => get_quarter_date($data['financialperiod'], 'start'), 'financial_year_end' => get_quarter_date($data['financialperiod'], 'end')));
     }
     # proceed with the addition if this is a unique plan
     if (!empty($data['plan_id']) || empty($data['plan_id']) && $planCount == 0) {
         $planId = $this->_query_reader->add_data((!empty($data['plan_id']) ? 'edit' : 'add') . '_procurement_plan', array('organization_id' => $data['pde_id'], 'financial_year_start' => get_quarter_date($data['financialperiod'], 'start'), 'financial_year_end' => get_quarter_date($data['financialperiod'], 'end'), 'title' => htmlentities($data['name'], ENT_QUOTES), 'details' => '', 'document_url' => '', 'status' => $data['status__procurementplanstatus'], 'user_id' => $this->native_session->get('__user_id'), 'plan_id' => !empty($data['plan_id']) ? $data['plan_id'] : ''));
         $planId = !empty($data['plan_id']) ? $data['plan_id'] : $planId;
         # proceed with processing the procurement plan details
         if (!empty($planId)) {
             $this->native_session->set('plan_id', $planId);
             require_once HOME_URL . 'external_libraries/phpexcel/PHPExcel.php';
             $objPHPExcel = PHPExcel_IOFactory::load(UPLOAD_DIRECTORY . $data['document']);
             $sheetData = $objPHPExcel->getActiveSheet()->toArray('', true, true, true);
             $DATA_START = 9;
             $result = TRUE;
             $usefulData = array();
             # extract the useful data
             foreach ($sheetData as $i => $row) {
                 if ($i >= $DATA_START && !is_empty_row($row)) {
                     # where they can not edit
                     if (!empty($row['B']) && trim($row['B']) == 'DO NOT EDIT BELOW THIS LINE') {
                         break;
                     }
                     # put only categories with data
                     if (in_array(trim(strtolower($row['B'])), get_option_list($this, 'procurementcategories', 'array')) && !empty($sheetData[$i + 1]['D'])) {
                         array_push($usefulData, $row);
                     } else {
                         if (!empty($row['D'])) {
                             # convert back to UK format if excel importer converted the dates to US format
                             $tempRow = $row;
                             foreach ($tempRow as $key => $value) {
                                 if (strtolower($key) > 'd') {
                                     if (strpos($value, '/', 3) !== FALSE) {
                                         $value = date('m-d-y', strtotime(make_us_date($value)));
                                     }
                                     # correct dates in the format 12-31-15 as they can not be converted correctly
                                     if (strpos($value, '-', 3) !== FALSE && strlen($value) == 8) {
                                         $valueParts = explode('-', $value);
                                         if (count($valueParts) > 2) {
                                             $value = substr(@date('Y'), 0, 2) . $valueParts[2] . '-' . $valueParts[0] . '-' . $valueParts[1];
                                         }
                                     }
                                     $row[$key] = !empty($value) ? date('d/m/Y', strtotime($value)) : '';
                                 }
                             }
                             array_push($usefulData, $row);
                         }
                     }
                 }
             }
             # remove the old sheet data
             if (!empty($usefulData) && !empty($data['plan_id'])) {
                 $result = $this->_query_reader->run('remove_plan_detail_rows', array('plan_id' => $data['plan_id']));
             }
             # add the new sheet data
             foreach ($usefulData as $row) {
                 # save the rows with data
                 $rowData = array_merge($row, array('plan_id' => $planId, 'user_id' => $this->native_session->get('__user_id')));
                 $result = $this->_query_reader->run('add_plan_detail_row', $rowData);
             }
             # return with the list of added items for immediate display
             if ($result) {
                 return $this->_query_reader->get_list('get_procurement_plan_details', array('plan_id' => $planId));
             } else {
                 $reason = "The plan details could not be fully recorded.";
             }
         } else {
             $reason = "The plan headers could not be recorded.";
         }
     } else {
         $reason = "This PDE already has a plan for the same financial period. Please edit that instead.";
     }
     return $reason;
 }
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;
    }
}