package com.product.org.admin.service; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import com.alibaba.druid.util.StringUtils; import com.product.admin.service.CodeService; import com.product.core.dao.BaseDao; import com.product.core.entity.DataTableEntity; import com.product.core.entity.FieldSetEntity; import com.product.core.exception.BaseException; import com.product.core.permission.PermissionService; import com.product.core.service.support.AbstractBaseService; import com.product.core.service.support.QueryFilterService; import com.product.core.spring.context.SpringMVCContextHolder; import com.product.org.admin.config.CmnConst; import com.product.util.BaseUtil; /** * * Copyright LX-BASE * * @Title: LX-BASE- * @Project: StaffChangeService * @Date: 2020-06-09 15:27 * @Author: 杜洪波 * @Description:人员变更 */ @Component public class StaffChangeService extends AbstractBaseService { @Autowired public BaseDao baseDao; @Autowired PermissionService permissionService; @Autowired public CodeService codeService; @Autowired QueryFilterService queryFilterService; /** * 人员变更信息列表 * * @param cpage * @param pageSize * @return * @throws BaseException */ public DataTableEntity listStaffChange(Integer cpage, Integer pageSize ,FieldSetEntity fse) throws BaseException { //数据策略 String dataFilter=permissionService.getDataFilter("b."+CmnConst.ORG_LEVEL_UUID) ; //高级查询 String searchFilter=queryFilterService.getQueryFilter(fse); if(!StringUtils.isEmpty(dataFilter)) { dataFilter=" WHERE "+dataFilter; if (!StringUtils.isEmpty(searchFilter)) { dataFilter+=" AND "+ searchFilter; } }else { if (!StringUtils.isEmpty(searchFilter)) { dataFilter=searchFilter; } } StringBuilder sql=new StringBuilder(); sql.append(" SELECT * FROM ( "); sql.append(" SELECT a.uuid uuid,b.uuid uuid1,c.uuid uuid2,d.uuid uuid3,e.uuid uuid4,f.uuid uuid5,a.staff_uuid,a.transferred_staff_post_uuid,a.movement_effective_utc_date,a.result_status,b.show_name,b.preferred_name,c.client_name,d.org_level_all,e.job_post_name,f.job_grade_name "); sql.append(" FROM product_sys_staff_movement a "); sql.append(" LEFT JOIN product_sys_staffs b ON a.staff_uuid=b.uuid "); sql.append(" LEFT JOIN product_sys_clients c ON c.uuid=b.client_uuid "); sql.append(" LEFT JOIN product_sys_org_levels d ON d.uuid=b.dept_uuid "); sql.append(" LEFT JOIN product_sys_job_posts e ON e.uuid=b.job_post_uuid "); sql.append(" LEFT JOIN product_sys_job_post_grades f ON f.uuid=b.job_post_grade_uuid "); sql.append(dataFilter); sql.append(" )b "); DataTableEntity dt=baseDao.listTable(sql.toString(), null, pageSize, cpage); dt.getMeta().addAliasTable("product_sys_staff_movement","a"); dt.getMeta().addAliasTable("product_sys_staffs","b"); dt.getMeta().addAliasTable("product_sys_clients","c"); dt.getMeta().addAliasTable(CmnConst.PRODUCT_SYS_ORG_LEVELS,"d"); dt.getMeta().addAliasTable("product_sys_job_posts","e"); dt.getMeta().addAliasTable("product_sys_job_post_grades","f"); dt.getMeta().addAliasField("product_sys_staffs.uuid", "uuid1"); dt.getMeta().addAliasField("product_sys_staffs.show_name", "show_name"); if (!BaseUtil.dataTableIsEmpty(dt)) { baseDao.listInternationDataTable(dt, null); baseDao.loadPromptData(dt); } return dt; } /** * 人员变更信息详情 * * @param uuid * @return * @throws BaseException */ public FieldSetEntity findStaffChange(String uuid) throws BaseException { return baseDao.getFieldSetEntity("product_sys_staff_movement", uuid, false); } /** * 格式化员工信息 * * @return * @throws BaseException */ public DataTableEntity formatStaff(DataTableEntity dt) throws BaseException { if (dt.getRows() > 0) { for (int i = 0; i < dt.getRows(); i++) { FieldSetEntity staff = dt.getFieldSetEntity(i); FieldSetEntity staffInfo = baseDao.getFieldSetEntityByFilter("product_sys_staffs", "uuid=?",new String[] { staff.getString("uuid") }, false); FieldSetEntity org_level = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_ORG_LEVELS, "uuid=?",new String[] { staffInfo.getString("org_level_uuid") }, false); if (org_level != null) { staff.setValue("org_level", org_level.getString(CmnConst.ORG_LEVEL_ALL)); } FieldSetEntity dept_level = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_ORG_LEVELS, "uuid=?",new String[] { staffInfo.getString("dept_uuid") }, false); dept_level.getMeta().addAliasField(CmnConst.PRODUCT_SYS_ORG_LEVELS, CmnConst.ORG_LEVEL_ALL); dept_level.getMeta().addAliasField(CmnConst.PRODUCT_SYS_ORG_LEVELS, CmnConst.ORG_LEVEL_NAME); FieldSetEntity dept_levels = baseDao.listInternationDataTable(dept_level, null); if (dept_levels != null) { staff.setValue("client_and_org", dept_levels.getSubDataTable(CmnConst.ORG_LEVEL_ALL)); staff.setValue("dept_name", dept_levels.getSubDataTable(CmnConst.ORG_LEVEL_NAME)); } } } dt.getMeta().addAliasField("product_sys_staffs.show_name", "show_name"); dt.getMeta().addAliasField("product_sys_job_posts.job_post_name", "job_post_name"); dt.getMeta().addAliasField("product_sys_job_post_grades.job_grade_name", "job_grade_name"); dt.getMeta().addAliasField("product_sys_clients.client_name", "client_name"); dt.getMeta().addAliasField("product_sys_org_levels.org_level_all", CmnConst.ORG_LEVEL_ALL); DataTableEntity listInternationDataTable = baseDao.listInternationDataTable(dt, null); return listInternationDataTable; } /** * 格式化员工信息 * * @return * @throws BaseException */ public void moreStaffs() throws BaseException { // String sql="select * from mrbase_sys_users a, mrbase_sys_staffs b where a.user_id=b.user_id"; // DataTableEntity adt=baseDao.listTable(sql, new Object[] {}); // DataTableEntity dt=baseDao.listTable(sql, new Object[] {}, 100, 1, null); // // String sql1="select a.user_id,(select first_employment_date from mrbase_sys_staff_employment_info c where c.staff_uuid=b.uuid) first_employment_date from mrbase_sys_users a, mrbase_sys_staffs b where a.user_id=b.user_id"; // DataTableEntity adt1=baseDao.listTable(sql1, new Object[] {}); // DataTableEntity dt1=baseDao.listTable(sql1, new Object[] {}, 100, 1, null); // // String sql2="SELECT a.*,CONCAT(CONVERT(a.file_size / 1024, decimal(10,2)), 'KB') file_size_kb,b.show_name FROM (" // + "SELECT * FROM mrbase_sys_document WHERE directory_uuid = '' " // + ") a " // + "LEFT JOIN mrbase_sys_staffs b ON a.created_by = b.user_id LEFT JOIN mrbase_sys_attachments c ON b.staff_avatar=c.uuid "; // DataTableEntity adt2=baseDao.listTable(sql2, new Object[] {}); // DataTableEntity dt2=baseDao.listTable(sql2, new Object[] {}, 100, 1, null); // // String sql3="SELECT * FROM mrbase_sys_users t JOIN mrbase_sys_staffs n ON t.user_id = n.user_id AND n.uuid = (SELECT uuid from mrbase_sys_staffs where show_name='dfd') "; // DataTableEntity adt3=baseDao.listTable(sql3, new Object[] {}); // DataTableEntity dt3=baseDao.listTable(sql3, new Object[] {}, 100, 1, null); // // String sql4="SELECT a.dict_id,a.uuid,a.dict_name,a.dict_label,a.is_used,a.sequence,a.dict_value FROM (SELECT * FROM (select dict_id,uuid,dict_name,dict_label,is_used,sequence,dict_value from mrbase_sys_dict WHERE (client_uuid is null or client_uuid='') and is_used=1 and CONCAT(dict_name,dict_value ) not in( select CONCAT(dict_name,dict_value ) from mrbase_sys_dict WHERE client_uuid='781cb807-b7a6-43ec-8c60-f7d23499dfb7') union all select dict_id,uuid,dict_name,dict_label,is_used,sequence,dict_value from mrbase_sys_dict b WHERE is_used=1 and client_uuid='781cb807-b7a6-43ec-8c60-f7d23499dfb7') a) a "; // DataTableEntity adt4=baseDao.listTable(sql4, new Object[] {}); // DataTableEntity dt4=baseDao.listTable(sql4, new Object[] {}, 100, 1, null); // // String sql5="SELECT *, ? AS 'data_type', ? , (select 'sge ?* esg' FROM mrbase_sys_staffs AS r WHERE r.user_id= a.user_id ) as k from mrbase_sys_users a where user_id=? "; // DataTableEntity adt5=baseDao.listTable(sql5, new Object[] {5,1,186}); // DataTableEntity dt5=baseDao.listTable(sql5, new Object[] {5,1,186}, 100, 1, null); // // String sql6="SELECT ? AS 'data_type', ? , (select 'sge ?* esg' FROM mrbase_sys_staffs AS r WHERE r.user_id= a.user_id ) as k from mrbase_sys_users a where user_id=? union all select 6,3,'sdgsd' from mrbase_sys_users a where user_id=?"; // DataTableEntity adt6=baseDao.listTable(sql6, new Object[] {5,1,186,186}); // DataTableEntity dt6=baseDao.listTable(sql6, new Object[] {5,1,186,186}, 100, 1, null); // // String sql7=" select distinct uuid,user_account from mrbase_sys_users "; // // //报错：select count(distinct a.*,b.show_name) from mrbase_sys_users a join mrbase_sys_staffs b on a.user_id=b.user_id // //DataTableEntity dt=baseDao.listTable(sql, new Object[] {}, 100, 1, null); // //DataTableEntity dt1=baseDao.listTable(sql1, new Object[] {}, 100, 1, null); // DataTableEntity adt7=baseDao.listTable(sql7, new Object[] {}); // DataTableEntity dt7=baseDao.listTable(sql7, new Object[] {}, 100, 1, null); // // String sql8="select uuid from mrbase_sys_users union all select uuid from mrbase_sys_staffs"; // DataTableEntity adt8=baseDao.listTable(sql8, new Object[] {}); // DataTableEntity dt8=baseDao.listTable(sql8, new Object[] {}, 100, 1, null); // // String sql9="select uuid from mrbase_sys_users"; // DataTableEntity adt9=baseDao.listTable(sql9, new Object[] {}); // DataTableEntity dt9=baseDao.listTable(sql9, new Object[] {}, 100, 1, null); // // String sql10="select ? as 'uuid' from mrbase_sys_users"; // DataTableEntity adt10=baseDao.listTable(sql10, new Object[] {3433}); // DataTableEntity dt10=baseDao.listTable(sql10, new Object[] {3433}, 100, 1, null); // // String sql12="SELECT DISTINCT preferred_name AS name FROM mrbase_sys_staffs"; // DataTableEntity adt12=baseDao.listTable(sql12, new Object[] {}); // DataTableEntity dt12=baseDao.listTable(sql12, new Object[] {}, 100, 1, null); // // String sql11="SELECT DISTINCT s.uuid, ? AS 'data_type' FROM mrbase_sys_staffs s"; // DataTableEntity adt11=baseDao.listTable(sql11, new Object[] {1}); // DataTableEntity dt11=baseDao.listTable(sql11, new Object[] {1}, 100, 1, null); String sql13="select * from (select a53.uuid value_field,a53.job_post_name view_fields,a74.org_level_all f1,a53.job_post_name f2,a74.uuid f3,a53.uuid uuid0,a74.uuid uuid1 FROM product_sys_job_posts a53 left join product_sys_org_levels a74 on a53.dept_uuid=a74.uuid where (a53.dept_uuid='' and is_used=1) order by a53.sequence asc ) b "; DataTableEntity adt13=baseDao.listTable(sql13, new Object[] {}); DataTableEntity dt13=baseDao.listTable(sql13, new Object[] {}, 100, 1, null); String sql14="SELECT\r\n" + " t.attendance_master_id,\r\n" + " t.uuid,\r\n" + " t.utc_att_date,\r\n" + " t.utc_att_time_zone,\r\n" + " t.staff_uuid,\r\n" + " t.raw_shift_uuid,\r\n" + " t.raw_start_utc_time,\r\n" + " t.raw_start_time_zone,\r\n" + " t.raw_end_utc_time,\r\n" + " t.raw_end_time_zone,\r\n" + " t.schedule_shift_uuid,\r\n" + " t.schedule_start_utc_time,\r\n" + " t.schedule_start_time_zone,\r\n" + " t.schedule_end_utc_time,\r\n" + " t.schedule_end_time_zone,\r\n" + " t.prefix_shift_abbr,\r\n" + " t.suffix_shift_abbr,\r\n" + " t.official_shift_uuid,\r\n" + " t.official_start_utc_time,\r\n" + " t.official_start_time_zone,\r\n" + " t.official_end_utc_time,\r\n" + " t.official_end_time_zone,\r\n" + " t.shift_modification_reason_uuid,\r\n" + " t.shift_modification_reason_text,\r\n" + " t.status_uuid,\r\n" + " t.is_punch_in_required,\r\n" + " t.is_punch_out_required,\r\n" + " t.actual_punch_in_uuid,\r\n" + " t.actual_punch_out_uuid,\r\n" + " t.actual_punch_in_utc_datetime,\r\n" + " t.actual_punch_in_time_zone,\r\n" + " t.actual_punch_out_utc_datetime,\r\n" + " t.actual_punch_out_time_zone,\r\n" + " t.modified_flow_task_uuid,\r\n" + " t.modified_punch_in_utc_datetime,\r\n" + " t.modified_punch_in_time_zone,\r\n" + " t.modified_punch_out_utc_datetime,\r\n" + " t.modified_punch_out_time_zone,\r\n" + " t.official_punch_in_utc_datetime,\r\n" + " t.official_punch_in_time_zone,\r\n" + " t.official_punch_out_utc_datetime,\r\n" + " t.official_punch_out_time_zone,\r\n" + " t.schedule_working_minutes,\r\n" + " t.actual_working_minutes,\r\n" + " t.modified_working_minutes,\r\n" + " t.official_working_minutes,\r\n" + " t.actual_meal_minutes,\r\n" + " t.modified_meal_mintues,\r\n" + " t.official_meal_minutes,\r\n" + " t.actual_break_minutes,\r\n" + " t.modified_break_minutes,\r\n" + " t.official_break_minutes,\r\n" + " t.actual_ot_minutes,\r\n" + " t.modified_ot_minutes,\r\n" + " t.official_total_ot_minutes,\r\n" + " t.actual_late_minutes,\r\n" + " t.modified_late_minutes,\r\n" + " t.official_total_late_minutes,\r\n" + " t.actual_early_leave_minutes,\r\n" + " t.modified_early_leave_minutes,\r\n" + " t.official_total_early_leave_minutes,\r\n" + " t.is_adhoc,\r\n" + " t.is_late,\r\n" + " t.is_early_leave,\r\n" + " t.is_ot,\r\n" + " t.is_absense,\r\n" + " t.is_on_leave,\r\n" + " t.leave_type_uuid,\r\n" + " t.leave_type_remark_uuid,\r\n" + " t.is_holiday,\r\n" + " t.holiday_uuid,\r\n" + " t.is_onduty,\r\n" + " t.is_pre_locked,\r\n" + " t.is_locked,\r\n" + " t.is_work_from_home,\r\n" + " t.is_out_of_office,\r\n" + " t.is_missing_punch,\r\n" + " t.shift_property_group_uuid,\r\n" + " t.org_level_uuid,\r\n" + " t.attendance_modification_reason_uuid,\r\n" + " t.attendance_modification_reason_text,\r\n" + " t.remarks,\r\n" + " t.application_uuid,\r\n" + " t.site_uuid,\r\n" + " t.cost_center_uuid,\r\n" + " t.actual_absense_minutes,\r\n" + " t.modified_absense_minutes,\r\n" + " t.official_absense_minutes,\r\n" + " t.actual_before_work_minutes,\r\n" + " t.modified_before_work_minutes,\r\n" + " t.official_before_work_minutes,\r\n" + " t.actual_after_work_minutes,\r\n" + " t.modified_after_work_minutes,\r\n" + " t.official_after_work_minutes,\r\n" + " t.is_punch_criteria_matched,\r\n" + " t.is_temp,\r\n" + " t.actual_leave_minutes,\r\n" + " t.modified_leave_minutes,\r\n" + " t.official_total_leave_minutes \r\n" + "FROM\r\n" + " ess_att_attendance_master t,(\r\n" + " SELECT\r\n" + " '6973' AS staff_uuid from dual UNION ALL\r\n" + " SELECT\r\n" + " '6982' AS staff_uuid from dual UNION ALL\r\n" + " SELECT\r\n" + " '6983' AS staff_uuid from dual \r\n" + " ) AS jt \r\n" + "WHERE jt.staff_uuid = t.staff_uuid \r\n" + " AND t.utc_att_date BETWEEN '2021-09-01T00:00Z[UTC]' \r\n" + " AND '2021-09-30T23:59:59.847Z[UTC]' \r\n" + "ORDER BY\r\n" + " t.utc_att_date DESC,\r\n" + " t.staff_uuid \r\n"; DataTableEntity adt14=baseDao.listTable(sql14, new Object[] {}); DataTableEntity dt14=baseDao.listTable(sql14, new Object[] {}, 20, 1, null); SpringMVCContextHolder.getSystemLogger().info("---------------------------------------------"); // if(adt.getRows()!=dt.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------0查询不正确："+adt.getRows()+" "+dt.getSqle().getTotalCount()); // } // if(adt1.getRows()!=dt1.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------1查询不正确："+adt1.getRows()+" "+dt1.getSqle().getTotalCount()); // } // if(adt2.getRows()!=dt2.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------2查询不正确："+adt2.getRows()+" "+dt2.getSqle().getTotalCount()); // } // if(adt3.getRows()!=dt3.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------3查询不正确："+adt3.getRows()+" "+dt3.getSqle().getTotalCount()); // } // if(adt4.getRows()!=dt4.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------4查询不正确："+adt4.getRows()+" "+dt4.getSqle().getTotalCount()); // } // if(adt5.getRows()!=dt5.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------5查询不正确："+adt5.getRows()+" "+dt5.getSqle().getTotalCount()); // } // if(adt6.getRows()!=dt6.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------6查询不正确："+adt6.getRows()+" "+dt6.getSqle().getTotalCount()); // } // if(adt7.getRows()!=dt7.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------7查询不正确："+adt7.getRows()+" "+dt7.getSqle().getTotalCount()); // } // if(adt8.getRows()!=dt8.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------8查询不正确："+adt8.getRows()+" "+dt8.getSqle().getTotalCount()); // } // if(adt9.getRows()!=dt9.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------9查询不正确："+adt9.getRows()+" "+dt9.getSqle().getTotalCount()); // } // if(adt10.getRows()!=dt10.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------10查询不正确："+adt10.getRows()+" "+dt10.getSqle().getTotalCount()); // } // // if(adt11.getRows()!=dt11.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------11查询不正确："+adt11.getRows()+" "+dt11.getSqle().getTotalCount()); // } // if(adt12.getRows()!=dt12.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------12查询不正确："+adt12.getRows()+" "+dt12.getSqle().getTotalCount()); // } // if(adt13.getRows()!=dt13.getSqle().getTotalCount()) { // SpringMVCContextHolder.getSystemLogger().error("----------------------13查询不正确："+adt13.getRows()+" "+dt13.getSqle().getTotalCount()); // } if(adt14.getRows()!=dt14.getSqle().getTotalCount()) { SpringMVCContextHolder.getSystemLogger().error("----------------------14查询不正确："+adt14.getRows()+" "+dt14.getSqle().getTotalCount()); } } }