package cn.com.qmth.stmms.ms.admin.exporter;

import cn.com.qmth.stmms.ms.admin.dto.ExportLevelResultDTO;
import cn.com.qmth.stmms.ms.admin.dto.HeaderNode;
import cn.com.qmth.stmms.ms.admin.dto.ScoreDTO;
import cn.com.qmth.stmms.ms.commons.config.ImageConfig;
import cn.com.qmth.stmms.ms.commons.utils.SqlUtil;
import cn.com.qmth.stmms.ms.commons.utils.excel.ExportUtils;
import cn.com.qmth.stmms.ms.core.domain.MarkSubject;
import cn.com.qmth.stmms.ms.core.domain.Paper;
import cn.com.qmth.stmms.ms.core.domain.Student;
import cn.com.qmth.stmms.ms.core.domain.enums.TrialEnum;
import cn.com.qmth.stmms.ms.core.repository.ExamQuestionRepo;
import cn.com.qmth.stmms.ms.core.repository.MarkSubjectRepo;
import cn.com.qmth.stmms.ms.core.repository.PaperRepo;
import cn.com.qmth.stmms.ms.core.repository.StudentRepo;
import cn.com.qmth.stmms.ms.core.vo.Subject;
import com.google.gson.Gson;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Created by zhengmin on 2017/1/18.
 */
@RestController
@RequestMapping("api/export/score")
public class ScoreExporter {
    private static final org.slf4j.Logger LOGGER = LoggerFactory.getLogger(ScoreExporter.class);

    @Autowired
    private PaperRepo paperRepo;

    @Autowired
    private ExamQuestionRepo examQuestionRepo;

    @Autowired
    private StudentRepo studentRepo;

    @Autowired
    SqlUtil sqlUtil;

    private int level = 4,//动态报表开始列固定从4开始
            score = 6,//打分报表固定从6开始
            levelInterval = 2,//分档列固定从2开始
            scoreInterval = 1;//分数列固定从1开始

    private final String LEADER = "科组长定档",
            AVG_SOCRE = "最终成绩",
            FINAL = "最终档位",
            UPLOAD = "是否上传",
            MISSING = "是否缺考",
            SAMPLE = "是否标准卷",
            ONE_CLICK = "是否一键定档",
            SHIFT = "是否改档",
            TEACHER = "阅卷员",
            EXAM_NUMBER = "准考证号",
            NAME = "姓名",
            AREA_NAME = "考区",
            SCHOOL = "学校",
            EXAM_ROOM = "考场",
            SOURCE_NAME = "生源地",
            SC_NAME = "色彩",
            SM_NAME = "素描",
            SX_NAME = "速写",
            LEVEL_FILE_NAME = "档位成绩表",
            SCORE_FILE_NAME = "分数成绩表";

    @Resource
    MarkSubjectRepo markSubjectRepo;

    @Resource
    ImageConfig imageConfig;

    //@RequestMapping(method = RequestMethod.GET)
    public void export(@RequestParam Long workId, HttpServletResponse response) {
        List<ScoreDTO> scoreDTOs = new ArrayList<>();
        List<Object[]> objects = paperRepo.findScores(workId);
        objects.forEach(o -> {
            Long questionId = Long.valueOf(o[2].toString());
            String sourceName = o[3].toString();
            String areaName = examQuestionRepo.findOne(questionId).getAreaName();
            ScoreDTO scoreDTO = new ScoreDTO();
            scoreDTO.setExamNumber(o[0].toString());
            scoreDTO.setStudentName(o[1].toString());
            String scoreList = String.valueOf(o[4]);
            if (scoreList != null && scoreList.split(",").length == 3) {
                scoreDTO.setSc(scoreList.split(",")[0]);
                scoreDTO.setSm(scoreList.split(",")[1]);
                scoreDTO.setSx(scoreList.split(",")[2]);
            }
            scoreDTO.setAreaName(areaName);
            scoreDTO.setSourceName(sourceName);
            scoreDTOs.add(scoreDTO);
        });
        String fileName = "成绩总表";
        ExportUtils.exportEXCEL(fileName, ScoreDTO.class, scoreDTOs, response);
    }

    @RequestMapping(method = RequestMethod.GET)
    public void export2(@RequestParam Long workId, HttpServletResponse response) {
        List<ScoreDTO> scoreDTOs = new ArrayList<>();
        List<Student> students = studentRepo.findByWorkIdAndTest(workId, String.valueOf(TrialEnum.DEFAULT.getId()));
        for (Student student : students) {
            ScoreDTO scoreDTO = new ScoreDTO();
            scoreDTO.setSourceName(student.getSourceName());
            scoreDTO.setAreaName(student.getAreaName());
            scoreDTO.setExamNumber(student.getExamNumber());
            scoreDTO.setStudentName(student.getName());

            Paper sc = paperRepo.findByWorkIdAndSubjectAndExamNumber(workId, Subject.SC, student.getExamNumber());
            Paper sm = paperRepo.findByWorkIdAndSubjectAndExamNumber(workId, Subject.SM, student.getExamNumber());
            Paper sx = paperRepo.findByWorkIdAndSubjectAndExamNumber(workId, Subject.SX, student.getExamNumber());

            int scScore = (sc == null || !NumberUtils.isNumber(String.valueOf(sc.getScore()))) ? 0 : sc.getScore().intValue();
            int smScore = (sm == null || !NumberUtils.isNumber(String.valueOf(sm.getScore()))) ? 0 : sm.getScore().intValue();
            int sxScore = (sx == null || !NumberUtils.isNumber(String.valueOf(sx.getScore()))) ? 0 : sx.getScore().intValue();
            scoreDTO.setSc(String.valueOf(scScore));
            scoreDTO.setSm(String.valueOf(smScore));
            scoreDTO.setSx(String.valueOf(sxScore));
            scoreDTOs.add(scoreDTO);
        }
        String fileName = "成绩总表";
        ExportUtils.exportEXCEL(fileName, ScoreDTO.class, scoreDTOs, response);
    }

