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

package restcontroller.webmanage.sale.order;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.clear.ClearData;
import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import beans.datateam.DataTeam;
import beans.enterprise.Enterprise;
import beans.hr.Hr;
import beans.itemprice.ItemPrice;
import beans.parameter.Parameter;
import beans.remind.Remind;
import beans.salearea.SaleArea;
import beans.uploaderpdata.UploadDataToERP;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import common.data.db.DBConnect;
import common.data.db.SQLiteTable;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.jdbc.SQL;
import restcontroller.R;
import restcontroller.sale.cashbill.cashbill;
import restcontroller.sale.promotion.promotion;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.sale.accessoryorder.accessoryorder;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import static beans.order.Order.*;
import static com.alibaba.fastjson.util.TypeUtils.castToBigDecimal;

/**
 * 销售订单
 */
public class Order extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Order(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新增或更新", apiversion = R.ID20221108111402.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String insertOrUpdate() throws YosException {

        ArrayList<String> sqlList = new ArrayList<>();
        //表名
        String tableName = "sa_order";
        Long sa_orderid = content.getLong("sa_orderid");
        Long sys_enterpriseid_temp = content.getLongValue("sys_enterpriseid");
        if (sys_enterpriseid_temp > 0) {
            sys_enterpriseid = sys_enterpriseid_temp;
        }
        if (dbConnect.runSqlQuery("SELECT 1 FROM sa_agents WHERE siteid='" + siteid + "' AND `status`='启用' AND sys_enterpriseid=" + sys_enterpriseid).isEmpty()) {
            return getErrReturnObject().setErrMsg("当前企业不存在或已禁用，无法下单").toString();
        }
        Long sa_brandid = content.getLong("sa_brandid");
        Long sa_contractid = content.getLongValue("sa_contractid");
        Long sa_projectid = content.getLongValue("sa_projectid");
        String type = content.getString("type");

        boolean isAdd;
        SQLFactory sqlFactory = new SQLFactory(this, "订单_新增");
        Rows rows = dbConnect.runSqlQuery("SELECT status from sa_order WHERE sa_orderid = " + sa_orderid);
        if (sa_orderid <= 0) {
            isAdd = true;
            sa_orderid = createTableID(tableName);
            //默认当前用户为负责人
            DataTeam.createTeam(this, "sa_order", sa_orderid, userid);
            dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "新增", "订单新增成功").getSQL());
        } else if (rows.isEmpty()) {
            isAdd = true;
            //默认当前用户为负责人
            DataTeam.createTeam(this, "sa_order", sa_orderid, userid);
            dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "新增", "订单新增成功").getSQL());
        } else {
            if (rows.get(0).getString("status").equals("新建") || rows.get(0).getString("status").equals("提交")) {

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

                sqlFactory = new SQLFactory(this, "订单_更新");
                isAdd = false;
                dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "更新", "订单更新成功").getSQL());
            } else {
                return getErrReturnObject().setErrMsg("【新建】【提交】状态可修改").toString();
            }
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        //订单编号
        sqlFactory.addParameter("sonum", createBillCode("orderbill"));
        //订单类型
        sqlFactory.addParameter("type", type);
        //明细分类
        sqlFactory.addParameter("typemx", content.getStringValue("typemx"));
        //订货企业id
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        //营销账户类型ID
        OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);
//        Long sa_accountclassid = orderItemsHelper.getAccountClassId(type);
        Long sa_accountclassid = content.getLong("sa_accountclassid");
        if (sa_accountclassid <= 0) {
            return getErrReturnObject().setErrMsg("未找到扣款账户信息").toString();
        }

        orderItemsHelper.isAccountCanUsed(sa_accountclassid);

        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlList.addAll(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));

//        if ((type.equals("标准订单") || type.equals("特殊订单")) && content.getString("tradefield").equals("整装工程")) {
//            sqlFactory.addParameter("sa_accountclassid", "69");
//            sqlList.addAll(updateAccountclassinfos(sa_orderid, 69, sys_enterpriseid));
//        } else {
//            if (type.equals("促销订单")) {
//                Rows accountclassRows = dbConnect.runSqlQuery("select sa_accountclassid from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + content.getLongValue("sa_promotionid"));
//                if (!accountclassRows.isEmpty()) {
//                    sqlFactory.addParameter("sa_accountclassid", accountclassRows.get(0).getLong("sa_accountclassid"));
//                } else {
//                    sqlFactory.addParameter("sa_accountclassid", beans.order.Order.getDefaultAccount(siteid, type));
//                }
//            } else {
//                sqlFactory.addParameter("sa_accountclassid", beans.order.Order.getDefaultAccount(siteid, type));
//            }
//            sqlList.addAll(updateAccountclassinfos(sa_orderid, beans.order.Order.getDefaultAccount(siteid, type), sys_enterpriseid));
//        }
        //品牌ID
        sqlFactory.addParameter("sa_brandid", sa_brandid);
        //合同ID
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        //工程项目表ID
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        //合作企业财务信息ID（开票信息）
        Long sys_enterprise_financeid = content.getLongValue("sys_enterprise_financeid");
        if (sys_enterprise_financeid <= 0 && isAdd) {
            sys_enterprise_financeid = getDefaultEnterpriseFinanceid(sys_enterpriseid);
        }
        sqlFactory.addParameter("sys_enterprise_financeid", sys_enterprise_financeid);
        //物流公司档案ID
        sqlFactory.addParameter("sa_logiscompid", content.getLongValue("sa_logiscompid"));
        //合作企业联系人表ID（收货信息）
        //合作企业联系人表ID（收货信息），设置默认
        Long rec_contactsid = content.getLongValue("rec_contactsid");
        if (rec_contactsid <= 0 && isAdd) {
            rec_contactsid = getDefaultContactsid(sys_enterpriseid);
        }
        sqlFactory.addParameter("rec_contactsid", rec_contactsid);
        //备注说明
        sqlFactory.addParameter("remarks", content.getStringValue("remarks"));
        //销售人员hrid,业务员hrid
        sqlFactory.addParameter("saler_hrid", getSaleHrid(sys_enterpriseid));
        //制单方 1表示工厂方，0表示客户方
        if (usertype == 21 || usertype == 22) {
            sqlFactory.addParameter("createflag", 0);
        } else {
            sqlFactory.addParameter("createflag", 1);
        }
        // 领域
        sqlFactory.addParameter("tradefield", content.getString("tradefield"));
        //结算单位
        Long pay_enterpriseid = content.getLongValue("pay_enterpriseid");
        if (isAdd) {
            pay_enterpriseid = sys_enterpriseid;
        }
        sqlFactory.addParameter("pay_enterpriseid", pay_enterpriseid);
        //单据日期
        String billdate = content.getStringValue("billdate");
        //订单新增时
        if (isAdd) {
            if (billdate.equals("")) {
                sqlFactory.addParameter_SQL("billdate", "current_time");
            } else {
                sqlFactory.addParameter("billdate", billdate);
            }
            if (content.containsKey("sa_accountclassid") && !content.getString("sa_accountclassid").equals("")) {
                sa_accountclassid = content.getLongValue("sa_accountclassid");
                if (sa_accountclassid <= 0) {
                    return getErrReturnObject().setErrMsg("账户不存在").toString();
                }
                if (type.equals("标准订单")) {
                    Rows accountclassrows = dbConnect.runSqlQuery("select * from sa_accountclass where siteid='" + siteid + "' and sa_accountclassid=" + sa_accountclassid);
                    if (accountclassrows.isEmpty()) {
                        return getErrReturnObject().setErrMsg("账户不存在").toString();
                    } else {
                        if (accountclassrows.get(0).getString("accountname").equals("活动账户")) {
                            return getErrReturnObject().setErrMsg("标准订单禁止选择活动账户").toString();
                        }
                    }
                }
                sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
                sqlList.addAll(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
            }
        } else {
            //订单更新时
            if (content.getLongValue("sa_accountclassid") != 0) {
                Rows accountclassRows = dbConnect
                        .runSqlQuery(
                                "select accountno,accountname from sa_accountclass where sa_accountclassid=" + content.getLongValue("sa_accountclassid"));
                if (accountclassRows.isNotEmpty()) {
                    if (accountclassRows.get(0).getString("accountname").equals("物料账户")) {
                        Rows wuliaoRows = dbConnect.runSqlQuery("select * from sa_orderitems t1 where exists(select 1 from plm_item t left join plm_itemclass t2 on t.marketingcategory=t2.itemclassid and t.siteid=t2.siteid where t.itemid=t1.itemid and t2.itemclassname='物料') and t1.sa_orderid=" + sa_orderid);
                        Rows itemRows = dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid=" + sa_orderid);
                        if (wuliaoRows.size() != itemRows.size()) {
                            return getErrReturnObject().setErrMsg("物料账户只能添加物料商品，请检查").toString();
                        }
                    }

                    if (type.equals("标准订单")) {
                        if (accountclassRows.get(0).getString("accountname").equals("活动账户")) {
                            return getErrReturnObject().setErrMsg("标准订单禁止选择活动账户").toString();
                        }
                    }
                } else {
                    return getErrReturnObject().setErrMsg("账户不存在").toString();
                }
                sqlFactory.addParameter("sa_accountclassid", content.getLongValue("sa_accountclassid"));
                sqlList.addAll(updateAccountclassinfos(sa_orderid, content.getLongValue("sa_accountclassid"), sys_enterpriseid));
            }
            if (billdate.equals("")) {
                sqlFactory.addParameter_SQL("billdate", "createdate");
            } else {
                sqlFactory.addParameter("billdate", billdate);
            }
        }

        sqlFactory.addParameter("rebate_userate", content.getBigDecimal("rebate_userate"));
        sqlFactory.addParameter("rebate_used", content.getBooleanValue("rebate_used"));
        sqlFactory.addParameter("signbacknum", content.getStringValue("signbacknum"));
        sqlFactory.addParameter("tradefieldmx", content.getStringValue("tradefieldmx"));
        sqlFactory.addParameter("sa_promotionid", content.getLongValue("sa_promotionid"));
        sqlFactory.addParameter("freightstatus", content.getStringValue("freightstatus"));
        sqlFactory.addParameter("saletype", content.getStringValue("saletype"));
        sqlFactory.addParameter("signbackstatus", content.getStringValue("signbackstatus", true, "不需要"));
        sqlFactory.addParameter("projectnote", content.getStringValue("projectnote"));
        sqlFactory.addParameter("abstract", content.getStringValue("abstract"));

        sqlFactory.addParameter("delivery", content.getStringValue("delivery"));
        sqlFactory.addParameter("tracknumber", content.getStringValue("tracknumber"));

        String sql = sqlFactory.getSQL();

        dbConnect.runSqlUpdate(sql);

        dbConnect.runSqlUpdate(sqlList);


        content.put("sa_orderid", sa_orderid);
        return selectDetail();
    }

    @API(title = "购物车转化订单", apiversion = R.ID20221128183202.v1.class, intervaltime = 2000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String ship() throws YosException {
        ArrayList<String> sqlList1 = new ArrayList<>();
        Long istool = content.getLongValue("istool");
        Long sa_brandid = 0L;
        String tradefield = content.getStringValue("tradefield");
        Long sa_promotionid = content.getLongValue("sa_promotionid");

        JSONArray items = content.getJSONArray("items");
        ArrayList<Long> itemidids = new ArrayList();
        ArrayList<Long> sa_shoppingcartids = new ArrayList();
        String type = content.getStringValue("type", false, "标准订单");
        OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);
        //判断是否是同一个品牌的商品
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            long itemid = item.getLongValue("itemid");
            if (type.equals("促销订单")) {
                orderItemsHelper.checkOffOrderItems("sa_promotion_items", itemid, sa_promotionid);
            } else {
                orderItemsHelper.checkOffOrderItems("plm_item", itemid, sa_promotionid);
            }

            Long temp_sa_brandid = item.getLongValue("sa_brandid");
            itemidids.add(itemid);
            sa_shoppingcartids.add(item.getLongValue("sa_shoppingcartid"));

            if (sa_brandid == 0) {
                sa_brandid = temp_sa_brandid;
            }
            if (!type.equals("促销订单")) {
                if (!sa_brandid.equals(temp_sa_brandid)) {
                    return getErrReturnObject().setErrMsg("请添加同一品牌的商品").toString();
                }
            }
            Row itemRow = dbConnect.runSqlQuery(0, "SELECT * from plm_item WHERE itemid=" + itemid);
            if (itemRow.getString("iscustomsize").equals("1")) {
                Long length = item.getLongValue("length", 0L);
                Long width = item.getLongValue("width", 0L);
                if (length == 0 || width == 0) {
                    return getErrReturnObject().setErrMsg("检测到长宽尺寸存在0，无法下单").toString();
                }
            }


        }

        //判断领域
//        String sql = "SELECT count(0) count from plm_item_tradefield WHERE tradefield='" + tradefield + "' and itemid in " + itemidids;
//        sql = sql.replace("[", "(").replace("]", ")");
//        if (dbConnect.runSqlQuery(sql).get(0).getLong("count") != itemidids.size()) {
//            return getErrReturnObject().setErrMsg("请添加同一领域的商品").toString();
//        }
        if (sa_promotionid > 0) {
            Rows rows = dbConnect.runSqlQuery("SELECT ifnull(tradefield,'[]') tradefield  from sa_promotion WHERE sa_promotionid=" + sa_promotionid + " and siteid='" + siteid + "'");
            if (rows.isNotEmpty()) {
                String tradefields = rows.get(0).getString("tradefield");
                JSONArray jsonArray = JSONArray.parseArray(tradefields);
                tradefield = StringUtils.join(jsonArray, ",");
            }
        }


        if (dbConnect.runSqlQuery("SELECT 1 FROM sa_agents WHERE siteid='" + siteid + "' AND `status`='启用' AND sys_enterpriseid=" + sys_enterpriseid).isEmpty()) {
            return getErrReturnObject().setErrMsg("当前企业不存在或已禁用，无法下单").toString();
        }

        //表名
        String tableName = "sa_order";
        //类型为标准订单时查询默认框架合同信息
        Long sa_contractid = 0L;
        Long sa_projectid = 0L;

        Long sa_orderid = createTableID(tableName);
        SQLFactory sqlFactory = new SQLFactory(this, "订单_新增");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        //订单编号
        sqlFactory.addParameter("sonum", createBillCode("orderbill"));
        //订单类型
        sqlFactory.addParameter("type", type);
        //明细分类
        sqlFactory.addParameter("typemx", "");
        //订货企业id
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        Long sa_accountclassid = 0L;
//        //营销账户类型ID
//        if ((type.equals("标准订单") || type.equals("特殊订单")) && content.getString("tradefield").equals("整装工程")) {
//            sa_accountclassid = 69l;
//
//
//        } else {
//            if (type.equals("促销订单")) {
//                Rows accountclassRows = dbConnect.runSqlQuery("select sa_accountclassid from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + content.getLongValue("sa_promotionid"));
//                if (!accountclassRows.isEmpty()) {
//                    sa_accountclassid = accountclassRows.get(0).getLong("sa_accountclassid");
//
//                } else {
//                    sa_accountclassid = beans.order.Order.getDefaultAccount(siteid, type);
//
//                }
//            } else {
//                sa_accountclassid = beans.order.Order.getDefaultAccount(siteid, type);
//
//            }
//
//        }
//        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);

        Long sa_accountclassid = orderItemsHelper.getAccountClassId(type);
        if (sa_accountclassid <= 0) {
            return getErrReturnObject().setErrMsg("未找到扣款账户信息").toString();
        }
        orderItemsHelper.isAccountCanUsed(sa_accountclassid);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);


        //品牌ID
        sqlFactory.addParameter("sa_brandid", sa_brandid);
        //合同ID
        sqlFactory.addParameter("sa_contractid", sa_contractid);
        //工程项目表ID
        sqlFactory.addParameter("sa_projectid", sa_projectid);
        //合作企业财务信息ID（开票信息）
        Long sys_enterprise_financeid = content.getLongValue("sys_enterprise_financeid");
        if (sys_enterprise_financeid <= 0) {
            sys_enterprise_financeid = getDefaultEnterpriseFinanceid(sys_enterpriseid);
        }
        sqlFactory.addParameter("sys_enterprise_financeid", sys_enterprise_financeid);
        //物流公司档案ID
        //sqlFactory.addParameter("sa_logiscompid", content.getLongValue("sa_logiscompid"));
        //合作企业联系人表ID（收货信息），设置默认
        Long rec_contactsid = content.getLongValue("rec_contactsid");
        if (rec_contactsid <= 0) {
            rec_contactsid = getDefaultContactsid(sys_enterpriseid);
        }

        sqlFactory.addParameter("rec_contactsid", rec_contactsid);
        //备注说明
        sqlFactory.addParameter("remarks", content.getStringValue("remarks"));
        //销售人员hrid,业务员hrid

        sqlFactory.addParameter("saler_hrid", getSaleHrid(sys_enterpriseid));
        //制单方 1表示工厂方，0表示客户方
        if (usertype == 21 || usertype == 22) {
            sqlFactory.addParameter("createflag", 0);
        } else {
            sqlFactory.addParameter("createflag", 1);
        }
        sqlFactory.addParameter("tradefield", tradefield);
        //结算单位
        Long pay_enterpriseid = content.getLongValue("pay_enterpriseid");
        if (pay_enterpriseid <= 0) {
            pay_enterpriseid = sys_enterpriseid;
        }
        sqlFactory.addParameter("pay_enterpriseid", pay_enterpriseid);
        //单据日期
        sqlFactory.addParameter_SQL("billdate", "current_time");
        sqlFactory.addParameter("rebate_used", content.getBooleanValue("rebate_used"));
        sqlFactory.addParameter("signbacknum", content.getBooleanValue("signbacknum"));
        sqlFactory.addParameter("tradefieldmx", content.getStringValue("tradefieldmx"));
        sqlFactory.addParameter("sa_promotionid", content.getLongValue("sa_promotionid"));
        sqlFactory.addParameter("freightstatus", content.getStringValue("freightstatus"));
        sqlFactory.addParameter("saletype", content.getStringValue("saletype"));
        sqlFactory.addParameter("signbackstatus", content.getStringValue("signbackstatus", true, "不需要"));
        sqlFactory.addParameter("projectnote", content.getStringValue("projectnote"));
        sqlFactory.addParameter("abstract", content.getStringValue("abstract"));

        sqlFactory.addParameter("delivery", content.getStringValue("delivery"));
        sqlFactory.addParameter("tracknumber", content.getStringValue("tracknumber"));

        dbConnect.runSqlUpdate(sqlFactory);

        content.put("sa_orderid", sa_orderid);
        content.put("sys_enterpriseid", sys_enterpriseid);
        content.put("type", type);

        String result = new OrderItems(content).insertOrUpdate();
        JSONObject jsonObject = JSONObject.parseObject(result);
        if (!jsonObject.getString("msg").equals("成功")) {
            return result;
        }

        ArrayList<String> sqlList = new ArrayList<>();

        //删除购物车中的数据
        String sql = "DELETE from sa_shoppingcart WHERE sys_enterpriseid = " + sys_enterpriseid + " and sa_shoppingcartid in " + sa_shoppingcartids + " AND siteid ='" + siteid + "'";
        sql = sql.replace("[", "(").replace("]", ")");
        sqlList.add(sql);
//        //清除多余的操作记录
//        sqlList.add("DELETE FROM sys_contrllog WHERE ownertable='sa_shoppingcart'  AND siteid='" + siteid + "'  AND NOT EXISTS (SELECT 1 FROM sa_shoppingcart WHERE siteid='" + siteid + "' AND sa_shoppingcartid=ownerid  )");
        //默认当前用户为负责人
        sqlList.addAll(DataTeam.createTeamSQL(this, "sa_order", sa_orderid, userid));
        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "新增", "购物车转化订单成功").getSQL());

        dbConnect.runSqlUpdate(sqlList);

