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

package restcontroller.webmanage.sale.dbstockbill;

import beans.data.BatchDeleteErr;
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.*;
import restcontroller.R;

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

public class dbstockbilldetail extends Controller {


    public dbstockbilldetail(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "出库商品列表查询", apiversion = R.ID2025061915091103.v1.class)
    @CACHEING
    public String queryOrderItemgList() 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("t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory;
        QuerySQL querySQL;
        querySQL = SQLFactory.createQuerySQL(this, "st_invbal", "*");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid = t2.itemid AND t1.siteid = t2.siteid","itemno","itemname","model","spec");
        querySQL.addJoinTable(JOINTYPE.left, "st_stock", "t3", "t1.stockid = t3.stockid AND t1.siteid = t3.siteid","stockno","stockname");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t4", "t4.unitid = t2.unitid AND t4.siteid = t2.siteid","unitname");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("t1.stockid",content.getLongValue("stockid"));

        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();

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


    @API(title = "调拨单明细新增更新", apiversion = R.ID2025061915092903.v1.class)
    @CACHEING_CLEAN(apiClass = {dbstockbill.class, dbstockbilldetail.class})
    public String insertormodify_dbstcokbillItems() throws YosException {
        Long st_dbstockbillid = content.getLong("st_dbstockbillid");
        JSONArray iteminfos = content.getJSONArray("iteminfos");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rowscount = dbConnect.runSqlQuery("select billno,status,outstockid,instockid from st_dbstockbill where st_dbstockbillid=" + st_dbstockbillid);
        if (!rowscount.isEmpty()) {
            if (!rowscount.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("非新建状态的调拨单无法新增修改").toString();
            }
        }else{
            return getErrReturnObject().setErrMsg("调拨单不存在").toString();
        }
        int i = 0;
        long maxid = 0;
        long[] st_dbstockbilldetailid = createTableID("st_dbstockbilldetail", iteminfos.size());
        Rows maxidRows = dbConnect
                .runSqlQuery("select MAX(rowno) rowno from st_dbstockbilldetail where st_dbstockbillid=" + st_dbstockbillid);
        if (!maxidRows.isEmpty()) {
            maxid = maxidRows.get(0).getLong("rowno");
        }
        for (Object obj : iteminfos) {
            JSONObject iteminfo = (JSONObject) obj;
            if (iteminfo.getLong("st_dbstockbilldetailid") <= 0 || dbConnect
                    .runSqlQuery("select st_dbstockbilldetailid from st_dbstockbilldetail where st_dbstockbilldetailid="
                            + iteminfo.getLong("st_dbstockbilldetailid"))
                    .isEmpty()) {
                InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_dbstockbilldetail");
                insertSQL.setUniqueid(st_dbstockbilldetailid[i]);
                insertSQL.setSiteid(siteid);
                insertSQL.setValue("rowno", maxid + i + 1);
                insertSQL.setValue("st_dbstockbillid", st_dbstockbillid);
                insertSQL.setValue("outstockid", rowscount.get(0).getLong("outstockid"));
                insertSQL.setValue("instockid",rowscount.get(0).getLong("instockid"));
                insertSQL.setValue("itemid", iteminfo.getLongValue("itemid"));
                insertSQL.setValue("qty", iteminfo.getBigDecimal("qty"));
                insertSQL.setValue("undeliqty", iteminfo.getBigDecimal("qty"));
                insertSQL.setValue("remarks", iteminfo.getStringValue("remarks"));
                i++;
                sqlList.add(insertSQL.getSQL());
            } else {
                Rows rows = dbConnect.runSqlQuery(
                        "SELECT status from st_dbstockbill WHERE st_dbstockbillid = "
                                + st_dbstockbillid);
                if (rows.isNotEmpty()) {
                    if (rows.get(0).getString("status").equals("新建")) {

                        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "st_dbstockbilldetail");
                        updateSQL.setUniqueid(iteminfo.getLong("st_dbstockbilldetailid"));
                        updateSQL.setSiteid(siteid);
                        updateSQL.setValue("itemid", iteminfo.getLongValue("itemid"));
                        updateSQL.setValue("qty", iteminfo.getBigDecimal("qty"));
                        updateSQL.setValue("undeliqty", iteminfo.getBigDecimal("qty"));
                        updateSQL.setValue("remarks", iteminfo.getStringValue("remarks"));
                        sqlList.add(updateSQL.getSQL());
                    } else {
                        return getErrReturnObject().setErrMsg("非新建状态下无法编辑").toString();
                    }

                } else {
                    return getErrReturnObject().setErrMsg("该销售调拨单不存在").toString();
                }
            }

        }
        dbConnect.runSqlUpdate(sqlList);
        return querStcokbillItemsList();
    }


