package com.zbooksoft.gdmis.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ruili.wcp.web.model.AjaxResponse;
import com.ruili.wcp.web.model.ErrorInfo;
import com.zbooksoft.gdmis.data.entity.ProcessingDetail;
import com.zbooksoft.gdmis.service.ProcessingDetailService;
import org.apache.shiro.authz.annotation.RequiresUser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: 利用统计
 * @Author: zhai
 * @Date: 2024/9/13
 **/
@Controller
@RequestMapping("/utlStatistics")
public class UtlStatisticsController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    ProcessingDetailService processingDetailService;
    private static final Logger logger = LoggerFactory.getLogger(UtlStatisticsController.class);

    /**
     * 服务利用统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/serviceStatistics")
    @RequiresUser
    public ModelAndView serviceStatistics() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/serviceStatistics");
        return mav;
    }


    /**
     * 根据借阅类型
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlType")
    @RequiresUser
    public ModelAndView utlType() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlType");
        return mav;
    }

    @RequestMapping({"/getUtlType"})
    @ResponseBody
    @RequiresUser
    public Object getUtlType(String code) {
        try {
            ArrayList<Map<String, Object>> mapList = new ArrayList<>();
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 按人件份统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/transferStatistics")
    @RequiresUser
    public ModelAndView transferStatistics() {
//        ModelAndView mav = new ModelAndView("gh/utlStatistics/transferStatistics");
        ModelAndView mav = new ModelAndView("gh/utlStatistics/cs");
        return mav;
    }

    @RequestMapping({"/getTransferStatistics"})
    @ResponseBody
    @RequiresUser
    public Object getTransferStatistics(String selectParameter, String startData, String endData) {
        try {
            String sql = "SELECT " +
                    "    A.ID," +
                    "    A.XMKYQMC ," +
                    "    COUNT(DISTINCT B.ID) AS transferTime," +
                    "    COUNT(C.ID) AS transferCount," +
                    "    COUNT(CASE WHEN C.yszt = '3' THEN C.ID END) AS tidyCount," +
                    "    COUNT(CASE WHEN C.yszt = '3' THEN C.ID END) AS acceptCount," +
                    "    SUM(C.WJDX) AS acceptData," +
                    "    COUNT(CASE WHEN D.is_pass = 0 THEN D.id END) AS returnTime," +
                    "    SUM(C.YM) AS YM," +
                    "    SUM(C.SL) AS SL" +
                    " FROM CAT_FILE_AJJXX A" +
                    " LEFT JOIN BUS_TRANSFER_LOG B ON A.ID = B.AJ_ID" +
                    " LEFT JOIN CAT_ITEM_YSWJXX C ON A.ID = C.AJ_ID" +
                    " LEFT JOIN CAT_CHECK_INFO D ON A.ID = D.aj_id" +
                    " WHERE 1=1 ";
            if (selectParameter.equals("rk")){
                sql += " AND C.YSZT=3 ";
            }
            // 添加时间段筛选条件
            if (startData != null && !startData.isEmpty()) {
                sql += " AND B.TRANSFER_TIEM >= '" + startData + "'";
            }
            if (endData != null && !endData.isEmpty()) {
                sql += " AND B.TRANSFER_TIEM <= '" + endData + "'";
            }
            sql += " GROUP BY A.ID, A.XMKYQMC" +
                    " ORDER BY A.ID;";
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    // 获取饼状图数据
    @RequestMapping("/getCollectionEchartsPieData")
    @ResponseBody
    public Object getCollectionEchartsPieData() {
        String sql="select xmkyqlx as name,count(b.id) as value FROM cat_file_ajjxx a left join cat_item_yswjxx b on b.aj_id=a.id GROUP by a.xmkyqlx";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        // 将键名转换为小写
        List<Map<String, Object>> result = new ArrayList<>();
        for (Map<String, Object> map : mapList) {
            Map<String, Object> newMap = new HashMap<>();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                newMap.put(entry.getKey().toLowerCase(), entry.getValue());
            }
            result.add(newMap);
        }
        return result;
    }

    /**
     * 入库统计
     * @param year
     * @param startData
     * @param endData
     * @return
     */
    @RequestMapping({"/getInboundTimelinessRate"})
    @ResponseBody
    @RequiresUser
    public Object getInboundTimelinessRate(String year, String startData, String endData) {
        try {
            String sql = "select aj.xmkyqmc,ys.wjtm,ys.sl,ys.ym,ys.wjdx from CAT_ITEM_YSWJXX as ys left join cat_file_ajjxx as aj on ys.aj_id=aj.id where ys.yszt='3' ";
            // 添加时间段筛选条件
            if (startData != null && !startData.isEmpty()) {
                sql += " AND aj.yswsj >= '" + startData + "'";
            }
            if (endData != null && !endData.isEmpty()) {
                sql += " AND aj.yswsj <= '" + endData + "'";
            }
            List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 按人件份统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByUser")
    @RequiresUser
    public ModelAndView utlStatisticsByUser() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByUser");
        return mav;
    }


    @RequestMapping({"/getUtlStatisticsByUser"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsByUser(String year, String startData, String endData) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();
            if (year == null || year.equals("")) {
                String yearSQL = "SELECT DISTINCT EXTRACT(YEAR FROM DATE_TIME) AS YEAR FROM UTL_VIEW_FORM";
                jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                    String years = map.get("YEAR").toString().replace(".0", "");
                    Map<String, Object> hashMap = getCount(" EXTRACT(YEAR FROM DATE_TIME)", years);
                    hashMap.put("year", years);
                    mapList.add(hashMap);
                });
            } else {
                String yearSQL = "SELECT DISTINCT EXTRACT(MONTH FROM DATE_TIME) AS MONTH FROM  UTL_VIEW_FORM where EXTRACT(YEAR FROM DATE_TIME) = '" + year + "'";
                jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                    String month = map.get("MONTH").toString().replace(".0", "");
                    Map<String, Object> hashMap = getCount(" EXTRACT(MONTH FROM DATE_TIME)", month);
                    hashMap.put("year", year);
                    hashMap.put("month", month);
                    mapList.add(hashMap);
                });
            }
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 按借阅单位统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByDept")
    @RequiresUser
    public ModelAndView utlStatisticsByDept() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByDept");
        return mav;
    }

    //获取部门信息

    @RequestMapping({"/getUtlStatisticsByDept"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsByDept(String deptName,String startData, String endData) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();
            String yearSQL="SELECT DISTINCT DEPT_NAME AS DEPT_NAME FROM  UTL_VIEW_FORM where dept_name like '%"+deptName+"%'";
            if (startData != null && !startData.isEmpty()) {
                yearSQL += " AND date >= '" + startData + "'";
            }
            if (endData != null && !endData.isEmpty()) {
                yearSQL += " AND date <= '" + endData + "'";
            }
            jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                String deptNames = map.get("DEPT_NAME") == null ? "" : map.get("DEPT_NAME").toString();
                Map<String, Object> hashMap = getCount("DEPT_NAME", deptNames);
                hashMap.put("deptName", deptNames);
                mapList.add(hashMap);
            });
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 按借阅单位所在地统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByDeptAddress")
    @RequiresUser
    public ModelAndView utlStatisticsByDeptAddress() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByDeptAddress");
        return mav;
    }

    //获取部门信息

    @RequestMapping({"/getUtlStatisticsByDeptAddress"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsByDeptAddress(String year, String startData, String endData) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();

            String yearSQL = "SELECT DISTINCT city AS city FROM  UTL_VIEW_FORM";
            jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                String city = map.get("city").toString();
                Map<String, Object> hashMap = getCount("city", city);
                hashMap.put("city", city);
                mapList.add(hashMap);
            });
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }


    /**
     * 按所属行业统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByIndustry")
    @RequiresUser
    public ModelAndView utlStatisticsByIndustry() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByIndustry");
        return mav;
    }
    //获取部门信息

    @RequestMapping({"/getUtlStatisticsByIndustry"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsByIndustry(String year, String startData, String endData) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();

            String yearSQL = "SELECT DISTINCT TRADE AS TRADE FROM  UTL_VIEW_FORM";
            jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                String trade = map.get("TRADE").toString();
                Map<String, Object> hashMap = getCount("TRADE", trade);
                hashMap.put("trade", trade);
                mapList.add(hashMap);
            });
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }


    /**
     * 按单位性质统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByDeptType")
    @RequiresUser
    public ModelAndView utlStatisticsByDeptType() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByDeptType");
        return mav;
    }

    //获取部门信息

    @RequestMapping({"/getUtlStatisticsByDeptType"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsByDeptType(String year, String startData, String endData) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();

            String yearSQL = "SELECT DISTINCT DEPT_NATURE AS DEPT_NATURE FROM  UTL_VIEW_FORM";
            jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                String deptNature = map.get("DEPT_NATURE").toString();
                Map<String, Object> hashMap = getCount("DEPT_NATURE", deptNature);
                hashMap.put("deptNature", deptNature);
                mapList.add(hashMap);
            });
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 按借阅区域统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByArea")
    @RequiresUser
    public ModelAndView utlStatisticsByArea() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByArea");
        return mav;
    }

    @RequestMapping({"/getUtlStatisticsByArea"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsByArea(String year, String startData, String endData) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();

            String yearSQL = "SELECT DISTINCT DEPT_NATURE AS DEPT_NATURE FROM  UTL_VIEW_FORM";
            jdbcTemplate.queryForList(yearSQL).forEach(map -> {
                String deptNature = map.get("DEPT_NATURE").toString();
                Map<String, Object> hashMap = getCount("DEPT_NATURE", deptNature);
                hashMap.put("deptNature", deptNature);
                mapList.add(hashMap);
            });
            return mapList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 按资料类别统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByType")
    @RequiresUser
    public ModelAndView utlStatisticsByType() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByType");
        return mav;
    }

    /**
     * 对外服务详单
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsByService")
    @RequiresUser
    public ModelAndView utlStatisticsByService() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsByService");
        return mav;
    }

    /**
     * 涉密资料加工信息统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatisticsBySecurity")
    @RequiresUser
    public ModelAndView utlStatisticsBySecurity() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatisticsBySecurity");
        return mav;
    }

    @RequestMapping({"/getUtlStatisticsBySecurity"})
    @ResponseBody
    @RequiresUser
    public Object getUtlStatisticsBySecurity() {
        try {
            QueryWrapper<ProcessingDetail> wrapper = new QueryWrapper<>();
            wrapper.ne("SECURITY_CLASSIFICATION", "");
            wrapper.ne("SECURITY_CLASSIFICATION", "内部");
            wrapper.isNotNull("SECURITY_CLASSIFICATION");
            List<ProcessingDetail> processingDetailList = processingDetailService.list(wrapper);
            return processingDetailList;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 地质资料利用情况统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/utlStatistics")
    @RequiresUser
    public ModelAndView utlStatistics() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/utlStatistics");
        return mav;
    }

    /**
     * 地质资料利用情况统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/physicalStatistics")
    @RequiresUser
    public ModelAndView physicalStatistics() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/physicalStatistics");
        return mav;
    }

    public Map<String, Object> getCount(String tableName, String value) {
        HashMap<String, Object> map = new HashMap<>();
        String sql = "SELECT  APPLY_NAME, COUNT(APPLY_NAME) as count,SUM(FILE_COUNT) as file_count,SUM(ITEM_COUNT) as item_count FROM utl_view_form where " + tableName + " ='" + value + "' GROUP BY  APPLY_NAME ";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map1 : mapList) {
            if (map1.get("APPLY_NAME") != null && "借阅".equals(map1.get("APPLY_NAME").toString())) {
                map.put("borrow", map1.get("count"));
                map.put("borrowFileCount", map1.get("file_count"));
                map.put("borrowItemCount", map1.get("item_count"));
            }
            if (map1.get("APPLY_NAME") != null && "加工".equals(map1.get("APPLY_NAME").toString())) {
                map.put("process", map1.get("count"));
                map.put("processFileCount", map1.get("file_count"));
                map.put("processItemCount", map1.get("item_count"));
            }
            if (map1.get("APPLY_NAME") != null && "实物利用".equals(map1.get("APPLY_NAME").toString())) {
                map.put("physical", map1.get("count"));
                map.put("physicalFileCount", map1.get("file_count"));
                map.put("physicalItemCount", map1.get("item_count"));
            }
        }
        return map;
    }


    /**
     * 获取地质资料 成果资料 原始资料 实物资料数量
     *
     * @return
     */
    @RequestMapping({"/getCount"})
    @ResponseBody
    @RequiresUser
    public Object getCount() {
        try {
            HashMap<Object, Object> counMap = new HashMap<>();
//            String dzzlCountSql = "select count(1) from CAT_FILE_AJJXX where fbzt = 1";
            String dzzlCountSql = "select count(1) from CAT_FILE_AJJXX";
            Integer dzzlCount = jdbcTemplate.queryForObject(dzzlCountSql, Integer.class);
            counMap.put("dzzlCount", dzzlCount);

            String yzCountSql = "select count(1) from CAT_ITEM_YSWJXX";
//            String yzCountSql = "select count(1) from CAT_ITEM_YSWJXX where AJ_ID IN (select ID from CAT_FILE_AJJXX where fbzt = 1)";
            Integer yzCount = jdbcTemplate.queryForObject(yzCountSql, Integer.class);
            counMap.put("yzCount", yzCount);

            String cgCountSql = "select count(1) from CAT_ITEM_CGWJXX ";
//            String cgCountSql = "select count(1) from CAT_ITEM_CGWJXX where AJ_ID IN (select ID from CAT_FILE_AJJXX where fbzt = 1)";
            Integer cgCount = jdbcTemplate.queryForObject(cgCountSql, Integer.class);
            counMap.put("cgCount", cgCount);

            String swCountSql = "select count(1) from CAT_FILE_SWYP ";
            Integer swCount = jdbcTemplate.queryForObject(swCountSql, Integer.class);
            counMap.put("swCount", swCount);

            return counMap;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
        }

    }

    /**
     * 根据资料类别统计
     *
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/typeStatistics")
    @RequiresUser
    public ModelAndView typeStatistics() {
        ModelAndView mav = new ModelAndView("gh/utlStatistics/typeStatistics");
        return mav;
    }
}