//        if ((type.equals("标准订单") || type.equals("特殊订单")) && content.getString("tradefield").equals("整装工程")) {
//            sqlList1.addAll(updateAccountclassinfos(sa_orderid, 69, sys_enterpriseid));
//        } else {
//            if (type.equals("促销订单")) {
//                Rows accountclassRows = dbConnect.runSqlQuery("select sa_accountclassid from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + content.getLongValue("sa_promotionid"));
//                if (!accountclassRows.isEmpty()) {
//                    sqlList1.addAll(updateAccountclassinfos(sa_orderid, accountclassRows.get(0).getLong("sa_accountclassid"), sys_enterpriseid));
//                } else {
//                    sqlList1.addAll(updateAccountclassinfos(sa_orderid, beans.order.Order.getDefaultAccount(siteid, type), sys_enterpriseid));
//                }
//            } else {
//                sqlList1.addAll(updateAccountclassinfos(sa_orderid, beans.order.Order.getDefaultAccount(siteid, type), sys_enterpriseid));
//            }
//
//        }
        sqlList1.addAll(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
        dbConnect.runSqlUpdate(sqlList1);

        ClearData.clearShoppingCart(this);

        return selectDetail();
    }

    /**
     * 获取企业默认扣款账户
     *
     * @param sys_enterpriseid
     * @return
     * @throws YosException
     */
    public Long getDefaultAccountclassid(Long sys_enterpriseid) throws YosException {
        Long sa_accountclassid = 0L;
        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商营销账号");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid);
        Rows accountRows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        if (accountRows.isNotEmpty()) {
            sa_accountclassid = accountRows.get(0).getLong("sa_accountclassid");
        }
        return sa_accountclassid;
    }

    /**
     * 获取企业默认财务信息（开票）
     *
     * @param sys_enterpriseid
     * @return
     * @throws YosException
     */
    public Long getDefaultEnterpriseFinanceid(Long sys_enterpriseid) throws YosException {
        Long id = 0L;
        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商默认开票信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid);
        Rows row = dbConnect.runSqlQuery(sqlFactory.getSQL());
        if (row.isNotEmpty()) {
            id = row.get(0).getLong("sys_enterprise_financeid");
        }

        return id;
    }

    /**
     * 获取企业默认联系人
     *
     * @param sys_enterpriseid
     * @return
     * @throws YosException
     */
    public Long getDefaultContactsid(Long sys_enterpriseid) throws YosException {
        Long rec_contactsid = 0L;
        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商默认联系人");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_in("sys_enterpriseids", Enterprise.getUpDown(this, sys_enterpriseid));
        String sql = sqlFactory.getSQL();
        Rows contactsRows = dbConnect.runSqlQuery(sql);
        if (contactsRows.isNotEmpty()) {
            rec_contactsid = contactsRows.get(0).getLong("contactsid");
        }
        return rec_contactsid;
    }


    @API(title = "详情", apiversion = R.ID20221108151302.v1.class)
    @CACHEING
    public String selectDetail() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");

        SQLFactory sqlFactory = new SQLFactory(this, "订单_详情");
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        //System.err.println(sqlFactory.getSQL());
        //查询数据：amount，defaultamount，qty,writeoffamount,invoiceamount
        ArrayList<Long> sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>());
        sqlFactory = new SQLFactory(this, "订单_查询数据");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderid", sa_orderids);
        RowsMap dataRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_orderid");

        ArrayList<Long> sys_enterpriseids = rows.toArrayList("pay_enterpriseid", new ArrayList<>());
        //查询开票信息
        sqlFactory = new SQLFactory(this, "查询经销商默认开票信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseids);
        RowsMap financeRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sys_enterpriseid");

        for (Row row : rows) {
            row.put("pay_finance", financeRowsMap.getOrDefault(row.getString("sys_enterpriseid"), new Rows()));
            Rows dataRows = dataRowsMap.getOrDefault(row.getString("sa_orderid"), new Rows());
            BigDecimal writeoffamount = BigDecimal.valueOf(0);
            BigDecimal invoiceamount = BigDecimal.valueOf(0);
            BigDecimal amount = BigDecimal.valueOf(0);
            BigDecimal defaultamount = BigDecimal.valueOf(0);
            BigDecimal saleamount = BigDecimal.valueOf(0);
            BigDecimal qty = new BigDecimal("0");
            if (dataRows.isNotEmpty()) {
                writeoffamount = dataRows.get(0).getBigDecimal("writeoffamount");
                invoiceamount = dataRows.get(0).getBigDecimal("invoiceamount");
                amount = dataRows.get(0).getBigDecimal("amount");
                defaultamount = dataRows.get(0).getBigDecimal("defaultamount");
                saleamount = dataRows.get(0).getBigDecimal("saleamount");
                qty = dataRows.get(0).getBigDecimal("qty");
            }
            row.put("amount", amount);
            row.put("defaultamount", defaultamount);
            row.put("saleamount", saleamount);
            row.put("qty", qty);
            row.put("writeoffamount", writeoffamount);
            row.put("invoiceamount", invoiceamount);

            if (writeoffamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("writeoffstatus", "未核销");
            } else if (writeoffamount.compareTo(amount) == 0) {
                row.put("writeoffstatus", "已核销");
            } else {
                row.put("writeoffstatus", "部分核销");
            }
            if (invoiceamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("invoicestatus", "未开票");
            } else if (invoiceamount.compareTo(amount) == 0) {
                row.put("invoicestatus", "已开票");
            } else {
                row.put("invoicestatus", "部分开票");
            }
        }
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        Long saler_hrid = row.getLong("saler_hrid");
        //开票信息
        sqlFactory = new SQLFactory(this, "订单_开票信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows financeRows = dbConnect.runSqlQuery(sqlFactory);
        row.put("finance", financeRows.isNotEmpty() ? financeRows.get(0) : new Row());

        //营销账户类型
        sqlFactory = new SQLFactory(this, "订单-查询营销账号");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("pay_enterpriseid", pay_enterpriseid);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        String sql = sqlFactory.getSQL();
        Rows accountclassRows = dbConnect.runSqlQuery(sql);
        if (accountclassRows.isNotEmpty()) {
            accountclassRows.get(0).put("amount", row.getBigDecimal("amount"));
            accountclassRows.get(0).put("rebate_used", row.getBigDecimal("rebate_used"));
            accountclassRows.get(0).put("rebate_userate", row.getBigDecimal("rebate_userate"));
            accountclassRows.get(0).put("rebate_amount", row.getBigDecimal("amount").multiply(row.getBigDecimal("rebate_userate")));
        }
        row.put("accountclass", accountclassRows.isNotEmpty() ? accountclassRows.get(0) : new Row());
        //收货信息
        row.put("contacts", beans.order.Order.getContactsRow(this, row.getLong("rec_contactsid")));

        Rows deliveryRows = dbConnect.runSqlQuery("SELECT count(0) count from plm_item WHERE itemid in (SELECT itemid from sa_orderitems WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "') and delivery >0 and siteid = '" + siteid + "'");
        if (deliveryRows.get(0).getLong("count") > 0) {
            row.put("isdelivery", 1);
        } else {
            row.put("isdelivery", 0);
        }

        row.put("rebatebalance", Accountbalance.getRebateBalance(this, pay_enterpriseid));

//        //查询业务员是否可编辑价格
//        Rows saleRows = dbConnect.runSqlQuery("SELECT * from sa_salearea_hr WHERE hrid = " + hrid + " and siteid='" + siteid + "'");
        //查询定制费用
        sql = "SELECT ifnull(sum(costamount),0) costamount from sa_order_cost WHERE sa_orderid=" + sa_orderid;
        BigDecimal costamount = dbConnect.runSqlQuery(0, sql).getBigDecimal("costamount");
        row.put("costamount", costamount);

        JSONArray jsonArray = row.getJSONArray("sa_accountclassinfos");
        int index = 1;
        for (Object object : jsonArray) {

            JSONObject jsonObject = (JSONObject) object;
            Long id = jsonObject.getLong("sa_accountclassid");
            Rows accountbalanceRows = dbConnect.runSqlQuery("SELECT balance from sa_accountbalance WHERE sa_accountclassid=" + id + " and sys_enterpriseid=" + row.getLong("sys_enterpriseid"));
            if (accountbalanceRows.isNotEmpty()) {
                jsonObject.put("realbalance", accountbalanceRows.get(0).getBigDecimal("balance"));
            }
            if (jsonArray.size() == index) {
                jsonObject.put("amount", jsonObject.getBigDecimal("amount").add(costamount));
            }
            index++;
        }
        row.replace("sa_accountclassinfos", jsonArray);
        row.replace("amount", row.getBigDecimal("amount").add(costamount));
        row.replace("defaultamount", row.getBigDecimal("defaultamount").add(costamount));

        //订单套餐
        Rows packagetypemxrows = dbConnect.runSqlQuery("select t2.packagetypemx,min(t1.qty/t2.orderminqty) packagetypeqty 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 ifnull(t2.packagetypemx,'')!='' group by t2.packagetypemx");
        row.put("packagetypemxrows", packagetypemxrows);
        return getSucReturnObject().setData(row).toString();
    }

    @API(title = "查询订单列表(管理端)", apiversion = R.ID20230105143002.v1.class)
    @CACHEING
    public String selectListManage() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
//                where.append("or t5.erpbillno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                JSONArray jsonArray = whereObject.getJSONArray("status");
                if (!jsonArray.isEmpty()) {
                    StringBuffer str = new StringBuffer("(");
                    for (Object o : jsonArray) {
                        str.append("'" + (String) o + "',");
                    }
                    String result = str.substring(0, str.length() - 1);
                    result = result + ")";
                    where.append(" and(");
                    where.append("t1.status in" + result);
                    where.append(")");
                }

            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type ='").append(whereObject.getString("type")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("notclose") && !"".equals(whereObject.getString("notclose"))) {
                if (whereObject.getBoolean("notclose")) {
                    where.append(" and(");
                    where.append("t1.status !='关闭' ");
                    where.append(")");
                }
            }
            if (whereObject.containsKey("bzdd") && !"".equals(whereObject.getString("bzdd"))) {
                if (whereObject.getBoolean("bzdd")) {
                    where.append(" and(");
                    where.append("t1.type ='标准订单' ");
                    where.append(")");
                }
            }
            //品牌
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where.append(" and(");
                where.append("t3.brandname like'%").append(whereObject.getString("brandname")).append("%' ");
                where.append(")");
            }
            //领域
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and(");
                where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("begindate1") && !"".equals(whereObject.getString("begindate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate1")).append("' ");
            }
            if (whereObject.containsKey("enddate1") && !"".equals(whereObject.getString("enddate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate1")).append("' ");
            }
            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate2")).append("' ");
            }
            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate2")).append("' ");
            }
            if (whereObject.containsKey("remarks") && !"".equals(whereObject.getString("remarks"))) {
                where.append("and t1.remarks like'%").append(whereObject.getString("remarks")).append("%' ");
            }
            if (whereObject.containsKey("createby") && !"".equals(whereObject.getString("createby"))) {
                where.append("and t1.createby like'%").append(whereObject.getString("createby")).append("%' ");
            }
            if (whereObject.containsKey("checkby") && !"".equals(whereObject.getString("checkby"))) {
                where.append("and t1.checkby like'%").append(whereObject.getString("checkby")).append("%' ");
            }
            if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) {
                where.append(" and(");
                where.append(" t2.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append("or t7.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append(")");
            }

//            if (whereObject.containsKey("writeoffstatus") && !"".equals(whereObject.getString("writeoffstatus"))) {
//                String writeoffstatus = whereObject.getStringValue("writeoffstatus");
//                if (writeoffstatus.equals("未核销")) {
//                    where.append(" and t7.writeoffamount = 0");
//                } else if (writeoffstatus.equals("已核销")) {
//                    where.append(" and t7.writeoffamount = t7.amount and t7.amount > 0");
//                } else {
//                    where.append(" and t7.writeoffamount < t7.amount and t7.writeoffamount > 0");
//                }
//
//            }
//            if (whereObject.containsKey("invoicestatus") && !"".equals(whereObject.getString("invoicestatus"))) {
//                String invoicestatus = whereObject.getStringValue("invoicestatus");
//                if (invoicestatus.equals("未开票")) {
//                    where.append(" and t7.invoiceamount = 0");
//                } else if (invoicestatus.equals("已开票")) {
//                    where.append(" and t7.invoiceamount = t7.amount and t7.amount > 0");
//                } else {
//                    where.append(" and t7.invoiceamount < t7.amount and t7.invoiceamount > 0");
//                }
//
//            }
            if (whereObject.containsKey("isreturn") && !"".equals(whereObject.getString("isreturn"))) {
                String isreturn = whereObject.getStringValue("isreturn");
                if (isreturn.equals("1")) {
                    where.append(" and exists (select 1 from sa_orderitems where sa_orderid=t1.sa_orderid and siteid=t1.siteid and ifnull(returnqty,0)>0) ");
                } else {
                    where.append(" and not exists (select 1 from sa_orderitems where sa_orderid=t1.sa_orderid and siteid=t1.siteid and ifnull(returnqty,0)=0) ");
                }
            }
            if (whereObject.containsKey("ischange") && !"".equals(whereObject.getString("ischange"))) {
                String ischange = whereObject.getStringValue("ischange");
                if (ischange.equals("0")) {
                    where.append(" and not exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)");
                } else {
                    where.append(" and exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)");
                }
            }
            if (whereObject.containsKey("tobeclosebyhand") && !"".equals(whereObject.getString("tobeclosebyhand"))) {
                String tobeclosebyhand = whereObject.getStringValue("tobeclosebyhand");
                if (tobeclosebyhand.equals("1")) {
                    where.append(" and  t1.status !='手工关闭' and sa_orderid in (select sa_orderid from sa_orderitems t1 left 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)  and t1.isfreeze=1) and sa_orderid in(select t1.sa_orderid from (select sa_orderid,t1.siteid,count(1) count 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.isfreeze=0 and  t1.qty=t2.sumoutwarehouseqty group by sa_orderid,t1.siteid) t1 inner join (select sa_orderid,t1.siteid,count(1) count from sa_orderitems t1 where t1.isfreeze=0 group by sa_orderid,t1.siteid) t2 on t1.siteid=t2.siteid and t1.sa_orderid=t2.sa_orderid where t1.count=t2.count)");
                } else {
                    where.append(" and 1=1");
                }
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "订单(管理端)_列表", pageSize, pageNumber, pageSorting);
//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "订单(管理端)_列表");
//        }
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("hrid", hrid);
//        sqlFactory.addParameter("userid", userid);
//        sqlFactory.addParameter_SQL("where", where);
//        String sql = sqlFactory.getSQL();
        QuerySQL querySQL = queryListManage(where.toString());
        querySQL.setOrderBy(pageSorting);
        Rows rows;
        querySQL.setPage(pageSize, pageNumber);
        rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("sa_orderid", new ArrayList<>());
        ids.add(0l);
        //查询数量金额
        RowsMap OrderAmountRowsMap = beans.order.Order.getSumOrderAmount(this, ids);

//        SQLFactory ischangeSQl = new SQLFactory("sql:SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in $sa_orderids$ ");
//        ischangeSQl.addParameter_in("sa_orderids", rows.toArrayList("sa_orderid"));
        ArrayList<Long> ischangesa_orderidList = new ArrayList<>();
        if (rows.toArrayList("sa_orderid").size() > 0) {
            ischangesa_orderidList = dbConnect.runSqlQuery("SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in" + rows.toArrayList("sa_orderid").toString().replace("[", "(").replace("]", ")")).toArrayList("sa_orderid", new ArrayList<>());
        }


        for (Row row : rows) {
            Long id = row.getLong("sa_orderid");
            if (OrderAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("amount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("amount"));
                row.put("defaultamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("defaultamount"));
                row.put("qty", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("qty"));
                row.put("writeoffamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("writeoffamount"));
                row.put("invoiceamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("invoiceamount"));
                if (OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("returnqty").compareTo(BigDecimal.ZERO) > 0) {
                    row.put("isreturn", "1");
                } else {
                    row.put("isreturn", "0");
                }
            } else {
                row.put("amount", 0);
                row.put("defaultamount", 0);
                row.put("sumamount", 0);
                row.put("qty", 0);
                row.put("writeoffamount", 0);
                row.put("invoiceamount", 0);
                row.put("isreturn", "0");
            }
            BigDecimal writeoffamount = row.getBigDecimal("writeoffamount");
            BigDecimal invoiceamount = row.getBigDecimal("invoiceamount");
            BigDecimal amount = row.getBigDecimal("amount");
            row.put("ischange", ischangesa_orderidList.contains(row.getLong("sa_orderid")) ? "1" : "0");

            if (writeoffamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("writeoffstatus", "未核销");
            } else if (writeoffamount.compareTo(amount) == 0) {
                row.put("writeoffstatus", "已核销");
            } else {
                row.put("writeoffstatus", "部分核销");
            }
            if (invoiceamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("invoicestatus", "未开票");
            } else if (invoiceamount.compareTo(amount) == 0) {
                row.put("invoicestatus", "已开票");
            } else {
                row.put("invoicestatus", "部分开票");
            }
            //收货信息
            row.put("contacts", beans.order.Order.getContactsRow(this, row.getLong("rec_contactsid")));
        }


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

    @API(title = "订单批处理", apiversion = R.ID2025032809311903.v1.class)
    @CACHEING
    public String query_Order() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
//                where.append("or t5.erpbillno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("type")).append("%' ");
                where.append(")");
            }
            //品牌
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where.append(" and(");
                where.append("t3.brandname like'%").append(whereObject.getString("brandname")).append("%' ");
                where.append(")");
            }
            //领域
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and(");
                where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("begindate1") && !"".equals(whereObject.getString("begindate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate1")).append("' ");
            }
            if (whereObject.containsKey("enddate1") && !"".equals(whereObject.getString("enddate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate1")).append("' ");
            }
            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate2")).append("' ");
            }
            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate2")).append("' ");
            }
            if (whereObject.containsKey("remarks") && !"".equals(whereObject.getString("remarks"))) {
                where.append("and t1.remarks like'%").append(whereObject.getString("remarks")).append("%' ");
            }
            if (whereObject.containsKey("createby") && !"".equals(whereObject.getString("createby"))) {
                where.append("and t1.createby like'%").append(whereObject.getString("createby")).append("%' ");
            }
            if (whereObject.containsKey("checkby") && !"".equals(whereObject.getString("checkby"))) {
                where.append("and t1.checkby like'%").append(whereObject.getString("checkby")).append("%' ");
            }
            if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) {
                where.append(" and(");
                where.append(" t2.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append("or t7.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append(")");
            }

//            if (whereObject.containsKey("writeoffstatus") && !"".equals(whereObject.getString("writeoffstatus"))) {
//                String writeoffstatus = whereObject.getStringValue("writeoffstatus");
//                if (writeoffstatus.equals("未核销")) {
//                    where.append(" and t7.writeoffamount = 0");
//                } else if (writeoffstatus.equals("已核销")) {
//                    where.append(" and t7.writeoffamount = t7.amount and t7.amount > 0");
//                } else {
//                    where.append(" and t7.writeoffamount < t7.amount and t7.writeoffamount > 0");
//                }
//
//            }
//            if (whereObject.containsKey("invoicestatus") && !"".equals(whereObject.getString("invoicestatus"))) {
//                String invoicestatus = whereObject.getStringValue("invoicestatus");
//                if (invoicestatus.equals("未开票")) {
//                    where.append(" and t7.invoiceamount = 0");
//                } else if (invoicestatus.equals("已开票")) {
//                    where.append(" and t7.invoiceamount = t7.amount and t7.amount > 0");
//                } else {
//                    where.append(" and t7.invoiceamount < t7.amount and t7.invoiceamount > 0");
//                }
//
//            }
            if (whereObject.containsKey("isreturn") && !"".equals(whereObject.getString("isreturn"))) {
                String isreturn = whereObject.getStringValue("isreturn");
                if (isreturn.equals("1")) {
                    where.append(" and exists (select 1 from sa_orderitems where sa_orderid=t1.sa_orderid and siteid=t1.siteid and ifnull(returnqty,0)>0) ");
                } else {
                    where.append(" and not exists (select 1 from sa_orderitems where sa_orderid=t1.sa_orderid and siteid=t1.siteid and ifnull(returnqty,0)=0) ");
                }
            }
            if (whereObject.containsKey("ischange") && !"".equals(whereObject.getString("ischange"))) {
                String ischange = whereObject.getStringValue("ischange");
                if (ischange.equals("0")) {
                    where.append(" and not exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)");
                } else {
                    where.append(" and exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)");
                }
            }
            if (whereObject.containsKey("tobeclosebyhand") && !"".equals(whereObject.getString("tobeclosebyhand"))) {
                String tobeclosebyhand = whereObject.getStringValue("tobeclosebyhand");
                if (tobeclosebyhand.equals("1")) {
                    where.append(" and  t1.status !='手工关闭' and sa_orderid in (select sa_orderid from sa_orderitems t1 left 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)  and t1.isfreeze=1) and sa_orderid in(select t1.sa_orderid from (select sa_orderid,t1.siteid,count(1) count 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.isfreeze=0 and  t1.qty=t2.sumoutwarehouseqty group by sa_orderid,t1.siteid) t1 inner join (select sa_orderid,t1.siteid,count(1) count from sa_orderitems t1 where t1.isfreeze=0 group by sa_orderid,t1.siteid) t2 on t1.siteid=t2.siteid and t1.sa_orderid=t2.sa_orderid where t1.count=t2.count)");
                } else {
                    where.append(" and 1=1");
                }
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "订单(管理端)_列表", pageSize, pageNumber, pageSorting);
//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "订单(管理端)_列表");
//        }
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("hrid", hrid);
//        sqlFactory.addParameter("userid", userid);
//        sqlFactory.addParameter_SQL("where", where);
//        String sql = sqlFactory.getSQL();
        QuerySQL querySQL = queryListManage1(where.toString());
        querySQL.setOrderBy(pageSorting);
        Rows rows;
        querySQL.setPage(pageSize, pageNumber);
        rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("sa_orderid", new ArrayList<>());
        //查询数量金额
        RowsMap OrderAmountRowsMap = beans.order.Order.getSumOrderAmount(this, ids);

//        SQLFactory ischangeSQl = new SQLFactory("sql:SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in $sa_orderids$ ");
//        ischangeSQl.addParameter_in("sa_orderids", rows.toArrayList("sa_orderid"));
        ArrayList<Long> sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>());
        sa_orderids.add(0l);
        ArrayList<Long> ischangesa_orderidList = dbConnect.runSqlQuery("SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in" + sa_orderids.toString().replace("[", "(").replace("]", ")")).toArrayList("sa_orderid", new ArrayList<>());

        for (Row row : rows) {
            Long id = row.getLong("sa_orderid");
            if (OrderAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("amount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("amount"));
                row.put("defaultamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("defaultamount"));
                row.put("qty", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("qty"));
                row.put("writeoffamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("writeoffamount"));
                row.put("invoiceamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("invoiceamount"));
                if (OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("returnqty").compareTo(BigDecimal.ZERO) > 0) {
                    row.put("isreturn", "1");
                } else {
                    row.put("isreturn", "0");
                }
            } else {
                row.put("amount", 0);
                row.put("defaultamount", 0);
                row.put("sumamount", 0);
                row.put("qty", 0);
                row.put("writeoffamount", 0);
                row.put("invoiceamount", 0);
                row.put("isreturn", "0");
            }
            BigDecimal writeoffamount = row.getBigDecimal("writeoffamount");
            BigDecimal invoiceamount = row.getBigDecimal("invoiceamount");
            BigDecimal amount = row.getBigDecimal("amount");
            row.put("ischange", ischangesa_orderidList.contains(row.getLong("sa_orderid")) ? "1" : "0");

            if (writeoffamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("writeoffstatus", "未核销");
            } else if (writeoffamount.compareTo(amount) == 0) {
                row.put("writeoffstatus", "已核销");
            } else {
                row.put("writeoffstatus", "部分核销");
            }
            if (invoiceamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("invoicestatus", "未开票");
            } else if (invoiceamount.compareTo(amount) == 0) {
                row.put("invoicestatus", "已开票");
            } else {
                row.put("invoicestatus", "部分开票");
            }

            //收货信息
            row.put("contacts", beans.order.Order.getContactsRow(this, row.getLong("rec_contactsid")));

        }


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


    @API(title = "查询订单列表(区域经理)", apiversion = R.ID20231025160203.v1.class)
    @CACHEING
    public String selectListManage_regionalManager() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 and t1.status !='新建' ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
//                where.append("or t5.erpbillno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("type")).append("%' ");
                where.append(")");
            }
            //品牌
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where.append(" and(");
                where.append("t3.brandname like'%").append(whereObject.getString("brandname")).append("%' ");
                where.append(")");
            }
            //领域
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and(");
                where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("begindate1") && !"".equals(whereObject.getString("begindate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate1")).append("' ");
            }
            if (whereObject.containsKey("enddate1") && !"".equals(whereObject.getString("enddate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate1")).append("' ");
            }
            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate2")).append("' ");
            }
            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate2")).append("' ");
            }
            if (whereObject.containsKey("remarks") && !"".equals(whereObject.getString("remarks"))) {
                where.append("and t1.remarks like'%").append(whereObject.getString("remarks")).append("%' ");
            }
            if (whereObject.containsKey("createby") && !"".equals(whereObject.getString("createby"))) {
                where.append("and t1.createby like'%").append(whereObject.getString("createby")).append("%' ");
            }
            if (whereObject.containsKey("checkby") && !"".equals(whereObject.getString("checkby"))) {
                where.append("and t1.checkby like'%").append(whereObject.getString("checkby")).append("%' ");
            }
            if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) {
                where.append(" and(");
                where.append(" t2.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append("or t7.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append(")");
            }

            if (whereObject.containsKey("isreturn") && !"".equals(whereObject.getString("isreturn"))) {
                String isreturn = whereObject.getStringValue("isreturn");
                if (isreturn.equals("1")) {
                    where.append(" and exists (select 1 from sa_orderitems where sa_orderid=t1.sa_orderid and siteid=t1.siteid and ifnull(returnqty,0)>0) ");
                } else {
                    where.append(" and not exists (select 1 from sa_orderitems where sa_orderid=t1.sa_orderid and siteid=t1.siteid and ifnull(returnqty,0)=0) ");
                }
            }
            if (whereObject.containsKey("ischange") && !"".equals(whereObject.getString("ischange"))) {
                String ischange = whereObject.getStringValue("ischange");
                if (ischange.equals("0")) {
                    where.append(" and not exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)");
                } else {
                    where.append(" and exists(SELECT * from sa_orderitems_change WHERE sa_orderid = t1.sa_orderid and siteid = t1.siteid)");
                }
            }
        }


        SQLFactory areasqlFactory = new SQLFactory(new promotion(content), "区域经理查询下属营销区域");
        areasqlFactory.addParameter("siteid", siteid);
        areasqlFactory.addParameter("hrid", hrid);
        Rows arearows = dbConnect.runSqlQuery(areasqlFactory);
        ArrayList<Long> arrayList = arearows.toArrayList("sa_saleareaid", new ArrayList<Long>());
        arrayList.addAll(SaleArea.getSubSaleAreaIds(this, arrayList));

        String sql = " and t1.sys_enterpriseid in (select sys_enterpriseid from sys_enterprise_tradefield where sa_saleareaid in " + arrayList + ")";
        sql = sql.replace("[", "(").replace("]", ")");
        where.append(sql);


        QuerySQL querySQL = queryListManage(where.toString());
        querySQL.setOrderBy(pageSorting);
        Rows rows;
        querySQL.setPage(pageSize, pageNumber);
        rows = querySQL.query();

        ArrayList<Long> ids = rows.toArrayList("sa_orderid", new ArrayList<>());
        //查询数量金额
        RowsMap OrderAmountRowsMap = beans.order.Order.getSumOrderAmount(this, ids);

