package com.yc.userCenter.service;
|
|
import com.yc.service.BaseService;
|
import com.yc.userCenter.entity.JurisdictionEntiy;
|
import com.yc.userCenter.entity.UserEntity;
|
import org.apache.xmlbeans.impl.xb.xsdschema.Public;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.jdbc.support.rowset.SqlRowSet;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service("UserCenterImpl")
|
public class UserCenterImpl extends BaseService implements UserCenterIfc {
|
|
@Override
|
public List<Map<String, Object>> getUserCenter(String value,int page,int limit,String allow,String appAllow,String appList) throws Exception {
|
try {
|
String sql = "set nocount on\n" +
|
" declare @tel varchar(max) = '" + appList + "' \n"
|
+ "select top " + limit + " COUNT(1) OVER() as total,a.avatarunid,a.usercode,a.username,"
|
+ "(case a.gender when '1' then '男' when '2' then '女' else '' end) gender,b.ccname,"
|
+ "(case a.usertype when 'NormalUser' then '普通用户' when 'SuperUser' then '超级用户' else '' end) usertype,"
|
+ "a.tel,a.pwdexpired,a.inactive,a.logontype from _sys_LoginUser a left join t110601 b on a.cccode = b.ccCode"
|
+ " where ( a.usercode like '%" + value + "%' or a.username like '%" + value + "%' or b.ccname like '%" + value + "%' "
|
+ "or a.tel like '%" + value + "%') and a.UserCode not in "
|
+ "(select top ((" + page + "-1)*" + limit + ") aa.UserCode from _sys_LoginUser aa order by aa.UserCode desc) "
|
+ "and (a.LogonType in (" + allow + ")) \n";
|
if (!"1".equals(appAllow)) {//查询是否注册app
|
if ("2".equals(appAllow)) {//已注册
|
sql += " and a.tel in (select list from GetInStr(@tel))";
|
} else if ("3".equals(appAllow)) {//未注册
|
sql += " and a.tel not in (select list from GetInStr(@tel))";
|
} else {
|
throw new Exception("为找到符合条件的信息");
|
}
|
}
|
sql += " order by a.UserCode desc";
|
return this.jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public Map<String, Object> getSysUser(String usercode) throws Exception {
|
try {
|
String sql="select a.avatarunid,a.usercode,a.username,(case a.gender when '1' then '男' when '2' then '女' else '' end) gender,"
|
+ "b.ccname,(case a.usertype when 'NormalUser' then '普通用户' when 'SuperUser' then '超级用户' else '' end) usertype,"
|
+ "a.tel,a.email,a.pwdexpired,a.cccode,a.memo3,a.PwdLength,"
|
+ "(case a.PwdComplexLevel when '1' then '随便' when '2' then '数字[0..9]' when '3' then '数字+字母混合' else '' end) PwdComplexLevel,"
|
+ "a.PromptExpired,(case a.LangId when '1' then '中文' when '2' then 'English' else '' end) LangId,"
|
+ "(case a.Currency when '1' then '[RMB]人民币' when '2' then '[USD]美元' else '' end) Currency,a.OpenId,a.inactive,a.userName as NickName,"
|
+ "a.WeiXinCorpUserId,a.WeiXinCorpUserName,a.oauth2openidforweixin,a.oauth2openidforqq,"
|
+ "a.openid,d.nickname as qqname,d.AvatarUnid as qqunid,c.nickname as wxname,c.AvatarUnid as wxunid,"
|
+ "a.accessids,a.profileids,a.defaultacctcode,a.defaultbrand,a.defaultstcode,a.isstopsystem,a.cccodepermission,a.ismodifypricewhenscanqrcode,"
|
+ "a.isreceivemaintenancefeenotification,a.ismodifypricewhensalesorder,a.ismodifypricewhenpurchaseorder,a.switchToPageWhenLogonApp "
|
+ "from _sys_LoginUser a left join t110601 b on a.cccode = b.ccCode "
|
+ "left join t9666 d on a.oauth2openidforqq = d.OpenId "
|
+ "left join t9665 c on a.oauth2openidforweixin = c.OpenId "
|
+ "where a.usercode='"+usercode+"'";
|
return this.jdbcTemplate.queryForMap(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public Map<String, Object> getUpdateUserPwd(String newpwd, String usercode,Integer logontype) throws Exception {
|
try {
|
String sql="declare @UserCode varchar(20)=?,@newPwd varchar(max)=?, @row int \n"
|
+ "select @UserCode = usercode from _sysUser where UserCode = @UserCode \n"
|
+ "select @row=@@ROWCOUNT \n if @row > 0 begin \n"
|
+ "update _sysUser set PassWord = @newPwd where UserCode = @UserCode\n"
|
+ "select @row=@@ROWCOUNT \n if @row >0 \n"
|
+ " select '密码修改成功' as msg \n else \n select '密码修改失败' as msg \n"
|
+ "end \n else \n begin \n select '未能找到对应账户信息' as msg \n end";
|
|
String sql1="declare @UserCode varchar(20)=?,@newPwd varchar(max)=?, @row int \n"
|
+ "select @UserCode = cltcode from t110203 where cltcode = @UserCode \n"
|
+ "select @row=@@ROWCOUNT \n if @row > 0 begin \n"
|
+ "update t110203 set PassWord = @newPwd where cltcode = @UserCode\n"
|
+ "select @row=@@ROWCOUNT \n if @row >0 \n"
|
+ " select '密码修改成功' as msg \n else \n select '密码修改失败' as msg \n"
|
+ "end \n else \n begin \n select '未能找到对应账户信息' as msg \n end";
|
|
String sql2="declare @UserCode varchar(20)=?,@newPwd varchar(max)=?, @row int \n"
|
+ "select @UserCode = EnterCode from t180201 where EnterCode = @UserCode \n"
|
+ "select @row=@@ROWCOUNT \n if @row > 0 begin \n"
|
+ "update t180201 set PassWord = @newPwd where EnterCode = @UserCode\n"
|
+ "select @row=@@ROWCOUNT \n if @row >0 \n"
|
+ " select '密码修改成功' as msg \n else \n select '密码修改失败' as msg \n"
|
+ "end \n else \n begin \n select '未能找到对应账户信息' as msg \n end";
|
|
String sql3="declare @UserCode varchar(20)=?,@newPwd varchar(max)=?, @row int \n"
|
+ "select @UserCode = vndCode from t110302 where vndCode = @UserCode \n"
|
+ "select @row=@@ROWCOUNT \n if @row > 0 begin \n"
|
+ "update t110302 set PassWord = @newPwd where vndCode = @UserCode\n"
|
+ "select @row=@@ROWCOUNT \n if @row >0 \n"
|
+ " select '密码修改成功' as msg \n else \n select '密码修改失败' as msg \n"
|
+ "end \n else \n begin \n select '未能找到对应账户信息' as msg \n end";
|
if (logontype == 0) {
|
return this.jdbcTemplate.queryForMap(sql,new Object[] {usercode,newpwd});
|
} else if (logontype == 1) {
|
return this.jdbcTemplate.queryForMap(sql1,new Object[] {usercode,newpwd});
|
} else if (logontype == 2) {
|
return this.jdbcTemplate.queryForMap(sql2,new Object[] {usercode,newpwd});
|
} else if (logontype == 3) {
|
return this.jdbcTemplate.queryForMap(sql3,new Object[] {usercode,newpwd});
|
} else {
|
return null;
|
}
|
// return this.jdbcTemplate.queryForMap(sql,new Object[] {usercode,newpwd});
|
} catch (Exception e) {
|
throw e;// TODO: handle exception
|
}
|
}
|
|
@Override
|
public int getUserCenterInfo(String value, String usercode,Integer logontype) throws Exception {
|
try {
|
String sql = "set nocount on;\n update _sysUser set " + value + " where usercode='" + usercode
|
+ "' \n select @@rowcount \n";
|
String sql1 = "set nocount on;\n update t110203 set " + value + " where CltCode='" + usercode
|
+ "' \n select @@rowcount \n";
|
String sql2 = "set nocount on;\n update t180201 set " + value + " where EnterCode='" + usercode
|
+ "' \n select @@rowcount \n";
|
String sql3 = "set nocount on;\n update t110302 set " + value + " where vndCode='" + usercode
|
+ "' \n select @@rowcount \n";
|
if (logontype == 0) {
|
return this.jdbcTemplate.queryForObject(sql, Integer.class);
|
} else if (logontype == 1) {
|
return this.jdbcTemplate.queryForObject(sql1, Integer.class);
|
} else if (logontype == 2) {
|
return this.jdbcTemplate.queryForObject(sql2, Integer.class);
|
} else if (logontype == 3) {
|
return this.jdbcTemplate.queryForObject(sql3, Integer.class);
|
} else {
|
return 0;
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
|
}
|
|
@Override
|
public Map<String, Object> getselect2SQL(String codeId,String userCode) throws Exception {
|
try {
|
// String sql= "select sqlscript from gField where fieldid in('defaultStcode','defaultAcctcode','ProfileIds','AccessIds','defaultBrand','cccode','CcCodePermission') and formid=9901";
|
String sql= "select g.sqlscript,(select a."+codeId+" from _sysUser a where a.UserCode='"+userCode+"') as val from gField g where g.fieldid ='"+codeId+"' and g.formid=9901";
|
return this.jdbcTemplate.queryForMap(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
|
}
|
|
@Override
|
public List<Map<String, Object>> getMessage(String key,String search) throws Exception {
|
try {
|
String type="'notice','success','warning','error'";
|
if("".equals(search) || !"0".equals(key)) {
|
switch (key) {
|
case "1":
|
type="'notice'";
|
break;
|
case "2":
|
type="'success'";
|
break;
|
case "3":
|
type="'warning'";
|
break;
|
case "4":
|
type="'error'";
|
break;
|
}
|
}
|
String sql =" set nocount on \n"
|
+ " select a.messid,a.messagetxt,a.unvaliddate,a.createuser,a.createtime,\n"
|
+ "a.topic ,(case a.messagetype when 'notice' then '通知消息' when 'success' then "
|
+ "'成功消息' when 'warning' then '警告消息' when 'error' then '错误消息' else '通知消息' end) as type "
|
+ "from _sysmessage a where a.messagetype in ("+type+") and a.topic like '%"+search+"%'\n"
|
+ " order by a.createtime desc \n";
|
return this.jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public int delMessage(String id) throws Exception {
|
try {
|
String sql=" set nocount on; \n"
|
+ "delete _sysMessage where messid in ("+id+") \n"
|
+ "select @@rowcount \n";
|
return this.jdbcTemplate.queryForObject(sql,Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
/**
|
* 删除维护登录用户
|
*/
|
@Override
|
public int delUser(String usercode,Integer logontype) throws Exception {
|
try {
|
String sql="set nocount on;\n delete _sysUser where usercode='"+usercode+"' \n select @@rowcount \n";
|
String sql1="set nocount on;\n delete t110203 where CltCode='"+usercode+"' \n select @@rowcount \n";
|
String sql2="set nocount on;\n delete t180201 where EnterCode='"+usercode+"' \n select @@rowcount \n";
|
String sql3="set nocount on;\n delete t110302 where vndCode='"+usercode+"' \n select @@rowcount \n";
|
if (logontype == 0) {
|
return this.jdbcTemplate.queryForObject(sql, Integer.class);
|
} else if (logontype == 1) {
|
return this.jdbcTemplate.queryForObject(sql1, Integer.class);
|
} else if (logontype == 2) {
|
return this.jdbcTemplate.queryForObject(sql2, Integer.class);
|
} else if (logontype == 3) {
|
return this.jdbcTemplate.queryForObject(sql3, Integer.class);
|
} else {
|
return 0;
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
//sql查询
|
@Override
|
public SqlRowSet getSelect(String sql) throws Exception {
|
try {
|
return this.jdbcTemplate.queryForRowSet(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
//添加用户
|
@Override
|
public int addUser(UserEntity user) throws Exception {
|
try {
|
String sql="insert into _sysUser(UserCode,UserName,passWord,cccode,ccname,Companyid,companyname,UserType) values(?,?,?,?,?,?,?,?)\n select @@rowcount \n";
|
return jdbcTemplate.queryForObject(sql, new Object[] {user.getUrserCode(),user.getUserName(),user.getPassWord(),
|
user.getCcCode(),user.getCcName(),user.getCompanyId(),user.getCompanyName(),user.getUserType()},Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public Map<String, Object> selectApp(String phone, String dbId) throws Exception {
|
try {
|
String sql="select userid,username,invitationcode,organizationname,departmentname,"
|
+ "status,isdebug from gProfileInvitationCode "
|
+ "where UserId in (select UserId from gProfile where Telephone='"+phone+"') and "
|
+ "invitationcode in (select invitationcode from gsystem where id = "+dbId+")";
|
return super.jdbcTemplate.queryForMap(sql);
|
} catch (Exception e) {
|
return null;
|
}
|
}
|
|
@Override
|
public List<String> selectApps(String dbId) {
|
try {
|
String sql = " select a.Telephone from gProfile a join gProfileInvitationCode b on a.UserId=b.UserId \n" +
|
" where b.invitationcode in (select invitationcode from gsystem where id = "+dbId+")";
|
return super.jdbcTemplate.query(sql, new RowMapper<String>() {
|
@Override
|
public String mapRow(ResultSet resultSet, int i) throws SQLException {
|
return resultSet.getString(1);
|
}
|
},null);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public int updateApp(String value,String userId, String dbId) throws Exception {
|
try {
|
String sql="set nocount on \n declare @UserId int = ?, @id int = ? \n"
|
+ " update gProfileInvitationCode set "+value
|
+ " where UserId=@UserId and invitationcode in (select invitationcode from gsystem where id = @id) \n select @@rowcount";
|
return super.jdbcTemplate.queryForObject(sql, new Object[] {userId,dbId},Integer.class);
|
} catch (Exception e) {
|
return 0;
|
}
|
}
|
|
@Override
|
public int deleteApp(String userId, String dbId) throws Exception {
|
try {
|
String sql="set nocount on\n delete gProfileInvitationCode where UserId=? and invitationcode in (select invitationcode from gsystem where id = ?)\n select @@rowcount";
|
return super.jdbcTemplate.queryForObject(sql, new Object[] {userId,dbId},Integer.class);
|
} catch (Exception e) {
|
return 0;
|
}
|
}
|
|
@Override
|
public int updateSetTing(JurisdictionEntiy j) throws Exception {
|
try {
|
String sql="set nocount on;\n update _sysUser set companyId=?,Cccode=?,DefaultBrand=?,ProfileIds=?,AccessIds=?"
|
+ ",DefaultAcctcode=?,DefaultStcode=?,CompanyPermission=?,CcCodePermission=?,UserCodePermission=?,isModifyPriceWhenScanQrCode=?,IsStopSystem=?," +
|
"isModifyPriceWhenSalesOrder=?,isModifyPriceWhenPurchaseOrder=?,isReceiveMaintenanceFeeNotification=?" +
|
" where usercode=? \n select @@rowcount \n";
|
return super.jdbcTemplate.queryForObject(sql, new Object[] {j.getCompanyId(),j.getCccode(),j.getDefaultBrand(),j.getProfileIds(),j.getAccessIds(),
|
j.getDefaultAcctcode(),j.getDefaultStcode(),j.getCompanyPermission(),j.getCcCodePermission(),j.getUserCodePermission(),
|
j.getIsModifyPriceWhenScanQrCode(),j.getIsStopSystem(),j.getIsModifyPriceWhenSalesOrder(),j.getIsModifyPriceWhenPurchaseOrder(),
|
j.getIsReceiveMaintenanceFeeNotification(),j.getUserCode()},Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public int updateTelephone(String value, String userCode, String userId) throws Exception {
|
try {
|
String sql = "declare @tel nvarchar\r\n";
|
if (!userCode.equals("") && userId.equals("")) {
|
sql += "select @tel=Telephone from t9668h where UserCode='" + userCode + "'\r\n"
|
+ "if ISNULL(@tel,'') !='' and @tel !=''\r\n" + "begin\r\n" + "update t9668h set Telephone='"
|
+ value + "' where UserCode='" + userCode + "'\r\n" + "end\r\n";
|
} else if (userCode.equals("") && !userId.equals("")) {
|
sql += "select @tel=Telephone from gProfile where UserId=" + userId + "\r\n"
|
+ "if ISNULL(@tel,'') !='' and @tel !=''\r\n" + "begin\r\n" + "update gProfile set Telephone='"
|
+ value + "' where UserId=" + userId + "\r\n" + "end\r\n";
|
}
|
sql += "select @@ROWCOUNT";
|
return super.jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
return 0;
|
}
|
}
|
|
@Override
|
public Map<String, Object> getIsAllowLogin() throws Exception {
|
try {
|
String sql="select top 1 isAllowCustomerLogin,isAllowStaffLogin,isAllowVendorLogin from v111613";
|
return super.jdbcTemplate.queryForMap(sql);
|
} catch (Exception e) {
|
return null;
|
}
|
}
|
|
}
|
