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

package restcontroller.webmanage.sale.order;

import beans.Item.Item;
import beans.attachment.Attachment;
import beans.brand.Brand;
import beans.datacontrllog.DataContrlLog;
import beans.enterprise.Enterprise;
import beans.itemprice.ItemPrice;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;
import restcontroller.webmanage.sale.accessoryorder.accessoryorder;
import restcontroller.webmanage.sale.rebate.Rebate;
import restcontroller.webmanage.sale.toolbill.ToolBill;
import utility.ERPDocking;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.Year;
import java.time.YearMonth;
import java.time.temporal.IsoFields;
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, accessoryorder.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("提交") && !orderRows.get(0).getString("status").equals("预提交")) {
                return getErrReturnObject().setErrMsg("该状态无法操作").toString();
            }
        }

        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        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.getBigDecimal("qty");
            sumqty = sumqty.add(qty);


            //商品信息
            Row itemRow = getItemRow(itemid);
            long width = itemRow.getLong("width");
            long length = itemRow.getLong("length");
            if (item.containsKey("width")) {
                width = Long.parseLong(item.getString("width"));
            }
            if (item.containsKey("length")) {
                length = Long.parseLong(item.getString("length"));
            }


            if (sa_promotionid > 0 && dbConnect.runSqlQuery("SELECT 1 from sa_promotion_items WHERE  islimit=1 and groupqty <= saledqty  and  sa_promotion_itemsid=" + item.getLong("sa_promotion_itemsid") + " and siteid='" + siteid + "' and sa_promotionid='" + sa_promotionid + "'").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("商品【" + itemRow.getString("itemno") + "】已售罄，不能添加。").toString();
            }

            BigDecimal price1 = new BigDecimal(0);
//            SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_新增");
            Boolean isInsert = true;
            JSONArray customproperties = new JSONArray();
            String spec = "";
            if (sa_orderitemsid <= 0) {
                //获取定制属性
                customproperties = orderItemsHelper.getCustomProperties(itemRowsMap, item);
                if (itemRow.getString("iscustomsize").equals("1") && customproperties.isEmpty()) {
                    return getErrReturnObject().setErrMsg(itemRow.getString("itemno") + "的定制信息为空，无法添加").toString();
                }

                spec = orderItemsHelper.getCustomSpec(itemRowsMap, item);
                Rows rows = new Rows();
                if(!type.equals("配件订单")){
                    rows= dbConnect.runSqlQuery("SELECT sa_orderitemsid,price from sa_orderitems WHERE siteid='" + siteid + "' and itemid=" + itemid + " and sa_orderid=" + sa_orderid + " and sa_promotion_itemsid=" + item.getLongValue("sa_promotion_itemsid"));
                }else{
                    if(StringUtils.isBlank(item.getStringValue("cardno"))){
                        rows= dbConnect.runSqlQuery("SELECT t1.sa_orderitemsid,qty from sa_orderitems t1  WHERE t1.siteid='" + siteid + "' and t1.itemid=" + itemid + " and t1.sa_orderid=" + sa_orderid);
                    }else{
                        rows= dbConnect.runSqlQuery("SELECT t1.sa_orderitemsid,qty from sa_orderitems t1  WHERE t1.siteid='" + siteid + "' and t1.itemid=" + itemid + " and t1.sa_orderid=" + sa_orderid + " and ifnull(t1.cardno,'')='"+item.getStringValue("cardno")+"'");
                    }

                }

                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");
                        item.put("qty",item.getBigDecimal("qty").add(rows.get(0).getBigDecimal("qty")));
                        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;
            }
            if (!isInsert) {
                if (dbConnect.runSqlQuery("SELECT 1 from sa_orderitems WHERE itemid=" + itemid + " and sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'").isEmpty()) {
                    continue;
                }
            }

            //是否符合商品起订量和增量判断
            orderItemsHelper.compareQty(type, itemRowsMap, item, itemRow, promotionitems);

            BigDecimal defaultprice;
            BigDecimal price;
            BigDecimal saleprice;
            BigDecimal custamount = new BigDecimal(0);
            BigDecimal rwpricerate = new BigDecimal(1);
            BigDecimal pricerate = new BigDecimal(1);

            SQLFactory sqlFactory = new SQLFactory(this, "商品等级价格查询");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("itemid", itemid);
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
            if (rows.isNotEmpty()) {
                pricerate = rows.get(0).getBigDecimal("discountrate");
                //如果折扣率为0，则默认按照1计算
                pricerate = (pricerate.compareTo(BigDecimal.ZERO) == 0 ? new BigDecimal(1) : pricerate);
            }

            custamount = item.getBigDecimal("custamount");
