From 77a145b91efa63fddd18ba243ddc95a631620f72 Mon Sep 17 00:00:00 2001
From: 杜洪波 <1074825718@qq.com>
Date: 星期一, 20 十月 2025 11:53:31 +0800
Subject: [PATCH] 会议代码提交
---
src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java | 60 ++++++++++++++++++++++++++----------------------------------
1 files changed, 26 insertions(+), 34 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..9cd0227 100644
--- a/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
+++ b/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
@@ -4,8 +4,6 @@
import java.util.*;
import java.time.YearMonth;
-import cn.hutool.core.util.ObjectUtil;
-import com.product.util.UnifySQLFunction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@@ -128,6 +126,7 @@
return baseDao.listTable(sb.toString(), param.toArray());
}
+
/**
* 鎵撳崱鏈堢湅鏉�(鏂�)
* @param fse
@@ -142,53 +141,46 @@
//鑾峰彇鏌ヨ鏈堜唤
String yearAndMonth=fse.getString("yearAndMonth");
- String day;
if (StringUtils.isEmpty(yearAndMonth)) {
yearAndMonth=DateUtils.formatDate(new Date(), "yyyy-MM");
- day = DateUtils.formatDate(new Date(), "yyyy-MM-dd");
- } else {
- Calendar calendar = Calendar.getInstance();
- calendar.setTime(DateUtils.parseDate(yearAndMonth + "-01"));
- calendar.add(Calendar.MONTH, 1);
- calendar.add(Calendar.DATE, -1);
- day = DateUtils.formatDate(calendar.getTime(), "yyyy-MM-dd");
}
+
+ //
+ String day=DateUtils.formatDate(new Date(), "yyyy-MM-dd");
StringBuilder sb=new StringBuilder();
List<Object> param=new ArrayList<>();
sb.append(" SELECT a.punch_date, ");
-// sb.append(" CASE WHEN b.uuid is not null THEN '璇峰亣' ELSE ");
-// sb.append(" CASE WHEN d.created_by is not null THEN ");
-// sb.append(" CONCAT(d.punch_type1,',',d.punch_type2,',',d.punch_type3,',',d.punch_type4) ");
-//
-// sb.append(" ELSE '鍏ㄥぉ鏈墦鍗�' END END reidis, ");
-// sb.append(" CASE WHEN b.uuid is not null THEN 1 ELSE ");
-// sb.append(" CASE WHEN d.created_by is not null THEN 0 ELSE 2 END END type, ");
- sb.append(" e.bk FROM (");
- /*=================*/
- 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 FROM (SELECT ? month_start) t");
+ sb.append(" CASE WHEN b.uuid is not null THEN '璇峰亣' ELSE ");
+ sb.append(" CASE WHEN d.created_by is not null THEN ");
+ sb.append(" CONCAT(d.punch_type1,',',d.punch_type2,',',d.punch_type3,',',d.punch_type4) ");
+
+ sb.append(" ELSE '鍏ㄥぉ鏈墦鍗�' END END reidis, ");
+ sb.append(" CASE WHEN b.uuid is not null THEN 1 ELSE ");
+ sb.append(" CASE WHEN d.created_by is not null THEN 0 ELSE 2 END END type, ");
+ sb.append(" e.bk FROM ");
+ sb.append(" (SELECT CONCAT(DATE_FORMAT(?, '%Y-%m-'),IF(i<9,CONCAT('0',i + 1),i + 1)) punch_date ");
param.add(yearAndMonth+"-01");
- sb.append("\n INNER JOIN (");
- sb.append("\n SELECT 0 as day UNION ALL SELECT 1 as day UNION ALL SELECT 2 as day UNION ALL SELECT 3 as day UNION ALL SELECT 4 as day UNION ALL SELECT 5 as day UNION ALL SELECT 6 as day UNION ALL SELECT 7 as day UNION ALL SELECT 8 as day UNION ALL SELECT 9 as day UNION ALL SELECT 10 as day UNION ALL SELECT 11 as day UNION ALL SELECT 12 as day UNION ALL SELECT 13 as day UNION ALL SELECT 14 as day UNION ALL SELECT 15 as day UNION ALL SELECT 16 as day UNION ALL SELECT 17 as day UNION ALL SELECT 18 as day UNION ALL SELECT 19 as day UNION ALL SELECT 20 as day UNION ALL SELECT 21 as day UNION ALL SELECT 22 as day UNION ALL SELECT 23 as day UNION ALL SELECT 24 as day UNION ALL SELECT 25 as day UNION ALL SELECT 26 as day UNION ALL SELECT 27 as day UNION ALL SELECT 28 as day UNION ALL SELECT 29 as day UNION ALL SELECT 30 as day");
- sb.append("\n ) d ON 1=1");
- sb.append("\n ) t1");
- sb.append("\n WHERE t1.punch_date<=?");
+
+ sb.append(" FROM (SELECT @row := @row + 1 as i FROM ");
+ sb.append(" (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1, ");
+ sb.append(" (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2, ");
+ sb.append(" (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3, ");
+ sb.append(" (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t4, ");
+ sb.append(" (SELECT @row := -1) t0) d WHERE i < DAY(last_day(DATE_FORMAT(?, '%Y-%m-01'))) and CONCAT(DATE_FORMAT(?, '%Y-%m-'),IF(i<9,CONCAT('0',i + 1),i + 1))<=? ) a ");
+ param.add(yearAndMonth+"-01");
+ param.add(yearAndMonth+"-01");
param.add(day);
- /*==================*/
- sb.append(") 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(" ");
+ sb.append(" on DATE_FORMAT( a.punch_date, '%Y-%m-%d' )>= DATE_FORMAT( b.start_time, '%Y-%m-%d' ) and DATE_FORMAT( a.punch_date, '%Y-%m-%d' )<= DATE_FORMAT( b.end_time, '%Y-%m-%d' ) ");
sb.append(" LEFT JOIN (SELECT * FROM product_oa_punch_record_v where created_by=?) d on a.punch_date=d.punch_time ");
param.add(user_id);
- sb.append(" LEFT JOIN (SELECT applicant,").append(UnifySQLFunction.dateFormat("replenish_date", "yyyy-MM-dd")).append(" replenish_date,").append(UnifySQLFunction.groupConcat("replacement_card_type")).append(" bk FROM product_oa_replenish_punch ");
- sb.append(" where applicant=? and flow_flag=2 GROUP BY applicant,").append(UnifySQLFunction.dateFormat("replenish_date", "yyyy-MM-dd")).append(") e on a.punch_date=e.replenish_date ");
+ sb.append(" LEFT JOIN (SELECT applicant,DATE_FORMAT( replenish_date, '%Y-%m-%d') replenish_date,GROUP_CONCAT(replacement_card_type) bk FROM product_oa_replenish_punch ");
+ sb.append(" where applicant=? and flow_flag=2 GROUP BY applicant,DATE_FORMAT( replenish_date, '%Y-%m-%d')) e on a.punch_date=e.replenish_date ");
param.add(user_id);
- sb.append(" where a.punch_date NOT IN (SELECT ").append(UnifySQLFunction.dateFormat("date_holiday", "yyyy-MM-dd")).append(" FROM product_sys_company_holiday) ");
+ sb.append(" where a.punch_date NOT IN (SELECT DATE_FORMAT( date_holiday, '%Y-%m-%d' ) FROM product_sys_company_holiday) ");
DataTableEntity dt = baseDao.listTable(sb.toString(), param.toArray());
FieldSetEntity time = baseDao.getFieldSetByFilter(com.product.org.admin.config.CmnConst.PRODUCT_OA_PUNCH_TIME, "CONCAT(',',attendance_object,',') like CONCAT('%',?,'%')", new String[]{","+user_id+","},false);
--
Gitblit v1.9.2