cucu_YOS/src/custom/restcontroller/webmanage/sale/order/OrderItems.java

package restcontroller.webmanage.sale.order;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.brand.Brand;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datateam.DataTeam;
import beans.enterprise.Enterprise;
import beans.itemprice.ItemPrice;
import beans.parameter.Parameter;
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 restcontroller.R;
import restcontroller.webmanage.sale.aftersalesmag.twriteoffbill_orderchange;
import restcontroller.webmanage.sale.rebate.Rebate;
import restcontroller.webmanage.sale.toolbill.ToolBilItem;
import restcontroller.webmanage.sale.toolbill.ToolBill;
import utility.ERPDocking;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;

import static beans.order.Order.*;

/**
 * 销售订单商品明细表
 */
public class OrderItems extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public OrderItems(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新增或更新", apiversion = R.ID20221109093602.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class})
    public String insertOrUpdate() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
//        Long sa_contractid = content.getLongValue("sa_contractid");
        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("数据不存在，无法添加").toString();
        }

//        sa_contractid = orderRows.get(0).getLong("sa_contractid");

        String type = orderRows.get(0).getString("type");
        JSONArray items = content.getJSONArray("items");
        Long sa_promotionid=orderRows.get(0).getLong("sa_promotionid");


        ArrayList<String> sqlList = new ArrayList<>();
        //查询erp库存