//            int year = Year.now().getValue();
//            int quarter = YearMonth.now().get(IsoFields.QUARTER_OF_YEAR);
            Rows custamountrows = dbConnect.runSqlQuery("select * from sa_agents where sys_enterpriseid=" + sys_enterpriseid + " and siteid='" + siteid + "'");
            if(custamountrows.isNotEmpty()){
                if(custamountrows.get(0).getDouble("pricerate")>0){
                    rwpricerate = custamountrows.get(0).getBigDecimal("pricerate");
                }
            }

            //价格
            ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, itemid);
            price = orderItemsHelper.getPrice(orderRows.get(0), itemPrice, item);
            saleprice = orderItemsHelper.getSalePrice(orderRows.get(0), itemPrice, item, itemRowsMap);
            if (price.compareTo(BigDecimal.ZERO) < 0) {
                return getErrReturnObject().setErrMsg("价格不可小于0").toString();
            }
            defaultprice = price.multiply(rwpricerate);
            if (isInsert) {
                price = price.multiply(rwpricerate).add(custamount);
                sqlList.add(orderItemsHelper.getInsertSQL(customproperties, sa_orderitemsid, item, itemRow, type, defaultprice, price, saleprice, itemPrice.getMarketprice(), spec, promotionitems, width, length, custamount, rwpricerate, pricerate).getSQL());
                if (item.getLongValue("sa_workorderid") != 0) {
                    sqlList.add("update sa_workorder_node_items set sourceid=" + sa_orderid + ",sourcetable='sa_order' where sa_workorderid=" + item.getLongValue("sa_workorderid") + " and itemid=" + item.getLong("itemid"));
                }
            } else {
                price = price.multiply(rwpricerate).add(item.getBigDecimal("custamount"));
                if(item.containsKey("defaultprice")){
                    defaultprice=item.getBigDecimal("defaultprice");
                    price = defaultprice.multiply(pricerate).add(custamount);
                }
                sqlList.add(orderItemsHelper.getUpdateSQL(sa_orderitemsid, item, itemRow, type, defaultprice, price, saleprice, width, length).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 || sumqty.compareTo(orderminqty) < 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);

        //查询商品费用方案方法
        orderItemsHelper.executeDataFunction(sa_orderid);
        //生成摘要
        SQLFactory sqlFactory = new SQLFactory(this, "查询定制费用统计");
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        if (rows.isNotEmpty()) {
            String remarks = StringUtils.join(rows.toArray("remarks"), ";");
            dbConnect.runSqlUpdate("UPDATE sa_order set abstract ='" + remarks + "' WHERE sa_orderid='" + sa_orderid + "'");

        } else {
            dbConnect.runSqlUpdate("UPDATE sa_order set abstract ='' WHERE sa_orderid='" + sa_orderid + "'");
        }
        if(type.equals("配件订单")){
            dbConnect.runSqlUpdate("UPDATE sa_order o JOIN (SELECT  sa_orderid,GROUP_CONCAT(DISTINCT delivery SEPARATOR '+') AS delivery FROM sa_orderitems where sa_orderid="+sa_orderid+" GROUP BY sa_orderid) od ON o.sa_orderid = od.sa_orderid SET  o.delivery = od.delivery ");
        }
        sqlList=new ArrayList<>();
        if (type.equals("配件订单")) {
            Rows rowsdetails = dbConnect.runSqlQuery("select t1.price,t1.amount,t1.sa_orderitemsid,t1.itemid,ifnull(t2.iswriteoff,0) iswriteoff,t3.begdate,t3.enddate from sa_orderitems t1 " +
                    "left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid " +
                    "left join sa_warrantycard t3 on t1.cardno=t3.cardno and t1.siteid=t3.siteid and t3.isvoid=0 and t3.deleted=0 where t1.sa_orderid=" + sa_orderid);
            for (Row rowsdetail : rowsdetails) {
                String billingstatus = "";
                BigDecimal price = rowsdetail.getBigDecimal("price");
                BigDecimal amount = rowsdetail.getBigDecimal("amount");
                String begdate = rowsdetail.getString("begdate");
                String enddate = rowsdetail.getString("enddate");
                if (StringUtils.isBlank(begdate) || StringUtils.isBlank(enddate)) {
                    billingstatus = "保外收费";
                } else {
                    Date date = new Date();
                    if (date.before(rowsdetail.getDate("enddate"))) {
                        if (rowsdetail.getBoolean("iswriteoff")) {
                            billingstatus = "保内收费";
                        } else {
                            billingstatus = "保内免费";
                            price = BigDecimal.ZERO;
                            amount = BigDecimal.ZERO;
                        }
                    }else{
                        billingstatus = "保外收费";
                    }
                }
                if(!billingstatus.equals("保内收费")){
                    sqlList.add("update sa_orderitems set canoffqty=0 where sa_orderitemsid=" + rowsdetail.getLong("sa_orderitemsid"));
                }
                sqlList.add("update sa_orderitems set price=" + price + ",amount=" + amount + ",billingstatus='" + billingstatus + "' where sa_orderitemsid=" + rowsdetail.getLong("sa_orderitemsid"));
            }
        }
        dbConnect.runSqlUpdate(sqlList);

        if (systemclient.equalsIgnoreCase("wechatsaletool")) {
            rows = dbConnect.runSqlQuery("SELECT sa_orderitemsid,sa_orderitems_v,itemid from sa_orderitems WHERE sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
            return getSucReturnObject().setData(rows).toString();
        }

        return getSucReturnObject().toString();
    }

    @API(title = "更新可核销数", apiversion = R.ID2025111710122903.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class, accessoryorder.class})
    public String updatecanoffqty() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Long sa_orderitemsid = content.getLong("sa_orderitemsid");
        BigDecimal canoffqty = content.getBigDecimal("canoffqty");
        Rows orderRows = dbConnect.runSqlQuery("SELECT status,sonum,type from sa_order WHERE sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        Rows orderitemsRows = dbConnect.runSqlQuery("SELECT sa_orderitemsid,qty,billingstatus,rowno from sa_orderitems WHERE sa_orderid=" + sa_orderid + " and sa_orderitemsid='" + sa_orderitemsid + "'");
        if(orderRows.isEmpty()){
            return getErrReturnObject().setErrMsg("配件订单不存在").toString();
        }
        if(!orderRows.get(0).getString("type").equals("配件订单")){
            return getErrReturnObject().setErrMsg("非配件订单无法更新可核销数").toString();
        }
        if(orderRows.get(0).getString("status").equals("审核")){
            return getErrReturnObject().setErrMsg("配件订单【"+orderRows.get(0).getString("sonum")+"】已审核，无法更新可核销数").toString();
        }
        if(orderitemsRows.isEmpty()){
            return getErrReturnObject().setErrMsg("订单明细不存在").toString();
        }
        if(!orderitemsRows.get(0).getString("billingstatus").equals("保内收费")){
            return getErrReturnObject().setErrMsg("行号【"+orderitemsRows.get(0).getString("rowno")+"】为非保内收费配件，无法更新可核销数").toString();
        }
        if(orderitemsRows.get(0).getBigDecimal("qty").compareTo(canoffqty)<0){
            return getErrReturnObject().setErrMsg("可核销数不可大于商品明细行数量").toString();
        }
        dbConnect.runSqlUpdate("update sa_orderitems set canoffqty="+canoffqty+" where sa_orderitemsid="+sa_orderitemsid);
        return getSucReturnObject().toString();
    }


    @API(title = "查询订单重复商品", apiversion = R.ID2024020201095102.v1.class)
    public String get() throws YosException {
        Long sa_orderid = content.getLongValue("sa_orderid");
        JSONArray items = content.getJSONArray("items");

        OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);
        //批量查询商品信息
        RowsMap itemRowsMap = orderItemsHelper.getItemRowsMap(items);
        Rows rows = new Rows();
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            Long itemid = item.getLongValue("itemid");
            JSONArray customproperties = new JSONArray();
            customproperties = orderItemsHelper.getCustomProperties(itemRowsMap, item);

            String custompropertie = customproperties.toJSONString().replace("[", "").replace("]", "").replace("\"", "");

            Rows temprows = dbConnect.runSqlQuery("SELECT itemname from  sa_orderitems WHERE sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' and itemid=" + itemid + " and ifnull(customproperties,'')='" + custompropertie + "'");
            rows.addAll(temprows);
        }
        Rows orderRows = beans.order.Order.getOrderRows(this, sa_orderid);
        String type = orderRows.get(0).getString("type");
        Row resRow = new Row();
        resRow.put("items", rows);

        resRow.put("isrepeat", beans.order.Order.getDefaultIsRepeatValue(siteid, type));


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

    }


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

        Rows orderRows = getOrderRows(this, sa_orderid);


        if (orderRows.isEmpty()) {
            return "订单不存在";
        }
        String type = "";
        Rows promotionrows = dbConnect.runSqlQuery("select * from sa_promotion where siteid='" + siteid + "' and  sa_promotionid=" + orderRows.get(0).getLong("sa_promotionid"));
        Rows promotionenterpriserows = dbConnect.runSqlQuery("select * from sa_promotion_auth where siteid='" + siteid + "'and sys_enterpriseid=" + orderRows.get(0).getLong("pay_enterpriseid") + " and  sa_promotionid=" + orderRows.get(0).getLong("sa_promotionid"));
        if (promotionrows.isNotEmpty()) {
            type = promotionrows.get(0).getString("type");
        }
        JSONArray jsonArray = new JSONArray();
        BigDecimal orderamount = getAmount(this, sa_orderid);
        //System.out.println(orderamount);
        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 + "' and t1.sa_accountclassid=" + orderRows.get(0).getLong("sa_accountclassid"));
        if (accountclassrows.isEmpty()) {
            return "未找到扣款账户";
        }


        JSONObject jsonObject = new JSONObject();
        if (type.equals("返利促销")) {
            BigDecimal associationamount = promotionrows.get(0).getBigDecimal("associationamount");
            long associationaccountclassid = promotionrows.get(0).getLong("associationaccountclassid");
            Rows associationaccountclassrows = 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 + "' and t1.sa_accountclassid=" + associationaccountclassid);
            if (promotionenterpriserows.isNotEmpty()) {
                if (promotionenterpriserows.get(0).getBoolean("islimit")) {
                    BigDecimal limitamount = promotionenterpriserows.get(0).getBigDecimal("limitamount");
                    BigDecimal saledamount = promotionenterpriserows.get(0).getBigDecimal("saledamount");
                    if (orderamount.compareTo(limitamount.subtract(saledamount)) <= 0) {
                        //全部优先账户扣款
                        jsonObject.put("amount", orderamount);
                        jsonObject.put("sa_accountclassid", accountclassrows.get(0).getLong("sa_accountclassid"));
                        jsonObject.put("accountname", accountclassrows.get(0).getString("accountname"));
                        jsonObject.put("balance", accountclassrows.get(0).getBigDecimal("balance"));
                        jsonObject.put("creditquota", accountclassrows.get(0).getBigDecimal("creditquota"));
                        jsonArray.add(jsonObject);
                    } else if (orderamount.compareTo(limitamount.subtract(saledamount)) > 0) {
                        //全部优先账户扣款
                        jsonObject.put("amount", limitamount.subtract(saledamount));
                        jsonObject.put("sa_accountclassid", accountclassrows.get(0).getLong("sa_accountclassid"));
                        jsonObject.put("accountname", accountclassrows.get(0).getString("accountname"));
                        jsonObject.put("balance", accountclassrows.get(0).getBigDecimal("balance"));
                        jsonObject.put("creditquota", accountclassrows.get(0).getBigDecimal("creditquota"));
                        jsonArray.add(jsonObject);

                        JSONObject associationjsonObject = new JSONObject();
                        associationjsonObject.put("amount", orderamount.subtract((limitamount.subtract(saledamount))));
                        associationjsonObject.put("sa_accountclassid", associationaccountclassrows.get(0).getLong("sa_accountclassid"));
                        associationjsonObject.put("accountname", associationaccountclassrows.get(0).getString("accountname"));
                        associationjsonObject.put("balance", associationaccountclassrows.get(0).getBigDecimal("balance"));
                        associationjsonObject.put("creditquota", associationaccountclassrows.get(0).getBigDecimal("creditquota"));
                        jsonArray.add(associationjsonObject);
                    } else {
                        return "未知错误，请联系管理员";
                    }
                }
            } else {
                //全部优先账户扣款
                jsonObject.put("amount", orderamount);
                jsonObject.put("sa_accountclassid", accountclassrows.get(0).getLong("sa_accountclassid"));
                jsonObject.put("accountname", accountclassrows.get(0).getString("accountname"));
                jsonObject.put("balance", accountclassrows.get(0).getBigDecimal("balance"));
                jsonObject.put("creditquota", accountclassrows.get(0).getBigDecimal("creditquota"));
                jsonArray.add(jsonObject);
            }
        } else {
            //全部优先账户扣款
            jsonObject.put("amount", orderamount);
            jsonObject.put("sa_accountclassid", accountclassrows.get(0).getLong("sa_accountclassid"));
            jsonObject.put("accountname", accountclassrows.get(0).getString("accountname"));
            jsonObject.put("balance", accountclassrows.get(0).getBigDecimal("balance"));
            jsonObject.put("creditquota", accountclassrows.get(0).getBigDecimal("creditquota"));
            jsonArray.add(jsonObject);
        }
        //System.out.println(jsonArray.toJSONString());
        dbConnect.runSqlUpdate("update sa_order set sa_accountclassinfos='" + jsonArray + "' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        return "true";
    }

    /**
     * 更新居间费信息
     */
    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, accessoryorder.class})
    public String delete() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        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();
            }
        }

        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);


        JSONArray sa_orderitemsids = content.getJSONArray("sa_orderitemsids");
        String packagetypemxsql = "select t1.sa_orderitemsid from sa_orderitems t1 inner join sa_promotion_items t2 on t1.sa_promotion_itemsid=t2.sa_promotion_itemsid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and  t1.sa_orderid in(select sa_orderid from sa_orderitems where sa_orderitemsid in" + sa_orderitemsids.toJSONString() + ") and t2.packagetypemx in(select distinct t2.packagetypemx from sa_orderitems t1 inner join sa_promotion_items t2 on t1.sa_promotion_itemsid=t2.sa_promotion_itemsid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.sa_orderitemsid in" + sa_orderitemsids.toJSONString() + " and ifnull(t2.packagetypemx,'')!='')";
        packagetypemxsql = packagetypemxsql.replace("[", "(").replace("]", ")");
        Rows packagetypemxrows = dbConnect.runSqlQuery(packagetypemxsql);
        sa_orderitemsids.addAll(packagetypemxrows.toJsonArray("sa_orderitemsid"));

        String sqldetail = "select sa_workorderid,itemid from sa_orderitems where siteid='" + siteid + "' and sa_orderitemsid in" + sa_orderitemsids.toJSONString();
        sqldetail = sqldetail.replace("[", "(").replace("]", ")");
        Rows rowsdetail = dbConnect.runSqlQuery(sqldetail);
        for (Row row : rowsdetail) {
            if (row.getLong("sa_workorderid") != 0) {
                dbConnect.runSqlUpdate("update sa_workorder_node_items set sourceid=0,sourcetable='' where sa_workorderid=" + row.getLong("sa_workorderid") + " and itemid=" + row.getLong("itemid"));
            }
        }


        SQLFactory sqlFactory = new SQLFactory(this, "订单商品明细_删除");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids.toArray());
        String sql = sqlFactory.getSQL();
        dbConnect.runSqlUpdate(sql);

