lsa_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.parameter.Parameter;
import beans.remind.Remind;
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 restcontroller.R;
import restcontroller.sale.cashbill.cashbill;
import restcontroller.sale.promotion.promotion;
import restcontroller.webmanage.executorService.Executor;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;

import static beans.order.Order.*;

/**
 * 销售订单
 */
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})
    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, "订单_新增");
        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 {
            Rows rows = dbConnect.runSqlQuery("SELECT status from sa_order WHERE sa_orderid = " + sa_orderid);
            if (rows.isNotEmpty()) {
                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();
                }

            } 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
        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
        if (content.containsKey("saler_hrid")) {
            hrid = content.getLongValue("saler_hrid");
        }
        if (isAdd) {
            hrid = getSaleHrid(sys_enterpriseid);
        }
        sqlFactory.addParameter("saler_hrid", hrid);
        //制单方 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")) {
                sqlFactory.addParameter("sa_accountclassid", content.getLongValue("sa_accountclassid"));
                sqlList.addAll(updateAccountclassinfos(sa_orderid, content.getLongValue("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 sa_itemsaleclass t left join plm_itemclass t2 on t.itemclassid=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();
                        }
                    }
                }
                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.getBigDecimalValue("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"));

        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)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String ship() throws YosException {
        ArrayList<String> sqlList1 = new ArrayList<>();
        Long istool = content.getLongValue("istool");
        Long sa_brandid = 0L;
        String tradefield = content.getString("tradefield");

        JSONArray items = content.getJSONArray("items");
        ArrayList<Long> itemidids = new ArrayList();
        ArrayList<Long> sa_shoppingcartids = new ArrayList();
        //判断是否是同一个品牌的商品
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            Long temp_sa_brandid = item.getLongValue("sa_brandid");
            itemidids.add(item.getLongValue("itemid"));
            sa_shoppingcartids.add(item.getLongValue("sa_shoppingcartid"));

            if (sa_brandid == 0) {
                sa_brandid = temp_sa_brandid;
            }
            if (!sa_brandid.equals(temp_sa_brandid)) {
                return getErrReturnObject().setErrMsg("请添加同一品牌的商品").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 (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";
        String type = content.getStringValue("type", false, "标准订单");
        //类型为标准订单时查询默认框架合同信息
        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);
        //营销账户类型ID
        if ((type.equals("标准订单") || type.equals("特殊订单")) && content.getString("tradefield").equals("整装工程")) {
            sqlFactory.addParameter("sa_accountclassid", "69");

        } 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));
            }
            sqlFactory.addParameter("sa_accountclassid", beans.order.Order.getDefaultAccount(siteid, type));

        }

        //品牌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
        hrid = getSaleHrid(sys_enterpriseid);

        sqlFactory.addParameter("saler_hrid", hrid);
        //制单方 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"));

        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 {
            sqlList1.addAll(updateAccountclassinfos(sa_orderid, beans.order.Order.getDefaultAccount(siteid, type), 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(false));
        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(false));
        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(false);
        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 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");
                qty = dataRows.get(0).getBigDecimal("qty");
            }
            row.put("amount", amount);
            row.put("defaultamount", defaultamount);
            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 + "'");
        row.put("saler", saleRows);


        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"))) {
                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)");
                }
            }
        }
//        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<>());
        //查询数量金额
        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(ischangeSQl.getSQL()).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", "部分开票");
            }

        }

