md_YOS/src/custom/beans/stockbill/bills/XSCK.java

package beans.stockbill.bills;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.parameter.Parameter;
import com.alibaba.fastjson2.JSONObject;
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"))).compareTo(itemRows.sum("amount")) != 0) {
                throw new YosException("扣款金额与应扣金额不符，不能审核");
            }
            BigDecimal discountamount = accountbalancerows.get(0).getBigDecimal("discountamount");
            if (billRow.getBigDecimal("paydiscountamount").compareTo(discountamount) > 0) {
                throw new YosException("优惠金额不能大于可用优惠金额");
            }
            /*
              库存校验
             */
            /*
            其他校验
             */
        } else {
            if (billRow.getBoolean("isreceiver")) {
                throw new YosException("经销商已经确认收货，不可进行反审核");
            }
            /*
              库存校验
             */
             /*
            其他校验
             */
        }
        if ((ischeck && rb == 1) || (!ischeck && rb == 0)) {
            if (order_paymentnode.equals("3") && !Accountbalance.judgeBalance(dbConnect, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), itemRows.sum("amount"))) {
                throw new YosException("账户余额不足，还差" + Accountbalance.InsufficientBalance(dbConnect, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), itemRows.sum("amount")) + "元！");
            }
            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()) {
                throw new YosException("存在商品已完全出库，无法反审核");
            }
        }
    }

    @Override
    public ArrayList<String> getCheckSql(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = super.getCheckSql(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")) {
                    CashbillEntity entity = getCashbillEntity(billRow.getBigDecimal("payamount"), billRow.getBigDecimal("paydiscountamount"), st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    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=deliedqty+" + row.getDouble("qty") + ",offstatus=if(billingstatus='保内收费','待核销','不可核销'),offdate=CURRENT_TIME  where 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)));
            } else {
                if (order_paymentnode.equals("3")) {
                    CashbillEntity entity = getCashbillEntity(billRow.getBigDecimal("payamount").negate(), billRow.getBigDecimal("paydiscountamount").negate(), st_stockbillid, "销售出库单审核", billno, "由销售出库单" + billno + "审核时生成");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    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"));
                }
                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 {
            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")) {
                    CashbillEntity entity = getCashbillEntity(billRow.getBigDecimal("payamount").negate(), billRow.getBigDecimal("paydiscountamount").negate(), st_stockbillid, "销售出库单反审核", billno, "由销售出库单" + billno + "反审核时生成");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    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"));
                }
                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"));
                    }
                }
                sqlList.addAll(updateAccountbalance_freez(isInStock(ischeck)));
            } else {
                if (order_paymentnode.equals("3")) {
                    CashbillEntity entity = getCashbillEntity(billRow.getBigDecimal("payamount"), billRow.getBigDecimal("paydiscountamount"), st_stockbillid, "销售出库单反审核", billno, "由销售出库单" + billno + "反审核时生成");
                    JSONObject createCashbillPay = Accountbalance.createCashbillPay(dbConnect, userInfo, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                    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=deliedqty+" + row.getDouble("qty") + ",offstatus=if(billingstatus='保内收费','待核销','不可核销'),offdate=CURRENT_TIME  where 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"));
                    }
                }
            }

        }
        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,ROUND(sum(t1.qty*t2.price*ifnull(t2.pricerate,1)*ifnull(t2.rwpricerate,1)), 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", "由销售订单" + 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) {
                    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"));
                }
            }
        }
        return sqllist;
    }


    public static CashbillEntity getCashbillEntity(BigDecimal amount, BigDecimal discountamount, Long ownerid, String source, String sourcenotes, String remarks) 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.setSourcenote(sourcenotes);
        return entity;

    }

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