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.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.ID2025121314111503.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, ToolBill.class, accessoryorder.class})
    public String insertOrUpdate_pj() 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");


        //批量查询商品信息
        RowsMap itemRowsMap = orderItemsHelper.getItemRowsMap(items);

        BigDecimal sumqty = BigDecimal.ZERO;
        ArrayList<Long> sa_orderitemsids_insert = new ArrayList<>();
        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);
            if (type.equals("标准订单") && itemRow.getString("ismodule").equals("1")) {
                return getErrReturnObject().setErrMsg("商品编号【" + itemRow.getString("itemno") + "】是配件，无法添加").toString();
            }
            if (type.equals("配件订单") && itemRow.getString("ismodule").equals("0")) {
                return getErrReturnObject().setErrMsg("商品编号【" + itemRow.getString("itemno") + "】不是配件，无法添加").toString();
            }

            Boolean isInsert = true;
            JSONArray customproperties = new JSONArray();
            if (sa_orderitemsid <= 0) {
//                   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 ifnull(cardno,'')='' 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")+"'");
//                    }
//
//                }
                sa_orderitemsid = createTableID("sa_orderitems");
                isInsert = true;
//                if (rows.isEmpty()) {
//                    sa_orderitemsid = createTableID("sa_orderitems");
//                    isInsert = true;
//                } 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 {
                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, new Rows());

            BigDecimal defaultprice;
            BigDecimal price;
            BigDecimal saleprice;


            //价格
            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;
            if (isInsert) {
                sa_orderitemsids_insert.add(sa_orderitemsid);
                sqlList.add(orderItemsHelper.getInsertSQL(customproperties, sa_orderitemsid, item, itemRow, type, defaultprice, price, saleprice, itemPrice.getMarketprice(), "", new Rows(), 0, 0, BigDecimal.ZERO, BigDecimal.valueOf(1), BigDecimal.valueOf(1)).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 {
                Rows itemidRows = dbConnect.runSqlQuery("select itemid from sa_orderitems where sa_orderitemsid=" + sa_orderitemsid);
                price = item.getBigDecimal("price");
                defaultprice = item.getBigDecimal("defaultprice");
                if (itemidRows.isNotEmpty()) {
                    if (itemidRows.get(0).getLong("itemid") != itemid) {
                        defaultprice = orderItemsHelper.getPrice(orderRows.get(0), itemPrice, item);
                        price = orderItemsHelper.getPrice(orderRows.get(0), itemPrice, item);
                    }
                }
                sqlList.add(orderItemsHelper.getUpdateSQL(sa_orderitemsid, item, itemRow, type, defaultprice, price, saleprice, 0, 0).getSQL());
//                if(StringUtils.isNotBlank(item.getStringValue("cardno"))){
//                    Rows aftersalesbomrows = dbConnect.runSqlQuery("select t2.sa_aftersalesbomid from sa_warrantycard t1 inner join sa_aftersalesbom_items t2 on t1.itemid=t2.itemid inner join sa_aftersalesbom t3 on t2.sa_aftersalesbomid=t3.sa_aftersalesbomid where t1.cardno='"+item.getStringValue("cardno")+"' and t3.status='审核'");
//                    if(aftersalesbomrows.isNotEmpty()){
//                        Rows aftersalesbompjrows = dbConnect.runSqlQuery("select qty from sa_aftersalesbom_pjs t1 where t1.sa_aftersalesbomid="+aftersalesbomrows.get(0).getString("sa_aftersalesbomid")+" and t1.itemid="+itemid);
//                        if(aftersalesbompjrows.isNotEmpty()){
//                            sqlList.add("update sa_orderitems set canoffqty="+(aftersalesbompjrows.get(0).getBigDecimal("qty").compareTo(qty)>0?qty:aftersalesbompjrows.get(0).getBigDecimal("qty"))+" where sa_orderitemsid=" + sa_orderitemsid);
//                        }else{
//                            sqlList.add("update sa_orderitems set canoffqty=1 where sa_orderitemsid=" + sa_orderitemsid);
//                        }
//                    }else{
//                        sqlList.add("update sa_orderitems set canoffqty=1 where sa_orderitemsid=" + sa_orderitemsid);
//                    }
//                }
            }


        }

        dbConnect.runSqlUpdate(sqlList);
        //重新排序
        updateRowNo(sa_orderid);
        //更新订单扣款信息
        updateAccountclassinfos(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 = "保内免费";
                            if (sa_orderitemsids_insert.contains(rowsdetail.getLong("sa_orderitemsid"))) {
                                price = BigDecimal.ZERO;
                                amount = BigDecimal.ZERO;
                            }

                        }
                    } else {
                        billingstatus = "保外收费";
                    }
                }
                if (!billingstatus.equals("保内收费")) {
                    if (sa_orderitemsids_insert.contains(rowsdetail.getLong("sa_orderitemsid"))) {
                        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);

        return getSucReturnObject().toString();
    }

    @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;
        ArrayList<Long> sa_orderitemsids_insert = new ArrayList<>();
        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);
            if (type.equals("标准订单") && itemRow.getString("ismodule").equals("1")) {
                return getErrReturnObject().setErrMsg("商品编号【" + itemRow.getString("itemno") + "】是配件，无法添加").toString();
            }
            if (type.equals("配件订单") && itemRow.getString("ismodule").equals("0")) {
                return getErrReturnObject().setErrMsg("商品编号【" + itemRow.getString("itemno") + "】不是配件，无法添加").toString();
            }

            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 ifnull(cardno,'')='' 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");
                    isInsert = true;
                } 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");
            Rows itemrows = dbConnect.runSqlQuery("select topclassnum from plm_item where itemid=" + itemid + " and siteid='" + siteid + "'");
            if (!type.equals("配件订单") && "集成灶".equals(itemrows.get(0).getString("topclassnum"))) {
                rwpricerate = Enterprise.getRWPriceRate(this, Enterprise.getAgentsID(this, sys_enterpriseid));
            }
            //价格
            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;
            if (isInsert) {
                sa_orderitemsids_insert.add(sa_orderitemsid);
                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(rwpricerate).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 = "保内免费";
                            if (sa_orderitemsids_insert.contains(rowsdetail.getLong("sa_orderitemsid"))) {
                                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.ID2025121316011603.v1.class)
    public String oneclickchangedelivery() throws YosException {
        String delivery = content.getString("delivery");
        long sa_orderid = content.getLong("sa_orderid");
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add("update sa_orderitems set delivery='" + delivery + "' where sa_orderid=" + sa_orderid);
        sqlList.add("update sa_order set delivery='" + delivery + "' where sa_orderid=" + sa_orderid);
        dbConnect.runSqlUpdate(sqlList);
        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");
        JSONArray sa_tpartreimbursementids = new JSONArray();
        if (content.containsKey("sa_tpartreimbursementids")) {
            sa_tpartreimbursementids = content.getJSONArray("sa_tpartreimbursementids");
        }
        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,itemname,canoffqty from sa_orderitems WHERE sa_orderid=" + sa_orderid + " and sa_orderitemsid='" + sa_orderitemsid + "'");
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("配件订单不存在").toString();
        }
        Row orderRow = orderRows.get(0);
        if (!orderRow.getString("type").equals("配件订单")) {
            return getErrReturnObject().setErrMsg("非配件订单无法更新可核销数").toString();
        }
        if (orderRow.getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("配件订单【" + orderRow.getString("sonum") + "】已审核，无法更新可核销数").toString();
        }
        if (orderitemsRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单明细不存在").toString();
        }
        Row orderitemsRow = orderitemsRows.get(0);
        if (orderitemsRow.getBigDecimal("qty").compareTo(canoffqty) < 0) {
            return getErrReturnObject().setErrMsg("可核销数不可大于商品明细行数量").toString();
        }
        String itemname = orderitemsRow.getString("itemname");
        BigDecimal canoffqty_old = orderitemsRow.getBigDecimal("canoffqty");

        dbConnect.runSqlUpdate("update sa_orderitems set canoffqty=" + canoffqty + " where sa_orderitemsid=" + sa_orderitemsid);

        DataContrlLog.createLog(this, "sa_order", sa_orderid, "更新可核销数", "【" + itemname + "】可核销数量 由" + canoffqty_old + " 变更为 " + canoffqty).insert();
        for (Object obj : sa_tpartreimbursementids) {
            Long sa_tpartreimbursementid = Long.parseLong(obj.toString());
            DataContrlLog.createLog(this, "sa_tpartreimbursement", sa_tpartreimbursementid, "更新可核销数", "【" + itemname + "】可核销数量 由" + canoffqty_old + " 变更为 " + canoffqty).insert();
        }


        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;
            if (item != null) {
                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");
        if (sa_orderitemsids.isEmpty()) {
            return getErrReturnObject().setErrMsg("请选择要删除的订单商品").toString();
        }

        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", "iswriteoff");
        }
        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");

        QuerySQL tpartreimbursement = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_tpartreimbursementid", "sa_orderitemsid").setTableAlias("t1");
        tpartreimbursement.addJoinTable(JOINTYPE.inner, "sa_tpartreimbursement", "t2", "t2.sa_tpartreimbursementid=t1.sa_tpartreimbursementid", "billno");
        tpartreimbursement.setWhere("t1.sa_orderitemsid", rows.toArrayList("sa_orderitemsid"));
        RowsMap tpartreimbursementRowsMap = tpartreimbursement.query().toRowsMap("sa_orderitemsid");


        //查询附件
        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);
            }
            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", "");
            }

            Long sa_orderitemsid = row.getLong("sa_orderitemsid");
            row.put("tpartreimbursement", tpartreimbursementRowsMap.getOrDefault(sa_orderitemsid, new Rows()));

        }
        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");
        boolean ismanage = content.getBooleanValue("ismanage");

        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 (!ismanage) {
            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", "审核");
        if (!ismanage) {
            itemquery.setWhere("t1.isonsale=1 and t1.isused=1");
        }
        itemquery.setWhere("t1.ismodule=0");
        if (content_where.containsKey("isonsale")) {
            itemquery.setWhere("t1.isonsale", content_where.getBooleanValue("isonsale"));
        }
        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));
        }
        int i = dbConnect.runSqlUpdate(sqlList);
        if (i >= 0) {
            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);
                    Rows accountbalancerows = dbConnect.runSqlQuery("select * from  sa_accountbalance t1 inner join sa_accountclass t2 on  t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid where t2.accountname='现金账户' and t1.sys_enterpriseid=" + row.getLong("sys_enterpriseid"));
                    dbConnect.runSqlUpdate("UPDATE sa_accountbalance t1 left JOIN sa_accountbalance_freez t2 ON t1.sa_accountbalanceid = t2.sa_accountbalanceid " + "SET t1.freezamount = case when ifnull(t1.freezamount,0)>ifnull(t2.amount,0) then t1.freezamount-ifnull(t2.amount,0) else 0 end " + "WHERE t1.sa_accountbalanceid=" + accountbalancerows.get(0).getLong("sa_accountbalanceid") + " and t2.sourcetable='sa_order' and t2.sourceid=" + sa_orderid);
                    dbConnect.runSqlUpdate("delete from sa_accountbalance_freez t1 WHERE t1.sourcetable='sa_order' and t1.sourceid=" + 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;
    }

    @API(title = "刷订单保修状态", apiversion = R.ID2026040214285502.v1.class)
    public String updatebillingstatus() throws YosException {
        System.err.println(getDateTime_Str() + ":准备刷订单保修状态...");

        String sql = "SELECT if(billdate BETWEEN begdate and enddate,if(price>0,'保内收费','保内免费'),'保外收费'  ) billingstatus,sa_orderitemsid ,verifiedqty,tobeoffqty,canoffqty,qty from ( " +
                "SELECT t1.sa_orderitemsid,t1.price,t2.billdate, IF(isvoid=1,voiddate,enddate) enddate,begdate,verifiedqty,tobeoffqty,canoffqty,qty from  sa_orderitems t1 " +
                "INNER JOIN sa_order t2 ON t2.sa_orderid=t1.sa_orderid " +
                "LEFT JOIN sa_warrantycard t3 ON t3.cardno=t1.cardno " +
                "WHERE t2.type='配件订单'  " +
                "and t1.billingstatus is  null " +
                "and t2.deleted=0 " +
                "and t2.billdate<='2025-12-05'  " +
                "ORDER BY t2.billdate " +
                ") a ";

        // 用于暂存 SQL 的列表
        ArrayList<String> sqlList = new ArrayList<>();
        // 设定批次大小
        int batchSize = 5000;

        Rows rows = dbConnect.runSqlQuery(sql);
        int count = 0; // 计数器

        for (Row row : rows) {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_orderitems");
            updateSQL.setSiteid(siteid);
            updateSQL.setUniqueid(row.getLong("sa_orderitemsid"));
            updateSQL.setValue("billingstatus", row.getString("billingstatus"));
            updateSQL.setValue("tobeoffqty", 0);
            if (row.getString("billingstatus").equals("保内收费")) {
                updateSQL.setValue("canoffqty", row.getString("qty"));
                updateSQL.setValue("offstatus", 1);
            } else {
                updateSQL.setValue("canoffqty", 0);
            }

            // 将生成的 SQL 加入列表
            sqlList.add(updateSQL.getSQL());
            count++;

            // 当达到批次大小时，执行更新并清空列表
            if (count % batchSize == 0) {
                System.err.println(getDateTime_Str() + ":正在执行第 " + count + " 条数据的批量更新...");
                dbConnect.runSqlUpdate(sqlList);
                sqlList.clear(); // 清空列表以释放内存
            }
        }

        // 【重要】循环结束后，处理剩余不足 5000 条的数据
        if (!sqlList.isEmpty()) {
            System.err.println(getDateTime_Str() + ":正在执行最后剩余 " + sqlList.size() + " 条数据的批量更新...");
            dbConnect.runSqlUpdate(sqlList);
            sqlList.clear();
        }

        System.err.println(getDateTime_Str() + ":结束刷订单保修状态...");
        return getSucReturnObject().toString();
    }


}