backend_dormitory/src/main/java/com/template/controller/WelcomeStudentController.java

package com.template.controller;


import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.api.R;
import com.template.api.WelcomeStudentControllerAPI;
import com.template.common.utils.ExcelUtils;
import com.template.common.utils.paramUtils;
import com.template.model.enumModel.eFileType;
import com.template.model.enumModel.eProjectType;
import com.template.model.pojo.*;
import com.template.model.request.*;
import com.template.model.result.CommonResult;
import com.template.model.result.PageUtils;
import com.template.model.vo.*;
import com.template.services.*;
import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.validation.BindingResult;

import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author ceshi
 * @since 2025-06-13
 */
@RestController
public class WelcomeStudentController implements WelcomeStudentControllerAPI {

    @Autowired
    private WelcomeStudentService welcomeStudentService;

    @Autowired
    private WelcomeFamilyService welcomeFamilyService;

    @Autowired
    private WelcomeAccompanyService welcomeAccompanyService;

    @Autowired
    private WelcomeBedService welcomeBedService;

    @Autowired
    private WelcomeArriveSettingService welcomeArriveSettingService;

    @Autowired
    private WelcomeOrgService welcomeOrgService;

    @Autowired
    private WelcomeCityService welcomeCityService;

    @Autowired
    WelcomeSchoolService welcomeSchoolService;

    @Autowired
    WelcomeBuildService welcomeBuildService;

    @Autowired
    WelcomeDormitoryService welcomeDormitoryService;

    @Autowired
    WelcomeVisitorService welcomeVisitorService;


    private static Logger logger = LoggerFactory.getLogger(WelcomeStudentController.class);

