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

package restcontroller.webmanage.sale.stockbill;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import beans.dispatch.Dispatch;
import beans.invbal.Invbal;
import beans.parameter.Parameter;
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 org.apache.commons.lang.StringUtils;
import restcontroller.R;
import restcontroller.webmanage.sale.order.Order;
import restcontroller.webmanage.sale.order.OrderItems;

import javax.swing.text.StyledEditorKit;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import static java.time.LocalDate.now;

/**
 * 出入库单
 */
@API(title = "出入库单")
public class stockbill extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public stockbill(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "新增销售出库单", apiversion = R.ID2025043010105603.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class})
    public String insertormodify_stockbill() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名

        String tableName = "st_stockbill";
        Long st_stockbillid = content.getLong("st_stockbillid");
        Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        Rows contactrows =dbConnect.runSqlQuery("select * from sys_enterprise_contacts where sys_enterpriseid="+sys_enterpriseid+" and ifnull(deleted,0)=0");
        Long rec_contactsid=content.getLongValue("rec_contactsid");
        if(contactrows.isNotEmpty()){
            rec_contactsid = contactrows.get(0).getLong("contactsid");
        }
        Long departmentid = content.getLongValue("departmentid");
        String type = content.getStringValue("type");
        String typemx = content.getStringValue("typemx");
        String remarks = content.getStringValue("remarks");
        String billdate = content.getStringValue("billdate");
        String outplace = content.getStringValue("outplace");
        String delivery = content.getStringValue("delivery");
        Rows invoicerows =dbConnect.runSqlQuery("select * from sys_enterprise_finance where sys_enterpriseid="+sys_enterpriseid+" order by isdefault desc");
        String invoice_enterprisename = content.getStringValue("invoice_enterprisename");
        String invoice_address = content.getStringValue("invoice_address");
        String invoice_taxno = content.getStringValue("invoice_taxno");
        if(invoicerows.isNotEmpty()){
            invoice_enterprisename=invoicerows.get(0).getString("enterprisename");
            invoice_address=invoicerows.get(0).getString("address");
            invoice_taxno=invoicerows.get(0).getString("taxno");
        }
        String tracknumber = content.getStringValue("tracknumber");

        boolean isconfirm = content.getBooleanValue("isconfirm");
        SQLFactory sqlFactory = new SQLFactory(this, "发货单新增");

        if (st_stockbillid <= 0) {

            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", rec_contactsid);
            insertSQL.setValue("departmentid", departmentid);
            insertSQL.setValue("type", type);
            insertSQL.setValue("typemx", typemx);
            insertSQL.setValue("remarks", remarks);
            insertSQL.setValue("billdate", billdate);
            insertSQL.setValue("billno", createBillCode("stockbill"));
            insertSQL.setValue("outplace", outplace);
            insertSQL.setValue("delivery", delivery);
            insertSQL.setValue("invoice_enterprisename", invoice_enterprisename);
            insertSQL.setValue("invoice_address", invoice_address);
            insertSQL.setValue("invoice_taxno", invoice_taxno);
            insertSQL.setValue("tracknumber", tracknumber);
            insertSQL.setValue("isconfirm", isconfirm);

            insertSQL.setValue("period", content.getStringValue("period"));

            insertSQL.setValue("createby", username);
            insertSQL.setDateValue("createdate");
            insertSQL.setValue("changeby", username);
            insertSQL.setDateValue("changedate");
            insertSQL.setValue("status", "新建");
            insertSQL.setValue("payamount", 0);
            insertSQL.setValue("paydiscountamount", 0);
            sqlList.add(insertSQL.getSQL());

            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "新增", "销售出库单新增成功").getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery(
                    "SELECT status,sys_enterpriseid from st_stockbill WHERE st_stockbillid = "
                            + st_stockbillid);
            if (rows.isNotEmpty()) {
                if (rows.get(0).getString("status").equals("新建")) {

                    UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, tableName);
                    updateSQL.setUniqueid(st_stockbillid);
                    updateSQL.setSiteid(siteid);
                    updateSQL.setValue("sys_enterpriseid", sys_enterpriseid);
                    updateSQL.setValue("rec_contactsid", rec_contactsid);
                    updateSQL.setValue("departmentid", departmentid);
                    updateSQL.setValue("remarks", remarks);
                    updateSQL.setValue("billdate", billdate);
                    updateSQL.setValue("outplace", outplace);
                    updateSQL.setValue("delivery", delivery);
                    updateSQL.setValue("invoice_enterprisename", content.getStringValue("invoice_enterprisename"));
                    updateSQL.setValue("invoice_address", content.getStringValue("invoice_address"));
                    updateSQL.setValue("invoice_taxno", content.getStringValue("invoice_taxno"));
                    updateSQL.setValue("tracknumber", tracknumber);
                    updateSQL.setValue("isconfirm", isconfirm);
                    updateSQL.setValue("payamount", content.getBigDecimal("payamount"));
                    updateSQL.setValue("paydiscountamount", content.getBigDecimal("paydiscountamount"));
                    updateSQL.setValue("period", content.getStringValue("period"));

                    updateSQL.setValue("changeby", username);
                    updateSQL.setDateValue("changedate");
                    sqlList.add(updateSQL.getSQL());

                    sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "更新", "销售出库单更新成功").getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("非新建状态下无法编辑").toString();
                }

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

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

        return queryStockbillMain();
    }

    @API(title = "出入库单详情", apiversion = R.ID20230719153803.v1.class)
    @CACHEING
    public String queryStockbillMain() throws YosException {
        Long st_stockbillid = content.getLong("st_stockbillid");
        SQLFactory sqlFactory = new SQLFactory(this, "出入库单详情");
        sqlFactory.addParameter("st_stockbillid", st_stockbillid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        Rows rowsdetail = dbConnect.runSqlQuery("select amount from st_stockbill_items where st_stockbillid="+st_stockbillid);
        row.put("receivableamount",rowsdetail.sum("amount"));

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

    @API(title = "查询出入库单列表", apiversion = R.ID20230719153903.v1.class)
    @CACHEING
    public String queryStockbillList() 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("t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.outplace like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t6.depname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                where.append(" and t1.type ='").append(whereObject.getString("type")).append("' ");
            }
            if (whereObject.containsKey("typemx") && !"".equals(whereObject.getString("typemx"))) {
                where.append(" and t1.typemx ='").append(whereObject.getString("typemx")).append("' ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("rb") && !"".equals(whereObject.getString("rb"))) {
                where.append(" and t1.rb ='").append(whereObject.getString("rb")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and DATE_FORMAT(t1.billdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
            if (whereObject.containsKey("remarks") && !"".equals(whereObject.getString("remarks"))) {
                where.append("and t1.remarks like'%").append(whereObject.getString("remarks")).append("%' ");
            }


            if (whereObject.containsKey("dateinterval") && !"".equals(whereObject.getString("dateinterval"))) {
                if(whereObject.getString("dateinterval").equals("past30days")){
                    where.append("and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >=DATE_SUB(CURDATE(), INTERVAL 30 DAY)");
                } else if (whereObject.getString("dateinterval").equals("past15days")) {
                    where.append("and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >=DATE_SUB(CURDATE(), INTERVAL 15 DAY)");
                }else if (whereObject.getString("dateinterval").equals("today")) {
                    where.append("and DATE_FORMAT(t1.billdate, '%Y-%m-%d') >=CURDATE()");
                }
            }

            if (whereObject.containsKey("checknoprint") && !"".equals(whereObject.getString("checknoprint"))) {
                if(whereObject.getBooleanValue("checknoprint")){
                    where.append("and t1.status ='审核' and isreport=0");
                }
            }
            if (whereObject.containsKey("nowaybillgenerated") && !"".equals(whereObject.getString("nowaybillgenerated"))) {
                if(whereObject.getBooleanValue("checknoprint")){
                    where.append("and t1.isreceiver=0 and ifnull(txlogisticid,'')='' ");
                }
            }
            if (whereObject.containsKey("paynocheck") && !"".equals(whereObject.getString("paynocheck"))) {
                where.append("and t1.status ='新建' and delivery='到付'");
            }


            if (whereObject.containsKey("param") && !"".equals(whereObject.getString("param"))) {
                Rows rowsStockbillid = dbConnect.runSqlQuery("select t1.st_stockbillid from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid " +
                        " where t1.siteid='" + siteid + "' and (t2.itemname like '%" + whereObject.getString("param") + "%' or t2.itemno like '%" + whereObject.getString("param") + "%')");

                if (rowsStockbillid.toJsonArray("st_stockbillid").isEmpty()) {
                    where.append(" and t1.st_stockbillid in (0)");
                } else {
                    String str = rowsStockbillid.toJsonArray("st_stockbillid").toJSONString();
                    str = str.replace("[", "(").replace("]", ")");
                    where.append(" and t1.st_stockbillid  in").append(str);
                }
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "出入库单列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = queryStockbillList(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        Rows rows = querySQL.query();

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

    //查询出入库单列表
    public QuerySQL queryStockbillList(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill","*");
        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_contacts", "t4", "t1.rec_contactsid=t4.contactsid and t1.siteid = t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_accountbalance", "t5", "t5.sys_enterpriseid=t1.sys_enterpriseid and t1.siteid = t5.siteid and t5.sa_accountclassid =(select t1.sa_accountclassid from sa_accountclass t1 where t1.siteid='"+siteid+"' and t1.accountname='现金账户')",
                "discountamount");
        querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t6", "t1.departmentid=t6.departmentid and t1.siteid = t6.siteid",
                "depno","depname");
        querySQL.addQueryFields("recname", "t4.name");
        querySQL.addQueryFields("recphonenumber", "t4.phonenumber");
        querySQL.addQueryFields("recaddress", "t4.address");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        return querySQL;
    }


    @API(title = "删除", apiversion = R.ID20230719154003.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class})
    public String delete() throws YosException {

        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, st_stockbillids.size());
        for (Object o : st_stockbillids) {
            long st_stockbillid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select st_stockbillid,status from st_stockbill where siteid='"
                    + siteid + "' and st_stockbillid='" + st_stockbillid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(st_stockbillid, "非新建状态的出入库单无法删除");
                    continue;
                }
            }

            dbConnect.runSqlUpdate(
                    "delete from st_stockbill where siteid='" + siteid + "' and st_stockbillid=" + st_stockbillid);
        }
        return batchDeleteErr.getReturnObject().toString();

    }
    @API(title = "审核", apiversion = R.ID20230719154103.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class, Order.class, OrderItems.class,restcontroller.sale.stockbill.stockbill.class})
    public String check() throws YosException {
        Long st_stockbillid = content.getLong("st_stockbillid");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = dbConnect.runSqlQuery("select * from st_stockbill where st_stockbillid ='"
                + st_stockbillid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该出入库单不存在")
                    .toString();
        }
        String type=rows.get(0).getString("type");
        boolean isinstock = isinstock(type,rows.get(0).getString("rb"),true);
        if(type.equals("销售出库")){
            String str = unxsckcheck(st_stockbillid);
            if(!str.equals("success")){
                return getErrReturnObject().setErrMsg(str)
                        .toString();
            }
        }else if (type.equals("外购入库")) {

        } else if (type.equals("生产领料出库")) {

        } else if (type.equals("委外领料出库")) {

        } else if (type.equals("生产入库") || type.equals("委外入库")) {

        } else if (type.equals("其他入库")) {

        } else if (type.equals("返修入库")) {// 云链

        } else if (type.equals("返修出库")) {// 云链

        }
        updateIcinvbal(isinstock,0,st_stockbillid);// 即时库存计算

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "出入库单审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("st_stockbillid", st_stockbillid);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "审核", "出入库单审核成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "反审核", apiversion = R.ID20230719154203.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class, Order.class, OrderItems.class,stockbill.class,restcontroller.sale.stockbill.stockbill.class})
    public String uncheck() throws YosException {
        Long st_stockbillid = content.getLong("st_stockbillid");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = dbConnect.runSqlQuery("select type,rb from st_stockbill where st_stockbillid ='"
                + st_stockbillid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该出入库单不存在")
                    .toString();
        }
        String type=rows.get(0).getString("type");
        boolean isinstock = isinstock(rows.get(0).getString("type"),rows.get(0).getString("rb"),false);
        if(type.equals("销售出库")){
            String str = unxsckcheck(st_stockbillid);
            if(!str.equals("success")){
                return getErrReturnObject().setErrMsg(str)
                        .toString();
            }
        }else if (type.equals("外购入库")) {

        } else if (type.equals("生产领料出库")) {

        } else if (type.equals("委外领料出库")) {

        } else if (type.equals("生产入库") || type.equals("委外入库")) {

        } else if (type.equals("其他入库")) {

        } else if (type.equals("返修入库")) {// 云链

        } else if (type.equals("返修出库")) {// 云链

        }
        updateIcinvbal(isinstock,0,st_stockbillid);// 即时库存计算

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "出入库单反审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("st_stockbillid", st_stockbillid);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "反审核", "出入库单反审核成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    public String xsckcheck(Long st_stockbillid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("select * from st_stockbill where st_stockbillid ='"
                + st_stockbillid + "' and  siteid='" + siteid + "'");
        Rows rowsdetail = dbConnect.runSqlQuery("select t1.*,t2.skucontrol from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.st_stockbillid ='"
                + st_stockbillid + "' and  t1.siteid='" + siteid + "'");
        long sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");

        if (rows.isEmpty()) {
            return "该出入库单不存在";
        } else {
            if (!rows.get(0).getString("status").equals("新建")) {
                return "单号为：【" + rows.get(0).getString("billno") + "】的出入库单为非新建状态，无法审核";
            }
        }
        for(Row row : rowsdetail){
            if (row.getBigDecimal("qty").compareTo(BigDecimal.ZERO)<1) {
                return "表体数量不能小于0";
            }
        }
        if (rows.get(0).getBigDecimal("payamount").compareTo(BigDecimal.ZERO)<=0) {
            return "扣款金额需大于0";
        }
        if (StringUtils.isBlank(rows.get(0).getString("outplace"))) {
            return "发货地不可为空";
        }
        if ((rows.get(0).getBigDecimal("payamount").add(rows.get(0).getBigDecimal("paydiscountamount"))).compareTo(rowsdetail.sum("amount"))!=0) {
            return "扣款金额与应扣金额不符，不能审核";
        }
        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);
        if (accountbalancerows.isNotEmpty()) {
            BigDecimal discountamount = accountbalancerows.get(0).getBigDecimal("discountamount");
            if (rows.get(0).getBigDecimal("paydiscountamount").compareTo(discountamount)>0) {
                return "优惠金额不能大于可用优惠金额";
            }
        }else{
            return "账户不存在";
        }
        ArrayList<String> sqlList = new ArrayList<>();
//        Rows stockRows= dbConnect.runSqlQuery("select * from st_stock where siteid='"+siteid+"'");
//        RowsMap stockRowsMap =stockRows.toRowsMap("itemid");

        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        if(rows.get(0).getBoolean("rb")){
            if(rows.get(0).getString("type").equals("销售出库")){
                if (order_paymentnode.equals("3")) {
                    if (Accountbalance.judgeBalance(this, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"),rowsdetail.sum("amount"))) {
                        CashbillEntity entity = getCashbillEntity(rows.get(0).getBigDecimal("payamount"),rows.get(0).getBigDecimal("paydiscountamount"), st_stockbillid, "销售出库单审核", rows.get(0).getString("billno"), "由销售出库单" + rows.get(0).getString("billno") + "审核时生成");
                        JSONObject createCashbillPay = Accountbalance.createCashbillPay(this, sys_enterpriseid,accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                        sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    } else {
                        return "账户余额不足，还差" + Accountbalance.InsufficientBalance(this, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"),rowsdetail.sum("amount")) + "元！";
                    }
                }
                Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from st_stockbill_items where st_stockbillid=" + st_stockbillid + ")");
                RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
                Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,t1.sa_orderitemsid,t1.siteid from st_stockbill_items t1  where  t1.st_stockbillid=" + st_stockbillid + " group by t1.sa_orderitemsid,t1.siteid");
                Rows rowsjudge =dbConnect.runSqlQuery("select * from (select sa_orderitemsid,sum(qty) qty from st_stockbill_items where st_stockbillid ='"+st_stockbillid+"' group by sa_orderitemsid) t1 inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid where t1.qty>t2.undeliqty");
                if(rowsjudge.isNotEmpty()){
                    return "存在商品已完全出库，无法审核";
                }
                for (Row row : rowsDispatchDetailGroup) {
                    sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").subtract(row.getBigDecimal("qty")) + ",deliedqty=" + (row.getBigDecimal("qty").add(rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                }
                for(Row row :rowsdetail){
                    if(row.getBoolean("skucontrol")){
                        sqlList.add("update st_stockbill_items set wmsuploadflag2=1 where st_stockbill_itemsid="+row.getLong("st_stockbill_itemsid"));
                    }

                }
            }
//            for(Row row :rowsdetail){
//                String itemid= row.getString("itemid");
//                long stockid=row.getLong("stockid");
//                if(stockRowsMap.containsKey(itemid)){
//                    for(Row row1 :stockRowsMap.get(itemid)){
//                        if(row1.getLong("stockid")==stockid){
//                            sqlList.add("update st_stock set qty="+row1.getBigDecimal("qty").add(row.getBigDecimal("qty"))+" where stockid="+stockid);
//                        }
//                    }
//                }
//                // String batchno = row.getString("batchno");
//            }
        }else{
//            for(Row row :rowsdetail){
//                String itemid= row.getString("itemid");
//                long stockid=row.getLong("stockid");
//                if(stockRowsMap.containsKey(itemid)){
//                    for(Row row1 :stockRowsMap.get(itemid)){
//                        if(row1.getLong("stockid")==stockid){
//                            sqlList.add("update st_stock set qty="+row1.getBigDecimal("qty").subtract(row.getBigDecimal("qty"))+" where stockid="+stockid);
//                        }
//                    }
//                }
//                // String batchno = row.getString("batchno");
//            }
        }
//            Rows stockbillitems = dbConnect.runSqlQuery("select t2.sa_orderid,sum(t2.price*t1.qty) amount from st_stockbill_items t1 inner join sa_orderitems t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.siteid='"+siteid+"'and t1.st_stockbillid="+st_stockbillid+" group by t2.sa_orderid");
//            Rows orderRows =dbConnect.runSqlQuery("select * from sa_order where sa_orderid in(select t2.sa_orderid from st_stockbill_items t1 inner join sa_orderitems t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.st_stockbillid='"+st_stockbillid+"')");
//            RowsMap orderRowsMap =orderRows.toRowsMap("sa_orderid");

        dbConnect.runSqlUpdate(sqlList);
//        Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
        return "success";

    }

    public String unxsckcheck(Long st_stockbillid) throws YosException {


        Rows rows = dbConnect.runSqlQuery("select * from st_stockbill where st_stockbillid ='"
                + st_stockbillid + "' and  siteid='" + siteid + "'");
        Rows rowsdetail = dbConnect.runSqlQuery("select t1.*,t2.skucontrol from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.st_stockbillid ='"
                + st_stockbillid + "' and  t1.siteid='" + siteid + "'");
        long sys_enterpriseid = rows.get(0).getLong("sys_enterpriseid");

        if (rows.isEmpty()) {
            return "该出入库单不存在";
        } else {
            if (!rows.get(0).getString("status").equals("审核")) {
                return "单号为：【" + rows.get(0).getString("billno") + "】的出入库单为非审核状态，无法反审核";
            }
        }
        for(Row row : rowsdetail){
            if (row.getBigDecimal("qty").compareTo(BigDecimal.ZERO)<1) {
                return "表体数量不能小于0";
            }
        }
        if (rows.get(0).getBigDecimal("paydiscountamount").compareTo(BigDecimal.ZERO)<0) {
            return "优惠金额不能小于0";
        }
        if (rows.get(0).getBigDecimal("payamount").compareTo(BigDecimal.ZERO)<=0) {
            return "扣款金额需大于0";
        }
        if (StringUtils.isBlank(rows.get(0).getString("outplace"))) {
            return "发货地不可为空";
        }
        if ((rows.get(0).getBigDecimal("payamount").add(rows.get(0).getBigDecimal("paydiscountamount"))).compareTo(rowsdetail.sum("amount"))!=0) {
            return "扣款金额与应扣金额不符，不能反审核";
        }
        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);
        if (accountbalancerows.isEmpty()) {
            return "账户不存在";
        }
        if (rows.get(0).getString("type").equals("销售出库") && rows.get(0).getBoolean("isreceiver")) {
            return "经销商已经确认收货，不可进行反审核";
        }
        ArrayList<String> sqlList = new ArrayList<>();
//        Rows stockRows= dbConnect.runSqlQuery("select * from st_stock where siteid='"+siteid+"'");
//        RowsMap stockRowsMap =stockRows.toRowsMap("itemid");

        String order_paymentnode = Parameter.getString(siteid, "order_paymentnode");
        if(rows.get(0).getBoolean("rb")){
            if(rows.get(0).getString("type").equals("销售出库")){
                if (order_paymentnode.equals("3")) {
                    if (Accountbalance.judgeBalance(this, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"),rowsdetail.sum("amount").negate())) {
                        CashbillEntity entity = getCashbillEntity(rows.get(0).getBigDecimal("payamount").negate(),rows.get(0).getBigDecimal("paydiscountamount").negate(), st_stockbillid, "销售出库单反审核", rows.get(0).getString("billno"), "由销售出库单" + rows.get(0).getString("billno") + "反审核时生成");
                        JSONObject createCashbillPay = Accountbalance.createCashbillPay(this, sys_enterpriseid,accountbalancerows.get(0).getLong("sa_accountclassid"), entity, true);
                        sqlList.addAll(createCashbillPay.getJSONArray("sqlList").toJavaList(String.class));
                    } else {
                        return "账户余额不足，还差" + Accountbalance.InsufficientBalance(this, sys_enterpriseid, accountbalancerows.get(0).getLong("sa_accountclassid"),rowsdetail.sum("amount")) + "元！";
                    }
                }
                Rows rowsOrderDetail = dbConnect.runSqlQuery("select t1.undeliqty,t1.deliedqty,t1.qty,t1.sa_orderitemsid from sa_orderitems t1 where t1.sa_orderitemsid in (select sa_orderitemsid from st_stockbill_items where st_stockbillid=" + st_stockbillid + ")");
                RowsMap rowsMap = rowsOrderDetail.toRowsMap("sa_orderitemsid");
                Rows rowsDispatchDetailGroup = dbConnect.runSqlQuery("select sum(t1.qty) qty,t1.sa_orderitemsid,t1.siteid from st_stockbill_items t1  where  t1.st_stockbillid=" + st_stockbillid + " group by t1.sa_orderitemsid,t1.siteid");
                for (Row row : rowsDispatchDetailGroup) {
                    sqlList.add("update sa_orderitems set undeliqty=" + rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("undeliqty").add(row.getBigDecimal("qty")) + ",deliedqty=" + (rowsMap.get(row.getLong("sa_orderitemsid")).get(0).getBigDecimal("deliedqty").subtract(row.getBigDecimal("qty"))) + " where sa_orderitemsid=" + row.getLong("sa_orderitemsid"));
                }
                for(Row row :rowsdetail){
                    if(row.getBoolean("skucontrol")){
                        sqlList.add("update st_stockbill_items set wmsuploadflag2=0 where st_stockbill_itemsid="+row.getLong("st_stockbill_itemsid"));
                    }

                }
            }
//            for(Row row :rowsdetail){
//                String itemid= row.getString("itemid");
//                long stockid=row.getLong("stockid");
//                if(stockRowsMap.containsKey(itemid)){
//                    for(Row row1 :stockRowsMap.get(itemid)){
//                        if(row1.getLong("stockid")==stockid){
//                            sqlList.add("update st_stock set qty="+row1.getBigDecimal("qty").subtract(row.getBigDecimal("qty"))+" where stockid="+stockid);
//                        }
//                    }
//                }
//                // String batchno = row.getString("batchno");
//            }
        }else{
//            for(Row row :rowsdetail){
//                String itemid= row.getString("itemid");
//                long stockid=row.getLong("stockid");
//                if(stockRowsMap.containsKey(itemid)){
//                    for(Row row1 :stockRowsMap.get(itemid)){
//                        if(row1.getLong("stockid")==stockid){
//                            sqlList.add("update st_stock set qty="+row1.getBigDecimal("qty").add(row.getBigDecimal("qty"))+" where stockid="+stockid);
//                        }
//                    }
//                }
//                // String batchno = row.getString("batchno");
//            }
        }
//            Rows stockbillitems = dbConnect.runSqlQuery("select t2.sa_orderid,sum(t2.price*t1.qty) amount from st_stockbill_items t1 inner join sa_orderitems t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.siteid='"+siteid+"'and t1.st_stockbillid="+st_stockbillid+" group by t2.sa_orderid");
//            Rows orderRows =dbConnect.runSqlQuery("select * from sa_order where sa_orderid in(select t2.sa_orderid from st_stockbill_items t1 inner join sa_orderitems t2 on t1.siteid=t2.siteid and t1.sa_orderitemsid=t2.sa_orderitemsid where t1.st_stockbillid='"+st_stockbillid+"')");
//            RowsMap orderRowsMap =orderRows.toRowsMap("sa_orderid");
        dbConnect.runSqlUpdate(sqlList);

//        Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
        return "success";
    }

    @API(title = "仓库确认", apiversion = R.ID2025050709505703.v1.class)
    public String confirm() throws YosException {
        Long st_stockbillid = content.getLong("st_stockbillid");
        boolean isconfirm = content.getBooleanValue("isconfirm");
        Rows rows = dbConnect.runSqlQuery("select * from st_stockbill where st_stockbillid ='"
                + st_stockbillid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该出入库单不存在")
                    .toString();
        } else {
            if (!rows.get(0).getString("status").equals("审核")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("billno") + "】的出入库单为非审核状态，无法进行仓库确认")
                        .toString();
            }
        }
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "st_stockbill");
        updateSQL.setUniqueid(st_stockbillid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("isconfirm", isconfirm);
        updateSQL.setValue("changeby", username);
        updateSQL.setDateValue("changedate");
        dbConnect.runSqlUpdate(updateSQL.getSQL());
        return getSucReturnObject().toString();
    }


    @API(title = "更新站点信息详情详情", apiversion = R.ID20230720143503.v1.class)
    public String updateParameterSiteInfo() throws YosException {
        BigDecimal icstockbillrebaterate = content.getBigDecimal("icstockbillrebaterate");
        Long icstockbillrebateaccount = content.getLong("icstockbillrebateaccount");
        dbConnect.runSqlUpdate("update sys_site_parameter set icstockbillrebaterate='" + icstockbillrebaterate + "',icstockbillrebateaccount='" + icstockbillrebateaccount + "' where siteid='" + siteid + "'");

        return getSucReturnObject().toString();
    }

    @API(title = "站点信息详情", apiversion = R.ID20230720143603.v1.class)
    public String queryParameterSiteInfo() throws YosException {

        Rows rows = dbConnect.runSqlQuery("select icstockbillrebateaccount,icstockbillrebaterate from sys_site_parameter where siteid='" + siteid + "'");
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();

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

    public CashbillEntity getCashbillEntity(BigDecimal amount,BigDecimal discountamount, Long ownerid, String source, String sourcenotes, String remarks) throws YosException {
        CashbillEntity entity = new CashbillEntity();
        entity.setAmount(amount);
        entity.setDiscountamountamount(discountamount);
        entity.setOwnerid(ownerid);
        entity.setOwnertable("st_stockbill");
        entity.setSource(source);
        entity.setRemarks(remarks);
        entity.setSourcenote(sourcenotes);
        return entity;

    }

    /**
     *
     * @param type
     * @param rb
     * @param fischeck
     * @return
     */
    public boolean isinstock(String type,String rb,boolean fischeck){
        if (type.equals("其他出库") || type.equals("销售出库")
                || type.equals("生产领料出库") || type.equals("委外领料出库")
                || type.equals("返修出库")) {
            if ((fischeck && rb.equals("1")) || (!fischeck && rb.equals("0"))) {
                return false;
            } else {
                return true;
            }

        } else if (type.equals("其他入库") || type.equals("外购入库")
                || type.equals("生产入库") || type.equals("委外入库")
                || type.equals("返修入库")) {
            if ((fischeck && rb.equals("1")) || (!fischeck && rb.equals("0"))) {
                return true;
            } else {
                return false;
            }
        }
        return false;
    }

    /**
     * @param isinstock 是否增加库存
     * @param type      库存更新类型 0为默认形式。1表示生产领料更新现场仓 2表示委外领料更新现场仓
     * @throws YosException
     */
    public void updateIcinvbal(boolean isinstock, int type,Long st_stockbillid) throws YosException {
        ArrayList<String> sqlList =new ArrayList();
        Rows icstockbilldetail =dbConnect.runSqlQuery("select t1.*,t2.skucontrol from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.st_stockbillid ='"
                + st_stockbillid + "' and  t1.siteid='" + siteid + "'");// 出入库表体
        ArrayList<Long> itemids = new ArrayList<Long>();
        itemids = icstockbilldetail.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");
         for(Row row : icstockbilldetail){
             String itemid = row.getString("itemid");
             //String batchno = row.getString("batchno");
             //String fdcspno = row.getString("fdcspno");
             String stockid = row.getString("stockid");

//             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);
                     sqlFactory.addParameter("siteid",siteid);
                     sqlFactory.addParameter("userid", userid);
                     sqlFactory.addParameter("username", username);
                     sqlList.add(sqlFactory.getSQL());
                 }
             }
         }
         dbConnect.runSqlUpdate(sqlList);
    }
}