import com.guiyang.education.base.Constants;
import com.guiyang.education.controller.global.GlobalAuthController;
import com.guiyang.education.controller.service.DataManagementService;
import com.guiyang.education.model.highchart.HighchartModel;
import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.jfinal.upload.UploadFile;
import com.kabao.ext.plugin.memcached.MemcachedKit;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;




   /**
     * 上传小学体测数据
     *
     * @author qyq
     */
    @Before(Tx.class)
    public void uploadSmallSchoolFile() {
        String result="";
        UploadFile uf = getFile("uploadExcel");
        File file = null;
        int erro=0;
        try {
            if (uf == null) {
                result="请选择文件！";
            } else {
                file = uf.getFile();
                String schoolYear=getPara("schoolYear");
                String city=new String(getPara("cityCode").getBytes("ISO8859-1"),"utf-8");
                String area=new String(getPara("areaCode").getBytes("ISO8859-1"),"utf-8");
                String school=new String(getPara("schoolCode").getBytes("ISO8859-1"),"utf-8");
                FileInputStream fis = new FileInputStream(file);
                HSSFWorkbook book = new HSSFWorkbook(fis);
                HSSFSheet sheet = book.getSheetAt(0);
                int rows = sheet.getLastRowNum();//总行数
                int columnNum=sheet.getRow(0).getPhysicalNumberOfCells();//总列数
                List<Record> recordList = new ArrayList<Record>();
                for (int i = 1; i <= rows; i++) {
                    erro=i+1;
                    HSSFRow row = sheet.getRow(i);// 行数
                    String idcard = row.getCell(3)==null?null:row.getCell(3).getStringCellValue();
                    int current_grade=0;
                    String name=null;
                    String ethnic_code = null;
                    BigDecimal height=null;
                    BigDecimal weight=null;
                    BigDecimal weight_scoring=null;
                    String weight_level=null;
                    BigDecimal vital_capacity=null;
                    BigDecimal vital_capacity_scoring=null;
                    String vital_capacity_level=null;
                    String fifty_sprint=null;
                    String fifty_sprint_level=null;
                    String antexed_level=null;
                    String skipping_level=null;
                    String sit_ups_level=null;
                    String fifty_goback=null;
                    String fifty_goback_level=null;
                    String jump_level=null;
                    String pull_up_level=null;
                    String km_race=null;
                    String km_level=null;
                    String eight_hundred=null;
                    String eight_hundred_level=null;
                    String total_score_level=null;
                    String sex=null;
                    String class_name=null;
                    String class_code=null;
                    BigDecimal fifty_sprint_scoring=null;
                    BigDecimal antexed=null;
                    BigDecimal antexed_score=null;
                    BigDecimal skipping_scoring=null;
                    BigDecimal sit_ups_scoring=null;
                    BigDecimal fifty_goback_scoring=null;
                    BigDecimal jump=null;
                    BigDecimal jump_scoring=null;
                    BigDecimal pull_up_scoring=null;
                    BigDecimal km_scoring=null;
                    BigDecimal eight_hundred_scoring=null;
                    BigDecimal standard_score=null;
                    BigDecimal additional=null;
                    BigDecimal total_score=null;
                    int skipping=0;
                    int sit_ups=0;
                    int pull_up=0;
                    if(StrKit.isBlank(idcard)){//姓名为空的行跳过
                        continue;
                    }else{
                        HSSFRow firstRow = sheet.getRow(0);//首行
                        for(int j=0;i<columnNum;j++) {
                            if (firstRow.getCell(j)!=null) {
                                String column = firstRow.getCell(j).getStringCellValue();
                                if (column.equals("年级编号")) {
                                    current_grade = row.getCell(j) == null ? 0 : row.getCell(j).toString().trim().equals("") ? 0 : Integer.parseInt(row.getCell(j).getStringCellValue());
                                } else if (column.equals("班级编号")) {
                                    class_code = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("班级名称")) {
                                    class_name = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("学籍号")) {
                                    idcard = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("性别")) {
                                    sex = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("民族")) {
                                    ethnic_code = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("身高")) {
                                    height = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("体重")) {
                                    weight = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("体重指数(BMI)得分")) {
                                    weight_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("体重指数(BMI)等级")) {
                                    weight_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("肺活量")) {
                                    vital_capacity = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("肺活量得分")) {
                                    vital_capacity_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("肺活量等级")) {
                                    vital_capacity_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("50米跑")) {
                                    fifty_sprint = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("50米跑得分")) {
                                    fifty_sprint_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("50米跑等级")) {
                                    fifty_sprint_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("坐位体前屈")) {
                                    antexed = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("坐位体前屈得分")) {
                                    antexed_score = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("坐位体前屈等级")) {
                                    antexed_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("一分钟跳绳")) {
                                    skipping = row.getCell(j) == null ? 0 : row.getCell(j).toString().trim().equals("") ? 0 : Integer.parseInt(row.getCell(j).getStringCellValue());
                                } else if (column.equals("一分钟跳绳得分")) {
                                    skipping_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("一分钟跳绳等级")) {
                                    skipping_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("一分钟仰卧起坐")) {
                                    sit_ups = row.getCell(j) == null ? 0 : row.getCell(j).toString().trim().equals("") ? 0 : Integer.parseInt(row.getCell(j).getStringCellValue());
                                } else if (column.equals("一分钟仰卧起坐得分")) {
                                    sit_ups_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("一分钟仰卧起坐等级")) {
                                    sit_ups_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("50米x8往返跑")) {
                                    fifty_goback = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("50米x8往返跑得分")) {
                                    fifty_goback_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("50米x8往返跑等级")) {
                                    fifty_goback_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("立定跳远")) {
                                    jump = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("立定跳远得分")) {
                                    jump_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("立定跳远等级")) {
                                    jump_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("引体向上")) {
                                    pull_up = row.getCell(j) == null ? 0 : row.getCell(j).toString().trim().equals("") ? 0 : Integer.parseInt(row.getCell(j).getStringCellValue());
                                } else if (column.equals("引体向上得分")) {
                                    pull_up_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("引体向上等级")) {
                                    pull_up_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("1000米跑")) {
                                    km_race = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("1000米跑得分")) {
                                    km_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("1000米跑等级")) {
                                    km_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("800米跑")) {
                                    eight_hundred = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("800米跑得分")) {
                                    eight_hundred_scoring = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("800米跑等级")) {
                                    eight_hundred_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                } else if (column.equals("标准总分")) {
                                    standard_score = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("附加总分")) {
                                    additional = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("总得分")) {
                                    total_score = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : new BigDecimal(row.getCell(j).getStringCellValue());
                                } else if (column.equals("总等级")) {
                                    total_score_level = row.getCell(j) == null ? null : row.getCell(j).toString().trim().equals("") ? null : row.getCell(j).getStringCellValue();
                                }
                            }else{
                                break;
                            }
                        }
                        Record record = Db.findFirst("select * from s_report_province_" + schoolYear + " where idcard=? or roll_code=?", idcard,idcard);
                        if (record != null) {
                            recordList.add(new Record()
                                            .set("id", record.getInt("id"))
                                            .set("name", name)
                                            .set("current_grade", current_grade)
                                            .set("class_code", class_code)
                                            .set("class_name", class_name)
                                            .set("sex", sex == "男" ? 1 : 0)
                                            .set("ethnic_code", ethnic_code)
                                            .set("height", height)
                                            .set("weight", weight)
                                            .set("weight_scoring", weight_scoring)
                                            .set("weight_level", weight_level)
                                            .set("skipping", skipping == 0 ? null : skipping)
                                            .set("skipping_scoring", skipping_scoring)
                                            .set("skipping_level", skipping_level)
                                            .set("vital_capacity", vital_capacity)
                                            .set("vital_capacity_scoring", vital_capacity_scoring)
                                            .set("vital_capacity_level", vital_capacity_level)
                                            .set("fifty_sprint", fifty_sprint)
                                            .set("fifty_sprint_scoring", fifty_sprint_scoring)
                                            .set("fifty_sprint_level", fifty_sprint_level)
                                            .set("fifty_goback", fifty_goback)
                                            .set("fifty_goback_scoring", fifty_goback_scoring)
                                            .set("fifty_goback_level", fifty_goback_level)
                                            .set("jump", jump)
                                            .set("jump_scoring", jump_scoring)
                                            .set("jump_level", jump_level)
                                            .set("antexed", antexed)
                                            .set("antexed_score", antexed_score)
                                            .set("antexed_level", antexed_level)
                                            .set("eight_hundred", eight_hundred)
                                            .set("eight_hundred_scoring", eight_hundred_scoring)
                                            .set("eight_hundred_level", eight_hundred_level)
                                            .set("km_race", km_race)
                                            .set("km_scoring", km_scoring)
                                            .set("km_level", km_level)
                                            .set("sit_ups", sit_ups == 0 ? null : sit_ups)
                                            .set("sit_ups_scoring", sit_ups_scoring)
                                            .set("sit_ups_level", sit_ups_level)
                                            .set("pull_up", pull_up == 0 ? null : pull_up)
                                            .set("pull_up_scoring", pull_up_scoring)
                                            .set("pull_up_level", pull_up_level)
                                            .set("standard_score", standard_score)
                                            .set("additional", additional)
                                            .set("total_score", total_score)
                                            .set("total_score_level", total_score_level)
                            );
                        }
                        result = "导入成功！";
                    }
                }
                Db.batchUpdate("s_report_province_"+schoolYear, recordList, Constants.BATCH_NUM);
                recordList = null;
            }
        }catch (IllegalStateException e) {
            System.out.println("错误行数erro=================================》"+erro);
            e.printStackTrace();
            result="第"+erro+"行单元格格式有误，请重新设置后再导入！";
        } catch (Exception e) {
            System.out.println("错误行数erro=================================》"+erro);
            e.printStackTrace();
            result="第"+erro+"行某列信息有误，请根据模版要求填写后再导入！";
        }finally {
            file.delete();//导入成功后删除文件
        }
        setAttr("result", result);
        renderJson();
    }