//        SQLFactory ischangeSQl = new SQLFactory("sql:SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in $sa_orderids$ ");
//        ischangeSQl.addParameter_in("sa_orderids", rows.toArrayList("sa_orderid"));
        ArrayList<Long> ischangesa_orderidList = dbConnect.runSqlQuery("SELECT distinct sa_orderid from sa_orderitems_change WHERE siteid='" + siteid + "' and sa_orderid in " + rows.toArrayList("sa_orderid").toString().replace("[", "(").replace("]", ")")).toArrayList("sa_orderid", new ArrayList<>());

        for (Row row : rows) {
            Long id = row.getLong("sa_orderid");
            if (OrderAmountRowsMap.get(id.toString()).isNotEmpty()) {
                row.put("amount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("amount"));
                row.put("defaultamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("defaultamount"));
                row.put("qty", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("qty"));
                row.put("writeoffamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("writeoffamount"));
                row.put("invoiceamount", OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("invoiceamount"));
                if (OrderAmountRowsMap.get(id.toString()).get(0).getBigDecimal("returnqty").compareTo(BigDecimal.ZERO) > 0) {
                    row.put("isreturn", "1");
                } else {
                    row.put("isreturn", "0");
                }
            } else {
                row.put("amount", 0);
                row.put("defaultamount", 0);
                row.put("sumamount", 0);
                row.put("qty", 0);
                row.put("writeoffamount", 0);
                row.put("invoiceamount", 0);
                row.put("isreturn", "0");
            }
            BigDecimal writeoffamount = row.getBigDecimal("writeoffamount");
            BigDecimal invoiceamount = row.getBigDecimal("invoiceamount");
            BigDecimal amount = row.getBigDecimal("amount");
            row.put("ischange", ischangesa_orderidList.contains(row.getLong("sa_orderid")) ? "1" : "0");

            if (writeoffamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("writeoffstatus", "未核销");
            } else if (writeoffamount.compareTo(amount) == 0) {
                row.put("writeoffstatus", "已核销");
            } else {
                row.put("writeoffstatus", "部分核销");
            }
            if (invoiceamount.compareTo(BigDecimal.ZERO) == 0) {
                row.put("invoicestatus", "未开票");
            } else if (invoiceamount.compareTo(amount) == 0) {
                row.put("invoicestatus", "已开票");
            } else {
                row.put("invoicestatus", "部分开票");
            }

        }


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

    //查询订单列表(管理端)
    public QuerySQL queryListManage(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order",
                "sa_orderid", "sonum", "type", "status", "submitdate", "checkdate", "remarks", "createby", "checkby", "billdate", "tradefield", "rec_contactsid", "delivery", "tracknumber", "mailno");

        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid and t2.siteid = t1.siteid",
                "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t6.sys_enterpriseid=t1.sys_enterpriseid and t6.siteid = t1.siteid",
                "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sa_promotion", "t7", "t7.sa_promotionid=t1.sa_promotionid and t7.siteid = t1.siteid",
                "promnum", "promname");
        querySQL.addQueryFields("amount", "CAST(0 AS CHAR(20))");
        querySQL.addQueryFields("qty", "CAST(0 AS CHAR(20))");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.deleted=0 and t1.type!='配件订单'");
        querySQL.setWhere("(t1.status!='新建' or t1.createuserid =$userid$ or t1.createflag=1)");
        querySQL.addParameter("userid", userid);
        return querySQL;
    }

    public QuerySQL queryListManage1(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order",
                "sa_orderid", "sonum", "type", "status", "submitdate", "checkdate", "remarks", "createby", "checkby", "billdate", "tradefield", "rec_contactsid", "delivery", "tracknumber", "mailno");

        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid and t2.siteid = t1.siteid",
                "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t6.sys_enterpriseid=t1.sys_enterpriseid and t6.siteid = t1.siteid",
                "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sa_promotion", "t7", "t7.sa_promotionid=t1.sa_promotionid and t7.siteid = t1.siteid",
                "promnum", "promname");
        querySQL.addQueryFields("amount", "CAST(0 AS CHAR(20))");
        querySQL.addQueryFields("qty", "CAST(0 AS CHAR(20))");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.deleted=0 and t1.type!='配件订单' and t1.status in('新建','提交') and t1.createflag=0");
        return querySQL;
    }

    @API(title = "查询订单列表(业务员)", apiversion = R.ID20221111145202.v1.class)
    public String selectList() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            //状态
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status like'%").append(whereObject.getString("status")).append("%' ");
                where.append(")");
            }
            //品牌
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where.append(" and(");
                where.append("t3.brandname like'%").append(whereObject.getString("brandname")).append("%' ");
                where.append(")");
            }
            //领域
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where.append(" and(");
                where.append("t1.tradefield like'%").append(whereObject.getString("tradefield")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("type")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("begindate1") && !"".equals(whereObject.getString("begindate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate1")).append("' ");
            }
            if (whereObject.containsKey("enddate1") && !"".equals(whereObject.getString("enddate1"))) {
                where.append(" and DATE_FORMAT(t1.submitdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate1")).append("' ");
            }
            if (whereObject.containsKey("begindate2") && !"".equals(whereObject.getString("begindate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate2")).append("' ");
            }
            if (whereObject.containsKey("enddate2") && !"".equals(whereObject.getString("enddate2"))) {
                where.append(" and DATE_FORMAT(t1.checkdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate2")).append("' ");
            }
            if (whereObject.containsKey("remarks") && !"".equals(whereObject.getString("remarks"))) {
                where.append("and t1.remarks like'%").append(whereObject.getString("remarks")).append("%' ");
            }
            if (whereObject.containsKey("agentinfo") && !"".equals(whereObject.getString("agentinfo"))) {
                where.append(" and(");
                where.append(" t2.enterprisename like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append("or t7.agentnum like'%").append(whereObject.getString("agentinfo")).append("%' ");
                where.append(")");
            }

//            if (whereObject.containsKey("writeoffstatus") && !"".equals(whereObject.getString("writeoffstatus"))) {
//                String writeoffstatus = whereObject.getStringValue("writeoffstatus");
//                if (writeoffstatus.equals("未核销")) {
//                    where.append(" and t7.writeoffamount = 0");
//                } else if (writeoffstatus.equals("已核销")) {
//                    where.append(" and t7.writeoffamount = t7.amount and t7.amount > 0");
//                } else {
//                    where.append(" and t7.writeoffamount < t7.amount and t7.writeoffamount > 0");
//                }
//
//            }
//            if (whereObject.containsKey("invoicestatus") && !"".equals(whereObject.getString("invoicestatus"))) {
//                String invoicestatus = whereObject.getStringValue("invoicestatus");
//                if (invoicestatus.equals("未开票")) {
//                    where.append(" and t7.invoiceamount = 0");
//                } else if (invoicestatus.equals("已开票")) {
//                    where.append(" and t7.invoiceamount = t7.amount and t7.amount > 0");
//                } else {
//                    where.append(" and t7.invoiceamount < t7.amount and t7.invoiceamount > 0");
//                }
//
//            }
        }

        ArrayList<Long> sys_enterpriseids = Hr.getAgents(this, hrid);
        String sql = " and t1.sys_enterpriseid in " + sys_enterpriseids;
        sql = sql.replace("[", "(").replace("]", ")");
        where.append(sql);
        boolean isExport = content.getBooleanValue("isExport");
//
//
//        SQLFactory sqlFactory = new SQLFactory(this, "订单_列表", pageSize, pageNumber, pageSorting);
//        if (isExport) {
//            sqlFactory = new SQLFactory(this, "订单_列表");
//        }
//
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("hrid", hrid);
//        sqlFactory.addParameter("userid", userid);
//        sqlFactory.addParameter_SQL("where", where);
//        sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        QuerySQL querySQL = queryList(where.toString());
        querySQL.setOrderBy(pageSorting);
        Rows rows;
//        if (!isExport) {
        querySQL.setPage(pageSize, pageNumber);
//        }
        rows = querySQL.query();
        //查询数据：amount，defaultamount，qty
        ArrayList<Long> sa_orderids = rows.toArrayList("sa_orderid", new ArrayList<>());
        SQLFactory sqlFactory = new SQLFactory(this, "订单_查询数据");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderid", sa_orderids);
        RowsMap dataRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_orderid");
        for (Row row : rows) {
            Rows dataRows = dataRowsMap.getOrDefault(row.getString("sa_orderid"), new Rows());
            if (dataRows.isNotEmpty()) {
                row.putAll(dataRows.get(0));
            } else {
                row.put("amount", 0);
                row.put("defaultamount", 0);
                row.put("qty", 0);
            }

        }

//        if (isExport) {
//            //去除不需要导出项
//            rows.getFieldList().remove("sa_orderid");
//            rows.getFieldList().remove("submitdate");
//            rows.getFieldList().remove("checkdate");
//            rows.getFieldList().remove("typemx");
//            rows.getFieldList().remove("sys_enterpriseid");
//            Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }

        sqlFactory = new SQLFactory(this, "订单-查询筛选出的价格");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseids);
        sqlFactory.addParameter_SQL("where", where);
        Rows amountrows = dbConnect.runSqlQuery(sqlFactory);

        return getSucReturnObject().setData(rows).setTips(amountrows.get(0).toJsonObject()).toString();
    }


    //查询订单列表(业务员)
    public QuerySQL queryList(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_order", "*");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t2.sys_enterpriseid = t1.sys_enterpriseid and t2.siteid = t1.siteid",
                "enterprisename", "abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "sa_brand", "t3", "t3.sa_brandid = t1.sa_brandid",
                "brandname");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t4", "t4.contactsid = t1.rec_contactsid and t4.siteid = t1.siteid",
                "name", "province", "city", "county", "address", "phonenumber");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t5", "t5.sys_enterpriseid=t1.sys_enterpriseid and t5.siteid = t1.siteid",
                "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_hr", "t6", "t6.hrid = t1.saler_hrid and t6.siteid = t1.siteid");
        //设置别名
        querySQL.addQueryFields("salename", "t6.name");
        querySQL.addJoinTable(JOINTYPE.left, "sa_project", "t7", "t7.sa_projectid = t1.sa_projectid and t7.siteid = t1.siteid",
                "projectname");
        querySQL.addQueryFields("amount", "CAST(0 AS CHAR(20))");
        querySQL.addQueryFields("qty", "CAST(0 AS CHAR(20))");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.type!='工具借用单' and t1.type!='配件订单'");
        querySQL.setWhere("t1.deleted=0");
        return querySQL;
    }


    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("sonum", "订单编号");
        titleMap.put("brandname", "品牌名称");
        titleMap.put("type", "订单类型");
        titleMap.put("tradefield", "领域");
        titleMap.put("remarks", "备注");
        titleMap.put("status", "状态");
        titleMap.put("createdate", "创建时间");
        titleMap.put("enterprisename", "经销商名称");
        titleMap.put("abbreviation", "简称");
        titleMap.put("name", "收货人");
        titleMap.put("amount", "订单金额");
        titleMap.put("qty", "订单数量");

        return titleMap;
    }

    @API(title = "删除", apiversion = R.ID20221108152102.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String delete() throws YosException {
        JSONArray sa_orderids = content.getJSONArray("sa_orderids");

        if (sa_orderids.size() <= 0) {
            return getErrReturnObject().setErrMsg("请选择要执行删除操作的数据。").toString();
        }

        String sql = "SELECT 1 from sa_order WHERE sa_orderid in " + sa_orderids + " and `status` !='新建' and siteid = '" + siteid + "'";
        sql = sql.replace("[", "(").replace("]", ")");
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在非新建状态数据，无法执行删除操作。").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactory = new SQLFactory(this, "订单_删除");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sa_orderid", sa_orderids.toArray());
        sqlList.add(sqlFactory.getSQL());
        //订单删除原因
        DataExtend.createDeleteReasonSql(this, "sa_order", sa_orderids.toJavaList(Long.class), content.getStringValue("deletereason"));
        //操作记录

        for (Object obj : sa_orderids) {
            Long sa_orderid = Long.valueOf(obj.toString());
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "删除", "订单删除").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        ClearData.clearOrder(this);
        return getSucReturnObject().toString();
    }


    @API(title = "更新项目备注，备注，销售类别，摘要", apiversion = R.ID20230301152702.v1.class)
    public String update() throws YosException {

        Long sa_orderid = content.getLong("sa_orderid");
        String projectnote = content.getStringValue("projectnote");
        String remarks = content.getStringValue("remarks");
        String saletype = content.getStringValue("saletype");


        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.get(0).getString("status").equals("关闭")) {
            return getErrReturnObject().setErrMsg("关闭状态无法修改").toString();
        }

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

        SQLFactory sqlFactory = new SQLFactory(this, "更新订单项目备注等信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("projectnote", projectnote);
        sqlFactory.addParameter("remarks", remarks);
        sqlFactory.addParameter("saletype", saletype);
        sqlFactory.addParameter("abstract", content.getStringValue("abstract"));
        dbConnect.runSqlUpdate(sqlFactory);


        return selectDetail();
    }

    @API(title = "更新订单明细分类", apiversion = R.ID20230706094703.v1.class)
    public String updateTypemx() throws YosException {

        Long sa_orderid = content.getLong("sa_orderid");
        String typemx = content.getStringValue("typemx");

        Rows orderRows = getOrderRows(this, sa_orderid);
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单数据不存在").toString();
        }
        String status = orderRows.get(0).getString("status");
        if (!(status.equals("预提交") || status.equals("确认") || status.equals("提交"))) {
            return getErrReturnObject().setErrMsg("非预提交、提交、确认状态无法修改").toString();
        }

        SQLFactory sqlFactory = new SQLFactory(this, "更新订单明细分类");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("typemx", typemx);
        dbConnect.runSqlUpdate(sqlFactory);


        return selectDetail();
    }


    @API(title = "使用返利金", apiversion = R.ID20230218225002.v1.class)
    public String isUseRate() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        boolean isused = content.getBooleanValue("isused");
        BigDecimal rebateamount = content.getBigDecimal("rebateamount");
        if (rebateamount.compareTo(BigDecimal.ZERO) == 0) {
            rebateamount = getMaxUsedRebateAmount(this, sa_orderid);
        }

        ArrayList<String> sqlList = new ArrayList<>();

        if (!Parameter.getBoolean(siteid, "order_rebate_used")) {
            return getErrReturnObject().setErrMsg("站点未开启返利金，无法使用返利金").toString();
        }

        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("未找到订单数据，无法使用返利金").toString();
        }

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

        Row orderRow = rows.get(0);
        String type = orderRow.getString("type");
        if (type.equals("标准订单") || type.equals("特殊订单")) {
            //先初始化返利金价格
            sqlList.add(beans.order.Order.updateRateInfo(this, sa_orderid, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));
            sqlList.add("UPDATE sa_orderitems SET amount = defaultamount ,price = defaultprice ,rebateamount = 0  WHERE sa_orderid = " + sa_orderid + " and siteid='" + siteid + "'");
            dbConnect.runSqlUpdate(sqlList);
            sqlList.clear();
            //使用返利金
            if (isused) {
                //站点返利金比例
                BigDecimal order_rebate_userate = BigDecimal.valueOf(Parameter.getDouble(siteid, "order_rebate_userate"));
                //订单金额
                BigDecimal defaultAmount = getDefaultAmount(this, sa_orderid);

                //返利金最大使用金额
                BigDecimal maxAmount = defaultAmount.multiply(order_rebate_userate).divide(BigDecimal.valueOf(1));
                if (rebateamount.compareTo(maxAmount) == 1) {
                    return getErrReturnObject().setErrMsg("超过最大使用金度:" + maxAmount).toString();
                }
                Long pay_enterpriseid = orderRow.getLong("pay_enterpriseid");
                if (pay_enterpriseid <= 0) {
                    return getErrReturnObject().setErrMsg("请选择结算企业").toString();
                }
                //当前返利金额度
                BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, pay_enterpriseid);
                if (rebateamount.compareTo(rebateBalance) == 1) {
                    return getErrReturnObject().setErrMsg("超出返利金账户余额，可用余额为:" + rebateBalance).toString();
                }
                BigDecimal rebate_userate;
                if (defaultAmount.compareTo(BigDecimal.ZERO) == 0) {
                    rebate_userate = BigDecimal.ZERO;
                } else {
                    rebate_userate = rebateamount.divide(defaultAmount, 6, BigDecimal.ROUND_CEILING);
                }
                sqlList.add(beans.order.Order.updateRateInfo(this, sa_orderid, BigDecimal.valueOf(1), rebate_userate, rebateamount));
                dbConnect.runSqlUpdate(sqlList);
//                //更新订单商品数据
                setRebateAmount(this, sa_orderid, rebateamount);
                adjustLastRebateAmount(this, sa_orderid);

            }

        } else {
            return getErrReturnObject().setErrMsg("非标准订单或特殊订单无法使用返利金").toString();
        }

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


    @API(title = "提交", apiversion = R.ID20221108153402.v1.class, intervaltime = 5000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class, promotion.class, restcontroller.webmanage.sale.promotion.promotion.class, accessoryorder.class})
    public String submit() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");

        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        Row row = rows.get(0);
        String type = row.getString("type");
        String typemx = row.getString("typemx");
        ArrayList<String> sqllist = new ArrayList<>();
        if (type.equals("配件订单")) {
            Rows rowsdetails = dbConnect.runSqlQuery("select t1.price,t1.amount,t1.sa_orderitemsid,t1.itemid,ifnull(t2.iswriteoff,0) iswriteoff,t3.begdate,t3.enddate from sa_orderitems t1 " +
                    "left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid " +
                    "left join sa_warrantycard t3 on t1.cardno=t3.cardno and t1.siteid=t3.siteid and t3.isvoid=0 and t3.deleted=0 where t1.sa_orderid=" + sa_orderid);
            for (Row rowsdetail : rowsdetails) {
                String billingstatus = "";
                BigDecimal price = rowsdetail.getBigDecimal("price");
                BigDecimal amount = rowsdetail.getBigDecimal("amount");
                String begdate = rowsdetail.getString("begdate");
                String enddate = rowsdetail.getString("enddate");
                if (StringUtils.isBlank(begdate) || StringUtils.isBlank(enddate)) {
                    billingstatus = "保外收费";
                } else {
                    Date date = new Date();
                    if (date.before(rowsdetail.getDate("enddate"))) {
                        if (rowsdetail.getBoolean("iswriteoff")) {
                            billingstatus = "保内收费";
                        } else {
                            billingstatus = "保内免费";
                            price = BigDecimal.ZERO;
                            amount = BigDecimal.ZERO;
                        }
                    }
                }
                sqllist.add("update sa_orderitems set price=" + price + ",amount=" + amount + ",billingstatus='" + billingstatus + "' where sa_orderitemsid=" + rowsdetail.getLong("sa_orderitemsid"));
            }
        }
        dbConnect.runSqlUpdate(sqllist);
        //付款账户信息
        JSONArray accountclassinfos = row.getJSONArray("sa_accountclassinfos");
        //扣款的金额
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);

        Long sa_accountclassid = row.getLong("sa_accountclassid");
        if (sa_accountclassid <= 0) {
            return getErrReturnObject().setErrMsg("请选择支付账号").toString();
        }
        if (siteid.equalsIgnoreCase("CCYOSG") && !type.equals("促销订单")) {
            if (typemx.equals("工程") || typemx.equals("电商") || typemx.equals("内部")) {

            } else {
                String tradefield = row.getString("tradefield");
                QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_optiontypemx");
                querySQL.addJoinTable(JOINTYPE.inner, "sys_optiontype", "t2", "t2.optiontypeid=t1.optiontypeid");
                querySQL.setWhere("t2.typename='domainrelatedaccounts'");
                querySQL.setWhere("(t1.value='" + tradefield + "' or t1.remarks='" + tradefield + "')");
                querySQL.setTableAlias("t1");
                Rows accountsTows = querySQL.query();
                if (accountsTows.isNotEmpty()) {
                    JSONArray subvalues = accountsTows.get(0).getJSONArray("subvalues");
                    if (!subvalues.contains(sa_accountclassid.toString())) {
                        return getErrReturnObject().setErrMsg("扣款账户不对,禁止提交,请更换支付账户后提交").toString();
                    }
                }
            }

        }

        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        if (sys_enterpriseid <= 0) {
            return getErrReturnObject().setErrMsg("请选择订货企业信息").toString();
        }
        //验证结算企业
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        if (pay_enterpriseid <= 0) {
            dbConnect.runSqlUpdate("UPDATE sa_order set pay_enterpriseid=sys_enterpriseid  WHERE sa_orderid=" + sa_orderid);
//            return getErrReturnObject().setErrMsg("请选择结算企业").toString();
        }
        Long sys_enterprise_financeid = row.getLong("sys_enterprise_financeid");
        if (sys_enterprise_financeid <= 0) {
            //return getErrReturnObject().setErrMsg("请选择财务信息").toString();
        }