//        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.getFieldList().remove("erpbillno");
//            rows.getFieldList().remove("agentnum");
//            rows.getFieldList().remove("projectname");
//            rows.getFieldList().remove("siteid");
//            rows.getFieldList().remove("billdate");
//            rows.getFieldList().remove("createuserid");
//            rows.getFieldList().remove("changeuserid");
//            rows.getFieldList().remove("createby");
//            rows.getFieldList().remove("changeby");
//            rows.getFieldList().remove("changedate");
//            rows.getFieldList().remove("checkby");
//            rows.getFieldList().remove("submitby");
//            rows.getFieldList().remove("closeby");
//            rows.getFieldList().remove("closedate");
////            rows.getFieldList().remove("rec_contactsid");
////            rows.getFieldList().remove("sa_contractid");
////            rows.getFieldList().remove("sa_projectid");
////            rows.getFieldList().remove("sa_accountclassid");
////            rows.getFieldList().remove("sa_accountclassinfos");
////            rows.getFieldList().remove("sa_brandid");
////
////
////            rows.getFieldList().remove("sys_enterprise_financeid");
////            rows.getFieldList().remove("sa_logiscompid");
////            rows.getFieldList().remove("saler_hrid");
////            rows.getFieldList().remove("createflag");
////            rows.getFieldList().remove("pay_enterpriseid");
////            rows.getFieldList().remove("signbacknum");
////            rows.getFieldList().remove("rebate_used");
////            rows.getFieldList().remove("rebate_userate");
////            rows.getFieldList().remove("sa_promotionid");
////            rows.getFieldList().remove("tradefieldmx");
////            rows.getFieldList().remove("reviewtype");
////            rows.getFieldList().remove("freightstatus");
////            rows.getFieldList().remove("toolcount");
////            rows.getFieldList().remove("rebateamount");
////            rows.getFieldList().remove("signbackstatus");
////            rows.getFieldList().remove("saletype");
//
////            rows.getFieldList().remove("oldid");
////            rows.getFieldList().remove("projectnote");
////            rows.getFieldList().remove("deleted");
////            rows.getFieldList().remove("abstract");
////            rows.getFieldList().remove("accountclassinfos");
////            rows.getFieldList().remove("updatek3flag");
////            rows.getFieldList().remove("salename");
//            rows.getFieldList().remove("ischange");
//
//            Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }

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

        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");
        querySQL.setWhere("(t1.status!='新建' or t1.createuserid =$userid$ or t1.createflag=1)");
        querySQL.addParameter("userid", userid);
        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!='工具借用单'");
        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})
    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.ID20230218225002.v1.class)
    public String isUseRate() throws YosException {
        Long sa_orderid = content.getLong("sa_orderid");
        boolean isused = content.getBooleanValue("isused");
        BigDecimal rebateamount = content.getBigDecimalValue("rebateamount");
        if (rebateamount.compareTo(BigDecimal.ZERO) == 0) {
            rebateamount = getMaxUsedRebateAmount(this, sa_orderid);
        }

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

        if (Parameter.get(siteid, "order_rebate_used").equals("0")) {
            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(Double.valueOf(Parameter.get(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})
    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);
        //付款账户信息
        JSONArray accountclassinfos = row.getJSONArray("sa_accountclassinfos");

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

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

//        String type = row.getString("type");
        //提交
        String status = "提交";
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        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");
        String type = row.getString("type");
        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select * from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid);
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + row.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()) {
                        BigDecimal saledqty = promotionitemsRowsMap.get(itemid).get(0).getBigDecimal("saledqty");
                        BigDecimal groupqty = promotionitemsRowsMap.get(itemid).get(0).getBigDecimal("groupqty");
                        boolean islimit = promotionitemsRowsMap.get(itemid).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=" + promotionitemsRowsMap.get(itemid).get(0).getLong("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()){
                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=saledamount+" + 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=saledamount+" + (limitamount.subtract(saledamount)) + " where sa_promotion_authid=" + promotion_auth.get(0).getLong("sa_promotion_authid"));
                        }else{
                            sqlList.add("update sa_promotion_auth set saledamount=saledamount+" + amount + " where sa_promotion_authid=" + promotion_auth.get(0).getLong("sa_promotion_authid"));
                        }

                    }


                }
            }
        }


        String order_paymentnode = Parameter.get(siteid, "order_paymentnode");
        //订单返利使用开关
        Boolean rebate_used = row.getBoolean("rebate_used");
        //站点订单返利使用开关
        String order_rebate_used = Parameter.get(siteid, "order_rebate_used");
        if (order_paymentnode.equals("1")) {
            if (order_rebate_used.equals("1") && rebate_used) {
                //站点返利金比例
                BigDecimal order_rebate_userate = BigDecimal.valueOf(Double.valueOf(Parameter.get(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();
                }

                sqlList.addAll(useRateBill(row, "订单提交"));

            } 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()) {
                    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.getString("accountname") + "账户自动支出产生");
                            //
                            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(jsonObject.getBigDecimal("amount")));
                            } else {
                                jsonObject.put("balance", jsonObject.getBigDecimalValue("balance").subtract(jsonObject.getBigDecimal("amount")));
                            }

                            sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), entity, true));
                        } 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 + "订单提交时自动产生");
                        sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
                    } else {
                        return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, sa_accountclassid, amount) + "元！").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, new ArrayList<>());
        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 ArrayList<String> 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");


        //金额=金额-返利金金额
        BigDecimal amount = beans.order.Order.getAmount(this, sa_orderid);
        //创建支出凭证
        CashbillEntity entity = getCashbillEntity(amount, sa_orderid, source,sonum, "由" + sonum + source + "时自动产生");
        sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
        //创建返利金支出凭证
        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中，多张返利金时会出现同时使用同一个余额减
            dbConnect.runSqlUpdate(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity2, true, sa_cashbillid));
            sqlList.add(updateCashbillSql(sa_cashbillid, rebate_balance));
            count++;

        }

        return sqlList;
    }


    @API(title = "审核", apiversion = R.ID20221108153502.v1.class, intervaltime = 5000)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class, cashbill.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.get(siteid, "order_paymentnode");
        //订单返利使用开关
        Boolean rebate_used = row.getBoolean("rebate_used");
        //站点订单返利使用开关
        String order_rebate_used = Parameter.get(siteid, "order_rebate_used");
        if (order_paymentnode.equals("2")) {
            if (order_rebate_used.equals("1") && rebate_used) {
                //站点返利金比例
                BigDecimal order_rebate_userate = BigDecimal.valueOf(Double.valueOf(Parameter.get(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();
                }

                sqlList.addAll(useRateBill(row, "订单审核"));

            } 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.getString("accountname") + "账户自动支出产生");
                            jsonObject.put("balance", jsonObject.getBigDecimalValue("balance").subtract(jsonObject.getBigDecimal("amount")));
                            sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, jsonObject.getLong("sa_accountclassid"), entity, true));
                        } 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 + "订单审核时自动产生");
                        sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
                    } else {
                        return getErrReturnObject().setErrMsg("账户余额不足，还差" + Accountbalance.InsufficientBalance(this, pay_enterpriseid, sa_accountclassid, amount) + "元！").toString();
                    }
                }
            }
        }
        String message = "您的订单【" + sonum + "】已审核！";

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


        dbConnect.runSqlUpdate(sqlList);

        Rows orderBrand = dbConnect.runSqlQuery("select brandname from sa_brand where siteid='" + siteid + "' and sa_brandid=" + row.getLong("sa_brandid"));
        if (Parameter.get("system.erp_dockswitch").equalsIgnoreCase("true")) {
            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})
    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.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");

        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.get(siteid, "order_paymentnode");
        if (order_paymentnode.equals("2")) {
            sqlList.addAll(returnPayBill(row, "订单反审核",sonum, "由" + sonum + "订单反审核时自动产生"));
        }


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

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

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

    @API(title = "预提交反预提交", apiversion = R.ID20230331152503.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.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());

        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})
    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})
    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();
        }
        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);
        Row row = rows.get(0);
        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");

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

        String type = row.getString("type");
        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select * from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid);
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + row.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"));
                    }
                }
            }

            //促销订单经销商限购金额判断
            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");
                if(islimit){
                    sqlList.add("update sa_promotion_auth set saledamount=saledamount-" + 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));
