package com.yc.MaintenanceFee.service.impl;
|
|
import com.yc.MaintenanceFee.entiy.PayInfo;
|
import com.yc.MaintenanceFee.service.MaintainService;
|
import com.yc.MaintenanceFee.service.PayInfoService;
|
import com.yc.service.BaseService;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @USER: xinyb_
|
* @DATE: 2021-11-22 17:56
|
*/
|
@Service
|
public class MaintainServiceImpl extends BaseService implements MaintainService {
|
|
@Autowired
|
PayInfoService payInfoService;
|
|
@Override
|
public boolean savePostingLogic(String docCode) {
|
try {
|
Integer formId = 150101;
|
StringBuffer sb = new StringBuffer();
|
sb.append(" set nocount on\n" +
|
" declare @docCode varchar(50)='" + docCode + "' ,@FormId int=" + formId + ",@PostFormId int,@DocStatusValue int,@now datetime=getdate() \n");
|
sb.append(" declare @PreDocStatus int,@PostDocStatus int, @EnterCode varchar(50)='SYSTEM',@EnterName varchar(50)='系统管理员',@EnterDate dateTime=@now \n");
|
sb.append(" declare @Memo varchar(50), @LinkDocInfo varchar(50)\n");
|
//标记为在维护费线支付
|
sb.append(" update t" + formId + "H set OnlinePaymentReturnCode='0000' where DocCode=@docCode \n");
|
//更新推送消息表的字段
|
sb.append(" update t150372 \n" +
|
" set ISpay = 1 ,PayDate=GETDATE() \n" +
|
" where Refdoccode in(select SODoccode from t" + formId + "D where DocCode=@docCode) \n");
|
sb.append(createPostSql(formId));
|
sb.append(" select @docCode as docCode");
|
log.info("do120311sql:" + sb.toString());
|
this.jdbcTemplate.execute(sb.toString());
|
return true;
|
} catch (Exception e) {
|
return false;
|
}
|
}
|
|
@Override
|
public List<Map<String, Object>> selectMaintenanceFeeNotification(String userCode) {
|
try {
|
String sql = "select isReceiveMaintenanceFeeNotification,inactive,usercode,username,tel from _sys_LoginUser where isReceiveMaintenanceFeeNotification=1 and inactive=0";
|
if (!StringUtils.isBlank(userCode)) {
|
sql += " and userCode='" + userCode + "'";
|
}
|
return super.jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
return null;
|
}
|
}
|
|
@Override
|
public boolean updateT150372(String autoIds) {
|
boolean bol = false;
|
try {
|
String sql = "set nocount on\n" +
|
" declare @autoids varchar(max) = '" + autoIds + "' \n" +
|
" update a set ISpush = 1 ,PushDate = getdate() from t150372 a where a.autoid in (select list from GetInStr( @autoids)) \n" +
|
" select @@ROWCOUNT";
|
Integer cunt = super.jdbcTemplate.update(sql);
|
if (cunt != null && cunt > 0) {
|
bol = true;
|
}
|
} catch (Exception e) {
|
bol = false;
|
}
|
return bol;
|
}
|
|
@Override
|
public boolean deleteSysMessage(String whCode) {
|
boolean bol = false;
|
try {
|
String sql = "declare @whCode varchar(50)='"+whCode+"',@row int \n" +
|
" delete _sysMessageCount where messid in(select messId from _sysMessage a where a.origfields=@whCode) \n" +
|
" select @row=@@ROWCOUNT \n" +
|
" if(isnull(@row,0)>0) \n" +
|
" begin \n" +
|
" delete _sysMessage where origfields=@whCode \n" +
|
" select @row=@@ROWCOUNT \n" +
|
" end \n" +
|
" select @row";
|
Integer count = super.jdbcTemplate.update(sql);
|
if (count > 0) {
|
bol = true;
|
}
|
} catch (Exception e) {
|
bol = false;
|
}
|
return bol;
|
}
|
|
@Override
|
public String selectCltCodeDbId(String table, String docCode) throws Exception {
|
try {
|
String sql="select DocCode,CltCode,DocType,DocDate \n" +
|
" from "+table+" where DocCode = '"+docCode+"'";
|
PayInfo info = super.jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(PayInfo.class));
|
if (info != null && !StringUtils.isBlank(info.getCltCode())) {
|
Integer dbId = payInfoService.queryDbIdByCltCode(info.getCltCode());
|
if (dbId == null || dbId == 0) {
|
throw new Exception("获取不到客户编号:" + info.getCltCode() + "的dbId值，请完善【维护客户资料-110203】表内的dbId值");
|
}
|
return dbId + "";
|
} else {
|
throw new Exception("客户编号不能为空！");
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
/**
|
* 生成收款单
|
*
|
* @param whCode
|
* @param accountCode
|
* @return
|
* @throws Exception
|
*/
|
@Override
|
public String createSKCode(String whCode, String accountCode) throws Exception {
|
try {
|
String sql = " declare @linkdocinfo varchar(8000) \n" +
|
" declare @FromDocCode varchar(5000) ='" + whCode + "' ,@FromFormId int = 120311 , @usercode varchar(20) = 'SYSTEM' \n" +
|
" declare @username varchar(50) = '系统管理员',@newdoccode varchar(20) ,@newformid int \n" +
|
" declare @AccountCode varchar(20) = '" + accountCode + "' \n" +
|
" exec p150105V6 @FromDocCode = @FromDocCode ,@FromFormId =@FromFormId , @usercode = @usercode , \n" +
|
" @username =@username,@newdoccode = @newdoccode output,@newformid = @newformid output,@linkdocinfo = @linkdocinfo output \n" +
|
" update a set AccountCode = @AccountCode from t150348 a where doccode = @newdoccode\n" +
|
" select @newdoccode as DocCode ";
|
return super.jdbcTemplate.queryForObject(sql, String.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<Map<String, Object>> selectMaintainMsg() {
|
try {
|
String sql = "declare @today datetime = convert(varchar(10),getdate(),120) \n" +
|
" select a.autoid,a.messagetxt,a.refdoccode,a.refformid,a.refformtype,a.insertactioncode," +
|
" a.insertactionname,a.insertdate ,b.dbid,a.unvaliddate,a.tipcount \n" +
|
" from t150372 a join t110203 b on a.cltcode = b.cltcode \n" +
|
" where a.ScheduledSendDate <= @today and isnull(a.ispush,0) = 0 and isnull(a.ispay,0) = 0";
|
return super.jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
return null;
|
}
|
}
|
|
@Override
|
public Integer selectMaintainCunt(String dbId) {
|
try {
|
String sql = "select COUNT(*) from t120301H a join t110203 b on a.CltCode=b.cltCode and a.DocStatus=100 and isnull(a.ReceivMoney,0) - isnull(a.ShouKuanAmount,0)>0 where b.dbId=" + dbId;
|
return super.jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
return 0;
|
}
|
}
|
|
@Override
|
public Integer updateMessage(String whCode) {
|
try {
|
String sql = "set nocount on\n" +
|
" declare @whCode varchar(max) = '" + whCode + "' \n" +
|
" update a set a.tipcount = 0 from _sysMessage a where a.FromDbId=338 and a.formid=120311 \n" +
|
" and a.origfields in(select list from GetInStr(@whCode)) \n" +
|
" select @@ROWCOUNT";
|
Integer count = super.jdbcTemplate.update(sql);
|
return count;
|
} catch (Exception e) {
|
return 0;
|
}
|
}
|
|
@Override
|
public List<String> selectWHCode(String skCode) {
|
try {
|
String sql = "select SODoccode from t150101D where DocCode='" + skCode + "'";
|
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) {
|
return null;
|
}
|
}
|
|
@Override
|
public String queryPayDbId(String whCode) {
|
try {
|
String sql="select b.dbid from t120301H a join t110203 b on a.CltCode=b.cltCode where a.docCode='"+whCode+"'";
|
return super.jdbcTemplate.queryForObject(sql,String.class);
|
}catch (Exception e){
|
return "";
|
}
|
}
|
|
|
/**
|
* 通过生成单据保存及过账sql,默认以@doccode为单号变量
|
*
|
* @param formid
|
* @return
|
*/
|
private String createPostSql(int formid) {
|
return " --执行保存及过账---维护费支付后进行的保存及过账---\n" +
|
" exec p" + formid + "Save @doccode\n" +
|
" select @PostFormId = FormId,@DocStatusValue = DocStatus from t" + formid + "H where docCode=@docCode\n" +
|
" select @PreDocStatus = PreDocStatus,@PostDocStatus = a.PostDocStatus from gform a where a.formid = @PostFormId \n" +
|
" if isnull(@DocStatusValue,0) = isnull(@PreDocStatus,0)\n" +
|
" begin \n" +
|
" update a set DocStatus = @PostDocStatus,PostCode=@EnterCode,PostName=@EnterName,PostDate=@now\n" +
|
" from t" + formid + "H a where a.DocCode = @docCode \n" +
|
" exec p" + formid + "Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
|
" @DocCode = @docCode,@FormId = @PostFormId,\n" +
|
" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
|
" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
|
" end\n";
|
}
|
}
|
