product-server-administration
blame | 历史 | 补丁 | 提交 | 提交对比 | 显示空白
zm
2024-11-04 9d22bf2b596d8f5627f671f009b3f42d71d73bf4 
 src/main/java/com/product/administration/service/WorkAttendanceKanbanService.java


@@ -1,9 +1,11 @@


package com.product.administration.service;




import java.time.LocalDate;


import java.util.ArrayList;


import java.util.Arrays;


import java.util.Date;


import java.util.List;


import java.time.YearMonth;




import cn.hutool.core.util.ObjectUtil;


import org.springframework.beans.factory.annotation.Autowired;


@@ -450,6 +452,10 @@


         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));


@@ -466,11 +472,11 @@




      StringBuilder sb=new StringBuilder();


      sb.append(" SELECT ");


      sb.append(" ( SELECT user_name FROM product_sys_users WHERE user_id = a.created_by ) created_by, ");


      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,workDayOfMonth,lateCome,leaveEarly,total_hours,askForLeave,(dayOfWork - workDayOfMonth-askForLeave) absenteeism  ");


      sb.append(" FROM(SELECT b.created_by,b.dayOfWork,b.workDayOfMonth+bb.replenish_date workDayOfMonth,b.dept_uuid,b.lateCome1 lateCome,b.leaveEarly2 leaveEarly FROM ");


      sb.append(" (SELECT a.created_by,"+dayOfWork+" dayOfWork,COUNT( day_of_month ) workDayOfMonth,a.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, ");


@@ -478,7 +484,7 @@


      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(" AND DATE_FORMAT( d.punch_time, '%Y-%m' )=? ");


      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  ");


@@ -490,23 +496,36 @@


      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(" LEFT JOIN (SELECT user_id,sum( duration ) total_hours FROM product_oa_work_overtime o ");


      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(DATE_FORMAT(end_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' )=? AND flow_flag = 2 ) a ");


      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(" LEFT JOIN product_sys_company_holiday b on b.date_holiday<= a.end_time and b.date_holiday>=a.start_time ");


      sb.append(" GROUP BY a.created_by,a.askForLeave) a GROUP BY created_by )");




      sb.append(" c ON a.created_by = c.created_by ");


      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"));


         }


      }




      //处理补卡的情况


//      List<Object>param2= new ArrayList<>();


@@ -533,8 +552,5 @@


      return  dataTableEntityReport;




   }










}