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.itemprice.ItemPrice;
import beans.parameter.Parameter;
import beans.report.Report;
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 org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.sale.order.Order;
import restcontroller.webmanage.sale.order.OrderItems;
import restcontroller.webmanage.sale.sendrepair.sendrepair;
import restcontroller.webmanage.sale.sendrepair.sendrepair_pj;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

/**
 * 出入库单
 */
@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");
        long stockid = content.getLongValue("stockid");
        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");
        String logisticsmethod = content.getStringValue("logisticsmethod");
        Rows invoicerows = dbConnect.runSqlQuery("select * from sys_enterprise_finance where sys_enterpriseid=" + sys_enterpriseid + " order by isdefault desc");
        Rows addressrows = dbConnect.runSqlQuery("select * from sys_enterprise_contacts where sys_enterpriseid=" + sys_enterpriseid + " order by isdefault desc");
        RowsMap addressrowsMap = addressrows.toRowsMap("type");
        Rows departmentrows = dbConnect.runSqlQuery("select depname from sys_department where departmentid=" + departmentid);
        String depname = "";
        if (departmentrows.isNotEmpty()) {
            depname = departmentrows.get(0).getString("depname");
        }
        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");

        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("stockid", stockid);
            insertSQL.setValue("type", type);
            insertSQL.setValue("typemx", typemx);
            insertSQL.setValue("sa_supplierid", content.getLongValue("sa_supplierid"));
            insertSQL.setValue("rb", content.getString("rb"));
            insertSQL.setValue("remarks", remarks);
            if (StringUtils.isBlank(billdate)) {
                insertSQL.setDateValue("billdate");
                insertSQL.setValue("period", getPeriod(false, new Date()));
            } else {
                insertSQL.setValue("billdate", billdate);
                insertSQL.setValue("period", getPeriod(false, content.getDate("billdate")));
            }
            if (type.equals("销售出库")) {
                insertSQL.setValue("billno", createBillCode("stockbill"));
            } else if (type.equals("其他出库单")) {
                insertSQL.setValue("billno", createBillCode("STOCKBILL1"));
            } else if (type.equals("其他入库单")) {
                insertSQL.setValue("billno", createBillCode("STOCKBILL2"));
            } else {
                insertSQL.setValue("billno", createBillCode("stockbill"));
            }
            String name = "";
            String phonenumber = "";
            String address = "";
            if (content.getString("rb").equals("1") && type.equals("销售出库")) {
                if (depname.equals("售后部")) {
                    if (addressrowsMap.containsKey("配件")) {
                        name = addressrowsMap.get("配件").get(0).getString("name");
                        phonenumber = addressrowsMap.get("配件").get(0).getString("phonenumber");
                        address = addressrowsMap.get("配件").get(0).getString("address");
                    } else if (addressrowsMap.containsKey("收货")) {
                        name = addressrowsMap.get("收货").get(0).getString("name");
                        phonenumber = addressrowsMap.get("收货").get(0).getString("phonenumber");
                        address = addressrowsMap.get("收货").get(0).getString("address");
                    }
                } else {
                    if (addressrowsMap.containsKey("收货")) {
                        name = addressrowsMap.get("收货").get(0).getString("name");
                        phonenumber = addressrowsMap.get("收货").get(0).getString("phonenumber");
                        address = addressrowsMap.get("收货").get(0).getString("address");
                    }
                }
            }

            insertSQL.setValue("name", name);
            insertSQL.setValue("phonenumber", phonenumber);
            insertSQL.setValue("address", address);
            insertSQL.setValue("outplace", outplace);
            insertSQL.setValue("delivery", delivery);
            insertSQL.setValue("logisticsmethod", logisticsmethod);
            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("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,paydiscountamount,payamount,sourceobject from st_stockbill WHERE st_stockbillid = "
                            + st_stockbillid);
            if (rows.isNotEmpty()) {
                if (rows.get(0).getString("status").equals("新建")) {
                    if (rows.get(0).getString("sourceobject").equals("tpartreimbursement")) {
                        return getErrReturnObject().setErrMsg("由核销生成的出入库单无法编辑").toString();
                    }
                    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("stockid", stockid);
                    updateSQL.setValue("remarks", remarks);
                    updateSQL.setValue("rb", content.getString("rb"));
                    updateSQL.setValue("typemx", typemx);
                    updateSQL.setValue("billdate", billdate);
                    updateSQL.setValue("outplace", outplace);
                    updateSQL.setValue("delivery", delivery);
                    updateSQL.setValue("logisticsmethod", logisticsmethod);
                    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);
                    if (type.equals("销售出库")) {
                        updateSQL.setValue("payamount", content.getBigDecimal("payamount"));
                        updateSQL.setValue("paydiscountamount", content.getBigDecimal("paydiscountamount"));
                        if (content.getBigDecimal("payamount").compareTo(rows.get(0).getBigDecimal("payamount")) != 0
                                || content.getBigDecimal("paydiscountamount").compareTo(rows.get(0).getBigDecimal("paydiscountamount")) != 0) {
                            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "金额更新变更", "销售出库单更支付金额:" + content.getBigDecimal("payamount") + "，优惠金额:" + content.getBigDecimal("paydiscountamount")).getSQL());
                        }
                    }

                    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.ID2025102215133403.v1.class)
    public String billdateChange() throws YosException {
        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        Date billdate = content.getDate("billdate");
        String period = getPeriod(false, billdate);
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, st_stockbillids.size());
        ArrayList<String> sqlList = new ArrayList<>();
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "*").setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.st_stockbillid", st_stockbillids);
        Rows rows = querySQL.query();
        RowsMap rowsMap = rows.toRowsMap("st_stockbillid");
        RowsMap periodrowsMap = dbConnect.runSqlQuery("select CONCAT(year, '-',LPAD(month, 2, '0')) period,isclose from st_period").toRowsMap("isclose");