//        DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sa_order_cost");
//        deleteSQL.setSiteid(siteid);
//        deleteSQL.setWhere("sa_orderitemsid", sa_orderitemsids.toArray());
//        deleteSQL.delete();
        //重新排序
        updateRowNo(sa_orderid);
        //居间费设置
        updateRebateFee(sa_orderid);
        //调整价格（返利金）
        if (orderRows.get(0).getBoolean("rebate_used")) {
            setRebateAmount(this, sa_orderid, getMaxUsedRebateAmount(this, sa_orderid));
            adjustLastRebateAmount(this, sa_orderid);
        }
//        else {
//            dbConnect.runSqlUpdate("UPDATE sa_orderitems SET amount=amount-rebateamount,price=amount/qty WHERE sa_orderid='" + sa_orderid + "' and siteid='" + siteid + "' and qty!=0");
//            dbConnect.runSqlUpdate("UPDATE sa_orderitems SET amount=0 WHERE sa_orderid='" + sa_orderid + "' and siteid='" + siteid + "' and qty=0");
//        }


        updateAccountclassinfos(sa_orderid);
        new OrderItemsHelper(this).executeDataFunction(sa_orderid);
        //生成摘要
        sqlFactory = new SQLFactory(this, "查询定制费用统计");
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        if (rows.isNotEmpty()) {
            String remarks = StringUtils.join(rows.toArray("remarks"), ";");
            dbConnect.runSqlUpdate("UPDATE sa_order set abstract ='" + remarks + "' WHERE sa_orderid='" + sa_orderid + "'");

        } else {
            dbConnect.runSqlUpdate("UPDATE sa_order set abstract ='' WHERE sa_orderid='" + sa_orderid + "'");
        }

        return getSucReturnObject().toString();
    }

    @API(title = "商品行冻结", apiversion = R.ID20230508093003.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class, accessoryorder.class})
    public String freeze() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        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, accessoryorder.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();
        }

        if (!(discountrate.compareTo(BigDecimal.ZERO) > 0 && discountrate.compareTo(new BigDecimal(1)) <= 0)) {
            return getErrReturnObject().setErrMsg("折扣必须在0-100之间").toString();
        }

        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        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"));
            }
        }
        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单整单折扣变更【" + discountrate + "】", "整单折扣").getSQL());
        dbConnect.runSqlUpdate(sqlList);

        updateAccountclassinfos(sa_orderid);
        return getSucReturnObject().toString();
    }

    @API(title = "商品行价格刷新（变为原价）", apiversion = R.ID20230518151803.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class, accessoryorder.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();
        }

        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        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"));
            }
        }
        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单商品行价格刷新(恢复原价)", "订单商品行价格刷新").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        updateAccountclassinfos(sa_orderid);
        return getSucReturnObject().toString();
    }


    @API(title = "查询列表", apiversion = R.ID20221109093902.v1.class)
    public String selectList() throws YosException, IOException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isNotEmpty()) {
            sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
        }
        Long sa_promotionid = orderRows.get(0).getLong("sa_promotionid");//大于0表示为促销订单
        String type = orderRows.get(0).getString("type");//订单类型

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems", "*");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "st_invbal_sale", "t2", "t2.siteid = t1.siteid AND t2.itemid = t1.itemid", "cansaleqty", "candispatchqty");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t3", "t3.itemid = t1.itemid and t3.siteid = t1.siteid", "orderminqty_auxunit", "orderaddqty_auxunit", "spec", "iscustomsize", "widthschemeid", "lengthschemeid", "standards", "k3outcode","outplace","aftersalesitemno");
        if (sa_promotionid > 0) {
            querySQL.addJoinTable(JOINTYPE.left, "sa_promotion_items", "t4", "t4.sa_promotion_itemsid = t1.sa_promotion_itemsid and t4.siteid = t1.siteid and t4.sa_promotionid=" + sa_promotionid, "packagetypemx", "saledqty", "groupqty");
        }
        if (type.equals("配件订单")) {
            querySQL.addJoinTable(JOINTYPE.left, "sa_workorder", "t5", "t1.sa_workorderid=t5.sa_workorderid and t1.siteid=t5.siteid");
            querySQL.addJoinTable(JOINTYPE.left, "sa_warrantycard", "t6", "t6.cardno=t1.cardno and t6.siteid=t1.siteid", "sku","address");
            querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t7", "t7.itemid=t6.itemid and t7.siteid=t6.siteid");
        }
        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))");
        if (sa_promotionid > 0) {
            querySQL.addQueryFields("packageqty", "ifnull(t4.packageqty, 0)");
            querySQL.addQueryFields("orderaddqty", "t4.orderaddqty");
            querySQL.addQueryFields("orderminqty", "t4.orderminqty");
        } else {
            querySQL.addQueryFields("packageqty", "ifnull(t3.packageqty, 0)");
            querySQL.addQueryFields("orderaddqty", "t3.orderaddqty");
            querySQL.addQueryFields("orderminqty", "t3.orderminqty");
        }
        querySQL.addQueryFields("itemdelivery", "ifnull(t3.delivery, 0)");

        if (type.equals("配件订单")) {
            querySQL.addQueryFields("workorderbillno", "t5.billno");
            querySQL.addQueryFields("workorderdate", "t5.createdate");
            querySQL.addQueryFields("workorderstatus", "t5.status");
            querySQL.addQueryFields("cardname", "t6.name");
            querySQL.addQueryFields("cardphonenumber", "t6.phonenumber");
            querySQL.addQueryFields("cardbegdate", "t6.begdate");
            querySQL.addQueryFields("cardenddate", "t6.enddate");
            querySQL.addQueryFields("machineitemno", "t7.itemno");
            querySQL.addQueryFields("machineitemname", "t7.itemname");
            querySQL.addQueryFields("machinemodel", "t7.model");
            querySQL.addQueryFields("machinespec", "t7.spec");
            querySQL.addQueryFields("warrantystatus", "CASE WHEN ifnull(t6.enddate,'')='' then '' else CASE WHEN t6.enddate >= CURDATE() THEN '保内' ELSE '保外' END end");

        }

        querySQL.setCondition("t1.itemno", "t1.itemname", "t1.model", "t3.spec", "t1.remarks");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.sa_orderid", sa_orderid);
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (sa_promotionid > 0 && whereObject.containsKey("packagetypemx") && !"".equals(whereObject.getString("packagetypemx"))) {
                querySQL.setWhere("t4.packagetypemx", whereObject.getString("packagetypemx"));
            }
        }
        querySQL.setPage(pageSize, pageNumber).setOrderBy("t1.rowno asc");

        Rows rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        ArrayList<Long> itemnos = rows.toArrayList("itemno", new ArrayList<>());

