package restcontroller.webmanage.sale.item;

import beans.brand.Brand;
import beans.datacontrllog.DataContrlLog;
import beans.datatag.DataTag;
import beans.itemclass.ItemClass;
import beans.parameter.Parameter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.sale.itempriceadjust.Itempriceadjust;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@API(title = "货品档案管理")
public class Item extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Item(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "货品档案新增", apiversion = R.ID20220923141502.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.class})
    public String insertOrUpdate() throws YosException {

        Long itemid = content.getLong("itemid");
        String itemno = content.getString("itemno").trim();
        boolean isauxunit = content.getBoolean("isauxunit");
        String itemname = content.getString("itemname");
        Long plm_itemextendid = content.getLongValue("plm_itemextendid");
        Rows rows = dbConnect.runSqlQuery("SELECT * from plm_item WHERE (itemtype='成品' or isshow=1 ) and itemno='" + itemno + "' and siteid='" + siteid + "'");

        if (rows.isNotEmpty()) {
            long tempitemid = rows.get(0).getLong("itemid");
            if (itemid > 0 && itemid != tempitemid) {
                return getErrReturnObject().setErrMsg("当前料号已存在").toString();
            }
            itemid = tempitemid;
            Rows itemextendrows = dbConnect.runSqlQuery("select * from plm_itemextend WHERE itemid='" + itemid + "' and siteid='" + siteid + "'");
            if (itemextendrows.isNotEmpty()) {
                plm_itemextendid = itemextendrows.get(0).getLong("plm_itemextendid");
            }
        }

        ArrayList<String> sqlList = new ArrayList<>();


        SQLFactory sqlFactory = new SQLFactory(this, "货品档案新增");
        if (itemid <= 0) {
            if (dbConnect.runSqlQuery("SELECT 1 from plm_item  WHERE itemno='" + itemno + "' and siteid='" + siteid + "' and (itemtype='成品' or isshow=1 )").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("商品编号已存在").toString();
            }
            itemid = createTableID("plm_item");
            sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "新增", "新建商品：" + itemname).getSQL());
        } else {
            if (dbConnect.runSqlQuery("SELECT 1 from plm_item WHERE itemid=" + itemid + " and siteid ='" + siteid + "' and (itemtype='成品' or isshow=1 )")
                    .isEmpty()) {
                return getReturnObject().setCode("0132", new String[0]).toString();//数据不存在

            }
            sqlFactory = new SQLFactory(this, "货品档案更新");
            String remarks = getLogRemarks(content);
            sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "编辑", remarks).getSQL());
        }

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("itemid", itemid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("itemno", itemno);
        sqlFactory.addParameter("unitid", content.getLongValue("unitid"));
        sqlFactory.addParameter("isauxunit", isauxunit);
        sqlFactory.addParameter("unitgroupid", isauxunit ? content.getString("unitgroupid") : "null");

        sqlFactory.addParameter("itemname", itemname);
        sqlFactory.addParameter("isonsale", 0);
        sqlFactory.addParameter("model", content.getString("model"));
        sqlFactory.addParameter("spec", content.getString("spec"));
        // 订单起订量
        sqlFactory.addParameter("orderminqty", content.getBigDecimalValue("orderminqty"));
        // 订单增量
        sqlFactory.addParameter("orderaddqty", content.getBigDecimalValue("orderaddqty"));
        // 订单起订量控制单位（0:主单位；1:辅助单位）
        sqlFactory.addParameter("orderminqty_auxunit", content.getString("orderminqty_auxunit"));
        // 订单增量控制单位（0:主单位；1:辅助单位）
        sqlFactory.addParameter("orderaddqty_auxunit", content.getString("orderaddqty_auxunit"));
        // 说明
        sqlFactory.addParameter("remarks", content.getStringValue("remarks"));
        // 商品条形码
        sqlFactory.addParameter("barcode", content.getStringValue("barcode"));
        // 是否单品管理
        sqlFactory.addParameter("skucontrol", content.getBooleanValue("skucontrol"));
        // 是否批次管理
        sqlFactory.addParameter("batchcontrol", content.getBooleanValue("batchcontrol"));
        // 毛重(g)
        sqlFactory.addParameter("grossweight", content.getBigDecimalValue("grossweight"));
        // 净重(g)
        sqlFactory.addParameter("weight", content.getBigDecimalValue("weight"));
        // 高度(mm)
        sqlFactory.addParameter("height", content.getBigDecimalValue("height"));
        // 宽度(mm)
        sqlFactory.addParameter("width", content.getBigDecimalValue("width"));
        // 长度(mm)
        sqlFactory.addParameter("length", content.getBigDecimalValue("length"));
        // 交期
        sqlFactory.addParameter("delivery", content.getLongValue("delivery"));
        // 牌价
        sqlFactory.addParameter("marketprice", content.getBigDecimalValue("marketprice"));
        // 是否工具
        sqlFactory.addParameter("istool", content.getBooleanValue("istool"));
        // 产品标准
        sqlFactory.addParameter("standards", content.getStringValue("standards"));
        // 产品标准明细
        sqlFactory.addParameter("standardsmx", content.getStringValue("standardsmx"));
        // 库存缺货标准
        sqlFactory.addParameter("stockstatus2", content.getIntValue("stockstatus2"));
        // 库存充足标准
        sqlFactory.addParameter("stockstatus1", content.getIntValue("stockstatus1"));
        // 保修期（年）
        sqlFactory.addParameter("warrantyday", content.getIntValue("warrantyday"));
        // 是否营销物料
        sqlFactory.addParameter("iswuliao", content.getBooleanValue("iswuliao"));
        // 是否服务物料
        sqlFactory.addParameter("isservice", content.getBooleanValue("isservice"));
        sqlFactory.addParameter("packqty", content.getBigDecimalValue("packqty", 1));
        sqlList.add(sqlFactory.getSQL());
        // 货品档案扩展属性字段表

        sqlFactory = new SQLFactory(this, "货品档案-扩展新增");
        if (plm_itemextendid <= 0) {
            plm_itemextendid = createTableID("plm_itemextend");
        } else {
            sqlFactory = new SQLFactory(this, "货品档案-扩展更新");
        }
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("plm_itemextendid", plm_itemextendid);
        sqlFactory.addParameter("itemid", itemid);
        sqlFactory.addParameter("material", content.getStringValue("material"));
        sqlFactory.addParameter("pressure", content.getStringValue("pressure"));
        JSONArray nominalpressure = new JSONArray();
        if (content.containsKey("nominalpressure")) {
            if (!StringUtils.isBlank(content.getStringValue("nominalpressure"))) {
                nominalpressure = content.getJSONArray("nominalpressure");
            }
        }
        sqlFactory.addParameter("caliber", content.getStringValue("caliber"));
        sqlFactory.addParameter("nominalpressure", nominalpressure);
        sqlFactory.addParameter("butterflyplatedrive", content.getStringValue("butterflyplatedrive"));
        sqlFactory.addParameter("connection", content.getStringValue("connection"));
        sqlFactory.addParameter("actuatordrivetype", content.getStringValue("actuatordrivetype"));
        sqlFactory.addParameter("valveplatematerial", content.getStringValue("valveplatematerial"));
        sqlFactory.addParameter("bodymaterial", content.getStringValue("bodymaterial"));
        sqlFactory.addParameter("actuatortype", content.getStringValue("actuatortype"));
        sqlFactory.addParameter("actuatorbrand", content.getStringValue("actuatorbrand"));
        sqlFactory.addParameter("isbutterfly", content.getBooleanValue("isbutterfly"));
        sqlFactory.addParameter("erpitemno", content.getStringValue("erpitemno"));
        sqlFactory.addParameter("erpitemname", content.getStringValue("erpitemname"));
        sqlFactory.addParameter("specalnote", content.getStringValue("specalnote"));
        sqlFactory.addParameter("prodline", content.getStringValue("prodline"));
        sqlFactory.addParameter("device", content.getStringValue("device"));
        sqlFactory.addParameter("categories", content.getStringValue("categories"));
        sqlFactory.addParameter("goodstype", content.getStringValue("goodstype"));
        sqlFactory.addParameter("explains", content.getStringValue("explains"));
        sqlFactory.addParameter("assistance", content.getStringValue("assistance"));


        sqlList.add(sqlFactory.getSQL());

        dbConnect.runSqlUpdate(sqlList);

        content.put("itemid", itemid);

        Rows itemrows = dbConnect.runSqlQuery("SELECT * FROM plm_item WHERE siteid='" + siteid + "' AND itemtype='成品' AND itemid='" + itemid + "'");
        if (itemrows.isNotEmpty()) {
            BigDecimal marketprice = content.getBigDecimalValue("marketprice");
            if (marketprice.compareTo(itemrows.get(0).getBigDecimal("marketprice")) != 0) {
                content.put("price", marketprice);
                String json = "[{\"pricegrade\":\"1\",\"price\":" + marketprice + "}]";
                content.put("itempriceadjust", json);
                content.put("adjustremarks", "来源【商品管理】一键调价");
                new Itempriceadjust(content).updatePrice();
            }


            JSONObject extradata = new JSONObject();
            extradata.put("extraUserList", new ArrayList<>());
            Executor.sendEml_controller(this, "itemchange", itemid, extradata);
        }

        return queryDetail();
    }

    public String getLogRemarks(JSONObject content) throws YosException {
        StringBuffer remarks = new StringBuffer("");
        Long itemid = content.getLong("itemid");
        Long plm_itemextendid = content.getLongValue("plm_itemextendid");
        Rows itemRows = dbConnect.runSqlQuery("SELECT * from plm_item WHERE itemid=" + itemid + " and siteid='" + siteid + "'");
        Rows itemextendRows = dbConnect.runSqlQuery("SELECT * from plm_itemextend WHERE plm_itemextendid=" + plm_itemextendid + " and siteid='" + siteid + "'");
        Rows itemkeyRows = dbConnect.runSqlQuery("SELECT column_name,column_title,column_type from sys_objectcols WHERE table_name='plm_item'");
        Rows itemextendkeyRows = dbConnect.runSqlQuery("SELECT column_name,column_title,column_type from sys_objectcols WHERE table_name='plm_itemextend'");

        try {
            remarks = remarks(remarks, itemRows, itemkeyRows, content);
            remarks = remarks(remarks, itemextendRows, itemextendkeyRows, content);
        } catch (Exception e) {
            e.printStackTrace();
            remarks.append("操作记录已丢失");
        }


        if (remarks.toString().equals("")) {
            return "无";
        }
        return remarks.toString();
    }


    public StringBuffer remarks(StringBuffer remarks, Rows rows, Rows keyRows, JSONObject content) throws YosException {
        if (rows.isNotEmpty()) {
            for (Row itemextendkeyRow : keyRows) {
                String key = itemextendkeyRow.getString("column_name");
                String type = itemextendkeyRow.getString("column_type");
                if (content.containsKey(key)) {
                    if (key.equals("changeby")) {
                        continue;
                    }
                    Object obj1 = rows.get(0).get(key) == null ? "null" : rows.get(0).get(key).toString();
                    Object obj2 = content.get(key);
                    if (key.equals("nominalpressure")) {
                        System.err.println(obj1);
                        System.err.println(obj2);
                    }
                    switch (type) {
                        case "decimal":
                            if (StringUtils.isNotEmpty(obj2.toString())) {
                                System.err.println(key);
                                BigDecimal decimal1 = rows.get(0).getBigDecimal(key);
                                BigDecimal decimal2 = content.getBigDecimal(key);
                                if (decimal1.compareTo(decimal2) == 0) {
                                    continue;
                                } else {
                                    remarks.append(itemextendkeyRow.getString("column_title") + "原值【" + rows.get(0).get(key) + "】，改为新值【" + content.get(key) + "】;");
                                }
                            }
                            break;
                        default:
                            System.err.println(key);
                            if (obj1.equals(content.get(obj2)) || obj1.toString().equals(obj2.toString())) {
                                continue;
                            } else {
                                remarks.append(itemextendkeyRow.getString("column_title") + "原值【" + rows.get(0).get(key) + "】，改为新值【" + content.get(key) + "】;");
                            }
                    }
                }
            }
        }

        return remarks;
    }

    @API(title = "货品档案详情", apiversion = R.ID20220923155302.v1.class)
    public String queryDetail() throws YosException {

        Long itemid = content.getLong("itemid");
        ArrayList<Long> itemids = new ArrayList<>();
        itemids.add(itemid);

        SQLFactory sqlFactory = new SQLFactory(this, "货品档案详情");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("itemid", itemid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false));
        // 默认商品图片
        Rows defaultImageRows = beans.Item.Item.getItemdefaultImage(this);
        // 品牌
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, itemids);
        // 营销类别
        RowsMap itemClassRowsMap = ItemClass.getAllItemClassRowsMap(this, itemids);
        // 附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", itemids);

        for (Row row : rows) {
            if (attRowsMap.getOrDefault(row.getString("itemid"), new Rows()).isEmpty()) {
                row.put("attinfos", defaultImageRows);
            } else {
                row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            }
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemClassRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
        }

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

    @API(title = "货品档案列表", apiversion = R.ID20220923140602.v1.class)
    @CACHEING
    public String queryList() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where.append(" and(");
                where.append("t1.isonsale ='").append(whereObject.getString("isonsale")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("model") && !"".equals(whereObject.getString("model"))) {
                where.append(" and(");
                where.append("t1.model like'%").append(whereObject.getString("model")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("spec") && !"".equals(whereObject.getString("spec"))) {
                where.append(" and(");
                where.append("t1.spec like'%").append(whereObject.getString("spec")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.onsaledate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.onsaledate <='").append(whereObject.getString("enddate")).append("' ");
            }
        }


        SQLFactory sqlFactory = new SQLFactory(this, "货品档案列表", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        // 默认商品图片
        Rows defaultImageRows = beans.Item.Item.getItemdefaultImage(this);
        // 附件
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        // 商品领域
        RowsMap tradefieldRowsMap = beans.Item.Item.getTradefieldRowsMap(this, ids);
        // 商品品牌
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, ids);
        RowsMap itemclassRowsMap = ItemClass.getAllItemClassRowsMap(this, ids);
        for (Row row : rows) {
            if (attRowsMap.getOrDefault(row.getString("itemid"), new Rows()).isEmpty()) {
                row.put("attinfos", defaultImageRows);
            } else {
                row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            }
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "货品档案删除", apiversion = R.ID20220923152202.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.v1.class})
    public String delete() throws YosException {

        JSONArray jsonArray = content.getJSONArray("itemids");

        String sql = " SELECT 1 from plm_item WHERE itemid in " + jsonArray + " and `status` ='审核' and siteid ='"
                + siteid + "'";
        sql = sql.replace("[", "(").replace("]", ")");
        if (dbConnect.runSqlQuery(sql).isNotEmpty()) {
            return getReturnObject().setCode("0154", new String[0]).toString();//存在已审核的数据，无法删除。

        }

        for (Object object : jsonArray) {
            long itemid = Long.parseLong(object.toString());
            if (dbConnect.runSqlQuery("SELECT 1 FROM plm_item WHERE siteid='" + siteid + "' AND itemtype='成品' AND itemid='" + itemid + "'").isNotEmpty()) {
                JSONObject extradata = new JSONObject();
                extradata.put("extraUserList", new ArrayList<>());
                Executor.sendEml_controller(this, "itemchange", itemid, extradata);
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "货品档案删除");
        sqlFactory.addParameter_in("itemid", jsonArray.toArray());
        sqlFactory.addParameter("siteid", siteid);
        dbConnect.runSqlUpdate(sqlFactory);


        DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "plm_item_parts");
        deleteSQL.setSiteid(siteid);
        deleteSQL.setWhere("itemid", jsonArray.toArray());
        deleteSQL.delete();

        return getSucReturnObject().toString();
    }

    @API(title = "货品档案审核", apiversion = R.ID20220923153902.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.v1.class})
    public String audit() throws YosException {

        JSONArray jsonArray = content.getJSONArray("itemids");
        String status = content.getString("status");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemname");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("itemid", jsonArray.toArray());
        Rows query = querySQL.query();
        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : query) {
            long itemid = row.getLong("itemid");
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "plm_item");
            updateSQL.setSiteid(siteid);
            updateSQL.setWhere("itemid", itemid);
            updateSQL.setValue("status", status.equals("0") ? "新建" : "审核");
            updateSQL.setValue("isonsale", 0);
            updateSQL.setValue("checkby", username);
            updateSQL.setValue("onsaledate", "null");
            if (status.equals("0")) {
                updateSQL.setValue("checkdate", "null");
            } else {
                updateSQL.setDateValue("checkdate");
            }
            sqlList.add(updateSQL.getSQL());