//        //验证收货人信息
//        Long rec_contactsid = row.getLong("rec_contactsid");
//        if (rec_contactsid <= 0) {
//            return getErrReturnObject().setErrMsg("请选择物收货人信息").toString();
//        }
        if (dbConnect.runSqlQuery("SELECT 1 FROM sa_agents WHERE siteid='" + siteid + "' AND `status`='启用' AND sys_enterpriseid=" + sys_enterpriseid).isEmpty()) {
            return getErrReturnObject().setErrMsg("当前企业不存在或已禁用，无法下单").toString();
        }
        Long sa_promotionid = row.getLong("sa_promotionid");
        if (sa_promotionid > 0) {

            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_promotion WHERE  siteid='" + siteid + "' AND sa_promotionid='" + sa_promotionid + "'").isEmpty()) {
                return getErrReturnObject().setErrMsg("该促销活动不存在，无法提交").toString();
            }
            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_promotion WHERE CURRENT_TIME> enddate AND siteid='" + siteid + "' AND sa_promotionid='" + sa_promotionid + "'").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("该促销活动已截止，无法提交").toString();
            }
            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_promotion WHERE status='关闭' AND siteid='" + siteid + "' AND sa_promotionid='" + sa_promotionid + "'").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("该促销活动已结束，无法提交").toString();
            }

            Rows promotionRows = dbConnect.runSqlQuery("SELECT isauth,orderminamount FROM sa_promotion WHERE  siteid='" + siteid + "' AND sa_promotionid='" + sa_promotionid + "'");
            if (promotionRows.isNotEmpty()) {
                if (amount.compareTo(promotionRows.get(0).getBigDecimal("orderminamount")) < 0) {
                    return getErrReturnObject().setErrMsg("订单未满足最低起订金额:" + promotionRows.get(0).getBigDecimal("orderminamount") + ",请勿提交！").toString();
                }
            }
            if (!promotionRows.get(0).getBoolean("isauth")) {
                SQLFactory sqlFactory = new SQLFactory(this, "促销方案经销商授权区域查询");
                sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                sqlFactory.addParameter("siteid", siteid);
                Rows spromotionsaleareaRows = dbConnect.runSqlQuery(sqlFactory);
                RowsMap spromotionsaleareaRowsMap = spromotionsaleareaRows.toRowsMap("sa_promotionid");
                if ((dbConnect.runSqlQuery("SELECT 1 FROM sa_promotion_auth t1 inner join sa_promotion t2 on t1.sa_promotionid=t2.sa_promotionid and t1.siteid=t2.siteid WHERE if(t2.authmethod=1,not exists(select 1 from sa_promotion_auth where sa_promotionid=t1.sa_promotionid and sys_enterpriseid= " + sys_enterpriseid + "),t1.sys_enterpriseid= " + sys_enterpriseid + ") AND t1.siteid='" + siteid + "' AND t1.sa_promotionid='" + sa_promotionid + "'").isEmpty() && !spromotionsaleareaRowsMap.containsKey(String.valueOf(sa_promotionid)))) {
                    return getErrReturnObject().setErrMsg("该促销活动已过期，无法提交").toString();
                }
            }

        }
        if (!((row.getString("status").equals("新建") && !row.getString("type").equals("特殊订单")) || (row.getString("status").equals("确认") && row.getString("type").equals("特殊订单")))) {
            return getErrReturnObject().setErrMsg("新建或非确认无法提交").toString();
        }
        if (dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'").isEmpty()) {
            return getErrReturnObject().setErrMsg("订单表体不存在商品无法提交").toString();
        }

        if (type.equals("标准订单") || type.equals("促销订单")) {
            if (dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' and  (price<=0 or amount<=0 or qty<=0)").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("商品单价或金额禁止小于等于0").toString();
            }
        }
        Rows orderitems = dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' and qty<=0");
        if (orderitems.isNotEmpty()) {
            return getErrReturnObject().setErrMsg("订单表体行号为【" + orderitems.get(0).getLong("rowno") + "】的商品数量为0无法提交").toString();
        }
        OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);

        //标准订单：提交时商品必须是上架状态，否则不允许提交
        if (type.equals("标准订单")) {
            orderItemsHelper.checkOffOrderItems(sa_orderid, "plm_item", sa_promotionid);
        }
        //促销订单：提交时商品必须是上架状态，并且活动方案中的商品必须也是上架状态，否则不允许提交
        if (type.equals("促销订单")) {
            Rows promotionitemsRows = dbConnect.runSqlQuery("select t2.itemno from  sa_promotion_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where  t1.sa_promotionid=" + sa_promotionid + " and t1.siteid='" + siteid + "' and t1.isonsale=0 and t1.itemid in(select itemid from sa_orderitems where sa_orderid=" + sa_orderid + ")");
            if (promotionitemsRows.isNotEmpty()) {
                return getErrReturnObject().setErrMsg("商品：" + promotionitemsRows.get(0).getString("itemno") + "已下架，无法提交。").toString();
            }
            Rows promotionauthRows = dbConnect.runSqlQuery("select * from  sa_promotion_auth where  sa_promotionid=" + sa_promotionid + " and sys_enterpriseid ='" + sys_enterpriseid + "' and siteid='" + siteid + "'");
            if (promotionauthRows.isNotEmpty()) {
                if (promotionauthRows.get(0).getBoolean("settlestatus")) {
                    return getErrReturnObject().setErrMsg("该促销活动已返利完结，无法提交，请删除或联系总部。").toString();
                }
            }
//            orderItemsHelper.checkOffOrderItems(sa_orderid, "plm_item");
            orderItemsHelper.checkOffOrderItems(sa_orderid, "sa_promotion_items", sa_promotionid);
        }

        if (type.equals("标准订单") || type.equals("促销订单")) {
            Rows orderiteminfos = dbConnect.runSqlQuery("select t1.* from sa_orderitems t1  where t1.sa_orderid=" + sa_orderid + " and t1.siteid='" + siteid + "'");
            JSONArray jsonArray = new JSONArray();
            //批量查询商品信息
            for (Row orderiteminfo : orderiteminfos) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("itemid", orderiteminfo.getLong("itemid"));
                jsonArray.add(jsonObject);
            }
            RowsMap itemRowsMap = orderItemsHelper.getItemRowsMap(jsonArray);
            for (Row row1 : orderiteminfos) {
                ItemPrice itemPrice = ItemPrice.getItemPrice(this, rows.get(0).getLong("sys_enterpriseid"), row1.getLong("itemid"));
                if (row1.getBigDecimal("saleprice").compareTo(BigDecimal.ZERO) > 0) {
                    JSONObject item = new JSONObject();
                    item.put("itemid", row1.getString("itemid"));
                    item.put("length", row1.getString("length"));
                    item.put("width", row1.getString("width"));

                    if (orderItemsHelper.getSalePrice(rows.get(0), itemPrice, item, itemRowsMap).compareTo(row1.getBigDecimal("saleprice")) != 0) {
                        return getErrReturnObject().setErrMsg("商品:" + row1.getString("itemno") + "销售价格有更新，请删除后重新添加！").toString();
                    }
                }

            }
        }

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

//        String type = row.getString("type");
        //提交
        String status = "提交";

        Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid);
        BigDecimal freefreightamount = BigDecimal.ZERO;
        if (!enterpriseRow.isEmpty()) {
            freefreightamount = enterpriseRow.getBigDecimal("freefreightamount");
        }
        String freightstatus = row.getString("freightstatus");
        if (freightstatus.isEmpty()) {
            if (freefreightamount.compareTo(BigDecimal.ZERO) == -1) {
                freightstatus = "到付";
            }
            if (freefreightamount.compareTo(BigDecimal.ZERO) == 0) {
                freightstatus = "预付";
            }
            if (freefreightamount.compareTo(BigDecimal.ZERO) == 1) {
                if (amount.compareTo(freefreightamount) == 1) {
                    freightstatus = "预付";
                } else {
                    freightstatus = "到付";
                }
            }
        }
        ArrayList<String> sqlList = new ArrayList<>();
        String sonum = row.getString("sonum");

        Rows orderdetails_items = dbConnect.runSqlQuery("select itemid,sum(qty) qty from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by itemid");
        for (Row orderdetail : orderdetails_items) {
            sqlList.add("update plm_item set saleqty=ifnull(saleqty,0)+" + orderdetail.getBigDecimal("qty") + " where itemid=" + orderdetail.getLong("itemid"));
        }

        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select sa_promotion_itemsid,itemno,sum(qty) qty  from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by sa_promotion_itemsid,itemno");
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + row.getLong("sa_promotionid"));
            RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("sa_promotion_itemsid");
            for (Row orderdetail : orderdetails) {
                String sa_promotion_itemsid = orderdetail.getString("sa_promotion_itemsid");
                BigDecimal qty = orderdetail.getBigDecimal("qty");
                if (promotionitemsRowsMap.containsKey(sa_promotion_itemsid)) {
                    if (promotionitemsRowsMap.get(sa_promotion_itemsid).isNotEmpty()) {
                        BigDecimal saledqty = promotionitemsRowsMap.get(sa_promotion_itemsid).get(0).getBigDecimal("saledqty");
                        BigDecimal groupqty = promotionitemsRowsMap.get(sa_promotion_itemsid).get(0).getBigDecimal("groupqty");
                        boolean islimit = promotionitemsRowsMap.get(sa_promotion_itemsid).get(0).getBoolean("islimit");
                        if (islimit) {
                            if (groupqty.compareTo(saledqty.add(qty)) < 0) {
                                return getErrReturnObject().setErrMsg("品号：" + orderdetail.getString("itemno") + "已超过限购数量：" + groupqty.subtract(saledqty)).toString();
                            }
                        }
                        sqlList.add("update sa_promotion_items set saledqty=ifnull(saledqty,0)+" + qty + " where sa_promotion_itemsid=" + sa_promotion_itemsid);
                    }
                }
            }

            //促销订单经销商限购金额判断
            String promotiontype = "";
            BigDecimal associationamount = BigDecimal.ZERO;
            Rows promotion = dbConnect.runSqlQuery(" select * from sa_promotion where siteid='" + siteid + "'and  sa_promotionid=" + sa_promotionid);
            Rows promotion_auth = dbConnect.runSqlQuery(" select * from sa_promotion_auth where siteid='" + siteid + "'and sys_enterpriseid=" + sys_enterpriseid + " and sa_promotionid=" + sa_promotionid);
            if (promotion.isNotEmpty()) {
                if (promotion.get(0).getString("type").equals("套餐活动")) {
                    ArrayList<Long> sa_promotion_itemsids = new ArrayList<Long>();
                    sa_promotion_itemsids.add((long) 0);
                    for (Row orderdetail : orderdetails) {
                        sa_promotion_itemsids.add(orderdetail.getLong("sa_promotion_itemsid"));
                    }
                    int eachlimitqty = promotion.get(0).getInteger("eachlimitqty");
                    if (promotion.get(0).getBoolean("eachneedadd") && eachlimitqty > 0) {
                        String sql = "select ifnull(packagetypemx,'') packagetypemx,count(1) count from  sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid + " and sa_promotion_itemsid in " + sa_promotion_itemsids + " group by ifnull(packagetypemx,'')";
                        sql = sql.replace("[", "(").replace("]", ")");
                        Rows promotionItemsRows = dbConnect.runSqlQuery(sql);
                        for (Row row1 : promotionItemsRows) {
                            if (StringUtils.isBlank(row1.getString("packagetypemx"))) {
                                return getErrReturnObject().setErrMsg("促销方案维护出错").toString();
                            }
                            if (eachlimitqty != row1.getInteger("count")) {
                                return getErrReturnObject().setErrMsg(row1.getString("packagetypemx") + "套餐未满足限购品数" + eachlimitqty + "的要求，禁止创建订单，请修改后重试！").toString();
                            }
                        }
                    }
                }
                promotiontype = promotion.get(0).getString("type");
                associationamount = promotion.get(0).getBigDecimal("associationamount");
            }
            if (promotion_auth.isNotEmpty()) {
                boolean islimit = promotion_auth.get(0).getBoolean("islimit");
                BigDecimal limitamount = promotion_auth.get(0).getBigDecimal("limitamount");
                BigDecimal saledamount = promotion_auth.get(0).getBigDecimal("saledamount");
                //System.out.println("limitamount:" + limitamount);
                //System.out.println("saledamount:" + saledamount);
                //System.out.println("associationamount:" + associationamount);
                if (islimit) {
                    if (!promotiontype.equals("返利促销")) {
                        if (amount.compareTo(limitamount.subtract(saledamount)) > 0) {
                            return getErrReturnObject().setErrMsg("已超过促销方案限定金额，无法提交").toString();
                        }
                        sqlList.add("update sa_promotion_auth set saledamount=ifnull(saledamount,0)+" + amount + " where sa_promotion_authid=" + promotion_auth.get(0).getLong("sa_promotion_authid"));
                    } else {
                        if (amount.subtract((limitamount.subtract(saledamount))).compareTo(associationamount) > 0) {
                            return getErrReturnObject().setErrMsg("已超过促销方案限定金额，无法提交").toString();
                        }
                        if (amount.compareTo((limitamount.subtract(saledamount))) > 0) {
                            sqlList.add("update sa_promotion_auth set saledamount=ifnull(saledamount,0)+" + (limitamount.subtract(saledamount)) + " where sa_promotion_authid=" + promotion_auth.get(0).getLong("sa_promotion_authid"));
                        } else {
                            sqlList.add("update sa_promotion_auth set saledamount=ifnull(saledamount,0)+" + amount + " where sa_promotion_authid=" + promotion_auth.get(0).getLong("sa_promotion_authid"));
                        }

                    }


                }
            }
        }


        //总的金额
        BigDecimal amountAll = amount;
        BigDecimal costamount_single = BigDecimal.ZERO;
        //查询是否有定制费用，sa_order_cost

        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        //订单返利使用开关
        Boolean rebate_used = row.getBoolean("rebate_used");
        //站点订单返利使用开关
        String order_rebate_used = Parameter.getString(siteid, "order_rebate_used");
        ArrayList<Long> sa_cashbillidList = new ArrayList<>();

        //查询定制费用
        String sql = "SELECT ifnull(sum(costamount),0) costamount from sa_order_cost WHERE sa_orderid=" + sa_orderid;
        BigDecimal costamount = dbConnect.runSqlQuery(0, sql).getBigDecimal("costamount");

        if (order_paymentnode.equals("1")) {
            if (order_rebate_used.equals("1") && rebate_used) {
                //站点返利金比例
                BigDecimal order_rebate_userate = BigDecimal.valueOf(Parameter.getDouble(siteid, "order_rebate_userate"));
                //订单返利金比例
                BigDecimal rebate_userate = row.getBigDecimal("rebate_userate");
                if (rebate_userate.compareTo(order_rebate_userate) == 1) {
                    return getErrReturnObject().setErrMsg("返利金使用比例超出站点设置比例，站点比例为" + order_rebate_userate).toString();
                }
                //查询返利金账户余额
                BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, pay_enterpriseid);
                //返利金金额
                BigDecimal rebateamount = row.getBigDecimal("rebateamount");
                if (rebateamount.compareTo(rebateBalance) == 1) {
                    return getErrReturnObject().setErrMsg("超过返利金账户余额，返利金账户余额为" + rebateBalance).toString();
                }
                if (!Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) {
                    return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, sa_accountclassid, amount) + "元！").toString();
                }
                JSONObject rateBillMap = useRateBill(row, "订单提交");
                sqlList.addAll(rateBillMap.getJSONArray("sqlList").toJavaList(String.class));
                sa_cashbillidList.add(rateBillMap.getLong("sa_cashbillid"));
            } else {

                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=" + pay_enterpriseid + " where isused=1 and t1.siteid='" + siteid + "'");
                RowsMap accountclassrowsMap = accountclassrows.toRowsMap("sa_accountclassid");
                if (!accountclassinfos.isEmpty()) {
                    int index = 1;
                    for (Object object : accountclassinfos) {
                        JSONObject jsonObject = (JSONObject) object;
                        BigDecimal amount1 = jsonObject.getBigDecimal("amount");
                        if (index == accountclassinfos.size() && costamount.compareTo(BigDecimal.ZERO) > 0) {
                            if (Accountbalance.judgeBalance(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), amount1.add(costamount))) {
                                CashbillEntity entity = getCashbillEntity(amount1, sa_orderid, "订单提交", sonum, "由订单号" + sonum + "提交时生成");
                                //
                                if (accountclassrowsMap.containsKey(jsonObject.getString("sa_accountclassid")) && accountclassrowsMap.get(jsonObject.getString("sa_accountclassid")).isNotEmpty()) {
                                    jsonObject.put("balance", accountclassrowsMap.get(jsonObject.getString("sa_accountclassid")).get(0).getBigDecimal("balance").subtract(amount1));
                                } else {
                                    jsonObject.put("balance", jsonObject.getBigDecimal("balance").subtract(amount1));
                                }
                                JSONObject cashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), entity, true);
//                                sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                                ArrayList<String> sqlList2 = (ArrayList<String>) cashbillPay.getJSONArray("sqlList").toJavaList(String.class);
                                dbConnect.runSqlUpdate(sqlList2);
                                sa_cashbillidList.add(cashbillPay.getLong("sa_cashbillid"));


                                //定制费用

                                CashbillEntity entityCost = getCashbillEntity(costamount, sa_orderid, "订单提交", sonum, "由" + sonum + "【定制费用】订单提交时自动产生");
                                JSONObject cashbillPayCost = Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entityCost, true);
                                sqlList.addAll(cashbillPayCost.getJSONArray("sqlList").toJavaList(String.class));
                                sa_cashbillidList.add(cashbillPayCost.getLong("sa_cashbillid"));


                            } else {
                                return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), amount1.add(costamount)) + "元！").toString();
                            }
                        } else {
                            if (Accountbalance.judgeBalance(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), amount1)) {
                                CashbillEntity entity = getCashbillEntity(amount1, sa_orderid, "订单提交", sonum, "由订单号" + sonum + "提交时生成");
                                //
                                if (accountclassrowsMap.containsKey(jsonObject.getString("sa_accountclassid")) && accountclassrowsMap.get(jsonObject.getString("sa_accountclassid")).isNotEmpty()) {
                                    jsonObject.put("balance", accountclassrowsMap.get(jsonObject.getString("sa_accountclassid")).get(0).getBigDecimal("balance").subtract(amount1));
                                } else {
                                    jsonObject.put("balance", jsonObject.getBigDecimal("balance").subtract(amount1));
                                }
                                JSONObject cashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), entity, true);
                                sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                                sa_cashbillidList.add(cashbillPay.getLong("sa_cashbillid"));
                            } else {
                                return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), amount1) + "元！").toString();
                            }
                        }
                        index++;
                    }
                } else {
                    if (Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount.add(costamount))) {
                        CashbillEntity entity = getCashbillEntity(amount, sa_orderid, "订单提交", sonum, "由" + sonum + "订单提交时自动产生");
                        JSONObject cashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true);