    /**
     * 评卷元动态绘制
     *
     * @param type
     * @param map
     * @param sheet
     * @param style
     * @param row
     * @param index
     * @return
     */
    public void drawExcelTeacher(int type, Map<String, ExportLevelResultDTO> map, Sheet sheet, CellStyle style, Row row, AtomicInteger index) {
        if (Objects.nonNull(map) && map.size() > 0) {
            map.forEach((k, v) -> {
                drawExcelFixedHead(sheet, style, row, new HeaderNode(v.getTeacherName() + TEACHER, 1, 1, index.get(), index.get()));
                v.setFirstCol(index.get());
                index.getAndIncrement();
            });
        }
        if (type == 2) {//type为2：分档报表
            //最终档位
            drawExcelFixedHead(sheet, style, row, new HeaderNode(FINAL, 1, 1, index.get(), index.get()));
            //是否上传
            index.getAndIncrement();
            drawExcelFixedHead(sheet, style, row, new HeaderNode(UPLOAD, 1, 1, index.get(), index.get()));
            //是否缺考（空白卷）
            index.getAndIncrement();
            drawExcelFixedHead(sheet, style, row, new HeaderNode(MISSING, 1, 1, index.get(), index.get()));
            //标准卷
            index.getAndIncrement();
            drawExcelFixedHead(sheet, style, row, new HeaderNode(SAMPLE, 1, 1, index.get(), index.get()));
            //一键定档
            index.getAndIncrement();
            drawExcelFixedHead(sheet, style, row, new HeaderNode(ONE_CLICK, 1, 1, index.get(), index.get()));
            //改档
            index.getAndIncrement();
            drawExcelFixedHead(sheet, style, row, new HeaderNode(SHIFT, 1, 1, index.get(), index.get()));
        } else if (type == 1) {//type为1：分数报表
            //最终成绩
            drawExcelFixedHead(sheet, style, row, new HeaderNode(AVG_SOCRE, 1, 1, index.get(), index.get()));
        }
    }

    /**
     * 绘制excel列
     *
     * @param sheet
     * @param style
     * @param row
     * @return
     */
    public Sheet drawExcelFixedHead(Sheet sheet, CellStyle style, Row row, HeaderNode headerNode) {
        Cell cell = row.createCell(headerNode.getFirstCol());
        cell.setCellValue(headerNode.getName());
        cell.setCellStyle(style);
//        CellRangeAddress region = new CellRangeAddress(headerNode.getFirstRow(), headerNode.getLastRow(), (short) headerNode.getFirstCol(), (short) headerNode.getLastCol());
//        sheet.addMergedRegionUnsafe(region);
        return sheet;
    }

