md_YOS/src/custom/restcontroller/webmanage/sale/tpartreimbursement/tpartreimbursement.java

package restcontroller.webmanage.sale.tpartreimbursement;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;
import restcontroller.sale.stockbill.stockbill;
import restcontroller.webmanage.sale.order.Order;
import restcontroller.webmanage.sale.order.OrderItems;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import static beans.stockbill.bills.XSCK.getCashbillEntity;

@API(title = "配件核销单")
public class tpartreimbursement extends Controller {
    public tpartreimbursement(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新增更新配件核销单", apiversion = R.ID2025081811143103.v1.class)
    @CACHEING_CLEAN(apiClass = {tpartreimbursement.class})
    public String insertormodify_tpartreimbursement() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名

        String tableName = "sa_tpartreimbursement";
        Long sa_tpartreimbursementid = content.getLong("sa_tpartreimbursementid");
        String remarks = content.getStringValue("remarks");
        String billdate = content.getStringValue("billdate");
        if (sys_enterpriseid <= 0) {
            sys_enterpriseid = content.getLong("sys_enterpriseid");
        }

        if (sa_tpartreimbursementid <= 0) {
            sa_tpartreimbursementid = createTableID(tableName);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, tableName);
            insertSQL.setUniqueid(sa_tpartreimbursementid);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("billdate", StringUtils.isBlank(billdate) ? getDateTime_Str() : billdate);
            insertSQL.setValue("remarks", remarks);
            insertSQL.setValue("billno", createBillCode("tpartreimbursement"));
            insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);

            insertSQL.setValue("createby", username);
            insertSQL.setDateValue("createdate");
            insertSQL.setValue("changeby", username);
            insertSQL.setDateValue("changedate");
            insertSQL.setValue("status", "新建");
            //制单方 1表示工厂方，0表示客户方
            if (usertype == 21 || usertype == 22) {
                insertSQL.setValue("createflag", 0);
            } else {
                insertSQL.setValue("createflag", 1);
            }
            sqlList.add(insertSQL.getSQL());

            sqlList.add(DataContrlLog.createLog(this, "sa_tpartreimbursement", sa_tpartreimbursementid, "新增", "配件核销单新增成功").getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery(
                    "SELECT status from sa_tpartreimbursement WHERE sa_tpartreimbursementid = "
                            + sa_tpartreimbursementid);
            if (rows.isNotEmpty()) {
                if (rows.get(0).getString("status").equals("新建")) {

                    UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, tableName);
                    updateSQL.setUniqueid(sa_tpartreimbursementid);
                    updateSQL.setSiteid(siteid);
                    updateSQL.setValue("sys_enterpriseid", sys_enterpriseid);
                    updateSQL.setValue("remarks", remarks);
                    updateSQL.setValue("changeby", username);
                    updateSQL.setDateValue("changedate");
                    sqlList.add(updateSQL.getSQL());

                    sqlList.add(DataContrlLog.createLog(this, "sa_tpartreimbursement", sa_tpartreimbursementid, "更新", "配件核销单更新成功").getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("非新建状态下无法编辑").toString();
                }

            } else {
                return getErrReturnObject().setErrMsg("该配件核销单不存在").toString();
            }

        }
        dbConnect.runSqlUpdate(sqlList);
        content.put("sa_tpartreimbursementid", sa_tpartreimbursementid);