//        Invbal.selectErpInvbal(this, items);

        Rows itemRows =dbConnect.runSqlQuery("select itemid,orderaddqty,orderminqty,itemno from plm_item where siteid='"+siteid+"'");
        RowsMap itemRowsMap= itemRows.toRowsMap("itemid");
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            Long itemid = item.getLong("itemid");
            Long sa_orderitemsid = item.getLong("sa_orderitemsid");
            BigDecimal qty = item.getBigDecimalValue("qty");
            if(!type.equals("促销订单")){
                if(itemRowsMap.containsKey(item.getString("itemid"))){
                    if(itemRowsMap.get(item.getString("itemid")).isNotEmpty()){
                        BigDecimal orderaddqty =itemRowsMap.get(item.getString("itemid")).get(0).getBigDecimal("orderaddqty");
                        BigDecimal orderminqty =itemRowsMap.get(item.getString("itemid")).get(0).getBigDecimal("orderminqty");
                        if(((qty.subtract(orderminqty)).remainder(orderaddqty)).compareTo(BigDecimal.ZERO)!=0){
                            return getErrReturnObject().setErrMsg("品号为"+itemRowsMap.get(item.getString("itemid")).get(0).getString("itemno")+"的商品数量不符合该商品的起订量和增量").toString();
                        }

                    }
                }
            }
            //商品信息
            Row itemRow = getItemRow(itemid);
            Rows promotionitems =dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='"+siteid+"' and sa_promotionid="+sa_promotionid);
            RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("itemid");
            if(type.equals("促销订单")){
                if(promotionitemsRowsMap.containsKey(item.getString("itemid"))){
                    if(promotionitemsRowsMap.get(item.getString("itemid")).isNotEmpty()){
                        BigDecimal saledqty =promotionitemsRowsMap.get(item.getString("itemid")).get(0).getBigDecimal("saledqty");
                        BigDecimal groupqty =promotionitemsRowsMap.get(item.getString("itemid")).get(0).getBigDecimal("groupqty");
                        boolean islimit =promotionitemsRowsMap.get(item.getString("itemid")).get(0).getBoolean("islimit");
                        if(islimit){
                            if(groupqty.compareTo(saledqty.add(qty))<0){
                                return getErrReturnObject().setErrMsg(itemRow.getString("itemno")+"商品数量已超过促销活动的限定数量【"+groupqty.subtract(saledqty)+"】，无法新建").toString();
                            }
                        }
            }
                }
            }

            BigDecimal price1=new BigDecimal(0);
            SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_新增");
            if (sa_orderitemsid <= 0) {
                Rows rows = dbConnect.runSqlQuery("SELECT sa_orderitemsid,price from sa_orderitems WHERE siteid='" + siteid + "' and itemid=" + itemid + " and sa_orderid=" + sa_orderid);
                if (rows.isEmpty()) {
                    sa_orderitemsid = createTableID("sa_orderitems");
                } else {
                    price1=rows.get(0).getBigDecimal("price");
                    sa_orderitemsid = rows.get(0).getLong("sa_orderitemsid");
                    sqlFactory = new SQLFactory(this, "订单商品明细_更新");
                }
            } else {
                sqlFactory = new SQLFactory(this, "订单商品明细_更新");
            }
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("sa_orderitemsid", sa_orderitemsid);
            sqlFactory.addParameter("sa_orderid", sa_orderid);
            sqlFactory.addParameter("rowno", 1);
            sqlFactory.addParameter("itemid", itemid);
            sqlFactory.addParameter("remarks", item.getStringValue("remarks"));

            BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");
            if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
                conversionrate = BigDecimal.valueOf(1);
            }
            sqlFactory.addParameter("itemno", itemRow.getString("itemno"));
            sqlFactory.addParameter("itemname", itemRow.getString("itemname"));
            sqlFactory.addParameter("model", itemRow.getString("model"));
            sqlFactory.addParameter("unit", itemRow.getString("unit"));
            sqlFactory.addParameter("auxunit", itemRow.getString("auxunit"));
            sqlFactory.addParameter("batchcontrol", itemRow.getLong("batchcontrol"));
            sqlFactory.addParameter("delivery", itemRow.getLong("delivery"));

            sqlFactory.addParameter("stockno", item.getOrDefault("stockno", "null"));
            sqlFactory.addParameter("position", item.getOrDefault("position", "null"));
            sqlFactory.addParameter("batchno", item.getOrDefault("batchno", "null"));

            String deliverydate = item.getStringValue("deliverydate");
            Rows rows = dbConnect.runSqlQuery("SELECT deliverydate,defaultprice from sa_orderitems WHERE sa_orderitemsid =" + sa_orderitemsid);
            if (deliverydate.isEmpty()) {
                if (rows.isNotEmpty()) {
                    deliverydate = rows.get(0).getString("deliverydate");
                } else {
                    deliverydate = "null";
                }
            }
            String needdate = item.getStringValue("needdate");
            sqlFactory.addParameter("needdate", needdate.equals("") ? "null" : needdate);
            sqlFactory.addParameter("deliverydate", deliverydate.equals("") ? "null" : deliverydate);
            sqlFactory.addParameter("conversionrate", conversionrate);
            //订购数量
            sqlFactory.addParameter("qty", qty);
            //辅助单位数量
            sqlFactory.addParameter("auxqty", qty.divide(conversionrate));
            BigDecimal defaultprice;
            BigDecimal price;
            //价格
            ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, itemid);
            if (!item.containsKey("defaultprice")) {
                switch (type) {
//                    case "项目订单":
//                        defaultprice = itemPrice.getContractprice(sa_contractid);
//                        break;
//                    case "工具订单":
//                        defaultprice = itemPrice.getMarketprice();
//                        break;
                    case "促销订单":
                        defaultprice = itemPrice.getGraderateprice();
                        price= itemPrice.getPromotionPrice(sa_promotionid);
                        break;
                    default:
                        defaultprice = itemPrice.getGraderateprice();
                        price= itemPrice.getGraderateprice();
                }
            } else {
                price = item.getBigDecimalValue("price");
                if(price.compareTo(price1)!=0){
                    sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单商品【"+itemRow.getString("itemname")+"】由单价:"+price1+"修改为单价:"+price, "订单修改单价").getSQL());
                }
                defaultprice = item.getBigDecimalValue("defaultprice");
//                if (!type.equals("特殊订单") && defaultprice.compareTo(rows.get(0).getBigDecimal("defaultprice")) != 0 && defaultprice.compareTo(itemPrice.getContractprice()) < 0) {
//                    return getErrReturnObject().setErrMsg("调整价格不可小于协议价").toString();
//                }
            }
            if(type.equals("特殊订单")){
                if(item.getBigDecimalValue("price").compareTo(BigDecimal.ZERO)>0 && item.getBigDecimalValue("amount").compareTo(BigDecimal.ZERO)<=0){
                    sqlFactory.addParameter("price", item.getBigDecimalValue("price"));
                    //折前金额（元）
                    sqlFactory.addParameter("amount", price.multiply(qty));
                }else if(item.getBigDecimalValue("price").compareTo(BigDecimal.ZERO)<=0 && item.getBigDecimalValue("amount").compareTo(BigDecimal.ZERO)>0){
                    sqlFactory.addParameter("price",item.getBigDecimalValue("amount").divide(qty,4));
                    //折前金额（元）
                    sqlFactory.addParameter("amount", item.getBigDecimalValue("amount"));
                }
            }else {
                sqlFactory.addParameter("price", price);
                //折前金额（元）
                sqlFactory.addParameter("amount", price.multiply(qty));
            }
            //单价,折后价（元）,取合同价
            sqlFactory.addParameter("defaultprice", defaultprice);
            //金额,折后金额（元）
            sqlFactory.addParameter("defaultamount", defaultprice.multiply(qty));
            //牌价、市场价（元）,标准订单牌价取商品价格，项目订单取合同里的牌价
            sqlFactory.addParameter("marketprice", itemPrice.getMarketprice());


            sqlList.add(sqlFactory.getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        //重新排序
        updateRowNo(sa_orderid);
        //居间费设置
        updateRebateFee(sa_orderid);

        //调整价格（返利金）
        if (orderRows.get(0).getBoolean("rebate_used")) {
            setRebateAmount(this, sa_orderid, getMaxUsedRebateAmount(this, sa_orderid));
            adjustLastRebateAmount(this, sa_orderid);
        }
//        else {
//            dbConnect.runSqlUpdate("UPDATE sa_orderitems SET amount=amount-rebateamount,price=amount/qty WHERE sa_orderid='" + sa_orderid + "' and siteid='" + siteid + "' and qty!=0");
//            dbConnect.runSqlUpdate("UPDATE sa_orderitems SET amount=0 WHERE sa_orderid='" + sa_orderid + "' and siteid='" + siteid + "' and qty=0");
//        }
        return getSucReturnObject().toString();
    }

    /**
     * 更新居间费信息
     */
    public void updateRebateFee(Long sa_orderid) throws YosException {

        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isEmpty()) {
            return;
        }
        Long sa_projectid = orderRows.get(0).getLong("sa_projectid");

        Rows contractRows = dbConnect.runSqlQuery("SELECT * from sa_contract WHERE sa_projectid = " + sa_projectid + " and type='居间' and status='审核'");
        if (contractRows.isEmpty()) {
            return;
        }
        Long sa_contractid = contractRows.get(0).getLong("sa_contractid");

        Rows partiesRows = dbConnect.runSqlQuery("SELECT sys_enterpriseid from sa_project_parties WHERE type ='居间服务商' and sa_projectid = " + sa_projectid + " and siteid ='" + siteid + "'");
        if (partiesRows.isEmpty()) {
            return;
        }
        Long sys_enterpriseid_temp = partiesRows.get(0).getLong("sys_enterpriseid");
        content.put("sys_enterpriseid", sys_enterpriseid_temp);
        content.put("ownertable", "sa_order");
        content.put("ownerid", sa_orderid);
        content.put("billdate", "");
        content.put("remarks", "");
        content.put("sa_contractid", sa_contractid);
        content.put("settlementmode", "线上");
        content.put("sa_rebatesettlementid", 0);

        new Rebate(content).insertOrUpdate();
    }


    @API(title = "详情", apiversion = R.ID20221110134302.v1.class)
    @CACHEING
    public String selectDetail() throws YosException {
        Long sa_orderitemsid = content.getLong("sa_orderitemsid");
        SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_详情");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsid);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();

        return getSucReturnObject().setData(row).toString();

    }

    @API(title = "删除", apiversion = R.ID20221109093702.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class})
    public String delete() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        JSONArray sa_orderitemsids = content.getJSONArray("sa_orderitemsids");
        SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_删除");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids.toArray());
        String sql = sqlFactory.getSQL();
        dbConnect.runSqlUpdate(sql);
        //重新排序
        updateRowNo(sa_orderid);
        //居间费设置
        updateRebateFee(sa_orderid);
        //调整价格（返利金）
        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.get(0).getBoolean("rebate_used")) {
            setRebateAmount(this, sa_orderid, getMaxUsedRebateAmount(this, sa_orderid));
            adjustLastRebateAmount(this, sa_orderid);
        }