//                        sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                        ArrayList<String> sqlList2 = (ArrayList<String>) cashbillPay.getJSONArray("sqlList").toJavaList(String.class);
                        dbConnect.runSqlUpdate(sqlList2);
                        sa_cashbillidList.add(cashbillPay.getLong("sa_cashbillid"));

                        //定制费用
                        if (costamount.compareTo(BigDecimal.ZERO) > 0) {
                            CashbillEntity entityCost = getCashbillEntity(costamount, sa_orderid, "订单提交", sonum, "由" + sonum + "【定制费用】订单提交时自动产生");
                            JSONObject cashbillPayCost = Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entityCost, true);
                            sqlList.addAll(cashbillPayCost.getJSONArray("sqlList").toJavaList(String.class));
                            sa_cashbillidList.add(cashbillPayCost.getLong("sa_cashbillid"));
                        }


                    } else {
                        return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, sa_accountclassid, amount.add(costamount)) + "元！").toString();
                    }
                }


            }
        }

        String message = "您的订单【" + sonum + "】已提交 ，您的资金账户已支出【" + amount + "】！";

        sqlList.add("update sa_order set sa_accountclassinfos='" + accountclassinfos + "' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        sqlList.add(getOrderSubmitSql(sa_orderid, status, freightstatus));
        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单提交", "订单提交成功，金额：" + amount).getSQL());
        dbConnect.runSqlUpdate(sqlList);

//        sendMsg(message, sa_orderid, sys_enterpriseid);
//        Executor.sendEml(this, "ordercheck", sa_orderid, siteid);
        for (Long sa_cashbillid : sa_cashbillidList) {
            Accountbalance.remindSend(this, pay_enterpriseid, sa_cashbillid);
        }
        return getSucReturnObject().toString();
    }

    public void sendMsg(String content, Long sa_orderid, Long sys_enterpriseid) throws YosException {

        ArrayList<Long> userList = getEnterpriseHrs(sys_enterpriseid).toArrayList("userid", new ArrayList<>());
        Remind remind = new Remind(this);
        remind.setTitle("订单消息");
        remind.setContent(content);
        remind.setType("应用");
        remind.setObjectid(sa_orderid);
        remind.setObjectname("sa_order");
        remind.setTouserid(userList);
        remind.sendByDialogMsg().createSys_message();
    }

    /**
     * @param orderRow
     * @param source
     * @return
     * @throws YosException
     */
    public JSONObject useRateBill(Row orderRow, String source) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Long sa_orderid = orderRow.getLong("sa_orderid");
        Long pay_enterpriseid = orderRow.getLong("pay_enterpriseid");
        Long sa_accountclassid = orderRow.getLong("sa_accountclassid");
        BigDecimal rebateamount = orderRow.getBigDecimal("rebateamount");
        String sonum = orderRow.getString("sonum");


        JSONObject jsonObject = new JSONObject();
        //金额=金额-返利金金额
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        //创建支出凭证
        CashbillEntity entity = getCashbillEntity(amount, sa_orderid, source, sonum, "由" + sonum + source + "时自动产生");
        JSONObject cashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true);
        sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
        Long sa_cashbillid1 = cashbillPay.getLong("sa_cashbillid");

        //创建返利金支出凭证
        Rows cashbillRows = dbConnect.runSqlQuery("SELECT * from sa_cashbill WHERE class='返利金' and STATUS ='' and rebate_enddate>=CURRENT_TIME and rebate_balance>0 and sys_enterpriseid= " + pay_enterpriseid + " and siteid = '" + siteid + "'  ORDER BY  rebate_enddate ");
        int count = 0;
        while (rebateamount.compareTo(BigDecimal.ZERO) == 1) {
            Row cashbillRow = cashbillRows.get(count);
            //返利账户
            sa_accountclassid = cashbillRow.getLong("sa_accountclassid");
            //返利
            Long sa_cashbillid = cashbillRow.getLong("sa_cashbillid");
            BigDecimal rebate_balance = cashbillRow.getBigDecimal("rebate_balance");
            CashbillEntity entity2 = new CashbillEntity();

            //返利代金券小于返利金金额，例如：rebate_balance:10,rebateamount:50
            if (rebate_balance.compareTo(rebateamount) == -1) {
                entity2 = getCashbillEntity(rebate_balance, sa_orderid, source, sonum, "【返利金】由" + sonum + source + "时自动产生");
                rebateamount = rebateamount.subtract(rebate_balance);
                rebate_balance = BigDecimal.ZERO;


            }
            //返利代金券等于返利金金额
            if (rebate_balance.compareTo(rebateamount) == 0) {
                entity2 = getCashbillEntity(rebate_balance, sa_orderid, source, sonum, "【返利金】由" + sonum + source + "时自动产生");
                rebateamount = BigDecimal.ZERO;
                rebate_balance = BigDecimal.ZERO;

            }
            //返利代金券大于返利金金额
            if (rebate_balance.compareTo(rebateamount) == 1) {
                entity2 = getCashbillEntity(rebateamount, sa_orderid, source, sonum, "【返利金】由" + sonum + source + "时自动产生");
                rebate_balance = rebate_balance.subtract(rebateamount);
                rebateamount = BigDecimal.ZERO;


            }
            //不要放到sqlList中，多张返利金时会出现同时使用同一个余额减
            JSONObject cashbillPay1 = Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity2, true, sa_cashbillid);
            dbConnect.runSqlUpdate(new ArrayList<>(cashbillPay1.getJSONArray("sqlList").toJavaList(String.class)));

            Accountbalance.remindSend(this, pay_enterpriseid, cashbillPay1.getLong("sa_cashbillid"));

            sqlList.add(updateCashbillSql(sa_cashbillid, rebate_balance));
            count++;

        }
        jsonObject.put("sqlList", sqlList);
        jsonObject.put("sa_cashbillid", sa_cashbillid1);
        return jsonObject;
    }


    @API(title = "审核", apiversion = R.ID20221108153502.v1.class, intervaltime = 5000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class, accessoryorder.class})
    public String check() throws YosException {
        boolean isdebug = content.getBooleanValue("isdebug");
        Long sa_orderid = content.getLong("sa_orderid");
        String reviewtype = content.getString("reviewtype");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        Row row = rows.get(0);
        JSONArray accountclassinfos = row.getJSONArray("accountclassinfos");
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        if (sa_accountclassid <= 0) {
            return getErrReturnObject().setErrMsg("请选择支付账号").toString();
        }
//        if (siteid.equals("TZ")) {
//            if (StringUtils.isBlank(reviewtype)) {
//                return getErrReturnObject().setErrMsg("订单评审类型为空，无法审核").toString();
//            }
//        }
//        if (StringUtils.isBlank(row.getString("saletype"))) {
//            return getErrReturnObject().setErrMsg("销售分类为空，无法审核").toString();
//        }

        //验证结算企业
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        if (pay_enterpriseid <= 0) {
            return getErrReturnObject().setErrMsg("请选择结算企业").toString();
        }


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

//        String type = row.getString("type");
        String sonum = row.getString("sonum");
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();

        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        //订单返利使用开关
        Boolean rebate_used = row.getBoolean("rebate_used");
        //站点订单返利使用开关
        String order_rebate_used = Parameter.getString(siteid, "order_rebate_used");

        ArrayList<Long> sa_cashbillidList = new ArrayList<>();

        if (order_paymentnode.equals("2")) {
            if (order_rebate_used.equals("1") && rebate_used) {
                //站点返利金比例
                BigDecimal order_rebate_userate = BigDecimal.valueOf(Parameter.getDouble(siteid, "order_rebate_userate"));
                //订单返利金比例
                BigDecimal rebate_userate = row.getBigDecimal("rebate_userate");
                if (rebate_userate.compareTo(order_rebate_userate) == 1) {
                    return getErrReturnObject().setErrMsg("返利金使用比例超出站点设置比例，站点比例为" + order_rebate_userate).toString();
                }
                //查询返利金账户余额
                BigDecimal rebateBalance = Accountbalance.getRebateBalance(this, pay_enterpriseid);
                //返利金金额
                BigDecimal rebateamount = row.getBigDecimal("rebateamount");
                if (rebateamount.compareTo(rebateBalance) == 1) {
                    return getErrReturnObject().setErrMsg("超过返利金账户余额，返利金账户余额为" + rebateBalance).toString();
                }
                if (!Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) {
                    return getErrReturnObject().setErrMsg("账户余额不足").toString();
                }
                JSONObject rateBillMap = useRateBill(row, "订单审核");
                sqlList.addAll(rateBillMap.getJSONArray("sqlList").toJavaList(String.class));
                sa_cashbillidList.add(rateBillMap.getLong("sa_cashbillid"));
            } else {
                if (!accountclassinfos.isEmpty()) {
                    for (Object object : accountclassinfos) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (Accountbalance.judgeBalance(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), jsonObject.getBigDecimal("amount"))) {
                            CashbillEntity entity = getCashbillEntity(jsonObject.getBigDecimal("amount"), sa_orderid, "订单审核", sonum, "由订单号" + sonum + "审核时生成");
                            jsonObject.put("balance", jsonObject.getBigDecimal("balance").subtract(jsonObject.getBigDecimal("amount")));
                            JSONObject createCashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), entity, true);
                            sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                            sa_cashbillidList.add(createCashbillPay.getLong("sa_cashbillid"));
                        } else {
                            return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), jsonObject.getBigDecimal("amount")) + "元！").toString();
                        }
                    }
                } else {
                    if (Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) {
                        CashbillEntity entity = getCashbillEntity(amount, sa_orderid, "订单审核", sonum, "由" + sonum + "订单审核时自动产生");
                        JSONObject cashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true);
                        sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                        sa_cashbillidList.add(cashbillPay.getLong("sa_cashbillid"));
                    } else {
                        return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, sa_accountclassid, amount) + "元！").toString();
                    }
                }
            }
        }
        String message = "您的订单【" + sonum + "】已审核！";


        sqlList.addAll(updateAccountbalance_freez(amount, true, "由销售订单" + sonum + "审核时冻结", sys_enterpriseid, sa_accountclassid, sa_orderid));

        sqlList.add(getOrderCheckSql(sa_orderid, reviewtype));

        sqlList.addAll(updateIcinvbal_sale(sa_orderid, false, false));

        dbConnect.runSqlUpdate(sqlList);

        for (Long sa_cashbillid : sa_cashbillidList) {
            Accountbalance.remindSend(this, pay_enterpriseid, sa_cashbillid);
        }
        ArrayList<Long> userIdList = dbConnect.runSqlQuery("select userid from sys_enterprise_hr where siteid='" + siteid + "' and isleader=1 and sys_enterpriseid=" + sys_enterpriseid).toArrayList("userid", new ArrayList<>());
        Executor.sendEml(this, "order_check", sa_orderid, siteid, userIdList);

        Rows orderBrand = dbConnect.runSqlQuery("select brandname from sa_brand where siteid='" + siteid + "' and sa_brandid=" + row.getLong("sa_brandid"));
//        if (Parameter.getBoolean("system.erp_dockswitch")) {
////            UploadDataToERP uploadDataToERP = new UploadDataToERP(this, sa_orderid, "");
////            boolean issuccess = uploadDataToERP.upload();
////            if (!issuccess) {
////                uncheck();
////                return getErrReturnObject().setErrMsg("上传ERP失败，当前单据将自动反审核，请检查！").toString();
////            } else {
////                dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单审核", "订单审核成功，金额：" + amount).getSQL());
//////                sendMsg(message, sa_orderid, sys_enterpriseid);
////                sendEmail(sa_orderid, row, orderBrand);
////            }
//        } else {
//
//        }
        dbConnect.runSqlUpdate(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单审核", "订单审核成功，金额：" + amount).getSQL());
//            sendMsg(message, sa_orderid, sys_enterpriseid);
        sendEmail(sa_orderid, row, orderBrand);

        return getSucReturnObject().toString();
    }

    @API(title = "反审核", apiversion = R.ID20230102141502.v1.class, intervaltime = 5000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class, accessoryorder.class})
    public String uncheck() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        Row row = rows.get(0);

        if (dbConnect.runSqlQuery("SELECT 1 from sa_dispatch t1 " +
                "left join sa_dispatch_items t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid " +
                "left join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid " +
                "WHERE t1.`status` != '新建' and  t1.siteid='" + siteid + "' and t3.sa_orderid=" + sa_orderid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在发货单，无法反审核").toString();
        }


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

        //验证结算企业
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        String sonum = row.getString("sonum");

        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "订单_反审核");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_orderid", sa_orderid);
        sqlList.add(sqlFactory.getSQL());
        //订单扣款节点（1:订单提交、2:订单审核）
        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        Long sa_cashbillid = 0L;
        if (order_paymentnode.equals("2")) {
            JSONObject jsonObject = returnPayBill(row, "订单反审核", sonum, "由" + sonum + "订单反审核时自动产生");
            sqlList.addAll(jsonObject.getJSONArray("sqlList").toJavaList(String.class));
            sa_cashbillid = jsonObject.getLong("sa_cashbillid");
        }


        String message = "您的订单【" + sonum + "】已反审核，请注意查看！";

        sqlList.addAll(updateAccountbalance_freez(amount, false, "由销售订单" + sonum + "审核时冻结", sys_enterpriseid, sa_accountclassid, sa_orderid));

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单反审核", "订单反审核成功，金额：" + amount).getSQL());

        sqlList.addAll(updateIcinvbal_sale(sa_orderid, true, false));

        dbConnect.runSqlUpdate(sqlList);
        sendMsg(message, sa_orderid, sys_enterpriseid);
        if (order_paymentnode.equals("2")) {
            Accountbalance.remindSend(this, row.getLong("pay_enterpriseid"), sa_cashbillid);
        }
        return getSucReturnObject().toString();
    }

    @API(title = "预提交反预提交", apiversion = R.ID20230331152503.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String presubmit() throws YosException {
        boolean isrecheck = content.getBooleanValue("isrecheck");
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        Row row = rows.get(0);
        String sonum = row.getString("sonum");
        //验证结算企业
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        if (dbConnect.runSqlQuery("SELECT 1 FROM sa_agents WHERE siteid='" + siteid + "' AND `status`='启用' AND sys_enterpriseid=" + sys_enterpriseid).isEmpty()) {
            return getErrReturnObject().setErrMsg("当前企业不存在或已禁用，无法下单").toString();
        }
        Long sa_promotionid = row.getLong("sa_promotionid");
        if (sa_promotionid > 0) {
            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_promotion WHERE CURRENT_TIME> enddate AND siteid='" + siteid + "' AND sa_promotionid='" + sa_promotionid + "'").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("该促销活动已截止，无法提交").toString();
            }
        }


        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);

        if (dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'").isEmpty()) {
            return getErrReturnObject().setErrMsg("订单表体不存在商品无法提交").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        if (isrecheck) {
            if (!row.getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非新建状态，无法预提交")
                        .toString();
            }
        } else {
            if (!row.getString("status").equals("预提交")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非预提交状态，无法撤回")
                        .toString();
            }
        }
        SQLFactory sqlFactoryupdate;
        String message = "";
        if (isrecheck) {
            message = "您的订单【" + sonum + "】已预提交，请注意查看！";
            sqlFactoryupdate = new SQLFactory(this, "订单预提交");
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "预提交", "订单预提交成功，金额：" + amount).getSQL());
        } else {
            message = "您的订单【" + sonum + "】已反预提交，请注意查看！";
            sqlFactoryupdate = new SQLFactory(this, "订单反预提交");
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "反预提交", "订单反预提交成功，金额：" + amount).getSQL());
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_orderid", sa_orderid);
        sqlFactoryupdate.addParameter("recheckby", username);
        sqlList.add(sqlFactoryupdate.getSQL());

        if (isrecheck && row.getString("type").equals("特殊订单")) {
            sqlList.add("update sa_order set typemx='特价' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        }

        dbConnect.runSqlUpdate(sqlList);
        sendMsg(message, sa_orderid, sys_enterpriseid);
        return getSucReturnObject().toString();
    }

    @API(title = "确认反确认", apiversion = R.ID20230331102503.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String recheck() throws YosException {
        boolean isrecheck = content.getBooleanValue("isrecheck");
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        Row row = rows.get(0);

        String sonum = row.getString("sonum");
        //验证结算企业
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");

        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();
        if (isrecheck) {
            if (row.getBoolean("createflag")) {
                if (!row.getString("status").equals("新建")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为新建状态，无法确认")
                            .toString();
                }
            } else {
                if (!row.getString("status").equals("预提交")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非预提交状态，无法确认")
                            .toString();
                }
            }

        } else {
            if (!row.getString("status").equals("确认")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("sonum") + "】的订单为非确认状态，无法反确认")
                        .toString();
            }
        }
        SQLFactory sqlFactoryupdate;
        String message = "";
        if (isrecheck) {
            message = "您的订单【" + sonum + "】已确认，请注意查看！";
            sqlFactoryupdate = new SQLFactory(this, "订单确认");
            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "确认", "订单确认成功，金额：" + amount).getSQL());
        } else {
            message = "您的订单【" + sonum + "】已反确认，请注意查看！";
            if (!row.getBoolean("createflag")) {
                sqlFactoryupdate = new SQLFactory(this, "订单反确认(业务端创建)");
            } else {
                sqlFactoryupdate = new SQLFactory(this, "订单反确认(管理端创建)");
            }

            sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "反确认", "订单反确认成功，金额：" + amount).getSQL());
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_orderid", sa_orderid);
        sqlFactoryupdate.addParameter("recheckby", username);
        sqlList.add(sqlFactoryupdate.getSQL());

        dbConnect.runSqlUpdate(sqlList);
        sendMsg(message, sa_orderid, sys_enterpriseid);
        return getSucReturnObject().toString();
    }

    @API(title = "退回", apiversion = R.ID20221108161902.v1.class, intervaltime = 5000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class, promotion.class, restcontroller.webmanage.sale.promotion.promotion.class, accessoryorder.class})
    public String back() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        if (!rows.get(0).getString("status").equals("提交")) {
            return getErrReturnObject().setErrMsg("订单非提交状态无法退回").toString();
        }
        Row row = rows.get(0);
        Date submitdate = new Date();
        Date date = new Date();
        if (!StringUtils.isBlank(row.getString("submitdate"))) {
            submitdate = row.getDate("submitdate");
        }
        if (!row.getString("type").equals("标准订单") && siteid.equalsIgnoreCase("ccyosg")) {
            return getErrReturnObject().setErrMsg("非标准订单无法退回").toString();
        }
        if (!isMonth(submitdate, date) && row.getString("type").equals("标准订单") && siteid.equalsIgnoreCase("ccyosg")) {
            return getErrReturnObject().setErrMsg("标准订单无法跨月退回").toString();
        }

        if (rows.get(0).getLong("sa_promotionid") != 0) {
            if (dbConnect.runSqlQuery("SELECT 1 FROM sa_promotion WHERE CURRENT_TIME> enddate AND siteid='" + siteid + "' AND sa_promotionid='" + rows.get(0).getLong("sa_promotionid") + "'").isNotEmpty()) {
                //return getErrReturnObject().setErrMsg("活动已结束，是否确认退回，退回后无法再次提交").toString();
            }
        }
        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        //验证结算企业
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        String sonum = row.getString("sonum");
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");

        if (StringUtils.isBlank(row.getString("sa_accountclassinfos"))) {
            dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
            rows = getOrderRows(this, sa_orderid);
            row = rows.get(0);
        } else {
            if (isJSONArray(row.getString("sa_accountclassinfos"))) {
                JSONArray jsonArrayResult = JSONArray.parseArray(row.getString("sa_accountclassinfos"));
                if (jsonArrayResult.isEmpty()) {
                    dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
                    rows = getOrderRows(this, sa_orderid);
                    row = rows.get(0);
                }
            }
        }

        //System.out.println(rows.get(0).getString("sa_accountclassinfos"));
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();

        Rows orderdetails_items = dbConnect.runSqlQuery("select itemid,sum(qty) qty from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by itemid");
        for (Row orderdetail : orderdetails_items) {
            sqlList.add("update plm_item set saleqty=saleqty-" + orderdetail.getBigDecimal("qty") + " where itemid=" + orderdetail.getLong("itemid"));
        }

        String type = row.getString("type");
        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select sa_promotion_itemsid,itemno,sum(qty) qty  from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by sa_promotion_itemsid,itemno");
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + row.getLong("sa_promotionid"));
            RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("sa_promotion_itemsid");
            for (Row orderdetail : orderdetails) {
                String sa_promotion_itemsid = orderdetail.getString("sa_promotion_itemsid");
                BigDecimal qty = orderdetail.getBigDecimal("qty");
                if (promotionitemsRowsMap.containsKey(sa_promotion_itemsid)) {
                    if (promotionitemsRowsMap.get(sa_promotion_itemsid).isNotEmpty()) {
                        sqlList.add("update sa_promotion_items set saledqty=saledqty-" + qty + " where sa_promotion_itemsid=" + sa_promotion_itemsid);
                    }
                }
            }

            //促销订单经销商限购金额判断
            Rows promotion_auth = dbConnect.runSqlQuery("select * from sa_promotion_auth where sys_enterpriseid=" + sys_enterpriseid + " and sa_promotionid=" + row.getLong("sa_promotionid"));
            if (promotion_auth.isNotEmpty()) {
                boolean islimit = promotion_auth.get(0).getBoolean("islimit");
//                BigDecimal limitamount = promotion_auth.get(0).getBigDecimal("limitamount");
//                BigDecimal saledamount = promotion_auth.get(0).getBigDecimal("saledamount");
                long sa_promotionid = promotion_auth.get(0).getLong("sa_promotionid");
                Rows promotion = dbConnect.runSqlQuery("select * from sa_promotion where siteid='" + siteid + "'and  sa_promotionid=" + sa_promotionid);
                if (islimit) {
                    if (promotion.isNotEmpty()) {
                        Rows amountrows = dbConnect.runSqlQuery("select sum(amount) amount from sa_cashbill where ownerid='" + sa_orderid + "' and ownertable='sa_order' and sa_accountclassid=" + promotion.get(0).getLong("sa_accountclassid"));
                        if (amountrows.isNotEmpty()) {
                            if (amountrows.get(0).getBigDecimal("amount").compareTo(BigDecimal.ZERO) > 0) {
                                sqlList.add("update sa_promotion_auth set saledamount=saledamount-" + amountrows.get(0).getBigDecimal("amount") + " where sa_promotion_authid=" + promotion_auth.get(0).getLong("sa_promotion_authid"));
                            }
                        }
                    }

                }
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "订单_退回");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_orderid", sa_orderid);
        sqlList.add(sqlFactory.getSQL());