        return query_tpartreimbursementMain();
    }

    @API(title = "配件核销单详情", apiversion = R.ID2025081811144603.v1.class)
    @CACHEING
    public String query_tpartreimbursementMain() throws YosException {
        long sa_tpartreimbursementid = content.getLong("sa_tpartreimbursementid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_tpartreimbursement", "*");
        querySQL.setTableAlias("t1");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.sa_tpartreimbursementid", sa_tpartreimbursementid);
        Rows rows = querySQL.query();

//        QuerySQL querySQLdetail = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_tpartreimbursementid", "sa_tpartreimbursementdetailid","qty");
//        querySQLdetail.setTableAlias("t1");
//        querySQLdetail.addJoinTable(JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid", "price");
//        querySQLdetail.setWhere("t1.siteid", siteid);
//        querySQLdetail.setWhere("t1.sa_tpartreimbursementid", rows.toArrayList("sa_tpartreimbursementid"));
//        Rows rowsdetail = querySQL.query();
//        RowsMap rowsMap = rowsdetail.toRowsMap("sa_tpartreimbursementid");
//        for (Row row : rows) {
//            row.put("totalamount", rowsMap.get(row.getString("sa_tpartreimbursementid")).sum("amount"));
//            row.put("offamount", rowsMap.get(row.getString("sa_tpartreimbursementid")).toRowsMap("offstatus").get("已核销").sum("amount"));
//        }

        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        return getSucReturnObject().setData(row).toString();
    }


    @API(title = "配件核销单列表", apiversion = R.ID2025081811145503.v1.class)
    @CACHEING
    public String querysa_tpartreimbursementList() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (sys_enterpriseid > 0) {
            where.append(" and t1.sys_enterpriseid=" + sys_enterpriseid);
        }
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.billdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.billdate <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
        }


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_tpartreimbursement", "*");
        querySQL.setTableAlias("t1");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

