服务器端批号分摊

//批号分摊
function batchShare(billno,billcode,ruleid,dbtype) {


function _copyNotesDt(alias,dataset,doclx,retlx,row){
//doclx 分字符串拼接
//retlx 0 表示select时拼接sql宏；1表示增加行赋值 ；2表示insert时数据集取值
//row 行号
var fldlist = "";
var a = "billno,billsn,entid,billsort,goodsid,ownerid,whorgid,whid,packid,unit";
a += ",meas,angleid,basenum,num,execnum,cancelnum,price,taxprice,dedrate,baseprice";
a += ",basetaxprice,amount,rate,tax,taxamount,profit,isfree,isdone,rfbillno,rfbillcode";
a += ",rfbillsn,rfruleid,remark,oldbillno,oldbillcode,oldbillsn,oldruleid,locatid";
var b = "ftnum,ftwc";
var c = "goodscode,goodsname";
var d = "whname,iscontrol";
if (doclx == "a"){
fldlist = a;
}else if(doclx == "b") {
fldlist = b;
}else if(doclx == "c") {
fldlist = c;
}else if(doclx == "d") {
fldlist = d;
}else{
fldlist= a +","+ b +","+ c +","+ d;
}
var arr_fld = fldlist.split(",");
var retstr = "";


for(var i = 0; i < arr_fld.length; i++)
{
if (retlx == 0){
if(i == 0) {
retstr += alias + arr_fld[i];
}else {
retstr += "," + alias + arr_fld[i];
}
}else if(retlx == 1){
dataset.field(arr_fld[i]).value = dataset.getValueAt(row,arr_fld[i]);
}else {
if(i == 0) {
retstr += "'" + dataset.getValue(arr_fld[i]) + "'";
}else {
retstr += ",'" + dataset.getValueAt(row,arr_fld[i]) + "'";
}
}
}
if (retlx == 0 || retlx == 2){
return retstr;
}
}


function _regGoodsOccu(billno,billsn,billcode,ruleid,dsOccu,row) {
var sql = " insert into GoodsOccu(entid,billno,billsn,billcode,ruleid"
+ ",goodsid,angleid,basenum,ownerid,whid,locatid,whorgid) "
+ " values('" + dsOccu.getValueAt(row, "entid") + "'," + billno
+ "," + billsn + ",'" + billcode
+ "','" + ruleid + "'" + " ,'" + dsOccu.getValueAt(row, "goodsid")
+ "','" + dsOccu.getValueAt(row, "angleid") + "'," + dsOccu.getValueAt(row, "ftnum")
+ " ,'" + dsOccu.getValueAt(row, "ownerid") + "','" + dsOccu.getValueAt(row, "whid")
+ "','" + dsOccu.getValueAt(row, "locatid") + "','" + dsOccu.getValueAt(row, "whorgid") + "' "
+ " )";
return sql;
}


//服务于客开时，全部拦截负库存时，可在分摊完以后调用该函数进行负库存拦截
function _chkBal() {
var fkclj = "";
if(dbtype.equals("Oracle")) {
fkclj += " select '商品：'||w.GoodsCode||':['||w.GoodsName||']库存不足，请核对！'";
fkclj += " ||'当前并发开票：['||cast(a.basenum as varchar(80))";
fkclj += " ||'] 结存：['||cast(nvl(y.placenum,0) as varchar(80))||']'";
fkclj += " from  (select entid,goodsid,ownerid,locatid,angleid,SUM(basenum) as basenum";
fkclj += " from ecNotesDt";
fkclj += " where billno = " + billno;
fkclj += " group by entid,goodsid,ownerid,locatid,angleid";
fkclj += " ) a";
fkclj += " left join (";
fkclj += " select entid,goodsid,ownerid,locatid,angleid,SUM(basenum) as basenum";
fkclj += " from goodsoccu";
fkclj += " group by entid,goodsid,angleid,ownerid,locatid";
fkclj += " ) x on a.entid = x.entid and a.goodsid = x.goodsid and a.angleid = x.angleid";
fkclj += " and a.locatid = x.locatid and a.ownerid = x.ownerid";
fkclj += " left join(";
fkclj += " select Entid,GoodsId,ownerid,locatid,AngleId";
fkclj += " ,OccupNum,(PlaceNum-OccupNum) as PlaceNum";
fkclj += " from ANGLEBALANCE";
fkclj += " )y on a.GoodsId = y.GoodsId and a.AngleId = y.AngleId";
fkclj += " and a.OwnerId = y.OwnerId and a.LocatId = y.LocatId and a.EntId = y.EntId";
fkclj += " join GOODSDOC w on a.GoodsId = w.GoodsId and a.EntId = w.EntId";
fkclj += " where nvl(x.basenum,0)>nvl(y.PlaceNum,0)";
}else {
fkclj += " select '商品：'+w.GoodsCode+':['+w.GoodsName+']库存不足，请核对！'";
fkclj += " +'当前并发开票：['+cast(a.basenum as varchar(80))";
fkclj += " +'] 结存：['+cast(isnull(y.placenum,0) as varchar(80))+']'";
fkclj += " from  (select entid,goodsid,ownerid,locatid,angleid,SUM(basenum) as basenum";
fkclj += " from ecNotesDt";
fkclj += " where billno = " + billno;
fkclj += " group by entid,goodsid,ownerid,locatid,angleid";
fkclj += " ) a";
fkclj += " left join (";
fkclj += " select entid,goodsid,ownerid,locatid,angleid,SUM(basenum) as basenum";
fkclj += " from goodsoccu";
fkclj += " group by entid,goodsid,angleid,ownerid,locatid";
fkclj += " ) x on a.entid = x.entid and a.goodsid = x.goodsid and a.angleid = x.angleid";
fkclj += " and a.locatid = x.locatid and a.ownerid = x.ownerid";
fkclj += " left join(";
fkclj += " select Entid,GoodsId,ownerid,locatid,AngleId";
fkclj += " ,OccupNum,(PlaceNum-OccupNum) as PlaceNum";
fkclj += " from ANGLEBALANCE";
fkclj += " )y on a.GoodsId = y.GoodsId and a.AngleId = y.AngleId";
fkclj += " and a.OwnerId = y.OwnerId and a.LocatId = y.LocatId and a.EntId = y.EntId";
fkclj += " join GOODSDOC w on a.GoodsId = w.GoodsId and a.EntId = w.EntId";
fkclj += " where isnull(x.basenum,0)>isnull(y.PlaceNum,0)";
}
var chkRet = DBUtil.uniqueValue(fkclj);
if(! Utility.isSpace(chkRet)) {
Utility.throwError("\n\n"+chkRet+"\n\n");
}
}


/*
//批号分摊规则,批号信息检索排序使用。
hz 货主，使用hz desc时,优先货主为本机构
placenum 可开数量
xq 批号有效期


*/
var ftgz = "order by hz desc,placenum desc";


var sqlNotes = "";
sqlNotes += " select 0 as ftnum,'N' as ftwc,"+_copyNotesDt("a.","","a",0,0)+""
sqlNotes += " ,d.whname,c.goodscode,c.goodsname,d.iscontrol"
sqlNotes += " ,b.ruleid,'' as batchcode,'' as producedate,'' as valdate,'' as sterilcode,'' as sysdates"
sqlNotes += " from ecnotesdt a"
sqlNotes += " join ecnotesmt b on a.billno = b.billno and a.entid = b.entid"
sqlNotes += " join goodsdoc c on a.goodsid = c.goodsid and a.entid = c.entid"
sqlNotes += " join storehouse d on a.whid = d.whid and a.entid = d.entid"
sqlNotes += " where a.billno = "+billno;
var dsNotes = DBUtil.openDataSet(sqlNotes, {});
if(dsNotes.recordCount == 0) {
Utility.throwError("\n\n" + "批号分摊，没有检索到单据信息，单据信息异常！" + "\n\n" + " ");
}else {
var regBillsn = 1;//临时占用表的序号
for(var i = 0;i < dsNotes.recordCount;i++) {
/*库房资料是否拦截负库存 不拦截时，
判断batchcode是否存在空批号，不存在则插入
*/
if(Utility.isSpace(dsNotes.getValueAt(i,"locatid"))) {
Utility.throwError("\n\n" + "默认货位为空，请为库房设置默认货位！" + "\n\n" + " ");
}
if(dsNotes.getValueAt(i, "iscontrol").equals("N")) {
continue;
}


var regSql = [];//临时占用记录
//当前行分摊完成，跳转下一行
if (parseFloat(dsNotes.getValueAt(i,"basenum")) - parseFloat(dsNotes.getValueAt(i,"ftnum")) <= 0
|| dsNotes.getValueAt(i, "ftwc").equals("Y")) {
continue;
}
var angleBal = "";
angleBal +=" select a.goodsid,a.angleid,a.locatid,b.whid,a.ownerid";
if(dbtype.equals("Oracle")) {
angleBal +=" ,a.entid,a.placenum - nvl(a.occupnum,0) - nvl(x.basenum,0) as placenum,0 as ftnum,'N' as ftwc";
}else {
angleBal +=" ,a.entid,a.placenum - isnull(a.occupnum,0) - isnull(x.basenum,0) as placenum,0 as ftnum,'N' as ftwc";
}
angleBal +=" ,case when a.ownerid = d.orgid then 2 else 1 end as hz,e.valdate as xq";
angleBal +=" from anglebalance a";
angleBal +=" join storeroom b on a.locatid = b.locatid and a.entid = b.entid";
angleBal +=" join storehouse d on b.whid = d.whid and b.entid = d.entid";
angleBal +=" join goodsdoc c on a.goodsid = c.goodsid and a.entid = c.entid";
angleBal +=" left join batchcode e on a.angleid = e.angleid and a.goodsid = e.goodsid and a.entid = e.entid"
angleBal +=" left join(";
angleBal +=" select entid,ownerid,goodsid,angleid,locatid,sum(basenum) as basenum ";
angleBal +=" from goodsoccu";
angleBal +=" group by entid,ownerid,goodsid,angleid,locatid";
angleBal +=" ) x on a.angleid = x.angleid and a.goodsid = x.goodsid and a.locatid = x.locatid";
angleBal +=" and a.ownerid = x.ownerid and a.entid = x.entid";
angleBal +=" where a.SalesState=0"
angleBal +=" and a.goodsid = '"+dsNotes.getValueAt(i,"goodsid")+"'";
angleBal +=" and b.whid = '"+dsNotes.getValueAt(i,"whid")+"'";
angleBal +=" and a.entid = '"+dsNotes.getValueAt(i,"entid")+"'";
if(dbtype.equals("Oracle")) {
angleBal +=" and (a.placenum - nvl(a.occupnum,0) - nvl(x.basenum,0)) > 0";
}else {
angleBal +=" and (a.placenum - isnull(a.occupnum,0) - isnull(x.basenum,0)) > 0";
}
angleBal +=" "+ftgz;
var dsAngleBal = DBUtil.openDataSet(angleBal, {});//检索该库房品种的可分摊批号结存
if(dsAngleBal.recordCount == 0) {
var goodsid = dsNotes.getValueAt(i, "goodsid");
var entid = dsNotes.getValueAt(i, "entid");
var whid = dsNotes.getValueAt(i, "whid");
var balnum = DBUtil.uniqueValue("select sum(a.placenum) - sum(a.occupnum) as placenum from anglebalance a "
+ " join storeroom b on a.locatid = b.locatid and a.entid = b.entid"
+ " where a.goodsid = '" + goodsid + "'"
+ " and b.whid = '" + whid + "' and a.entid = '" + entid + "' and a.SalesState = 0");
var kpNum = DBUtil.uniqueValue("select sum(basenum) as basenum from ecnotesdt where billno = " + billno
+ " and entid ='" + entid + "'" + " and goodsid = '" + goodsid + "' and whid = '" + whid + "'");
Utility.throwError("\n\n" + "商品名称：【" + dsNotes.getValueAt(i, "goodsname") + "】 库存不足！\n"
+ "并发开票数量:[" + kpNum + "]" + "       库存(含占用库存):[" + balnum + "]\n"
+ "" + "\n" + " ");
}else {
var ABaseNum = parseFloat(dsNotes.getValueAt(i,"basenum"));
var Aftnum = parseFloat(dsNotes.getValueAt(i,"ftnum"));
var ALastNum = ABaseNum - Aftnum;//需分摊数量
if(ALastNum <= 0 || dsNotes.getValueAt(i,"ftwc").equals("Y")) {
continue;
}
for(var j = 0;j < dsAngleBal.recordCount;j++) {
var Bplacenum = parseFloat(dsAngleBal.getValueAt(j,"placenum"));
var Bftnum = parseFloat(dsAngleBal.getValueAt(j,"ftnum"));
var BalLastNum = Bplacenum - Bftnum;//可分摊数量
//库存分摊
if(ALastNum != 0 && j != 0) {
dsNotes.append();
_copyNotesDt("",dsNotes,"",1,i);
dsNotes.field("ftwc").value = "Y";
dsNotes.field("ftnum").value = 0;
dsNotes.field("ruleid").value = ruleid;
if (dsAngleBal.getValueAt(j, "ftwc").equals("N")) {
if (BalLastNum >= ALastNum) {
dsNotes.field("ftnum").value = ALastNum;
dsNotes.field("angleid").value = dsAngleBal.getValueAt(j, "angleid");
dsNotes.field("locatid").value = dsAngleBal.getValueAt(j, "locatid");
dsNotes.field("ownerid").value = dsAngleBal.getValueAt(j, "ownerid");
dsNotes.field("goodsid").value = dsAngleBal.getValueAt(j, "goodsid");
dsAngleBal.setValueAt(j, "ftnum", ALastNum);
BalLastNum = BalLastNum - ALastNum;
ALastNum = 0;
regSql[regSql.length] = _regGoodsOccu(billno,regBillsn,"批号分摊占用",ruleid,dsNotes,dsNotes.recordCount - 1);
regBillsn++;
break;
} else if (BalLastNum < ALastNum) {
dsNotes.field("ftnum").value = BalLastNum;
dsNotes.field("angleid").value = dsAngleBal.getValueAt(j, "angleid");
dsNotes.field("locatid").value = dsAngleBal.getValueAt(j, "locatid");
dsNotes.field("ownerid").value = dsAngleBal.getValueAt(j, "ownerid");
dsNotes.field("goodsid").value = dsAngleBal.getValueAt(j, "goodsid");
dsAngleBal.setValueAt(j, "ftnum", BalLastNum);
ALastNum = ALastNum - BalLastNum;
BalLastNum = 0;
regSql[regSql.length] = _regGoodsOccu(billno,regBillsn,"批号分摊占用",ruleid,dsNotes,dsNotes.recordCount - 1);
regBillsn++;
}
}
}else {
if (dsAngleBal.getValueAt(j, "ftwc").equals("N")) {
if (BalLastNum >= ALastNum) {
dsNotes.setValueAt(i, "ftnum", ALastNum);
dsNotes.setValueAt(i, "angleid", dsAngleBal.getValueAt(j, "angleid"));
dsNotes.setValueAt(i, "locatid", dsAngleBal.getValueAt(j, "locatid"));
dsNotes.setValueAt(i, "ownerid", dsAngleBal.getValueAt(j, "ownerid"));
dsAngleBal.setValueAt(j, "ftnum", ALastNum);
BalLastNum = BalLastNum - ALastNum;
ALastNum = 0;
regSql[regSql.length] = _regGoodsOccu(billno,regBillsn,"批号分摊占用",ruleid,dsNotes,i);
regBillsn++;
break;
} else if (BalLastNum < ALastNum) {
dsNotes.setValueAt(i, "ftnum", BalLastNum);
dsNotes.setValueAt(i, "angleid", dsAngleBal.getValueAt(j, "angleid"));
dsNotes.setValueAt(i, "locatid", dsAngleBal.getValueAt(j, "locatid"));
dsNotes.setValueAt(i, "ownerid", dsAngleBal.getValueAt(j, "ownerid"));
dsAngleBal.setValueAt(j, "ftnum", BalLastNum);
ALastNum = ALastNum - BalLastNum;
BalLastNum = 0;
regSql[regSql.length] = _regGoodsOccu(billno,regBillsn,"批号分摊占用",ruleid,dsNotes,i);
regBillsn++;
}
}
}
//anglebalance中的数据已分摊完成。
if(BalLastNum <= 0) {
dsAngleBal.setValueAt(j,"ftwc","Y");
}
}
//库存不足校验
if(ALastNum > 0) {
var goodsid = dsNotes.getValueAt(i, "goodsid");
var entid = dsNotes.getValueAt(i, "entid");
var whid = dsNotes.getValueAt(i, "whid");
var balnum = DBUtil.uniqueValue("select sum(a.placenum) - sum(a.occupnum) as placenum from anglebalance a "
+ " join storeroom b on a.locatid = b.locatid and a.entid = b.entid"
+ " where a.goodsid = '" + goodsid + "'"
+ " and b.whid = '" + whid + "' and a.entid = '" + entid + "' and a.SalesState = 0");
var kpNum = DBUtil.uniqueValue("select sum(basenum) as basenum from ecnotesdt where billno = " + billno
+ " and entid ='" + entid + "'" + " and goodsid = '" + goodsid + "' and whid = '" + whid + "'");
Utility.throwError("\n\n" + "商品名称：【" + dsNotes.getValueAt(i, "goodsname") + "】 库存不足！\n"
+ "并发开票数量:[" + kpNum + "]" + "       库存(含占用库存):[" + balnum + "]\n"
+ "" + "\n" + " ");
}
//插入临时占用记录，防止一个品种、库房的多条记录分摊多次
DBUtil.batchUpdate(regSql);
}
}
}
var sqlList = [];
sqlList[sqlList.length] = "delete from ecnotesdt where billno = "
+dsNotes.field("billno").value+" and entid = '"
+dsNotes.field("entid").value+"'";
//处理数据
var isBatch = false;
for(var i = 0; i < dsNotes.recordCount;i++) {
dsNotes.setValueAt(i, "billsn",(i+1));
dsNotes.setValueAt(i, "billsort",(i+1));
//虚拟库不拦截负库存 根据库存属性 是否拦截负库存判断
if(dsNotes.getValueAt(i, "iscontrol").equals("Y")) {
dsNotes.setValueAt(i, "baseNum",dsNotes.getValueAt(i,"ftnum"));
dsNotes.setValueAt(i, "Num",dsNotes.getValueAt(i,"baseNum")/dsNotes.getValueAt(i,"meas"));
dsNotes.setValueAt(i, "taxAmount",dsNotes.getValueAt(i,"num")*dsNotes.getValueAt(i,"taxprice"));
dsNotes.setValueAt(i, "amount",dsNotes.getValueAt(i,"num")*dsNotes.getValueAt(i,"price"));
dsNotes.setValueAt(i, "tax",dsNotes.getValueAt(i,"taxAmount") - dsNotes.getValueAt(i,"amount"));
} else {
isBatch = true;
dsNotes.setValueAt(i, "ownerid",dsNotes.getValueAt(i,"whorgid"));//虚拟库默认货主为库存组织
}
}
//删除本单临时占用记录regSql
DBUtil.batchUpdate("delete from goodsOccu where billcode = '批号分摊占用'");
//重新插入数据


//批号创建   明细中存在不同库房时可考虑该创建方法放在明细循环中
if(isBatch) {
pf_createLotInfo(dsNotes,dsNotes.field("entid").value,dsNotes,1);
}
for(var i = 0; i < dsNotes.recordCount;i++) {
if(dsNotes.getValueAt(i,"baseNum") > 0) {
//插入分摊后数据
sqlList[sqlList.length] = "insert into ecNotesDt("+_copyNotesDt("","","a",0,0)+")"
+" values("+_copyNotesDt("",dsNotes,"a",2,i)+")";
//库存占用
sqlList[sqlList.length] = _regGoodsOccu(billno,dsNotes.getValueAt(i,"billsn"),billcode,ruleid,dsNotes,i);
}
}


DBUtil.batchUpdate(sqlList);
//负库存拦截 客开可考虑使用
// _chkBal();
}