package beans.stockbill.bills;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.parameter.Parameter;
import com.alibaba.fastjson2.JSONObject;
import common.BaseClass;
import common.Controller;
import common.YosException;
import common.data.*;
import org.apache.commons.lang.StringUtils;

import java.math.BigDecimal;
import java.util.ArrayList;

public class XSCK extends BasicBill {
    long sys_enterpriseid;

    public XSCK(Controller controller, long st_stockbillid) throws YosException {
        super(controller, st_stockbillid);
        sys_enterpriseid = billRow.getLong("sys_enterpriseid");
    }

    @Override
    public void checkValidate(boolean ischeck) throws YosException {
        super.checkValidate(ischeck);

        Rows accountbalancerows = dbConnect.runSqlQuery("select * from  sa_accountbalance t1 inner join sa_accountclass t2 on  t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid where t2.accountname='现金账户' and t1.sys_enterpriseid=" + sys_enterpriseid);
        if (accountbalancerows.isEmpty()) {
            throw new YosException("账户不存在");
        }
        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        if (ischeck) {
            if (itemRows.min("payamount").doubleValue() < 0) {
                throw new YosException("扣款金额需大于0");
            }
            if (StringUtils.isBlank(billRow.getString("outplace"))) {
                throw new YosException("发货地不可为空");
            }
            if ((billRow.getBigDecimal("payamount").add(billRow.getBigDecimal("paydiscountamount").abs())).compareTo(itemRows.sum("amount")) != 0) {
                throw new YosException("扣款金额与应扣金额不符，不能审核");
            }
            BigDecimal discountamount = accountbalancerows.get(0).getBigDecimal("discountamount");
            if (billRow.getBigDecimal("paydiscountamount").abs().compareTo(discountamount) > 0) {
                throw new YosException("优惠金额不能大于可用优惠金额");
            }
            if(billRow.getString("sourceobject").equals("tpartreimbursement")){
                QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_tpartreimbursementdetail", "sa_tpartreimbursementdetailid","isverified","qty","sa_orderitemsid");
                querySQL.setTableAlias("t1");
                querySQL.addJoinTable(BaseClass.JOINTYPE.inner, "sa_tpartreimbursement", "t3", "t1.sa_tpartreimbursementid = t3.sa_tpartreimbursementid AND t1.siteid = t3.siteid");
                querySQL.setSiteid(siteid);
                querySQL.setWhere("t1.hongbillno='"+billno+"'");
                Rows rows = querySQL.query();
                for (Row row : rows) {
                    if (row.getBoolean("isverified")) {
                        throw new YosException("存在记录已核销,请检查");
                    }
                }
            }


            /*
              库存校验
             */
            /*
            其他校验
             */
        } else {
            if (billRow.getBoolean("isreceiver")) {
                throw new YosException("经销商已经确认收货，不可进行反审核");
            }
            /*
              库存校验
             */
             /*
            其他校验
             */
        }
        //库存校验
        RowsMap invbalsRowsMap = SQLFactory.createQuerySQL(dbConnect, "st_invbal").setWhere("siteid", siteid).setWhere("itemid", itemRows.toArrayList("itemid")).query().toRowsMap("itemid");
        for(Row row : itemRows){
            if(!row.getBoolean("isnegative") && row.getBoolean("ismodule")){
                long itemid = row.getLong("itemid");
                long stockid = row.getLong("stockid");
                BigDecimal qty =row.getBigDecimal("qty");
                if(!isInStock(ischeck)){
                    if (!invbalsRowsMap.containsKey(String.valueOf(itemid)) || !invbalsRowsMap.get(String.valueOf(itemid)).toRowsMap("stockid").containsKey(String.valueOf(stockid))) {
                        throw new YosException("行【"+row.getString("itemno")+"】不能负库存出库");
                    } else {
                        BigDecimal invbalqty = invbalsRowsMap.get(String.valueOf(itemid)).toRowsMap("stockid").get(String.valueOf(stockid)).get(0).getBigDecimal("qty");
                        if(invbalqty.compareTo(qty)<0){
                            throw new YosException("行【"+row.getString("itemno")+"】不能负库存出库");
                        }
                    }
                }

            }
        }

        if ((ischeck && rb == 1) || (!ischeck && rb == 0)) {
//            if (order_paymentnode.equals("3") && !Accountbalance.judgeBalance(dbConnect, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), itemRows.toRowsMap("sa_orderitemsidnum").get("0").sum("amount"))) {
//                //throw new YosException("账户余额不足，还差" + Accountbalance.InsufficientBalance(dbConnect, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), itemRows.toRowsMap("sa_orderitemsidnum").get("0").sum("amount")) + "元！");
//            }
            if(!billRow.getString("sourceobject").equals("tpartreimbursement")){
                Rows rowsjudge = dbConnect.runSqlQuery("select * from (select sa_orderitemsid,sum(qty) qty from st_stockbill_items where st_stockbillid ='" + st_stockbillid + "' group by sa_orderitemsid) t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid where t1.qty>t2.undeliqty");
                if (rowsjudge.isNotEmpty()) {
                    if(ischeck){
                        throw new YosException("存在商品已完全出库，无法审核");
                    }else{
                        throw new YosException("存在商品已完全出库，无法反审核");
                    }

                }
            }

        }
    }

