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