From 31fb7637ca8c74de54f8bd29ec6e9dea3babf0f9 Mon Sep 17 00:00:00 2001
From: luxiaotao1123 <t1341870251@163.com>
Date: 星期一, 15 六月 2020 09:54:18 +0800
Subject: [PATCH] #视图
---
version/v1.0.1/asr_loc_use_view.sql | 31 +++++++++++++++
src/main/java/com/zy/asrs/controller/ConsoleController.java | 29 --------------
version/v1.0.1/asr_stk_use_view.sql | 50 +++++++++++++++++++++++++
3 files changed, 82 insertions(+), 28 deletions(-)
diff --git a/src/main/java/com/zy/asrs/controller/ConsoleController.java b/src/main/java/com/zy/asrs/controller/ConsoleController.java
index 744e0e5..1458636 100644
--- a/src/main/java/com/zy/asrs/controller/ConsoleController.java
+++ b/src/main/java/com/zy/asrs/controller/ConsoleController.java
@@ -1,12 +1,11 @@
package com.zy.asrs.controller;
-import com.alibaba.fastjson.JSON;
+import com.core.common.R;
import com.zy.asrs.entity.AxisBean;
import com.zy.asrs.entity.ChartBean;
import com.zy.asrs.entity.LocChartPie;
import com.zy.asrs.entity.WorkChartAxis;
import com.zy.asrs.mapper.ReportQueryMapper;
-import com.core.common.R;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
@@ -24,32 +23,6 @@
@Autowired
private ReportQueryMapper reportQueryMapper;
-
- @PostMapping("/pie")
- public R pieStats(){
-// ViewStockUseBean bean = new ViewStockUseBean();
-// bean.setPageSize(65535);
-// bean.setPageNumber(1);
-// List<ViewStockUseBean> list= reportQueryMapper.queryViewStockUseList(bean);
-// PieChartsVo pieVo = new PieChartsVo();
-// for (ViewStockUseBean one: list){
-// // 鎬诲簱浣�
-// pieVo.setTotalQty(pieVo.getTotalQty() + one.getTotal_qty());
-// // 鍦ㄥ簱
-// pieVo.setFullQty(pieVo.getFullQty() + one.getFull_qty());
-// // 绌洪棽
-// pieVo.setNullQty(pieVo.getNullQty() + one.getNull_qty());
-// // 绂佺敤
-// pieVo.setForbidQty(pieVo.getForbidQty() + one.getForbid_qty());
-// // 浣跨敤 = 鎬� - 鍦ㄥ簱 - 绌洪棽 - 绂佺敤
-// pieVo.setOccQty(pieVo.getOccQty()+(one.getTotal_qty()-one.getFull_qty()-one.getNull_qty()-one.getForbid_qty()));
-// }
-// pieVo.complete();
-// System.out.println(JSON.toJSONString(pieVo));
-// return R.ok(pieVo);
- String s = "{\"forbidDes\":\"绂佺敤搴撲綅0.1%\",\"forbidQty\":2,\"fullDes\":\"鍦ㄥ簱搴撲綅44.4%\",\"fullQty\":1061,\"nullDes\":\"绌哄簱浣�47.2%\",\"nullQty\":1128,\"occDes\":\"浣跨敤搴撲綅8.4%\",\"occQty\":201,\"totalDes\":\"\",\"totalQty\":2392}\n";
- return R.ok(JSON.parse(s));
- }
@PostMapping("loc/pie/charts")
public R dsa(){
diff --git a/version/v1.0.1/asr_loc_use_view.sql b/version/v1.0.1/asr_loc_use_view.sql
new file mode 100644
index 0000000..b634f44
--- /dev/null
+++ b/version/v1.0.1/asr_loc_use_view.sql
@@ -0,0 +1,31 @@
+USE [cool]
+GO
+
+/****** Object: View [dbo].[asr_loc_use_view] Script Date: 2020/6/15 9:45:48 ******/
+SET ANSI_NULLS ON
+GO
+
+SET QUOTED_IDENTIFIER ON
+GO
+
+
+create view [dbo].[asr_loc_use_view] as
+select SUM(fqty) fqty,SUM(oqty) oqty,SUM(uqty) uqty,SUM(xqty) xqty
+from (
+select COUNT(*) fqty,0 oqty,0 uqty,0 xqty from asr_loc_mast
+where loc_sts='F' or loc_sts='D'
+union all
+select 0,COUNT(*),0,0 from asr_loc_mast
+where loc_sts='O'
+union all
+select 0,0,COUNT(*),0 from asr_loc_mast
+where loc_sts='P' or loc_sts='Q' or loc_sts='R' or loc_sts='S'
+union all
+select 0,0,0,COUNT(*) from asr_loc_mast
+where loc_sts='X'
+) a
+
+
+GO
+
+
diff --git a/version/v1.0.1/asr_stk_use_view.sql b/version/v1.0.1/asr_stk_use_view.sql
new file mode 100644
index 0000000..d72b7e2
--- /dev/null
+++ b/version/v1.0.1/asr_stk_use_view.sql
@@ -0,0 +1,50 @@
+USE [cool]
+GO
+
+/****** Object: View [dbo].[asr_stk_use_view] Script Date: 2020/6/15 9:50:53 ******/
+SET ANSI_NULLS ON
+GO
+
+SET QUOTED_IDENTIFIER ON
+GO
+
+create view [dbo].[asr_stk_use_view] as
+SELECT row1, SUM(total_qty) AS total_qty, SUM(full_qty) AS full_qty, SUM(null_qty) AS null_qty, SUM(forbid_qty) AS forbid_qty, SUM(empty_qty) AS empty_qty, SUM(use_qty) AS use_qty, CONVERT(float,
+ SUM(CAST(full_qty AS DECIMAL(12, 2))) * 100 / SUM(total_qty)) AS full_rate, CONVERT(float, (SUM(CAST(full_qty AS DECIMAL(12, 2))) + SUM(forbid_qty) + SUM(empty_qty) + SUM(use_qty))
+ * 100 / SUM(total_qty)) AS occ_rate
+FROM (SELECT row1, COUNT(*) AS total_qty, 0 AS full_qty, 0 AS null_qty, 0 AS forbid_qty, 0 AS empty_qty, 0 AS use_qty
+ FROM dbo.asr_loc_mast
+ WHERE (whs_type = 1)
+ GROUP BY row1
+ UNION ALL
+ SELECT row1, 0 AS Expr1, COUNT(*) AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5, 0 AS Expr6
+ FROM dbo.asr_loc_mast AS asr_loc_mast_5
+ WHERE (loc_sts = 'F') AND (whs_type = 1)
+ GROUP BY row1
+ UNION ALL
+ SELECT row1, 0 AS Expr1, 0 AS Expr2, COUNT(*) AS Expr3, 0 AS Expr4, 0 AS Expr5, 0 AS Expr6
+ FROM dbo.asr_loc_mast AS asr_loc_mast_4
+ WHERE (loc_sts = 'O') AND (whs_type = 1)
+ GROUP BY row1
+ UNION ALL
+ SELECT row1, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, COUNT(*) AS Expr4, 0 AS Expr5, 0 AS Expr6
+ FROM dbo.asr_loc_mast AS asr_loc_mast_3
+ WHERE (loc_sts = 'X') AND (whs_type = 1)
+ GROUP BY row1
+ UNION ALL
+ SELECT row1, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, COUNT(*) AS Expr5, 0 AS Expr6
+ FROM dbo.asr_loc_mast AS asr_loc_mast_2
+ WHERE (loc_sts = 'D') AND (whs_type = 1)
+ GROUP BY row1
+ UNION ALL
+ SELECT row1, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5, COUNT(*) AS Expr6
+ FROM dbo.asr_loc_mast AS asr_loc_mast_1
+ WHERE (loc_sts = 'R' OR
+ loc_sts = 'S' OR
+ loc_sts = 'P' OR
+ loc_sts = 'Q') AND (whs_type = 1)
+ GROUP BY row1) AS a
+GROUP BY row1
+GO
+
+
--
Gitblit v1.9.1