package restcontroller.webmanage.sale.dispatch; import beans.data.BatchDeleteErr; import beans.dispatch.Dispatch; import beans.parameter.Parameter; import beans.salearea.SaleArea; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import common.Controller; import common.YosException; import common.annotation.API; import common.annotation.CACHEING; import common.annotation.CACHEING_CLEAN; import common.data.*; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import restcontroller.R; import restcontroller.sale.promotion.promotion; import restcontroller.webmanage.sale.order.Order; import restcontroller.webmanage.sale.order.OrderItems; import utility.ERPDocking; import utility.tools.WebRequest; import java.io.IOException; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; public class dispatchItems extends Controller { public dispatchItems(JSONObject arg0) throws YosException { super(arg0); // TODO Auto-generated constructor stub } @API(title = "审核订单列表查询", apiversion = R.ID20221114165903.v1.class) @CACHEING public String queryCheckOrderList() throws YosException { /* * 过滤条件设置 */ StringBuffer where = new StringBuffer(" 1=1 "); if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where.append(" and("); where.append("t1.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } if (whereObject.containsKey("sonum") && !"".equals(whereObject.getString("sonum"))) { where.append(" and t1.sonum ='").append(whereObject.getString("sonum")).append("' "); } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' "); } } // String hrid = content.getString("hrid"); SQLFactory sqlFactory = new SQLFactory(this, "审核订单列表查询", pageSize, pageNumber, pageSorting); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("sys_enterpriseid", content.getLong("sys_enterpriseid")); sqlFactory.addParameter("siteid", siteid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false)); return getSucReturnObject().setData(rows).toString(); } @API(title = "发货商品列表查询", apiversion = R.ID20221114170003.v1.class) @CACHEING public String queryOrderItemgList() throws YosException { /* * 过滤条件设置 */ StringBuffer where = new StringBuffer(" 1=1 "); if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where.append(" and("); where.append("t1.itemname like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.itemno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.unit like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t3.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t3.typemx like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } if (whereObject.containsKey("sa_orderid") && !"".equals(whereObject.getString("sa_orderid"))) { Long sa_orderid = whereObject.getLongValue("sa_orderid"); if (sa_orderid > 0) { where.append(" and t1.sa_orderid ='").append(sa_orderid).append("' "); } } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and DATE_FORMAT(t3.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and DATE_FORMAT(t3.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' "); } } SQLFactory sqlFactory = new SQLFactory(this, "发货商品列表查询", pageSize, pageNumber, pageSorting); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("sys_enterpriseid", content.getLongValue("sys_enterpriseid")); sqlFactory.addParameter("sa_dispatchid", content.getLongValue("sa_dispatchid")); sqlFactory.addParameter("siteid", siteid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false)); return getSucReturnObject().setData(rows).toString(); } @API(title = "erp批次库存查询", apiversion = R.ID20230427101303.v1.class) @CACHEING public String queryErpIcinvbalBatchList() throws YosException { BigDecimal qty = content.getBigDecimalValue("qty"); JSONArray jsonArray = content.getJSONArray("itemnos"); JSONArray jsonArrayfinal = new JSONArray(); ERPDocking erpDocking = new ERPDocking(siteid); JSONArray rows = erpDocking.getErpIcinvbalBatchRows(1000, 1, jsonArray); for (Object object : rows) { JSONObject jsonObject = (JSONObject) object; if (jsonObject.getBigDecimalValue("fqty").compareTo(qty) >= 0) { jsonArrayfinal.add(jsonObject); } } return getSucReturnObject().setData(jsonArrayfinal).toString(); } @API(title = "新建或修改发货单商品明细", apiversion = R.ID20221115104603.v1.class, intervaltime = 200) @CACHEING_CLEAN(apiClass = {dispatchItems.class, dispatch.class}) public String insertormodify_dispatchItems() throws YosException { Long sa_dispatchid = content.getLong("sa_dispatchid"); JSONArray iteminfos = content.getJSONArray("iteminfos"); ArrayList sqlList = new ArrayList<>(); Rows rowscount = dbConnect.runSqlQuery("select billno,status from sa_dispatch where sa_dispatchid=" + sa_dispatchid); if (!rowscount.isEmpty()) { if (!rowscount.get(0).getString("status").equals("新建")) { return getErrReturnObject().setErrMsg("非新建状态的发货单无法新增修改").toString(); } } int i = 0; long maxid = 0; long[] sa_dispatch_itemsid = createTableID("sa_dispatch_items", iteminfos.size()); Rows maxidRows = dbConnect .runSqlQuery("select MAX(rowno) rowno from sa_dispatch_items where sa_dispatchid=" + sa_dispatchid); if (!maxidRows.isEmpty()) { maxid = maxidRows.get(0).getLong("rowno"); } for (Object obj : iteminfos) { JSONObject iteminfo = (JSONObject) obj; if (iteminfo.getLong("sa_dispatch_itemsid") <= 0 || dbConnect .runSqlQuery("select sa_dispatch_itemsid from sa_dispatch_items where sa_dispatch_itemsid=" + iteminfo.getLong("sa_dispatch_itemsid")) .isEmpty()) { // Rows sa_dispatch_itemsrows = dbConnect.runSqlQuery("select sa_dispatch_itemsid,qty,remarks from sa_dispatch_items where sa_dispatchid=" // + sa_dispatchid + " and sa_orderitemsid=" + iteminfo.getLong("sa_orderitemsid")); // if (!sa_dispatch_itemsrows.isEmpty()) { // SQLFactory saleFactory = new SQLFactory(this, "发货单明细更新"); // saleFactory.addParameter("sa_dispatch_itemsid", sa_dispatch_itemsrows.get(0).getLong("sa_dispatch_itemsid")); // // saleFactory.addParameter("itemno", iteminfo.getString("itemno")); // saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty").add(sa_dispatch_itemsrows.get(0).getBigDecimal("qty"))); // if (!StringUtils.isBlank(iteminfo.getString("remarks"))) { // saleFactory.addParameter("remarks", iteminfo.getString("remarks")); // } else { // saleFactory.addParameter("remarks", sa_dispatch_itemsrows.get(0).getString("remarks")); // } // // saleFactory.addParameter("userid", userid); // saleFactory.addParameter("username", username); // saleFactory.addParameter("billno", rowscount.get(0).getString("billno")); // saleFactory.addParameter("batchno", iteminfo.getStringValue("batchno")); // sqlList.add(saleFactory.getSQL()); // } else { // SQLFactory saleFactory = new SQLFactory(this, "发货单明细新增"); // saleFactory.addParameter("siteid", siteid); // saleFactory.addParameter("rowno", maxid + i + 1); // saleFactory.addParameter("sa_dispatch_itemsid", sa_dispatch_itemsid[i]); // saleFactory.addParameter("sa_dispatchid", sa_dispatchid); // saleFactory.addParameter("sa_orderitemsid", iteminfo.getLong("sa_orderitemsid")); // saleFactory.addParameter("itemid", iteminfo.getString("itemid")); // saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty")); // saleFactory.addParameter("batchcontrol", iteminfo.getBooleanValue("batchcontrol")); // saleFactory.addParameter("batchno", ""); // saleFactory.addParameter("remarks", iteminfo.getString("remarks")); // saleFactory.addParameter("userid", userid); // saleFactory.addParameter("username", username); // saleFactory.addParameter("billno", rowscount.get(0).getString("billno")); // sqlList.add(saleFactory.getSQL()); // } SQLFactory saleFactory = new SQLFactory(this, "发货单明细新增"); saleFactory.addParameter("siteid", siteid); saleFactory.addParameter("rowno", maxid + i + 1); saleFactory.addParameter("sa_dispatch_itemsid", sa_dispatch_itemsid[i]); saleFactory.addParameter("sa_dispatchid", sa_dispatchid); saleFactory.addParameter("sa_orderitemsid", iteminfo.getLong("sa_orderitemsid")); saleFactory.addParameter("itemid", iteminfo.getString("itemid")); saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty")); saleFactory.addParameter("batchcontrol", iteminfo.getBooleanValue("batchcontrol")); saleFactory.addParameter("batchno", ""); saleFactory.addParameter("remarks", iteminfo.getString("remarks")); saleFactory.addParameter("userid", userid); saleFactory.addParameter("username", username); saleFactory.addParameter("billno", rowscount.get(0).getString("billno")); sqlList.add(saleFactory.getSQL()); i++; } else { SQLFactory saleFactory = new SQLFactory(this, "发货单明细更新"); saleFactory.addParameter("sa_dispatch_itemsid", iteminfo.getLong("sa_dispatch_itemsid")); // saleFactory.addParameter("itemno", iteminfo.getString("itemno")); saleFactory.addParameter("qty", iteminfo.getBigDecimal("qty")); saleFactory.addParameter("remarks", iteminfo.getString("remarks")); saleFactory.addParameter("batchno", iteminfo.getStringValue("batchno")); saleFactory.addParameter("userid", userid); saleFactory.addParameter("username", username); saleFactory.addParameter("billno", rowscount.get(0).getString("billno")); sqlList.add(saleFactory.getSQL()); } } dbConnect.runSqlUpdate(sqlList); return queryDispatchItemsList(); } @API(title = "发货单商品明细列表", apiversion = R.ID20221115104703.v1.class) @CACHEING public String queryDispatchItemsList() throws YosException { /* * 过滤条件设置 */ StringBuffer where = new StringBuffer(" 1=1 "); if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where.append(" and("); where.append("t2.itemname like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.model like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.spec like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } } Long sa_dispatchid = content.getLong("sa_dispatchid"); // String hrid = content.getString("hrid"); SQLFactory sqlFactory = new SQLFactory(this, "发货单明细列表查询", pageSize, pageNumber, pageSorting); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("sa_dispatchid", sa_dispatchid); sqlFactory.addParameter("siteid", siteid); Rows rows = dbConnect.runSqlQuery(sqlFactory); RowsMap rowsMap = rows.toRowsMap("itemno"); ERPDocking erpDocking = new ERPDocking(siteid); JSONArray jsonArray = new JSONArray(); JSONObject object = new JSONObject(); object.put("classname", "getIcinvbal"); object.put("method", "getMsg"); JSONObject content = new JSONObject(); content.put("fitemnos", rows.toJsonArray("itemno")); object.put("content", content); WebRequest request = new WebRequest(); //System.out.println(object.toString()); if(siteid.equalsIgnoreCase("lsa")){ String result = request.doPost(object.toString(), "http://60.190.151.198:8089/BYESB/jaxrs/webclientrest"); if(isJSONArray(result)){ jsonArray = JSONArray.parseArray(result); } }else{ if (rows.toJsonArray("itemno").size() != 0) { if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") ) { jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno")); } } } SQLFactory sqlFactory1 = new SQLFactory(this, "商品发货数量汇总"); sqlFactory1.addParameter("siteid", siteid); sqlFactory1.addParameter_in("itemno", rows.toArrayList("itemno", new ArrayList<>())); Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1); RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("itemno"); if (!jsonArray.isEmpty()) { for (Object obj : jsonArray) { JSONObject jsonObject = (JSONObject) obj; if (rowsMap.containsKey(jsonObject.getString("fitemno"))) { if (rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()) { if (sumQtyRowsMap.containsKey(jsonObject.getString("fitemno"))) { if(siteid.equalsIgnoreCase("lsa")){ rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("FQty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty"))); }else{ rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty"))); } } else { if(siteid.equalsIgnoreCase("lsa")){ rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("FQty")); }else{ rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty")); } } if(siteid.equalsIgnoreCase("lsa")){ rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("FQty")); }else{ rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("fqty")); } } } } } for (Row row : rows) { if (!row.containsKey("delinvbalqty")) { row.put("delinvbalqty", row.getBigDecimal("undeliqty").negate()); row.put("invbalqty", 0); } } return getSucReturnObject().setData(rows).toString(); } @API(title = "发货单商品所有明细列表", apiversion = R.ID20230508113003.v1.class) @CACHEING public String queryDispatchAllItemsList() throws YosException, IOException { /* * 过滤条件设置 */ StringBuffer where = new StringBuffer(" 1=1 "); if (sys_enterpriseid > 0) { where.append(" and t4.sys_enterpriseid ='").append(sys_enterpriseid).append("' "); where.append(" and ifnull(t1.outwarehouseqty, 0)>0 "); } if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where.append(" and("); where.append(" t4.billno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t8.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t9.agentnum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t10.enterprisename like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.itemname like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) { where.append(" and("); where.append("t9.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' "); where.append("or t10.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' "); where.append(")"); } if (whereObject.containsKey("iteminfo") && !"".equals(whereObject.getString("iteminfo"))) { where.append(" and("); where.append("t2.itemno like'%").append(whereObject.getString("iteminfo")).append("%' "); where.append("or t2.itemname like'%").append(whereObject.getString("iteminfo")).append("%' "); where.append(")"); } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and DATE_FORMAT(t4.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and DATE_FORMAT(t4.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' "); } if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and t4.status ='").append(whereObject.getString("status")).append("' "); } if (whereObject.containsKey("isnotsent") && !"".equals(whereObject.getString("isnotsent"))) { if (whereObject.getBooleanValue("isnotsent")) { //System.out.println(" and t4.status ='复核' and ifnull(t1.outwarehouseqty, 0)< ifnull(t1.qty, 0)"); where.append(" and t1.sa_dispatchid in(SELECT sa_dispatchid FROM (SELECT sum(outwarehouseqty) outwarehouseqty,sa_dispatchid FROM sa_dispatch_items WHERE siteid='"+siteid+"' GROUP BY sa_dispatchid) a WHERE a.outwarehouseqty=0)"); } } } boolean isExport = content.getBooleanValue("isExport"); // SQLFactory sqlFactory = new SQLFactory(this, "发货单明细所有列表查询", pageSize, pageNumber, pageSorting); // if (isExport) { // sqlFactory = new SQLFactory(this, "发货单明细所有列表查询"); // } // sqlFactory.addParameter_SQL("where", where); // sqlFactory.addParameter("siteid", siteid); // Rows rows = dbConnect.runSqlQuery(sqlFactory); QuerySQL querySQL = queryListManage(where.toString()); querySQL.setOrderBy(pageSorting); Rows rows; querySQL.setPage(pageSize, pageNumber); rows = querySQL.query(); SQLFactory areasqlFactory = new SQLFactory(this, "发货单明细关联区域列表查询"); areasqlFactory.addParameter("siteid", siteid); areasqlFactory.addParameter_in("sys_enterpriseids", rows.toArrayList("sys_enterpriseid", new ArrayList<>())); Rows areaRows = dbConnect.runSqlQuery(areasqlFactory); RowsMap areaRowsMap = areaRows.toRowsMap("sys_enterpriseid"); SQLFactory itemsqlFactory = new SQLFactory(this, "发货单明细关联商品列表查询"); itemsqlFactory.addParameter("siteid", siteid); itemsqlFactory.addParameter_in("itemids", rows.toArrayList("itemid", new ArrayList<>())); Rows itemRows = dbConnect.runSqlQuery(itemsqlFactory); RowsMap itemRowsMap = itemRows.toRowsMap("itemid"); SQLFactory ordersqlFactory = new SQLFactory(this, "发货单明细关联订单列表查询"); ordersqlFactory.addParameter("siteid", siteid); ordersqlFactory.addParameter_in("sa_orderitemsids", rows.toArrayList("sa_orderitemsid", new ArrayList<>())); Rows orderRows = dbConnect.runSqlQuery(ordersqlFactory); RowsMap orderRowsMap = orderRows.toRowsMap("sa_orderitemsid"); for (Row row : rows) { if (orderRowsMap.containsKey(row.getString("sa_orderitemsid"))) { row.put("sa_orderid", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getString("sa_orderid")); row.put("sonum", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getString("sonum")); row.put("type", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getString("type")); row.put("orderRowno", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getString("orderRowno")); row.put("price", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getDouble("price")); row.put("undeliqty", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getDouble("undeliqty")); row.put("deliedqty", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getDouble("deliedqty")); row.put("amount", orderRowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("price").multiply(row.getBigDecimal("qty")).doubleValue()); } if (itemRowsMap.containsKey(row.getString("itemid"))) { row.put("itemno", itemRowsMap.get(row.getString("itemid")).get(0).getString("itemno")); row.put("itemname", itemRowsMap.get(row.getString("itemid")).get(0).getString("itemname")); row.put("unitname", itemRowsMap.get(row.getString("itemid")).get(0).getString("unitname")); } if (areaRowsMap.containsKey(row.getString("sys_enterpriseid"))) { row.put("areaname", areaRowsMap.get(row.getString("sys_enterpriseid")).get(0).getString("areaname")); } else { row.put("areaname", ""); } } RowsMap rowsMap = rows.toRowsMap("itemno"); ERPDocking erpDocking = new ERPDocking(siteid); JSONArray jsonArray = new JSONArray(); if (rows.toJsonArray("itemno").size() != 0) { if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") ) { jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno")); } } SQLFactory sqlFactory1 = new SQLFactory(this, "商品发货数量汇总"); sqlFactory1.addParameter("siteid", siteid); sqlFactory1.addParameter_in("itemno", rows.toArrayList("itemno", new ArrayList<>())); Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1); RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("itemno"); if (!jsonArray.isEmpty()) { for (Object object : jsonArray) { JSONObject jsonObject = (JSONObject) object; if (rowsMap.containsKey(jsonObject.getString("fitemno"))) { if (rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()) { if (sumQtyRowsMap.containsKey(jsonObject.getString("fitemno"))) { rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty").subtract(sumQtyRowsMap.get(jsonObject.getString("fitemno")).get(0).getBigDecimal("qty"))); } else { rowsMap.get(jsonObject.getString("fitemno")).get(0).put("delinvbalqty", jsonObject.getBigDecimalValue("fqty")); } rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("fqty")); } } } } for (Row row : rows) { if (!row.containsKey("delinvbalqty")) { row.put("delinvbalqty", row.getBigDecimal("undeliqty").negate().doubleValue()); row.put("invbalqty", 0); } } // if (isExport) { // //去除不需要导出项 // rows.getFieldList().remove("sa_dispatch_itemsid"); // rows.getFieldList().remove("sa_dispatchid"); // rows.getFieldList().remove("sa_orderitemsid"); // rows.getFieldList().remove("sa_orderid"); // rows.getFieldList().remove("sys_enterpriseid"); // rows.getFieldList().remove("isclose"); // rows.getFieldList().remove("rowno"); // rows.getFieldList().remove("itemid"); // rows.getFieldList().remove("batchno"); // Rows rowsrolename= dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid="+userid); // if(!rowsrolename.isEmpty()){ // rows.getFieldList().remove("price"); // rows.getFieldList().remove("amount"); // } // rows.getFieldList().remove("undeliqty"); // rows.getFieldList().remove("deliedqty"); // rows.getFieldList().remove("outwarehousedate"); // Rows uploadRows = uploadExcelToObs("dispatch", "发货单明细列表", rows, getTitleMap()); // return getSucReturnObject().setData(uploadRows).toString(); // } return getSucReturnObject().setData(rows).toString(); } //查询订单列表(管理端) public QuerySQL queryListManage(String where) throws YosException { QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_dispatch_items", "sa_dispatch_itemsid", "sa_dispatchid", "sa_orderitemsid", "isclose", "rowno", "itemid", "outwarehousedate", "batchno", "remarks"); querySQL.setTableAlias("t1"); querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t2.itemid = t1.itemid AND t2.siteid = t1.siteid", "itemno"); querySQL.addJoinTable(JOINTYPE.left, "sa_dispatch", "t4", "t4.sa_dispatchid = t1.sa_dispatchid AND t4.siteid = t1.siteid", "billno", "status", "billdate", "closedate"); querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t5", "t5.sa_orderitemsid=t1.sa_orderitemsid and t5.siteid=t1.siteid", "spec"); querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t8", "t8.sa_orderid=t4.sa_orderid and t8.siteid=t4.siteid", "sonum"); querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t9", "t9.sys_enterpriseid = t4.sys_enterpriseid and t9.siteid = t4.siteid", "agentnum"); querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t10", "t10.sys_enterpriseid = t4.sys_enterpriseid and t10.siteid = t4.siteid", "enterprisename", "sys_enterpriseid"); querySQL.addQueryFields("qty", "ifnull(t1.qty, 0)"); querySQL.addQueryFields("outwarehouseqty", "ifnull(t1.outwarehouseqty, 0)"); querySQL.setWhere("t1.siteid", siteid); querySQL.setWhere(where); return querySQL; } @API(title = "工厂发货/退货明细", apiversion = R.ID20230626104003.v1.class) @CACHEING public String queryDispatchAndAftersalesmagItemsList() throws YosException, IOException { /* * 过滤条件设置 */ StringBuffer where = new StringBuffer(" 1=1 "); if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where.append(" and("); where.append("t.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.itemno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.itemname like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.remarks like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.billno like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) { where.append(" and("); where.append("t.type ='").append(whereObject.getString("type")).append("' "); where.append(")"); } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' "); } if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and t.status ='").append(whereObject.getString("status")).append("' "); } } SQLFactory sqlFactory = new SQLFactory(this, "工厂发货及退货明细(经销商)", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("siteid", siteid); Rows rows = dbConnect.runSqlQuery(sqlFactory); return getSucReturnObject().setData(rows).toString(); } @API(title = "工厂发货/退货明细(区域经理)", apiversion = R.ID20231030091403.v1.class) @CACHEING public String queryDispatchAndAftersalesmagItemsManagerList() throws YosException, IOException { /* * 过滤条件设置 */ StringBuffer where = new StringBuffer(" 1=1 "); if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where.append(" and("); where.append("t.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.itemno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.itemname like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t.remarks like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) { where.append(" and("); where.append("t.type ='").append(whereObject.getString("type")).append("' "); where.append(")"); } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and DATE_FORMAT(t.date, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' "); } if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and t.status ='").append(whereObject.getString("status")).append("' "); } } boolean isExport = content.getBooleanValue("isExport"); SQLFactory areasqlFactory = new SQLFactory(new promotion(content), "区域经理查询下属营销区域"); areasqlFactory.addParameter("siteid", siteid); areasqlFactory.addParameter("hrid", hrid); Rows arearows = dbConnect.runSqlQuery(areasqlFactory); ArrayList arrayList = arearows.toArrayList("sa_saleareaid", new ArrayList()); arrayList.addAll(SaleArea.getSubSaleAreaIds(this, arrayList)); SQLFactory sqlFactory = new SQLFactory(this, "工厂发货及退货明细(区域经理)", pageSize, pageNumber, pageSorting); sqlFactory.addParameter_in("sa_saleareaids", arrayList); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("siteid", siteid); Rows rows = dbConnect.runSqlQuery(sqlFactory); // if (isExport) { // //去除不需要导出项 // rows.getFieldList().remove("sa_dispatch_itemsid"); // rows.getFieldList().remove("sa_dispatchid"); // rows.getFieldList().remove("sa_orderitemsid"); // rows.getFieldList().remove("sa_orderid"); // rows.getFieldList().remove("sys_enterpriseid"); // rows.getFieldList().remove("isclose"); // rows.getFieldList().remove("rowno"); // rows.getFieldList().remove("itemid"); // rows.getFieldList().remove("batchno"); // Rows rowsrolename= dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid="+userid); // if(!rowsrolename.isEmpty()){ // rows.getFieldList().remove("price"); // rows.getFieldList().remove("amount"); // } // rows.getFieldList().remove("undeliqty"); // rows.getFieldList().remove("deliedqty"); // rows.getFieldList().remove("outwarehousedate"); // Rows uploadRows = uploadExcelToObs("dispatch", "发货单明细列表", rows, getTitleMap()); // return getSucReturnObject().setData(uploadRows).toString(); // } return getSucReturnObject().setData(rows).toString(); } //返回导出的标题 public HashMap getTitleMap() throws YosException { HashMap titleMap = new HashMap<>(); titleMap.put("billno", "发货单号"); titleMap.put("status", "状态"); titleMap.put("sonum", "订单号"); titleMap.put("enterprisename", "企业名称"); titleMap.put("agentnum", "经销商编号"); titleMap.put("billdate", "发货日期"); titleMap.put("orderRowno", "订单行号"); titleMap.put("itemno", "产品编码"); titleMap.put("itemname", "品名"); titleMap.put("unitname", "单位"); titleMap.put("qty", "发货数量"); titleMap.put("remarks", "发货行备注"); titleMap.put("outwarehouseqty", "已出库数量"); Rows rowsrolename = dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid=" + userid); if (rowsrolename.isEmpty()) { titleMap.put("price", "单价"); titleMap.put("amount", "金额"); } return titleMap; } @API(title = "删除明细", apiversion = R.ID20221115104803.v1.class) @CACHEING_CLEAN(apiClass = {dispatchItems.class, dispatch.class}) public String deletemx() throws YosException { JSONArray sa_dispatch_itemsids = content.getJSONArray("sa_dispatch_itemsids"); BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_dispatch_itemsids.size()); long sa_dispatchid = 0; for (Object o : sa_dispatch_itemsids) { long sa_dispatch_itemsid = Long.parseLong(o.toString()); Rows RowsStatus = dbConnect.runSqlQuery("select t1.sa_dispatch_itemsid,t2.status,t1.sa_dispatchid from sa_dispatch_items t1 left join sa_dispatch t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.sa_dispatch_itemsid='" + sa_dispatch_itemsid + "'"); if (RowsStatus.isNotEmpty()) { sa_dispatchid = RowsStatus.get(0).getLong("sa_dispatchid"); if (!RowsStatus.get(0).getString("status").equals("新建")) { batchDeleteErr.addErr(sa_dispatch_itemsid, "非新建状态的发货单明细无法删除"); continue; } } ArrayList list = new ArrayList<>(); SQLFactory deletesql = new SQLFactory("sql:delete from sa_dispatch_items where siteid='" + siteid + "' and sa_dispatch_itemsid=" + sa_dispatch_itemsid); list.add(deletesql.getSQL()); dbConnect.runSqlUpdate(list); } //重新排序 updateRowNo(sa_dispatchid); return batchDeleteErr.getReturnObject().toString(); } /** * 重新对发货单行进行排序 * * @param * @throws YosException */ public void updateRowNo(Long sa_dispatchid) throws YosException { String sql = "SELECT sa_dispatch_itemsid from sa_dispatch_items WHERE sa_dispatchid= " + sa_dispatchid + " and siteid = '" + siteid + "' ORDER BY sa_dispatch_itemsid asc "; ArrayList sa_dispatch_itemsids = dbConnect.runSqlQuery(sql).toArrayList("sa_dispatch_itemsid", new ArrayList<>()); ArrayList sqlList = new ArrayList<>(); int rowno = 1; for (Long id : sa_dispatch_itemsids) { sqlList.add("UPDATE sa_dispatch_items SET rowno=" + rowno + " WHERE sa_dispatch_itemsid = " + id); rowno++; } dbConnect.runSqlUpdate(sqlList); } }