package com.yxproject.start.api;

import com.yxproject.start.entity.*;
import com.yxproject.start.service.*;
import com.yxproject.start.utils.YXJSONResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.IncorrectCredentialsException;
import org.apache.shiro.authc.UnknownAccountException;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.apache.shiro.subject.Subject;
import org.dom4j.*;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.*;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

/**
 * @author Administrator
 */
@RestController
@RequestMapping("user")
public class UserApi {
    @Autowired
    private TaskService taskService;
    @Autowired
    private GroupNoService groupNoService;
    @Autowired
    private PoliceStationApplyReasonService policeStationApplyReasonService;
    @Autowired
    private FailedCardService failedCardService;
    @Autowired
    private CountyListService countyListService;
    @Autowired
    private CardBodyService cardBodyService;
    @Autowired
    private GroupListService groupListService;
    @Autowired
    private CityListService cityListService;
    @Autowired
    private CountyService countyService;
    @Autowired
    private TypeListService typeListService;

//    @Autowired
//    private ReadCountyListXMLService readCountyListXMLService;
//    @Autowired
//    private PrintCountyListXMLService printCountyListXMLService;
//    @Autowired
//    private static Dom4JCRUD dom4JCRUD;

    @PostMapping("login")
    public Map<String, Object> submitLogin(@RequestBody String jsonStr) {
        JSONObject jsonObject = JSONObject.fromObject(jsonStr);
        String username = jsonObject.getString("username");
        String password = jsonObject.getString("password");
        Map<String, Object> resultMap = new LinkedHashMap<String, Object>();
        Subject currentUser = SecurityUtils.getSubject();
        // 把用户名和密码封装为 UsernamePasswordToken 对象
        UsernamePasswordToken token = new UsernamePasswordToken(username, password);
        try {
            currentUser.login(token);
            UserInfo userInfo = (UserInfo) SecurityUtils.getSubject().getPrincipal();
            resultMap.put("status", 200);
            resultMap.put("message", "登录成功");
            resultMap.put("user", userInfo);
        } catch (UnknownAccountException e) {
            resultMap.put("status", 201);
            resultMap.put("message", "账号不存在！");
        } catch (IncorrectCredentialsException e1) {
            resultMap.put("status", 202);
            resultMap.put("message", "密码错误！");
        } catch (Exception e) {
            resultMap.put("status", 500);
            resultMap.put("message", "用户名密码错误");
        }
        return resultMap;
    }

    @RequestMapping("logout")
    public void logout() {
        Subject subject = SecurityUtils.getSubject();
        if (subject.isAuthenticated()) {
            subject.logout();
        }
    }