//        }

        sqlList.addAll(returnPayBill(row, "订单退回",sonum, "由" + sonum + "订单退回时自动产生"));

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

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

        String type = row.getString("type");
        if (type.equals("促销订单")) {
            Rows orderdetails = dbConnect.runSqlQuery("select * from sa_orderitems where siteid='" + siteid + "' and sa_orderid=" + sa_orderid);
            Rows promotionitems = dbConnect.runSqlQuery("select * from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid=" + row.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"));
                    }
                }
            }
        }

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


        sqlList.addAll(returnPayBill(row, "订单撤回", sonum,"由" + sonum + "订单撤回时自动产生"));

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

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

    @API(title = "交期待确认", apiversion = R.ID20230102143502.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.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, new ArrayList<>());
        return getSucReturnObject().toString();
    }

    @API(title = "确认交期，操作交期待确认的订单", apiversion = R.ID20221230094802.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.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})
    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.get(siteid, "order_paymentnode");
        if (order_paymentnode.equals("2")) {
            sqlList.addAll(returnPayBill(row, "订单_交期撤回",sonum, "由" + sonum + "订单交期撤回时自动产生"));
        }

        sqlList.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "订单交期撤回", "订单交期撤回：" + amount).getSQL());
        dbConnect.runSqlUpdate(sqlList);
        sendMsg("订单交期撤回" + sonum, sa_orderid, sys_enterpriseid);
        return getSucReturnObject().toString();
    }


    public ArrayList<String> returnPayBill(Row orderrow, String source, String sourcenotes, String remarks) throws YosException {
        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);
                    sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, row.getLong("sa_accountclassid"), entity, true));
                }
            }


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

    /**
     * 邮件发送验证
     *
     * @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, new ArrayList<>());
                break;
            case "燃气":
                Executor.sendEml(this, "ordertradefieldrq", sa_orderid, new ArrayList<>());
                break;
            case "给水":
                Executor.sendEml(this, "ordertradefieldgs", sa_orderid, new ArrayList<>());
                break;
            case "暖通":
                Executor.sendEml(this, "ordertradefieldnt", sa_orderid, new ArrayList<>());
                break;
            case "消防":
                Executor.sendEml(this, "ordertradefieldxf", sa_orderid, new ArrayList<>());
                break;
        }
        if (orderBrand.isNotEmpty()) {
            switch (orderBrand.get(0).getString("brandname")) {
                case "全品牌":
                    Executor.sendEml(this, "orderbrand", sa_orderid, new ArrayList<>());
                    break;
                case "班尼戈":
                    Executor.sendEml(this, "orderbrandbng", sa_orderid, new ArrayList<>());
                    break;
                case "中捷":
                    Executor.sendEml(this, "orderbrandzj", sa_orderid, new ArrayList<>());
                    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})
    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.get("system.erp_dockswitch").equalsIgnoreCase("true")) {
            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));
                row.putIfAbsent("sumqty", rows2.get(0).getBigDecimal("sumqty").setScale(2));
            }
            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("t3.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.phonenumber 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);
        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, "订单_出库单列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        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, "查询订单进度列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        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);

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

    @API(title = "一键复制订单", apiversion = R.ID20230102144502.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.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"));
        //单据日期
        sqlFactory.addParameter("billdate", row.getString("billdate"));
        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"));


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

        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());
        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, "查询经销商上下级", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);

        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})
    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})
    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, "居间费结算-详情", pageSize, pageNumber, pageSorting);
        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());

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

    @API(title = "修改业务员", apiversion = R.ID20230213154802.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.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());
        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对接记录", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        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());
        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.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 hrid from sys_enterprise_tradefield WHERE sys_enterpriseid = " + sys_enterpriseid + " and hrid is not null and siteid='" + siteid + "'");

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

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

        return hrid;
    }


    @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 (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 rows = dbConnect.runSqlQuery("SELECT sum(undeliqty*price) totalprice from sa_orderitems WHERE sa_orderid=" + sa_orderid + " and siteid = '" + siteid + "'");
        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");
            }
            if(orderaccountclassinfo.getBigDecimal("amount").compareTo(totalprice)>=0){
                 System.out.println("1111");
                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(totalprice.negate());
                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("订单关闭退款");
                sqlList.addAll(Accountbalance.createCashbillPay(this, sys_enterpriseid, orderaccountclassinfo.getLongValue("sa_accountclassid"), entity, true));
                sqlList.add("update sa_promotion_auth set saledamount=saledamount-" + totalprice + " where sa_promotion_authid=" + sa_promotion_authid);
            }else if(orderaccountclassinfo.getBigDecimal("amount").compareTo(totalprice)<0){
                System.out.println("222");
                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(orderaccountclassinfo.getBigDecimal("amount").negate());
                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("订单关闭退款");
                sqlList.addAll(Accountbalance.createCashbillPay(this, sys_enterpriseid, orderaccountclassinfo.getLongValue("sa_accountclassid"), entity, true));

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

                CashbillEntity  promotionentity = new CashbillEntity();
                promotionentity.setAmount((totalprice.subtract(orderaccountclassinfo.getBigDecimal("amount"))).negate());
                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("订单关闭退款");
                sqlList.addAll(Accountbalance.createCashbillPay(this, sys_enterpriseid,  promotionaccountclassinfo.getLongValue("sa_accountclassid"),  promotionentity, true));

            }

        }

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

        dbConnect.runSqlUpdate(sqlList);

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

    /**
     * 更新订单扣款信息
     */
    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 (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;
    }


//    @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();
//    }
}