BigScreenController.java
8.25 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
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" +
"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\n" +
") a left join (\n" +
"SELECT DATE(h.created) AS created , SUM(d.qty) AS taskQty from shipment_container_detail d join shipment_container_header h on d.shippingContainerId = h.id and h.warehouseCode='"+ShiroUtils.getWarehouseCodeCS0001()+"' WHERE h.created >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND h.status=20 GROUP BY DATE(h.created)\n" +
") b on a.click_date = b.created 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\n" +
") a left join (\n" +
"SELECT DATE(h.created) AS created , SUM(d.qty) AS taskQty from receipt_container_detail d join receipt_container_header h on d.receiptContainerId = h.id and h.warehouseCode='"+ShiroUtils.getWarehouseCodeCS0001()+"' WHERE h.created >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND d.status=20 GROUP BY DATE(h.created)\n" +
") b on a.click_date = b.created 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(t.a),0) 'total' from (\n" +
"SELECT COUNT(*) 'a' FROM receipt_header WHERE DATEDIFF(NOW(), created)=0 " + condition +
" UNION \n" +
"SELECT COUNT(*) 'a' FROM shipment_header WHERE DATEDIFF(NOW(), created)=0 " + condition +
") t";
String receiptTotal = "SELECT IFNULL(SUM(d.qty),0) 'total' FROM receipt_container_detail d join receipt_container_header h on d.receiptContainerId = h.id WHERE DATEDIFF(NOW(), h.created) = 0 AND d.status > 19 AND h.warehouseCode = '"+ShiroUtils.getWarehouseCodeCS0001()+"' ;";
String shipmentTotal = "SELECT IFNULL(SUM(d.qty),0) 'total' from shipment_container_detail d JOIN shipment_container_header h on d.shippingContainerId = h.id WHERE DATEDIFF(NOW(), h.created) = 0 AND h.status > 19 AND h.warehouseCode = '"+ShiroUtils.getWarehouseCodeCS0001()+"' ;";
String inventoryTotal = "SELECT IFNULL(SUM(totalQty),0) 'total' from inventory_header where 1=1 " + condition;
String materialCount = "SELECT count(DISTINCT materialSkuQty) 'total' from inventory_header WHERE 1=1" + condition;
String taskUncompletedTotal = "SELECT COUNT(*) 'total' from task_header WHERE status < 100 " + condition;
//AGV库，空闲库位数
String availableLocationCount="SELECT COUNT(*) 'total' from location where containerCode is null or trim(containerCode)='' and status='empty' and zoneCode='B'";
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);
}
}