From ce15656efc12043dd465a41af716a6bf2af091cf Mon Sep 17 00:00:00 2001
From: zwl <1051256694@qq.com>
Date: 星期三, 15 四月 2026 22:54:22 +0800
Subject: [PATCH] 电视机数据对接
---
src/main/java/com/zy/asrs/mapper/ReportQueryMapper.java | 9 ++
src/main/java/com/zy/asrs/controller/ConsoleController.java | 41 +++++++++-
src/main/resources/sql/20260415_wrk_cube_day_stat.sql | 96 ++++++++++++++++++++++++
src/main/java/com/zy/asrs/controller/OpenController.java | 45 +++++++++-
src/main/java/com/zy/asrs/entity/WorkCubeTotalAxis.java | 15 +++
src/main/java/com/zy/asrs/entity/AxisBean.java | 6
6 files changed, 199 insertions(+), 13 deletions(-)
diff --git a/src/main/java/com/zy/asrs/controller/ConsoleController.java b/src/main/java/com/zy/asrs/controller/ConsoleController.java
index 8de26a6..ae6e67f 100644
--- a/src/main/java/com/zy/asrs/controller/ConsoleController.java
+++ b/src/main/java/com/zy/asrs/controller/ConsoleController.java
@@ -4,6 +4,7 @@
import com.zy.asrs.entity.AxisBean;
import com.zy.asrs.entity.ChartBean;
import com.zy.asrs.entity.LocChartPie;
+import com.zy.asrs.entity.WorkCubeTotalAxis;
import com.zy.asrs.entity.WorkChartAxis;
import com.zy.asrs.mapper.ReportQueryMapper;
import org.springframework.beans.factory.annotation.Autowired;
@@ -63,8 +64,8 @@
List<WorkChartAxis> listChart = reportQueryMapper.getChartAxis();
if(listChart!=null) {
- ArrayList<Integer> data1 = new ArrayList<Integer>();
- ArrayList<Integer> data2 = new ArrayList<Integer>();
+ ArrayList<Number> data1 = new ArrayList<Number>();
+ ArrayList<Number> data2 = new ArrayList<Number>();
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
Calendar calendar = Calendar.getInstance();
@@ -88,14 +89,46 @@
}
AxisBean inqty = new AxisBean();
inqty.setName("鍏ュ簱鎵樼洏鏁�");
- Integer[] array1 = new Integer[data1.size()];
+ Number[] array1 = new Number[data1.size()];
inqty.setData(data1.toArray(array1));
list.add(inqty);
AxisBean outqty = new AxisBean();
outqty.setName("鍑哄簱鎵樼洏鏁�");
- Integer[] array2 = new Integer[data2.size()];
+ Number[] array2 = new Number[data2.size()];
outqty.setData(data2.toArray(array2));
list.add(outqty);
+
+ AxisBean cube5 = new AxisBean();
+ cube5.setName("5鐘舵�佷綋绉�");
+ AxisBean cube15 = new AxisBean();
+ cube15.setName("15鐘舵�佷綋绉�");
+ List<WorkCubeTotalAxis> workCubeTotalAxes = reportQueryMapper.getCubeTotal();
+ Map<String, WorkCubeTotalAxis> cubeMap = new HashMap<String, WorkCubeTotalAxis>();
+ if (workCubeTotalAxes != null) {
+ for (WorkCubeTotalAxis w : workCubeTotalAxes) {
+ if (w.getYmd() != null) {
+ cubeMap.put(w.getYmd(), w);
+ }
+ }
+ }
+ ArrayList<Number> data4 = new ArrayList<Number>();
+ ArrayList<Number> data5 = new ArrayList<Number>();
+ SimpleDateFormat sfCube = new SimpleDateFormat("yyyy-MM-dd");
+ Calendar calendarCube = Calendar.getInstance();
+ calendarCube.add(Calendar.DATE, -12);
+ for (int i = 0; i < 12; i++) {
+ calendarCube.add(Calendar.DATE, 1);
+ String str = sfCube.format(calendarCube.getTime());
+ WorkCubeTotalAxis cubeAxis = cubeMap.get(str);
+ data4.add(cubeAxis == null || cubeAxis.getCube5Total() == null ? 0 : cubeAxis.getCube5Total());
+ data5.add(cubeAxis == null || cubeAxis.getCube15Total() == null ? 0 : cubeAxis.getCube15Total());
+ }
+ Number[] array4 = new Number[data4.size()];
+ cube5.setData(data4.toArray(array4));
+ list.add(cube5);
+ Number[] array5 = new Number[data5.size()];
+ cube15.setData(data5.toArray(array5));
+ list.add(cube15);
}
map.put("rows",list);
return R.ok(map);
diff --git a/src/main/java/com/zy/asrs/controller/OpenController.java b/src/main/java/com/zy/asrs/controller/OpenController.java
index 8364138..22818dc 100644
--- a/src/main/java/com/zy/asrs/controller/OpenController.java
+++ b/src/main/java/com/zy/asrs/controller/OpenController.java
@@ -698,8 +698,8 @@
List<WorkChartAxis> listChart = reportQueryMapper.getChartAxis();
if(listChart!=null) {
- ArrayList<Integer> data1 = new ArrayList<Integer>();
- ArrayList<Integer> data2 = new ArrayList<Integer>();
+ ArrayList<Number> data1 = new ArrayList<Number>();
+ ArrayList<Number> data2 = new ArrayList<Number>();
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
Calendar calendar = Calendar.getInstance();
@@ -723,25 +723,58 @@
}
AxisBean inqty = new AxisBean();
inqty.setName("鍏ュ簱鎵樼洏鏁�");
- Integer[] array1 = new Integer[data1.size()];
+ Number[] array1 = new Number[data1.size()];
inqty.setData(data1.toArray(array1));
list.add(inqty);
AxisBean outqty = new AxisBean();
outqty.setName("鍑哄簱鎵樼洏鏁�");
- Integer[] array2 = new Integer[data2.size()];
+ Number[] array2 = new Number[data2.size()];
outqty.setData(data2.toArray(array2));
list.add(outqty);
AxisBean teu= new AxisBean();
teu.setName("TEU");
List<WorkTeuTotalAxis> workTeuTotalAxes = reportQueryMapper.getteuTotal();
- ArrayList<Integer> data3 = new ArrayList<>();
+ ArrayList<Number> data3 = new ArrayList<Number>();
for (WorkTeuTotalAxis w : workTeuTotalAxes) {
data3.add(w.getTeu_total());
}
- Integer[] array3 = new Integer[data3.size()];
+ Number[] array3 = new Number[data3.size()];
teu.setData(data3.toArray(array3));
list.add(teu);
+
+ AxisBean cube5 = new AxisBean();
+ cube5.setName("鍏ュ簱浣撶Н");
+ AxisBean cube15 = new AxisBean();
+ cube15.setName("鍑哄簱浣撶Н");
+ List<WorkCubeTotalAxis> workCubeTotalAxes = reportQueryMapper.getCubeTotal();
+ Map<String, WorkCubeTotalAxis> cubeMap = new HashMap<String, WorkCubeTotalAxis>();
+ if (workCubeTotalAxes != null) {
+ for (WorkCubeTotalAxis w : workCubeTotalAxes) {
+ if (w.getYmd() != null) {
+ cubeMap.put(w.getYmd(), w);
+ }
+ }
+ }
+ ArrayList<Number> data4 = new ArrayList<Number>();
+ ArrayList<Number> data5 = new ArrayList<Number>();
+ SimpleDateFormat sfCube = new SimpleDateFormat("yyyy-MM-dd");
+ Calendar calendarCube = Calendar.getInstance();
+ calendarCube.add(Calendar.DATE, -12);
+ for (int i = 0; i < 12; i++) {
+ calendarCube.add(Calendar.DATE, 1);
+ String str = sfCube.format(calendarCube.getTime());
+ WorkCubeTotalAxis cubeAxis = cubeMap.get(str);
+ data4.add(cubeAxis == null || cubeAxis.getCube5Total() == null ? 0 : cubeAxis.getCube5Total());
+ data5.add(cubeAxis == null || cubeAxis.getCube15Total() == null ? 0 : cubeAxis.getCube15Total());
+ }
+ Number[] array4 = new Number[data4.size()];
+ cube5.setData(data4.toArray(array4));
+ list.add(cube5);
+ Number[] array5 = new Number[data5.size()];
+ cube15.setData(data5.toArray(array5));
+ list.add(cube15);
+
}
map.put("rows",list);
return R.ok(map);
diff --git a/src/main/java/com/zy/asrs/entity/AxisBean.java b/src/main/java/com/zy/asrs/entity/AxisBean.java
index 9d74e90..001b9bc 100644
--- a/src/main/java/com/zy/asrs/entity/AxisBean.java
+++ b/src/main/java/com/zy/asrs/entity/AxisBean.java
@@ -7,17 +7,17 @@
*/
public class AxisBean {
private String name;
- private Integer[] data;
+ private Number[] data;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
- public Integer[] getData() {
+ public Number[] getData() {
return data;
}
- public void setData(Integer[] data) {
+ public void setData(Number[] data) {
this.data = data;
}
diff --git a/src/main/java/com/zy/asrs/entity/WorkCubeTotalAxis.java b/src/main/java/com/zy/asrs/entity/WorkCubeTotalAxis.java
new file mode 100644
index 0000000..594c387
--- /dev/null
+++ b/src/main/java/com/zy/asrs/entity/WorkCubeTotalAxis.java
@@ -0,0 +1,15 @@
+package com.zy.asrs.entity;
+
+import lombok.Data;
+
+import java.math.BigDecimal;
+
+/**
+ * 浣撶Н缁熻鏇茬嚎鍥�
+ */
+@Data
+public class WorkCubeTotalAxis {
+ private String ymd;
+ private BigDecimal cube5Total;
+ private BigDecimal cube15Total;
+}
diff --git a/src/main/java/com/zy/asrs/mapper/ReportQueryMapper.java b/src/main/java/com/zy/asrs/mapper/ReportQueryMapper.java
index 01cd112..eb453e5 100644
--- a/src/main/java/com/zy/asrs/mapper/ReportQueryMapper.java
+++ b/src/main/java/com/zy/asrs/mapper/ReportQueryMapper.java
@@ -81,6 +81,15 @@
@Select("SELECT ymd, teu_total FROM asr_wrk_detl_teu_day_view ORDER BY ymd")
List<WorkTeuTotalAxis> getteuTotal();
+ /**
+ * 鏈�杩� 12 澶╀綋绉眹鎬�
+ */
+ @Select("SELECT CONVERT(char(10), stat_date, 23) AS ymd, "
+ + "CAST(ISNULL(wrk_sts_5_cube_sum, 0) AS decimal(18,4)) AS cube5Total, "
+ + "CAST(ISNULL(wrk_sts_15_cube_sum, 0) AS decimal(18,4)) AS cube15Total "
+ + "FROM dbo.asr_wrk_cube_day_stat ORDER BY stat_date")
+ List<WorkCubeTotalAxis> getCubeTotal();
+
List<ViewWorkCountInView> selectWorkCountIn(@Param("curr") Integer curr, @Param("limit") Integer limit, @Param("matnr")String matnr, @Param("start") String startTime, @Param("end") String endTime);
diff --git a/src/main/resources/sql/20260415_wrk_cube_day_stat.sql b/src/main/resources/sql/20260415_wrk_cube_day_stat.sql
new file mode 100644
index 0000000..20c8bb4
--- /dev/null
+++ b/src/main/resources/sql/20260415_wrk_cube_day_stat.sql
@@ -0,0 +1,96 @@
+IF OBJECT_ID('dbo.asr_wrk_cube_day_stat', 'U') IS NULL
+BEGIN
+ CREATE TABLE dbo.asr_wrk_cube_day_stat (
+ stat_date date NOT NULL,
+ wrk_sts_5_cube_sum decimal(18,4) NOT NULL CONSTRAINT DF_asr_wrk_cube_day_stat_sts5 DEFAULT (0),
+ wrk_sts_15_cube_sum decimal(18,4) NOT NULL CONSTRAINT DF_asr_wrk_cube_day_stat_sts15 DEFAULT (0),
+ refresh_time datetime NOT NULL CONSTRAINT DF_asr_wrk_cube_day_stat_refresh DEFAULT (GETDATE()),
+ CONSTRAINT PK_asr_wrk_cube_day_stat PRIMARY KEY CLUSTERED (stat_date)
+ );
+END
+GO
+
+IF OBJECT_ID('dbo.usp_refresh_wrk_cube_day_stat', 'P') IS NOT NULL
+ DROP PROCEDURE dbo.usp_refresh_wrk_cube_day_stat;
+GO
+
+CREATE PROCEDURE dbo.usp_refresh_wrk_cube_day_stat
+AS
+BEGIN
+ SET NOCOUNT ON;
+
+ DECLARE @start_dt date = DATEADD(DAY, -11, CONVERT(date, GETDATE()));
+ DECLARE @end_dt date = DATEADD(DAY, 1, CONVERT(date, GETDATE()));
+
+ BEGIN TRY
+ BEGIN TRAN;
+
+ DELETE FROM dbo.asr_wrk_cube_day_stat;
+
+ ;WITH n AS (
+ SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
+ UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
+ UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
+ ),
+ dates AS (
+ SELECT DATEADD(DAY, n.n, @start_dt) AS stat_date
+ FROM n
+ ),
+ mast AS (
+ SELECT
+ m.wrk_no,
+ m.io_time,
+ m.wrk_sts
+ FROM dbo.asr_wrk_mast_log m
+ WHERE m.wrk_sts IN (5, 15)
+ AND m.io_time >= @start_dt
+ AND m.io_time < @end_dt
+ ),
+ detl AS (
+ SELECT
+ d.wrk_no,
+ d.io_time,
+ SUM(ISNULL(d.cube_number, 0)) AS cube_sum
+ FROM dbo.asr_wrk_detl_log d
+ WHERE d.io_time >= @start_dt
+ AND d.io_time < @end_dt
+ GROUP BY d.wrk_no, d.io_time
+ ),
+ agg AS (
+ SELECT
+ CONVERT(date, m.io_time) AS stat_date,
+ SUM(CASE WHEN m.wrk_sts = 5 THEN d.cube_sum ELSE 0 END) AS wrk_sts_5_cube_sum,
+ SUM(CASE WHEN m.wrk_sts = 15 THEN d.cube_sum ELSE 0 END) AS wrk_sts_15_cube_sum
+ FROM mast m
+ INNER JOIN detl d
+ ON d.wrk_no = m.wrk_no
+ AND d.io_time = m.io_time
+ GROUP BY CONVERT(date, m.io_time)
+ )
+ INSERT INTO dbo.asr_wrk_cube_day_stat (
+ stat_date,
+ wrk_sts_5_cube_sum,
+ wrk_sts_15_cube_sum,
+ refresh_time
+ )
+ SELECT
+ ds.stat_date,
+ CAST(ISNULL(a.wrk_sts_5_cube_sum, 0) AS decimal(18,4)),
+ CAST(ISNULL(a.wrk_sts_15_cube_sum, 0) AS decimal(18,4)),
+ GETDATE()
+ FROM dates ds
+ LEFT JOIN agg a
+ ON a.stat_date = ds.stat_date;
+
+ COMMIT TRAN;
+ END TRY
+ BEGIN CATCH
+ IF @@TRANCOUNT > 0
+ ROLLBACK TRAN;
+ THROW;
+ END CATCH
+END
+GO
+
+EXEC dbo.usp_refresh_wrk_cube_day_stat;
+GO
--
Gitblit v1.9.1