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 utility.tools.WebRequest;

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");
        JSONArray items = content.getJSONArray("items");
        //辅助类
        OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);
        ArrayList<String> sqlList = new ArrayList<>();

        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("数据不存在，无法添加").toString();
        } else {
            if (!orderRows.get(0).getString("status").equals("新建") && !orderRows.get(0).getString("status").equals("预提交")) {
                return getErrReturnObject().setErrMsg("非新建或预提交状态无法操作").toString();
            }
        }

        //更新表头
        sqlList.add(orderItemsHelper.getUpdateSql().getSQL());

        String type = orderRows.get(0).getString("type");
        Long sa_promotionid = orderRows.get(0).getLong("sa_promotionid");
        //查询活动
        Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
        //批量查询商品信息
        RowsMap itemRowsMap = orderItemsHelper.getItemRowsMap(items);

        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);
            //商品信息
            Row itemRow = getItemRow(itemid);
            //是否符合商品起订量和增量判断
            orderItemsHelper.compareQty(type, itemRowsMap, item, itemRow, promotionitems);

            BigDecimal price1 = new BigDecimal(0);
//            SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_新增");
            Boolean isInsert = true;
            JSONArray customproperties = new JSONArray();
            if (sa_orderitemsid <= 0) {
                //获取定制属性
                customproperties = orderItemsHelper.getCustomProperties(itemRowsMap, item);

                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 {
                    System.out.println(beans.order.Order.getDefaultIsRepeatValue(siteid, type));
                    if (beans.order.Order.getDefaultIsRepeatValue(siteid, type)) {
                        sa_orderitemsid = createTableID("sa_orderitems");
                        isInsert = true;
                    } else {
                        price1 = rows.get(0).getBigDecimal("price");
                        sa_orderitemsid = rows.get(0).getLong("sa_orderitemsid");
                        isInsert = false;
                    }

                }
            } else {
                Rows rows = dbConnect.runSqlQuery("SELECT sa_orderitemsid,price from sa_orderitems WHERE siteid='" + siteid + "' and sa_orderitemsid=" + sa_orderitemsid);
                if(rows.isNotEmpty()){
                    price1 =rows.get(0).getBigDecimal("price");
                }
                isInsert = false;
            }

            BigDecimal defaultprice;
            BigDecimal price;
            //价格
            ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, itemid);
            if (!item.containsKey("defaultprice")) {
                defaultprice = orderItemsHelper.getDefaultprice(itemPrice);
                price = orderItemsHelper.getPrice(orderRows.get(0), itemPrice, item, itemRowsMap);
            } else {
                defaultprice = item.getBigDecimalValue("defaultprice");
                price = item.getBigDecimalValue("price");
                if (price.compareTo(price1) != 0) {
                    sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单商品【" + itemRow.getString("itemname") + "】由单价:" + price1 + "修改为单价:" + price, "订单修改单价").getSQL());
                }
            }

            if (isInsert) {
                sqlList.add(orderItemsHelper.getInsertSQL(customproperties, sa_orderitemsid, item, itemRow, type, defaultprice, price, itemPrice.getMarketprice()).getSQL());
            } else {
                sqlList.add(orderItemsHelper.getUpdateSQL(sa_orderitemsid, item, itemRow, type, defaultprice, price).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);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems");
        querySQL.addJoinTable(JOINTYPE.left, "st_invbal_sale", "t2", "t2.siteid = t1.siteid AND t2.itemid = t1.itemid");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t3", "t3.itemid = t1.itemid and t3.siteid = t1.siteid",
                "orderminqty_auxunit", "orderminqty", "orderaddqty_auxunit", "orderaddqty", "spec", "iscustomsize", "widthschemeid", "lengthschemeid", "standards", "k3outcode");
