package yangtz.cs.liu.campus.service.impl.teacherFiles;

import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.system.service.ISysDictDataService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import yangtz.cs.liu.campus.domain.teacherFiles.*;
import yangtz.cs.liu.campus.service.teacherFiles.ITeacherFilesService;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class TeacherFilesExcelUtil {

    @Autowired
    private RuoYiConfig ruoYiConfig;

    @Autowired
    private ITeacherFilesService teacherFilesService;

    @Autowired
    private ISysDictDataService sysDictDataService;


    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    public void getExcel(String noticeId, HttpServletResponse response) {
        TeacherFiles teacherFiles = new TeacherFiles();
        teacherFiles.setNoticeId(noticeId);
        List<TeacherFiles> teacherFilesList = teacherFilesService.selectTeacherFilesList(teacherFiles);

        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        /**
         * 主标题
         */
        XSSFCellStyle mainTitle = workbook.createCellStyle();
        //垂直居中
        mainTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        mainTitle.setAlignment(HorizontalAlignment.CENTER);
        Font mainfont = workbook.createFont();
        mainfont.setFontHeightInPoints((short) 12);
        mainfont.setBold(true);
        mainTitle.setFont(mainfont);
        mainTitle.setBorderBottom(BorderStyle.THIN); //下边框
        mainTitle.setBorderLeft(BorderStyle.THIN);//左边框
        mainTitle.setBorderTop(BorderStyle.THIN);//上边框
        mainTitle.setBorderRight(BorderStyle.THIN);//右边框


        /**
         * 正文
         */
        XSSFCellStyle title = workbook.createCellStyle();
        //垂直居中
        title.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        title.setAlignment(HorizontalAlignment.CENTER);
        Font font0 = workbook.createFont();
        font0.setFontHeightInPoints((short) 12);
        title.setFont(font0);
        title.setBorderBottom(BorderStyle.THIN); //下边框
        title.setBorderLeft(BorderStyle.THIN);//左边框
        title.setBorderTop(BorderStyle.THIN);//上边框
        title.setBorderRight(BorderStyle.THIN);//右边框


        for (int t = 0; t < teacherFilesList.size(); t++) {
            TeacherFilesVO files = teacherFilesService.selectTeacherFilesById(teacherFilesList.get(t).getId());
            XSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(t, files.getTeacherName()+t);
            /**
             * 第一行
             */
            XSSFRow row0 = sheet.createRow(0);
            row0.setHeightInPoints(34);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
            XSSFCell cell0 = row0.createCell(0);
            cell0.setCellStyle(mainTitle);
            cell0.setCellValue("基本信息");
            XSSFCell cell05 = row0.createCell(5);
            cell05.setCellStyle(mainTitle);

            sheet.setColumnWidth(0, 34 * 140);
            sheet.setColumnWidth(1, 34 * 100);
            sheet.setColumnWidth(2, 34 * 100);
            sheet.setColumnWidth(3, 34 * 140);
            sheet.setColumnWidth(4, 34 * 100);
            sheet.setColumnWidth(5, 34 * 100);
            /**
             * 第二行
             */
            for(int i=1;i<=8;i++){
                XSSFRow row2 = sheet.createRow(i);
                row2.setHeightInPoints(20);
                XSSFCell cell = row2.createCell(0);
                String cellValue1 = "";
                switch (i){
                    case 1:
                        cellValue1="学年学期";
                        break;
                    case 2:
                        cellValue1="姓名";
                        break;
                    case 3:
                        cellValue1="年龄";
                        break;
                    case 4:
                        cellValue1="学历";
                        break;
                    case 5:
                        cellValue1="任教学科";
                        break;
                    case 6:
                        cellValue1="任正班主任班级";
                        break;
                    case 7:
                        cellValue1="是否教学组长";
                        break;
                    case 8:
                        cellValue1="设计课时量";
                        break;
                }
                cell.setCellValue(cellValue1);
                cell.setCellStyle(title);


                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                XSSFCell cell1 = row2.createCell(1);
                String cellValue2 = "";
                switch (i){
                    case 1:
                        cellValue2=files.getFilesSemester();
                        break;
                    case 2:
                        cellValue2=files.getTeacherName();
                        break;
                    case 3:
                        if(files.getAge()!=null){
                            cellValue2=String.valueOf(files.getAge());
                        }

                        break;
                    case 4:
                        //学历用字典映射
                        String s = sysDictDataService.selectDictLabel("education", files.getEducation());
                        cellValue2=s;
                        break;
                    case 5:
                        cellValue2=files.getCourseName();
                        break;
                    case 6:
                        cellValue2=files.getBzrClassName();
                        break;
                    case 7:
                        cellValue2=files.getIsTeachingGroupLeader();
                        break;
                    case 8:
                        if(files.getClassHour()!=null){
                            cellValue2=String.valueOf(files.getClassHour());
                        }

                        break;
                }
                cell1.setCellValue(cellValue2);
                cell1.setCellStyle(title);

                XSSFCell cell3 = row2.createCell(2);
                cell3.setCellStyle(title);


                XSSFCell cell2 = row2.createCell(3);
                String cellValue3 = "";
                switch (i){
                    case 1:
                        cellValue3="级部";
                        break;
                    case 2:
                        cellValue3="性别";
                        break;
                    case 3:
                        cellValue3="职称";
                        break;
                    case 4:
                        cellValue3="政治面貌";
                        break;
                    case 5:
                        cellValue3="任教班级";
                        break;
                    case 6:
                        cellValue3="任副班主任班级";
                        break;
                    case 7:
                        cellValue3="是否教研组长";
                        break;
                    case 8:
                        cellValue3="班主任情况";
                        break;
                }
                cell2.setCellValue(cellValue3);
                cell2.setCellStyle(title);

                sheet.addMergedRegion(new CellRangeAddress(i, i, 4, 5));
                XSSFCell cell4 = row2.createCell(4);
                String cellValue4 = "";
                switch (i){
                    case 1:
                        cellValue4=files.getGradeName();
                        break;
                    case 2:
                        if(files.getSex()!=null){
                            if(files.getSex().equals("0")){
                                cellValue4="男";
                            }else{
                                cellValue4="女";
                            }
                        }
                        break;
                    case 3:
                        cellValue4=files.getTitle();
                        break;
                    case 4:
                        cellValue4=files.getAppearance();
                        break;
                    case 5:
                        cellValue4=files.getClassName();
                        break;
                    case 6:
                        cellValue4=files.getFbzrClassName();
                        break;
                    case 7:
                        cellValue4=files.getIsResearchGroupLeader();
                        break;
                    case 8:
                        cellValue4=files.getClassTeacherDetails();
                        break;
                }
                cell4.setCellValue(cellValue4);
                cell4.setCellStyle(title);



                XSSFCell cell5 = row2.createCell(5);
                cell5.setCellStyle(title);
            }

            int h =9;

            XSSFRow row9 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 0, 5));
            row9.setHeightInPoints(34);
            XSSFCell cell90 = row9.createCell(0);
            cell90.setCellStyle(mainTitle);
            cell90.setCellValue("发表成就奖论文情况");
            XSSFCell cell95 = row9.createCell(5);
            cell95.setCellStyle(mainTitle);
            h++;

            XSSFRow row10 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 2));
            row10.setHeightInPoints(20);
            XSSFCell cell100 = row10.createCell(0);
            cell100.setCellStyle(title);
            cell100.setCellValue("序号");
            XSSFCell cell101 = row10.createCell(1);
            cell101.setCellStyle(title);
            cell101.setCellValue("论文名称");
            XSSFCell cell102 = row10.createCell(2);
            cell102.setCellStyle(title);
            XSSFCell cell103 = row10.createCell(3);
            cell103.setCellStyle(title);
            cell103.setCellValue("级别");
            XSSFCell cell104 = row10.createCell(4);
            cell104.setCellStyle(title);
            cell104.setCellValue("等级");
            XSSFCell cell105 = row10.createCell(5);
            cell105.setCellStyle(title);
            cell105.setCellValue("发表时间");
            h++;

            List<TeacherFilesThesis> thesisList = files.getThesisList();
            int thesisxh=1;
            for(TeacherFilesThesis thesis : thesisList){
                XSSFRow row11 = sheet.createRow(h);
                sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 2));
                row11.setHeightInPoints(20);
                XSSFCell cell110 = row11.createCell(0);
                cell110.setCellStyle(title);
                cell110.setCellValue(thesisxh);
                XSSFCell cell111 = row11.createCell(1);
                cell111.setCellStyle(title);
                cell111.setCellValue(thesis.getThesisName());
                XSSFCell cell112 = row11.createCell(2);
                cell112.setCellStyle(title);
                XSSFCell cell113 = row11.createCell(3);
                cell113.setCellStyle(title);
                cell113.setCellValue(sysDictDataService.selectDictLabel("level",thesis.getLevel()));
                XSSFCell cell114 = row11.createCell(4);
                cell114.setCellStyle(title);
                cell114.setCellValue(sysDictDataService.selectDictLabel("grade_type1",thesis.getGrade()));
                XSSFCell cell115 = row11.createCell(5);
                cell115.setCellStyle(title);
                cell115.setCellValue(sdf.format(thesis.getPublishTime()));
                thesisxh++;
                h++;
            }
            XSSFRow row12 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 0, 5));
            row12.setHeightInPoints(34);
            XSSFCell cell120 = row12.createCell(0);
            cell120.setCellStyle(mainTitle);
            cell120.setCellValue("课题、结题、专著、校本教材编写情况");
            XSSFCell cell125 = row12.createCell(5);
            cell125.setCellStyle(mainTitle);
            h++;

            XSSFRow row13 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 3));
            sheet.addMergedRegion(new CellRangeAddress(h, h, 4, 5));
            row13.setHeightInPoints(20);
            XSSFCell cell130 = row13.createCell(0);
            cell130.setCellStyle(title);
            cell130.setCellValue("序号");
            XSSFCell cell131 = row13.createCell(1);
            cell131.setCellStyle(title);
            cell131.setCellValue("名称");
            XSSFCell cell132 = row13.createCell(2);
            cell132.setCellStyle(title);
            XSSFCell cell133 = row13.createCell(3);
            cell133.setCellStyle(title);
            XSSFCell cell134 = row13.createCell(4);
            cell134.setCellStyle(title);
            cell134.setCellValue("发表时间");
            XSSFCell cell135 = row13.createCell(5);
            cell135.setCellStyle(title);
            h++;

            List<TeacherFilesMaterial> materialList = files.getMaterialList();
            int materialxh=1;
            for(TeacherFilesMaterial material : materialList){
                XSSFRow row11 = sheet.createRow(h);
                sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 3));
                sheet.addMergedRegion(new CellRangeAddress(h, h, 4, 5));
                row11.setHeightInPoints(20);
                XSSFCell cell110 = row11.createCell(0);
                cell110.setCellStyle(title);
                cell110.setCellValue(materialxh);
                XSSFCell cell111 = row11.createCell(1);
                cell111.setCellStyle(title);
                cell111.setCellValue(material.getName());
                XSSFCell cell112 = row11.createCell(2);
                cell112.setCellStyle(title);
                XSSFCell cell113 = row11.createCell(3);
                cell113.setCellStyle(title);
                XSSFCell cell114 = row11.createCell(4);
                cell114.setCellStyle(title);
                cell114.setCellValue(sdf.format(material.getPublishTime()));
                XSSFCell cell115 = row11.createCell(5);
                cell115.setCellStyle(title);
                materialxh++;
                h++;
            }
            XSSFRow row14 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 0, 5));
            row14.setHeightInPoints(34);
            XSSFCell cell140 = row14.createCell(0);
            cell140.setCellStyle(mainTitle);
            cell140.setCellValue("培训情况");
            XSSFCell cell145 = row14.createCell(5);
            cell145.setCellStyle(mainTitle);
            h++;

            XSSFRow row15 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 2));
            sheet.addMergedRegion(new CellRangeAddress(h, h, 3, 4));
            row15.setHeightInPoints(20);
            XSSFCell cell150 = row15.createCell(0);
            cell150.setCellStyle(title);
            cell150.setCellValue("序号");
            XSSFCell cell151 = row15.createCell(1);
            cell151.setCellStyle(title);
            cell151.setCellValue("培训时间");
            XSSFCell cell152 = row15.createCell(2);
            cell152.setCellStyle(title);
            XSSFCell cell153 = row15.createCell(3);
            cell153.setCellValue("培训名称");
            cell153.setCellStyle(title);
            XSSFCell cell154 = row15.createCell(4);
            cell154.setCellStyle(title);
            XSSFCell cell155 = row15.createCell(5);
            cell155.setCellValue("是否合格");
            cell155.setCellStyle(title);
            h++;

            List<TeacherFilesTraining> trainingList = files.getTrainingList();
            int trainingxh=1;
            for (TeacherFilesTraining training:trainingList){
                XSSFRow row11 = sheet.createRow(h);
                sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 2));
                sheet.addMergedRegion(new CellRangeAddress(h, h, 3, 4));
                row15.setHeightInPoints(20);
                XSSFCell cell110 = row11.createCell(0);
                cell110.setCellStyle(title);
                cell110.setCellValue(trainingxh);
                XSSFCell cell111 = row11.createCell(1);
                cell111.setCellStyle(title);
                cell111.setCellValue(sdf.format(training.getTrainingTime()));
                XSSFCell cell112 = row11.createCell(2);
                cell112.setCellStyle(title);
                XSSFCell cell113 = row11.createCell(3);
                cell113.setCellValue(training.getTrainingName());
                cell113.setCellStyle(title);
                XSSFCell cell114 = row11.createCell(4);
                cell114.setCellStyle(title);
                XSSFCell cell115 = row11.createCell(5);
                cell115.setCellValue(sysDictDataService.selectDictLabel("is_qualified",training.getIsQualified()));
                cell115.setCellStyle(title);
                trainingxh++;
                h++;
            }


            XSSFRow row16 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 0, 5));
            row16.setHeightInPoints(34);
            XSSFCell cell160 = row16.createCell(0);
            cell160.setCellStyle(mainTitle);
            cell160.setCellValue("业务获奖情况");
            XSSFCell cell165 = row16.createCell(5);
            cell165.setCellStyle(mainTitle);
            h++;

            XSSFRow row17 = sheet.createRow(h);
            sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 2));
            row15.setHeightInPoints(20);
            XSSFCell cell170 = row17.createCell(0);
            cell170.setCellStyle(title);
            cell170.setCellValue("序号");
            XSSFCell cell171 = row17.createCell(1);
            cell171.setCellStyle(title);
            cell171.setCellValue("获奖名称");
            XSSFCell cell172 = row17.createCell(2);
            cell172.setCellStyle(title);
            XSSFCell cell173 = row17.createCell(3);
            cell173.setCellValue("获奖时间");
            cell173.setCellStyle(title);
            XSSFCell cell174 = row17.createCell(4);
            cell174.setCellValue("获奖等级");
            cell174.setCellStyle(title);
            XSSFCell cell175 = row17.createCell(5);
            cell175.setCellValue("发证机关");
            cell175.setCellStyle(title);
            h++;

            List<TeacherFilesAward> awardList = files.getAwardList();
            int awardxh =1;
            for (TeacherFilesAward award : awardList){
                XSSFRow row11 = sheet.createRow(h);
                sheet.addMergedRegion(new CellRangeAddress(h, h, 1, 2));
                XSSFCell cell110 = row11.createCell(0);
                cell110.setCellStyle(title);
                cell110.setCellValue(awardxh);
                XSSFCell cell111 = row11.createCell(1);
                cell111.setCellStyle(title);
                cell111.setCellValue(award.getAwardName());
                XSSFCell cell112 = row11.createCell(2);
                cell112.setCellStyle(title);
                XSSFCell cell113 = row11.createCell(3);
                cell113.setCellValue(sdf.format(award.getAwardTime()));
                cell113.setCellStyle(title);
                XSSFCell cell114 = row11.createCell(4);
                cell114.setCellValue(sysDictDataService.selectDictLabel("grade_type1",award.getGradeOfAward()));
                cell114.setCellStyle(title);
                XSSFCell cell115 = row11.createCell(5);
                cell115.setCellValue(award.getLicenseIssuingAuthority());
                cell115.setCellStyle(title);
                awardxh++;
                h++;
            }

        }

        // 把相应的Excel 工作簿存盘
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename) {
        String downloadPath = ruoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }



}
