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;
|
}
|
}
|