//        else {
//            dbConnect.runSqlUpdate("UPDATE sa_orderitems SET amount=amount-rebateamount,price=amount/qty WHERE sa_orderid='" + sa_orderid + "' and siteid='" + siteid + "' and qty!=0");
//            dbConnect.runSqlUpdate("UPDATE sa_orderitems SET amount=0 WHERE sa_orderid='" + sa_orderid + "' and siteid='" + siteid + "' and qty=0");
//        }
        return getSucReturnObject().toString();
    }

    @API(title = "关闭", apiversion = R.ID20221109093802.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");
        JSONArray sa_orderitemsids = content.getJSONArray("sa_orderitemsids");
        SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_行关闭");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("closereason", content.getStringValue("closereason"));
        sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids.toArray());
        dbConnect.runSqlUpdate(sqlFactory);
        //重新排序
        updateRowNo(sa_orderid);
        return getSucReturnObject().toString();
    }

    @API(title = "查询列表", apiversion = R.ID20221109093902.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.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sa_orderid = content.getLong("sa_orderid");
        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isNotEmpty()) {
            sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
        }
        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("sa_orderid", sa_orderid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap rowsMap =rows.toRowsMap("itemno");

        SQLFactory sqlFactory1 =new SQLFactory(this,"商品发货数量汇总");
        sqlFactory1.addParameter("siteid", siteid);
        Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumQtyRowsMap= sumQtyRows.toRowsMap("itemno");

        ERPDocking erpDocking =new ERPDocking();
        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"));
            }
        }

        if(!jsonArray.isEmpty()){
            for (Object object:jsonArray) {
                JSONObject jsonObject =(JSONObject)object;
                if(rowsMap.containsKey(jsonObject.getString("fitemno"))){
                    if(rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()){
                        rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("fqty"));
                    }
                }
            }
        }
        //查询附件
        RowsMap attinfoRowsMap = getAttachmentUrl("plm_item", ids);
        for (Row row : rows) {
            if(sumQtyRowsMap.get(row.getString("itemno")).isNotEmpty()){
                row.put("unsoldqty", sumQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty"));
            }else {
                row.put("unsoldqty", BigDecimal.ZERO);
            }
            if(!row.containsKey("invbalqty")){
                row.put("invbalqty",BigDecimal.ZERO);
            }
            row.put("attinfos", attinfoRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("contractprice", 0);
        }

        if (isExport) {
            //去除不需要导出项
            String[] removeFieldList = {"sa_orderitemsid", "itemid", "sa_orderid", "candispatchqty", "cansaleqty",
                    "totalaty", "orderminqty_auxunit", "orderminqty", "orderaddqty_auxunit", "orderaddqty",
                    "spec", "delivery", "erpitemno", "erpitemname", "material", "standards", "stockstatus"};
            for (String key : removeFieldList) {
                rows.getFieldList().remove(key);
            }

            Rows uploadRows = uploadExcelToObs("order", "订单商品明细_列表", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }


        return getSucReturnObject().setData(rows).toString();
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("rowno", "行号");
        titleMap.put("batchcontrol", "是否批次管理");
        titleMap.put("itemno", "产品编号");
        titleMap.put("itemname", "产品名称");
        titleMap.put("model", "型号");
        titleMap.put("conversionrate", "换算率（辅助数量=主单位数量/换算率）");
        titleMap.put("price", "单价,折后价（元）");
        titleMap.put("amount", "金额,折后金额（元）");
        titleMap.put("marketprice", "牌价、市场价（元）");
        titleMap.put("defaultprice", "折前价（元）");
        titleMap.put("defaultamount", "折前金额（元）");
        titleMap.put("qty", "订购数量");
        titleMap.put("auxqty", "辅助单位数量");
        titleMap.put("needdate", "需求日期");
        titleMap.put("deliedqty", "已交货量（主单位）");
        titleMap.put("rebateamount", "返利分摊金额");
        titleMap.put("undeliqty", "未交货量（主单位）");
        titleMap.put("invoiceamount", "已开票金额");
        titleMap.put("writeoffamount", "已核销金额");
        titleMap.put("unit", "计量单位名称");
        titleMap.put("auxunit", "辅助计量单位名称");
        titleMap.put("deliverydate", "交货日期");
        titleMap.put("returnqty", "退货数量");
        titleMap.put("remarks", "备注");


        return titleMap;
    }

    @API(title = "查询可添加商品列表", apiversion = R.ID20221109153502.v1.class)
    @CACHEING
    public String selectItemList() 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.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.material like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sa_orderid = content.getLong("sa_orderid");

        Rows orderRows = getOrderRows(this, sa_orderid);
        Long sa_brandid = 0L;
        String tradefield = "";
        String type = "";
        Long sys_enterpriseid = 0L;
        if (orderRows.isNotEmpty()) {
            sa_brandid = orderRows.get(0).getLong("sa_brandid");
            tradefield = orderRows.get(0).getString("tradefield");
            type = orderRows.get(0).getString("type");
            sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
        }
        SQLFactory sqlFactory = new SQLFactory(this, "查询可添加商品列表", pageSize, pageNumber, pageSorting);
        switch (type) {
//            case "工具订单":
//                sqlFactory = new SQLFactory(this, "查询可添加商品列表_工具订单", pageSize, pageNumber, pageSorting);
//                break;
            case "特殊订单":
                sqlFactory = new SQLFactory(this, "查询可添加商品列表_特殊订单", pageSize, pageNumber, pageSorting);
                sqlFactory.addParameter("tradefield", tradefield);
                break;
            default:
                StringBuffer where2 = new StringBuffer(" 1=1 ");
                Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid);
                boolean saleclassauth = enterpriseRow.getBoolean("saleclassauth");
                //需要经营授权
                if (saleclassauth) {
                    where2.append(" and t1.itemclassid in (SELECT itemclassid from sys_enterprise_saleclass WHERE sys_enterpriseid = " + sys_enterpriseid + "  and siteid = '" + siteid + "')");
                }
                sqlFactory.addParameter("sa_brandid", sa_brandid);
                sqlFactory.addParameter("tradefield", tradefield);
                sqlFactory.addParameter_SQL("where2", where2);
                break;
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL(false);
        Rows rows = dbConnect.runSqlQuery(sql);
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        //查询附件
        RowsMap attinfoRowsMap = getAttachmentUrl("plm_item", ids);
        //商品领域
        RowsMap tradefieldRowsMap = beans.Item.Item.getTradefieldRowsMap(this, ids);
        //品牌
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, ids);
        //价格
        HashMap<Long, ItemPrice> itemPriceRowsMap = ItemPrice.getItemPrice(this, sys_enterpriseid, ids);
        for (Row row : rows) {

            row.put("attinfos", attinfoRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("tradefield", tradefieldRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            if( itemPriceRowsMap.containsKey(row.getLong("itemid"))){
                row.put("price", itemPriceRowsMap.get(row.getLong("itemid")).getPrice());
            }else {
                row.put("price", 0);
            }

        }
        return getSucReturnObject().setData(rows).toString();
    }

    //获取非工具数据
    public String getOrderItemsList(Long sa_orderid, String tradefield, Long sys_enterpriseid, Long sa_brandid) throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid);
        boolean saleclassauth = enterpriseRow.getBoolean("saleclassauth");
        //需要经营授权
        if (saleclassauth) {
            where.append(" and itemclassid in (SELECT itemclassid from sys_enterprise_saleclass WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid = '" + siteid + "')");
        }

        SQLFactory sqlFactory = new SQLFactory(this, "商品范围");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter_in("tradefield", tradefield);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter("sa_brandid", sa_brandid);
        sqlFactory.addParameter_SQL("where", where);
        return sqlFactory.getSQL(false);
    }