//        QuerySQL querySQLdetail = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_tpartreimbursementid", "sa_tpartreimbursementdetailid");
//        querySQLdetail.setTableAlias("t1");
//        querySQLdetail.addJoinTable(JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid", "*");
//        querySQLdetail.setWhere("t1.siteid", siteid);
//        querySQLdetail.setWhere("t1.sa_tpartreimbursementid", rows.toArrayList("sa_tpartreimbursementid"));
//        Rows rowsdetail = querySQL.query();
//        RowsMap rowsMap = rowsdetail.toRowsMap("sa_tpartreimbursementid");
//        for (Row row : rows) {
//            row.put("totalamount", rowsMap.get(row.getString("sa_tpartreimbursementid")).sum("amount"));
//            row.put("offamount", rowsMap.get(row.getString("sa_tpartreimbursementid")).toRowsMap("offstatus").get("已核销").sum("amount"));
//        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "删除", apiversion = R.ID2025081811150603.v1.class)
    @CACHEING_CLEAN(apiClass = {tpartreimbursement.class})
    public String delete() throws YosException {
        JSONArray sa_tpartreimbursementids = content.getJSONArray("sa_tpartreimbursementids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_tpartreimbursementids.size());
        for (Object o : sa_tpartreimbursementids) {
            long sa_tpartreimbursementid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select sa_tpartreimbursementid,status from sa_tpartreimbursement where siteid='"
                    + siteid + "' and sa_tpartreimbursementid='" + sa_tpartreimbursementid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(sa_tpartreimbursementid, "非新建状态的配件核销单无法删除");
                    continue;
                }
            }
            ArrayList<String> sqllist = new ArrayList<>();
            sqllist.add("delete from sa_tpartreimbursement where siteid='" + siteid + "' and sa_tpartreimbursementid=" + sa_tpartreimbursementid);
            sqllist.add("delete from sa_tpartreimbursementdetail where siteid='" + siteid + "' and sa_tpartreimbursementid=" + sa_tpartreimbursementid);
            dbConnect.runSqlUpdate(sqllist);
        }
        return batchDeleteErr.getReturnObject().toString();
    }


    @API(title = "提交反提交", apiversion = R.ID2025081811223203.v1.class)
    @CACHEING_CLEAN(apiClass = {tpartreimbursement.class})
    public String submit() throws YosException {
        Long sa_tpartreimbursementid = content.getLong("sa_tpartreimbursementid");
        boolean issubmit = content.getBooleanValue("issubmit");

        Rows rows = dbConnect.runSqlQuery("select t1.billno,t1.status,createflag from sa_tpartreimbursement t1 where t1.sa_tpartreimbursementid ='"
                + sa_tpartreimbursementid + "' and  t1.siteid='" + siteid + "'");

        for (Row row : rows) {
            if (issubmit) {
                if (!row.getString("status").equals("新建")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的配件核销单为非新建状态，无法提交")
                            .toString();
                }
            } else {
                if (!row.getString("status").equals("提交")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的配件核销单为非提交状态，无法反提交")
                            .toString();
                }
            }

        }
        ArrayList<String> sqlList = new ArrayList<>();
        if (issubmit) {
            Rows rowsdetail = dbConnect.runSqlQuery("select t1.sa_orderitemsid from sa_tpartreimbursementdetail t1 where t1.sa_tpartreimbursementid ='"
                    + sa_tpartreimbursementid + "' and  t1.siteid='" + siteid + "'");

            QuerySQL tpartreimbursementdetailQuery = SQLFactory.createQuerySQL(dbConnect, "sa_tpartreimbursementdetail", "sa_orderitemsid");
            tpartreimbursementdetailQuery.addQueryFields("sumqty", "sum(qty)");
            tpartreimbursementdetailQuery.setWhere("sa_orderitemsid", rowsdetail.toArrayList("sa_orderitemsid"));
            tpartreimbursementdetailQuery.addGroupBy("sa_orderitemsid");


            QuerySQL orderitemsQuery = SQLFactory.createQuerySQL(dbConnect, "sa_orderitems", "sa_orderitemsid");
            orderitemsQuery.setTableAlias("t1");
            orderitemsQuery.addJoinTable(JOINTYPE.left,tpartreimbursementdetailQuery,"t2","t1.sa_orderitemsid=t2.sa_orderitemsid");
            orderitemsQuery.setWhere("t1.sa_orderitemsid", rowsdetail.toArrayList("sa_orderitemsid"));
            orderitemsQuery.addQueryFields("qty", "t1.tobeoffqty-ifnull(t2.sumqty,0)");
            Rows orderitemsrows = orderitemsQuery.query();
            if(!rows.get(0).getBoolean("createflag")){
                for (Row row :orderitemsrows) {
                    if(row.getBigDecimal("qty").compareTo(BigDecimal.ZERO)<0){
                        return getErrReturnObject().setErrMsg("该配件核销单数量不符合要求，请检查").toString();
                    }
                }
            }
            sqlList.add(
                    DataContrlLog.createLog(this, "sa_tpartreimbursement", sa_tpartreimbursementid, "提交", "配件核销单提交成功").getSQL());
        } else {
            sqlList.add(
                    DataContrlLog.createLog(this, "sa_tpartreimbursement", sa_tpartreimbursementid, "反提交", "配件核销单反提交成功").getSQL());
        }
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_tpartreimbursement");
        updateSQL.setUniqueid(sa_tpartreimbursementid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("status", issubmit ? "提交" : "新建");
        updateSQL.setValue("submitby", issubmit ? username : "");
        if (issubmit) {
            updateSQL.setDateValue("submitdate");
        } else {
            updateSQL.setValue("submitdate", "null");
        }

        sqlList.add(updateSQL.getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "审核", apiversion = R.ID2025081811295903.v1.class)
    @CACHEING_CLEAN(apiClass = {tpartreimbursement.class})
    public String check() throws YosException {
        Long sa_tpartreimbursementid = content.getLong("sa_tpartreimbursementid");
        Rows rows = dbConnect.runSqlQuery("select t1.billno,t1.status from sa_tpartreimbursement t1 where t1.sa_tpartreimbursementid ='"
                + sa_tpartreimbursementid + "' and  t1.siteid='" + siteid + "'");

        for (Row row : rows) {
            if (!row.getString("status").equals("提交")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的配件核销单为非提交状态，无法审核")
                        .toString();
            }

        }
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add(
                DataContrlLog.createLog(this, "sa_tpartreimbursement", sa_tpartreimbursementid, "审核", "配件核销单审核成功").getSQL());
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_tpartreimbursement");
        updateSQL.setUniqueid(sa_tpartreimbursementid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("status", "审核");
        updateSQL.setValue("checkby", username);
        updateSQL.setDateValue("checkdate");

        sqlList.add(updateSQL.getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "选择要核销的配件核销单明细", apiversion = R.ID2025081909593203.v1.class)
    @CACHEING
    public String quertpartreimbursementdetailList() throws YosException {
        /*
         * 过滤条件设置
         */
        long sys_enterpriseid = content.getLong("sys_enterpriseid");
        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("t3.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_tpartreimbursementdetailid", "remarks","qty", "sa_orderitemsid", "sa_tpartreimbursementid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid","price", "amount", "rowno", "cardno", "offstatus", "billingstatus");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t3", "t2.itemid = t3.itemid AND t2.siteid = t3.siteid", "itemno", "itemname", "spec", "model");
        querySQL.addJoinTable(JOINTYPE.left, "sa_order", "t4", "t2.sa_orderid = t4.sa_orderid AND t2.siteid = t4.siteid", "sonum");
        querySQL.addJoinTable(JOINTYPE.left, "sa_warrantycard", "t5", "t2.cardno = t5.cardno AND t2.siteid = t5.siteid", "name", "phonenumber");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_tpartreimbursement", "t6", "t1.sa_tpartreimbursementid = t6.sa_tpartreimbursementid AND t1.siteid = t6.siteid", "billno", "submitdate", "checkdate", "remarks");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t7", "t6.sys_enterpriseid = t7.sys_enterpriseid AND t6.siteid = t7.siteid", "abbreviation", "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t8", "t8.sys_enterpriseid = t6.sys_enterpriseid AND t8.siteid = t6.siteid", "agentnum");

        querySQL.setSiteid(siteid);
        querySQL.setWhere("t6.status='审核' and t1.isverified=0 and t6.sys_enterpriseid=" + sys_enterpriseid);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

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

    @API(title = "查询当月剩余可核销金额", apiversion = R.ID2025081815152503.v1.class)
    @CACHEING
    public String queryReimbursableAmount() throws YosException {
        sys_enterpriseid = content.getLong("sys_enterpriseid");
        Rows sumamountRows = dbConnect.runSqlQuery("select amount from st_stockbill t1 " +
                "inner join st_stockbill_items t2 on t1.st_stockbillid=t2.st_stockbillid and t1.siteid=t2.siteid  " +
                "inner join sa_order t3 on t2.sa_orderid=t3.sa_orderid and t2.siteid=t3.siteid" +
                " where t1.siteid='" + siteid + "' and t1.status='审核' and t1.type='销售出库' and t3.type='配件订单' and t3.sys_enterpriseid='" + sys_enterpriseid + "' and DATE_FORMAT( t1.checkdate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "st_stockbill_items", "t2", "t2.st_stockbillid = t1.st_stockbillid AND t2.siteid = t1.siteid","amount");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sys_enterpriseid", sys_enterpriseid);
        querySQL.setWhere("t1.sourceobject='tpartreimbursement' and t1.status='审核' and t1.type='销售出库' and DATE_FORMAT(t1.checkdate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
        Rows rows = querySQL.query();


        return getSucReturnObject().setData(sumamountRows.sum("amount").subtract(rows.sum("amount"))).toString();
    }

    @API(title = "查询审核需核销金额", apiversion = R.ID2025081815213903.v1.class)
    @CACHEING
    public String queryCheckAmount() throws YosException {
        JSONArray sa_tpartreimbursementdetailids = content.getJSONArray("sa_tpartreimbursementdetailids");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "qty");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid","price");
        querySQL.setSiteid(siteid);
        querySQL.addQueryFields("amount","ROUND(t1.qty*t2.price, 2)");
        querySQL.setWhere("t1.sa_tpartreimbursementdetailid", sa_tpartreimbursementdetailids);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows.sum("amount")).toString();
    }


    @API(title = "核销", apiversion = R.ID2025081814422703.v1.class)
    @CACHEING_CLEAN(apiClass = {tpartreimbursement.class, stockbill.class, Order.class, OrderItems.class})
    public String hexiao() throws YosException {
        ArrayList<String> sqllist = new ArrayList<>();
        long sys_enterpriseid = content.getLong("sys_enterpriseid");
        JSONArray sa_tpartreimbursementdetailids = content.getJSONArray("sa_tpartreimbursementdetailids");
        JSONArray iteminfos = content.getJSONArray("iteminfos");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_tpartreimbursementdetailid","isverified","qty","sa_orderitemsid");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_tpartreimbursementdetailid", sa_tpartreimbursementdetailids);
        Rows rows = querySQL.query();

        QuerySQL querySQLgroup = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_orderitemsid");
        querySQLgroup.setTableAlias("t1");
        querySQLgroup.addJoinTable(JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid");
        querySQLgroup.setSiteid(siteid);
        querySQLgroup.addQueryFields("sumqty", "sum(t1.qty)");
        querySQLgroup.setWhere("t1.sa_tpartreimbursementdetailid", sa_tpartreimbursementdetailids);
        querySQLgroup.addGroupBy("sa_orderitemsid");
        Rows rowsgroup = querySQLgroup.query();

        for (Row row : rows) {
            if (row.getBoolean("isverified")) {
                return getErrReturnObject().setErrMsg("存在记录已核销,请检查").toString();
            }
            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"));
        }

        String tableName = "st_stockbill";
        Rows stocks = dbConnect.runSqlQuery("select stockid from st_stock where stockname='配件仓库' and siteid='" + siteid + "'");
        Rows department = dbConnect.runSqlQuery("select departmentid from sys_department where depname='售后部' and siteid='" + siteid + "'");
        List<Long> list = new ArrayList<>();

        for (Object object : iteminfos) {
            list.add(((JSONObject)object).getLong("itemid"));
        }
        RowsMap itemRowsMap = SQLFactory.createQuerySQL(this, "plm_item", "*").setWhere("itemid", list).query().toRowsMap("itemid");

        long st_stockbillid = createTableID(tableName);
        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, tableName);
        insertSQL.setUniqueid(st_stockbillid);
        insertSQL.setSiteid(siteid);
        insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);
        insertSQL.setValue("rec_contactsid", 0);
        if (department.isEmpty()) {
            insertSQL.setValue("departmentid", 0);
        } else {
            insertSQL.setValue("departmentid", department.get(0).getLong("departmentid"));
        }
        if (stocks.isEmpty()) {
            insertSQL.setValue("stockid", 0);
        } else {
            insertSQL.setValue("stockid", stocks.get(0).getLong("stockid"));
        }
        insertSQL.setValue("type", "销售出库");
        insertSQL.setValue("typemx", "");
        insertSQL.setValue("sa_supplierid", content.getLongValue("sa_supplierid"));
        insertSQL.setValue("rb", 0);
        insertSQL.setValue("remarks", "配件核销;" + content.getStringValue("remarks"));
        insertSQL.setValue("billdate", getDate_Str());
        String billno = createBillCode("stockbill");
        insertSQL.setValue("billno", billno);
        insertSQL.setValue("outplace", "谈桥");
        insertSQL.setValue("delivery", "");
        insertSQL.setValue("invoice_enterprisename", "");
        insertSQL.setValue("invoice_address", "");
        insertSQL.setValue("invoice_taxno", "");
        insertSQL.setValue("tracknumber", "");
        insertSQL.setValue("isconfirm", 0);
        insertSQL.setValue("sourceobject", "tpartreimbursement");


        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
        Calendar calendar = Calendar.getInstance();
        insertSQL.setValue("period", dateFormat.format(calendar.getTime()));

        insertSQL.setValue("createby", username);
        insertSQL.setDateValue("createdate");
        insertSQL.setValue("changeby", username);
        insertSQL.setDateValue("changedate");
        insertSQL.setValue("checkby", username);
        insertSQL.setDateValue("checkdate");
        insertSQL.setValue("status", "审核");

        int i = 0;
        long maxid = 0;
        long[] st_stockbill_itemsid = createTableID("st_stockbill_items", iteminfos.size());
        Rows maxidRows = dbConnect
                .runSqlQuery("select MAX(rowno) rowno from st_stockbill_items where st_stockbillid=" + st_stockbillid);
        if (!maxidRows.isEmpty()) {
            maxid = maxidRows.get(0).getLong("rowno");
        }
        BigDecimal totalamount = BigDecimal.ZERO;
        for (Object obj : iteminfos) {
            JSONObject iteminfo = (JSONObject) obj;
            if (iteminfo.getLong("st_stockbill_itemsid") <= 0 || dbConnect
                    .runSqlQuery("select st_stockbill_itemsid from st_stockbill_items where st_stockbill_itemsid="
                            + iteminfo.getLong("st_stockbill_itemsid"))
                    .isEmpty()) {
                InsertSQL insertDetailSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items");
                insertDetailSQL.setUniqueid(st_stockbill_itemsid[i]);
                insertDetailSQL.setSiteid(siteid);
                insertDetailSQL.setValue("rowno", maxid + i + 1);
                insertDetailSQL.setValue("st_stockbillid", st_stockbillid);
                insertDetailSQL.setValue("sa_orderitemsid", 0);
                insertDetailSQL.setValue("itemid", iteminfo.getLongValue("itemid"));
                insertDetailSQL.setValue("sa_orderid", 0);
                insertDetailSQL.setValue("qty", iteminfo.getBigDecimal("qty"));
                insertDetailSQL.setValue("discountrate", iteminfo.getBigDecimal("discountrate"));
                insertDetailSQL.setValue("sa_dispatch_itemsid", 0);
                insertDetailSQL.setValue("defaultprice", iteminfo.getBigDecimal("defaultprice"));
                insertDetailSQL.setValue("remarks", "");
                insertDetailSQL.setValue("itemno", itemRowsMap.containsKey(iteminfo.getStringValue("itemid"))?itemRowsMap.get(iteminfo.getStringValue("itemid")).get(0).getString("itemno"):iteminfo.getString("itemno"));
                insertDetailSQL.setValue("itemname", itemRowsMap.containsKey(iteminfo.getStringValue("itemid"))?itemRowsMap.get(iteminfo.getStringValue("itemid")).get(0).getString("itemname"):iteminfo.getString("itemname"));
                insertDetailSQL.setValue("model", itemRowsMap.containsKey(iteminfo.getStringValue("itemid"))?itemRowsMap.get(iteminfo.getStringValue("itemid")).get(0).getString("model"):iteminfo.getString("model"));
                insertDetailSQL.setValue("price", iteminfo.getBigDecimal("price"));
                insertDetailSQL.setValue("amount", iteminfo.getBigDecimal("qty").multiply(iteminfo.getBigDecimal("price")));
                insertDetailSQL.setValue("untaxedprice", iteminfo.getBigDecimal("price").divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP));
                insertDetailSQL.setValue("untaxedamount", iteminfo.getBigDecimal("qty").multiply(iteminfo.getBigDecimal("price").divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP)));
                insertDetailSQL.setValue("skucontrol", itemRowsMap.containsKey(iteminfo.getStringValue("itemid"))?itemRowsMap.get(iteminfo.getStringValue("itemid")).get(0).getBoolean("skucontrol"):iteminfo.getBooleanValue("skucontrol"));
                i++;
                sqllist.add(insertDetailSQL.getSQL());
                totalamount = totalamount.add(iteminfo.getBigDecimal("qty").multiply(iteminfo.getBigDecimal("price")));
            }
        }
        insertSQL.setValue("payamount", totalamount);
        insertSQL.setValue("paydiscountamount", 0);

        sqllist.add(insertSQL.getSQL());

        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);

        CashbillEntity entity = getCashbillEntity(totalamount.negate(), BigDecimal.ZERO, 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));

        dbConnect.runSqlUpdate(sqllist);
        return getSucReturnObject().toString();
    }


}