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 beans.stockbill.Stockbill;
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.HashMap;
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,billdate 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");
                    updateSQL.setValue("billdate", StringUtils.isBlank(billdate) ? rows.get(0).getString("billdate") : billdate);
                    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.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.sys_enterpriseid = t2.sys_enterpriseid AND t1.siteid = t2.siteid","enterprisename","abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t1.sys_enterpriseid = t3.sys_enterpriseid AND t1.siteid = t3.siteid","agentnum");
        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","isverified");
        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"));
        querySQLdetail.addQueryFields("amount","ROUND(t2.price*t1.qty)");
        Rows rowsdetail = querySQLdetail.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("isverified").get("1").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.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.sys_enterpriseid = t2.sys_enterpriseid AND t1.siteid = t2.siteid","enterprisename","abbreviation");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t1.sys_enterpriseid = t3.sys_enterpriseid AND t1.siteid = t3.siteid","agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_tradefield", "t4", "t4.sys_enterpriseid = t1.sys_enterpriseid AND t4.siteid = t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t5", "t5.sa_saleareaid = t4.sa_saleareaid AND t5.siteid = t4.siteid","areanum","areaname","areafullname");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        if(sys_enterpriseid <= 0){
            querySQL.setWhere("((t1.createflag=0 and t1.status!='新建') or t1.createflag=1)");
        }else{
            querySQL.setWhere("((t1.createflag=1 and t1.status!='新建') or t1.createflag=0)");
        }

        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy("t1.sa_tpartreimbursementid desc");
        Rows rows = querySQL.query();



        QuerySQL querySQLdetail = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail", "sa_tpartreimbursementid", "sa_tpartreimbursementdetailid","qty","isverified");
        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"));
        querySQLdetail.addQueryFields("amount","ROUND(t2.price*t1.qty)");
        Rows rowsdetail = querySQLdetail.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("isverified").get("1").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(checkqty)");
            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();
            RowsMap rowsMap = orderitemsrows.toRowsMap("sa_orderitemsid");
            if(!rows.get(0).getBoolean("createflag")){
                for (Row row :orderitemsrows) {
                    if(row.getBigDecimal("qty").compareTo(BigDecimal.ZERO)<0){
                        return getErrReturnObject().setErrMsg("该配件核销单数量不符合要求，请检查").toString();
                    }
                }
            }

            for (int i = 0; i < rowsdetail.size(); i++) {
                if(rowsMap.containsKey(rowsdetail.get(i).getString("sa_orderitemsid"))){
                    if(rowsMap.get(rowsdetail.get(i).getString("sa_orderitemsid")).get(0).getBigDecimal("qty").compareTo(rowsdetail.get(i).getBigDecimal("qty"))<0){
                        return getErrReturnObject().setErrMsg("该配件核销单数量不符合要求，请检查").toString();
                    }
                }else{
                    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();
            }

        }
        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(checkqty)");
        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();
        RowsMap rowsMap = orderitemsrows.toRowsMap("sa_orderitemsid");
        if(!rows.get(0).getBoolean("createflag")){
            for (Row row :orderitemsrows) {
                if(row.getBigDecimal("qty").compareTo(BigDecimal.ZERO)<0){
                    return getErrReturnObject().setErrMsg("该配件核销单数量不符合要求，请检查").toString();
                }
            }
        }

        for (int i = 0; i < rowsdetail.size(); i++) {
            if(rowsMap.containsKey(rowsdetail.get(i).getString("sa_orderitemsid"))){
                if(rowsMap.get(rowsdetail.get(i).getString("sa_orderitemsid")).get(0).getBigDecimal("qty").compareTo(rowsdetail.get(i).getBigDecimal("qty"))<0){
                    return getErrReturnObject().setErrMsg("该配件核销单数量不符合要求，请检查").toString();
                }
            }else{
                return getErrReturnObject().setErrMsg("订单行不存在，请检查").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.ID2025121717464803.v1.class)
    @CACHEING_CLEAN(apiClass = {tpartreimbursement.class})
    public String uncheck() 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 + "'");

        Rows rowsdetail = dbConnect.runSqlQuery("select * from sa_tpartreimbursementdetail t1 where t1.sa_tpartreimbursementid ='"
                + sa_tpartreimbursementid + "' and  t1.siteid='" + siteid + "' and t1.isverified=1");


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

        }
        if(rowsdetail.isNotEmpty()){
            return getErrReturnObject().setErrMsg("有配件行已核销，核销申请单不能反审核").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", "");
        updateSQL.setValue("checkdate","null");

        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","checkqty", "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", "itemid","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 min(t1.payamount) payamount, min(t1.paydiscountamount) paydiscountamount,t1.st_stockbillid 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.rb=1 and t1.type='销售出库' and t3.type='配件订单' and t3.sys_enterpriseid='" + sys_enterpriseid + "' and DATE_FORMAT( t1.checkdate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )  group by t1.st_stockbillid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill","payamount","paydiscountamount");
        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();

        HashMap<String,BigDecimal> map =new HashMap<>();
        map.put("amount",sumamountRows.sum("payamount").subtract(rows.sum("payamount")));
        map.put("discountamount",sumamountRows.sum("paydiscountamount").subtract(rows.sum("paydiscountamount")));
        return getSucReturnObject().setData(map).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", "checkqty");
        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.checkqty*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<>();
        BigDecimal amount =BigDecimal.ZERO;
        BigDecimal discountamount =BigDecimal.ZERO;
        long sys_enterpriseid = content.getLong("sys_enterpriseid");


        Rows sumamountRows = dbConnect.runSqlQuery("select min(t1.payamount) payamount, min(t1.paydiscountamount) paydiscountamount,t1.st_stockbillid 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.rb=1 and t1.type='销售出库' and t3.type='配件订单' and t3.sys_enterpriseid='" + sys_enterpriseid + "' and DATE_FORMAT( t1.checkdate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )  group by t1.st_stockbillid");

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

        HashMap<String,BigDecimal> map =new HashMap<>();
        amount=sumamountRows.sum("payamount").subtract(rows1.sum("payamount"));
        discountamount=sumamountRows.sum("paydiscountamount").subtract(rows1.sum("paydiscountamount"));

        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","hongbillno");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_orderitems", "t2", "t1.sa_orderitemsid = t2.sa_orderitemsid AND t1.siteid = t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_tpartreimbursement", "t3", "t1.sa_tpartreimbursementid = t3.sa_tpartreimbursementid AND t1.siteid = t2.siteid","sa_tpartreimbursementid","billno");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_tpartreimbursementdetailid", sa_tpartreimbursementdetailids);
        Rows rows = querySQL.query();
        ArrayList<String> billnolist = rows.toArrayList("hongbillno");
        ArrayList<String> sa_tpartreimbursementidList = rows.toArrayList("sa_tpartreimbursementid");
        billnolist.add("111");
        QuerySQL stockbillquerySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "billno");
        stockbillquerySQL.setTableAlias("t1");
        stockbillquerySQL.setSiteid(siteid);
        stockbillquerySQL.setWhere("t1.billno",billnolist);
        Rows stockbillrows =stockbillquerySQL.query();
        RowsMap stockbillrowsMap = stockbillrows.toRowsMap("billno");
        for (Row row : rows) {
            if (StringUtils.isNotBlank(row.getString("hongbillno"))) {
                if(stockbillrowsMap.containsKey(row.getString("hongbillno"))){
                    return getErrReturnObject().setErrMsg("存在核销生成的红字出库单，无法核销").toString();
                }

            }
        }

