BigScreenController.java
8.29 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
package com.huaheng.api.general.controller;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.huaheng.api.general.domain.BigScreenTask;
import com.huaheng.api.general.service.BigScreenService;
import com.huaheng.common.utils.security.ShiroUtils;
import com.huaheng.framework.web.domain.AjaxResult;
import com.huaheng.pc.report.excelReport.mapper.ExcelReportMapper;
import com.huaheng.pc.system.user.controller.IndexController;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping
public class BigScreenController {
@Resource
private IndexController indexController;
@Resource
ExcelReportMapper mapper;
@Resource
private BigScreenService bigScreenService;
@CrossOrigin
@RequestMapping("/bigScreen")
private JSON BigScreenData(){
Map<String,Object> data = (Map<String,Object>)getCommonData().getData();
String sql = "SELECT m.`name`,sum(i.qty) as total from inventory_detail i join material m on i.materialCode = m.`code` and i.warehouseCode = m.warehouseCode AND i.warehouseCode = '"+ShiroUtils.getWarehouseCodeCS0001()+"' \n" +
"GROUP BY m.`name` ORDER BY total desc;";
List<LinkedHashMap<String, Object>> getInventoryProp = mapper.selectCommon(sql);
String sql1 = "SELECT (SELECT count(1) FROM location WHERE\tIFNULL(containerCode,'') !='' and zoneCode='B' AND warehouseCode = '"+ShiroUtils.getWarehouseCodeCS0001()+"' ) as 'inStock', (SELECT count(1) FROM location WHERE\tIFNULL(containerCode,'') ='' and zoneCode='B' AND warehouseCode = '"+ShiroUtils.getWarehouseCode()+"' ) as 'soldOut' from DUAL";
List<LinkedHashMap<String, Object>> locationProp = mapper.selectCommon(sql1);
String sql2 = "select a.click_date as date,ifnull(b.taskQty,0) as qty\n" +
" FROM (\n" +
" SELECT curdate() as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 1 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 2 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 3 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 4 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 5 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 6 day) as click_date ) a\n" +
" LEFT JOIN (\n" +
" SELECT DATE(h.completeTime) AS completeTime , SUM(d.qty) AS taskQty\n" +
" FROM task_detail d join task_header h\n" +
" ON d.taskId = h.id and h.warehouseCode='CS0001'\n" +
" WHERE h.completeTime >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND h.status=100 and h.zoneCode='B'" +
" and h.taskType=100 or h.taskType=200 GROUP BY DATE(h.completeTime)) b\n" +
" ON a.click_date = b.completeTime ORDER BY a.click_date;";
List<LinkedHashMap<String, Object>> receiptNum = mapper.selectCommon(sql2);
String sql3 = "select a.click_date as date,ifnull(b.taskQty,0) as qty\n" +
" FROM (\n" +
" SELECT curdate() as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 1 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 2 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 3 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 4 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 5 day) as click_date\n" +
" UNION all\n" +
" SELECT date_sub(curdate(), interval 6 day) as click_date ) a\n" +
" LEFT JOIN (\n" +
" SELECT DATE(h.completeTime) AS completeTime , SUM(d.qty) AS taskQty\n" +
" FROM task_detail d join task_header h\n" +
" ON d.taskId = h.id and h.warehouseCode='CS0001'\n" +
" WHERE h.completeTime >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND h.status=100 and h.zoneCode='B' and h.taskType=300 or h.taskType=400 GROUP BY DATE(h.completeTime)) b\n" +
" ON a.click_date = b.completeTime ORDER BY a.click_date;";
List<LinkedHashMap<String, Object>> shipmentNum = mapper.selectCommon(sql3);
List<BigScreenTask> itemReceipts= bigScreenService.selectReceiptTask();
List<BigScreenTask> itemShipments= bigScreenService.selectShipmentTask();
JSONObject jsonObject=new JSONObject();
jsonObject.put("CommonData",data);
//jsonObject.put("inventoryProp",getInventoryProp);
jsonObject.put("locationProp",locationProp);
jsonObject.put("receiptNum",receiptNum);
jsonObject.put("shipmentNum",shipmentNum);
jsonObject.put("itemReceipts",itemReceipts);
jsonObject.put("itemShipments",itemShipments);
return jsonObject;
}
public AjaxResult getCommonData(){
String condition = " and warehouseCode = '" + ShiroUtils.getWarehouseCodeCS0001() +"'";
String bllCount = "select ifnull(sum( d.qty ),0) 'total' FROM task_header h join task_detail d on h.id =d.taskId WHERE DATEDIFF(NOW(), h.completeTime) = 0 and (h.taskType=100 or h.taskType=200 or h.taskType=300 or h.taskType=400) and h.zoneCode='B' and h.status=100" ;
String receiptTotal= "select ifnull(sum( d.qty ),0) 'total' FROM task_header h join task_detail d on h.id =d.taskId WHERE DATEDIFF(NOW(), h.completeTime) = 0 and (h.taskType=100 or h.taskType=200) and h.zoneCode='B' and h.status=100" ;
String shipmentTotal = "select ifnull(sum( d.qty ),0) 'total' FROM task_header h join task_detail d on h.id =d.taskId WHERE DATEDIFF(NOW(), h.completeTime) = 0 and (h.taskType=300 or h.taskType=400) and h.zoneCode='B' and h.status=100";
String inventoryTotal = "SELECT IFNULL(SUM(totalQty),0) 'total' from inventory_header where 1=1 and zoneCode='B'" ;
String materialCount = "SELECT ifnull(count(DISTINCT materialCode),0) 'total' from inventory_detail WHERE 1=1 and h.zoneCode='B'" ;
//AGV库，空闲库位数
String availableLocationCount="SELECT COUNT(*) 'total' from location where containerCode is null or trim(containerCode)='' and zoneCode='B' and status='empty'" ;
String taskUncompletedTotal = "SELECT ifnull(COUNT(*),0) 'total' from task_header WHERE zoneCode='B' and status < 100" ;
Map<String, Object> map = new HashMap<>();
List<LinkedHashMap<String, Object>> temp1 = mapper.selectCommon(bllCount);
map.put("bllCount",temp1.get(0).entrySet().iterator().next().getValue());
List<LinkedHashMap<String, Object>> temp2 = mapper.selectCommon(receiptTotal);
map.put("receiptTotal",temp2.get(0).entrySet().stream().findFirst().get().getValue());
List<LinkedHashMap<String, Object>> temp3 = mapper.selectCommon(shipmentTotal);
map.put("shipmentTotal",temp3.get(0).entrySet().stream().findFirst().get().getValue());
List<LinkedHashMap<String, Object>> temp4 = mapper.selectCommon(inventoryTotal);
map.put("inventoryTotal",temp4.get(0).entrySet().stream().findFirst().get().getValue());
//List<LinkedHashMap<String, Object>> temp5 = mapper.selectCommon(materialCount);
//map.put("materialCount",temp5.get(0).entrySet().stream().findFirst().get().getValue());
List<LinkedHashMap<String, Object>> temp5 = mapper.selectCommon(availableLocationCount);
map.put("availableLocationCount",temp5.get(0).entrySet().stream().findFirst().get().getValue());
List<LinkedHashMap<String, Object>> temp6 = mapper.selectCommon(taskUncompletedTotal);
map.put("taskUncompletedTotal",temp6.get(0).entrySet().stream().findFirst().get().getValue());
return AjaxResult.success(map);
}
}