//        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");
//
//
        SQLFactory sqlFactory3 = new SQLFactory(this, "商品原价汇总");
        sqlFactory3.addParameter("siteid", siteid);
        sqlFactory3.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory3.addParameter_in("itemnos", itemnos);
        Rows itempriceRows = dbConnect.runSqlQuery(sqlFactory3);
        RowsMap itempriceRowsMap = itempriceRows.toRowsMap("itemno");


        //查询附件
        RowsMap attinfoRowsMap = getAttachmentUrl("plm_item", ids);
        //默认商品图片
        Rows defaultImageRows = Item.getItemdefaultImage(this);
        // 封面cover
        RowsMap defaultItemCoverRowsMap = getAttachmentUrl("plm_item", ids, "cover");

        for (Row row : rows) {
            if (itempriceRowsMap.get(row.getString("itemno")).isNotEmpty()) {
                row.put("originalprice", itempriceRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("price"));
            } else {
                row.put("originalprice", BigDecimal.ZERO);
            }
//            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", "");
            //设置默认商品图片
            Rows coverRows = defaultItemCoverRowsMap.get(row.getString("itemid"));
            if (coverRows.isNotEmpty()) {
                row.put("cover", coverRows.get(0).getString("url"));
            } else if (defaultImageRows.isNotEmpty()) {
                row.put("cover", defaultImageRows.get(0).getString("url"));
            } else {
                row.put("cover", "");
            }

        }
        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("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t5.remarks 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 t5.billdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t5.billdate <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("begindate1") && !"".equals(whereObject.getString("begindate1"))) {
                where.append(" and t5.submitdate >='").append(whereObject.getString("begindate1")).append("' ");
            }
            if (whereObject.containsKey("enddate1") && !"".equals(whereObject.getString("enddate1"))) {
                where.append(" and t5.submitdate <='").append(getDate_Str(whereObject.getDate("enddate1"), 1)).append("' ");
            }

            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and t5.checkdate >='").append(whereObject.getString("begindate2")).append("' ");
            }

            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and t5.checkdate <='").append(getDate_Str(whereObject.getDate("enddate2"), 1)).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>ifnull(t2.sumoutwarehouseqty,0) or t1.undeliqty>0)");
                } else {
                    where.append(" and t1.sa_orderitemsid not 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>ifnull(t2.sumoutwarehouseqty,0) or t1.undeliqty>0)");
                }
            }
            if (whereObject.containsKey("iscancel") && !"".equals(whereObject.getString("iscancel"))) {
                String iscancel = whereObject.getStringValue("iscancel");
                if (iscancel.equals("1")) {
                    where.append(" and t5.status ='手工关闭' and not exists(select 1 from st_stockbill_items s1 inner join st_stockbill s2 on s1.st_stockbillid=s2.st_stockbillid and s1.siteid=s2.siteid where s2.status='审核' and s2.rb=1 and s1.sa_orderitemsid=t1.sa_orderitemsid)");
                }
            }
            if (whereObject.containsKey("convenient") && !"".equals(whereObject.getString("convenient"))) {
                String convenient = whereObject.getStringValue("convenient");
                if (convenient.equals("待确认")) {
                    where.append(" and t5.status ='预提交' ");
                } else if (convenient.equals("待审核")) {
                    where.append(" and t5.status ='提交' ");
                } else if (convenient.equals("待发货")) {
                    where.append(" and t5.status in ('审核') and not exists(select 1 from st_stockbill_items s1 inner join st_stockbill s2 on s1.st_stockbillid=s2.st_stockbillid and s1.siteid=s2.siteid where s2.status='审核' and s2.rb=1 and s1.sa_orderitemsid=t1.sa_orderitemsid)");
                } else if (convenient.equals("已发货")) {
                    where.append(" and exists(select 1 from st_stockbill_items s1 inner join st_stockbill s2 on s1.st_stockbillid=s2.st_stockbillid and s1.siteid=s2.siteid where s2.status='审核' and s2.rb=1 and s1.sa_orderitemsid=t1.sa_orderitemsid)");
                } else if (convenient.equals("已取消")) {
                    where.append(" and t5.status ='手工关闭' and  exists(select 1 from (\n" + "select  s1.sa_orderitemsid,s1.siteid,sum(s1.qty) qty from st_stockbill_items s1 inner join st_stockbill s2 on s1.st_stockbillid=s2.st_stockbillid and s1.siteid=s2.siteid where s2.status='审核' and s2.rb=1 group by s1.sa_orderitemsid,s1.siteid) s right join sa_orderitems s1  on s.sa_orderitemsid=s1.sa_orderitemsid and s.siteid=s1.siteid where s1.qty>ifnull(s.qty,0) and s1.sa_orderitemsid=t1.sa_orderitemsid)");
                }
            }
        }

        QuerySQL querySQL = queryAllListManage(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap rowsMap = rows.toRowsMap("itemno");

        ArrayList<Long> sa_orderitemsid = rows.toArrayList("sa_orderitemsid", new ArrayList<>());

        QuerySQL t11 = SQLFactory.createQuerySQL(this, "sa_dispatch_items", "sa_orderitemsid", "siteid");
        t11.addQueryFields("outwarehouseqty", "sum(outwarehouseqty)");
        t11.setSiteid(siteid);
        t11.setWhere("sa_orderitemsid", sa_orderitemsid);
        t11.addGroupBy("sa_orderitemsid,siteid");
        RowsMap outwarehouseqtyRowsMap = t11.query().toRowsMap("sa_orderitemsid");

        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(siteid);
        JSONArray jsonArray = new JSONArray();
        if (rows.toJsonArray("itemno").size() != 0) {
//            if (Parameter.getBoolean("system.ccerp_dockswitch")) {
//                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()) {
                        for (Row row : rowsMap.get(jsonObject.getString("fitemno"))) {
                            row.put("invbalqty", jsonObject.getBigDecimal("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 (outwarehouseqtyRowsMap.get(row.getString("sa_orderitemsid")).isNotEmpty()) {
                row.put("outwarehouseqty", outwarehouseqtyRowsMap.get(row.getString("sa_orderitemsid")).get(0).getBigDecimal("outwarehouseqty"));
            } else {
                row.put("outwarehouseqty", 0);
            }
        }
        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", "saleprice", "defaultprice", "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", "decorationrebateflag","isclose","custamount");
        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", "closedate","createby");
        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", "abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "plm_itemclass", "t8", "t8.itemclassid = t3.marketingcategory and t8.siteid = t3.siteid", "itemclassname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_tradefield", "t9", "t9.sys_enterpriseid = t5.sys_enterpriseid and t9.tradefield=t5.tradefield and t9.siteid = t5.siteid", "sys_enterprise_tradefieldid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t10", "t10.sa_saleareaid = t9.sa_saleareaid and t10.siteid = t9.siteid", "areaname");
//        querySQL.addJoinTable(JOINTYPE.left, t11, "t11", "t11.sa_orderitemsid = t1.sa_orderitemsid and t11.siteid = t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_accountclass", "t12", "t12.sa_accountclassid=t5.sa_accountclassid and t12.siteid=t5.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_promotion", "t13", "t13.sa_promotionid=t5.sa_promotionid and t13.siteid=t5.siteid", "promnum", "promname");
        querySQL.addQueryFields("accountnames", "if(ifnull(replace(replace(replace(JSON_EXTRACT (t5.sa_accountclassinfos, '$**.accountname' ),'[',''),']',''),'\\\"',''),'')='',t12.accountname,replace(replace(replace(JSON_EXTRACT (t5.sa_accountclassinfos, '$**.accountname' ),'[',''),']',''),'\\\"',''))");
        querySQL.addQueryFields("orderremarks", "t5.remarks");
//        querySQL.addQueryFields("outwarehouseqty", "ifnull(t11.outwarehouseqty,0)");
        //querySQL.addQueryFields("accountclassinfos", "REPLACE(SUBSTRING(JSON_EXTRACT(t5.sa_accountclassinfos, '$[*].accountname'), 2, CHAR_LENGTH(JSON_EXTRACT(t5.sa_accountclassinfos, '$[*].accountname'))-2),'\"','')");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t5.status in ('审核', '关闭','手工关闭') and t5.type!='配件订单'");
        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 {
        Long sa_orderid = content.getLong("sa_orderid");

        String type = "";
        Long order_sys_enterpriseid = 0L;
        JSONArray sa_accountclassinfos = new JSONArray();

        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isNotEmpty()) {
            type = orderRows.get(0).getString("type");
            order_sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
            sa_accountclassinfos = orderRows.get(0).getJSONArray("sa_accountclassinfos");
        }

        boolean iswuliao = false;
        for (Object sa_accountclassinfo : sa_accountclassinfos) {
            JSONObject jsonObject = (JSONObject) sa_accountclassinfo;
            if (jsonObject.getString("accountname").equals("物料账户")) {
                iswuliao = true;
            }
        }

        QuerySQL itemquery = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemno", "itemname", "model", "batchcontrol", "orderminqty_auxunit", "orderminqty", "orderaddqty_auxunit", "orderaddqty", "spec", "standards", "delistingstatus", "marketprice", "packageqty", "iscustomsize", "widthschemeid", "lengthschemeid", "materialschemeid", "colorschemeid", "cheekschemeid", "sa_customschemeid", "custamount", "isonsale").setTableAlias("t1");
        itemquery.addJoinTable(JOINTYPE.left, "plm_unitgroup", "t2", "t1.unitgroupid=t2.unitgroupid ", "conversionrate");
        itemquery.addJoinTable(JOINTYPE.left, "plm_itemextend", "t3", "t1.itemid=t3.itemid ", "material", "erpitemno", "erpitemname", "specalnote", "prodline", "device");
        itemquery.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t1.unitid=t4.unitid ");
        itemquery.addJoinTable(JOINTYPE.left, "plm_unit", "t5", "t5.unitid=t2.auxunitid ");
        if (!type.equals("特殊订单") && Enterprise.getEnterprise(this, order_sys_enterpriseid).getBoolean("saleclassauth")) {
            itemquery.addJoinTable(JOINTYPE.inner, "sys_enterprise_itemauth_view", "t6", "t1.itemid=t6.itemid and t6.sys_enterpriseid=" + order_sys_enterpriseid);
        }
        itemquery.addQueryFields("unit", "t4.unitname");
        itemquery.addQueryFields("auxunit", "t5.unitname");
        itemquery.setSiteid(siteid);
        itemquery.setCondition("t1.itemno", "t1.itemname", "t1.model", "t1.spec", "t1.standards", "t3.material", "t3.erpitemno");
        itemquery.setWhere("status", "审核");
        itemquery.setWhere("t1.ismodule=0");
        if (sys_enterpriseid > 0 || !type.equals("特殊订单")) {
            itemquery.setWhere("t1.isonsale", true);
        } else if (content_where.containsKey("isonsale")) {
            itemquery.setWhere("t1.isonsale", content_where.getBooleanValue("isonsale"));
        }
        if (!type.equals("特殊订单")) {
            itemquery.setWhere("t1.istool", false);
        }
        if (iswuliao) {
            itemquery.setWhere("t1.iswuliao", true);
        }

        itemquery.setPage(pageSize, pageNumber);
        Rows rows = itemquery.query();

        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        //默认商品图片
        Rows defaultImageRows = Item.getItemdefaultImage(this);
        //查询附件
        RowsMap attinfoRowsMap = Attachment.get(this, "plm_item", ids);
        // 封面cover
        RowsMap defaultItemCoverRowsMap = getAttachmentUrl("plm_item", ids, "cover");
        //商品领域
        RowsMap tradefieldRowsMap = Item.getTradefieldRowsMap(this, ids);
        //品牌
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, ids);
        //价格
        HashMap<Long, ItemPrice> itemPriceRowsMap = ItemPrice.getItemPrice(this, order_sys_enterpriseid, ids);

        for (Row row : rows) {
            row.put("attinfos", attinfoRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("tradefield", tradefieldRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            if (itemPriceRowsMap.containsKey(row.getLong("itemid"))) {
                row.put("price", itemPriceRowsMap.get(row.getLong("itemid")).getPrice());
            } else {
                row.put("price", 0);
            }
            //设置默认商品图片
            Rows coverRows = defaultItemCoverRowsMap.get(row.getString("itemid"));
            if (coverRows.isNotEmpty()) {
                row.put("cover", coverRows.get(0).getString("url"));
            } else if (defaultImageRows.isNotEmpty()) {
                row.put("cover", defaultImageRows.get(0).getString("url"));
            } else {
                row.put("cover", "");
            }
        }
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "判断订单中是否存在某个套餐", apiversion = R.ID2024062715055803.v1.class)
    @CACHEING
    public String jundgePackagetypemx() throws YosException {
        String packagetypemx = content.getString("packagetypemx");
        long sa_orderid = content.getLong("sa_orderid");
        Rows rows = dbConnect.runSqlQuery("select t1.sa_orderitemsid from sa_orderitems t1 inner join sa_promotion_items t2 on t1.sa_promotion_itemsid=t2.sa_promotion_itemsid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.sa_orderid=" + sa_orderid + " and t2.packagetypemx=" + packagetypemx);
        if (rows.isNotEmpty()) {
            return getSucReturnObject().setData(true).toString();
        } else {
            return getSucReturnObject().setData(false).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();
    }

//    //获取特殊数据
//    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");
//        //通过版本更新订单表头数据
//        beans.order.Order.updateOrderWithVersion(this);
//
//        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);
//
//        }
//
//        Long sa_cashbillid = 0L;
//        if (ce.compareTo(BigDecimal.ZERO) != 0) {
//            CashbillEntity cashbillEntity = new CashbillEntity();
//            cashbillEntity.setAmount(ce);
//            cashbillEntity.setDiscountamountamount(BigDecimal.ZERO);
//            cashbillEntity.setOwnerid(sa_orderid);
//            cashbillEntity.setOwnertable("sa_order");
//            cashbillEntity
//                    .setRemarks("订单变更:" + rowsOrder.get(0).getString("sonum") + "订单变更单id:" + sa_orderitems_changeid);
//            JSONObject cashbillPay = Accountbalance.createCashbillPay(this, rowsOrder.get(0).getLong("sys_enterpriseid"),
//                    rowsOrder.get(0).getLong("sa_accountclassid"), cashbillEntity, true);
//            sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
//            sa_cashbillid = cashbillPay.getLong("sa_cashbillid");
//        }
//
//        /**
//         * * 核销对冲
//         */
//        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.getBoolean("system.erp_dockswitch")) {
//                UploadDataToERP uploadDataToERP = new UploadDataToERP(this, sa_orderitems_changeid, "订单变更");
//                boolean issuccess = uploadDataToERP.upload();
//                if (!issuccess) {
//                    return getErrReturnObject().setErrMsg("上传ERP失败，请检查！").toString();
//                }
//            }
//        }
//
//        if (ce.compareTo(BigDecimal.ZERO) != 0) {
//            Accountbalance.remindSend(this, rowsOrder.get(0).getLong("sys_enterpriseid"), sa_cashbillid);
//        }
//        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.getBoolean("system.erp_dockswitch")) {
//            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();
//        }
//
//    }

    @API(title = "行关闭", apiversion = R.ID2025102210393903.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String close() throws YosException {
        boolean isclose= content.getBoolean("isclose");
        long sa_orderid=content.getLong("sa_orderid");
        JSONArray sa_orderitemsids = content.getJSONArray("sa_orderitemsids");
        String closereason=content.getStringValue("closereason");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        Row row = rows.get(0);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems", "*").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_order", "t2", "t1.siteid = t2.siteid and t1.sa_orderid = t2.sa_orderid");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_orderitemsid",sa_orderitemsids);
        querySQL.setWhere("t2.sa_orderid",sa_orderid);
        Rows rowsdetails = querySQL.query();
        closevalidate(isclose,row,rowsdetails);

        /******** 行关闭状态更新 ********/
        Date date = getDate();
        BigDecimal returnamount =BigDecimal.ZERO;// 退还金额
        for(Row rowsdetail :rowsdetails){
            BigDecimal amount = rowsdetail.getBigDecimal("amount");// 订单金额
            BigDecimal price = rowsdetail.getBigDecimal("price");// 单价
            BigDecimal qty = rowsdetail.getBigDecimal("qty");// 订单数量
            BigDecimal undeliqty = rowsdetail.getBigDecimal("undeliqty");;// 订单未发货数量
            BigDecimal sendqty = qty.subtract(undeliqty);
            BigDecimal sendamount =price.multiply(sendqty).setScale(2, RoundingMode.HALF_UP);
            returnamount = returnamount.add(amount.subtract(sendamount));

            sqlList.add("update sa_orderitems set isclose="+isclose+",closereason='"+closereason+"',closeby='"+username+"',closedate=CURRENT_TIME where sa_orderitemsid="+rowsdetail.getLong("sa_orderitemsid"));
        }

        Rows freezRows = dbConnect.runSqlQuery("select * from sa_accountbalance_freez where sourcetable='sa_order' and sourceid="+sa_orderid);
        Row freezRow= null;
        if (!freezRows.isEmpty()) {
            freezRow = freezRows.get(0);
        }
        if (isclose) {
            if (freezRow != null) {
                BigDecimal amount = freezRow.getBigDecimal("amount");// 冻结金额
                sqlList.add("update sa_accountbalance_freez set amount="+amount.subtract(returnamount)+" where sa_accountbalance_freezid="+freezRow.getLong("sa_accountbalance_freezid"));
                sqlList.add("update sa_accountbalance set freezamount=freezamount-"+returnamount+" where sa_accountbalanceid="+freezRow.getLong("sa_accountbalanceid"));

                if (amount.subtract(returnamount).compareTo(BigDecimal.ZERO)<=0) {
                    sqlList.add("delete from  sa_accountbalance_freez where sa_accountbalance_freezid="+freezRow.getLong("sa_accountbalance_freezid"));
                }
            }
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "关闭", "行"+rowsdetails.toJsonArray("rowno")+"关闭成功")
                    .getSQL());
        } else {
            if (freezRow == null) {
                Rows accountbalanceRows = dbConnect.runSqlQuery("select * from sa_accountbalance where sys_enterpriseid="+row.getLong("sys_enterpriseid")+" and sa_accountclassid="+row.getLong("sa_accountclassid"));
                if(accountbalanceRows.isNotEmpty()){
                    InsertSQL frezzInsertSQL = SQLFactory.createInsertSQL(this, "sa_accountbalance_freez");
                    frezzInsertSQL.setUniqueid(createTableID("sa_accountbalance_freez"));
                    frezzInsertSQL.setSiteid(siteid);
                    frezzInsertSQL.setValue("sa_accountbalanceid", accountbalanceRows.get(0).getLong("sa_accountbalanceid"));
                    frezzInsertSQL.setValue("sourcetable", "sa_order");
                    frezzInsertSQL.setValue("sourceid", sa_orderid);
                    frezzInsertSQL.setValue("amount", returnamount);
                    frezzInsertSQL.setValue("remarks", "订单反关闭冻结");
                    frezzInsertSQL.setValue("createby", username);
                    frezzInsertSQL.setDateValue("createdate");
                    sqlList.add(frezzInsertSQL.getSQL());
                    sqlList.add("update sa_accountbalance set freezamount=freezamount+"+returnamount+" where sa_accountbalanceid="+accountbalanceRows.get(0).getLong("sa_accountbalanceid"));
                }else{
                    return getErrReturnObject().setErrMsg("账户不存在").toString();
                }
            } else {
                BigDecimal amount = freezRow.getBigDecimal("amount");// 冻结金额
                sqlList.add("update sa_accountbalance_freez set amount="+amount.add(returnamount)+" where sa_accountbalance_freezid="+freezRow.getLong("sa_accountbalance_freezid"));
                sqlList.add("update sa_accountbalance set freezamount=freezamount+"+returnamount+" where sa_accountbalanceid="+freezRow.getLong("sa_accountbalanceid"));
            }
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "反关闭", "行"+rowsdetails.toJsonArray("rowno")+"反关闭成功")
                    .getSQL());
        }
        /******** 增加总仓可销售量 ********/
        if (isclose) {
            sqlList.addAll(updateIcinvbal_sale(rowsdetails,true, true));
        } else {
            sqlList.addAll(updateIcinvbal_sale(rowsdetails,false, true));
        }
        dbConnect.runSqlUpdate(sqlList);
        if (isclose) {
            if(dbConnect.runSqlQuery("select * from sa_orderitems where isclose=0 and undeliqty!=0 and sa_orderid="+sa_orderid).isEmpty()){
                dbConnect.runSqlUpdate("update sa_order set status='关闭',closeby='"+username+"',closedate=CURRENT_TIME where sa_orderid=" +sa_orderid);
            }
        }else{
            dbConnect.runSqlUpdate("update sa_order set status='审核',closeby='',closedate='NUlL' where sa_orderid=" +sa_orderid);
        }



        return getSucReturnObject().toString();
    }


    public void closevalidate(boolean isclose,Row order,Rows orderdetails) throws YosException {
        if (isclose && !order.getString("status").equals("审核")) {
            throw new YosException("非审核状态下不可关闭");
        }

        if (isclose && orderdetails.size() == 0) {
            throw new YosException("请购选行再进行关闭操作");
        }
        if (!isclose && orderdetails.size() == 0) {
            throw new YosException("请购选行再进行取消关闭操作");
        }
        int i = 0;
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill_items", "*").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "st_stockbill", "t2", "t1.siteid = t2.siteid and t1.st_stockbillid = t2.st_stockbillid");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_orderitemsid",orderdetails.toArrayList("sa_orderitemsid"));
        querySQL.setWhere("t2.status='新建'");
        Rows rows = querySQL.query();
        RowsMap rowsMap = rows.toRowsMap("sa_orderitemsid");
        for(Row row :orderdetails){
            int rownum = row.getInteger("rowno");
            if (isclose) {
                if (row.getBoolean("isclose")) {
                    throw new YosException("行" + rownum
                            + "已经关闭，不可重复关闭");
                }
                if (row.getBigDecimal("undeliqty").compareTo(BigDecimal.ZERO)==0) {
                    throw new YosException("行" + rownum
                            + "已发货完毕，不可关闭");
                }

                if (rowsMap.containsKey(row.getString("sa_orderitemsid"))) {
                    throw new YosException("行" + rownum
                            + "存在新建的销售出库单，不可关闭");
                }
//                if (saorderdetail.getPao(i).getPaoSet("SAINVOICEDETAIL")
//                        .count() > 0) {
//                    throw new YosException("行" + rownum
//                            + "存在发货单，不可关闭");
//                }
            } else {
                if (!row.getBoolean("isclose")) {
                    throw new YosException("行" + rownum
                            + "未关闭，不可取消关闭");
                }
                if (row.getDouble("undeliqty") == 0) {
                    throw new YosException("行" + rownum
                            + "已发货完毕，不可取消关闭");
                }
            }
        }
    }


    /**
     * 获取当前订单的最大行号
     *
     * @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());
        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<>(), false);
        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();
            dbConnect.runSqlUpdate(sql);
        }

    }

    /**
     * @param isinstock 是否增加库存
     * @throws YosException
     */
    public ArrayList<String> updateIcinvbal_sale(Rows rowsdetails, boolean isinstock, boolean close) throws YosException {
        Rows ICINVBAL_SALE = SQLFactory.createQuerySQL(this, "st_invbal_sale").setSiteid(siteid).setWhere("itemid", rowsdetails.toArrayList("itemid")).query();
        RowsMap ICINVBAL_SALERowsMap = ICINVBAL_SALE.toRowsMap("itemid");
        int i = 0;
        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : rowsdetails) {
            long st_invbal_saleid;
            if (ICINVBAL_SALERowsMap.containsKey(row.getString("itemid"))) {
                st_invbal_saleid = ICINVBAL_SALERowsMap.get(row.getString("itemid")).get(0).getLong("st_invbal_saleid");
            } else {
                st_invbal_saleid = createTableID("st_invbal_sale");
                InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_invbal_sale");
                insertSQL.setUniqueid(st_invbal_saleid);
                insertSQL.setSiteid(siteid);
                insertSQL.setValue("itemid", row.getString("itemid"));
                insertSQL.setValue("candispatchqty", 0);
                insertSQL.setValue("cansaleqty", 0);
                insertSQL.setValue("qty", 0);
                insertSQL.setValue("changeuserid", userid);
                insertSQL.setValue("changeby", username);
                insertSQL.setDateValue("changedate");
                sqlList.add(insertSQL.getSQL());
            }
            BigDecimal qty = BigDecimal.ZERO;
            if (close) {
                qty = isinstock ? row.getBigDecimal("undeliqty") : row.getBigDecimal("undeliqty").negate();// 如果是扣减则根据数量进行扣减，反之根据未发货数量进行增加
            } else {
                qty = isinstock ? row.getBigDecimal("undeliqty")
                        : row.getBigDecimal("qty").negate();// 如果是扣减则根据数量进行扣减，反之根据未发货数量进行增加
            }
            sqlList.add("update st_invbal_sale set cansaleqty=cansaleqty+" + qty + " where st_invbal_saleid=" + st_invbal_saleid);
        }
        return sqlList;
    }

}