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

            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(st_stockbillid, "原单据会计期间已关闭，不可调整");
                        continue;
                    }else if(!periodrowsMap.get("0").toRowsMap("period").containsKey(period)){
                        batchDeleteErr.addErr(st_stockbillid, "原单据会计期间未生成，不可调整");
                        continue;
                    }
                }
            }else{
                batchDeleteErr.addErr(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);
        return batchDeleteErr.getReturnObject().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 + "'");
        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"));
        row.put("expressform",expressformrows);


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

        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");
        for (Object o : st_stockbillids) {
            long st_stockbillid = Long.parseLong(o.toString());
            Stockbill.check(this, st_stockbillid, true);
        }
        return getSucReturnObject().toString();
    }

    @API(title = "反审核", apiversion = R.ID20230719154203.v1.class)
    @CACHEING_CLEAN(apiClass = {stockbill.class, stockbillitems.class, Order.class, OrderItems.class, stockbill.class, restcontroller.sale.stockbill.stockbill.class})
    public String uncheck() throws YosException {
        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)
    @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");
        ArrayList<String> sqllist = new ArrayList<>();
        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 + "'");
        Rows stockbillitemsrows = dbConnect.runSqlQuery("select t1.itemid,t1.qty,t2.skucontrol 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 * from st_stockbill_items_sku where st_stockbillid ='"
                + st_stockbillid + "' and  siteid='" + siteid + "'");
        QuerySQL skuQuerySQL = SQLFactory.createQuerySQL(this, "sa_itemsku","*");
        skuQuerySQL.setTableAlias("t1");
        skuQuerySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid=t2.itemid","itemno");
        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) {
            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 (!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();
                }

                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);
                if (stockrows.isNotEmpty()) {
                    skustockid = stockrows.get(0).getLong("stockid");
                }
                if (stockid == 0) {
                    skustockid = st_stockbill.getLong("stockid");
                }


                insertSQL.setValue("stockid", skustockid);
                insertSQL.setValue("itemid", skuitemid);
                insertSQL.setValue("sku", FMACHINECODE);
                insertSQL.setValue("st_stockbillid", st_stockbillid);
                insertSQL.setValue("st_stockbill_itemsid", st_stockbill_itemsid==0?st_stockbillid: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 from st_stockbill_items t1 where t1.st_stockbillid="+st_stockbillid);
        RowsMap stockbilldetailRowsMap = stockbilldetailRows.toRowsMap("itemid");
        for(Row row :stockbillskuRows){
            String itemid = row.getString("itemid");
            long st_stockbill_items_skuid =row.getLong("st_stockbill_items_skuid");
            if(stockbilldetailRowsMap.containsKey(itemid)){
                sqllist.add("update st_stockbill_items_sku set st_stockbill_itemsid="+stockbilldetailRowsMap.get(itemid).get(0).getLong("st_stockbill_itemsid")+" where st_stockbill_items_skuid="+st_stockbill_items_skuid);
            }
        }

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

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

        QuerySQL stockbillQuerySQL = SQLFactory.createQuerySQL(this, "st_stockbill","*");
        stockbillQuerySQL.setTableAlias("t1");
        stockbillQuerySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_contacts", "t2", "t2.contactsid=t1.rec_contactsid and t1.siteid=t2.siteid");
        stockbillQuerySQL.addQueryFields("rec_name","t2.name");
        stockbillQuerySQL.addQueryFields("rcv_phone","t2.phonenumber");
        stockbillQuerySQL.addQueryFields("rcv_mobile","t2.telephone");
        stockbillQuerySQL.addQueryFields("rcv_prov","t2.province");
        stockbillQuerySQL.addQueryFields("rcv_city","t2.city");
        stockbillQuerySQL.addQueryFields("rcv_county","t2.county");
        stockbillQuerySQL.addQueryFields("rcv_address","t2.address");

        stockbillQuerySQL.setWhere("t1.siteid",siteid);
        stockbillQuerySQL.setWhere("t1.st_stockbillid",st_stockbillids);
        stockbillQuerySQL.setWhere("t1.type='销售出库'");
        Rows stockbillRows =stockbillQuerySQL.query();
        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", "4");
            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("rcv_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", row.getString("rcv_prov"));
            insertSQL.setValue("rcv_city", row.getString("rcv_city"));
            insertSQL.setValue("rcv_county", row.getString("rcv_county"));
            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+)?");
    }
}