package com.yc.service.user; import com.yc.action.grid.GridUtils; import com.yc.entity.Page; import com.yc.entity.UserAccountEntity; import com.yc.factory.FactoryBean; import com.yc.sdk.password.action.ChangePassword; import com.yc.service.BaseService; import com.yc.utils.IPUtil; import org.springframework.dao.DataAccessException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.CallableStatementCreator; import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.stereotype.Service; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Date; import java.util.*; /** * 用户账号有关业务处理 * * @author 邓文峰 2010-2-24 * */ @Service("UserAccountServiceImpl") public class UserAccountServiceImpl extends BaseService implements UserAccountServiceIfc { // 登录日志 private final String LOGIN_LOG = "set nocount on ; \n" + " declare @UserCode varchar(50),@UserName varchar(50), @Useripaddress varchar(50) , @SessionId varchar(80) ; \n" + " select @UserCode = ? \n select @UserName =? \n select @Useripaddress =? \n select @SessionId =? \n" + " if not exists(select 1 from _sysLoginLog a where a.sessionid = @SessionId ) \n" + " begin \n" + " insert into _sysLoginLog(UserGUID,userCode,userName,inTime,useripaddress,sessionid ) \n" + " values('.',@UserCode,@UserName" + ",getDate(),@useripaddress,@sessionid) \n" + " end else \n" + " begin \n" + " update a set inTime = getDate() from _sysLoginLog a where a.sessionid = @SessionId \n" + " end \n"; // 用户权限 private final String GET_USER_PROFILE ="set nocount on; declare @formid int,@optype int,@hideFields varchar(800),@ReadOnlyFields varchar(800),@datelimit int \n"+ " declare @gridop varchar(20),@gridopVal varchar(20),@gridreadonlyyn int,@formtype int,@lastoptype int \n" + " declare @lastStatisIds varchar(4000),@StatisIds varchar(4000) \n"+ " declare @table table( formid int,optype int,hideFields varchar(800),ReadOnlyFields varchar(800),datelimit int, \n"+ " gridop varchar(20),gridopVal varchar(20),gridreadonlyyn int,formtype int,StatisIds varchar(4000) ) \n"+ " insert into @table (formid, optype,formtype,StatisIds ) \n"+ // " select a.formid, a.optype,a.hidefields,a.readonlyfields,a.datelimit,a.gridop,a.gridopVal,a.gridreadonlyyn,b.formtype \n"+ // " from gprofileformop a left join _sysmenu b on a.formid=b.formid where a.profileid in(select profileid from gprofile \n"+ // " where profileid in(select profileid from gUserProfile where userCode=?)) order by a.formid,optype \n"+ //by by danaus 2020/4/20 17:25,修正增加读取子职责权限 " select a.formid,a.optype,a.formtype,a.StatisIds \n" + "from (\n" + "\tselect a.formid , a.optype,b.formtype,a.StatisIds \n" + "\tfrom gprofileformop a \n" + "\tleft join _sysmenu b on a.formid=b.formid \n" + "\tinner join gprofile c on a.ProfileID = c.ProfileID\n" + "\tinner join gUserProfile d on a.ProfileID = d.ProfileID\n" + "\twhere d.userCode=?\n" + "\n" + "\t union \n" + "\t select c.formid, c.optype,d.formtype, c.StatisIds \n" + "\t FROM gprofile_subprofile a \n" + "\t INNER JOIN gUserProfile b ON a.profileid = b.ProfileID \n" + "\t INNER JOIN gProfileFormOP c ON a.subprofileid = c.ProfileID\n" + "\t left join _sysmenu d on c.formid=d.formid \n" + "\t where b.UserCode = ?\n" + " ) a \n" + " declare mycurFormId cursor for \n"+ "select formid from @table group by formid having count(1) > 1 \n"+ "open mycurFormId \n"+ "fetch next from mycurFormId into @formid \n"+ "while @@fetch_status = 0 \n"+ "begin \n"+ " set @lastoptype = 0 \n"+ " declare mycurOp cursor for \n"+ " select isnull(optype,0) as optype from @table where formid = @formid \n"+ " open mycurOp \n"+ " fetch next from mycurOp into @optype \n"+ " while @@fetch_status = 0 \n"+ " begin \n"+ " select @lastoptype = @lastoptype | @optype \n"+ " fetch next from mycurOp into @optype \n"+ " end \n"+ " close mycurOp \n"+ " deallocate mycurOp \n"+ " set @lastStatisIds = '' \n" + " declare mycurStatisIds cursor for \n" + " select isnull(StatisIds,'') as StatisIds from @table where formid = @formid and isnull(StatisIds,'') <> '' \n" + " open mycurStatisIds \n" + " fetch next from mycurStatisIds into @StatisIds \n" + " while @@fetch_status = 0 \n" + " begin \n" + " if not exists(select 1 from getinstr(@lastStatisIds) a where a.list = isnull(@StatisIds,'') ) \n" + " begin \n" + " set @lastStatisIds = case when isnull(@lastStatisIds,'') = '' then '' else isnull(@lastStatisIds,'') + ';' end + isnull(@StatisIds,'') \n" + " end \n" + " fetch next from mycurStatisIds into @StatisIds" + " end \n" + " close mycurStatisIds \n" + " deallocate mycurStatisIds \n"+ " update a set optype = @lastoptype,StatisIds = @StatisIds from @table a where formid = @formid \n"+ " fetch next from mycurFormId into @formid \n"+ "end \n"+ "close mycurFormId \n"+ "deallocate mycurFormId \n"+ " \n"+ " \n"+ "select distinct formid, optype,hidefields,readonlyfields,datelimit,gridop,gridopVal,gridreadonlyyn,formtype,StatisIds from @table \n"; private final String GET_DEFAULT_SET = "set nocount on ; select * from _sysloaddefaultvalue where defaultset=?"; @Override public void loginLog(String userCode, String userName, String userIpAddress,String sessionId) { this.jdbcTemplate.update(LOGIN_LOG, userCode, userName, userIpAddress,sessionId); } @Override public Map> getUserProfiles(String userCode) { SqlRowSet rs = this.jdbcTemplate.queryForRowSet(GET_USER_PROFILE, userCode,userCode); Map> map = new HashMap>(); Map temp = null; while (rs.next()) { temp = new HashMap(); temp.put("optype", rs.getObject("optype")); temp.put("statisIds", rs.getObject("StatisIds")); //------by by danaus 2020/4/20 17:26 这些已不使用 //temp.put("hidefields", rs.getObject("hidefields")); //temp.put("readonlyfields", rs.getObject("readonlyfields")); //temp.put("datelimit", rs.getObject("datelimit")); //temp.put("gridop", rs.getObject("gridop")); //temp.put("gridopval", rs.getObject("gridopVal")); //temp.put("gridreadonlyyn", rs.getObject("gridreadonlyyn")); //------ temp.put("formtype", rs.getObject("formtype")); map.put(rs.getString("formid"), temp); } return map; } @Override public List> getDefaultSet(String defaultSetName) { return this.jdbcTemplate.queryForList(GET_DEFAULT_SET, defaultSetName); } @Override public SqlRowSet getRowSet(String sql, Object... objects) { return this.jdbcTemplate.queryForRowSet(sql, objects); } @Override public List> StatisticUserLoginByDay(final Date start, final Date end) { return this.jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection conn) throws SQLException { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); CallableStatement cs = conn.prepareCall("{call onLineUser_statistic_by_day(?,?)}"); cs.setString(1, df.format(start)); cs.setString(2, df.format(end)); return cs; } }, new CallableStatementCallback>>() { @Override public List> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { ResultSet rs = cs.executeQuery(); if (rs != null) { List> list = new ArrayList>(); Map temp = null; while (rs.next()) { temp = new HashMap(); temp.put("id", rs.getString("id")); temp.put("dt", rs.getString("dt")); temp.put("counts", rs.getString("counts")); list.add(temp); } temp = null; return list; } return null; } }); } @Override public List> StatisticUserLoginByHours(final Date start) { return this.jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection conn) throws SQLException { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); CallableStatement cs = conn.prepareCall("{call onLineUser_statistic_by_Hours(?)}"); cs.setString(1, df.format(start)); return cs; } }, new CallableStatementCallback>>() { @Override public List> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { ResultSet rs = cs.executeQuery(); if (rs != null) { List> list = new ArrayList>(); Map temp = null; while (rs.next()) { temp = new HashMap(); temp.put("id", rs.getString("id")); temp.put("dt", rs.getString("dt")); temp.put("counts", rs.getString("counts")); list.add(temp); } temp = null; return list; } return null; } }); } public Page> findPages(final Page> page) { return this.jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection conn) throws SQLException { String preStr = "{call " + PROC_NAME + "(?,?,?,?,?,?,?,?,?,?,?)}"; CallableStatement cs = null; cs = conn.prepareCall(preStr); cs.setString(1, page.getTableName()); cs.setString(2, page.getFieldList()); cs.setString(3, page.getPrimaryKey()); cs.setString(4, page.getWhere()); cs.setString(5, page.getOrder()); cs.setInt(6, page.getSortType()); cs.setInt(7, page.getRecorderCount()); cs.setInt(8, page.getPageSize()); cs.setInt(9, page.getPageIndex()); cs.registerOutParameter(10, Types.INTEGER); cs.registerOutParameter(11, Types.INTEGER); return cs; } }, new CallableStatementCallback>>() { @Override public Page> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); List> list = new ArrayList>(); ResultSet rs = cs.getResultSet(); Map map = null; String[] temp = page.getFieldList().split(","); if (rs != null) while (rs.next()) { map = new HashMap(); for (int i = 0; i < temp.length; i++) map.put(temp[i], rs.getObject(temp[i])); list.add(map); } cs.getMoreResults(); page.setTotalCount(cs.getInt(10)); page.setTotalPageCount(cs.getInt(11)); page.setResult(list); return page; } }); } private static final String QUIT_LOG = "set nocount on\n" + " update _sysloginlog set outtime=getdate() where sessionid = ? "; @Override public void doQuitLog(String sessionId) { this.jdbcTemplate.update(QUIT_LOG, sessionId); } // 查找用户信息 private final String fieldid = " UserCode,UserName,CompanyId,CompanyName, \n" + " CcCode,CcName,AreaId,AreaName,EnterCode,EnterName, \n" + " [Password] as Password,[Description] as Description,Memo, \n" + " Memo1, Memo2, Memo3,HrCode,HrName,[Role] as Role,FlowchartYN, \n" + " AgentActive,AgentUser,InDomain,DefaultStcode,DefaultSet, \n" + " CompanyPermission,UserType,InActive,PwdLastModified, \n" + " PwdExpired,PromptExpired,CalPwdExpired,PwdLength,PwdComplexLevel, \n" + " DefaultAcctCode,LogonType,IsEnableLoadCollection,WxDeptID,Tel, \n" + " Gender,EMail, \n" + " LangId,Currency,WxCcCode,ProfileIds, \n" + " AccessIds,OpenId,IsStopSystem , activeApp,DefaultBrand,cccodepermission,isModifyPriceWhenScanQrCode,UserCodePermission,\n" + " case when ISNULL(inActive,0) = 1 then '当前用户【'+ISNULL(UserName,'') + '】已停用,请与管理员联系!' " +" else case when isnull(CalPwdExpired,0) > 0 then '您的密码已过期【' " +" + CAST(ISNULL(CalPwdExpired,0) as varchar)+'】天' " +" else '您的密码还剩【'+CAST(0 - ISNULL(CalPwdExpired,0) as varchar)+'】天过期' end " +" +',请在【' + convert(varchar(10),dateadd(day,PwdExpired, PwdLastModified ), 120) +'】前修改' " +" end as PasswordExpiredHint,WeiXinCorpUserId,Oauth2OpenIdForWeiXin,Oauth2OpenIdForQQ, \n" + " ShopCcCode,CompanyMemo,AvatarUnid \n"; @Override public UserAccountEntity getUserInfoByUserCode(String userCode) { String sql = "set nocount on\n" + " select top 1 " + fieldid + " from _sys_LoginUser \n" + " where UserCode collate Chinese_PRC_CS_AI = ? and inActive<>1 \n"; try { Map map = this.jdbcTemplate.queryForMap(sql, userCode); return getUserAccountEntity(map); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public UserAccountEntity getUserInfoByWeiXinCorpUserId(String userid) { String sql = " set nocount on\n" + " select top 1 " + fieldid + " from _sys_LoginUser \n" + " where isnull(WeiXinCorpUserId,'') <> '' and WeiXinCorpUserId = ? and inActive<>1 \n"; try { Map map = this.jdbcTemplate.queryForMap(sql, userid); return getUserAccountEntity(map); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public UserAccountEntity getUserInfoByOauth2OpenIdForWeiXin(String openid) { String sql = " set nocount on\n" + " select top 1 " + fieldid + " from _sys_LoginUser \n" + " where isnull(Oauth2OpenIdForWeiXin,'') <> '' and Oauth2OpenIdForWeiXin = ? and inActive<>1 \n"; try { Map map = this.jdbcTemplate.queryForMap(sql, openid); return getUserAccountEntity(map); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public UserAccountEntity getUserInfoByOauth2OpenIdForQQ(String openid) { String sql = "set nocount on \n" + " select top 1 " + fieldid + " from _sys_LoginUser \n" + " where isnull(Oauth2OpenIdForQQ,'') <> '' and Oauth2OpenIdForQQ = ? and inActive<>1 \n"; try { Map map = this.jdbcTemplate.queryForMap(sql, openid); return getUserAccountEntity(map); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public UserAccountEntity getUserInfoByEmail(String email) { String sql = "set nocount on \n" + " select top 1 " + fieldid + " from _sys_LoginUser \n" + " where isnull(email,'') <> '' and email = ? and inActive<>1 \n"; try { Map map = this.jdbcTemplate.queryForMap(sql, email); return getUserAccountEntity(map); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public UserAccountEntity getUserInfoByTelephone(String telephone) { try { String sql = " set nocount on\n" + " select top 1 " + fieldid + " from _sys_LoginUser \n" + " where isnull(tel,'') <> '' and tel = ? and inActive<>1 \n"; Map map = this.jdbcTemplate.queryForMap(sql, telephone); return getUserAccountEntity(map); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public List getUserInfos(String userCodes) { String sql = " set nocount on \n " + " declare @UserCodes varchar(2000) = ? \n" + " select " + fieldid + " from _sys_LoginUser \n" + " where (isnull(@UserCodes,'') = '' or UserCode in (select list from getinstr(@UserCodes))) \n" ; //+ " where inActive<>1 \n"; try { List retList = new ArrayList() ; List> list = this.jdbcTemplate.queryForList(sql,userCodes); for (int i = 0 ; list != null && i < list.size();i++) { retList.add(getUserAccountEntity(list.get(i))) ; } return retList ; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } @Override public List getUserInfos() { String sql = " set nocount on \n" + " select " + fieldid + " from _sys_LoginUser \n" ; //+ " where inActive<>1 \n"; try { List retList = new ArrayList() ; List> list = this.jdbcTemplate.queryForList(sql); for (int i = 0 ; list != null && i < list.size();i++) { retList.add(getUserAccountEntity(list.get(i))) ; } return retList ; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } private UserAccountEntity getUserAccountEntity(Map map) { if (map == null) return null ; UserAccountEntity userAccountEntity = new UserAccountEntity() ; userAccountEntity.setUserCode(map.get("UserCode") == null?"":(String)map.get("UserCode")); userAccountEntity.setUserName(map.get("UserName") == null?"":(String)map.get("UserName")); userAccountEntity.setCompanyId(map.get("CompanyId") == null?"":(String)map.get("CompanyId")); userAccountEntity.setCompanyName(map.get("CompanyName") == null?"":(String)map.get("CompanyName")); userAccountEntity.setCompanyMemo(map.get("CompanyMemo") == null?"":(String)map.get("CompanyMemo")); userAccountEntity.setCcCode(map.get("CcCode") == null?"":(String)map.get("CcCode")); userAccountEntity.setCcName(map.get("CcName") == null?"":(String)map.get("CcName")); userAccountEntity.setAreaId(map.get("AreaId") == null?"":(String)map.get("AreaId")); userAccountEntity.setAreaName(map.get("AreaName") == null?"":(String)map.get("AreaName")); userAccountEntity.setEnterCode(map.get("EnterCode") == null?"":(String)map.get("EnterCode")); userAccountEntity.setEnterName(map.get("EnterName") == null?"":(String)map.get("EnterName")); userAccountEntity.setPassword(map.get("Password") == null?"":(String)map.get("Password")); userAccountEntity.setDescription(map.get("Description") == null?"":(String)map.get("Description")); userAccountEntity.setMemo(map.get("Memo") == null?"":(String)map.get("Memo")); userAccountEntity.setMemo1(map.get("Memo1") == null?"":(String)map.get("Memo1")); userAccountEntity.setMemo2(map.get("Memo2") == null?"":(String)map.get("Memo2")); userAccountEntity.setMemo3(map.get("Memo3") == null?"":(String)map.get("Memo3")); userAccountEntity.setHrCode(map.get("HrCode") == null?"":(String)map.get("HrCode")); userAccountEntity.setHrName(map.get("HrName") == null?"":(String)map.get("HrName")); userAccountEntity.setRole(map.get("Role") == null?"":(String)map.get("Role")); userAccountEntity.setFlowchartYN(map.get("FlowchartYN") != null&& map.get("FlowchartYN").equals(1)?true:false); userAccountEntity.setAgentActive(map.get("AgentActive") != null&& map.get("AgentActive").equals(1)?true:false); userAccountEntity.setAgentUser(map.get("AgentUser") == null?"":(String)map.get("AgentUser")); userAccountEntity.setInDomain(map.get("InDomain") == null?"":(String)map.get("InDomain")); userAccountEntity.setDefaultStcode(map.get("DefaultStcode") == null?"":(String)map.get("DefaultStcode")); userAccountEntity.setCompanyPermission(map.get("CompanyPermission") == null?"":(String)map.get("CompanyPermission")); userAccountEntity.setUserType(map.get("UserType") == null?"":(String)map.get("UserType")); userAccountEntity.setInActive(map.get("InActive") != null&&map.get("InActive").equals(1)?true:false); userAccountEntity.setPwdLastModified(map.get("PwdLastModified") == null?null:(Date)map.get("PwdLastModified")); userAccountEntity.setPwdExpired(map.get("PwdExpired") == null?0:(Integer)map.get("PwdExpired")); userAccountEntity.setPromptExpired(map.get("PromptExpired") == null?0:(Integer)map.get("PromptExpired")); userAccountEntity.setCalPwdExpired(map.get("CalPwdExpired") == null?0:(Integer)map.get("CalPwdExpired")); userAccountEntity.setPwdLength(map.get("PwdLength") == null?0:(Integer)map.get("PwdLength")); userAccountEntity.setPwdComplexLevel(map.get("PwdComplexLevel") == null?1:(Integer)map.get("PwdComplexLevel")); userAccountEntity.setDefaultAcctCode(map.get("DefaultAcctCode") == null?"":(String)map.get("DefaultAcctCode")); userAccountEntity.setDefaultBrand(map.get("Defaultbrand") == null?"":(String)map.get("Defaultbrand")); userAccountEntity.setCcCodePermission(map.get("cccodepermission") == null?"":(String)map.get("cccodepermission")); userAccountEntity.setUserCodePermission(map.get("UserCodePermission") == null?"":(String)map.get("UserCodePermission")); userAccountEntity.setIsModifyPriceWhenScanQrCode(map.get("isModifyPriceWhenScanQrCode") == null?0:(Integer)map.get("isModifyPriceWhenScanQrCode")); userAccountEntity.setLogonType(map.get("LogonType") == null?0:(Integer)map.get("LogonType")); userAccountEntity.setEnableLoadCollection(map.get("IsEnableLoadCollection") != null&&map.get("IsEnableLoadCollection").equals(1)?true:false); userAccountEntity.setWxDeptID(map.get("WxDeptID") == null?0:(Integer)map.get("WxDeptID")); userAccountEntity.setTel(map.get("Tel") == null?"":(String)map.get("Tel")); userAccountEntity.setGender(map.get("Gender") == null?0:(Integer)map.get("Gender")); userAccountEntity.setEMail(map.get("EMail") == null?"":(String)map.get("EMail")); userAccountEntity.setLangId(map.get("LangId") == null?null:(Integer)map.get("LangId")); userAccountEntity.setCurrency(map.get("Currency") == null?"":(String)map.get("Currency")); userAccountEntity.setWxCcCode(map.get("WxCcCode") == null?"":(String)map.get("WxCcCode")); userAccountEntity.setProfileIds(map.get("ProfileIds") == null?"":(String)map.get("ProfileIds")); userAccountEntity.setAccessIds(map.get("AccessIds") == null?"":(String)map.get("AccessIds")); userAccountEntity.setOpenId(map.get("OpenId") == null?"":(String)map.get("OpenId")); userAccountEntity.setStopSystem(map.get("IsStopSystem") != null&&map.get("IsStopSystem").equals(1)?true:false); userAccountEntity.setDefaultSet(map.get("DefaultSet") == null?"":(String)map.get("DefaultSet")); userAccountEntity.setPasswordExpiredHint(map.get("PasswordExpiredHint") == null?"":(String)map.get("PasswordExpiredHint")); userAccountEntity.setWeiXinCorpUserId(map.get("WeiXinCorpUserId") == null?"":(String)map.get("WeiXinCorpUserId")); userAccountEntity.setOauth2OpenIdForWeiXin(map.get("Oauth2OpenIdForWeiXin") == null?"":(String)map.get("Oauth2OpenIdForWeiXin")); userAccountEntity.setOauth2OpenIdForQQ(map.get("Oauth2OpenIdForQQ") == null?"":(String)map.get("Oauth2OpenIdForQQ")); userAccountEntity.setShopCcCode(map.get("ShopCcCode") == null?"":(String)map.get("ShopCcCode")); userAccountEntity.setActiveApp(GridUtils.prossRowSetDataType_Int(map,"activeApp")); userAccountEntity.setAvatarUnid(GridUtils.prossRowSetDataType_String(map,"AvatarUnid")); return userAccountEntity ; } //获取所有用户信息 public List> getLoginUser(int curPage,int pageSize) { try { String sql = "set nocount on \n" + " select top "+pageSize+" * from _sys_LoginUser where UserCode not in (select top "+((curPage-1)*pageSize)+" UserCode from _sys_LoginUser)"; return this.jdbcTemplate.queryForList(sql); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } //获得所有用户信息的总数 public int getUserTotal() { try { String sql = "set nocount on \n" + " select count(1) from _sys_LoginUser"; SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql); if(rs.next()){ return rs.getInt(1); } return 0; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return 0 ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } /** * 获取用户信息(带查询条件) */ public List> getLoginUser(int curPage, int pageSize, String companyname, String usercocde, String username) { try { String filter = filterSql(companyname,usercocde,username); String sql = "set nocount on \n" + " select top "+pageSize+" * from _sys_LoginUser where UserCode not in (select top "+((curPage-1)*pageSize)+" UserCode from _sys_LoginUser where "+filter+") and "+filter; return this.jdbcTemplate.queryForList(sql); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } //获得用户信息的总数(带查询条件) public int getUserTotal(String companyname, String usercocde, String username) { try { String filter=filterSql(companyname, usercocde, username); String sql = "set nocount on\n" + " select count(Companyid) from _sys_LoginUser where "+filter; SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql); if(rs.next()){ return rs.getInt(1); } return 0; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return 0 ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } } public String filterSql(String companyname, String usercocde, String username){ String filter=""; if(!companyname.equals("") && !usercocde.equals("") && !username.equals("")){ filter=" companyname like '%"+companyname+"%' and UserCode like '%"+usercocde+"%' and UserName like '%"+username+"%'"; }else if(!companyname.equals("") && !usercocde.equals("")){ filter=" companyname like '%"+companyname+"%' and UserCode like '%"+usercocde+"%'"; }else if(!usercocde.equals("") && !username.equals("")){ filter=" UserCode like '%"+usercocde+"%' and UserName like '%"+username+"%'"; }else if(!companyname.equals("") && !username.equals("")){ filter=" companyname like '%"+companyname+"%' and UserName like '%"+username+"%'"; }else if(!companyname.equals("") && usercocde.equals("") && username.equals("")){ filter=" companyname like '%"+companyname+"%'"; }else if(companyname.equals("") && !usercocde.equals("") && username.equals("")){ filter=" UserCode like '%"+usercocde+"%'"; }else if(companyname.equals("") && usercocde.equals("") && !username.equals("")){ filter=" UserName like '%"+username+"%'"; }else{ filter="0=0"; } return filter; } @Override public void doCheckHasExpired() { try { this.simpleJdbcCallShopping .withProcedureName("oa_ChangeUserCodePasswordExpired") .execute(); }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } return ; } @Override public boolean savePassword(String userCode, String password,String enterCode) { String sql = "set nocount on \n" + " declare @UserCode varchar(50), @UserName varchar(50),@Password varchar(max) ;\n" + " declare @EnterCode varchar(50),@EnterName varchar(50),@LogonType int\n" + " declare @EnterDate datetime = getdate() ,@Result varchar(500) \n" + " declare @MyRowCount int,@MyError int,@Formid int ; \n" + " select @UserCode = ?,@Password = ? ,@EnterCode = ? ; \n" + " select @Formid = 301114 ; \n" + " select @UserName = UserName,@LogonType = LogonType from _sys_LoginUser where usercode = @UserCode ;\n" + " select @MyRowCount = @@rowcount,@MyError = @@error ; \n" + " if isnull(@MyRowCount,0) = 0 \n" + " begin \n" + " raiserror('用户不存在或已被禁用',16,1) ;\n" + " return \n" + " end \n" + " select @EnterName = UserName from _sys_LoginUser where usercode = @EnterCode ;\n" + " select @MyRowCount = 0,@MyError = 0 ; \n" + " if isnull(@LogonType,0) = 0 \n" + " begin \n" + " update a set [password] = @Password , PwdLastModified = getdate() \n" + " from _sysuser a where usercode = @UserCode ; \n" + " select @MyRowCount = @@rowcount,@MyError = @@error ; \n" + " end \n" + " if isnull(@LogonType,0) = 1 \n" + " begin \n" + " update a set [password] = @Password , PwdLastModified = getdate() \n" + " from t110203 a where cltcode = @UserCode ; \n" + " select @MyRowCount = @@rowcount,@MyError = @@error ; \n" + " end \n" + " if isnull(@LogonType,0) = 2 \n" + " begin \n" + " update a set [password] = @Password , PwdLastModified = getdate() \n" + " from t180201 a where hrcode = @UserCode ; \n" + " select @MyRowCount = @@rowcount,@MyError = @@error ; \n" + " end \n" + " if isnull(@LogonType,0) = 3 \n" + " begin \n" + " update a set [password] = @Password , PwdLastModified = getdate() \n" + " from t110302 a where vndcode = @UserCode ; \n" + " select @MyRowCount = @@rowcount,@MyError = @@error ; \n" + " end \n" + " if isnull(@MyRowCount,0) > 0 \n" + " begin \n" + " insert into _sysUserPwdHistory(UserCode,UserName,PwdLastModified,[Password],EnterCode,EnterName) \n" + " values(@UserCode,@UserName,getdate(),@Password,@EnterCode,@EnterName) ; \n" + " end \n" + " if isnull(@MyRowCount,0) > 0 \n" + " begin \n" + " exec p219001 @UserCodes = @UserCode , @UserNames = @UserName ,@Formid = @Formid , \n" + " @FormType = 22,@Doccode = @UserCode ,@CurFormid = @Formid ,@SenderCode=@UserCode,@SenderName=@UserName, \n" + " @ActiveType = 6 ,@isSendWxCorpMsgs = 0,@CorpAgentId = 0,@MsgFrom = 1,@EnterCode=@EnterCode,\n" + " @EnterName=@EnterName,@EnterDate=@EnterDate,@PostCode=@EnterCode,@PostName=@EnterName,\n" + " @PostDate=@EnterDate,@ButtonType='提交',@ActionType='通知',@Result=@Result output\n" + " end \n" + " select @MyRowCount ; \n"; Integer ret = null ; try { ret = this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] { userCode,password,enterCode}) ; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return false ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } return (ret != null && ret.equals(1)?true:false ); } @Override public String getTranslateVariable(String templateMsg,int formId,String docCode, String userCode,String userName,String whereFieldId,String whereOthers) { String sql = " set nocount on \n" + " declare @TemplateMsg nvarchar(max) = ?,@FormId int = ?,@DocCode nvarchar(50) = ? \n" + " declare @UserCode nvarchar(50) = ? ,@UserName nvarchar(100) = ? \n" + " declare @WhereFieldId nvarchar(100) = ?,@WhereOthers nvarchar(2000) = ? \n" + " exec p111637 @TemplateMsg = @TemplateMsg output ,@FormId = @FormId, \n" + " @DocCode = @DocCode,@UserCode = @UserCode, \n" + " @UserName = @UserName ,@WhereFieldId = @WhereFieldId,@WhereOthers = @WhereOthers \n" + " select isnull(@TemplateMsg,'') as TemplateMsg \n" ; String ret = null ; try { ret = this.jdbcTemplate.queryForObject(sql, String.class,new Object[] { templateMsg, formId, docCode,userCode, userName, whereFieldId, whereOthers}) ; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return null ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } return ret ; } @Override public boolean checkPasswordComplexity(String passwordPlainText) { String sql = " set nocount on \n" + " select top 1 1 from _sysuserPwdEx where defaultpwd = ? \n" ; Integer ret = null ; try { ret = this.jdbcTemplate.queryForObject(sql, Integer.class,new Object[] { passwordPlainText}) ; }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return false ; }else { e.printStackTrace(); throw e; } }catch(Exception e){ e.printStackTrace(); throw e; } return (ret != null && ret.equals(1)?true:false ); } @Override public boolean checkPasswordHistory(String userCode,String passwordPlainText) throws Exception { String sql = "set nocount on \n" + " select [password] as password from _sysUserPwdHistory \n" + " where UserCode = ? \n" + " and PwdLastModified between DATEADD(year,-1,getdate() ) \n" + " and getdate() and isnull(password,'') <> '' \n" ; try { List> list = this.jdbcTemplate.queryForList(sql,new Object[] { userCode}) ; for (int i = 0 ;list != null && i < list.size();i ++) { String orgPassword = (String)list.get(i).get("password"); if (orgPassword != null) { if ( orgPassword.length()<30 ) { //该用户原密码未加密 if (passwordPlainText.equals(orgPassword)) { return true ; }else { continue ; } } String passwordHis = ChangePassword.getDecryptPassword( orgPassword); if (passwordPlainText.equals(passwordHis)) { return true ; } } } }catch(DataAccessException e ) { if (e instanceof EmptyResultDataAccessException){ return false ; }else { //e.printStackTrace(); throw e; } }catch(Exception e){ //e.printStackTrace(); throw e; } return false; } /** * 检查限制时间和IP，决定 是否可以登录 * */ @Override public int checkUserLoginTimeAndIp(String usercode, String ip) throws DataAccessException { BaseService base = (BaseService) FactoryBean.getBean("BaseService"); String sql="set nocount on; \n" + " declare @UserCode varchar(50) =?,@Now datetime = getdate(),@IP varchar(50) =?\n" + " declare @Today datetime = convert(datetime,convert(varchar(10),@Now,120))\n" + " declare @WeekDay int = datepart(weekday,@Today) \n" + " if not exists(select top 1 1 from _sys_LimitLogin where usercode = @UserCode and isnull(isActived,0) = 1 ) \n" + " begin select 1 ; return ; end \n" + " if exists(\n" + " select top 1 1 \n" + " from _sys_LimitLogin a \n" + " where a.usercode=@UserCode \n" + " and (a.EnterDate is null or a.EnterDate = @Today)\n" + " and (a.WeekDay is null or a.WeekDay = isnull(@WeekDay,0) - 1 )\n" + " and (isnull(a.isExStatutoryHolidays,0) = 0 \n" + " or (isnull(a.isExStatutoryHolidays,0) = 1 \n" + " and not exists(select 1 from _sys_HolidaysDate where HolidaysDate = @Today)))\n" + " and (isnull(a.StartTime,'') = '' and isnull(a.EndTime,'') = '' \n" + " or @Now between convert(datetime,@Today+' ' + replace(isnull(a.StartTime,''),' ','')) \n" + " and convert(datetime,@Today + ' ' + replace(isnull( a.EndTime,''),' ','')))\n" + " and (isnull(a.StartIP,'') = '' and isnull(a.EndIP,'') = '' \n" + " or isnull(@IP,'') between replace(replace(isnull(a.StartIP,''),' ',''),'.00','.') \n" + " and replace(replace(isnull(a.EndIP,''),' ',''),'.00','.'))\n" + " and isnull(a.isActived,0) =1 )" + " begin \n" + " select 1 ; return ; \n" + " end \n" + " else \n" + " begin \n" + " select 0 ; return ; \n" + " end "; return base.getSimpleJdbcTemplate().queryForObject(sql,Integer.class,usercode,ip); // Calendar ca = Calendar.getInstance(); // ca.setTime(new Date()); // int week = ca.get(Calendar.DAY_OF_WEEK) - 1;// 当前星期几 // //int year = ca.get(Calendar.YEAR);// 当前年份 // String today = DateUtil.toDayDate();// 当前日期 // // 第一步先查找自定义日期 // // List> lm = base.getSimpleJdbcTemplate().queryForList("set nocount on; select * from _sys_LimitLogin where usercode=? and EnterDate is not null and isActived=1", usercode); // if (lm.size() > 0) {// 比较自定义日期 // boolean dateFlag = false; // for (Map m : lm) { // // 比较那一个是当前时间 // // String d1=(Date)m.get("EnterDate"); // // SimpleDateFormat myFmt2=new SimpleDateFormat("yyyy-MM-dd"); // Date da1 = (Date) m.get("EnterDate"); // // if (DateUtil.daysBetween(da1, new Date()) == 0) { return prossIPAndTime(ip, base, today, m); } // } // if (!dateFlag) return 3; // // 其他的都不能通过登录 // } else {// 比较星期,取出用户的星期设置 // List> list = base.getSimpleJdbcTemplate().queryForList("set nocount on; select * from _sys_LimitLogin where usercode=? and (EnterDate is null or EnterDate='') and isActived=1", usercode); // if (list.size() > 0) {// 存在设置，否则就是还没对用户进行设置，应该是全都可以访问 // boolean flg = false; // for (Map m : list) { // if (m.get("WeekDay")!=null&&!"".equalsIgnoreCase(m.get("WeekDay")+"")&&((Integer) m.get("WeekDay")) == week) {// 是当前设置的星期 // flg = true; // return prossIPAndTime(ip, base, today, m); // }else {//没设置星期，则检查ip // return prossIPAndTime(ip, base, today, m); // } // } // if (!flg) {// 表示不在当前设置内，限制登录 // return 3; // } // } // // } //return 0; } @Override public int prossIPAndTime(String ip, BaseService base, String today, Map m) { // 读取节假日表是否存在当前日期记录，再判断是不是当前的星期 if (m.get("isExStatutoryHolidays") != null && ((Integer) m.get("isExStatutoryHolidays")) == 1) {// 排除节假日时间 int holid = base.getSimpleJdbcTemplate().queryForObject("set nocount on; select count(*) from _sys_HolidaysDate where HolidaysDate=?", Integer.class, new Object[] { today }); if (holid != 0) {// 表示当前是节假日 return 3;// 与节假日和星期相同，所有不能登录 } } // 不是同一个星期，则继续处理时间和ip//取时间和ip与当前时间和ip相对比 boolean ipFlag = false; boolean timeFlag = false; // IP String sIP = (String) m.get("StartIP"); String eIP = (String) m.get("EndIP"); if (sIP == null || "*".equalsIgnoreCase(sIP)) { return 0; } else {// 有IP段设置 if (IPUtil.ipCheck(sIP) && IPUtil.ipCheck(eIP)) { int start = Integer.parseInt(sIP.split("\\.")[3]); int end = Integer.parseInt(eIP.split("\\.")[3]); int cur = Integer.parseInt(ip.split("\\.")[3]); if (cur >= start && cur <= end) {// 在这个范围之内 ipFlag = true; } else { return 1; } } } // 时间 String sTime = (String) m.get("StartTime"); String eTime = (String) m.get("EndTime"); if (sTime == null || "".equalsIgnoreCase(sTime)) {// 没设置时间 return 0; } else { Calendar ca2 = Calendar.getInstance();// start Calendar ca3 = Calendar.getInstance();// end Calendar ca4 = Calendar.getInstance();// cur ca2.set(Calendar.HOUR_OF_DAY, Integer.parseInt(sTime.split(":")[0])); ca2.set(Calendar.MINUTE, Integer.parseInt(sTime.split(":")[1])); ca3.set(Calendar.HOUR_OF_DAY, Integer.parseInt(eTime.split(":")[0])); ca3.set(Calendar.MINUTE, Integer.parseInt(eTime.split(":")[1])); if (ca4.compareTo(ca2) >= 0 && ca4.compareTo(ca3) <= 0) {// 在充许时间内 timeFlag = true; } else { return 2; } } if (ipFlag && timeFlag) return 0; return 0; } }