md_YOS/src/custom/restcontroller/webmanage/sale/dbstockbill/dbstockbillreceive.java

package restcontroller.webmanage.sale.dbstockbill;

import beans.invbal.Invbal;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import restcontroller.R;

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

public class dbstockbillreceive extends Controller {
    public dbstockbillreceive(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "查询需要收货确认的审核调拨单", apiversion = R.ID2025090313530203.v1.class)
    public String queryDbstockbills_Receive() 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("t4.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t4.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t4.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t4.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_dbstockbilldetail", "*");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "st_stock", "t2", "t1.instockid = t2.stockid AND t1.siteid = t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "st_stock", "t3", "t1.outstockid = t3.stockid AND t1.siteid = t3.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t4", "t1.itemid=t4.itemid and t1.siteid = t4.siteid", "itemno", "itemname"
                , "spec", "model");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t5", "t4.unitid=t5.unitid and t4.siteid = t5.siteid", "unitname");
        querySQL.addJoinTable(JOINTYPE.inner, "st_dbstockbill", "t6", "t1.st_dbstockbillid=t6.st_dbstockbillid and t1.siteid = t6.siteid", "billno", "checkdate");

        querySQL.addQueryFields("outstockno", "t3.stockno");
        querySQL.addQueryFields("outstockname", "t3.stockname");
        querySQL.addQueryFields("instockno", "t2.stockno");
        querySQL.addQueryFields("instockname", "t2.stockname");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setWhere("t6.status='审核' and t1.undeliqty>0");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy("t6.st_dbstockbillid desc");
        Rows rows = querySQL.query();

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

    @API(title = "生成入库单", apiversion = R.ID2025090314003303.v1.class)
    public String received() throws YosException {
        JSONArray st_dbstockbilldetails = content.getJSONArray("st_dbstockbilldetails");
        if (st_dbstockbilldetails.isEmpty()) {
            return getErrReturnObject().setErrMsg("未选择调拨单明细").toString();
        }
        Rows dbstockbilldetail = new Rows();
        Rows rows = dbConnect.runSqlQuery("select t1.*,t2.instockid,t2.billdate from st_dbstockbilldetail t1 inner join st_dbstockbill t2 on t1.siteid=t2.siteid and t1.st_dbstockbillid=t2.st_dbstockbillid  where t1.siteid='" + siteid + "' and t1.undeliqty>0 and t2.status='审核'");
        RowsMap rowsMap = rows.toRowsMap("st_dbstockbilldetailid");
        long st_dbstockbillid = 0;
        for (Object object : st_dbstockbilldetails) {
            JSONObject jsonObject = (JSONObject) object;
            Row st_dbstockbilldetail;
            if (rowsMap.containsKey(jsonObject.getLong("st_dbstockbilldetailid"))) {
                st_dbstockbilldetail = rowsMap.get(jsonObject.getLong("st_dbstockbilldetailid")).get(0);
            } else {
                return getErrReturnObject().setErrMsg("调拨单明细不存在").toString();
            }
            if (st_dbstockbillid != 0 && st_dbstockbillid != st_dbstockbilldetail.getLong("st_dbstockbillid")) {
                return getErrReturnObject().setErrMsg("不能同时选择两个调拨单进行收货确认").toString();
            } else {
                st_dbstockbillid = st_dbstockbilldetail.getLong("st_dbstockbillid");
            }
            int recqty = jsonObject.getIntValue("recqty");// 收货确认数量
            int undeliqty = st_dbstockbilldetail.getInteger("undeliqty");// 未入库数量
            if (recqty > undeliqty) {
                return getErrReturnObject().setErrMsg("收货确认数量不能大于未入库数量").toString();
            }
            if (jsonObject.getJSONArray("skuinfos").size() > 0) {
                if (recqty != jsonObject.getJSONArray("skuinfos").size()) {
                    return getErrReturnObject().setErrMsg("机器码数量与收货确认数量不匹配").toString();
                }
            }
            Rows skurows = dbConnect.runSqlQuery("select * from sa_itemsku where siteid='" + siteid + "' and ifnull(st_dbstockbillid,0)>0 ");
            RowsMap skuRowsMap = skurows.toRowsMap("sku");
            for (Object skuinfo : jsonObject.getJSONArray("skuinfos")) {
                if (!skuRowsMap.containsKey(((JSONObject) skuinfo).getString("sku"))) {
                    return getErrReturnObject().setErrMsg("序列号" + ((JSONObject) skuinfo).getString("sku") + "不存在或者已经确认收货").toString();
                }
            }

            st_dbstockbilldetail.put("qty", recqty);
            st_dbstockbilldetail.put("skuinfos", jsonObject.getJSONArray("skuinfos"));
            dbstockbilldetail.add(st_dbstockbilldetail);

        }
        ArrayList<String> sqllist = new ArrayList<>();
        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill");
        long st_stockbillid = createTableID("st_stockbill");
        insertSQL.setUniqueid(st_stockbillid);
        insertSQL.setSiteid(siteid);
        insertSQL.setValue("billno", createBillCode("STOCKBILL2"));
        insertSQL.setValue("type", "其他入库");
        insertSQL.setValue("typemx", "调拨入库");
        insertSQL.setValue("rb", 1);
        insertSQL.setValue("sys_enterpriseid", 0);
        insertSQL.setValue("sourceobject", "st_dbstockbill");
        insertSQL.setValue("sourceid", st_dbstockbillid);
        insertSQL.setValue("stockid", rows.get(0).getLong("instockid"));
        insertSQL.setValue("remarks", "收货确认入库");
        insertSQL.setValue("status", "审核");
        insertSQL.setValue("createby", username);
        insertSQL.setDateValue("createdate");
        insertSQL.setValue("checkby", username);
        insertSQL.setDateValue("checkdate");
        insertSQL.setValue("billdate", rows.get(0).getLong("billdate"));
        sqllist.add(insertSQL.getSQL());
        int j = 1;
        for (Row row : dbstockbilldetail) {
            insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items");
            long st_stockbill_itemsid = createTableID("st_stockbill_items");
            insertSQL.setUniqueid(st_stockbill_itemsid);
            insertSQL.setValue("rowno", j);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("sa_dispatch_itemsid", 0);
            insertSQL.setValue("sourceobject", "st_dbstockbilldetail");
            insertSQL.setValue("sourceid", row.getLong("st_dbstockbilldetailid"));
            insertSQL.setValue("stockid", row.getLong("instockid"));
            insertSQL.setValue("itemid", row.getLong("itemid"));
            insertSQL.setValue("itemno", row.getString("itemno"));
            insertSQL.setValue("itemname", row.getString("itemname"));
            insertSQL.setValue("model", row.getString("model"));
            insertSQL.setValue("qty", row.getBigDecimal("qty"));
            insertSQL.setValue("st_stockbillid", st_stockbillid);
            sqllist.add(insertSQL.getSQL());
            j++;
            for (Object objectdetail : row.getJSONArray("skuinfos")) {
                JSONObject rowdetail = (JSONObject) objectdetail;
                insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items_sku");
                long st_stockbill_items_skuid = createTableID("st_stockbill_items_sku");
                insertSQL.setUniqueid(st_stockbill_items_skuid);
                insertSQL.setSiteid(siteid);
                insertSQL.setValue("stockid", row.getLong("instockid"));
                insertSQL.setValue("itemid", row.getLong("itemid"));
                insertSQL.setValue("sku", rowdetail.getString("sku"));
                insertSQL.setValue("st_stockbillid", st_stockbillid);
                insertSQL.setValue("st_stockbill_itemsid", st_stockbill_itemsid);
                sqllist.add(insertSQL.getSQL());
                sqllist.add("update sa_itemsku set stockid=" + row.getLong("instockid") + ",st_dbstockbillid=0,st_dbstockbilldetailid=0 where sku='" + rowdetail.getString("sku") + "'");
            }
        }
        sqllist.addAll(updateIcinvbal(true, true, dbstockbilldetail));
        sqllist.addAll(updateUnInQty(true, dbstockbilldetail));
        dbConnect.runSqlUpdate(sqllist);
        return getSucReturnObject().toString();
    }

    public ArrayList<String> updateIcinvbal(boolean ischeck, boolean isinstock, Rows dbstockbilldetail) throws YosException {
        ArrayList<String> sqlList = new ArrayList();
        ArrayList<Long> itemids = new ArrayList<Long>();
        itemids = dbstockbilldetail.toArrayList("itemid", new ArrayList<>());
        itemids.add((long) 0);
        int i = 0;
        String sql = "select * from st_invbal where itemid in" + itemids;
        sql = sql.replace("[", "(").replace("]", ")");
        Rows invbals = dbConnect.runSqlQuery(sql);
        RowsMap invbalsRowsMap = invbals.toRowsMap("itemid");
        RowsMap invbalsSaleRowsMap = SQLFactory.createQuerySQL(dbConnect, "st_invbal_sale").setWhere("siteid", siteid).setWhere("itemid", itemids).query().toRowsMap("itemid");
        for (Row row : dbstockbilldetail) {
            String itemid = row.getString("itemid");
            //String batchno = row.getString("batchno");
            //String fdcspno = row.getString("fdcspno");
            long stockid;

            if ((ischeck && isinstock) || (!ischeck && !isinstock)) {
                stockid = row.getLong("instockid");
            } else {
                stockid = row.getLong("outstockid");
            }

//             if (type == 1 && !getSysVars().getBoolean("FISBATCHFORXC")) {
//                 fdcspno = "**********";
//                 fbatchno = "**********";
//                 fstockno = getPaoSet("TDEPARTMENT").getPao(0)
//                         .getPaoSet("TSTOCKXC").getPao(0).getString("fstockno");
//                 icinvbal = detailpao.getPaoSet("$icinvbal", "icinvbal",
//                         "fitemno='" + fitemno + "' and fstockno='" + fstockno
//                                 + "' and fdcspno='" + fdcspno
//                                 + "' and fbatchno='" + fbatchno + "'");
//             } else if (type == 2 && !getSysVars().getBoolean("FISBATCHFORXC")) {
//                 fdcspno = "**********";
//                 fbatchno = "**********";
//                 fstockno = getPaoSet("TSUPPLIER").getPao(0)
//                         .getPaoSet("TSTOCKXC").getPao(0).getString("fstockno");
//                 icinvbal = detailpao.getPaoSet("$icinvbal", "icinvbal",
//                         "fitemno='" + fitemno + "' and fstockno='" + fstockno
//                                 + "' and fdcspno='" + fdcspno
//                                 + "' and fbatchno='" + fbatchno + "'");
//             }
            BigDecimal qty = isinstock ? row.getBigDecimal("qty") : row.getBigDecimal("qty").negate();
            if (!invbalsRowsMap.containsKey(itemid)) {
                SQLFactory sqlFactory = new SQLFactory(this, "即时库存新增");
                sqlFactory.addParameter("st_invbalid", createTableID("st_invbal"));
                sqlFactory.addParameter("qty", qty);
                sqlFactory.addParameter("itemid", itemid);
                sqlFactory.addParameter("stockid", stockid);
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("userid", userid);
                sqlFactory.addParameter("username", username);
                sqlList.add(sqlFactory.getSQL());
            } else {
                if (invbalsRowsMap.get(itemid).toRowsMap("stockid").containsKey(stockid)) {
                    SQLFactory sqlFactory = new SQLFactory(this, "即时库存更新");
                    sqlFactory.addParameter("st_invbalid", createTableID("st_invbal"));
                    sqlFactory.addParameter("qty", invbalsRowsMap.get(itemid).toRowsMap("stockid").get(stockid).get(0).getBigDecimal("qty").add(qty));
                    sqlFactory.addParameter("itemid", itemid);
                    sqlFactory.addParameter("stockid", stockid);
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("username", username);
                    sqlList.add(sqlFactory.getSQL());
                } else {
                    SQLFactory sqlFactory = new SQLFactory(new Invbal(), "即时库存新增");
                    sqlFactory.addParameter("st_invbalid", createTableID("st_invbal"));
                    sqlFactory.addParameter("qty", qty);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlFactory.addParameter("stockid", stockid);
                    System.err.println(stockid);
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("username", username);
                    sqlList.add(sqlFactory.getSQL());
                }
            }

            //是否为销售仓库
            if (SQLFactory.getRow(this, "st_stock", stockid).getBoolean("issalestock")) {
                if (!invbalsSaleRowsMap.containsKey(String.valueOf(itemid))) {
                    InsertSQL invbalInsert = SQLFactory.createInsertSQL(this, "st_invbal_sale");
                    invbalInsert.setValue("siteid", siteid);
                    invbalInsert.setValue("itemid", itemid);
                    invbalInsert.setValue("cansaleqty", qty.doubleValue());//可销售数量
                    invbalInsert.setValue("candispatchqty", qty.doubleValue());//可发货数量
                    invbalInsert.setValue("qty", qty.doubleValue());//库存数量
                    sqlList.add(invbalInsert.getSQL());
                } else {
                    UpdateSQL invbalUpdate = SQLFactory.createUpdateSQL(this, "st_invbal_sale");
                    invbalUpdate.addValue("qty", invbalsSaleRowsMap.get(String.valueOf(itemid)).get(0).getBigDecimal("qty").add(qty));//库存数量
                    invbalUpdate.setWhere("itemid", itemid);
                    invbalUpdate.setWhere("siteid", siteid);
                    sqlList.add(invbalUpdate.getSQL());
                }

            }
        }
        return sqlList;
    }

    public ArrayList<String> updateUnInQty(boolean ischeck, Rows dbstockbilldetail) {
        ArrayList<String> sqllist = new ArrayList<>();
        for (Row row : dbstockbilldetail) {
            if (ischeck) {// 审核
                sqllist.add("update st_dbstockbilldetail  set undeliqty=undeliqty-" + row.getInteger("recqty") + " where st_dbstockbilldetailid='" + row.getString("st_dbstockbilldetailid") + "'");
            } else {// 反审核
                sqllist.add("update st_dbstockbilldetail set undeliqty=" + row.getBigDecimal("qty") + " where st_dbstockbilldetailid='" + row.getString("st_dbstockbilldetailid") + "'");
            }
        }
        return sqllist;
    }
}