From 87d0248925a5a8c3cf07ba8a0283dc363e2b59f4 Mon Sep 17 00:00:00 2001
From: Fancy <Fancy.fx@outlook.com>
Date: 星期二, 07 一月 2025 17:02:20 +0800
Subject: [PATCH] statistic_work
---
pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportSv.java | 12 ++++++
pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportCtrl.java | 10 +++++
pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoOth/OthStatisticWorkloadMapper.java | 4 ++
pms-parent/pms-global/src/main/resources/mapper/OthStatisticWorkloadMapper.xml | 40 +++++++++++++++++++
pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoSta/StaAssemblyWorkLastMapper.java | 1
5 files changed, 66 insertions(+), 1 deletions(-)
diff --git a/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoOth/OthStatisticWorkloadMapper.java b/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoOth/OthStatisticWorkloadMapper.java
index 3d14aff..e584c75 100644
--- a/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoOth/OthStatisticWorkloadMapper.java
+++ b/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoOth/OthStatisticWorkloadMapper.java
@@ -1,11 +1,13 @@
package com.dy.pmsGlobal.daoOth;
+import cn.hutool.json.JSONObject;
import com.dy.pmsGlobal.pojoOth.OthStatisticWorkload;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.time.LocalDate;
import java.util.Date;
+import java.util.List;
/**
* @author User
@@ -31,4 +33,6 @@
String selectMaxDate();
void insertBatch(@Param("startDt")Date startDt, @Param("endDt") Date endDt);
+
+ List<JSONObject> queryStatisticWork(@Param("startDt")Date startDt, @Param("endDt") Date endDt);
}
diff --git a/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoSta/StaAssemblyWorkLastMapper.java b/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoSta/StaAssemblyWorkLastMapper.java
index 20be688..7e8b77d 100644
--- a/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoSta/StaAssemblyWorkLastMapper.java
+++ b/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/daoSta/StaAssemblyWorkLastMapper.java
@@ -42,4 +42,5 @@
List<JSONObject> queryWorkList();
List<JSONObject> queryAttendUser(@Param("startTime") Date startTime, @Param("endTime") Date endTime, @Param("todayDt") Date todayDt);
+
}
diff --git a/pms-parent/pms-global/src/main/resources/mapper/OthStatisticWorkloadMapper.xml b/pms-parent/pms-global/src/main/resources/mapper/OthStatisticWorkloadMapper.xml
index 27203de..f4c89a9 100644
--- a/pms-parent/pms-global/src/main/resources/mapper/OthStatisticWorkloadMapper.xml
+++ b/pms-parent/pms-global/src/main/resources/mapper/OthStatisticWorkloadMapper.xml
@@ -34,7 +34,45 @@
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY),'%Y-%m-%d') -- 濡傛灉涓や釜琛ㄩ兘娌℃湁鏁版嵁锛岃繑鍥炲墠涓�澶╂椂闂� 鏃犵敤
) AS max_date;
</select>
-
+ <select id="queryStatisticWork" resultType="cn.hutool.json.JSONObject">
+ WITH total as (select
+ DISTINCT(t.device_no) device_no,t.curr_node,t.node_content,t.number,p.pro_name, p.plan_name,u.id user_id,u.`name` user_name
+ FROM
+ (select DISTINCT device_no,plan_id,station_id,curr_node,node_content,number,updated_by from sta_device_production_log_past
+ where out_time BETWEEN #{startDt} AND #{endDt}
+ union
+ select DISTINCT device_no,plan_id,station_id,curr_node,node_content,number,updated_by from sta_device_production_log
+ where out_time BETWEEN #{startDt} AND #{endDt}
+ ) t
+ left join (select pap.id,pap.`name` as plan_name,pp.`name` as pro_name
+ from pr_assembly_plan pap, plt_product pp where pap.pro_id = pp.id) p on t.plan_id = p.id
+ left JOIN ba_user u on u.id=t.updated_by),
+ assistants_total as (select t.device_no,t.curr_node,t.node_content,t.number,p.pro_name, p.plan_name,u.id user_id,u.`name` user_name
+ FROM
+ ( SELECT a.*,SUBSTRING_INDEX( SUBSTRING_INDEX( a.assistants, ',', b.help_topic_id + 1 ), ',', - 1 ) AS assistant FROM
+ (select DISTINCT device_no,plan_id,station_id,curr_node,node_content,number,updated_by,assistants from sta_device_production_log_past
+ where assistants IS NOT NULL AND out_time BETWEEN #{startDt} AND #{endDt}
+ union
+ select DISTINCT device_no,plan_id,station_id,curr_node,node_content,number,updated_by,assistants from sta_device_production_log
+ where assistants IS NOT NULL AND out_time BETWEEN #{startDt} AND #{endDt}) a
+ JOIN mysql.help_topic AS b ON b.help_topic_id <![CDATA[ < ]]> ( LENGTH( a.assistants ) - LENGTH( REPLACE ( a.assistants, ',', '' ) ) + 1 )
+ ) t
+ left join (select pap.id,pap.`name` as plan_name,pp.`name` as pro_name
+ from pr_assembly_plan pap, plt_product pp where pap.pro_id = pp.id) p on t.plan_id = p.id
+ left JOIN ba_user u on u.id=t.assistant)
+ SELECT user_name, MAX(CASE record.type WHEN '璁″垝浠诲姟' THEN record.number ELSE 0 END) plan,
+ MAX(CASE record.type WHEN '涓存椂浠诲姟' THEN record.number ELSE 0 END) unplan_device,
+ MAX(CASE record.type WHEN '涓存椂浠诲姟(鏃犺澶囩爜)' THEN record.number ELSE 0 END) unplan,
+ MAX(CASE record.type WHEN '杈呭姪宸ヤ綔' THEN record.number ELSE 0 END) assistant
+ FROM (
+ select user_name,'璁″垝浠诲姟' AS type,sum(number) as number from total where curr_node IS NOT NULL GROUP BY user_name
+ UNION
+ select user_name,'涓存椂浠诲姟' AS type, sum(number) as number from total where curr_node IS NULL AND (device_no !='' AND device_no IS NOT NULL) GROUP BY user_name
+ UNION
+ select user_name,'涓存椂浠诲姟(鏃犺澶囩爜)' AS type, sum(number) as number from total where curr_node IS NULL AND (device_no IS NULL OR device_no ='') GROUP BY user_name
+ UNION
+ select user_name,'杈呭姪宸ヤ綔' AS type , sum(number) as number from assistants_total GROUP BY user_name ) record GROUP BY record.user_name;
+ </select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from oth_statistic_workload
where id = #{id,jdbcType=BIGINT}
diff --git a/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportCtrl.java b/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportCtrl.java
index 4ff045e..fdfb916 100644
--- a/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportCtrl.java
+++ b/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportCtrl.java
@@ -85,10 +85,20 @@
/**
* 浠诲姟鐪嬫澘 鏌ヨ鍦ㄤ骇浠诲姟鐪嬫澘
* */
+ @GetMapping(path="queryStatisticWork")
+ @Log("鏌ヨ璁㈠崟鐪嬫澘")
+ public BaseResponse<List<JSONObject>> queryStatisticWork(){
+ List<JSONObject> list = sv.queryStatisticWork();
+ return BaseResponseUtils.buildSuccess(list);
+ }
+ /**
+ * 浠诲姟鐪嬫澘 鏌ヨ鍦ㄤ骇浠诲姟鐪嬫澘
+ * */
@GetMapping(path="queryOrderList")
@Log("鏌ヨ璁㈠崟鐪嬫澘")
public BaseResponse<List<JSONObject>> queryOrderList(){
List<JSONObject> list = sv.queryOrderList();
return BaseResponseUtils.buildSuccess(list);
}
+
}
\ No newline at end of file
diff --git a/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportSv.java b/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportSv.java
index 8d6cd6e..a107e2a 100644
--- a/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportSv.java
+++ b/pms-parent/pms-web-other/src/main/java/com/dy/pmsOther/screen/ScreenReportSv.java
@@ -49,6 +49,7 @@
private OthStatisticWorkloadMapper workloadDao;
private PrOrderMapper orderDao;
+ private OthStatisticWorkloadMapper statisticWorkDao;
@Autowired
public void setOrderDao(PrOrderMapper orderDao) {
@@ -95,6 +96,10 @@
this.workloadDao = workloadDao;
}
+ @Autowired
+ public void setStatisticWorkDao(OthStatisticWorkloadMapper statisticWorkDao) {
+ this.statisticWorkDao = statisticWorkDao;
+ }
public Map<String, Object> getDeviceInfo(String deviceNo) {
if (StringUtils.isBlank(deviceNo)) {
@@ -221,4 +226,11 @@
List<JSONObject> list = assemblyWorkLastDao.queryAttendUser(startDt, endDt,todayDt);
return list;
}
+
+ public List<JSONObject> queryStatisticWork() {
+ Date startDt = Date.from(LocalDateTime.of(LocalDate.now(), LocalTime.of(0, 0, 0)).atZone(ZoneId.systemDefault()).toInstant());
+ Date endDt = new Date();
+ List<JSONObject> list = statisticWorkDao.queryStatisticWork(startDt, endDt);
+ return list;
+ }
}
--
Gitblit v1.8.0