|
|
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
|
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 warehouseCode = '"+ShiroUtils.getWarehouseCodeCS0001()+"' ) as 'inStock', (SELECT count(1) FROM location WHERE\tIFNULL(containerCode,'') ='' 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" +
|
|
|
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
" 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;";
|
|
|
67
68
69
|
List<LinkedHashMap<String, Object>> receiptNum = mapper.selectCommon(sql2);
String sql3 = "select a.click_date as date,ifnull(b.taskQty,0) as qty\n" +
|
|
|
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
" 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;";
|
|
|
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
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() +"'";
|
|
|
111
112
113
114
115
116
|
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'" ;
|
|
|
117
|
//AGV库，空闲库位数
|
|
|
118
119
|
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" ;
|
|
|
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
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());
|
|
|
134
135
136
137
138
|
//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());
|
|
|
139
140
141
142
143
144
145
|
List<LinkedHashMap<String, Object>> temp6 = mapper.selectCommon(taskUncompletedTotal);
map.put("taskUncompletedTotal",temp6.get(0).entrySet().stream().findFirst().get().getValue());
return AjaxResult.success(map);
}
}
|
