BigScreenController.java
8.84 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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
package com.huaheng.api.tv.controller;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.huaheng.api.tv.domain.BigScreenTask;
import com.huaheng.api.tv.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("/mainA")
private JSON mainA() {
return BigScreenData("A");
}
@CrossOrigin
@RequestMapping("/mainB")
private JSON mainB() {
return BigScreenData("B");
}
@CrossOrigin
@RequestMapping("/mainC")
private JSON mainC() {
return BigScreenData("C");
}
private JSON BigScreenData(String zone) {
JSONObject jsonObject = new JSONObject();
//库位利用率
String sql1 = "SELECT (SELECT count(1) FROM location WHERE\tIFNULL(containerCode,'') !='' and zoneCode='" + zone + "' AND warehouseCode = '" + ShiroUtils.getWarehouseCodeCS0001() + "' ) as 'inStock', (SELECT count(1) FROM location WHERE\tIFNULL(containerCode,'') ='' and zoneCode='" + zone + "' AND warehouseCode = '" + ShiroUtils.getWarehouseCode() + "' ) as 'soldOut' from DUAL";
List<LinkedHashMap<String, Object>> locationProp = mapper.selectCommon(sql1);
jsonObject.put("locationProp", locationProp);
//近7天入库数
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='" + zone + "'" +
" 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);
jsonObject.put("receiptNum", receiptNum);
//近7天出库数
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);
jsonObject.put("shipmentNum", shipmentNum);
//出入库任务
List<BigScreenTask> itemReceipts = bigScreenService.selectReceiptTask();
List<BigScreenTask> itemShipments = bigScreenService.selectShipmentTask();
jsonObject.put("itemReceipts", itemReceipts);
jsonObject.put("itemShipments", itemShipments);
// 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);
//jsonObject.put("inventoryProp",getInventoryProp);
Map<String, Object> data = (Map<String, Object>) getCommonData(zone).getData();
jsonObject.put("CommonData", data);
return jsonObject;
}
public AjaxResult getCommonData(String zone) {
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='" + zone + "' 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='" + zone + "' 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='" + zone + "' and h.status=100";
String inventoryTotal = "SELECT IFNULL(SUM(totalQty),0) 'total' from inventory_header where 1=1 and zoneCode='" + zone + "'";
String materialCount = "SELECT ifnull(count(DISTINCT materialCode),0) 'total' from inventory_detail WHERE 1=1 and h.zoneCode='" + zone + "'";
//AGV库，空闲库位数
String availableLocationCount = "SELECT COUNT(*) 'total' from location where containerCode is null or trim(containerCode)='' and zoneCode='" + zone + "' and status='empty'";
String taskUncompletedTotal = "SELECT ifnull(COUNT(*),0) 'total' from task_header WHERE zoneCode='" + zone + "' 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);
}
}