//        querySQL.addJoinTable(JOINTYPE.left, "plm_itemextend", "t4", "t4.itemid = t1.itemid and t4.siteid = t1.siteid",
//                "erpitemno", "erpitemname", "material", "prodline", "device", "specalnote");
        querySQL.addQueryFields("candispatchqty", "ifnull(t2.candispatchqty, 0)");
        querySQL.addQueryFields("cansaleqty", "ifnull(t2.cansaleqty, 0)");
        querySQL.addQueryFields("totalaty", "ifnull(t2.qty, 0)");
        querySQL.addQueryFields("stockstatus", "(SELECT (CASE WHEN t2.cansaleqty>=t3.stockstatus1 THEN '充足' WHEN t2.cansaleqty<=t3.stockstatus2 THEN '缺货' ELSE '紧缺' END))");
        querySQL.addQueryFields("packageqty", "ifnull(t3.packageqty, 0)");
        querySQL.addQueryFields("delivery", "ifnull(t3.delivery, 0)");
        querySQL.setWhere(where.toString()).setTableAlias("t1");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.sa_orderid", sa_orderid);
        if (!isExport) {
            querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        }


        Rows rows = querySQL.query();

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

        JSONArray jsonArray = new JSONArray();

        JSONObject object = new JSONObject();
        object.put("classname", "getIcinvbal");
        object.put("method", "getMsg");

        JSONObject content = new JSONObject();
        content.put("fitemnos", rows.toJsonArray("itemno"));
        object.put("content", content);

        WebRequest request = new WebRequest();
        String result = request.doPost(object.toString(),
                "http://60.190.151.198:8092/BYESB/jaxrs/webclientrest");
        jsonArray = JSONArray.parseArray(result);

        for (Object obj : jsonArray) {
            JSONObject jsonObject = (JSONObject) obj;
            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);
            //处理日期字段空的情况
            row.putIfAbsent("needdate", "");
            row.putIfAbsent("deliverydate", "");
        }

//        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("begindate1") && !"".equals(whereObject.getString("begindate1"))) {
                where.append(" and DATE_FORMAT(t5.submitdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate1")).append("' ");
            }
            if (whereObject.containsKey("enddate1") && !"".equals(whereObject.getString("enddate1"))) {
                where.append(" and DATE_FORMAT(t5.submitdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate1")).append("' ");
            }

            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and DATE_FORMAT(t5.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate2")).append("' ");
            }

            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and DATE_FORMAT(t5.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate2")).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.equals("")) {
                    where.append(" and DATE_FORMAT(t5.submitdate, '%Y-%m-%d') >='").append(begindate).append("' ");
                }
                if (!enddate.equals("")) {
                    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);
        QuerySQL querySQL = queryAllListManage(where.toString());
        querySQL.setOrderBy(pageSorting);
        if (!isExport) {
            querySQL.setPage(pageSize, pageNumber);
        }
        Rows rows = querySQL.query();
        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 QuerySQL queryAllListManage(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems",
                "sa_orderitemsid", "itemid", "rowno", "itemno", "itemname", "unit", "qty", "undeliqty", "price", "amount", "remarks", "customproperties", "batchcontrol", "model",
                "conversionrate", "marketprice", "defaultprice", "defaultamount", "auxqty", "needdate", "deliedqty", "rebateamount", "invoiceamount", "writeoffamount", "auxunit", "sa_orderid",
                "deliverydate", "returnqty", "stockno", "position", "batchno", "isfreeze");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t3", "t3.itemid = t1.itemid and t3.siteid = t1.siteid",
                "orderminqty_auxunit", "orderminqty", "orderaddqty_auxunit", "orderaddqty", "spec", "standards", "packageqty", "delivery", "k3outcode");
        querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t5", "t5.sa_orderid = t1.sa_orderid and t5.siteid = t1.siteid",
                "sonum", "type", "typemx", "status", "billdate", "submitdate", "checkdate", "tradefield");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t6.sys_enterpriseid = t5.sys_enterpriseid and t6.siteid = t5.siteid",
                "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t7", "t7.sys_enterpriseid = t5.sys_enterpriseid and t7.siteid = t5.siteid",
                "enterprisename");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t5.status in ('审核', '关闭','手工关闭')");
        return querySQL;
    }


    //返回导出的标题
    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);
        }

    }


}