//        Rows cashbillRows = getCashbillRows(sa_orderid);
//        String source = "";
//        if (cashbillRows.isNotEmpty()) {
//            source = cashbillRows.get(0).getString("source");
//            amount = cashbillRows.get(0).getBigDecimal("amount");
//        }
//        if (source.equals("订单提交") || source.equals("订单审核")) {
//            CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, "订单退回", "由" + sonum + "订单退回时自动产生");
//            sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
//        }
        JSONObject jsonObject = returnPayBill(row, "订单退回", sonum, "由订单号" + sonum + "退回时生成");
        sqlList.addAll(jsonObject.getJSONArray("sqlList").toJavaList(String.class));
        Long sa_cashbillid = jsonObject.getLong("sa_cashbillid");

        String message = "您的订单【" + sonum + "】已退回，退款金额为【" + amount + "】 已转入到您的资金账户";

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单退回", "订单退回成功，金额：" + amount).getSQL());
        dbConnect.runSqlUpdate(sqlList);
        //订单退回原因
        ArrayList<Long> sa_orderids = new ArrayList<>();
        sa_orderids.add(sa_orderid);
        DataExtend.createBackReasonSql(this, "sa_order", sa_orderids, content.getStringValue("backreason"));
//        sendMsg(message, sa_orderid, sys_enterpriseid);

        Accountbalance.remindSend(this, pay_enterpriseid, sa_cashbillid);
        ArrayList<Long> userIdList = dbConnect.runSqlQuery("select userid from sys_enterprise_hr where siteid='" + siteid + "' and sys_enterpriseid=" + sys_enterpriseid).toArrayList("userid", new ArrayList<>());
        Executor.sendEml(this, "order_back", sa_orderid, siteid, userIdList);
        return getSucReturnObject().toString();
    }

    @API(title = "撤回", apiversion = R.ID20230301190202.v1.class, intervaltime = 5000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.class, promotion.class, restcontroller.webmanage.sale.promotion.promotion.class, accessoryorder.class})
    public String revoke() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }

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

        Row row = rows.get(0);
        if (!row.getString("status").equals("提交")) {
            return getErrReturnObject().setErrMsg("非提交状态无法撤回").toString();
        }
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        //验证结算企业
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        String sonum = row.getString("sonum");

        if (StringUtils.isBlank(row.getString("sa_accountclassinfos"))) {
            dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
            rows = getOrderRows(this, sa_orderid);
            row = rows.get(0);
        } else {
            if (isJSONArray(row.getString("sa_accountclassinfos"))) {
                JSONArray jsonArrayResult = JSONArray.parseArray(row.getString("sa_accountclassinfos"));
                if (jsonArrayResult.isEmpty()) {
                    dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
                    rows = getOrderRows(this, sa_orderid);
                    row = rows.get(0);
                }
            }
        }
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();

        Rows orderdetails_items = dbConnect.runSqlQuery("select itemid,sum(qty) qty from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by itemid");
        for (Row orderdetail : orderdetails_items) {
            sqlList.add("update plm_item set saleqty=saleqty-" + orderdetail.getBigDecimal("qty") + " where itemid=" + orderdetail.getLong("itemid"));
        }

        String type = row.getString("type");
        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select sa_promotion_itemsid,itemno,sum(qty) qty  from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by sa_promotion_itemsid,itemno");
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + row.getLong("sa_promotionid"));
            RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("sa_promotion_itemsid");
            for (Row orderdetail : orderdetails) {
                String sa_promotion_itemsid = orderdetail.getString("sa_promotion_itemsid");
                BigDecimal qty = orderdetail.getBigDecimal("qty");
                if (promotionitemsRowsMap.containsKey(sa_promotion_itemsid)) {
                    if (promotionitemsRowsMap.get(sa_promotion_itemsid).isNotEmpty()) {
                        sqlList.add("update sa_promotion_items set saledqty=saledqty-" + qty + " where sa_promotion_itemsid=" + sa_promotion_itemsid);
                    }
                }
            }
        }

        if (row.getString("type").equals("特殊订单")) {
            sqlList.add("update sa_order set status='确认' where sa_orderid = " + sa_orderid + " and siteid='" + siteid + "'");
        } else {
            SQLFactory sqlFactory = new SQLFactory(this, "订单_撤回");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter_in("sa_orderid", sa_orderid);
            sqlList.add(sqlFactory.getSQL());
        }

        JSONObject jsonObject = returnPayBill(row, "订单撤回", sonum, "由订单号" + sonum + "撤回时生成");
        sqlList.addAll(jsonObject.getJSONArray("sqlList").toJavaList(String.class));
        Long sa_cashbillid = jsonObject.getLong("sa_cashbillid");

        String message = "您的订单【" + sonum + "】 金额为【" + amount + "】 已撤回！";

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单撤回", "订单撤回成功，金额：" + amount).getSQL());
        dbConnect.runSqlUpdate(sqlList);
        //订单退回原因
//        sendMsg(message, sa_orderid, sys_enterpriseid);

        Accountbalance.remindSend(this, pay_enterpriseid, sa_cashbillid);
        return getSucReturnObject().toString();
    }

    @API(title = "交期待确认", apiversion = R.ID20230102143502.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String confirmDelivery() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");

        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }


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

        Row row = rows.get(0);
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        if (sa_accountclassid <= 0) {
            return getErrReturnObject().setErrMsg("请选择支付账号").toString();
        }
        //验证结算企业
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        if (pay_enterpriseid <= 0) {
            return getErrReturnObject().setErrMsg("请选择结算企业").toString();
        }
        Long sys_enterprise_financeid = row.getLong("sys_enterprise_financeid");
        if (sys_enterprise_financeid <= 0) {
            return getErrReturnObject().setErrMsg("请选择财务信息").toString();
        }
//        //物流
//        Long sa_logiscompid = row.getLong("sa_logiscompid");
//        if (sa_logiscompid <= 0) {
//            return getErrReturnObject().setErrMsg("请选择物流信息").toString();
//        }
        //验证收货人信息
        Long rec_contactsid = row.getLong("rec_contactsid");
        if (rec_contactsid <= 0) {
            return getErrReturnObject().setErrMsg("请选择物收货人信息").toString();
        }
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        if (sys_enterpriseid <= 0) {
            return getErrReturnObject().setErrMsg("请选择订货企业信息").toString();
        }
        //提交，交期待确认
        String status = "交期待确认";
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        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_orderid", sa_orderid);
        sqlFactory.addParameter("status", status);
        sqlList.add(sqlFactory.getSQL());

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, status, "订单" + status + "：" + amount).getSQL());
        dbConnect.runSqlUpdate(sqlList);
        Executor.sendEml(this, "orderresponse", sa_orderid, siteid);
        return getSucReturnObject().toString();
    }

    @API(title = "确认交期，操作交期待确认的订单", apiversion = R.ID20221230094802.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String confirmDeliveryDate() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }


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

        //交期待确认
        String status = rows.get(0).getString("status");
        if (!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_orderid", sa_orderid);
        sqlList.add(sqlFactory.getSQL());

        //操作记录
        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "交期确认", "订单交期确认").getSQL());

        dbConnect.runSqlUpdate(sqlList);


        return getSucReturnObject().toString();
    }

    @API(title = "交期撤回", apiversion = R.ID20230227160002.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String revokeDelivery() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = getOrderRows(this, sa_orderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }

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

        Row row = rows.get(0);
        Long sa_accountclassid = row.getLong("sa_accountclassid");
        //验证结算企业
        Long pay_enterpriseid = row.getLong("pay_enterpriseid");
        Long sys_enterpriseid = row.getLong("sys_enterpriseid");
        String sonum = row.getString("sonum");

        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactory = new SQLFactory(this, "订单_交期撤回");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter_in("sa_orderid", sa_orderid);
        sqlList.add(sqlFactory.getSQL());
        //订单扣款节点（1:订单提交、2:订单审核）
        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        Long sa_cashbillid = 0L;
        if (order_paymentnode.equals("2")) {
            JSONObject jsonObject = returnPayBill(row, "订单_交期撤回", sonum, "由" + sonum + "订单交期撤回时自动产生");
            sqlList.addAll(jsonObject.getJSONArray("sqlList").toJavaList(String.class));
            sa_cashbillid = jsonObject.getLong("sa_cashbillid");
        }

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单交期撤回", "订单交期撤回：" + amount).getSQL());
        dbConnect.runSqlUpdate(sqlList);
        sendMsg("订单交期撤回" + sonum, sa_orderid, sys_enterpriseid);
        if (order_paymentnode.equals("2")) {
            Accountbalance.remindSend(this, pay_enterpriseid, sa_cashbillid);
        }
        return getSucReturnObject().toString();
    }


    public JSONObject returnPayBill(Row orderrow, String source, String sourcenotes, String remarks) throws YosException {
        JSONObject cashJsonObject = new JSONObject();
        Long sa_orderid = orderrow.getLong("sa_orderid");
        JSONArray sa_accountclassinfos = orderrow.getJSONArray("sa_accountclassinfos");
        //Long sa_accountclassid = orderrow.getLong("sa_accountclassid");
        //验证结算企业
        Long pay_enterpriseid = orderrow.getLong("pay_enterpriseid");
        ArrayList<String> sqlList = new ArrayList<>();
        BigDecimal amount;
        Rows rebateRows = getCashbillRebateRows(sa_orderid);
        //返利金
        if (rebateRows.isNotEmpty()) {
            for (Row rebateRow : rebateRows) {
                amount = rebateRow.getBigDecimal("amount");
                if (amount.compareTo(BigDecimal.ZERO) > 0) {
                    Long sa_cashbillid_rebate = rebateRow.getLong("sa_cashbillid_rebate");
                    CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, source, sourcenotes, "【返利金】" + remarks);
                    sqlList.addAll(beans.order.Order.createRebateBillPay(this, entity, pay_enterpriseid, sa_cashbillid_rebate));
                }
            }


        }
        //金额
        Rows amountRows = getMultipleAccountsCashbillRows(sa_orderid);
        if (amountRows.isNotEmpty()) {
            for (Row row : amountRows) {
                amount = row.getBigDecimal("amount");
                if (!sa_accountclassinfos.isEmpty()) {
                    for (Object object : sa_accountclassinfos) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getLong("sa_accountclassid") == row.getLong("sa_accountclassid")) {
                            //System.out.println(jsonObject.getBigDecimal("balance"));
                            //System.out.println(amount);
                            //System.out.println(jsonObject.getBigDecimal("balance").add(amount));
                            jsonObject.put("balance", jsonObject.getBigDecimal("balance").add(amount));
                            break;
                        }
                    }
                }
                if (amount.compareTo(BigDecimal.ZERO) > 0) {
                    CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, source, sourcenotes, remarks);
                    JSONObject cashbillPay = Accountbalance.createCashbillPay(this, pay_enterpriseid, row.getLong("sa_accountclassid"), entity, true);
                    sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    cashJsonObject.put("sa_cashbillid", cashbillPay.getLongValue("sa_cashbillid"));
                }
            }


        }
        sqlList.add("update sa_order set sa_accountclassinfos='" + sa_accountclassinfos + "' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        cashJsonObject.put("sqlList", sqlList);
        return cashJsonObject;
    }

    /**
     * 邮件发送验证
     *
     * @param sa_orderid
     * @param row
     * @param orderBrand
     */
    public void sendEmail(Long sa_orderid, Row row, Rows orderBrand) {
        switch (row.getString("tradefield")) {
            case "全领域":
                Executor.sendEml(this, "ordertradefield", sa_orderid, siteid);
                break;
            case "燃气":
                Executor.sendEml(this, "ordertradefieldrq", sa_orderid, siteid);
                break;
            case "给水":
                Executor.sendEml(this, "ordertradefieldgs", sa_orderid, siteid);
                break;
            case "暖通":
                Executor.sendEml(this, "ordertradefieldnt", sa_orderid, siteid);
                break;
            case "消防":
                Executor.sendEml(this, "ordertradefieldxf", sa_orderid, siteid);
                break;
        }
        if (orderBrand.isNotEmpty()) {
            switch (orderBrand.get(0).getString("brandname")) {
                case "全品牌":
                    Executor.sendEml(this, "orderbrand", sa_orderid, siteid);
                    break;
                case "班尼戈":
                    Executor.sendEml(this, "orderbrandbng", sa_orderid, siteid);
                    break;
                case "中捷":
                    Executor.sendEml(this, "orderbrandzj", sa_orderid, siteid);
                    break;
            }
        }
    }

    /**
     * 订单结案验证
     *
     * @throws
     */
    public String closeordervalidate(long sa_orderid) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Rows Rowstatus = dbConnect.runSqlQuery(
                "select * from sa_order where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        Rows RowsDetail = dbConnect.runSqlQuery(
                "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by rowno asc");
        if (Rowstatus.isEmpty()) {
            return "订单不存在";
        } else {
            if (!Rowstatus.get(0).getString("status").equals("审核")) {
                return "非审核状态的订单无法结案";
            }
        }
        if (!siteid.equalsIgnoreCase("TZ")) {
            return "当前站点不可执行订单结案操作";
        }
        DBConnect connect = new DBConnect("ORACLE_TZ");
        Rows rows = connect.runSqlQuery("select SO_LINE2,sum(QTY)as QTY from v_eso_shipped_qty where SO_NO2='" + Rowstatus.get(0).getString("sonum") + "' and ZT='" + (siteid.equalsIgnoreCase("TZ") ? "IDCZJ10" : "IBPNB10") + "' group by SO_LINE2");
        for (Row row : rows) {
            int SO_LINE2 = row.getInteger("SO_LINE2");//erp订单行号
            double QTY = row.getInteger("QTY");//erp已发货数量
            int i = 0;
            for (Row row2 : RowsDetail) {
                int rowno = row2.getInteger("rowno");//订单行号
                if (SO_LINE2 == rowno) {
                    sqlList.add("update sa_orderitems set erpdeliedqty=" + QTY + " where sa_orderitemsid=" + row2.getLong("sa_orderitemsid"));
                }
            }
            dbConnect.runSqlUpdate(sqlList);
        }
        {
            String str = "";
            RowsDetail = dbConnect.runSqlQuery(
                    "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by rowno asc");
            for (Row row : RowsDetail) {
                int rowno = row.getInteger("rowno");//订单行号
                BigDecimal deliedqty = row.getBigDecimal("deliedqty");//订单已发货数量
                BigDecimal erpdeliedqty = row.getBigDecimal("erpdeliedqty");//erp已发货数量
                //System.out.println(deliedqty);
                //System.out.println(erpdeliedqty);
                if (deliedqty.compareTo(erpdeliedqty) != 0) {
                    str = str + "行" + rowno + "商品发货数量" + deliedqty + "与erp发货数量" + erpdeliedqty + "不一致，请检查；";
                }
            }
            if (!str.equalsIgnoreCase("")) {
                return str;
            }
        }
        return "true";
    }


    @API(title = "关闭", apiversion = R.ID20221108164502.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String close() throws YosException {
        long sa_orderid = content.getLong("sa_orderid");
        String str = closeordervalidate(sa_orderid);
        if (!str.equals("true")) {
            return getErrReturnObject().setErrMsg(str).toString();
        }

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


        Rows RowsDetails = dbConnect.runSqlQuery(
                "select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "' order by rowno asc");
        content.put("type", "数量");
        content.put("remarks", "订单结案变更");
        JSONArray itemifnos = new JSONArray();
        for (Row row : RowsDetails) {
            if (row.getBigDecimal("qty").compareTo(row.getBigDecimal("erpdeliedqty")) > 0) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("itemid", row.getLong("itemid"));
                jsonObject.put("sa_orderitemsid", row.getLong("sa_orderitemsid"));
                jsonObject.put("newvalue", row.getString("erpdeliedqty"));
                itemifnos.add(jsonObject);
            }
        }
        content.put("itemifnos", itemifnos);
        content.put("isjiean", true);
        OrderItems orderItems = new OrderItems(content);
        orderItems.changeItem();

        if (Parameter.getBoolean("system.erp_dockswitch")) {
            Rows rows = dbConnect.runSqlQuery("select * from sa_orderitems_change where sa_orderid=" + sa_orderid
                    + " and siteid='" + siteid + "' order by createdate desc");
            //System.out.println(!rows.isEmpty());
            if (!rows.isEmpty()) {

                UploadDataToERP uploadDataToERP = new UploadDataToERP(this, rows.get(0).getLong("sa_orderitems_changeid"), "订单变更");
                boolean issuccess = uploadDataToERP.upload();
                if (!issuccess) {
                    dbConnect.runSqlUpdate("update sa_order set changeuserid='" + userid + "',changeby= '"
                            + username
                            + "',changedate = current_time,status= '审核',closeby='',closedate  = NULL where siteid = '"
                            + siteid + "' and sa_orderid =" + sa_orderid);
                    return getErrReturnObject().setErrMsg("上传ERP失败，当前单据将自动反关闭，请检查！").toString();
                }
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "订单_关闭");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        dbConnect.runSqlUpdate(sqlFactory);


        return getSucReturnObject().toString();
    }

    @API(title = "查询发货单列表", apiversion = R.ID20221205111302.v1.class)
    @CACHEING
    public String dispatchList() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "订单_发货单列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = queryDispatchList(where.toString(), sa_orderid);
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();

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

        SQLFactory sqlFactory = new SQLFactory(this, "查询发货单数据");
        sqlFactory.addParameter_in("sa_dispatchid", ids);
        RowsMap rowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sa_dispatchid");
        for (Row row : rows) {
            Rows rows2 = rowsMap.getOrDefault(row.getString("sa_dispatchid"), new Rows());
            if (rows2.isNotEmpty()) {
                row.putIfAbsent("sumamount", rows2.get(0).getBigDecimal("sumamount").setScale(2, BigDecimal.ROUND_HALF_UP));
                row.putIfAbsent("sumqty", rows2.get(0).getBigDecimal("sumqty").setScale(2, BigDecimal.ROUND_HALF_UP));
            }
            row.putIfAbsent("sumamount", 0);
            row.putIfAbsent("sumqty", 0);
        }

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

    //查询订单列表(业务员)
    public QuerySQL queryDispatchList(String where, Long sa_orderid) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_dispatch");
        querySQL.setTableAlias("t1");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.sa_orderid", sa_orderid);
        querySQL.setWhere(where);
        querySQL.setWhere("t1.`status` != '新建'");
        return querySQL;
    }


    @API(title = "查询物流单列表", apiversion = R.ID20221205111402.v1.class)
    @CACHEING
    public String logisticsList() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t9.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t9.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "订单_物流单列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_logistics_items", "sa_logisticsid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "st_stockbill_items", "t2", "t2.st_stockbill_itemsid = t1.st_stockbill_itemsid AND t2.siteid = t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_dispatch_items", "t6", "t6.sa_dispatch_itemsid = t2.sa_dispatch_itemsid AND t6.siteid = t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t7", "t6.sa_orderitemsid = t7.sa_orderitemsid AND t6.siteid = t7.siteid", "sa_orderid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_logistics", "t9", "t9.sa_logisticsid = t1.sa_logisticsid AND t9.siteid = t1.siteid",
                "billno", "billdate", "logisticsstatus", "packageqty", "logisticsamount", "address", "remarks");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t9.`status` != '新建'");
        querySQL.setWhere(" t7.sa_orderid", sa_orderid);
        querySQL.setWhere(where.toString());
        querySQL.addGroupBy("t7.sa_orderid,t9.billno,t9.billdate,t9.logisticsstatus,t9.packageqty,t9.logisticsamount,t9.address,t9.remarks,t1.sa_logisticsid");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询出库单列表", apiversion = R.ID20230815151203.v1.class)
    @CACHEING
    public String stockbillList() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "订单_出库单列表");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t", "t1.siteid ='111'  ", "*");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();

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

    @API(title = "查询订单进度", apiversion = R.ID20221229093602.v1.class)
    @CACHEING
    public String getOrderProcess() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.action like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.actionby like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sa_orderid = content.getLong("sa_orderid");
        SQLFactory sqlFactory = new SQLFactory(this, "查询订单进度列表");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t.siteid = '111' ", "*");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询发票列表", apiversion = R.ID20221229154102.v1.class)
    @CACHEING
    public String queryinvoiceapplyList() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.invoiceno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.invoicecode like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.invoiceserialnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.sa_invoicebillid_blue like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("rb") && !"".equals(whereObject.getString("rb"))) {
                where.append(" and t1.rb ='").append(whereObject.getString("rb")).append("' ");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "查询发票列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_invoicebill", "*");
        querySQL.setTableAlias("t1");
        querySQL.addQueryFields("sumtaxincludedamount", "ifnull((select sum(taxincludedamount) sumtaxincludedamount\n" +
                "                from sa_invoicebill_item\n" +
                "                where sa_invoicebillid = t1.sa_invoicebillid\n" +
                "                and siteid = t1.siteid), 0)");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_invoiceapplyid in (SELECT sa_invoiceapplyid from sa_invoiceapply_order WHERE sa_orderid = '" + sa_orderid + "' and siteid = '" + siteid + "')");
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();

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

    @API(title = "一键复制订单", apiversion = R.ID20230102144502.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String copyOrder() throws YosException {

        Long sa_orderid = content.getLong("sa_orderid");

        Long sys_enterpriseid = content.getLong("sys_enterpriseid");

        ArrayList<String> sqlList = new ArrayList<>();

        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_order  WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'");

        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        Row row = rows.get(0);

        if (sys_enterpriseid > 0) {
            boolean flag = true;
            Row enterpriseRowOld = Enterprise.getEnterprise(this, row.getLong("sys_enterpriseid"));
            Row enterpriseRowNew = Enterprise.getEnterprise(this, sys_enterpriseid);
            if (enterpriseRowOld.isEmpty() || enterpriseRowNew.isEmpty()) {
                return getErrReturnObject().setErrMsg("经销商不存在").toString();
            } else {
//                if (Enterprise.getEnterpriseGrade(this, row.getLong("sys_enterpriseid")).getGrade() != Enterprise.getEnterpriseGrade(this, sys_enterpriseid).getGrade() ||
//                        Enterprise.getEnterpriseGrade(this, row.getLong("sys_enterpriseid")).getPricegrade() != Enterprise.getEnterpriseGrade(this, sys_enterpriseid).getPricegrade()) {
//                    System.out.println("grade");
//                    flag = false;
//                }
                //企业领域
//                Rows tradefieldrowsOld = dbConnect.runSqlQuery("SELECT tradefield from sys_enterprise_tradefield WHERE sys_enterpriseid =" + row.getLong("sys_enterpriseid") + " and siteid ='" + siteid + "'");
//                Rows tradefieldrowsNew = dbConnect.runSqlQuery("SELECT tradefield from sys_enterprise_tradefield WHERE sys_enterpriseid =" + sys_enterpriseid + " and siteid ='" + siteid + "'");
//                if (!tradefieldrowsNew.containsAll(tradefieldrowsOld)) {
//                    System.out.println("tradefield");
//                    flag = false;
//                }

                //企业营销类别
                String itemclasssqlOld = "select itemclassid from plm_itemclass where siteid='" + siteid + "'";
                boolean saleclassauthOld = enterpriseRowOld.getBoolean("saleclassauth");
                if (saleclassauthOld) {
                    itemclasssqlOld = "select itemclassid from plm_itemclass where siteid='" + siteid + "' and itemclassid in ( SELECT itemclassid from sys_enterprise_saleclass WHERE sys_enterpriseid = " + row.getLong("sys_enterpriseid") + " and siteid='" + siteid + "')";
                }
                Rows itemclassrowsOld = dbConnect.runSqlQuery(itemclasssqlOld);

                String itemclasssqlNew = "select itemclassid from plm_itemclass where siteid='" + siteid + "'";
                boolean saleclassauthNew = enterpriseRowNew.getBoolean("saleclassauth");
                if (saleclassauthNew) {
                    itemclasssqlNew = "select itemclassid from plm_itemclass where siteid='" + siteid + "' and itemclassid in ( SELECT itemclassid from sys_enterprise_saleclass WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "')";
                }
                Rows itemclassrowsNew = dbConnect.runSqlQuery(itemclasssqlNew);
                if (!itemclassrowsNew.containsAll(itemclassrowsOld)) {
                    System.out.println("itemclass");
                    flag = false;
                }

                if (!flag) {
                    return getErrReturnObject().setErrMsg("所选经销商经营范围不一致，不允许复制").toString();
                }
            }


        }

        Long sa_orderid_copy = createTableID("sa_order");
        SQLFactory sqlFactory = new SQLFactory(this, "复制订单");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid_copy);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        //订单编号
        sqlFactory.addParameter("sonum", createBillCode("orderbill"));
        //订单类型
        sqlFactory.addParameter("type", row.getString("type"));
        //明细分类
        sqlFactory.addParameter("typemx", row.getString("typemx"));
        //订货企业id
        if (sys_enterpriseid == 0) {
            sqlFactory.addParameter("sys_enterpriseid", row.getLong("sys_enterpriseid"));
            //结算单位
            sqlFactory.addParameter("pay_enterpriseid", row.getLong("sys_enterpriseid"));
        } else {
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            //结算单位
            sqlFactory.addParameter("pay_enterpriseid", sys_enterpriseid);
        }

        //营销账户类型ID
        sqlFactory.addParameter("sa_accountclassid", row.getLong("sa_accountclassid"));
        //品牌ID
        sqlFactory.addParameter("sa_brandid", row.getLong("sa_brandid"));
        //合同ID
        sqlFactory.addParameter("sa_contractid", row.getLong("sa_contractid"));
        //工程项目表ID
        sqlFactory.addParameter("sa_projectid", row.getLong("sa_projectid"));
        //合作企业财务信息ID（开票信息）
        sqlFactory.addParameter("sys_enterprise_financeid", 0);
        //物流公司档案ID
        sqlFactory.addParameter("sa_logiscompid", row.getLong("sa_logiscompid"));
        //合作企业联系人表ID（收货信息）
        sqlFactory.addParameter("rec_contactsid", row.getLong("rec_contactsid"));
        //备注说明
        sqlFactory.addParameter("remarks", row.getString("remarks"));
        //销售人员hrid,业务员hrid
        sqlFactory.addParameter("saler_hrid", row.getLong("saler_hrid"));
        //制单方 1表示工厂方，0表示客户方
        if (usertype == 21 || usertype == 22) {
            sqlFactory.addParameter("createflag", 0);
        } else {
            sqlFactory.addParameter("createflag", 1);
        }
        // 领域
        sqlFactory.addParameter("tradefield", row.getString("tradefield"));
        //单据日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        sqlFactory.addParameter("billdate", sdf.format(new Date()));
        sqlFactory.addParameter("rebate_userate", row.getBigDecimal("rebate_userate"));
        if (row.getBoolean("rebate_used")) {
            sqlFactory.addParameter("rebate_used", 1);
        } else {
            sqlFactory.addParameter("rebate_used", 0);
        }
        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid_copy, "订单复制", "由订单" + row.getString("sonum") + "复制而来").getSQL());
        sqlList.add(sqlFactory.getSQL());

        //复制商品
        Rows orderitemsRows = dbConnect.runSqlQuery("SELECT * from sa_orderitems WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'");
        for (Row orderitemsRow : orderitemsRows) {
            sqlFactory = new SQLFactory(this, "复制订单商品");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("sa_orderitemsid", createTableID("sa_orderitems"));
            sqlFactory.addParameter("sa_orderid", sa_orderid_copy);
            sqlFactory.addParameter("rowno", orderitemsRow.getLong("rowno"));
            sqlFactory.addParameter("itemid", orderitemsRow.getLong("itemid"));
            sqlFactory.addParameter("remarks", orderitemsRow.getString("remarks"));

            sqlFactory.addParameter("itemno", orderitemsRow.getString("itemno"));
            sqlFactory.addParameter("itemname", orderitemsRow.getString("itemname"));
            sqlFactory.addParameter("model", orderitemsRow.getString("model"));
            sqlFactory.addParameter("unit", orderitemsRow.getString("unit"));
            sqlFactory.addParameter("auxunit", orderitemsRow.getString("auxunit"));
            sqlFactory.addParameter("batchcontrol", orderitemsRow.getLong("batchcontrol"));
            sqlFactory.addParameter("delivery", orderitemsRow.getLong("delivery"));

            sqlFactory.addParameter("needdate", orderitemsRow.getString("needdate").equals("") ? "null" : orderitemsRow.getString("needdate"));
            sqlFactory.addParameter("deliverydate", orderitemsRow.getString("deliverydate").equals("") ? "null" : orderitemsRow.getString("deliverydate"));
            sqlFactory.addParameter("conversionrate", orderitemsRow.getBigDecimal("conversionrate"));
            //订购数量
            sqlFactory.addParameter("qty", orderitemsRow.getBigDecimal("qty"));
            //辅助单位数量
            sqlFactory.addParameter("auxqty", orderitemsRow.getBigDecimal("auxqty"));


            //单价,折后价（元）
            sqlFactory.addParameter("price", orderitemsRow.getBigDecimal("price"));
            //金额,折后金额（元）
            sqlFactory.addParameter("amount", orderitemsRow.getBigDecimal("amount"));
            //牌价、市场价（元）
            sqlFactory.addParameter("marketprice", orderitemsRow.getBigDecimal("marketprice"));
            //标准订单取框架内的价格

            //折前价（元）
            sqlFactory.addParameter("defaultprice", orderitemsRow.getBigDecimal("defaultprice"));
            //折前金额（元）
            sqlFactory.addParameter("defaultamount", orderitemsRow.getBigDecimal("defaultamount"));

            sqlFactory.addParameter("saleprice", orderitemsRow.getBigDecimal("saleprice"));
            sqlFactory.addParameter("length", orderitemsRow.getString("length"));
            sqlFactory.addParameter("width", orderitemsRow.getString("width"));
            sqlFactory.addParameter("spec", orderitemsRow.getString("spec"));
            sqlFactory.addParameter("customproperties", orderitemsRow.getString("customproperties"));

            sqlList.add(sqlFactory.getSQL());
        }


        dbConnect.runSqlUpdate(sqlList);
        content.put("sa_orderid", sa_orderid_copy);

        Rows ordercopyRows = dbConnect.runSqlQuery("select * from sa_order where siteid='" + siteid + "' and sa_orderid=" + sa_orderid_copy);
        if (ordercopyRows.isNotEmpty()) {
            dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid_copy, ordercopyRows.get(0).getLong("sa_accountclassid"), ordercopyRows.get(0).getLong("sys_enterpriseid")));
        }

        //查询商品费用方案方法
        new OrderItemsHelper(this).executeDataFunction(sa_orderid_copy);
        //生成摘要
        sqlFactory = new SQLFactory(this, "查询定制费用统计");
        sqlFactory.addParameter("sa_orderid", sa_orderid_copy);
        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_copy + "'");

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

        return selectDetail();
    }

    @API(title = "回款记录，查询的是核销单", apiversion = R.ID20230102164402.v1.class)
    @CACHEING
    public String query() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (t1.billno like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t2.enterprisename like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t2.abbreviation like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t5.agentnum like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t4.billno like '%").append(whereObject.getString("condition")).append("%')");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status = '").append(whereObject.getString("status")).append("'");
            }
        }
        Long sa_orderid = content.getLong("sa_orderid");
