package com.zy.common.service.erp;
|
|
import com.baomidou.mybatisplus.MybatisSqlSessionTemplate;
|
import com.baomidou.mybatisplus.mapper.EntityWrapper;
|
import com.core.common.Cools;
|
import com.core.common.DateUtils;
|
import com.core.exception.CoolException;
|
import com.zy.asrs.service.OutStockMainService;
|
import com.zy.asrs.service.OutStockService;
|
import com.zy.common.service.erp.dto.InStockDto;
|
import com.zy.common.service.erp.dto.PInStockDto;
|
import com.zy.common.service.erp.entity.*;
|
import lombok.extern.slf4j.Slf4j;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
|
import java.math.BigDecimal;
|
import java.text.MessageFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
/**
|
* Created by vincent on 2020/11/27
|
*/
|
@Slf4j
|
@Service
|
public class ErpService {
|
|
@Autowired
|
private ErpSqlServer erpSqlServer;
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
public List<StockEntryCensus> get(){
|
return erpSqlServer.select("select * from StockEntryCensus where 1=1",StockEntryCensus.class);
|
}
|
|
public List<M_item> getItem(){
|
return erpSqlServer.select("select * from M_item where 1=1",M_item.class);
|
}
|
|
public boolean deleteItem(String Mnumber) {
|
return erpSqlServer.update("delete from M_item where 1=1 and Mnumber = '" + Mnumber + "'") > 0;
|
}
|
|
//////////////////////成品、原材料入库通知单数据提取//////////////////////////////////////////////////
|
/**
|
* 提取中间表原材料入库单据
|
*/
|
public List<InStockDto> getReadyInStock() {
|
List<InStockDto> result = new ArrayList<>();
|
String sql = "select * from InStockBill where 1=1 and Fflag_rw = 0";
|
List<InStockBill> inStockBills = erpSqlServer.select(sql, InStockBill.class);
|
if (Cools.isEmpty(inStockBills)) {
|
return result;
|
}
|
for (InStockBill bill : inStockBills) {
|
InStockDto inStockDto = new InStockDto();
|
inStockDto.setInStockBill(bill);
|
List<InStockBillEntry> inStockBillEntries = getInStockBillEntry(bill.getFInterID());
|
if (!Cools.isEmpty(inStockBillEntries)) {
|
inStockDto.setInStockBillEntries(inStockBillEntries);
|
}
|
result.add(inStockDto);
|
}
|
return result;
|
}
|
|
/**
|
* 查询同一单号是否存在相同物料的最大数量值，
|
* @return
|
*/
|
public Number QueryFnumberInCount(Integer FInterID) {
|
String sql = "select top 1 qty from (\n" +
|
"select Fnumber,count(*) as qty from InStockbill a,InStockbillEntry b\n" +
|
"where a.FInterID=b.FInterID and a.FInterID=" + FInterID +
|
" group by Fnumber\n" +
|
") a\n" +
|
"order by qty desc";
|
return erpSqlServer.executeQueryCount(sql,"qty");
|
}
|
|
/**
|
* 获取入库物流明细
|
* @param fInterID 单据内码
|
*/
|
private List<InStockBillEntry> getInStockBillEntry(Integer fInterID) {
|
// String sql = "select * from InStockBillEntry where 1=1 and FSourceBillNo!='' and FAuxCommitQty=0 and FInterID = " + fInterID;
|
String sql = "select * from InStockBillEntry where 1=1 and FAuxCommitQty=0 and FInterID = " + fInterID;
|
return erpSqlServer.select(sql, InStockBillEntry.class);
|
}
|
|
/**
|
* 将未读的原材料入库单据设置为已读
|
*/
|
public boolean haveReadInStock(String FBillNo, Integer value){
|
String sql = "update InStockBill set Fflag_rw = {1,number,#} where FBillNo = ''{0}''";
|
sql = MessageFormat.format(sql, FBillNo, value);
|
return erpSqlServer.update(sql) > 0;
|
}
|
|
/**
|
* 提取中间表成品入库单据
|
*/
|
public List<CPICMO> getReadyCPICMO() {
|
// String sql = "select * from CPICMO where 1=1 and FSourceBillNo!='' and FAuxCommitQty=0 and Fflag_rw = 0";
|
String sql = "select * from CPICMO where 1=1 and FAuxCommitQty=0 and Fflag_rw = 0";
|
return erpSqlServer.select(sql, CPICMO.class);
|
}
|
|
/**
|
* 查询同一单号是否存在相同物料的最大数量值，
|
* @return
|
*/
|
public Number QueryFnumberCPICMOCount(Integer FInterID) {
|
String sql = "select top 1 qty from (\n" +
|
"select count(*) as qty from CPICMO\n" +
|
"where FInterID=" + FInterID +
|
" group by Fnumber\n" +
|
") a\n" +
|
"order by qty desc";
|
return erpSqlServer.executeQueryCount(sql,"qty");
|
}
|
|
/**
|
* 将未读的成品入库单据设置为已读
|
*/
|
public boolean haveReadCPICMO(Integer FInterID, Integer value){
|
String sql = "update CPICMO set Fflag_rw = {1,number,#} where FInterID = ''{0,number,#}''";
|
sql = MessageFormat.format(sql, FInterID, value);
|
return erpSqlServer.update(sql) > 0;
|
}
|
////////////////////////////////////////////////////////////////////////////////////////
|
|
////////////////////// 出库通知单数据提取//////////////////////////////////////////////////
|
/**
|
* 查询erp中间表表头OutStockBill数据
|
* @return
|
*/
|
public List<OutStockBill> syncOutStock() {
|
return erpSqlServer.select("SELECT * FROM xtyasrs_dual.dbo.OutStockBill where Fflag_rw=0", OutStockBill.class);
|
// return erpSqlServer.select("SELECT * FROM xtyasrs_dual.dbo.OutStockBill where Fflag_rw=0", OutStockBill.class);
|
}
|
|
/**
|
* 查询同一单号是否存在相同物料的最大数量值，
|
* @return
|
*/
|
public Number QueryFnumberOutCount(Integer FInterID) {
|
String sql = "select top 1 qty from (\n" +
|
"select Fnumber,count(*) as qty from OutStockbill a,OutStockbillEntry b\n" +
|
"where a.FInterID=b.FInterID and a.FInterID=" + FInterID +
|
" group by Fnumber\n" +
|
") a\n" +
|
"order by qty desc";
|
return erpSqlServer.executeQueryCount(sql,"qty");
|
}
|
|
|
/**
|
* 查询erp中间表表体OutStockbillEntry数据
|
* @param FInterID
|
* @return
|
*/
|
public List<OutStockBillEntry> syncOutStockDetail(Integer FInterID) {
|
// String sql = "SELECT * FROM xtyasrs_dual.dbo.OutStockBillEntry where FSourceBillNo!='' and FAuxCommitQty=0 and FInterID=" + FInterID;
|
String sql = "SELECT * FROM xtyasrs_dual.dbo.OutStockBillEntry where FAuxCommitQty=0 and FInterID=" + FInterID;
|
return erpSqlServer.select(sql, OutStockBillEntry.class);
|
}
|
|
/**
|
* 将提取完成的成品出库通知单据设置为已读
|
*/
|
public boolean haveReadOutBill(String FBrNo, Integer FInterID, Integer value){
|
String sql = "update OutStockbill set Fflag_rw = {2,number,#} where FBillNo = ''{0}'' and FInterID = {1,number,#} ";
|
sql = MessageFormat.format(sql, FBrNo, FInterID, value);
|
return erpSqlServer.update(sql) > 0;
|
}
|
////////////////////////////////////////////////////////////////////////////////////////////
|
|
|
|
|
/**
|
* 提交实际收货数量
|
* @param FInterID 单据内码 -- 关联
|
* @param FItemID 物料内码
|
* @param FQty 实际数量
|
*/
|
public boolean actFQtySubmit(Integer FInterID, Integer FItemID, Double FQty){
|
String sql = "update InStockBillEntry set FQty = {0,number,#} where FInterID = {1,number,#} and FItemID = {2,number,#}";
|
sql = MessageFormat.format(sql, FQty, FInterID, FItemID);
|
return erpSqlServer.update(sql) > 0;
|
}
|
|
/**
|
* 原材料增量入库
|
*/
|
public boolean incrementPakIn(String FBillNo, String Fnumber, Double increment) {
|
// String[] arrays = Fnumbers.split("|");
|
// String Fnumber="",sBillNo=""; //产品代码,生产单号
|
|
String sql = "update ise \n" +
|
"set ise.FAuxCommitQty = (ise.FAuxCommitQty + {0,number,#}) \n" +
|
"from InStockBillEntry ise\n" +
|
"left join InStockbill isb on isb.FInterID = ise.FInterID\n" +
|
"where 1=1 \n" +
|
"and ise.Fnumber = ''{1}''\n" +
|
"and isb.FBillNo= ''{2}''\n";
|
sql = MessageFormat.format(sql, increment, Fnumber, FBillNo);
|
|
// Fnumber = arrays[0];
|
// if(arrays.length>1){
|
// sBillNo = arrays[1];
|
// sql = sql + "and ise.FSourceBillNo= ''{3}''";
|
// sql = MessageFormat.format(sql, increment, Fnumber, FBillNo, sBillNo);
|
// } else {
|
// sql = MessageFormat.format(sql, increment, Fnumber, FBillNo);
|
// }
|
|
if (erpSqlServer.update(sql) > 0) {
|
// log.error("更新ERP中间表成功===>>[FBillNo:{},Fnumber:{},increment:{}]",FBillNo,Fnumber,increment);
|
List<InStockBillEntry> inStockBillEntries = getInStockBillEntry(FBillNo);
|
boolean complete = true;
|
// 比较单笔资料物料是否全部完成
|
for (InStockBillEntry entry : inStockBillEntries) {
|
// if (entry.getFQty() > 0) {
|
// if (entry.getFAuxCommitQty() < entry.getFQty()) {
|
// complete = false;
|
// break;
|
// }
|
// } else {
|
if (entry.getFAuxCommitQty() < entry.getFAuxQty()) {
|
complete = false;
|
break;
|
}
|
// }
|
}
|
// 如果任务完成，则标记完成位
|
if (complete) {
|
if (!completePakIn(FBillNo)) {
|
log.error("{}入库单标记完成失败", FBillNo);
|
}
|
}
|
return true;
|
} else {
|
log.error("更新ERP中间表失败===>>[FBillNo:{},Fnumber:{},increment:{}]",FBillNo,Fnumber,increment);
|
return false;
|
}
|
}
|
|
/**
|
* 成品增量入库
|
*/
|
public boolean incrementCPakIn(String FBillNo, String Fnumber, Double increment) {
|
// String[] arrays = Fnumbers.split("|");
|
// String Fnumber="",sBillNo=""; //产品代码,生产单号
|
|
String sql = "update CPICMO set FAuxCommitQty = (FAuxCommitQty + {0,number,#}) where 1=1 and Fnumber = ''{1}'' and FBillNo = ''{2}'' ";
|
sql = MessageFormat.format(sql, increment, Fnumber, FBillNo);
|
|
// Fnumber = arrays[0];
|
// if(arrays.length>1){
|
// sBillNo = arrays[1];
|
// sql = sql + " and FSourceBillNo= ''{3}''";
|
// sql = MessageFormat.format(sql, increment, Fnumber, FBillNo, sBillNo);
|
// } else {
|
// sql = MessageFormat.format(sql, increment, Fnumber, FBillNo);
|
// }
|
|
if (erpSqlServer.update(sql) > 0) {
|
// if(arrays.length>1) {
|
// sql = "select * from CPICMO where 1=1 and Fnumber = ''{0}'' and FBillNo = ''{1}'' and FSourceBillNo= ''{2}''";
|
// sql = MessageFormat.format(sql, Fnumber, FBillNo, sBillNo);
|
// } else {
|
sql = "select * from CPICMO where 1=1 and Fnumber = ''{0}'' and FBillNo = ''{1}''";
|
sql = MessageFormat.format(sql, Fnumber, FBillNo);
|
// }
|
List<CPICMO> select = erpSqlServer.select(sql, CPICMO.class);
|
CPICMO cpicmo = select.get(0);
|
boolean complete = false;
|
if (cpicmo.getFQty() > 0) {
|
if (Double.doubleToLongBits(cpicmo.getFAuxCommitQty()) == Double.doubleToLongBits(cpicmo.getFQty())) {
|
complete = true;
|
}
|
} else {
|
if (Double.doubleToLongBits(cpicmo.getFAuxCommitQty()) == Double.doubleToLongBits(cpicmo.getFAuxQty())) {
|
complete = true;
|
}
|
}
|
if (complete) {
|
if (!completeCPakIn(FBillNo, Fnumber)) {
|
log.error("{}入库单标记完成失败", FBillNo);
|
}
|
}
|
return true;
|
} else {
|
return false;
|
}
|
}
|
|
|
/**
|
* 库存明细同步
|
* @param FItemID 物料内码
|
* @param qty 数量（正表示增加库存、负表示减少库存）
|
*/
|
public boolean stockEntitySync(Integer FItemID, Integer FEntryID, Double qty) {
|
String sql = "select * from StockEntryCensus where 1=1 and FItemID = {0,number,#} and FEntryID = {1,number,#}";
|
sql = MessageFormat.format(sql, FItemID, FEntryID);
|
List<StockEntryCensus> censuses = erpSqlServer.select(sql, StockEntryCensus.class);
|
if (Cools.isEmpty(censuses)) {
|
// 新增
|
sql = "insert [dbo].[StockEntryCensus] ([FItemID], [FEntryID], [StockQty], [UpdateTime]) values ({0,number,#}, {1,number,#}, {2,number,#}, ''{3}'');";
|
sql = MessageFormat.format(sql, FItemID, FEntryID, qty, DateUtils.convert(new Date()));
|
if (erpSqlServer.update(sql) == 0) {
|
log.error("{}新增物料库存明细失败", FItemID);
|
return false;
|
}
|
} else {
|
// 修改
|
StockEntryCensus census = censuses.get(0);
|
// 减少库存
|
if (qty < 0) {
|
// 删除
|
if (census.getStockQty() < Math.abs(qty)) {
|
sql = "delete from StockEntryCensus where FItemID = {0,number,#} and FEntryID = {1,number,#}";
|
sql = MessageFormat.format(sql, FItemID, FEntryID);
|
if (erpSqlServer.update(sql) == 0) {
|
log.error("{}删除物料库存明细失败", FItemID);
|
return false;
|
}
|
// 减少
|
} else {
|
sql = "update StockEntryCensus set StockQty = (StockQty - {0,number,#}) where FItemID = {1,number,#} and FEntryID = {2,number,#}";
|
sql = MessageFormat.format(sql, Math.abs(qty), FItemID, FEntryID);
|
if (erpSqlServer.update(sql) == 0) {
|
log.error("{}减少物料库存明细失败", FItemID);
|
return false;
|
}
|
}
|
// 增加库存
|
} else {
|
sql = "update StockEntryCensus set StockQty = (StockQty + {0,number,#}) where FItemID = {1,number,#} and FEntryID = {2,number,#}";
|
sql = MessageFormat.format(sql, Math.abs(qty), FItemID, FEntryID);
|
if (erpSqlServer.update(sql) == 0) {
|
log.error("{}增加物料库存明细失败", FItemID);
|
return false;
|
}
|
}
|
}
|
return true;
|
}
|
|
/**
|
* 库存盘点
|
* @param FNumber 物料内码
|
* @param qty 数量（正表示盘盈、负表示盘亏）
|
*/
|
public boolean checkStockEntity(String FNumber, Double qty){
|
// String[] arrays = FNumber.split("|");
|
// FNumber = arrays[0];
|
String sql = "insert [dbo].[StockCheckRecord] ([FNumber],[CheckQty],[Fflag_rw],[Fflag_finish],[sync_id]) values (''{0}'', {1,number,#}, 0, 0, -1);";
|
sql = MessageFormat.format(sql, FNumber, qty);
|
if (erpSqlServer.update(sql) == 0) {
|
log.error("{}新增盘点记录失败", FNumber);
|
return false;
|
}
|
return true;
|
}
|
|
// 私有方法 --------------------------------------------------------------------------------------
|
|
private List<InStockBillEntry> getInStockBillEntry(String FBillNo) {
|
String sql = "select ise.* from InStockBillEntry ise left join InStockbill isb on isb.FInterID = ise.FInterID where 1=1 and isb.FBillNo = '" + FBillNo +"'";
|
return erpSqlServer.select(sql, InStockBillEntry.class);
|
}
|
|
/**
|
* 原材料入库单标记完成
|
*/
|
private boolean completePakIn(String FBillNo){
|
String sql = "update InStockBill set Fflag_finish = 1 where FBillNo = ''{0}''";
|
sql = MessageFormat.format(sql, FBillNo);
|
return erpSqlServer.update(sql) > 0;
|
}
|
|
/**
|
* 成品入库单标记完成
|
*/
|
private boolean completeCPakIn(String FBillNo, String Fnumber){
|
// if(Cools.isEmpty(sBillNo)) {
|
String sql = "update CPICMO set Fflag_finish = 1 where FBillNo = ''{0}'' and Fnumber = ''{1}''";
|
sql = MessageFormat.format(sql, FBillNo, Fnumber);
|
return erpSqlServer.update(sql) > 0;
|
// } else {
|
// String sql = "update CPICMO set Fflag_finish = 1 where FBillNo = ''{0}'' and Fnumber = ''{1}'' and FSourceBillNo= ''{2}'' ";
|
// sql = MessageFormat.format(sql, FBillNo, Fnumber, sBillNo);
|
// return erpSqlServer.update(sql) > 0;
|
// }
|
}
|
|
|
// /**
|
// * 出库单标记完成
|
// */
|
// private boolean completePakOut(Integer fInterID){
|
// String sql = "update OutStockBill set Fflag_finish = 1 where FInterID = {0,number,#}";
|
// sql = MessageFormat.format(sql, fInterID);
|
// return erpSqlServer.update(sql) > 0;
|
// }
|
|
/* 成品增量出库 */
|
public boolean incrementCPakOut(Integer FInterID, String Fnumber, Double increment, String FBillNo) {
|
// String[] arrays = Fnumbers.split("|");
|
// String Fnumber="",sBillNo=""; //产品代码,生产单号
|
|
String sql = "update OutStockbillEntry set FAuxCommitQty = (FAuxCommitQty + {0,number,#}) where 1=1 and Fnumber = ''{1}'' and FInterID = {2,number,#} ";
|
sql = MessageFormat.format(sql, increment, Fnumber, FInterID);
|
|
// Fnumber = arrays[0];
|
// if(arrays.length>1){
|
// sBillNo = arrays[1];
|
// sql = sql + " and FSourceBillNo= ''{3}''";
|
// sql = MessageFormat.format(sql, increment, Fnumber, FInterID, sBillNo);
|
// } else {
|
// sql = MessageFormat.format(sql, increment, Fnumber, FInterID);
|
// }
|
|
try {
|
if (erpSqlServer.update(sql) > 0) {
|
// if(arrays.length>1) {
|
// sql = "select * from OutStockbillEntry where 1=1 and Fnumber = ''{0}'' and FInterID = {1,number,#} and FSourceBillNo= ''{2}''";
|
// sql = MessageFormat.format(sql, Fnumber, FInterID, sBillNo);
|
// } else {
|
sql = "select * from OutStockbillEntry where 1=1 and Fnumber = ''{0}'' and FInterID = {1,number,#}";
|
sql = MessageFormat.format(sql, Fnumber, FInterID);
|
// }
|
|
List<OutStockBillEntry> select = erpSqlServer.select(sql, OutStockBillEntry.class);
|
OutStockBillEntry outStockBillEntry = select.get(0);
|
boolean complete = false;
|
if (outStockBillEntry.getFQty().compareTo(BigDecimal.ZERO) == 1) {
|
if (outStockBillEntry.getFAuxCommitQty().compareTo(outStockBillEntry.getFQty()) > -1) {
|
complete = true;
|
}
|
} else {
|
if (outStockBillEntry.getFAuxCommitQty().compareTo(outStockBillEntry.getFAuxQty()) > -1) {
|
complete = true;
|
}
|
}
|
if (complete) {
|
sql = "select * from OutStockbillEntry where 1=1 and FInterID = {1,number,#}";
|
sql = MessageFormat.format(sql, Fnumber, FInterID);
|
List<OutStockBillEntry> select1 = erpSqlServer.select(sql, OutStockBillEntry.class);
|
|
boolean flag = true;
|
for(OutStockBillEntry outStockBillEntry1 : select1){
|
if (outStockBillEntry1.getFAuxCommitQty().compareTo(outStockBillEntry1.getFQty()) == -1) {
|
flag = false;
|
}
|
}
|
|
if(flag){
|
if (!completeCPakOut(FBillNo) ) {
|
log.error("{}出库单标记完成失败", FBillNo);
|
}
|
int updateCount = jdbcTemplate.update("update OutStockbill set Fflag_finish=1 where FBillNo='" + FBillNo + "'");
|
if(updateCount<=0){
|
throw new CoolException("更新出库通知档完成标记失败[FBillNo="+FBillNo+"]");
|
}
|
}
|
}
|
return true;
|
} else {
|
return false;
|
}
|
}catch (Exception e){
|
String msg = "incrementCPakOut成品增量出库失败[FInterID={0},Fnumber={1},FBillNo={2}]\n";
|
msg = MessageFormat.format(msg,FInterID,Fnumber,FBillNo);
|
log.error(msg, e);
|
// e.printStackTrace();
|
log.error("incrementCPakOut----" + sql);
|
return false;
|
}
|
|
}
|
|
/* 成品出库单标记完成 */
|
private boolean completeCPakOut(String FBillNo){
|
String sql = "update OutStockbill set Fflag_finish = 1 where FBillNo = ''{0}''";
|
sql = MessageFormat.format(sql, FBillNo);
|
return erpSqlServer.update(sql) > 0;
|
}
|
|
|
// public boolean updateFnumber(Integer itemId, String FNumber){
|
// String sql = "update InStockbillEntry set Fnumber = ''{0}'' where FItemID = {1,number,#}";
|
// sql = MessageFormat.format(sql, FNumber, itemId);
|
// return erpSqlServer.update(sql) > 0;
|
// }
|
|
// public boolean updateCFnumber(Integer itemId, String FNumber){
|
// String sql = "update CPICMO set Fnumber = ''{0}'' where FItemID = {1,number,#}";
|
// sql = MessageFormat.format(sql, FNumber, itemId);
|
// return erpSqlServer.update(sql) > 0;
|
// }
|
|
public List<CPICMO> queryErpCPICMO(String fbillNo, String fsourceBillNo) {
|
String sql = "select * from CPICMO";
|
if (!Cools.isEmpty(fbillNo) && Cools.isEmpty(fsourceBillNo)) {
|
sql = "select * from CPICMO where FBillNo = ''{0}''";
|
sql = MessageFormat.format(sql, fbillNo);
|
} else if (Cools.isEmpty(fbillNo) && !Cools.isEmpty(fsourceBillNo)) {
|
sql = "select * from CPICMO where FSourceBillNo = ''{0}''";
|
sql = MessageFormat.format(sql, fsourceBillNo);
|
} else if (!Cools.isEmpty(fbillNo) && !Cools.isEmpty(fsourceBillNo)) {
|
sql = "select * from CPICMO where FBillNo = ''{0}'' and FSourceBillNo = ''{1}''";
|
sql = MessageFormat.format(sql, fbillNo, fsourceBillNo);
|
}
|
return erpSqlServer.select(sql, CPICMO.class);
|
}
|
}
|