    @Override
    @Transactional(rollbackFor = {Exception.class})
    public CommonResult insertStudentInfo(InsertStudentRequest isr, BindingResult bindingResult) throws Exception {

        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        int existCount = welcomeStudentService.existStudentInfo(isr.getAdmissNum(), isr.getCardId());
        if (existCount > 0) {
            return CommonResult.fail("当前录取号或身份证号已存在！");
        }

        //region 学生信息
        WelcomeStudent ws = new WelcomeStudent();
        ws.setAdmissNum(isr.getAdmissNum());
        ws.setName(isr.getName());
        ws.setPicture(isr.getPicture());
        ws.setCardId(isr.getCardId());
        ws.setSex(isr.getSex());
        ws.setBirthday(isr.getBirthday());
        ws.setSchool(isr.getSchool());
        ws.setCollege(isr.getCollege());
        ws.setMajor(isr.getMajor());
        ws.setClassstr(isr.getClassstr());
        ws.setCollegeId(isr.getCollegeId());
        ws.setMajorId(isr.getMajorId());
        ws.setClassstrId(isr.getClassstrId());
        ws.setExamNum(isr.getExamNum());
        ws.setEduSystem(isr.getEduSystem());
        ws.setGraduationSchool(isr.getGraduationSchool());
        ws.setBatchValue(isr.getBatchValue());
        ws.setPoliticalStatu(isr.getPoliticalStatu());
        ws.setNationality(isr.getNationality());
        ws.setPhone(isr.getPhone());
        ws.setOprovinceId(isr.getOprovinceId());
        ws.setOcityId(isr.getOcityId());
        ws.setOdistrictId(isr.getOdistrictId());
        ws.setProvinceId(isr.getProvinceId());
        ws.setCityId(isr.getCityId());
        ws.setDistrictId(isr.getDistrictId());
        ws.setAddress(isr.getAddress());
        ws.setTrafficMethod(isr.getTrafficMethod());
        ws.setArrive(isr.getArrive());
        ws.setArrvieDate(isr.getArrvieDate());
        ws.setArriveTime(isr.getArriveTime());
        ws.setZipCode(isr.getZipCode());
        ws.setRegisterOntime(isr.getRegisterOntime());
        //ws.setAmountPayable(isr.getAmountPayable());
        //ws.setPayAmount(isr.getPayAmount());
        //ws.setIsDrive(isr.getIsDrive());
        //ws.setCarNumber(isr.getCarNumber());
        //endregion

        //region 家庭成员数据
        List<WelcomeFamily> wfs = new ArrayList<>();
        if (isr.getFamilys() != null && isr.getFamilys().size() > 0) {
            for (InsertFamilyRequest family : isr.getFamilys()) {
                if(!(StringUtils.hasText(family.getFamilyShip()) && StringUtils.hasText(family.getName())
                && StringUtils.hasText(family.getWorkUnit()) && StringUtils.hasText(family.getPhone()))){
                    return CommonResult.fail("家庭成员信息不能为空！");
                }
                WelcomeFamily wf = new WelcomeFamily();
                wf.setStudentCard(ws.getCardId());
                wf.setFamilyShip(family.getFamilyShip());
                wf.setName(family.getName());
                wf.setWorkUnit(family.getWorkUnit());
                wf.setPhone(family.getPhone());
                wfs.add(wf);
            }
        }
        //endregion

        //region 陪同人员数据
        List<WelcomeAccompany> was = new ArrayList<>();
        if (isr.getAccompanys() != null && isr.getAccompanys().size() > 0) {
            for (InsertAccompanyRequest accompany : isr.getAccompanys()) {
                if(!(StringUtils.hasText(accompany.getName()) && StringUtils.hasText(accompany.getPhone()))){
                    return CommonResult.fail("陪同人员信息不能为空！");
                }
                WelcomeAccompany wa = new WelcomeAccompany();
                wa.setStudentCard(ws.getCardId());
                wa.setName(accompany.getName());
                wa.setPhone(accompany.getPhone());
                was.add(wa);
            }
        }

        //endregion

        //region 宿舍信息
        WelcomeBed bedData = welcomeBedService.queryBedData(isr.getSchool(), isr.getBuildId(), isr.getDormitoryId(), Integer.valueOf(isr.getBed()));
        if (bedData != null) {
            bedData.setStudentCard(isr.getCardId());
            bedData.setCollege(isr.getCollege());
            bedData.setCollegeId(isr.getCollegeId());
            bedData.setMajor(isr.getMajor());
            bedData.setMajorId(isr.getMajorId());
            bedData.setClassstr(isr.getClassstr());
            bedData.setClassstrId(isr.getClassstrId());
            bedData.setIsCheck(1);
            bedData.setCardNum(isr.getAdmissNum());
            bedData.setName(isr.getName());
        }

        //endregion

        try {

            int studentId = welcomeStudentService.insertWelcomeStudent(ws);
            if (studentId <= 0) {
                logger.error("添加学生失败，参数：" + JSON.toJSON(isr));
                throw new Exception("添加失败！");
            }

            if (wfs != null && wfs.size() > 0) {
                boolean insertBatch = welcomeFamilyService.saveBatch(wfs);
                if (!insertBatch) {
                    logger.error("添加家庭成员失败，参数：" + JSON.toJSON(wfs));
                    throw new Exception("添加失败！");
                }
            }

            if (was != null && was.size() > 0) {
                boolean insertBatch = welcomeAccompanyService.saveBatch(was);
                if (!insertBatch) {
                    logger.error("添加陪同人员失败，参数：" + JSON.toJSON(was));
                    throw new Exception("添加失败！");
                }
            }

            int insertStudentDormitory = welcomeBedService.updateWelcomeBed(bedData);
            if (insertStudentDormitory <= 0) {
                logger.error("添加学生床位信息失败，参数：" + JSON.toJSON(bedData));
                throw new Exception("添加失败！");
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception("添加失败！");
        }

        return CommonResult.ok("添加成功");
    }

    @Override
    public CommonResult updateStudentInfo(updateStudentRequest usr, BindingResult bindingResult) throws Exception {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        WelcomeStudent student = welcomeStudentService.getManageById(usr.getId());
        if (student == null) {
            return CommonResult.fail("学生数据已失效，编辑失败！");
        }

        //region 学生信息
        student.setAdmissNum(usr.getAdmissNum());
        student.setName(usr.getName());
        student.setPicture(usr.getPicture());
        //student.setCardId(usr.getCardId());
        student.setSex(usr.getSex());
        student.setBirthday(usr.getBirthday());
        student.setSchool(usr.getSchool());
        student.setCollege(usr.getCollege());
        student.setMajor(usr.getMajor());
        student.setClassstr(usr.getClassstr());
        student.setCollegeId(usr.getCollegeId());
        student.setMajorId(usr.getMajorId());
        student.setClassstrId(usr.getClassstrId());
        student.setExamNum(usr.getExamNum());
        student.setEduSystem(usr.getEduSystem());
        student.setGraduationSchool(usr.getGraduationSchool());
        student.setBatchValue(usr.getBatchValue());
        student.setPoliticalStatu(usr.getPoliticalStatu());
        student.setNationality(usr.getNationality());
        student.setPhone(usr.getPhone());
        student.setOprovinceId(usr.getOprovinceId());
        student.setOcityId(usr.getOcityId());
        student.setOdistrictId(usr.getOdistrictId());
        student.setProvinceId(usr.getProvinceId());
        student.setCityId(usr.getCityId());
        student.setDistrictId(usr.getDistrictId());
        student.setAddress(usr.getAddress());
        student.setTrafficMethod(usr.getTrafficMethod());
        student.setArrive(usr.getArrive());
        student.setArrvieDate(usr.getArrvieDate());
        student.setArriveTime(usr.getArriveTime());
        student.setZipCode(usr.getZipCode());
        student.setRegisterOntime(usr.getRegisterOntime());
        //ws.setAmountPayable(isr.getAmountPayable());
        //ws.setPayAmount(isr.getPayAmount());
        //ws.setIsDrive(isr.getIsDrive());
        //ws.setCarNumber(isr.getCarNumber());
        //endregion

        //region 家庭成员数据

        List<WelcomeFamily> wfs = new ArrayList<>();
        if (usr.getFamilys() != null && usr.getFamilys().size() > 0) {
            for (InsertFamilyRequest family : usr.getFamilys()) {
                if(!(StringUtils.hasText(family.getFamilyShip()) && StringUtils.hasText(family.getName())
                        && StringUtils.hasText(family.getWorkUnit()) && StringUtils.hasText(family.getPhone()))){
                    return CommonResult.fail("家庭成员信息不能为空！");
                }
                WelcomeFamily wf = new WelcomeFamily();
                wf.setStudentCard(student.getCardId());
                wf.setFamilyShip(family.getFamilyShip());
                wf.setName(family.getName());
                wf.setWorkUnit(family.getWorkUnit());
                wf.setPhone(family.getPhone());
                wfs.add(wf);
            }
        }
        //endregion

        //region 陪同人员数据
        List<WelcomeAccompany> was = new ArrayList<>();
        if (usr.getAccompanys() != null && usr.getAccompanys().size() > 0) {
            for (InsertAccompanyRequest accompany : usr.getAccompanys()) {
                if(!(StringUtils.hasText(accompany.getName()) && StringUtils.hasText(accompany.getPhone()))){
                    return CommonResult.fail("陪同人员信息不能为空！");
                }
                WelcomeAccompany wa = new WelcomeAccompany();
                wa.setStudentCard(student.getCardId());
                wa.setName(accompany.getName());
                wa.setPhone(accompany.getPhone());
                was.add(wa);
            }
        }

        //endregion

        //region 宿舍信息
        List<WelcomeBed> bedDatas = new ArrayList<>();
        WelcomeBed bedData = welcomeBedService.queryBedData(usr.getSchool(), usr.getBuildId(), usr.getDormitoryId(), Integer.valueOf(usr.getBed()));
        if (bedData != null) {//新床位
            if (!StringUtils.hasText(bedData.getStudentCard())) {
                bedData.setStudentCard(student.getCardId());
                bedData.setCollege(student.getCollege());
                bedData.setCollegeId(student.getCollegeId());
                bedData.setMajor(student.getMajor());
                bedData.setMajorId(student.getMajorId());
                bedData.setClassstr(student.getClassstr());
                bedData.setClassstrId(student.getClassstrId());
                bedData.setIsCheck(1);
                bedData.setCardNum(student.getAdmissNum());
                bedData.setName(student.getName());

                //把旧床位清空
                WelcomeBed obedData = welcomeBedService.getBedByCardId(student.getCardId());
                if (obedData == null) {
                    obedData.setStudentCard(null);
                    obedData.setCollege(null);
                    obedData.setCollegeId(null);
                    obedData.setMajor(null);
                    obedData.setMajorId(null);
                    obedData.setClassstr(null);
                    obedData.setClassstrId(null);
                    obedData.setIsCheck(0);
                    obedData.setCardNum(null);
                    obedData.setName(null);
                    bedDatas.add(obedData);
                }
            } else {
                bedData.setStudentCard(student.getCardId());
                bedData.setCollege(student.getCollege());
                bedData.setCollegeId(student.getCollegeId());
                bedData.setMajor(student.getMajor());
                bedData.setMajorId(student.getMajorId());
                bedData.setClassstr(student.getClassstr());
                bedData.setClassstrId(student.getClassstrId());
                bedData.setIsCheck(1);
                bedData.setCardNum(student.getAdmissNum());
                bedData.setName(student.getName());
            }
            bedDatas.add(bedData);

        }

        //endregion

        try {

            int studentId = welcomeStudentService.updateWelcomeStudent(student);
            if (studentId <= 0) {
                logger.error("编辑学生失败，参数：" + JSON.toJSON(usr));
                throw new Exception("编辑失败！");
            }

            int deleteF = welcomeFamilyService.deleteWelcomeFamilyByCard(student.getCardId());
            if (wfs != null && wfs.size() > 0) {
                boolean insertBatch = welcomeFamilyService.saveBatch(wfs);
                if (!insertBatch) {
                    logger.error("编辑家庭成员失败，参数：" + JSON.toJSON(wfs));
                    throw new Exception("编辑失败！");
                }
            }

            int deleteA = welcomeAccompanyService.deleteWelcomeAccompanyByCard(student.getCardId());
            if (was != null && was.size() > 0) {
                boolean insertBatch = welcomeAccompanyService.saveBatch(was);
                if (!insertBatch) {
                    logger.error("编辑陪同人员失败，参数：" + JSON.toJSON(was));
                    throw new Exception("编辑失败！");
                }
            }

            boolean updateBatch = welcomeBedService.updateBatchById(bedDatas);
            if (!updateBatch) {
                logger.error("添加宿舍信息失败，参数：" + JSON.toJSON(bedDatas));
                throw new Exception("添加失败！");
            }

        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception("编辑失败！");
        }

        return CommonResult.ok("编辑成功");
    }

    @Override
    public CommonResult queryPageStudents(int currentPage, int pageCount, Integer collegeId, Integer majorId, Integer classstrId, String trafficMethod, String name) {
        PageUtils<StudentPageVo> result = welcomeStudentService.queryStudentPageList(currentPage, pageCount, collegeId, majorId, classstrId, trafficMethod, name);
        return CommonResult.ok(result);
    }

    @Override
    public CommonResult queryStudentDetail(int id) {
        StudentDetailVo result = new StudentDetailVo();

        WelcomeStudent ws = welcomeStudentService.getManageById(id);
        if (ws == null) {
            return CommonResult.ok("学生信息已失效，查看失败！");
        }

        //家庭成员
        List<FamilyVo> fvs = new ArrayList<>();
        List<WelcomeFamily> familys = welcomeFamilyService.getManageByCardId(ws.getCardId());
        if (familys != null && familys.size() > 0) {
            for (WelcomeFamily wf : familys) {
                FamilyVo fv = new FamilyVo();
                fv.setId(wf.getId());
                fv.setFamilyShip(wf.getFamilyShip());
                fv.setName(wf.getName());
                fv.setPhone(wf.getPhone());
                fv.setWorkUnit(wf.getWorkUnit());
                fvs.add(fv);
            }

        }
        result.setFvs(fvs);

        //陪同人员
        List<AccompanyVo> avs = new ArrayList<>();
        List<WelcomeAccompany> accompanys = welcomeAccompanyService.getManageByCardId(ws.getCardId());
        if (accompanys != null && accompanys.size() > 0) {
            for (WelcomeAccompany ac : accompanys) {
                AccompanyVo av = new AccompanyVo();
                av.setId(ac.getId());
                av.setName(ac.getName());
                av.setPhone(ac.getPhone());
                avs.add(av);
            }
        }
        result.setAvs(avs);

        //宿舍信息
        WelcomeBed wsd = welcomeBedService.getBedByCardId(ws.getCardId());
        if (wsd != null) {
            result.setBuild(wsd.getBuild());
            result.setBuildId(wsd.getBuild());
            result.setDormitory(wsd.getDormitory());//寝室号
            result.setDormitoryId(wsd.getDormitoryId());//寝室号
            result.setBed(String.valueOf(wsd.getNumber()));//床位号
            result.setBedId(wsd.getId());//床位号
        }

        //时间段
        List<ArriveTimeVo> atvs = new ArrayList<>();
        List<WelcomeArriveSetting> wass = welcomeArriveSettingService.queryCheckDatas();
        if (wass != null && wass.size() > 0) {
            for (WelcomeArriveSetting was : wass) {
                ArriveTimeVo atv = new ArriveTimeVo();
                atv.setId(was.getId());
                atv.setStartTime(was.getStartTime());
                atv.setEndTime(was.getEndTime());
                atv.setTimeStr(was.getStartTime() + "-" + was.getEndTime());
                atv.setIsCheck((ws.getArriveTimeId() != null && was.getId().equals(ws.getArriveTimeId())) ? 1 : 0);
                atvs.add(atv);
            }
        }
        result.setAtvs(atvs);

        result.setId(ws.getId());
        result.setSchool(ws.getSchool());
        result.setAdmissNum(ws.getAdmissNum());
        result.setName(ws.getName());
        result.setPicture(ws.getPicture());
        result.setCardId(ws.getCardId());
        result.setSex(ws.getSex());
        result.setBirthday(ws.getBirthday());
        result.setCollege(ws.getCollege());
        result.setMajor(ws.getMajor());
        result.setClassstr(ws.getClassstr());
        result.setCollegeId(ws.getCollegeId());
        result.setMajorId(ws.getMajorId());
        result.setClassstrId(ws.getClassstrId());
        result.setExamNum(ws.getExamNum());
        result.setEduSystem(ws.getEduSystem());
        result.setGraduationSchool(ws.getGraduationSchool());
        result.setBatchValue(ws.getBatchValue());
        result.setPoliticalStatu(ws.getPoliticalStatu());
        result.setNationality(ws.getNationality());
        result.setPhone(ws.getPhone());
        result.setOprovinceId(ws.getOprovinceId());
        result.setOprovince(ws.getOprovince());
        result.setOcityId(ws.getOcityId());
        result.setOcity(ws.getOcity());
        result.setOdistrictId(ws.getOdistrictId());
        result.setOdistrict(ws.getOdistrict());
        result.setProvinceId(ws.getProvinceId());
        result.setProvince(ws.getProvince());
        result.setCityId(ws.getCityId());
        result.setCity(ws.getCity());
        result.setDistrictId(ws.getDistrictId());
        result.setDistrict(ws.getDistrict());
        result.setAddress(ws.getAddress());
        result.setRegisterOntime(ws.getRegisterOntime());
        result.setZipCode(ws.getZipCode());
        result.setTrafficMethod(ws.getTrafficMethod());
        result.setArrive(ws.getArrive());
        result.setArrvieDate(ws.getArrvieDate());
        result.setArriveTimeId(ws.getArriveTimeId());
        result.setAmountPayable(ws.getAmountPayable());
        result.setPayAmount(ws.getPayAmount());
        result.setIsDrive(ws.getIsDrive());
        result.setCarNumber(ws.getCarNumber());

        return CommonResult.ok(result);
    }

    @Override
    @Transactional(rollbackFor = {Exception.class})
    public CommonResult infoCollection(InfoCollectionRequest icr, BindingResult bindingResult) throws Exception {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        WelcomeStudent ws = welcomeStudentService.getDataByIdcard(icr.getCardId());
        if (ws == null) {
            ws = new WelcomeStudent();
        }
        ws.setSchool(icr.getSchool());
        ws.setAdmissNum(icr.getAdmissNum());
        ws.setName(icr.getName());
        ws.setPicture(icr.getPicture());
        ws.setCardId(icr.getCardId());
        ws.setSex(icr.getSex());
        ws.setBirthday(icr.getBirthday());
        ws.setCollegeId(icr.getCollegeId());
        ws.setMajorId(icr.getMajorId());
        ws.setClassstrId(icr.getClassstrId());
        ws.setExamNum(icr.getExamNum());
        ws.setEduSystem(icr.getEduSystem());
        ws.setGraduationSchool(icr.getGraduationSchool());
        ws.setBatchValue(icr.getBatchValue());
        ws.setPoliticalStatu(icr.getPoliticalStatu());
        ws.setNationality(icr.getNationality());
        ws.setPhone(icr.getPhone());
        ws.setOprovinceId(icr.getOprovinceId());
        ws.setOprovince(icr.getOprovince());
        ws.setOcityId(icr.getOcityId());
        ws.setOcity(icr.getOcity());
        ws.setOdistrictId(icr.getOdistrictId());
        ws.setOdistrict(icr.getOdistrict());
        ws.setProvinceId(icr.getProvinceId());
        ws.setProvince(icr.getProvince());
        ws.setCityId(icr.getCityId());
        ws.setCity(icr.getCity());
        ws.setDistrictId(icr.getDistrictId());
        ws.setDistrict(icr.getDistrict());
        ws.setAddress(icr.getAddress());
        ws.setRegisterOntime(icr.getRegisterOntime());
        ws.setZipCode(icr.getZipCode());
        ws.setTrafficMethod(icr.getTrafficMethod());
        ws.setArrive(icr.getArrive());
        ws.setArrvieDate(icr.getArrvieDate());
        ws.setArriveTimeId(icr.getArriveTimeId());
        ws.setFillStatus("已填报");
        //家庭成员
        List<WelcomeFamily> wfs = new ArrayList<>();
        if (icr.getFvs() != null && icr.getFvs().size() > 0) {
            for (FamilyVo fv : icr.getFvs()) {
                if(!(StringUtils.hasText(fv.getName()) && StringUtils.hasText(fv.getPhone()) && StringUtils.hasText(fv.getWorkUnit()) && StringUtils.hasText(fv.getFamilyShip()))){
                    return CommonResult.fail("家庭成员信息不能为空！");
                }

                WelcomeFamily wf = new WelcomeFamily();
                wf.setName(fv.getName());
                wf.setPhone(fv.getPhone());
                wf.setWorkUnit(fv.getWorkUnit());
                wf.setFamilyShip(fv.getFamilyShip());
                wf.setStudentCard(icr.getCardId());
                wfs.add(wf);
            }
        }

        //陪同人员
        List<WelcomeAccompany> was = new ArrayList<>();
        if (icr.getAvs() != null && icr.getAvs().size() > 0) {
            for (AccompanyVo av : icr.getAvs()) {
                if(!(StringUtils.hasText(av.getName()) && StringUtils.hasText(av.getPhone()))){
                    return CommonResult.fail("家庭成员信息不能为空！");
                }

                WelcomeAccompany wa = new WelcomeAccompany();
                wa.setName(av.getName());
                wa.setPhone(av.getPhone());
                wa.setStudentCard(icr.getCardId());
                was.add(wa);
            }
        }

        try {
            System.out.println("信息采集1");
            boolean resultWs = welcomeStudentService.saveOrUpdate(ws);
            System.out.println("信息采集1.2");
            if (!resultWs) {
                System.out.println("信息采集1.1");
                logger.error("采集学生信息失败，ws参数：" + JSON.toJSON(ws));
                throw new Exception("采集学生信息失败！");
            }
            System.out.println("信息采集2");
            boolean resultWf = welcomeFamilyService.saveBatch(wfs);
            System.out.println("信息采集1.3");
            if (!resultWf) {
                System.out.println("信息采集1.4");
                logger.error("采集学生信息失败，wfs参数：" + JSON.toJSON(wfs));
                throw new Exception("采集学生信息失败！");
            }
            System.out.println("信息采集3");
            boolean resultWa = welcomeAccompanyService.saveBatch(was);
            System.out.println("信息采集1.5");
            if (!resultWa) {
                System.out.println("信息采集1.6");
                logger.error("采集学生信息失败，was参数：" + JSON.toJSON(was));
                throw new Exception("采集学生信息失败！");
            }

        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception("采集失败！");
        }

        return CommonResult.ok("采集完成！");
    }

    @Override
    @Transactional(rollbackFor = {Exception.class})
    public CommonResult deleteStudentInfo(int id) throws Exception {

        WelcomeStudent ws = welcomeStudentService.getManageById(id);
        if (ws == null) {
            return CommonResult.fail("学生信息已失效，无法进行删除操作！");
        }
        //把旧床位清空
        WelcomeBed obedData = welcomeBedService.getBedByCardId(ws.getCardId());
        if (obedData != null) {
            obedData.setStudentCard(null);
            obedData.setCollege(null);
            obedData.setCollegeId(null);
            obedData.setMajor(null);
            obedData.setMajorId(null);
            obedData.setClassstr(null);
            obedData.setClassstrId(null);
            obedData.setIsCheck(0);
            obedData.setCardNum(null);
            obedData.setName(null);
        }

        try {
            //删除学生数据
            int deleteStudent = welcomeStudentService.deleteWelcomeStudentById(id);
            if (deleteStudent <= 0) {
                logger.error("删除学生信息失败，id参数：" + JSON.toJSON(id));
                throw new Exception("删除学生信息失败！");
            }

            if (obedData != null) {
                //删除宿舍数据
                int deleteSd = welcomeBedService.updateWelcomeBed(obedData);
                if (deleteSd < 0) {
                    logger.error("删除宿舍失败，obedData参数：" + JSON.toJSON(obedData));
                    throw new Exception("删除学生信息失败！");
                }
            }
            //删除同行人员
            int deleteWa = welcomeAccompanyService.deleteWelcomeAccompanyByCard(ws.getCardId());
            //删除家庭成员
            int deletef = welcomeFamilyService.deleteWelcomeFamilyByCard(ws.getCardId());

        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new Exception("删除失败！");
        }

        return CommonResult.ok("删除成功");
    }

    @Override
    public CommonResult importStudentExcel(MultipartFile file) throws IOException, ParseException {
        System.out.println("导入学生信息");
        if (file.isEmpty() || file.getSize() == 0) {
            return CommonResult.fail("导入文件不能为空");
        }
        String ContentType = file.getContentType();
        InputStream inputStream = file.getInputStream();
        List<WelcomeStudent> result = new ArrayList<>();

        //xls格式文件
        if (ContentType.equals(eFileType.Xls.getValue())) {

            CommonResult<List<WelcomeStudent>> resultData = readXls(inputStream);
            if (!resultData.isSuccess()) {
                return resultData;
            }
            result = resultData.getData();

        } else if (ContentType.equals(eFileType.Xlsx.getValue())) {
            CommonResult<List<WelcomeStudent>> resultData = readXlsx(inputStream);
            if (!resultData.isSuccess()) {
                return resultData;
            }
            result = resultData.getData();

        } else {
            return CommonResult.fail("学生信息数据导入只支持Xls或Xlsx格式文件");
        }

        List<String> cardIds = result.stream().map(WelcomeStudent::getCardId).collect(Collectors.toList());
        List<WelcomeStudent> existStudents = welcomeStudentService.queryStudentByCardId(cardIds);
        if (existStudents != null && existStudents.size() > 0) {
            for (WelcomeStudent ws : result) {
                Optional<WelcomeStudent> ows = existStudents.stream().filter(e -> e.getCardId().equals(ws.getCardId())).findFirst();
                if (ows != null && ows.isPresent()) {
                    ws.setId(ws.getId());
                    ws.setAdmissNum(ws.getAdmissNum());//录取号
                    ws.setName(ws.getName());//姓名
                    ws.setCardId(ws.getCardId());//身份证号
                    ws.setSchool(ws.getSchool());//校区
                    ws.setCollege(ws.getCollege());
                    ws.setCollegeId(ws.getCollegeId());
                    ws.setMajor(ws.getMajor());
                    ws.setMajorId(ws.getMajorId());
                    ws.setClassstr(ws.getClassstr());
                    ws.setClassstrId(ws.getClassstrId());
                    ws.setExamNum(ws.getExamNum());//考生号
                    ws.setEduSystem(ws.getEduSystem());//学制
                    ws.setGraduationSchool(ws.getGraduationSchool());//毕业中学
                    ws.setBatchValue(ws.getBatchValue());//批次
                    ws.setPoliticalStatu(ws.getPoliticalStatu());//政治面貌
                    ws.setNationality(ws.getNationality());//民族
                    ws.setPhone(ws.getPhone());//手机号码
                    ws.setOprovince(ws.getOprovince());
                    ws.setOprovinceId(ws.getOprovinceId());
                    ws.setOcity(ws.getOcity());
                    ws.setOcityId(ws.getOcityId());
                    ws.setOdistrict(ws.getOdistrict());
                    ws.setOdistrictId(ws.getOdistrictId());
                    ws.setProvince(ws.getProvince());
                    ws.setProvinceId(ws.getProvinceId());
                    ws.setCity(ws.getCity());
                    ws.setCityId(ws.getCityId());
                    ws.setDistrict(ws.getDistrict());
                    ws.setDistrictId(ws.getDistrictId());
                    ws.setAddress(ws.getAddress());
                    ws.setZipCode(ws.getZipCode());
                }
            }
        }

        boolean resultBool = welcomeStudentService.saveOrUpdateBatch(result);

        System.out.println("导入学生1");
        return resultBool ? CommonResult.ok("导入成功") : CommonResult.fail("导入失败");
    }

    /**
     * xls文件读取方法
     *
     * @param inputStream
     * @return
     * @throws IOException
     * @throws ParseException
     */
    private CommonResult<List<WelcomeStudent>> readXls(InputStream inputStream) throws IOException, ParseException {
        List<WelcomeStudent> result = new ArrayList<>();
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);

        List<WelcomeOrg> ws = welcomeOrgService.list(null);
        List<WelcomeCity> citys = welcomeCityService.list(null);

        //读取第一张sheet
        HSSFSheet sheetAt = sheets.getSheetAt(0);
        DataFormatter dataFormatter = new DataFormatter();

        try {
            //rowNum = 3 从第三行开始获取值
            //sheetAt.getLastRowNum()：从0开始统计数量 所以得+1
            for (int rowNum = 0; rowNum < sheetAt.getLastRowNum() + 1; rowNum++) {

                HSSFRow row = sheetAt.getRow(rowNum);

                if (row != null) {
                    //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                    //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                    //然后poi会根据字符串读取它
                    //第一行数据获取月份
                    if (rowNum == 0) {
                        String number = dataFormatter.formatCellValue(row.getCell(0));//序号
                        if (!number.equals("序号")) {
                            return CommonResult.fail("导入数据第一列为序号");
                        }
                        String assNum = dataFormatter.formatCellValue(row.getCell(1));//录取号
                        if (!assNum.equals("录取号")) {
                            return CommonResult.fail("导入数据第二列为录取号");
                        }
                        String name = dataFormatter.formatCellValue(row.getCell(2));//姓名
                        if (!name.equals("姓名")) {
                            return CommonResult.fail("导入数据第三列为姓名");
                        }
                        String cardId = dataFormatter.formatCellValue(row.getCell(3));//身份证号
                        if (!cardId.equals("身份证号")) {
                            return CommonResult.fail("导入数据第四列为身份证号");
                        }
                        String school = dataFormatter.formatCellValue(row.getCell(4));//校区
                        if (!school.equals("校区")) {
                            return CommonResult.fail("导入数据第五列为校区");
                        }
                        String college = dataFormatter.formatCellValue(row.getCell(5));//院系
                        if (!college.equals("院系")) {
                            return CommonResult.fail("导入数据第六列为院系");
                        }
                        String major = dataFormatter.formatCellValue(row.getCell(6));//专业
                        if (!major.equals("专业")) {
                            return CommonResult.fail("导入数据第七列为专业");
                        }
                        String classstr = dataFormatter.formatCellValue(row.getCell(7));//班级
                        if (!classstr.equals("班级")) {
                            return CommonResult.fail("导入数据第八列为班级");
                        }
                        String examNum = dataFormatter.formatCellValue(row.getCell(8));//考生号
                        if (!examNum.equals("考生号")) {
                            return CommonResult.fail("导入数据第九列为考生号");
                        }
                        String eduSystem = dataFormatter.formatCellValue(row.getCell(9));//学制
                        if (!eduSystem.equals("学制")) {
                            return CommonResult.fail("导入数据第十列为学制");
                        }
                        String graSchool = dataFormatter.formatCellValue(row.getCell(10));//毕业中学
                        if (!graSchool.equals("毕业中学")) {
                            return CommonResult.fail("导入数据第十一列为毕业中学批次");
                        }
                        String batchValue = dataFormatter.formatCellValue(row.getCell(11));//批次
                        if (!batchValue.equals("批次")) {
                            return CommonResult.fail("导入数据第十一列为批次");
                        }
                        String politicalStatu = dataFormatter.formatCellValue(row.getCell(12));//政治面貌
                        if (!politicalStatu.equals("政治面貌")) {
                            return CommonResult.fail("导入数据第十一列为政治面貌");
                        }
                        String nationality = dataFormatter.formatCellValue(row.getCell(13));//民族
                        if (!nationality.equals("民族")) {
                            return CommonResult.fail("导入数据第十一列为民族");
                        }
                        String phone = dataFormatter.formatCellValue(row.getCell(14));//手机号码
                        if (!phone.equals("手机号码")) {
                            return CommonResult.fail("导入数据第十一列为手机号码");
                        }
                        String nativePlace = dataFormatter.formatCellValue(row.getCell(15));//籍贯
                        if (!nativePlace.equals("籍贯")) {
                            return CommonResult.fail("导入数据第十一列为籍贯");
                        }
                        String addressPlace = dataFormatter.formatCellValue(row.getCell(16));//家庭住址
                        if (!addressPlace.equals("家庭住址")) {
                            return CommonResult.fail("导入数据第十一列为家庭住址");
                        }
                        String addressDetail = dataFormatter.formatCellValue(row.getCell(17));//详细住址
                        if (!addressDetail.equals("详细住址")) {
                            return CommonResult.fail("导入数据第十一列为详细住址");
                        }
                        String zipCode = dataFormatter.formatCellValue(row.getCell(18));//邮编
                        if (!zipCode.equals("邮编")) {
                            return CommonResult.fail("导入数据第十一列为邮编");
                        }

                    } else {
                        WelcomeStudent studentData = new WelcomeStudent();

                        String assNum = dataFormatter.formatCellValue(row.getCell(1));//录取号
                        studentData.setAdmissNum(assNum);
                        String name = dataFormatter.formatCellValue(row.getCell(2));//姓名
                        studentData.setName(name);
                        String cardId = dataFormatter.formatCellValue(row.getCell(3));//身份证号
                        studentData.setCardId(cardId);

                        String school = dataFormatter.formatCellValue(row.getCell(4));//校区
                        studentData.setSchool(school);

                        //region 院系专业
                        String college = dataFormatter.formatCellValue(row.getCell(5));//院系
                        Optional<WelcomeOrg> owo = ws.stream().filter(e -> e.getName().equals(college)).findFirst();
                        if (owo != null && owo.isPresent()) {
                            studentData.setCollege(college);
                            studentData.setCollegeId(owo.get().getId());
                        }

                        String major = dataFormatter.formatCellValue(row.getCell(6));//专业
                        Optional<WelcomeOrg> omwo = ws.stream().filter(e -> e.getName().equals(major)).findFirst();
                        if (omwo != null && omwo.isPresent()) {
                            studentData.setMajor(major);
                            studentData.setMajorId(omwo.get().getId());
                        }

                        String classstr = dataFormatter.formatCellValue(row.getCell(7));//班级
                        Optional<WelcomeOrg> ocwo = ws.stream().filter(e -> e.getName().equals(classstr)).findFirst();
                        if (ocwo != null && ocwo.isPresent()) {
                            studentData.setClassstr(classstr);
                            studentData.setClassstrId(ocwo.get().getId());
                        }
                        //endregion

                        String examNum = dataFormatter.formatCellValue(row.getCell(8));//考生号
                        studentData.setExamNum(examNum);

                        String eduSystem = dataFormatter.formatCellValue(row.getCell(9));//学制
                        studentData.setEduSystem(eduSystem);

                        String graSchool = dataFormatter.formatCellValue(row.getCell(10));//毕业中学
                        studentData.setGraduationSchool(graSchool);

                        String batchValue = dataFormatter.formatCellValue(row.getCell(11));//批次
                        studentData.setBatchValue(batchValue);

                        String politicalStatu = dataFormatter.formatCellValue(row.getCell(12));//政治面貌
                        studentData.setPoliticalStatu(politicalStatu);

                        String nationality = dataFormatter.formatCellValue(row.getCell(13));//民族
                        studentData.setNationality(nationality);

                        String phone = dataFormatter.formatCellValue(row.getCell(14));//手机号码
                        studentData.setPhone(phone);

                        //region 籍贯
                        String nativePlace = dataFormatter.formatCellValue(row.getCell(15));//籍贯
                        String[] addressDatas = nativePlace.split("/");
                        if (citys != null && citys.size() > 0) {
                            if (addressDatas != null && addressDatas.length >= 1) {
                                if (!addressDatas[0].contains("省")) {
                                    return CommonResult.fail("录取编号为\" + assNum + \"的籍贯格式应该是 省/市/县");
                                }
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressDatas[0])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setOprovince(addressDatas[0]);
                                    studentData.setOprovinceId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的籍贯格式应该是 省/市/县");
                                }
                            }
                            if (addressDatas != null && addressDatas.length >= 2) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressDatas[1])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setOcity(addressDatas[1]);
                                    studentData.setOcityId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的籍贯格式应该是 省/市/县");
                                }
                            }
                            if (addressDatas != null && addressDatas.length >= 3) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getCityZh().equals(addressDatas[1]) && e.getDistrictZh().equals(addressDatas[2])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setOdistrict(addressDatas[2]);
                                    studentData.setOdistrictId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的籍贯格式应该是 省/市/县");
                                }
                            }
                        }
                        //endregion

                        //region 家庭住址
                        String addressPlace = dataFormatter.formatCellValue(row.getCell(16));//家庭住址
                        String[] addressPlaces = addressPlace.split("/");
                        if (citys != null && citys.size() > 0) {
                            if (addressPlaces != null && addressPlaces.length >= 1) {
                                if (!addressPlaces[0].contains("省")) {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressPlaces[0])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setProvince(addressPlaces[0]);
                                    studentData.setProvinceId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                            }
                            if (addressPlaces != null && addressPlaces.length >= 2) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressPlaces[1])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setCity(addressPlaces[1]);
                                    studentData.setCityId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                            }
                            if (addressPlaces != null && addressPlaces.length >= 3) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getCityZh().equals(addressPlaces[1]) && e.getDistrictZh().equals(addressPlaces[2])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setDistrict(addressPlaces[2]);
                                    studentData.setDistrictId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                            }
                        }
                        //endregion

                        String addressDetail = dataFormatter.formatCellValue(row.getCell(17));//详细住址
                        studentData.setAddress(addressDetail);

                        String zipCode = dataFormatter.formatCellValue(row.getCell(18));//邮编
                        studentData.setZipCode(zipCode);

                        result.add(studentData);
                    }
                }
            }
        } catch (Exception e) {
            return CommonResult.fail("请按模板格式导入数据");
        }

        return CommonResult.ok(result);
    }

    /**
     * xls文件读取方法
     *
     * @param inputStream
     * @return
     * @throws IOException
     * @throws ParseException
     */
    private CommonResult<List<WelcomeStudent>> readXlsx(InputStream inputStream) throws IOException, ParseException {
        List<WelcomeStudent> result = new ArrayList<>();
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);

        List<WelcomeOrg> ws = welcomeOrgService.list(null);
        List<WelcomeCity> citys = welcomeCityService.list(null);

        //读取第一张sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        DataFormatter dataFormatter = new DataFormatter();

        try {
            //rowNum = 3 从第三行开始获取值
            //sheetAt.getLastRowNum()：从0开始统计数量 所以得+1
            for (int rowNum = 0; rowNum < sheetAt.getLastRowNum() + 1; rowNum++) {

                XSSFRow row = sheetAt.getRow(rowNum);

                if (row != null) {
                    //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                    //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                    //然后poi会根据字符串读取它
                    //第一行数据获取月份
                    if (rowNum == 0) {
                        String number = dataFormatter.formatCellValue(row.getCell(0));//序号
                        if (!number.equals("序号")) {
                            return CommonResult.fail("导入数据第一列为序号");
                        }
                        String assNum = dataFormatter.formatCellValue(row.getCell(1));//录取号
                        if (!assNum.equals("录取号")) {
                            return CommonResult.fail("导入数据第二列为录取号");
                        }
                        String name = dataFormatter.formatCellValue(row.getCell(2));//姓名
                        if (!name.equals("姓名")) {
                            return CommonResult.fail("导入数据第三列为姓名");
                        }
                        String cardId = dataFormatter.formatCellValue(row.getCell(3));//身份证号
                        if (!cardId.equals("身份证号")) {
                            return CommonResult.fail("导入数据第四列为身份证号");
                        }
                        String school = dataFormatter.formatCellValue(row.getCell(4));//校区
                        if (!school.equals("校区")) {
                            return CommonResult.fail("导入数据第五列为校区");
                        }
                        String college = dataFormatter.formatCellValue(row.getCell(5));//院系
                        if (!college.equals("院系")) {
                            return CommonResult.fail("导入数据第六列为院系");
                        }
                        String major = dataFormatter.formatCellValue(row.getCell(6));//专业
                        if (!major.equals("专业")) {
                            return CommonResult.fail("导入数据第七列为专业");
                        }
                        String classstr = dataFormatter.formatCellValue(row.getCell(7));//班级
                        if (!classstr.equals("班级")) {
                            return CommonResult.fail("导入数据第八列为班级");
                        }
                        String examNum = dataFormatter.formatCellValue(row.getCell(8));//考生号
                        if (!examNum.equals("考生号")) {
                            return CommonResult.fail("导入数据第九列为考生号");
                        }
                        String eduSystem = dataFormatter.formatCellValue(row.getCell(9));//学制
                        if (!eduSystem.equals("学制")) {
                            return CommonResult.fail("导入数据第十列为学制");
                        }
                        String graSchool = dataFormatter.formatCellValue(row.getCell(10));//毕业中学
                        if (!graSchool.equals("毕业中学")) {
                            return CommonResult.fail("导入数据第十一列为毕业中学批次");
                        }
                        String batchValue = dataFormatter.formatCellValue(row.getCell(11));//批次
                        if (!batchValue.equals("批次")) {
                            return CommonResult.fail("导入数据第十一列为批次");
                        }
                        String politicalStatu = dataFormatter.formatCellValue(row.getCell(12));//政治面貌
                        if (!politicalStatu.equals("政治面貌")) {
                            return CommonResult.fail("导入数据第十一列为政治面貌");
                        }
                        String nationality = dataFormatter.formatCellValue(row.getCell(13));//民族
                        if (!nationality.equals("民族")) {
                            return CommonResult.fail("导入数据第十一列为民族");
                        }
                        String phone = dataFormatter.formatCellValue(row.getCell(14));//手机号码
                        if (!phone.equals("手机号码")) {
                            return CommonResult.fail("导入数据第十一列为手机号码");
                        }
                        String nativePlace = dataFormatter.formatCellValue(row.getCell(15));//籍贯
                        if (!nativePlace.equals("籍贯")) {
                            return CommonResult.fail("导入数据第十一列为籍贯");
                        }
                        String addressPlace = dataFormatter.formatCellValue(row.getCell(16));//家庭住址
                        if (!addressPlace.equals("家庭住址")) {
                            return CommonResult.fail("导入数据第十一列为家庭住址");
                        }
                        String addressDetail = dataFormatter.formatCellValue(row.getCell(17));//详细住址
                        if (!addressDetail.equals("详细住址")) {
                            return CommonResult.fail("导入数据第十一列为详细住址");
                        }
                        String zipCode = dataFormatter.formatCellValue(row.getCell(18));//邮编
                        if (!zipCode.equals("邮编")) {
                            return CommonResult.fail("导入数据第十一列为邮编");
                        }

                    } else {
                        WelcomeStudent studentData = new WelcomeStudent();

                        String assNum = dataFormatter.formatCellValue(row.getCell(1));//录取号
                        studentData.setAdmissNum(assNum);
                        String name = dataFormatter.formatCellValue(row.getCell(2));//姓名
                        studentData.setName(name);
                        String cardId = dataFormatter.formatCellValue(row.getCell(3));//身份证号
                        studentData.setCardId(cardId);

                        String school = dataFormatter.formatCellValue(row.getCell(4));//校区
                        studentData.setSchool(school);

                        //region 院系专业
                        String college = dataFormatter.formatCellValue(row.getCell(5));//院系
                        Optional<WelcomeOrg> owo = ws.stream().filter(e -> e.getName().equals(college)).findFirst();
                        if (owo != null && owo.isPresent()) {
                            studentData.setCollege(college);
                            studentData.setCollegeId(owo.get().getId());
                        }

                        String major = dataFormatter.formatCellValue(row.getCell(6));//专业
                        Optional<WelcomeOrg> omwo = ws.stream().filter(e -> e.getName().equals(major)).findFirst();
                        if (omwo != null && omwo.isPresent()) {
                            studentData.setMajor(major);
                            studentData.setMajorId(omwo.get().getId());
                        }

                        String classstr = dataFormatter.formatCellValue(row.getCell(7));//班级
                        Optional<WelcomeOrg> ocwo = ws.stream().filter(e -> e.getName().equals(classstr)).findFirst();
                        if (ocwo != null && ocwo.isPresent()) {
                            studentData.setClassstr(classstr);
                            studentData.setClassstrId(ocwo.get().getId());
                        }
                        //endregion

                        String examNum = dataFormatter.formatCellValue(row.getCell(8));//考生号
                        studentData.setExamNum(examNum);

                        String eduSystem = dataFormatter.formatCellValue(row.getCell(9));//学制
                        studentData.setEduSystem(eduSystem);

                        String graSchool = dataFormatter.formatCellValue(row.getCell(10));//毕业中学
                        studentData.setGraduationSchool(graSchool);

                        String batchValue = dataFormatter.formatCellValue(row.getCell(11));//批次
                        studentData.setBatchValue(batchValue);

                        String politicalStatu = dataFormatter.formatCellValue(row.getCell(12));//政治面貌
                        studentData.setPoliticalStatu(politicalStatu);

                        String nationality = dataFormatter.formatCellValue(row.getCell(13));//民族
                        studentData.setNationality(nationality);

                        String phone = dataFormatter.formatCellValue(row.getCell(14));//手机号码
                        studentData.setPhone(phone);

                        //region 籍贯
                        String nativePlace = dataFormatter.formatCellValue(row.getCell(15));//籍贯
                        String[] addressDatas = nativePlace.split("/");
                        if (citys != null && citys.size() > 0) {
                            if (addressDatas != null && addressDatas.length >= 1) {
                                if (!addressDatas[0].contains("省")) {
                                    return CommonResult.fail("录取编号为\" + assNum + \"的籍贯格式应该是 省/市/县");
                                }
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressDatas[0])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setOprovince(addressDatas[0]);
                                    studentData.setOprovinceId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的籍贯格式应该是 省/市/县");
                                }
                            }
                            if (addressDatas != null && addressDatas.length >= 2) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressDatas[1])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setOcity(addressDatas[1]);
                                    studentData.setOcityId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的籍贯格式应该是 省/市/县");
                                }
                            }
                            if (addressDatas != null && addressDatas.length >= 3) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getCityZh().equals(addressDatas[1]) && e.getDistrictZh().equals(addressDatas[2])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setOdistrict(addressDatas[2]);
                                    studentData.setOdistrictId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的籍贯格式应该是 省/市/县");
                                }
                            }
                        }
                        //endregion

                        //region 家庭住址
                        String addressPlace = dataFormatter.formatCellValue(row.getCell(16));//家庭住址
                        String[] addressPlaces = addressPlace.split("/");
                        if (citys != null && citys.size() > 0) {
                            if (addressPlaces != null && addressPlaces.length >= 1) {
                                if (!addressPlaces[0].contains("省")) {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressPlaces[0])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setProvince(addressPlaces[0]);
                                    studentData.setProvinceId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                            }
                            if (addressPlaces != null && addressPlaces.length >= 2) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getDistrictZh().equals(addressPlaces[1])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setCity(addressPlaces[1]);
                                    studentData.setCityId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                            }
                            if (addressPlaces != null && addressPlaces.length >= 3) {
                                Optional<WelcomeCity> oProvince = citys.stream().filter(e -> e.getCityZh().equals(addressPlaces[1]) && e.getDistrictZh().equals(addressPlaces[2])).findFirst();
                                if (oProvince != null && oProvince.isPresent()) {
                                    studentData.setDistrict(addressPlaces[2]);
                                    studentData.setDistrictId(oProvince.get().getId());
                                } else {
                                    return CommonResult.fail("录取编号为" + assNum + "的家庭住址格式应该是 省/市/县");
                                }
                            }
                        }
                        //endregion

                        String addressDetail = dataFormatter.formatCellValue(row.getCell(17));//详细住址
                        studentData.setAddress(addressDetail);

                        String zipCode = dataFormatter.formatCellValue(row.getCell(18));//邮编
                        studentData.setZipCode(zipCode);

                        result.add(studentData);
                    }
                }
            }
        } catch (Exception e) {
            return CommonResult.fail("请按模板格式导入数据");
        }
        return CommonResult.ok(result);
    }


    @Override
    public CommonResult downloadStudentExcel() {
        return CommonResult.ok("200", "操作成功", "https://chtech.ncjti.edu.cn/alumnus/homeimage/学生信息管理.xlsx");
    }

    @Override
    public void welcomeStudentExport(HttpServletResponse response, Integer collegeId, Integer majorId, Integer classstrId, String trafficMethod, String name) {
        List<StudentPageVo> result = welcomeStudentService.queryStudentList(collegeId, majorId, classstrId, trafficMethod, name);
        //导出
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("学生信息");

        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("序号");
        headerRow.createCell(1).setCellValue("录取号");
        headerRow.createCell(2).setCellValue("姓名");
        headerRow.createCell(3).setCellValue("身份证号");
        headerRow.createCell(4).setCellValue("院系");
        headerRow.createCell(5).setCellValue("专业");
        headerRow.createCell(6).setCellValue("班级");
        headerRow.createCell(7).setCellValue("交通方式");
        headerRow.createCell(8).setCellValue("到站地点");
        headerRow.createCell(9).setCellValue("陪同人数");
        headerRow.createCell(10).setCellValue("填报状态");
        headerRow.createCell(11).setCellValue("应缴金额");
        headerRow.createCell(12).setCellValue("实付金额");
        headerRow.createCell(13).setCellValue("宿舍信息");
        for (int i = 0; i < result.size(); i++) {
            StudentPageVo studentData = result.get(i);
            Row dataRow = sheet.createRow(i + 1);
            dataRow.createCell(0).setCellValue(i + 1);
            dataRow.createCell(1).setCellValue(studentData.getAdmissNum());
            dataRow.createCell(2).setCellValue(studentData.getName());
            dataRow.createCell(3).setCellValue(studentData.getCardId());
            dataRow.createCell(4).setCellValue(studentData.getCollege());
            dataRow.createCell(5).setCellValue(studentData.getMajor());
            dataRow.createCell(6).setCellValue(studentData.getClassstr());
            dataRow.createCell(7).setCellValue(studentData.getTrafficMethod());
            dataRow.createCell(8).setCellValue(studentData.getArrive());
            dataRow.createCell(9).setCellValue(studentData.getAccompanyNum());
            dataRow.createCell(10).setCellValue(studentData.getFillStatus());
            dataRow.createCell(11).setCellValue(String.valueOf(studentData.getAmountPayable()));
            dataRow.createCell(12).setCellValue(String.valueOf(studentData.getPayAmount()));
            dataRow.createCell(13).setCellValue(studentData.getDormitory());
        }
        // 将工作簿写入文件
        ExcelUtils.excelDownload(workbook, "学生信息.xlsx", response);
    }

    @Override
    public CommonResult studentOverview() {
//        报到总数
        int studentTotal = welcomeStudentService.count(new QueryWrapper<>());
//        寝室总数
        int dormitoryTotal = welcomeDormitoryService.count(new QueryWrapper<>());

//        已入住寝室数
        LambdaQueryWrapper<WelcomeDormitory> wrapper=new LambdaQueryWrapper<>();
        wrapper.ne(WelcomeDormitory::getStatus,2);
        int count = welcomeDormitoryService.count(wrapper);

        LocalDateTime start = LocalDateTime.now().withHour(0).withMinute(0).withSecond(0);
        LocalDateTime end = start.plusDays(1);
        DateTimeFormatter dateTimeFormatter1 = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        Integer visitorTotal=welcomeVisitorService.countTotal(start,end);

        StudentOverviewVo vo = new StudentOverviewVo();
        vo.setStudentTotal(studentTotal);
        vo.setDormitoryTotal(dormitoryTotal);
        vo.setCheckInTotal(count);
        vo.setVisitorTotal(visitorTotal);

        return CommonResult.ok(vo);
    }


    @Override
    public CommonResult studentRegister() {

//        获取总数
        Integer count = welcomeStudentService.totalCount();
        List<StudentRegisterVo> vos = welcomeStudentService.studentRegister();
        StudentRegisterVo vo = new StudentRegisterVo();
        vo.setCollegeName("报到总数");
        vo.setCount(count);
        vos.add(vo);
        return CommonResult.ok(vos);
    }

    @Override
    public CommonResult studentTraffic() {
        List<StudentTrafficVo> vos = welcomeStudentService.studentTraffic();
        return CommonResult.ok(vos);
    }

    @Override
    public CommonResult studentSexRatio() {

        Integer manCount = welcomeStudentService.genderCount("男");
        Integer girlCount = welcomeStudentService.genderCount("女");
        StudentSexRatioVo vo = new StudentSexRatioVo();
        vo.setCollegeName("全部");
        vo.setManCount(manCount);
        vo.setGirlCount(girlCount);

        List<StudentSexRatioVo> vos = welcomeStudentService.studentSexRatio();
        vos.add(vo);

        return CommonResult.ok(vos);
    }

    @Override
    public CommonResult studentStay() {
        ArrayList<StudentStayVo> vos = new ArrayList<>();
        List<WelcomeSchool> list = welcomeSchoolService.list(new QueryWrapper<>());
        for (WelcomeSchool welcomeSchool : list) {
            Integer schoolId = welcomeSchool.getId();
            List<WelcomeBuild> builds=welcomeBuildService.getBuild(schoolId);
            for (WelcomeBuild build : builds) {
                Integer buildId = build.getId();
                StudentStayVo vo =welcomeDormitoryService.getTotalCount(buildId);
                vo.setSchoolName(welcomeSchool.getSchool());
                vo.setBuildName(build.getBuild());
                vos.add(vo);
            }
        }

        return CommonResult.ok(vos);
    }
}