    @Override
    public ArrayList<String> getCheckSql(boolean ischeck) throws YosException {

        ArrayList<String> sqlList = super.getCheckSql(ischeck);
        Rows accountbalancerows = dbConnect.runSqlQuery("select * from  sa_accountbalance t1 inner join sa_accountclass t2 on  t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid where t2.accountname='现金账户' and t1.sys_enterpriseid=" + sys_enterpriseid);
        if (ischeck) {
            String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
            if (rb == 1) {
                if (order_paymentnode.equals("3")) {
                    CashbillEntity entity = getCashbillEntity(billRow.getBigDecimal("payamount"), billRow.getBigDecimal("paydiscountamount").abs(), st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成","货款");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    if(!(billRow.getBigDecimal("payamount").compareTo(BigDecimal.ZERO)==0 && billRow.getBigDecimal("paydiscountamount").abs().compareTo(BigDecimal.ZERO)==0)){
                        sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    }
                }
                Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,t1.sa_orderitemsid,t1.siteid from st_stockbill_items t1  where t1.sa_orderitemsid>0 and t1.st_stockbillid=" + st_stockbillid + " group by t1.sa_orderitemsid,t1.siteid");
                for (Row row : rowsDispatchDetailGroup) {
                    sqlList.add("update sa_orderitems set undeliqty=undeliqty-" + row.getDouble("qty") + ",deliedqty=ifnull(deliedqty,0)+" + row.getDouble("qty") + ",offstatus=if(billingstatus='保内收费','待核销','不可核销'),offdate=CURRENT_TIME  where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                    sqlList.add("update sa_orderitems set tobeoffqty=case when tobeoffqty+" + row.getDouble("qty") + ">canoffqty then canoffqty else tobeoffqty+" + row.getDouble("qty") + " end where billingstatus='保内收费' and sa_orderitemsid=" + row.getLong("sa_orderitemsid"));

                }
                for (Row row : itemRows) {
                    if (row.getBoolean("skucontrol")) {
                        sqlList.add("update st_stockbill_items set wmsuploadflag2=1 where st_stockbill_itemsid=" + row.getLong("st_stockbill_itemsid"));
                    }
                }
                sqlList.addAll(updateAccountbalance_freez(isInStock(ischeck)));
                //判断是否关闭订单
                int orderautoclosepoint = Parameter.getInteger(siteid, "orderautoclosepoint");
                if(orderautoclosepoint==2){
                    ArrayList<Long> sa_orderids = itemRows.toArrayList("sa_orderid",new ArrayList<>());
                    QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_order", "sa_orderid");
                    querySQL.addJoinTable(BaseClass.JOINTYPE.inner, "sa_orderitems", "t2", "t2.sa_orderid = t1.sa_orderid and t2.siteid = t1.siteid",
                            "undeliqty","sa_orderitemsid");
                    querySQL.setTableAlias("t1");
                    querySQL.setWhere("t1.siteid", siteid);
                    querySQL.setWhere("t1.sa_orderid", sa_orderids);
                    querySQL.setWhere("t1.status='审核' and t2.undeliqty!=0");
                    Rows rows = querySQL.query();
                    RowsMap rowsMap =rows.toRowsMap("sa_orderid");
                    RowsMap itemRowsMap=itemRows.toRowsMap("sa_orderid");
                    for(Long sa_orderid : sa_orderids){
                        Rows stockbillitemrows = itemRowsMap.getOrDefault(String.valueOf(sa_orderid),new Rows());
                        Rows orderrows = rowsMap.getOrDefault(String.valueOf(sa_orderid),new Rows());
                        RowsMap stockbillitemrowsMap =stockbillitemrows.toRowsMap("sa_orderitemsid");
                        int count=0;
                        for(Row row : orderrows){
                            if(stockbillitemrowsMap.containsKey(row.getString("sa_orderitemsid"))){
                                if(stockbillitemrowsMap.getOrDefault(row.getString("sa_orderitemsid"),new Rows()).sum("qty").compareTo(row.getBigDecimal("undeliqty"))==0){
                                    count++;
                                }
                            }
                        }
                        if(orderrows.size()==count){
                            sqlList.add("update sa_order set status='关闭',closeby='"+controller.username+"',closedate=CURRENT_TIME where sa_orderid=" +sa_orderid);
                            sqlList.add("UPDATE sa_accountbalance t1 " +
                                    "INNER JOIN sa_accountbalance_freez t2 ON t1.sa_accountbalanceid = t2.sa_accountbalanceid " +
                                    "SET t1.freezamount = case when ifnull(t1.freezamount,0)>t2.amount then t1.freezamount-t2.amount else 0 end " +
                                    "WHERE t1.sa_accountbalanceid="+accountbalancerows.get(0).getLong("sa_accountbalanceid")+" and t2.sourcetable='sa_order' and t2.sourceid="+sa_orderid);
                            sqlList.add("delete from sa_accountbalance_freez t1 WHERE t1.sourcetable='sa_order' and t1.sourceid="+sa_orderid);
                        }
                    }

                }
            } else {
                if (order_paymentnode.equals("3")) {
                    CashbillEntity entity = getCashbillEntity(billRow.getBigDecimal("payamount").negate(), billRow.getBigDecimal("paydiscountamount").abs().negate(), st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成","货款");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    if(!(billRow.getBigDecimal("payamount").compareTo(BigDecimal.ZERO)==0 && billRow.getBigDecimal("paydiscountamount").abs().compareTo(BigDecimal.ZERO)==0)){
                        sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    }

                }
                if(!billRow.getString("sourceobject").equals("tpartreimbursement")){
                    Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from st_stockbill_items where sa_orderitemsid>0 and st_stockbillid=" + st_stockbillid + ")");
                    RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
                    Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,t1.sa_orderitemsid,t1.siteid from st_stockbill_items t1  where t1.sa_orderitemsid>0 and t1.st_stockbillid=" + st_stockbillid + " group by t1.sa_orderitemsid,t1.siteid");
                    for (Row row : rowsDispatchDetailGroup) {
                        sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty")) + ",deliedqty=" + (rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));

                        sqlList.add("update sa_orderitems set tobeoffqty=case when tobeoffqty-" + row.getDouble("qty") + ">0 then tobeoffqty-" + row.getDouble("qty")+" else 0 end where billingstatus='保内收费' and sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                    }
                    for (Row row : itemRows) {
                        if (row.getBoolean("skucontrol")) {
                            sqlList.add("update st_stockbill_items set wmsuploadflag2=0 where st_stockbill_itemsid=" + row.getLong("st_stockbill_itemsid"));
                        }
                    }
                }else{
                    QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_tpartreimbursementdetail", "sa_tpartreimbursementdetailid","isverified","qty","sa_orderitemsid");
                    querySQL.setTableAlias("t1");
                    querySQL.addJoinTable(BaseClass.JOINTYPE.inner, "sa_tpartreimbursement", "t3", "t1.sa_tpartreimbursementid = t3.sa_tpartreimbursementid AND t1.siteid = t3.siteid");
                    querySQL.setSiteid(siteid);
                    querySQL.setWhere("t1.hongbillno='"+billno+"'");

                    Rows rows = querySQL.query();


                    QuerySQL querySQLgroup = SQLFactory.createQuerySQL(controller, "sa_tpartreimbursementdetail", "sa_orderitemsid");
                    querySQLgroup.setTableAlias("t1");
                    querySQLgroup.addJoinTable(BaseClass.JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid");
                    querySQLgroup.setSiteid(siteid);
                    querySQLgroup.addQueryFields("sumqty", "sum(t1.checkqty)");
                    querySQLgroup.setWhere("t1.hongbillno='"+ billno+"'");
                    querySQLgroup.addGroupBy("sa_orderitemsid");
                    Rows rowsgroup = querySQLgroup.query();

                    for (Row row : rows) {
                        sqlList.add("update sa_tpartreimbursementdetail set isverified=1 where sa_tpartreimbursementdetailid=" + row.getLong("sa_tpartreimbursementdetailid"));
                    }
                    for (Row row : rowsgroup) {
                        sqlList.add("update sa_orderitems set verifiedqty=ifnull(verifiedqty,0)+"+row.getBigDecimal("sumqty")+" where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                    }

                }
                int orderautoclosepoint = Parameter.getInteger(siteid, "orderautoclosepoint");
                if(orderautoclosepoint==2){
                    ArrayList<Long> sa_orderids = itemRows.toArrayList("sa_orderid",new ArrayList<>());
                    for(Long sa_orderid : sa_orderids){
                        sqlList.add("update sa_order set status='审核',closeby='',closedate=NULL where sa_orderid=" +sa_orderid);
                    }
                }
            }


        } else {
            String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
            if (rb == 1) {
                if (order_paymentnode.equals("3")) {
                    Rows cashbillRows =dbConnect.runSqlQuery("select amount,discountamount from sa_cashbill where ownerid="+st_stockbillid +" and ownertable='st_stockbill' and type=0 and class='货款'");
                    CashbillEntity entity = getCashbillEntity(cashbillRows.sum("amount").negate(), cashbillRows.sum("discountamount").negate(), st_stockbillid, "销售出库单反审核", billno, "由销售出库单" + billno + "反审核时生成","货款");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    if(!(cashbillRows.sum("amount").negate().compareTo(BigDecimal.ZERO)==0 && cashbillRows.sum("discountamount").negate().compareTo(BigDecimal.ZERO)==0)){
                        sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    }
                }
                Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from st_stockbill_items where sa_orderitemsid>0 and st_stockbillid=" + st_stockbillid + ")");
                RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
                Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,t1.sa_orderitemsid,t1.siteid from st_stockbill_items t1  where t1.sa_orderitemsid>0 and t1.st_stockbillid=" + st_stockbillid + " group by t1.sa_orderitemsid,t1.siteid");
                for (Row row : rowsDispatchDetailGroup) {
                    sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty")) + ",deliedqty=" + (rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));

                    sqlList.add("update sa_orderitems set tobeoffqty=case when tobeoffqty-" + row.getDouble("qty") + ">0 then tobeoffqty-" + row.getDouble("qty")+" else 0 end where billingstatus='保内收费' and sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                }
                for (Row row : itemRows) {
                    if (row.getBoolean("skucontrol")) {
                        sqlList.add("update st_stockbill_items set wmsuploadflag2=0 where st_stockbill_itemsid=" + row.getLong("st_stockbill_itemsid"));
                    }
                }
                //判断是否关闭订单
                int orderautoclosepoint = Parameter.getInteger(siteid, "orderautoclosepoint");
                if(orderautoclosepoint==2){
                    ArrayList<Long> sa_orderids = itemRows.toArrayList("sa_orderid",new ArrayList<>());
                    for(Long sa_orderid : sa_orderids){
                        sqlList.add("update sa_order set status='审核',closeby='',closedate=NULL where sa_orderid=" +sa_orderid);
                    }
                }
                sqlList.addAll(updateAccountbalance_freez(isInStock(ischeck)));
            } else {
                if (order_paymentnode.equals("3")) {
                    Rows cashbillRows =dbConnect.runSqlQuery("select amount,discountamount from sa_cashbill where ownerid="+st_stockbillid +" and ownertable='st_stockbill' and type=0 and class='货款'");
                    CashbillEntity entity = getCashbillEntity(cashbillRows.sum("amount").negate(), cashbillRows.sum("discountamount").negate(), st_stockbillid, "销售出库单反审核", billno, "由销售出库单" + billno + "反审核时生成","货款");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    if(!(cashbillRows.sum("amount").negate().compareTo(BigDecimal.ZERO)==0 && cashbillRows.sum("discountamount").negate().compareTo(BigDecimal.ZERO)==0)){
                        sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    }

                }
                if(!billRow.getString("sourceobject").equals("tpartreimbursement")){
                    Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,t1.sa_orderitemsid,t1.siteid from st_stockbill_items t1  where t1.sa_orderitemsid>0 and t1.st_stockbillid=" + st_stockbillid + " group by t1.sa_orderitemsid,t1.siteid");
                    for (Row row : rowsDispatchDetailGroup) {
                        sqlList.add("update sa_orderitems set undeliqty=undeliqty-" + row.getDouble("qty") + ",deliedqty=ifnull(deliedqty,0)+" + row.getDouble("qty") + ",offstatus=if(billingstatus='保内收费','待核销','不可核销'),offdate=CURRENT_TIME  where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                        sqlList.add("update sa_orderitems set tobeoffqty=case when tobeoffqty+" + row.getDouble("qty") + ">canoffqty then canoffqty else tobeoffqty+" + row.getDouble("qty") + " end where billingstatus='保内收费' and sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                    }
                    for (Row row : itemRows) {
                        if (row.getBoolean("skucontrol")) {
                            sqlList.add("update st_stockbill_items set wmsuploadflag2=1 where st_stockbill_itemsid=" + row.getLong("st_stockbill_itemsid"));
                        }
                    }
                }else{
                    QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_tpartreimbursementdetail", "sa_tpartreimbursementdetailid","isverified","qty","sa_orderitemsid");
                    querySQL.setTableAlias("t1");
                    querySQL.addJoinTable(BaseClass.JOINTYPE.inner, "sa_tpartreimbursement", "t3", "t1.sa_tpartreimbursementid = t3.sa_tpartreimbursementid AND t1.siteid = t3.siteid");
                    querySQL.setSiteid(siteid);
                    querySQL.setWhere("t1.hongbillno='"+billno+"'");
                    Rows rows = querySQL.query();


                    QuerySQL querySQLgroup = SQLFactory.createQuerySQL(controller, "sa_tpartreimbursementdetail", "sa_orderitemsid");
                    querySQLgroup.setTableAlias("t1");
                    querySQLgroup.addJoinTable(BaseClass.JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid");
                    querySQLgroup.setSiteid(siteid);
                    querySQLgroup.addQueryFields("sumqty", "sum(t1.checkqty)");
                    querySQLgroup.setWhere("t1.hongbillno='"+ billno+"'");
                    querySQLgroup.addGroupBy("sa_orderitemsid");
                    Rows rowsgroup = querySQLgroup.query();

                    for (Row row : rows) {
                        sqlList.add("update sa_tpartreimbursementdetail set isverified=0 where sa_tpartreimbursementdetailid=" + row.getLong("sa_tpartreimbursementdetailid"));
                    }
                    for (Row row : rowsgroup) {
                        sqlList.add("update sa_orderitems set verifiedqty=ifnull(verifiedqty,0)-"+row.getBigDecimal("sumqty")+" where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                    }
                }
                //判断是否关闭订单
                int orderautoclosepoint = Parameter.getInteger(siteid, "orderautoclosepoint");
                if(orderautoclosepoint==2){
                    ArrayList<Long> sa_orderids = itemRows.toArrayList("sa_orderid",new ArrayList<>());
                    QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_order", "sa_orderid");
                    querySQL.addJoinTable(BaseClass.JOINTYPE.inner, "sa_orderitems", "t2", "t2.sa_orderid = t1.sa_orderid and t2.siteid = t1.siteid",
                            "undeliqty","sa_orderitemsid");
                    querySQL.setTableAlias("t1");
                    querySQL.setWhere("t1.siteid", siteid);
                    querySQL.setWhere("t1.sa_orderid", sa_orderids);
                    querySQL.setWhere("t1.status='审核' and t2.undeliqty!=0");
                    Rows rows = querySQL.query();
                    RowsMap rowsMap =rows.toRowsMap("sa_orderid");
                    RowsMap itemRowsMap=itemRows.toRowsMap("sa_orderid");
                    for(Long sa_orderid : sa_orderids){
                        Rows stockbillitemrows = itemRowsMap.getOrDefault(String.valueOf(sa_orderid),new Rows());
                        Rows orderrows = rowsMap.getOrDefault(String.valueOf(sa_orderid),new Rows());
                        RowsMap stockbillitemrowsMap =stockbillitemrows.toRowsMap("sa_orderitemsid");
                        int count=0;
                        for(Row row : orderrows){
                            if(stockbillitemrowsMap.containsKey(row.getString("sa_orderitemsid"))){
                                if(stockbillitemrowsMap.getOrDefault(row.getString("sa_orderitemsid"),new Rows()).sum("qty").compareTo(row.getBigDecimal("undeliqty"))==0){
                                    count++;
                                }
                            }
                        }
                        if(orderrows.size()==count){
                            sqlList.add("update sa_order set status='关闭',closeby='"+controller.username+"',closedate=CURRENT_TIME where sa_orderid=" +sa_orderid);
                            sqlList.add("UPDATE sa_accountbalance t1 " +
                                    "INNER JOIN sa_accountbalance_freez t2 ON t1.sa_accountbalanceid = t2.sa_accountbalanceid " +
                                    "SET t1.freezamount = case when ifnull(t1.freezamount,0)>t2.amount then t1.freezamount-t2.amount else 0 end " +
                                    "WHERE t1.sa_accountbalanceid="+accountbalancerows.get(0).getLong("sa_accountbalanceid")+" and t2.sourcetable='sa_order' and t2.sourceid="+sa_orderid);
                            sqlList.add("delete from sa_accountbalance_freez t1 WHERE t1.sourcetable='sa_order' and t1.sourceid="+sa_orderid);
                        }
                    }

                }

            }

        }
        return sqlList;
    }


    public ArrayList<String> getCheckSql_custamount(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = super.getCheckSql_custamount(ischeck);
        if (ischeck) {
            Rows accountbalancerows = dbConnect.runSqlQuery("select * from sa_accountbalance t1 inner join sa_accountclass t2 on  t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid where t2.accountname='现金账户' and t1.sys_enterpriseid=" + sys_enterpriseid);

            String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
            if (rb == 1) {
                if (order_paymentnode.equals("3")) {
                    BigDecimal custamount=BigDecimal.ZERO;
                    for(Row row :itemRows){
                        custamount=custamount.add(row.getBigDecimal("custamount").multiply(row.getBigDecimal("qty")));
                    }

                    CashbillEntity entity_custamount = getCashbillEntity(custamount, BigDecimal.ZERO, st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成","客户档案资料费");
                    JSONObject createCashbillPay_custamount = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity_custamount, true);
                    if(custamount.compareTo(BigDecimal.ZERO)!=0){
                        sqlList.addAll(createCashbillPay_custamount.getJSONArray("sqlList").toJavaList(String.class));
                    }


                }
            } else {
                if (order_paymentnode.equals("3")) {
                    BigDecimal custamount=BigDecimal.ZERO;
                    for(Row row :itemRows){
                        custamount=custamount.add(row.getBigDecimal("custamount").multiply(row.getBigDecimal("qty")));
                    }
                    CashbillEntity entity_custamount = getCashbillEntity(custamount.negate(), BigDecimal.ZERO, st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成","客户档案资料费");
                    JSONObject createCashbillPay_custamount = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity_custamount, true);
                    if(custamount.negate().compareTo(BigDecimal.ZERO)!=0){
                        sqlList.addAll(createCashbillPay_custamount.getJSONArray("sqlList").toJavaList(String.class));
                    }
                }
            }
        } else {
            Rows accountbalancerows = dbConnect.runSqlQuery("select * from  sa_accountbalance t1 inner join sa_accountclass t2 on  t1.sa_accountclassid=t2.sa_accountclassid and t1.siteid=t2.siteid where t2.accountname='现金账户' and t1.sys_enterpriseid=" + sys_enterpriseid);
            String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
            if (rb == 1) {
                if (order_paymentnode.equals("3")) {
                    Rows cashbillRows_custamount =dbConnect.runSqlQuery("select amount,discountamount from sa_cashbill where ownerid="+st_stockbillid +" and ownertable='st_stockbill' and type=0 and class='客户档案资料费'");
                    CashbillEntity entity_custamount = getCashbillEntity(cashbillRows_custamount.sum("amount").negate(), cashbillRows_custamount.sum("discountamount").negate(), st_stockbillid, "销售出库单反审核", billno, "由销售出库单" + billno + "反审核时生成","客户档案资料费");
                    JSONObject createCashbillPay_custamount = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity_custamount, true);
                    if(cashbillRows_custamount.sum("amount").negate().compareTo(BigDecimal.ZERO)!=0){
                        sqlList.addAll(createCashbillPay_custamount.getJSONArray("sqlList").toJavaList(String.class));
                    }
                }
            } else {
                if (order_paymentnode.equals("3")) {
                    Rows cashbillRows_custamount =dbConnect.runSqlQuery("select amount,discountamount from sa_cashbill where ownerid="+st_stockbillid +" and ownertable='st_stockbill' and type=0 and class='客户档案资料费'");
                    CashbillEntity entity_custamount = getCashbillEntity(cashbillRows_custamount.sum("amount").negate(),cashbillRows_custamount.sum("discountamount").negate(), st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成","客户档案资料费");
                    JSONObject createCashbillPay_custamount = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity_custamount, true);
                    if(cashbillRows_custamount.sum("amount").negate().compareTo(BigDecimal.ZERO)!=0){
                        sqlList.addAll(createCashbillPay_custamount.getJSONArray("sqlList").toJavaList(String.class));
                    }
                }
            }

        }
        return sqlList;
    }


    /**
     * 账户余额冻结
     *
     * @param addorsub
     */
    public ArrayList<String> updateAccountbalance_freez(boolean addorsub) throws YosException {
        ArrayList<String> sqllist = new ArrayList<>();
        /******** 冻结数据 ********/
        Rows rows = dbConnect.runSqlQuery("select t4.sa_accountbalanceid,t3.sa_orderid,t3.sonum,t3.type,ROUND(sum(t1.qty*(t1.price+ifnull(t1.custamount,0))), 2) amount from st_stockbill_items t1 \n" +
                "                   inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid\n" +
                "                  inner join sa_order t3 on t2.sa_orderid=t3.sa_orderid and t2.siteid=t3.siteid\n" +
                "\t\t\t\t\t\t\t\t\tleft join sa_accountbalance t4 on t4.sa_accountclassid=t3.sa_accountclassid and t4.sys_enterpriseid=t3.sys_enterpriseid and t4.siteid=t3.siteid\n" +
                "                where t1.st_stockbillid=" + st_stockbillid + " group by t4.sa_accountbalanceid,t3.sa_orderid,t3.sonum");
        if (addorsub) {
            for (Row row : rows) {
                String sonum = row.getString("sonum");
                long sa_orderid = row.getLong("sa_orderid");
                long sa_accountbalanceid = row.getLong("sa_accountbalanceid");
                BigDecimal amount = row.getBigDecimal("amount");
                Rows accountbalancefreezrows = dbConnect.runSqlQuery("select sa_accountbalance_freezid from sa_accountbalance_freez where sourceid=" + sa_orderid + " and sourcetable='sa_order' and siteid='" + siteid + "'");

                sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)+" + amount + " where sa_accountbalanceid=" + sa_accountbalanceid + " and siteid='" + siteid + "'");

                if (accountbalancefreezrows.isNotEmpty()) {
                    sqllist.add("update sa_accountbalance_freez set amount=amount+" + amount + " where sa_accountbalance_freezid=" + accountbalancefreezrows.get(0).getLong("sa_accountbalance_freezid"));
                } else {
                    InsertSQL insertSQL = SQLFactory.createInsertSQL(dbConnect, "sa_accountbalance_freez");
                    insertSQL.setSiteid(siteid);
                    insertSQL.setValue("sa_accountbalanceid", sa_accountbalanceid);
                    insertSQL.setValue("sourcetable", "sa_order");
                    insertSQL.setValue("sourceid", sa_orderid);
                    insertSQL.setValue("amount", amount);
                    insertSQL.setValue("remarks", "由"+ (row.getString("type").equals("配件订单")?"配件":"销售")+"订单" + sonum + "审核时冻结");
                    insertSQL.setValue("createby", userInfo.getUserName());
                    insertSQL.setDateValue("createdate");
                    sqllist.add(insertSQL.getSQL());
                }
            }
        } else {
            for (Row row : rows) {
                long sa_orderid = row.getLong("sa_orderid");
                long sa_accountbalanceid = row.getLong("sa_accountbalanceid");
                BigDecimal amount = row.getBigDecimal("amount");
                Rows accountbalancefreezrows = dbConnect.runSqlQuery("select sa_accountbalance_freezid,amount from sa_accountbalance_freez where sourceid=" + sa_orderid + " and sourcetable='sa_order' and siteid='" + siteid + "'");
                if (accountbalancefreezrows.size() > 0) {
                    if(accountbalancefreezrows.get(0).getBigDecimal("amount").compareTo(amount)>0){
                        sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)-" + amount + " where sa_accountbalanceid=" + sa_accountbalanceid);
                        sqllist.add("update sa_accountbalance_freez set amount=amount-" + amount + " where sa_accountbalance_freezid=" + accountbalancefreezrows.get(0).getLong("sa_accountbalance_freezid"));
                    }else{
                        sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)-" + accountbalancefreezrows.get(0).getBigDecimal("amount") + " where sa_accountbalanceid=" + sa_accountbalanceid);
                        sqllist.add("update sa_accountbalance_freez set amount=0 where sa_accountbalance_freezid=" + accountbalancefreezrows.get(0).getLong("sa_accountbalance_freezid"));
                    }

                }
            }
        }
        return sqllist;
    }


    public static CashbillEntity getCashbillEntity(BigDecimal amount, BigDecimal discountamount, Long ownerid, String source, String sourcenotes, String remarks,String type) throws YosException {
        CashbillEntity entity = new CashbillEntity();
        entity.setAmount(amount);
        entity.setDiscountamountamount(discountamount);
        entity.setOwnerid(ownerid);
        entity.setOwnertable("st_stockbill");
        entity.setSource(source);
        entity.setRemarks(remarks);
        entity.setType(type);
        entity.setSourcenote(sourcenotes);
        return entity;

    }

    @Override
    public boolean isInStock(boolean fischeck) {
        if (fischeck && rb == 0 || !fischeck && rb == 1) {
            return true;
        }
        return false;
    }
}