    /**
     * 绘制固定表头
     *
     * @param type
     * @param sheet
     * @param style
     * @param row
     * @param workId
     * @param subjectMap
     */
    public Sheet drawExcelFixedHeadLevel(int type, Sheet sheet, CellStyle style, Row row, Map<String, Map<String, ExportLevelResultDTO>> subjectMap, Long workId) {
        //准考证号
        Cell cell = row.createCell(0);
        cell.setCellValue(EXAM_NUMBER);
        cell.setCellStyle(style);
        CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 0);
        sheet.addMergedRegion(region1);
        sheet.setColumnWidth(0, 15 * 256);
        //姓名
        Cell cell1 = row.createCell(1);
        cell1.setCellValue(NAME);
        cell1.setCellStyle(style);
        CellRangeAddress region2 = new CellRangeAddress(0, 1, (short) 1, (short) 1);
        sheet.addMergedRegion(region2);
        //考区
        Cell cell2 = row.createCell(2);
        cell2.setCellValue(AREA_NAME);
        cell2.setCellStyle(style);
        CellRangeAddress region3 = new CellRangeAddress(0, 1, (short) 2, (short) 2);
        sheet.addMergedRegion(region3);
        sheet.setColumnWidth(2, 30 * 256);
        //生源地
        Cell cell3 = row.createCell(3);
        cell3.setCellValue(SOURCE_NAME);
        cell3.setCellStyle(style);
        CellRangeAddress region4 = new CellRangeAddress(0, 1, (short) 3, (short) 3);
        sheet.addMergedRegion(region4);
        if (!imageConfig.isCustomSubject()) {
            //色彩
            int size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.SC.toString()))) {
                size = subjectMap.get(Subject.SC.toString()).size();
            }
            int scSumCount  = level + size + 5;
            Cell cell4 = row.createCell(level);
            cell4.setCellValue(SC_NAME);
            cell4.setCellStyle(style);
            if (level != scSumCount) {
                CellRangeAddress region5 = new CellRangeAddress(0, 0, (short) level, scSumCount);
                sheet.addMergedRegion(region5);
            }
            LOGGER.info("level:{},scSumCount:{}", level, scSumCount);

            //素描
            scSumCount = scSumCount + 1;//起始列+1
            size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.SM.toString()))) {
                size = subjectMap.get(Subject.SM.toString()).size();
            }
            int smSumCount = scSumCount + size + 5;//最终列+1，因为从0开始
            Cell cell7 = row.createCell(scSumCount);
            cell7.setCellValue(SM_NAME);
            cell7.setCellStyle(style);
            if (scSumCount != smSumCount) {
                CellRangeAddress region6 = new CellRangeAddress(0, 0, (short) scSumCount, smSumCount);
                sheet.addMergedRegion(region6);
            }
            LOGGER.info("scSumCount:{},smSumCount:{}", scSumCount, smSumCount);
            //速写
            smSumCount = smSumCount + 1;//起始列+1
            size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.SX.toString()))) {
                size = subjectMap.get(Subject.SX.toString()).size();
            }
            int sxSumCount = smSumCount + size + 5;//最终列+1，因为从0开始
            Cell cell8 = row.createCell(smSumCount);
            cell8.setCellValue(SX_NAME);
            cell8.setCellStyle(style);
            if (smSumCount != sxSumCount) {
                CellRangeAddress region9 = new CellRangeAddress(0, 0, (short) smSumCount, sxSumCount);
                sheet.addMergedRegion(region9);
            }
            LOGGER.info("smSumCount:{},sxSumCount:{}", smSumCount, sxSumCount);
        } else {
            //自定义
            int size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.CUSTOM.toString()))) {
                size = subjectMap.get(Subject.CUSTOM.toString()).size();
            }
            int customSumCount = 0;
            if (type == 2) {
                customSumCount = score + size + 1;
            } else {
                customSumCount = score + size;
            }
            MarkSubject markSubject = markSubjectRepo.findOne(workId + "-" + Subject.CUSTOM.name());
            Cell cell6 = row.createCell(score);
            cell6.setCellValue(markSubject.getName());
            cell6.setCellStyle(style);
            if (score != customSumCount) {
                CellRangeAddress region7 = new CellRangeAddress(0, 0, (short) score, customSumCount);
                sheet.addMergedRegion(region7);
            }
            LOGGER.info("level:{},customSumCount:{}", score, customSumCount);
        }
        return sheet;
    }

    /**
     * 绘制固定表头
     *
     * @param type
     * @param sheet
     * @param style
     * @param row
     * @param workId
     * @param subjectMap
     */
    public Sheet drawExcelFixedHead(int type, Sheet sheet, CellStyle style, Row row, Map<String, Map<String, ExportLevelResultDTO>> subjectMap, Long workId) {
        //准考证号
        Cell cell = row.createCell(0);
        cell.setCellValue(EXAM_NUMBER);
        cell.setCellStyle(style);
        CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 0);
        sheet.addMergedRegion(region1);
        sheet.setColumnWidth(0, 15 * 256);
        //姓名
        Cell cell1 = row.createCell(1);
        cell1.setCellValue(NAME);
        cell1.setCellStyle(style);
        CellRangeAddress region2 = new CellRangeAddress(0, 1, (short) 1, (short) 1);
        sheet.addMergedRegion(region2);
        //考区
        Cell cell2 = row.createCell(2);
        cell2.setCellValue(AREA_NAME);
        cell2.setCellStyle(style);
        CellRangeAddress region3 = new CellRangeAddress(0, 1, (short) 2, (short) 2);
        sheet.addMergedRegion(region3);
        sheet.setColumnWidth(2, 30 * 256);
        //学校
        Cell cell3 = row.createCell(3);
        cell3.setCellValue(SCHOOL);
        cell3.setCellStyle(style);
        CellRangeAddress region4 = new CellRangeAddress(0, 1, (short) 3, (short) 3);
        sheet.addMergedRegion(region4);
        sheet.setColumnWidth(3, 30 * 256);
        //考场
        Cell cell4 = row.createCell(4);
        cell4.setCellValue(EXAM_ROOM);
        cell4.setCellStyle(style);
        CellRangeAddress region5 = new CellRangeAddress(0, 1, (short) 4, (short) 4);
        sheet.addMergedRegion(region5);
        sheet.setColumnWidth(4, 30 * 256);
        //生源地
        Cell cell5 = row.createCell(5);
        cell5.setCellValue(SOURCE_NAME);
        cell5.setCellStyle(style);
        CellRangeAddress region6 = new CellRangeAddress(0, 1, (short) 5, (short) 5);
        sheet.addMergedRegion(region6);
        if (!imageConfig.isCustomSubject()) {
            //色彩
            int size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.SC.toString()))) {
                size = subjectMap.get(Subject.SC.toString()).size();
            }
            int scSumCount = 0;
            if (type == 2) {
                scSumCount = score + size + 7;
            } else {
                scSumCount = score + size;
            }
            Cell cell6 = row.createCell(score);
            cell6.setCellValue(SC_NAME);
            cell6.setCellStyle(style);
            if (score != scSumCount) {
                CellRangeAddress region7 = new CellRangeAddress(0, 0, (short) score, scSumCount);
                sheet.addMergedRegion(region7);
            }
            LOGGER.info("level:{},scSumCount:{}", score, scSumCount);

            //素描
            scSumCount = scSumCount + 1;//起始列+1
            size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.SM.toString()))) {
                size = subjectMap.get(Subject.SM.toString()).size();
            }
            int smSumCount = 0;
            if (type == 2) {
                smSumCount = scSumCount + size + 7;//最终列+1，因为从0开始
            } else {
                smSumCount = scSumCount + size;
            }
            Cell cell7 = row.createCell(scSumCount);
            cell7.setCellValue(SM_NAME);
            cell7.setCellStyle(style);
            if (scSumCount != smSumCount) {
                CellRangeAddress region8 = new CellRangeAddress(0, 0, (short) scSumCount, smSumCount);
                sheet.addMergedRegion(region8);
            }
            LOGGER.info("scSumCount:{},smSumCount:{}", scSumCount, smSumCount);
            //速写
            smSumCount = smSumCount + 1;//起始列+1
            size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.SX.toString()))) {
                size = subjectMap.get(Subject.SX.toString()).size();
            }
            int sxSumCount = 0;
            if (type == 2) {
                sxSumCount = smSumCount + size + 7;//最终列+1，因为从0开始
            } else {
                sxSumCount = smSumCount + size;
            }
            Cell cell8 = row.createCell(smSumCount);
            cell8.setCellValue(SX_NAME);
            cell8.setCellStyle(style);
            if (smSumCount != sxSumCount) {
                CellRangeAddress region9 = new CellRangeAddress(0, 0, (short) smSumCount, sxSumCount);
                sheet.addMergedRegion(region9);
            }
            LOGGER.info("smSumCount:{},sxSumCount:{}", smSumCount, sxSumCount);
        } else {
            //自定义
            int size = 0;
            if (Objects.nonNull(subjectMap.get(Subject.CUSTOM.toString()))) {
                size = subjectMap.get(Subject.CUSTOM.toString()).size();
            }
            int customSumCount = 0;
            if (type == 2) {
                customSumCount = score + size + 1;
            } else {
                customSumCount = score + size;
            }
            MarkSubject markSubject = markSubjectRepo.findOne(workId + "-" + Subject.CUSTOM.name());
            Cell cell6 = row.createCell(score);
            cell6.setCellValue(markSubject.getName());
            cell6.setCellStyle(style);
            if (score != customSumCount) {
                CellRangeAddress region7 = new CellRangeAddress(0, 0, (short) score, customSumCount);
                sheet.addMergedRegion(region7);
            }
            LOGGER.info("level:{},customSumCount:{}", score, customSumCount);
        }
        return sheet;
    }

    /**
     * 导出档位成绩
     *
     * @param workId
     * @param response
     */
    @RequestMapping(value = "exportLevelResult", method = RequestMethod.GET)
    public void exportLevelResult(@RequestParam Long workId, HttpServletResponse response) {
        //测试-导出档位成绩表
        StringBuilder sbAll = null;
        if (!imageConfig.isCustomSubject()) {
            String scSql = "select temp.*, mt.`result`, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level`, p.id upload, p.is_missing isMissing, p.is_sample isSample,p.is_one_click isOneClick, cl.id isShift from student t left join paper p on t.work_id = p.work_id and t.exam_number = p.exam_number left join mark_task AS mt on p.work_id = mt.work_id and p.id = mt.paper_id left join (SELECT mu.id, mu.name AS teacherName, mu.`role`, mu.subject, mu.work_id AS workId FROM mark_user AS mu WHERE mu.work_id = ? AND mu.`role` = 'MARKER' AND (mu.mark_right = 0 OR mu.mark_right = 2) AND mu.subject = 'SC' AND mu.enabled = 1) temp on mt.work_id = temp.workId and mt.marker_id = temp.id left join  (SELECT id, work_id, paper_id FROM change_level WHERE work_id = ? AND subject = 'SC') cl ON p.work_id = cl.work_id and p.id = cl.paper_id WHERE mt.work_id = ? AND mt.stage = 1 AND p.is_missing = FALSE UNION ALL select temp.*, null as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level`, p.id upload, p.is_missing missing, p.is_sample isSample,p.is_one_click isOneClick, cl.id isShift from student t LEFT JOIN paper p ON t.work_id = p.work_id AND t.exam_number = p.exam_number LEFT JOIN (SELECT  id, work_id, paper_id FROM change_level WHERE work_id = ? AND subject = 'SC') cl ON p.work_id = cl.work_id AND p.id = cl.paper_id LEFT JOIN (SELECT mu.id,mu.name AS teacherName,mu.`role`,mu.subject, mu.work_id AS workId FROM  mark_user AS mu WHERE mu.work_id = ? AND mu.`role` = 'MARKER' AND (mu.mark_right = 0 OR mu.mark_right = 2) AND mu.subject = 'SC' AND mu.enabled = 1) temp ON 1 = 1 WHERE (p.is_missing = TRUE OR p.is_test = 3) AND p.work_id = ? AND p.subject = 'SC' ";
            String smSql = "select temp.*, mt.`result`, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level`, p.id upload, p.is_missing isMissing, p.is_sample isSample,p.is_one_click isOneClick, cl.id isShift from student t left join paper p on t.work_id = p.work_id and t.exam_number = p.exam_number left join mark_task AS mt on p.work_id = mt.work_id and p.id = mt.paper_id left join (SELECT mu.id, mu.name AS teacherName, mu.`role`, mu.subject, mu.work_id AS workId FROM mark_user AS mu WHERE mu.work_id = ? AND mu.`role` = 'MARKER' AND (mu.mark_right = 0 OR mu.mark_right = 2) AND mu.subject = 'SM' AND mu.enabled = 1) temp on mt.work_id = temp.workId and mt.marker_id = temp.id left join  (SELECT id, work_id, paper_id FROM change_level WHERE work_id = ? AND subject = 'SM') cl ON p.work_id = cl.work_id and p.id = cl.paper_id WHERE mt.work_id = ? AND mt.stage = 1 AND p.is_missing = FALSE UNION ALL select temp.*, null as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level`, p.id upload, p.is_missing missing, p.is_sample isSample,p.is_one_click isOneClick, cl.id isShift from student t LEFT JOIN paper p ON t.work_id = p.work_id AND t.exam_number = p.exam_number LEFT JOIN (SELECT  id, work_id, paper_id FROM change_level WHERE work_id = ? AND subject = 'SC') cl ON p.work_id = cl.work_id AND p.id = cl.paper_id LEFT JOIN (SELECT mu.id,mu.name AS teacherName,mu.`role`,mu.subject, mu.work_id AS workId FROM  mark_user AS mu WHERE mu.work_id = ? AND mu.`role` = 'MARKER' AND (mu.mark_right = 0 OR mu.mark_right = 2) AND mu.subject = 'SM' AND mu.enabled = 1) temp ON 1 = 1 WHERE (p.is_missing = TRUE OR p.is_test = 3) AND p.work_id = ? AND p.subject = 'SM' ";
            String sxSql = "select temp.*, mt.`result`, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level`, p.id upload, p.is_missing isMissing, p.is_sample isSample,p.is_one_click isOneClick, cl.id isShift from student t left join paper p on t.work_id = p.work_id and t.exam_number = p.exam_number left join mark_task AS mt on p.work_id = mt.work_id and p.id = mt.paper_id left join (SELECT mu.id, mu.name AS teacherName, mu.`role`, mu.subject, mu.work_id AS workId FROM mark_user AS mu WHERE mu.work_id = ? AND mu.`role` = 'MARKER' AND (mu.mark_right = 0 OR mu.mark_right = 2) AND mu.subject = 'SX' AND mu.enabled = 1) temp on mt.work_id = temp.workId and mt.marker_id = temp.id left join  (SELECT id, work_id, paper_id FROM change_level WHERE work_id = ? AND subject = 'SX') cl ON p.work_id = cl.work_id and p.id = cl.paper_id WHERE mt.work_id = ? AND mt.stage = 1 AND p.is_missing = FALSE UNION ALL select temp.*, null as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level`, p.id upload, p.is_missing missing, p.is_sample isSample,p.is_one_click isOneClick, cl.id isShift from student t LEFT JOIN paper p ON t.work_id = p.work_id AND t.exam_number = p.exam_number LEFT JOIN (SELECT  id, work_id, paper_id FROM change_level WHERE work_id = ? AND subject = 'SC') cl ON p.work_id = cl.work_id AND p.id = cl.paper_id LEFT JOIN (SELECT mu.id,mu.name AS teacherName,mu.`role`,mu.subject, mu.work_id AS workId FROM  mark_user AS mu WHERE mu.work_id = ? AND mu.`role` = 'MARKER' AND (mu.mark_right = 0 OR mu.mark_right = 2) AND mu.subject = 'SX' AND mu.enabled = 1) temp ON 1 = 1 WHERE (p.is_missing = TRUE OR p.is_test = 3) AND p.work_id = ? AND p.subject = 'SX' ";
            //拼装问号
            scSql = sqlUtil.sqlPrint(scSql, workId, workId, workId, workId, workId, workId);
            smSql = sqlUtil.sqlPrint(smSql, workId, workId, workId, workId, workId, workId);
            sxSql = sqlUtil.sqlPrint(sxSql, workId, workId, workId, workId, workId, workId);
            sbAll = new StringBuilder(sqlUtil.sqlUnionAll(scSql));//union all
            sbAll = sbAll.append(smSql);
            sbAll = new StringBuilder(sqlUtil.sqlUnionAll(sbAll.toString()));//union all
            sbAll = sbAll.append(sxSql);
        } else {
            String customSql = "select temp.*, mt.`result`, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level` from mark_task as mt , (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER'and (mu.mark_right = 0 or mu.mark_right = 2) and mu.subject = 'CUSTOM' and mu.enabled = 1) temp, paper p, student t where mt.marker_id = temp.id and mt.paper_id = p.id and t.exam_number = p.exam_number and mt.work_id = ? and mt.stage = 1 and p.is_missing = false UNION ALL select temp.*, null as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, p.mark_by_leader as markByLeader, p.`level` from paper p, (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER' and (mu.mark_right = 0 or mu.mark_right = 2) and mu.subject = 'CUSTOM' and mu.enabled = 1) temp, student t where (p.is_missing = true or p.is_test = 3) and p.work_id = ? and p.subject = 'CUSTOM' and t.exam_number = p.exam_number ";
            customSql = sqlUtil.sqlPrint(customSql, workId, workId, workId, workId);
            sbAll = new StringBuilder(customSql);
        }
        List list = sqlUtil.execSqlForMap(sbAll.toString());
        if (Objects.nonNull(list)) {
            Map<String, Map<String, ExportLevelResultDTO>> subjectMap = new HashMap<>();//课程老师批改集合
            Map<String, Map<String, ExportLevelResultDTO>> studentAnswerMap = new HashMap<>();//学生答题集合
            Map<String, ExportLevelResultDTO> exportLevelResultDTOs = new HashMap();//学生集合
            assembleData(list, subjectMap, studentAnswerMap, exportLevelResultDTOs);

            //创建excel
            SXSSFWorkbook wb = new SXSSFWorkbook();
            Sheet sheet = wb.createSheet(LEVEL_FILE_NAME);
            CellStyle style = wb.createCellStyle();
//            style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
//            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中格式
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
            Row row = sheet.createRow(0);

            //绘制固定表头
            sheet = drawExcelFixedHeadLevel(levelInterval, sheet, style, row, subjectMap, workId);

            //绘制动态表头
            Row dynamicHeadRow = sheet.createRow(1);
            if (!imageConfig.isCustomSubject()) {
                //SC表头
                Map<String, ExportLevelResultDTO> scMap = subjectMap.get(Subject.SC.toString());
                int size = 0;
//                if (Objects.nonNull(scMap) && scMap.size() > 0) {
                    drawExcelTeacher(levelInterval, scMap, sheet, style, dynamicHeadRow, new AtomicInteger(level));
                    size = Objects.nonNull(scMap) && scMap.size() > 0 ? scMap.size() : 0;
//                }
                //SM表头
                Map<String, ExportLevelResultDTO> smMap = subjectMap.get(Subject.SM.toString());
//                if (Objects.nonNull(smMap) && smMap.size() > 0) {
                    drawExcelTeacher(levelInterval, smMap, sheet, style, dynamicHeadRow, new AtomicInteger(level + size + 6));
                    size = size + (Objects.nonNull(smMap) && smMap.size() > 0 ? smMap.size() : 0);
//                }
                //SX表头
                Map<String, ExportLevelResultDTO> sxMap = subjectMap.get(Subject.SX.toString());
//                if (Objects.nonNull(sxMap) && sxMap.size() > 0) {
                    drawExcelTeacher(levelInterval, sxMap, sheet, style, dynamicHeadRow, new AtomicInteger(level + size + 6*2));
//                }
                //加载excel数据
                AtomicInteger cellIndex = new AtomicInteger(0);
                Sheet finalSheet = sheet;
                AtomicInteger i = new AtomicInteger(0);
                AtomicInteger r = new AtomicInteger(2);
                exportLevelResultDTOs.forEach((listk, listv) -> {
                    int firstRow = i.get() + r.get();
                    cellIndex.set(0);
                    Row hssfRow = finalSheet.createRow(firstRow);
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getExamNumber(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getStudentName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getAreaName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getSourceName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    Map<String, ExportLevelResultDTO> map = studentAnswerMap.get(listv.getExamNumber());
                    if (Objects.nonNull(map) && map.size() > 0) {
                        map.forEach((k, v) -> {
                            if (k.contains(Subject.SC.toString())) {
                                ExportLevelResultDTO exportLevelResultDTO = scMap.get(v.getTeacherName());
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                                if (Objects.nonNull(v.getLevel())) {
                                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()), level + (scMap == null ? 0 : scMap.size())));
                                }
                                //上传
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getUpload()) ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + 1, level + (scMap == null ? 0 : scMap.size()) + 1));
                                //缺考
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getMissing()) && v.getMissing() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + 2, level + (scMap == null ? 0 : scMap.size()) + 2));
                                //标准卷
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getSample()) && v.getSample() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + 3, level + (scMap == null ? 0 : scMap.size()) + 3));
                                //一键定档
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getOneClick()) && v.getOneClick() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + 4, level + (scMap == null ? 0 : scMap.size()) + 4));
                                //改档
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getIsShift()) ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + 5, level + (scMap == null ? 0 : scMap.size()) + 5));
                            } else if (k.contains(Subject.SM.toString())) {
                                ExportLevelResultDTO exportLevelResultDTO = smMap.get(v.getTeacherName());
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                                if (Objects.nonNull(v.getLevel())) {
                                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 1, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 1));
                                }
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getUpload()) ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size())  + (smMap == null ? 0 : smMap.size()) + 5 + 2, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 2));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getMissing()) && v.getMissing() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 3, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 3));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getSample()) && v.getSample() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 4, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 4));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getOneClick()) && v.getOneClick() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 5, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 5));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getIsShift()) ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 6, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + 5 + 6));
                            } else if (k.contains(Subject.SX.toString())) {
                                ExportLevelResultDTO exportLevelResultDTO = sxMap.get(v.getTeacherName());
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                                if (Objects.nonNull(v.getLevel())) {
                                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2));
                                }
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getUpload()) ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 1, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 1));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getMissing()) && v.getMissing() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 2, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 2));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getSample()) && v.getSample() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 3, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 3));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getOneClick()) && v.getOneClick() ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 4, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 4));
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(Objects.nonNull(v.getIsShift()) ? "是" : "否", firstRow, firstRow, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 5, level + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + 6 * 2 + 5));
                            }
                        });
                    }
                    i.getAndIncrement();
                });
            } else {
                //SC表头
                Map<String, ExportLevelResultDTO> customMap = subjectMap.get(Subject.CUSTOM.toString());
                int size = 0;
                if (Objects.nonNull(customMap) && customMap.size() > 0) {
                    drawExcelTeacher(levelInterval, customMap, sheet, style, dynamicHeadRow, new AtomicInteger(level));
                    size = customMap.size();
                }
                //加载excel数据
                AtomicInteger cellIndex = new AtomicInteger(0);
                Sheet finalSheet = sheet;
                AtomicInteger i = new AtomicInteger(0);
                AtomicInteger r = new AtomicInteger(2);
                exportLevelResultDTOs.forEach((listk, listv) -> {
                    int firstRow = i.get() + r.get();
                    cellIndex.set(0);
                    Row hssfRow = finalSheet.createRow(firstRow);
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getExamNumber(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getStudentName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getAreaName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getSourceName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    Map<String, ExportLevelResultDTO> map = studentAnswerMap.get(listv.getExamNumber());
                    if (Objects.nonNull(map) && map.size() > 0) {
                        map.forEach((k, v) -> {
                            ExportLevelResultDTO exportLevelResultDTO = customMap.get(v.getTeacherName());
                            drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                            if (v.getMarkByLeader()) {
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, level + (customMap == null ? 0 : customMap.size()), level + (customMap == null ? 0 : customMap.size())));
                            }
                            if (Objects.nonNull(v.getLevel())) {
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, level + (customMap == null ? 0 : customMap.size()) + 1, level + (customMap == null ? 0 : customMap.size()) + 1));
                            }
                        });
                    }
                    i.getAndIncrement();
                });
            }
            ExportUtils.exportEXCEL(LEVEL_FILE_NAME, wb, response);
        }
    }

    /**
     * 导出分数成绩
     *
     * @param workId
     * @param response
     */
    @RequestMapping(value = "exportScoreResult", method = RequestMethod.GET)
    public void exportScoreResult(@RequestParam Long workId, HttpServletResponse response) {
        //测试-导出分数成绩表
        StringBuilder sbAll = null;
        if (!imageConfig.isCustomSubject()) {
            String scSql = "select temp.*, IFNULL(mt.`result`,0) as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level,p.mark_by_leader as markByLeader from mark_task as mt , (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER'and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'SC'and mu.enabled = 1) temp, paper p, student t where mt.marker_id = temp.id and mt.paper_id = p.id and t.exam_number = p.exam_number and mt.work_id = ? and mt.stage = 2 and p.is_missing = false UNION ALL select temp.*, 0 as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level, p.mark_by_leader as markByLeader from paper p, (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER' and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'SC' and mu.enabled = 1) temp, student t where p.is_missing = true and p.work_id = ? and p.subject = 'SC' and t.exam_number = p.exam_number ";
            String smSql = "select temp.*, IFNULL(mt.`result`,0) as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level,p.mark_by_leader as markByLeader from mark_task as mt , (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER'and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'SM'and mu.enabled = 1) temp, paper p, student t where mt.marker_id = temp.id and mt.paper_id = p.id and t.exam_number = p.exam_number and mt.work_id = ? and mt.stage = 2 and p.is_missing = false UNION ALL select temp.*, 0 as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level, p.mark_by_leader as markByLeader from paper p, (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER' and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'SM' and mu.enabled = 1) temp, student t where p.is_missing = true and p.work_id = ? and p.subject = 'SM' and t.exam_number = p.exam_number ";
            String sxSql = "select temp.*, IFNULL(mt.`result`,0) as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level,p.mark_by_leader as markByLeader from mark_task as mt , (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER'and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'SX'and mu.enabled = 1) temp, paper p, student t where mt.marker_id = temp.id and mt.paper_id = p.id and t.exam_number = p.exam_number and mt.work_id = ? and mt.stage = 2 and p.is_missing = false UNION ALL select temp.*, 0 as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level, p.mark_by_leader as markByLeader from paper p, (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER' and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'SX' and mu.enabled = 1) temp, student t where p.is_missing = true and p.work_id = ? and p.subject = 'SX' and t.exam_number = p.exam_number ";
            //拼装问号
            scSql = sqlUtil.sqlPrint(scSql, workId, workId, workId, workId);
            smSql = sqlUtil.sqlPrint(smSql, workId, workId, workId, workId);
            sxSql = sqlUtil.sqlPrint(sxSql, workId, workId, workId, workId);
            sbAll = new StringBuilder(sqlUtil.sqlUnionAll(scSql));//union all
            sbAll = sbAll.append(smSql);
            sbAll = new StringBuilder(sqlUtil.sqlUnionAll(sbAll.toString()));//union all
            sbAll = sbAll.append(sxSql);
        } else {
            String customSql = "select temp.*, IFNULL(mt.`result`,0) as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level,p.mark_by_leader as markByLeader from mark_task as mt , (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER'and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'CUSTOM'and mu.enabled = 1) temp, paper p, student t where mt.marker_id = temp.id and mt.paper_id = p.id and t.exam_number = p.exam_number and mt.work_id = ? and mt.stage = 2 and p.is_missing = false UNION ALL select temp.*, 0 as result, p.source_name as sourceName, p.exam_number as examNumber, p.student_name as studentName, t.area_name as areaName, t.school, t.exam_room examRoom, IFNULL(p.score,0) as level, p.mark_by_leader as markByLeader from paper p, (select mu.id, mu.name as teacherName, mu.`role`, mu.subject, mu.work_id as workId from mark_user as mu where mu.work_id = ? and mu.`role` = 'MARKER' and (mu.mark_right = 1 or mu.mark_right = 2) and mu.subject = 'CUSTOM' and mu.enabled = 1) temp, student t where p.is_missing = true and p.work_id = ? and p.subject = 'CUSTOM' and t.exam_number = p.exam_number ";
            customSql = sqlUtil.sqlPrint(customSql, workId, workId, workId, workId);
            sbAll = new StringBuilder(customSql);
        }
        List list = sqlUtil.execSqlForMap(sbAll.toString());
        if (Objects.nonNull(list)) {
            Map<String, Map<String, ExportLevelResultDTO>> subjectMap = new HashMap<>();//课程老师批改集合
            Map<String, Map<String, ExportLevelResultDTO>> studentAnswerMap = new HashMap<>();//学生答题集合
            Map<String, ExportLevelResultDTO> exportLevelResultDTOs = new HashMap();//学生集合
            assembleData(list, subjectMap, studentAnswerMap, exportLevelResultDTOs);

            //创建excel
            SXSSFWorkbook wb = new SXSSFWorkbook();
            Sheet sheet = wb.createSheet(SCORE_FILE_NAME);
            CellStyle style = wb.createCellStyle();
//            style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
//            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中格式
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
            Row row = sheet.createRow(0);

            //绘制固定表头
            sheet = drawExcelFixedHead(scoreInterval, sheet, style, row, subjectMap, workId);

            //绘制动态表头
            Row dynamicHeadRow = sheet.createRow(1);
            if (!imageConfig.isCustomSubject()) {
                //SC表头
                Map<String, ExportLevelResultDTO> scMap = subjectMap.get(Subject.SC.toString());
                int size = 0;
                if (Objects.nonNull(scMap) && scMap.size() > 0) {
                    drawExcelTeacher(scoreInterval, scMap, sheet, style, dynamicHeadRow, new AtomicInteger(score));
                    size = scMap.size();
                }
                //SM表头
                Map<String, ExportLevelResultDTO> smMap = subjectMap.get(Subject.SM.toString());
                if (Objects.nonNull(smMap) && smMap.size() > 0) {
                    drawExcelTeacher(scoreInterval, smMap, sheet, style, dynamicHeadRow, new AtomicInteger(score + size + scoreInterval));
                    size = size + smMap.size();
                }
                //SX表头
                Map<String, ExportLevelResultDTO> sxMap = subjectMap.get(Subject.SX.toString());
                if (Objects.nonNull(sxMap) && sxMap.size() > 0) {
                    drawExcelTeacher(scoreInterval, sxMap, sheet, style, dynamicHeadRow, new AtomicInteger(score + size + scoreInterval * 2));
                }
                //加载excel数据
                AtomicInteger cellIndex = new AtomicInteger(0);
                Sheet finalSheet = sheet;
                AtomicInteger i = new AtomicInteger(0);
                AtomicInteger r = new AtomicInteger(2);
                exportLevelResultDTOs.forEach((listk, listv) -> {
                    int firstRow = i.get() + r.get();
                    cellIndex.set(0);
                    Row hssfRow = finalSheet.createRow(firstRow);
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getExamNumber(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getStudentName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getAreaName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getSchool(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getExamRoom(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getSourceName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    Map<String, ExportLevelResultDTO> map = studentAnswerMap.get(listv.getExamNumber());
                    if (Objects.nonNull(map) && map.size() > 0) {
                        map.forEach((k, v) -> {
                            if (k.contains(Subject.SC.toString())) {
                                ExportLevelResultDTO exportLevelResultDTO = scMap.get(v.getTeacherName());
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                                if (Objects.nonNull(v.getLevel())) {
                                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, score + (scMap == null ? 0 : scMap.size()), score + (scMap == null ? 0 : scMap.size())));
                                }
                            } else if (k.contains(Subject.SM.toString())) {
                                ExportLevelResultDTO exportLevelResultDTO = smMap.get(v.getTeacherName());
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                                if (Objects.nonNull(v.getLevel())) {
                                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, score + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + scoreInterval, score + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + scoreInterval));
                                }
                            } else if (k.contains(Subject.SX.toString())) {
                                ExportLevelResultDTO exportLevelResultDTO = sxMap.get(v.getTeacherName());
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                                if (Objects.nonNull(v.getLevel())) {
                                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, score + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + scoreInterval * 2, score + (scMap == null ? 0 : scMap.size()) + (smMap == null ? 0 : smMap.size()) + (sxMap == null ? 0 : sxMap.size()) + scoreInterval * 2));
                                }
                            }
                        });
                    }
                    i.getAndIncrement();
                });
            } else {
                //SC表头
                Map<String, ExportLevelResultDTO> customMap = subjectMap.get(Subject.CUSTOM.toString());
                int size = 0;
                if (Objects.nonNull(customMap) && customMap.size() > 0) {
                    drawExcelTeacher(scoreInterval, customMap, sheet, style, dynamicHeadRow, new AtomicInteger(level));
                    size = customMap.size();
                }
                //加载excel数据
                AtomicInteger cellIndex = new AtomicInteger(0);
                Sheet finalSheet = sheet;
                AtomicInteger i = new AtomicInteger(0);
                AtomicInteger r = new AtomicInteger(2);
                exportLevelResultDTOs.forEach((listk, listv) -> {
                    int firstRow = i.get() + r.get();
                    cellIndex.set(0);
                    Row hssfRow = finalSheet.createRow(firstRow);
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getExamNumber(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getStudentName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getAreaName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(listv.getSourceName(), firstRow, firstRow, cellIndex.get(), cellIndex.get()));
                    cellIndex.getAndIncrement();
                    Map<String, ExportLevelResultDTO> map = studentAnswerMap.get(listv.getExamNumber());
                    if (Objects.nonNull(map) && map.size() > 0) {
                        map.forEach((k, v) -> {
                            ExportLevelResultDTO exportLevelResultDTO = customMap.get(v.getTeacherName());
                            drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getResult(), firstRow, firstRow, exportLevelResultDTO.getFirstCol(), exportLevelResultDTO.getFirstCol()));
                            if (Objects.nonNull(v.getLevel())) {
                                drawExcelFixedHead(finalSheet, style, hssfRow, new HeaderNode(v.getLevel(), firstRow, firstRow, level + (customMap == null ? 0 : customMap.size()), level + (customMap == null ? 0 : customMap.size())));
                            }
                        });
                    }
                    i.getAndIncrement();
                });
            }
            ExportUtils.exportEXCEL(SCORE_FILE_NAME, wb, response);
        }
    }

    /**
     * 组装数据
     *
     * @param list
     * @param subjectMap
     * @param studentAnswerMap
     * @param exportLevelResultDTOs
     */
    public void assembleData(List list, Map<String, Map<String, ExportLevelResultDTO>> subjectMap, Map<String, Map<String, ExportLevelResultDTO>> studentAnswerMap, Map<String, ExportLevelResultDTO> exportLevelResultDTOs) {
        for (int i = 0; i < list.size(); i++) {
            Map map = (Map) list.get(i);
            Gson gson = new Gson();
            ExportLevelResultDTO exportLevelResultDTO = gson.fromJson(gson.toJson(map), ExportLevelResultDTO.class);
            exportLevelResultDTOs.put(exportLevelResultDTO.getExamNumber(), exportLevelResultDTO);
            if (subjectMap.containsKey(exportLevelResultDTO.getSubject())) {
                subjectMap.get(exportLevelResultDTO.getSubject()).put(exportLevelResultDTO.getTeacherName(), new ExportLevelResultDTO(exportLevelResultDTO.getExamNumber(), exportLevelResultDTO.getTeacherName(), exportLevelResultDTO.getResult()));
            } else {
                Map<String, ExportLevelResultDTO> m = new HashMap<>();
                m.put(exportLevelResultDTO.getTeacherName(), new ExportLevelResultDTO(exportLevelResultDTO.getExamNumber(), exportLevelResultDTO.getTeacherName(), exportLevelResultDTO.getResult()));
                subjectMap.put(exportLevelResultDTO.getSubject(), m);
            }
            if (studentAnswerMap.containsKey(exportLevelResultDTO.getExamNumber())) {
                studentAnswerMap.get(exportLevelResultDTO.getExamNumber()).put(exportLevelResultDTO.getSubject() + ":" + exportLevelResultDTO.getTeacherName(), new ExportLevelResultDTO(exportLevelResultDTO.getResult(), exportLevelResultDTO.getMarkByLeader(), exportLevelResultDTO.getLevel(), exportLevelResultDTO.getTeacherName(), exportLevelResultDTO.getUpload(), exportLevelResultDTO.getMissing(), exportLevelResultDTO.getSample(), exportLevelResultDTO.getOneClick(), exportLevelResultDTO.getIsShift(), exportLevelResultDTO.getSchool(), exportLevelResultDTO.getExamRoom()));
            } else {
                Map<String, ExportLevelResultDTO> m = new HashMap<>();
                m.put(exportLevelResultDTO.getSubject() + ":" + exportLevelResultDTO.getTeacherName(), new ExportLevelResultDTO(exportLevelResultDTO.getResult(), exportLevelResultDTO.getMarkByLeader(), exportLevelResultDTO.getLevel(), exportLevelResultDTO.getTeacherName(), exportLevelResultDTO.getUpload(), exportLevelResultDTO.getMissing(), exportLevelResultDTO.getSample(), exportLevelResultDTO.getOneClick(), exportLevelResultDTO.getIsShift(), exportLevelResultDTO.getSchool(), exportLevelResultDTO.getExamRoom()));
                studentAnswerMap.put(exportLevelResultDTO.getExamNumber(), m);
            }
        }
    }
}
