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