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

package beans.stockbill.bills;

import beans.datacontrllog.DataContrlLog;
import common.Controller;
import common.UserInfo;
import common.YosException;
import common.data.*;
import common.data.db.DBConnect;
import org.apache.commons.lang.StringUtils;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public abstract class BasicBill {
    Controller controller;
    DBConnect dbConnect;
    UserInfo userInfo;
    long st_stockbillid;
    boolean issale;//是否销售出库
    String billType;//单据类型
    String billTypeMX;//单据类型
    String billno;
    String siteid;
    String status;
    int rb;
    String period;
    Row billRow;
    Rows itemRows;
    Rows codeRows;

    public static BasicBill getBill(Controller controller, long st_stockbillid) throws YosException {
        Rows billRows = controller.dbConnect.runSqlQuery("select type from st_stockbill where st_stockbillid ='" + st_stockbillid + "'");
        if (billRows.isEmpty()) {
            throw new YosException("该出入库单不存在");
        }
        String type = billRows.get(0).getString("type");
        switch (type) {
            case "销售出库":
                return new XSCK(controller, st_stockbillid);
            case "返修出库":
                return new ck(controller, st_stockbillid);
            case "生产领料出库":
                return new ck(controller, st_stockbillid);
            case "委外领料出库":
                return new ck(controller, st_stockbillid);
            case "其他出库":
                return new QTCK(controller, st_stockbillid);
            case "外购入库":
                return new rk(controller, st_stockbillid);
            case "生产入库":
                return new rk(controller, st_stockbillid);
            case "委外入库":
                return new rk(controller, st_stockbillid);
            case "返修入库":
                return new FXRK(controller, st_stockbillid);
            case "其他入库":
                return new QTRK(controller, st_stockbillid);
            default: {
                throw new YosException("不支持的出入库单类型");
            }
        }
    }

    /**
     * 构造函数
     *
     * @param controller
     * @param st_stockbillid 出入库单ID
     * @throws YosException
     */
    public BasicBill(Controller controller, long st_stockbillid) throws YosException {
        this.controller = controller;
        this.dbConnect = this.controller.dbConnect;
        this.userInfo = controller.userInfo;
        this.st_stockbillid = st_stockbillid;
        queryBills();
    }

    /**
     * 单据审核校验
     *
     * @param ischeck
     * @throws YosException
     */
    public void checkValidate(boolean ischeck) throws YosException {
        /*
        通用校验
         */
        if (ischeck) {
            if (StringUtils.isBlank(period)) {
                period = getPeriod(true, billRow.getDate("billdate"));
                dbConnect.runSqlUpdate("update st_stockbill set period='" + period + "' where st_stockbillid=" + st_stockbillid);
            }
            if (!status.equals("新建")) {
                throw new YosException("单号为：【" + billno + "】的出入库单为非新建状态，无法审核");
            }
            RowsMap rowsMap = dbConnect.runSqlQuery("select CONCAT(year, '-',LPAD(month, 2, '0')) period,isclose from st_period where CONCAT(year, '-',LPAD(month, 2, '0'))='" + period + "'").toRowsMap("period");
            if (rowsMap.containsKey(period)) {
                if (rowsMap.get(period).toRowsMap("isclose").containsKey("1")) {
                    throw new YosException("原单据会计期间已关闭，不可审核");
                }
            } else {
                throw new YosException("原单据会计期间未生成，不可审核");
            }
            if (itemRows.isEmpty()) {
                throw new YosException("表体为空");
            }
            if (itemRows.min("qty").doubleValue() < 0) {
                throw new YosException("表体数量不能小于0");
            }
            if (rb == 0) {
                if (!billRow.getString("sourceobject").equals("tpartreimbursement")) {
                    Rows olditemRows = dbConnect.runSqlQuery("select * from st_stockbill_items where siteid='" + siteid + "' and st_stockbillid=" + billRow.getLong("sourceid"));
                    RowsMap olditemRowsMap = olditemRows.toRowsMap("st_stockbill_itemsid");
                    for (Row row : itemRows) {
                        if (StringUtils.isNotBlank(row.getString("sourceobject")) && row.getString("sourceobject").equals("st_stockbill_items")) {
                            if (olditemRowsMap.containsKey(row.getString("sourceid"))) {
                                Row row1 = olditemRowsMap.get(row.getString("sourceid")).get(0);
                                if (row.getBigDecimal("qty").compareTo((row1.getBigDecimal("qty").subtract(row1.getBigDecimal("hongqty")))) > 0) {
                                    throw new YosException("行号为:" + row.getString("rowno") + "的商品红冲数量超过原单可红冲数量" + row1.getBigDecimal("qty").subtract(row1.getBigDecimal("hongqty")));
                                }
                            } else {
                                throw new YosException("行号为:" + row.getString("rowno") + "的物料不在原单中，不可进行红冲");
                            }
                        }
                    }
                }

            }
        } else {
            if (StringUtils.isBlank(period)) {
                period = getPeriod(true, billRow.getDate("billdate"));
                dbConnect.runSqlUpdate("update st_stockbill set period='" + period + "' where st_stockbillid=" + st_stockbillid);
            }
            if (!status.equals("审核")) {
                throw new YosException("单号为：【" + billno + "】的出入库单为非审核状态，无法反审核");
            }
            RowsMap rowsMap = dbConnect.runSqlQuery("select CONCAT(year, '-',LPAD(month, 2, '0')) period,isclose from st_period where CONCAT(year, '-',LPAD(month, 2, '0'))='" + period + "'").toRowsMap("period");
            if (rowsMap.containsKey(period)) {
                if (rowsMap.get(period).toRowsMap("isclose").containsKey("1")) {
                    throw new YosException("原单据会计期间已关闭，不可反审核");
                }
            } else {
                throw new YosException("单据日期所属会计期间未生成，不可反审核");
            }
        }
    }

    public ArrayList<String> getCheckSql(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.addAll(updateInvbal(ischeck));//更新库存
        sqlList.addAll(updateMCode(ischeck));//更新序列号
        sqlList.addAll(updateHongqty(ischeck));//更新红冲数量
        sqlList.addAll(updateStatus(ischeck));//更新状态
        if (ischeck) {
            sqlList.add(DataContrlLog.createLog(controller, "st_stockbill", st_stockbillid, "审核", billRow.getString("type") + "单审核成功").getSQL());
        } else {
            sqlList.add(DataContrlLog.createLog(controller, "st_stockbill", st_stockbillid, "反审核", billRow.getString("type") + "单反审核成功").getSQL());
        }
        return sqlList;
    }

    public ArrayList<String> getCheckSql_custamount(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        return sqlList;
    }

    /**
     * 更新库存
     *
     * @param ischeck
     * @return
     * @throws YosException
     */

    private ArrayList<String> updateInvbal(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        RowsMap invbalsRowsMap = SQLFactory.createQuerySQL(dbConnect, "st_invbal").setWhere("siteid", siteid).setWhere("itemid", itemRows.toArrayList("itemid")).query().toRowsMap("itemid");
        RowsMap invbalsSaleRowsMap = SQLFactory.createQuerySQL(dbConnect, "st_invbal_sale").setWhere("siteid", siteid).setWhere("itemid", itemRows.toArrayList("itemid")).query().toRowsMap("itemid");
        for (Row row : itemRows) {
            long itemid = row.getLong("itemid");
            long stockid = row.getLong("stockid");
            BigDecimal qty = isInStock(ischeck) ? row.getBigDecimal("qty") : row.getBigDecimal("qty").negate();
            if (!invbalsRowsMap.containsKey(String.valueOf(itemid)) || !invbalsRowsMap.get(String.valueOf(itemid)).toRowsMap("stockid").containsKey(String.valueOf(stockid))) {
                InsertSQL invbalInsert = SQLFactory.createInsertSQL(controller, "st_invbal");
                invbalInsert.setValue("siteid", siteid);
                invbalInsert.setValue("stockid", stockid);
                invbalInsert.setValue("itemid", itemid);
                invbalInsert.setValue("qty", qty.doubleValue());
                sqlList.add(invbalInsert.getSQL());
            } else {
                UpdateSQL invbalUpdate = SQLFactory.createUpdateSQL(controller, "st_invbal");
                invbalUpdate.addValue("qty", qty);
                invbalUpdate.setWhere("itemid", itemid);
                invbalUpdate.setWhere("stockid", stockid);
                invbalUpdate.setWhere("siteid", siteid);
                sqlList.add(invbalUpdate.getSQL());
            }
            //是否为销售仓库
            if (SQLFactory.getRow(controller, "st_stock", stockid).getBoolean("issalestock")) {
                if (!invbalsSaleRowsMap.containsKey(String.valueOf(itemid))) {
                    InsertSQL invbalInsert = SQLFactory.createInsertSQL(controller, "st_invbal_sale");
                    invbalInsert.setValue("siteid", siteid);
                    invbalInsert.setValue("itemid", itemid);
                    if (!issale) {
                        invbalInsert.setValue("cansaleqty", qty.doubleValue());//可销售数量
                        invbalInsert.setValue("candispatchqty", qty.doubleValue());//可发货数量
                    } else {
                        invbalInsert.setValue("cansaleqty", 0);//可销售数量
                        invbalInsert.setValue("candispatchqty", 0);//可发货数量
                    }
                    invbalInsert.setValue("qty", qty.doubleValue());//库存数量
                    sqlList.add(invbalInsert.getSQL());
                } else {
                    UpdateSQL invbalUpdate = SQLFactory.createUpdateSQL(controller, "st_invbal_sale");
                    if (!issale) {
                        invbalUpdate.addValue("cansaleqty", qty.doubleValue());//可销售数量
                        invbalUpdate.addValue("candispatchqty", qty.doubleValue());//可发货数量
                    }
                    invbalUpdate.addValue("qty", qty);//库存数量
                    invbalUpdate.setWhere("itemid", itemid);
                    invbalUpdate.setWhere("siteid", siteid);
                    sqlList.add(invbalUpdate.getSQL());
                }

            }

        }
        return sqlList;
    }

    /**
     * 更新已红冲数量
     *
     * @param ischeck
     * @return
     * @throws YosException
     */
    private ArrayList<String> updateHongqty(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        if (rb == 0) {
            for (Row row : itemRows) {
                if (row.getString("sourceobject").equals("st_stockbill_items")) {
                    long sourceid = row.getLong("sourceid");
                    BigDecimal qty = ischeck ? row.getBigDecimal("qty") : row.getBigDecimal("qty").negate();
                    sqlList.add("update st_stockbill_items set hongqty=ifnull(hongqty,0)+" + qty + " where st_stockbill_itemsid=" + sourceid);
                }

            }
        }
        return sqlList;
    }

    /**
     * 更新状态
     *
     * @param ischeck
     * @return
     * @throws YosException
     */
    private ArrayList<String> updateStatus(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();

        if (ischeck) {
            sqlList.add("update st_stockbill set status='审核',checkby='" + controller.username + "',checkdate=CURRENT_TIMESTAMP where st_stockbillid = " + st_stockbillid + " and siteid='" + siteid + "'");
        } else {
            sqlList.add("update st_stockbill set status='新建',checkby='',checkdate=null where st_stockbillid = " + st_stockbillid + " and siteid='" + siteid + "'");
        }
        return sqlList;
    }

    /**
     * 更新序列号
     *
     * @param ischeck
     * @return
     * @throws YosException
     */
    public ArrayList<String> updateMCode(boolean ischeck) throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : codeRows) {
            long itemid = row.getLong("itemid");
            long stockid = row.getLong("stockid");
            String sku = row.getString("sku");
            UpdateSQL saItemsku = SQLFactory.createUpdateSQL(controller, "sa_itemsku");
            if (isInStock(ischeck)) {
                saItemsku.setValue("stockid", stockid);
                saItemsku.setValue("status", "在库");
                if (issale) {
                    saItemsku.setValue("sys_enterpriseid", null);
                }
            } else {
                saItemsku.setValue("stockid", 0);
                if (issale) {
                    saItemsku.setValue("status", "售出");
                    saItemsku.setValue("sys_enterpriseid", billRow.getLong("sys_enterpriseid"));
                }
            }
            saItemsku.setWhere("itemid", itemid);
            saItemsku.setWhere("sku", sku);

            sqlList.add(saItemsku.getSQL());
        }
        return sqlList;
    }

    /**
     * 单据查询
     *
     * @throws YosException
     */
    private void queryBills() throws YosException {
        Rows billRows = dbConnect.runSqlQuery("select * from st_stockbill where st_stockbillid ='" + st_stockbillid + "'");
        if (billRows.isNotEmpty()) {
            billRow = billRows.get(0);
        } else {
            throw new YosException("该出入库单不存在");
        }
        this.billno = billRow.getString("billno");
        this.issale = billRow.getString("type").equals("销售出库");
        this.billType = billRow.getString("type");
        this.billTypeMX = billRow.getString("typemx");
        this.rb = billRow.getInteger("rb");
        this.siteid = billRow.getString("siteid");
        this.status = billRow.getString("status");
        this.period = billRow.getString("period");
        itemRows = dbConnect.runSqlQuery("select t1.*,ifnull(t1.sa_orderitemsid,0) sa_orderitemsidnum,t2.isnegative,t2.ismodule from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid where t1.st_stockbillid ='" + st_stockbillid + "'");
        codeRows = dbConnect.runSqlQuery("select t1.* from st_stockbill_items_sku t1 where t1.st_stockbillid ='" + st_stockbillid + "'");
    }

    public String getPeriod(boolean isjudge, Date date) throws YosException {

        String preioddate = new SimpleDateFormat("yyyy-MM-dd").format(date);
        Rows rows = dbConnect.runSqlQuery("select CONCAT_WS('-', year, LPAD(month, 2, '0')) period from st_period where begdate<='" + preioddate + "' and enddate>='" + preioddate + "'");
        if (rows.size() != 1) {
            if (isjudge) {
                throw new YosException("单据日期所属会计期间未生成");
            } else {
                return "null";
            }
        } else {
            return rows.get(0).getString("period");
        }
    }


    public abstract boolean isInStock(boolean fischeck);

}