//    //获取特殊数据
//    public String getOrderItemsListSpec(Long sa_orderid, String tradefield, Long sys_enterpriseid) throws YosException {
//        SQLFactory sqlFactory = new SQLFactory(this, "商品范围_特殊");
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        sqlFactory.addParameter_in("tradefield", tradefield);
//        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        return sqlFactory.getSQL(false);
//    }


    @API(title = "订单商品变更", apiversion = R.ID20221110145302.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String changeItem() throws YosException {
        boolean isjiean = content.getBooleanValue("isjiean");
        Long sa_orderid = content.getLong("sa_orderid");
        String type = content.getString("type");
        Long sa_orderitems_changeid = createTableID("sa_orderitems_change");
        JSONArray itemifnos = content.getJSONArray("itemifnos");
        BigDecimal oldValue = new BigDecimal(0);
        Rows Rowstatus = dbConnect.runSqlQuery(
                "select * from sa_order where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        if (Rowstatus.isEmpty()) {
            return "订单不存在";
        } else {
            if (!Rowstatus.get(0).getString("status").equals("审核")) {
                return getErrReturnObject().setErrMsg("非审核状态的订单无法进行商品变更").toString();
            }
        }
        ArrayList<String> sqlList = new ArrayList<>();
        // 订单商品变更
        SQLFactory sqlFactory = new SQLFactory(this, "订单商品变更_新增");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_orderitems_changeid", sa_orderitems_changeid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("remarks", content.getStringValue("remarks"));
        sqlList.add(sqlFactory.getSQL());

        HashMap<Long, Row> orderdetailmap = new HashMap<>();
        HashMap<Long, BigDecimal> orderdetailchangeamount = new HashMap<>();
        Rows rowsOrder = dbConnect.runSqlQuery("select * from sa_order t1 where t1.sa_orderid=" + sa_orderid);

        BigDecimal ce = new BigDecimal(0);

        for (Object obj : itemifnos) {
            JSONObject jsonObject = (JSONObject) obj;
            Rows rows = dbConnect.runSqlQuery(
                    "SELECT sa_orderitemsid,qty,deliedqty,undeliqty,writeoffamount,price from sa_orderitems WHERE sa_orderitemsid="
                            + jsonObject.getLongValue("sa_orderitemsid"));
            if (rows.isNotEmpty()) {
                oldValue = rows.get(0).getBigDecimal("qty");
            } else {
                return getErrReturnObject().setErrMsg("订单行不存在").toString();
            }
            if (type.equals("数量")) {
                // 更新数量
                BigDecimal newqty = jsonObject.getBigDecimal("newvalue");
                BigDecimal oldqty = rows.get(0).getBigDecimal("qty");
                BigDecimal oldundeliqty = rows.get(0).getBigDecimal("undeliqty");
                BigDecimal addqty = newqty.subtract(oldqty);// 新增的数量
                if (newqty.compareTo(oldqty) >= 0) {
                    return getErrReturnObject().setErrMsg("修改数量必须小于原订单数量" + oldqty).toString();
                }
                if (newqty.compareTo(oldqty.subtract(oldundeliqty)) < 0) {
                    return getErrReturnObject().setErrMsg("修改数量必须大于等于已发货数量" + oldqty.subtract(oldundeliqty)).toString();
                }
                if (newqty.compareTo(BigDecimal.ZERO) < 0) {
                    return getErrReturnObject().setErrMsg("修改数量不可小于0").toString();
                }

                sqlFactory = new SQLFactory(this, "订单商品明细变更_更新");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_orderitemsid", jsonObject.getLongValue("sa_orderitemsid"));
                sqlFactory.addParameter("qty", jsonObject.getStringValue("newvalue"));
//                sqlFactory.addParameter("undeliqty",  oldundeliqty.add(addqty));
                ce = ce.add(addqty.multiply(rows.get(0).getBigDecimal("price")));
                sqlList.add(sqlFactory.getSQL());
                /**
                 * * 核销对冲计算
                 */
                {
                    /**
                     * * 核销对冲计算
                     */
                    BigDecimal writeoffamount = rows.get(0).getBigDecimal("writeoffamount");// 订单行已核销金额
                    //System.out.println(newqty.multiply(rows.get(0).getBigDecimal("price")));
                    //System.out.println(writeoffamount);
                    if (writeoffamount.compareTo(BigDecimal.ZERO) > 0
                            && (newqty.multiply(rows.get(0).getBigDecimal("price"))).compareTo(writeoffamount) < 0) {
                        // 如果已核销金额大于0 且调整后折后金额小于已核销金额，则需要将差价进行回冲
                        orderdetailmap.put(rows.get(0).getLong("sa_orderitemsid"), rows.get(0));
                        //System.out.println(writeoffamount.subtract(newqty.multiply(rows.get(0).getBigDecimal("price"))));
                        //System.out.println("sa_orderitemsid:"+rows.get(0).getLong("sa_orderitemsid"));
                        orderdetailchangeamount.put(rows.get(0).getLong("sa_orderitemsid"),
                                writeoffamount.subtract(newqty.multiply(rows.get(0).getBigDecimal("price"))));
                    }

                }
            }
            // 订单商品变更明细
            sqlFactory = new SQLFactory(this, "订单商品变更明细_新增");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_orderitems_changemxid", createTableID("sa_orderitems_changemx"));
            sqlFactory.addParameter("itemid", jsonObject.getLongValue("itemid"));
            sqlFactory.addParameter("sa_orderitemsid", jsonObject.getLongValue("sa_orderitemsid"));
            sqlFactory.addParameter("sa_orderitems_changeid", sa_orderitems_changeid);
            sqlFactory.addParameter("type", type);
            sqlFactory.addParameter("oldvalue", oldValue);
            sqlFactory.addParameter("newvalue", jsonObject.getStringValue("newvalue"));

            sqlList.add(sqlFactory.getSQL());

//            content.put("sa_orderitemsid", sa_orderitemsid);

        }

        if (ce.compareTo(BigDecimal.ZERO) != 0) {
            CashbillEntity cashbillEntity = new CashbillEntity();
            cashbillEntity.setAmount(ce);
            cashbillEntity.setOwnerid(sa_orderid);
            cashbillEntity.setOwnertable("sa_order");
            cashbillEntity
                    .setRemarks("订单变更:" + rowsOrder.get(0).getString("sonum") + "订单变更单id:" + sa_orderitems_changeid);
            sqlList.addAll(Accountbalance.createCashbillPay(this, rowsOrder.get(0).getLong("sys_enterpriseid"),
                    rowsOrder.get(0).getLong("sa_accountclassid"), cashbillEntity, true));
        }

        /**
         * * 核销对冲
         */
        if (!orderdetailmap.isEmpty()) {
            //System.out.println("测试");
            sqlList.addAll(
                    new twriteoffbill_orderchange(rowsOrder.get(0), orderdetailmap, orderdetailchangeamount).hedging());
        }
        for (String string : sqlList) {
            //System.out.println(string);
        }
        dbConnect.runSqlUpdate(sqlList);
        // 居间费设置
        updateRebateFee(sa_orderid);
        if (!isjiean) {
            if (Parameter.get("system.erp_dockswitch").equalsIgnoreCase("true")) {
                UploadDataToERP uploadDataToERP = new UploadDataToERP(this, sa_orderitems_changeid, "订单变更");
                boolean issuccess = uploadDataToERP.upload();
                if (!issuccess) {
                    return getErrReturnObject().setErrMsg("上传ERP失败，请检查！").toString();
                }
            }
        }


        return getSucReturnObject().toString();
    }


    @API(title = "订单商品变更（手动推erp）", apiversion = R.ID20230306161703.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String changeItem1() throws YosException {
        if (Parameter.get("system.erp_dockswitch").equalsIgnoreCase("true")) {
            UploadDataToERP uploadDataToERP = new UploadDataToERP(this, content.getLong("sa_orderitems_changeid"), "订单变更");
            boolean issuccess = uploadDataToERP.upload();
            if (!issuccess) {
                return getErrReturnObject().setErrMsg("上传ERP失败，请检查！").toString();
            } else {
                return getSucReturnObject().toString();
            }
        } else {
            return getErrReturnObject().setErrMsg("erp对接开关未开！").toString();
        }

    }


    /**
     * 获取当前订单的最大行号
     *
     * @param sa_orderid
     * @return
     * @throws YosException
     */
    public Long getRowNo(Long sa_orderid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT COUNT(0) count from sa_orderitems WHERE sa_orderid = " + sa_orderid + " AND siteid = '" + siteid + "'");
        return rows.get(0).getLong("count") + 1;
    }

    /**
     * 查询商品信息
     *
     * @param itemid
     * @return
     * @throws YosException
     */
    public Row getItemRow(Long itemid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "查询商品信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("itemid", itemid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false));
        return rows.isNotEmpty() ? rows.get(0) : new Row();

    }

    /**
     * 重新对商品行排序
     *
     * @param sa_orderid
     * @throws YosException
     */
    public void updateRowNo(Long sa_orderid) throws YosException {
        String sql = "SELECT sa_orderitemsid from sa_orderitems WHERE sa_orderid= " + sa_orderid + "  and siteid = '" + siteid + "' ORDER  BY createdate ";
        ArrayList<Long> sa_orderitemsids = dbConnect.runSqlQuery(sql).toArrayList("sa_orderitemsid", new ArrayList<>());
        if (sa_orderitemsids.size() > 0) {
            int rowno = 1;
            StringBuffer where = new StringBuffer("");
            for (Long id : sa_orderitemsids) {
                where.append(" WHEN " + id + " THEN " + rowno);
                rowno++;
            }


            SQLFactory sqlFactory = new SQLFactory(this, "更新行号");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids);
            sql = sqlFactory.getSQL(false);
            dbConnect.runSqlUpdate(sql);
        }

    }


}