mysql -小计

//表单某字段值为当前打开文档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;

})()