package restcontroller.webmanage.sale.order; import beans.accountbalance.Accountbalance; import beans.accountbalance.CashbillEntity; import beans.clear.ClearData; import beans.datacontrllog.DataContrlLog; import beans.dataextend.DataExtend; import beans.datateam.DataTeam; import beans.enterprise.Enterprise; import beans.hr.Hr; import beans.parameter.Parameter; import beans.remind.Remind; import beans.uploaderpdata.UploadDataToERP; 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.Row; import common.data.Rows; import common.data.RowsMap; import common.data.SQLFactory; import common.data.db.DBConnect; import org.apache.commons.lang.StringUtils; import restcontroller.R; import restcontroller.sale.cashbill.cashbill; import restcontroller.webmanage.executorService.Executor; import java.io.IOException; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import static beans.order.Order.*; /** * 销售订单 */ public class Order extends Controller { /** * 构造函数 * * @param content */ public Order(JSONObject content) throws YosException { super(content); } @API(title = "新增或更新", apiversion = R.ID20221108111402.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String insertOrUpdate() throws YosException { //表名 String tableName = "sa_order"; Long sa_orderid = content.getLong("sa_orderid"); Long sys_enterpriseid_temp = content.getLongValue("sys_enterpriseid"); if (sys_enterpriseid_temp > 0) { sys_enterpriseid = sys_enterpriseid_temp; } Long sa_brandid = content.getLong("sa_brandid"); Long sa_contractid = content.getLongValue("sa_contractid"); Long sa_projectid = content.getLongValue("sa_projectid"); String type = content.getString("type"); boolean isAdd; SQLFactory sqlFactory = new SQLFactory(this, "订单_新增"); if (sa_orderid <= 0) { isAdd = true; sa_orderid = createTableID(tableName); //默认当前用户为负责人 DataTeam.createTeam(this, "sa_order", sa_orderid, userid); dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "新增", "订单新增成功").getSQL()); } else { Rows rows = dbConnect.runSqlQuery("SELECT status from sa_order WHERE sa_orderid = " + sa_orderid); if (rows.isNotEmpty()) { if (rows.get(0).getString("status").equals("新建") || rows.get(0).getString("status").equals("提交")) { sqlFactory = new SQLFactory(this, "订单_更新"); isAdd = false; dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "更新", "订单更新成功").getSQL()); } else { return getErrReturnObject().setErrMsg("【新建】【提交】状态可修改").toString(); } } else { return getErrReturnObject().setErrMsg("未找到数据").toString(); } } sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderid", sa_orderid); //订单编号 sqlFactory.addParameter("sonum", createBillCode("orderbill")); //订单类型 sqlFactory.addParameter("type", type); //明细分类 sqlFactory.addParameter("typemx", content.getStringValue("typemx")); //订货企业id sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid); //营销账户类型ID sqlFactory.addParameter("sa_accountclassid", beans.order.Order.getDefaultAccount(siteid, type)); //品牌ID sqlFactory.addParameter("sa_brandid", sa_brandid); //合同ID sqlFactory.addParameter("sa_contractid", sa_contractid); //工程项目表ID sqlFactory.addParameter("sa_projectid", sa_projectid); //合作企业财务信息ID（开票信息） Long sys_enterprise_financeid = content.getLongValue("sys_enterprise_financeid"); if (sys_enterprise_financeid <= 0 && isAdd) { sys_enterprise_financeid = getDefaultEnterpriseFinanceid(sys_enterpriseid); } sqlFactory.addParameter("sys_enterprise_financeid", sys_enterprise_financeid); //物流公司档案ID sqlFactory.addParameter("sa_logiscompid", content.getLongValue("sa_logiscompid")); //合作企业联系人表ID（收货信息） //合作企业联系人表ID（收货信息），设置默认 Long rec_contactsid = content.getLongValue("rec_contactsid"); if (rec_contactsid <= 0 && isAdd) { rec_contactsid = getDefaultContactsid(sys_enterpriseid); } sqlFactory.addParameter("rec_contactsid", rec_contactsid); //备注说明 sqlFactory.addParameter("remarks", content.getStringValue("remarks")); //销售人员hrid,业务员hrid if (content.containsKey("saler_hrid")) { hrid = content.getLongValue("saler_hrid"); } if (isAdd) { hrid = getSaleHrid(sys_enterpriseid); } sqlFactory.addParameter("saler_hrid", hrid); //制单方 1表示工厂方，0表示客户方 if (usertype == 21 || usertype == 22) { sqlFactory.addParameter("createflag", 0); } else { sqlFactory.addParameter("createflag", 1); } // 领域 sqlFactory.addParameter("tradefield", content.getString("tradefield")); //结算单位 Long pay_enterpriseid = content.getLongValue("pay_enterpriseid"); if (isAdd) { pay_enterpriseid = sys_enterpriseid; } sqlFactory.addParameter("pay_enterpriseid", pay_enterpriseid); //单据日期 String billdate = content.getStringValue("billdate"); //订单新增时 if (isAdd) { if (billdate.equals("")) { sqlFactory.addParameter_SQL("billdate", "current_time"); } else { sqlFactory.addParameter("billdate", billdate); } } else { //订单更新时 if (billdate.equals("")) { sqlFactory.addParameter_SQL("billdate", "createdate"); } else { sqlFactory.addParameter("billdate", billdate); } } sqlFactory.addParameter("rebate_userate", content.getBigDecimalValue("rebate_userate")); sqlFactory.addParameter("rebate_used", content.getBooleanValue("rebate_used")); sqlFactory.addParameter("signbacknum", content.getStringValue("signbacknum")); sqlFactory.addParameter("tradefieldmx", content.getStringValue("tradefieldmx")); sqlFactory.addParameter("sa_promotionid", content.getLongValue("sa_promotionid")); sqlFactory.addParameter("freightstatus", content.getStringValue("freightstatus")); sqlFactory.addParameter("saletype", content.getStringValue("saletype")); sqlFactory.addParameter("signbackstatus", content.getStringValue("signbackstatus", true, "不需要")); sqlFactory.addParameter("projectnote", content.getStringValue("projectnote")); String sql = sqlFactory.getSQL(); dbConnect.runSqlUpdate(sql); content.put("sa_orderid", sa_orderid); return selectDetail(); } @API(title = "购物车转化订单", apiversion = R.ID20221128183202.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String ship() throws YosException { Long istool = content.getLongValue("istool"); Long sa_brandid = 0L; String tradefield = content.getString("tradefield"); JSONArray items = content.getJSONArray("items"); ArrayList itemidids = new ArrayList(); //判断是否是同一个品牌的商品 for (Object obj : items) { JSONObject item = (JSONObject) obj; Long temp_sa_brandid = item.getLongValue("sa_brandid"); itemidids.add(item.getLongValue("itemid")); if (sa_brandid == 0) { sa_brandid = temp_sa_brandid; } if (!sa_brandid.equals(temp_sa_brandid)) { return getErrReturnObject().setErrMsg("请添加同一品牌的商品").toString(); } } //判断领域 // String sql = "SELECT count(0) count from plm_item_tradefield WHERE tradefield='" + tradefield + "' and itemid in " + itemidids; // sql = sql.replace("[", "(").replace("]", ")"); // if (dbConnect.runSqlQuery(sql).get(0).getLong("count") != itemidids.size()) { // return getErrReturnObject().setErrMsg("请添加同一领域的商品").toString(); // } //表名 String tableName = "sa_order"; String type = content.getStringValue("type", false, "标准订单"); //类型为标准订单时查询默认框架合同信息 Long sa_contractid = 0L; Long sa_projectid = 0L; Long sa_orderid = createTableID(tableName); SQLFactory sqlFactory = new SQLFactory(this, "订单_新增"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderid", sa_orderid); //订单编号 sqlFactory.addParameter("sonum", createBillCode("orderbill")); //订单类型 sqlFactory.addParameter("type", type); //明细分类 sqlFactory.addParameter("typemx", ""); //订货企业id sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid); //营销账户类型ID sqlFactory.addParameter("sa_accountclassid", beans.order.Order.getDefaultAccount(siteid, type)); //品牌ID sqlFactory.addParameter("sa_brandid", sa_brandid); //合同ID sqlFactory.addParameter("sa_contractid", sa_contractid); //工程项目表ID sqlFactory.addParameter("sa_projectid", sa_projectid); //合作企业财务信息ID（开票信息） Long sys_enterprise_financeid = content.getLongValue("sys_enterprise_financeid"); if (sys_enterprise_financeid <= 0) { sys_enterprise_financeid = getDefaultEnterpriseFinanceid(sys_enterpriseid); } sqlFactory.addParameter("sys_enterprise_financeid", sys_enterprise_financeid); //物流公司档案ID //sqlFactory.addParameter("sa_logiscompid", content.getLongValue("sa_logiscompid")); //合作企业联系人表ID（收货信息），设置默认 Long rec_contactsid = content.getLongValue("rec_contactsid"); if (rec_contactsid <= 0) { rec_contactsid = getDefaultContactsid(sys_enterpriseid); } sqlFactory.addParameter("rec_contactsid", rec_contactsid); //备注说明 sqlFactory.addParameter("remarks", content.getStringValue("remarks")); //销售人员hrid,业务员hrid hrid = getSaleHrid(sys_enterpriseid); sqlFactory.addParameter("saler_hrid", hrid); //制单方 1表示工厂方，0表示客户方 if (usertype == 21 || usertype == 22) { sqlFactory.addParameter("createflag", 0); } else { sqlFactory.addParameter("createflag", 1); } sqlFactory.addParameter("tradefield", tradefield); //结算单位 Long pay_enterpriseid = content.getLongValue("pay_enterpriseid"); if (pay_enterpriseid <= 0) { pay_enterpriseid = sys_enterpriseid; } sqlFactory.addParameter("pay_enterpriseid", pay_enterpriseid); //单据日期 sqlFactory.addParameter_SQL("billdate", "current_time"); sqlFactory.addParameter("rebate_used", content.getBooleanValue("rebate_used")); sqlFactory.addParameter("signbacknum", content.getBooleanValue("signbacknum")); sqlFactory.addParameter("tradefieldmx", content.getStringValue("tradefieldmx")); sqlFactory.addParameter("sa_promotionid", content.getLongValue("sa_promotionid")); sqlFactory.addParameter("freightstatus", content.getStringValue("freightstatus")); sqlFactory.addParameter("saletype", content.getStringValue("saletype")); sqlFactory.addParameter("signbackstatus", content.getStringValue("signbackstatus", true, "不需要")); sqlFactory.addParameter("projectnote", content.getStringValue("projectnote")); dbConnect.runSqlUpdate(sqlFactory); content.put("sa_orderid", sa_orderid); content.put("sys_enterpriseid", sys_enterpriseid); content.put("type", type); new OrderItems(content).insertOrUpdate(); ArrayList sqlList = new ArrayList<>(); //删除购物车中的数据 String sql = "DELETE from sa_shoppingcart WHERE sys_enterpriseid = " + sys_enterpriseid + " and itemid in " + itemidids + " AND siteid ='" + siteid + "'"; sql = sql.replace("[", "(").replace("]", ")"); sqlList.add(sql); // //清除多余的操作记录 // sqlList.add("DELETE FROM sys_contrllog WHERE ownertable='sa_shoppingcart' AND siteid='" + siteid + "' AND NOT EXISTS (SELECT 1 FROM sa_shoppingcart WHERE siteid='" + siteid + "' AND sa_shoppingcartid=ownerid )"); //默认当前用户为负责人 sqlList.addAll(DataTeam.createTeamSQL(this, "sa_order", sa_orderid, userid)); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "新增", "购物车转化订单成功").getSQL()); dbConnect.runSqlUpdate(sqlList); ClearData.clearShoppingCart(this); return selectDetail(); } /** * 获取企业默认扣款账户 * * @param sys_enterpriseid * @return * @throws YosException */ public Long getDefaultAccountclassid(Long sys_enterpriseid) throws YosException { Long sa_accountclassid = 0L; SQLFactory sqlFactory = new SQLFactory(this, "查询经销商营销账号"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid); Rows accountRows = dbConnect.runSqlQuery(sqlFactory.getSQL(false)); if (accountRows.isNotEmpty()) { sa_accountclassid = accountRows.get(0).getLong("sa_accountclassid"); } return sa_accountclassid; } /** * 获取企业默认财务信息（开票） * * @param sys_enterpriseid * @return * @throws YosException */ public Long getDefaultEnterpriseFinanceid(Long sys_enterpriseid) throws YosException { Long id = 0L; SQLFactory sqlFactory = new SQLFactory(this, "查询经销商默认开票信息"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid); Rows row = dbConnect.runSqlQuery(sqlFactory.getSQL(false)); if (row.isNotEmpty()) { id = row.get(0).getLong("sys_enterprise_financeid"); } return id; } /** * 获取企业默认联系人 * * @param sys_enterpriseid * @return * @throws YosException */ public Long getDefaultContactsid(Long sys_enterpriseid) throws YosException { Long rec_contactsid = 0L; SQLFactory sqlFactory = new SQLFactory(this, "查询经销商默认联系人"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid); sqlFactory.addParameter_in("sys_enterpriseids", Enterprise.getUpDown(this, sys_enterpriseid)); String sql = sqlFactory.getSQL(false); Rows contactsRows = dbConnect.runSqlQuery(sql); if (contactsRows.isNotEmpty()) { rec_contactsid = contactsRows.get(0).getLong("contactsid"); } return rec_contactsid; } @API(title = "详情", apiversion = R.ID20221108151302.v1.class) @CACHEING public String selectDetail() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "订单_详情"); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter("siteid", siteid); Rows rows = dbConnect.runSqlQuery(sqlFactory); //查询数据：amount，defaultamount，qty,writeoffamount,invoiceamount ArrayList sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>()); sqlFactory = new SQLFactory(this, "订单_查询数据"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sa_orderid", sa_orderids); RowsMap dataRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_orderid"); ArrayList sys_enterpriseids = rows.toArrayList("pay_enterpriseid", new ArrayList<>()); //查询开票信息 sqlFactory = new SQLFactory(this, "查询经销商默认开票信息"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseids); RowsMap financeRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sys_enterpriseid"); for (Row row : rows) { row.put("pay_finance", financeRowsMap.getOrDefault(row.getString("sys_enterpriseid"), new Rows())); Rows dataRows = dataRowsMap.getOrDefault(row.getString("sa_orderid"), new Rows()); BigDecimal writeoffamount = BigDecimal.valueOf(0); BigDecimal invoiceamount = BigDecimal.valueOf(0); BigDecimal amount = BigDecimal.valueOf(0); BigDecimal defaultamount = BigDecimal.valueOf(0); BigDecimal qty = new BigDecimal("0"); if (dataRows.isNotEmpty()) { writeoffamount = dataRows.get(0).getBigDecimal("writeoffamount"); invoiceamount = dataRows.get(0).getBigDecimal("invoiceamount"); amount = dataRows.get(0).getBigDecimal("amount"); defaultamount = dataRows.get(0).getBigDecimal("defaultamount"); qty = dataRows.get(0).getBigDecimal("qty"); } row.put("amount", amount); row.put("defaultamount", defaultamount); row.put("qty", qty); row.put("writeoffamount", writeoffamount); row.put("invoiceamount", invoiceamount); if (writeoffamount.compareTo(BigDecimal.ZERO) == 0) { row.put("writeoffstatus", "未核销"); } else if (writeoffamount.compareTo(amount) == 0) { row.put("writeoffstatus", "已核销"); } else { row.put("writeoffstatus", "部分核销"); } if (invoiceamount.compareTo(BigDecimal.ZERO) == 0) { row.put("invoicestatus", "未开票"); } else if (invoiceamount.compareTo(amount) == 0) { row.put("invoicestatus", "已开票"); } else { row.put("invoicestatus", "部分开票"); } } Row row = rows.isNotEmpty() ? rows.get(0) : new Row(); Long pay_enterpriseid = row.getLong("pay_enterpriseid"); Long sa_accountclassid = row.getLong("sa_accountclassid"); Long saler_hrid = row.getLong("saler_hrid"); //开票信息 sqlFactory = new SQLFactory(this, "订单_开票信息"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); Rows financeRows = dbConnect.runSqlQuery(sqlFactory); row.put("finance", financeRows.isNotEmpty() ? financeRows.get(0) : new Row()); //营销账户类型 sqlFactory = new SQLFactory(this, "订单-查询营销账号"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter("pay_enterpriseid", pay_enterpriseid); sqlFactory.addParameter("sa_accountclassid", sa_accountclassid); String sql = sqlFactory.getSQL(); Rows accountclassRows = dbConnect.runSqlQuery(sql); if (accountclassRows.isNotEmpty()) { accountclassRows.get(0).put("amount", row.getBigDecimal("amount")); accountclassRows.get(0).put("rebate_used", row.getBigDecimal("rebate_used")); accountclassRows.get(0).put("rebate_userate", row.getBigDecimal("rebate_userate")); accountclassRows.get(0).put("rebate_amount", row.getBigDecimal("amount").multiply(row.getBigDecimal("rebate_userate"))); } row.put("accountclass", accountclassRows.isNotEmpty() ? accountclassRows.get(0) : new Row()); //收货信息 row.put("contacts", beans.order.Order.getContactsRow(this, row.getLong("rec_contactsid"))); Rows deliveryRows = dbConnect.runSqlQuery("SELECT count(0) count from plm_item WHERE itemid in (SELECT itemid from sa_orderitems WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "') and delivery >0 and siteid = '" + siteid + "'"); if (deliveryRows.get(0).getLong("count") > 0) { row.put("isdelivery", 1); } else { row.put("isdelivery", 0); } row.put("rebatebalance", Accountbalance.getRebateBalance(this, pay_enterpriseid)); //查询业务员是否可编辑价格 Rows saleRows = dbConnect.runSqlQuery("SELECT * from sa_salearea_hr WHERE hrid = " + hrid + " and siteid='" + siteid + "'"); row.put("saler", saleRows); return getSucReturnObject().setData(row).toString(); } @API(title = "查询订单列表(管理端)", apiversion = R.ID20230105143002.v1.class) public String selectListManage() 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("t1.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t5.erpbillno like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } //状态 if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and("); where.append("t1.status like'%").append(whereObject.getString("status")).append("%' "); where.append(")"); } if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) { where.append(" and("); where.append("t1.type like'%").append(whereObject.getString("type")).append("%' "); where.append(")"); } //品牌 if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) { where.append(" and("); where.append("t3.brandname like'%").append(whereObject.getString("brandname")).append("%' "); where.append(")"); } //领域 if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) { where.append(" and("); where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' "); where.append(")"); } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and t1.billdate >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).append("' "); } if (whereObject.containsKey("writeoffstatus") && !"".equals(whereObject.getString("writeoffstatus"))) { String writeoffstatus = whereObject.getStringValue("writeoffstatus"); if (writeoffstatus.equals("未核销")) { where.append(" and t7.writeoffamount = 0"); } else if (writeoffstatus.equals("已核销")) { where.append(" and t7.writeoffamount = t7.amount and t7.amount > 0"); } else { where.append(" and t7.writeoffamount < t7.amount and t7.writeoffamount > 0"); } } if (whereObject.containsKey("invoicestatus") && !"".equals(whereObject.getString("invoicestatus"))) { String invoicestatus = whereObject.getStringValue("invoicestatus"); if (invoicestatus.equals("未开票")) { where.append(" and t7.invoiceamount = 0"); } else if (invoicestatus.equals("已开票")) { where.append(" and t7.invoiceamount = t7.amount and t7.amount > 0"); } else { where.append(" and t7.invoiceamount < t7.amount and t7.invoiceamount > 0"); } } if (whereObject.containsKey("isreturn") && !"".equals(whereObject.getString("isreturn"))) { String isreturn = whereObject.getStringValue("isreturn"); if (isreturn.equals("1")) { where.append(" and t6.returnqty > 0"); } else { where.append(" and t6.returnqty = 0"); } } if (whereObject.containsKey("ischange") && !"".equals(whereObject.getString("ischange"))) { String ischange = whereObject.getStringValue("ischange"); if (ischange.equals("0")) { where.append(" and not exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)"); } else { where.append(" and exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)"); } } } boolean isExport = content.getBooleanValue("isExport"); SQLFactory sqlFactory = new SQLFactory(this, "订单(管理端)_列表", pageSize, pageNumber, pageSorting); if (isExport) { sqlFactory = new SQLFactory(this, "订单(管理端)_列表"); } sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("hrid", hrid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter_SQL("where", where); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); SQLFactory ischangeSQl = new SQLFactory("sql:SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in $sa_orderids$ "); ischangeSQl.addParameter_in("sa_orderids", rows.toArrayList("sa_orderid")); ArrayList ischangesa_orderidList = dbConnect.runSqlQuery(ischangeSQl.getSQL()).toArrayList("sa_orderid", new ArrayList<>()); for (Row row : rows) { BigDecimal writeoffamount = row.getBigDecimal("writeoffamount"); BigDecimal invoiceamount = row.getBigDecimal("invoiceamount"); BigDecimal amount = row.getBigDecimal("amount"); row.put("ischange", ischangesa_orderidList.contains(row.getLong("sa_orderid")) ? "1" : "0"); if (writeoffamount.compareTo(BigDecimal.ZERO) == 0) { row.put("writeoffstatus", "未核销"); } else if (writeoffamount.compareTo(amount) == 0) { row.put("writeoffstatus", "已核销"); } else { row.put("writeoffstatus", "部分核销"); } if (invoiceamount.compareTo(BigDecimal.ZERO) == 0) { row.put("invoicestatus", "未开票"); } else if (invoiceamount.compareTo(amount) == 0) { row.put("invoicestatus", "已开票"); } else { row.put("invoicestatus", "部分开票"); } } if (isExport) { //去除不需要导出项 rows.getFieldList().remove("sa_orderid"); rows.getFieldList().remove("submitdate"); rows.getFieldList().remove("checkdate"); rows.getFieldList().remove("typemx"); rows.getFieldList().remove("sys_enterpriseid"); //添加 rows.getFieldList().add("amount"); rows.getFieldList().add("qty"); Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap()); return getSucReturnObject().setData(uploadRows).toString(); } return getSucReturnObject().setData(rows).toString(); } @API(title = "查询订单列表(业务员)", apiversion = R.ID20221111145202.v1.class) public String selectList() 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("t1.sonum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } //状态 if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and("); where.append("t1.status like'%").append(whereObject.getString("status")).append("%' "); where.append(")"); } //品牌 if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) { where.append(" and("); where.append("t3.brandname like'%").append(whereObject.getString("brandname")).append("%' "); where.append(")"); } //领域 if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) { where.append(" and("); where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' "); where.append(")"); } if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) { where.append(" and("); where.append("t1.type like'%").append(whereObject.getString("type")).append("%' "); where.append(")"); } if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) { where.append(" and t1.billdate >='").append(whereObject.getString("begindate")).append("' "); } if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) { where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).append("' "); } if (whereObject.containsKey("writeoffstatus") && !"".equals(whereObject.getString("writeoffstatus"))) { String writeoffstatus = whereObject.getStringValue("writeoffstatus"); if (writeoffstatus.equals("未核销")) { where.append(" and t7.writeoffamount = 0"); } else if (writeoffstatus.equals("已核销")) { where.append(" and t7.writeoffamount = t7.amount and t7.amount > 0"); } else { where.append(" and t7.writeoffamount < t7.amount and t7.writeoffamount > 0"); } } if (whereObject.containsKey("invoicestatus") && !"".equals(whereObject.getString("invoicestatus"))) { String invoicestatus = whereObject.getStringValue("invoicestatus"); if (invoicestatus.equals("未开票")) { where.append(" and t7.invoiceamount = 0"); } else if (invoicestatus.equals("已开票")) { where.append(" and t7.invoiceamount = t7.amount and t7.amount > 0"); } else { where.append(" and t7.invoiceamount < t7.amount and t7.invoiceamount > 0"); } } } ArrayList sys_enterpriseids = Hr.getAgents(this, hrid); String sql = " and t1.sys_enterpriseid in " + sys_enterpriseids; sql = sql.replace("[", "(").replace("]", ")"); where.append(sql); boolean isExport = content.getBooleanValue("isExport"); SQLFactory sqlFactory = new SQLFactory(this, "订单_列表", pageSize, pageNumber, pageSorting); if (isExport) { sqlFactory = new SQLFactory(this, "订单_列表"); } sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("hrid", hrid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter_SQL("where", where); sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); //查询数据：amount，defaultamount，qty ArrayList sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>()); sqlFactory = new SQLFactory(this, "订单_查询数据"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sa_orderid", sa_orderids); RowsMap dataRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_orderid"); for (Row row : rows) { Rows dataRows = dataRowsMap.getOrDefault(row.getString("sa_orderid"), new Rows()); if (dataRows.isNotEmpty()) { row.putAll(dataRows.get(0)); } else { row.put("amount", 0); row.put("defaultamount", 0); row.put("qty", 0); } } if (isExport) { //去除不需要导出项 rows.getFieldList().remove("sa_orderid"); rows.getFieldList().remove("submitdate"); rows.getFieldList().remove("checkdate"); rows.getFieldList().remove("typemx"); rows.getFieldList().remove("sys_enterpriseid"); //添加 rows.getFieldList().add("amount"); rows.getFieldList().add("qty"); Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap()); return getSucReturnObject().setData(uploadRows).toString(); } sqlFactory = new SQLFactory(this, "订单-查询筛选出的价格"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseids); sqlFactory.addParameter_SQL("where", where); Rows amountrows = dbConnect.runSqlQuery(sqlFactory); return getSucReturnObject().setData(rows).setTips(amountrows.get(0).toJsonObject()).toString(); } //返回导出的标题 public HashMap getTitleMap() { HashMap titleMap = new HashMap<>(); titleMap.put("sonum", "订单编号"); titleMap.put("brandname", "品牌名称"); titleMap.put("type", "订单类型"); titleMap.put("tradefield", "领域"); titleMap.put("remarks", "备注"); titleMap.put("status", "状态"); titleMap.put("createdate", "创建时间"); titleMap.put("enterprisename", "经销商名称"); titleMap.put("abbreviation", "简称"); titleMap.put("name", "收货人"); titleMap.put("amount", "订单金额"); titleMap.put("qty", "订单数量"); return titleMap; } @API(title = "删除", apiversion = R.ID20221108152102.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String delete() throws YosException { JSONArray sa_orderids = content.getJSONArray("sa_orderids"); if (sa_orderids.size() <= 0) { return getErrReturnObject().setErrMsg("请选择要执行删除操作的数据。").toString(); } String sql = "SELECT 1 from sa_order WHERE sa_orderid in " + sa_orderids + " and `status` !='新建' and siteid = '" + siteid + "'"; sql = sql.replace("[", "(").replace("]", ")"); if (dbConnect.runSqlQuery(sql).isNotEmpty()) { return getErrReturnObject().setErrMsg("存在非新建状态数据，无法执行删除操作。").toString(); } ArrayList sqlList = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "订单_删除"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sa_orderid", sa_orderids.toArray()); sqlList.add(sqlFactory.getSQL()); //订单删除原因 DataExtend.createDeleteReasonSql(this, "sa_order", sa_orderids.toJavaList(Long.class), content.getStringValue("deletereason")); //操作记录 for (Object obj : sa_orderids) { Long sa_orderid = Long.valueOf(obj.toString()); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "删除", "订单删除").getSQL()); } dbConnect.runSqlUpdate(sqlList); ClearData.clearOrder(this); return getSucReturnObject().toString(); } @API(title = "更新项目备注，备注，销售类别", apiversion = R.ID20230301152702.v1.class) public String update() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); String projectnote = content.getStringValue("projectnote"); String remarks = content.getStringValue("remarks"); String saletype = content.getStringValue("saletype"); Rows orderRows = getOrderRows(this, sa_orderid); if (orderRows.get(0).getString("status").equals("关闭")) { return getErrReturnObject().setErrMsg("关闭状态无法修改").toString(); } SQLFactory sqlFactory = new SQLFactory(this, "更新订单项目备注等信息"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter("projectnote", projectnote); sqlFactory.addParameter("remarks", remarks); sqlFactory.addParameter("saletype", saletype); dbConnect.runSqlUpdate(sqlFactory); return selectDetail(); } @API(title = "使用返利金", apiversion = R.ID20230218225002.v1.class) public String isUseRate() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); boolean isused = content.getBooleanValue("isused"); BigDecimal rebateamount = content.getBigDecimalValue("rebateamount"); if (rebateamount.compareTo(BigDecimal.ZERO) == 0) { rebateamount = getMaxUsedRebateAmount(this, sa_orderid); } ArrayList sqlList = new ArrayList<>(); if (Parameter.get(siteid, "order_rebate_used").equals("0")) { return getErrReturnObject().setErrMsg("站点未开启返利金，无法使用返利金").toString(); } Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("未找到订单数据，无法使用返利金").toString(); } Row orderRow = rows.get(0); String type = orderRow.getString("type"); if (type.equals("标准订单") || type.equals("特殊订单")) { //先初始化返利金价格 sqlList.add(beans.order.Order.updateRateInfo(this, sa_orderid, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO)); sqlList.add("UPDATE sa_orderitems SET amount = defaultamount ,price = defaultprice ,rebateamount = 0 WHERE sa_orderid = " + sa_orderid + " and siteid='" + siteid + "'"); dbConnect.runSqlUpdate(sqlList); sqlList.clear(); //使用返利金 if (isused) { //站点返利金比例 BigDecimal order_rebate_userate = BigDecimal.valueOf(Double.valueOf(Parameter.get(siteid, "order_rebate_userate"))); //订单金额 BigDecimal defaultAmount = getDefaultAmount(this, sa_orderid); //返利金最大使用金额 BigDecimal maxAmount = defaultAmount.multiply(order_rebate_userate).divide(BigDecimal.valueOf(1)); if (rebateamount.compareTo(maxAmount) == 1) { return getErrReturnObject().setErrMsg("超过最大使用金度:" + maxAmount).toString(); } Long pay_enterpriseid = orderRow.getLong("pay_enterpriseid"); if (pay_enterpriseid <= 0) { return getErrReturnObject().setErrMsg("请选择结算企业").toString(); } //当前返利金额度 BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, pay_enterpriseid); if (rebateamount.compareTo(rebateBalance) == 1) { return getErrReturnObject().setErrMsg("超出返利金账户余额，可用余额为:" + rebateBalance).toString(); } BigDecimal rebate_userate; if (defaultAmount.compareTo(BigDecimal.ZERO) == 0) { rebate_userate = BigDecimal.ZERO; } else { rebate_userate = rebateamount.divide(defaultAmount, 6, BigDecimal.ROUND_CEILING); } sqlList.add(beans.order.Order.updateRateInfo(this, sa_orderid, BigDecimal.valueOf(1), rebate_userate, rebateamount)); dbConnect.runSqlUpdate(sqlList); // //更新订单商品数据 setRebateAmount(this, sa_orderid, rebateamount); adjustLastRebateAmount(this, sa_orderid); } } else { return getErrReturnObject().setErrMsg("非标准订单或特殊订单无法使用返利金").toString(); } return getSucReturnObject().setData(rebateamount).toString(); } @API(title = "提交", apiversion = R.ID20221108153402.v1.class, intervaltime = 5000) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class}) public String submit() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); Long sa_accountclassid = row.getLong("sa_accountclassid"); if (sa_accountclassid <= 0) { return getErrReturnObject().setErrMsg("请选择支付账号").toString(); } //验证结算企业 Long pay_enterpriseid = row.getLong("pay_enterpriseid"); if (pay_enterpriseid <= 0) { return getErrReturnObject().setErrMsg("请选择结算企业").toString(); } Long sys_enterprise_financeid = row.getLong("sys_enterprise_financeid"); if (sys_enterprise_financeid <= 0) { return getErrReturnObject().setErrMsg("请选择财务信息").toString(); } //验证收货人信息 Long rec_contactsid = row.getLong("rec_contactsid"); if (rec_contactsid <= 0) { return getErrReturnObject().setErrMsg("请选择物收货人信息").toString(); } Long sys_enterpriseid = row.getLong("sys_enterpriseid"); if (sys_enterpriseid <= 0) { return getErrReturnObject().setErrMsg("请选择订货企业信息").toString(); } if (!((row.getString("status").equals("新建") && !row.getString("type").equals("特殊订单")) || (row.getString("status").equals("确认") && row.getString("type").equals("特殊订单")))) { return getErrReturnObject().setErrMsg("新建或非确认无法提交").toString(); } if (dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid="+sa_orderid+" and siteid='"+siteid+"'").isEmpty()) { return getErrReturnObject().setErrMsg("订单表体不存在商品无法提交").toString(); } // String type = row.getString("type"); //提交 String status = "提交"; BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid); BigDecimal freefreightamount = BigDecimal.ZERO; if (!enterpriseRow.isEmpty()) { freefreightamount = enterpriseRow.getBigDecimal("freefreightamount"); } String freightstatus = row.getString("freightstatus"); if (freightstatus.isEmpty()) { if (freefreightamount.compareTo(BigDecimal.ZERO) == -1) { freightstatus = "到付"; } if (freefreightamount.compareTo(BigDecimal.ZERO) == 0) { freightstatus = "预付"; } if (freefreightamount.compareTo(BigDecimal.ZERO) == 1) { if (amount.compareTo(freefreightamount) == 1) { freightstatus = "预付"; } else { freightstatus = "到付"; } } } String sonum = row.getString("sonum"); ArrayList sqlList = new ArrayList<>(); String order_paymentnode = Parameter.get(siteid, "order_paymentnode"); //订单返利使用开关 Boolean rebate_used = row.getBoolean("rebate_used"); //站点订单返利使用开关 String order_rebate_used = Parameter.get(siteid, "order_rebate_used"); if (order_paymentnode.equals("1")) { if (order_rebate_used.equals("1") && rebate_used) { //站点返利金比例 BigDecimal order_rebate_userate = BigDecimal.valueOf(Double.valueOf(Parameter.get(siteid, "order_rebate_userate"))); //订单返利金比例 BigDecimal rebate_userate = row.getBigDecimal("rebate_userate"); if (rebate_userate.compareTo(order_rebate_userate) == 1) { return getErrReturnObject().setErrMsg("返利金使用比例超出站点设置比例，站点比例为" + order_rebate_userate).toString(); } //查询返利金账户余额 BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, pay_enterpriseid); //返利金金额 BigDecimal rebateamount = row.getBigDecimal("rebateamount"); if (rebateamount.compareTo(rebateBalance) == 1) { return getErrReturnObject().setErrMsg("超过返利金账户余额，返利金账户余额为" + rebateBalance).toString(); } if (!Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) { return getErrReturnObject().setErrMsg("账户余额不足").toString(); } sqlList.addAll(useRateBill(row, "订单提交")); } else { if (Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) { CashbillEntity entity = getCashbillEntity(amount, sa_orderid, "订单提交", "由" + sonum + "订单提交时自动产生"); sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true)); } else { return getErrReturnObject().setErrMsg("账户余额不足").toString(); } } } String message = "您的订单【" + sonum + "】 金额为【" + amount + "】 已提交！"; sqlList.add(getOrderSubmitSql(sa_orderid, status, freightstatus)); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单提交", "订单提交成功，金额：" + amount).getSQL()); dbConnect.runSqlUpdate(sqlList); sendMsg(message, sa_orderid, sys_enterpriseid); Executor.sendEml(this, "ordercheck", sa_orderid, new ArrayList<>()); return getSucReturnObject().toString(); } public void sendMsg(String content, Long sa_orderid, Long sys_enterpriseid) throws YosException { ArrayList userList = getEnterpriseHrs(sys_enterpriseid).toArrayList("userid", new ArrayList<>()); Remind remind = new Remind(this); remind.setTitle("订单消息"); remind.setContent(content); remind.setType("应用"); remind.setObjectid(sa_orderid); remind.setObjectname("sa_order"); remind.setTouserid(userList); remind.createSys_message(); } /** * @param orderRow * @param source * @return * @throws YosException */ public ArrayList useRateBill(Row orderRow, String source) throws YosException { ArrayList sqlList = new ArrayList<>(); Long sa_orderid = orderRow.getLong("sa_orderid"); Long pay_enterpriseid = orderRow.getLong("pay_enterpriseid"); Long sa_accountclassid = orderRow.getLong("sa_accountclassid"); BigDecimal rebateamount = orderRow.getBigDecimal("rebateamount"); String sonum = orderRow.getString("sonum"); //金额=金额-返利金金额 BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); //创建支出凭证 CashbillEntity entity = getCashbillEntity(amount, sa_orderid, source, "由" + sonum + source + "时自动产生"); sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true)); //创建返利金支出凭证 Rows cashbillRows = dbConnect.runSqlQuery("SELECT * from sa_cashbill WHERE class='返利金' and STATUS ='审核' and rebate_enddate>=CURRENT_TIME and rebate_balance>0 and sys_enterpriseid= " + pay_enterpriseid + " and siteid = '" + siteid + "' ORDER BY rebate_enddate "); int count = 0; while (rebateamount.compareTo(BigDecimal.ZERO) == 1) { Row cashbillRow = cashbillRows.get(count); //返利账户 sa_accountclassid = cashbillRow.getLong("sa_accountclassid"); //返利 Long sa_cashbillid = cashbillRow.getLong("sa_cashbillid"); BigDecimal rebate_balance = cashbillRow.getBigDecimal("rebate_balance"); CashbillEntity entity2 = new CashbillEntity(); //返利代金券小于返利金金额，例如：rebate_balance:10,rebateamount:50 if (rebate_balance.compareTo(rebateamount) == -1) { entity2 = getCashbillEntity(rebate_balance, sa_orderid, source, "【返利金】由" + sonum + source + "时自动产生"); rebateamount = rebateamount.subtract(rebate_balance); rebate_balance = BigDecimal.ZERO; } //返利代金券等于返利金金额 if (rebate_balance.compareTo(rebateamount) == 0) { entity2 = getCashbillEntity(rebate_balance, sa_orderid, source, "【返利金】由" + sonum + source + "时自动产生"); rebateamount = BigDecimal.ZERO; rebate_balance = BigDecimal.ZERO; } //返利代金券大于返利金金额 if (rebate_balance.compareTo(rebateamount) == 1) { entity2 = getCashbillEntity(rebateamount, sa_orderid, source, "【返利金】由" + sonum + source + "时自动产生"); rebate_balance = rebate_balance.subtract(rebateamount); rebateamount = BigDecimal.ZERO; } //不要放到sqlList中，多张返利金时会出现同时使用同一个余额减 dbConnect.runSqlUpdate(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity2, true, sa_cashbillid)); sqlList.add(updateCashbillSql(sa_cashbillid, rebate_balance)); count++; } return sqlList; } @API(title = "审核", apiversion = R.ID20221108153502.v1.class, intervaltime = 5000) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class}) public String check() throws YosException { boolean isdebug = content.getBooleanValue("isdebug"); Long sa_orderid = content.getLong("sa_orderid"); String reviewtype = content.getString("reviewtype"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); Long sys_enterpriseid = row.getLong("sys_enterpriseid"); Long sa_accountclassid = row.getLong("sa_accountclassid"); if (sa_accountclassid <= 0) { return getErrReturnObject().setErrMsg("请选择支付账号").toString(); } // if (siteid.equals("TZ")) { // if (StringUtils.isBlank(reviewtype)) { // return getErrReturnObject().setErrMsg("订单评审类型为空，无法审核").toString(); // } // } // if (StringUtils.isBlank(row.getString("saletype"))) { // return getErrReturnObject().setErrMsg("销售分类为空，无法审核").toString(); // } //验证结算企业 Long pay_enterpriseid = row.getLong("pay_enterpriseid"); if (pay_enterpriseid <= 0) { return getErrReturnObject().setErrMsg("请选择结算企业").toString(); } // String type = row.getString("type"); String sonum = row.getString("sonum"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); String order_paymentnode = Parameter.get(siteid, "order_paymentnode"); //订单返利使用开关 Boolean rebate_used = row.getBoolean("rebate_used"); //站点订单返利使用开关 String order_rebate_used = Parameter.get(siteid, "order_rebate_used"); if (order_paymentnode.equals("2")) { if (order_rebate_used.equals("1") && rebate_used) { //站点返利金比例 BigDecimal order_rebate_userate = BigDecimal.valueOf(Double.valueOf(Parameter.get(siteid, "order_rebate_userate"))); //订单返利金比例 BigDecimal rebate_userate = row.getBigDecimal("rebate_userate"); if (rebate_userate.compareTo(order_rebate_userate) == 1) { return getErrReturnObject().setErrMsg("返利金使用比例超出站点设置比例，站点比例为" + order_rebate_userate).toString(); } //查询返利金账户余额 BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, pay_enterpriseid); //返利金金额 BigDecimal rebateamount = row.getBigDecimal("rebateamount"); if (rebateamount.compareTo(rebateBalance) == 1) { return getErrReturnObject().setErrMsg("超过返利金账户余额，返利金账户余额为" + rebateBalance).toString(); } if (!Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) { return getErrReturnObject().setErrMsg("账户余额不足").toString(); } sqlList.addAll(useRateBill(row, "订单审核")); } else { if (Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) { CashbillEntity entity = getCashbillEntity(amount, sa_orderid, "订单审核", "由" + sonum + "订单审核时自动产生"); sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true)); } else { return getErrReturnObject().setErrMsg("账户余额不足").toString(); } } } String message = "您的订单【" + sonum + "】 金额为【" + amount + "】 已审核！"; sqlList.add(getOrderCheckSql(sa_orderid, reviewtype)); dbConnect.runSqlUpdate(sqlList); Rows orderBrand = dbConnect.runSqlQuery("select brandname from sa_brand where siteid='" + siteid + "' and sa_brandid=" + row.getLong("sa_brandid")); if (Parameter.get("system.erp_dockswitch").equalsIgnoreCase("true")) { UploadDataToERP uploadDataToERP = new UploadDataToERP(this, sa_orderid, ""); boolean issuccess = uploadDataToERP.upload(); if (!issuccess) { uncheck(); return getErrReturnObject().setErrMsg("上传ERP失败，当前单据将自动反审核，请检查！").toString(); } else { dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单审核", "订单审核成功，金额：" + amount).getSQL()); sendMsg(message, sa_orderid, sys_enterpriseid); sendEmail(sa_orderid, row, orderBrand); } } else { dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单审核", "订单审核成功，金额：" + amount).getSQL()); sendMsg(message, sa_orderid, sys_enterpriseid); sendEmail(sa_orderid, row, orderBrand); } return getSucReturnObject().toString(); } @API(title = "反审核", apiversion = R.ID20230102141502.v1.class, intervaltime = 5000) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class}) public String uncheck() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); if (dbConnect.runSqlQuery("SELECT 1 from sa_dispatch t1 left join sa_dispatch_items t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid left join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid WHERE t1.status!='关闭' and t1.siteid='" + siteid + "' and t3.sa_orderid=" + sa_orderid).isNotEmpty()) { return getErrReturnObject().setErrMsg("存在发货单，无法反审核").toString(); } //验证结算企业 Long sys_enterpriseid = row.getLong("sys_enterpriseid"); String sonum = row.getString("sonum"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "订单_反审核"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter_in("sa_orderid", sa_orderid); sqlList.add(sqlFactory.getSQL()); //订单扣款节点（1:订单提交、2:订单审核） String order_paymentnode = Parameter.get(siteid, "order_paymentnode"); if (order_paymentnode.equals("2")) { sqlList.addAll(returnPayBill(row, "订单反审核", "由" + sonum + "订单反审核时自动产生")); } String message = "您的订单【" + sonum + "】已反审核，请注意查看！"; sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单反审核", "订单反审核成功，金额：" + amount).getSQL()); dbConnect.runSqlUpdate(sqlList); sendMsg(message, sa_orderid, sys_enterpriseid); return getSucReturnObject().toString(); } @API(title = "预提交反预提交", apiversion = R.ID20230331152503.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String presubmit() throws YosException { boolean isrecheck = content.getBooleanValue("isrecheck"); Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); String sonum = row.getString("sonum"); //验证结算企业 Long sys_enterpriseid = row.getLong("sys_enterpriseid"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); if (dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid="+sa_orderid+" and siteid='"+siteid+"'").isEmpty()) { return getErrReturnObject().setErrMsg("订单表体不存在商品无法提交").toString(); } ArrayList sqlList = new ArrayList<>(); if (isrecheck) { if (!row.getString("status").equals("新建")) { return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非新建状态，无法预提交") .toString(); } } else { if (!row.getString("status").equals("预提交")) { return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非预提交状态，无法撤回") .toString(); } } SQLFactory sqlFactoryupdate; String message = ""; if (isrecheck) { message = "您的订单【" + sonum + "】已预提交，请注意查看！"; sqlFactoryupdate = new SQLFactory(this, "订单预提交"); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "预提交", "订单预提交成功，金额：" + amount).getSQL()); } else { message = "您的订单【" + sonum + "】已反预提交，请注意查看！"; sqlFactoryupdate = new SQLFactory(this, "订单反预提交"); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "反预提交", "订单反预提交成功，金额：" + amount).getSQL()); } sqlFactoryupdate.addParameter("siteid", siteid); sqlFactoryupdate.addParameter("sa_orderid", sa_orderid); sqlFactoryupdate.addParameter("recheckby", username); sqlList.add(sqlFactoryupdate.getSQL()); dbConnect.runSqlUpdate(sqlList); sendMsg(message, sa_orderid, sys_enterpriseid); return getSucReturnObject().toString(); } @API(title = "确认反确认", apiversion = R.ID20230331102503.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String recheck() throws YosException { boolean isrecheck = content.getBooleanValue("isrecheck"); Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); String sonum = row.getString("sonum"); //验证结算企业 Long sys_enterpriseid = row.getLong("sys_enterpriseid"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); if (isrecheck) { if (row.getBoolean("createflag")) { if (!row.getString("status").equals("新建")) { return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为新建状态，无法确认") .toString(); } } else { if (!row.getString("status").equals("预提交")) { return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非预提交状态，无法确认") .toString(); } } } else { if (!row.getString("status").equals("确认")) { return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非确认状态，无法反确认") .toString(); } } SQLFactory sqlFactoryupdate; String message = ""; if (isrecheck) { message = "您的订单【" + sonum + "】已确认，请注意查看！"; sqlFactoryupdate = new SQLFactory(this, "订单确认"); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "确认", "订单确认成功，金额：" + amount).getSQL()); } else { message = "您的订单【" + sonum + "】已反确认，请注意查看！"; if (!row.getBoolean("createflag")) { sqlFactoryupdate = new SQLFactory(this, "订单反确认(业务端创建)"); } else { sqlFactoryupdate = new SQLFactory(this, "订单反确认(管理端创建)"); } sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "反确认", "订单反确认成功，金额：" + amount).getSQL()); } sqlFactoryupdate.addParameter("siteid", siteid); sqlFactoryupdate.addParameter("sa_orderid", sa_orderid); sqlFactoryupdate.addParameter("recheckby", username); sqlList.add(sqlFactoryupdate.getSQL()); dbConnect.runSqlUpdate(sqlList); sendMsg(message, sa_orderid, sys_enterpriseid); return getSucReturnObject().toString(); } @API(title = "退回", apiversion = R.ID20221108161902.v1.class, intervaltime = 5000) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class}) public String back() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); Long sa_accountclassid = row.getLong("sa_accountclassid"); //验证结算企业 Long pay_enterpriseid = row.getLong("pay_enterpriseid"); String sonum = row.getString("sonum"); Long sys_enterpriseid = row.getLong("sys_enterpriseid"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "订单_退回"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter_in("sa_orderid", sa_orderid); sqlList.add(sqlFactory.getSQL()); // Rows cashbillRows = getCashbillRows(sa_orderid); // String source = ""; // if (cashbillRows.isNotEmpty()) { // source = cashbillRows.get(0).getString("source"); // amount = cashbillRows.get(0).getBigDecimal("amount"); // } // if (source.equals("订单提交") || source.equals("订单审核")) { // CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, "订单退回", "由" + sonum + "订单退回时自动产生"); // sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true)); // } sqlList.addAll(returnPayBill(row, "订单退回", "由" + sonum + "订单退回时自动产生")); String message = "您的订单【" + sonum + "】 金额为【" + amount + "】 已退回，退回原因【" + content.getStringValue("backreason") + "】，请注意查看"; sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单退回", "订单退回成功，金额：" + amount).getSQL()); dbConnect.runSqlUpdate(sqlList); //订单退回原因 ArrayList sa_orderids = new ArrayList<>(); sa_orderids.add(sa_orderid); DataExtend.createBackReasonSql(this, "sa_order", sa_orderids, content.getStringValue("backreason")); sendMsg(message, sa_orderid, sys_enterpriseid); return getSucReturnObject().toString(); } @API(title = "撤回", apiversion = R.ID20230301190202.v1.class, intervaltime = 5000) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class}) public String revoke() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); if (!row.getString("status").equals("提交")) { return getErrReturnObject().setErrMsg("非提交状态无法撤回").toString(); } Long sa_accountclassid = row.getLong("sa_accountclassid"); //验证结算企业 Long pay_enterpriseid = row.getLong("pay_enterpriseid"); Long sys_enterpriseid = row.getLong("sys_enterpriseid"); String sonum = row.getString("sonum"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); if (row.getString("type").equals("特殊订单")) { sqlList.add("update sa_order set status='确认' where sa_orderid = " + sa_orderid + " and siteid='" + siteid + "'"); } else { SQLFactory sqlFactory = new SQLFactory(this, "订单_撤回"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter_in("sa_orderid", sa_orderid); sqlList.add(sqlFactory.getSQL()); } sqlList.addAll(returnPayBill(row, "订单撤回", "由" + sonum + "订单撤回时自动产生")); String message = "您的订单【" + sonum + "】 金额为【" + amount + "】 已撤回！"; sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单撤回", "订单撤回成功，金额：" + amount).getSQL()); dbConnect.runSqlUpdate(sqlList); //订单退回原因 sendMsg(message, sa_orderid, sys_enterpriseid); return getSucReturnObject().toString(); } @API(title = "交期待确认", apiversion = R.ID20230102143502.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String confirmDelivery() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); Long sa_accountclassid = row.getLong("sa_accountclassid"); if (sa_accountclassid <= 0) { return getErrReturnObject().setErrMsg("请选择支付账号").toString(); } //验证结算企业 Long pay_enterpriseid = row.getLong("pay_enterpriseid"); if (pay_enterpriseid <= 0) { return getErrReturnObject().setErrMsg("请选择结算企业").toString(); } Long sys_enterprise_financeid = row.getLong("sys_enterprise_financeid"); if (sys_enterprise_financeid <= 0) { return getErrReturnObject().setErrMsg("请选择财务信息").toString(); } // //物流 // Long sa_logiscompid = row.getLong("sa_logiscompid"); // if (sa_logiscompid <= 0) { // return getErrReturnObject().setErrMsg("请选择物流信息").toString(); // } //验证收货人信息 Long rec_contactsid = row.getLong("rec_contactsid"); if (rec_contactsid <= 0) { return getErrReturnObject().setErrMsg("请选择物收货人信息").toString(); } Long sys_enterpriseid = row.getLong("sys_enterpriseid"); if (sys_enterpriseid <= 0) { return getErrReturnObject().setErrMsg("请选择订货企业信息").toString(); } //提交，交期待确认 String status = "交期待确认"; BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "订单_交期待确认"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter("status", status); sqlList.add(sqlFactory.getSQL()); sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, status, "订单" + status + "：" + amount).getSQL()); dbConnect.runSqlUpdate(sqlList); Executor.sendEml(this, "orderresponse", sa_orderid, new ArrayList<>()); return getSucReturnObject().toString(); } @API(title = "确认交期，操作交期待确认的订单", apiversion = R.ID20221230094802.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String confirmDeliveryDate() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } //交期待确认 String status = rows.get(0).getString("status"); if (!status.equals("交期待确认")) { return getErrReturnObject().setErrMsg("只能操作交期待确认的订单").toString(); } ArrayList sqlList = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "订单-确认交期"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlList.add(sqlFactory.getSQL()); //操作记录 sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "交期确认", "订单交期确认").getSQL()); dbConnect.runSqlUpdate(sqlList); return getSucReturnObject().toString(); } @API(title = "交期撤回", apiversion = R.ID20230227160002.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String revokeDelivery() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = getOrderRows(this, sa_orderid); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); Long sa_accountclassid = row.getLong("sa_accountclassid"); //验证结算企业 Long pay_enterpriseid = row.getLong("pay_enterpriseid"); Long sys_enterpriseid = row.getLong("sys_enterpriseid"); String sonum = row.getString("sonum"); BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid); ArrayList sqlList = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "订单_交期撤回"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter_in("sa_orderid", sa_orderid); sqlList.add(sqlFactory.getSQL()); //订单扣款节点（1:订单提交、2:订单审核） String order_paymentnode = Parameter.get(siteid, "order_paymentnode"); if (order_paymentnode.equals("2")) { sqlList.addAll(returnPayBill(row, "订单_交期撤回", "由" + sonum + "订单交期撤回时自动产生")); } sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单交期撤回", "订单交期撤回：" + amount).getSQL()); dbConnect.runSqlUpdate(sqlList); sendMsg("订单交期撤回" + sonum, sa_orderid, sys_enterpriseid); return getSucReturnObject().toString(); } public ArrayList returnPayBill(Row orderrow, String source, String remarks) throws YosException { Long sa_orderid = orderrow.getLong("sa_orderid"); Long sa_accountclassid = orderrow.getLong("sa_accountclassid"); //验证结算企业 Long pay_enterpriseid = orderrow.getLong("pay_enterpriseid"); ArrayList sqlList = new ArrayList<>(); BigDecimal amount; Rows rebateRows = getCashbillRebateRows(sa_orderid); //返利金 if (rebateRows.isNotEmpty()) { for (Row rebateRow : rebateRows) { amount = rebateRow.getBigDecimal("amount"); if (amount.compareTo(BigDecimal.ZERO) > 0) { Long sa_cashbillid_rebate = rebateRow.getLong("sa_cashbillid_rebate"); CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, source, "【返利金】" + remarks); sqlList.addAll(beans.order.Order.createRebateBillPay(this, entity, pay_enterpriseid, sa_cashbillid_rebate)); } } } //金额 Rows amountRows = getCashbillRows(sa_orderid); if (amountRows.isNotEmpty()) { amount = amountRows.get(0).getBigDecimal("amount"); if (amount.compareTo(BigDecimal.ZERO) > 0) { CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, source, remarks); sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true)); } } return sqlList; } /** * 邮件发送验证 * * @param sa_orderid * @param row * @param orderBrand */ public void sendEmail(Long sa_orderid, Row row, Rows orderBrand) { switch (row.getString("tradefield")) { case "全领域": Executor.sendEml(this, "ordertradefield", sa_orderid, new ArrayList<>()); break; case "燃气": Executor.sendEml(this, "ordertradefieldrq", sa_orderid, new ArrayList<>()); break; case "给水": Executor.sendEml(this, "ordertradefieldgs", sa_orderid, new ArrayList<>()); break; case "暖通": Executor.sendEml(this, "ordertradefieldnt", sa_orderid, new ArrayList<>()); break; case "消防": Executor.sendEml(this, "ordertradefieldxf", sa_orderid, new ArrayList<>()); break; } if (orderBrand.isNotEmpty()) { switch (orderBrand.get(0).getString("brandname")) { case "全品牌": Executor.sendEml(this, "orderbrand", sa_orderid, new ArrayList<>()); break; case "班尼戈": Executor.sendEml(this, "orderbrandbng", sa_orderid, new ArrayList<>()); break; case "中捷": Executor.sendEml(this, "orderbrandzj", sa_orderid, new ArrayList<>()); break; } } } /** * 订单结案验证 * * @throws */ public String closeordervalidate(long sa_orderid) throws YosException { ArrayList sqlList = new ArrayList<>(); Rows Rowstatus = dbConnect.runSqlQuery( "select * from sa_order where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'"); Rows RowsDetail = dbConnect.runSqlQuery( "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by rowno asc"); if (Rowstatus.isEmpty()) { return "订单不存在"; } else { if (!Rowstatus.get(0).getString("status").equals("审核")) { return "非审核状态的订单无法结案"; } } if (!siteid.equalsIgnoreCase("TZ")) { return "当前站点不可执行订单结案操作"; } DBConnect connect = new DBConnect("ORACLE_TZ"); Rows rows = connect.runSqlQuery("select SO_LINE2,sum(QTY)as QTY from v_eso_shipped_qty where SO_NO2='" + Rowstatus.get(0).getString("sonum") + "' and ZT='" + (siteid.equalsIgnoreCase("TZ") ? "IDCZJ10" : "IBPNB10") + "' group by SO_LINE2"); for (Row row : rows) { int SO_LINE2 = row.getInteger("SO_LINE2");//erp订单行号 double QTY = row.getInteger("QTY");//erp已发货数量 int i = 0; for (Row row2 : RowsDetail) { int rowno = row2.getInteger("rowno");//订单行号 if (SO_LINE2 == rowno) { sqlList.add("update sa_orderitems set erpdeliedqty=" + QTY + " where sa_orderitemsid=" + row2.getLong("sa_orderitemsid")); } } dbConnect.runSqlUpdate(sqlList); } { String str = ""; RowsDetail = dbConnect.runSqlQuery( "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by rowno asc"); for (Row row : RowsDetail) { int rowno = row.getInteger("rowno");//订单行号 BigDecimal deliedqty = row.getBigDecimal("deliedqty");//订单已发货数量 BigDecimal erpdeliedqty = row.getBigDecimal("erpdeliedqty");//erp已发货数量 //System.out.println(deliedqty); //System.out.println(erpdeliedqty); if (deliedqty.compareTo(erpdeliedqty) != 0) { str = str + "行" + rowno + "商品发货数量" + deliedqty + "与erp发货数量" + erpdeliedqty + "不一致，请检查；"; } } if (!str.equalsIgnoreCase("")) { return str; } } return "true"; } @API(title = "关闭", apiversion = R.ID20221108164502.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String close() throws YosException { long sa_orderid = content.getLong("sa_orderid"); String str = closeordervalidate(sa_orderid); if (!str.equals("true")) { return getErrReturnObject().setErrMsg(str).toString(); } Rows RowsDetails = dbConnect.runSqlQuery( "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by rowno asc"); content.put("type", "数量"); content.put("remarks", "订单结案变更"); JSONArray itemifnos = new JSONArray(); for (Row row : RowsDetails) { if (row.getBigDecimal("qty").compareTo(row.getBigDecimal("erpdeliedqty")) > 0) { JSONObject jsonObject = new JSONObject(); jsonObject.put("itemid", row.getLong("itemid")); jsonObject.put("sa_orderitemsid", row.getLong("sa_orderitemsid")); jsonObject.put("newvalue", row.getString("erpdeliedqty")); itemifnos.add(jsonObject); } } content.put("itemifnos", itemifnos); content.put("isjiean", true); OrderItems orderItems = new OrderItems(content); orderItems.changeItem(); if (Parameter.get("system.erp_dockswitch").equalsIgnoreCase("true")) { Rows rows = dbConnect.runSqlQuery("select * from sa_orderitems_change where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by createdate desc"); //System.out.println(!rows.isEmpty()); if (!rows.isEmpty()) { UploadDataToERP uploadDataToERP = new UploadDataToERP(this, rows.get(0).getLong("sa_orderitems_changeid"), "订单变更"); boolean issuccess = uploadDataToERP.upload(); if (!issuccess) { dbConnect.runSqlUpdate("update sa_order set changeuserid='" + userid + "',changeby= '" + username + "',changedate = current_time,status= '审核',closeby='',closedate = NULL where siteid = '" + siteid + "' and sa_orderid =" + sa_orderid); return getErrReturnObject().setErrMsg("上传ERP失败，当前单据将自动反关闭，请检查！").toString(); } } } SQLFactory sqlFactory = new SQLFactory(this, "订单_关闭"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderid", sa_orderid); dbConnect.runSqlUpdate(sqlFactory); return getSucReturnObject().toString(); } @API(title = "查询发货单列表", apiversion = R.ID20221205111302.v1.class) @CACHEING public String dispatchList() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); 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.billno like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } } SQLFactory sqlFactory = new SQLFactory(this, "订单_发货单列表", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("sa_orderid", sa_orderid); Rows rows = dbConnect.runSqlQuery(sqlFactory); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询物流单列表", apiversion = R.ID20221205111402.v1.class) @CACHEING public String logisticsList() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); 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("t3.name like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t3.phonenumber like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } } SQLFactory sqlFactory = new SQLFactory(this, "订单_物流单列表", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter("sa_orderid", sa_orderid); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询订单进度", apiversion = R.ID20221229093602.v1.class) @CACHEING public String getOrderProcess() 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.type like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.action like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.actionby like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } } Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "查询订单进度列表", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter_SQL("where", where); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询发票列表", apiversion = R.ID20221229154102.v1.class) @CACHEING public String queryinvoiceapplyList() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); 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.invoiceno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.invoicecode like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.invoiceserialnum like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t1.sa_invoicebillid_blue like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and t1.status ='").append(whereObject.getString("status")).append("' "); } if (whereObject.containsKey("rb") && !"".equals(whereObject.getString("rb"))) { where.append(" and t1.rb ='").append(whereObject.getString("rb")).append("' "); } } SQLFactory sqlFactory = new SQLFactory(this, "查询发票列表查询", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter_SQL("where", where); Rows rows = dbConnect.runSqlQuery(sqlFactory); return getSucReturnObject().setData(rows).toString(); } @API(title = "一键复制订单", apiversion = R.ID20230102144502.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String copyOrder() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); ArrayList sqlList = new ArrayList<>(); Rows rows = dbConnect.runSqlQuery("SELECT * from sa_order WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'"); if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Row row = rows.get(0); Long sa_orderid_copy = createTableID("sa_order"); SQLFactory sqlFactory = new SQLFactory(this, "复制订单"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid_copy); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); //订单编号 sqlFactory.addParameter("sonum", createBillCode("orderbill")); //订单类型 sqlFactory.addParameter("type", row.getString("type")); //明细分类 sqlFactory.addParameter("typemx", row.getString("typemx")); //订货企业id sqlFactory.addParameter("sys_enterpriseid", row.getLong("sys_enterpriseid")); //营销账户类型ID sqlFactory.addParameter("sa_accountclassid", row.getLong("sa_accountclassid")); //品牌ID sqlFactory.addParameter("sa_brandid", row.getLong("sa_brandid")); //合同ID sqlFactory.addParameter("sa_contractid", row.getLong("sa_contractid")); //工程项目表ID sqlFactory.addParameter("sa_projectid", row.getLong("sa_projectid")); //合作企业财务信息ID（开票信息） sqlFactory.addParameter("sys_enterprise_financeid", row.getLong("sys_enterprise_financeid")); //物流公司档案ID sqlFactory.addParameter("sa_logiscompid", row.getLong("sa_logiscompid")); //合作企业联系人表ID（收货信息） sqlFactory.addParameter("rec_contactsid", row.getLong("rec_contactsid")); //备注说明 sqlFactory.addParameter("remarks", row.getString("remarks")); //销售人员hrid,业务员hrid sqlFactory.addParameter("saler_hrid", row.getLong("saler_hrid")); //制单方 1表示工厂方，0表示客户方 if (usertype == 21 || usertype == 22) { sqlFactory.addParameter("createflag", 0); } else { sqlFactory.addParameter("createflag", 1); } // 领域 sqlFactory.addParameter("tradefield", row.getString("tradefield")); //结算单位 sqlFactory.addParameter("pay_enterpriseid", row.getLong("pay_enterpriseid")); //单据日期 sqlFactory.addParameter("billdate", row.getString("billdate")); sqlFactory.addParameter("rebate_userate", row.getBigDecimal("rebate_userate")); if (row.getBoolean("rebate_used")) { sqlFactory.addParameter("rebate_used", 1); } else { sqlFactory.addParameter("rebate_used", 0); } sqlList.add(sqlFactory.getSQL()); //复制商品 Rows orderitemsRows = dbConnect.runSqlQuery("SELECT * from sa_orderitems WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'"); for (Row orderitemsRow : orderitemsRows) { sqlFactory = new SQLFactory(this, "复制订单商品"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderitemsid", createTableID("sa_orderitems")); sqlFactory.addParameter("sa_orderid", sa_orderid_copy); sqlFactory.addParameter("rowno", orderitemsRow.getLong("rowno")); sqlFactory.addParameter("itemid", orderitemsRow.getLong("itemid")); sqlFactory.addParameter("remarks", orderitemsRow.getString("remarks")); sqlFactory.addParameter("itemno", orderitemsRow.getString("itemno")); sqlFactory.addParameter("itemname", orderitemsRow.getString("itemname")); sqlFactory.addParameter("model", orderitemsRow.getString("model")); sqlFactory.addParameter("unit", orderitemsRow.getString("unit")); sqlFactory.addParameter("auxunit", orderitemsRow.getString("auxunit")); sqlFactory.addParameter("batchcontrol", orderitemsRow.getLong("batchcontrol")); sqlFactory.addParameter("delivery", orderitemsRow.getLong("delivery")); sqlFactory.addParameter("needdate", orderitemsRow.getString("needdate").equals("") ? "null" : orderitemsRow.getString("needdate")); sqlFactory.addParameter("deliverydate", orderitemsRow.getString("deliverydate").equals("") ? "null" : orderitemsRow.getString("deliverydate")); sqlFactory.addParameter("conversionrate", orderitemsRow.getBigDecimal("conversionrate")); //订购数量 sqlFactory.addParameter("qty", orderitemsRow.getBigDecimal("qty")); //辅助单位数量 sqlFactory.addParameter("auxqty", orderitemsRow.getBigDecimal("auxqty")); //单价,折后价（元） sqlFactory.addParameter("price", orderitemsRow.getBigDecimal("price")); //金额,折后金额（元） sqlFactory.addParameter("amount", orderitemsRow.getBigDecimal("amount")); //牌价、市场价（元） sqlFactory.addParameter("marketprice", orderitemsRow.getBigDecimal("marketprice")); //标准订单取框架内的价格 //折前价（元） sqlFactory.addParameter("defaultprice", orderitemsRow.getBigDecimal("defaultprice")); //折前金额（元） sqlFactory.addParameter("defaultamount", orderitemsRow.getBigDecimal("defaultamount")); sqlList.add(sqlFactory.getSQL()); } dbConnect.runSqlUpdate(sqlList); content.put("sa_orderid", sa_orderid_copy); return selectDetail(); } @API(title = "回款记录，查询的是核销单", apiversion = R.ID20230102164402.v1.class) @CACHEING public String query() 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 (t1.billno like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t2.enterprisename like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t2.abbreviation like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t5.agentnum like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t4.billno like '%").append(whereObject.getString("condition")).append("%')"); } if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) { where.append(" and t1.status = '").append(whereObject.getString("status")).append("'"); } } Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "回款记录", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter_SQL("where", where); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询企业的上下级，含支付账号信息", apiversion = R.ID20230104103702.v1.class) public String getString() 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 (t1.enterprisename like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t1.province like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t1.city like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t1.county like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t1.contact like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t1.phonenumber like '%").append(whereObject.getString("condition")).append("%')"); } } sys_enterpriseid = content.containsKey("sys_enterpriseid") ? content.getLongValue("sys_enterpriseid") : sys_enterpriseid; SQLFactory sqlFactory = new SQLFactory(this, "查询经销商上下级", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid); sqlFactory.addParameter_SQL("where", where); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); ArrayList sys_enterpriseids = rows.toArrayList("sys_enterpriseid", new ArrayList<>()); //查询开票信息 sqlFactory = new SQLFactory(this, "查询经销商默认开票信息"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseids); RowsMap financeRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sys_enterpriseid"); for (Row row : rows) { row.put("finance", financeRowsMap.getOrDefault(row.getString("sys_enterpriseid"), new Rows())); row.put("accounts", new Rows()); } return getSucReturnObject().setData(rows).toString(); } @API(title = "一键填写交货日期", apiversion = R.ID20230104143102.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String updateDelivery() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); String deliverydate = content.getString("deliverydate"); dbConnect.runSqlUpdate("UPDATE sa_orderitems SET deliverydate='" + deliverydate + "' WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'"); return getSucReturnObject().toString(); } @API(title = "一键填写需求日期", apiversion = R.ID20230104143802.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String updateNeeddate() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); String needdate = content.getString("needdate"); dbConnect.runSqlUpdate("UPDATE sa_orderitems SET needdate='" + needdate + "' WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'"); return getSucReturnObject().toString(); } @API(title = "查询居间费表头", apiversion = R.ID20230105194402.v1.class) @CACHEING public String getRebateInfo() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "居间费结算-详情", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); Row row = rows.isNotEmpty() ? rows.get(0) : new Row(); return getSucReturnObject().setData(row).toString(); } @API(title = "查询居间费列表", apiversion = R.ID20230105194902.v1.class) @CACHEING public String getRebateList() 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 (t1.billno like '%").append(whereObject.getString("condition")).append("%'"); where.append(" and t2.enterprisename like '%").append(whereObject.getString("condition")).append("%'"); where.append(" or t1.remarks like '%").append(whereObject.getString("condition")).append("%')"); } } Long sa_rebatesettlementid = content.getLong("sa_rebatesettlementid"); SQLFactory sqlFactory = new SQLFactory(this, "居间费结算明细-列表", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_rebatesettlementid", sa_rebatesettlementid); sqlFactory.addParameter_SQL("where", where); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); return getSucReturnObject().setData(rows).toString(); } @API(title = "修改业务员", apiversion = R.ID20230213154802.v1.class) @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class}) public String changeSaler() throws YosException { Long saler_hrid = content.getLong("saler_hrid"); Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "订单-修改业务员"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("saler_hrid", saler_hrid); sqlFactory.addParameter("sa_orderid", sa_orderid); dbConnect.runSqlUpdate(sqlFactory.getSQL()); return selectDetail(); } @API(title = "查询订单流水信息", apiversion = R.ID20230221193203.v1.class) @CACHEING public String getOrderCashbill() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows rows = dbConnect.runSqlQuery("SELECT * from sa_cashbill WHERE ownertable = 'sa_order' and ownerid = " + sa_orderid + " and siteid = '" + siteid + "' ORDER BY createdate DESC"); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询核销记录", apiversion = R.ID20230225141804.v1.class) @CACHEING public String getWriteoffbill() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "订单核销记录", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询erp对接记录", apiversion = R.ID20230225141904.v1.class) @CACHEING public String getErpupdatelog() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "订单erp对接记录", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); return getSucReturnObject().setData(rows).toString(); } @API(title = "查询变更记录", apiversion = R.ID20230225142004.v1.class) @CACHEING public String getChange() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); SQLFactory sqlFactory = new SQLFactory(this, "订单变更记录", pageSize, pageNumber, pageSorting); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_orderid", sa_orderid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); return getSucReturnObject().setData(rows).toString(); } /** * 获取订单提交SQL * * @param sa_orderid * @return * @throws YosException */ public String getOrderSubmitSql(Long sa_orderid, String status, String freightstatus) throws YosException { SQLFactory sqlFactory = new SQLFactory(this, "订单_提交"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("sa_orderid", sa_orderid); sqlFactory.addParameter("status", status); sqlFactory.addParameter("freightstatus", freightstatus); return sqlFactory.getSQL(); } /** * 获取订单审核SQL * * @param sa_orderid * @return * @throws YosException */ public String getOrderCheckSql(Long sa_orderid, String reviewtype) throws YosException { SQLFactory sqlFactory = new SQLFactory(this, "订单_审核"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("reviewtype", reviewtype); sqlFactory.addParameter("sa_orderid", sa_orderid); return sqlFactory.getSQL(); } /** * 创建凭证实体 * * @param amount * @param ownerid * @param source * @return * @throws YosException */ public CashbillEntity getCashbillEntity(BigDecimal amount, Long ownerid, String source, String remarks) throws YosException { CashbillEntity entity = new CashbillEntity(); entity.setAmount(amount); entity.setOwnerid(ownerid); entity.setOwnertable("sa_order"); entity.setSource(source); entity.setRemarks(remarks); return entity; } /** * 更新返利代金券余额 * * @param sa_cashbillid * @param rebate_balance * @return * @throws YosException */ public String updateCashbillSql(Long sa_cashbillid, BigDecimal rebate_balance) throws YosException { SQLFactory sqlFactory = new SQLFactory(this, "更新返利代金券"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("userid", userid); sqlFactory.addParameter("username", username); sqlFactory.addParameter("rebate_balance", rebate_balance); sqlFactory.addParameter("sa_cashbillid", sa_cashbillid); return sqlFactory.getSQL(); } /** * 获取支付凭证信息 * * @param sa_orderid * @return * @throws YosException */ public Rows getCashbillRows(Long sa_orderid) throws YosException { return dbConnect.runSqlQuery("SELECT sum(amount) amount from sa_cashbill WHERE ownerid ='" + sa_orderid + "' and ownertable='sa_order' and type = 0 and sa_cashbillid_rebate is null and siteid = '" + siteid + "'"); } public Rows getCashbillRebateRows(Long sa_orderid) throws YosException { return dbConnect.runSqlQuery("SELECT sum(amount) amount,sa_cashbillid_rebate from sa_cashbill WHERE ownerid ='" + sa_orderid + "' and ownertable='sa_order' and type = 0 and sa_cashbillid_rebate is not null and siteid = '" + siteid + "' GROUP BY sa_cashbillid_rebate"); } public Long getSaleHrid(Long sys_enterpriseid) throws YosException { Rows rows = dbConnect.runSqlQuery("SELECT hrid from sys_enterprise_tradefield WHERE sys_enterpriseid = " + sys_enterpriseid + " and hrid is not null and siteid='" + siteid + "'"); if (rows.isNotEmpty()) { hrid = rows.get(0).getLong("hrid"); } if (rows.isNotEmpty()) { hrid = rows.get(0).getLong("hrid"); } return hrid; } @API(title = "订单ERP仓库选择", apiversion = R.ID20230228193201.v1.class) public String selectErpStock() throws YosException { long itemid = content.getLong("itemid"); double qty = content.getDoubleValue("qty"); Rows rows = dbConnect.runSqlQuery("select erpitemno from plm_itemextend where siteid='" + siteid + "' and itemid=" + itemid); if (rows.isEmpty()) { return getSucReturnObject().toString(); } String erpitemno = rows.get(0).getString("erpitemno"); String oraclebase = ""; String sid = ""; if ("NB".equals(siteid)) { sid = "IBPNB10"; oraclebase = "ORACLE_NB"; } else if ("TZ".equals(siteid)) { sid = "IDCZJ10"; oraclebase = "ORACLE_TZ"; } else if ("HY".equals(siteid)) { sid = "IBPJX10"; oraclebase = "ORACLE_HY"; } DBConnect oracleConnect = new DBConnect(oraclebase); Rows icrows = oracleConnect.runSqlQuery( "select WAREHOUSE,QTY,LOCATION,LOT from v_eso_actual_inventory where FITEMNO = '" + erpitemno + "' and SITEID='" + sid + "' and QTY>=" + qty); return getSucReturnObject().setData(icrows).toString(); } @API(title = "订单手动关闭", apiversion = R.ID20230406155202.v1.class) public String closeOrder() throws YosException { Long sa_orderid = content.getLong("sa_orderid"); Rows orderRow = getOrderRows(this, sa_orderid); if (orderRow.isEmpty()) { return getErrReturnObject().setErrMsg("订单不存在").toString(); } Long sys_enterpriseid = orderRow.get(0).getLong("sys_enterpriseid"); Long sa_accountclassid = orderRow.get(0).getLong("sa_accountclassid"); if (dbConnect.runSqlQuery("SELECT 1 from sa_dispatch t1 left join sa_dispatch_items t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid left join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid WHERE t1.status!='关闭' and t1.siteid='" + siteid + "' and t3.sa_orderid=" + sa_orderid).isNotEmpty()) { return getErrReturnObject().setErrMsg("存在未关闭的发货单，无法关闭").toString(); } ArrayList sqlList = new ArrayList<>(); sqlList.add("UPDATE sa_order SET STATUS='关闭' WHERE sa_orderid='" + sa_orderid + "' and siteid = '" + siteid + "'"); sqlList.add("UPDATE sa_orderitems SET isclose=1 WHERE sa_orderid='" + sa_orderid + "' and siteid = '" + siteid + "'"); Rows rows = dbConnect.runSqlQuery("SELECT sum(undeliqty*price) totalprice from sa_orderitems WHERE sa_orderid="+sa_orderid+" and siteid = '"+siteid+"'"); if (rows.isNotEmpty()) { BigDecimal totalprice = rows.get(0).getBigDecimal("totalprice"); if (totalprice.compareTo(BigDecimal.ZERO) > 0) { sqlList.add("UPDATE sa_accountbalance set balance=" + totalprice + " WHERE sys_enterpriseid=" + sys_enterpriseid + " and sa_accountclassid=" + sa_accountclassid + " and siteid='" + siteid + "'"); } } sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单手动关闭", "订单手动关闭").getSQL()); dbConnect.runSqlUpdate(sqlList); return getSucReturnObject().toString(); } // @API(title = "发货单手动关闭", apiversion = R.ID20230406155302.v1.class) // @Deprecated // public String closeDispatch() throws YosException { // // Long sa_dispatchid = content.getLongValue("sa_dispatchid"); // // ArrayList sqlList = new ArrayList<>(); // sqlList.add("UPDATE sa_dispatch SET STATUS='关闭' WHERE sa_dispatchid='" + sa_dispatchid + "' and siteid = '" + siteid + "'"); // Rows rows = dbConnect.runSqlQuery("SELECT qty,sa_orderitemsid FROM sa_dispatch_items WHERE sa_dispatchid='" + sa_dispatchid + "' and siteid = '" + siteid + "'"); // for (Row row : rows) { // Long sa_orderitemsid = row.getLong("sa_orderitemsid"); // Long qty = row.getLong("qty"); // sqlList.add("UPDATE sa_orderitems SET undeliqty=qty-" + qty + " WHERE sa_orderitemsid='" + sa_orderitemsid + "' and siteid = '" + siteid + "'"); // } // sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "发货单手动关闭", "发货单手动关闭").getSQL()); // // dbConnect.runSqlUpdate(sqlList); // // // return getSucReturnObject().toString(); // } }