From e7fdc9967246ec64cc4f27d514c4ac1bdd488933 Mon Sep 17 00:00:00 2001
From: 杜洪波 <1074825718@qq.com>
Date: 星期一, 13 十月 2025 18:07:55 +0800
Subject: [PATCH] 代码提交
---
src/main/java/com/product/administration/controller/ConferenceManagerController.java | 2
src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java | 60 ++--
src/main/java/com/product/administration/service/ConferenceManagerService.java | 690 +++++++++++++++++++++++++++++++++-------------------
pom.xml | 2
4 files changed, 465 insertions(+), 289 deletions(-)
diff --git a/pom.xml b/pom.xml
index 586b5ae..e6a8ec2 100644
--- a/pom.xml
+++ b/pom.xml
@@ -6,7 +6,7 @@
<parent>
<groupId>com.lx</groupId>
<artifactId>product-server</artifactId>
- <version>2.2.0-release</version>
+ <version>2.0.0-release</version>
</parent>
<artifactId>product-server-administration</artifactId>
<name>product-server-administration</name>
diff --git a/src/main/java/com/product/administration/controller/ConferenceManagerController.java b/src/main/java/com/product/administration/controller/ConferenceManagerController.java
index 4391525..6e1ccb8 100644
--- a/src/main/java/com/product/administration/controller/ConferenceManagerController.java
+++ b/src/main/java/com/product/administration/controller/ConferenceManagerController.java
@@ -494,7 +494,7 @@
* @param request
* @return
*/
- @PostMapping("/get_sign_or_visit_info/{version}")
+ @PostMapping("/list-meeting-minute/{version}")
@ApiVersion(1)
public String listMeetingMinute(HttpServletRequest request) {
try {
diff --git a/src/main/java/com/product/administration/service/ConferenceManagerService.java b/src/main/java/com/product/administration/service/ConferenceManagerService.java
index 14a507e..2412139 100644
--- a/src/main/java/com/product/administration/service/ConferenceManagerService.java
+++ b/src/main/java/com/product/administration/service/ConferenceManagerService.java
@@ -27,7 +27,6 @@
import com.product.core.websocket.service.WebsocketMesssageServiceThread;
import com.product.module.sys.entity.SystemUser;
import com.product.util.BaseUtil;
-import com.product.util.UnifySQLFunction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@@ -37,6 +36,9 @@
import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
+import java.time.DayOfWeek;
+import java.time.LocalDate;
+import java.time.format.DateTimeFormatter;
import java.util.*;
/**
@@ -212,126 +214,265 @@
public boolean delConferenceApply(FieldSetEntity fse) throws BaseException {
return baseDao.delete(fse.getTableName(), fse.getUUID().split(","));
}
-
+
+ /**
+ * 浼氳鐪嬫澘鍛ㄤ俊鎭�
+ */
@Override
@Transactional
- public List getKbData(FieldSetEntity fse) throws BaseException {
+ public DataTableEntity getKanBanData(FieldSetEntity fse) {
+ String queryStartDate = fse.getString("query_start_date");
+ String queryEndDate = fse.getString("query_end_date");
+ String meeting_resource = fse.getString("meeting_resource");
+ StringBuilder sbSql = new StringBuilder();
+ sbSql.append("SELECT \n");
+ sbSql.append(" A.meeting_room AS meeting_room_uuid, \n");
+ sbSql.append(" start_time, \n");
+ sbSql.append(" end_time, \n");
+ sbSql.append(" DATE(start_time) as start_date, \n");
+ sbSql.append(" DATE(end_time) as end_date, \n");
+ sbSql.append(" TIME(start_time) as start_time_part, \n");
+ sbSql.append(" TIME(end_time) as end_time_part, \n");
+ sbSql.append(" HOUR(start_time) as start_hour, \n");
+ sbSql.append(" HOUR(end_time) as end_hour, \n");
+ sbSql.append(" DATEDIFF(end_time, start_time) AS diff_day, \n");
+ sbSql.append(" (SELECT room_name FROM product_oa_conference_room_config WHERE uuid = A.meeting_room) as meeting_room \n");
+ sbSql.append("FROM product_oa_conference_apply A \n");
+ sbSql.append("WHERE (end_time >= ? AND start_time <= ?)");
- List<Object>param=new ArrayList<>();
- String meeting_date= fse.getString("meeting_date");
- String meeting_date_end= fse.getString("meeting_date_end");
- String meeting_resource = fse.getString("meeting_resource");
- param.add(meeting_date);
- param.add(meeting_date_end);
- String replace_meeting_resource="";
+ DataTableEntity dtRecord = baseDao.listTable(sbSql.toString(), new Object[] {queryStartDate, queryEndDate + " 23:59:59"});
+ String replace_meeting_resource="";
if(!StringUtils.isEmpty(meeting_resource)){
String[] split = meeting_resource.split(",");
for (int i = 0; i < split.length; i++) {
+ replace_meeting_resource += "CONCAT(',', enable_seal, ',') LIKE '%,"+split[i]+",%'";
if(i!=split.length-1){
- replace_meeting_resource=replace_meeting_resource+"y.meeting_resource like '%"+split[i]+"%' and ";
- }else {
- replace_meeting_resource=replace_meeting_resource+"y.meeting_resource like '%"+split[i]+"%'";
- }
-
- }
-
- }
-
- StringBuilder bs=new StringBuilder();
- bs.append(" SELECT DISTINCT g.room_name,g.uuid as meet_room_uuid,").append(UnifySQLFunction.dateFormat("y.start_time", "dd-MM-yyyy HH:mm")).append(" start_time_format,").append(UnifySQLFunction.dateFormat("y.end_time", "dd-MM-yyyy HH:mm")).append(" end_time_format,y.* ");
- bs.append(" FROM product_oa_conference_room_config g");
- bs.append(" left JOIN product_oa_conference_apply y ");
- bs.append(" on y.meeting_room=g.uuid and y.start_time between ? and ? ");
- if(!StringUtils.isEmpty(meeting_resource)){
- bs.append(" where ( "+replace_meeting_resource+" )");
- }
- bs.append(" ORDER BY y.start_time");
- DataTableEntity dataTableEntity = baseDao.listTable(bs.toString(), param.toArray());
- List<String> list=new ArrayList();
- List<HashMap> listDate=new ArrayList<>();
-
- for(int i=0;i<dataTableEntity.getRows();i++){
- FieldSetEntity fieldSetEntity = dataTableEntity.getData().get(i);
-
- //鍒濆鍖栦細璁鐨勪娇鐢ㄧ殑鐘舵��
- String filter=" meeting_room = '"+fieldSetEntity.getString("meeting_room")+"' and start_time between '"+meeting_date+"' and '"+meeting_date_end+"'";
- DataTableEntity product_oa_conference_apply = baseDao.listTable("product_oa_conference_apply", filter, new Object[]{});
- for (int i1 = 0; i1 < product_oa_conference_apply.getRows(); i1++) {
- FieldSetEntity fieldSetEntity1 = product_oa_conference_apply.getFieldSetEntity(i1);
- Date date = new Date();
- if(date.before(fieldSetEntity1.getDate("start_time"))){
- fieldSetEntity1.setValue("status","鏈紑濮�");
- }else if(date.after(fieldSetEntity1.getDate("end_time"))){
- fieldSetEntity1.setValue("status","宸茬粨鏉�");
- }else {
- fieldSetEntity1.setValue("status","浼氳涓�");
- }
- baseDao.executeUpdate("update product_oa_conference_apply set status='"+fieldSetEntity1.getString("status")+"' where uuid='"+fieldSetEntity1.getString("uuid")+"'");
- }
-
- if(ObjectUtil.isNotEmpty(fieldSetEntity.getString("uuid"))){
- //鏋勯�犳墍闇�瑕佺殑鏍煎紡
- if(!list.contains(fieldSetEntity.getValue("room_name").toString())){
- HashMap<String,Object> hashMap=new HashMap<>();
- hashMap.put("room_name",fieldSetEntity.getValue("room_name").toString());
- hashMap.put("uuid",fieldSetEntity.getValue("meet_room_uuid").toString());
- Object getStartTime = null;
- for (int j = 0; j < dataTableEntity.getRows(); j++) {
- FieldSetEntity fieldSetEntity1 = dataTableEntity.getFieldSetEntity(j);
- if(fieldSetEntity1.getString("room_name").equals(hashMap.get("room_name"))){
- getStartTime = fieldSetEntity1.getValue("start_time");
- SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH:mm");
- String time = dateFormat.format(getStartTime);
- hashMap.put("start_time",time);
- break;
- }
-
- }
- list.add(fieldSetEntity.getValue("room_name").toString());
- listDate.add(hashMap);
-
- }
- fieldSetEntity.setValue("parent",fieldSetEntity.getValue("meet_room_uuid").toString());
- HashMap<String,Object> hashMap=new HashMap<>();
- for (int j = 0; j < fieldSetEntity.getValues().keySet().size(); j++) {
- if(fieldSetEntity.getValues().keySet().toArray()[j].toString().equals("start_time") || fieldSetEntity.getValues().keySet().toArray()[j].toString().equals("end_time")){
- Object meeting_time = fieldSetEntity.getValue(fieldSetEntity.getValues().keySet().toArray()[j].toString());
- SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- String time = dateFormat.format(meeting_time);
- hashMap.put(fieldSetEntity.getValues().keySet().toArray()[j].toString(),time);
- if(fieldSetEntity.getValues().keySet().toArray()[j].equals("start_time")){
- hashMap.put("start_date",time);
- }else {
- hashMap.put("end_date",time);
- }
- }else {
- hashMap.put(fieldSetEntity.getValues().keySet().toArray()[j].toString(),fieldSetEntity.getValue(fieldSetEntity.getValues().keySet().toArray()[j].toString()));
- }
-
- }
- listDate.add(hashMap);
-
- }else {
- if(!list.contains(fieldSetEntity.getValue("room_name").toString())){
- HashMap<String,Object> hashMap=new HashMap<>();
- hashMap.put("room_name",fieldSetEntity.getValue("room_name").toString());
- hashMap.put("uuid",fieldSetEntity.getValue("meet_room_uuid").toString());
- hashMap.put("start_time","");
- list.add(fieldSetEntity.getValue("room_name").toString());
- listDate.add(hashMap);
+ replace_meeting_resource += " and ";
}
}
-
}
- return listDate;
+ DataTableEntity dtRoom = baseDao.listTable(CmnConst.PRODUCT_OA_CONFERENCE_ROOM_CONFIG, replace_meeting_resource);
+
+ // 鍒濆鍖栦細璁璁板綍
+ Map<String, FieldSetEntity> roomRecord = new HashMap<>();
+ for (int i = 0; i < dtRoom.getRows(); i++) {
+ FieldSetEntity fseRoom = dtRoom.getFieldSetEntity(i);
+ FieldSetEntity fseRoomMeeting = new FieldSetEntity(CmnConst.PRODUCT_OA_CONFERENCE_APPLY);
+ fseRoomMeeting.setValue("room_name", fseRoom.getString("room_name"));
+ fseRoomMeeting.setValue("room_uuid", fseRoom.getString("uuid"));
+ roomRecord.put(fseRoom.getString("room_name"), fseRoomMeeting);
+ }
+
+ DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
+ LocalDate queryStart = LocalDate.parse(queryStartDate, formatter);
+ LocalDate queryEnd = LocalDate.parse(queryEndDate, formatter);
+
+ // 澶勭悊浼氳璁板綍
+ for (int i = 0; i < dtRecord.getRows(); i++) {
+ FieldSetEntity record = dtRecord.getFieldSetEntity(i);
+ String meetingRoom = record.getString("meeting_room");
+ if (!roomRecord.containsKey(meetingRoom)) continue;
+
+ FieldSetEntity roomMeeting = roomRecord.get(meetingRoom);
+
+ String startDateStr = record.getString("start_date");
+ String endDateStr = record.getString("end_date");
+ String startTime = record.getString("start_time_part");
+ String endTime = record.getString("end_time_part");
+ int startHour = record.getInteger("start_hour");
+ int endHour = record.getInteger("end_hour");
+ int diffDay = record.getInteger("diff_day");
+
+ LocalDate startDate = LocalDate.parse(startDateStr, formatter);
+ LocalDate endDate = LocalDate.parse(endDateStr, formatter);
+
+ if (diffDay > 0) {
+ // 璺ㄥぉ浼氳 - 澶勭悊姣忎竴澶�
+ LocalDate currentDate = startDate.isBefore(queryStart) ? queryStart : startDate;
+ LocalDate lastDate = endDate.isAfter(queryEnd) ? queryEnd : endDate;
+
+ while (!currentDate.isAfter(lastDate)) {
+ DayOfWeek dayOfWeek = currentDate.getDayOfWeek();
+ boolean isFirstDay = currentDate.equals(startDate);
+ boolean isLastDay = currentDate.equals(endDate);
+
+ if (isFirstDay) {
+ // 寮�濮嬫棩
+ if (startHour < 12) {
+ // 涓婂崍寮�濮嬶紝鎷嗗垎鎴愪笂鍗堟鍜屼笅鍗堝叏澶�
+ addTimeSlot(roomMeeting, dayOfWeek + "_AM", startTime + "-12:00:00");
+ addTimeSlot(roomMeeting, dayOfWeek + "_PM", "12:00:00-00:00:00");
+ } else {
+ // 涓嬪崍寮�濮嬶紝鍙湁涓嬪崍娈�
+ addTimeSlot(roomMeeting, dayOfWeek + "_PM", startTime + "-00:00:00");
+ }
+ } else if (isLastDay) {
+ // 缁撴潫鏃�
+ // 涓婂崍鍏ㄥぉ
+ addTimeSlot(roomMeeting, dayOfWeek + "_AM", "00:00:00-12:00:00");
+ if (endHour >= 12) {
+ // 缁撴潫鍦ㄤ笅鍗堬紝鎷嗗垎鎴愪笂鍗堝叏澶╁拰涓嬪崍娈�
+ addTimeSlot(roomMeeting, dayOfWeek + "_PM", "12:00:00-" + endTime);
+ }
+ } else {
+ // 涓棿鏃� - 鍏ㄥぉ
+ addTimeSlot(roomMeeting, dayOfWeek + "_AM", "00:00:00-12:00:00");
+ addTimeSlot(roomMeeting, dayOfWeek + "_PM", "12:00:00-00:00:00");
+ }
+
+ currentDate = currentDate.plusDays(1);
+ }
+ } else {
+ // 鍗曞ぉ浼氳
+ LocalDate date = LocalDate.parse(startDateStr, formatter);
+ DayOfWeek dayOfWeek = date.getDayOfWeek();
+
+ if (startHour < 12 && endHour > 12) {
+ // 璺ㄤ笂涓嬪崍鐨勪細璁紝鎷嗗垎鎴愪笂鍗堟鍜屼笅鍗堟
+ addTimeSlot(roomMeeting, dayOfWeek + "_AM", startTime + "-12:00:00");
+ addTimeSlot(roomMeeting, dayOfWeek + "_PM", "12:00:00-" + endTime);
+ } else if (startHour < 12) {
+ // 绾笂鍗堜細璁�
+ addTimeSlot(roomMeeting, dayOfWeek + "_AM", startTime + "-" + endTime);
+ } else {
+ // 绾笅鍗堜細璁�
+ addTimeSlot(roomMeeting, dayOfWeek + "_PM", startTime + "-" + endTime);
+ }
+ }
+ }
+
+ DataTableEntity dtKanBan = new DataTableEntity();
+ for (FieldSetEntity fseKanban : roomRecord.values()) {
+ dtKanBan.addFieldSetEntity(fseKanban);
+ }
+ return dtKanBan;
}
+
+ private void addTimeSlot(FieldSetEntity roomMeeting, String field, String timeSlot) {
+ String existing = roomMeeting.getString(field);
+ if (StringUtils.isEmpty(existing)) {
+ roomMeeting.setValue(field, timeSlot);
+ } else {
+ roomMeeting.setValue(field, existing + "," + timeSlot);
+ }
+ }
+
+// @Override
+// @Transactional
+// public List getkbData(FieldSetEntity fse) throws BaseException {
+// List<Object>param=new ArrayList<>();
+// String meeting_date= fse.getString("meeting_date");
+// String meeting_date_end= fse.getString("meeting_date_end");
+// String meeting_resource = fse.getString("meeting_resource");
+// param.add(meeting_date);
+// param.add(meeting_date_end);
+// String replace_meeting_resource="";
+// if(!StringUtils.isEmpty(meeting_resource)){
+//
+// String[] split = meeting_resource.split(",");
+// for (int i = 0; i < split.length; i++) {
+// if(i!=split.length-1){
+// replace_meeting_resource=replace_meeting_resource+"y.meeting_resource like '%"+split[i]+"%' and ";
+// }else {
+// replace_meeting_resource=replace_meeting_resource+"y.meeting_resource like '%"+split[i]+"%'";
+// }
+// }
+// }
+// StringBuilder bs=new StringBuilder();
+// bs.append(" SELECT DISTINCT g.room_name,g.uuid as meet_room_uuid,DATE_FORMAT(y.start_time, \"%d-%m-%Y %H:%i\") start_time_format,DATE_FORMAT(y.end_time, \"%d-%m-%Y %H:%i\") end_time_format,y.* ");
+// bs.append(" FROM product_oa_conference_room_config g");
+// bs.append(" left JOIN product_oa_conference_apply y ");
+// bs.append(" on y.meeting_room=g.uuid and y.start_time between ? and ? ");
+// if(!StringUtils.isEmpty(meeting_resource)){
+// bs.append(" where ( "+replace_meeting_resource+" )");
+// }
+// bs.append(" ORDER BY y.start_time");
+// DataTableEntity dataTableEntity = baseDao.listTable(bs.toString(), param.toArray());
+// List<String> list=new ArrayList();
+// List<HashMap> listDate=new ArrayList<>();
+//
+// for(int i=0;i<dataTableEntity.getRows();i++){
+// FieldSetEntity fieldSetEntity = dataTableEntity.getData().get(i);
+//
+// //鍒濆鍖栦細璁鐨勪娇鐢ㄧ殑鐘舵��
+// String filter=" meeting_room = '"+fieldSetEntity.getString("meeting_room")+"' and start_time between '"+meeting_date+"' and '"+meeting_date_end+"'";
+// DataTableEntity product_oa_conference_apply = baseDao.listTable("product_oa_conference_apply", filter, new Object[]{});
+// for (int i1 = 0; i1 < product_oa_conference_apply.getRows(); i1++) {
+// FieldSetEntity fieldSetEntity1 = product_oa_conference_apply.getFieldSetEntity(i1);
+// Date date = new Date();
+// if(date.before(fieldSetEntity1.getDate("start_time"))){
+// fieldSetEntity1.setValue("status","鏈紑濮�");
+// }else if(date.after(fieldSetEntity1.getDate("end_time"))){
+// fieldSetEntity1.setValue("status","宸茬粨鏉�");
+// }else {
+// fieldSetEntity1.setValue("status","浼氳涓�");
+// }
+// baseDao.executeUpdate("update product_oa_conference_apply set status='"+fieldSetEntity1.getString("status")+"' where uuid='"+fieldSetEntity1.getString("uuid")+"'");
+// }
+//
+// if(ObjectUtil.isNotEmpty(fieldSetEntity.getString("uuid"))){
+// //鏋勯�犳墍闇�瑕佺殑鏍煎紡
+// if(!list.contains(fieldSetEntity.getValue("room_name").toString())){
+// HashMap<String,Object> hashMap=new HashMap<>();
+// hashMap.put("room_name",fieldSetEntity.getValue("room_name").toString());
+// hashMap.put("uuid",fieldSetEntity.getValue("meet_room_uuid").toString());
+// Object getStartTime = null;
+// for (int j = 0; j < dataTableEntity.getRows(); j++) {
+// FieldSetEntity fieldSetEntity1 = dataTableEntity.getFieldSetEntity(j);
+// if(fieldSetEntity1.getString("room_name").equals(hashMap.get("room_name"))){
+// getStartTime = fieldSetEntity1.getValue("start_time");
+// SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH:mm");
+// String time = dateFormat.format(getStartTime);
+// hashMap.put("start_time",time);
+// break;
+// }
+//
+// }
+// list.add(fieldSetEntity.getValue("room_name").toString());
+// listDate.add(hashMap);
+//
+// }
+// fieldSetEntity.setValue("parent",fieldSetEntity.getValue("meet_room_uuid").toString());
+// HashMap<String,Object> hashMap=new HashMap<>();
+// for (int j = 0; j < fieldSetEntity.getValues().keySet().size(); j++) {
+// if(fieldSetEntity.getValues().keySet().toArray()[j].toString().equals("start_time") || fieldSetEntity.getValues().keySet().toArray()[j].toString().equals("end_time")){
+// Object meeting_time = fieldSetEntity.getValue(fieldSetEntity.getValues().keySet().toArray()[j].toString());
+// SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+// String time = dateFormat.format(meeting_time);
+// hashMap.put(fieldSetEntity.getValues().keySet().toArray()[j].toString(),time);
+// if(fieldSetEntity.getValues().keySet().toArray()[j].equals("start_time")){
+// hashMap.put("start_date",time);
+// }else {
+// hashMap.put("end_date",time);
+// }
+// }else {
+// hashMap.put(fieldSetEntity.getValues().keySet().toArray()[j].toString(),fieldSetEntity.getValue(fieldSetEntity.getValues().keySet().toArray()[j].toString()));
+// }
+//
+// }
+// listDate.add(hashMap);
+//
+// }else {
+// if(!list.contains(fieldSetEntity.getValue("room_name").toString())){
+// HashMap<String,Object> hashMap=new HashMap<>();
+// hashMap.put("room_name",fieldSetEntity.getValue("room_name").toString());
+// hashMap.put("uuid",fieldSetEntity.getValue("meet_room_uuid").toString());
+// hashMap.put("start_time","");
+// list.add(fieldSetEntity.getValue("room_name").toString());
+// listDate.add(hashMap);
+// }
+// }
+//
+// }
+// return listDate;
+// }
@Override
public List getMeetingDetails(FieldSetEntity fse) throws BaseException {
String meeting_room = fse.getString("meeting_room");
- String meeting_date = fse.getString("meeting_date");
- String meeting_date_end = fse.getString("meeting_date_end");
+ String meeting_start_date = fse.getString("meeting_start_date");
+ String meeting_end_date = fse.getString("meeting_end_date");
String meeting_resource = fse.getString("meeting_resource");
String replace_meeting_resource="";
if(!StringUtils.isEmpty(meeting_resource)){
@@ -343,44 +484,42 @@
}else {
replace_meeting_resource=replace_meeting_resource+"T1.meeting_resource like '%"+split[i]+"%'";
}
-
}
-
}
-
- if(!StringUtils.isEmpty(meeting_room)&&!StringUtils.isEmpty(meeting_date)&&!StringUtils.isEmpty(meeting_date_end)){
+ if(!StringUtils.isEmpty(meeting_room)&&!StringUtils.isEmpty(meeting_start_date)&&!StringUtils.isEmpty(meeting_end_date)){
List<Object>param=new ArrayList<>();
param.add(meeting_room);
- param.add(meeting_date);
- param.add(meeting_date_end);
+ param.add(meeting_start_date);
+ param.add(meeting_end_date);
StringBuilder sb=new StringBuilder();
- sb.append(" SELECT b.uuid,").append(UnifySQLFunction.dateFormat("b.start_time", "yyyy-MM-dd")).append(" start_time,b.start_time start_time1,b.end_time,b.meeting_topic,b.status,b.record_man as record_man_uuid, ");
- sb.append(" (select user_name from product_sys_users where user_id=b.record_man) as record_man,");
- sb.append(" (select user_name from product_sys_users where user_id=b.record_master) as record_master,");
- sb.append(" CONCAT(").append(UnifySQLFunction.dateFormat("b.start_time", "HH:mm")).append(",'~',").append(UnifySQLFunction.dateFormat("b.end_time", "HH:mm")).append(") as time_quantum,");
- sb.append(" ").append(UnifySQLFunction.dateFormat("b.start_time", "yyyy-MM-dd")).append(" as meeting_date,");
- sb.append(" ").append(UnifySQLFunction.groupConcat("b.dict_label")).append(" AS meeting_resouces ");
- sb.append(" FROM (");
- sb.append(" SELECT DISTINCT a.uuid,a.start_time,a.end_time,a.meeting_topic,a.record_man,a.record_master,a.status,t.dict_label ");
- sb.append(" FROM (");
- sb.append(" SELECT T1.*, ");
- sb.append(" SUBSTRING_INDEX( SUBSTRING_INDEX( T1.meeting_resource, ',', T2.digit + 1 ), ',',- 1 ) AS type ");
- sb.append(" FROM product_oa_conference_apply T1 ");
- sb.append(" LEFT JOIN ( SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ) T2");
- sb.append(" ON T2.digit < ( length( T1.meeting_resource )- length( REPLACE ( T1.meeting_resource, ',', '' ))+ 1 )");
- sb.append(" WHERE T1.meeting_room = ? ");
- sb.append(" and T1.start_time between ? and ? ");
+ sb.append("\nSELECT b.uuid,DATE_FORMAT(b.start_time, \"%Y-%m-%d\") start_time,b.start_time start_time1,b.end_time,b.meeting_topic,b.status,b.meeting_recorder as meeting_recorder_uuid, ");
+ sb.append("\n (select user_name from product_sys_users where user_id=b.meeting_recorder) as meeting_recorder,");
+ sb.append("\n (select user_name from product_sys_users where user_id=b.meeting_master) as meeting_master,");
+ sb.append("\n CONCAT(DATE_FORMAT(b.start_time,\"%H:%i\"),'~',DATE_FORMAT(b.end_time,\"%H:%i\")) as time_quantum,");
+ sb.append("\n DATE_FORMAT(b.start_time,\"%Y-%m-%d\") as meeting_date,");
+ sb.append("\n GROUP_CONCAT(b.dict_label SEPARATOR ',') AS meeting_resouces ");
+ sb.append("\nFROM (");
+ sb.append("\n SELECT DISTINCT a.uuid,a.start_time,a.end_time,a.meeting_topic,a.meeting_recorder,a.meeting_master,a.status,t.dict_label ");
+ sb.append("\n FROM (");
+ sb.append("\n SELECT T1.*, ");
+ sb.append("\n SUBSTRING_INDEX( SUBSTRING_INDEX( T1.meeting_resource, ',', T2.digit + 1 ), ',',- 1 ) AS type ");
+ sb.append("\n FROM product_oa_conference_apply T1 ");
+ sb.append("\n LEFT JOIN (");
+ sb.append("\n SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 ");
+ sb.append("\n ) T2");
+ sb.append("\n ON T2.digit < ( length( T1.meeting_resource )- length( REPLACE ( T1.meeting_resource, ',', '' ))+ 1 )");
+ sb.append("\n WHERE T1.meeting_room = ? ");
+ sb.append("\n and T1.start_time between ? and ? ");
if(!StringUtils.isEmpty(meeting_resource)){
sb.append(" and ("+replace_meeting_resource+" )");
}
- sb.append(" ) a");
- sb.append(" LEFT JOIN product_sys_dict t ON a.type = t.dict_value ");
- sb.append(" AND t.dict_name = '浼氳瀹よ祫婧�'");
- sb.append(" ) b ");
- sb.append(" group by b.uuid,b.start_time,b.end_time,b.meeting_topic,b.record_man,b.record_master,b.status ");
+ sb.append("\n ) a");
+ sb.append("\n LEFT JOIN product_sys_dict t ON a.type = t.dict_value AND t.dict_name = '浼氳瀹よ祫婧�'");
+ sb.append("\n)b ");
+ sb.append("\ngroup by b.uuid,b.start_time,b.end_time,b.meeting_topic,b.meeting_recorder,b.meeting_master,b.status ");
DataTableEntity dataTableEntity = baseDao.listTable(sb.toString(), param.toArray());
- List dataList =new ArrayList();
+ List dataList =new ArrayList();
for (int i = 0; i < dataTableEntity.getRows(); i++) {
FieldSetEntity fieldSetEntity = dataTableEntity.getData().get(i);
Map<Object, Object> MapValues = fieldSetEntity.getValues();
@@ -511,8 +650,6 @@
@Override
public String ConferenceSign(FieldSetEntity fse) throws BaseException, ParseException {
-
-
JSONObject jsonObject = new JSONObject();
if(!StringUtils.isEmpty(fse.getString("uuid"))&&!StringUtils.isEmpty(fse.getString("date"))){
String uuid = fse.getString("uuid");
@@ -551,115 +688,152 @@
return jsonObject.toString();
}
- @Override
- public HashMap getSignOrVisitInfo(FieldSetEntity fse) throws BaseException {
-
- String meeting_uuid = fse.getString("meeting_uuid");
- //type=1灏辨槸绛惧埌鏁版嵁锛宼ype=2灏辨槸璁块棶鏁版嵁
- String type = fse.getString("type");
- List<Object> params=new ArrayList<>();
- params.add(meeting_uuid);
- params.add(meeting_uuid);
-
- StringBuilder sb=new StringBuilder();
- sb.append(" SELECT c.* FROM ( ");
- sb.append(" SELECT b.staff_id,b.uuid as meeting_uuid,f.show_name,l.uuid as dept_uuid,l.org_level_name,");
+ /**
+ * 鑾峰彇浼氳璁块棶/绛惧埌淇℃伅
+ */
+ public DataTableEntity getMeetingSignOrVisit(FieldSetEntity fse) throws BaseException {
+ String meeting_uuid = fse.getString("meeting_uuid");
+ String type = fse.getString("type");
+ StringBuilder sb = new StringBuilder();
+ sb.append("\nSELECT c.* FROM ( ");
+ sb.append("\n SELECT b.staff_id,b.uuid as meeting_uuid,f.show_name,l.uuid as dept_uuid,l.org_level_name,");
if(type.equals("1")){
- sb.append(" n.sign_status");
+ sb.append(" n.sign_status");
}else {
sb.append(" n.visit_status");
}
- sb.append(" FROM (" );
- sb.append(" SELECT y.*, ");
- sb.append(" SUBSTRING_INDEX( SUBSTRING_INDEX( participator, ',', n ), ',', - 1 ) AS staff_id");
- sb.append(" FROM product_oa_conference_apply y, ");
- sb.append(" ( SELECT @rownum := @rownum + 1 AS n FROM ( SELECT @rownum := 0 ) r, product_oa_conference_apply ) x ");
- sb.append(" WHERE 1 = 1 ");
- sb.append(" AND n <= ( LENGTH( participator ) - LENGTH( REPLACE ( participator, ',', '' ) ) + 1 )");
- sb.append(" and y.uuid= ? ");
- sb.append(" )b ");
- sb.append(" LEFT JOIN product_sys_staffs f on b.staff_id=f.user_id ");
- sb.append(" LEFT JOIN product_sys_org_levels l on f.dept_uuid=l.uuid ");
+ sb.append("\n FROM (" );
+ sb.append("\n SELECT y.*, ");
+ sb.append("\n SUBSTRING_INDEX( SUBSTRING_INDEX( participator, ',', n ), ',', - 1 ) AS staff_id");
+ sb.append("\n FROM product_oa_conference_apply y,(SELECT @rownum := @rownum + 1 AS n FROM ( SELECT @rownum := 0 ) r, product_oa_conference_apply ) x ");
+ sb.append("\n WHERE 1 = 1 AND n <= ( LENGTH( participator ) - LENGTH( REPLACE ( participator, ',', '' ) ) + 1 )");
+ sb.append("\n and y.uuid= ? ");
+ sb.append("\n )b ");
+ sb.append("\n LEFT JOIN product_sys_staffs f on b.staff_id=f.user_id ");
+ sb.append("\n LEFT JOIN product_sys_org_levels l on f.dept_uuid=l.uuid ");
if(type.equals("1")){
- sb.append(" LEFT JOIN product_oa_conference_sign n ");
+ sb.append("\n LEFT JOIN product_oa_conference_sign n ");
}else {
- sb.append(" LEFT JOIN product_oa_conference_visit n ");
+ sb.append("\n LEFT JOIN product_oa_conference_visit n ");
}
- sb.append(" on b.staff_id=n.user_id and n.meeting_uuid=? ");
- sb.append(" )c");
-
- DataTableEntity dataTableEntity = baseDao.listTable(sb.toString(),params.toArray());
- List<HashMap> listDate=new ArrayList<>();
- List<HashMap> listDateSign=new ArrayList<>();
- List<HashMap> listDateNoSign=new ArrayList<>();
-
- List<String> list=new ArrayList();
-
- int sign_count=0;
- int sign_all=0;
- int sign_down=0;
- HashMap<String,Object> hashMapResult=new HashMap<>();
+ sb.append("\n ON b.staff_id=n.user_id and n.meeting_uuid=? ");
+ sb.append("\n)c");
+ // 鑾峰彇浼氳璁块棶/绛惧埌淇℃伅
+ DataTableEntity dataTableEntity = baseDao.listTable(sb.toString(), new Object[] {meeting_uuid, meeting_uuid});
+ // 閬嶅巻浼氳璁块棶/绛惧埌淇℃伅
for (int i = 0; i < dataTableEntity.getRows(); i++) {
- FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
-
- if(!list.contains(fieldSetEntity.getString("org_level_name"))){
- HashMap<String,Object> hashMap=new HashMap<>();
- hashMap.put("dept_uuid",fieldSetEntity.getString("dept_uuid"));
- hashMap.put("org_level_name",fieldSetEntity.getString("org_level_name"));
- list.add(fieldSetEntity.getString("org_level_name"));
- listDate.add(hashMap);
- }
-
- fieldSetEntity.setValue("parent",fieldSetEntity.getString("dept_uuid"));
- HashMap<String,Object> hashMap=new HashMap<>();
- for (int j = 0; j < fieldSetEntity.getValues().keySet().size(); j++) {
- hashMap.put(fieldSetEntity.getValues().keySet().toArray()[j].toString(),fieldSetEntity.getValue(fieldSetEntity.getValues().keySet().toArray()[j].toString()));
-
- }
- listDate.add(hashMap);
-
+ FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
}
- sign_all=listDate.size();
- List<String> list1=new ArrayList();
- List<String> list2=new ArrayList();
- for (int j = 0; j < listDate.size(); j++) {
- if((ObjectUtil.isNotEmpty(listDate.get(j).get("sign_status"))&&listDate.get(j).get("sign_status").equals("宸茬鍒�")) || (ObjectUtil.isNotEmpty(listDate.get(j).get("visit_status"))&&listDate.get(j).get("visit_status").equals("宸茶闂�")) ){
- if(!list1.contains(listDate.get(j).get("org_level_name"))){
- list1.add(listDate.get(j).get("org_level_name").toString());
- HashMap<String,Object> hashMap=new HashMap<>();
- hashMap.put("dept_uuid",listDate.get(j).get("dept_uuid"));
- hashMap.put("org_level_name",listDate.get(j).get("org_level_name"));
- listDateSign.add(hashMap);
- }
- sign_count++;
- //閬垮厤閲嶅娣诲姞閮ㄩ棬
- if(listDate.get(j).size()!=2){
- listDateSign.add(listDate.get(j));
- }
-
- }else {
- if(!list2.contains(listDate.get(j).get("org_level_name"))){
- list2.add(listDate.get(j).get("org_level_name").toString());
- HashMap<String,Object> hashMap=new HashMap<>();
- hashMap.put("dept_uuid",listDate.get(j).get("dept_uuid"));
- hashMap.put("org_level_name",listDate.get(j).get("org_level_name"));
- listDateNoSign.add(hashMap);
- }
- //閬垮厤閲嶅娣诲姞閮ㄩ棬
- if(listDate.get(j).size()!=2){
- listDateNoSign.add(listDate.get(j));
- }
-
-
- }
- }
- sign_down=sign_all-sign_count;
- hashMapResult.put("signOrvisit_down",sign_count);
- hashMapResult.put("no_signOrvisit",sign_down);
- hashMapResult.put("listDateNoSignOrVisit",listDateNoSign);
- hashMapResult.put("listDateSignOrVisit",listDateSign);
- return hashMapResult;
+ return dataTableEntity;
}
+
+// @Override
+// public HashMap getSignOrVisitInfo(FieldSetEntity fse) throws BaseException {
+//
+// String meeting_uuid = fse.getString("meeting_uuid");
+// //type=1灏辨槸绛惧埌鏁版嵁锛宼ype=2灏辨槸璁块棶鏁版嵁
+// String type = fse.getString("type");
+// List<Object> params=new ArrayList<>();
+// params.add(meeting_uuid);
+// params.add(meeting_uuid);
+//
+// StringBuilder sb = new StringBuilder();
+// sb.append("\nSELECT c.* FROM ( ");
+// sb.append("\n SELECT b.staff_id,b.uuid as meeting_uuid,f.show_name,l.uuid as dept_uuid,l.org_level_name,");
+// if(type.equals("1")){
+// sb.append(" n.sign_status");
+// }else {
+// sb.append(" n.visit_status");
+// }
+// sb.append("\n FROM (" );
+// sb.append("\n SELECT y.*, ");
+// sb.append("\n SUBSTRING_INDEX( SUBSTRING_INDEX( participator, ',', n ), ',', - 1 ) AS staff_id");
+// sb.append("\n FROM product_oa_conference_apply y,(SELECT @rownum := @rownum + 1 AS n FROM ( SELECT @rownum := 0 ) r, product_oa_conference_apply ) x ");
+// sb.append("\n WHERE 1 = 1 AND n <= ( LENGTH( participator ) - LENGTH( REPLACE ( participator, ',', '' ) ) + 1 )");
+// sb.append("\n and y.uuid= ? ");
+// sb.append("\n )b ");
+// sb.append("\n LEFT JOIN product_sys_staffs f on b.staff_id=f.user_id ");
+// sb.append("\n LEFT JOIN product_sys_org_levels l on f.dept_uuid=l.uuid ");
+// if(type.equals("1")){
+// sb.append("\n LEFT JOIN product_oa_conference_sign n ");
+// }else {
+// sb.append("\n LEFT JOIN product_oa_conference_visit n ");
+// }
+// sb.append("\n ON b.staff_id=n.user_id and n.meeting_uuid=? ");
+// sb.append("\n)c");
+// // 鑾峰彇浼氳璁块棶/绛惧埌淇℃伅
+// DataTableEntity dataTableEntity = baseDao.listTable(sb.toString(), params.toArray());
+//
+// // 閮ㄩ棬淇℃伅瀛樺偍
+// List<HashMap<String, Object>> listDate = new ArrayList<>();
+// List<HashMap> listDateSign = new ArrayList<>();
+// List<HashMap> listDateNoSign = new ArrayList<>();
+// // 鐩稿叧浜哄憳鎵�灞為儴闂�
+// List<String> listDeptName = new ArrayList<>();
+//
+// HashMap<String,Object> hashMapResult=new HashMap<>();
+//
+// // 閬嶅巻浼氳璁块棶/绛惧埌淇℃伅
+// for (int i = 0; i < dataTableEntity.getRows(); i++) {
+// FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
+// // 鏂伴儴闂ㄤ汉鍛�
+// if(!listDeptName.contains(fieldSetEntity.getString(CmnConst.ORG_LEVEL_NAME))){
+// HashMap<String,Object> hashMap = new HashMap<>();
+// hashMap.put(CmnConst.DEPT_UUID, fieldSetEntity.getString(CmnConst.DEPT_UUID));
+// hashMap.put(CmnConst.ORG_LEVEL_NAME, fieldSetEntity.getString(CmnConst.ORG_LEVEL_NAME));
+// listDeptName.add(fieldSetEntity.getString(CmnConst.ORG_LEVEL_NAME));
+// listDate.add(hashMap);
+// }
+// fieldSetEntity.setValue("parent", fieldSetEntity.getString("dept_uuid"));
+// HashMap<String,Object> hashMap=new HashMap<>();
+// for (int j = 0; j < fieldSetEntity.getValues().keySet().size(); j++) {
+// hashMap.put(fieldSetEntity.getValues().keySet().toArray()[j].toString(),fieldSetEntity.getValue(fieldSetEntity.getValues().keySet().toArray()[j].toString()));
+// }
+// listDate.add(hashMap);
+//
+// }
+// int sign_all = listDate.size();
+// int sign_count=0;
+// List<String> list1=new ArrayList<>();
+// List<String> list2=new ArrayList<>();
+// for (int j = 0; j < listDate.size(); j++) {
+// HashMap<String, Object> mapdata = listDate.get(j);
+// if((ObjectUtil.isNotEmpty(mapdata.get("sign_status")) && mapdata.get("sign_status").equals("宸茬鍒�"))
+// || (ObjectUtil.isNotEmpty(mapdata.get("visit_status")) && mapdata.get("visit_status").equals("宸茶闂�")) ){
+// if(!list1.contains(mapdata.get("org_level_name"))){
+// list1.add(mapdata.get("org_level_name").toString());
+// HashMap<String,Object> hashMap=new HashMap<>();
+// hashMap.put("dept_uuid", mapdata.get("dept_uuid"));
+// hashMap.put("org_level_name", mapdata.get("org_level_name"));
+// listDateSign.add(hashMap);
+// }
+// sign_count++;
+// //閬垮厤閲嶅娣诲姞閮ㄩ棬
+// if(mapdata.size()!=2){
+// listDateSign.add(mapdata);
+// }
+//
+// }else {
+// if(!list2.contains(mapdata.get("org_level_name"))){
+// list2.add(mapdata.get("org_level_name").toString());
+// HashMap<String,Object> hashMap=new HashMap<>();
+// hashMap.put("dept_uuid", mapdata.get("dept_uuid"));
+// hashMap.put("org_level_name", mapdata.get("org_level_name"));
+// listDateNoSign.add(hashMap);
+// }
+// //閬垮厤閲嶅娣诲姞閮ㄩ棬
+// if(mapdata.size()!=2){
+// listDateNoSign.add(mapdata);
+// }
+// }
+// }
+// int sign_down=sign_all-sign_count;
+// hashMapResult.put("signOrvisit_down",sign_count);
+// hashMapResult.put("no_signOrvisit",sign_down);
+// hashMapResult.put("listDateNoSignOrVisit",listDateNoSign);
+// hashMapResult.put("listDateSignOrVisit",listDateSign);
+// return hashMapResult;
+// }
@Override
public void sendMsg(FieldSetEntity fse) throws BaseException {
@@ -984,7 +1158,6 @@
fieldSetEntity.setValue("meetint_type", fieldSetEntity.getString("meetint_type_save_value"));
}
-
//瀛樺偍璁块棶璁板綍
SystemUser currentUser = SpringMVCContextHolder.getCurrentUser();
String fliter="meeting_uuid='"+fse.getUUID()+"' and user_id='"+currentUser.getUser_id()+"'";
@@ -998,23 +1171,21 @@
fieldSetEntity1.setValue("visit_date_new",new Date());
baseDao.update(fieldSetEntity1);
}else {
- FieldSetEntity fieldSetEntityVisit = new FieldSetEntity();
- fieldSetEntityVisit.setTableName("product_oa_conference_visit");
- fieldSetEntityVisit.setValue("visit_user",currentUser.getUser_name());
- fieldSetEntityVisit.setValue("user_id",currentUser.getUser_id());
- fieldSetEntityVisit.setValue("org_level_uuid","00000000-0000-0000-0000-000000000000");
- fieldSetEntityVisit.setValue("organization",currentUser.getDept_uuid());
- fieldSetEntityVisit.setValue("organization_name",currentUser.getCurrentDept().getString("org_level_name"));
- fieldSetEntityVisit.setValue("visit_date_first",new Date());
- fieldSetEntityVisit.setValue("visit_date_new",new Date());
- fieldSetEntityVisit.setValue("visit_count",1);
- fieldSetEntityVisit.setValue("visit_status","宸茶闂�");
- fieldSetEntityVisit.setValue("meeting_uuid",fse.getUUID());
- BaseUtil.createCreatorAndCreationTime(SpringMVCContextHolder.getCurrentUser(), fieldSetEntityVisit);
- baseDao.add(fieldSetEntityVisit);
+ FieldSetEntity fieldSetEntityVisit = new FieldSetEntity();
+ fieldSetEntityVisit.setTableName("product_oa_conference_visit");
+ fieldSetEntityVisit.setValue("visit_user",currentUser.getUser_name());
+ fieldSetEntityVisit.setValue("user_id",currentUser.getUser_id());
+ fieldSetEntityVisit.setValue("org_level_uuid","00000000-0000-0000-0000-000000000000");
+ fieldSetEntityVisit.setValue("organization",currentUser.getDept_uuid());
+ fieldSetEntityVisit.setValue("organization_name",currentUser.getCurrentDept().getString("org_level_name"));
+ fieldSetEntityVisit.setValue("visit_date_first",new Date());
+ fieldSetEntityVisit.setValue("visit_date_new",new Date());
+ fieldSetEntityVisit.setValue("visit_count",1);
+ fieldSetEntityVisit.setValue("visit_status","宸茶闂�");
+ fieldSetEntityVisit.setValue("meeting_uuid",fse.getUUID());
+ BaseUtil.createCreatorAndCreationTime(SpringMVCContextHolder.getCurrentUser(), fieldSetEntityVisit);
+ baseDao.add(fieldSetEntityVisit);
}
-
-
return fieldSetEntity;
}
@@ -1066,6 +1237,19 @@
baseDao.loadPromptData(dt);
return dt;
}
+
+ /**
+ * 浼氳绾鍒楄〃
+ * @param fse
+ * @return
+ */
+ public DataTableEntity listMeetingMinute(FieldSetEntity fse) {
+ StringBuilder sbSql = new StringBuilder();
+ sbSql.append("\nSELECT B.meeting_topic, A.*");
+ sbSql.append("\nFROM product_oa_conference_minute A");
+ sbSql.append("\nLEFT JOIN product_oa_conference_apply B ON B.uuid = A.meeting_uuid");
+ return baseDao.listTable(sbSql.toString(), new Object[] {}, fse.getInteger(CmnConst.PAGESIZE), fse.getInteger(CmnConst.CPAGE));
+ }
public void taskMeetingRminder() {
DataTableEntity dt = baseDao.listTable(CmnConst.PRODUCT_OA_CONFERENCE_APPLY, " flow_flag=2 and ( to_remind IS NULL OR to_remind = 0 ) AND reminder_time > 0 and reminder_time>=(( UNIX_TIMESTAMP(start_time ) - UNIX_TIMESTAMP( now( ) ) ) / 60) ", new Object[]{});
@@ -1076,9 +1260,9 @@
Set<String> users = new HashSet<>();
String created_user = dt.getString(i, CmnConst.CREATED_BY + "_save_value");
//璁板綍浜�
- users.add(dt.getString(i, "record_man"));
+ users.add(dt.getString(i, "meeting_recorder"));
//涓绘寔浜�
- users.add(dt.getString(i, "record_master"));
+ users.add(dt.getString(i, "meeting_master"));
//鍙傚姞鑰�
String[] participators = dt.getString(i, "participator").split(",");
users.addAll(Arrays.asList(participators));
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