package com.yc.sdk.weixincp3rd.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.sdk.weixincp3rd.entity.AfterSalesEntity;
|
import com.yc.sdk.weixincp3rd.entity.AfterSalesParameters;
|
import com.yc.service.BaseService;
|
import com.yc.service.upload.AttachmentIfc;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.stereotype.Service;
|
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @BelongsProject: eCoWorksV3
|
* @BelongsPackage: com.yc.sdk.weixincp3rd.service
|
* @author: xinyb
|
* @CreateTime: 2024-04-30 09:12
|
* @Description: 售后对接api
|
*/
|
@Service
|
public class AfterSalesImpl extends BaseService implements AfterSalesIfc {
|
@Autowired
|
private AttachmentIfc attachmentIfc;
|
|
@Override
|
public void saveUpkeep(AfterSalesParameters param) throws Exception {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(50) ,@cltCode varchar(50) = '',@cltName varchar(100) = ''," +
|
"@companyId varchar(10),@companyName varchar(100),@seq int =" + param.getSeq() + "," +
|
"@ccCode varchar(50),@ccName varchar(50),"+
|
"@Longitude varchar(50),@Latitude varchar(50),@FullAddressName varchar(200)," +
|
"@FullAddress varchar(300),@Street varchar(200)\n";
|
if (StringUtils.isNotBlank(param.getOpenId())) {
|
sql += "select @cltCode=cltCode,@cltName=cltName,@ccCode=ccCode,@ccName=ccName," +
|
"@companyId=companyId,@companyName=companyName from t110203 where MiniAppOpenId=" + GridUtils.prossSqlParm(param.getOpenId()) + "\n";
|
sql += " if isnull(@cltCode,'') = '' \n " +
|
" begin \n" +
|
" raiserror('系统未绑定微信用户ID,请绑定后再执行',16,1) \n" +
|
" return \n" +
|
" end \n";
|
}
|
sql += "select @Longitude = Longitude,@Latitude=Latitude,@FullAddressName=FullAddressName," +
|
"@FullAddress=isnull(FullAddress,'')+''+isnull(FullAddressName,'')+''+isnull(Street,'')," +
|
"@Street=Street from t110209 " +
|
"where seq=@seq";
|
sql += " exec sp_newdoccode 640210,'SYSTEM',@docCode output\n";
|
sql += "insert into t640210H(docCode,formId,docDate,periodID,ccCode,ccName,DocStatus,LastDocStatus,PostCode,PostName,companyId,companyName,AcceptSource,cltCode,cltName,tel," +
|
"PropertyAddress,PreSendDate,Memo,enterDate,UploadFiles,seq,Longitude,Latitude,FullAddressName,Street)" +
|
" values (@docCode," + param.getFormId() + ",convert(varchar(10),getdate(),120),convert(varchar(7),getdate(),120),@ccCode,@ccName,0,0,'system','小程序端',@companyId,@companyName,'报修',@cltCode," +
|
GridUtils.prossSqlParm(param.getCltName()) + "," + GridUtils.prossSqlParm(param.getTel()) + ",@FullAddress," +
|
GridUtils.prossSqlParm(param.getPreSendDate()) + "," +
|
GridUtils.prossSqlParm(param.getMemo()) + ",getdate()," + GridUtils.prossSqlParm(param.getUploadFiles()) + "," +
|
"@seq,@Longitude,@Latitude,@FullAddressName,@Street) \n";
|
sql+=" exec dbo.p640210post 'SYSTEM','系统管理员',@docCode,640210,0,'提交','','','' \n";
|
if(StringUtils.isNotBlank(param.getUploadFiles())){
|
String unId=param.getUploadFiles().split(";")[0];//0元素必定是unId值
|
sql+="update _sys_Attachment set docCode=@docCode where UNID='"+unId+"'";
|
}
|
jdbcTemplate.update(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<Map<String, Object>> getUpkeepList(Integer docStatus, String openId) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select a.acceptSource,a.docCode,a.enterDate,a.preSendDate,a.cltCode,a.cltName," +
|
"(case when isnull(a.DocStatus,0) <100 then 1\n" +
|
" when isnull(a.DocStatus,0)=100 and isnull(a.WanGongStatus,'未完工') = '未完工' then 2 \n" +
|
" when isnull(a.DocStatus,0)=100 and isnull(a.WanGongStatus,'未完工') = '已完工' then 3 \n" +
|
" else 1 end) docStatus,isnull(a.lastDocStatus,0) lastDocStatus,a.memo \n" +
|
"from t640210H a join t110203 b on a.CltCode=b.CltCode and b.MiniAppOpenId=" + GridUtils.prossSqlParm(openId) + "\n" +
|
"where a.AcceptSource='报修' ";
|
if (docStatus > 0 ){
|
if (docStatus == 1) {
|
sql += " and isnull(a.DocStatus,0) < 100";
|
} else if (docStatus ==2) {
|
sql += " and isnull(a.DocStatus,0) = 100 and isnull(a.WanGongStatus,'未完工') = '未完工'";
|
}else if(docStatus == 3){
|
sql += " and isnull(a.DocStatus,0) = 100 and isnull(a.WanGongStatus,'未完工') = '已完工'";
|
}
|
}
|
sql+=" order by a.enterDate desc";
|
return jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public AfterSalesEntity getUpkeepInfo(String docCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select a.topic,a.id,a.inserttime,a.curStatus,a.ActionCode,a.ActionName,a.ActionTel,\n" +
|
"b.acceptSource,b.docCode,b.formId,b.PaiGongDocCode,b.PaiGongFormId,b.enterDate,b.preSendDate,b.uploadFiles,b.cltCode,b.cltName,b.tel," +
|
"isnull(b.propertyAddress,'') as propertyAddress," +
|
"isnull(b.docStatus,0) docStatus,isnull(b.LastDocStatus,0) lastDocStatus,b.memo,b.longitude,b.latitude,b.fullAddressName \n" +
|
"from t640210D2 a right join t640210h b on a.SODocCode=b.docCode where b.docCode= " + GridUtils.prossSqlParm(docCode) + " order by id desc";
|
return AfterSalesEntity.setAfterSalesEntity(jdbcTemplate.queryForList(sql));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public void saveComplain(AfterSalesParameters param) throws Exception {
|
String sql = "set nocount on\n";
|
try {
|
|
sql += "declare @docCode varchar(50) ,@cltCode varchar(50)='',@cltName varchar(200)='',@tel varchar(50)=''," +
|
"@ccCode varchar(50),@ccName varchar(50),"+
|
"@propertyAddress varchar(200),@companyId varchar(10),@companyName varchar(100)\n";
|
if (StringUtils.isNotBlank(param.getOpenId())) {
|
sql += "select @cltCode=cltCode,@cltName=cltName,@ccCode=ccCode,@ccName=ccName,@tel=tel," +
|
"@propertyAddress=PropertyAddress,@companyId=companyId,@companyName=companyName from t110203 where MiniAppOpenId=" + GridUtils.prossSqlParm(param.getOpenId()) + "\n";
|
sql+=" if isnull(@cltCode,'') = '' \n " +
|
" begin \n" +
|
" raiserror('系统未绑定微信用户ID,请绑定后再执行',16,1) \n" +
|
" return \n" +
|
" end \n";
|
}
|
sql += " exec sp_newdoccode 640210,'SYSTEM',@docCode output\n";
|
sql += "insert into t640210H(docCode,formId,ccCode,ccName,DocStatus,LastDocStatus,PostCode,PostName,docDate,periodID,companyId,companyName,AcceptSource,cltCode,cltName,tel,PropertyAddress,Memo,enterDate,UploadFiles)" +
|
" values (@docCode," + param.getFormId() + ",@ccCode,@ccName,0,0,'system','小程序端',convert(varchar(10),getdate(),120),convert(varchar(7),getdate(),120),@companyId,@companyName,'投诉',@cltCode,@cltName,@tel,@propertyAddress," +
|
GridUtils.prossSqlParm(param.getMemo()) + ",getdate()," + GridUtils.prossSqlParm(param.getUploadFiles()) + ") \n";
|
sql+=" exec dbo.p640210post 'SYSTEM','系统管理员',@docCode,640210,0,'提交','','','' \n";
|
if(StringUtils.isNotBlank(param.getUploadFiles())){
|
String unId=param.getUploadFiles().split(";")[0];//0元素必定是unId值
|
sql+="update _sys_Attachment set docCode=@docCode where UNID='"+unId+"'";
|
}
|
jdbcTemplate.update(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<Map<String, Object>> getComplainList(Integer docStatus, String openId) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select a.acceptSource,a.docCode,a.enterDate,a.preSendDate,a.cltCode,a.cltName," +
|
"(case when isnull(a.DocStatus,0) <100 then 1\n" +
|
" when isnull(a.DocStatus,0)=100 and isnull(a.WanGongStatus,'未完工') = '未完工' then 2 \n" +
|
" when isnull(a.DocStatus,0)=100 and isnull(a.WanGongStatus,'未完工') = '已完工' then 3 \n" +
|
" else 1 end) docStatus,isnull(a.lastDocStatus,0) lastDocStatus,a.memo \n" +
|
"from t640210H a join t110203 b on a.CltCode=b.CltCode and b.MiniAppOpenId=" + GridUtils.prossSqlParm(openId) + "\n" +
|
"where a.AcceptSource='投诉' ";
|
if (docStatus > 0 ){
|
if (docStatus == 1) {
|
sql += " and isnull(a.DocStatus,0) < 100";
|
} else if (docStatus ==2) {
|
sql += " and isnull(a.DocStatus,0) = 100 and isnull(a.WanGongStatus,'未完工') = '未完工'";
|
}else if(docStatus == 3){
|
sql += " and isnull(a.DocStatus,0) = 100 and isnull(a.WanGongStatus,'未完工') = '已完工'";
|
}
|
}
|
sql+=" order by a.enterDate desc";
|
return jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public AfterSalesEntity getComplainInfo(String docCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select a.topic,a.id,a.inserttime,a.curStatus,a.ActionCode,a.ActionName,a.ActionTel,\n" +
|
"b.acceptSource,b.docCode,b.formId,b.PaiGongDocCode,b.PaiGongFormId,b.enterDate,b.preSendDate,b.UploadFiles,b.cltCode,b.cltName,b.tel," +
|
"isnull(b.propertyAddress,'') as propertyAddress," +
|
"isnull(b.docStatus,0) docStatus,isnull(b.lastDocStatus,0) lastDocStatus,b.memo\n" +
|
"from t640210D2 a right join t640210h b on a.SODocCode=b.docCode where b.docCode= " + GridUtils.prossSqlParm(docCode) + " order by id desc";
|
return AfterSalesEntity.setAfterSalesEntity(jdbcTemplate.queryForList(sql));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public void saveSatisfaction(AfterSalesParameters param) throws Exception {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(50) ,@cltCode varchar(50)='',@cltName varchar(200)='',@tel varchar(20)=''," +
|
"@propertyAddress varchar(200),@companyId varchar(10),@companyName varchar(100)," +
|
"@PaiGongDocCode varchar(50),@PaiGongFormId int,@PaiGongFormType int ,@periodID varchar(20)," +
|
"@postCode varchar(50),@postName varchar(50),@ccCode varchar(50),@ccName varchar(50)\n";
|
sql += "select @cltCode=cltCode,@cltName=cltName,@tel=tel,@propertyAddress=propertyAddress,@postCode=postCode," +
|
"@postName=postName,@companyId=companyId,@companyName=companyName,@ccCode=ccCode,@ccName=ccName," +
|
"@PaiGongDocCode=PaiGongDocCode,@PaiGongFormId=PaiGongFormId,@PaiGongFormType=PaiGongFormType,@periodID=periodID " +
|
"from t640210H where docCode=" + GridUtils.prossSqlParm(param.getRefCode())+"\n";
|
sql += " if @@ROWCOUNT = 0 \n " +
|
" begin \n" +
|
" raiserror('未找到提交单号，请检测是否已经提交',16,1) \n" +
|
" return \n" +
|
" end \n";
|
sql += " exec sp_newdoccode 640402,'SYSTEM',@docCode output\n";
|
sql += "insert into t640402H(docCode,formId,refCode,refFormId,refFormType,postCode,postName,PaiGongDocCode,PaiGongFormId,PaiGongFormType,periodID," +
|
"DocStatus,companyId,companyName,docDate,cltCode,cltName,tel,EnterDate,OverallEvaluationScore,InstallEvaluationScore,AfterSalesEvaluationScore," +
|
"DeliveryEvaluationScore,HDMemo,UploadFiles,propertyAddress,ccCode,ccName)" +
|
" values (@docCode,640402," + GridUtils.prossSqlParm(param.getRefCode()) + "," + param.getRefFormId() + ",496,@postCode,@postName,@PaiGongDocCode,@PaiGongFormId,@PaiGongFormType,@periodID," +
|
"0,@companyId,@companyName,convert(varchar(10),getdate(),120),@cltCode,@cltName,@tel,getdate()," +
|
GridUtils.prossSqlParm(param.getOverallEvaluationScore()) + "," + GridUtils.prossSqlParm(param.getInstallEvaluationScore()) + "," +
|
GridUtils.prossSqlParm(param.getAfterSalesEvaluationScore()) + "," + GridUtils.prossSqlParm(param.getDeliveryEvaluationScore()) + "," +
|
GridUtils.prossSqlParm(param.getHDMemo()) + "," + GridUtils.prossSqlParm(param.getUploadFiles()) + ",@propertyAddress,@ccCode,@ccName) \n";
|
sql += "exec dbo.p640402post 'SYSTEM','系统管理员',@doccode,640402,0,'提交','','','' \n";
|
if(StringUtils.isNotBlank(param.getUploadFiles())){
|
String unId=param.getUploadFiles().split(";")[0];//0元素必定是unId值
|
sql+="update _sys_Attachment set docCode=@docCode where UNID='"+unId+"'";
|
}
|
jdbcTemplate.update(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public void savePreInstallation(AfterSalesParameters param) throws Exception {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(50) ,@cltCode varchar(50) = '',@cltName varchar(100) = ''," +
|
"@companyId varchar(10),@companyName varchar(100),@seq int =" + param.getSeq() + "," +
|
"@ccCode varchar(50),@ccName varchar(50),"+
|
"@Longitude varchar(50),@Latitude varchar(50),@FullAddressName varchar(200)," +
|
"@FullAddress varchar(300),@Street varchar(200)\n";
|
if (StringUtils.isNotBlank(param.getOpenId())) {
|
sql += "select @cltCode=cltCode,@cltName=cltName,@ccCode=ccCode,@ccName=ccName," +
|
"@companyId=companyId,@companyName=companyName from t110203 where MiniAppOpenId=" + GridUtils.prossSqlParm(param.getOpenId()) + "\n";
|
sql += " if isnull(@cltCode,'') = '' \n " +
|
" begin \n" +
|
" raiserror('系统未绑定微信用户ID,请绑定后再执行',16,1) \n" +
|
" return \n" +
|
" end \n";
|
}
|
sql += "select @cltCode=cltCode,@cltName=cltName,@Longitude = Longitude,@Latitude=Latitude,@FullAddressName=FullAddressName," +
|
"@FullAddress=isnull(FullAddress,'')+''+isnull(FullAddressName,'')+''+isnull(Street,'')," +
|
"@Street=Street from t110209 " +
|
"where seq=@seq";
|
sql += " exec sp_newdoccode 640210,'SYSTEM',@docCode output\n";
|
sql += "insert into t640210H(docCode,formId,docDate,periodID,ccCode,ccName,DocStatus,LastDocStatus,PostCode,PostName,companyId,companyName,AcceptSource,cltCode,cltName,tel," +
|
"PropertyAddress,PreSendDate,Memo,enterDate,UploadFiles,seq,Longitude,Latitude,FullAddressName,Street)" +
|
" values (@docCode," + param.getFormId() + ",convert(varchar(10),getdate(),120),convert(varchar(7),getdate(),120),@ccCode,@ccName,0,0,'system','小程序端',@companyId,@companyName,'预约',@cltCode," +
|
"@cltName," + GridUtils.prossSqlParm(param.getTel()) + ",@FullAddress," +
|
GridUtils.prossSqlParm(param.getPreSendDate()) + "," +
|
GridUtils.prossSqlParm(param.getMemo()) + ",getdate()," + GridUtils.prossSqlParm(param.getUploadFiles()) + "," +
|
"@seq,@Longitude,@Latitude,@FullAddressName,@Street) \n";
|
sql+=" exec dbo.p640210post 'SYSTEM','系统管理员',@docCode,640210,0,'提交','','','' \n";
|
if(StringUtils.isNotBlank(param.getUploadFiles())){
|
String unId=param.getUploadFiles().split(";")[0];//0元素必定是unId值
|
sql+="update _sys_Attachment set docCode=@docCode where UNID='"+unId+"'";
|
}
|
jdbcTemplate.update(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<Map<String, Object>> getPreInstallation(Integer docStatus, String openId) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select a.acceptSource,a.docCode,a.enterDate,a.preSendDate,a.cltCode,a.cltName," +
|
"(case when isnull(a.DocStatus,0) <100 then 1\n" +
|
" when isnull(a.DocStatus,0)=100 then 3 \n" +
|
" else 1 end) docStatus,isnull(a.lastDocStatus,0) lastDocStatus,a.memo \n" +
|
"from t640210H a join t110203 b on a.CltCode=b.CltCode and b.MiniAppOpenId=" + GridUtils.prossSqlParm(openId) + "\n" +
|
"where a.AcceptSource='预约' ";
|
if (docStatus == 1) {
|
sql += " and isnull(a.DocStatus,0) < 100";
|
} else if (docStatus == 3) {
|
sql += " and isnull(a.DocStatus,0) = 100";
|
}
|
sql += " order by a.enterDate desc";
|
return jdbcTemplate.queryForList(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public AfterSalesEntity getPreInstallationInfo(String docCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select a.topic,a.id,a.inserttime,a.curStatus,a.ActionCode,a.ActionName,a.ActionTel,\n" +
|
"b.acceptSource,b.docCode,b.formId,b.PaiGongDocCode,b.PaiGongFormId,b.enterDate,b.preSendDate,b.uploadFiles,b.cltCode,b.cltName,b.tel," +
|
"isnull(b.propertyAddress,'') as propertyAddress," +
|
"isnull(b.docStatus,0) docStatus,isnull(b.LastDocStatus,0) lastDocStatus,b.memo,b.longitude,b.latitude,b.fullAddressName \n" +
|
"from t640210D2 a right join t640210h b on a.SODocCode=b.docCode where b.docCode= " + GridUtils.prossSqlParm(docCode) + " order by id desc";
|
return AfterSalesEntity.setAfterSalesEntity(jdbcTemplate.queryForList(sql));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public void saveRenewal(AfterSalesParameters param) throws Exception {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(50) ,@cltCode varchar(50) = '',@cltName varchar(100) = ''," +
|
"@companyId varchar(10),@companyName varchar(100),@seq int =" + param.getSeq() + "," +
|
"@ccCode varchar(50),@ccName varchar(50),"+
|
"@Longitude varchar(50),@Latitude varchar(50),@FullAddressName varchar(200)," +
|
"@FullAddress varchar(300),@Street varchar(200)\n";
|
if (StringUtils.isNotBlank(param.getOpenId())) {
|
sql += "select @cltCode=cltCode,@cltName=cltName,@ccCode=ccCode,@ccName=ccName," +
|
"@companyId=companyId,@companyName=companyName from t110203 where MiniAppOpenId=" + GridUtils.prossSqlParm(param.getOpenId()) + "\n";
|
sql += " if isnull(@cltCode,'') = '' \n " +
|
" begin \n" +
|
" raiserror('系统未绑定微信用户ID,请绑定后再执行',16,1) \n" +
|
" return \n" +
|
" end \n";
|
}
|
sql += "select @Longitude = Longitude,@Latitude=Latitude,@FullAddressName=FullAddressName," +
|
"@FullAddress=isnull(FullAddress,'')+''+isnull(FullAddressName,'')+''+isnull(Street,'')," +
|
"@Street=Street from t110209 " +
|
"where seq=@seq";
|
sql += " exec sp_newdoccode 640210,'SYSTEM',@docCode output\n";
|
sql += "insert into t640210H(docCode,formId,docDate,periodID,ccCode,ccName,DocStatus,LastDocStatus,PostCode,PostName,companyId,companyName,AcceptSource,cltCode,cltName,tel," +
|
"PropertyAddress,PreSendDate,Memo,enterDate,UploadFiles,seq,Longitude,Latitude,FullAddressName,Street)" +
|
" values (@docCode," + param.getFormId() + ",convert(varchar(10),getdate(),120),convert(varchar(7),getdate(),120),@ccCode,@ccName,0,0,'system','小程序端',@companyId,@companyName,'换新',@cltCode," +
|
GridUtils.prossSqlParm(param.getCltName()) + "," + GridUtils.prossSqlParm(param.getTel()) + ",@FullAddress," +
|
GridUtils.prossSqlParm(param.getPreSendDate()) + "," +
|
GridUtils.prossSqlParm(param.getMemo()) + ",getdate()," + GridUtils.prossSqlParm(param.getUploadFiles()) + "," +
|
"@seq,@Longitude,@Latitude,@FullAddressName,@Street) \n";
|
sql+=" exec dbo.p640210post 'SYSTEM','系统管理员',@docCode,640210,0,'提交','','','' \n";
|
if(StringUtils.isNotBlank(param.getUploadFiles())){
|
String unId=param.getUploadFiles().split(";")[0];//0元素必定是unId值
|
sql+="update _sys_Attachment set docCode=@docCode where UNID='"+unId+"'";
|
}
|
jdbcTemplate.update(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public Integer deleteFile(String unId, Integer seq) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @unid varchar(100) =" + GridUtils.prossSqlParm(unId) + " ,@seq int =" + seq + "\n";
|
sql += "delete from _sys_Attachment where unid = @unid and seq = @seq\n";
|
sql += " select @@rowcount \n";
|
Integer ret = this.jdbcTemplate.queryForObject(sql, Integer.class);
|
return ret;
|
} catch (DataAccessException e) {
|
if (e instanceof EmptyResultDataAccessException) {
|
return null;
|
} else {
|
e.printStackTrace();
|
throw e;
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw e;
|
}
|
}
|
}
|
