From 07ac3f874dda4318b3ba00b5ea08388a33f43d53 Mon Sep 17 00:00:00 2001
From: 6c <420680616@qq.com>
Date: 星期五, 18 七月 2025 11:17:40 +0800
Subject: [PATCH] 2025年7月18日 11:17:13-适配国产数据库
---
src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java | 532 ++++++++++++++++++++++++++++++++++-------------------------
1 files changed, 306 insertions(+), 226 deletions(-)
diff --git a/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java b/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
index 0a7fe28..d9b889a 100644
--- a/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
+++ b/src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java
@@ -1,11 +1,11 @@
package com.product.administration.service;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.Date;
-import java.util.List;
+import java.time.LocalDate;
+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;
@@ -142,68 +142,79 @@
//鑾峰彇鏌ヨ鏈堜唤
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,e.bk,f.morning_work,f.morning_work_off,f.afternoon_work,f.afternoon_work_off, ");
-
- sb.append(" CONCAT(if(d.punch_range_one is not null and d.punch_range_one>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0) ");
- sb.append(" ,',',if(d.punch_range_two is not null and d.punch_range_two>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0) ");
- sb.append(" ,',',if(d.punch_time_three is not null and d.punch_time_three>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0) ");
- sb.append(" ,',',if(d.punch_range_four is not null and d.punch_range_four>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0)) dkfw, ");
- sb.append(" CASE WHEN b.uuid is not null THEN c.dict_label ELSE ");
- sb.append(" CASE WHEN d.uuid is not null THEN ");
- sb.append(" CONCAT(IF(d.punch_time_one IS TRUE, IF ( DATE_FORMAT(d.punch_time_one, '%H:%i:%S' )>DATE_FORMAT(f.morning_work, '%H:%i:%S'), CONCAT('涓婂崍杩熷埌',TIMESTAMPDIFF(MINUTE, DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.morning_work, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S'),d.punch_time_one),'鍒嗛挓'), 0 ), '涓婂崍涓婄彮鏈墦鍗�' ) ");
- sb.append(" ,',',IF(d.punch_time_two IS TRUE, IF ( DATE_FORMAT(d.punch_time_two, '%H:%i:%S' )<DATE_FORMAT(f.morning_work_off, '%H:%i:%S') and f.morning_work_off is not null, CONCAT('涓婂崍鏃╅��',TIMESTAMPDIFF(MINUTE,d.punch_time_two,DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.morning_work_off, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S')),'鍒嗛挓'), 0 ), '涓婂崍涓嬬彮鏈墦鍗�' ) ");
- sb.append(" ,',',IF(d.punch_time_three IS TRUE, IF ( DATE_FORMAT(d.punch_time_three, '%H:%i:%S' )>DATE_FORMAT(f.afternoon_work, '%H:%i:%S') and f.afternoon_work is not null, CONCAT('涓嬪崍杩熷埌',TIMESTAMPDIFF(MINUTE, DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.afternoon_work, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S'),d.punch_time_three),'鍒嗛挓'), 0 ), '涓嬪崍涓婄彮鏈墦鍗�' ) ");
- sb.append(" ,',',IF(d.punch_time_four IS TRUE, IF ( DATE_FORMAT(d.punch_time_four, '%H:%i:%S' )<DATE_FORMAT(f.afternoon_work_off, '%H:%i:%S'), CONCAT('涓嬪崍鏃╅��',TIMESTAMPDIFF(MINUTE,d.punch_time_four,DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.afternoon_work_off, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S')),'鍒嗛挓'), 0 ), '涓嬪崍涓嬬彮鏈墦鍗�' )) ");
- sb.append(" ELSE '鍏ㄥぉ鏈墦鍗�' END END reidis,CASE WHEN b.uuid is not null THEN 1 ELSE CASE WHEN d.uuid is not null THEN 0 ELSE 2 END END type ");
- sb.append(" FROM (SELECT CONCAT(DATE_FORMAT(?, '%Y-%m-'),IF(i<9,CONCAT('0',i + 1),i + 1)) punch_date ");
+ 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");
param.add(yearAndMonth+"-01");
- 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");
+ 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<=?");
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 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_sys_dict where dict_name='Leave_Type') c on b.leave_type =c.dict_value ");
- sb.append(" LEFT JOIN product_oa_punch_record d on a.punch_date=DATE_FORMAT( d.punch_time_one, '%Y-%m-%d' ) ");
- sb.append(" LEFT JOIN (SELECT applicant,DATE_FORMAT( replenish_date, '%Y-%m-%d') replenish_date,max(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 ");
+ 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(" 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 product_oa_punch_time f on d.punch_congfig_uuid =f.punch_site_uuid ");
- sb.append(" where a.punch_date NOT IN (SELECT DATE_FORMAT( date_holiday, '%Y-%m-%d' ) FROM product_sys_company_holiday) ");
+ 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 ");
+ 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) ");
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);
+
for (int i = 0; i < dt.getRows(); i++) {
FieldSetEntity fs = dt.getFieldSetEntity(i);
//褰撳ぉ鎵撳崱澶勭悊
if (fs.getString("punch_date").equals(day)){
- if (fs.getString("reidis").equals("鍏ㄥぉ鏈墦鍗�")){
- fs.setValue("reidis",null);
- fs.setValue("dkfw",null);
- }else {
- String[] reidis = fs.getString("reidis").split(",");
- for (int j = 0; j <reidis.length ; j++) {
- if (reidis[j].contains("鏈墦鍗�")){
- reidis[j]="0";
- }
- }
- String string = Arrays.toString(reidis);
- fs.setValue("reidis",string);
- }
+ if (null!=fs.getString("reidis")&&fs.getString("reidis").equals("鍏ㄥぉ鏈墦鍗�")){
+ fs.setValue("reidis",null);
+ fs.setValue("dkfw",null);
+ }else {
+ String[] reidis = fs.getString("reidis").split(",");
+ for (int j = 0; j <reidis.length ; j++) {
+ if (reidis[j].contains("鏈墦鍗�")){
+ reidis[j]="0";
+ }
+ }
+ String string="";
+ if(reidis.length!=1){
+ string = Arrays.toString(reidis);
+ }else {
+ string=fs.getString("reidis");
+ }
+ fs.setValue("reidis",string);
+ }
}
//鑾峰彇琛ュ崱鏍囪瘑
List<String> bk=new ArrayList<>();
@@ -211,60 +222,59 @@
bk= Arrays.asList(fs.getString("bk").split(","));
}
if (!StringUtils.isEmpty(fs.getString("reidis"))&&fs.getString("reidis").equals("鍏ㄥぉ鏈墦鍗�")&&!StringUtils.isEmpty(fs.getString("bk"))){
- if (!bk.contains("1")){
+ if (!bk.contains("1")){
fs.setValue("result_one", "涓婂崍涓婄彮鏈墦鍗�");
}
- if (!bk.contains("2")&&!StringUtils.isEmpty(fs.getString("morning_work_off"))){
+ if (!bk.contains("2")&&!StringUtils.isEmpty(time.getString("morning_work_off"))){
fs.setValue("result_two", "涓婂崍涓嬬彮鏈墦鍗�");
}
- if (!bk.contains("3")&&!StringUtils.isEmpty(fs.getString("afternoon_work"))){
+ if (!bk.contains("3")&&!StringUtils.isEmpty(time.getString("afternoon_work"))){
fs.setValue("result_three", "涓嬪崍涓婄彮鏈墦鍗�");
}
- if (!bk.contains("4")){
+ if (!bk.contains("4")){
fs.setValue("result_four", "涓嬪崍涓嬬彮鏈墦鍗�");
+ }
+ if (StringUtils.isEmpty(fs.getString("result_one"))&&StringUtils.isEmpty(fs.getString("result_two"))&&StringUtils.isEmpty(fs.getString("result_three"))&&StringUtils.isEmpty(fs.getString("result_four"))){
+ fs.setValue("type",1);
+ fs.setValue("result_one","宸茶ˉ鍗�");
}
continue;
}
if ("0".equals(fs.getString("type"))){
String[] reidis = fs.getString("reidis").split(",");
- String[] dkfwsArr = fs.getString("dkfw").split(",");
String a="1";
for (int j = 0; j < reidis.length; j++) {
- String dkfws="";
- if (!dkfwsArr[j].equals("0")){
- dkfws=dkfwsArr[j];
- }
if (reidis[j].contains("涓婂崍杩熷埌")) {
if (!bk.contains("1")) {
- fs.setValue("result_one", reidis[j]+dkfws);
+ fs.setValue("result_one", reidis[j]);
a = "2";
}
}
if (reidis[j].contains("涓婂崍鏃╅��")) {
if (!bk.contains("2")) {
- fs.setValue("result_two", reidis[j]+dkfws);
+ fs.setValue("result_two", reidis[j]);
a = "2";
}
}
if (reidis[j].contains("涓嬪崍杩熷埌")) {
if (!bk.contains("3")) {
- fs.setValue("result_three", reidis[j]+dkfws);
+ fs.setValue("result_three", reidis[j]);
a = "2";
}
}
if (reidis[j].contains("涓嬪崍鏃╅��")) {
if (!bk.contains("3")) {
- fs.setValue("result_four", reidis[j]+dkfws);
+ fs.setValue("result_four", reidis[j]);
a = "2";
}
}
if (reidis[j].contains("涓婂崍涓婄彮鏈墦鍗�")) {
fs.setValue("result_one", reidis[j]);
}
- if (reidis[j].contains("涓婂崍涓嬬彮鏈墦鍗�")) {
+ if (reidis[j].contains("涓婂崍涓嬬彮鏈墦鍗�")&&!StringUtils.isEmpty(time.getString("morning_work_off"))) {
fs.setValue("result_two", reidis[j]);
}
- if (reidis[j].contains("涓嬪崍涓婄彮鏈墦鍗�")) {
+ if (reidis[j].contains("涓嬪崍涓婄彮鏈墦鍗�")&&!StringUtils.isEmpty(time.getString("afternoon_work"))) {
fs.setValue("result_three", reidis[j]);
}
if (reidis[j].contains("涓嬪崍涓嬬彮鏈墦鍗�")) {
@@ -282,6 +292,159 @@
}
return dt;
}
+// public DataTableEntity listMonthKanBanNew(FieldSetEntity fse) {
+// //鑾峰彇褰撳墠浜轰俊鎭�
+// Integer user_id=fse.getInteger("user_id"); //user_id
+// if(user_id==null){
+// return null;
+// }
+//
+// //鑾峰彇鏌ヨ鏈堜唤
+// String yearAndMonth=fse.getString("yearAndMonth");
+// if (StringUtils.isEmpty(yearAndMonth)) {
+// yearAndMonth=DateUtils.formatDate(new Date(), "yyyy-MM");
+// }
+//
+// //
+// String day=DateUtils.formatDate(new Date(), "yyyy-MM-dd");
+//
+// StringBuilder sb=new StringBuilder();
+// List<Object> param=new ArrayList<>();
+//
+// sb.append(" SELECT a.punch_date,e.bk,f.morning_work,f.morning_work_off,f.afternoon_work,f.afternoon_work_off, ");
+//
+// sb.append(" CONCAT(if(d.punch_range_one is not null and d.punch_range_one>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0) ");
+// sb.append(" ,',',if(d.punch_range_two is not null and d.punch_range_two>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0) ");
+// sb.append(" ,',',if(d.punch_time_three is not null and d.punch_time_three>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0) ");
+// sb.append(" ,',',if(d.punch_range_four is not null and d.punch_range_four>f.distance,'瓒呭嚭鎵撳崱鑼冨洿',0)) dkfw, ");
+// sb.append(" CASE WHEN b.uuid is not null THEN '璇峰亣' ELSE ");
+// sb.append(" CASE WHEN d.uuid is not null THEN ");
+// sb.append(" CONCAT(IF(d.punch_time_one IS TRUE, IF ( DATE_FORMAT(d.punch_time_one, '%H:%i:%S' )>DATE_FORMAT(f.morning_work, '%H:%i:%S'), CONCAT('涓婂崍杩熷埌',TIMESTAMPDIFF(MINUTE, DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.morning_work, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S'),d.punch_time_one),'鍒嗛挓'), 0 ), '涓婂崍涓婄彮鏈墦鍗�' ) ");
+// sb.append(" ,',',IF(d.punch_time_two IS TRUE, IF ( DATE_FORMAT(d.punch_time_two, '%H:%i:%S' )<DATE_FORMAT(f.morning_work_off, '%H:%i:%S') and f.morning_work_off is not null, CONCAT('涓婂崍鏃╅��',TIMESTAMPDIFF(MINUTE,d.punch_time_two,DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.morning_work_off, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S')),'鍒嗛挓'), 0 ), '涓婂崍涓嬬彮鏈墦鍗�' ) ");
+// sb.append(" ,',',IF(d.punch_time_three IS TRUE, IF ( DATE_FORMAT(d.punch_time_three, '%H:%i:%S' )>DATE_FORMAT(f.afternoon_work, '%H:%i:%S') and f.afternoon_work is not null, CONCAT('涓嬪崍杩熷埌',TIMESTAMPDIFF(MINUTE, DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.afternoon_work, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S'),d.punch_time_three),'鍒嗛挓'), 0 ), '涓嬪崍涓婄彮鏈墦鍗�' ) ");
+// sb.append(" ,',',IF(d.punch_time_four IS TRUE, IF ( DATE_FORMAT(d.punch_time_four, '%H:%i:%S' )<DATE_FORMAT(f.afternoon_work_off, '%H:%i:%S'), CONCAT('涓嬪崍鏃╅��',TIMESTAMPDIFF(MINUTE,d.punch_time_four,DATE_FORMAT(CONCAT(a.punch_date,' ',DATE_FORMAT(f.afternoon_work_off, '%H:%i:%S')),'%Y-%m-%d %H:%i:%S')),'鍒嗛挓'), 0 ), '涓嬪崍涓嬬彮鏈墦鍗�' )) ");
+// sb.append(" ELSE '鍏ㄥぉ鏈墦鍗�' END END reidis,CASE WHEN b.uuid is not null THEN 1 ELSE CASE WHEN d.uuid is not null THEN 0 ELSE 2 END END type ");
+// sb.append(" FROM (SELECT CONCAT(DATE_FORMAT(?, '%Y-%m-'),IF(i<9,CONCAT('0',i + 1),i + 1)) punch_date ");
+// param.add(yearAndMonth+"-01");
+// 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(" LEFT JOIN (SELECT * FROM product_oa_ask_for_leave WHERE user_id=? and flow_flag=2) b ");
+// param.add(user_id);
+// 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 product_oa_punch_record d on a.punch_date=DATE_FORMAT( d.punch_time_one, '%Y-%m-%d' ) ");
+// sb.append(" LEFT JOIN (SELECT applicant,DATE_FORMAT( replenish_date, '%Y-%m-%d') replenish_date,max(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(" LEFT JOIN product_oa_punch_time f on d.punch_congfig_uuid =f.punch_site_uuid ");
+// 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());
+// for (int i = 0; i < dt.getRows(); i++) {
+// FieldSetEntity fs = dt.getFieldSetEntity(i);
+// //褰撳ぉ鎵撳崱澶勭悊
+// if (fs.getString("punch_date").equals(day)){
+// if (null!=fs.getString("reidis")&&fs.getString("reidis").equals("鍏ㄥぉ鏈墦鍗�")){
+// fs.setValue("reidis",null);
+// fs.setValue("dkfw",null);
+// }else {
+// String[] reidis = fs.getString("reidis").split(",");
+// for (int j = 0; j <reidis.length ; j++) {
+// if (reidis[j].contains("鏈墦鍗�")){
+// reidis[j]="0";
+// }
+// }
+// String string="";
+// if(reidis.length!=1){
+// string = Arrays.toString(reidis);
+// }else {
+// string=fs.getString("reidis");
+// }
+// fs.setValue("reidis",string);
+// }
+// }
+// //鑾峰彇琛ュ崱鏍囪瘑
+// List<String> bk=new ArrayList<>();
+// if (!StringUtils.isEmpty(fs.getString("bk"))){
+// bk= Arrays.asList(fs.getString("bk").split(","));
+// }
+// if (!StringUtils.isEmpty(fs.getString("reidis"))&&fs.getString("reidis").equals("鍏ㄥぉ鏈墦鍗�")&&!StringUtils.isEmpty(fs.getString("bk"))){
+// if (!bk.contains("1")){
+// fs.setValue("result_one", "涓婂崍涓婄彮鏈墦鍗�");
+// }
+// if (!bk.contains("2")&&!StringUtils.isEmpty(fs.getString("morning_work_off"))){
+// fs.setValue("result_two", "涓婂崍涓嬬彮鏈墦鍗�");
+// }
+// if (!bk.contains("3")&&!StringUtils.isEmpty(fs.getString("afternoon_work"))){
+// fs.setValue("result_three", "涓嬪崍涓婄彮鏈墦鍗�");
+// }
+// if (!bk.contains("4")){
+// fs.setValue("result_four", "涓嬪崍涓嬬彮鏈墦鍗�");
+// }
+// continue;
+// }
+// if ("0".equals(fs.getString("type"))){
+// String[] reidis = fs.getString("reidis").split(",");
+// String[] dkfwsArr = fs.getString("dkfw").split(",");
+// String a="1";
+// for (int j = 0; j < reidis.length; j++) {
+// String dkfws="";
+// if (!dkfwsArr[j].equals("0")){
+// dkfws=dkfwsArr[j];
+// }
+// if (reidis[j].contains("涓婂崍杩熷埌")) {
+// if (!bk.contains("1")) {
+// fs.setValue("result_one", reidis[j]+dkfws);
+// a = "2";
+// }
+// }
+// if (reidis[j].contains("涓婂崍鏃╅��")) {
+// if (!bk.contains("2")) {
+// fs.setValue("result_two", reidis[j]+dkfws);
+// a = "2";
+// }
+// }
+// if (reidis[j].contains("涓嬪崍杩熷埌")) {
+// if (!bk.contains("3")) {
+// fs.setValue("result_three", reidis[j]+dkfws);
+// a = "2";
+// }
+// }
+// if (reidis[j].contains("涓嬪崍鏃╅��")) {
+// if (!bk.contains("3")) {
+// fs.setValue("result_four", reidis[j]+dkfws);
+// a = "2";
+// }
+// }
+// if (reidis[j].contains("涓婂崍涓婄彮鏈墦鍗�")) {
+// fs.setValue("result_one", reidis[j]);
+// }
+// if (reidis[j].contains("涓婂崍涓嬬彮鏈墦鍗�")) {
+// fs.setValue("result_two", reidis[j]);
+// }
+// if (reidis[j].contains("涓嬪崍涓婄彮鏈墦鍗�")) {
+// fs.setValue("result_three", reidis[j]);
+// }
+// if (reidis[j].contains("涓嬪崍涓嬬彮鏈墦鍗�")) {
+// fs.setValue("result_four", reidis[j]);
+// }
+// }
+// if ("1".equals(a)){
+// fs.setValue("type",a);
+// fs.setValue("result_one","姝ｅ父鎵撳崱");
+// }
+//
+// }else {
+// fs.setValue("result_one",fs.getString("reidis"));
+// }
+// }
+// return dt;
+// }
/**
* 鑰冨嫟缁熻鎶ヨ〃
* @param fse
@@ -289,38 +452,15 @@
*/
public DataTableEntity listMonthReport(FieldSetEntity fse) {
- //鑾峰彇褰撳墠浜轰俊鎭�
- SystemUser currentUser=SpringMVCContextHolder.getCurrentUser();
- List<Object>param1=new ArrayList<>();
- param1.add(currentUser.getUser_id());
- param1.add(currentUser.getUser_id()+",");
- //鑾峰彇浣跨敤鍒扮殑璁剧疆瑙勫垯
- StringBuilder sbPunchTime=new StringBuilder();
- sbPunchTime.append(" SELECT DISTINCT c.* ");
- sbPunchTime.append(" FROM ( ");
- sbPunchTime.append(" SELECT d.* ");
- sbPunchTime.append(" FROM product_oa_punch_time d ");
- sbPunchTime.append(" left JOIN product_oa_punch_record b ");
- sbPunchTime.append(" on d.punch_site_uuid=b.punch_congfig_uuid and d.created_by=? ");
- sbPunchTime.append(" ) c ");
- sbPunchTime.append(" where c.uuid in ( ");
- sbPunchTime.append(" SELECT DISTINCT uuid ");
- sbPunchTime.append(" FROM product_oa_punch_time ");
- sbPunchTime.append(" where LOCATE(?, concat(attendance_object,','))>0 ");
- sbPunchTime.append(" ) ");
- DataTableEntity dataTableEntity = baseDao.listTable(sbPunchTime.toString(), param1.toArray());
- List<DataTableEntity> dataTableEntityLits=new ArrayList<>();
- for(int i=0;i<dataTableEntity.getRows();i++){
-
- FieldSetEntity fseCompanyPunch = dataTableEntity.getData().get(i);
- if (fseCompanyPunch==null) {
- throw new BaseException(SystemCode.COMPANY_PUNCH_TIME_NOT_CONFIG.getValue(), SystemCode.COMPANY_PUNCH_TIME_NOT_CONFIG.getText(), this.getClass(), "listMonthReport");
- }
//鑾峰彇鏌ヨ鏈堜唤
String yearAndMonth=fse.getString("yearAndMonth");
if (StringUtils.isEmpty(yearAndMonth)) {
yearAndMonth=DateUtils.formatDate(new Date(), "yyyy-MM");
}
+ int year = Integer.parseInt(yearAndMonth.split("-")[0]);
+ int month = Integer.parseInt(yearAndMonth.split("-")[1]);
+
+ LocalDate monthEndDate = YearMonth.of(year, month).atEndOfMonth();
//鑾峰彇姣忔湀澶╂暟
int dayOfMonth = DateUtils.getMonthHasDays(DateUtils.parseDate(yearAndMonth));
@@ -333,149 +473,89 @@
}else {
dayOfWork=dayOfMonth-dtHoliday.getRows();
}
+ 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' )=? ");
+ 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 ");
- StringBuilder sb=new StringBuilder();
- List<Object>param=new ArrayList<>();
- String morinng_work = fseCompanyPunch.getValue("morning_work").toString().split(" ")[1];
- param.add(morinng_work);
- if(ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("morning_work_off"))&& ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("afternoon_work"))){
- String morinng_work_off = fseCompanyPunch.getValue("morning_work_off").toString().split(" ")[1];
- String afternoon_work = fseCompanyPunch.getValue("afternoon_work").toString().split(" ")[1];
- param.add(afternoon_work);
- param.add(morinng_work_off);
+ 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 ");
+ 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' )=? ");
+ 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 ");
+ param.add(String.valueOf(monthEndDate));
+ param.add(String.valueOf(monthEndDate));
+ param.add(yearAndMonth+"-01");
+ param.add(yearAndMonth+"-01");
+
+ 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 )");
+ 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());
+ 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);
+ fs.setValue("dept_uuid",org_level_name.getString("org_level_name"));
}
-
- String afternoon_work_off = fseCompanyPunch.getValue("afternoon_work_off").toString().split(" ")[1];
- String uuid = fseCompanyPunch.getValue("uuid").toString();
-
- param.add(afternoon_work_off);
- param.add(yearAndMonth);
- param.add(uuid);
- param.add(yearAndMonth);
- param.add(yearAndMonth);
- sb.append(" SELECT ");
- sb.append(" (SELECT user_name FROM product_sys_users WHERE user_id=a.created_by)created_by, ");
- sb.append(" (SELECT org_level_name FROM product_sys_org_levels WHERE uuid=dept_uuid)dept_uuid, ");
- sb.append(" dayOfWork,workDayOfMonth,lateCome,leaveEarly,total_hours,casual_leave,medical_leave,marriage_leave,paid_leave,other_leave,(dayOfWork-workDayOfMonth)absenteeism ");
- sb.append(" FROM ( ");
- if(ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("morning_work_off"))&& ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("afternoon_work"))){
- sb.append(" SELECT b.created_by,b.dayOfWork,b.workDayOfMonth,b.dept_uuid,b.lateCome1+b.lateCome2 lateCome,b.leaveEarly1+b.leaveEarly2 leaveEarly");
- }else {
- sb.append(" SELECT b.created_by,b.dayOfWork,b.workDayOfMonth,b.dept_uuid,b.lateCome1 lateCome,b.leaveEarly2 leaveEarly");
- }
-
- sb.append(" FROM ( ");
- sb.append(" SELECT ");
- sb.append(" a.created_by,"+dayOfWork+" dayOfWork,COUNT(day_of_month)workDayOfMonth,a.dept_uuid, ");
- sb.append(" COUNT(");
- sb.append(" IF ");
- sb.append(" ( result1 = '杩熷埌', TRUE, NULL )) lateCome1,");
- if(ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("morning_work_off"))&& ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("afternoon_work"))){
- sb.append(" COUNT(");
- sb.append(" IF ");
- sb.append(" ( result2 = '杩熷埌', TRUE, NULL )) lateCome2,");
- sb.append(" COUNT(");
- sb.append(" IF ");
- sb.append(" ( result3 = '鏃╅��', TRUE, NULL )) leaveEarly1,");
-
- }
- sb.append(" COUNT(");
- sb.append(" IF ");
- sb.append(" ( result4 = '鏃╅��', TRUE, NULL )) leaveEarly2 ");
- sb.append(" FROM ( ");
- sb.append(" SELECT d.created_by,d.dept_uuid,DATE_FORMAT( d.created_utc_datetime, '%Y-%m-%d' ) day_of_month,punch_time_one, ");
- sb.append(" IF ");
- sb.append(" ( punch_time_one IS TRUE, IF ( DATE_FORMAT( punch_time_one, '%H:%i:%S' )> ?, '杩熷埌', '姝ｅ父' ), '鏈墦鍗�' ) ");
- sb.append(" result1 ,");
- if(ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("morning_work_off"))&& ObjectUtil.isNotEmpty(fseCompanyPunch.getValue("afternoon_work"))){
- sb.append(" IF ");
- sb.append(" ( punch_time_three IS TRUE, IF ( DATE_FORMAT( punch_time_three, '%H:%i:%S' )> ?, '杩熷埌', '姝ｅ父' ), '鏈墦鍗�' ) ");
- sb.append(" result2 ,");
- sb.append(" IF ");
- sb.append(" ( punch_time_two IS TRUE, IF ( DATE_FORMAT( punch_time_two, '%H:%i:%S' )< ?, '鏃╅��', '姝ｅ父' ), '鏈墦鍗�' ) ");
- sb.append(" result3 ,");
- }
-
- sb.append(" IF ");
- sb.append(" ( punch_time_four IS TRUE, IF ( DATE_FORMAT( punch_time_four, '%H:%i:%S' ) < ?, '鏃╅��', '姝ｅ父' ), '鏈墦鍗�' ) ");
- sb.append(" result4 ");
- sb.append(" FROM product_oa_punch_record d ");
- sb.append(" left JOIN product_oa_punch_time e ");
- sb.append(" on d.punch_congfig_uuid=e.punch_site_uuid ");
- sb.append(" and DATE_FORMAT( d.created_utc_datetime,'%Y-%m')=? AND DATE_FORMAT(punch_time_one,'%Y-%m-%d') NOT IN(SELECT date_holiday FROM product_sys_company_holiday) ");
- sb.append(" where e.uuid=? ");
- sb.append(" )a ");
- sb.append(" GROUP BY a.created_by,a.dept_uuid ");
- sb.append(" )b");
- sb.append(" )a ");
- sb.append(" LEFT JOIN (SELECT user_id, sum( duration ) total_hours FROM product_oa_work_overtime o left join product_oa_work_overtime_sub b on o.uuid=b.main_uuid and DATE_FORMAT( b.start_time, '%Y-%m' )=? GROUP BY o.user_id)b ");
- sb.append(" ON a.created_by=b.user_id ");
- sb.append(" LEFT JOIN ( ");
- sb.append(" SELECT ");
- sb.append(" created_by,sum(IF(leave_type=1,TRUE,0))casual_leave,sum(IF(leave_type=2,TRUE,0))medical_leave, ");
- sb.append(" sum(IF(leave_type=9,TRUE,0))marriage_leave,sum(IF(leave_type=4,TRUE,0))paid_leave, ");
- sb.append(" sum(IF(leave_type!=1 && leave_type!=2 && leave_type!=4 && leave_type!=9,TRUE,0))other_leave ");
- sb.append(" FROM product_oa_ask_for_leave ");
- sb.append(" WHERE DATE_FORMAT(start_time,'%Y-%m')=? and flow_flag=2 ");
- sb.append(" GROUP BY created_by ");
- sb.append(" )c ");
- sb.append(" ON a.created_by=c.created_by ");
- sb.append(" ORDER BY dept_uuid,a.created_by ");
- DataTableEntity dataTableEntityReport = baseDao.listTable(sb.toString(), param.toArray());
- if(dataTableEntityReport.getRows()!=0){
- dataTableEntityLits.add(baseDao.listTable(sb.toString(), param.toArray()));
- }
-
- }
- DataTableEntity dataTableEntity1=new DataTableEntity();
- if(ObjectUtil.isNotEmpty(dataTableEntityLits)){
- dataTableEntity1=dataTableEntityLits.get(0);
- //鏈湀鍐呰繜鍒版棭閫�鏁扮殑绱姞
- for (int i = 1; i < dataTableEntityLits.size(); i++) {
- FieldSetEntity fieldSetEntity = dataTableEntityLits.get(i).getData().get(0);
- FieldSetEntity fieldSetEntity1 = dataTableEntity1.getData().get(0);
- dataTableEntity1.getData().get(0).setValue("lateCome",Integer.parseInt(fieldSetEntity.getValue("lateCome").toString())+Integer.parseInt(fieldSetEntity1.getValue("lateCome").toString()));
- dataTableEntity1.getData().get(0).setValue("leaveEarly",Integer.parseInt(fieldSetEntity.getValue("leaveEarly").toString())+Integer.parseInt(fieldSetEntity1.getValue("leaveEarly").toString()));
- dataTableEntity1.getData().get(0).setValue("workDayOfMonth",Integer.parseInt(fieldSetEntity.getValue("workDayOfMonth").toString())+Integer.parseInt(fieldSetEntity1.getValue("workDayOfMonth").toString()));
- dataTableEntity1.getData().get(0).setValue("absenteeism",Integer.parseInt(fieldSetEntity1.getValue("absenteeism").toString())-Integer.parseInt(fieldSetEntity.getValue("workDayOfMonth").toString()));
- }
-
-
-
}
//澶勭悊琛ュ崱鐨勬儏鍐�
- List<Object>param2=new ArrayList<>();
- param2.add(currentUser);
- param2.add(fse.getString("yearAndMonth"));
+// List<Object>param2= new ArrayList<>();
+// param2.add(fse.getString("yearAndMonth"));
+//
+// StringBuilder sbReplenish=new StringBuilder();
+// sbReplenish.append(" SELECT DISTINCT replenish_date ");
+// sbReplenish.append(" FROM product_oa_replenish_punch ");
+// sbReplenish.append(" WHERE");
+// sbReplenish.append(" applicant =? and flow_flag=2");
+// sbReplenish.append(" AND DATE_FORMAT( replenish_date, '%Y-%m' )=? ");
+// sbReplenish.append(" and DATE_FORMAT( replenish_date, '%Y-%m-%d') ");
+// sbReplenish.append(" not in (");
+// sbReplenish.append(" SELECT DISTINCT record_data FROM ");
+// sbReplenish.append(" ( ");
+// sbReplenish.append(" select DATE_FORMAT(punch_time, '%Y-%m-%d') as record_data from product_oa_punch_record_v ");
+// sbReplenish.append(" )n )");
+// DataTableEntity dataTableEntityReplenish = baseDao.listTable(sbReplenish.toString(), param2.toArray());
+// if(dataTableEntityReplenish.getRows()!=0){
+// dataTableEntityReport.getData().get(0).setValue("workDayOfMonth",Integer.parseInt(dataTableEntityReport.getData().get(0).getString("workDayOfMonth"))+dataTableEntityReplenish.getRows());
+// dataTableEntityReport.getData().get(0).setValue("dayOfWork",Integer.parseInt(dataTableEntityReport.getData().get(0).getString("dayOfWork"))-dataTableEntityReplenish.getRows());
+// }
- StringBuilder sbReplenish=new StringBuilder();
- sbReplenish.append(" SELECT DISTINCT replenish_date ");
- sbReplenish.append(" FROM product_oa_replenish_punch ");
- sbReplenish.append(" WHERE");
- sbReplenish.append(" created_by =? and flow_flag=2");
- sbReplenish.append(" AND DATE_FORMAT( replenish_date, '%Y-%m' )=? ");
- sbReplenish.append(" and DATE_FORMAT( replenish_date, '%Y-%m-%d') ");
- sbReplenish.append(" not in (");
- sbReplenish.append(" SELECT DISTINCT record_data FROM ");
- sbReplenish.append(" ( ");
- sbReplenish.append(" select DATE_FORMAT(punch_time_one, '%Y-%m-%d') as record_data from product_oa_punch_record ");
- sbReplenish.append(" union all select DATE_FORMAT(punch_time_two, '%Y-%m-%d') as record_data from product_oa_punch_record");
- sbReplenish.append(" union all select DATE_FORMAT(punch_time_three, '%Y-%m-%d') as record_data from product_oa_punch_record");
- sbReplenish.append(" union all select DATE_FORMAT(punch_time_four, '%Y-%m-%d') as record_data from product_oa_punch_record");
- sbReplenish.append(" )n )");
- DataTableEntity dataTableEntityReplenish = baseDao.listTable(sbReplenish.toString(), param2.toArray());
- if(dataTableEntityReplenish.getRows()!=0){
- dataTableEntity1.getData().get(0).setValue("workDayOfMonth",Integer.parseInt(dataTableEntity1.getData().get(0).getString("workDayOfMonth"))+dataTableEntityReplenish.getRows());
- dataTableEntity1.getData().get(0).setValue("dayOfWork",Integer.parseInt(dataTableEntity1.getData().get(0).getString("dayOfWork"))-dataTableEntityReplenish.getRows());
- }
-
- return dataTableEntity1;
+ return dataTableEntityReport;
}
-
-
-
}
--
Gitblit v1.9.2