    /**
     * 查询任务单;
     *
     * @return
     */
    @RequestMapping(value = "/getProductionTaskListByID", method = RequestMethod.GET)
    @ResponseBody
    public String getProductionTaskListByID(@RequestParam("id") String id, HttpServletResponse resp) {

        List<TaskEntity> productionTaskListEntity = taskService.findProductionTaskListEntityByID(Long.valueOf(id));
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(productionTaskListEntity);
        Map map = new HashMap();
        System.out.println(yxjsonResponse.toJSONString()+"--------");
//        map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }


    /**
     * 更新任务单;
     *
     * @return
     */
    @RequestMapping(value = "/updateProductionTask", method = RequestMethod.GET)
    @RequiresPermissions("userInfo.add")//权限管理;
    @ResponseBody
    public String updateProductionTask(@RequestParam("id") String id, HttpServletResponse resp) {
        String map = "{\"production_Task_List_Id\":\"20181016001\",\"make_Type\":8,\"old_Make_Type\":8,} ";
        JSONObject jsonObject = JSONObject.fromObject(map);
        TaskEntity productionTaskListEntity = (TaskEntity) JSONObject.toBean(jsonObject, TaskEntity.class);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = taskService.updateProductionTask(productionTaskListEntity);
        yxjsonResponse.outPutSuccess(i + "更新成功");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 保存任务单;
     *
     * @return
     */
    @RequestMapping(value = "/addProductionTaskList", method = RequestMethod.GET)
    @RequiresPermissions("userInfo.add")//权限管理;
    @ResponseBody
    public String addProductionTaskList(@RequestParam("id") String id, HttpServletResponse resp) {
        String map = "{\"productionTaskListId\":\"20181016001\",\"makeType\":4,\"oldMakeType\":7}";
        JSONObject jsonObject = JSONObject.fromObject(map);
        Object taskList = jsonObject.get("taskList");
        Object groupInfoList = jsonObject.get("groupInfoList");
        List<GroupNoEntity> groupNoEntities = (List<GroupNoEntity>) groupInfoList;
        TaskEntity taskEntity = (TaskEntity) taskList;
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = taskService.addProductionTaskListEntity(taskEntity, groupNoEntities);
        yxjsonResponse.outPutSuccess(i + "添加成功");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询证件信息;
     *
     * @return
     */
    @RequestMapping(value = "/findCardInfoByCardIDOrAcceptNo", method = RequestMethod.GET)
    @RequiresPermissions("userInfo.add")//权限管理;
    @ResponseBody
    public String findCardInfoByCardIDOrAcceptNo(@RequestParam("id") String id, HttpServletResponse resp) {

        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        List<Object> cardInfoByCardIDOrAcceptNo = taskService.findCardInfoByCardIDOrAcceptNo(id);
        yxjsonResponse.outPutSuccess(cardInfoByCardIDOrAcceptNo);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 添加快证任务单;
     *
     * @return
     */
    @RequestMapping(value = "/addQuickCyclesheetInfo", method = RequestMethod.GET)
    @RequiresPermissions("userInfo.add")//权限管理;
    @ResponseBody
    public String addQuickCyclesheetInfo(@RequestParam("id") String id, HttpServletResponse resp) {

        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = taskService.addQuickCyclesheetInfo(id);
        yxjsonResponse.outPutSuccess(i + "添加成功");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 保存派出所照片质量详情;
     *
     * @return
     */
    @RequestMapping(value = "/savePoliceStationVailedInfoEntity", method = RequestMethod.GET)
    @RequiresPermissions("userInfo.add")//权限管理;
    @ResponseBody
    public String savePoliceStationVailedInfoEntity(@RequestParam("id") String id, HttpServletResponse resp) {
        String map = "[{\"cyclesheetid\":\"20181016001\",\"invalid_Count\":1,\"police_Station_Code\":\"41108201\",\"police_Station_Vailed_Info_Id\":\"1\",\"sava_Date\":\"20181025\",\"vailed_Count\":1}]";
        JSONArray jsonArray = JSONArray.fromObject(map);
        List<PoliceStationVailedEntity> policeStationVailedInfoEntities = new ArrayList<>();
        for (Object object : jsonArray) {
            PoliceStationVailedEntity o = (PoliceStationVailedEntity) JSONObject.toBean((JSONObject) object, PoliceStationVailedEntity.class);
            policeStationVailedInfoEntities.add(o);
        }
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = taskService.savePoliceStationVailedEntity(policeStationVailedInfoEntities);
        yxjsonResponse.outPutSuccess(i + "添加成功");
        return yxjsonResponse.toJSONString();
    }


    /**
     * test
     *
     * @return
     */
    @RequestMapping("/test")
    @RequiresPermissions("userInfo.add")//权限管理;
    @ResponseBody
    public String test(@RequestParam("id") String permissionId, HttpServletResponse resp) {
        //TODO
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        List<PoliceStationVailedEntity> policeStationVailedEntities = taskService.find(permissionId);
        yxjsonResponse.outPutSuccess(policeStationVailedEntities);
        System.out.println(yxjsonResponse);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 保存废证详情;
     *
     * @return
     */
    @RequestMapping("addFailedinfo")
    public String addFailedinfo(@RequestParam("id") String id, HttpServletResponse resp) {
        String map = "[{\"failedinfoid\":\"20181016002\",\"failed_Reason\":1,\"groupno\":\"41108201\",\"cyclesheetid\":\"20181016001\"}]";
        JSONArray jsonArray = JSONArray.fromObject(map);
        List<FailedCardEntity> failedinfoEntityList = new ArrayList<>();
        for (Object object : jsonArray) {
            FailedCardEntity o = (FailedCardEntity) JSONObject.toBean((JSONObject) object, FailedCardEntity.class);
            failedinfoEntityList.add(o);
        }
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = failedCardService.saveFailedinfo(failedinfoEntityList);
        yxjsonResponse.outPutSuccess(i + "添加成功");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 更新废证详情;
     *
     * @return
     */
    @RequestMapping("updateFailedinfo")
    @RequiresPermissions("userInfo.add")//权限管理;
    public String updateFailedinfo(@RequestParam("id") String id, HttpServletResponse resp) {
        String map = "[{\"failedinfoid\":\"201810302\",\"failed_Reason\":1,\"groupno\":\"411081\",\"cyclesheetid\":\"20181016001\"}]";
        JSONArray jsonArray = JSONArray.fromObject(map);
        List<FailedCardEntity> failedinfoEntityList = new ArrayList<>();
        for (Object object : jsonArray) {
            FailedCardEntity o = (FailedCardEntity) JSONObject.toBean((JSONObject) object, FailedCardEntity.class);
            failedinfoEntityList.add(o);
        }
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = failedCardService.updateFailedinfo(failedinfoEntityList);
        yxjsonResponse.outPutSuccess(i + "更新成功");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询废证详情;
     *
     * @return
     */
    @RequestMapping("findFailedinfo")
    @RequiresPermissions("userInfo.add")//权限管理;
    public String findFailedinfo(@RequestParam("id") String id, HttpServletResponse resp) {
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        List<Map<String, Object>> maps = failedCardService.selectFailedinfo(Integer.valueOf(id));
        yxjsonResponse.outPutSuccess(maps + "------添加成功---" + maps.size());
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询任务单详情;
     *
     * @return
     */
    @RequestMapping("findProductionTaskListByState")
    @RequiresPermissions("userInfo.add")//权限管理;
    public String findProductionTaskListByState(@RequestParam("state") String state, HttpServletResponse resp) {
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        List<Map<String, Object>> productionTaskInfoList = taskService.findProductionTaskListEntityByState(Integer.valueOf(state));
        yxjsonResponse.outPutSuccess(productionTaskInfoList);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询区县列表详情;
     *
     * @return
     */
    @RequestMapping("findProdCountyList")
    @RequiresPermissions("userInfo.add")//权限管理;
    public String findProdCountyList(@RequestParam("cyclesheetID") String cyclesheetID, HttpServletResponse resp) {
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        List<Map<String, Object>> countyInfoList = groupNoService.findCountyInfoList(cyclesheetID);
        yxjsonResponse.outPutSuccess(countyInfoList);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 保存派出所申请类型数量;
     *
     * @return
     */
    @RequestMapping("savePoliceApplyCount")
    @RequiresPermissions("userInfo.add")//权限管理;
    public String savePoliceApplyCount(@RequestParam("cyclesheetID") String cyclesheetID, HttpServletResponse resp) {
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = policeStationApplyReasonService.savePoliceStationApplyReasonEntity(cyclesheetID);
        yxjsonResponse.outPutSuccess(i + "");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 通过任务单查询区县列表;
     *
     * @return
     */
    @RequestMapping("getCountyList")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getCountyListInfoByTaskListID(@RequestParam("taskListId") String taskListId,@RequestParam("taskStateId") String taskStateId,@RequestParam("cardType") String cardType, HttpServletResponse resp) {
        List<CountyEntity> countyListEntity =  countyService.findCountyList(taskListId,taskStateId,cardType);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(countyListEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 通过任务单查询组列表;
     *
     * @return
     */
    @RequestMapping("getGroupListByTaskListId")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getGroupListByTaskListId(@RequestParam("taskListId") String taskListId, HttpServletResponse resp) {
        List<GroupListEntity> groupNoEntity =  groupListService.findGroupListByTaskListId(taskListId);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(groupNoEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 通过状态查询组列表;
     *
     * @returns
     */
    @RequestMapping("getGroupNoByTaskStateId")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String findGroupListByTaskStateId(@RequestParam("taskStateId") String taskStateId, HttpServletResponse resp) {
        List<GroupListEntity> groupNoEntity =  groupListService.findGroupListByTaskStateId(taskStateId);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(groupNoEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 通过制证类型查询组列表;
     *
     * @returns
     */
    @RequestMapping("getGroupNoByCardType")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getGroupListByCardType(@RequestParam("cardType") String cardType, HttpServletResponse resp) {
        List<GroupListEntity> groupNoEntity =  groupListService.findGroupListByCardType(cardType);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(groupNoEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询TypeList通过状态;
     *
     * @return
     */
    @RequestMapping("getTypeListByTaskStateId")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getTypeListByTaskStateId(@RequestParam("taskStateId") String taskStateId, HttpServletResponse resp) {
        List<TypeListEntity> typeListEntity = typeListService.findTypeListByTaskStateId(taskStateId);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(typeListEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询CityList通过状态;
     *
     * @return
     */
    @RequestMapping("getCityList")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getCityListByTaskStateId(@RequestParam("taskStateId") String taskStateId,@RequestParam("cardType") String cardType, HttpServletResponse resp) {
        List <CityListEntity> cityListEntity = cityListService.findCityList(taskStateId,cardType);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(cityListEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }


    /**
     * 查询派出所申领表通过保存时间;
     *
     * @return
     */
    @RequestMapping("getPoliceStationApplyReasonBySaveDate")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getPoliceStationApplyReasonBySaveDate(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, HttpServletResponse resp) {
        List <Map <String, Object>> policeStationApplyReasonEntity = policeStationApplyReasonService.findPoliceStationApplyReasonBySaveDate(startDate,endDate);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(policeStationApplyReasonEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询派出所申领表通过制证类型;
     *
     * @return
     */
    @RequestMapping("getPoliceStationApplyReasonByCardType")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getPoliceStationApplyReasonByCardType(@RequestParam("cardType") String cardType, HttpServletResponse resp) {
        List <Map <String, Object>> policeStationApplyReasonEntity = policeStationApplyReasonService.findPoliceStationApplyReasonByCardType(cardType);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(policeStationApplyReasonEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询派出所申领表通过地市,区县,派出所;
     *
     * @return
     */
    @RequestMapping("getPoliceStationApplyReasonByCityCode")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getPoliceStationApplyReasonByCity(@RequestParam("cityCode") String cityCode, HttpServletResponse resp) {
        List <Map <String, Object>> policeStationApplyReasonEntity = policeStationApplyReasonService.findPoliceStationApplyReasonByCity(cityCode);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(policeStationApplyReasonEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询出入库数据;
     *
     * @return
     */
    @RequestMapping("getStorageCountByTaskListId")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getStorageCountByTaskListId(@RequestParam("taskListID") String taskListId, HttpServletResponse resp) {
        List <CountyListEntity> countyListEntity=countyListService.findStorageCountByTaskListId(taskListId);
        YXJSONResponse yxjsonResponse=new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(countyListEntity);
        Map map=new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 查询卡基表;
     *
     * @return
     */
    @RequestMapping("getCardBody")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String insertCardBody(@RequestParam("cardBodyId") String cardbodyid, HttpServletResponse resp) {
        List<CardBodyEntity> cardBodyEntity =  cardBodyService.insertCardBody(cardbodyid);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(cardBodyEntity);
        Map map = new HashMap();
        return yxjsonResponse.toJSONString();
    }

    /**
     * 更新循环单
     * 添加异常状态;
     * @return
     */
    @RequestMapping("addExceptionState")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String addExceptionState(@RequestParam("exception_Information") String exception_Information, HttpServletResponse resp) {
        String map = "{\"task_Id\":\"20181016001\",\"exception_Information\":0} ";
        JSONObject jsonObject = JSONObject.fromObject(map);
        TaskEntity taskEntity = (TaskEntity) JSONObject.toBean(jsonObject, TaskEntity.class);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        int i = taskService.addExceptionState(taskEntity);
        yxjsonResponse.outPutSuccess(i + "更新成功");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 对出入库数进行更改
     *
     * @return
     */
    @RequestMapping("reviseBoundCount")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String reviseBoundCount(@RequestParam("countyListId") String countyListId,@RequestParam("outStorageCount") String out_Storage_Count,@RequestParam("inStorageCount") String in_Storage_Count, HttpServletResponse resp) {
        int i=countyListService.reviseBoundCount(countyListId, out_Storage_Count, in_Storage_Count);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(i+"--更新成功--");
        return yxjsonResponse.toJSONString();
    }

    /**
     * 更新入库时间
     * 前端空值
     * @return
     */
    @RequestMapping("updateInStorageDate")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String updateInStorageDate(@RequestParam("taskListId") String taskListId,@RequestParam("inStorageCount") String inStorageCount,@RequestParam("outStorageCount") String outStorageCount, HttpServletResponse resp) {
        int i = taskService.updateStorageDate(taskListId,inStorageCount,outStorageCount);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(i + "更新成功");
        return yxjsonResponse.toJSONString();
    }

//    /**
//     * 更新出库时间
//     * @return
//     */
//    @RequestMapping("updateOutBoundDate")
////    @RequiresPermissions("userInfo.add")//权限管理;
//    public String updateOutBoundDate(@RequestParam("id") String id,  HttpServletResponse resp) {
//        String map = "{\"id\":\"20181016001\",} ";
//        JSONObject jsonObject = JSONObject.fromObject(map);
//        Object outStorageCount = jsonObject.get("outStorageCount");
//        Object putOutStorageDate = jsonObject.get("putOutStorageDate");
//        TaskEntity taskEntity = (TaskEntity) putOutStorageDate;
//        CountyListEntity countyListEntity = (CountyListEntity) outStorageCount;
//        YXJSONResponse yxjsonResponse = new YXJSONResponse();
//        resp.setCharacterEncoding("UTF-8");
//        int i = taskService.updateOutBoundDate(taskEntity,countyListEntity);
//        yxjsonResponse.outPutSuccess(i + "更新成功");
//        return yxjsonResponse.toJSONString();
//    }

    /**
     * 查询交接单;
     *
     * @return
     */
    @RequestMapping("getConnectList")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public String getConnectList(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, HttpServletResponse resp) {
        List<CountyListEntity> countyListEntity =  countyListService.getConnectList(startDate,endDate);
        YXJSONResponse yxjsonResponse = new YXJSONResponse();
        resp.setCharacterEncoding("UTF-8");
        yxjsonResponse.outPutSuccess(countyListEntity);
        Map map = new HashMap();
//      map.put("MakeType",productionTaskListEntity);
        return yxjsonResponse.toJSONString();
    }

    /**
     * 下载装箱单
     */
    @RequestMapping("printPackingList")
//    @RequiresPermissions("userInfo.add")//权限管理;
    public byte[] printPackingList(@RequestParam("cyclesheetID") String taskID, HttpServletResponse response) {
//        cyclesheetID ="20180802032";
        response.setContentType("application/x-download");
        response.setCharacterEncoding("UTF-8");
        String dateTime = DateFormatUtils.format(new Date(), "yyyyMMddHH");
//        //使用Servlet实现文件下载的时候，避免浏览器自动打开文件
        String fout = null;
        List<Map<String, Object>> countyInfoList = groupNoService.findCountyInfoList(taskID);
        List<TaskEntity> taskEntities = taskService.findProductionTaskListEntityByID(Long.valueOf(taskID));
        fout = exportExcel(countyInfoList, taskEntities.get(0).getCard_Type() + "", "p1", 6000, taskEntities.get(0).getCitycode(), dateTime, taskID);
        String outFile = dateTime + taskEntities.get(0).getCitycode() + "二代身份证统计表";
        try {
            FileInputStream fis = new FileInputStream(new File(fout));
            byte[] b = new byte[fis.available()];
            fis.read(b);
            fis.close();
            response.addHeader("Content-Disposition", "attachment;filename=" + outFile + ".xls");
            return b;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

//    /**
//     * 下载交接单
//     */
//    @RequestMapping("printConnectList")
////    @RequiresPermissions("userInfo.add")//权限管理;
//    public byte[] printConnectList(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, HttpServletResponse response) {
////        cyclesheetID ="20180802032";
//        response.setContentType("application/x-download");
//        response.setCharacterEncoding("UTF-8");
//        String dateTime = DateFormatUtils.format(new Date(), "yyyyMMddHH");
////        //使用Servlet实现文件下载的时候，避免浏览器自动打开文件
//        String fout = null;
//        List <CountyListEntity> countyListEntity =  countyListService.getConnectList(startDate,endDate);
//        fout = connectListExcel(countyListEntity, countyListEntity.get(0).getOut_Storage_Count() + "", 6000, countyListEntity.get(0).getCounty_Code(), dateTime, startDate, endDate);
//        String outFile = dateTime + countyListEntity.get(0).getCounty_Code() + "二代身份证交接单";
//        try {
//            FileInputStream fis = new FileInputStream(new File(fout));
//            byte[] b = new byte[fis.available()];
//            fis.read(b);
//            fis.close();
//            response.addHeader("Content-Disposition", "attachment;filename=" + outFile + ".xls");
//            return b;
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }
//
//    /**
//     * 下载交接单
//     *
//     * @param countyListEntity  交接单信息
//     * @param typeName           制证类型
//     * @param sum                 总数
//     * @param countyCode        区县代码
//     */
//    private String connectListExcel( List <CountyListEntity> countyListEntity, String typeName, int sum, String countyCode, String dateTime, String startDate ,String endDate) {
//        if (typeName.contains("null")) {
//            typeName = typeName.replace("null", "");
//        }
//        //第一步创建workbook
//        HSSFWorkbook wb = new HSSFWorkbook();
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//        HSSFSheet sheet = wb.createSheet("二代身份证交接单");
//        sheet.setColumnWidth(0, 30 * 100);
//        sheet.setColumnWidth(1, 30 * 70);
//        sheet.setColumnWidth(2, 30 * 80);
//        sheet.setColumnWidth(3, 30 * 50);
//        sheet.setColumnWidth(4, 30 * 110);
//
//        HSSFPrintSetup ps = sheet.getPrintSetup();
//        ps.setLandscape(true); // 打印方向，true：横向，false：纵向
//        ps.setPaperSize(HSSFPrintSetup.B5_PAPERSIZE); //纸张
//        sheet.setColumnWidth(0, (int) 35.7 * 100);
//        //第三步创建行row:添加表头0行
//        HSSFRow row = sheet.createRow(0);
//        HSSFCellStyle style = wb.createCellStyle();//样式
//
//        style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);    //设置垂直居中
//        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//        style.setWrapText(true);//设置自动换行
//
//        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
//        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
//        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//
//        HSSFFont font = wb.createFont();
//        font.setFontHeightInPoints((short) 12);
//        style.setFont(font);
//        HSSFCellStyle style2 = wb.createCellStyle(); //表头样式
//
//        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    //设置垂直居中
//        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//
//        HSSFFont font2 = wb.createFont();
//        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
//        font2.setFontHeightInPoints((short) 14);
//        style2.setFont(font2);
//
//        row = sheet.createRow(0);                     //创建下标为0的单元格
//        row.setHeightInPoints(Short.parseShort("20"));//设置行高
//        HSSFCell cell = row.createCell(0);         //设定值
//        cell.setCellValue("二代身份证交接单");
//        cell.setCellStyle(style2);
//        for (int i = 0; i < 12; i++) {
//            cell = row.createCell(i + 1);
//            cell.setCellValue("");
//            cell.setCellStyle(style2);
//        }
//        CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, 12);//起始行,结束行,起始列,结束列
//        sheet.addMergedRegion(callRangeAddress);
//
//        row = sheet.createRow(1);
//        row.setHeightInPoints(Short.parseShort("16"));//设置行高
//
//        cell = row.createCell(0);
//        cell.setCellValue("区县");                  //设定值
//        cell.setCellStyle(style);                   //内容居中
//
//        cell = row.createCell(1);                   //第二个单元格
//        cell.setCellValue("普通证");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(2);                   //第三个单元格
//        cell.setCellValue("快证普");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(3);                   //第四个单元格
//        cell.setCellValue("KS普");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(4);                   //第五个单元格
//        cell.setCellValue("异地KS普");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(5);                   //第六个单元格
//        cell.setCellValue("异地普");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(6);                   //第七个单元格
//        cell.setCellValue("全国证");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(7);                   //第八个单元格
//        cell.setCellValue("军人证");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(8);                   //第九个单元格
//        cell.setCellValue("港澳");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(9);                   //第十个单元格
//        cell.setCellValue("台湾");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(10);                   //第十一个单元格
//        cell.setCellValue("小计");
//        cell.setCellStyle(style);
//
//        cell = row.createCell(11);                   //第十二个单元格
//        cell.setCellValue("盒数");
//        cell.setCellStyle(style);
//
//        row = sheet.createRow(2);//i+2行之后开始创建
//        row.setHeightInPoints(Short.parseShort("16"));//设置行高
//        //创建单元格并且添加数据
//        cell = row.createCell(0);
//        cell.setCellValue(countyCode);
//        cell.setCellStyle(style);
//        for (int i = 0; i < 12; i++) {
//            cell = row.createCell(i + 1);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//        }
//
//        //第五步插入数据
//        int num = 0;
//        for (int i = 0; i < countyListEntity.size(); i++) {
//            Map<String, Object> map =(Map <String, Object>) countyListEntity.get(i);
//            //创建行
//            row = sheet.createRow(i + 3);//i+2行之后开始创建
//            row.setHeightInPoints(Short.parseShort("16"));//设置行高
//            //创建单元格并且添加数据
//            cell = row.createCell(0);
//            cell.setCellValue((String) map.get("COUNTYNAME"));
//            cell.setCellStyle(style);
//
//            cell = row.createCell(1);
//            cell.setCellValue(map.get("DOWNLOAD").toString().equals("0") ? "" : map.get("DOWNLOAD").toString());
//            cell.setCellStyle(style);
//
//            cell = row.createCell(2);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(3);
//            cell.setCellValue(((Integer.parseInt(map.get("DOWNLOAD").toString()) % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 : Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 + 1)) + "");
//            num += ((Integer.parseInt(map.get("DOWNLOAD").toString()) % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 : Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 + 1));
//            cell.setCellStyle(style);
//
//            cell = row.createCell(4);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(5);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(6);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(7);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(8);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(9);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(10);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//            cell = row.createCell(11);
//            cell.setCellValue("");
//            cell.setCellStyle(style);
//
//        }
//    }

    /**
     * 下载装箱单
     *
     * @param countyInfoList 装箱单信息
     * @param typeName       制证类型
     * @param workShop       制证车间
     * @param sum            总数
     * @param cityName       地市
     */
    private String exportExcel(List<Map<String, Object>> countyInfoList, String typeName, String workShop, int sum, String cityName, String permanentPositionDate, String cyclesheetid) {
        if (typeName.contains("null")) {
            typeName = typeName.replace("null", "");
        }
        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        HSSFSheet sheet = wb.createSheet("二代身份证统计表");
        sheet.setColumnWidth(0, 30 * 100);
        sheet.setColumnWidth(1, 30 * 70);
        sheet.setColumnWidth(2, 30 * 80);
        sheet.setColumnWidth(3, 30 * 50);
        sheet.setColumnWidth(4, 30 * 110);

        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true); // 打印方向，true：横向，false：纵向
        ps.setPaperSize(HSSFPrintSetup.B5_PAPERSIZE); //纸张
        sheet.setColumnWidth(0, (int) 35.7 * 100);
        //第三步创建行row:添加表头0行
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();//样式

        style.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);    //设置垂直居中
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setWrapText(true);//设置自动换行

        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        HSSFCellStyle style2 = wb.createCellStyle(); //表头样式

        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    //设置垂直居中
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFFont font2 = wb.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        font2.setFontHeightInPoints((short) 14);
        style2.setFont(font2);

        row = sheet.createRow(0);                     //创建下标为0的单元格
        row.setHeightInPoints(Short.parseShort("20"));//设置行高
        HSSFCell cell = row.createCell(0);         //设定值
        cell.setCellValue("二代身份证统计表");
        cell.setCellStyle(style2);
        for (int i = 0; i < 4; i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue("");
            cell.setCellStyle(style2);
        }
        CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, 4);//起始行,结束行,起始列,结束列
        sheet.addMergedRegion(callRangeAddress);

        row = sheet.createRow(1);
        row.setHeightInPoints(Short.parseShort("16"));//设置行高
        cell = row.createCell(0);
        cell.setCellValue("单位");                  //设定值
        cell.setCellStyle(style);                   //内容居中

        cell = row.createCell(1);                   //第二个单元格
        cell.setCellValue("数量");
        cell.setCellStyle(style);

        cell = row.createCell(2);                   //第三个单元格
        cell.setCellValue("质检号");
        cell.setCellStyle(style);

        cell = row.createCell(3);                   //第四个单元格
        cell.setCellValue("盒数");
        cell.setCellStyle(style);

        cell = row.createCell(4);                   //第五个单元格
        cell.setCellValue("入库日期");
        cell.setCellStyle(style);
        row = sheet.createRow(2);//i+2行之后开始创建
        row.setHeightInPoints(Short.parseShort("16"));//设置行高
        //创建单元格并且添加数据
        cell = row.createCell(0);
        cell.setCellValue(cityName);
        cell.setCellStyle(style);
        for (int i = 0; i < 4; i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue("");
            cell.setCellStyle(style);
        }

        //第五步插入数据
        int num = 0;
        for (int i = 0; i < countyInfoList.size(); i++) {
            Map<String, Object> map = countyInfoList.get(i);
            //创建行
            row = sheet.createRow(i + 3);//i+2行之后开始创建
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue((String) map.get("COUNTYNAME"));
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(map.get("DOWNLOAD").toString().equals("0") ? "" : map.get("DOWNLOAD").toString());
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue(((Integer.parseInt(map.get("DOWNLOAD").toString()) % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 : Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 + 1)) + "");
            num += ((Integer.parseInt(map.get("DOWNLOAD").toString()) % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 : Integer.parseInt(map.get("DOWNLOAD").toString()) / 250 + 1));
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("");
            cell.setCellStyle(style);
        }
        if (countyInfoList.size() < 16) {
            for (int i = 0; i <= 16 - countyInfoList.size(); i++) {
                //创建行
                row = sheet.createRow(countyInfoList.size() + 3 + i);// 行之后开始创建
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                for (int j = 0; j < 5; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                    cell.setCellStyle(style);
                }
            }
            row = sheet.createRow(18);
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(style);

            cell = row.createCell(1);
            cell.setCellValue(sum);
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("制证车间");
            cell.setCellStyle(style);
            if ("p1".equals(workShop) || "p3".equals(workShop)) {
                cell = row.createCell(2);
                cell.setCellValue(" 平 一 ");
                cell.setCellStyle(style2);
            } else {
                cell = row.createCell(2);
                cell.setCellValue("平二");
                cell.setCellStyle(style2);
            }
            cell = row.createCell(3);
            cell.setCellValue(num);
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue(permanentPositionDate);
            cell.setCellStyle(style);

            row = sheet.createRow(19);
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("类型");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(typeName);
            cell.setCellStyle(style);
            for (int j = 0; j < 3; j++) {
                cell = row.createCell(j + 2);
                cell.setCellValue("");
                cell.setCellStyle(style);
            }
            CellRangeAddress callRangeNum1 = new CellRangeAddress(19, 19, 1, 4);//起始行,结束行,起始列,结束列
            sheet.addMergedRegion(callRangeNum1);

            row = sheet.createRow(20);
            row.setHeightInPoints(Short.parseShort("32"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("循环单");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(cyclesheetid);
            cell.setCellStyle(style);
            for (int j = 0; j < 3; j++) {
                cell = row.createCell(j + 2);
                cell.setCellValue("");
                cell.setCellStyle(style);
            }
            CellRangeAddress callRangeNum = new CellRangeAddress(20, 20, 1, 4);//起始行,结束行,起始列,结束列
            sheet.addMergedRegion(callRangeNum);

            row = sheet.createRow(21);
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("出库日期");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("20   年    月     日");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("");
            cell.setCellStyle(style);
            CellRangeAddress callRangedate = new CellRangeAddress(21, 21, 1, 4);//起始行,结束行,起始列,结束列
            sheet.addMergedRegion(callRangedate);
        }

        if (countyInfoList.size() >= 16) {
            row = sheet.createRow(countyInfoList.size() + 3);
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(style);

            cell = row.createCell(1);
            cell.setCellValue(sum);
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("制证车间");
            cell.setCellStyle(style);
            if ("p1".equals(workShop) || "p3".equals(workShop)) {
                cell = row.createCell(2);
                cell.setCellValue("平一");
                cell.setCellStyle(style);
            } else {
                cell = row.createCell(2);
                cell.setCellValue("平二");
                cell.setCellStyle(style);
            }
            cell = row.createCell(3);
            cell.setCellValue(num);
            cell.setCellStyle(style);

            cell = row.createCell(4);
            cell.setCellValue(permanentPositionDate);
            cell.setCellStyle(style);

            row = sheet.createRow(countyInfoList.size() + 4);
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("类型");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(typeName);
            cell.setCellStyle(style);
            for (int j = 0; j < 3; j++) {
                cell = row.createCell(j + 2);
                cell.setCellValue("");
                cell.setCellStyle(style);
            }

            row = sheet.createRow(countyInfoList.size() + 5);
            row.setHeightInPoints(Short.parseShort("32"));//设置行高
            cell = row.createCell(0);
            cell.setCellValue("循环单");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(cyclesheetid);
            cell.setCellStyle(style);
            for (int j = 0; j < 3; j++) {
                cell = row.createCell(j + 2);
                cell.setCellValue("");
                cell.setCellStyle(style);
            }
            CellRangeAddress callRangeNum = new CellRangeAddress(countyInfoList.size() + 5, countyInfoList.size() + 5, 1, 4);//起始行,结束行,起始列,结束列
            sheet.addMergedRegion(callRangeNum);

            row = sheet.createRow(countyInfoList.size() + 6);
            row.setHeightInPoints(Short.parseShort("16"));//设置行高
            //创建单元格并且添加数据
            cell = row.createCell(0);
            cell.setCellValue("出库日期");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("20   年    月     日");
            cell.setCellStyle(style);
            for (int j = 0; j < 3; j++) {
                cell = row.createCell(j + 2);
                cell.setCellValue("");
                cell.setCellStyle(style);
            }
            CellRangeAddress callRangedate = new CellRangeAddress(countyInfoList.size() + 6, countyInfoList.size() + 6, 1, 4);//起始行,结束行,起始列,结束列
            sheet.addMergedRegion(callRangedate);

        }
//创建分单
        //创建辖区合单
        HSSFSheet mergeSheet = wb.createSheet("辖区合单");
        mergeSheet.setColumnWidth(0, 30 * 115);
        mergeSheet.setColumnWidth(1, 30 * 70);
        mergeSheet.setColumnWidth(2, 30 * 80);
        mergeSheet.setColumnWidth(3, 30 * 50);
        mergeSheet.setColumnWidth(4, 30 * 110);

        HSSFPrintSetup ps1 = mergeSheet.getPrintSetup();
        ps1.setLandscape(true); // 打印方向，true：横向，false：纵向
        ps1.setPaperSize(HSSFPrintSetup.B5_PAPERSIZE); //纸张
        //计算合单的行数
        int lineNum = 0;
        //计算合单的总数
        int mergeSum = 0;
        //计算合单的盒数
        int boxNum = 0;
        for (int a = 0; a < countyInfoList.size(); a++) {
            Map<String, Object> map = countyInfoList.get(a);
            //第二步创建sheet
            if (map.get("COUNTYNAME").toString().contains("区")) {
                //第三步创建行row:添加表头0行
                row = mergeSheet.createRow(0);
                row.setHeightInPoints(Short.parseShort("20"));//设置行高
                cell = row.createCell(0);         //第一个单元格
                cell.setCellValue("二代身份证统计表");                  //设定值
                cell.setCellStyle(style2);
                for (int j = 0; j < 4; j++) {
                    cell = row.createCell(j + 1);
                    cell.setCellValue("");
                    cell.setCellStyle(style2);
                }
                CellRangeAddress callRange = new CellRangeAddress(0, 0, 0, 4);//起始行,结束行,起始列,结束列
                mergeSheet.addMergedRegion(callRange);
                row = mergeSheet.createRow(1);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //第四步创建单元格
                cell = row.createCell(0);         //第一个单元格
                cell.setCellValue("单位");                  //设定值
                cell.setCellStyle(style);                   //内容居中

                cell = row.createCell(1);                   //第二个单元格
                cell.setCellValue("数量");
                cell.setCellStyle(style);

                cell = row.createCell(2);                   //第三个单元格
                cell.setCellValue("质检号");
                cell.setCellStyle(style);

                cell = row.createCell(3);                   //第四个单元格
                cell.setCellValue("盒数");
                cell.setCellStyle(style);

                cell = row.createCell(4);                   //第五个单元格
                cell.setCellValue("入库日期");
                cell.setCellStyle(style);

                //第五步插入数据
                //创建行
                row = mergeSheet.createRow(2);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue(cityName);
                cell.setCellStyle(style);
                for (int j = 0; j < 4; j++) {
                    cell = row.createCell(j + 1);
                    cell.setCellValue("");
                    cell.setCellStyle(style);
                }

                row = mergeSheet.createRow(lineNum + 3);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue(map.get("COUNTYNAME").toString());
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(map.get("DOWNLOAD") + "");
                mergeSum += Integer.valueOf(map.get("DOWNLOAD") + "");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellStyle(style);
                cell.setCellValue(((Integer.parseInt(map.get("DOWNLOAD") + "") % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD") + "") / 250 : Integer.parseInt(map.get("DOWNLOAD") + "") / 250 + 1)) + "");
                boxNum += ((Integer.parseInt(map.get("DOWNLOAD") + "") % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD") + "") / 250 : Integer.parseInt(map.get("DOWNLOAD") + "") / 250 + 1));
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);

                //创建空的行和列
                for (int i = 0; i <= 14 - lineNum; i++) {
                    //创建行
                    row = mergeSheet.createRow(lineNum + 4 + i);// 行之后开始创建
                    row.setHeightInPoints(Short.parseShort("16"));//设置行高
                    //创建单元格并且添加数据
                    for (int j = 0; j < 5; j++) {
                        cell = row.createCell(j);
                        cell.setCellValue("");
                        cell.setCellStyle(style);
                    }
                }

                row = mergeSheet.createRow(18);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue("合计");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(mergeSum + "");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("制证车间");
                cell.setCellStyle(style);
                if ("p1".equals(workShop) || "p3".equals(workShop)) {
                    cell = row.createCell(2);
                    cell.setCellValue("平一");
                    cell.setCellStyle(style);
                } else {
                    cell = row.createCell(2);
                    cell.setCellValue("平二");
                    cell.setCellStyle(style);
                }
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue(boxNum + "");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue(permanentPositionDate);
                cell.setCellStyle(style);

                //创建单元格并且添加数据
                row = mergeSheet.createRow(19);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                cell = row.createCell(0);
                cell.setCellValue("类型");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(typeName);
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);
                CellRangeAddress callRangea = new CellRangeAddress(19, 19, 1, 4);//起始行,结束行,起始列,结束列
                mergeSheet.addMergedRegion(callRangea);
                //创建单元格并且添加数据
                row = mergeSheet.createRow(20);
                row.setHeightInPoints(Short.parseShort("32"));//设置行高
                cell = row.createCell(0);
                cell.setCellValue("编号");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(cyclesheetid);
                cell.setCellStyle(style);
                cell = row.createCell(0);
                cell.setCellValue("循环单");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);
                CellRangeAddress callRangea1 = new CellRangeAddress(20, 20, 1, 4);//起始行,结束行,起始列,结束列
                mergeSheet.addMergedRegion(callRangea1);


                row = mergeSheet.createRow(21);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue("出库日期");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue("20   年    月     日");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);
                CellRangeAddress callRangeb = new CellRangeAddress(21, 21, 1, 4);//起始行,结束行,起始列,结束列
                mergeSheet.addMergedRegion(callRangeb);
                lineNum++;
            } else {
                sheet = wb.createSheet(a + "-" + map.get("COUNTYNAME"));

                sheet.setColumnWidth(0, 30 * 100);
                sheet.setColumnWidth(1, 30 * 70);
                sheet.setColumnWidth(2, 30 * 80);
                sheet.setColumnWidth(3, 30 * 50);
                sheet.setColumnWidth(4, 30 * 110);
                HSSFPrintSetup ps2 = sheet.getPrintSetup();

                ps2.setLandscape(true); // 打印方向，true：横向，false：纵向
                ps2.setPaperSize(HSSFPrintSetup.B5_PAPERSIZE);
                //第三步创建行row:添加表头0行
                row = sheet.createRow(0);
                row.setHeightInPoints(Short.parseShort("20"));//设置行高
                cell = row.createCell(0);         //第一个单元格
                cell.setCellValue("二代身份证统计表");                  //设定值
                cell.setCellStyle(style2);
                for (int j = 0; j < 4; j++) {
                    cell = row.createCell(j + 1);
                    cell.setCellValue("");
                    cell.setCellStyle(style2);
                }
                CellRangeAddress callRange = new CellRangeAddress(0, 0, 0, 4);//起始行,结束行,起始列,结束列
                sheet.addMergedRegion(callRange);

                row = sheet.createRow(1);

                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //第四步创建单元格
                cell = row.createCell(0);         //第一个单元格
                cell.setCellValue("单位");                  //设定值
                cell.setCellStyle(style);                   //内容居中

                cell = row.createCell(1);                   //第二个单元格
                cell.setCellValue("数量");
                cell.setCellStyle(style);

                cell = row.createCell(2);                   //第三个单元格
                cell.setCellValue("质检号");
                cell.setCellStyle(style);

                cell = row.createCell(3);                   //第四个单元格
                cell.setCellValue("盒数");
                cell.setCellStyle(style);

                cell = row.createCell(4);                   //第五个单元格
                cell.setCellValue("入库日期");
                cell.setCellStyle(style);

                //第五步插入数据
                //创建行
                row = sheet.createRow(2);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
//            cell.setCellValue(typeName);
                cell.setCellValue(cityName);
                cell.setCellStyle(style);
                for (int j = 0; j < 4; j++) {
                    cell = row.createCell(j + 1);
                    cell.setCellValue("");
                    cell.setCellStyle(style);
                }

                row = sheet.createRow(3);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue(map.get("COUNTYNAME").toString());
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(map.get("DOWNLOAD") + "");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellStyle(style);
                cell.setCellValue(((Integer.parseInt(map.get("DOWNLOAD") + "") % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD") + "") / 250 : Integer.parseInt(map.get("DOWNLOAD") + "") / 250 + 1)) + "");
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);

                //创建空的行和列
                for (int i = 0; i <= 14; i++) {
                    //创建行
                    row = sheet.createRow(4 + i);// 行之后开始创建
                    row.setHeightInPoints(Short.parseShort("16"));//设置行高
                    //创建单元格并且添加数据
                    for (int j = 0; j < 5; j++) {
                        cell = row.createCell(j);
                        cell.setCellValue("");
                        cell.setCellStyle(style);
                    }
                }

                row = sheet.createRow(18);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue("合计");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(Integer.valueOf(map.get("DOWNLOAD") + ""));
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("制证车间");
                cell.setCellStyle(style);
                if ("p1".equals(workShop) || "p3".equals(workShop)) {
                    cell = row.createCell(2);
                    cell.setCellValue("平一");
                    cell.setCellStyle(style);
                } else {
                    cell = row.createCell(2);
                    cell.setCellValue("平二");
                    cell.setCellStyle(style);
                }
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue(((Integer.parseInt(map.get("DOWNLOAD") + "") % 250 == 0 ? Integer.parseInt(map.get("DOWNLOAD") + "") / 250 : Integer.parseInt(map.get("DOWNLOAD") + "") / 250 + 1)) + "");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue(permanentPositionDate);
                cell.setCellStyle(style);

                //创建单元格并且添加数据
                row = sheet.createRow(19);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                cell = row.createCell(0);
                cell.setCellValue("类型");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(typeName);
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);
                CellRangeAddress callRangea = new CellRangeAddress(19, 19, 1, 4);//起始行,结束行,起始列,结束列
                sheet.addMergedRegion(callRangea);
                //创建单元格并且添加数据
                row = sheet.createRow(20);
                row.setHeightInPoints(Short.parseShort("32"));//设置行高
                cell = row.createCell(0);
                cell.setCellValue("编号");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue(cyclesheetid);
                cell.setCellStyle(style);
                cell = row.createCell(0);
                cell.setCellValue("循环单");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);
                CellRangeAddress callRangea1 = new CellRangeAddress(20, 20, 1, 4);//起始行,结束行,起始列,结束列
                sheet.addMergedRegion(callRangea1);


                row = sheet.createRow(21);
                row.setHeightInPoints(Short.parseShort("16"));//设置行高
                //创建单元格并且添加数据
                cell = row.createCell(0);
                cell.setCellValue("出库日期");
                cell.setCellStyle(style);
                cell = row.createCell(1);
                cell.setCellValue("20   年    月     日");
                cell.setCellStyle(style);
                cell = row.createCell(2);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(3);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(4);
                cell.setCellValue("");
                cell.setCellStyle(style);
                CellRangeAddress callRangeb = new CellRangeAddress(21, 21, 1, 4);//起始行,结束行,起始列,结束列
                sheet.addMergedRegion(callRangeb);
            }

        }

        //第六步将生成excel文件保存到指定路径下
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream("E:\\Excel\\" + simpleDateFormat.format(new Date()) + countyInfoList.get(0).get("COUNTYNAME") + ".xls");
//            fout = new FileOutputStream("D:\\" + simpleDateFormat.format(new Date()) + list.get(0).getString("COUNTYNAME") + ".xls");
            wb.write(fout);
            fout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("Excel文件生成成功..." + new Date());
        return "E:\\Excel\\" + simpleDateFormat.format(new Date()) +countyInfoList.get(0).get("COUNTYNAME") + ".xls";
//        return "D:\\" + simpleDateFormat.format(new Date()) + list.get(0).getString("COUNTYNAME") + ".xls";
    }


}
