package com.yc.api.service;
|
|
import com.yc.api.bean.SerialNumberEntity;
|
import com.yc.api.bean.T110503Entity;
|
import com.yc.service.BaseService;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
public class ApiService extends BaseService implements ApiServiceIfc {
|
|
@Override
|
@Transactional
|
public int uploadPortrait(String usercode, String unid) {
|
String sql = " set nocount on \n"
|
+ " declare @UserCode varchar(50),@LogonType int,@unid varchar(200);\n"
|
+ " declare @MyRowCount int,@MyError int ; \n"
|
+ " select @UserCode = ?,@unid= ? \n"
|
+ " select @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"
|
+ " if isnull(@LogonType,0) = 0 \n"
|
+ " begin \n"
|
+ " update a set [AvatarUnid] = @unid \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 [AvatarUnid] = @unid \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 [AvatarUnid] = @unid \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 [AvatarUnid] = @unid \n"
|
+ " from t110302 a where vndcode = @UserCode ; \n"
|
+ " select @MyRowCount = @@rowcount,@MyError = @@error ; \n"
|
+ " end \n"
|
+ " select @MyRowCount ; \n";
|
return this.getSimpleJdbcTemplate().queryForObject(sql, Integer.class, usercode, unid);
|
}
|
|
@Override
|
public List<T110503Entity> showSerialNumberList(SerialNumberEntity entity) {
|
String sql = " set nocount on \n" +
|
" declare @formid int=?,@formType int=?,@tableName varchar(20)\n" +
|
" if @formType=1 or @formType=496 \n" +
|
" begin\n" +
|
" select @tableName=hdtable from gform where formid=@formid\n" +
|
" end\n" +
|
" else\n" +
|
" begin\n" +
|
" select @tableName=dttable from gform where formid=@formid\n" +
|
" end \n" +
|
" select @tableName as tableNmae";
|
String tableName = this.jdbcTemplate.queryForObject(sql, String.class, entity.getFormid(), entity.getFormType());
|
sql = "set nocount on \n" +
|
" select a.matCode,a.matName,a.rowid,a.special,a.uom ,a.digit,a.serialNumbers from " + tableName + " a join t110503 b on a.MatCode=b.MatCode where a.docCode=? and isnull(b.isStartupSerialNumber,0)=1 order by a.docitem asc ";
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T110503Entity.class), entity.getDocCode());
|
}
|
|
@Override
|
public Map getMatCodeBySerialNumberOut(SerialNumberEntity entity) {
|
String sql = " set nocount on \n" +
|
" declare @formid int=?,@formType int=?,@tableName varchar(20)\n" +
|
" if @formType=1 or @formType=496 \n" +
|
" begin\n" +
|
" select @tableName=hdtable from gform where formid=@formid\n" +
|
" end\n" +
|
" else\n" +
|
" begin\n" +
|
" select @tableName=dttable from gform where formid=@formid\n" +
|
" end \n" +
|
" select @tableName as tableNmae";
|
String tableName = this.jdbcTemplate.queryForObject(sql, String.class, entity.getFormid(), entity.getFormType());
|
|
sql = " set nocount on \n" +
|
" declare @docCode varchar(50)=?,@SerialNumber varchar(50)=?,@count int \n" +
|
" select @count=count(1) from " + tableName + " a\n" +
|
" join t140906 b on a.MatCode=b.MatCode\n" +
|
" where a.docCode=@docCode and b.SerialNumber=@SerialNumber \n" +
|
" if @count=0\n" +
|
" begin\n" +
|
" select a.matCode,a.matName,a.special,a.BaseUOM as uom,b.serialNumber ,-1 as state from t110503 a \n" +
|
" join t140906 b on a.MatCode=b.MatCode\n" +
|
" where b.SerialNumber=@SerialNumber\n" +
|
" end\n" +
|
" else\n" +
|
" begin\n" +
|
" select top 1 b.serialNumber,0 as state,a.rowid,a.matCode,b.Status from " + tableName + " a\n" +
|
" join t140906 b on a.MatCode=b.MatCode\n" +
|
" where a.docCode=@docCode and b.SerialNumber=@SerialNumber\n" +
|
" end";
|
return this.jdbcTemplate.queryForMap(sql, entity.getDocCode(), entity.getSerialNumber());
|
}
|
|
@Override
|
public Map getMatCodeBySerialNumberIn(SerialNumberEntity entity) {
|
|
String sql = " set nocount on \n" +
|
" declare @SerialNumber varchar(50)=?,@matCode varchar(50)=?,@Status int = 0,@state int = 0 \n" +
|
" select @Status = Status from t140906 a where a.MatCode = @MatCode and a.SerialNumber = @SerialNumber \n" +
|
" if @@rowcount = 0 or isnull(@Status,0) = 2 \n" +
|
" begin \n" +
|
" select @state = 0 \n" +
|
" end else \n" +
|
" begin \n" +
|
" select @state = -1 \n" +
|
" end \n" +
|
" select @SerialNumber as SerialNumber ,@state as state ,@Matcode as MatCode \n";
|
|
return this.jdbcTemplate.queryForMap(sql, entity.getSerialNumber(), entity.getMatCode());
|
}
|
|
@Override
|
public void clearCookie(String userCode, Integer formid, String cookieType) {
|
String sql = " set nocount on \n" +
|
" declare @formid int=?,@userCode varchar(50)=?,@cookieType varchar(10)=? \n" +
|
" delete from _sys_gridCookies where formid=@formid\n";
|
if (userCode != null) {
|
sql += " and userCode=@userCode\n";
|
}
|
if (cookieType != null) {
|
sql += " and cookieType=@cookieType";
|
}
|
this.jdbcTemplate.update(sql, formid, userCode, cookieType);
|
}
|
|
@Override
|
public String doUpdateUserPwd(String pwd, String tel) {
|
String sql = "set nocount on ; declare @password varchar(max)=?,@telePhone varchar(20)=? \n" +
|
" update gProfile set password=@password where Telephone=@telePhone \n " +
|
" if @@rowcount>0 \n" +
|
" begin \n" +
|
" SELECT stuff((SELECT',' + CONVERT(VARCHAR, b.id) from gProfileInvitationCode a \n" +
|
"left join gsystem b on a.InvitationCode=b.InvitationCode \n" +
|
"left join gProfile c on c.UserId=a.UserId\n" +
|
" where c.Telephone=@telePhone FOR XML PATH ('')),1,1,'')" +
|
"end else \n" +
|
" select null";
|
return this.getSimpleJdbcTemplate().queryForObject(sql, String.class, pwd, tel);
|
}
|
}
|