//            SQLFactory sqlFactory = new SQLFactory(this, "货品档案审核");
//            sqlFactory.addParameter("itemid", itemid);
//            sqlFactory.addParameter("siteid", siteid);
//            sqlFactory.addParameter("username", username);
//            sqlFactory.addParameter("status", status.equals("0") ? "新建" : "审核");
//            sqlFactory.addParameter("isonsale", 0);
//            sqlList.add(sqlFactory.getSQL());
            String s = status.equals("0") ? "反审核" : "审核";
            if (status.equals("0")) {
                sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "下架", "下架商品：" + row.getString("itemname")).getSQL());
            }
            sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, s, s + "商品：" + row.getString("itemname")).getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "货品档案上架", apiversion = R.ID20220923154802.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.v1.class})
    public String onsale() throws YosException {

        JSONArray jsonArray = content.getJSONArray("itemids");
        String isonsale = content.getString("isonsale");
        if (isonsale.equals("1")) {

            String sql = "SELECT DISTINCT itemid from sa_itemsaleclass WHERE itemid in " + jsonArray + " and siteid='" + siteid + "'";
            sql = sql.replace("[", "(").replace("]", ")");
            if (dbConnect.runSqlQuery(sql).size() < jsonArray.size()) {
                return getReturnObject().setCode("0155", new String[0]).toString();//存在没有营销类别的商品，无法上架！
            }

            SQLFactory isHas = new SQLFactory(this, "查询是否存在新建货品");
            isHas.addParameter_in("itemid", jsonArray.toArray());
            isHas.addParameter("siteid", siteid);
            Rows hasRows = dbConnect.runSqlQuery(isHas.getSQL(false));
            if (hasRows.isNotEmpty()) {
                return getReturnObject().setCode("0156", new String[0]).toString();//存在未审核的商品，无法上架！
            }

            SQLFactory isHasPrice = new SQLFactory(this, "查询是否存在未维护价格的商品");
            isHasPrice.addParameter_in("itemid", jsonArray.toArray());
            isHasPrice.addParameter("siteid", siteid);
            Rows hasPriceRows = dbConnect.runSqlQuery(isHasPrice.getSQL(false));
            if (hasPriceRows.isNotEmpty()) {
                return getReturnObject().setCode("0157", new String[0]).toString();//存在未维护价格的商品，无法上架！
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemname");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("itemid", jsonArray.toArray());
        Rows query = querySQL.query();

        ArrayList<String> sqlList = new ArrayList<>();
        for (Row row : query) {
            Long id = row.getLong("itemid");
//            SQLFactory sqlFactory = new SQLFactory(this, "货品档案上架");
//            if (isonsale.equals("0")) {
//                sqlFactory = new SQLFactory(this, "货品档案下架");
//
//            }
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "plm_item");
            updateSQL.setSiteid(siteid);
            updateSQL.setWhere("itemid", id);
            updateSQL.setValue("isonsale", isonsale);
            updateSQL.setValue("onsaleby", username);
            if (isonsale.equals("0")) {
                updateSQL.setValue("onsaledate", "null");
            } else {
                updateSQL.setDateValue("onsaledate");
                updateSQL.setWhere("status", "审核");
            }
            sqlList.add(updateSQL.getSQL());

//            sqlFactory.addParameter("itemid", id);
//            sqlFactory.addParameter("siteid", siteid);
//            sqlFactory.addParameter("username", username);
//            sqlFactory.addParameter("isonsale", isonsale);
//            sqlList.add(sqlFactory.getSQL(false));
            String s = isonsale.equals("0") ? "下架" : "上架";
            sqlList.add(DataContrlLog.createLog(this, "plm_item", id, s, s + "商品：" + row.getString("itemname")).getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "查询商品组", apiversion = R.ID20221216102302.v1.class)
    public String selectItemGroup() throws YosException {
        Long itemid = content.getLong("itemid");
        Boolean isexist = content.getBooleanValue("isexist");

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.groupname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        if (isexist) {
            ArrayList<Long> ids = new ArrayList<>();
            ids.add(itemid);
            Rows brandRows = Brand.getBrandRows(this, ids);
            ArrayList<String> sa_brandid = brandRows.toArrayList("sa_brandid");
            sa_brandid.add("0");
            String replace = sa_brandid.toString().replace("[", "(").replace("]", ")");
            where.append(" and t1.sa_itemgroupid not in (select sa_itemgroupid from sa_itemgroupmx where siteid='");
            where.append(siteid).append("' and itemid =").append(itemid).append(") and t1.sa_brandid in ").append(replace);
        } else {
            where.append(" and t2.itemid =").append(itemid);
        }
        SQLFactory sqlFactory = new SQLFactory(this, "查询商品组", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        String sql = sqlFactory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

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

    @API(title = "查询商品资料", apiversion = R.ID20221216105402.v1.class)
    public String selectTechnicalinfo() throws YosException {
        Long itemid = content.getLong("itemid");

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.type like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "查询产品资料", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("itemid", itemid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        ArrayList<Long> ids = rows.toArrayList("plm_technicalinfoid", new ArrayList<>());

        // 默认商品图片
        Rows defaultImageRows = beans.Item.Item.getItemdefaultImage(this);
        // 封面cover
        RowsMap coverRowsMap = getAttachmentUrl("plm_technicalinfo", ids, "cover");
        // 附件
        RowsMap attRowsMap = getAttachmentUrl("plm_technicalinfo", ids);
        for (Row row : rows) {
            Rows coverRows = coverRowsMap.get(row.getString("plm_technicalinfoid"));
            if (coverRows.isEmpty()) {
                if (!defaultImageRows.isEmpty()) {
                    row.put("cover", defaultImageRows.get(0).getString("url"));
                } else {
                    row.put("cover", "");
                }

            } else {
                row.put("cover", coverRows.get(0).getString("url"));
            }

            row.put("attinfos", attRowsMap.getOrDefault(row.getString("plm_technicalinfoid"), new Rows()));
        }

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

    @API(title = "查询商品价格", apiversion = R.ID20221216134102.v1.class)
    public String selectItemPrice() throws YosException {
        Long itemid = content.getLong("itemid");

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.pricegrade like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        SQLFactory sqlFactory = new SQLFactory(this, "查询商品价格", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("itemid", itemid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());

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

    @API(title = "货品档案导入模板", apiversion = R.ID20230311102404.v1.class)
    public String downloadExcel() throws YosException {
        boolean isInsert = true;
        if (content.containsKey("isInsert")) {
            isInsert = content.getBoolean("isInsert");
        }
        ExcelFactory excelFactory = new ExcelFactory("货品档案导入模板");

        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 <= 32; i++) {
            if (siteid.equals("HY")) {
                if (i == 9 || i == 10 || i == 14 || i == 16 || i == 13) {
                    continue;
                }
            } else {
                if (i == 9 || i == 10 || i == 13 || i == 15 || i == 12) {
                    continue;
                }
            }
            sheet.setDefaultColumnStyle(i, style);
        }
        // 设置工作薄列宽
        ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
        XSSFCellStyle titleCellStyle4 = ExportExcel.createBodyCellStyle4(xssfFWorkbook);

        ExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4, xssfFWorkbook, isInsert, siteid);

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

    @API(title = "货品档案新增导入", apiversion = R.ID20230311111404.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.class})
    public String importItems() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        ExcelFactory e;
        try {

            // 华为云
            e = getPostExcelFactory(content.getLong("attachmentid"));
            // 本地
            //e = getPostExcelFactory();

            ArrayList<String> keys = new ArrayList<>();
            keys.add("itemno");
            keys.add("itemname");
            keys.add("spec");
            keys.add("caliber");
            keys.add("nominalpressure");
            keys.add("model");
            keys.add("categories");
            keys.add("unitname");
            keys.add("erpitemname");
            keys.add("erpitemno");
            keys.add("standards");
            keys.add("standardsmx");
            keys.add("orderminqty");
            keys.add("orderaddqty");
            keys.add("material");
            keys.add("stockstatus1");
            keys.add("stockstatus2");
            keys.add("tradefield");
            keys.add("delivery");
            keys.add("prodline");
            keys.add("device");
            keys.add("specalnote");
            keys.add("isservice");
            keys.add("pressure");
            keys.add("butterflyplatedrive");
            keys.add("connection");
            keys.add("valveplatematerial");
            keys.add("bodymaterial");
            keys.add("actuatortype");
            keys.add("actuatorbrand");
            keys.add("isbutterfly");
            keys.add("brandname");
            keys.add("itemclassnum");
            keys.add("groupnum");

            HashMap<String, CellType> cellmap = new HashMap<>();
            for (String key : keys) {
                cellmap.put(key, CellType.STRING);
            }

            Rows rows = e.getSheetRows(0, keys, cellmap, 3);
            for (Row row : rows) {
                row.put("itemno", row.getString("itemno").trim());
            }
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();

            Boolean iserr = verify(rows, rowserr, rowssuc, true);


            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    String orderminqty = row.getString("orderminqty");
                    String orderaddqty = row.getString("orderaddqty");
                    String delivery = row.getString("delivery");
                    String stockstatus2 = row.getString("stockstatus2");
                    String stockstatus1 = row.getString("stockstatus1");
                    Long itemid = createTableID("plm_item");
                    SQLFactory sqlFactory = new SQLFactory(this, "货品档案新增");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlFactory.addParameter("username", username);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("itemno", row.getString("itemno").trim());
                    sqlFactory.addParameter("unitid", row.getLong("unitid"));
                    sqlFactory.addParameter("isauxunit", 0);
                    sqlFactory.addParameter("unitgroupid", "null");
                    sqlFactory.addParameter("itemname", row.getString("itemname"));
                    sqlFactory.addParameter("isonsale", 0);
                    sqlFactory.addParameter("model", row.getString("model"));
                    sqlFactory.addParameter("spec", row.getString("spec"));
                    // 订单起订量
                    sqlFactory.addParameter("orderminqty", StringUtils.isEmpty(orderminqty) ? "null" : orderminqty);
                    // 订单增量
                    sqlFactory.addParameter("orderaddqty", StringUtils.isEmpty(orderaddqty) ? "null" : orderaddqty);
                    // 订单起订量控制单位（0:主单位；1:辅助单位）
                    sqlFactory.addParameter("orderminqty_auxunit", 0);
                    // 订单增量控制单位（0:主单位；1:辅助单位）
                    sqlFactory.addParameter("orderaddqty_auxunit", 0);
                    // 说明
                    sqlFactory.addParameter("remarks", "null");
                    // 商品条形码
                    sqlFactory.addParameter("barcode", "null");
                    // 是否单品管理
                    sqlFactory.addParameter("skucontrol", 0);
                    // 是否批次管理
                    sqlFactory.addParameter("batchcontrol", 0);
                    // 毛重(g)
                    sqlFactory.addParameter("grossweight", "null");
                    // 净重(g)
                    sqlFactory.addParameter("weight", "null");
                    // 高度(mm)
                    sqlFactory.addParameter("height", "null");
                    // 宽度(mm)
                    sqlFactory.addParameter("width", "null");
                    // 长度(mm)
                    sqlFactory.addParameter("length", "null");
                    // 交期
                    sqlFactory.addParameter("delivery", StringUtils.isEmpty(delivery) ? "null" : delivery);
                    // 牌价
                    sqlFactory.addParameter("marketprice", 0);
                    // 是否工具
                    sqlFactory.addParameter("istool", 0);
                    // 产品标准
                    sqlFactory.addParameter("standards", row.getString("standards"));
                    // 产品标准明细
                    sqlFactory.addParameter("standardsmx", row.getString("standardsmx"));
                    // 库存缺货标准
                    sqlFactory.addParameter("stockstatus2", StringUtils.isEmpty(stockstatus2) ? "null" : stockstatus2);
                    // 库存充足标准
                    sqlFactory.addParameter("stockstatus1", StringUtils.isEmpty(stockstatus1) ? "null" : stockstatus1);
                    // 保修期（年）
                    sqlFactory.addParameter("warrantyday", "null");
                    // 是否营销物料
                    sqlFactory.addParameter("iswuliao", 0);
                    // 是否服务物料
                    sqlFactory.addParameter("isservice", row.getString("isservice"));
                    sqlFactory.addParameter("packqty", "1");
                    sqlFactory.addParameter("explains", "");
                    sqlFactory.addParameter("goodstype", "");
                    sqlFactory.addParameter("assistance", "");
                    sqlList.add(sqlFactory.getSQL());
                    // 货品档案扩展属性字段表

                    sqlFactory = new SQLFactory(this, "货品档案-扩展新增");
                    Long plm_itemextendid = createTableID("plm_itemextend");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("username", username);
                    sqlFactory.addParameter("plm_itemextendid", plm_itemextendid);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlFactory.addParameter("material", row.getString("material"));
                    sqlFactory.addParameter("pressure", row.getString("pressure"));

                    sqlFactory.addParameter("butterflyplatedrive", row.getString("butterflyplatedrive"));
                    sqlFactory.addParameter("connection", row.getString("connection"));
                    sqlFactory.addParameter("valveplatematerial", row.getString("valveplatematerial"));
                    sqlFactory.addParameter("bodymaterial", row.getString("bodymaterial"));
                    sqlFactory.addParameter("actuatortype", row.getString("actuatortype"));
                    sqlFactory.addParameter("actuatorbrand", row.getString("actuatorbrand"));
                    sqlFactory.addParameter("isbutterfly", row.getString("isbutterfly"));
                    sqlFactory.addParameter("erpitemno", row.getString("erpitemno"));
                    sqlFactory.addParameter("erpitemname", row.getString("erpitemname"));
                    sqlFactory.addParameter("specalnote", row.getString("specalnote"));
                    sqlFactory.addParameter("prodline", row.getString("prodline"));
                    sqlFactory.addParameter("device", row.getString("device"));
                    sqlFactory.addParameter("actuatordrivetype", "null");
                    sqlFactory.addParameter("categories", row.getString("categories"));
                    if (siteid.equals("HY")) {
                        sqlFactory.addParameter("caliber", row.getString("caliber"));
                        JSONArray jsonArray = new JSONArray();
                        jsonArray.add(row.getString("nominalpressure"));
                        Object value = row.get("nominalpressure");
                        String[] nominalpressure = value.toString().split("/");
                        if (value.toString().contains("\n")) {
                            nominalpressure = value.toString().split("\n");
                        }

                        JSONArray nominalpressureJsonArray = JSONArray.parseArray(JSON.toJSONString(nominalpressure));
                        sqlFactory.addParameter("nominalpressure", nominalpressureJsonArray.toJSONString());
                    } else {
                        sqlFactory.addParameter("caliber", "null");
                        sqlFactory.addParameter("nominalpressure", "[]");
                    }

                    sqlList.add(sqlFactory.getSQL());

                    Long plm_item_tradefieldid = createTableID("plm_item_tradefield");
                    sqlFactory = new SQLFactory(this, "新增领域");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("plm_item_tradefieldid", plm_item_tradefieldid);
                    sqlFactory.addParameter("tradefield", row.getString("tradefield"));
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlList.add(sqlFactory.getSQL());

                    JSONArray itemclassids = row.getJSONArray("itemclassid");
                    if (itemclassids.size() > 0) {
                        for (Object id : itemclassids) {
                            Long sa_itemsaleclassid = createTableID("sa_itemsaleclass");
                            sqlFactory = new SQLFactory(this, "新增营销类别");
                            sqlFactory.addParameter("siteid", siteid);
                            sqlFactory.addParameter("sa_itemsaleclassid", sa_itemsaleclassid);
                            sqlFactory.addParameter("itemclassid", id);
                            sqlFactory.addParameter("itemno", row.getString("itemno"));
                            sqlFactory.addParameter("itemid", itemid);
                            sqlList.add(sqlFactory.getSQL());
                            sqlList.add(DataContrlLog.createLog(this, "sa_itemsaleclass", sa_itemsaleclassid, "新增", "货品档案导入").getSQL());
                        }
                    }
                    JSONArray sa_itemgroupids = row.getJSONArray("sa_itemgroupids");
                    if (sa_itemgroupids.size() > 0) {
                        sqlList.add("DELETE FROM sa_itemgroupmx WHERE siteid='" + siteid + "' AND itemid=" + itemid);
                    }
                    for (Object obj : sa_itemgroupids) {
                        long sa_itemgroupid = Long.parseLong(obj.toString());
                        Long sa_itemgroupmxid = createTableID("sa_itemgroupmx");
                        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_itemgroupmx");
                        insertSQL.setSiteid(siteid);
                        insertSQL.setUniqueid(sa_itemgroupmxid);
                        insertSQL.setValue("sa_itemgroupid", sa_itemgroupid);
                        insertSQL.setValue("itemid", itemid);
                        insertSQL.setValue("itemno", row.getString("itemno"));
                        insertSQL.setWhere(" not exists (select * from sa_itemgroupmx where  itemid='" + itemid + "' and  sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "')");
                        sqlList.add(insertSQL.getSQL());
                        sqlList.add(DataContrlLog.createLog(this, "sa_itemgroup", sa_itemgroupid, "新增", "货品档案导入").getSQL());
                    }

                    sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "新增", "货品档案导入").getSQL());
                    sqlList.add(DataContrlLog.createLog(this, "plm_itemextend", plm_itemextendid, "新增", "货品档案导入").getSQL());
                    sqlList.add(DataContrlLog.createLog(this, "plm_item_tradefield", plm_item_tradefieldid, "新增", "货品档案导入").getSQL());
                }
            }

            if (!sqlList.isEmpty()) {
                dbConnect.runSqlUpdate(sqlList);
                ArrayList<String> sqlList2 = new ArrayList<>();
                for (Row row : rowssuc) {
                    JSONArray sa_itemgroupids = row.getJSONArray("sa_itemgroupids");
                    for (Object obj : sa_itemgroupids) {
                        long sa_itemgroupid = Long.parseLong(obj.toString());
                        Rows mxrows = dbConnect.runSqlQuery("SELECT itemid,itemno FROM sa_itemgroupmx WHERE sa_itemgroupid=" + sa_itemgroupid + " ORDER BY sa_itemgroupmxid LIMIT 1");
                        if (mxrows.isNotEmpty()) {
                            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_itemgroup");
                            updateSQL.setUniqueid(sa_itemgroupid);
                            updateSQL.setSiteid(siteid);
                            updateSQL.setValue("itemid", mxrows.getRow(0).getLong("itemid"));
                            updateSQL.setValue("itemno", mxrows.getRow(0).getString("itemno"));
                            sqlList2.add(updateSQL.getSQL());
                        }
                    }
                }
                dbConnect.runSqlUpdate(sqlList2);
            }
            if (iserr) {
                String url = errImport(rowserr, true);
                return getErrReturnObject().setErrMsg(url).toString();
            }
        } catch (Exception e1) {
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows,
                              HashMap<String, String> titlemap, Boolean isInsert) {
        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 <= 34; i++) {
            if (siteid.equals("HY")) {
                if (i == 9 || i == 10 || i == 14 || i == 16 || i == 13) {
                    continue;
                }
            } else {
                if (i == 9 || i == 10 || i == 13 || i == 15 || i == 12) {
                    continue;
                }
            }
            sheet.setDefaultColumnStyle(i, style);
        }

        ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
        ExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook, isInsert, siteid);// 写入标题

        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 == 34) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    @API(title = "添加商品组", apiversion = R.ID20230331094304.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220922164403.v1.class, R.ID20220923101603.v1.class})
    public String insertitemgroup() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Long itemid = content.getLong("itemid");
        String itemno = content.getString("itemno");
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
        String groupname;
        String groupnum;
        if (sa_itemgroupid <= 0 || dbConnect.runSqlQuery("select 1 from sa_itemgroup where siteid='" + siteid + "' and sa_itemgroupid=" + sa_itemgroupid).isEmpty()) {
            sa_itemgroupid = createTableID("sa_itemgroup");
            long sa_brandid = content.getLong("sa_brandid");
            groupname = content.getString("groupname");
            long sequence = content.getLongValue("sequence", 0L);
            Rows rows = dbConnect.runSqlQuery("select sequence from sa_itemgroup where siteid = '" + siteid + "' order by sequence asc");
            if (rows.isNotEmpty() && rows.toArrayList("sequence").contains(Long.toString(sequence))) {
                if (sequence <= rows.getLastRow().getLong("sequence")) {
                    sqlList.add("update sa_itemgroup set sequence = sequence+1 where siteid = '" + siteid + "' and sequence >= " + sequence);
                }
            }
            SQLFactory sqlAddFactory = new SQLFactory(this, "商品组新增");
            groupnum = createBillCode("itemgroup");
            sqlAddFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
            sqlAddFactory.addParameter("siteid", siteid);
            sqlAddFactory.addParameter("groupname", groupname);
            sqlAddFactory.addParameter("itemno", itemno);
            sqlAddFactory.addParameter("username", username);
            sqlAddFactory.addParameter("groupnum", groupnum);
            sqlAddFactory.addParameter("tradefield", "");
            sqlAddFactory.addParameter("sa_brandid", sa_brandid);
            sqlAddFactory.addParameter("sequence", sequence);
            sqlAddFactory.addParameter("itemid", itemid);
            sqlList.add(sqlAddFactory.getSQL());
        } else {
            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_itemgroup", "groupnum", "groupname");
            querySQL.setTableAlias("t1");
            querySQL.setSiteid(siteid);
            querySQL.setWhere("sa_itemgroupid", sa_itemgroupid);
            Rows query = querySQL.query();
            groupnum = query.get(0).getString("groupnum");
            groupname = query.get(0).getString("groupname");
        }
        sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "添加商品组", "添加商品组：" + groupnum + "-" + groupname).getSQL());
        Rows itemgroupmxsequence = dbConnect.runSqlQuery("select sequence from sa_itemgroupmx where siteid='" + siteid + "' and sa_itemgroupid=" + sa_itemgroupid);
        long sequence = 1L;
        if (itemgroupmxsequence.isNotEmpty()) {
            sequence = itemgroupmxsequence.getLastRow().getLong("sequence") + 1;
        }
        SQLFactory saleFactory = new SQLFactory(this, "商品组商品明细新增");
        saleFactory.addParameter("siteid", siteid);
        saleFactory.addParameter("sequence", sequence);
        saleFactory.addParameter("sa_itemgroupmxid", createTableID("sa_itemgroupmx"));
        saleFactory.addParameter("itemid", itemid);
        saleFactory.addParameter("itemno", itemno);
        saleFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
        sqlList.add(saleFactory.getSQL());
        dbConnect.runSqlUpdate(sqlList);
        Rows itemtradefield = dbConnect.runSqlQuery("select distinct t2.tradefield  from sa_itemgroupmx t1 inner join plm_item_tradefield t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where sa_itemgroupid=" + sa_itemgroupid);
        StringBuilder stringBuilder = new StringBuilder();
        if (!itemtradefield.isEmpty()) {
            for (String tradefield : itemtradefield.toArrayList("tradefield")) {
                stringBuilder.append(tradefield).append(",");
            }
            if (stringBuilder.length() > 0) {
                stringBuilder.deleteCharAt(stringBuilder.length() - 1);
            }
        }
        dbConnect.runSqlUpdate("update sa_itemgroup set tradefield='" + stringBuilder + "' where sa_itemgroupid=" + sa_itemgroupid);
        return getSucReturnObject().toString();
    }

    @API(title = "商品组删除指定商品", apiversion = R.ID20230404111404.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220922164403.v1.class, R.ID20220923101603.v1.class})
    public String deleteitemgroupmx() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Long itemid = content.getLong("itemid");
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
        String sql = "DELETE FROM sa_itemgroupmx WHERE sa_itemgroupid =" + sa_itemgroupid + " and itemid=" + itemid;
        sqlList.add(sql);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_itemgroup", "groupnum", "groupname");
        querySQL.setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_itemgroupid", sa_itemgroupid);
        Rows query = querySQL.query();
        for (Row row : query) {
            String groupnum = row.getString("groupnum");
            String groupname = row.getString("groupname");
            sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "删除商品组", "删除商品组：" + groupnum + "-" + groupname).getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);

        Rows itemtradefield = dbConnect.runSqlQuery("select distinct t2.tradefield  from sa_itemgroupmx t1 inner join plm_item_tradefield t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where sa_itemgroupid=" + sa_itemgroupid);
        StringBuilder stringBuilder = new StringBuilder();
        if (!itemtradefield.isEmpty()) {
            for (String tradefield : itemtradefield.toArrayList("tradefield")) {
                stringBuilder.append(tradefield).append(",");
            }
            if (stringBuilder.length() > 0) {
                stringBuilder.deleteCharAt(stringBuilder.length() - 1);
            }
        }
        dbConnect.runSqlUpdate("update sa_itemgroup set tradefield='" + stringBuilder + "' where sa_itemgroupid=" + sa_itemgroupid);
        return getSucReturnObject().toString();

    }


    @API(title = "设置商品大类提成方案", apiversion = R.ID20240311150704.v1.class)
    public String setitemcategoriesreward() throws YosException {
        // true 新增  false 编辑
        boolean action = content.getBooleanValue("action");
        String type = content.getString("type");
        String value = content.getString("value");
        List<JSONObject> valuereward = content.getJSONArray("valuereward").toJavaList(JSONObject.class);

        if (action && dbConnect.runSqlQuery("select 1 from plm_item_categories_reward where siteid='" + siteid + "' and type='" + type + "' and value='" + value + "'").isNotEmpty()) {
            if (type.equals("领域")) {
                return getReturnObject().setCode("0158", new String[0]).toString();//该领域提成方案已存在
            } else if (type.equals("商品大类")) {
                return getReturnObject().setCode("0159", new String[0]).toString();//该商品大类提成方案已存在

            } else {
                return getErrReturnObject().toString();
            }
        }
        if (valuereward.isEmpty()) {
            return getReturnObject().setCode("0160", new String[0]).toString();//提成方案不能为空

        }
        valuereward.sort((o1, o2) -> {
            try {
                String rewardstart1 = o1.getString("rewardstart");
                String rewardend1 = o1.getString("rewardend");
                String rewardstart2 = o2.getString("rewardstart");
                String rewardend2 = o2.getString("rewardend");
                if (StringUtils.isEmpty(rewardstart1)) {
                    return -1;
                } else if (StringUtils.isEmpty(rewardend1)) {
                    return 1;
                } else if (StringUtils.isEmpty(rewardstart2)) {
                    return 1;
                } else if (StringUtils.isEmpty(rewardend2)) {
                    return -1;
                }
                return rewardstart1.compareTo(rewardstart2);
            } catch (YosException e) {
                throw new RuntimeException(e);
            }
        });

        for (int i = 0; i < valuereward.size(); i++) {
            if (i == valuereward.size() - 1) {
                break;
            }
            JSONObject row = valuereward.get(i);
            JSONObject row1 = valuereward.get(i + 1);

            String rewardend = row.getString("rewardend");
            Boolean includerewardend = row.getBooleanValue("includerewardend");

            String rewardstart = row1.getString("rewardstart");
            Boolean includerewardstart = row1.getBooleanValue("includerewardstart");


            if (rewardend.compareTo(rewardstart) > 0 || StringUtils.isEmpty(rewardend)) {
                return getReturnObject().setCode("0161", new String[]{(StringUtils.isNotEmpty(rewardstart) ? new BigDecimal(rewardstart).multiply(BigDecimal.valueOf(100)) + "%" : rewardstart), (StringUtils.isNotEmpty(rewardend) ? new BigDecimal(rewardend).multiply(BigDecimal.valueOf(100)) + "%" : rewardend)}).toString();//存在重复折扣区间【"(StringUtils.isNotEmpty(rewardstart) ? new BigDecimal(rewardstart).multiply(BigDecimal.valueOf(100)) + "%" : rewardstart) + "~" + (StringUtils.isNotEmpty(rewardend) ? new BigDecimal(rewardend).multiply(BigDecimal.valueOf(100)) + "%" : rewardend)+ "】"
            }

            if (rewardend.compareTo(rewardstart) == 0 && includerewardend && includerewardstart) {
                return getReturnObject().setCode("0162", new String[]{String.valueOf(new BigDecimal(rewardstart).multiply(BigDecimal.valueOf(100)))}).toString();//多个区间包含此折扣值【"+ new BigDecimal(rewardstart).multiply(BigDecimal.valueOf(100)) + "%】
            }
        }

        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add("delete from plm_item_categories_reward where siteid='" + siteid + "' and type='" + type + "' and value='" + value + "'");
        for (JSONObject row : valuereward) {
            SQLFactory sqlFactory = new SQLFactory(this, "商品提成方案新增");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("plm_item_categories_rewardid", createTableID("plm_item_categories_reward"));
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("value", value);
            sqlFactory.addParameter("type", type);
            sqlFactory.addParameter("reward_rate", row.getBigDecimalValue("reward_rate"));
            sqlFactory.addParameter("rewardtype", row.getStringValue("rewardtype"));
            sqlFactory.addParameter("rewardstart", StringUtils.isEmpty(row.getString("rewardstart")) ? "null" : row.getString("rewardstart"));
            sqlFactory.addParameter("includerewardstart", row.getString("includerewardstart"));
            sqlFactory.addParameter("rewardend", StringUtils.isEmpty(row.getString("rewardend")) ? "null" : row.getString("rewardend"));
            sqlFactory.addParameter("includerewardend", row.getString("includerewardend"));
            sqlList.add(sqlFactory.getSQL());
        }
        if (action) {
            sqlList.add(DataContrlLog.createLog(this, "plm_item_categories_reward", 0L, "新增", "新增【" + type + "】的【" + value + "】大类提成方案【" + valuereward + "】").getSQL());
        } else {
            sqlList.add(DataContrlLog.createLog(this, "plm_item_categories_reward", 0L, "编辑", "编辑【" + type + "】的【" + value + "】大类提成方案【" + valuereward + "】").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "查询商品大类提成方案", apiversion = R.ID20240311150804.v1.class)
    public String queryitemcategoriesreward() throws YosException {
        String type = content.getString("type");
        SQLFactory sqlFactory = new SQLFactory(this, "商品提成方案查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("type", type);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        for (Row row : rows) {
            String rewardstart = row.getString("rewardstart");
            String rewardend = row.getString("rewardend");
            Boolean includerewardstart = row.getBoolean("includerewardstart");
            Boolean includerewardend = row.getBoolean("includerewardend");
            String reward_rate = (row.getBigDecimal("reward_rate").multiply(BigDecimal.valueOf(100))).setScale(2, RoundingMode.HALF_UP).toPlainString() + "%";
            String remarksend = "，提成比例为" + reward_rate;
            boolean iscategories = type.equals("商品大类");
            if (StringUtils.isEmpty(rewardstart) && StringUtils.isEmpty(rewardend)) {
                row.put("remarks", iscategories ? remarksend : "");
            } else if (StringUtils.isNotEmpty(rewardstart) && StringUtils.isNotEmpty(rewardend)) {
                rewardstart = new BigDecimal(rewardstart).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.HALF_UP).toPlainString();
                rewardend = new BigDecimal(rewardend).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.HALF_UP).toPlainString();
                if (includerewardstart && includerewardend) {
                    row.put("remarks", "当牌价的" + rewardstart + "%<=产品售价折扣<=" + rewardend + "%" + (iscategories ? remarksend : ""));
                } else if (includerewardstart) {
                    row.put("remarks", "当牌价的" + rewardstart + "%<=产品售价折扣<" + rewardend + "%" + (iscategories ? remarksend : ""));
                } else if (includerewardend) {
                    row.put("remarks", "当牌价的" + rewardstart + "%<产品售价折扣<=" + rewardend + "%" + (iscategories ? remarksend : ""));
                } else {
                    row.put("remarks", "当牌价的" + rewardstart + "%<产品售价折扣<" + rewardend + "%" + (iscategories ? remarksend : ""));
                }
            } else if (StringUtils.isEmpty(rewardstart)) {
                rewardend = new BigDecimal(rewardend).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.HALF_UP).toPlainString();
                if (includerewardend) {
                    row.put("remarks", "当产品售价折扣<=" + rewardend + "%" + (iscategories ? remarksend : ""));
                } else {
                    row.put("remarks", "当产品售价折扣<" + rewardend + "%" + (iscategories ? remarksend : ""));
                }
            } else {
                rewardstart = new BigDecimal(rewardstart).multiply(BigDecimal.valueOf(100)).setScale(2, RoundingMode.HALF_UP).toPlainString();
                if (includerewardstart) {
                    row.put("remarks", "当牌价的" + rewardstart + "%<=产品售价折扣" + (iscategories ? remarksend : ""));
                } else {
                    row.put("remarks", "当牌价的" + rewardstart + "%<产品售价折扣" + (iscategories ? remarksend : ""));
                }
            }
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询商品大类提成方案明细", apiversion = R.ID20240313164004.v1.class)
    public String querymxitemcategoriesreward() throws YosException {
        String value = content.getString("value");
        String type = content.getString("type");
        Rows rows = dbConnect.runSqlQuery("select * from plm_item_categories_reward where siteid='" + siteid + "' and type='" + type + "' and value='" + value + "'");
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "删除商品大类提成方案", apiversion = R.ID20240313133204.v1.class)
    public String deleteitemcategoriesreward() throws YosException {
        String value = content.getString("value");
        String type = content.getString("type");
        dbConnect.runSqlUpdate("delete from plm_item_categories_reward where siteid='" + siteid + "' and type='" + type + "' and value='" + value + "'");
        DataContrlLog.createLog(this, "plm_item_categories_reward", 0L, "删除", "删除【" + type + "】提成方案【" + value + "】").insert();
        return getSucReturnObject().toString();
    }


    @API(title = "图片导入", apiversion = R.ID20230407164004.v1.class)
    public String attionfosDownloadExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("货品图片导入模板");

        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 <= 1; i++) {
            sheet.setDefaultColumnStyle(i, style);
        }
        // 设置工作薄列宽
        ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
        XSSFCellStyle titleCellStyle4 = ExportExcel.createBodyCellStyle4(xssfFWorkbook);

        ExportExcel.attionfosBatchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, titleCellStyle4, xssfFWorkbook);

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

    @API(title = "货品图片导入", apiversion = R.ID20230407164104.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.class})
    public String importItemsAttionfos() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        ExcelFactory e;
        try {

            // 华为云
            e = getPostExcelFactory(content.getLong("attachmentid"));
            // 本地
            //e = getPostExcelFactory();

            ArrayList<String> keys = new ArrayList<>();
            keys.add("itemno");
            keys.add("attionfosName");

            HashMap<String, CellType> cellmap = new HashMap<>();
            cellmap.put("itemno", CellType.STRING);
            cellmap.put("attionfosName", CellType.STRING);

            Rows rows = e.getSheetRows(0, keys, 3);
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            String str = "select itemno,itemid from plm_item where isshow=1 and siteid='" + siteid + "' and itemno in " + rows.toJsonArray("itemno");
            str = str.replace("[", "(").replace("]", ")");
            RowsMap itemRowsMap = dbConnect.runSqlQuery(str).toRowsMap("itemno");
            String postfix = "'xbm','tif','pjp','svgz','jpg','jpeg ','ico','tiff','gif','svg','jfif','webp','png','bmp','pjpeg','avif'";
            str = "select attachmentid,document from sys_attachment where siteid='" + siteid + "' and type='sourcefile' and isfolder=0 and postfix in (" + postfix + ") and document in " + rows.toJsonArray("attionfosName");
            str = str.replace("[", "(").replace("]", ")");
            RowsMap attionfosRowsMap = dbConnect.runSqlQuery(str).toRowsMap("document");
            for (Row row : rows) {
                String itemno = row.getString("itemno");
                String attionfosName = row.getString("attionfosName");
                if (StringUtils.isEmpty(itemno) || StringUtils.isEmpty(attionfosName)) {
                    iserr = true;
                    row.put("msg", "错误信息:产品编号/图片名称不能为空");
                    rowserr.add(row);
                    continue;
                }
                if (!itemRowsMap.containsKey(itemno)) {
                    iserr = true;
                    row.put("msg", "错误信息:产品不存在");
                    rowserr.add(row);
                    continue;
                }
                if (!attionfosRowsMap.containsKey(attionfosName)) {
                    iserr = true;
                    row.put("msg", "错误信息:图片不存在");
                    rowserr.add(row);
                    continue;
                }
                if (attionfosRowsMap.get(attionfosName).size() > 1) {
                    iserr = true;
                    row.put("msg", "错误信息:存在重复图片名称,无法自动匹配");
                    rowserr.add(row);
                    continue;
                } else {
                    row.put("attachmentid", attionfosRowsMap.get(attionfosName).get(0).getLong("attachmentid"));
                    row.put("itemid", itemRowsMap.get(itemno).get(0).getLong("itemid"));
                }
                rowssuc.add(row);
            }

            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    SQLFactory sqlFactory = new SQLFactory(this, "附件关联新增");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("linksid", createTableID("sys_attachment_links"));
                    sqlFactory.addParameter("attachmentid", row.getLong("attachmentid"));
                    sqlFactory.addParameter("ownerid", row.getLong("itemid"));
                    sqlFactory.addParameter("username", username);
                    sqlList.add(sqlFactory.getSQL());
                }
            }

            if (!sqlList.isEmpty()) {
                dbConnect.runSqlUpdate(sqlList);
                cleanAllDataPool();
            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("货品档案图片导入错误信息");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "产品编码");
                map.put("attionfosName", "图片名称");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("itemno");
                colNameList.add("attionfosName");
                colNameList.add("msg");
                keytypemap.put("itemno", String.class);
                keytypemap.put("attionfosName", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                attionfosAddSheet(excelFactory, "Sheet1", rowserr, map);

                Rows aa = uploadExcelToObs(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getErrReturnObject().setErrMsg(url).toString();
            }
        } catch (Exception e1) {
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    public XSSFSheet attionfosAddSheet(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 <= 1; i++) {
            sheet.setDefaultColumnStyle(i, style);
        }

        ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook);
        ExportExcel.attionfosBatchDetailerr(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 == 2) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }


    @API(title = "货品导入更新", apiversion = R.ID20230411155104.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20220923140602.class})
    public String updateitems() {
        ArrayList<String> sqlList = new ArrayList<>();
        ExcelFactory e;
        try {

            // 华为云
            e = getPostExcelFactory(content.getLong("attachmentid"));
            // 本地
            //e = getPostExcelFactory();

            ArrayList<String> keys = new ArrayList<>();
            keys.add("itemno");
            keys.add("itemname");
            keys.add("spec");
            keys.add("caliber");
            keys.add("nominalpressure");
            keys.add("model");
            keys.add("categories");
            keys.add("unitname");
            keys.add("erpitemname");
            keys.add("erpitemno");
            keys.add("standards");
            keys.add("standardsmx");
            keys.add("orderminqty");
            keys.add("orderaddqty");
            keys.add("material");
            keys.add("stockstatus1");
            keys.add("stockstatus2");
            keys.add("tradefield");
            keys.add("delivery");
            keys.add("prodline");
            keys.add("device");
            keys.add("specalnote");
            keys.add("isservice");
            keys.add("pressure");
            keys.add("butterflyplatedrive");
            keys.add("connection");
            keys.add("valveplatematerial");
            keys.add("bodymaterial");
            keys.add("actuatortype");
            keys.add("actuatorbrand");
            keys.add("isbutterfly");
            keys.add("brandname");
            keys.add("itemclassnum");
            keys.add("groupnum");

            HashMap<String, CellType> cellmap = new HashMap<>();
            cellmap.put("itemno", CellType.STRING);
            cellmap.put("spec", CellType.STRING);
            cellmap.put("model", CellType.STRING);
            cellmap.put("erpitemno", CellType.STRING);
            cellmap.put("itemclassnum", CellType.STRING);


            Rows rows = e.getSheetRows(0, keys, cellmap, 3);
            for (Row row : rows) {
                row.put("itemno", row.getString("itemno").trim());
            }
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            Boolean iserr = verify(rows, rowserr, rowssuc, false);
//            System.out.println(rowssuc.toString());
            if (!rowssuc.isEmpty()) {

                RowsMap plm_item = getTableColumns("plm_item");
                RowsMap plm_item_tradefield = getTableColumns("plm_item_tradefield");
                RowsMap sa_itemsaleclass = getTableColumns("sa_itemsaleclass");
                RowsMap plm_itemextend = getTableColumns("plm_itemextend");
                for (Row row : rowssuc) {

                    StringBuilder item_key = new StringBuilder();
                    StringBuilder item_tradefield_key = new StringBuilder();
                    StringBuilder item_tradefield_value = new StringBuilder();
                    StringBuilder itemclass_key = new StringBuilder();
                    StringBuilder itemclass_value = new StringBuilder();
                    StringBuilder itemextend_key = new StringBuilder();
                    for (String key : row.keySet()) {
                        Object value = row.get(key);
                        if (key.equals("nominalpressure") && StringUtils.isEmpty(value.toString())) {
                            value = "";
                        }
                        if (StringUtils.isNotEmpty(value.toString()) && !key.equals("itemid") && !key.equals("itemno")) {
                            String[] nominalpressure;
                            if (key.equals("nominalpressure")) {
                                nominalpressure = value.toString().split("/");
                                if (value.toString().contains("\n")) {
                                    nominalpressure = value.toString().split("\n");
                                }

                                JSONArray nominalpressureJsonArray = JSONArray.parseArray(JSON.toJSONString(nominalpressure));
                                value = nominalpressureJsonArray.toJSONString();
                            }
                            if (plm_item.containsKey(key)) {
                                if (item_key.length() > 0) {
                                    item_key.append(",");
                                }
                                item_key.append(key).append("='").append(value).append("'");
                            } else if (plm_item_tradefield.containsKey(key)) {
                                if (item_tradefield_key.length() > 0) {
                                    item_tradefield_key.append(",");
                                }
                                if (item_tradefield_value.length() > 0) {
                                    item_tradefield_value.append(",");
                                }
                                item_tradefield_key.append(key);
                                item_tradefield_value.append("'").append(value).append("'");
                            }
//                            else if (sa_itemsaleclass.containsKey(key)) {
//                                if (itemclass_key.length() > 0) {
//                                    itemclass_key.append(",");
//                                }
//                                if (itemclass_value.length() > 0) {
//                                    itemclass_value.append(",");
//                                }
//                                itemclass_key.append(key);
//                                itemclass_value.append("'").append(value).append("'");
//                            }
                            else if (plm_itemextend.containsKey(key)) {
                                if (itemextend_key.length() > 0) {
                                    itemextend_key.append(",");
                                }
                                itemextend_key.append(key).append("='").append(value).append("'");
                            }
                        }
                    }
                    long itemid = row.getLong("itemid");
                    if (item_key.length() > 0) {
                        item_key.append(",changeuserid=").append(userid).append(",changeby='").append(username).append("',changedate=CURRENT_TIME");
                        sqlList.add("update plm_item set " + item_key + " where siteid='" + siteid + "' and itemid=" + itemid);
                    }
                    if (item_tradefield_key.length() > 0) {
                        sqlList.add("delete from plm_item_tradefield where siteid='" + siteid + "' and itemid=" + itemid);

                        long plm_item_tradefieldid = createTableID("plm_item_tradefield");
                        item_tradefield_key.append(",plm_item_tradefieldid,itemid,changeuserid,siteid");
                        item_tradefield_value.append(",").append(plm_item_tradefieldid).append(",").append(itemid).append(",").append(userid).append(",'").append(siteid).append("'");
                        sqlList.add("insert into plm_item_tradefield (" + item_tradefield_key + ") values(" + item_tradefield_value + ")");
                    }
                    if (row.getJSONArray("itemclassid").size() > 0) {
                        JSONArray itemclassids = row.getJSONArray("itemclassid");
                        sqlList.add("delete from sa_itemsaleclass where siteid='" + siteid + "' and itemid=" + itemid);
                        for (Object ids : itemclassids) {
                            StringBuilder itemclass_key2 = new StringBuilder();
                            StringBuilder itemclass_value2 = new StringBuilder();
                            long sa_itemsaleclassid = createTableID("sa_itemsaleclass");
                            itemclass_key2.append("itemclassid,sa_itemsaleclassid,itemid,itemno,siteid");
                            itemclass_value2.append(ids).append(",").append(sa_itemsaleclassid).append(",").append(itemid).append(",").append("'").append(row.getString("itemno")).append("'").append(",'").append(siteid).append("'");
                            sqlList.add("insert into sa_itemsaleclass (" + itemclass_key2 + ") values(" + itemclass_value2 + ")");
                        }

                    }
                    JSONArray sa_itemgroupids = row.getJSONArray("sa_itemgroupids");
                    if (sa_itemgroupids.size() > 0) {
                        sqlList.add("DELETE FROM sa_itemgroupmx WHERE siteid='" + siteid + "' AND itemid=" + itemid);
                    }
                    for (Object obj : sa_itemgroupids) {
                        long sa_itemgroupid = Long.parseLong(obj.toString());
                        Long sa_itemgroupmxid = createTableID("sa_itemgroupmx");
                        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_itemgroupmx");
                        insertSQL.setSiteid(siteid);
                        insertSQL.setUniqueid(sa_itemgroupmxid);
                        insertSQL.setValue("sa_itemgroupid", sa_itemgroupid);
                        insertSQL.setValue("itemid", itemid);
                        insertSQL.setValue("itemno", row.getString("itemno"));
                        insertSQL.setWhere(" not exists (select * from sa_itemgroupmx where  itemid='" + itemid + "' and  sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "')");
                        sqlList.add(insertSQL.getSQL());
                        sqlList.add(DataContrlLog.createLog(this, "sa_itemgroup", sa_itemgroupid, "新增", "货品档案导入").getSQL());
                    }
                    if (itemextend_key.length() > 0) {
                        itemextend_key.append(",changeuserid=").append(userid).append(",changeby='").append(username).append("',changedate=CURRENT_TIME");
                        if (StringUtils.isEmpty(row.get("nominalpressure").toString())) {
                            itemextend_key.append(",nominalpressure=null");
                        }
                        sqlList.add("update plm_itemextend set " + itemextend_key + " where siteid='" + siteid + "' and itemid=" + itemid);
                    }
                    sqlList.add(DataContrlLog.createLog(this, "plm_item", itemid, "编辑", "编辑导入修改").getSQL());
                }
            }

            if (!sqlList.isEmpty()) {
                dbConnect.runSqlUpdate(sqlList);
                ArrayList<String> sqlList2 = new ArrayList<>();
                for (Row row : rowssuc) {
                    JSONArray sa_itemgroupids = row.getJSONArray("sa_itemgroupids");
                    for (Object obj : sa_itemgroupids) {
                        long sa_itemgroupid = Long.parseLong(obj.toString());
                        Rows mxrows = dbConnect.runSqlQuery("SELECT itemid,itemno FROM sa_itemgroupmx WHERE sa_itemgroupid=" + sa_itemgroupid + " ORDER BY sa_itemgroupmxid LIMIT 1");
                        if (mxrows.isNotEmpty()) {
                            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_itemgroup");
                            updateSQL.setUniqueid(sa_itemgroupid);
                            updateSQL.setSiteid(siteid);
                            updateSQL.setValue("itemid", mxrows.getRow(0).getLong("itemid"));
                            updateSQL.setValue("itemno", mxrows.getRow(0).getString("itemno"));
                            sqlList2.add(updateSQL.getSQL());
                        }
                    }
                }
                dbConnect.runSqlUpdate(sqlList2);
                System.err.println("货品导入更新结束");
            }
            if (iserr) {
                String url = errImport(rowserr, false);
                System.err.println("货品导入更新结束" + url);
                return getErrReturnObject().setErrMsg(url).toString();
            }

        } catch (Exception e1) {
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();

    }

    public Boolean verify(Rows rows, Rows rowserr, Rows rowssuc, Boolean isInsert) throws YosException {
        RowsMap itemnoRowsMap = dbConnect.runSqlQuery("select itemid,itemno from plm_item t1  where isshow=1 and siteid='" + siteid + "'").toRowsMap("itemno");
        RowsMap unitnameRowsMap = dbConnect.runSqlQuery("select unitid,unitname from plm_unit where siteid='" + siteid + "'").toRowsMap("unitname");
        RowsMap typeRowsMap = dbConnect.runSqlQuery("select t1.typename,t2.value from sys_optiontype t1 left join sys_optiontypemx t2 on t1.optiontypeid=t2.optiontypeid and t2.siteid='" + siteid + "' where t1.typename in ('categories','tradefield','itemstandards','pressurelevel','itemmaterial','butterflyplatedrive','valveplatematerial','bodymaterial','actuatortype','actuatorbrand','connectionmode') and t2.isused=1").toRowsMap("typename");
        RowsMap groupnumMap = dbConnect.runSqlQuery("SELECT groupnum,sa_itemgroupid from sa_itemgroup WHERE siteid='" + siteid + "' ").toRowsMap("groupnum");
        RowsMap imitemnoRowsMap = rows.toRowsMap("itemno");
        Rows itemcalss = dbConnect.runSqlQuery("select t1.sa_brandid,t1.brandname,t2.itemclassid,t2.itemclassnum from sa_brand t1 inner join plm_itemclass t2 on t1.siteid=t2.siteid and t1.sa_brandid=t2.sa_brandid where t1.siteid='" + siteid + "'");
        RowsMap itemclassRowsMap = itemcalss.toRowsMap("brandname");

        ArrayList<String> tradefield = typeRowsMap.get("tradefield").toArrayList("value");
        ArrayList<String> itemstandards = typeRowsMap.get("itemstandards").toArrayList("value");
        ArrayList<String> pressurelevel = typeRowsMap.get("pressurelevel").toArrayList("value");
        ArrayList<String> itemmaterial = typeRowsMap.get("itemmaterial").toArrayList("value");
        ArrayList<String> butterflyplatedrive = typeRowsMap.get("butterflyplatedrive").toArrayList("value");
        ArrayList<String> valveplatematerial = typeRowsMap.get("valveplatematerial").toArrayList("value");
        ArrayList<String> bodymaterial = typeRowsMap.get("bodymaterial").toArrayList("value");
        ArrayList<String> actuatortype = typeRowsMap.get("actuatortype").toArrayList("value");
        ArrayList<String> actuatorbrand = typeRowsMap.get("actuatorbrand").toArrayList("value");
        ArrayList<String> connectionmode = typeRowsMap.get("connectionmode").toArrayList("value");
        ArrayList<String> categories = typeRowsMap.get("categories").toArrayList("value");

        for (Row row : rows) {
            Boolean iserr = false;
            StringBuffer errmsg = new StringBuffer("错误信息:");
            String brandname = row.getString("brandname");
            String itemclassnum = row.getString("itemclassnum");
            String itemno = row.getString("itemno");
            String unitname = row.getString("unitname");
            try {
                if (StringUtils.isNotEmpty(row.getString("orderminqty"))) row.getBigDecimal("orderminqty");
                if (StringUtils.isNotEmpty(row.getString("orderaddqty"))) row.getBigDecimal("orderaddqty");
                if (StringUtils.isNotEmpty(row.getString("stockstatus1"))) row.getBigDecimal("stockstatus1");
                if (StringUtils.isNotEmpty(row.getString("stockstatus2"))) row.getBigDecimal("stockstatus2");
                if (StringUtils.isNotEmpty(row.getString("delivery"))) row.getBigDecimal("delivery");
            } catch (Exception e1) {
                iserr = true;
                errmsg.append("销售起订量/销售增量/库存充足标准/库存缺货标准/销售交期填写格式错误;");
//                row.put("msg", "错误信息:销售起订量/销售增量/库存充足标准/库存缺货标准/销售交期填写格式错误");
//                rowserr.add(row);
//                continue;
            }
            if (isInsert) {
                if (StringUtils.isEmpty(itemno)
                        || StringUtils.isEmpty(row.getString("itemname"))
                        || StringUtils.isEmpty(unitname)
                        || StringUtils.isEmpty(row.getString("erpitemname"))
                        || StringUtils.isEmpty(row.getString("erpitemno"))
                        || StringUtils.isEmpty(row.getString("tradefield"))) {
                    iserr = true;
                    errmsg.append("产品编号/产品名称/商品大类/计量单位/品号/品号/领域，不能为空;");
//                    row.put("msg", "错误信息:产品编号/产品名称/商品大类/计量单位/品号/品号/领域，不能为空");
//                    rowserr.add(row);
//                    continue;
                }
                if (StringUtils.isEmpty(row.getString("categories"))) {
                    iserr = true;
                    errmsg.append("产品编号/产品名称/商品大类/计量单位/品号/品号/领域，不能为空;");
//                    row.put("msg", "错误信息:产品编号/产品名称/商品大类/计量单位/品号/品号/领域，不能为空");
//                    rowserr.add(row);
//                    continue;
                }
                if (itemnoRowsMap.containsKey(itemno)) {
                    iserr = true;
                    errmsg.append("商品已存在;");
//                    row.put("msg", "错误信息:商品已存在");
//                    rowserr.add(row);
//                    continue;
                }
                row.put("isservice", row.getString("isservice").equals("是") ? 1 : 0);
                row.put("isbutterfly", row.getString("isbutterfly").equals("是") ? 1 : 0);
            } else {
                if (!itemnoRowsMap.containsKey(itemno)) {
                    iserr = true;
                    errmsg.append("商品不存在;");
//                    row.put("msg", "错误信息:商品不存在");
//                    rowserr.add(row);
//                    continue;
                } else {
                    if (StringUtils.isNotEmpty(row.getString("isservice"))) {
                        row.put("isservice", row.getString("isservice").equals("是") ? 1 : 0);
                    }
                    if (StringUtils.isNotEmpty(row.getString("isbutterfly"))) {
                        row.put("isbutterfly", row.getString("isbutterfly").equals("是") ? 1 : 0);
                    }
                    row.put("itemid", itemnoRowsMap.get(itemno).get(0).getLong("itemid"));
                }
            }
            if (StringUtils.isEmpty("itemno")) {
                iserr = true;
                errmsg.append("产品编号不能为空;");
//                row.put("msg", "错误信息:产品编号不能为空");
//                rowserr.add(row);
//                continue;
            }
            if (siteid.equals("HY") || siteid.equals("TZ")) {
                if (StringUtils.isNotEmpty(row.getString("categories")) && !categories.contains(row.getString("categories"))) {
                    iserr = true;
                    errmsg.append("商品大类不存在;");
//                    row.put("msg", "错误信息:商品大类不存在");
//                    rowserr.add(row);
//                    continue;
                }
            }

            if ((StringUtils.isEmpty(brandname) && StringUtils.isNotEmpty(itemclassnum)) || (StringUtils.isNotEmpty(brandname) && StringUtils.isEmpty(itemclassnum))) {
                iserr = true;
                errmsg.append("不能单独填写品牌和营销类别;");
//                row.put("msg", "错误信息:不能单独填写品牌和营销类别");
//                rowserr.add(row);
//                continue;
            }
            if (imitemnoRowsMap.containsKey(itemno) && imitemnoRowsMap.get(itemno).size() > 1) {
                iserr = true;
                errmsg.append("本次导入商品中存在重复的商品编号;");
//                row.put("msg", "错误信息:本次导入商品中存在重复的商品编号");
//                rowserr.add(row);
//                continue;
            }

            if (StringUtils.isNotEmpty(unitname) && !unitnameRowsMap.containsKey(unitname)) {
                iserr = true;
                errmsg.append("计量单位不存在;");
//                row.put("msg", "错误信息:计量单位不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("tradefield")) && !tradefield.contains(row.getString("tradefield"))) {
                iserr = true;
                errmsg.append("领域不存在;");
//                row.put("msg", "错误信息:领域不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("standards")) && !itemstandards.contains(row.getString("standards"))) {
                iserr = true;
                errmsg.append("产品标准不存在;");
//                row.put("msg", "错误信息:产品标准不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("pressure")) && !pressurelevel.contains(row.getString("pressure"))) {
                iserr = true;
                errmsg.append("压力等级不存在;");
//                row.put("msg", "错误信息:压力等级不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("material")) && !itemmaterial.contains(row.getString("material"))) {
                iserr = true;
                errmsg.append("材质不存在;");
//                row.put("msg", "错误信息:材质不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("butterflyplatedrive")) && !butterflyplatedrive.contains(row.getString("butterflyplatedrive"))) {
                iserr = true;
                errmsg.append("蝶板驱动不存在;");
//                row.put("msg", "错误信息:蝶板驱动不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("valveplatematerial")) && !valveplatematerial.contains(row.getString("valveplatematerial"))) {
                iserr = true;
                errmsg.append("阀板材质不存在;");
//                row.put("msg", "错误信息:阀板材质不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("bodymaterial")) && !bodymaterial.contains(row.getString("bodymaterial"))) {
                iserr = true;
                errmsg.append("阀体材质不存在;");
//                row.put("msg", "错误信息:阀体材质不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("actuatortype")) && !actuatortype.contains(row.getString("actuatortype"))) {
                iserr = true;
                errmsg.append("执行器类型不存在;");
//                row.put("msg", "错误信息:执行器类型不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("actuatorbrand")) && !actuatorbrand.contains(row.getString("actuatorbrand"))) {
                iserr = true;
                errmsg.append("执行器品牌不存在;");
//                row.put("msg", "错误信息:执行器品牌不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("connection")) && !connectionmode.contains(row.getString("connection"))) {
                iserr = true;
                errmsg.append("连接形式不存在;");
//                row.put("msg", "错误信息:连接形式不存在");
//                rowserr.add(row);
//                continue;
            }
            if (StringUtils.isNotEmpty(brandname) && StringUtils.isNotEmpty(itemclassnum)) {
                String[] itemclassnums = itemclassnum.split("/");
                for (String str : itemclassnums) {
                    if (!itemclassRowsMap.containsKey(brandname) || !itemclassRowsMap.get(brandname).toArrayList("itemclassnum").contains(str)) {
                        iserr = true;
                        errmsg.append("品牌不存在或该品牌下没有对应的营销类别;");
                    }
                }
            }
            String groupnum = row.getString("groupnum");
            if (StringUtils.isNotEmpty(groupnum)) {
                String[] groupnums = groupnum.split("/");
                for (String str : groupnums) {
                    if (!groupnumMap.containsKey(str)) {
                        iserr = true;
                        errmsg.append("该商品组不存在;");
                    }
                }
            }
            if (StringUtils.isNotEmpty(groupnum)) {
                String[] groupnums = groupnum.split("/");
                ArrayList<Long> ids = new ArrayList<>();
                for (String str : groupnums) {
                    if (groupnumMap.containsKey(str)) {
                        ids.add(groupnumMap.get(str).get(0).getLong("sa_itemgroupid"));
                    }
                }
                row.put("sa_itemgroupids", ids);
            }
            if (iserr) {
                row.put("msg", errmsg);
                rowserr.add(row);
                continue;
            }
            if (StringUtils.isNotEmpty(row.getString("unitname"))) {
                row.put("unitid", unitnameRowsMap.get(unitname).get(0).getLong("unitid"));
            }
            if (StringUtils.isNotEmpty(brandname) && StringUtils.isNotEmpty(itemclassnum)) {
                String[] itemclassnums = itemclassnum.split("/");
                ArrayList<Long> itemclassids = new ArrayList<>();
                for (Row row1 : itemclassRowsMap.get(brandname)) {

                    for (String str : itemclassnums) {
                        if (row1.getString("itemclassnum").equals(str)) {
                            itemclassids.add(row1.getLong("itemclassid"));
                        }
                    }
                    row.put("sa_brandid", row1.getLong("sa_brandid"));
                    row.put("itemclassid", itemclassids);
                }
            }
            rowssuc.add(row);
        }
        return rowserr.size() > 0;
    }

    public String errImport(Rows rowserr, Boolean isInsert) throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("货品档案导入错误信息");
        HashMap<String, String> map = new HashMap<String, String>();
        map.put("itemno", "产品编码");
        map.put("itemname", "产品名称");
        map.put("spec", "型号");
        map.put("caliber", "公称通径");
        map.put("nominalpressure", "公称压力");
        map.put("model", "规格尺寸");
        map.put("categories", "商品大类");
        map.put("unitname", "计量单位");
        map.put("erpitemname", "品名");
        map.put("erpitemno", "品号");
        map.put("standards", "产品标准");
        map.put("standards", "产品标准明细");
        map.put("orderminqty", "销售增量");
        map.put("orderaddqty", "销售起订量");
        map.put("material", "材质");
        map.put("stockstatus1", "库存充足标准");
        map.put("stockstatus2", "库存缺货标准");
        map.put("tradefield", "领域");
        map.put("delivery", "销售交期");
        map.put("prodline", "产线");
        map.put("device", "装置");
        map.put("specalnote", "特殊说明");
        map.put("isservice", "是否服务物料");
        map.put("pressure", "压力等级");
        map.put("butterflyplatedrive", "蝶板驱动");
        map.put("connection", "连接形式");
        map.put("valveplatematerial", "阀板材质");
        map.put("bodymaterial", "阀体材质");
        map.put("actuatortype", "执行器类型");
        map.put("actuatorbrand", "执行器品牌");
        map.put("isbutterfly", "是否中线蝶阀");
        map.put("brandname", "品牌");
        map.put("itemclassnum", "营销类别编码");
        map.put("groupnum", "商品组编号");
        map.put("msg", "错误信息");
        ArrayList<String> colNameList = new ArrayList<String>();
        HashMap<String, Class> keytypemap = new HashMap<String, Class>();
        colNameList.add("itemno");
        colNameList.add("itemname");
        colNameList.add("spec");
        colNameList.add("caliber");
        keytypemap.put("caliber", String.class);
        colNameList.add("nominalpressure");
        keytypemap.put("nominalpressure", String.class);
        colNameList.add("model");
        colNameList.add("categories");
        keytypemap.put("categories", String.class);
        colNameList.add("unitname");
        colNameList.add("erpitemname");
        colNameList.add("erpitemno");
        colNameList.add("standards");
        colNameList.add("standardsmx");
        colNameList.add("orderminqty");
        colNameList.add("orderaddqty");
        colNameList.add("material");
        colNameList.add("stockstatus1");
        colNameList.add("stockstatus2");
        colNameList.add("tradefield");
        colNameList.add("delivery");
        colNameList.add("prodline");
        colNameList.add("device");
        colNameList.add("specalnote");
        colNameList.add("isservice");
        colNameList.add("pressure");
        colNameList.add("butterflyplatedrive");
        colNameList.add("connection");
        colNameList.add("valveplatematerial");
        colNameList.add("bodymaterial");
        colNameList.add("actuatortype");
        colNameList.add("actuatorbrand");
        colNameList.add("isbutterfly");
        colNameList.add("brandname");
        colNameList.add("itemclassnum");
        colNameList.add("groupnum");
        colNameList.add("msg");
        keytypemap.put("itemno", String.class);
        keytypemap.put("itemname", String.class);
        keytypemap.put("spec", String.class);
        keytypemap.put("model", String.class);
        keytypemap.put("unitname", String.class);
        keytypemap.put("erpitemname", String.class);
        keytypemap.put("erpitemno", String.class);
        keytypemap.put("standards", String.class);
        keytypemap.put("standardsmx", String.class);
        keytypemap.put("orderminqty", String.class);
        keytypemap.put("orderaddqty", String.class);
        keytypemap.put("material", String.class);
        keytypemap.put("stockstatus1", String.class);
        keytypemap.put("stockstatus2", String.class);
        keytypemap.put("tradefield", String.class);
        keytypemap.put("delivery", String.class);
        keytypemap.put("prodline", String.class);
        keytypemap.put("device", String.class);
        keytypemap.put("specalnote", String.class);
        keytypemap.put("isservice", String.class);
        keytypemap.put("pressure", String.class);
        keytypemap.put("butterflyplatedrive", String.class);
        keytypemap.put("connection", String.class);
        keytypemap.put("valveplatematerial", String.class);
        keytypemap.put("bodymaterial", String.class);
        keytypemap.put("actuatortype", String.class);
        keytypemap.put("actuatorbrand", String.class);
        keytypemap.put("isbutterfly", String.class);
        keytypemap.put("brandname", String.class);
        keytypemap.put("itemclassnum", String.class);
        keytypemap.put("groupnum", String.class);
        keytypemap.put("msg", String.class);
        rowserr.setFieldList(colNameList);
        rowserr.setFieldTypeMap(keytypemap);
        addSheet(excelFactory, "Sheet1", rowserr, map, isInsert);

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


}