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