//        SQLFactory sqlFactory = new SQLFactory(this, "回款记录", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_writeoffbill", "sa_writeoffbillid", "createuserid",
                "createby", "createdate", "checkby", "checkdate", "billnodate", "status", "sys_enterpriseid", "remarks", "unwriteoffamount", "writeoffamount", "sa_cashbillid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid", "enterprisename", "abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t5", "t2.siteid = t5.siteid and t2.sys_enterpriseid = t5.sys_enterpriseid", "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sa_accountclass", "t3", "t1.siteid = t3.siteid and t1.sa_accountclassid = t3.sa_accountclassid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_cashbill", "t4", "t1.siteid = t4.siteid and t1.sa_cashbillid = t4.sa_cashbillid");
        querySQL.addQueryFields("writeoffbillidbillno", "t1.billno");
        querySQL.addQueryFields("cashbillidbillno", "t4.billno");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_writeoffbillid in(SELECT sa_writeoffbillid from sa_writeoffbill_order WHERE sa_orderid = '" + sa_orderid + "' and siteid = '" + siteid + "')");
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询企业的上下级，含支付账号信息", apiversion = R.ID20230104103702.v1.class)
    public String getString() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (t1.enterprisename like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.province like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.city like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.county like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.contact like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.phonenumber like '%").append(whereObject.getString("condition")).append("%')");
            }

        }

        sys_enterpriseid = content.containsKey("sys_enterpriseid") ? content.getLongValue("sys_enterpriseid") : sys_enterpriseid;

        SQLFactory sqlFactory = new SQLFactory(this, "查询经销商上下级");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_SQL("where", where);
//        String sql = sqlFactory.getSQL();
//        Rows rows = dbConnect.runSqlQuery(sql);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t2");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "'111' = t2.siteid ", "*");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        ArrayList<Long> sys_enterpriseids = rows.toArrayList("sys_enterpriseid", new ArrayList<>());
        //查询开票信息
        sqlFactory = new SQLFactory(this, "查询经销商默认开票信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("sys_enterpriseid", sys_enterpriseids);
        RowsMap financeRowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sys_enterpriseid");

        for (Row row : rows) {
            row.put("finance", financeRowsMap.getOrDefault(row.getString("sys_enterpriseid"), new Rows()));
            row.put("accounts", new Rows());
        }

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

    }

    @API(title = "一键填写交货日期", apiversion = R.ID20230104143102.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String updateDelivery() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        String deliverydate = content.getString("deliverydate");
        dbConnect.runSqlUpdate("UPDATE sa_orderitems SET deliverydate='" + deliverydate + "'  WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'");
        return getSucReturnObject().toString();

    }

    @API(title = "一键填写需求日期", apiversion = R.ID20230104143802.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String updateNeeddate() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");

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

        String needdate = content.getString("needdate");
        dbConnect.runSqlUpdate("UPDATE sa_orderitems SET needdate='" + needdate + "'  WHERE sa_orderid = " + sa_orderid + " and siteid = '" + siteid + "'");
        return getSucReturnObject().toString();

    }

    @API(title = "查询居间费表头", apiversion = R.ID20230105194402.v1.class)
    @CACHEING
    public String getRebateInfo() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");


        SQLFactory sqlFactory = new SQLFactory(this, "居间费结算-详情");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        return getSucReturnObject().setData(row).toString();
    }

    @API(title = "查询居间费列表", apiversion = R.ID20230105194902.v1.class)
    @CACHEING
    public String getRebateList() throws YosException {

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (t1.billno like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" and t2.enterprisename like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or t1.remarks like '%").append(whereObject.getString("condition")).append("%')");
            }

        }
        Long sa_rebatesettlementid = content.getLong("sa_rebatesettlementid");
//        SQLFactory sqlFactory = new SQLFactory(this, "居间费结算明细-列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sa_rebatesettlementid", sa_rebatesettlementid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_rebatesettlement_items", "sa_rebatesettlement_itemsid", "qty",
                "marketprice", "discountrate", "price", "amount", "rebate_discountrate", "rebate_price", "rebate");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid = t2.itemid and t1.siteid = t2.siteid", "itemno", "itemname");
        querySQL.addQueryFields("rebate_unit_price", "t1.price-t1.rebate_price");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_rebatesettlementid", sa_rebatesettlementid);
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "修改业务员", apiversion = R.ID20230213154802.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, accessoryorder.class})
    public String changeSaler() throws YosException {

        Long saler_hrid = content.getLong("saler_hrid");
        Long sa_orderid = content.getLong("sa_orderid");
        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        SQLFactory sqlFactory = new SQLFactory(this, "订单-修改业务员");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("saler_hrid", saler_hrid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);

        dbConnect.runSqlUpdate(sqlFactory.getSQL());

        return selectDetail();
    }

    @API(title = "查询订单流水信息", apiversion = R.ID20230221193203.v1.class)
    @CACHEING
    public String getOrderCashbill() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_cashbill WHERE ownertable = 'sa_order' and ownerid = " + sa_orderid + " and siteid = '" + siteid + "' ORDER BY createdate DESC");
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询核销记录", apiversion = R.ID20230225141804.v1.class)
    @CACHEING
    public String getWriteoffbill() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
//        SQLFactory sqlFactory = new SQLFactory(this, "订单核销记录", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_writeoffbill", "sa_writeoffbillid", "billno",
                "billnodate", "writeoffamount");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_writeoffbill_order", "t2", "t1.sa_writeoffbillid = t2.sa_writeoffbillid and t1.siteid = t2.siteid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_cashbill", "t3", "t1.sa_cashbillid = t3.sa_cashbillid and t1.siteid = t3.siteid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_orderitems", "t4", "t2.siteid = t4.siteid and t2.sa_orderitemsid = t4.sa_orderitemsid",
                "rowno", "itemno", "itemname");
        querySQL.addQueryFields("cashbillbillno", "t3.billno");
        querySQL.addQueryFields("ordetitemwriteoffamount", "t2.writeoffamount");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.status = '审核'");
        querySQL.setWhere("t2.sa_orderid", sa_orderid);
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询erp对接记录", apiversion = R.ID20230225141904.v1.class)
    @CACHEING
    public String getErpupdatelog() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        SQLFactory sqlFactory = new SQLFactory(this, "订单erp对接记录");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t.siteid = '111' ", "*");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询变更记录", apiversion = R.ID20230225142004.v1.class)
    @CACHEING
    public String getChange() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