    @API(title = "调拨单明细列表", apiversion = R.ID2025061915094203.v1.class)
    @CACHEING
    public String querStcokbillItemsList() 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(")");
            }
        }
        Long st_dbstockbillid = content.getLong("st_dbstockbillid");
        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.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("t1.st_dbstockbillid",st_dbstockbillid);
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();



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


    @API(title = "删除明细", apiversion = R.ID2025061915102603.v1.class)
    @CACHEING_CLEAN(apiClass = {dbstockbill.class, dbstockbilldetail.class})
    public String deletemx() throws YosException {


        JSONArray st_dbstockbilldetailids = content.getJSONArray("st_dbstockbilldetailids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, st_dbstockbilldetailids.size());
        long st_dbstockbillid = 0;
        for (Object o : st_dbstockbilldetailids) {
            long st_dbstockbilldetailid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select t1.st_dbstockbilldetailid,t2.status,t1.st_dbstockbillid from st_dbstockbilldetail t1 left join st_dbstockbill t2 on t1.st_dbstockbillid=t2.st_dbstockbillid and t1.siteid=t2.siteid where t1.siteid='"
                    + siteid + "' and t1.st_dbstockbilldetailid='" + st_dbstockbilldetailid + "'");

            if (RowsStatus.isNotEmpty()) {
                st_dbstockbillid = RowsStatus.get(0).getLong("st_dbstockbillid");
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(st_dbstockbilldetailid, "非新建状态的调拨单明细无法删除");
                    continue;
                }
            }
            ArrayList<String> list = new ArrayList<>();
            list.add("delete from st_dbstockbilldetail where siteid='" + siteid
                    + "' and st_dbstockbilldetailid=" + st_dbstockbilldetailid);
            list.add("delete from st_dbstockbilldetailsku where siteid='" + siteid
                    + "' and st_dbstockbilldetailid=" + st_dbstockbilldetailid);

            dbConnect.runSqlUpdate(list);
        }
        //重新排序
        updateRowNo(st_dbstockbillid);
        return batchDeleteErr.getReturnObject().toString();
    }
    /**
     * 重新将行进行排序
     *
     * @param
     * @throws YosException
     */
    public void updateRowNo(Long st_dbstockbillid) throws YosException {
        String sql = "SELECT st_dbstockbilldetailid from st_dbstockbilldetail WHERE st_dbstockbillid= " + st_dbstockbillid + " and siteid = '" + siteid + "' ORDER  BY st_dbstockbilldetailid asc ";
        ArrayList<Long> st_dbstockbilldetailids = dbConnect.runSqlQuery(sql).toArrayList("st_dbstockbilldetailid", new ArrayList<>());
        ArrayList<String> sqlList = new ArrayList<>();
        int rowno = 1;
        for (Long id : st_dbstockbilldetailids) {
            sqlList.add("UPDATE st_dbstockbilldetail SET rowno=" + rowno + " WHERE st_dbstockbilldetailid = " + id);
            rowno++;
        }
        dbConnect.runSqlUpdate(sqlList);
    }


}