//        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 stockno='111' 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"));
                if (stocks.isEmpty()) {
                    insertDetailSQL.setValue("stockid", 0);
                } else {
                    insertDetailSQL.setValue("stockid", stocks.get(0).getLong("stockid"));
                }
                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")));
            }
        }
        for(Object object:sa_tpartreimbursementdetailids){
            Integer sa_tpartreimbursementdetailid = (Integer) object;
            sqllist.add("update sa_tpartreimbursementdetail set hongbillno='"+billno+"' where sa_tpartreimbursementdetailid="+sa_tpartreimbursementdetailid);
        }
        if (discountamount.compareTo(totalamount) > 0) {
            insertSQL.setValue("payamount", 0);
            insertSQL.setValue("paydiscountamount", totalamount);
        } else {
            insertSQL.setValue("payamount", totalamount.subtract(discountamount));
            insertSQL.setValue("paydiscountamount", discountamount);
        }
        if(totalamount.compareTo((discountamount.add(amount)))>0){
            return getErrReturnObject().setErrMsg("大于当月剩余可核销金额，无法核销").toString();
        }


        sqllist.add(insertSQL.getSQL());
        sqllist.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "新增", "销售出库单新增成功,来源配件核销"+rows.toJsonArray("billno").toJSONString()).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);
        try {
            Stockbill.check(this, st_stockbillid, true);
        } catch (YosException e) {
            // Code to handle the exception
            dbConnect.runSqlUpdate("delete from st_stockbill where st_stockbillid="+st_stockbillid);
            dbConnect.runSqlUpdate("delete from st_stockbill_items where st_stockbillid="+st_stockbillid);
            dbConnect.runSqlUpdate("delete from sys_contrllog where ownerid="+st_stockbillid+" and ownertable='st_stockbill'");
            for(Object object:sa_tpartreimbursementdetailids){
                Integer sa_tpartreimbursementdetailid = (Integer) object;
                dbConnect.runSqlUpdate("update sa_tpartreimbursementdetail set hongbillno='' where sa_tpartreimbursementdetailid="+sa_tpartreimbursementdetailid);
            }
            return getErrReturnObject().setErrMsg(e.getMessage())
                    .toString();
        }

        sqllist=new ArrayList<>();
        QuerySQL tpartreimbursementSQL = SQLFactory.createQuerySQL(this, "sa_tpartreimbursementdetail","*");
        tpartreimbursementSQL.setTableAlias("t1");
        tpartreimbursementSQL.setSiteid(siteid);
        tpartreimbursementSQL.setWhere("t1.sa_tpartreimbursementid", sa_tpartreimbursementidList);
        Rows tpartreimbursementrows= tpartreimbursementSQL.query();
        RowsMap tpartreimbursementrowsMap = tpartreimbursementrows.toRowsMap("sa_tpartreimbursementid");
        for(String sa_tpartreimbursementid :sa_tpartreimbursementidList){
            if(tpartreimbursementrowsMap.containsKey(sa_tpartreimbursementid)){
                if(tpartreimbursementrowsMap.get(sa_tpartreimbursementid).size()
                        ==tpartreimbursementrowsMap.get(sa_tpartreimbursementid).toRowsMap("isverified").get("1").size()){
                    sqllist.add("update sa_tpartreimbursement set status='关闭' where sa_tpartreimbursementid= ='"+sa_tpartreimbursementid+"'");
                }

            }
        }
        dbConnect.runSqlUpdate(sqllist);

        return getSucReturnObject().toString();
    }


}