//        if(periodrowsMap.get("1").toRowsMap("period").containsKey(period)){
//            return getErrReturnObject().setErrMsg("原单据会计期间已关闭，不可调整").toString();
//        }
        for (Object o : st_stockbillids) {
            long st_stockbillid = Long.parseLong(o.toString());
            if (rowsMap.containsKey(st_stockbillid)) {
                if (rowsMap.get(st_stockbillid).get(0).getString("status").equals("审核")) {
                    if (periodrowsMap.get("1").toRowsMap("period").containsKey(period)) {
                        batchDeleteErr.addErr(rowsMap.get(st_stockbillid).get(0).getString("billno"), "原单据会计期间已关闭，不可调整");
                        continue;
                    } else if (!periodrowsMap.get("0").toRowsMap("period").containsKey(period)) {
                        batchDeleteErr.addErr(rowsMap.get(st_stockbillid).get(0).getString("billno"), "原单据会计期间未生成，不可调整");
                        continue;
                    }
                }
            } else {
                batchDeleteErr.addErr(String.valueOf(st_stockbillid), "该单据不存在");
                continue;
            }
            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "更新", "单据日期【" + content.getString("billdate") + "】调整成功").getSQL());
            sqlList.add("update st_stockbill set billdate='" + content.getString("billdate") + "',period='" + period + "' where st_stockbillid=" + st_stockbillid);
        }
        dbConnect.runSqlUpdate(sqlList);
        HashMap<String, Object> result = new HashMap<>();
        String success = "共需" + st_stockbillids.size() + "张单据日期调整,其中失败" + batchDeleteErr.size() + "张！";
        result.put("success", success);
        result.put("errinfo", batchDeleteErr.getReturnObject());
        return getSucReturnObject().setData(result).toString();
    }


    @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,qty from st_stockbill_items where st_stockbillid=" + st_stockbillid);
        row.put("receivableamount", rowsdetail.sum("amount"));
        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=" + row.getLong("sys_enterpriseid"));
        Rows stockbillrows_hong = dbConnect.runSqlQuery("select billno from st_stockbill t1 where t1.rb=0 and t1.sourceobject='st_stockbill' and t1.sourceid=" + st_stockbillid);
        Rows expressformrows = dbConnect.runSqlQuery("select t1.* from expressform t1 inner join sa_expressform_stockbill t2 on t1.expressformid=t2.expressformid and t1.siteid=t2.siteid where t2.st_stockbillid ='"
                + st_stockbillid + "' and  t1.siteid='" + siteid + "' order by t1.createdate desc");
        if (accountbalancerows.isNotEmpty()) {
            row.put("discountamount_xjzh", accountbalancerows.get(0).getBigDecimal("discountamount"));
        } else {
            row.put("discountamount_xjzh", 0);
        }
        if (stockbillrows_hong.isNotEmpty()) {
            row.put("billno_hong", stockbillrows_hong.get(0).getString("billno"));
        } else {
            row.put("billno_hong", "");
        }
        row.put("qty", rowsdetail.sum("qty"));
        if (expressformrows.isEmpty()) {
            row.put("expressform",new Rows());
            row.put("expressform_mailno", "");
            row.put("expressform_txlogisticid", "");
        } else {
            row.put("expressform", expressformrows);
            row.put("expressform_mailno", expressformrows.get(0).getString("mailno"));
            row.put("expressform_txlogisticid", expressformrows.get(0).getString("txlogisticid"));
        }


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

    }

    @API(title = "查询出入库单列表", apiversion = R.ID20230719153903.v1.class)
    @CACHEING
    public String queryStockbillList() throws YosException {
        ArrayList<String> billtypes = new ArrayList<>();
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("type") && !"".equals(whereObject.getString("type"))) {
                JSONArray jsonArray = whereObject.getJSONArray("type");
                billtypes = (ArrayList<String>) jsonArray.toJavaList(String.class);
            }
            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 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("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 t1.billdate >=DATE_SUB(CURDATE(), INTERVAL 30 DAY)");
                } else if (whereObject.getString("dateinterval").equals("past15days")) {
                    where.append(" and t1.billdate >=DATE_SUB(CURDATE(), INTERVAL 15 DAY)");
                } else if (whereObject.getString("dateinterval").equals("today")) {
                    where.append(" and t1.billdate >=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("jfnocheck") && !"".equals(whereObject.getString("jfnocheck"))) {
                where.append(" and t1.status ='新建' and delivery='寄付'");
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "*").setTableAlias("t1");
        if (billtypes.contains("销售出库") || billtypes.contains("返修入库") || billtypes.contains("返修出库")) {
            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");
        }
        if (billtypes.contains("销售出库")) {
            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.addQueryFields("recname", "t4.name");
            querySQL.addQueryFields("recphonenumber", "t4.phonenumber");
            querySQL.addQueryFields("recaddress", "t4.address");
        }
        querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t6", "t1.departmentid=t6.departmentid and t1.siteid = t6.siteid",
                "depno", "depname");
        if (billtypes.contains("其他入库")) {
            querySQL.addJoinTable(JOINTYPE.left, "sa_supplier", "t7", "t1.sa_supplierid=t7.sa_supplierid and t1.siteid = t7.siteid",
                    "suppno", "suppname", "suppshortname");
        }
        querySQL.addJoinTable(JOINTYPE.left, "st_stock", "t8", "t1.stockid=t8.stockid and t1.siteid = t8.siteid",
                "stockname");
        if (billtypes.contains("其他入库") || billtypes.contains("其他出库")) {
            querySQL.addJoinTable(JOINTYPE.left, "st_dbstockbill", "t9", "t1.sourceid=t9.st_dbstockbillid and t1.siteid = t9.siteid and t1.sourceobject='st_dbstockbill'");
            querySQL.addQueryFields("dbbillno", "t9.billno");
        }
        if (billtypes.contains("返修出库")) {
            querySQL.addJoinTable(JOINTYPE.left, "sa_sendrepair", "t10", "t10.sourceid=t1.st_stockbillid and t10.siteid = t1.siteid and t10.sourcetable='st_stockbill'", "instockdate");
        }


        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t1.type", billtypes);
        querySQL.setWhere(where);
        querySQL.setCondition("t1.billno", "t1.outplace", "t3.agentnum", " t2.enterprisename", "t6.depname");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();


        ArrayList<Long> ids = rows.toArrayList("st_stockbillid", new ArrayList<>());
        HashMap<Long, Integer> reportMap = Report.getViewCount(this, "st_stockbill", ids);
        for (Row row : rows) {
            if (reportMap.containsKey(row.getLong("st_stockbillid"))) {
                row.put("printcount", reportMap.get(row.getLong("st_stockbillid")));
            }
        }
        if (billtypes.contains("返修出库")) {
            QuerySQL querySQLDatail = SQLFactory.createQuerySQL(this, "st_stockbill_items", "sku", "st_stockbillid").setTableAlias("t1");
            querySQLDatail.addJoinTable(JOINTYPE.left, "st_stockbill_items_sku", "t2", "t1.st_stockbill_itemsid=t2.st_stockbill_itemsid and t1.siteid = t2.siteid");
            querySQLDatail.addJoinTable(JOINTYPE.left, "plm_item", "t3", "t3.itemid=t1.itemid and t3.siteid = t1.siteid ", "itemno", "itemname", "spec", "model");
            querySQLDatail.setWhere("t1.st_stockbillid", ids);
            Rows rowsdatail = querySQLDatail.query();
            RowsMap rowsdatailMap = rowsdatail.toRowsMap("st_stockbillid");
            for (Row row : rows) {
                if (rowsdatailMap.containsKey(row.getString("st_stockbillid"))) {
                    row.put("itemskus", rowsdatailMap.get(row.getString("st_stockbillid")));
                }
            }
        }

        if (billtypes.contains("返修入库")) {
            //querySQL.addJoinTable(JOINTYPE.left, "(SELECT st_stockbillid,itemno,itemname,model,sku,siteid FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY st_stockbillid ORDER BY st_stockbill_itemsid ASC) AS szrowno FROM st_stockbill_items) t WHERE szrowno = 1)", "t11", "t1.st_stockbillid=t11.st_stockbillid and t1.siteid = t11.siteid","itemno","itemname","model","sku");
            QuerySQL querySQLDatail = SQLFactory.createQuerySQL(this, "st_stockbill_items", "itemname", "itemno", "model", "sku", "st_stockbillid").setTableAlias("t1");
            querySQLDatail.setWhere("t1.st_stockbillid", ids);
            Rows rowsdatail = querySQLDatail.query();
            RowsMap rowsdatailMap = rowsdatail.toRowsMap("st_stockbillid");
            for (Row row : rows) {
                if (rowsdatailMap.containsKey(row.getString("st_stockbillid"))) {
                    row.put("itemno", rowsdatailMap.get(row.getString("st_stockbillid")).get(0).getString("itemno"));
                    row.put("itemname", rowsdatailMap.get(row.getString("st_stockbillid")).get(0).getString("itemname"));
                    row.put("model", rowsdatailMap.get(row.getString("st_stockbillid")).get(0).getString("model"));
                    row.put("sku", rowsdatailMap.get(row.getString("st_stockbillid")).get(0).getString("sku"));
                } else {
                    row.put("itemno", "");
                    row.put("itemname", "");
                    row.put("model", "");
                    row.put("sku", "");
                }
            }

        }
        if (billtypes.contains("销售出库")) {
            //querySQL.addJoinTable(JOINTYPE.left, "(SELECT st_stockbillid,itemno,itemname,model,sku,siteid FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY st_stockbillid ORDER BY st_stockbill_itemsid ASC) AS szrowno FROM st_stockbill_items) t WHERE szrowno = 1)", "t11", "t1.st_stockbillid=t11.st_stockbillid and t1.siteid = t11.siteid","itemno","itemname","model","sku");
            QuerySQL expressformQuery = SQLFactory.createQuerySQL(this, "expressform", "*").setTableAlias("t1");
            expressformQuery.addJoinTable(JOINTYPE.inner, "sa_expressform_stockbill", "t2", "t2.expressformid = t1.expressformid");
            expressformQuery.addJoinTable(JOINTYPE.inner, "st_stockbill", "t3", "t3.st_stockbillid = t2.st_stockbillid", "tracknumber", "st_stockbillid");
            expressformQuery.setWhere("t3.st_stockbillid", ids);
            expressformQuery.setOrderBy("t1.createdate desc");
            expressformQuery.setDistinct(true);
            Rows expressformrows = expressformQuery.query();
            RowsMap expressformrowsMap = expressformrows.toRowsMap("st_stockbillid");
            for (Row row : rows) {
                if (expressformrowsMap.containsKey(row.getString("st_stockbillid"))) {
                    row.put("expressform_mailno", expressformrowsMap.get(row.getString("st_stockbillid")).get(0).getString("mailno"));
                    row.put("expressform_txlogisticid", expressformrowsMap.get(row.getString("st_stockbillid")).get(0).getString("txlogisticid"));
                } else {
                    row.put("expressform_mailno", "");
                    row.put("expressform_txlogisticid", "");

                }
            }

        }


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


    @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);
            dbConnect.runSqlUpdate(
                    "delete from st_stockbill_items where siteid='" + siteid + "' and st_stockbillid=" + st_stockbillid);
            dbConnect.runSqlUpdate(
                    "delete from st_stockbill_items_sku 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 {
        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "billno", "st_stockbillid");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.st_stockbillid", st_stockbillids);
        Rows stockbillrows = querySQL.query();
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, stockbillrows.size());
        for (Row row : stockbillrows) {
            long st_stockbillid = row.getLong("st_stockbillid");
            String billno = row.getString("billno");
            try {
                Stockbill.check(this, st_stockbillid, true);
            } catch (Exception e) {
                batchDeleteErr.addErr(billno, e.getMessage());
            }

        }
        HashMap<String, Object> result = new HashMap<>();
        String success = "共审核" + st_stockbillids.size() + "张出入库单,其中失败" + batchDeleteErr.size() + "张！";
        result.put("success", success);
        result.put("errinfo", batchDeleteErr.getReturnObject());
        return getSucReturnObject().setData(result).toString();
    }

    @API(title = "批量反审核(仅刷数据)", apiversion = R.ID2025121320093303.v1.class)
    public String uncheck_pl() throws YosException {
        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "billno", "st_stockbillid");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.st_stockbillid", st_stockbillids);
        Rows stockbillrows = querySQL.query();
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, stockbillrows.size());
        for (Row row : stockbillrows) {
            long st_stockbillid = row.getLong("st_stockbillid");
            String billno = row.getString("billno");
            try {
                Stockbill.check(this, st_stockbillid, false);
            } catch (Exception e) {
                batchDeleteErr.addErr(billno, e.getMessage());
            }

        }
        HashMap<String, Object> result = new HashMap<>();
        String success = "共审核" + st_stockbillids.size() + "张出入库单,其中失败" + batchDeleteErr.size() + "张！";
        result.put("success", success);
        result.put("errinfo", batchDeleteErr.getReturnObject());
        return getSucReturnObject().setData(result).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 {
        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        for (Object o : st_stockbillids) {
            long st_stockbillid = Long.parseLong(o.toString());
            Stockbill.check(this, st_stockbillid, false);
        }
        return getSucReturnObject().toString();
    }

    @API(title = "生成送修单", apiversion = R.ID2025070415073603.v1.class)
    @CACHEING_CLEAN(apiClass = {sendrepair.class, sendrepair_pj.class})
    public String sendrepair() throws YosException {
        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "st_stockbillid", "status", "type", "typemx", "remarks", "sys_enterpriseid", "isrepair");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.st_stockbillid", st_stockbillids.toJavaList(Long.class));
        Rows rows = querySQL.query();

        QuerySQL querySQLDetail = SQLFactory.createQuerySQL(this, "st_stockbill_items", "*");
        querySQLDetail.setTableAlias("t1");
        querySQLDetail.addJoinTable(JOINTYPE.left, "sa_aftersalesmag_items", "t2", "t1.sourceobject ='sa_aftersalesmag_items' and t1.sourceid=t2.sa_aftersalesmag_itemsid AND t1.siteid = t2.siteid", "name", "phonenumber", "address", "stockdate", "enddate", "remarks", "duty", "demand", "reason");
        querySQLDetail.addQueryFields("sku", "t2.machinecode");

        querySQLDetail.setSiteid(siteid);
        querySQLDetail.setWhere("t1.st_stockbillid", st_stockbillids.toJavaList(Long.class));

        RowsMap rowsMapDetail = querySQLDetail.query().toRowsMap("st_stockbillid");
        ArrayList<Long> sa_sendrepairids = new ArrayList<>();
        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : rows) {
            if (!row.getString("type").equals("返修入库")) {
                return getErrReturnObject().setErrMsg("非返修入库单无法生成送修单").toString();
            }
            if (row.getBoolean("isrepair")) {
                return getErrReturnObject().setErrMsg("已生成送修单,无法再次生成").toString();
            }
            if (!row.getString("type").equals("返修入库")) {
                return getErrReturnObject().setErrMsg("非返修入库单无法生成送修单").toString();
            }
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_sendrepair");
            long sa_sendrepairid = createTableID("sa_sendrepair");
            sa_sendrepairids.add(sa_sendrepairid);
            insertSQL.setUniqueid(sa_sendrepairid);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("billno", createBillCode("sendrepair"));
            insertSQL.setValue("remarks", row.getString("remarks"));
            insertSQL.setValue("sys_enterpriseid", row.getLong("sys_enterpriseid"));
            insertSQL.setValue("sourcetable", "st_stockbill");
            insertSQL.setValue("sourceid", row.getLong("st_stockbillid"));
            insertSQL.setValue("status", "新建");
            insertSQL.setValue("createby", username);
            insertSQL.setDateValue("createdate");
            insertSQL.setDateValue("billdate");
            sqlList.add(insertSQL.getSQL());
            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", row.getLong("st_stockbillid"), "转送修单", "转送修单成功").getSQL());
            ArrayList<Long> ids = rowsMapDetail.get(row.getString("st_stockbillid")).toArrayList("itemid", new ArrayList<>());
            HashMap<Long, ItemPrice> itemPriceRowsMap = ItemPrice.getItemPrice(this, sys_enterpriseid, ids);
            for (Row rowdetail : rowsMapDetail.get(row.getString("st_stockbillid"))) {
                insertSQL = SQLFactory.createInsertSQL(this, "sa_sendrepair_detail");
                long sa_sendrepair_detailid = createTableID("sa_sendrepair_detail");
                insertSQL.setUniqueid(sa_sendrepair_detailid);
                insertSQL.setSiteid(siteid);
                insertSQL.setValue("sa_sendrepairid", sa_sendrepairid);
                insertSQL.setValue("name", rowdetail.getString("name"));
                insertSQL.setValue("phonenumber", rowdetail.getString("phonenumber"));
                insertSQL.setValue("address", rowdetail.getString("address"));
                insertSQL.setValue("stockdate", StringUtils.isBlank(rowdetail.getString("stockdate")) ? "null" : rowdetail.getString("stockdate"));
                insertSQL.setValue("enddate", StringUtils.isBlank(rowdetail.getString("enddate")) ? "null" : rowdetail.getString("enddate"));
                insertSQL.setValue("remarks", rowdetail.getString("remarks"));
                insertSQL.setValue("duty", rowdetail.getString("duty"));
                insertSQL.setValue("demand", rowdetail.getString("demand"));
                insertSQL.setValue("reason", rowdetail.getString("reason"));
                insertSQL.setValue("qty", rowdetail.getBigDecimal("qty"));
                insertSQL.setValue("sku", rowdetail.getString("sku"));
                insertSQL.setValue("itemid", rowdetail.getString("itemid"));
                if (itemPriceRowsMap.containsKey(rowdetail.getLong("itemid"))) {
                    insertSQL.setValue("price", itemPriceRowsMap.get(rowdetail.getLong("itemid")).getPrice());
                } else {
                    insertSQL.setValue("price", 0);
                }


                sqlList.add(insertSQL.getSQL());
                sqlList.add("update st_stockbill_items set isrepair=1 where st_stockbill_itemsid=" + rowdetail.getLong("st_stockbill_itemsid"));

            }
            sqlList.add("update st_stockbill set isrepair=1 where st_stockbillid=" + row.getLong("st_stockbillid"));
        }
        dbConnect.runSqlUpdate(sqlList);

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


