From 22d8b68b19e35bdd2086fd047296abe440639f32 Mon Sep 17 00:00:00 2001
From: 杜洪波 <1074825718@qq.com>
Date: 星期四, 18 九月 2025 15:52:27 +0800
Subject: [PATCH] 代码提交
---
src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java | 102 +++++++++++++++++++++++++++++++--------------------
1 files changed, 62 insertions(+), 40 deletions(-)
diff --git a/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java b/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
index d9b889a..0da4bdf 100644
--- a/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
+++ b/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
@@ -1,5 +1,6 @@
package com.product.administration.service;
+import java.sql.SQLException;
import java.time.LocalDate;
import java.util.*;
import java.time.YearMonth;
@@ -14,6 +15,7 @@
import com.product.administration.util.WorkDayUtil;
import com.product.common.lang.DateUtils;
import com.product.common.lang.StringUtils;
+import com.product.core.connection.ConnectionManager;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
@@ -169,7 +171,7 @@
/*=================*/
sb.append("\n SELECT *");
sb.append("\n FROM (");
- sb.append("\n SELECT ").append(UnifySQLFunction.dateAdd("t.month_start", "d.day", "DAY")).append(" punch_date");
+ sb.append("\n SELECT ").append(UnifySQLFunction.calculateTimeFunction("t.month_start", true, "d.day", "DAY")).append(" punch_date");
sb.append("\n FROM (SELECT ? month_start) t");
param.add(yearAndMonth+"-01");
sb.append("\n INNER JOIN (");
@@ -179,7 +181,7 @@
sb.append("\n WHERE t1.punch_date<=?");
param.add(day);
/*==================*/
- sb.append(") a ");
+ sb.append("\n) a ");
sb.append(" LEFT JOIN (SELECT * FROM product_oa_ask_for_leave WHERE user_id=? and flow_flag=2) b ");
param.add(user_id);
sb.append(" on ").append(UnifySQLFunction.dateFormat("a.punch_date", "yyyy-MM-dd")).append(">= ").append(UnifySQLFunction.dateFormat("b.start_time", "yyyy-MM-dd")).append(" and ").append(UnifySQLFunction.dateFormat("a.punch_date", "yyyy-MM-dd")).append("<= ").append(UnifySQLFunction.dateFormat("b.end_time", "yyyy-MM-dd")).append(" ");
@@ -476,39 +478,60 @@
ArrayList<String> param = new ArrayList<>();
StringBuilder sb=new StringBuilder();
- sb.append(" SELECT ");
- sb.append(" b.user_id,( SELECT user_name FROM product_sys_users WHERE user_id = b.user_id ) created_by, ");
- sb.append(" ( SELECT org_level_name FROM product_sys_org_levels WHERE uuid = dept_uuid ) dept_uuid, ");
- sb.append(" "+dayOfWork+" dayOfWork,workDayOfMonth,lateCome,leaveEarly,total_hours,askForLeave,("+dayOfWork+" - workDayOfMonth-askForLeave) absenteeism ");
- sb.append(" FROM(SELECT b.created_by,b.workDayOfMonth+bb.replenish_date workDayOfMonth,b.dept_uuid,b.lateCome1 lateCome,b.leaveEarly2 leaveEarly FROM ");
- sb.append(" (SELECT a.created_by,COUNT( day_of_month ) workDayOfMonth,a.dept_uuid, ");
- sb.append(" COUNT(IF( result1 like '%杩熷埌%', TRUE, NULL )) lateCome1,");
- sb.append(" COUNT(IF( result3 like '%杩熷埌%', TRUE, NULL )) lateCome2,");
- sb.append(" COUNT(IF( result2 like '%鏃╅��%', TRUE, NULL )) leaveEarly1, ");
- sb.append(" COUNT(IF( result4 like '%鏃╅��%', TRUE, NULL )) leaveEarly2 ");
- sb.append(" FROM(SELECT d.created_by,d.dept_uuid,DATE_FORMAT( d.punch_time, '%Y-%m-%d' ) day_of_month,punch_type1 result1,punch_type2 result2,punch_type3 result3,punch_type4 result4 ");
- sb.append(" FROM product_oa_punch_record_v d ");
- sb.append(" LEFT JOIN product_oa_punch_time e ON e.attendance_object like CONCAT('%',d.created_by,'%') ");
- sb.append(" where DATE_FORMAT( d.punch_time, '%Y-%m' )=? ");
+ sb.append("\nSELECT ");
+ sb.append("\n b.user_id,( SELECT user_name FROM product_sys_users WHERE user_id = b.user_id ) created_by, ");
+ sb.append("\n ( SELECT org_level_name FROM product_sys_org_levels WHERE uuid = dept_uuid ) dept_uuid, ");
+ sb.append("\n "+dayOfWork+" dayOfWork,workDayOfMonth,lateCome,leaveEarly,total_hours,askForLeave,("+dayOfWork+" - workDayOfMonth-askForLeave) absenteeism ");
+ sb.append("\nFROM(");
+ sb.append("\n SELECT b.created_by,b.workDayOfMonth+bb.replenish_date workDayOfMonth,b.dept_uuid,b.lateCome1 lateCome,b.leaveEarly2 leaveEarly");
+ sb.append("\n FROM (");
+ sb.append("\n SELECT a.created_by,COUNT( day_of_month ) workDayOfMonth,a.dept_uuid, ");
+ sb.append("\n COUNT(").append(UnifySQLFunction.ifFunction("result1 like '%杩熷埌%'", "TRUE", "NULL")).append(") lateCome1,");
+ sb.append("\n COUNT(").append(UnifySQLFunction.ifFunction("result3 like '%杩熷埌%'", "TRUE", "NULL")).append(") lateCome2,");
+ sb.append("\n COUNT(").append(UnifySQLFunction.ifFunction("result2 like '%鏃╅��%'", "TRUE", "NULL")).append(") leaveEarly1,");
+ sb.append("\n COUNT(").append(UnifySQLFunction.ifFunction("result4 like '%鏃╅��%'", "TRUE", "NULL")).append(") leaveEarly2");
+ sb.append("\n FROM(");
+ sb.append("\n SELECT d.created_by,d.dept_uuid,d.punch_time AS day_of_month,punch_type1 result1,punch_type2 result2,punch_type3 result3,punch_type4 result4 ");
+ sb.append("\n FROM product_oa_punch_record_v d ");
+ sb.append("\n LEFT JOIN product_oa_punch_time e ON e.attendance_object LIKE CONCAT('%',d.created_by,'%') ");
+ sb.append("\n WHERE ").append(UnifySQLFunction.timeFunction("d.punch_time", "year_and_month")).append(" =? ");
param.add(yearAndMonth);
- sb.append(" AND DATE_FORMAT( d.punch_time, '%Y-%m-%d' ) NOT IN ( SELECT date_holiday FROM product_sys_company_holiday )) a ");
- sb.append(" GROUP BY a.created_by,a.dept_uuid ) b ");
-
- sb.append(" LEFT JOIN (SELECT applicant,COUNT( 1 ) replenish_date FROM ");
- sb.append(" (SELECT applicant,DATE_FORMAT(replenish_date, '%Y-%m-%d') replenish_date FROM product_oa_replenish_punch a ");
- sb.append(" LEFT JOIN product_oa_punch_record_v b on DATE_FORMAT(a.replenish_date, '%Y-%m-%d')=b.punch_time and a.applicant=b.created_by ");
- sb.append(" where b.created_by is null and DATE_FORMAT(replenish_date, '%Y-%m')=? GROUP BY DATE_FORMAT(replenish_date, '%Y-%m-%d'),applicant)a ");
+ sb.append("\n AND ").append(UnifySQLFunction.timeFunction("d.punch_time", "year_month_day")).append(" NOT IN ( SELECT ").append(UnifySQLFunction.timeFunction("date_holiday", "year_month_day")).append(" FROM product_sys_company_holiday )");
+ sb.append("\n ) a ");
+ sb.append("\n GROUP BY a.created_by,a.dept_uuid");
+ sb.append("\n ) b ");
+ sb.append("\n LEFT JOIN (");
+ sb.append("\n SELECT applicant,COUNT( 1 ) replenish_date");
+ sb.append("\n FROM (");
+ sb.append("\n SELECT applicant,DATE_FORMAT(replenish_date, '%Y-%m-%d') replenish_date ");
+ sb.append("\n FROM product_oa_replenish_punch a ");
+ sb.append("\n LEFT JOIN product_oa_punch_record_v b on DATE_FORMAT(a.replenish_date, '%Y-%m-%d')=b.punch_time and a.applicant=b.created_by ");
+ sb.append("\n WHERE b.created_by is null and DATE_FORMAT(replenish_date, '%Y-%m')=? ");
+ sb.append("\n GROUP BY DATE_FORMAT(replenish_date, '%Y-%m-%d'),applicant");
+ sb.append("\n )a ");
param.add(yearAndMonth);
- sb.append(" GROUP BY applicant,DATE_FORMAT(replenish_date, '%Y-%m')) bb on b.created_by=bb.applicant ");
- sb.append(" ) a ");
- sb.append(" RIGHT JOIN (SELECT user_id,sum( duration ) total_hours FROM product_oa_work_overtime o ");
- sb.append(" LEFT JOIN product_oa_work_overtime_sub b ON o.uuid = b.main_uuid AND DATE_FORMAT( b.start_time, '%Y-%m' )=? ");
+ sb.append("\n GROUP BY applicant,DATE_FORMAT(replenish_date, '%Y-%m')) bb on b.created_by=bb.applicant ");
+ sb.append("\n) a ");
+ sb.append("\nRIGHT JOIN (");
+ sb.append("\n SELECT user_id,sum( duration ) total_hours");
+ sb.append("\n FROM product_oa_work_overtime o ");
+ sb.append("\n LEFT JOIN product_oa_work_overtime_sub b ON o.uuid = b.main_uuid AND DATE_FORMAT( b.start_time, '%Y-%m' )=? ");
param.add(yearAndMonth);
- sb.append(" GROUP BY o.user_id ) b ON a.created_by = b.user_id ");
- sb.append(" LEFT JOIN ");
- sb.append("(SELECT a.created_by,sum(askForLeave) askForLeave FROM (SELECT a.created_by,a.askForLeave-COUNT(1) askForLeave FROM ");
- sb.append(" (SELECT created_by,DATEDIFF(if(DATE_FORMAT(end_time, '%Y-%m-%d')<=?,DATE_FORMAT(end_time, '%Y-%m-%d'),?), if(DATE_FORMAT(start_time, '%Y-%m-%d' )>=?,DATE_FORMAT(start_time, '%Y-%m-%d' ),?))+1 askForLeave,start_time,end_time ");
- sb.append(" FROM product_oa_ask_for_leave a where (DATE_FORMAT( start_time, '%Y-%m' )=? or DATE_FORMAT( end_time, '%Y-%m' )=?) AND flow_flag = 2 ) a ");
+ sb.append("\n GROUP BY o.user_id");
+ sb.append("\n) b ON a.created_by = b.user_id ");
+ sb.append("\nLEFT JOIN (");
+ sb.append("\n SELECT a.created_by,sum(askForLeave) askForLeave");
+ sb.append("\n FROM (");
+ sb.append("\n SELECT a.created_by,a.askForLeave-COUNT(1) askForLeave");
+ sb.append("\n FROM (");
+ sb.append("\n SELECT");
+ sb.append("\n created_by,start_time,end_time,(");
+ sb.append("\n ").append(UnifySQLFunction.dateDiffFunction(UnifySQLFunction.ifFunction("DATE_FORMAT(end_time, '%Y-%m-%d')<=?", "DATE_FORMAT(end_time, '%Y-%m-%d')", "?"),
+ UnifySQLFunction.ifFunction("DATE_FORMAT(start_time, '%Y-%m-%d')>=?", "DATE_FORMAT(start_time, '%Y-%m-%d')", "?")));
+ sb.append("\n )+1 askForLeave ");
+ sb.append("\n FROM product_oa_ask_for_leave a");
+ sb.append("\n WHERE (DATE_FORMAT( start_time, '%Y-%m' )=? or DATE_FORMAT( end_time, '%Y-%m' )=?) AND flow_flag = 2 ");
+ sb.append("\n ) a ");
param.add(String.valueOf(monthEndDate));
param.add(String.valueOf(monthEndDate));
param.add(yearAndMonth+"-01");
@@ -516,18 +539,17 @@
param.add(yearAndMonth);
param.add(yearAndMonth);
- sb.append(" LEFT JOIN product_sys_company_holiday b on b.date_holiday<= a.end_time and b.date_holiday>=a.start_time and DATE_FORMAT(b.date_holiday, '%Y-%m')=?");
- sb.append(" GROUP BY a.created_by,a.askForLeave) a GROUP BY created_by )");
+ sb.append("\n LEFT JOIN product_sys_company_holiday b on b.date_holiday<= a.end_time and b.date_holiday>=a.start_time and DATE_FORMAT(b.date_holiday, '%Y-%m')=?");
+ sb.append("\n GROUP BY a.created_by,a.askForLeave");
+ sb.append("\n ) a GROUP BY created_by ");
+ sb.append("\n) c ON b.user_id = c.created_by ");
+ sb.append("\nORDER BY dept_uuid,a.created_by");
param.add(yearAndMonth);
-
- sb.append(" c ON b.user_id = c.created_by ");
- sb.append(" ORDER BY dept_uuid,a.created_by");
-
- DataTableEntity dataTableEntityReport = baseDao.listTable(sb.toString(), param.toArray());
+ DataTableEntity dataTableEntityReport = baseDao.listTable(UnifySQLFunction.timeFunctionReplace("multiple", sb.toString()), param.toArray());
for (int i = 0; i <dataTableEntityReport.getRows() ; i++) {
FieldSetEntity fs = dataTableEntityReport.getFieldSetEntity(i);
if (StringUtils.isEmpty(fs.getString("dept_uuid"))){
- FieldSetEntity org_level_name = baseDao.getFieldSetEntityBySQL("SELECT org_level_name FROM `product_sys_staffs` a LEFT JOIN product_sys_org_levels b on a.dept_uuid=b.uuid where a.user_id=?", new String[]{fs.getString("user_id")}, false);
+ FieldSetEntity org_level_name = baseDao.getFieldSetEntityBySQL("SELECT org_level_name FROM product_sys_staffs a LEFT JOIN product_sys_org_levels b on a.dept_uuid=b.uuid where a.user_id=?", new String[]{fs.getString("user_id")}, false);
fs.setValue("dept_uuid",org_level_name.getString("org_level_name"));
}
}
--
Gitblit v1.9.2