package com.yc.open.mutual.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.entity.DataSourceEntity;
|
import com.yc.exception.ApplicationException;
|
import com.yc.open.deli.entity.T110203Entity;
|
import com.yc.open.deli.entity.T110562Entity;
|
import com.yc.open.init.Task;
|
import com.yc.open.mutual.entity.*;
|
import com.yc.open.mutual.schedule.GateEntity;
|
import com.yc.service.BaseService;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.dao.IncorrectResultSizeDataAccessException;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class MutualServiceImpl extends BaseService implements MutualServiceIfc {
|
|
@Transactional
|
@Override
|
public void saveLogs(MutualEntity mutualEntity) {
|
String sql = "set nocount on \n insert into _sysAPIAuthLogs([cltCode],[cltName],[cltCompanyName],[vndCode],[vndName],[vndCompanyName],[remarks],insertTime) values (?,?,?,?,?,?,?,getdate())";
|
this.jdbcTemplate.update(sql
|
, mutualEntity.getT110203().getUserCode()
|
, mutualEntity.getT110203().getUserName()
|
, mutualEntity.getT110203().getCompanyName()
|
, mutualEntity.getT110302().getUserCode()
|
, mutualEntity.getT110302().getUserName()
|
, mutualEntity.getT110302().getCompanyName()
|
, mutualEntity.getRemarks()
|
);
|
}
|
|
@Override
|
public List get110203List(String cltName, Integer pageNumber) {
|
String newSql = "set nocount on \n" +
|
" declare @Limit int = 20 , @Page int = ?,@cltName varchar(150)=?,@StartRowNo int ,@EndRowNo int ;"
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY cltName desc) AS NO,cltCode,cltName, companyId, companyName,ccCode, ccName from t110203 where 1=1 \n";
|
if (StringUtils.isNotBlank(cltName)) {
|
newSql += " and cltName like '%'+@cltName+'%'";
|
}
|
newSql += " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
return this.jdbcTemplate.queryForList(newSql, pageNumber, cltName);
|
}
|
|
@Override
|
public List get110302List(String vndName, Integer pageNumber) {
|
String newSql = "set nocount on \n" +
|
" declare @Limit int = 20 , @Page int = ?,@vndName varchar(150)=?,@StartRowNo int ,@EndRowNo int ;"
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY vndName desc) AS NO,vndCode,vndName,companyId,companyName,ccCode, ccName from t110302 where 1=1 \n";
|
if (StringUtils.isNotBlank(vndName)) {
|
newSql += " and vndName like '%'+@vndName+'%'";
|
}
|
newSql += " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
return this.jdbcTemplate.queryForList(newSql, pageNumber, vndName);
|
}
|
|
@Transactional
|
@Override
|
public Integer save110203(SaveEntity saveEntity) {
|
if (saveEntity.isUnbind()) {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=null,a.AuthenticatedCompanyId=null,a.AuthenticatedCompanyName=null,a.AuthenticatedCcCode=null,a.AuthenticatedCcName=null,a.authenticatedCode=null from t110203 a where cltCode=?", saveEntity.getCltCode());
|
} else {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=?,a.AuthenticatedCompanyId=?,a.AuthenticatedCompanyName=?,a.AuthenticatedCcCode=?,a.AuthenticatedCcName=?,a.authenticatedCode=? from t110203 a where cltCode=?", saveEntity.getDbId(), saveEntity.getCompanyId(), saveEntity.getCompanyName(), saveEntity.getCcCode(), saveEntity.getCcName(),saveEntity.getAuthenticatedCode(),saveEntity.getCltCode());
|
}
|
}
|
|
@Transactional
|
@Override
|
public Integer save110302(SaveEntity saveEntity) {
|
if (saveEntity.isUnbind()) {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=null,a.AuthenticatedCompanyId=null,a.AuthenticatedCompanyName=null,a.AuthenticatedCcCode=null,a.AuthenticatedCcName=null,a.authenticatedCode=null from t110302 a where vndCode=?", saveEntity.getVndCode());
|
} else {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=? ,a.AuthenticatedCompanyId=?,a.AuthenticatedCompanyName=?,a.AuthenticatedCcCode=?,a.AuthenticatedCcName=?,a.authenticatedCode=? from t110302 a where vndCode=?", saveEntity.getDbId(), saveEntity.getCompanyId(), saveEntity.getCompanyName(), saveEntity.getCcCode(), saveEntity.getCcName(),saveEntity.getAuthenticatedCode(), saveEntity.getVndCode());
|
}
|
}
|
|
@Transactional
|
@Override
|
public Integer doChangeRef110203(Integer dbid, String oldCltCode, String newCltCode) {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=null from t110203 a where cltCode=? \n update a set a.dbId=? from t110203 a where cltCode=?", oldCltCode, dbid, newCltCode);
|
}
|
|
@Transactional
|
@Override
|
public Integer doChangeRef110302(Integer dbid, String oldVndCode, String newVndCode) {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=null from t110302 a where vndCode=? \n update a set a.dbId=? from t110302 a where vndCode=?", oldVndCode, dbid, newVndCode);
|
}
|
@Transactional
|
@Override
|
public Integer unbind110203(Integer refDbId) {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=null,a.AuthenticatedCompanyId=null,a.AuthenticatedCompanyName=null,a.AuthenticatedCcCode=null,a.AuthenticatedCcName=null,a.authenticatedCode=null from t110203 a where dbid=?", refDbId);
|
}
|
@Transactional
|
@Override
|
public Integer unbind110302(Integer refDbId) {
|
return this.jdbcTemplate.update("set nocount on \n update a set a.dbId=null,a.AuthenticatedCompanyId=null,a.AuthenticatedCompanyName=null,a.AuthenticatedCcCode=null,a.AuthenticatedCcName=null,a.authenticatedCode=null from t110302 a where dbid=?", refDbId);
|
}
|
|
@Override
|
public List get110203ListByMutualed(String cltName, Integer pageNumber) {
|
String newSql = "set nocount on \n" +
|
" declare @Limit int = 20 , @Page int = ?,@cltName varchar(150)=?,@StartRowNo int ,@EndRowNo int ;"
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY cltName desc) AS NO,dbId,cltCode,cltName from t110203 where 1=1 and isnull(dbid,0)>0\n";
|
if (StringUtils.isNotBlank(cltName)) {
|
newSql += " and cltName like '%'+@cltName+'%'";
|
}
|
newSql += " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
return this.jdbcTemplate.queryForList(newSql, pageNumber, cltName);
|
}
|
|
@Override
|
public List get110302ListByMutualed(String vndName, Integer pageNumber) {
|
String newSql = "set nocount on \n" +
|
" declare @Limit int = 20 , @Page int = ?,@vndName varchar(150)=?,@StartRowNo int ,@EndRowNo int ;"
|
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n"
|
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n"
|
+ " SELECT * FROM ( \n"
|
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY vndName desc) AS NO,dbId,vndCode,vndName from t110302 where 1=1 and isnull(dbid,0)>0\n";
|
if (StringUtils.isNotBlank(vndName)) {
|
newSql += " and vndName like '%'+@vndName+'%'";
|
}
|
newSql += " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo \n";
|
return this.jdbcTemplate.queryForList(newSql, pageNumber, vndName);
|
}
|
|
@Override
|
public T130301Entity getT130301(String docCode, Integer affectedDocStatus) {
|
String hsql = " set nocount on " +
|
" declare @DefaultVndCode varchar(50),@DefaultVndName varchar(80) ,@postdocstatus int \n" +
|
" declare @table table(docCode varchar(20) , \n" +
|
" cvenCode varchar(50),cvenName varchar(80), \n" +
|
" hDMemo varchar(400), \n" +
|
" docStatus int,rowid varchar(20), \n" +
|
" DocItem int,matCode varchar(50),matName varchar(80),special varchar(80),uom varchar(20),digit float, \n" +
|
" ItemMemo varchar(400),soDocCode varchar(20),canceldigit money, \n" +
|
" brand varchar(20),cv2 varchar(80), \n" +
|
" packingQuantity varchar(100),weight money,totalWeight money, \n" +
|
" unsubscribeDoccode varchar(20),sORowid varchar(20),errMsg varchar(500),dbId int,uploads varchar(200),hdcurrency varchar(10),hdcurrencyrate money,authenticatedCode varchar(50)) \n" +
|
|
" declare @docCode varchar(20), \n" +
|
" @cvenCode varchar(50),@cvenName varchar(80),\n" +
|
" @hDMemo varchar(400), \n" +
|
" @docStatus int,@rowid varchar(20), \n" +
|
" @DocItem int,@matCode varchar(50),@matName varchar(80),@special varchar(80),@uom varchar(20),@digit float, \n" +
|
" @ItemMemo varchar(400),@soDocCode varchar(20),@canceldigit money, \n" +
|
" @brand varchar(20),@cv2 varchar(80), \n" +
|
" @packingQuantity varchar(100),@weight money,@totalWeight money, \n" +
|
" @unsubscribeDoccode varchar(20),@soRowid varchar(20) ,@soCcCode varchar(20),@soCcName varchar(50),@dbId int,@uploads varchar(200),@hdcurrency varchar(10),@hdcurrencyrate money,@authenticatedCode varchar(50) \n" +
|
" declare @ObjMatCode varchar(50),@ObjMatName varchar(120) ,@ObjSpecial varchar(120) ,@FormId int = 130301 \n" +
|
|
" select @postdocstatus = postdocstatus from gform where formid = @FormId \n" +
|
" declare @errMsg varchar(2000) = '',@myrowcount int,@myerror int\n" +
|
" declare mycurDeli cursor for\n" +
|
|
" select a.docCode,a.hDMemo,\n" +
|
" a.docStatus,b.MatCode,b.MatName,b.rowid ,b.digit,b.SODoccode,b.SOrowid,j.dbId,a.uploads,a.hdcurrency,a.hdcurrencyrate,j.authenticatedCode \n" +
|
" from t130301H a join t130301D b on a.DocCode = b.DocCode\n" +
|
" join gform c on a.formid= c.formid\n" +
|
" left join t110601 d on a.cccode = d.cccode\n" +
|
//" join t110503 e on b.matcode = e.matcode " +
|
" join t110302 j on j.vndCode = a.vndcode " +//直接取表头的供应商
|
" where \n" +
|
// " a.vndCode = @DefaultVndCode " +
|
" isnull(b.PushStatus,0)=0 \n" +
|
" and a.DocStatus=? \n" +
|
//--只取有绑定关系的供应商
|
" and isnull(j.dbId,0)<>0 \n" +
|
" and a.docCode=? \n" +
|
//" and isnull(e.vndcode,'') = @DefaultVndCode \n" +
|
" order by a.doccode asc,b.DocItem asc \n" +
|
" open mycurDeli \n" +
|
" fetch next from mycurDeli into @docCode,@hDMemo,\n" +
|
" @docStatus,@MatCode,@MatName,@RowId,@digit,@soDocCode,@soRowId,@dbId,@uploads,@hdcurrency,@hdcurrencyrate,@authenticatedCode\n" +
|
" while @@FETCH_STATUS = 0 \n" +
|
" begin\n" +
|
" insert into @table(docCode , \n" +
|
" cvenCode ,cvenName , hDMemo,docStatus ,rowid,\n" +
|
" DocItem ,matCode ,matName,special ,uom ,digit , \n" +
|
" ItemMemo ,soDocCode,canceldigit, \n" +
|
" brand,cv2 , \n" +
|
" packingQuantity,[weight] ,totalWeight , \n" +
|
" unsubscribeDoccode,sORowid ,errMsg,dbId,uploads,hdcurrency,hdcurrencyrate,authenticatedCode) \n" +
|
" values(@docCode , \n" +
|
" @cvenCode ,@cvenName , \n" +
|
" @hDMemo, \n" +
|
" @docStatus ,@rowid, \n" +
|
" @DocItem ,@MatCode ,@MatName,@Special ,@uom ,@digit , \n" +
|
" @ItemMemo ,@soDocCode,@canceldigit, \n" +
|
" @brand,@cv2 , \n" +
|
" @packingQuantity,@weight ,@totalWeight , \n" +
|
" @unsubscribeDoccode,@sORowid ,@errMsg,@dbId,@uploads,@hdcurrency,@hdcurrencyrate,@authenticatedCode )\n" +
|
// " end \n" +
|
" fetch next from mycurDeli into @docCode, @hDMemo,\n" +
|
" @docStatus,@MatCode,@MatName,@RowId,@digit,@soDocCode,@soRowId,@dbId,@uploads,@hdcurrency,@hdcurrencyrate,@authenticatedCode\n" +
|
" end \n" +
|
" close mycurDeli\n" +
|
" deallocate mycurDeli \n" +
|
" select docCode , \n" +
|
" cvenCode ,cvenName ,\n" +
|
" hDMemo,docStatus ,rowid, \n" +
|
" DocItem ,matCode ,matName,special ,uom ,digit , \n" +
|
" ItemMemo ,soDocCode,canceldigit, \n" +
|
" brand,cv2 , \n" +
|
" packingQuantity,[weight] as weight ,totalWeight , \n" +
|
" unsubscribeDoccode,sORowid ,errMsg,dbId,uploads,hdcurrency,hdcurrencyrate,authenticatedCode\n" +
|
" from @table ";
|
T130301Entity t130301Entity = null;
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(hsql, affectedDocStatus, docCode);
|
if (list != null && list.size() > 0) {
|
t130301Entity = new T130301Entity();
|
|
t130301Entity.setRefCode(docCode);
|
List<T130301DetailEntity> detailEntityList = new ArrayList<>();
|
for (Map<String, Object> map : list) {
|
t130301Entity.setHDMemo(GridUtils.prossRowSetDataType_String(map, "hDMemo"));
|
t130301Entity.setUploads(GridUtils.prossRowSetDataType_String(map, "uploads"));
|
t130301Entity.setHdcurrency(GridUtils.prossRowSetDataType_String(map, "hdcurrency"));
|
t130301Entity.setHdcurrencyrate(GridUtils.prossRowSetDataType_Double(map, "hdcurrencyrate"));
|
t130301Entity.setAuthenticatedCode(GridUtils.prossRowSetDataType_String(map, "authenticatedCode"));
|
detailEntityList.add(new T130301DetailEntity(
|
GridUtils.prossRowSetDataType_String(map, "matCode"),
|
GridUtils.prossRowSetDataType_String(map, "matName"),
|
GridUtils.prossRowSetDataType_Double(map, "digit"),
|
GridUtils.prossRowSetDataType_String(map, "ItemMemo"),
|
GridUtils.prossRowSetDataType_String(map, "soDocCode"),
|
GridUtils.prossRowSetDataType_Int(map, "DocItem"),
|
GridUtils.prossRowSetDataType_String(map, "rowid"),
|
GridUtils.prossRowSetDataType_Double(map, "canceldigit"),
|
GridUtils.prossRowSetDataType_String(map, "brand"),
|
GridUtils.prossRowSetDataType_String(map, "cv2"),
|
GridUtils.prossRowSetDataType_String(map, "packingQuantity"),
|
GridUtils.prossRowSetDataType_Double(map, "weight"),
|
GridUtils.prossRowSetDataType_Double(map, "totalWeight"),
|
GridUtils.prossRowSetDataType_String(map, "unsubscribeDoccode"),
|
GridUtils.prossRowSetDataType_String(map, "sORowid"),
|
GridUtils.prossRowSetDataType_Int(map, "dbId")
|
));
|
}
|
t130301Entity.setDetails(detailEntityList);
|
}
|
|
return t130301Entity;
|
}
|
|
@Override
|
public T120201Entity getT120201(String docCode, Task task) {
|
try {
|
final FormEntity orderEntity = getFormEntityInfo(task.getAffectedFormId());
|
T120201Entity t120201Entity = this.jdbcTemplate.queryForObject(
|
" set nocount on \n" +
|
" select a.DistributorPoDocCode,a.docCode,a.uploads,a.HDMemo,c.dbid as cltDbid from " + orderEntity.getHDtable() + " a \n" +
|
" join t110203 c on c.cltCode=a.CltCode\n" +
|
" where a.DocCode=? and isnull(c.dbid,0)<>0", new BeanPropertyRowMapper<>(T120201Entity.class), docCode);
|
List<T120201DetailsEntity> detailsEntity = this.jdbcTemplate.query(
|
" set nocount on \n" +
|
" select b.MatCode,b.refrowid,b.Price from " + orderEntity.getDDtable() + " b \n" +
|
" where b.DocCode=?", new BeanPropertyRowMapper<>(T120201DetailsEntity.class), docCode);
|
t120201Entity.setDetails(detailsEntity);
|
return t120201Entity;
|
} catch (EmptyResultDataAccessException e) {
|
return null;
|
}
|
}
|
|
@Override
|
public T120201Entity getT120201ByDistributor(String docCode) {
|
try {
|
return this.jdbcTemplate.queryForObject(
|
" set nocount on \n" +
|
" select a.docCode,c.dbid as vndDbid from t120201H a \n" +
|
" join t110302 c on c.vndCode=a.HeadqVndCode\n" +
|
" where isnull(c.dbid,0)<>0 and isnull(a.HeadqVndCode,'')<>'' and a.DocCode=?", new BeanPropertyRowMapper<>(T120201Entity.class), docCode);
|
} catch (EmptyResultDataAccessException e) {
|
return null;
|
}
|
}
|
|
@Override
|
public T150201Entity getT150201(String docCode, Task task) {
|
String sql = "set nocount on \n" +
|
" select \n" +
|
" a.docCode,a.amount,\n" +
|
" a.checkAmount,\n" +
|
" a.natAmount,\n" +
|
" a.hDMemo,\n" +
|
" a.uploads,a.HDCurrency,a.HDCurrencyRate,a.bankCode,\n" +
|
" d.dbid as vndDbid,a.vndCode,d.authenticatedCode \n" +
|
" from t150201H a \n" +
|
" join t110302 d on d.vndCode=a.VndCode\n" +
|
" where isnull(d.dbid,0)<>0 and isnull(d.authenticatedCode,'')<>'' and a.DocCode=?";
|
T150201Entity t150201Entity = null;
|
try {
|
t150201Entity = this.jdbcTemplate.queryForObject(sql
|
, new BeanPropertyRowMapper<>(T150201Entity.class), docCode);
|
} catch (EmptyResultDataAccessException ex) {
|
return null;
|
} catch (IncorrectResultSizeDataAccessException e) {
|
return null;
|
}
|
if (StringUtils.isNotBlank(t150201Entity.getDocCode())) {
|
sql = "set nocount on \n" +
|
" select \n" +
|
" b.amount ,\n" +
|
" b.itemmemo, a.HeadqSoDocCode as SoDocCode,a.HeadqSoFormId as SoFormId,b.pipeitype\n" +
|
" from t150201D b \n" +
|
" join t130301H a on a.docCode=b.PODoccode" +
|
" where b.DocCode=? and isnull(a.HeadqSoDocCode,'')<>''";
|
List<T150232Entity> t150232List = this.jdbcTemplate.query(sql
|
, new BeanPropertyRowMapper<>(T150232Entity.class), docCode);
|
/*
|
这个是经销商自己的账户信息，不需要传给总公司
|
sql = "set nocount on \n" +
|
" select \n" +
|
" b.payType,\n" +
|
" b.accountCode,\n" +
|
" (select isnull(a.subacctname,'') + case when isnull(a.BankCode,'') <> '' then ' 【' + isnull(a.BankCode,'') + '】 'else '' end as aa from t110706 a join t110702 e on a.Acctcode = e.Acctcode\n" +
|
"where a.subacctcode= b.accountCode) as accountName,\n" +
|
" b.amount, \n" +
|
" b.feeAmount ,\n" +
|
" b.itemmemo ,b.Currency,b.CurrencyRate\n" +
|
" from t150348 b \n" +
|
" where b.DocCode=?";
|
List<T150348Entity> t150348List = this.jdbcTemplate.query(sql
|
, new BeanPropertyRowMapper<>(T150348Entity.class), docCode);
|
*/
|
sql = "set nocount on \n" +
|
" select \n" +
|
" c.checkCode,\n" +
|
" c.checkType,\n" +
|
" c.refAcctCode,\n" +
|
" c.billDate,\n" +
|
" c.effDays,\n" +
|
" c.checkDate,\n" +
|
" c.checkCodeFlag,\n" +
|
" c.checkAmount ,c.Currency,c.CurrencyRate,\n" +
|
" (select hrname from v180205 where hrcode=c.owner)as owner \n" +
|
" from t150344 c \n" +
|
" where c.DocCode=?";
|
T150344Entity t150344Entity = this.jdbcTemplate.queryForObject(sql
|
, new BeanPropertyRowMapper<>(T150344Entity.class), docCode);
|
|
t150201Entity.setList150232(t150232List);
|
t150201Entity.setT150344(t150344Entity);
|
|
}
|
return t150201Entity;
|
|
}
|
|
@Override
|
public T150201Entity getT150206(String docCode) {
|
String sql = "set nocount on \n" +
|
" select \n" +
|
" a.docCode,a.amount,\n" +
|
" a.natAmount,\n" +
|
" a.hDMemo,\n" +
|
" a.uploads as uploads,a.HDCurrency,a.HDCurrencyRate,\n" +
|
" d.dbid as vndDbid,d.authenticatedCode \n" +
|
" from t150206H a \n" +
|
" join t110302 d on d.vndCode=a.VndCode\n" +
|
" where isnull(d.dbid,0)<>0 and isnull(d.authenticatedCode,'')<>'' and a.DocCode=?";
|
T150201Entity t150201Entity = null;
|
try {
|
t150201Entity = this.jdbcTemplate.queryForObject(sql
|
, new BeanPropertyRowMapper<>(T150201Entity.class), docCode);
|
} catch (EmptyResultDataAccessException e) {
|
return null;
|
} catch (IncorrectResultSizeDataAccessException ex) {
|
return null;
|
}
|
|
if (StringUtils.isNotBlank(t150201Entity.getDocCode())) {
|
|
sql = "set nocount on \n" +
|
" select \n" +
|
" b.payType,\n" +
|
" b.accountCode,\n" +
|
" (select isnull(a.subacctname,'') + case when isnull(a.BankCode,'') <> '' then ' 【' + isnull(a.BankCode,'') + '】 'else '' end as aa from t110706 a join t110702 e on a.Acctcode = e.Acctcode\n" +
|
"where a.subacctcode= b.accountCode) as accountName,\n" +
|
" b.amount, \n" +
|
" b.feeAmount ,b.Currency,b.CurrencyRate,\n" +
|
" b.itemmemo \n" +
|
" from t150348 b \n" +
|
" where b.DocCode=?";
|
List<T150348Entity> t150348List = this.jdbcTemplate.query(sql
|
, new BeanPropertyRowMapper<>(T150348Entity.class), docCode);
|
|
sql = "set nocount on \n" +
|
" select \n" +
|
" c.checkCode,\n" +
|
" c.checkType,\n" +
|
" c.refAcctCode,\n" +
|
" c.billDate,\n" +
|
" c.effDays,\n" +
|
" c.checkDate,\n" +
|
" c.checkCodeFlag,\n" +
|
" c.checkAmount ,c.Currency,c.CurrencyRate,\n" +
|
" (select hrname from v180205 where hrcode=c.owner)as owner \n" +
|
" from t150344 c \n" +
|
" where c.DocCode=?";
|
T150344Entity t150344Entity = this.jdbcTemplate.queryForObject(sql
|
, new BeanPropertyRowMapper<>(T150344Entity.class), docCode);
|
|
t150201Entity.setList150348(t150348List);
|
t150201Entity.setT150344(t150344Entity);
|
|
}
|
return t150201Entity;
|
|
}
|
|
@Transactional
|
@Override
|
public String do150170(T150201Entity entity) {
|
String sql = " set nocount on\n" +
|
" --表头变量\n" +
|
" declare @doccode varchar(50),@dbid int=" + entity.getCltDbid() + " ,@formid int=150170,@DistributorPoFormId int=150206,@PeriodID varchar(50)\n" +
|
" declare @now datetime=getdate()\n" +
|
" declare @Docdate date=convert(varchar(10),@now,120),@doctype varchar(50)='收款',\n" +
|
" @EnterCode varchar(50),@EnterName varchar(50),@EnterDate dateTime=@now,@ModifyName varchar(50),@ModifyDate dateTime=@now,@DocStatus int=0,@companyID varchar(50),@companyName varchar(150),@CltCode varchar(50),@CltName varchar(50),@PropertyAddress varchar(250),@clttype varchar(50),@hDMemo varchar(250)=" + GridUtils.prossSqlParm(entity.getHDMemo()) + ",@ccCode varchar(50),@ccName varchar(50),@tel varchar(50),@uploads varchar(200)=" + GridUtils.prossSqlParm(entity.getUploads()) + ",@DistributorPoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + ",@SumAmount money =" + entity.getAmount() + ",@checkAmount money =" + entity.getCheckAmount() + ",@checkCode varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckCode()) + ",@checkType varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckType()) + ",@refAcctCode varchar(50) =" + GridUtils.prossSqlParm(entity.getT150344().getRefAcctCode()) + " ,@billDate varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getBillDate()) + ",@effDays int=" + entity.getT150344().getEffDays() + ",@checkDate varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckDate()) + ",@checkCodeFlag varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckCodeFlag()) + ",@checkAmount150344 money=" + entity.getT150344().getCheckAmount() + ",@owner varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getOwner()) + ",@HDCurrency varchar(50)=" + GridUtils.prossSqlParm(entity.getHDCurrency()) + ",@HDCurrencyRate money=" + entity.getHDCurrencyRate() + ",@Currency varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCurrency()) + ",@CurrencyRate money=" + entity.getT150344().getCurrencyRate() + ",@authenticatedCode varchar(50)="+GridUtils.prossSqlParm(entity.getAuthenticatedCode())+"\n" +
|
" --取客户资料\n" +
|
" select @EnterCode=cltCode,@EnterName=cltName,@ModifyName=cltName,@CltCode=cltCode,@CltName=cltName,@companyID=Authenticatedcompanyid,@companyName=AuthenticatedCompanyname,@ccCode=Authenticatedcccode,@ccName=Authenticatedccname,@tel=Tel,@PropertyAddress=PropertyAddress,@clttype=clttype from t110203 where dbid=@dbid and authenticatedCode=@authenticatedCode\n" +
|
//是否已存在同客户同单号的采购订单
|
" if not exists(select 1 from t150170H where CltCode=@CltCode and DistributorPoDocCode=@DistributorPoDocCode) begin \n" +
|
" exec sp_newdoccode @formid,'SYSTEM',@doccode output\n" +
|
" select @PeriodId = dbo.GetPeriodID(@formid,@CompanyID,@now)\n" +
|
|
" insert into t150170H(doccode ,formid,PeriodID ,Docdate,doctype ,EnterCode ,EnterName ,EnterDate,ModifyName ,ModifyDate ,DocStatus,companyID,companyName,CltCode ,CltName ,hDMemo,ccCode ,ccName,DistributorPoDocCode,DistributorPoFormId,uploads,Amount,HDCurrency,HDCurrencyRate )values(@doccode ,@formid,@PeriodID ,@Docdate,@doctype ,@EnterCode ,@EnterName ,@EnterDate,@ModifyName ,@ModifyDate,@DocStatus,@companyID,@companyName,@CltCode ,@CltName ,@hDMemo,@ccCode ,@ccName ,@DistributorPoDocCode,@DistributorPoFormId,@uploads,@SumAmount,@HDCurrency,@HDCurrencyRate )\n" +
|
|
|
"---150344 期票\n" +
|
" insert into t150344(doccode,checkCode,checkType,refAcctCode,billDate,effDays,checkDate,checkCodeFlag,checkAmount,owner,Currency,CurrencyRate) values(@doccode,@checkCode,@checkType,@refAcctCode,@billDate,@effDays,@checkDate,@checkCodeFlag,@checkAmount150344,@owner,@Currency,@CurrencyRate)" +
|
|
" ---150348 账户明细\n" +
|
" declare @docitem int,@rowid varchar(50),@payType varchar(50),@accountCode varchar(50),@accountName varchar(50),@itemMemo varchar(150),@amount150348 money,@feeAmount money,@soDocCode varchar(50)\n";
|
for (T150348Entity t150348 : entity.getList150348()) {
|
|
sql += " select @docitem=isnull(@docitem,0) + 10,@payType =" + GridUtils.prossSqlParm(t150348.getPayType()) + ",@accountCode =" + GridUtils.prossSqlParm(t150348.getAccountCode()) + ",@accountName=" + GridUtils.prossSqlParm(t150348.getAccountName()) + " ,@itemMemo=" + GridUtils.prossSqlParm(t150348.getItemmemo()) + ",@amount150348=" + t150348.getAmount() + ",@feeAmount=" + t150348.getFeeAmount() + ",@Currency=" + GridUtils.prossSqlParm(t150348.getCurrency()) + ",@CurrencyRate=" + t150348.getCurrencyRate() + "\n" +
|
" exec getXXXX @rowId output\n" +
|
//TODO payType,accountCode,accountName 暂时为空
|
" insert into t150348(doccode,docitem,rowid,payType,accountCode,accountName,itemMemo,amount,feeAmount,Currency,CurrencyRate) values(@doccode,@docitem,@rowid,null,null,null,@itemMemo,@amount150348,@feeAmount,@Currency,@CurrencyRate)";
|
|
// " insert into t150348(doccode,docitem,rowid,payType,accountCode,accountName,itemMemo,amount,feeAmount,Currency,CurrencyRate) values(@doccode,@docitem,@rowid,@payType,@accountCode,@accountName,@itemMemo,@amount150348,@feeAmount,@Currency,@CurrencyRate)";
|
}
|
|
|
sql += " --执行保存\n" +
|
" exec p150170Save @doccode\n" +
|
" declare @PostFormId int, @DocStatusValue int,@PreDocStatus int,@PostDocStatus int,@Memo varchar(50), @LinkDocInfo varchar(50)\n" +
|
" select @PostFormId = FormId,@DocStatusValue = DocStatus from t150170H 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 t150170H a where a.DocCode = @docCode \n" +
|
" exec p150170Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
|
" @DocCode = @docCode,@FormId = @PostFormId,\n" +
|
" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
|
" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
|
" end \n" +
|
" select @docCode as docCode \n" +
|
" end else \n" +
|
" begin \n" +
|
" raiserror('%s客户预付款单已存在不能重复推送',16,1,@DistributorPoDocCode)\n" +
|
" return " +
|
" end \n";
|
return this.jdbcTemplate.queryForObject(sql, String.class);
|
}
|
|
@Transactional
|
@Override
|
public String do150101(T150201Entity entity) {
|
String sql = " set nocount on\n" +
|
" --表头变量\n" +
|
" declare @doccode varchar(50),@dbid int=" + entity.getCltDbid() + " ,@formid int=150101,@DistributorPoFormId int=150201,@PeriodID varchar(50)\n" +
|
" declare @now datetime=getdate()\n" +
|
" declare @Docdate date=convert(varchar(10),@now,120),@doctype varchar(50)='收款',\n" +
|
" @EnterCode varchar(50),@EnterName varchar(50),@EnterDate dateTime=@now,@ModifyName varchar(50),@ModifyDate dateTime=@now,@DocStatus int=0,@companyID varchar(50),@companyName varchar(150),@CltCode varchar(50),@CltName varchar(50),@PropertyAddress varchar(250),@clttype varchar(50),@hDMemo varchar(250)=" + GridUtils.prossSqlParm(entity.getHDMemo()) + ",@ccCode varchar(50),@ccName varchar(50),@tel varchar(50),@uploads varchar(200)=" + GridUtils.prossSqlParm(entity.getUploads()) + ",@DistributorPoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + ",@SumAmount money =" + entity.getAmount() + ",@checkAmount money =" + entity.getCheckAmount() + ",@checkCode varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckCode()) + ",@checkType varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckType()) + ",@refAcctCode varchar(50) =" + GridUtils.prossSqlParm(entity.getT150344().getRefAcctCode()) + " ,@billDate varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getBillDate()) + ",@effDays int=" + entity.getT150344().getEffDays() + ",@checkDate varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckDate()) + ",@checkCodeFlag varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCheckCodeFlag()) + ",@checkAmount150344 money=" + entity.getT150344().getCheckAmount() + ",@owner varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getOwner()) + ",@HDCurrency varchar(50)=" + GridUtils.prossSqlParm(entity.getHDCurrency()) + ",@HDCurrencyRate money=" + entity.getHDCurrencyRate() + ",@Currency varchar(50)=" + GridUtils.prossSqlParm(entity.getT150344().getCurrency()) + ",@CurrencyRate money=" + entity.getT150344().getCurrencyRate() + ",@bankCode varchar(50)=" + GridUtils.prossSqlParm(entity.getBankCode()) + ",@authenticatedCode varchar(50)="+GridUtils.prossSqlParm(entity.getAuthenticatedCode())+"\n" +
|
" \n--取客户资料\n" +
|
" select @EnterCode=cltCode,@EnterName=cltName,@ModifyName=cltName,@CltCode=cltCode,@CltName=cltName,@companyID=Authenticatedcompanyid,@companyName=Authenticatedcompanyname,@ccCode=Authenticatedcccode,@ccName=Authenticatedccname,@tel=Tel,@PropertyAddress=PropertyAddress,@clttype=clttype from t110203 where dbid=@dbid and authenticatedCode=@authenticatedCode \n" +
|
//是否已存在同客户同单号的采购订单
|
" if not exists(select 1 from t150101H where CltCode=@CltCode and DistributorPoDocCode=@DistributorPoDocCode) begin \n" +
|
" exec sp_newdoccode @formid,'SYSTEM',@doccode output\n" +
|
" select @PeriodId = dbo.GetPeriodID(@formid,@CompanyID,@now)\n" +
|
|
" insert into t150101H(doccode ,formid,PeriodID ,Docdate,doctype ,EnterCode ,EnterName ,EnterDate,ModifyName ,ModifyDate ,DocStatus,companyID,companyName,CltCode ,CltName ,PropertyAddress ,hDMemo,ccCode ,ccName,DistributorPoDocCode,DistributorPoFormId,Attachment,checkAmount,SumAmount,HDCurrency,HDCurrencyRate )values(@doccode ,@formid,@PeriodID ,@Docdate,@doctype ,@EnterCode ,@EnterName ,@EnterDate,@ModifyName ,@ModifyDate,@DocStatus,@companyID,@companyName,@CltCode ,@CltName ,@PropertyAddress ,@hDMemo,@ccCode ,@ccName ,@DistributorPoDocCode,@DistributorPoFormId,@uploads,@checkAmount,@SumAmount,@HDCurrency,@HDCurrencyRate )\n" +
|
|
|
"\n---150344 期票\n" +
|
" insert into t150344(doccode,checkCode,checkType,refAcctCode,billDate,effDays,checkDate,checkCodeFlag,checkAmount,owner,Currency,CurrencyRate) values(@doccode,@checkCode,@checkType,@refAcctCode,@billDate,@effDays,@checkDate,@checkCodeFlag,@checkAmount150344,@owner,@Currency,@CurrencyRate)" +
|
|
" \n---150348 账户明细\n" +
|
" declare @docitem int,@rowid varchar(50),@payType varchar(50),@accountCode varchar(50),@accountName varchar(50),@itemMemo varchar(150),@soDocCode varchar(50),@soFormId int,@amount150348 money,@SubAcctCode varchar(50),@pipeitype varchar(50),@cltCode15232 varchar(50),@cltName15232 varchar(50),@SellerCode varchar(50),@SellerName varchar(50),@ShopGuideCode varchar(50),@ShopGuideName varchar(50),@sql nvarchar(2000),@ParmDefinition nvarchar(2000),@tableName varchar(50)\n";
|
|
sql += " select @docitem=isnull(@docitem,0) + 10 \n" +
|
" select @SubAcctCode=SubAcctCode from t110706 where BankCode = @bankCode\n" +
|
" exec getXXXX @rowId output\n" +
|
" insert into t150348(doccode,docitem,rowid,accountCode,amount,NatAmount,Currency,CurrencyRate,companyid,companyname) values(@doccode,@docitem,@rowid,@SubAcctCode,@SumAmount,@SumAmount,@HDCurrency,@HDCurrencyRate,@companyid,@companyname) \n" +
|
" select @pipeitype=initValue from gfield where formid=150118 and HeadFlag=0 and fieldid='pipeitype' \n";
|
sql += " select @docitem=0 \n";
|
//---收款明细
|
for (T150232Entity t15232 : entity.getList150232()) {
|
|
sql += " select @docitem=isnull(@docitem,0) + 10,@itemMemo=" + GridUtils.prossSqlParm(t15232.getItemmemo()) + ",@amount150348=" + t15232.getAmount() + ",@soDocCode=" + GridUtils.prossSqlParm(t15232.getSODoccode()) + ",@soFormId=" + t15232.getSoFormId() + "\n" +
|
//----通过销售订单(120205、120201）取数，需要用动态sql拼接
|
" select @tableName=hdtable from gform where formid=@soFormId \n" +
|
" select @sql = ' select @cltCode15232=cltCode,@cltName15232=cltName,@SellerCode=SellerCode,@SellerName=SellerName,@ShopGuideCode=ShopGuideCode,@ShopGuideName=ShopGuideName from ' + isnull(@tableName,'') + ' a where a.docCode=@soDocCode '\n" +
|
" set @ParmDefinition = N'@cltCode15232 nvarchar(50) OUTPUT,@cltName15232 nvarchar(50) OUTPUT,@SellerCode nvarchar(50) OUTPUT,@SellerName nvarchar(50) OUTPUT,@ShopGuideCode nvarchar(50) OUTPUT,@ShopGuideName nvarchar(50) OUTPUT,@soDocCode nvarchar(50)'\n" +
|
" EXEC sp_executesql @sql , @ParmDefinition ,@cltCode15232 OUTPUT,@cltName15232 OUTPUT,@SellerCode OUTPUT,@SellerName OUTPUT,@ShopGuideCode OUTPUT,@ShopGuideName OUTPUT, @soDocCode \n" +
|
//--------
|
" exec getXXXX @rowId output\n" +
|
" insert into t150101D(doccode,docitem,rowid,itemMemo,amount,soDocCode,soFormId,cltCode,cltName,SellerCode,SellerName,ShopGuideCode,ShopGuideName,pipeitype) values(@doccode,@docitem,@rowid,@itemMemo,@amount150348,@soDocCode,@soFormId,@cltCode15232,@cltName15232,@SellerCode,@SellerName,@ShopGuideCode,@ShopGuideName,@pipeitype) \n";
|
}
|
|
sql += " --执行保存\n" +
|
" exec p150101Save @doccode\n" +
|
" declare @PostFormId int, @DocStatusValue int,@PreDocStatus int,@PostDocStatus int,@Memo varchar(50), @LinkDocInfo varchar(50)\n" +
|
" select @PostFormId = FormId,@DocStatusValue = DocStatus from t150101H 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 t150101H a where a.DocCode = @docCode \n" +
|
" exec p150101Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
|
" @DocCode = @docCode,@FormId = @PostFormId,\n" +
|
" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
|
" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
|
" end \n" +
|
" select @docCode as docCode \n" +
|
" end else \n" +
|
" begin \n" +
|
" raiserror('%s客户付款单已存在不能重复推送',16,1,@DistributorPoDocCode)\n" +
|
" return " +
|
" end \n";
|
// System.out.println(sql);
|
return this.jdbcTemplate.queryForObject(sql, String.class);
|
}
|
|
@Override
|
public T140201Entity getT140201(GateEntity entity, Task task) {
|
final FormEntity orginEntity = getFormEntityInfo(task.getAffectedFormId());//发货单
|
String sql = " set nocount on \n" +
|
" declare @cltCode varchar(50),@dbid int,@docCode varchar(50)=?,@logisticsCode varchar(50),@shipperCode varchar(50),@shipperName varchar(50),@logisticsCodeState varchar(50),@ReceipterTelephone varchar(50),@DistributorPoDocCode varchar(50) \n" +
|
" select @cltCode=a.cltCode,@dbid=e.dbid,@logisticsCode=a.logisticsCode,@shipperCode =a.shipperCode,@shipperName =a.shipperName,@logisticsCodeState=a.logisticsCodeState,@ReceipterTelephone =a.ReceipterTelephone,@DistributorPoDocCode=a.DistributorPoDocCode" +
|
" from " + orginEntity.getHDtable() + " a " +
|
" join t110203 e on e.CltCode=a.CltCode \n" +
|
" where a.DocCode=@docCode " +
|
" and isnull(e.dbid,0)<>0\n" +
|
" select @DistributorPoDocCode as docCode,@dbid as dbid,@logisticsCode as logisticsCode,@shipperCode as shipperCode,@shipperName as shipperName,@logisticsCodeState as logisticsCodeState,@ReceipterTelephone as receipterTelephone ";
|
|
return this.jdbcTemplate.queryForObject(
|
sql, new BeanPropertyRowMapper<>(T140201Entity.class), entity.getDocCode());
|
}
|
|
@Override
|
public T150201Entity getT150101(String docCode, Task task) {
|
return this.jdbcTemplate.queryForObject("set nocount on\n" +
|
" select a.distributorPoDocCode ,a.docCode,b.dbid as cltDbid from t150101H a join t110203 b on a.CltCode=b.cltCode where a.DocCode=?", new BeanPropertyRowMapper<>(T150201Entity.class), docCode);
|
}
|
|
@Override
|
public T150201Entity getT150170(String docCode) {
|
return this.jdbcTemplate.queryForObject("set nocount on\n" +
|
" select a.distributorPoDocCode ,a.docCode,b.dbid as cltDbid from t150170H a join t110203 b on a.CltCode=b.cltCode where a.DocCode=?", new BeanPropertyRowMapper<>(T150201Entity.class), docCode);
|
}
|
|
@Override
|
public void do150201(T150201Entity entity) {
|
String sql = " set nocount on \n " +
|
" declare @vndName varchar(50),@dbid int=" + entity.getVndDbid() + ",@docCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDistributorPoDocCode()) + ",@HeadqSoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + " \n" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callBackMsg=@vndName+'已通过您的付款申请',a.HeadqSoFormId=150101,a.HeadqSoDocCode=@HeadqSoDocCode from t150201H a where a.docCode=@docCode \n";
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Override
|
public void do150206(T150201Entity entity) {
|
String sql = " set nocount on \n " +
|
" declare @vndName varchar(50),@dbid int=" + entity.getVndDbid() + ",@docCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDistributorPoDocCode()) + ",@HeadqSoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + " \n" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callBackMsg=@vndName+'已通过您的预付款申请',a.HeadqSoFormId=150170,a.HeadqSoDocCode=@HeadqSoDocCode from t150206H a where a.docCode=@docCode \n";
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Override
|
public void update150201Status(T150201Entity entity) {
|
String sql = " set nocount on \n " +
|
" declare @vndName varchar(50),@dbid int=" + entity.getVndDbid() + ",@docCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + ",@HeadqSoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getHeadqSoDocCode()) + " \n" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callBackMsg=@vndName+'接收成功',a.HeadqSoDocCode=@HeadqSoDocCode,a.HeadqSoFormId=150101 from t150201H a where a.docCode=@docCode \n";
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Override
|
public void update150206Status(T150201Entity entity) {
|
String sql = " set nocount on \n " +
|
" declare @vndName varchar(50),@dbid int=" + entity.getVndDbid() + ",@docCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + ",@HeadqSoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getHeadqSoDocCode()) + " \n" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callBackMsg=@vndName+'接收成功',a.HeadqSoDocCode=@HeadqSoDocCode,a.HeadqSoFormId=150170 from t150206H a where a.docCode=@docCode \n";
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Override
|
public DataSourceEntity getDataSourceByDbid(String dbId) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" select systemID,systemDescribe,DBConnectionStr,Protocol,domain,smsUid,smsKey,isnull(isShowAttendanceButton,0),isnull(LimitUserNumber,0),weixinID,systemType,ExpiredDate ,isnull(LimitDepartmentNumber,0),Remarks,isnull(DataCheckPageNum,0),CorpId,CorpContactsSecret,CorpAppSecret,CorpAppToken,CorpAppAesKey,CorpURL,MpAppId,MpSecret,MpToken,MpAesKey,MpOrgId,GeoWebApiKey,MpMchId,MpMchName,MpMchKey,isnull(CorpAppAgentId,0),isnull(isDisabledOpenNextPeriodId,0),InvitationCode,CorpAttendanceSecret,CorpSecret,CorpGroupSecret,CorpGroupName,MiniAppId,MiniAppSecret,MiniAppToken,MiniAppAesKey,MiniAppOrgId,SystemAccessKey,SystemSecretKey,DockingSystem,isnull(isUseAPP,0),isnull(isShowInLoginPage,0),OrgPassword,LogoIcon,TengXunMapLocationServiceKey,PaymentMethod,AbcMchId,DomainIpList,DomainStaticIpList,AliPayAppId,isnull(MaintenanceFeeToDbId,0),MaintenanceFeeAcctCode,deliUserId,deliUserCode from gsystem where id=?", new BeanPropertyRowMapper<>(DataSourceEntity.class), dbId);
|
}
|
|
@Override
|
public void update130301Status(T120201Entity entity) {
|
String sql = " set nocount on \n " +
|
" declare @vndName varchar(50),@dbid int=" + entity.getVndDbid() + ",@docCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + ",@HeadqSoFormId int=" + entity.getHeadqSoFormId() + ",@HeadqSoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getHeadqSoDocCode()) + " \n" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callBackMsg=@vndName+'接收成功',a.HeadqSoDocCode=@HeadqSoDocCode,a.HeadqSoFormId=@HeadqSoFormId from t130301H a where a.docCode=@docCode \n";
|
//log.info("update130301Status:"+sql);
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Transactional
|
@Override
|
public void do130301(T120201Entity entity) {
|
if (entity == null) {
|
throw new ApplicationException("回传的销售数据为空");
|
}
|
String sql = " set nocount on \n " +
|
" declare @vndName varchar(50),@dbid int=" + entity.getVndDbid() + ",@docCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDistributorPoDocCode()) + ",@HeadqSoDocCode varchar(50)=" + GridUtils.prossSqlParm(entity.getDocCode()) + ",@hdMemo varchar(100)= " + GridUtils.prossSqlParm(entity.getHDMemo()) + ",@uploads varchar(100)=" + GridUtils.prossSqlParm(entity.getUploads()) + "\n" +
|
" declare @matCode varchar(50),@price money,@Rowid varchar(50)" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callBackMsg=@vndName+'已通过您的采购申请',a.HeadqSoDocCode=@HeadqSoDocCode,a.HDMemo=@hdMemo,a.uploads=@uploads from t130301H a where a.docCode=@docCode \n";
|
|
for (T120201DetailsEntity detailsEntity : entity.getDetails()) {
|
sql += " select @matCode=null,@price=null,@Rowid=null \n" +
|
" select @matCode=" + GridUtils.prossSqlParm(detailsEntity.getMatCode()) + ",@price=" + detailsEntity.getPrice() + ",@Rowid=" + GridUtils.prossSqlParm(detailsEntity.getRefRowid()) + " \n" +
|
" update a set a.price=@price,a.MoneyType=7 from t130301D a where a.matcode=@matCode and a.Rowid=@Rowid and a.doccode=@docCode \n";
|
}
|
|
sql += " --执行保存\n" +
|
" exec p130301Save @docCode\n" +
|
" declare @PostFormId int, @DocStatusValue int,@PreDocStatus int,@PostDocStatus int,@Memo varchar(50), @LinkDocInfo varchar(50),@EnterCode varchar(50)= 'SYSTEM',@EnterName varchar(50)='管理员'\n" +
|
" select @PostFormId = FormId,@DocStatusValue = DocStatus from t130301H 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=getdate()\n" +
|
" from t130301H a where a.DocCode = @docCode \n" +
|
" exec p130301Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
|
" @DocCode = @docCode,@FormId = @PostFormId,\n" +
|
" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
|
" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
|
" end";
|
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Transactional
|
@Override
|
public Integer do140201(T140201Entity t140201Entity) {
|
return this.jdbcTemplate.queryForObject(" set nocount on \n " +
|
" declare @vndName varchar(100),@dbid int=?,@logisticsCode varchar(50)=?,@shipperCode varchar(50)=?,@shipperName varchar(50)=?,@logisticsCodeState varchar(50)=?,@docCode varchar(50)=?,@ReceipterTelephone varchar(50)=? \n" +
|
" select @vndName=vndName from t110302 where dbid=@dbid \n" +
|
" update a set a.callbackMsg=@vndName+'已发货您的采购申请',a.logisticsCode=@logisticsCode,a.shipperCode=@shipperCode ,a.shipperName=@shipperName ,a.logisticsCodeState=@logisticsCodeState,a.ReceipterTelephone=@ReceipterTelephone from t130301H a where a.docCode=@docCode \n select @@rowcount", Integer.class, t140201Entity.getRefDbId(),t140201Entity.getLogisticsCode(), t140201Entity.getShipperCode(), t140201Entity.getShipperName(), t140201Entity.getLogisticsCodeState(), t140201Entity.getDocCode(), t140201Entity.getReceipterTelephone());
|
}
|
|
@Transactional
|
@Override
|
public Integer updateLogisticState(TranceEntity tranceEntity) {
|
return this.getJdbcTemplate().queryForObject("set nocount on\n" +
|
" declare @sql nvarchar(2000),@ParmDefinition nvarchar(2000) ,@myrowcount INT\n" +
|
" declare @formid int=?,@docCode varchar(50)=?,@logisticsCodeState varchar(50)=?,@tableName varchar(50)\n" +
|
" select @tableName=hdtable from gform where formid=@formid \n" +
|
" select @sql = ' update a set a.logisticsCodeState=@logisticsCodeState from ' + isnull(@tableName,'') + ' a where a.docCode=@docCode '\n select @myrowcount=@@rowcount \n" +
|
" set @ParmDefinition = N'@myrowcount INT OUTPUT,@DOCCODE nvarchar(2000),@logisticsCodeState varchar(50)'\n" +
|
" EXEC sp_executesql @sql , @ParmDefinition ,@myrowcount OUTPUT, @DOCCODE,@logisticsCodeState \n" +
|
" SELECT @myrowcount", Integer.class, tranceEntity.getFormid(), tranceEntity.getDocCode(), tranceEntity.getLogisticsState());
|
}
|
|
/**
|
* 根据功能号返回所属类型及主从表名
|
* 120301,120386,120385,120384,120383,120382,120381,120380
|
*
|
* @param formid
|
* @return
|
*/
|
private FormEntity getFormEntityInfo(int formid) {
|
return this.jdbcTemplate.queryForObject(" select hdtable,dttable from gform where formid=?", new RowMapper<FormEntity>() {
|
@Override
|
public FormEntity mapRow(ResultSet resultSet, int i) throws SQLException {
|
FormEntity orderEntity = new FormEntity();
|
orderEntity.setHDtable(resultSet.getString("hdtable"));
|
orderEntity.setDDtable(resultSet.getString("dttable"));
|
orderEntity.setFormid(formid);
|
return orderEntity;
|
}
|
}, formid);
|
// FormEntity entity = new FormEntity();
|
// entity.setHDtable("t"+formid+"H");
|
// entity.setDDtable("t"+formid+"D");
|
// entity.setFormid(formid);
|
// return entity;
|
}
|
|
@Transactional
|
@Override
|
public String do120201(T130301Entity t130301Entity) {
|
//1,增加对重复单据的判断
|
//2,修改为取得需要由采购订单生成的功能号
|
final FormEntity orderEntity = getFormEntityInfo(t130301Entity.getAffectedFormIdForTargetDatabase());
|
String sql = " set nocount on\n" +
|
" --表头变量\n" +
|
" declare @doccode varchar(50),@dbid int=" + t130301Entity.getCltDbid() + " ,@formid int=" + orderEntity.getFormid() + ",@refcodeformid int=130301,@PeriodID varchar(50)\n" +
|
" declare @now datetime=getdate()\n" +
|
" declare @Docdate date=convert(varchar(10),@now,120),@doctype varchar(50)='门店单',@authenticatedCode varchar(50)="+GridUtils.prossSqlParm(t130301Entity.getAuthenticatedCode())+",\n" +
|
" @EnterCode varchar(50),@EnterName varchar(50),@EnterDate dateTime=@now,@ModifyName varchar(50),@ModifyDate dateTime=@now,@DocStatus int=0,@companyID varchar(50),@companyName varchar(150),@CltCode varchar(50),@CltName varchar(50),@PropertyAddress varchar(250),@clttype varchar(50),@hDMemo varchar(250)=" + GridUtils.prossSqlParm(t130301Entity.getHDMemo()) + ",@ccCode varchar(50),@ccName varchar(50),@tel varchar(50),@uploads varchar(200)=" + GridUtils.prossSqlParm(t130301Entity.getUploads()) + ",@DistributorPoDocCode varchar(50)=" + GridUtils.prossSqlParm(t130301Entity.getRefCode()) + ",@hdcurrency varchar(10)=" + GridUtils.prossSqlParm(t130301Entity.getHdcurrency()) + ",@hdcurrencyrate money=" + t130301Entity.getHdcurrencyrate() +
|
" declare @DealAfterDocSaves varchar(max) \n" +
|
" declare @DealAfterDocSaveTable table(DealAfterDocSave varchar(200)) \n" +
|
" --取客户资料\n" +
|
" select @EnterCode=cltCode,@EnterName=cltName,@ModifyName=cltName,@CltCode=cltCode,@CltName=cltName,@companyID=Authenticatedcompanyid,@companyName=Authenticatedcompanyname,@ccCode=Authenticatedcccode,@ccName=Authenticatedccname,@tel=Tel,@PropertyAddress=PropertyAddress,@clttype=clttype from t110203 where dbid=@dbid and authenticatedCode=@authenticatedCode \n" +
|
" if @@rowcount=0 begin \n" +
|
" raiserror('查找不到数据源dbid为%d的客户信息,请确保推送方与接收方系统进行过相互认证',16,1,@dbid);return \n" +
|
" end \n" +
|
//是否已存在同客户同单号的采购订单
|
" if not exists(select 1 from " + orderEntity.getHDtable() + " where CltCode=@CltCode and DistributorPoDocCode=@DistributorPoDocCode) begin \n" +
|
" exec sp_newdoccode @formid,'SYSTEM',@doccode output\n" +
|
" select @PeriodId = dbo.GetPeriodID(@formid,@CompanyID,@now)\n" +
|
" insert into " + orderEntity.getHDtable() + "(doccode ,formid,PeriodID ,Docdate,doctype ,EnterCode ,EnterName ,EnterDate,ModifyName ,ModifyDate ,DocStatus,companyID,companyName,CltCode ,CltName ,PropertyAddress,clttype ,hDMemo,ccCode ,ccName ,tel,DistributorPoDocCode,DistributorPoFormId,refformid,uploads,hdcurrency,hdcurrencyrate )values(@doccode ,@formid,@PeriodID ,@Docdate,@doctype ,@EnterCode ,@EnterName ,@EnterDate,@ModifyName ,@ModifyDate,@DocStatus,@companyID,@companyName,@CltCode ,@CltName ,@PropertyAddress,@clttype ,@hDMemo,@ccCode ,@ccName ,@tel,@DistributorPoDocCode,130301,@refcodeformid,@uploads,@hdcurrency,@hdcurrencyrate)\n" +
|
" ---表体变量\n" +
|
" declare @docitem int,@rowid varchar(50),@matCode varchar(50),@price money,@digit money,@cv2 varchar(50),@tCcode varchar(50),@itemMemo varchar(150),@matName varchar(50),\n" +
|
" @Barcode varchar(50),@Brand varchar(50),@Stcode varchar(50),@stname varchar(50),@UOM varchar(50),@baseuom varchar(50),@netprice money,@TrademarkPrice money,@special varchar(50),@weight money,@matgroup varchar(50),@matgroupname varchar(50),@TCname varchar(50) ,@TaxPrice money ,@pricetype varchar(50) ,@totalmoney2 money,@discount money,@whcode varchar(50) ,@refcodeitem varchar(50) ,@refrowid varchar(50) ,@Pricerefcodeitem varchar(50) ,@Pricerefrowid varchar(50) ,@Pricerefcodeformid varchar(50),@MoneyType int=7,@cv1 varchar(50)\n" +
|
" declare @errMsg varchar(2000),@myrowcount int,@myerror int,@ObjMatCode varchar(50),@ObjMatName varchar(50)\n" +
|
" ---循环拼接,先把变量都清空\n";
|
final List<T130301DetailEntity> details = t130301Entity.getDetails();
|
for (T130301DetailEntity entity : details) {
|
sql += " select @errMsg=null,@rowId=null,@matCode =null,@price =null,@cv2 =null,@tCcode =null,@itemMemo=null,@matName =null,\n" +
|
" @Barcode =null,@Brand =null,@Stcode =null,@stname =null,@UOM =null,@baseuom =null,@netprice =null,@TrademarkPrice =null,@special =null,@weight =null,@matgroup =null,@matgroupname =null,@TCname =null ,@TaxPrice =null ,@pricetype =null ,@totalmoney2=null,@discount =null,@whcode =null ,@Pricerefcodeitem =null ,@Pricerefrowid =null ,@Pricerefcodeformid =null\n" +
|
" select @MatCode ='" + entity.getMatCode() + "', @MatName ='" + entity.getMatName() + "',@refcodeitem ='" + t130301Entity.getRefCode() + "',@refrowid ='" + entity.getRowid() + "',@digit=" + entity.getDigit() + "\n" +
|
"begin\n" +
|
" ---由物料取相关内容写入表体t120201D\n" +
|
" select @Barcode=Barcode,@Brand=brand,@Stcode=stcode,@stname=stname,@uom=baseuom,@baseuom=baseuom,@netprice=SalesPrice,@TrademarkPrice=SalesPrice,@price=SalesPrice,@TaxPrice=SalesPrice,@weight=BaseUomWeight,@matgroup=matgroup,@cv1=baseuomrate from v110503 where MatCode=@matCode \n" +
|
" select @docitem=isnull(@docitem,0) + 10\n" +
|
" exec getXXXX @rowId output\n" +
|
" insert into " + orderEntity.getDDtable() + "(docCode,docitem,rowid,matCode,price,digit,cv2,tCcode,itemMemo,matName,Barcode,Brand,Stcode,stname,UOM,baseuom,netprice,TrademarkPrice,special,weight,matgroup,matgroupname,TCname ,TaxPrice ,pricetype ,totalmoney2 ,discount ,whcode ,refcodeitem ,refrowid ,refcodeformid ,MoneyType)values(@doccode,@docitem,@rowid,@matCode,@price,@digit,@cv2,@tCcode,@itemMemo,@matName,@Barcode,@Brand,@Stcode,@stname,@UOM,@baseuom,@netprice,@TrademarkPrice,@special,@weight,@matgroup,@matgroupname,@TCname ,@TaxPrice ,@pricetype ,@totalmoney2 ,@discount ,@whcode ,@refcodeitem ,@refrowid ,@refcodeformid ,@MoneyType) \n" +
|
" end\n";
|
}
|
sql += " --执行保存\n" +
|
" insert into @DealAfterDocSaveTable(DealAfterDocSave) \n" +
|
" select DealAfterDocSave from gform where formid = " + orderEntity.getFormid() + " and isnull(DealAfterDocSave,'') <> '' \n" +
|
" union \n" +
|
" select ProcName from _sys_FormProc where formid = " + orderEntity.getFormid() + "\n" +
|
" and action in ('insert','update') and objectType = 'header' and isnull(ProcName,'') <> '' \n" +
|
" update a set DealAfterDocSave = trim(replace(DealAfterDocSave,' ',' ')) from @DealAfterDocSaveTable a \n" +
|
" update a set DealAfterDocSave = replace(DealAfterDocSave,'doccode',''''+isnull(@doccode,'') + '''') \n" +
|
" from @DealAfterDocSaveTable a \n" +
|
" select @DealAfterDocSaves = isnull(@DealAfterDocSaves,'') + 'exec '+ isnull(DealAfterDocSave,'') + '; ' \n" +
|
" from (select distinct DealAfterDocSave from @DealAfterDocSaveTable) a \n" +
|
" if isnull(@DealAfterDocSaves,'') <> '' \n" +
|
" begin \n" +
|
" exec(@DealAfterDocSaves) \n" +
|
" end \n" +
|
" declare @PostFormId int, @DocStatusValue int,@PreDocStatus int,@PostDocStatus int,@Memo varchar(50), @LinkDocInfo varchar(50)\n" +
|
" select @PostFormId = FormId,@DocStatusValue = DocStatus from " + orderEntity.getHDtable() + " 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 " + orderEntity.getHDtable() + " a where a.DocCode = @docCode \n" +
|
" exec p" + orderEntity.getFormid() + "Post @UserCode = @EnterCode,@UserName = @EnterName, \n" +
|
" @DocCode = @docCode,@FormId = @PostFormId,\n" +
|
" @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n" +
|
" @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n" +
|
" end\n" +
|
" select @docCode as docCode \n " +
|
" end else \n" +
|
" begin \n" +
|
" raiserror('%s采购订单已存在不能重复推送',16,1,@DistributorPoDocCode)\n" +
|
" return " +
|
" end \n";
|
//log.info("120201sql:"+sql);
|
return this.jdbcTemplate.queryForObject(sql, String.class);
|
}
|
|
@Override
|
public Map getCompanyInfo() {
|
List company = this.getJdbcTemplate().queryForList("select companyid,CompanyName from oCompany");
|
List department = this.getJdbcTemplate().queryForList("select cccode,ccname from t110601");
|
Map<String, List> map = new HashMap<>();
|
map.put("company", company);
|
map.put("department", department);
|
return map;
|
}
|
|
@Transactional
|
@Override
|
public Integer do170424(List<T170424Entity> list) {
|
String sql = "set nocount on \n" +
|
" --保存客户系统活跃记录到 t170424 表\n" +
|
" declare @SystemDescribe varchar(150)=null\n" +
|
" declare @DatabaseName varchar(50) = null,@dbid int = null\n" +
|
" declare @LogonTimes int= null,@CustomerCount int = null,@TraceCustomerCount int = null\n" +
|
" declare @SalesDocumentCount int = null,@SalesDocumentDigit money= null,@SalesDocumentAmount money= null\n" +
|
" declare @PickupDocumentCount int = null,@PickupDocumentDigit money= null,@PickupDocumentAmount money= null\n" +
|
" declare @DeliveryDocumentCount int = null,@DeliveryDocumentDigit money= null,@DeliveryDocumentAmount money= null\n" +
|
" declare @PurchaseDocumentCount int = null,@PurchaseDocumentDigit money= null,@PurchaseDocumentAmount money= null\n" +
|
" declare @ReceiptDocumentCount int = null,@ReceiptDocumentDigit money= null,@ReceiptDocumentAmount money= null\n" +
|
" declare @PaidDocumentCount int = null,@PaidDocumentAmount money= null\n" +
|
" declare @TransferDocumentCount int = null,@TransferDocumentAmount money= null\n" +
|
" declare @GeneralExpenseDocumentCount int = null,@GeneralExpenseDocumentAmount money= null\n" +
|
" declare @IncomeDocumentCount int = null,@IncomeDocumentAmount money= null\n" +
|
" declare @FeeBillsReceivableDocumentCount int = null,@FeeBillsReceivableDocumentAmount money= null\n" +
|
" declare @FeePaymentDocumentCount int = null,@FeePaymentDocumentAmount money= null\n" +
|
" declare @FeeExpenseDocumentCount int = null,@FeeExpenseDocumentAmount money= null\n" +
|
" declare @FeeIncomeDocumentCount int = null,@FeeIncomeDocumentAmount money= null\n" +
|
" declare @TransferVoucherDocumentCount int = null,@TransferVoucherDocumentAmount money= null\n" +
|
" declare @InsertTime datetime = getdate()\n" +
|
" declare @CheckDocDate datetime \n" +
|
" declare @DataSize int,@LogSize int,@TotalSize int,@DataFreeSpace int,@LogFreeSpace int,@TotalFreeSpace int ,@activated int \n";
|
for (T170424Entity entity : list) {
|
sql += " select @SystemDescribe=" + GridUtils.prossSqlParm(entity.getSystemDescribe()) + ",@DatabaseName=" + GridUtils.prossSqlParm(entity.getDatabaseName()) + ",@dbid=" + entity.getDbid() + ",@CheckDocDate = " + GridUtils.prossSqlParm(entity.getCheckDocDate()) + "\n" +
|
" select @LogonTimes = " + entity.getLogonTimes() + ",@CustomerCount = " + entity.getCustomerCount() + ",@TraceCustomerCount = " + entity.getTraceCustomerCount() + "\n" +
|
" select @SalesDocumentCount = " + entity.getSalesDocumentCount() + ",@SalesDocumentDigit = " + entity.getSalesDocumentDigit() + ",@SalesDocumentAmount = " + entity.getSalesDocumentAmount() + "\n" +
|
" select @PickupDocumentCount = " + entity.getPickupDocumentCount() + ",@PickupDocumentDigit = " + entity.getPickupDocumentDigit() + ",@PickupDocumentAmount = " + entity.getPickupDocumentAmount() + "\n" +
|
" select @DeliveryDocumentCount = " + entity.getDeliveryDocumentCount() + ",@DeliveryDocumentDigit = " + entity.getDeliveryDocumentDigit() + ",@DeliveryDocumentAmount = " + entity.getDeliveryDocumentAmount() + "\n" +
|
" select @PurchaseDocumentCount = " + entity.getPurchaseDocumentCount() + ",@PurchaseDocumentDigit = " + entity.getPurchaseDocumentDigit() + ",@PurchaseDocumentAmount = " + entity.getPurchaseDocumentAmount() + "\n" +
|
" select @ReceiptDocumentCount = " + entity.getReceiptDocumentCount() + ",@ReceiptDocumentDigit = " + entity.getReceiptDocumentDigit() + ",@ReceiptDocumentAmount = " + entity.getReceiptDocumentAmount() + "\n" +
|
" select @PaidDocumentCount = " + entity.getPaidDocumentCount() + ",@PaidDocumentAmount = " + entity.getPaidDocumentAmount() + "\n" +
|
" select @TransferDocumentCount = " + entity.getTransferDocumentCount() + ",@TransferDocumentAmount = " + entity.getTransferDocumentAmount() + "\n" +
|
" select @GeneralExpenseDocumentCount = " + entity.getGeneralExpenseDocumentCount() + ",@GeneralExpenseDocumentAmount = " + entity.getGeneralExpenseDocumentAmount() + "\n" +
|
" select @IncomeDocumentCount = " + entity.getIncomeDocumentCount() + ",@IncomeDocumentAmount = " + entity.getIncomeDocumentAmount() + "\n" +
|
" select @FeeBillsReceivableDocumentCount = " + entity.getFeeBillsReceivableDocumentCount() + ",@FeeBillsReceivableDocumentAmount = " + entity.getFeeBillsReceivableDocumentAmount() + "\n" +
|
" select @FeePaymentDocumentCount = " + entity.getFeePaymentDocumentCount() + ",@FeePaymentDocumentAmount = " + entity.getFeePaymentDocumentAmount() + "\n" +
|
" select @FeeExpenseDocumentCount = " + entity.getFeeExpenseDocumentCount() + ",@FeeExpenseDocumentAmount = " + entity.getFeeExpenseDocumentAmount() + "\n" +
|
" select @FeeIncomeDocumentCount = " + entity.getFeeIncomeDocumentCount() + ",@FeeIncomeDocumentAmount = " + entity.getFeeIncomeDocumentAmount() + "\n" +
|
" select @TransferVoucherDocumentCount = " + entity.getTransferVoucherDocumentCount() + ",@TransferVoucherDocumentAmount = " + entity.getTransferVoucherDocumentAmount() + "\n" +
|
" select @DataSize = " + entity.getDataSize() + ",@LogSize = " + entity.getLogSize() + ",@TotalSize = " + entity.getTotalSize() + ",@DataFreeSpace = " + entity.getDataFreeSpace() + ",@LogFreeSpace = " + entity.getLogFreeSpace() + ",@TotalFreeSpace = " + entity.getTotalFreeSpace() + ",@activated=" + entity.getActivated() + " \n" +
|
" \n" +
|
" update a set LogonTimes = @LogonTimes,CustomerCount = @CustomerCount,TraceCustomerCount = @TraceCustomerCount,\n" +
|
" SalesDocumentCount = @SalesDocumentCount,SalesDocumentDigit = @SalesDocumentDigit,SalesDocumentAmount = @SalesDocumentAmount,\n" +
|
" PickupDocumentCount = @PickupDocumentCount,PickupDocumentDigit = @PickupDocumentDigit,PickupDocumentAmount = @PickupDocumentAmount,\n" +
|
" DeliveryDocumentCount = @DeliveryDocumentCount,DeliveryDocumentDigit = @DeliveryDocumentDigit,DeliveryDocumentAmount = @DeliveryDocumentAmount,\n" +
|
" PurchaseDocumentCount = @PurchaseDocumentCount,PurchaseDocumentDigit = @PurchaseDocumentDigit,PurchaseDocumentAmount = @PurchaseDocumentAmount,\n" +
|
" ReceiptDocumentCount = @ReceiptDocumentCount,ReceiptDocumentDigit = @ReceiptDocumentDigit,ReceiptDocumentAmount = @ReceiptDocumentAmount,\n" +
|
" PaidDocumentCount = @PaidDocumentCount,PaidDocumentAmount = @PaidDocumentAmount,\n" +
|
" TransferDocumentCount = @TransferDocumentCount,TransferDocumentAmount = @TransferDocumentAmount,\n" +
|
" GeneralExpenseDocumentCount = @GeneralExpenseDocumentCount,GeneralExpenseDocumentAmount = @GeneralExpenseDocumentAmount,\n" +
|
" IncomeDocumentCount = @IncomeDocumentCount,IncomeDocumentAmount = @IncomeDocumentAmount,\n" +
|
" FeeBillsReceivableDocumentCount = @FeeBillsReceivableDocumentCount,FeeBillsReceivableDocumentAmount = @FeeBillsReceivableDocumentAmount,\n" +
|
" FeePaymentDocumentCount = @FeePaymentDocumentCount,FeePaymentDocumentAmount = @FeePaymentDocumentAmount,\n" +
|
" FeeExpenseDocumentCount = @FeeExpenseDocumentCount,@FeeExpenseDocumentAmount = null,\n" +
|
" FeeIncomeDocumentCount = @FeeIncomeDocumentCount,FeeIncomeDocumentAmount = @FeeIncomeDocumentAmount,\n" +
|
" TransferVoucherDocumentCount = @TransferVoucherDocumentCount,TransferVoucherDocumentAmount = @TransferVoucherDocumentAmount,activated=@activated\n" +
|
" from t170424 a\n" +
|
" where a.CheckDocDate = @CheckDocDate and a.dbid = @dbid \n" +
|
" if @@ROWCOUNT = 0\n" +
|
" begin \n" +
|
" insert into t170424(CheckDocDate , LogonTimes ,CustomerCount ,TraceCustomerCount ,\n" +
|
" SalesDocumentCount ,SalesDocumentDigit ,SalesDocumentAmount ,\n" +
|
" PickupDocumentCount ,PickupDocumentDigit ,PickupDocumentAmount ,\n" +
|
" DeliveryDocumentCount ,DeliveryDocumentDigit ,DeliveryDocumentAmount ,\n" +
|
" PurchaseDocumentCount ,PurchaseDocumentDigit ,PurchaseDocumentAmount ,\n" +
|
" ReceiptDocumentCount ,ReceiptDocumentDigit ,ReceiptDocumentAmount ,\n" +
|
" PaidDocumentCount ,PaidDocumentAmount ,\n" +
|
" TransferDocumentCount ,TransferDocumentAmount ,\n" +
|
" GeneralExpenseDocumentCount ,GeneralExpenseDocumentAmount ,\n" +
|
" IncomeDocumentCount ,IncomeDocumentAmount ,\n" +
|
" FeeBillsReceivableDocumentCount ,FeeBillsReceivableDocumentAmount ,\n" +
|
" FeePaymentDocumentCount ,FeePaymentDocumentAmount ,\n" +
|
" FeeExpenseDocumentCount ,FeeExpenseDocumentAmount ,\n" +
|
" FeeIncomeDocumentCount ,FeeIncomeDocumentAmount,\n" +
|
" TransferVoucherDocumentCount ,TransferVoucherDocumentAmount ,\n" +
|
" InsertTime,SystemDescribe,DatabaseName ,dbid,DataSize,LogSize,TotalSize,DataFreeSpace,LogFreeSpace,TotalFreeSpace,activated)\n" +
|
" values(@CheckDocDate , @LogonTimes ,@CustomerCount ,@TraceCustomerCount ,\n" +
|
" @SalesDocumentCount ,@SalesDocumentDigit ,@SalesDocumentAmount ,\n" +
|
" @PickupDocumentCount ,@PickupDocumentDigit ,@PickupDocumentAmount ,\n" +
|
" @DeliveryDocumentCount ,@DeliveryDocumentDigit ,@DeliveryDocumentAmount ,\n" +
|
" @PurchaseDocumentCount ,@PurchaseDocumentDigit ,@PurchaseDocumentAmount ,\n" +
|
" @ReceiptDocumentCount ,@ReceiptDocumentDigit ,@ReceiptDocumentAmount ,\n" +
|
" @PaidDocumentCount ,@PaidDocumentAmount ,\n" +
|
" @TransferDocumentCount ,@TransferDocumentAmount ,\n" +
|
" @GeneralExpenseDocumentCount ,@GeneralExpenseDocumentAmount ,\n" +
|
" @IncomeDocumentCount ,@IncomeDocumentAmount ,\n" +
|
" @FeeBillsReceivableDocumentCount ,@FeeBillsReceivableDocumentAmount ,\n" +
|
" @FeePaymentDocumentCount ,@FeePaymentDocumentAmount ,\n" +
|
" @FeeExpenseDocumentCount ,@FeeExpenseDocumentAmount ,\n" +
|
" @FeeIncomeDocumentCount ,@FeeIncomeDocumentAmount,\n" +
|
" @TransferVoucherDocumentCount ,@TransferVoucherDocumentAmount ,\n" +
|
" getdate(),@SystemDescribe,@DatabaseName ,@dbid,@DataSize,@LogSize,@TotalSize,@DataFreeSpace,@LogFreeSpace,@TotalFreeSpace,@activated)\n" +
|
" end \n";
|
}
|
//log.info("save170424sql:"+sql);
|
return this.jdbcTemplate.update(sql);
|
}
|
|
@Override
|
public Integer getCheckDays(Integer dbid) {
|
return this.jdbcTemplate.queryForObject("set nocount on\n" +
|
" declare @LastCheckDocDate datetime,@dbid int = ? ,@CheckDays int = 30\n" +
|
" select @LastCheckDocDate = max(CheckDocDate) from t170424 where dbid = @dbid; \n" +
|
" if @LastCheckDocDate is not null set @CheckDays = datediff(day,@LastCheckDocDate,getdate())\n" +
|
" select @CheckDays as CheckDays", Integer.class, dbid);
|
}
|
|
@Override
|
public List<T170424Entity> get170424List(DataSourceEntity dataSource, String checkDays) {
|
int activated = 1;
|
Integer expiredDays = (dataSource.getExpiredDays() == null ? null : dataSource.getExpiredDays());//过期天数：null或负数表示未过期，正数表示已过期
|
boolean isActivated = dataSource.getActived();
|
if (expiredDays != null && expiredDays.intValue() > 0 || !isActivated) {
|
activated = 0;
|
}
|
String sql = " set nocount on\n" +
|
" declare @CheckDays int =" + (Integer.parseInt(checkDays)+1) + " \n" +
|
" declare @SystemDescribe varchar(150)=" + GridUtils.prossSqlParm(dataSource.getSystemDescribe()) + "\n" +
|
" declare @DatabaseName varchar(50) = " + GridUtils.prossSqlParm(dataSource.getDb()) + ",@dbid int = " + dataSource.getDbId() + "\n" +
|
" declare @LogonTimes int,@CustomerCount int,@TraceCustomerCount int\n" +
|
" declare @SalesDocumentCount int,@SalesDocumentDigit money,@SalesDocumentAmount money\n" +
|
" declare @PickupDocumentCount int,@PickupDocumentDigit money,@PickupDocumentAmount money\n" +
|
" declare @DeliveryDocumentCount int,@DeliveryDocumentDigit money,@DeliveryDocumentAmount money\n" +
|
" declare @PurchaseDocumentCount int,@PurchaseDocumentDigit money,@PurchaseDocumentAmount money\n" +
|
" declare @ReceiptDocumentCount int,@ReceiptDocumentDigit money,@ReceiptDocumentAmount money\n" +
|
" declare @PaidDocumentCount int,@PaidDocumentAmount money\n" +
|
" declare @TransferDocumentCount int,@TransferDocumentAmount money\n" +
|
" declare @GeneralExpenseDocumentCount int,@GeneralExpenseDocumentAmount money\n" +
|
" declare @IncomeDocumentCount int,@IncomeDocumentAmount money\n" +
|
" declare @FeeBillsReceivableDocumentCount int,@FeeBillsReceivableDocumentAmount money\n" +
|
" declare @FeePaymentDocumentCount int,@FeePaymentDocumentAmount money\n" +
|
" declare @FeeExpenseDocumentCount int,@FeeExpenseDocumentAmount money\n" +
|
" declare @FeeIncomeDocumentCount int,@FeeIncomeDocumentAmount money\n" +
|
" declare @TransferVoucherDocumentCount int,@TransferVoucherDocumentAmount money\n" +
|
" declare @InsertTime datetime = getdate()\n" +
|
" declare @CheckDocDate datetime ,@Beginday datetime ,@Endday datetime \n" +
|
" declare @i int = 0\n" +
|
" declare @DataSize int,@LogSize int,@TotalSize int,@DataFreeSpace int,@LogFreeSpace int,@TotalFreeSpace int,@Activated int=" + activated + " \n" +
|
" declare @table table(CheckDocDate datetime primary key, LogonTimes int ,CustomerCount int,TraceCustomerCount int,\n" +
|
" SalesDocumentCount int,SalesDocumentDigit money,SalesDocumentAmount money,\n" +
|
" PickupDocumentCount int,PickupDocumentDigit money,PickupDocumentAmount money,\n" +
|
" DeliveryDocumentCount int,DeliveryDocumentDigit money,DeliveryDocumentAmount money,\n" +
|
" PurchaseDocumentCount int,PurchaseDocumentDigit money,PurchaseDocumentAmount money,\n" +
|
" ReceiptDocumentCount int,ReceiptDocumentDigit money,ReceiptDocumentAmount money,\n" +
|
" PaidDocumentCount int,PaidDocumentAmount money,\n" +
|
" TransferDocumentCount int,TransferDocumentAmount money,\n" +
|
" GeneralExpenseDocumentCount int,GeneralExpenseDocumentAmount money,\n" +
|
" IncomeDocumentCount int,IncomeDocumentAmount money,\n" +
|
" FeeBillsReceivableDocumentCount int,FeeBillsReceivableDocumentAmount money,\n" +
|
" FeePaymentDocumentCount int,FeePaymentDocumentAmount money,\n" +
|
" FeeExpenseDocumentCount int,FeeExpenseDocumentAmount money,\n" +
|
" FeeIncomeDocumentCount int,FeeIncomeDocumentAmount money,\n" +
|
" TransferVoucherDocumentCount int,TransferVoucherDocumentAmount money,\n" +
|
" InsertTime datetime,SystemDescribe varchar(150),DatabaseName varchar(50),dbid int,DataSize int,LogSize int,TotalSize int,DataFreeSpace int,LogFreeSpace int,TotalFreeSpace int,Activated int)\n" +
|
" while isnull(@i,0) < isnull(@CheckDays,0)\n" +
|
" begin\n" +
|
" select @CheckDocDate = convert(varchar(10),dateadd(day,0 - isnull(@i,0),@InsertTime),120)\n" +
|
" select @Beginday = convert(varchar(10),@CheckDocDate,120)\n" +
|
" select @Endday = dateadd(second,-1,dateadd(day,1,@CheckDocDate))\n" +
|
" \n" +
|
" select @LogonTimes = null,@CustomerCount = null,@TraceCustomerCount = null\n" +
|
" select @SalesDocumentCount = null,@SalesDocumentDigit = null,@SalesDocumentAmount = null\n" +
|
" select @PickupDocumentCount = null,@PickupDocumentDigit = null,@PickupDocumentAmount = null\n" +
|
" select @DeliveryDocumentCount = null,@DeliveryDocumentDigit = null,@DeliveryDocumentAmount = null\n" +
|
" select @PurchaseDocumentCount = null,@PurchaseDocumentDigit = null,@PurchaseDocumentAmount = null\n" +
|
" select @ReceiptDocumentCount = null,@ReceiptDocumentDigit = null,@ReceiptDocumentAmount = null\n" +
|
" select @PaidDocumentCount = null,@PaidDocumentAmount = null\n" +
|
" select @TransferDocumentCount = null,@TransferDocumentAmount = null\n" +
|
" select @GeneralExpenseDocumentCount = null,@GeneralExpenseDocumentAmount = null\n" +
|
" select @IncomeDocumentCount = null,@IncomeDocumentAmount = null\n" +
|
" select @FeeBillsReceivableDocumentCount = null,@FeeBillsReceivableDocumentAmount = null\n" +
|
" select @FeePaymentDocumentCount = null,@FeePaymentDocumentAmount = null\n" +
|
" select @FeeExpenseDocumentCount = null,@FeeExpenseDocumentAmount = null\n" +
|
" select @FeeIncomeDocumentCount = null,@FeeIncomeDocumentAmount = null\n" +
|
" select @TransferVoucherDocumentCount = null,@TransferVoucherDocumentAmount = null\n" +
|
" \n" +
|
" --登录次数\n" +
|
" select @LogonTimes = count(1) from _sysLoginLog where InTime between @BeginDay and @endday \n" +
|
" \n" +
|
" --新客计数\n" +
|
" begin try \n" +
|
" select @CustomerCount = count(1) from t110203 where Docdate = @CheckDocDate\n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" begin try \n" +
|
" select @TraceCustomerCount = count(1) from t170139H where Docdate = @CheckDocDate\n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --销售订单\n" +
|
" begin try \n" +
|
" select @SalesDocumentCount = count(1),@SalesDocumentDigit = sum(isnull(c.Digit,0)),@SalesDocumentAmount = sum(isnull(c.Totalmoney,0))\n" +
|
" from t120201h a \n" +
|
" left join (select b.DocCode,sum(isnull(c.Digit,0)) as Digit,sum(isnull(c.Totalmoney,0)) as TotalMoney\n" +
|
" from t120201h b join t120201d c on b.DocCode = c.DocCode \n" +
|
" where b.DocDate = @CheckDocDate\n" +
|
" group by b.DocCode) c on a.DocCode = c.DocCode \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --销售开单\n" +
|
" begin try \n" +
|
" select @PickupDocumentCount = count(1),@PickupDocumentDigit = sum(isnull(c.Digit,0)),@PickupDocumentAmount = sum(isnull(c.Totalmoney,0))\n" +
|
" from t120301h a \n" +
|
" left join (select b.DocCode,sum(isnull(c.Digit,0)) as Digit,sum(isnull(c.Totalmoney,0)) as TotalMoney\n" +
|
" from t120301h b join t120301d c on b.DocCode = c.DocCode \n" +
|
" where b.DocDate = @CheckDocDate\n" +
|
" group by b.DocCode) c on a.DocCode = c.DocCode \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --销售发货单\n" +
|
" begin try \n" +
|
" select @DeliveryDocumentCount = count(1),@DeliveryDocumentDigit = sum(isnull(c.Digit,0)),@DeliveryDocumentAmount = sum(isnull(c.Totalmoney,0))\n" +
|
" from t140201h a \n" +
|
" left join (select b.DocCode,sum(isnull(c.Digit,0)) as Digit,sum(isnull(c.Totalmoney,0)) as TotalMoney\n" +
|
" from t140201h b join t140201d c on b.DocCode = c.DocCode \n" +
|
" where b.DocDate = @CheckDocDate\n" +
|
" group by b.DocCode) c on a.DocCode = c.DocCode \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --采购订单\n" +
|
" begin try \n" +
|
" select @PurchaseDocumentCount = count(1),@PurchaseDocumentDigit = sum(isnull(c.Digit,0)),@PurchaseDocumentAmount = sum(isnull(c.Totalmoney,0))\n" +
|
" from t130301h a \n" +
|
" left join (select b.DocCode,sum(isnull(c.Digit,0)) as Digit,sum(isnull(c.Totalmoney,0)) as TotalMoney\n" +
|
" from t130301h b join t130301d c on b.DocCode = c.DocCode \n" +
|
" where b.DocDate = @CheckDocDate\n" +
|
" group by b.DocCode) c on a.DocCode = c.DocCode \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --采购收货单\n" +
|
" begin try \n" +
|
" select @PurchaseDocumentCount = count(1),@PurchaseDocumentDigit = sum(isnull(c.Digit,0)),@PurchaseDocumentAmount = sum(isnull(c.Totalmoney,0))\n" +
|
" from t140101h a \n" +
|
" left join (select b.DocCode,sum(isnull(c.Digit,0)) as Digit,sum(isnull(c.Totalmoney,0)) as TotalMoney\n" +
|
" from t140101h b join t140101d c on b.DocCode = c.DocCode \n" +
|
" where b.DocDate = @CheckDocDate\n" +
|
" group by b.DocCode) c on a.DocCode = c.DocCode \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --采购收货单\n" +
|
" begin try \n" +
|
" select @ReceiptDocumentCount = count(1),@ReceiptDocumentDigit = sum(isnull(c.Digit,0)),@ReceiptDocumentAmount = sum(isnull(c.Totalmoney,0))\n" +
|
" from t140101h a \n" +
|
" left join (select b.DocCode,sum(isnull(c.Digit,0)) as Digit,sum(isnull(c.Totalmoney,0)) as TotalMoney\n" +
|
" from t140101h b join t140101d c on b.DocCode = c.DocCode \n" +
|
" where b.DocDate = @CheckDocDate\n" +
|
" group by b.DocCode) c on a.DocCode = c.DocCode \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --客户收款单\n" +
|
" begin try \n" +
|
" select @PaidDocumentCount = count(1),@PaidDocumentAmount = sum(isnull(a.SumAmount,0))\n" +
|
" from t150101H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --供应商付款单\n" +
|
" begin try \n" +
|
" select @TransferDocumentCount = count(1),@TransferDocumentAmount = sum(isnull(a.Amount,0))\n" +
|
" from t150201H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --日常支出\n" +
|
" begin try \n" +
|
" select @GeneralExpenseDocumentCount = count(1),@GeneralExpenseDocumentAmount = sum(isnull(a.Amount,0))\n" +
|
" from t150302H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --日常收入\n" +
|
" begin try \n" +
|
" select @IncomeDocumentCount = count(1),@IncomeDocumentAmount = sum(isnull(a.Amount,0))\n" +
|
" from t150301H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --费用挂账单\n" +
|
" begin try \n" +
|
" select @FeeBillsReceivableDocumentCount = count(1),@FeeBillsReceivableDocumentAmount = sum(isnull(a.TotalAmount,0))\n" +
|
" from t150380H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --费用付款单\n" +
|
" begin try \n" +
|
" select @FeePaymentDocumentCount = count(1),@FeePaymentDocumentAmount = sum(isnull(a.TotalAmount,0))\n" +
|
" from t150382H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --费用收入单\n" +
|
" begin try \n" +
|
" select @FeeIncomeDocumentCount = count(1),@FeeIncomeDocumentAmount = sum(isnull(a.TotalAmount,0))\n" +
|
" from t150355H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --费用支出单\n" +
|
" begin try \n" +
|
" select @FeeExpenseDocumentCount = count(1),@FeeExpenseDocumentAmount = sum(isnull(a.TotalAmount,0))\n" +
|
" from t150352H a \n" +
|
" where a.DocDate = @CheckDocDate \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" --转账凭证计数\n" +
|
" begin try \n" +
|
" select @TransferVoucherDocumentCount = count(1),@TransferVoucherDocumentAmount = sum(isnulL(c.debit,0)) \n" +
|
" from t150328H a \n" +
|
" left join (select b.DocCode,sum(isnull(c.debit,0)) as debit \n" +
|
" from t150328H b join t150328D c on b.DocCode = c.DocCode\n" +
|
" where b.DocDate = @CheckDocDate \n" +
|
" group by b.DocCode ) c on a.DocCode = c.DocCode \n" +
|
" end try \n" +
|
" begin catch \n" +
|
" end catch \n" +
|
" select @DataSize = 0,@LogSize = 0,@TotalSize = 0,@DataFreeSpace = 0,@LogFreeSpace = 0,@TotalFreeSpace = 0\n" +
|
" select @DataSize = sum(case when a.type_desc = 'ROWS' then ((isnull(a.size,0) * 8) / 1024) else 0 end) , \n" +
|
" @LogSize = sum(case when a.type_desc = 'LOG' then ((isnull(a.size,0) * 8) / 1024) else 0 end) , \n" +
|
" @TotalSize = sum(isnull(a.size,0) * 8) / 1024 , \n" +
|
" @DataFreeSpace = sum(case when a.type_desc = 'ROWS' then (a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128 else 0 end) , \n" +
|
" @LogFreeSpace = sum(case when a.type_desc = 'LOG' then (a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128 else 0 end) , \n" +
|
" @TotalFreeSpace = sum((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128 ) \n" +
|
" from sys.database_files as a \n" +
|
|
" insert into @table (CheckDocDate , LogonTimes ,CustomerCount ,TraceCustomerCount ,\n" +
|
" SalesDocumentCount ,SalesDocumentDigit ,SalesDocumentAmount ,\n" +
|
" PickupDocumentCount ,PickupDocumentDigit ,PickupDocumentAmount ,\n" +
|
" DeliveryDocumentCount ,DeliveryDocumentDigit ,DeliveryDocumentAmount ,\n" +
|
" PurchaseDocumentCount ,PurchaseDocumentDigit ,PurchaseDocumentAmount ,\n" +
|
" ReceiptDocumentCount ,ReceiptDocumentDigit ,ReceiptDocumentAmount ,\n" +
|
" PaidDocumentCount ,PaidDocumentAmount ,\n" +
|
" TransferDocumentCount ,TransferDocumentAmount ,\n" +
|
" GeneralExpenseDocumentCount ,GeneralExpenseDocumentAmount ,\n" +
|
" IncomeDocumentCount ,IncomeDocumentAmount ,\n" +
|
" FeeBillsReceivableDocumentCount ,FeeBillsReceivableDocumentAmount ,\n" +
|
" FeePaymentDocumentCount ,FeePaymentDocumentAmount ,\n" +
|
" FeeExpenseDocumentCount ,FeeExpenseDocumentAmount ,\n" +
|
" FeeIncomeDocumentCount ,FeeIncomeDocumentAmount,\n" +
|
" TransferVoucherDocumentCount ,TransferVoucherDocumentAmount ,\n" +
|
" InsertTime,SystemDescribe,DatabaseName ,dbid,DataSize,LogSize,TotalSize,DataFreeSpace,LogFreeSpace,TotalFreeSpace,Activated)\n" +
|
" values(@CheckDocDate , @LogonTimes ,@CustomerCount ,@TraceCustomerCount ,\n" +
|
" @SalesDocumentCount ,@SalesDocumentDigit ,@SalesDocumentAmount ,\n" +
|
" @PickupDocumentCount ,@PickupDocumentDigit ,@PickupDocumentAmount ,\n" +
|
" @DeliveryDocumentCount ,@DeliveryDocumentDigit ,@DeliveryDocumentAmount ,\n" +
|
" @PurchaseDocumentCount ,@PurchaseDocumentDigit ,@PurchaseDocumentAmount ,\n" +
|
" @ReceiptDocumentCount ,@ReceiptDocumentDigit ,@ReceiptDocumentAmount ,\n" +
|
" @PaidDocumentCount ,@PaidDocumentAmount ,\n" +
|
" @TransferDocumentCount ,@TransferDocumentAmount ,\n" +
|
" @GeneralExpenseDocumentCount ,@GeneralExpenseDocumentAmount ,\n" +
|
" @IncomeDocumentCount ,@IncomeDocumentAmount ,\n" +
|
" @FeeBillsReceivableDocumentCount ,@FeeBillsReceivableDocumentAmount ,\n" +
|
" @FeePaymentDocumentCount ,@FeePaymentDocumentAmount ,\n" +
|
" @FeeExpenseDocumentCount ,@FeeExpenseDocumentAmount ,\n" +
|
" @FeeIncomeDocumentCount ,@FeeIncomeDocumentAmount,\n" +
|
" @TransferVoucherDocumentCount ,@TransferVoucherDocumentAmount ,\n" +
|
" @InsertTime,@SystemDescribe,@DatabaseName ,@dbid,@DataSize,@LogSize,@TotalSize,@DataFreeSpace,@LogFreeSpace,@TotalFreeSpace,@Activated )\n" +
|
" select @i = isnull(@i,0) + 1\n" +
|
" end \n" +
|
" select * from @table\n";
|
//log.info("170424sql:"+sql);
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T170424Entity.class));
|
}
|
|
@Override
|
public List getDepartmentInfo(String companyId) {
|
return this.getJdbcTemplate().queryForList("select cccode,ccname from t110601 where CompanyID=?", companyId);
|
}
|
|
@Override
|
public T120201Entity checkDocPushSuccess130301(int formid, String docCode, Integer actionType) {
|
String sql = " set nocount on\n" +
|
" declare @formid int=?,@docCode varchar(50)=?,@HeadqSoDocCode varchar(50),@dbid int\n" +
|
" select @HeadqSoDocCode=HeadqSoDocCode,@dbid=b.dbid from t130301H a join gform c on a.formid= c.formid \n" +
|
" join t110302 b on b.vndCode=a.vndCode\n" +
|
" where a.docCode=@docCode \n";
|
if (actionType.intValue() == 1) {
|
sql += " and c.postdocstatus = a.DocStatus and isnull(a.callBackMsg,'')<>''\n";
|
}
|
sql += " and isnull(b.dbid,0)<>0\n" +
|
" SELECT @HeadqSoDocCode as docCode,@dbid as vndDbid";
|
return this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(T120201Entity.class), formid, docCode);
|
}
|
|
@Override
|
public T150201Entity checkDocPushSuccess150201(int formid, String docCode, Integer actionType) {
|
String sql = " set nocount on\n" +
|
" declare @formid int=?,@docCode varchar(50)=?,@HeadqSoDocCode varchar(50),@dbid int\n" +
|
" select @HeadqSoDocCode=HeadqSoDocCode,@dbid=b.dbid from t150201H a join gform c on a.formid= c.formid \n" +
|
" join t110302 b on b.vndCode=a.vndCode\n" +
|
" where a.docCode=@docCode \n";
|
if (actionType.intValue() == 1) {
|
sql += " and c.postdocstatus = a.DocStatus and isnull(a.callBackMsg,'')<>''\n";
|
}
|
sql += " and isnull(b.dbid,0)<>0\n" +
|
" SELECT @HeadqSoDocCode as docCode,@dbid as vndDbid";
|
return this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(T150201Entity.class), formid, docCode);
|
}
|
|
@Override
|
public T150201Entity checkDocPushSuccess150206(int formid, String docCode, Integer actionType) {
|
String sql = " set nocount on\n" +
|
" declare @formid int=?,@docCode varchar(50)=?,@HeadqSoDocCode varchar(50),@dbid int\n" +
|
" select @HeadqSoDocCode=HeadqSoDocCode,@dbid=b.dbid from t150206H a join gform c on a.formid= c.formid \n" +
|
" join t110302 b on b.vndCode=a.vndCode\n" +
|
" where a.docCode=@docCode \n";
|
if (actionType.intValue() == 1) {
|
sql += " and c.postdocstatus = a.DocStatus and isnull(a.callBackMsg,'')<>''\n";
|
}
|
sql += " and isnull(b.dbid,0)<>0\n" +
|
" SELECT @HeadqSoDocCode as docCode,@dbid as vndDbid";
|
return this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(T150201Entity.class), formid, docCode);
|
}
|
|
@Override
|
public Integer updateCallBackMsgBy130301(String docCode) {
|
return this.jdbcTemplate.queryForObject(" set nocount on \n update a set a.callBackMsg=null,a.HeadqSoFormId=null,a.HeadqSoDocCode=null from t130301H a where a.DocCode=? \n select @@rowcount", Integer.class, docCode);
|
}
|
|
@Override
|
public Integer updateCallBackMsgBy150201(String docCode) {
|
return this.jdbcTemplate.queryForObject(" set nocount on \n update a set a.callBackMsg=null,a.HeadqSoFormId=null,a.HeadqSoDocCode=null from t150201H a where a.DocCode=? \n select @@rowcount", Integer.class, docCode);
|
}
|
|
@Override
|
public Integer updateCallBackMsgBy150206(String docCode) {
|
return this.jdbcTemplate.queryForObject(" set nocount on \n update a set a.callBackMsg=null,a.HeadqSoFormId=null,a.HeadqSoDocCode=null from t150206H a where a.DocCode=? \n select @@rowcount", Integer.class, docCode);
|
}
|
|
/**
|
* 推送信息写到日志里面，方便查看
|
*/
|
@Override
|
public boolean sendLogBy110562(List<T110562Entity> list) {
|
|
if (list != null && list.size() > 0) {
|
String sql = "";
|
for (T110562Entity t110562Entity : list) {
|
sql +=
|
// " \n if exists(select 1 from t110562 where formid=" + t110562Entity.getFormId() + " and doccode " + (GridUtils.prossSqlParm(t110562Entity.getDocCode()) == null ? "is null" : "=" + GridUtils.prossSqlParm(t110562Entity.getDocCode())) + " and rowid=" + GridUtils.prossSqlParm(t110562Entity.getRowId()) + ")\n" +
|
// " update t110562 set PushStatus=" + (t110562Entity.getPushStatus() == 0 ? 1 : 0) + ",PushDate=getdate(),PushTimes=isnull(PushTimes,0)+1 ,PushMsg='" + t110562Entity.getPushMsg() + "' where formid=" + t110562Entity.getFormId() + " and doccode=" + GridUtils.prossSqlParm(t110562Entity.getDocCode()) + " and rowid=" + GridUtils.prossSqlParm(t110562Entity.getRowId()) + " and matcode" + (GridUtils.prossSqlParm(t110562Entity.getMatCode()) == null ? " is null " : "=" + GridUtils.prossSqlParm(t110562Entity.getMatCode())) +
|
// " \n else \n" +
|
" insert into t110562(FormId ,DocCode,DocItem ,RowId ,MatCode ,MatName ,Special ,Uom ,Digit ,Price,TotalMoney,StCode,StName,ItemMemo,PushStatus,PushDate,PushTimes ,PushMsg) \n" +
|
" values(" + t110562Entity.getFormId() + "," + GridUtils.prossSqlParm(t110562Entity.getDocCode()) + "," + t110562Entity.getDocItem() + "," + GridUtils.prossSqlParm(t110562Entity.getRowId()) + "," + GridUtils.prossSqlParm(t110562Entity.getMatCode()) + "," + GridUtils.prossSqlParm(t110562Entity.getMatName()) + "," + GridUtils.prossSqlParm(t110562Entity.getSpecial()) + "," + GridUtils.prossSqlParm(t110562Entity.getUom()) + "," + t110562Entity.getDigit() + "," + t110562Entity.getPrice() + "," + t110562Entity.getTotalMoney() + "," + GridUtils.prossSqlParm(t110562Entity.getStCode()) + "," + GridUtils.prossSqlParm(t110562Entity.getStName()) + "," + GridUtils.prossSqlParm(t110562Entity.getItemMemo()) + "," + (t110562Entity.getPushStatus() == 0 ? 1 : 0) + ",getdate()," + t110562Entity.getPushTimes() + "," + GridUtils.prossSqlParm(t110562Entity.getPushMsg()) + ")\n";
|
}
|
final Integer result = getSimpleJdbcTemplate().queryForObject("set nocount on \n " + sql + " \n select @@rowcount;", Integer.class);
|
|
if (result > 0) {//操作成功才执行回调
|
return true;
|
}
|
}
|
return false;
|
}
|
|
@Transactional
|
@Override
|
public Integer del121501(T120201Entity t120201Entity) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" declare @dbid int =?,@HeadqCltCode varchar(20),@docCode varchar(50)=? \n" +
|
" select @HeadqCltCode=cltCode from t110203 where dbid=@dbid \n" +
|
" declare @status int \n" +
|
" select @status=count(1) from t121501H a join gform c on a.formid= c.formid and a.DocStatus=c.postdocstatus and a.DocCode=@docCode\n" +
|
" if @status=1 \n" +//表示已确认不能删除
|
" select -2 \n" +
|
" else\n" +
|
" begin \n" +
|
" declare @DistributorPoDocCode varchar(20), @rowcount int,@userCode varchar(50)='SYSTEM',\n" +
|
" @userName varchar(50)='系统管理员' ,@formId int=121501 ,@formName varchar(50)='经销商销售订单',@SqlWhere varchar(150)\n" +
|
" select @SqlWhere='doccode='''+@docCode+''''\n" +
|
" exec pSaveRecForDeleted @UserCode=@userCode,@UserName=@userName,@OrgDocCode = @docCode,@OrgFormID = @formId,@OrgFormName=@formName,@SqlWhere =@SqlWhere\n" +
|
" exec ppublicdel @docCode,@formId\n" +
|
" insert into gdocmodifylog(DocFormID,EventFormID,DocCode,DocDate, PreDocStatus,PostDocStatus,UserName,Event,EventTime ) \n" +
|
" select formid ,@formId,doccode,docdate,docstatus, docstatus , @userName,'删除单据',getdate() from t121501H with (nolock) where doccode =@docCode; \n" +
|
" delete from t121501D where HeadqCltCode=@HeadqCltCode and refcodeitem =@docCode\n" +
|
" delete from t121501H where HeadqCltCode=@HeadqCltCode and refcode=@docCode\n" +
|
" begin\n" +
|
" delete from _sys_fullcalendar where title like '%'+@docCode+'%' or description like '%'+@docCode+'%' or fullname like '%'+@docCode+'%' \n" +
|
" delete from t219001 where doccode=@docCode \n" +
|
" delete from spickorderlog where doccode=@docCode \n" +
|
" delete from t112002 where refformid =121501 and refdoccode=@docCode \n" +
|
" delete from _sysMessage where messagetxt like '%'+@docCode+'%' or origfields=@docCode\n" +
|
" select @@rowcount \n" +
|
" end\n" +
|
" end", Integer.class, t120201Entity.getCltDbid(), t120201Entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Integer del150101(T150201Entity entity) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" declare @status int ,@docCode varchar(50)=? \n" +
|
" select @status=count(1) from t150101H a join gform c on a.formid= c.formid and a.DocStatus=c.postdocstatus and a.DocCode=@docCode\n" +
|
" if @status=1 \n" +//表示已确认不能删除
|
" select -2 \n" +
|
" else\n" +
|
" begin \n" +
|
" declare @rowcount int,@userCode varchar(50)='SYSTEM',\n" +
|
" @userName varchar(50)='系统管理员' ,@formId int=150101 ,@formName varchar(50)='客户收款订单',@SqlWhere varchar(150)\n" +
|
" select @SqlWhere='doccode='''+@docCode+''''\n" +
|
" exec pSaveRecForDeleted @UserCode=@userCode,@UserName=@userName,@OrgDocCode = @docCode,@OrgFormID = @formId,@OrgFormName=@formName,@SqlWhere =@SqlWhere\n" +
|
" exec ppublicdel @docCode,150101\n" +
|
" insert into gdocmodifylog(DocFormID,EventFormID,DocCode,DocDate, PreDocStatus,PostDocStatus,UserName,Event,EventTime ) \n" +
|
" select formid ,@formId,doccode,docdate,docstatus, docstatus , @userName,'删除单据',getdate() from t150101H with (nolock) where doccode =@docCode; \n" +
|
" delete from t150348 where docCode=@docCode\n" +
|
" delete from t150344 where docCode=@docCode\n" +
|
" delete from t150101D where docCode=@docCode\n" +
|
" delete from t150101H where docCode=@docCode\n" +
|
" begin\n" +
|
" delete from _sys_fullcalendar where title like '%'+@docCode+'%' or description like '%'+@docCode+'%' or fullname like '%'+@docCode+'%' \n" +
|
" delete from t219001 where doccode=@docCode \n" +
|
" delete from spickorderlog where doccode=@docCode \n" +
|
" delete from t112002 where refformid =150101 and refdoccode=@docCode \n" +
|
" delete from _sysMessage where messagetxt like '%'+@docCode+'%' or origfields=@docCode\n" +
|
" select @@rowcount \n" +
|
" end\n" +
|
" end ", Integer.class, entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Integer del150170(T150201Entity entity) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" declare @status int ,@docCode varchar(50)=? \n" +
|
" select @status=count(1) from t150170H a join gform c on a.formid= c.formid and a.DocStatus=c.postdocstatus and a.DocCode=@docCode\n" +
|
" if @status=1 \n" +//表示已确认不能删除
|
" select -2 \n" +
|
" else\n" +
|
" begin \n" +
|
" declare @rowcount int,@userCode varchar(50)='SYSTEM',\n" +
|
" @userName varchar(50)='系统管理员' ,@formId int=150170 ,@formName varchar(50)='预收客户定金单',@SqlWhere varchar(150)\n" +
|
" select @SqlWhere='doccode='''+@docCode+''''\n" +
|
" exec pSaveRecForDeleted @UserCode=@userCode,@UserName=@userName,@OrgDocCode = @docCode,@OrgFormID = @formId,@OrgFormName=@formName,@SqlWhere =@SqlWhere\n" +
|
" exec ppublicdel @docCode,150170\n" +
|
" insert into gdocmodifylog(DocFormID,EventFormID,DocCode,DocDate, PreDocStatus,PostDocStatus,UserName,Event,EventTime ) \n" +
|
" select formid ,@formId,doccode,docdate,docstatus, docstatus , @userName,'删除单据',getdate() from t150170H with (nolock) where doccode =@docCode; \n" +
|
" delete from t150348 where docCode=@docCode\n" +
|
" delete from t150344 where docCode=@docCode\n" +
|
" delete from t150170H where docCode=@docCode\n" +
|
" begin\n" +
|
" delete from _sys_fullcalendar where title like '%'+@docCode+'%' or description like '%'+@docCode+'%' or fullname like '%'+@docCode+'%' \n" +
|
" delete from t219001 where doccode=@docCode \n" +
|
" delete from spickorderlog where doccode=@docCode \n" +
|
" delete from t112002 where refformid =150170 and refdoccode=@docCode \n" +
|
" delete from _sysMessage where messagetxt like '%'+@docCode+'%' or origfields=@docCode\n" +
|
" select @@rowcount \n" +
|
" end\n" +
|
" end ", Integer.class, entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Integer del120201(T120201Entity t120201Entity) {
|
final FormEntity formEntity = getFormEntityInfo(t120201Entity.getAffectedFormIdForTargetDatabase());
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" declare @status int ,@docCode varchar(50)=? \n" +
|
" select @status=count(1) from " + formEntity.getHDtable() + " a join gform c on a.formid= c.formid and a.DocStatus=c.postdocstatus and a.DocCode=@docCode\n" +
|
" if @status=1 \n" +//表示已确认不能删除
|
" select -2 \n" +
|
" else\n" +
|
" begin \n" +
|
" declare @DistributorPoDocCode varchar(20), @rowcount int,@userCode varchar(50)='SYSTEM',\n" +
|
" @userName varchar(50)='系统管理员' ,@formId int=" + formEntity.getFormid() + " ,@formName varchar(50)='销售单',@SqlWhere varchar(150)\n" +
|
" select @SqlWhere='doccode='''+@docCode+''''\n" +
|
" exec pSaveRecForDeleted @UserCode=@userCode,@UserName=@userName,@OrgDocCode = @docCode,@OrgFormID = @formId,@OrgFormName=@formName,@SqlWhere =@SqlWhere\n" +
|
" exec ppublicdel @docCode,@formId\n" +
|
" insert into gdocmodifylog(DocFormID,EventFormID,DocCode,DocDate, PreDocStatus,PostDocStatus,UserName,Event,EventTime ) \n" +
|
" select formid ,@formId,doccode,docdate,docstatus, docstatus , @userName,'删除单据',getdate() from " + formEntity.getHDtable() + " with (nolock) where doccode =@docCode; \n" +
|
|
" delete from " + formEntity.getDDtable() + " where doccode =@docCode\n" +
|
" delete from " + formEntity.getHDtable() + " where doccode =@docCode\n" +
|
" begin\n" +
|
" delete from _sys_fullcalendar where title like '%'+@docCode+'%' or description like '%'+@docCode+'%' or fullname like '%'+@docCode+'%' \n" +
|
" delete from t219001 where doccode=@docCode \n" +
|
" delete from spickorderlog where doccode=@docCode \n" +
|
" delete from t112002 where refformid =" + formEntity.getFormid() + " and refdoccode=@docCode \n" +
|
" delete from _sysMessage where messagetxt like '%'+@docCode+'%' or origfields=@docCode\n" +
|
" select @@rowcount \n" +
|
" end\n" +
|
" end", Integer.class, t120201Entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Map reduction120201(T120201Entity t120201Entity) {
|
return this.jdbcTemplate.queryForMap("set nocount on \n" +
|
" declare @docCode varchar(50),@Memo varchar(50), @LinkDocInfo varchar(50),@refCode varchar(50)=? \n" +
|
" select @docCode=doccode from _Sys_SaveRecForDeleted where refcode=@refCode and DocStatus = 0\n" +
|
" if isnull(@docCode,'')<>''" +
|
" exec p9746 @docCode=@docCode,@UserCode = 'SYSTEM',@UserName = '系统管理员',@Memo=@Memo,@LinkDocInfo=@LinkDocInfo \n" +
|
" select @Memo as memo,@LinkDocInfo as linkDocInfo", t120201Entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Map reduction150170(T150201Entity entity) {
|
return this.jdbcTemplate.queryForMap("set nocount on \n" +
|
" declare @docCode varchar(50),@Memo varchar(50), @LinkDocInfo varchar(50),@refCode varchar(50)=? \n" +
|
" select @docCode=doccode from _Sys_SaveRecForDeleted where refcode=@refCode and DocStatus = 0\n" +
|
" if isnull(@docCode,'')<>''" +
|
" exec p9746 @docCode=@docCode,@UserCode = 'SYSTEM',@UserName = '系统管理员',@Memo=@Memo,@LinkDocInfo=@LinkDocInfo \n" +
|
" select @Memo as memo,@LinkDocInfo as linkDocInfo", entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Map reduction150101(T150201Entity entity) {
|
return this.jdbcTemplate.queryForMap("set nocount on \n" +
|
" declare @docCode varchar(50),@Memo varchar(50), @LinkDocInfo varchar(50),@refCode varchar(50)=? \n" +
|
" select @docCode=doccode from _Sys_SaveRecForDeleted where refcode=@refCode and DocStatus = 0\n" +
|
" if isnull(@docCode,'')<>''" +
|
" exec p9746 @docCode=@docCode,@UserCode = 'SYSTEM',@UserName = '系统管理员',@Memo=@Memo,@LinkDocInfo=@LinkDocInfo \n" +
|
" select @Memo as memo,@LinkDocInfo as linkDocInfo", entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Integer do140902(T140902Entity t140902Entity) {
|
if (t140902Entity != null && t140902Entity.getList().size() > 0) {
|
String sql = "set nocount on\n" +
|
" declare @cltCode varchar(50),@cltName varchar(50),@MatCode varchar(50),@MatName varchar(50),@BatchCode varchar(50),@unlimitStock money,@InspectStock money,@limitStock money,@returnStock money ,@SalesOrderStock money,@RemainStock money,@onkeepstock money,\n" +
|
" @ontransstock money,@onorderstock money,@payorderstock money,@transorderstock money,@onSupplystock money,@safestock money,@Freestock money,@LastInDate datetime,@LastOutDate datetime,\n" +
|
" @LastPrdDate datetime,@stockValue decimal(19,2),@PlanStock money,@FirstInDate datetime,@FirstOutDate datetime,@FirstPrdDate datetime,@onLendStock money,@onProductionStock money,\n" +
|
" @cv1 varchar(50),@cv2 varchar(50),@cv3 varchar(50),@cv4 varchar(50),@cv5 varchar(50),@cv6 varchar(50),@cv7 varchar(50),@cv8 varchar(50),@cv9 varchar(50),@cv10 varchar(50),@uom varchar(50),@uomrate money,@baseuom varchar(50),@baseuomrate money,@ratetxt varchar(50)\n";
|
//---取分销商编号和名称,先删除后增加库存
|
sql += " declare @dbid int=" + t140902Entity.getCustomerDbid() +
|
" select @cltCode=cltcode,@cltName=cltName from t110203 where isnull(dbid,0)=@dbid \n" +
|
//---删除指定数据
|
" delete t141002 where cltCode=@cltCode \n";
|
for (T140902 t140902 : t140902Entity.getList()) {
|
|
sql += " select @MatCode=null,@MatName=null,@BatchCode=null,@unlimitStock=null,@InspectStock=null,@limitStock=null,@returnStock=null ,@SalesOrderStock=null,@RemainStock=null,@onkeepstock=null," +
|
" @ontransstock=null,@onorderstock=null,@payorderstock=null,@transorderstock=null,@onSupplystock=null,@safestock=null,@Freestock=null,@LastInDate=null,@LastOutDate=null,\n" +
|
" @LastPrdDate=null,@stockValue=null,@PlanStock=null,@FirstInDate=null,@FirstOutDate=null,@FirstPrdDate=null,@onLendStock=null,@onProductionStock=null,\n" +
|
" @cv1=null,@cv2=null,@cv3=null,@cv4=null,@cv5=null,@cv6=null,@cv7=null,@cv8=null,@cv9=null,@cv10=null,@uom=null,@uomrate=null,@baseuom=null,@baseuomrate=null,@ratetxt=null\n" +
|
|
" select @MatCode=" + GridUtils.prossSqlParm(t140902.getMatCode()) + ",@BatchCode=" + GridUtils.prossSqlParm(t140902.getBatchCode()) + ",@unlimitStock=" + t140902.getUnlimitStock() + ",@InspectStock=" + t140902.getInspectStock() + ",@limitStock=" + t140902.getLimitStock() + ",@returnStock=" + t140902.getReturnStock() + " ,@SalesOrderStock=" + t140902.getSalesOrderStock() + ",@RemainStock=" + t140902.getRemainStock() + ",@onkeepstock=" + t140902.getOnkeepstock() + ", @ontransstock=" + t140902.getOntransstock() + ",@onorderstock=" + t140902.getOnorderstock() + ",@payorderstock=" + t140902.getPayorderstock() + ",@transorderstock=" + t140902.getTransorderstock() + ",@onSupplystock=" + t140902.getOnSupplystock() + ",@safestock=" + t140902.getSafestock() + ",@Freestock=" + t140902.getFreestock() + ",@LastInDate=" + GridUtils.prossSqlParm(t140902.getLastInDate()) + ",@LastOutDate=" + GridUtils.prossSqlParm(t140902.getLastOutDate()) + ",\n" +
|
" @LastPrdDate=" + GridUtils.prossSqlParm(t140902.getLastPrdDate()) + ",@stockValue=" + t140902.getStockValue() + ",@PlanStock=" + t140902.getPlanStock() + ",@FirstInDate=" + GridUtils.prossSqlParm(t140902.getFirstInDate()) + ",@FirstOutDate=" + GridUtils.prossSqlParm(t140902.getFirstOutDate()) + ",@FirstPrdDate=" + GridUtils.prossSqlParm(t140902.getFirstPrdDate()) + ",@onLendStock=" + t140902.getOnLendStock() + ",@onProductionStock=" + t140902.getOnProductionStock() + ",\n" +
|
" @cv1=" + GridUtils.prossSqlParm(t140902.getCv1()) + ",@cv2=" + GridUtils.prossSqlParm(t140902.getCv2()) + ",@cv3=" + GridUtils.prossSqlParm(t140902.getCv3()) + ",@cv4=" + GridUtils.prossSqlParm(t140902.getCv4()) + ",@cv5=" + GridUtils.prossSqlParm(t140902.getCv5()) + ",@cv6=" + GridUtils.prossSqlParm(t140902.getCv6()) + ",@cv7=" + GridUtils.prossSqlParm(t140902.getCv7()) + ",@cv8=" + GridUtils.prossSqlParm(t140902.getCv8()) + ",@cv9=" + GridUtils.prossSqlParm(t140902.getCv9()) + ",@cv10=" + GridUtils.prossSqlParm(t140902.getCv10()) + ",@uom=" + GridUtils.prossSqlParm(t140902.getUom()) + ",@uomrate=" + GridUtils.prossSqlParm(t140902.getUomRate()) + ",@baseuom=" + GridUtils.prossSqlParm(t140902.getBaseUOM()) + ",@baseuomrate=" + GridUtils.prossSqlParm(t140902.getBaseuomrate()) + ",@ratetxt=" + GridUtils.prossSqlParm(t140902.getRatetxt()) + "\n" +
|
|
" insert into t141002(cltCode,MatCode,BatchCode,unlimitStock,InspectStock,limitStock,returnStock,SalesOrderStock,RemainStock,onkeepstock,ontransstock,onorderstock,payorderstock,transorderstock,onSupplystock,safestock,Freestock,LastInDate,LastOutDate,LastPrdDate,stockValue,PlanStock,FirstInDate,FirstOutDate,FirstPrdDate,onLendStock,onProductionStock, cv1,cv2,cv3,cv4,cv5,cv6,cv7,cv8,cv9,cv10,uom,uomrate,baseuom,baseuomrate,ratetxt) values(@cltCode,@MatCode,@BatchCode,@unlimitStock,@InspectStock,@limitStock,@returnStock,@SalesOrderStock,@RemainStock,@onkeepstock,@ontransstock,@onorderstock,@payorderstock,@transorderstock,@onSupplystock,@safestock,@Freestock,@LastInDate,@LastOutDate,@LastPrdDate,@stockValue,@PlanStock,@FirstInDate,@FirstOutDate,@FirstPrdDate,@onLendStock,@onProductionStock, @cv1,@cv2,@cv3,@cv4,@cv5,@cv6,@cv7,@cv8,@cv9,@cv10,@uom,@uomrate,@baseuom,@baseuomrate,@ratetxt)\n";
|
}
|
sql += " select @@rowcount \n";
|
return this.jdbcTemplate.queryForObject(sql, Integer.class);
|
} else {
|
return null;
|
}
|
}
|
|
|
@Transactional
|
@Override
|
public void do120201All(Push120201Entity entity) {
|
final FormEntity orderEntity = getFormEntityInfo(entity.getAffectedFormId());
|
String sql = " set nocount on\n" +
|
" ---定义变量 \n" +
|
" declare @doccode varchar(50),@companyID varchar(50),@companyName varchar(50),@HeadqCltCode varchar(50),@HeadqCltName varchar(50),@ccCode varchar(50),@ccName varchar(50),@dbid int,@tel varchar(50),@PropertyAddress varchar(50),@clttype varchar(50), @PreDocStatus int,@PostDocStatus int,@DocStatusValue int,@FormId int, @Memo varchar(200),@LinkDocInfo varchar(200),@doccode1 varchar(50), @docitem int, @rowId varchar(50), @matName varchar(200)=null,@Barcode varchar(150)=null,@Brand varchar(150)=null,@UOM varchar(150)=null,@baseuom varchar(150)=null,@netprice decimal,@TrademarkPrice money=null,@special varchar(100)=null,@weight money=null,@matgroup varchar(100)=null,@matgroupname varchar(100)=null,@TCname varchar(100)=null,@TaxPrice money=null,@pricetype varchar(100)=null,@discount money=null,@whcode varchar(100)=null,@refcodeitem varchar(100)=null,@refrowid varchar(100)=null,@refcodeformid int,@Pricerefcodeitem varchar(100)=null,@Pricerefrowid varchar(100)=null,@Pricerefcodeformid int=null,@matCode varchar(50),@price money,@digit money,@itemMemo varchar(200),@stCode varchar(50),@stName varchar(50),@cv2 varchar(20),@totalmoney money,@totalmoney2 money,@MoneyType int,@CaiGouStatus varchar(50) ,@CaiGouDoccode varchar(50),@CaiGouFormid int,@CaiGouFormType int ,@CaiGouAmount varchar(50) ,@CaiGouLastDate datetime,@uploads varchar(200),@sumtotalmoney2 money,@authenticatedCode varchar(50) \n ";
|
|
for (T121501Entity t120201Entity : entity.getList()) {
|
//--客户资料
|
sql += get110203(t120201Entity, entity.getCustomerDbid());
|
sql += get120201Head(t120201Entity,orderEntity);
|
sql += get120201Detail(t120201Entity,orderEntity);
|
}
|
if (StringUtils.isNotBlank(entity.getPullSalesOrderFromDistributorLastDateTime())) {
|
sql += " update a set a.PullSalesOrderFromDistributorLastDateTime='" + entity.getPullSalesOrderFromDistributorLastDateTime() + "' from t110203 a where a.dbid=" + entity.getCustomerDbid();
|
}
|
log.info("120201sql:"+sql);
|
this.jdbcTemplate.execute(sql);
|
}
|
|
private String get120201Head(T121501Entity entity,FormEntity formEntity) {
|
T121501HeadEntity head = entity.getHead();
|
StringBuilder sb = new StringBuilder();
|
sb.append(" select @doccode1=doccode from "+formEntity.getHDtable()+" a where a.HeadqCltCode=@HeadqCltCode and a.refCode=" + GridUtils.prossSqlParm(head.getDocCode()) + " \n" +
|
" if @@rowcount >0 \n" +
|
" begin \n" +
|
" update a set a.Docdate=" + GridUtils.prossSqlParm(head.getDocDate()) + ",a.doctype=" + GridUtils.prossSqlParm(head.getDocType()) + ",a.DocStatus=" + head.getDocStatus() + ",a.[CltCode]=" + GridUtils.prossSqlParm(head.getCltCode()) + ",a.[CltName]=" + GridUtils.prossSqlParm(head.getCltName()) + ",a.[PropertyAddress]=" + GridUtils.prossSqlParm(head.getPropertyAddress()) + ",a.hDMemo=" + GridUtils.prossSqlParm(head.getHDMemo()) + ",a.tel=" + GridUtils.prossSqlParm(head.getTel()) + ",a.tel2=" + GridUtils.prossSqlParm(head.getTel2()) + ",a.CaiGouStatus=" + GridUtils.prossSqlParm(head.getCaiGouStatus()) + ",a.CaiGouDoccode=" + GridUtils.prossSqlParm(head.getCaiGouDoccode()) + ",a.CaiGouFormid =" + head.getCaiGouFormid() + ",a.CaiGouFormType=" + head.getCaiGouFormType() + " ,a.CaiGouAmount =" + head.getCaiGouAmount() + " ,a.CaiGouLastDate=" + GridUtils.prossSqlParm(head.getCaiGouLastDate()) + ",a.uploads=" + GridUtils.prossSqlParm(head.getUploads()) + ",a.sumtotalmoney2=" + head.getSumtotalmoney2() + " from "+formEntity.getHDtable()+" a where a.HeadqCltCode=@HeadqCltCode and a.refCode=" + GridUtils.prossSqlParm(head.getDocCode()) + " \n" +
|
" end \n" +
|
" else \n")
|
.append(" begin \n" +
|
" exec sp_newdoccode "+formEntity.getFormid()+",'SYSTEM',@doccode1 output \n" +
|
" insert into "+formEntity.getHDtable()+"(docCode,formid,PeriodID,Docdate,doctype,EnterCode,EnterName,DocStatus,companyID,companyName,[CltCode],[CltName],[PropertyAddress],hDMemo,tel,tel2,hdcurrency,hdcurrencyrate,refCode,HeadqCltCode,HeadqCltName,CaiGouStatus ,CaiGouDoccode ,CaiGouFormid ,CaiGouFormType ,CaiGouAmount ,CaiGouLastDate ,uploads,sumtotalmoney2) values(@doccode1,"+formEntity.getFormid()+",dbo.GetPeriodID("+formEntity.getFormid()+",@companyID,convert(char(10),GetDate(),120)),convert(char(10),GetDate(),120)," + GridUtils.prossSqlParm(head.getDocType()) + "," + GridUtils.prossSqlParm(head.getEnterCode()) + "," + GridUtils.prossSqlParm(head.getEnterName()) + ",0,@companyId,@companyName," + GridUtils.prossSqlParm(head.getCltCode()) + "," + GridUtils.prossSqlParm(head.getCltName()) + "," + GridUtils.prossSqlParm(head.getPropertyAddress()) + "," + GridUtils.prossSqlParm(head.getHDMemo()) + "," + GridUtils.prossSqlParm(head.getTel()) + "," + GridUtils.prossSqlParm(head.getTel2()) + ",'RMB',1," + GridUtils.prossSqlParm(head.getDocCode()) + ",@HeadqCltCode,@HeadqCltName," + GridUtils.prossSqlParm(head.getCaiGouStatus()) + "," + GridUtils.prossSqlParm(head.getCaiGouDoccode()) + "," + head.getCaiGouFormid() + "," + head.getCaiGouFormType() + "," + head.getCaiGouAmount() + "," + GridUtils.prossSqlParm(head.getCaiGouLastDate()) + "," + GridUtils.prossSqlParm(head.getUploads()) + "," + head.getSumtotalmoney2() + ") \n" +
|
" end \n");
|
return sb.toString();
|
}
|
|
private String get120201Detail(T121501Entity entity,FormEntity formEntity) {
|
List<T121501DetailEntity> list = entity.getOrders();
|
String sql = "--标记所有为删除状态 到最后再执行一次update以便可以删除这一次提交经销商已删除行的记录\n" +
|
" update a set a.isDeleted=1 from "+formEntity.getDDtable()+" a where a.HeadqCltCode=@HeadqCltCode and refcodeitem=" + GridUtils.prossSqlParm(entity.getHead().getDocCode()) +
|
" select @docitem=0 \n";
|
for (T121501DetailEntity detailEntity : list) {
|
sql += " select @docitem=isnull(@docitem,0)+10,@matCode=" + GridUtils.prossSqlParm(detailEntity.getMatCode()) + " , @price =" + detailEntity.getPrice() + ", @digit=" + detailEntity.getDigit() + ", @cv2=" + GridUtils.prossSqlParm(detailEntity.getCv2()) + " , @itemMemo=" + GridUtils.prossSqlParm(detailEntity.getItemMemo()) + ",@matName=" + GridUtils.prossSqlParm(detailEntity.getMatName()) + ",@Barcode=" + GridUtils.prossSqlParm(detailEntity.getBarcode()) + ",@Brand=" + GridUtils.prossSqlParm(detailEntity.getBrand()) + ",@stCode=" + GridUtils.prossSqlParm(detailEntity.getStcode()) + ", @stName=" + GridUtils.prossSqlParm(detailEntity.getStname()) + " ,@UOM=" + GridUtils.prossSqlParm(detailEntity.getUom()) + ",@baseuom=" + GridUtils.prossSqlParm(detailEntity.getBaseuom()) + ",@netprice=" + detailEntity.getNetprice() + ",@TrademarkPrice=" + detailEntity.getTrademarkPrice() + ",@special=" + GridUtils.prossSqlParm(detailEntity.getSpecial()) + ",@weight=" + detailEntity.getWeight() + ",@matgroup=" + GridUtils.prossSqlParm(detailEntity.getMatgroup()) + ",@matgroupname=" + GridUtils.prossSqlParm(detailEntity.getMatgroupname()) + ",@TCname=" + GridUtils.prossSqlParm(detailEntity.getTCname()) + " ,@TaxPrice=" + detailEntity.getTaxPrice() + " ,@pricetype=" + GridUtils.prossSqlParm(detailEntity.getPricetype()) + " ,@discount=" + detailEntity.getDiscount() + " ,@whcode=" + GridUtils.prossSqlParm(detailEntity.getWhcode()) + " ,@refcodeitem=" + GridUtils.prossSqlParm(detailEntity.getDocCode()) + " ,@refrowid=" + GridUtils.prossSqlParm(detailEntity.getRowid()) + " ,@refcodeformid=" + detailEntity.getRefcodeformid() + " ,@Pricerefcodeitem=" + GridUtils.prossSqlParm(detailEntity.getPricerefcodeitem()) + " ,@Pricerefrowid=" + GridUtils.prossSqlParm(detailEntity.getPricerefrowid()) + " ,@Pricerefcodeformid=" + GridUtils.prossSqlParm(detailEntity.getPricerefcodeformid()) + ",@totalmoney=" + detailEntity.getTotalmoney() + ",@totalmoney2=" + detailEntity.getTotalmoney2() + ",@MoneyType=" + detailEntity.getMoneyType() + " \n" +
|
|
" update a set a.isDeleted=0,a.matCode=@matCode,a.price=@price,a.digit=@digit,a.cv2=@cv2,a.itemMemo=@itemMemo,a.matName=@matName,a.Barcode=@Barcode,a.Brand=@Brand,a.Stcode=@stCode,a.stname=@stName,a.UOM=@UOM,a.baseuom=@baseuom,a.netprice=@netprice,a.TrademarkPrice=@TrademarkPrice,a.special=@special,a.weight=@weight,a.matgroup=@matgroup,a.matgroupname=@matgroupname,a.TaxPrice=@TaxPrice ,a.pricetype=@pricetype ,a.totalmoney=@totalmoney,a.totalmoney2=@totalmoney2,a.discount=@discount ,a.whcode=@whcode ,a.Pricerefcodeitem=@Pricerefcodeitem ,a.Pricerefrowid=@Pricerefrowid ,a.Pricerefcodeformid=@Pricerefcodeformid,a.MoneyType=@MoneyType from "+formEntity.getDDtable()+" a where a.HeadqCltCode=@HeadqCltCode and a.refcodeitem=@refcodeitem and a.refrowid=@refrowid \n" +
|
" if @@rowcount=0 \n" +
|
" begin \n" +
|
" exec getXXXX @rowId output \n" +
|
" insert into "+formEntity.getDDtable()+"(docCode,docitem,rowid,matCode,price,digit,cv2,itemMemo,matName,Barcode,Brand,Stcode,stname,UOM,baseuom,netprice,TrademarkPrice,special,weight,matgroup,matgroupname ,TaxPrice ,pricetype ,totalmoney,totalmoney2 ,discount ,whcode ,refcodeitem ,refrowid ,Pricerefcodeitem ,Pricerefrowid ,Pricerefcodeformid,MoneyType,isDeleted,HeadqCltCode,HeadqCltName)values(@doccode1,@docitem,@rowId, @matCode , @price , @digit , @cv2 , @itemMemo ,@matName,@Barcode,@Brand,@stCode, @stName ,@UOM,@baseuom,@netprice,@TrademarkPrice,@special,@weight,@matgroup,@matgroupname ,@TaxPrice ,@pricetype , @totalmoney,@totalmoney2 ,@discount ,@whcode ,@refcodeitem ,@refrowid ,@Pricerefcodeitem ,@Pricerefrowid ,@Pricerefcodeformid,7,0,@HeadqCltCode,@HeadqCltName) \n" +
|
" end \n";
|
}
|
sql += " delete "+formEntity.getDDtable()+" where HeadqCltCode=@HeadqCltCode and refcodeitem=" + GridUtils.prossSqlParm(entity.getHead().getDocCode()) + " and isDeleted=1 \n" +
|
|
" select @doccode1=doccode from "+formEntity.getHDtable()+" where HeadqCltCode=@HeadqCltCode and refcode= " + GridUtils.prossSqlParm(entity.getHead().getDocCode()) + " \n" +
|
" exec p"+formEntity.getFormid()+"Save @doccode1 \n" +//保存时执行
|
" select @FormId = FormId,@DocStatusValue = DocStatus from "+formEntity.getHDtable()+" where doccode = @doccode1\n" +
|
" select @PreDocStatus = PreDocStatus,@PostDocStatus = a.PostDocStatus from gform a where a.formid = @FormId \n" +
|
" if isnull(@DocStatusValue,0) = isnull(@PreDocStatus,0) \n"
|
+ " begin \n"
|
+ " update a set DocStatus = @PostDocStatus from "+formEntity.getHDtable()+" a where a.DocCode = @doccode1 \n"
|
+ " exec p"+formEntity.getFormid()+"Post @UserCode = 'SYSTEM',@UserName = '管理员', \n"
|
+ " @DocCode = @doccode1,@FormID = @FormId,\n"
|
+ " @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n"
|
+ " @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n"
|
+ " end \n";
|
return sql;
|
}
|
|
@Transactional
|
private String get110203(T121501Entity entity, Integer dbid) {
|
T110203Entity t110203 = entity.getCustomer();
|
StringBuilder sb = new StringBuilder();
|
//1，写客户表,先update，更新不成功表示需要新增
|
sb.append(" select @dbid=" + dbid + ",@authenticatedCode="+GridUtils.prossSqlParm(t110203.getAuthenticatedCode())+"\n")
|
.append(" --取客户资料\n" +
|
" select @HeadqCltCode=cltCode,@HeadqCltName=cltName,@companyID=Authenticatedcompanyid,@companyName=Authenticatedcompanyname,@clttype=clttype from t110203 where dbid=@dbid and authenticatedCode=@authenticatedCode\n")
|
.append(" update a set a.cltName=" + GridUtils.prossSqlParm(t110203.getCltName()) + ",a.tel=" + GridUtils.prossSqlParm(t110203.getTel()) + ",a.tel2=" + GridUtils.prossSqlParm(t110203.getTel2()) + ",a.propertyAddress=" + GridUtils.prossSqlParm(t110203.getPropertyAddress()) + ",a.SellerName=" + GridUtils.prossSqlParm(t110203.getSellerName()) + " from t171001H a where a.HeadqCltCode = @HeadqCltCode and a.cltCode=" + GridUtils.prossSqlParm(t110203.getCltCode()) + "\n")
|
.append("if @@rowcount =0\n")
|
.append(" begin\n")
|
.append(" exec sp_newdoccode 171001,'SYSTEM',@doccode output \n")
|
.append(" insert into t171001H(docCode,formid,PeriodID,Docdate,clttype,EnterCode,EnterName,EnterDate,ModifyName,ModifyDate,DocStatus,cltCode,cltName,tel,tel2,propertyAddress,SellerName,HeadqCltCode,HeadqCltName)values(@doccode,171001,dbo.GetPeriodID(171001,@companyID,convert(char(10),GetDate(),120)),convert(char(10),GetDate(),120),@clttype,'SYSTEM','管理员',getdate(),'管理员',getdate(),0," + GridUtils.prossSqlParm(t110203.getCltCode()) + "," + GridUtils.prossSqlParm(t110203.getCltName()) + "," + GridUtils.prossSqlParm(t110203.getTel()) + "," + GridUtils.prossSqlParm(t110203.getTel2()) + "," + GridUtils.prossSqlParm(t110203.getPropertyAddress()) + "," + GridUtils.prossSqlParm(t110203.getSellerName()) + ",@HeadqCltCode,@HeadqCltName) \n")
|
.append(" end\n" +
|
" select @doccode=doccode from t171001H a where a.HeadqCltCode = @HeadqCltCode and a.cltCode=" + GridUtils.prossSqlParm(t110203.getCltCode()));
|
sb.append("\n exec p171001Save @doccode \n")//保存时执行
|
.append(
|
" select @FormId = FormId,@DocStatusValue = DocStatus from t171001H where doccode = @doccode\n" +
|
" select @PreDocStatus = PreDocStatus,@PostDocStatus = a.PostDocStatus from gform a where a.formid = @FormId \n")
|
.append(" if isnull(@DocStatusValue,0) = isnull(@PreDocStatus,0) \n"
|
+ " begin \n"
|
+ " update a set DocStatus = @PostDocStatus from t171001H a where a.DocCode = @doccode \n"
|
+ " exec p171001Post @UserCode = 'SYSTEM',@UserName = '管理员', \n"
|
+ " @DocCode = @doccode,@FormID = @FormId,\n"
|
+ " @DocStatusValue = @DocStatusValue,@ButtonType ='提交', \n"
|
+ " @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n"
|
+ " end \n");
|
return sb.toString();
|
}
|
|
@Override
|
public List<T121501Entity> get120201List(T110302 t110302) {
|
final FormEntity orderEntity = getFormEntityInfo(t110302.getAffectedFormIdForTargetDatabase());
|
List<T121501Entity> results = null;
|
String sql = "set nocount on \n" +
|
" declare @shouKuanAmount money,@docCode varchar(20),@preSendDate datetime,@docType varchar(50),\n" +
|
" @cltCode varchar(20),@cltName varchar(80),@linkMan varchar(50),@ccCode varchar(20),@ccName varchar(50),@hDMemo varchar(400), @tel varchar(50), \n" +
|
" @tel2 varchar(50),@propertyAddress varchar(200), @sellerName varchar(50), @shopGuideName varchar(50),@serviceName varchar(50), \n" +
|
" @dengjiname varchar(80), @referralsName varchar(80),@docStatus int, @docStatusName varchar(50),@docDate datetime, \n" +
|
" @actname varchar(50),@refCode varchar(20),@cshopCode varchar(50),@cshopName varchar(50) ,@cVenCode varchar(50),@cVenName varchar(50),@EnterName varchar(50), @enterCode varchar(50), @enterDate datetime , @modifyName varchar(50), @modifyDate datetime, \n" +
|
" @rowid varchar(20),@DocItem int,@ObjMatCode varchar(50),@ObjMatName varchar(120) ,@ObjSpecial varchar(120) ,\n" +
|
" @uom varchar(20),@digit money,@price money,@totalmoney money,@stcode varchar(20),@stname varchar(50),@ItemMemo varchar(400), \n" +
|
" @overallcoding varchar(50),@locationName varchar(200), @brand varchar(50),@trademarkPrice money, \n" +
|
" @discount money, @cv2 varchar(80), @unsubscribeDoccode varchar(200), @subDocdate datetime , \n" +
|
" @cltdocdate datetime,@cltType varchar(50),@sourceInfo varchar(50),@clttel varchar(50),@clttel2 varchar(50),@cltpropertyAddress varchar(200), \n" +
|
" @cltenterName varchar(50),@cltsellerName varchar(50),@cltHDNote varchar(200), \n" +
|
" @cltreferralsName varchar(50),@cltbrithday varchar(50) ,@cltdocStatus int, @cltdocStatusName varchar(50),@baseuom varchar(50),@netprice money,@special varchar(50),@weight money,@matgroup varchar(50),@matgroupname varchar(50),@TCname varchar(50) ,@TaxPrice money ,@pricetype varchar(50) ,@totalmoney2 money ,@whcode varchar(50) ,@refcodeitem varchar(50),@refrowid varchar(50) ,@refcodeformid int ,@Pricerefcodeitem varchar(50) ,@Pricerefrowid varchar(50),@Pricerefcodeformid int,@MoneyType int,@CaiGouStatus varchar(50) ,@CaiGouDoccode varchar(50),@CaiGouFormid int,@CaiGouFormType int ,@CaiGouAmount varchar(50) ,@CaiGouLastDate datetime,@uploads varchar(200),@sumtotalmoney2 money \n" +
|
" declare @table table (shouKuanAmount money,docCode varchar(20),preSendDate datetime,docType varchar(50),\n" +
|
" cltCode varchar(20),cltName varchar(80),linkMan varchar(50),ccCode varchar(20),ccName varchar(50),hDMemo varchar(400), tel varchar(50), \n" +
|
" tel2 varchar(50),propertyAddress varchar(200), sellerName varchar(50), shopGuideName varchar(50),serviceName varchar(50), \n" +
|
" dengjiname varchar(80), referralsName varchar(80),docStatus int, docStatusName varchar(50),docDate datetime, \n" +
|
" actname varchar(50),refCode varchar(20),EnterName varchar(50),enterCode varchar(50), enterDate varchar(50) , modifyName varchar(50), modifyDate varchar(50), \n" +
|
" rowid varchar(20),DocItem int,MatCode varchar(50),MatName varchar(120) ,ObjSpecial varchar(120) ,\n" +
|
" uom varchar(20),digit money,price money,totalmoney money,stcode varchar(20),stname varchar(50),ItemMemo varchar(400), \n" +
|
" overallcoding varchar(50),locationName varchar(200), brand varchar(50),trademarkPrice money, \n" +
|
" discount money, cv2 varchar(80), unsubscribeDoccode varchar(200), subDocdate datetime , \n" +
|
" cltdocdate datetime,cltType varchar(50),sourceInfo varchar(50),clttel varchar(50),clttel2 varchar(50),cltpropertyAddress varchar(200), \n" +
|
" cltenterName varchar(50),cltsellerName varchar(50),cltHDNote varchar(200), \n" +
|
" cltreferralsName varchar(50),cltbrithday varchar(50) ,cltdocStatus int, cltdocStatusName varchar(50) ,baseuom varchar(50),netprice money,special varchar(50),weight money,matgroup varchar(50),matgroupname varchar(50),TCname varchar(50) ,TaxPrice money ,pricetype varchar(50) ,totalmoney2 money ,whcode varchar(50) ,refcodeitem varchar(50),refrowid varchar(50) ,refcodeformid int ,Pricerefcodeitem varchar(50) ,Pricerefrowid varchar(50),Pricerefcodeformid int,MoneyType int,CaiGouStatus varchar(50),CaiGouDoccode varchar(50),CaiGouFormid int,CaiGouFormType int ,CaiGouAmount varchar(50) ,CaiGouLastDate datetime,uploads varchar(200),sumtotalmoney2 money)\n" +
|
|
" declare @MatCode varchar(50),@MatName varchar(120) ,@Brands varchar(500)=?,@LastPullDate datetime=?,@vndDbid int=?,@authenticatedCode varchar(50)=?\n" +
|
" declare mycurDeli cursor for\n" +
|
" select b.shouKuanAmount,b.docCode,b.preSendDate,b.docType,\n" +
|
" b.cltCode,b.linkMan,b.ccCode,b.ccName,b.hDMemo, b.tel, \n" +
|
" b.tel2, b.propertyAddress, b.sellerName, b.shopGuideName, b.serviceName, \n" +
|
" b.dengjiname, b.referralsName,b.docStatus, b.docStatusName,b.docDate, \n" +
|
" b.actname,b.refCode ,b.EnterName, b.enterCode, b.enterDate, b.modifyName ,b.modifyDate, \n" +
|
" c.rowid,c.DocItem,c.MatCode , c.MatName,c.special,\n" +
|
" c.uom,c.digit,c.price,c.totalmoney,c.stcode,c.stname,c.ItemMemo, \n" +
|
" c.overallcoding,c.locationName, c.brand, c.trademarkPrice, \n" +
|
" c.discount, c.cv2, c.unsubscribeDoccode, c.subDocdate, \n" +
|
" c.baseuom,c.netprice,c.weight,c.matgroup,c.matgroupname,c.TCname ,c.TaxPrice ,c.pricetype ,c.totalmoney2 ,c.whcode ,c.refcodeitem ,c.refrowid ,c.refcodeformid ,c.Pricerefcodeitem ,c.Pricerefrowid ,c.Pricerefcodeformid,c.MoneyType, b.CaiGouStatus,b.CaiGouDoccode ,b.CaiGouFormid ,b.CaiGouFormType ,b.CaiGouAmount ,b.CaiGouLastDate ,b.uploads,b.sumtotalmoney2, \n" +
|
" e.cltname,\n" +
|
" e.docdate as cltdocdate,--登记日期 \n" +
|
" e.cltType,--客户类型 \n" +
|
" e.sourceInfo,--客户来源 \n" +
|
" e.tel as clttel,--客户电话 \n" +
|
" e.tel2 as clttel2,--联系人电话 \n" +
|
" e.propertyAddress as cltpropertyAddress,--送货地址 \n" +
|
" e.enterName as cltenterName,--登记人 \n" +
|
" e.sellerName as cltsellerName,--业务员 \n" +
|
" e.hDnote as cltHDNote,--备注 \n" +
|
" e.referralsName as cltreferralsName,--介绍人 \n" +
|
" e.cltbrithday as cltbrithday,\n" +
|
" e.docStatus as cltdocStatus, e.docStatusName as cltdocStatusName \n" +
|
" from "+orderEntity.getHDtable()+" b \n" +
|
" join "+orderEntity.getDDtable()+" c on b.doccode = c.doccode \n" +
|
" join t110503 d on c.matcode = d.matcode \n" +
|
" join t110203 e on b.cltcode = e.cltcode \n" +
|
" where \n" +
|
" d.Brand in (select list from getinstr(@Brands)) \n" +
|
" and (b.ModifyDate is null or b.ModifyDate between @LastPullDate and getdate()) \n" +
|
" and exists(select 1 from gform c where b.formid= c.formid and c.postdocstatus = b.DocStatus ) \n" +
|
" and exists(select 1 from t110302 where isnull(dbid,0)=@vndDbid and isnull(authenticatedCode,'')=@authenticatedCode)" +
|
" order by b.DocCode\n" +
|
" open mycurDeli \n" +
|
" fetch next from mycurDeli into @shouKuanAmount,@docCode,@preSendDate,@docType,\n" +
|
" @cltCode,@linkMan,@ccCode,@ccName,@hDMemo, @tel, \n" +
|
" @tel2, @propertyAddress,@sellerName, @shopGuideName,@serviceName, \n" +
|
" @dengjiname, @referralsName,@docStatus, @docStatusName,@docDate, \n" +
|
" @actname,@refCode, @EnterName,@enterCode, @enterDate, @modifyName, @modifyDate,\n" +
|
"@rowid,@DocItem,@MatCode , @MatName,@special,\n" +
|
" @uom,@digit,@price,@totalmoney,@stcode,@stname,@ItemMemo, \n" +
|
" @overallcoding,@locationName, @brand, @trademarkPrice, \n" +
|
" @discount, @cv2, @unsubscribeDoccode, @subDocdate, @baseuom,@netprice,@weight,@matgroup,@matgroupname," +
|
" @TCname ,@TaxPrice ,@pricetype ,@totalmoney2 ,@whcode ,@refcodeitem ,@refrowid ,@refcodeformid ,@Pricerefcodeitem ," +
|
" @Pricerefrowid ,@Pricerefcodeformid,@MoneyType,@CaiGouStatus,@CaiGouDoccode ,@CaiGouFormid ,@CaiGouFormType ,@CaiGouAmount ,@CaiGouLastDate ,@uploads,@sumtotalmoney2,\n" +
|
" @cltname,@cltdocdate,@cltType,@sourceInfo,@clttel,@clttel2,\n" +
|
" @cltpropertyAddress,@cltenterName,@cltsellerName,@cltHDNote,\n" +
|
" @cltreferralsName,@cltbrithday,@cltdocStatus,@cltdocStatusName \n" +
|
" while @@FETCH_STATUS = 0\n" +
|
" begin\n" +
|
" insert into @table (shouKuanAmount,docCode,preSendDate,docType,\n" +
|
" cltCode,linkMan,ccCode,ccName,hDMemo, tel, \n" +
|
" tel2, propertyAddress,sellerName, shopGuideName,serviceName, \n" +
|
" dengjiname, referralsName,docStatus, docStatusName,docDate, \n" +
|
" actname,refCode, EnterName,enterCode,enterDate,modifyName,modifyDate,\n" +
|
" rowid,DocItem,MatCode,MatName,Special ,\n" +
|
" uom,digit,price,totalmoney,stcode,stname,ItemMemo, \n" +
|
" overallcoding,locationName, brand, trademarkPrice, \n" +
|
" discount, cv2, unsubscribeDoccode, subDocdate, \n" +
|
" cltname,cltdocdate,cltType,sourceInfo,clttel,clttel2,\n" +
|
" cltpropertyAddress,cltenterName,cltsellerName,cltHDNote,\n" +
|
" cltreferralsName,cltbrithday,cltdocStatus,cltdocStatusName,baseuom,netprice,weight,matgroup,matgroupname,\n" +
|
" TCname ,TaxPrice ,pricetype ,totalmoney2 ,whcode ,refcodeitem ,refrowid ,refcodeformid ,Pricerefcodeitem ,\n" +
|
" Pricerefrowid ,Pricerefcodeformid,MoneyType,CaiGouStatus,CaiGouDoccode ,CaiGouFormid ,CaiGouFormType ,CaiGouAmount ,CaiGouLastDate,uploads,sumtotalmoney2 \n" +
|
" )values (@shouKuanAmount,@docCode,@preSendDate,@docType,\n" +
|
" @cltCode,@linkMan,@ccCode,@ccName,@hDMemo, @tel, \n" +
|
" @tel2, @propertyAddress,@sellerName, @shopGuideName,@serviceName, \n" +
|
" @dengjiname, @referralsName,@docStatus, @docStatusName,@docDate, \n" +
|
" @actname,@refCode,@EnterName,@enterCode, @enterDate, @modifyName, @modifyDate ,\n" +
|
" @rowid,@DocItem,@MatCode,@MatName,@Special ,\n" +
|
" @uom,@digit,@price,@totalmoney,@stcode,@stname,@ItemMemo, \n" +
|
" @overallcoding,@locationName, @brand, @trademarkPrice, \n" +
|
" @discount, @cv2, @unsubscribeDoccode, @subDocdate,\n" +
|
" @cltname,@cltdocdate,@cltType,@sourceInfo,@clttel,@clttel2,\n" +
|
" @cltpropertyAddress,@cltenterName,@cltsellerName,@cltHDNote,\n" +
|
" @cltreferralsName,@cltbrithday,@cltdocStatus,@cltdocStatusName,@baseuom,@netprice,@weight,@matgroup,@matgroupname,\n" +
|
"@TCname ,@TaxPrice ,@pricetype ,@totalmoney2 ,@whcode ,@refcodeitem ,@refrowid ,@refcodeformid ,\n" +
|
"@Pricerefcodeitem ,@Pricerefrowid ,@Pricerefcodeformid,@MoneyType,@CaiGouStatus,@CaiGouDoccode ,@CaiGouFormid ,@CaiGouFormType ,@CaiGouAmount ,@CaiGouLastDate,@uploads,@sumtotalmoney2)\n" +
|
" fetch next from mycurDeli into @shouKuanAmount,@docCode,@preSendDate,@docType,\n" +
|
" @cltCode,@linkMan,@ccCode,@ccName,@hDMemo, @tel, \n" +
|
" @tel2, @propertyAddress,@sellerName, @shopGuideName,@serviceName, \n" +
|
" @dengjiname, @referralsName,@docStatus, @docStatusName,@docDate, \n" +
|
" @actname,@refCode, @EnterName,@enterCode, @enterDate, @modifyName, @modifyDate,\n" +
|
"@rowid,@DocItem,@MatCode , @MatName,@special,\n" +
|
" @uom,@digit,@price,@totalmoney,@stcode,@stname,@ItemMemo, \n" +
|
" @overallcoding,@locationName, @brand, @trademarkPrice, \n" +
|
" @discount, @cv2, @unsubscribeDoccode, @subDocdate, @baseuom,@netprice,@weight,@matgroup,@matgroupname,\n" +
|
"@TCname ,@TaxPrice ,@pricetype ,@totalmoney2 ,@whcode ,\n" +
|
"@refcodeitem ,@refrowid ,@refcodeformid ,@Pricerefcodeitem ,@Pricerefrowid ,@Pricerefcodeformid,@MoneyType,@CaiGouStatus,@CaiGouDoccode ,@CaiGouFormid ,@CaiGouFormType ,@CaiGouAmount ,@CaiGouLastDate,@uploads,@sumtotalmoney2,\n" +
|
" @cltname,@cltdocdate,@cltType,@sourceInfo,@clttel,@clttel2,\n" +
|
" @cltpropertyAddress,@cltenterName,@cltsellerName,@cltHDNote,\n" +
|
" @cltreferralsName,@cltbrithday,@cltdocStatus,@cltdocStatusName \n" +
|
" end \n" +
|
" close mycurDeli\n" +
|
" deallocate mycurDeli \n" +
|
" select shouKuanAmount,docCode,preSendDate,docType,\n" +
|
" cltCode,linkMan,ccCode,ccName,hDMemo, tel, \n" +
|
" tel2, propertyAddress,sellerName, shopGuideName,serviceName, \n" +
|
" dengjiname, referralsName,docStatus, docStatusName,docDate, \n" +
|
" actname,refCode, EnterName,enterDate, modifyName, modifyDate,\n" +
|
" rowid,DocItem,matcode, matname, special ,\n" +
|
" uom,digit,price,totalmoney,stcode,stname,ItemMemo, \n" +
|
" overallcoding,locationName, brand, trademarkPrice, \n" +
|
" discount, cv2, unsubscribeDoccode, subDocdate,\n" +
|
" cltname,cltdocdate,cltType,sourceInfo,clttel,clttel2,\n" +
|
" cltpropertyAddress,cltenterName,cltsellerName,cltHDNote,\n" +
|
" cltreferralsName,cltbrithday,cltdocStatus,cltdocStatusName,baseuom,netprice,weight,matgroup,matgroupname,\n" +
|
" TCname ,TaxPrice ,pricetype ,totalmoney2 ,whcode ,refcodeitem ,refrowid ,refcodeformid ,Pricerefcodeitem ,CaiGouStatus,CaiGouDoccode ,CaiGouFormid ,CaiGouFormType ,CaiGouAmount ,CaiGouLastDate,uploads,sumtotalmoney2,\n" +
|
" Pricerefrowid ,Pricerefcodeformid,MoneyType \n" +
|
" from @table";
|
//System.out.println(">>>"+sql);
|
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, t110302.getBrands(), t110302.getPullSalesOrderFromDistributorLastDateTime(), t110302.getVndDbid(),t110302.getAuthenticatedCode());
|
if (list != null && list.size() > 0) {
|
results = new ArrayList<>();
|
Set<Object> distinctDoccdeList = list.stream().map(x -> x.get("docCode")).collect(Collectors.toSet());//取出集合中不重复单号
|
for (Object dist : distinctDoccdeList) {
|
List<Map<String, Object>> t120201List = list.stream().filter(x -> dist.equals(x.get("docCode"))).collect(Collectors.toList());
|
boolean flag = false;//
|
T121501Entity t120201Entity = new T121501Entity();
|
List<T121501DetailEntity> detailEntityList = new ArrayList<>();
|
for (Map<String, Object> map : t120201List) {
|
if (!flag) {//表示第一次，则要取主表和客户信息
|
t120201Entity.setHead(new T121501HeadEntity(
|
GridUtils.prossRowSetDataType_String(map, "docCode"),
|
GridUtils.prossRowSetDataType_String(map, "preSendDate"),
|
GridUtils.prossRowSetDataType_String(map, "docType"),
|
GridUtils.prossRowSetDataType_String(map, "cltCode"),
|
GridUtils.prossRowSetDataType_String(map, "cltName"),
|
GridUtils.prossRowSetDataType_String(map, "linkMan"),
|
GridUtils.prossRowSetDataType_String(map, "cshopCode"),
|
GridUtils.prossRowSetDataType_String(map, "cshopName"),
|
GridUtils.prossRowSetDataType_String(map, "hDMemo"),
|
GridUtils.prossRowSetDataType_Double(map, "shouKuanAmount"),
|
GridUtils.prossRowSetDataType_String(map, "tel"),
|
GridUtils.prossRowSetDataType_String(map, "tel2"),
|
GridUtils.prossRowSetDataType_String(map, "propertyAddress"),
|
GridUtils.prossRowSetDataType_String(map, "sellerName"),
|
GridUtils.prossRowSetDataType_String(map, "shopGuideName"),
|
GridUtils.prossRowSetDataType_String(map, "serviceName"),
|
GridUtils.prossRowSetDataType_String(map, "dengjiname"),
|
GridUtils.prossRowSetDataType_String(map, "referralsName"),
|
GridUtils.prossRowSetDataType_Int(map, "docStatus"),
|
GridUtils.prossRowSetDataType_String(map, "docStatusName"),
|
GridUtils.prossRowSetDataType_String(map, "docDate"),
|
GridUtils.prossRowSetDataType_String(map, "actname"),
|
GridUtils.prossRowSetDataType_String(map, "refCode"),
|
GridUtils.prossRowSetDataType_String(map, "cVenCode"),
|
GridUtils.prossRowSetDataType_String(map, "cVenName"),
|
GridUtils.prossRowSetDataType_String(map, "enterName"),
|
GridUtils.prossRowSetDataType_String(map, "enterCode"),
|
GridUtils.prossRowSetDataType_String(map, "enterDate"),
|
GridUtils.prossRowSetDataType_String(map, "modifyName"),
|
GridUtils.prossRowSetDataType_String(map, "modifyDate"),
|
GridUtils.prossRowSetDataType_String(map, "CaiGouStatus"),
|
GridUtils.prossRowSetDataType_String(map, "CaiGouDoccode"),
|
GridUtils.prossRowSetDataType_Integer(map, "CaiGouFormid"),
|
GridUtils.prossRowSetDataType_Integer(map, "CaiGouFormType"),
|
GridUtils.prossRowSetDataType_Double(map, "CaiGouAmount"),
|
GridUtils.prossRowSetDataType_String(map, "CaiGouLastDate"),
|
GridUtils.prossRowSetDataType_String(map, "uploads"),
|
GridUtils.prossRowSetDataType_Double(map, "sumtotalmoney2")
|
|
));
|
//客户信息
|
t120201Entity.setCustomer(new T110203Entity(
|
GridUtils.prossRowSetDataType_String(map, "cltCode"),
|
GridUtils.prossRowSetDataType_String(map, "cltName"),
|
GridUtils.prossRowSetDataType_Date(map, "cltdocdate"),
|
GridUtils.prossRowSetDataType_String(map, "cltType"),
|
GridUtils.prossRowSetDataType_String(map, "sourceInfo"),
|
GridUtils.prossRowSetDataType_String(map, "clttel"),
|
GridUtils.prossRowSetDataType_String(map, "clttel2"),
|
GridUtils.prossRowSetDataType_String(map, "cshopCode"),
|
GridUtils.prossRowSetDataType_String(map, "cshopName"),
|
GridUtils.prossRowSetDataType_String(map, "cltpropertyAddress"),
|
GridUtils.prossRowSetDataType_String(map, "cltenterName"),
|
GridUtils.prossRowSetDataType_String(map, "cltsellerName"),
|
GridUtils.prossRowSetDataType_String(map, "cltHDNote"),
|
GridUtils.prossRowSetDataType_String(map, "cltreferralsName"),
|
GridUtils.prossRowSetDataType_String(map, "cltbrithday"),
|
GridUtils.prossRowSetDataType_Int(map, "cltdocStatus"),
|
GridUtils.prossRowSetDataType_String(map, "cltdocStatusName"),
|
GridUtils.prossRowSetDataType_String(map, "cVenCode"),
|
GridUtils.prossRowSetDataType_String(map, "cVenName"),
|
t110302.getAuthenticatedCode()
|
));
|
}
|
//取明细表
|
detailEntityList.add(new T121501DetailEntity(
|
GridUtils.prossRowSetDataType_String(map, "matCode"),
|
GridUtils.prossRowSetDataType_String(map, "matName"),
|
GridUtils.prossRowSetDataType_String(map, "uom"),
|
GridUtils.prossRowSetDataType_Double(map, "digit"),
|
GridUtils.prossRowSetDataType_Double(map, "price"),
|
GridUtils.prossRowSetDataType_Double(map, "totalmoney"),
|
GridUtils.prossRowSetDataType_String(map, "stcode"),
|
GridUtils.prossRowSetDataType_String(map, "stname"),
|
GridUtils.prossRowSetDataType_String(map, "ItemMemo"),
|
GridUtils.prossRowSetDataType_Int(map, "DocItem"),
|
GridUtils.prossRowSetDataType_String(map, "rowid"),
|
GridUtils.prossRowSetDataType_String(map, "docCode"),
|
GridUtils.prossRowSetDataType_String(map, "overallcoding"),
|
GridUtils.prossRowSetDataType_String(map, "locationName"),
|
GridUtils.prossRowSetDataType_String(map, "brand"),
|
GridUtils.prossRowSetDataType_String(map, "barcode"),
|
GridUtils.prossRowSetDataType_Double(map, "trademarkPrice"),
|
GridUtils.prossRowSetDataType_Double(map, "discount"),
|
GridUtils.prossRowSetDataType_String(map, "cv2"),
|
GridUtils.prossRowSetDataType_String(map, "cv1"),
|
GridUtils.prossRowSetDataType_String(map, "unsubscribeDoccode"),
|
GridUtils.prossRowSetDataType_String(map, "subDocdate"),
|
GridUtils.prossRowSetDataType_String(map, "special"),
|
GridUtils.prossRowSetDataType_String(map, "baseuom"),
|
GridUtils.prossRowSetDataType_Double(map, "netprice"),
|
GridUtils.prossRowSetDataType_Double(map, "weight"),
|
GridUtils.prossRowSetDataType_String(map, "matgroup"),
|
GridUtils.prossRowSetDataType_String(map, "matgroupname"),
|
GridUtils.prossRowSetDataType_String(map, "tCname"),
|
GridUtils.prossRowSetDataType_Double(map, "taxPrice"),
|
GridUtils.prossRowSetDataType_String(map, "pricetype"),
|
GridUtils.prossRowSetDataType_Double(map, "totalmoney2"),
|
GridUtils.prossRowSetDataType_String(map, "whcode"),
|
GridUtils.prossRowSetDataType_String(map, "refcodeitem"),
|
GridUtils.prossRowSetDataType_String(map, "refrowid"),
|
GridUtils.prossRowSetDataType_Int(map, "refcodeformid"),
|
GridUtils.prossRowSetDataType_String(map, "pricerefcodeitem"),
|
GridUtils.prossRowSetDataType_String(map, "pricerefrowid"),
|
GridUtils.prossRowSetDataType_String(map, "pricerefcodeformid"),
|
GridUtils.prossRowSetDataType_Int(map, "moneyType")
|
));
|
flag = true;
|
}
|
t120201Entity.setOrders(detailEntityList);
|
results.add(t120201Entity);
|
}
|
}
|
return results;
|
}
|
|
@Override
|
public List<T140902> get140902(T110203VO t110203VO) {
|
return this.jdbcTemplate.query("set nocount on \n" +
|
" declare @Brands varchar(500) = ? \n" +
|
" declare @vndDbid int = ? \n" +
|
" select sum(isnull(a.unlimitStock,0)) as unlimitStock,\n" +
|
" case when sum(isnull(a.unlimitStock,0)) <> 0 then 1.00000000* sum(isnull(a.stockValue,0)) / sum(isnull(a.unlimitStock,0)) else 0 end as stockPrice ,\n" +
|
" sum(isnull(a.stockValue,0)) as stockValue,\n" +
|
" sum(isnull(a.onkeepstock,0)) as onkeepstock,\n" +
|
" sum(isnull(a.RemainStock,0)) as RemainStock, \n" +
|
" sum(isnull(a.ontransstock,0)) as ontransstock, \n" +
|
" sum(isnull(a.payorderstock,0)) as payorderstock,\n" +
|
" sum(isnull(a.Freestock,0)) as Freestock,\n" +
|
" sum(isnull(a.safestock,0)) as safestock,\n" +
|
" sum(isnull(a.PlanStock,0)) as PlanStock,\n" +
|
" sum(isnull(a.onProductionStock,0)) as onProductionStock,\n" +
|
" sum(isnull(a.onLendStock,0)) as onLendStock,\n" +
|
" sum(isnull(a.WaitIssueNumber,0)) as WaitIssueNumber,\n" +
|
" sum(isnull(a.SubcontractStock,0)) as SubcontractStock,\n" +
|
" sum(isnull(a.ChargingOfStock,0)) as ChargingOfStock,\n" +
|
" sum(isnull(a.onSupplystock,0)) as onSupplystock,\n" +
|
" min(a.FirstInDate) as FirstInDate, \n" +
|
" max(a.LastInDate) as LastInDate, \n" +
|
" min(a.FirstOutDate) as FirstOutDate , \n" +
|
" max(a.LastOutDate) as LastOutDate, \n" +
|
" min(a.FirstPrdDate) as FirstPrdDate, \n" +
|
" max(a.LastPrdDate) as LastPrdDate, \n" +
|
" f.cv1,f.cv2,f.cv3,f.cv4,f.cv5,f.cv6,f.cv7,f.cv8,f.cv9,f.cv10, \n" +
|
" f.BaseUom,\n" +
|
" f.ratetxt,f.Baseuomrate,f.uomRate,f.BaseUOM,a.BatchCode,b.MatCode,b.matname\n" +
|
" from t140902 a with (nolock) \n" +
|
" join t110503 b with (nolock) on a.matcode = b.matcode\n" +
|
" join t140901 f with (nolock) on a.matcode = f.matcode and a.batchcode = f.batchcode\n" +
|
" where b.brand in (select list from getinstr(@Brands)) \n" +
|
" and exists(select 1 from t110302 where isnull(dbid,0)=@vndDbid)" +
|
" group by f.cv1,f.cv2,f.cv3,f.cv4,f.cv5,f.cv6,f.cv7,f.cv8,f.cv9,f.cv10, \n" +
|
" f.BaseUom,\n" +
|
" f.ratetxt,f.Baseuomrate,f.uomRate,f.BaseUOM,a.BatchCode,b.MatCode,b.matname ",
|
new BeanPropertyRowMapper<>(T140902.class), t110203VO.getBrands(), t110203VO.getVndDbid());
|
|
}
|
|
@Override
|
public List<T210610Entity> getT210610(String docCode, Task task) {
|
String sql = " set nocount on\n" +
|
" select a.docCode,b.dbid as cltDbid,a.Content,a.upload,a.theme from t210610 a\n" +
|
" join t110203 b on b.cltCode in (select list from GetInStr(a.sendCode)) \n" +
|
" where exists(select 1 from gform where formid=210610 and postdocstatus=a.DocStatus) and a.DocCode=?";
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T210610Entity.class), docCode);
|
}
|
|
@Transactional
|
@Override
|
public Integer do210610(T210610Entity entity) {
|
String sql = " set nocount on \n" +
|
" declare @theme varchar(100)=?,@content varchar(5000)=?,@upload varchar(200)=?,@doccode varchar(50),@companyID varchar(50),@companyName varchar(50),@HeadqVndCode varchar(50),@HeadqVndName varchar(50),@dbid int=?,@PreDocStatus int,@PostDocStatus int,@DocStatusValue int,@FormId int=210610, @Memo varchar(200),@LinkDocInfo varchar(200),@refCode varchar(50)=? ,@FormType int \n" +
|
" declare @ButtonType varchar(20) ='提交' , @now datetime = getdate(),@ToDoActionType varchar(20) = '通知' " +
|
" declare @PopupMsgsUnValidDate datetime = DATEADD(day,2 , @now ) \n" +
|
|
" select @HeadqVndCode=vndCode,@HeadqVndName=vndName,@companyID=companyId,@companyName=companyName from t110302 where dbid=@dbid \n" +
|
" select @doccode=doccode from t210610 where HeadqVndCode=@HeadqVndCode and refCode=@refCode \n" +
|
" if @@rowcount>0 \n" +
|
" update a set a.theme=@theme,a.content=@content,a.upload=@upload from t210610 a where a.HeadqVndCode=@HeadqVndCode and a.refCode=@refCode \n " +
|
" else \n" +
|
" begin \n" +
|
" exec sp_newdoccode @Formid,@HeadqVndCode,@doccode output \n" +
|
" insert into t210610(doccode, formid, periodid, docdate, companyid, companyname, docstatus, entercode, entername, enterdate, modifyname, modifydate, theme, content, upload,HeadqVndCode,HeadqVndName,refCode) values (@doccode,@Formid, dbo.GetPeriodID(@Formid,@companyID,convert(char(10),@now,120)), convert(char(10),@now,120), @companyid, @companyname, 0, @HeadqVndCode, @HeadqVndName, @now, @HeadqVndName, @now, @theme, @content, @upload,@HeadqVndCode,@HeadqVndName,@refCode) \n" +
|
" end \n" +
|
" exec p210610Save @doccode \n" +
|
" select @FormId = FormId,@DocStatusValue = DocStatus from t210610 where doccode = @doccode\n" +
|
" select @PreDocStatus = PreDocStatus,@PostDocStatus = a.PostDocStatus from gform a where a.formid = @FormId \n" +
|
" if isnull(@DocStatusValue,0) = isnull(@PreDocStatus,0) \n"
|
+ " begin \n"
|
+ " update a set DocStatus = @PostDocStatus from t210610 a where a.DocCode = @doccode \n"
|
+ " exec p210610Post @UserCode = @HeadqVndCode,@UserName = @HeadqVndName, \n"
|
+ " @DocCode = @doccode,@FormID = @FormId,\n"
|
+ " @DocStatusValue = @DocStatusValue,@ButtonType =@ButtonType, \n"
|
+ " @Memo=@Memo output ,@LinkDocInfo = @LinkDocInfo output \n"
|
+ " end \n" +
|
" --发送待办事宜\n" +
|
" select @FormType = case a.FormType when 9 then 5 when 15 then 8 when 17 then 16 when 497 then 496 when 499 then 498 else a.FormType end \n" +
|
" from _sysmenu a where a.FormId = @FormId \n" +
|
" declare @UserCodes varchar(max),@UserNames varchar(max) \n" +
|
" select @UserCodes = case when isnull(@UserCodes,'') = '' then '' else @UserCodes + ';' end +a.UserCode, \n" +
|
" @UserNames = case when isnull(@UserNames,'') = '' then '' else @UserNames + ';' end +a.UserName \n" +
|
" from _sys_loginUser a where a.logonType = 0 \n" +
|
" exec p219001 @UserCodes = @UserCodes , @UserNames = @UserNames ,@Formid = @formid , \n" +
|
" @FormType = @FormType,@Doccode = @doccode ,@CurFormid = @formid ,\n" +
|
" @Topic = @theme , @ActiveType = 1 ,@SenderCode=@HeadqVndCode,@SenderName=@HeadqVndName, \n" +
|
" @EnterCode = @HeadqVndCode,@EnterName = @HeadqVndName,@EnterDate = @now,\n" +
|
" @PostCode = @HeadqVndCode,@PostName = @HeadqVndName,@PostDate = @now,@ButtonType = @ButtonType,@ActionType=@ToDoActionType,\n" +
|
" @isSendWxCorpMsgs = 0,@CorpAgentId = null,@isCcUserCode = 0 \n" +
|
"\n" +
|
"--发送弹窗提示信息\n" +
|
" exec p9912V2 @MessageTxt = @theme ,@UnValidDate = @PopupMsgsUnValidDate,@CreateUser = @HeadqVndCode,@CreateName=@HeadqVndName,@Readers = @UserCodes ,@TipCount = 99 , \n" +
|
" @FormID = @formid ,@FormType = @FormType,@OrigFields = @doccode,@LinkFields = 'doccode' ,@LinkMode = 0 ,@Doccode=@doccode \n" +
|
|
" select @@rowcount \n";
|
return this.jdbcTemplate.queryForObject(sql, Integer.class, entity.getTheme(), entity.getContent(), entity.getUpload(), entity.getVndDbid(), entity.getDocCode());
|
}
|
|
@Override
|
public Integer callBack210610(T210610Entity entity) {
|
String sql = " set nocount on \n" +
|
" declare @DeliveredCode varchar(100),@DeliveredName varchar(100) \n" +
|
" select @DeliveredCode=cltCode,@DeliveredName=cltName from t110203 where dbid=? \n" +
|
" update a set a.DeliveredCode=isnull(a.DeliveredCode,'')+@DeliveredCode+',',a.DeliveredName=isnull(a.DeliveredName,'')+@DeliveredName+',' from t210610 a where a.docCode=?\n select @@rowcount";
|
return this.jdbcTemplate.queryForObject(sql, Integer.class, entity.getCltDbid(), entity.getDocCode());
|
}
|
|
@Transactional
|
@Override
|
public Integer del210610(T210610Entity entity) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" declare @docCode varchar(50),@dbid int =?,@HeadqVndCode varchar(50) \n" +
|
" declare @refcode varchar(20)=?, @rowcount int,@userCode varchar(50)='SYSTEM',\n" +
|
" @userName varchar(50)='系统管理员' ,@formId int=210610 ,@formName varchar(50)='通知消息'\n" +
|
" select @HeadqVndCode=vndCode from t110302 where dbid=@dbid" +
|
" select @docCode=docCode from t210610 where HeadqVndCode=@HeadqVndCode and refcode=@refcode \n" +
|
" if @@rowcount >0" +
|
" begin \n" +
|
" exec ppublicdel @docCode,@formId \n" +
|
" insert into gdocmodifylog(DocFormID,EventFormID,DocCode,DocDate, PreDocStatus,PostDocStatus,UserName,Event,EventTime ) \n" +
|
" select formid ,@formId,doccode,docdate,docstatus, docstatus , @userName,'删除单据',getdate() from t210610 with (nolock) where doccode =@docCode; \n" +
|
" select @refcode = refcode from t210610 with (nolock) where doccode =@docCode and blclosed = -1 \n" +
|
" if isnull(@refcode,'') <> ''\n" +
|
" begin\n" +
|
" update t210610 set cleardoccode = null ,blclosed = 0 where doccode =@refcode and blclosed = 1 \n" +
|
" end\n" +
|
" delete from t210610 where doccode =@docCode\n" +
|
" begin\n" +
|
" delete from _sys_fullcalendar where title like '%'+@docCode+'%' or description like '%'+@docCode+'%' or fullname like '%'+@docCode+'%' \n" +
|
" delete from t219001 where doccode=@docCode \n" +
|
" delete from spickorderlog where doccode=@docCode \n" +
|
" delete from t112002 where refformid =210610 and refdoccode=@docCode \n" +
|
" delete from _sysMessage where messagetxt like '%'+@docCode+'%' or origfields=@docCode\n" +
|
" select @@rowcount \n" +
|
" end\n" +
|
" end", Integer.class, entity.getVndDbid(), entity.getDocCode());
|
}
|
|
/**
|
* 取所有经销商数据
|
*
|
* @return
|
*/
|
@Override
|
public List<T110203VO> get110203ByDbid() {
|
return this.jdbcTemplate.query("set nocount on\n" +
|
" select dbid,isnull(PushMatCodeToDistributorLastDateTime,'2000-01-01 00:00:00') as PushMatCodeToDistributorLastDateTime,brands,isnull(PullSalesOrderFromDistributorLastDateTime,'2000-01-01 00:00:00') as pullSalesOrderFromDistributorLastDateTime,isnull(PullStockFromDistributorLastDateTime,'2000-01-01 00:00:00') as pullStockFromDistributorLastDateTime,authenticatedCode from t110203 where isnull(dbid,0)<>0", new BeanPropertyRowMapper<>(T110203VO.class));
|
}
|
|
@Override
|
public List<T110503Data> get110503List(T110203VO entity) {
|
String sql = "set nocount on\n" +
|
" declare @PushMatCodeToDistributorLastDateTime datetime=?,@Brands varchar(500)=? \n" +
|
" select a.MatGroup,a.MatGroupName,b.ParentRowId,b.rowid,b.treecontrol,\n" +
|
" a.MatCode,a.MatName,a.Special,a.SalesPrice,a.BaseUOM,\n" +
|
" a.brand,\n" +
|
" a.CalCostType,\n" +
|
" a.MatType,\n" +
|
" a.qrCode,\n" +
|
" a.baseuomrate,a.photoPath,a.salesUOM,a.purchaseUOM,a.uomrate,a.ratetxt \n" +
|
" from t110503 a join t110501 b on a.MatGroup=b.MatGroup \n" +
|
" where \n" +
|
" a.Brand in (select list from getinstr(@Brands)) \n" +
|
" and a.ModifyDate is null or a.ModifyDate between isnull(@PushMatCodeToDistributorLastDateTime,'2000-01-01 00:00:00') and getdate()";
|
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T110503Data.class), entity.getPushMatCodeToDistributorLastDateTime(),entity.getBrands());
|
}
|
|
@Transactional
|
@Override
|
public void do110503(T110503Entity entity) {
|
String sql = " declare @MatGroup varchar(50),@MatGroupName varchar(80),@ParentRowId varchar(20),@rowid varchar(20),@treecontrol varchar(600)\n" +
|
" declare @MatCode varchar(50),@MatName varchar(80),@Special varchar(80),@PurchasePrice money \n" +
|
" declare @doccode varchar(20),@companyID varchar(50),@companyName varchar(50),@now datetime = getdate()\n" +
|
" declare @HeadqVndCode varchar(50),@HeadqVndName varchar(50),@vndDbid int=" + entity.getVndDbid() + ",@PreDocStatus int,@PostDocStatus int,@DocStatusValue int,@FormId int = 110514,@pushLatDateTime datetime,@BaseUOM varchar(50),@PhotoPath varchar(200),@salesUOM varchar(20),@purchaseUOM varchar(20),@uomrate int,@ratetxt varchar(20),\n" +
|
" @brand varchar(50),\n" +
|
" @CalCostType varchar(50),\n" +
|
" @MatType varchar(50),\n" +
|
" @qrCode varchar(50),\n" +
|
" @baseuomrate int,@stCode varchar(50),@stName varchar(50)\n" +
|
" declare @sql varchar(2000) , @hasP110514SaveObject int = 0 \n" +
|
" if exists(select 1 from t110302 where isnull(dbid,0)=@vndDbid) \n" +
|
" begin \n" +
|
" if exists(select 1 from sys.objects where name = 'p110514Save') set @hasP110514SaveObject = 1 \n";
|
|
for (T110503Data data : entity.getList()) {
|
sql +=
|
" select @MatGroup =" + GridUtils.prossSqlParm(data.getMatGroup()) + ",@MatGroupName =" + GridUtils.prossSqlParm(data.getMatGroupName()) + ",@ParentRowId= " + GridUtils.prossSqlParm(data.getParentRowId()) + ",@rowid =" + GridUtils.prossSqlParm(data.getRowid()) + ",@treecontrol =" + GridUtils.prossSqlParm(data.getTreecontrol()) + "\n" +
|
" select @MatCode =" + GridUtils.prossSqlParm(data.getMatCode()) + ",@MatName =" + GridUtils.prossSqlParm(data.getMatName()) + ",@Special =" + GridUtils.prossSqlParm(data.getSpecial()) + ",@PurchasePrice = " + data.getSalesPrice() + ",@pushLatDateTime=" + GridUtils.prossSqlParm(entity.getNowDateTime()) + ",@BaseUOM=" + GridUtils.prossSqlParm(data.getBaseUOM()) + ",@brand=" + GridUtils.prossSqlParm(data.getBrand()) + ",@CalCostType=" + GridUtils.prossSqlParm(data.getCalCostType()) + ",@MatType=" + GridUtils.prossSqlParm(data.getMatType()) + ",@qrCode=" + GridUtils.prossSqlParm(data.getQrCode()) + ",@baseuomrate=" + data.getBaseuomrate() + ",@salesUOM=" + GridUtils.prossSqlParm(data.getSalesUOM()) + ",@purchaseUOM=" + GridUtils.prossSqlParm(data.getPurchaseUOM()) + ",@uomrate=" + data.getUomrate() + ",@ratetxt=" + GridUtils.prossSqlParm(data.getRatetxt()) + ",@stCode='A',@PhotoPath=" + GridUtils.prossSqlParm(data.getPhotoPath()) + "\n" +
|
" if not exists(select 1 from t110501 a where a.MatGroup = @MatGroup) \n" +
|
" begin\n" +
|
" if exists(select 1 \n" +
|
" from sys.objects a join sys.columns b on a.object_id = b.object_id\n" +
|
" where a.name = 't110501' and b.name = 'matgroup' and b.is_computed = 1 ) \n" +
|
" begin \n" +
|
" select @sql = 'insert into t110501 (ShopMatGroup,matgroupname,rowid,parentrowid,treeControl) ' + \n" +
|
" ' values (''' + isnull(@matgroup,'') + ''',''' + isnull(@matgroupname,'') +''','''+isnull(@rowid,'') +''',''' + isnull(@parentrowid,'')+''',''' +isnull(@treecontrol,'') +''')' \n" +
|
" exec(@sql) \n" +
|
" end else \n" +
|
" begin \n" +
|
" select @sql = 'insert into t110501 (MatGroup,matgroupname,rowid,parentrowid,treeControl) ' + \n" +
|
" ' values (''' + isnull(@matgroup,'') + ''',''' + isnull(@matgroupname,'') +''','''+isnull(@rowid,'') +''',''' + isnull(@parentrowid,'')+''',''' +isnull(@treecontrol,'') +''')' \n" +
|
" exec(@sql) \n" +
|
" end \n" +
|
" end\n" +
|
" select @DocCode = a.DocCode from t110503 a where a.matcode= @matcode \n" +
|
" if @@rowcount > 0 \n" +
|
" begin \n" +
|
" update a set a.MatName = @MatName ,a.Special = @Special ,a.PurchasePrice = @PurchasePrice,\n" +
|
" a.PushMatCodeToDistributorLastDateTime=@pushLatDateTime,a.BaseUOM=@BaseUOM,a.brand=@brand,a.CalCostType=@CalCostType,a.MatType=@MatType,a.qrCode=@qrCode,a.baseuomrate=@baseuomrate,a.PhotoPath=@PhotoPath,a.MatGroup=@MatGroup,matgroupname=@MatGroupName \n" +
|
" from t110503 a where a.matcode= @matcode \n" +
|
" end else \n" +
|
" begin\n" +
|
" select @HeadqVndCode=vndCode,@HeadqVndName=vndName,@companyID=companyId,@companyName=companyName from t110302 where dbid=@vndDbid \n" +
|
" exec sp_newdoccode @FormId,'SYSTEM',@doccode output \n" +
|
" if exists(select 1 \n" +
|
" from sys.objects a join sys.columns b on a.object_id = b.object_id\n" +
|
" where a.name = 't110503' and b.name = 'matcode' and b.is_computed = 1 ) \n" +
|
" begin \n" +
|
" select @sql = 'insert into t110503 (doccode, formid, periodid, docdate, companyid, docstatus, entercode, entername, enterdate, modifyname, modifydate,ShopMatCode,matname,special,PurchasePrice,PushMatCodeToDistributorLastDateTime,MatGroup,matgroupname,BaseUOM,brand,vndcode,vndname,CalCostType,MatType,qrCode,baseuomrate,stCode,PhotoPath,salesUOM,purchaseUOM,uomrate,ratetxt) ' + \n" +
|
" ' values (''' +isnull(@doccode,'')+ ''',''' +cast(isnull(@FormId,0) as varchar(20))+ ''',''' + dbo.GetPeriodID(@FormId,@companyID,convert(varchar(10),@now,120))+ ''',''' + convert(char(10),@now,120)+ ''',''' +\n" +
|
" @companyid+ ''',0,''' +isnull(@HeadqVndCode,'')+ ''',''' + isnull(@HeadqVndName,'')+ ''',''' + convert(varchar(10),@now,120)+ ''',''' + isnull(@HeadqVndName,'')+ ''',''' + convert(varchar(10),@now,120)+ ''',''' +isnull(@matcode,'')+ ''',''' +isnull(@matname,'')+ ''',''' +isnull(@special,'')+ ''',''' +cast(isnull(@PurchasePrice,0) as varchar(20))+ ''',''' +convert(varchar(19),@pushLatDateTime,120)+ ''',''' + isnull(@MatGroup,'')+ ''',''' + isnull(@matgroupname,'')+ ''',''' + isnull(@BaseUOM,'')+ ''',''' + isnull(@brand,'')+ ''',''' + isnull(@HeadqVndCode,'')+ ''',''' + isnull(@HeadqVndName,'')+ ''',''' + isnull(@CalCostType,'')+ ''',''' + isnull(@MatType,'')+ ''',''' + isnull(@qrCode,'')+ ''',''' + cast(isnull(@baseuomrate,0) as varchar(20))+''',''A'','''+isnull(@PhotoPath,'')+''',''' + cast(isnull(@salesUOM,'') as varchar(20))+''',''' + cast(isnull(@purchaseUOM,'') as varchar(20))+''',''' + cast(isnull(@uomrate,0) as varchar(20))+''',''' + cast(isnull(@ratetxt,'') as varchar(20))+''')' \n" +
|
" exec(@sql) \n" +
|
" end else \n" +
|
" begin \n" +
|
" select @sql = 'insert into t110503 (doccode, formid, periodid, docdate, companyid, docstatus, entercode, entername, enterdate, modifyname, modifydate,MatCode,matname,special,PurchasePrice,PushMatCodeToDistributorLastDateTime,MatGroup,matgroupname,BaseUOM,brand,vndcode,vndname,CalCostType,MatType,qrCode,baseuomrate,stCode,PhotoPath,salesUOM,purchaseUOM,uomrate,ratetxt) ' + \n" +
|
" ' values (''' +isnull(@doccode,'')+ ''',''' +cast(isnull(@FormId,0) as varchar(20))+ ''',''' + dbo.GetPeriodID(@FormId,@companyID,convert(varchar(10),@now,120))+ ''',''' + convert(char(10),@now,120)+ ''',''' +\n" +
|
" @companyid+ ''',0,''' +isnull(@HeadqVndCode,'')+ ''',''' + isnull(@HeadqVndName,'')+ ''',''' + convert(varchar(10),@now,120)+ ''',''' + isnull(@HeadqVndName,'')+ ''',''' + convert(varchar(10),@now,120)+ ''',''' +isnull(@matcode,'')+ ''',''' +isnull(@matname,'')+ ''',''' +isnull(@special,'')+ ''',''' +cast(isnull(@PurchasePrice,0) as varchar(20))+ ''',''' +convert(varchar(19),@pushLatDateTime,120)+ ''',''' + isnull(@MatGroup,'')+ ''',''' + isnull(@matgroupname,'')+ ''',''' + isnull(@BaseUOM,'')+ ''',''' + isnull(@brand,'')+ ''',''' + isnull(@HeadqVndCode,'')+ ''',''' + isnull(@HeadqVndName,'')+ ''',''' + isnull(@CalCostType,'')+ ''',''' + isnull(@MatType,'')+ ''',''' + isnull(@qrCode,'')+ ''',''' + cast(isnull(@baseuomrate,0) as varchar(20))+''',''A'','''+isnull(@PhotoPath,'')+''',''' + cast(isnull(@salesUOM,'') as varchar(20))+''',''' + cast(isnull(@purchaseUOM,'') as varchar(20))+''',''' + cast(isnull(@uomrate,0) as varchar(20))+''',''' + cast(isnull(@ratetxt,'') as varchar(20))+''')' \n" +
|
" exec(@sql) \n" +
|
" end \n" +
|
" end \n" +
|
" if isnull(@hasP110514SaveObject,0) = 1 \n" +
|
" begin\n" +
|
" exec('exec p110514Save ''' + @DocCode + '''') \n" +
|
" end \n";
|
}
|
sql += " end else\n" +
|
" begin \n" +
|
" -- raiserror('在110302中找不到vndDbid为【%d】的供应商,不能更新主物料数据',16,1,@vndDbid)\n" +
|
" return \n" +
|
" end";
|
this.jdbcTemplate.execute(sql);
|
}
|
|
@Transactional
|
@Override
|
public Integer updateLastDateTimeBy110503(T110503Entity entity) {
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" update a set a.PushMatCodeToDistributorLastDateTime=? from t110203 a where a.dbid=? \n select @@rowcount", Integer.class, entity.getNowDateTime(), entity.getCltDbid());
|
}
|
|
@Transactional
|
@Override
|
public Integer update120201ByVndCode(CallBack120201Entity entity) {
|
final FormEntity formEntityInfo = getFormEntityInfo(entity.getAffectedFormIdForTargetDatabase());
|
return this.jdbcTemplate.queryForObject("set nocount on \n" +
|
" declare @HeadqVndCode varchar(50),@HeadqVndName varchar(50),@dbid int=?,@docCodeList varchar(500)=? \n" +
|
" select @HeadqVndCode=vndCode,@HeadqVndName=vndName from t110302 where isnull(dbid,0)=@dbid \n" +
|
" if @@rowcount>0 \n" +
|
" begin \n" +
|
" update a set a.HeadqVndCode=@HeadqVndCode ,a.HeadqVndName=@HeadqVndName from "+formEntityInfo.getHDtable()+" a where a.doccode in(@docCodeList) \n end \n select @@rowcount ", Integer.class, entity.getVndDbid(), entity.getDocCodeList());
|
}
|
|
/**
|
* @param uuid
|
* @param formid
|
* @param fieldid
|
* @param headflag
|
* @return
|
*/
|
@Override
|
public List<AttachmentEntity> getAttachmentInfo(String uuid, Integer formid, String fieldid, int headflag) {
|
if (StringUtils.isBlank(uuid)) {
|
return null;
|
}
|
String sql = "";
|
if (headflag == 0) {
|
sql = "select case when isnull(controltype,0)=19 then '_sys_Attachment' else '_sys_Attachment9' end as name from gField \n" +
|
"where formid=? and fieldid=? and headflag=?";
|
} else {
|
sql = "select case when isnull(gridcontroltype,0)=19 then '_sys_Attachment' else '_sys_Attachment9' end as name from gField \n" +
|
"where formid=? and fieldid=? and headflag=?";
|
}
|
String tableName = this.jdbcTemplate.queryForObject(sql, String.class, formid, fieldid, headflag);
|
|
List<AttachmentEntity> list = this.jdbcTemplate.query("set nocount on \n" +
|
" select unid,seq,FileType,formid,fieldid,OriginalFileName,FileSize from " + tableName + " where unid=?", new BeanPropertyRowMapper<>(AttachmentEntity.class), uuid);
|
if ("_sys_Attachment9".equalsIgnoreCase(tableName)) {
|
if (list != null && list.size() > 0) {
|
list.stream().forEach(x -> {
|
//0表示单附件9类型
|
x.setAttachemntType("0");
|
});
|
}
|
}
|
return list;
|
}
|
}
|
