cucu_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.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
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)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String insertOrUpdate() throws YosException {
        //表名
        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;
        }
        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("提交")) {
                    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
        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 && 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);
            }
        } else {
            //订单更新时
            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"));

        String sql = sqlFactory.getSQL();

        dbConnect.runSqlUpdate(sql);

        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 {
        Long istool = content.getLongValue("istool");
        Long sa_brandid = 0L;
        String tradefield = content.getString("tradefield");

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


        //表名
        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{
            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"));

        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 itemid in " + itemidids + " 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);

        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);
        //查询数据：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)
    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(")");
            }
            //状态
            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("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 t1.billdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).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)");
                }
            }
        }
        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);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        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().add("amount");
            rows.getFieldList().add("qty");
            Rows uploadRows = uploadExcelToObs("order", "订单列表", rows, getTitleMap());
            return getSucReturnObject().setData(uploadRows).toString();
        }

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

    @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(")");
            }
            //状态
            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 t1.billdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).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);

        //查询数据：amount，defaultamount，qty
        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");
        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.getFieldList().add("amount");
            rows.getFieldList().add("qty");
            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 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();
        }

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

//        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=saledqty+"+qty+" where sa_promotion_itemsid="+promotionitemsRowsMap.get(itemid).get(0).getLong("sa_promotion_itemsid"));
                    }
                }
            }
        }



        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("账户余额不足").toString();
                }

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

            } else {
                if (Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) {
                    CashbillEntity entity = getCashbillEntity(amount, sa_orderid, "订单提交", "由" + sonum + "订单提交时自动产生");
                    sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
                } else {
                    return getErrReturnObject().setErrMsg("账户余额不足").toString();
                }
            }
        }

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

        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 + 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 + source + "时自动产生");
                rebateamount = rebateamount.subtract(rebate_balance);
                rebate_balance = BigDecimal.ZERO;


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

            }
            //返利代金券大于返利金金额
            if (rebate_balance.compareTo(rebateamount) == 1) {
                entity2 = getCashbillEntity(rebateamount, sa_orderid, source, "【返利金】由" + 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);
        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();
        }
//        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 (Accountbalance.judgeBalance(this, pay_enterpriseid, sa_accountclassid, amount)) {
                    CashbillEntity entity = getCashbillEntity(amount, sa_orderid, "订单审核", "由" + sonum + "订单审核时自动产生");
                    sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
                } else {
                    return getErrReturnObject().setErrMsg("账户余额不足").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.status!='关闭' and t1.siteid='" + siteid + "' and t3.sa_orderid=" + sa_orderid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在发货单，无法反审核").toString();
        }
        //验证结算企业
        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 + "订单反审核时自动产生"));
        }


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

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

        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 + "订单退回时自动产生"));

        String message = "您的订单【" + sonum + "】 金额为【" + amount + "】 已退回，退回原因【" + content.getStringValue("backreason") + "】，请注意查看";

        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();
        }
        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 + "订单撤回时自动产生"));

        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();
        }
        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();
        }
        //交期待确认
        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();
        }
        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 + "订单交期撤回时自动产生"));
        }

        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 remarks) throws YosException {
        Long sa_orderid = orderrow.getLong("sa_orderid");
        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, "【返利金】" + remarks);
                    sqlList.addAll(beans.order.Order.createRebateBillPay(this, entity, pay_enterpriseid, sa_cashbillid_rebate));
                }
            }


        }
        //金额
        Rows amountRows = getCashbillRows(sa_orderid);
        if (amountRows.isNotEmpty()) {
            amount = amountRows.get(0).getBigDecimal("amount");
            if (amount.compareTo(BigDecimal.ZERO) > 0) {
                CashbillEntity entity = getCashbillEntity(amount.negate(), sa_orderid, source, remarks);
                sqlList.addAll(Accountbalance.createCashbillPay(this, pay_enterpriseid, sa_accountclassid, entity, true));
            }
        }

        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();
        }
        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);
        return getSucReturnObject().setData(rows).toString();
    }

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

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


        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
        sqlFactory.addParameter("sys_enterpriseid", row.getLong("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", row.getLong("sys_enterprise_financeid"));
        //物流公司档案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("pay_enterpriseid", row.getLong("pay_enterpriseid"));
        //单据日期
        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(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);

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

        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
     * @return
     * @throws YosException
     */
    public CashbillEntity getCashbillEntity(BigDecimal amount, Long ownerid, String source, String remarks) throws YosException {
        CashbillEntity entity = new CashbillEntity();
        entity.setAmount(amount);
        entity.setOwnerid(ownerid);
        entity.setOwnertable("sa_order");
        entity.setSource(source);
        entity.setRemarks(remarks);
        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 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");

        Rows orderRow = getOrderRows(this, sa_orderid);
        if (orderRow.isEmpty()) {
            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!='关闭' 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='关闭' 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");
            if (totalprice.compareTo(BigDecimal.ZERO) > 0) {
                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(totalprice.negate());
                entity.setOwnerid(sa_orderid);
                entity.setOwnertable("sa_order");
                entity.setRemarks("由"+orderRow.get(0).getString("sonum")+"订单手工关闭时自动产生");
                sqlList.addAll(Accountbalance.createCashbillPay(this, sys_enterpriseid, sa_accountclassid, entity, true)) ;
            }

        }

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

        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

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