//    public String xsckcheck(Long st_stockbillid, boolean isinstock) 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");
//
//        sqlList.addAll(updateAccountbalance_freez(st_stockbillid, isinstock));
//
//        dbConnect.runSqlUpdate(sqlList);
////        Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
//        return "success";
//
//    }

//    public String unxsckcheck(Long st_stockbillid, boolean isinstock) 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");
//        sqlList.addAll(updateAccountbalance_freez(st_stockbillid, isinstock));
//        dbConnect.runSqlUpdate(sqlList);
//

    /// /        Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
//        return "success";
//    }
    @API(title = "仓库确认", apiversion = R.ID2025050709505703.v1.class)
    public String confirm() throws YosException {
        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");
        boolean isconfirm = content.getBooleanValue("isconfirm");
        if (st_stockbillids.isEmpty()) {
            return getErrReturnObject().setErrMsg("请选择需要操作的出入库单")
                    .toString();
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "*");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("st_stockbillid", st_stockbillids);
        Rows rows = querySQL.query();

        ArrayList<String> sqllist = new ArrayList<>();
        for (Row row : rows) {
            if (!row.getString("status").equals("审核")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的出入库单为非审核状态，无法进行仓库确认")
                        .toString();
            }
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "st_stockbill");
            updateSQL.setUniqueid(row.getLong("st_stockbillid"));
            updateSQL.setSiteid(siteid);
            updateSQL.setValue("isconfirm", isconfirm);
            updateSQL.setValue("confirmby", username);
            updateSQL.setDateValue("confirmdate");

            updateSQL.setValue("changeby", username);
            updateSQL.setDateValue("changedate");
            sqllist.add(updateSQL.getSQL());
            if (isconfirm) {
                sqllist.add(DataContrlLog.createLog(this, "st_stockbill", row.getLong("st_stockbillid"), "仓库确认", row.getString("type") + "单仓库确认成功").getSQL());
            } else {
                sqllist.add(DataContrlLog.createLog(this, "st_stockbill", row.getLong("st_stockbillid"), "仓库反确认", row.getString("type") + "单仓库反确认成功").getSQL());
            }

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

    @API(title = "红冲", apiversion = R.ID2025072409344603.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class})
    public String hongc() throws YosException {
        long st_stockbillid = content.getLong("st_stockbillid");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = dbConnect.runSqlQuery("select t1.*,ifnull(t2.sa_sendrepairid,0) sa_sendrepairid from st_stockbill t1 left join sa_sendrepair t2 on t1.st_stockbillid=t2.sourceid and t1.siteid=t2.siteid and t2.sourcetable='st_stockbill'  where t1.st_stockbillid ='"
                + st_stockbillid + "' and  t1.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 + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该出入库单不存在")
                    .toString();
        }
        if (!rows.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("非审核的出入库单无法红冲")
                    .toString();
        }
        if (rows.get(0).getInteger("rb") == 0) {
            return getErrReturnObject().setErrMsg("只能红冲蓝字出入库单")
                    .toString();
        }
        if (rows.get(0).getInteger("sa_sendrepairid") != 0) {
            return getErrReturnObject().setErrMsg("来源于送修单的出库单不能红冲")
                    .toString();
        }

        st_stockbillid = createTableID("st_stockbill");
        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill");
        insertSQL.setUniqueid(st_stockbillid);
        insertSQL.setSiteid(siteid);
        insertSQL.setValue("sys_enterpriseid", rows.get(0).getLong("sys_enterpriseid"));
        insertSQL.setValue("rec_contactsid", rows.get(0).getLong("rec_contactsid"));
        insertSQL.setValue("departmentid", rows.get(0).getLong("departmentid"));
        insertSQL.setValue("stockid", rows.get(0).getLong("stockid"));
        insertSQL.setValue("type", rows.get(0).getString("type"));
        insertSQL.setValue("typemx", rows.get(0).getString("typemx"));
        insertSQL.setValue("sa_supplierid", rows.get(0).getLong("sa_supplierid"));
        insertSQL.setValue("rb", 0);
        insertSQL.setValue("remarks", rows.get(0).getString("remarks"));
        insertSQL.setValue("billdate", getDateTime_Str());
        String billno = "";
        if (rows.get(0).getString("type").equals("销售出库")) {
            billno = createBillCode("stockbill");
            insertSQL.setValue("billno", billno);
            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", rows.get(0).getLong("st_stockbillid"), "红冲", "已生成红字" + rows.get(0).getString("type") + "单【" + billno + "】").getSQL());
        } else if (rows.get(0).getString("type").equals("其他出库")) {
            billno = createBillCode("STOCKBILL1");
            insertSQL.setValue("billno", billno);
            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", rows.get(0).getLong("st_stockbillid"), "红冲", "已生成红字" + rows.get(0).getString("type") + "单【" + billno + "】").getSQL());
        } else if (rows.get(0).getString("type").equals("其他入库")) {
            billno = createBillCode("STOCKBILL2");
            insertSQL.setValue("billno", billno);
            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", rows.get(0).getLong("st_stockbillid"), "红冲", "已生成红字" + rows.get(0).getString("type") + "单【" + billno + "】").getSQL());
        } else {
            billno = createBillCode("stockbill");
            insertSQL.setValue("billno", billno);
            sqlList.add(DataContrlLog.createLog(this, "st_stockbill", rows.get(0).getLong("st_stockbillid"), "红冲", "已生成红字" + rows.get(0).getString("type") + "单【" + billno + "】").getSQL());
        }

        insertSQL.setValue("outplace", rows.get(0).getString("outplace"));
        insertSQL.setValue("delivery", rows.get(0).getString("delivery"));
        insertSQL.setValue("invoice_enterprisename", rows.get(0).getString("invoice_enterprisename"));
        insertSQL.setValue("invoice_address", rows.get(0).getString("invoice_address"));
        insertSQL.setValue("invoice_taxno", rows.get(0).getString("invoice_taxno"));
        insertSQL.setValue("tracknumber", rows.get(0).getString("tracknumber"));
        insertSQL.setValue("isconfirm", rows.get(0).getString("isconfirm"));

        insertSQL.setValue("sourceobject", "st_stockbill");
        insertSQL.setValue("sourceid", rows.get(0).getLong("st_stockbillid"));

        insertSQL.setValue("period", rows.get(0).getString("period"));

        insertSQL.setValue("createby", username);
        insertSQL.setDateValue("createdate");
        insertSQL.setValue("changeby", username);
        insertSQL.setDateValue("changedate");
        insertSQL.setValue("status", "新建");
        insertSQL.setValue("payamount", rows.get(0).getString("payamount"));
        insertSQL.setValue("paydiscountamount", rows.get(0).getString("paydiscountamount"));
        sqlList.add(insertSQL.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "新建", "由" + rows.get(0).getString("billno") + "单红冲生成").getSQL());

        int i = 0;
        for (Row row : rowsdetail) {
            InsertSQL insertDetailSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items");
            insertDetailSQL.setUniqueid(createTableID("st_stockbill_items"));
            insertDetailSQL.setSiteid(siteid);
            insertDetailSQL.setValue("rowno", i + 1);
            insertDetailSQL.setValue("st_stockbillid", st_stockbillid);
            insertDetailSQL.setValue("sa_orderitemsid", row.getLong("sa_orderitemsid"));
            insertDetailSQL.setValue("itemid", row.getString("itemid"));
            insertDetailSQL.setValue("sa_orderid", row.getString("sa_orderid"));
            insertDetailSQL.setValue("qty", row.getString("qty"));
            insertDetailSQL.setValue("discountrate", row.getBigDecimal("discountrate"));
            insertDetailSQL.setValue("sa_dispatch_itemsid", 0);
            insertDetailSQL.setValue("defaultprice", row.getBigDecimal("defaultprice"));
            insertDetailSQL.setValue("remarks", row.getString("remarks"));
            insertDetailSQL.setValue("sku", row.getString("sku"));
            insertDetailSQL.setValue("itemno", row.getString("itemno"));
            insertDetailSQL.setValue("itemname", row.getString("itemname"));
            insertDetailSQL.setValue("model", row.getString("model"));
            insertDetailSQL.setValue("price", row.getBigDecimal("price"));
            insertDetailSQL.setValue("stockid", row.getString("stockid"));
            insertDetailSQL.setValue("amount", row.getBigDecimal("qty").multiply(row.getBigDecimal("price")));
            insertDetailSQL.setValue("untaxedprice", row.getBigDecimal("price").divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP));
            insertDetailSQL.setValue("untaxedamount", row.getBigDecimal("qty").multiply(row.getBigDecimal("price").divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP)));

            insertDetailSQL.setValue("sourceid", row.getLong("st_stockbill_itemsid"));
            insertDetailSQL.setValue("sourceobject", "st_stockbill_items");
            sqlList.add(insertDetailSQL.getSQL());
        }

        sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "新增", "销售出库单新增成功").getSQL());

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


        return queryStockbillMain();
    }

    @API(title = "序列号收集", apiversion = R.ID2025101613261703.v1.class, intervaltime = 2000)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class, stockbillitemssku.class})
    public String skuCollect() throws YosException {
        long stockid = content.getLong("stockid");
        String sku = content.getString("sku");
        long st_stockbillid = content.getLong("st_stockbillid");
        long st_stockbill_itemsid = content.getLong("st_stockbill_itemsid");
        String ss = "&&%$&&";
        if (sku.contains(",")) {
            ss = ",";
        } else if (sku.contains("，")) {
            ss = "，";
        } else if (sku.contains(" ")) {
            ss = " ";
        } else if (sku.contains(";")) {
            ss = ";";
        } else if (sku.contains("；")) {
            ss = "；";
        }
        String[] CODE = getDistinctArray(sku.split(ss));

        Rows rows = dbConnect.runSqlQuery("select t1.*,t2.stockno from st_stockbill t1 left join st_stock t2 on t1.stockid=t2.stockid where t1.st_stockbillid ='"
                + st_stockbillid + "' and  t1.siteid='" + siteid + "'");

        QuerySQL skuQuerySQL = SQLFactory.createQuerySQL(this, "sa_itemsku", "*");
        skuQuerySQL.setTableAlias("t1");
        skuQuerySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid=t2.itemid", "itemno", "itemname", "model");
        skuQuerySQL.addJoinTable(JOINTYPE.left, "st_stock", "t3", "t1.stockid=t3.stockid", "stockno");
        skuQuerySQL.setWhere("t1.siteid", siteid);
        skuQuerySQL.setWhere("t1.sku", CODE);
        Rows skuRows = skuQuerySQL.query();
        RowsMap skuRowsMap = skuRows.toRowsMap("sku");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该出入库单不存在")
                    .toString();
        }
        Row st_stockbill = rows.get(0);
        for (String FMACHINECODE : CODE) {
            Rows stockbillitemsrows = dbConnect.runSqlQuery("select t1.itemid,t1.qty,t2.skucontrol,t1.st_stockbill_itemsid from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid where t1.st_stockbillid ='"
                    + st_stockbillid + "' and  t1.siteid='" + siteid + "'");
            Rows stockbillitemskurows = dbConnect.runSqlQuery("select t1.*,t2.itemid from st_stockbill_items_sku t1 left join sa_itemsku t2 on t1.sku=t2.sku where t1.st_stockbillid ='"
                    + st_stockbillid + "' and  t1.siteid='" + siteid + "'");

            ArrayList<String> sqllist = new ArrayList<>();
            if (FMACHINECODE.equals("")) {
                continue;
            }
            if (st_stockbill.getString("type").contains("入库")
                    && FMACHINECODE.startsWith("66")) {
                return getErrReturnObject().setErrMsg("序列号" + FMACHINECODE
                        + "不可在DRP手工入库").toString();
            }
            if (st_stockbill.getString("status").equals("审核") && stockbillitemskurows.size() == stockbillitemsrows.toRowsMap("skucontrol").get("1").sum("qty").intValue()) {
                return getErrReturnObject().setErrMsg("序列号数量已经满足，不可继续添加").toString();
            }
            if (stockbillitemskurows.toRowsMap("sku").containsKey(FMACHINECODE)) {
                return getErrReturnObject().setErrMsg("序列号" + FMACHINECODE + "重复").toString();
            }
            if (!skuRowsMap.containsKey(FMACHINECODE)) {
                return getErrReturnObject().setErrMsg("序列号" + FMACHINECODE + "不存在").toString();
            } else {
                if (skuRowsMap.get(FMACHINECODE).get(0).getString("status").equals("报废")) {
                    return getErrReturnObject().setErrMsg("该序列号已作废").toString();
                }
                long skustockid = skuRowsMap.get(FMACHINECODE).get(0).getLong(
                        "stockid");
                long skuitemid = skuRowsMap.get(FMACHINECODE).get(0).getLong("itemid");
                String skuitemno = skuRowsMap.get(FMACHINECODE).get(0).getString("itemno");
                if (st_stockbill.getString("status").equals("审核")) {
                    Rows stockbillskucheckRows = dbConnect.runSqlQuery("select * from stockbillskucheck where itemid=" + skuitemid + " and st_stockbillid=" + st_stockbillid);
                    if (!stockbillskucheckRows.isEmpty()) {
                        return getErrReturnObject().setErrMsg("商品编号" + skuitemno
                                + stockbillskucheckRows.get(0)
                                .getString("msg")).toString();
                    }
                }

                if (skustockid != 0 && isinstock(st_stockbill.getString("type"), st_stockbill.getString("rb"), true)) {
                    return getErrReturnObject().setErrMsg("该序列号不可进行入库操作").toString();
                }
                if (skustockid == 0 && !isinstock(st_stockbill.getString("type"), st_stockbill.getString("rb"), true)) {
                    return getErrReturnObject().setErrMsg("该序列号不可进行出库操作").toString();
                }
                if (skustockid == 0 && stockid == 0) {
                    return getErrReturnObject().setErrMsg("请指定仓库").toString();
                }
                if (st_stockbill.getString("status").equals("审核") && !stockbillitemsrows.toRowsMap("skucontrol").get("1").toRowsMap("itemid").containsKey(
                        skuRowsMap.get(FMACHINECODE).get(0).getString("itemid"))) {
                    return getErrReturnObject().setErrMsg("序列号所属的商品编号没有包含在单据表体中").toString();
                }
                if (st_stockbill.getString("type").equals("生产入库")
                        && skuRowsMap.get("FMACHINECODE").get(0).getString("mono")
                        .equals("")) {
                    return getErrReturnObject().setErrMsg("序列号非生产订单生成，不可添加").toString();
                }

//                if (st_stockbill.getString("type").equals("其他入库")
//                        && st_stockbill.getString("stockno").equals("101")
//                        && !getPaoSet(
//                        "MACHINECODEUSEVIEW",
//                        "MACHINECODEUSEVIEW",
//                        "fmachinecode='" + FMACHINECODE
//                                + "' and ftype like'%出库%'")
//                        .isEmpty()) {
//                    ((PaoSet) getThisPaoSet())
//                            .addWarning(new P2AppException("", "序列号"
//                                    + FMACHINECODE + "已存在出库记录，请确认该序列号是否有误"));
//                }
                String stockno = st_stockbill.getString("type").equals("销售出库")
                        && (skuRowsMap.get(FMACHINECODE).get(0).getString("stockno")
                        .equals("103") || skuRowsMap.get(FMACHINECODE).get(0).getString("stockno").equals("109")) ? "101"
                        : skuRowsMap.get(FMACHINECODE).get(0).getString("stockno");

                Rows stockrows = dbConnect.runSqlQuery("select *from st_stock where stockno='" + stockno + "'");


                if (st_stockbill_itemsid > 0 && dbConnect.runSqlQuery("select 1 from st_stockbill_items_sku where st_stockbillid=st_stockbill_itemsid and st_stockbillid=" + st_stockbillid).size() > 0) {
                    scanqtyrefresh();
                    stockbillitemsrows = dbConnect.runSqlQuery("select t1.itemid,t1.qty,t2.skucontrol,t1.st_stockbill_itemsid from st_stockbill_items t1 left join plm_item t2 on t1.itemid=t2.itemid where t1.st_stockbillid ='"
                            + st_stockbillid + "' and  t1.siteid='" + siteid + "'");
                    stockbillitemskurows = dbConnect.runSqlQuery("select t1.*,t2.itemid from st_stockbill_items_sku t1 left join sa_itemsku t2 on t1.sku=t2.sku where t1.st_stockbillid ='"
                            + st_stockbillid + "' and  t1.siteid='" + siteid + "'");
                }


                if (stockrows.isNotEmpty()) {
                    skustockid = stockrows.get(0).getLong("stockid");
                }
                if (stockid == 0) {
                    skustockid = st_stockbill.getLong("stockid");
                } else {
                    skustockid = stockid;
                }
                InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items_sku");
                long st_stockbill_items_skuid = createTableID("st_stockbill_items_sku");
                insertSQL.setUniqueid(st_stockbill_items_skuid);
                insertSQL.setSiteid(siteid);
                insertSQL.setValue("stockid", skustockid);
                insertSQL.setValue("itemid", skuitemid);
                insertSQL.setValue("sku", FMACHINECODE);


                if (!st_stockbill.getString("status").equals("审核")) {
                    if (st_stockbill_itemsid == 0) {
                        if (!stockbillitemsrows.toRowsMap("itemid").containsKey(skuitemid)) {
                            //新建表体
                            InsertSQL insertdetailSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items");
                            long st_stockbill_itemsidnew = createTableID("st_stockbill_items");
                            insertdetailSQL.setUniqueid(st_stockbill_itemsidnew);
                            insertdetailSQL.setSiteid(siteid);
                            insertdetailSQL.setValue("rowno", 1);
                            insertdetailSQL.setValue("sa_dispatch_itemsid", 0);
                            insertdetailSQL.setValue("stockid", skustockid);
                            insertdetailSQL.setValue("itemid", skuitemid);
                            insertdetailSQL.setValue("itemno", skuRowsMap.get(FMACHINECODE).get(0).getString("itemno"));
                            insertdetailSQL.setValue("itemname", skuRowsMap.get(FMACHINECODE).get(0).getString("itemname"));
                            insertdetailSQL.setValue("model", skuRowsMap.get(FMACHINECODE).get(0).getString("model"));
                            insertdetailSQL.setValue("qty", 1);
                            insertdetailSQL.setValue("sku", FMACHINECODE);
                            insertdetailSQL.setValue("skucontrol", 1);
                            insertdetailSQL.setValue("st_stockbillid", st_stockbillid);
                            sqllist.add(insertdetailSQL.getSQL());
                            insertSQL.setValue("st_stockbillid", st_stockbillid);
                            insertSQL.setValue("st_stockbill_itemsid", st_stockbill_itemsidnew);
                        } else {

                            if (stockbillitemsrows.toRowsMap("itemid").get(skuitemid).sum("qty").compareTo(BigDecimal.valueOf(stockbillitemskurows.toRowsMap("itemid").get(skuitemid).size())) <= 0) {
                                BigDecimal newqty = BigDecimal.valueOf(stockbillitemskurows.toRowsMap("itemid").get(skuitemid).size()).subtract(stockbillitemsrows.toRowsMap("itemid").get(skuitemid).sum("qty")).add(BigDecimal.ONE);
                                sqllist.add("update st_stockbill_items set qty=qty+" + newqty + " where st_stockbill_itemsid =" + stockbillitemsrows.toRowsMap("itemid").get(skuitemid).get(0).getLong("st_stockbill_itemsid"));
                            }
                            insertSQL.setValue("st_stockbillid", st_stockbillid);
                            insertSQL.setValue("st_stockbill_itemsid", stockbillitemsrows.toRowsMap("itemid").get(skuitemid).get(0).getLong("st_stockbill_itemsid"));
                        }
                    } else {
                        if (stockbillitemsrows.toRowsMap("st_stockbill_itemsid").get("st_stockbill_itemsid").sum("qty").compareTo(stockbillitemskurows.toRowsMap("st_stockbill_itemsid").get(st_stockbill_itemsid).sum("qty")) <= 0) {
                            int newqty = stockbillitemskurows.toRowsMap("st_stockbill_itemsid").get(st_stockbill_itemsid).size() + 1;
                            sqllist.add("update st_stockbill_items set qty=" + newqty + " where st_stockbill_itemsid =" + st_stockbill_itemsid);
                        }
                        insertSQL.setValue("st_stockbillid", st_stockbillid);
                        insertSQL.setValue("st_stockbill_itemsid", st_stockbill_itemsid);
                    }
                } else {
                    if (st_stockbill_itemsid == 0) {
                        if (stockbillitemsrows.toRowsMap("itemid").get(skuitemid).sum("qty").compareTo(stockbillitemskurows.toRowsMap("itemid").get(skuitemid).sum("qty")) == 0) {
                            return getErrReturnObject().setErrMsg("序列号数量已经满足，不可继续添加").toString();
                        } else {
                            insertSQL.setValue("st_stockbillid", st_stockbillid);
                            insertSQL.setValue("st_stockbill_itemsid", st_stockbillid);
                        }
                    } else {
                        if (stockbillitemsrows.toRowsMap("st_stockbill_itemsid").get(st_stockbill_itemsid).sum("qty").compareTo(stockbillitemskurows.toRowsMap("st_stockbill_itemsid").get(st_stockbill_itemsid).sum("qty")) == 0) {
                            return getErrReturnObject().setErrMsg("序列号数量已经满足，不可继续添加").toString();
                        } else {
                            insertSQL.setValue("st_stockbillid", st_stockbillid);
                            insertSQL.setValue("st_stockbill_itemsid", st_stockbill_itemsid);
                        }
                    }

                }
                sqllist.add(insertSQL.getSQL());
                if (st_stockbill.getString("status").equals("审核")) {
                    if (isinstock(st_stockbill.getString("type"), st_stockbill.getString("rb"), true)) {
                        sqllist.add("update sa_itemsku set stockid=" + skustockid + " where sku='" + FMACHINECODE + "'");
                        if (st_stockbill.getString("type").equals("销售出库")) {
                            sqllist.add("update sa_itemsku set sa_agentsid=0 where sku='" + FMACHINECODE + "'");
                        }
                    } else {
                        sqllist.add("update sa_itemsku set stockid=0 where sku='" + FMACHINECODE + "'");
                        if (st_stockbill.getString("type").equals("销售出库")) {
                            sqllist.add("update sa_itemsku set sa_agentsid=(select sa_agentsid from sa_agents where sys_enterpriseid=" + st_stockbill.getLong("sys_enterpriseid") + ") where sku='" + FMACHINECODE + "'");
                            if (st_stockbill.getBoolean("iswx")) {
                                sqllist.add("update sa_itemsku set stockid=(select stockid from st_stock where stockno='00') where sku='" + FMACHINECODE + "'");
                            }
                        }
                    }
                }
            }
            dbConnect.runSqlUpdate(sqllist);
        }
        return getSucReturnObject().toString();
    }

    @API(title = "扫描数量刷新", apiversion = R.ID2025102215584903.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class, stockbillitemssku.class})
    public String scanqtyrefresh() throws YosException {
        long st_stockbillid = content.getLong("st_stockbillid");
        ArrayList<String> sqllist = new ArrayList<>();
        Rows stockbillskuRows = dbConnect.runSqlQuery("select t1.sku,t2.itemid,t1.st_stockbill_items_skuid from  st_stockbill_items_sku t1 left join sa_itemsku t2 on t1.sku=t2.sku  where t1.st_stockbillid=t1.st_stockbill_itemsid and t1.st_stockbillid=" + st_stockbillid);
        Rows stockbilldetailRows = dbConnect.runSqlQuery("select t1.itemid,t1.st_stockbill_itemsid,(t1.qty-ifnull(t2.smqty,0)) qty from st_stockbill_items t1 left join (select st_stockbill_itemsid,count(1) smqty from st_stockbill_items_sku where st_stockbillid='" + st_stockbillid + "' and st_stockbillid!=st_stockbill_itemsid group by st_stockbill_itemsid) t2 on t1.st_stockbill_itemsid=t2.st_stockbill_itemsid where t1.st_stockbillid=" + st_stockbillid + " and skucontrol=1");
        RowsMap stockbilldetailRowsMap = stockbilldetailRows.toRowsMap("itemid");
        HashMap<String, Integer> itemqtymap = new HashMap<>();
        for (Row row : stockbillskuRows) {
            String itemid = row.getString("itemid");
            if (itemqtymap.containsKey(itemid)) {
                itemqtymap.put(itemid, itemqtymap.get(itemid) + 1);
            } else {
                itemqtymap.put(itemid, 1);
            }
            long st_stockbill_items_skuid = row.getLong("st_stockbill_items_skuid");
            if (stockbilldetailRowsMap.containsKey(itemid)) {
                for (Row rowdetail : stockbilldetailRowsMap.get(itemid)) {
                    if (rowdetail.getInteger("qty") >= itemqtymap.get(itemid)) {
                        sqllist.add("update st_stockbill_items_sku set st_stockbill_itemsid=" + rowdetail.getLong("st_stockbill_itemsid") + " where st_stockbill_items_skuid=" + st_stockbill_items_skuid);
                        break;
                    }
                }
            }
        }

        dbConnect.runSqlUpdate(sqllist);

        return getSucReturnObject().toString();
    }

    @API(title = "批量建快递信息单", apiversion = R.ID2025110810253603.v1.class)
    public String insertexpressforms() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名
        String tableName = "expressform";
        long expresssdrid = content.getLong("expresssdrid");
        String province = content.getStringValue("province");
        String city = content.getStringValue("city");
        String county = content.getStringValue("county");
        String phonenumber = content.getStringValue("phonenumber");
        String address = content.getStringValue("address");
        BigDecimal agencyfund = content.getBigDecimal("agencyfund");
        String ordertype = content.getStringValue("ordertype");

        JSONArray st_stockbillids = content.getJSONArray("st_stockbillids");

        QuerySQL stockbillQuerySQL = SQLFactory.createQuerySQL(this, "st_stockbill", "*");
        stockbillQuerySQL.setTableAlias("t1");
        stockbillQuerySQL.addQueryFields("rev_name", "t1.name");
        stockbillQuerySQL.addQueryFields("rcv_phone", "t1.phonenumber");
        stockbillQuerySQL.addQueryFields("rcv_mobile", "t1.phonenumber");
        stockbillQuerySQL.addQueryFields("rcv_address", "t1.address");

        stockbillQuerySQL.setWhere("t1.siteid", siteid);
        stockbillQuerySQL.setWhere("t1.st_stockbillid", st_stockbillids);
        stockbillQuerySQL.setWhere("t1.type='销售出库'");
        Rows stockbillRows = stockbillQuerySQL.query();

        QuerySQL orderQuerySQL = SQLFactory.createQuerySQL(this, "sa_order", "province", "city", "county");
        orderQuerySQL.setTableAlias("t1");
        orderQuerySQL.addJoinTable(JOINTYPE.inner, "sa_orderitems", "t2", "t2.sa_orderid=t1.sa_orderid");
        orderQuerySQL.addJoinTable(JOINTYPE.inner, "st_stockbill_items", "t3", "t3.sa_orderitemsid=t2.sa_orderitemsid", "st_stockbillid");
        orderQuerySQL.setWhere("t3.st_stockbillid", st_stockbillids);
        Rows orderRows = orderQuerySQL.query();
        RowsMap orderrowsMap = orderRows.toRowsMap("st_stockbillid");
        for (Row row : stockbillRows) {
            long expressformid = createTableID(tableName);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, tableName);
            insertSQL.setUniqueid(expressformid);
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("logisticproviderid", "YTO");
            insertSQL.setValue("ordertype", ordertype);
            insertSQL.setValue("agencyfund", agencyfund);
            insertSQL.setValue("itemname", "配件");
            insertSQL.setValue("number", 1);
            insertSQL.setValue("fpriority_ads", "配件>常规");
            insertSQL.setValue("sys_enterpriseid", row.getLong("sys_enterpriseid"));
            insertSQL.setValue("rcv_name", row.getString("rev_name"));
            insertSQL.setValue("rcv_phone", row.getString("rcv_phone"));
            insertSQL.setValue("rcv_mobile", row.getString("rcv_mobile"));
            insertSQL.setValue("rcv_postcode", "");
            insertSQL.setValue("rcv_prov", orderrowsMap.containsKey(row.getString("st_stockbillid")) ? orderrowsMap.get(row.getString("st_stockbillid")).get(0).getString("province") : province);
            insertSQL.setValue("rcv_city", orderrowsMap.containsKey(row.getString("st_stockbillid")) ? orderrowsMap.get(row.getString("st_stockbillid")).get(0).getString("city") : city);
            insertSQL.setValue("rcv_county", orderrowsMap.containsKey(row.getString("st_stockbillid")) ? orderrowsMap.get(row.getString("st_stockbillid")).get(0).getString("county") : city);
            insertSQL.setValue("rcv_address", row.getString("rcv_address"));
            insertSQL.setValue("expresssdrid", expresssdrid);
            insertSQL.setValue("remarks", "");
            insertSQL.setValue("mailno", "");
            insertSQL.setValue("province", province);
            insertSQL.setValue("city", city);
            insertSQL.setValue("county", county);
            insertSQL.setValue("phonenumber", phonenumber);
            insertSQL.setValue("address", address);

            insertSQL.setValue("txlogisticid", createBillCode("logisticsbill"));


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

            sqlList.add(DataContrlLog.createLog(this, "expressform", expressformid, "新增", "快递信息新增成功").getSQL());

            InsertSQL insertBindSQL = SQLFactory.createInsertSQL(this, "sa_expressform_stockbill");
            insertBindSQL.setUniqueid(createTableID("sa_expressform_stockbill"));
            insertBindSQL.setSiteid(siteid);
            insertBindSQL.setValue("st_stockbillid", row.getLong("st_stockbillid"));
            insertBindSQL.setValue("expressformid", expressformid);
            sqlList.add(insertBindSQL.getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);

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

    @API(title = "下载销售出库单导入单模板", apiversion = R.ID2025111309451103.v1.class)
    public String downloadstockbillExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForStockbill");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        CellStyle style = xssfFWorkbook.createCellStyle();
        // 设置为文本格式，防止身份证号变成科学计数法
        DataFormat format = xssfFWorkbook.createDataFormat();
        // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        style.setDataFormat(format.getFormat("@"));
        // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        for (int i = 0; i <= 13; i++) {
            sheet.setDefaultColumnStyle(i, style);
        }
        ExportExcelSpecial.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcelSpecial.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcelSpecial.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcelSpecial.createTitleCellStyle3(xssfFWorkbook);
        XSSFCellStyle titleCellStyle4 = ExportExcelSpecial.createBodyCellStyle4(xssfFWorkbook);
        ExportExcelSpecial.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4,
                xssfFWorkbook);// 写入标题

        Rows aa = saveToExcelAttachment(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();
    }

    @API(title = "导入销售出库单", apiversion = R.ID2025111309451703.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class, stockbillitemssku.class})
    public String uploadstockbillByExcel() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        ExcelFactory e;
        try {

            if (content.getLongValue("attachmentid") > 0) {
                e = getExcelAttachment(content.getLongValue("attachmentid"));
                // 本地
                //e = getPostExcelFactory();
            } else {
                e = getPostExcel();
                // 本地
                //e = getPostExcelFactory();
            }

            ArrayList<String> keys = new ArrayList<>();
            keys.add("type");
            keys.add("depno");
            keys.add("agentnum");
            keys.add("remarks");
            keys.add("payamount");
            keys.add("paydiscountamount");
            keys.add("outplace");
            keys.add("rb");
            keys.add("itemno");
            keys.add("stockno");
            keys.add("qty");
            keys.add("defaultprice");
            keys.add("discountrate");


            Rows rows = e.getSheetRows(0, keys, 3);
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();

            RowsMap stockRowsMap = SQLFactory.createQuerySQL(this, "st_stock", "stockid", "stockno").query().toRowsMap("stockno");

            RowsMap depRowsMap = SQLFactory.createQuerySQL(this, "sys_department", "departmentid", "depno").query().toRowsMap("depno");

            QuerySQL agentquerySQL = SQLFactory.createQuerySQL(this, "sa_agents", "agentnum", "sys_enterpriseid");
            agentquerySQL.setTableAlias("t1");
            agentquerySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t2", "t1.sys_enterpriseid=t2.sys_enterpriseid", "contactsid", "name", "phonenumber");
            agentquerySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_finance", "t3", "t1.sys_enterpriseid=t3.sys_enterpriseid", "enterprisename", "taxno", "address");
            agentquerySQL.setSiteid(siteid);
            agentquerySQL.setWhere("t1.agentnum", rows.toArrayList("agentnum"));
            Rows agentsRows = agentquerySQL.query();
            RowsMap agentsRowsMap = agentsRows.toRowsMap("agentnum");


            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemno", "itemname", "model", "skucontrol");
            querySQL.setTableAlias("t1");
            querySQL.setSiteid(siteid);
            querySQL.setWhere("t1.itemno", rows.toArrayList("itemno"));
            Rows itemrows = querySQL.query();
            RowsMap itemRowsMap = itemrows.toRowsMap("itemno");
            for (Row row : rows) {
                if (!(StringUtils.isBlank(row.getString("type"))
                        && StringUtils.isBlank(row.getString("depno"))
                        && StringUtils.isBlank(row.getString("agentnum"))
                        && StringUtils.isBlank(row.getString("remarks"))
                        && StringUtils.isBlank(row.getString("payamount"))
                        && StringUtils.isBlank(row.getString("paydiscountamount"))
                        && StringUtils.isBlank(row.getString("outplace"))
                        && StringUtils.isBlank(row.getString("rb")))) {
                    if (StringUtils.isBlank(row.getString("type"))
                            || StringUtils.isBlank(row.getString("rb"))
                            || StringUtils.isBlank(row.getString("outplace"))
                            || StringUtils.isBlank(row.getString("agentnum"))) {
                        iserr = true;
                        row.put("msg", "错误信息:类型/红蓝字/发货地/经销商编码不能为空");
                        rowserr.add(row);
                        continue;
                    }
                    if (!agentsRowsMap.containsKey(row.getString("agentnum"))) {
                        iserr = true;
                        row.put("msg", "错误信息:经销商编号不存在");
                        rowserr.add(row);
                        continue;
                    }
                    if (!row.getString("rb").equals("红") && !row.getString("rb").equals("蓝")) {
                        iserr = true;
                        row.put("msg", "错误信息:红蓝字只能填写红或蓝");
                        rowserr.add(row);
                        continue;
                    }
                    if (!row.getString("outplace").equals("谈桥") && !row.getString("outplace").equals("马桥")) {
                        iserr = true;
                        row.put("msg", "错误信息:发货地只能填写谈桥或马桥");
                        rowserr.add(row);
                        continue;
                    }
                    if (!row.getString("type").equals("销售出库")
                            && !row.getString("outplace").equals("其他入库")
                            && !row.getString("outplace").equals("其他出库")) {
                        iserr = true;
                        row.put("msg", "错误信息:出入库单类型错误");
                        rowserr.add(row);
                        continue;
                    }
                    if (!itemRowsMap.containsKey(row.getString("itemno"))) {
                        iserr = true;
                        row.put("msg", "错误信息:品号不存在");
                        rowserr.add(row);
                        continue;
                    }
                    if (!isNumeric(row.getString("paydiscountamount"))) {
                        iserr = true;
                        row.put("msg", "优惠金额不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    }
                    if (!isNumeric(row.getString("payamount"))) {
                        iserr = true;
                        row.put("msg", "扣款金额不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    }

                    if (!isNumeric(row.getString("qty"))) {
                        iserr = true;
                        row.put("msg", "数量不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    }
                    if (!isNumeric(row.getString("defaultprice"))) {
                        iserr = true;
                        row.put("msg", "原价不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    }
                    if (!isNumeric(row.getString("discountrate"))) {
                        iserr = true;
                        row.put("msg", "折扣率不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    } else {
                        if (row.getDouble("discountrate") > 1) {
                            iserr = true;
                            row.put("msg", "折扣率不能大于1，请检查");
                            rowserr.add(row);
                            continue;
                        }
                    }
                } else {
                    if (!itemRowsMap.containsKey(row.getString("itemno"))) {
                        iserr = true;
                        row.put("msg", "错误信息:品号不存在");
                        rowserr.add(row);
                        continue;
                    }

                    if (!isNumeric(row.getString("qty"))) {
                        iserr = true;
                        row.put("msg", "数量不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    }
                    if (!isNumeric(row.getString("defaultprice"))) {
                        iserr = true;
                        row.put("msg", "原价不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    }
                    if (!isNumeric(row.getString("discountrate"))) {
                        iserr = true;
                        row.put("msg", "折扣率不为数字格式，请检查");
                        rowserr.add(row);
                        continue;
                    } else {
                        if (row.getDouble("discountrate") > 1) {
                            iserr = true;
                            row.put("msg", "折扣率不能大于1，请检查");
                            rowserr.add(row);
                            continue;
                        }
                    }
                }
                rowssuc.add(row);
            }

            Rows rowsResult = new Rows();
            Rows rowsdetail = new Rows();
            Row rowresult = new Row();
            int i = 0;
            for (Row row : rowssuc) {
                if (StringUtils.isNotBlank(row.getString("agentnum"))
                        && StringUtils.isNotBlank(row.getString("type"))
                        && StringUtils.isNotBlank(row.getString("rb"))
                        && StringUtils.isNotBlank(row.getString("outplace"))) {
                    rowresult.put("iteminfos", rowsdetail);
                    rowsResult.add(rowresult);
                    rowresult = new Row();
                    rowresult.put("type", row.getString("type"));
                    rowresult.put("depno", row.getString("depno"));
                    rowresult.put("agentnum", row.getString("agentnum"));
                    rowresult.put("remarks", row.getString("remarks"));
                    rowresult.put("payamount", StringUtils.isNotBlank(row.getString("payamount")) ? row.getDouble("payamount") : 0);
                    rowresult.put("paydiscountamount", StringUtils.isNotBlank(row.getString("paydiscountamount")) ? row.getDouble("paydiscountamount") : 0);
                    rowresult.put("rb", row.getString("rb"));
                    rowresult.put("outplace", row.getString("outplace"));

                    rowsdetail = new Rows();
                    Row rowdetail = new Row();
                    rowdetail.put("itemno", row.getString("itemno"));
                    rowdetail.put("stockno", row.getString("stockno"));
                    rowdetail.put("qty", row.getString("qty"));
                    rowdetail.put("defaultprice", row.getString("defaultprice"));
                    rowdetail.put("discountrate", row.getString("discountrate"));
                    rowsdetail.add(rowdetail);
                } else {
                    Row rowdetail = new Row();
                    rowdetail.put("itemno", row.getString("itemno"));
                    rowdetail.put("stockno", row.getString("stockno"));
                    rowdetail.put("qty", row.getString("qty"));
                    rowdetail.put("defaultprice", row.getString("defaultprice"));
                    rowdetail.put("discountrate", row.getString("discountrate"));
                    rowsdetail.add(rowdetail);
                }
                i++;
                if (i == rows.size()) {
                    rowresult.put("iteminfos", rowsdetail);
                    rowsResult.add(rowresult);
                }
            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("出入库单导入错误信息");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("type", "类型");
                map.put("depno", "部门编号");
                map.put("agentnum", "经销商编号");
                map.put("remarks", "备注");
                map.put("payamount", "扣款金额");
                map.put("paydiscountamount", "优惠金额");
                map.put("outplace", "发货地");
                map.put("rb", "红蓝字");
                map.put("itemno", "品号");
                map.put("stockno", "仓库编号");
                map.put("qty", "数量");
                map.put("defaultprice", "原价");
                map.put("discountrate", "折扣率");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("type");
                colNameList.add("depno");
                colNameList.add("agentnum");
                colNameList.add("remarks");
                colNameList.add("payamount");
                colNameList.add("paydiscountamount");
                colNameList.add("outplace");
                colNameList.add("rb");
                colNameList.add("itemno");
                colNameList.add("stockno");
                colNameList.add("qty");
                colNameList.add("defaultprice");
                colNameList.add("discountrate");
                colNameList.add("msg");

                keytypemap.put("type", String.class);
                keytypemap.put("depno", String.class);
                keytypemap.put("agentnum", String.class);
                keytypemap.put("remarks", String.class);
                keytypemap.put("payamount", String.class);
                keytypemap.put("paydiscountamount", String.class);
                keytypemap.put("outplace", String.class);
                keytypemap.put("rb", String.class);
                keytypemap.put("itemno", String.class);
                keytypemap.put("stockno", String.class);
                keytypemap.put("qty", String.class);
                keytypemap.put("defaultprice", String.class);
                keytypemap.put("discountrate", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr, map);

                Rows aa = saveToExcelAttachment(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }

            if (!rowsResult.isEmpty()) {
                int a = 0;
                for (Row row : rowsResult) {
                    if (a == 0) {
                        a++;
                        continue;
                    }
                    long st_stockbillid = createTableID("st_stockbill");
                    InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill");
                    insertSQL.setUniqueid(st_stockbillid);
                    insertSQL.setSiteid(siteid);
                    insertSQL.setValue("sys_enterpriseid", agentsRowsMap.get(row.getString("agentnum")).get(0).getLong("sys_enterpriseid"));
                    insertSQL.setValue("rec_contactsid", agentsRowsMap.get(row.getString("agentnum")).get(0).getLong("contactsid"));
                    insertSQL.setValue("departmentid", depRowsMap.containsKey(row.getString("depno")) ? depRowsMap.get(row.getString("depno")).get(0).getLong("departmentid") : 0);
                    insertSQL.setValue("stockid", 0);
                    insertSQL.setValue("type", "销售出库");
                    insertSQL.setValue("typemx", "");
                    insertSQL.setValue("sa_supplierid", 0);
                    insertSQL.setValue("rb", 1);
                    insertSQL.setValue("remarks", row.getString("remarks"));
                    insertSQL.setDateValue("billdate");
                    insertSQL.setValue("billno", createBillCode("stockbill"));

                    insertSQL.setValue("outplace", row.getString("outplace"));
                    insertSQL.setValue("delivery", "到付");
                    insertSQL.setValue("invoice_enterprisename", agentsRowsMap.get(row.getString("agentnum")).get(0).getString("enterprisename"));
                    insertSQL.setValue("invoice_address", agentsRowsMap.get(row.getString("agentnum")).get(0).getString("address"));
                    insertSQL.setValue("invoice_taxno", agentsRowsMap.get(row.getString("agentnum")).get(0).getString("taxno"));
                    insertSQL.setValue("tracknumber", "");
                    insertSQL.setValue("isconfirm", 0);

                    insertSQL.setValue("period", getDate_Str().substring(0, 7));

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

                    sqlList.add(DataContrlLog.createLog(this, "st_stockbill", st_stockbillid, "新增", "销售出库单新增成功").getSQL());

                    int j = 1;
                    BigDecimal payamount = BigDecimal.ZERO;
                    for (Row rowdetail : row.getRows("iteminfos")) {
                        payamount = payamount.add(row.getBigDecimal("undeliqty").multiply(row.getBigDecimal("price")));
                        insertSQL = SQLFactory.createInsertSQL(this, "st_stockbill_items");
                        insertSQL.setUniqueid(createTableID("st_stockbill_items"));
                        insertSQL.setSiteid(siteid);
                        insertSQL.setValue("rowno", j);
                        insertSQL.setValue("st_stockbillid", st_stockbillid);
                        insertSQL.setValue("sa_orderitemsid", 0);
                        insertSQL.setValue("itemid", itemRowsMap.get(rowdetail.getString("itemno")).get(0).getLong("itemid"));
                        insertSQL.setValue("sa_orderid", 0);
                        insertSQL.setValue("stockid", stockRowsMap.get(rowdetail.getString("stockno")).get(0).getLong("stockid"));
                        insertSQL.setValue("qty", rowdetail.getDouble("qty"));
                        insertSQL.setValue("discountrate", rowdetail.getDouble("discountrate"));
                        insertSQL.setValue("sa_dispatch_itemsid", 0);
                        insertSQL.setValue("defaultprice", rowdetail.getDouble("defaultprice"));
                        insertSQL.setValue("remarks", "");
                        insertSQL.setValue("itemno", rowdetail.getString("itemno"));
                        insertSQL.setValue("itemname", itemRowsMap.get(rowdetail.getString("itemno")).get(0).getString("itemname"));
                        insertSQL.setValue("model", itemRowsMap.get(rowdetail.getString("itemno")).get(0).getString("model"));
                        insertSQL.setValue("price", new BigDecimal(rowdetail.getDouble("defaultprice")).multiply(new BigDecimal(rowdetail.getDouble("discountrate"))));
                        insertSQL.setValue("amount", new BigDecimal(rowdetail.getDouble("defaultprice")).multiply(new BigDecimal(rowdetail.getDouble("discountrate"))).multiply(new BigDecimal(rowdetail.getDouble("qty"))));
                        insertSQL.setValue("untaxedprice", new BigDecimal(rowdetail.getDouble("defaultprice")).multiply(new BigDecimal(rowdetail.getDouble("discountrate"))).divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP));
                        insertSQL.setValue("untaxedamount", new BigDecimal(rowdetail.getDouble("qty")).multiply(new BigDecimal(rowdetail.getDouble("defaultprice"))).multiply(new BigDecimal(rowdetail.getDouble("discountrate"))).divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP));
                        insertSQL.setValue("skucontrol", itemRowsMap.get(rowdetail.getString("itemno")).get(0).getBoolean("skucontrol"));
                        j++;
                        sqlList.add(insertSQL.getSQL());
                    }
                }
            }
            if (!sqlList.isEmpty()) {
                System.err.println(sqlList.toString());
                dbConnect.runSqlUpdate(sqlList);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().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);
//    }


    /**
     * 账户余额冻结
     *
     * @param addorsub
     */
    public ArrayList<String> updateAccountbalance_freez(long st_stockbillid, boolean addorsub) throws YosException {
        ArrayList<String> sqllist = new ArrayList<>();
        /******** 冻结数据 ********/
        Rows rows = dbConnect.runSqlQuery("select t4.sa_accountbalanceid,t3.sa_orderid,t3.sonum,ROUND(sum(t1.qty*t2.price*ifnull(t2.pricerate,1)*ifnull(t2.rwpricerate,1)), 2) amount from st_stockbill_items t1 \n" +
                "                   inner join sa_orderitems t2 on t1.sa_orderitemsid=t2.sa_orderitemsid and t1.siteid=t2.siteid\n" +
                "                  inner join sa_order t3 on t2.sa_orderid=t3.sa_orderid and t2.siteid=t3.siteid\n" +
                "\t\t\t\t\t\t\t\t\tleft join sa_accountbalance t4 on t4.sa_accountclassid=t3.sa_accountclassid and t4.sys_enterpriseid=t3.sys_enterpriseid and t4.siteid=t3.siteid\n" +
                "                where t1.st_stockbillid=" + st_stockbillid + " group by t4.sa_accountbalanceid,t3.sa_orderid,t3.sonum");
        if (addorsub) {
            for (Row row : rows) {
                String sonum = row.getString("sonum");
                long sa_orderid = row.getLong("sa_orderid");
                long sa_accountbalanceid = row.getLong("sa_accountbalanceid");
                BigDecimal amount = row.getBigDecimal("amount");
                Rows accountbalancefreezrows = dbConnect.runSqlQuery("select sa_accountbalance_freezid from sa_accountbalance_freez where sourceid=" + sa_orderid + " and sourcetable='sa_order' and siteid='" + siteid + "'");

                sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)+" + amount + " where sa_accountbalanceid=" + sa_accountbalanceid + " and siteid='" + siteid + "'");

                if (accountbalancefreezrows.isNotEmpty()) {
                    sqllist.add("update sa_accountbalance_freez set amount=amount+" + amount + " where sa_accountbalance_freezid=" + accountbalancefreezrows.get(0).getLong("sa_accountbalance_freezid"));
                } else {
                    long sa_accountbalance_freezid = createTableID("sa_accountbalance_freez");
                    InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_accountbalance_freez");
                    insertSQL.setUniqueid(sa_accountbalance_freezid);
                    insertSQL.setSiteid(siteid);
                    insertSQL.setValue("sa_accountbalanceid", sa_accountbalanceid);
                    insertSQL.setValue("sourcetable", "sa_order");
                    insertSQL.setValue("sourceid", sa_orderid);
                    insertSQL.setValue("amount", amount);
                    insertSQL.setValue("remarks", "由订单" + sonum + "审核时冻结");
                    insertSQL.setValue("createby", username);
                    insertSQL.setDateValue("createdate");
                    sqllist.add(insertSQL.getSQL());
                }
            }
        } else {
            for (Row row : rows) {
                long sa_orderid = row.getLong("sa_orderid");
                long sa_accountbalanceid = row.getLong("sa_accountbalanceid");
                BigDecimal amount = row.getBigDecimal("amount");
                Rows accountbalancefreezrows = dbConnect.runSqlQuery("select sa_accountbalance_freezid,amount from sa_accountbalance_freez where sourceid=" + sa_orderid + " and sourcetable='sa_order' and siteid='" + siteid + "'");
                if (accountbalancefreezrows.size() > 0) {
                    sqllist.add("update sa_accountbalance set freezamount=ifnull(freezamount,0)-" + amount + " where sa_accountbalanceid=" + sa_accountbalanceid);
                    sqllist.add("update sa_accountbalance_freez set amount=amount-" + amount + " where sa_accountbalance_freezid=" + accountbalancefreezrows.get(0).getLong("sa_accountbalance_freezid"));
                }
            }
        }
        return sqllist;
    }

    public String[] getDistinctArray(String[] arrays) {
        ArrayList<String> a = new ArrayList<String>();
        for (String array : arrays) {
            if (!a.contains(array)) {
                a.add(array);
            }
        }
        int size = a.size();
        return (String[]) a.toArray(new String[size]);
    }

    public String getPeriod(boolean isjudge, Date date) throws YosException {

        String preioddate = new SimpleDateFormat("yyyy-MM-dd").format(date);
        Rows rows = dbConnect.runSqlQuery("select CONCAT_WS('-', year, LPAD(month, 2, '0')) period from st_period where begdate<='" + preioddate + "' and enddate>='" + preioddate + "'");
        if (rows.size() != 1) {
            if (isjudge) {
                throw new YosException("单据日期所属会计期间未生成");
            } else {
                return "null";
            }
        } else {
            return rows.get(0).getString("period");
        }
    }

    public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows,
                              HashMap<String, String> titlemap) {
        ArrayList<String> keylist = datarows.getFieldList();
        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname);
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();

        XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle();
        XSSFFont font = xssfFWorkbook.createFont();

        font.setColor((short) 0xa);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        xssfCellStyle1.setFont(font);

        CellStyle style = xssfFWorkbook.createCellStyle();
        DataFormat format = xssfFWorkbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
        for (int i = 0; i <= 13; i++) {
            sheet.setDefaultColumnStyle(i, style);
        }
        ExportExcelSpecial.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcelSpecial.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcelSpecial.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcelSpecial.createTitleCellStyle3(xssfFWorkbook);
        ExportExcelSpecial.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题

        for (int n = 0; n < datarows.size(); n++) {
            Row row = datarows.get(n);
            XSSFRow datarow = sheet.createRow(n + 3);
            for (int i1 = 0; i1 < keylist.size(); i1++) {
                Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype();
                if (fieldclazztype == Integer.class) {
                    datarow.createCell(i1).setCellValue(row.getInteger((String) keylist.get(i1)));
                } else if (fieldclazztype == Long.class) {
                    datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1)));
                } else if (fieldclazztype == Float.class) {
                    datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1)));
                } else if (fieldclazztype == Double.class) {
                    datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1)));
                } else {
                    datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1)));
                }
                if (i1 == 13) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    public static boolean isNumeric(String strNum) {
        if (strNum == null) {
            return false;
        }
        return strNum.matches("-?\\d+(\\.\\d+)?");
    }
}