//表单某字段值为当前打开文档Id
(function () {
var rdoc = getRelateDocument();
var warehouseName = rdoc.getItemValueAsString(“warehouseName”);
var name = rdoc.getItemValueAsString(“name”);
var color = rdoc.getItemValueAsString(“color”);
var batchNumber = rdoc.getItemValueAsString(“batchNumber”);
var domainid = getDomainid();//获取当前登录用户所属的企业域ID
var sql = “”;
var sql1 = “”;
var sql2 = “”;
var where = “”;
if ((warehouseName != null && !"".equals(warehouseName)) || (name != null && !"".equals(name)) || (color != null && !"".equals(color)) || (batchNumber != null && !"".equals(batchNumber))) {if (isNotNull(warehouseName)) {var strw = warehouseName.replace(';', ',');var str = /,$/.test(strw);if (str == true) {strw = strw.substring(0, strw.lastIndexOf(','));}println('strs=='+strw.substring(0, 1).equals(","));if(strw.substring(0, 1).equals(",")){strw=strw.slice(1)}where += " and ITEM_WAREHOUSE in (" + strw + ")"}if (isNotNull(name)) {where += " and ITEM_COMMODITYNAME='" + name + "'"}if (isNotNull(color)) {where += " and item_颜色='" + color + "'"}if (isNotNull(batchNumber)) {where += " and item_batch like '%" + batchNumber + "%'"}
} else {where += " and 1>1 ";
}
//库存查询
sql1 += " select CREATED,id,DOMAINID,ITEM_COMMODITYNAME,'0' as ITEM_SCOUNT,ITEM_WAREHOUSE,(select ITEM_WAREHOUSENAME from tlk_warehouseinformation where DOMAINID='" + domainid + "' and ITEM_WAREHOUSENO=c.ITEM_WAREHOUSE limit 1) as item_warehouseName, item_颜色,item_batch,SUM(ITEM_INVENTORYQUANTITY) as item_sumCount,SUM(ITEM_入库条数) as item_items from tlk_Commodity_Inventory c where DOMAINID='" + domainid + "' "+where+" GROUP BY item_颜色,item_batch,ITEM_COMMODITYNAME,ITEM_WAREHOUSE ";
//库存查询(汇总)
sql2 += " select CREATED,id,DOMAINID,ITEM_COMMODITYNAME,'1' as ITEM_SCOUNT,'' as ITEM_WAREHOUSE,'小计：' as item_warehouseName, '' as item_颜色,'' as item_batch,SUM(ITEM_INVENTORYQUANTITY) as item_sumCount,SUM(ITEM_入库条数) as item_items from tlk_Commodity_Inventory c where DOMAINID='" + domainid + "' "+where+" GROUP BY ITEM_COMMODITYNAME ";sql = "select * from (select * from (" + sql1 + ") t1 UNION ALL select * from (" + sql2 + ") t2 ) as g ORDER BY g.ITEM_COMMODITYNAME DESC,ITEM_SCOUNT ";return sql;
})()