//        SQLFactory sqlFactory = new SQLFactory(this, "订单变更记录", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sa_orderid", sa_orderid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_orderitems_change", "createby", "createdate",
                "remarks", "sa_orderitems_changeid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_orderitems_changemx", "t2", "t1.sa_orderitems_changeid = t2.sa_orderitems_changeid and t1.siteid = t2.siteid", "oldvalue", "newvalue");
        querySQL.addJoinTable(JOINTYPE.inner, "plm_item", "t3", "t2.itemid = t3.itemid and t2.siteid = t3.siteid",
                "itemno", "itemname", "model");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t2.type = '数量'");
        querySQL.setWhere("t1.sa_orderid", sa_orderid);
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    /**
     * 获取订单提交SQL
     *
     * @param sa_orderid
     * @return
     * @throws YosException
     */
    public String getOrderSubmitSql(Long sa_orderid, String status, String freightstatus) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "订单_提交");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("status", status);
        sqlFactory.addParameter("freightstatus", freightstatus);
        return sqlFactory.getSQL();
    }

    /**
     * 获取订单审核SQL
     *
     * @param sa_orderid
     * @return
     * @throws YosException
     */
    public String getOrderCheckSql(Long sa_orderid, String reviewtype) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "订单_审核");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("reviewtype", reviewtype);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        return sqlFactory.getSQL();
    }


    /**
     * 创建凭证实体
     *
     * @param amount
     * @param ownerid
     * @param source
     * @param sourcenotes
     * @param remarks
     * @return
     * @throws YosException
     */
    public CashbillEntity getCashbillEntity(BigDecimal amount, Long ownerid, String source, String sourcenotes, String remarks) throws YosException {
        CashbillEntity entity = new CashbillEntity();
        entity.setAmount(amount);
        entity.setDiscountamountamount(BigDecimal.ZERO);
        entity.setOwnerid(ownerid);
        entity.setOwnertable("sa_order");
        entity.setSource(source);
        entity.setRemarks(remarks);
        entity.setSourcenote(sourcenotes);
        return entity;

    }

    /**
     * 更新返利代金券余额
     *
     * @param sa_cashbillid
     * @param rebate_balance
     * @return
     * @throws YosException
     */
    public String updateCashbillSql(Long sa_cashbillid, BigDecimal rebate_balance) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "更新返利代金券");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("rebate_balance", rebate_balance);
        sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
        return sqlFactory.getSQL();
    }

    /**
     * 获取支付凭证信息
     *
     * @param sa_orderid
     * @return
     * @throws YosException
     */

    public Rows getCashbillRows(Long sa_orderid) throws YosException {

        return dbConnect.runSqlQuery("SELECT sum(amount) amount from sa_cashbill WHERE  ownerid ='" + sa_orderid + "' and ownertable='sa_order' and type = 0 and sa_cashbillid_rebate is null and siteid = '" + siteid + "' ");

    }

    public Rows getMultipleAccountsCashbillRows(Long sa_orderid) throws YosException {

        return dbConnect.runSqlQuery("SELECT sa_accountclassid,sum(amount) amount from sa_cashbill WHERE  ownerid ='" + sa_orderid + "' and ownertable='sa_order' and type = 0 and sa_cashbillid_rebate is null and siteid = '" + siteid + "' group by sa_accountclassid");

    }

    public Rows getCashbillRebateRows(Long sa_orderid) throws YosException {
        return dbConnect.runSqlQuery("SELECT  sum(amount) amount,sa_cashbillid_rebate  from sa_cashbill WHERE  ownerid ='" + sa_orderid + "' and ownertable='sa_order' and type = 0 and sa_cashbillid_rebate is not null   and siteid = '" + siteid + "' GROUP  BY sa_cashbillid_rebate");

    }

    public Long getSaleHrid(Long sys_enterpriseid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT t1.hrid FROM sa_salearea_hr t1 INNER JOIN sys_enterprise_tradefield t2 ON t1.sa_saleareaid=t2.sa_saleareaid AND t1.siteid=t2.siteid WHERE t2.sys_enterpriseid=" + sys_enterpriseid);

        if (rows.isNotEmpty()) {
            return rows.get(0).getLong("hrid");
        }

        return 0L;
    }


    @API(title = "订单ERP仓库选择", apiversion = R.ID20230228193201.v1.class)
    public String selectErpStock() throws YosException {
        long itemid = content.getLong("itemid");
        double qty = content.getDoubleValue("qty");
        Rows rows = dbConnect.runSqlQuery("select erpitemno from plm_itemextend where siteid='" + siteid + "' and itemid=" + itemid);
        if (rows.isEmpty()) {
            return getSucReturnObject().toString();
        }
        String erpitemno = rows.get(0).getString("erpitemno");
        String oraclebase = "";
        String sid = "";
        if ("NB".equals(siteid)) {
            sid = "IBPNB10";
            oraclebase = "ORACLE_NB";
        } else if ("TZ".equals(siteid)) {
            sid = "IDCZJ10";
            oraclebase = "ORACLE_TZ";
        } else if ("HY".equals(siteid)) {
            sid = "IBPJX10";
            oraclebase = "ORACLE_HY";
        }
        DBConnect oracleConnect = new DBConnect(oraclebase);
        Rows icrows = oracleConnect.runSqlQuery(
                "select WAREHOUSE,QTY,LOCATION,LOT from v_eso_actual_inventory where FITEMNO = '" + erpitemno + "' and SITEID='" + sid + "' and QTY>=" + qty);
        return getSucReturnObject().setData(icrows).toString();
    }

    @API(title = "订单手动关闭", apiversion = R.ID20230406155202.v1.class)
    public String closeOrder() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");


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


        Rows orderRow = getOrderRows(this, sa_orderid);
        if (orderRow.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在").toString();
        }
        if (orderRow.get(0).getString("status").equals("手工关闭")) {
            return getErrReturnObject().setErrMsg("订单已关闭，无法再次手工关闭").toString();
        }
        if (!orderRow.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("非审核状态无法手工关闭").toString();
        }

        Long sys_enterpriseid = orderRow.get(0).getLong("sys_enterpriseid");
        Long sa_accountclassid = orderRow.get(0).getLong("sa_accountclassid");
        if (StringUtils.isBlank(orderRow.get(0).getString("sa_accountclassinfos"))) {
            dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
            orderRow = getOrderRows(this, sa_orderid);
        } else {
            if (isJSONArray(orderRow.get(0).getString("sa_accountclassinfos"))) {
                JSONArray jsonArrayResult = JSONArray.parseArray(orderRow.get(0).getString("sa_accountclassinfos"));
                if (jsonArrayResult.isEmpty()) {
                    dbConnect.runSqlUpdate(updateAccountclassinfos(sa_orderid, sa_accountclassid, sys_enterpriseid));
                    orderRow = getOrderRows(this, sa_orderid);
                }
            }
        }
        if (dbConnect.runSqlQuery("SELECT 1 from sa_dispatch t1 left join sa_dispatch_items t2 on t1.sa_dispatchid=t2.sa_dispatchid and t1.siteid=t2.siteid left join sa_orderitems t3 on t2.sa_orderitemsid=t3.sa_orderitemsid and t2.siteid=t3.siteid WHERE t1.status not in ('关闭','手工关闭') and t1.siteid='" + siteid + "' and t3.sa_orderid=" + sa_orderid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在未关闭的发货单，无法关闭").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add("UPDATE sa_order SET STATUS='手工关闭',closedate=current_time,closeby='" + username + "' WHERE sa_orderid='" + sa_orderid + "' and siteid = '" + siteid + "'");
        sqlList.add("UPDATE sa_orderitems SET isclose=1 WHERE sa_orderid='" + sa_orderid + "' and siteid = '" + siteid + "'");

        Rows orderdetails_items = dbConnect.runSqlQuery("select itemid,sum(undeliqty) qty from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by itemid");
        for (Row orderdetail : orderdetails_items) {
            sqlList.add("update plm_item set saleqty=saleqty-" + orderdetail.getBigDecimal("qty") + " where itemid=" + orderdetail.getLong("itemid"));
        }

        String type = orderRow.get(0).getString("type");
        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select sa_promotion_itemsid,itemno,sum(undeliqty) qty  from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by sa_promotion_itemsid,itemno");
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + orderRow.get(0).getLong("sa_promotionid"));
            RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("sa_promotion_itemsid");
            for (Row orderdetail : orderdetails) {
                String sa_promotion_itemsid = orderdetail.getString("sa_promotion_itemsid");
                BigDecimal qty = orderdetail.getBigDecimal("qty");
                if (promotionitemsRowsMap.containsKey(sa_promotion_itemsid)) {
                    if (promotionitemsRowsMap.get(sa_promotion_itemsid).isNotEmpty()) {
                        sqlList.add("update sa_promotion_items set saledqty=saledqty-" + qty + " where sa_promotion_itemsid=" + sa_promotion_itemsid);
                    }
                }
            }
        }

//        String type = orderRow.get(0).getString("type");
//        if (type.equals("促销订单")) {
//            Rows orderdetails = dbConnect.runSqlQuery("select itemid,itemno,sum(undeliqty) qty  from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid + " group by itemid,itemno");
//            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + orderRow.get(0).getLong("sa_promotionid"));
//            RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("itemid");
//            for (Row orderdetail : orderdetails) {
//                String itemid = orderdetail.getString("itemid");
//                BigDecimal qty = orderdetail.getBigDecimal("qty");
//                if (promotionitemsRowsMap.containsKey(itemid)) {
//                    if (promotionitemsRowsMap.get(itemid).isNotEmpty()) {
//                        sqlList.add("update sa_promotion_items set saledqty=saledqty-" + qty + " where sa_promotion_itemsid=" + promotionitemsRowsMap.get(itemid).get(0).getLong("sa_promotion_itemsid"));
//                    }
//                }
//            }
//        }

        //已发货数量
        BigDecimal deliedqty = BigDecimal.ZERO;
        Rows rows = dbConnect.runSqlQuery("SELECT ifnull(sum(deliedqty),0) deliedqty from sa_orderitems WHERE sa_orderid=" + sa_orderid + " and siteid = '" + siteid + "'");
        if (rows.isNotEmpty()) {
            deliedqty = rows.get(0).getBigDecimal("deliedqty");
        }

        rows = dbConnect.runSqlQuery("SELECT cast(sum(undeliqty*price) as DECIMAL(16,2)) totalprice from sa_orderitems WHERE sa_orderid=" + sa_orderid + " and siteid = '" + siteid + "'");
        ArrayList<Long> sa_cashbillidList = new ArrayList<>();
        if (rows.isNotEmpty()) {
            BigDecimal totalprice = rows.get(0).getBigDecimal("totalprice");
            JSONArray sa_accountclassinfos = orderRow.get(0).getJSONArray("sa_accountclassinfos");
            JSONObject orderaccountclassinfo = new JSONObject();
            JSONObject promotionaccountclassinfo = new JSONObject();
            for (Object object : sa_accountclassinfos) {
                JSONObject jsonObject = (JSONObject) object;
                if (jsonObject.getLong("sa_accountclassid") == sa_accountclassid) {
                    orderaccountclassinfo = jsonObject;
                } else {
                    promotionaccountclassinfo = jsonObject;
                }
            }
            Rows promotion_auth = dbConnect.runSqlQuery(" select * from sa_promotion_auth where siteid='" + siteid + "'and sys_enterpriseid=" + sys_enterpriseid + " and sa_promotionid=" + orderRow.get(0).getLong("sa_promotionid"));
            long sa_promotion_authid = 0;
            if (promotion_auth.isNotEmpty()) {
                sa_promotion_authid = promotion_auth.get(0).getLong("sa_promotion_authid");
            }
            BigDecimal amount_orderaccount = orderaccountclassinfo.getBigDecimal("amount");
            if (amount_orderaccount == null || amount_orderaccount.equals("")) {
                amount_orderaccount = BigDecimal.ZERO;
            }
            if (deliedqty.compareTo(BigDecimal.ZERO) == 0) {
                totalprice = amount_orderaccount;
            }
            if (amount_orderaccount.compareTo(totalprice) >= 0) {
                System.out.println("1111");
                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(totalprice.negate());
                entity.setDiscountamountamount(BigDecimal.ZERO);
                entity.setSource("订单手工关闭");
                entity.setSourcenote(orderRow.get(0).getString("sonum"));
                entity.setOwnerid(sa_orderid);
                entity.setOwnertable("sa_order");
                entity.setRemarks("由订单号" + orderRow.get(0).getString("sonum") + "手工关闭时生成");
                entity.setType("普通费用");
                entity.setTypemx("订单关闭退款");
                JSONObject cashbillPay = Accountbalance.createCashbillPay2(this, sys_enterpriseid, orderaccountclassinfo.getLongValue("sa_accountclassid"), entity, true);
                sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                sa_cashbillidList.add(cashbillPay.getLong("sa_cashbillid"));
                sqlList.add("update sa_promotion_auth set saledamount=saledamount-" + totalprice + " where sa_promotion_authid=" + sa_promotion_authid);
            } else if (amount_orderaccount.compareTo(totalprice) < 0) {
                System.out.println("222");
                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(amount_orderaccount.negate());
                entity.setDiscountamountamount(BigDecimal.ZERO);
                entity.setSource("订单手工关闭");
                entity.setSourcenote(orderRow.get(0).getString("sonum"));
                entity.setOwnerid(sa_orderid);
                entity.setOwnertable("sa_order");
                entity.setRemarks("由" + orderRow.get(0).getString("sonum") + "订单手工关闭时自动产生");
                entity.setType("普通费用");
                entity.setTypemx("订单关闭退款");
                JSONObject cashbillPay = Accountbalance.createCashbillPay2(this, sys_enterpriseid, orderaccountclassinfo.getLongValue("sa_accountclassid"), entity, true);
                sqlList.addAll(cashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                sa_cashbillidList.add(cashbillPay.getLong("sa_cashbillid"));

                sqlList.add("update sa_promotion_auth set saledamount=saledamount-" + amount_orderaccount + " where sa_promotion_authid=" + sa_promotion_authid);

                CashbillEntity promotionentity = new CashbillEntity();
                promotionentity.setAmount((totalprice.subtract(amount_orderaccount)).negate());
                promotionentity.setDiscountamountamount(BigDecimal.ZERO);
                promotionentity.setSource("订单手工关闭");
                promotionentity.setSourcenote(orderRow.get(0).getString("sonum"));
                promotionentity.setOwnerid(sa_orderid);
                promotionentity.setOwnertable("sa_order");
                promotionentity.setRemarks("由" + orderRow.get(0).getString("sonum") + "订单手工关闭时自动产生");
                promotionentity.setType("普通费用");
                promotionentity.setTypemx("订单关闭退款");
                JSONObject cashbillPay1 = Accountbalance.createCashbillPay2(this, sys_enterpriseid, promotionaccountclassinfo.getLongValue("sa_accountclassid"), promotionentity, true);
                sqlList.addAll(cashbillPay1.getJSONArray("sqlList").toJavaList(String.class));
                sa_cashbillidList.add(cashbillPay1.getLong("sa_cashbillid"));
            }

        }

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单手动关闭", "订单手动关闭").getSQL());

        sqlList.addAll(updateIcinvbal_sale(sa_orderid, true, true));

        dbConnect.runSqlUpdate(sqlList);

        //是否自动返利结算
        new OrderItemsHelper(this).executeAutoRebate(sa_orderid, sys_enterpriseid);

        for (Long sa_cashbillid : sa_cashbillidList) {
            Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
        }
        return getSucReturnObject().toString();
    }

    @API(title = "获取当前账号的角色名", apiversion = R.ID20230524160003.v1.class)
    public String getrolename() throws YosException {
        Rows rows = dbConnect.runSqlQuery("select rolename from sys_userrole t1 inner join sys_role t2 on t1.roleid=t2.roleid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.userid=" + userid);
        RowsMap rowsMap = rows.toRowsMap("rolename");
        String rolename = "其他";
        if (rowsMap.containsKey("电商部")) {
            rolename = "电商";
        } else if (rowsMap.containsKey("工程部")) {
            rolename = "工程";
        }
        return getSucReturnObject().setData(rolename).toString();
    }

    @API(title = "查询订单中商品单价为0的记录", apiversion = R.ID2024060515370903.v1.class)
    @CACHEING
    public String newApiMethod() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        Rows rows = dbConnect.runSqlQuery("select t2.itemno,t1.* from sa_orderitems t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.sa_orderid=" + sa_orderid + " and t1.siteid='" + siteid + "' and  t1.price<=0");
        return getSucReturnObject().setData(rows).toString();
    }


    /**
     * 更新订单扣款信息
     */
    public ArrayList<String> updateAccountclassinfos(Long sa_orderid, long sa_accountclassid, long sys_enterpriseid) throws YosException {

        Rows orderRows = getOrderRows(this, sa_orderid);
        ArrayList<String> sqlList = new ArrayList<>();
        JSONArray jsonArray = new JSONArray();
        JSONObject jsonObject = new JSONObject();
        BigDecimal orderamount = getAmount(this, sa_orderid);
        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=" + (sys_enterpriseid == 0 ? orderRows.get(0).getLong("pay_enterpriseid") : sys_enterpriseid) + " where isused=1 and t1.siteid='" + siteid + "' and t1.sa_accountclassid=" + (sa_accountclassid == 0 ? orderRows.get(0).getLong("sa_accountclassid") : sa_accountclassid));
        if (accountclassrows.isEmpty()) {
            return new ArrayList<>();
        }
        if (orderRows.isEmpty()) {
            //全部优先账户扣款
            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);
            sqlList.add("update sa_order set sa_accountclassinfos='" + jsonArray + "' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
            return sqlList;
        }
        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=" + (sys_enterpriseid == 0 ? orderRows.get(0).getLong("pay_enterpriseid") : sys_enterpriseid) + " and  sa_promotionid=" + orderRows.get(0).getLong("sa_promotionid"));
        if (promotionrows.isNotEmpty()) {
            type = promotionrows.get(0).getString("type");
        }
        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=" + (sys_enterpriseid == 0 ? orderRows.get(0).getLong("pay_enterpriseid") : sys_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 sqlList;
                    }
                }
            } 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);
        }

        sqlList.add("update sa_order set sa_accountclassinfos='" + jsonArray + "' where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        return sqlList;
    }

    /**
     * 判断是否在同一个月
     *
     * @return false:不在同一个月内，true在同一个月内
     */
    public boolean isMonth(Date date1, Date date2) {
        Calendar calendar1 = Calendar.getInstance();
        calendar1.setTime(date1);
        Calendar calendar2 = Calendar.getInstance();
        calendar2.setTime(date2);
        int year1 = calendar1.get(Calendar.YEAR);
        int year2 = calendar2.get(Calendar.YEAR);
        int month1 = calendar1.get(Calendar.MONTH);
        int month2 = calendar2.get(Calendar.MONTH);
        return calendar1.get(Calendar.YEAR) == calendar2.get(Calendar.YEAR) && calendar1.get(Calendar.MONTH) == calendar2.get(Calendar.MONTH);

    }

//    @API(title = "发货单手动关闭", apiversion = R.ID20230406155302.v1.class)
//    @Deprecated
//    public String closeDispatch() throws YosException {
//
//        Long sa_dispatchid = content.getLongValue("sa_dispatchid");
//
//        ArrayList<String> sqlList = new ArrayList<>();
//        sqlList.add("UPDATE sa_dispatch SET STATUS='关闭' WHERE sa_dispatchid='" + sa_dispatchid + "' and siteid = '" + siteid + "'");
//        Rows rows = dbConnect.runSqlQuery("SELECT qty,sa_orderitemsid FROM sa_dispatch_items WHERE sa_dispatchid='" + sa_dispatchid + "' and siteid = '" + siteid + "'");
//        for (Row row : rows) {
//            Long sa_orderitemsid = row.getLong("sa_orderitemsid");
//            Long qty = row.getLong("qty");
//            sqlList.add("UPDATE sa_orderitems SET undeliqty=qty-" + qty + " WHERE sa_orderitemsid='" + sa_orderitemsid + "' and siteid = '" + siteid + "'");
//        }
//        sqlList.add(DataContrlLog.createLog(this, "sa_dispatch", sa_dispatchid, "发货单手动关闭", "发货单手动关闭").getSQL());
//
//        dbConnect.runSqlUpdate(sqlList);
//
//
//        return getSucReturnObject().toString();
//    }


    /**
     * 账户余额冻结
     *
     * @param totalamount
     * @param addorsub
     * @param remarks
     */
    public ArrayList<String> updateAccountbalance_freez(BigDecimal totalamount, boolean addorsub,
                                                        String remarks, long sys_enterpriseid, long sa_accountclassid, long sa_orderid) throws YosException {
        ArrayList<String> sqllist = new ArrayList<>();
        /******** 冻结数据 ********/
        if (addorsub) {
            long sa_accountbalanceid = 0;
            totalamount = addorsub ? totalamount : totalamount.negate();
            Rows accountbalancerows = dbConnect.runSqlQuery("select * from sa_accountbalance where sa_accountclassid=" + sa_accountclassid + " and sys_enterpriseid=" + sys_enterpriseid + " and siteid='" + siteid + "'");
            if (accountbalancerows.isNotEmpty()) {
                sa_accountbalanceid = accountbalancerows.get(0).getLong("sa_accountbalanceid");
                sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)+" + totalamount + " where sa_accountbalanceid=" + sa_accountbalanceid);
            } else {
                sa_accountbalanceid = createTableID("sa_accountbalance");
                InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_accountbalance");
                insertSQL.setUniqueid(sa_accountbalanceid);
                insertSQL.setSiteid(siteid);
                insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);
                insertSQL.setValue("freezamount", totalamount);
                insertSQL.setValue("sa_accountclassid", sa_accountclassid);
                sqllist.add(insertSQL.getSQL());
            }
            InsertSQL frezzInsertSQL = SQLFactory.createInsertSQL(this, "sa_accountbalance_freez");
            frezzInsertSQL.setUniqueid(createTableID("sa_accountbalance_freez"));
            frezzInsertSQL.setSiteid(siteid);
            frezzInsertSQL.setValue("sa_accountbalanceid", sa_accountbalanceid);
            frezzInsertSQL.setValue("sourcetable", "sa_order");
            frezzInsertSQL.setValue("sourceid", sa_orderid);
            frezzInsertSQL.setValue("amount", totalamount);
            frezzInsertSQL.setValue("remarks", remarks);
            frezzInsertSQL.setValue("createby", username);
            frezzInsertSQL.setDateValue("createdate");
            sqllist.add(frezzInsertSQL.getSQL());
        } else {
            Rows accountbalancerows = dbConnect.runSqlQuery("select * from sa_accountbalance_freez where sourceid=" + sa_orderid + " and sourcetable='sa_order' and siteid='" + siteid + "'");
            BigDecimal freezamount = accountbalancerows.sum("amount");
            if (accountbalancerows.isNotEmpty()) {
                sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)-" + freezamount + " where sa_accountbalanceid=" + accountbalancerows.get(0).getLong("sa_accountbalanceid"));
            }
            sqllist.add("delete from sa_accountbalance_freez where sourceid=" + sa_orderid + " and sourcetable='sa_order' and siteid='" + siteid + "'");
        }
        return sqllist;
    }

    /**
     * @param isinstock 是否增加库存
     * @throws YosException
     */
    public ArrayList<String> updateIcinvbal_sale(long sa_orderid, boolean isinstock, boolean close) throws YosException {
        Rows saorderdetail = dbConnect.runSqlQuery("select * from sa_orderitems where sa_orderid=" + sa_orderid + " and siteid='" + siteid + "'");
        Rows ICINVBAL_SALE = SQLFactory.createQuerySQL(this, "st_invbal_sale").setSiteid(siteid).setWhere("itemid", saorderdetail.toArrayList("itemid")).query();
        RowsMap ICINVBAL_SALERowsMap = ICINVBAL_SALE.toRowsMap("itemid");
        int i = 0;
        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : saorderdetail) {
            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;
    }

}