lsa_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.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import restcontroller.R;
import restcontroller.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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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, intervaltime = 200)
    @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);

        ArrayList<Long> itemids = new ArrayList<>();
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            itemids.add(item.getLong("itemid"));
        }

        String sql = "select t1.*,t2.material from plm_item t1 left join plm_itemextend t2 ON t2.itemid = t1.itemid AND t2.siteid = t1.siteid where t1.siteid='" + siteid + "' and t1.itemid in  " + itemids;
        sql = sql.replace("[", "(").replace("]", ")");
        Rows itemRows = dbConnect.runSqlQuery(sql);

        RowsMap itemRowsMap = itemRows.toRowsMap("itemid");
        BigDecimal sumqty = BigDecimal.ZERO;
        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");
            sumqty = sumqty.add(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");
                        BigDecimal orderaddqty = promotionitemsRowsMap.get(item.getString("itemid")).get(0).getBigDecimal("orderaddqty");
                        BigDecimal orderminqty = promotionitemsRowsMap.get(item.getString("itemid")).get(0).getBigDecimal("orderminqty");
                        boolean islimit = promotionitemsRowsMap.get(item.getString("itemid")).get(0).getBoolean("islimit");
                        if (((qty.subtract(orderminqty)).remainder(orderaddqty)).compareTo(BigDecimal.ZERO) != 0) {
                            return getErrReturnObject().setErrMsg("品号为" + itemRowsMap.get(item.getString("itemid")).get(0).getString("itemno") + "的商品数量不符合该商品的起订量和增量").toString();
                        }
                        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, "订单商品明细_新增");
            JSONArray customproperties = new JSONArray();
            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);

                Rows customschemeRows = dbConnect.runSqlQuery("select * from sa_customscheme_items where siteid='" + siteid + "'");
                RowsMap customschemeRowsMap = customschemeRows.toRowsMap("sa_customschemeid");

                if (itemRowsMap.containsKey(item.getString("itemid"))) {
                    if (itemRowsMap.get(item.getString("itemid")).isNotEmpty()) {
                        if (itemRowsMap.get(item.getString("itemid")).get(0).getLong("sa_customschemeid") > 0 && !itemRowsMap.get(item.getString("itemid")).get(0).getBoolean("iscustomsize")) {
                            System.out.println(0000);
                            if (customschemeRowsMap.containsKey(itemRowsMap.get(item.getString("itemid")).get(0).getString("sa_customschemeid"))) {
                                System.out.println(111111);
                                for (Row customschemeItem : (customschemeRowsMap.get(itemRowsMap.get(item.getString("itemid")).get(0).getString("sa_customschemeid")))) {
                                    System.out.println(customschemeItem.getString("description"));
                                    System.out.println(2222);
                                    customproperties.add(customschemeItem.getString("description") + ":" + itemRowsMap.get(item.getString("itemid")).get(0).getString(customschemeItem.getString("value")));
                                }
                            }
                        } else if (itemRowsMap.get(item.getString("itemid")).get(0).getBoolean("iscustomsize")) {
                            System.out.println(3333);
                            if (customschemeRowsMap.containsKey(itemRowsMap.get(item.getString("itemid")).get(0).getString("sa_customschemeid"))) {
                                System.out.println(4444);
                                for (Row customschemeItem : (customschemeRowsMap.get(itemRowsMap.get(item.getString("itemid")).get(0).getString("sa_customschemeid")))) {
                                    System.out.println(5555);
                                    System.out.println((customschemeRowsMap.get(itemRowsMap.get(item.getString("itemid")).get(0).getString("sa_customschemeid"))).size());
                                    if (!customschemeItem.getString("value").equals("spec")) {
                                        customproperties.add(customschemeItem.getString("description") + ":" + itemRowsMap.get(item.getString("itemid")).get(0).getString(customschemeItem.getString("value")));
                                    } else {
                                        customproperties.add("尺寸:" + item.getString("length") + "*" + item.getString("width"));
                                    }

                                }
                            } else {
                                customproperties.add("尺寸:" + item.getString("length") + "*" + item.getString("width"));
                            }
                        }

                    }

                }

                if (rows.isEmpty()) {
                    sa_orderitemsid = createTableID("sa_orderitems");
                } else {
                    System.out.println(beans.order.Order.getDefaultIsRepeatValue(siteid, type));
                    if (beans.order.Order.getDefaultIsRepeatValue(siteid, type)) {
                        sa_orderitemsid = createTableID("sa_orderitems");
                        sqlFactory = new SQLFactory(this, "订单商品明细_新增");
                    } 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("customproperties", customproperties.toJSONString().replace("[", "").replace("]", "").replace("\"", ""));
            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();
                        if (itemRowsMap.containsKey(item.getString("itemid"))) {
                            if (itemRowsMap.get(item.getString("itemid")).isNotEmpty()) {
                                if (itemRowsMap.get(item.getString("itemid")).get(0).getBoolean("iscustomsize")) {
                                    System.out.println(price);
                                    price = price.multiply(item.getBigDecimal("length").multiply(item.getBigDecimal("width")).divide(new BigDecimal("1000000"), 4, BigDecimal.ROUND_HALF_UP));
                                    System.out.println(price);
                                }

                            }

                        }
                }
            } 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.containsKey("defaultprice")) {
                    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, 6, BigDecimal.ROUND_HALF_UP));
                        //折前金额（元）
                        sqlFactory.addParameter("amount", item.getBigDecimalValue("amount"));
                    } else {
                        sqlFactory.addParameter("price", price);
                        //折前金额（元）
                        sqlFactory.addParameter("amount", price.multiply(qty));
                    }
                } else {
                    sqlFactory.addParameter("price", price);
                    //折前金额（元）
                    sqlFactory.addParameter("amount", price.multiply(qty));
                }
            } 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());
        }

        if (type.equals("促销订单")) {
            Rows promotionRows = dbConnect.runSqlQuery("select * from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
            if (!promotionRows.isEmpty()) {
                if (promotionRows.get(0).getString("type").equals("打包促销")) {
                    BigDecimal orderaddqty = promotionRows.get(0).getBigDecimal("orderaddqty");
                    BigDecimal orderminqty = promotionRows.get(0).getBigDecimal("orderminqty");
                    if (((sumqty.subtract(orderminqty)).remainder(orderaddqty)).compareTo(BigDecimal.ZERO) != 0) {
                        return getErrReturnObject().setErrMsg("不符合促销活动订购数量规则，修改不成功").toString();
                    }
                }
            }
        }
        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);
        }

        //更新订单扣款信息
        updateAccountclassinfos(sa_orderid);


        return getSucReturnObject().toString();
    }


    /**
     * 更新订单扣款信息
     */
    public void updateAccountclassinfos(Long sa_orderid) throws YosException {

        Rows orderRows = getOrderRows(this, sa_orderid);


        if (orderRows.isEmpty()) {
            return;
        }
        JSONArray jsonArray = new JSONArray();
        BigDecimal orderSumamount = BigDecimal.ZERO;
        Rows classamountrows = dbConnect.runSqlQuery("select t2.marketingcategory topitemclassid,t1.siteid,sum(ifnull(t1.amount,0)) sumamount  from sa_orderitems t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid   where t1.sa_orderid='" + sa_orderid + "' and t1.siteid='"+siteid+"' group by t2.marketingcategory,t1.siteid");
        for (Row classamountrow : classamountrows) {
            orderSumamount = orderSumamount.add(classamountrow.getBigDecimal("sumamount"));
        }
        if (orderSumamount.compareTo(getAmount(this, sa_orderid)) != 0) {
            return;
        }

        RowsMap classamountrowsMap = classamountrows.toRowsMap("topitemclassid");
        Rows accountclassrows = dbConnect.runSqlQuery("select t1.*,ifnull(t2.balance,0) balance,ifnull(t2.creditquota,0) creditquota from sa_accountclass t1 left join sa_accountbalance t2 on t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid and t2.sys_enterpriseid=" + orderRows.get(0).getLong("pay_enterpriseid") + " where isused=1 and t1.siteid='" + siteid + "'");
        BigDecimal firstpayamount = BigDecimal.ZERO;


        if (!accountclassrows.isEmpty()) {
            for (Row row : accountclassrows) {
                BigDecimal sumamount = BigDecimal.ZERO;
                JSONObject jsonObject = new JSONObject();
                if (row.getJSONArray("bindingcategories") != null && !row.getJSONArray("bindingcategories").isEmpty()) {
                    for (Object object : row.getJSONArray("bindingcategories")) {
                        Long topitemclassid = Long.valueOf(object.toString());
                        if (classamountrowsMap.containsKey(object.toString())) {
                            if (classamountrowsMap.get(object.toString()).isNotEmpty()) {
                                sumamount = sumamount.add(classamountrowsMap.get(object.toString()).get(0).getBigDecimal("sumamount").multiply(row.getBigDecimal("maximumdiscount")).setScale(2, BigDecimal.ROUND_HALF_UP));
                            }
                        }
                    }
                }
                if (sumamount.compareTo(BigDecimal.ZERO) > 0) {
                    if ((row.getBigDecimal("balance").add(row.getBigDecimal("creditquota"))).compareTo(BigDecimal.ZERO) <= 0) {
                        continue;
                    } else if ((row.getBigDecimal("balance").add(row.getBigDecimal("creditquota"))).compareTo(sumamount) < 0) {
                        //部分优先账户扣款，部分现金扣款
                        jsonObject.put("amount", row.getBigDecimal("balance").add(row.getBigDecimal("creditquota")));
                        jsonObject.put("sa_accountclassid", row.getLong("sa_accountclassid"));
                        jsonObject.put("accountname", row.getString("accountname"));
                        jsonObject.put("balance", row.getBigDecimal("balance"));
                        jsonObject.put("creditquota", row.getBigDecimal("creditquota"));
                        jsonArray.add(jsonObject);
                        firstpayamount = firstpayamount.add(row.getBigDecimal("balance").add(row.getBigDecimal("creditquota")));

                    } else if ((row.getBigDecimal("balance").add(row.getBigDecimal("creditquota"))).compareTo(sumamount) >= 0) {
                        //全部优先账户扣款
                        jsonObject.put("amount", sumamount);
                        jsonObject.put("sa_accountclassid", row.getLong("sa_accountclassid"));
                        jsonObject.put("accountname", row.getString("accountname"));
                        jsonObject.put("balance", row.getBigDecimal("balance"));
                        jsonObject.put("creditquota", row.getBigDecimal("creditquota"));
                        jsonArray.add(jsonObject);
                        firstpayamount = firstpayamount.add(sumamount);
                    }
                }
            }
        }

        if (getAmount(this, sa_orderid).compareTo(BigDecimal.ZERO) == 0) {
            Rows xianjinaccountclassrows = dbConnect.runSqlQuery("select t1.*,ifnull(t2.balance,0) balance,ifnull(t2.creditquota,0) creditquota from sa_accountclass t1 left join sa_accountbalance t2 on t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid and t2.sys_enterpriseid=" + orderRows.get(0).getLong("pay_enterpriseid") + " where isorder=1 and isused=1 and t1.sa_accountclassid=" + orderRows.get(0).getLong("sa_accountclassid") + " and t1.siteid='" + siteid + "'");
            if (xianjinaccountclassrows.isNotEmpty()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("amount", BigDecimal.ZERO);
                jsonObject.put("sa_accountclassid", xianjinaccountclassrows.get(0).getLong("sa_accountclassid"));
                jsonObject.put("accountname", xianjinaccountclassrows.get(0).getString("accountname"));
                jsonObject.put("balance", xianjinaccountclassrows.get(0).getBigDecimal("balance"));
                jsonObject.put("creditquota", xianjinaccountclassrows.get(0).getBigDecimal("creditquota"));
                jsonArray.add(jsonObject);
            }
        }

        if (orderSumamount.compareTo(firstpayamount) > 0) {

            Rows xianjinaccountclassrows = dbConnect.runSqlQuery("select t1.*,ifnull(t2.balance,0) balance,ifnull(t2.creditquota,0) creditquota from sa_accountclass t1 left join sa_accountbalance t2 on t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid and t2.sys_enterpriseid=" + orderRows.get(0).getLong("pay_enterpriseid") + " where isorder=1 and isused=1 and t1.sa_accountclassid=" + orderRows.get(0).getLong("sa_accountclassid") + " and t1.siteid='" + siteid + "'");
            if (xianjinaccountclassrows.isNotEmpty()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("amount", orderSumamount.subtract(firstpayamount));
                jsonObject.put("sa_accountclassid", xianjinaccountclassrows.get(0).getLong("sa_accountclassid"));
                jsonObject.put("accountname", xianjinaccountclassrows.get(0).getString("accountname"));
                jsonObject.put("balance", xianjinaccountclassrows.get(0).getBigDecimal("balance"));
                jsonObject.put("creditquota", xianjinaccountclassrows.get(0).getBigDecimal("creditquota"));
                jsonArray.add(jsonObject);
            }
        }

        dbConnect.runSqlUpdate("update sa_order set sa_accountclassinfos='" + jsonArray + "' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
    }

    /**
     * 更新居间费信息
     */
    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.ID20230508093003.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class})
    public String freeze() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        JSONArray sa_orderitemsids = content.getJSONArray("sa_orderitemsids");
        boolean isfreeze = content.getBooleanValue("isfreeze");
        Rows orderRows = dbConnect.runSqlQuery("select * from sa_order where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        if (!orderRows.isEmpty()) {

            if (!orderRows.get(0).getString("status").equals("审核")) {
                if (isfreeze) {
                    return getErrReturnObject().setErrMsg("非审核状态无法进行冻结").toString();
                } else {
                    return getErrReturnObject().setErrMsg("非审核状态无法进行反冻结").toString();
                }

            }
        } else {
            return getErrReturnObject().setErrMsg("该订单不存在").toString();
        }
        SQLFactory sqlFactory;
        if (isfreeze) {
            sqlFactory = new SQLFactory(this, "订单商品明细_冻结");
        } else {
            sqlFactory = new SQLFactory(this, "订单商品明细_反冻结");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids.toArray());
        String sql = sqlFactory.getSQL();
        System.out.println(sql);
        dbConnect.runSqlUpdate(sql);
        return getSucReturnObject().toString();
    }

    @API(title = "商品行折扣变更", apiversion = R.ID20230508101703.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class})
    public String updateOrderItemsDiscountrate() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        BigDecimal discountrate = content.getBigDecimal("discountrate");
        JSONArray itemids = content.getJSONArray("itemids");
        Rows orderRows = dbConnect.runSqlQuery("select * from sa_order where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        if (!orderRows.isEmpty()) {
            if (!orderRows.get(0).getString("status").equals("新建") && !orderRows.get(0).getString("status").equals("预提交")) {
                return getErrReturnObject().setErrMsg("非新建或预提交状态无法进行商品行折扣变更").toString();
            }
        } else {
            return getErrReturnObject().setErrMsg("该订单不存在").toString();
        }
        String sql;
        if (itemids.size() == 0) {
            sql = "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'";
        } else {
            sql = "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' and itemid in " + itemids.toJSONString();
            sql = sql.replace("[", "(").replace("]", ")");
        }
        Rows orderItemsRows = dbConnect.runSqlQuery(sql);
        ArrayList<String> sqlList = new ArrayList<>();
        if (!orderItemsRows.isEmpty()) {
            for (Row row : orderItemsRows) {
                BigDecimal price = row.getBigDecimal("price").multiply(discountrate);
                BigDecimal amount = row.getBigDecimal("amount").multiply(discountrate);
                sqlList.add("update sa_orderitems set price=" + price + ",amount=" + amount + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "商品行价格刷新（变为原价）", apiversion = R.ID20230518151803.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class})
    public String updateOrderItemsPrice() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        JSONArray itemids = content.getJSONArray("itemids");
        Rows orderRows = dbConnect.runSqlQuery("select * from sa_order where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        if (!orderRows.isEmpty()) {
            if (!orderRows.get(0).getString("status").equals("新建") && !orderRows.get(0).getString("status").equals("预提交")) {
                return getErrReturnObject().setErrMsg("非新建或预提交状态无法进行商品行价格刷新").toString();
            }
        } else {
            return getErrReturnObject().setErrMsg("该订单不存在").toString();
        }
        String sql;
        if (itemids.size() == 0) {
            sql = "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'";
        } else {
            sql = "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' and itemid in " + itemids.toJSONString();
            sql = sql.replace("[", "(").replace("]", ")");
        }

        Rows orderItemsRows = dbConnect.runSqlQuery(sql);

        ArrayList<String> sqlList = new ArrayList<>();
        if (!orderItemsRows.isEmpty()) {
            for (Row row : orderItemsRows) {
                //价格
                ItemPrice itemPrice = ItemPrice.getItemPrice(this, orderRows.get(0).getLong("sys_enterpriseid"), row.getLong("itemid"));
                BigDecimal price = itemPrice.getPrice();
                BigDecimal amount = row.getBigDecimal("qty").multiply(price);
                sqlList.add("update sa_orderitems set price=" + price + ",amount=" + amount + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        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<>());
        ArrayList<Long> itemnos = rows.toArrayList("itemno", new ArrayList<>());
        RowsMap rowsMap = rows.toRowsMap("itemno");

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

        SQLFactory sqlFactory2 = new SQLFactory(this, "商品未发货数量汇总");
        sqlFactory2.addParameter("siteid", siteid);
        sqlFactory2.addParameter_in("itemnos", itemnos);
        Rows sumUnQtyRows = dbConnect.runSqlQuery(sqlFactory2);
        RowsMap sumUnQtyRowsMap = sumUnQtyRows.toRowsMap("itemno");

        ERPDocking erpDocking = new ERPDocking();
        JSONArray jsonArray = new JSONArray();
        if (rows.toJsonArray("itemno").size() != 0) {
            if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") && siteid.equalsIgnoreCase("ccyosg")) {
                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 (sumUnQtyRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("undeliqtysum", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("undeliqty"));
            } else {
                row.put("undeliqtysum", 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();
    }


    @API(title = "查询订单所有明细列表", apiversion = R.ID20230508111703.v1.class)
    public String selectAllList() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (sys_enterpriseid > 0) {
            where.append(" and t5.sys_enterpriseid ='").append(sys_enterpriseid).append("' ");
        }
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append(" t5.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t7.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("iteminfo") && !"".equals(whereObject.getString("iteminfo"))) {
                where.append(" and(");
                where.append("t1.itemno like'%").append(whereObject.getString("iteminfo")).append("%' ");
                where.append("or t1.itemname like'%").append(whereObject.getString("iteminfo")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) {
                where.append(" and(");
                where.append("t6.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append("or t7.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t5.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t5.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t5.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("isfreeze") && !"".equals(whereObject.getString("isfreeze"))) {
                where.append(" and t1.isfreeze ='").append(whereObject.getString("isfreeze")).append("' ");
            }
            if (whereObject.containsKey("typemx") && !"".equals(whereObject.getString("typemx"))) {
                where.append(" and t5.typemx ='").append(whereObject.getString("typemx")).append("' ");
            }

            if (whereObject.containsKey("isuncheckout") && !"".equals(whereObject.getString("isuncheckout"))) {
                String begindate = "";
                String enddate = "";
                if (whereObject.containsKey("datetype") && !"".equals(whereObject.getString("datetype"))) {
                    String datetype = whereObject.getStringValue("datetype");
                    switch (datetype) {
                        case "7":
                            begindate = getDate_Str(-7);
                            enddate = getDate_Str();
                            break;
                        case "30":
                            begindate = getDate_Str(-30);
                            enddate = getDate_Str();
                            break;
                        case "90":
                            begindate = getDate_Str(-90);
                            enddate = getDate_Str();
                            break;
                        default:
                            break;
                    }
                }
                if (begindate != "") {
                    where.append(" and DATE_FORMAT(t5.submitdate, '%Y-%m-%d') >='").append(begindate).append("' ");
                }
                if (enddate != "") {
                    where.append(" and DATE_FORMAT(t5.submitdate, '%Y-%m-%d') <='").append(enddate).append("' ");
                }

                String isuncheckout = whereObject.getStringValue("isuncheckout");
                if (isuncheckout.equals("1")) {
                    where.append(" and t1.sa_orderitemsid in (select t1.sa_orderitemsid from sa_orderitems t1 inner join (select t2.sa_orderitemsid,t2.siteid,sum(outwarehouseqty) sumoutwarehouseqty from sa_dispatch_items t2 inner join sa_dispatch t3 on t2.sa_dispatchid=t3.sa_dispatchid and t2.siteid=t3.siteid GROUP BY sa_orderitemsid,siteid) t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid where t1.qty>t2.sumoutwarehouseqty or t1.undeliqty>0)");
                } else {
                    where.append(" and 1=1");
                }
            }

        }

        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_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);
        sqlFactory1.addParameter_in("sa_orderitemsids", rows.toArrayList("sa_orderitemsid",new ArrayList<>()));
        Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
        RowsMap sumQtyRowsMap = sumQtyRows.toRowsMap("sa_orderitemsid");

        ERPDocking erpDocking = new ERPDocking();
        JSONArray jsonArray = new JSONArray();
        if (rows.toJsonArray("itemno").size() != 0) {
            if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") && siteid.equalsIgnoreCase("ccyosg")) {
                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("sa_orderitemsid")).isNotEmpty()) {
                row.put("unsoldqty", row.getBigDecimal("qty").subtract(sumQtyRowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("sumqty")));
            } else {
                row.put("unsoldqty",  row.getBigDecimal("qty"));
            }
            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) {
            //去除不需要导出项
            rows.getFieldList().remove("sa_orderitemsid");
            rows.getFieldList().remove("itemid");
            rows.getFieldList().remove("batchcontrol");
            rows.getFieldList().remove("model");
            rows.getFieldList().remove("conversionrate");
            rows.getFieldList().remove("marketprice");
            rows.getFieldList().remove("defaultprice");
            rows.getFieldList().remove("defaultamount");
            rows.getFieldList().remove("auxqty");
            rows.getFieldList().remove("needdate");
            rows.getFieldList().remove("deliedqty");
            rows.getFieldList().remove("rebateamount");
            rows.getFieldList().remove("invoiceamount");
            rows.getFieldList().remove("writeoffamount");
            rows.getFieldList().remove("auxunit");
            rows.getFieldList().remove("sa_orderid");
            rows.getFieldList().remove("deliverydate");
            rows.getFieldList().remove("returnqty");
            rows.getFieldList().remove("stockno");
            rows.getFieldList().remove("position");
            rows.getFieldList().remove("batchno");
            rows.getFieldList().remove("isfreeze");
            rows.getFieldList().remove("candispatchqty");
            rows.getFieldList().remove("cansaleqty");
            rows.getFieldList().remove("totalaty");
            rows.getFieldList().remove("orderminqty_auxunit");
            rows.getFieldList().remove("orderminqty");
            rows.getFieldList().remove("orderaddqty_auxunit");
            rows.getFieldList().remove("orderaddqty");
            rows.getFieldList().remove("spec");
            rows.getFieldList().remove("packageqty");
            rows.getFieldList().remove("delivery");
            rows.getFieldList().remove("erpitemno");
            rows.getFieldList().remove("erpitemname");
            rows.getFieldList().remove("prodline");
            rows.getFieldList().remove("material");
            rows.getFieldList().remove("device");
            rows.getFieldList().remove("specalnote");
            rows.getFieldList().remove("standards");
            rows.getFieldList().remove("agentnum");
            rows.getFieldList().remove("enterprisename");
            rows.getFieldList().remove("agentnum");
            rows.getFieldList().remove("stockstatus");
            rows.getFieldList().remove("submitdate");
            rows.getFieldList().remove("checkdate");
            Rows rowsrolename = dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid=" + userid);
            if (!rowsrolename.isEmpty()) {
                rows.getFieldList().remove("price");
                rows.getFieldList().remove("amount");
            }

            Rows uploadRows = uploadExcelToObs("orderdetail", "订单明细列表", rows, getTitleMapAgent());
            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", "备注");
        titleMap.put("submitdate", "提交时间");
        titleMap.put("checkdate", "审核时间");

        return titleMap;
    }

    //返回导出的标题
    public HashMap<String, String> getTitleMapAgent() throws YosException {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("rowno", "行号");
        titleMap.put("sonum", "订单号");
        titleMap.put("status", "订单状态");
        titleMap.put("type", "订单类型");
        titleMap.put("billdate", "单据日期");
        titleMap.put("tradefield", "领域");
        titleMap.put("itemno", "产品编码");
        titleMap.put("itemname", "品名");
        titleMap.put("unit", "单位");
        titleMap.put("qty", "数量");
        titleMap.put("undeliqty", "未发货数量");
        Rows rowsrolename = dbConnect.runSqlQuery("select * from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t2.rolename='经销商员工' and t1.userid=" + userid);
        if (rowsrolename.isEmpty()) {
            titleMap.put("price", "单价");
            titleMap.put("amount", "金额");
        }
        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(")");
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where.append(" and t1.isonsale ='").append(whereObject.getString("isonsale")).append("' ");
            }

        }
        Long sa_orderid = content.getLong("sa_orderid");
        Long sys_enterpriseid1 = sys_enterpriseid;
        Rows orderRows = getOrderRows(this, sa_orderid);
        Long sa_brandid = 0L;
        String tradefield = "";
        String type = "";
        String typemx = "";
        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");
            typemx = orderRows.get(0).getString("typemx");
            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 "特殊订单":
                if (typemx.equals("电商") || typemx.equals("工程")) {
                    sqlFactory = new SQLFactory(this, "查询可添加商品列表_总部订单", pageSize, pageNumber, pageSorting);
                } else {
                    sqlFactory = new SQLFactory(this, "查询可添加商品列表_特殊订单", pageSize, pageNumber, pageSorting);
                }
                sqlFactory.addParameter("tradefield", tradefield);
                if (sys_enterpriseid1 > 0) {
                    where.append(" and t1.isonsale ='1' ");
                }
                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);
        Rows sizecustomizedschemes = dbConnect.runSqlQuery("select * from sa_sizecustomizedscheme where siteid='" + siteid + "' ");
        RowsMap sizecustomizedschemesRowsMap = sizecustomizedschemes.toRowsMap("sa_sizecustomizedschemeid");
        Rows sizeschemedetails = dbConnect.runSqlQuery("select * from sa_sizeschemedetail where siteid='" + siteid + "' ");
        RowsMap sizeschemedetailsRowsMap = sizeschemedetails.toRowsMap("sa_sizecustomizedschemeid");
        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);
            }
            if (row.getLong("widthschemeid") != 0) {
                if (sizecustomizedschemesRowsMap.containsKey(row.getString("widthschemeid"))) {
                    if (sizecustomizedschemesRowsMap.get(row.getString("widthschemeid")).isNotEmpty()) {
                        row.put("widthtype", sizecustomizedschemesRowsMap.get(row.getString("widthschemeid")).get(0).getString("type"));
                        row.put("widthmin", sizecustomizedschemesRowsMap.get(row.getString("widthschemeid")).get(0).getBigDecimal("min"));
                        row.put("widthmax", sizecustomizedschemesRowsMap.get(row.getString("widthschemeid")).get(0).getBigDecimal("max"));
                        row.put("widthdecimalplaces", sizecustomizedschemesRowsMap.get(row.getString("widthschemeid")).get(0).getBigDecimal("decimalplaces"));
                        if (sizeschemedetailsRowsMap.containsKey(row.getString("widthschemeid"))) {
                            row.put("widthschemedetails", sizeschemedetailsRowsMap.get(row.getString("widthschemeid")));
                        } else {
                            row.put("widthschemedetails", new JSONArray());
                        }

                    }

                }
            }
            if (row.getLong("lengthschemeid") != 0) {
                if (sizecustomizedschemesRowsMap.containsKey(row.getString("lengthschemeid"))) {
                    if (sizecustomizedschemesRowsMap.get(row.getString("lengthschemeid")).isNotEmpty()) {
                        row.put("lengthtype", sizecustomizedschemesRowsMap.get(row.getString("lengthschemeid")).get(0).getString("type"));
                        row.put("lengthmin", sizecustomizedschemesRowsMap.get(row.getString("lengthschemeid")).get(0).getBigDecimal("min"));
                        row.put("lengthmax", sizecustomizedschemesRowsMap.get(row.getString("lengthschemeid")).get(0).getBigDecimal("max"));
                        row.put("lengthdecimalplaces", sizecustomizedschemesRowsMap.get(row.getString("lengthschemeid")).get(0).getBigDecimal("decimalplaces"));
                        if (sizeschemedetailsRowsMap.containsKey(row.getString("lengthschemeid"))) {
                            row.put("lengthschemedetails", sizeschemedetailsRowsMap.get(row.getString("lengthschemeid")));
                        } else {
                            row.put("lengthschemedetails", new JSONArray());
                        }
                    }

                }
            }

        }
        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);
        }

    }


}