package com.consum.base.service;
|
|
import com.consum.base.pojo.FinSysTenantUserSearchParam;
|
import com.consum.model.po.FinSysOrg;
|
import com.consum.model.po.FinSysTenantUser;
|
import com.iplatform.base.util.PlatformRSAUtils;
|
import com.iplatform.core.util.AESUtils;
|
import com.iplatform.model.po.S_dept;
|
import com.iplatform.model.po.S_role;
|
import com.walker.db.page.GenericPager;
|
import com.walker.infrastructure.utils.StringUtils;
|
import com.walker.jdbc.service.BaseServiceImpl;
|
import org.springframework.stereotype.Service;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
public class FinSysTenantUserServiceImpl extends BaseServiceImpl {
|
|
private static final String SQL_GET_USER = "SELECT * FROM fin_sys_tenant_user WHERE IS_DELETE = 1 AND `STATUS` = 1 AND TENANT_ID = ?";
|
private static final String SQL_QH_PAGE_USER_PREFIX = "select fstu.* from FIN_SYS_TENANT_USER fstu where fstu.IS_DELETE = 1 ";
|
private static final String SQL_QH_PAGE_USER_PREFIX_NEW = "select fstu.* from FIN_SYS_TENANT_USER fstu where IS_DELETE = 1 and status=1 ";
|
private static final String SQL_TENANT_USER = "select fstu.*, fst.TENANT_NAME, fst.LV from FIN_SYS_TENANT_USER fstu\n" +
|
"left join (SELECT CODE, NAME AS TENANT_NAME, LV FROM FIN_SYS_TENANT) fst ON fstu.TENANT_CODE = fst.CODE\n" +
|
"where fstu.SYS_USER_ID = ?";
|
private static final String SQL_TENANT_USER_BY_USER_ID = "select * from FIN_SYS_TENANT_USER where SYS_USER_ID = ? AND STATUS = 1";
|
private static final String SQL_INSERT_ROLE_USER="insert into s_role_user(user_id, role_id, org_id) values(?,?,0)";
|
|
private static final String SQL_DELETE_ROLE_USER="DELETE FROM S_ROLE_USER WHERE USER_ID =?";
|
|
private static final String SQL_SELECT_USER_CODE="SELECT * FROM FIN_SYS_TENANT_USER where 1 = 1 ";
|
|
private static final String SQL_SELECT_USER_ID="SELECT * FROM S_ROLE where 1=1 ";
|
private static final String SQL_DEPT_ROLES = "select * from s_dept where status=0 and del_flag=0";
|
|
private static final String SQL_FIN_SYS_ORG = "SELECT * from FIN_SYS_ORG where ID=?";
|
|
|
private static final String SQL_TENANT_KF_USER = "select fstu.*, sru.USER_ID from FIN_SYS_TENANT_USER fstu\n" +
|
"left join (select USER_ID FROM S_ROLE_USER WHERE ROLE_ID = '1690961420053') sru ON fstu.SYS_USER_ID = sru.USER_ID\n" +
|
"WHERE IS_DELETE = 1 AND STATUS = 1 AND sru.USER_ID is NOT NULL AND TENANT_CODE = ?";
|
|
private static final String SQL_TEAM_KF_USER = "select fstu.*, sru.USER_ID,FST2.CODE as PARENT_Code from FIN_SYS_TENANT_USER fstu\n" +
|
" left join (select USER_ID FROM S_ROLE_USER WHERE ROLE_ID = 2) sru ON fstu.SYS_USER_ID = sru.USER_ID\n" +
|
" LEFT JOIN FIN_SYS_TENANT FST ON FST.CODE = fstu.TENANT_CODE \n" +
|
" LEFT JOIN FIN_SYS_TENANT FST2 ON FST2.ID = FST.PARENT_ID \n" +
|
" WHERE IS_DELETE = 1 AND STATUS = 1 AND sru.USER_ID is NOT NULL AND fstu.ID != ?";
|
|
/**
|
* @Description 分页查询系统用户
|
* @Author wh
|
* @Date 2023/7/17 14:26
|
*/
|
public GenericPager<FinSysTenantUser> queryAllPageUser(FinSysTenantUserSearchParam param) {
|
Map<String, Object> parameter = new HashMap<>(5);
|
StringBuilder sql = new StringBuilder(SQL_QH_PAGE_USER_PREFIX);
|
if(param.getTenantCode() > 0){
|
sql.append(" and fstu.TENANT_CODE =:tenantCode");
|
parameter.put("tenantCode", param.getTenantCode());
|
}
|
if(param.getSupplierId() !=null ){
|
sql.append(" and fstu.supplier_Id =:supplier_Id");
|
parameter.put("supplier_Id", param.getSupplierId());
|
}
|
if(StringUtils.isNotEmpty(param.getUserName())){
|
sql.append(" and USER_NAME like :userName");
|
parameter.put("userName", StringUtils.CHAR_PERCENT + param.getUserName() + StringUtils.CHAR_PERCENT);
|
}
|
if(StringUtils.isNotEmpty(param.getUserCode())){
|
sql.append(" and USER_CODE like :USER_CODE");
|
parameter.put("USER_CODE", StringUtils.CHAR_PERCENT + param.getUserCode() + StringUtils.CHAR_PERCENT);
|
}
|
// if(StringUtils.isNotEmpty(param.getUserCode())){
|
// sql.append(" and user_code like :user_code");
|
// parameter.put("user_code", StringUtils.CHAR_PERCENT + param.getUserCode() + StringUtils.CHAR_PERCENT);
|
// }
|
if(param.getStatus()!=null){
|
sql.append(" and status =:status");
|
parameter.put("status", param.getStatus());
|
}
|
|
// if(param.getRoleId()!=null &&!param.getRoleId().equals("")){
|
// sql.append(" and fstu.SYS_USER_ID in (select user_id from s_role_user where role_id = :role_id)");
|
// parameter.put("role_id", param.getRoleId());
|
// }
|
|
|
if(StringUtils.isNotEmpty(param.getUserPhone())){
|
sql.append(" and USER_PHONE =:userPhone");
|
try {
|
// 加密手机号
|
String key = PlatformRSAUtils.AES_KEY;
|
parameter.put("userPhone", AESUtils.encryptStrAES(param.getUserPhone(), key));
|
} catch (Exception e) {
|
log.error("手机号加密失败， 原因是：" + e.getMessage());
|
parameter.put("userPhone", "");
|
}
|
}
|
|
// 这里是选择人员加的
|
if(param.getType()!=null){
|
// 1 是财政用户 2 供应商 他俩的区别就是 供应商id是否为空
|
if(param.getType()==1){
|
sql.append(" and fstu.supplier_Id is null ");
|
}
|
|
if(param.getType()==2){
|
//这里是 查的供应商 供应商id 已经在上面加过了
|
}
|
}
|
// 绑定CTI客服
|
if (param.getCtiStatus() != null) {
|
if (param.getCtiStatus() == 1) {
|
sql.append(" and fstu.AGENT_JID is not null ");
|
} else {
|
sql.append(" and fstu.AGENT_JID is null ");
|
}
|
}
|
sql.append(" ORDER BY SEQ asc ,CREATE_TIME desc");
|
return this.selectSplit(sql.toString(), parameter, new FinSysTenantUser());
|
}
|
|
|
|
/**
|
* @Description 分页查询系统用户
|
* @Author wh
|
* @Date 2023/7/17 14:26
|
*/
|
public GenericPager<FinSysTenantUser> queryAllPageUserNew(FinSysTenantUserSearchParam param) {
|
Map<String, Object> parameter = new HashMap<>(5);
|
StringBuilder sql = new StringBuilder(SQL_QH_PAGE_USER_PREFIX_NEW);
|
if(param.getTenantCode() > 0){
|
sql.append(" and fstu.TENANT_CODE =:tenantCode");
|
parameter.put("tenantCode", param.getTenantCode());
|
}
|
if(param.getSupplierId() !=null ){
|
sql.append(" and fstu.supplier_Id =:supplier_Id");
|
parameter.put("supplier_Id", param.getSupplierId());
|
}
|
if(StringUtils.isNotEmpty(param.getUserName())){
|
sql.append(" and USER_NAME like :userName");
|
parameter.put("userName", StringUtils.CHAR_PERCENT + param.getUserName() + StringUtils.CHAR_PERCENT);
|
}
|
if(StringUtils.isNotEmpty(param.getUserCode())){
|
sql.append(" and user_code like :user_code");
|
parameter.put("user_code", StringUtils.CHAR_PERCENT + param.getUserCode() + StringUtils.CHAR_PERCENT);
|
}
|
if(param.getStatus()!=null){
|
sql.append(" and status like :status");
|
parameter.put("status", param.getStatus());
|
}
|
|
if(param.getRoleId()!=null &&!param.getRoleId().equals("")){
|
sql.append(" and fstu.SYS_USER_ID in (select user_id from s_role_user where role_id = :role_id)");
|
parameter.put("role_id", param.getRoleId());
|
}
|
|
|
if(StringUtils.isNotEmpty(param.getUserPhone())){
|
sql.append(" and USER_PHONE =:userPhone");
|
try {
|
// 加密手机号
|
String key = PlatformRSAUtils.AES_KEY;
|
parameter.put("userPhone", AESUtils.encryptStrAES(param.getUserPhone(), key));
|
} catch (Exception e) {
|
log.error("手机号加密失败， 原因是：" + e.getMessage());
|
parameter.put("userPhone", "");
|
}
|
}
|
|
// 这里是选择人员加的
|
if(param.getType()!=null){
|
// 1 是财政用户 2 供应商 他俩的区别就是 供应商id是否为空
|
if(param.getType()==1){
|
sql.append(" and fstu.supplier_Id is null ");
|
}
|
|
if(param.getType()==2){
|
//这里是 查的供应商 供应商id 已经在上面加过了
|
}
|
}
|
sql.append(" ORDER BY SEQ asc ,CREATE_TIME desc");
|
return this.selectSplit(sql.toString(), parameter, new FinSysTenantUser());
|
}
|
|
public FinSysTenantUser queryOneById(String id) {
|
FinSysTenantUser finSysTenantUser = new FinSysTenantUser();
|
finSysTenantUser.setId(Long.valueOf(id));
|
List<FinSysTenantUser> finSysTenantUserList = this.select(finSysTenantUser);
|
if(!StringUtils.isEmptyList(finSysTenantUserList)){
|
return finSysTenantUserList.get(0);
|
}
|
return null;
|
}
|
|
public FinSysTenantUser queryOneByUserId(String userId) {
|
List<FinSysTenantUser> finSysTenantUserList = this.select(SQL_TENANT_USER, new Object[]{userId}, new FinSysTenantUser());
|
if (finSysTenantUserList.size() > 0) {
|
return finSysTenantUserList.get(0);
|
} else {
|
return null;
|
}
|
}
|
|
/**
|
* 批量添加角色对应的用户。
|
* @param roleIdList
|
* @param userId
|
*/
|
public void execInsertRoleUserList(List<Long> roleIdList, Long userId){
|
List<Object[]> parameters = new ArrayList<>();
|
Object[] one = null;
|
for(long roleId : roleIdList){
|
one = new Object[2];
|
one[0] = userId;
|
one[1] = roleId;
|
parameters.add(one);
|
}
|
this.execBatchUpdate(SQL_INSERT_ROLE_USER, parameters);
|
}
|
|
/**
|
* 批量添加角色对应的用户。支持同时多个用户
|
*/
|
public void execInsertRoleUserList(List<FinSysTenantUser> finSysTenantUsers){
|
List<Object[]> parameters = new ArrayList<>();
|
for (FinSysTenantUser finSysTenantUser : finSysTenantUsers) {
|
Object[] one = null;
|
for(long roleId : finSysTenantUser.getRoleList()){
|
one = new Object[2];
|
one[0] = finSysTenantUser.getSysUserId();
|
one[1] = roleId;
|
parameters.add(one);
|
}
|
}
|
|
this.execBatchUpdate(SQL_INSERT_ROLE_USER, parameters);
|
}
|
|
/**
|
* 批量删除用户角色
|
* @param userId
|
*/
|
public void execDelRoleUserList(Long userId){
|
List<Object[]> parameters = new ArrayList<>();
|
Object[] one = new Object[1];
|
one[0]=userId;
|
parameters.add(one);
|
this.execBatchUpdate(SQL_DELETE_ROLE_USER, parameters);
|
}
|
|
|
/**
|
* 根据userCode查询重复。
|
* @param userCode 用户登录标识
|
* @return
|
*/
|
public Integer getByUserCode(String userCode){
|
Map<String, Object> parameter = new HashMap<>();
|
StringBuilder sql = new StringBuilder(SQL_SELECT_USER_CODE);
|
sql.append("and USER_CODE= :userCode");
|
parameter.put("userCode", userCode);
|
sql.append(" limit 1");
|
List<FinSysTenantUser> select = this.select(sql.toString(), parameter, new FinSysTenantUser());
|
Integer flag=0;
|
if (select!=null && select.size() >0){
|
flag=select.size();
|
}
|
return flag;
|
}
|
|
/**
|
* 根据USERID查询角色
|
* @param userId
|
* @return
|
*/
|
public List<S_role> getByUserId(Long userId){
|
Map<String, Object> parameter = new HashMap<>();
|
StringBuilder sql = new StringBuilder(SQL_SELECT_USER_ID);
|
sql.append("and ROLE_ID IN (SELECT DISTINCT(ROLE_ID) FROM S_ROLE_USER WHERE USER_ID= :userId )");
|
parameter.put("userId", userId);
|
List<S_role> select = this.select(sql.toString(), parameter, new S_role());
|
return select;
|
}
|
|
public List<S_dept> selectDept(String deptName) {
|
StringBuilder sqlStr=new StringBuilder(SQL_DEPT_ROLES);
|
Map<String, Object> paramts = new HashMap<>();
|
sqlStr.append(" and dept_name =:dept_name");
|
paramts.put("dept_name",deptName);
|
List<S_dept> orgDeptList = this.select(sqlStr.toString(), paramts, new S_dept());
|
return orgDeptList;
|
}
|
|
/**
|
* @Description 获取客服
|
* @Author wh
|
* @Date 2023/7/19 10:12
|
*/
|
public List<FinSysTenantUser> selectKF(String tenantCode) {
|
// 查询角色为客服且机构和当前请求用户一致的
|
return this.select(SQL_TENANT_KF_USER, new Object[]{tenantCode}, new FinSysTenantUser());
|
}
|
// public FinSysTenantUser selectKF(String tenantCode) {
|
// // 查询角色为客服且机构和当前请求用户一致的
|
// List<FinSysTenantUser> finSysTenantUserList = this.select(SQL_TENANT_KF_USER, new Object[]{tenantCode}, new FinSysTenantUser());
|
// if (finSysTenantUserList.size() > 0) {
|
// return finSysTenantUserList.get(0);
|
// } else {
|
// return null;
|
// }
|
// }
|
|
/**
|
* @Description 获取所有客服信息，不包含自己
|
* @Author wh
|
* @Date 2023/7/20 15:33
|
*/
|
public List<FinSysTenantUser> selectTeamKF(Long id) {
|
// 查询角色为客服且机构和当前请求用户一致的
|
List<FinSysTenantUser> finSysTenantUserList = this.select(SQL_TEAM_KF_USER, new Object[]{id}, new FinSysTenantUser());
|
return finSysTenantUserList;
|
}
|
|
/**
|
* 根据机构id查询机构
|
* @param orgId
|
* @return
|
*/
|
public List<FinSysOrg> selectFinSysOrg(String orgId) {
|
List<FinSysOrg> select = this.select(SQL_FIN_SYS_ORG, new Object[]{orgId}, new FinSysOrg());
|
return select;
|
}
|
|
public FinSysTenantUser queryBySysUserId(Long userId) {
|
List<FinSysTenantUser> select = this.select(SQL_TENANT_USER_BY_USER_ID, new Object[]{userId}, new FinSysTenantUser());
|
if (StringUtils.isEmptyList(select)) {
|
return null;
|
} else {
|
return select.get(0);
|
}
|
}
|
|
private static final String SQL_GET_ALL_USER = "select DISTINCT( fstu.sys_user_id), fstu.USER_NAME as USER_Name, fstu.id as Id from (\n" +
|
"SELECT DISTINCT(CREATE_BY), EVENT_MANAGE_ID FROM FIN_EVENT_MANAGE_RECORD) femr LEFT JOIN FIN_SYS_TENANT_USER fstu ON femr.CREATE_BY = fstu.ID\n" +
|
"LEFT JOIN FIN_EVENT_MANAGE fem ON fem.id = femr.EVENT_MANAGE_ID WHERE USER_NAME is not NULL ";
|
|
/**
|
* @Description 查询数据统计的信息
|
* @Author wh
|
* @Date 2023/9/5 11:53
|
* */
|
public List<FinSysTenantUser> getStatics(FinSysTenantUserSearchParam finSysTenantUserSearchParam) {
|
Map<String, Object> parameters = new HashMap<>(10);
|
StringBuilder sql = new StringBuilder(SQL_GET_ALL_USER);
|
sql.append(" and fem.PROJECT_ID = :projectId");
|
parameters.put("projectId", finSysTenantUserSearchParam.getProjectId());
|
if (StringUtils.isNotEmpty(finSysTenantUserSearchParam.getUserName())) {
|
sql.append(" and fstu.USER_NAME like :userName");
|
parameters.put("userName", StringUtils.CHAR_PERCENT + finSysTenantUserSearchParam.getUserName() + StringUtils.CHAR_PERCENT);
|
}
|
if(finSysTenantUserSearchParam.getStartTime() != null && finSysTenantUserSearchParam.getStartTime() > 0){
|
sql.append(" and fem.create_time >= :startTime");
|
parameters.put("startTime", finSysTenantUserSearchParam.getStartTime());
|
}
|
if(finSysTenantUserSearchParam.getEndTime() != null && finSysTenantUserSearchParam.getEndTime() > 0){
|
sql.append(" and fem.create_time <= :endTime");
|
parameters.put("endTime", finSysTenantUserSearchParam.getEndTime());
|
}
|
sql.append(" ORDER BY ID DESC");
|
return this.select(sql.toString(), parameters, new FinSysTenantUser());
|
}
|
|
/**
|
* @Description 根据OrgId获取用户信息
|
* @Author wh
|
* @Date 2023/10/4 15:49
|
*/
|
public List<FinSysTenantUser> getByOrgId(Long orgId) {
|
return this.select(SQL_GET_USER, new Object[]{orgId}, new FinSysTenantUser());
|
}
|
}
|
