lsa_YOS/src/custom/restcontroller/webmanage/sale/item/Item.java

package restcontroller.webmanage.sale.item;

import beans.brand.Brand;
import beans.datacontrllog.DataContrlLog;
import beans.invbal.Invbal;
import beans.itemclass.ItemClass;
import beans.itemgroup.Itemgroup;
import beans.parameter.Parameter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;

import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;
import restcontroller.webmanage.sale.itemgroup.itemgroup;
import utility.ERPDocking;
import utility.tools.WebRequest;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;

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

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

        Long itemid = content.getLong("itemid");
        String itemno = content.getString("itemno");
        boolean isauxunit = content.getBoolean("isauxunit");

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

        Long plm_itemextendid = content.getLongValue("plm_itemextendid");
        SQLFactory sqlFactory = new SQLFactory(this, "货品档案新增");
        if (itemid <= 0) {
            itemid = createTableID("plm_item");
        } else {
            if (dbConnect.runSqlQuery("SELECT 1 from plm_item WHERE itemid=" + itemid + " and siteid ='" + siteid + "'")
                    .isEmpty()) {
                return getErrReturnObject().setErrMsg("数据不存在").toString();
            }
            Rows itemRows = dbConnect.runSqlQuery("SELECT t1.*,t2.material from plm_item t1 left join plm_itemextend t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid WHERE t1.itemid=" + itemid + " and t1.siteid ='" + siteid + "'");

            sqlFactory = new SQLFactory(this, "货品档案更新");
            if (content.getLongValue("sa_customschemeid") > 0) {
                Rows customschemeRows = dbConnect.runSqlQuery("SELECT  t1.*,t2.schemename from sa_customscheme_items t1 left join sa_customscheme t2 on t1.sa_customschemeid=t2.sa_customschemeid and t1.siteid=t2.siteid WHERE t1.sa_customschemeid=" + content.getLongValue("sa_customschemeid") + " and  t1.siteid ='" + siteid + "'");
                String where = " and 1=1 and t1.itemid != " + itemid + " and sa_customschemeid=" + content.getLongValue("sa_customschemeid");
                if (!customschemeRows.isEmpty()) {
                    for (Row row : customschemeRows) {

                        if (StringUtils.isBlank(itemRows.get(0).getString(row.getString("value"))) && StringUtils.isBlank(content.getStringValue(row.getString("value")))) {
                            return getErrReturnObject().setErrMsg("该商品定制方案" + row.getString("schemename") + "下" + row.getString("description") + "不可为空").toString();
                        } else if (StringUtils.isBlank(itemRows.get(0).getString(row.getString("value"))) || StringUtils.isBlank(content.getStringValue(row.getString("value")))) {
                            //System.out.println(1111);
                           // System.out.println(StringUtils.isBlank(itemRows.get(0).getString(row.getString("value"))));
                            where = where + " and " + row.getString("value") + "='" + (StringUtils.isBlank(itemRows.get(0).getString(row.getString("value"))) ? content.getStringValue(row.getString("value")) : itemRows.get(0).getString(row.getString("value"))) + "'";

                        } else if (StringUtils.isNotBlank(itemRows.get(0).getString(row.getString("value"))) && StringUtils.isNotBlank(content.getStringValue(row.getString("value"))))
                            where = where + " and " + row.getString("value") + "='" + content.getStringValue(row.getString("value")) + "'";
                    }
                    //System.out.println("select * from plm_item where siteid='" + siteid + "'" + where);
                    Rows items = dbConnect.runSqlQuery("select t1.*,t2.material from plm_item t1 left join plm_itemextend t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "'" + where);
                    if (items.isNotEmpty()) {
                        return getErrReturnObject().setErrMsg("该商品与定制方案下的" + items.get(0).getString("itemno") + "商品重复，请修改").toString();
                    }
                }
            }
            if (content.getLongValue("sa_customschemeid") != itemRows.get(0).getLong("sa_customschemeid")) {
                Rows rowsCount = dbConnect.runSqlQuery("select count(1) count,min(groupnum) groupnum from (select ifnull(t3.sa_customschemeid,0) count,t1.sa_itemgroupid,t1.groupnum,t1.siteid from sa_itemgroup t1 inner join sa_itemgroupmx t2 on t1.siteid=t2.siteid and t1.sa_itemgroupid=t2.sa_itemgroupid inner join plm_item t3 on t2.itemid=t3.itemid and t2.siteid=t3.siteid group by t1.sa_itemgroupid,t1.groupnum,t1.siteid,ifnull(t3.sa_customschemeid,0))t where t.siteid='" + siteid + "' and t.sa_itemgroupid in (select sa_itemgroupid from sa_itemgroupmx where itemid=" + itemid + ")");
                if (rowsCount.get(0).getLong("count") > 0) {
                    return getErrReturnObject().setErrMsg("该商品存在与商品组" + rowsCount.get(0).getString("groupnum") + ",请在商品组删除该商品后进行保存").toString();
                }
            }
        }
        if(content.getBooleanValue("iscustomsize")){
            if(content.getLongValue("widthschemeid")<=0 || content.getLongValue("lengthschemeid")<=0){
                return getErrReturnObject().setErrMsg("定制商品长度定制方案及宽度定制方案必选").toString();
            }
        }
        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", content.getString("itemname"));
        sqlFactory.addParameter("isonsale", 0);
        sqlFactory.addParameter("model", content.getString("model"));
        sqlFactory.addParameter("spec", content.getString("spec"));
        // 订单起订量
        if(content.getBigDecimalValue("orderminqty").compareTo(BigDecimal.ZERO)<=0){
            return getErrReturnObject().setErrMsg("起订量必须大于零").toString();
        }
        if(content.getBigDecimalValue("orderaddqty").compareTo(BigDecimal.ZERO)<=0){
            return getErrReturnObject().setErrMsg("增量必须大于零").toString();
        }
        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.getLongValue("grossweight"));
        // 净重(g)
        sqlFactory.addParameter("weight", content.getLongValue("weight"));
        // 高度(mm)
        sqlFactory.addParameter("height", content.getLongValue("height"));
        // 宽度(mm)
        sqlFactory.addParameter("width", content.getLongValue("width"));
        // 长度(mm)
        sqlFactory.addParameter("length", content.getLongValue("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("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("marketingcategory", content.getStringValue("marketingcategory"));

        sqlFactory.addParameter("color", content.getStringValue("color"));
        sqlFactory.addParameter("cheek", content.getStringValue("cheek"));
        sqlFactory.addParameter("delistingstatus", content.getStringValue("delistingstatus"));

        sqlFactory.addParameter("ismodule", content.getStringValue("ismodule"));
        sqlFactory.addParameter("packageqty", content.getStringValue("packageqty"));
        sqlFactory.addParameter("iswoodproducts", content.getStringValue("iswoodproducts"));

        sqlFactory.addParameter("sa_customschemeid", content.getLongValue("sa_customschemeid"));
        sqlFactory.addParameter("financeclasstype", content.getStringValue("financeclasstype"));
        sqlFactory.addParameter("stockno", content.getStringValue("stockno"));
        sqlFactory.addParameter("volume", content.getBigDecimalValue("volume"));

        sqlFactory.addParameter("iscustomsize", content.getBooleanValue("iscustomsize"));
        sqlFactory.addParameter("customprice", content.getBigDecimalValue("customprice"));
        sqlFactory.addParameter("widthschemeid", content.getBigDecimalValue("widthschemeid"));
        sqlFactory.addParameter("lengthschemeid", content.getBigDecimalValue("lengthschemeid"));
        sqlFactory.addParameter("pricingmetod", content.getLongValue("pricingmetod")==0?1:content.getLongValue("pricingmetod"));

        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"));
        sqlFactory.addParameter("caliber", content.getStringValue("caliber"));
        sqlFactory.addParameter("butterflyplatedrive", content.getStringValue("butterflyplatedrive"));
        sqlFactory.addParameter("connection", content.getStringValue("connection"));
        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"));

        sqlList.add(sqlFactory.getSQL());

        // 添加领域
        if (content.containsKey("tradefields")) {
            JSONArray tradefields = content.getJSONArray("tradefields");
            if (!tradefields.isEmpty()) {
                if (tradefields.size() > 1) {
                    return getErrReturnObject().setErrMsg("商品只能维护一个领域").toString();
                }
                sqlList.add("delete from plm_item_tradefield where itemid =" + itemid + " and siteid='" + siteid + "'");
                for (Object obj : tradefields) {
                    String tradefield = (String) obj;
                    sqlFactory = new SQLFactory(this, "新增领域");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("plm_item_tradefieldid", createTableID("plm_item_tradefield"));
                    sqlFactory.addParameter("tradefield", tradefield);
                    sqlFactory.addParameter("userid", userid);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlList.add(sqlFactory.getSQL());
                }

//                if (tradefields.size() > 0) {
//                    sqlFactory = new SQLFactory(this, "删除多余的领域");
//                    sqlFactory.addParameter("siteid", siteid);
//                    sqlFactory.addParameter_in("tradefield", tradefields.toArray());
//                    sqlFactory.addParameter("userid", userid);
//                    sqlFactory.addParameter("itemid", itemid);
//                    sqlList.add(sqlFactory.getSQL());
//                }
            }

        }

        if (content.containsKey("itemclassids")) {
            JSONArray itemclassids = content.getJSONArray("itemclassids");
            if (!itemclassids.isEmpty()) {
                if (itemclassids.size() > 1) {
                    return getErrReturnObject().setErrMsg("商品只能维护一个营销类别").toString();
                }
                sqlList.add("delete from sa_itemsaleclass where itemid =" + itemid + " and siteid='" + siteid + "'");
                for (Object obj : itemclassids) {
                    int itemclassid = (int) obj;
                    sqlFactory = new SQLFactory(this, "商品营销类别新增");
                    sqlFactory.addParameter("sa_itemsaleclassid", createTableID("sa_itemsaleclass"));
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlFactory.addParameter("itemno", itemno);
                    sqlFactory.addParameter("itemclassid", itemclassid);
                    sqlList.add(sqlFactory.getSQL());
                }
            }
//            sqlFactory = new SQLFactory(this, "商品营销类别删除");
//            sqlFactory.addParameter("sa_itemsaleclassid", createTableID("sa_itemsaleclass"));
//            sqlFactory.addParameter("siteid", siteid);
//            sqlFactory.addParameter("itemid", itemid);
//            sqlFactory.addParameter("itemno", itemno);
//            sqlFactory.addParameter_in("itemclassid", itemclassids.toArray());
            sqlList.add(sqlFactory.getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);

        content.put("itemid", itemid);

        return queryDetail();
    }

    @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);
        //查询erp库存
        Invbal.selectErpInvbal(this, itemid);

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

        ERPDocking erpDocking = new ERPDocking();
        JSONArray jsonArray = new JSONArray();
        if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true") && siteid.equalsIgnoreCase("ccyosg")) {
            if (!rows.toJsonArray("itemno").isEmpty()) {
                jsonArray = erpDocking.getErpIcinvbalRows(200000, 1, rows.toJsonArray("itemno"));
            }
        }
        if (!jsonArray.isEmpty()) {
            for (Object object : jsonArray) {
                JSONObject jsonObject = (JSONObject) object;
                if (!rows.isEmpty()) {
                    if (rows.get(0).getString("itemno").equals(jsonObject.getString("fitemno"))) {
                        rows.get(0).put("invbal_qty", jsonObject.getBigDecimalValue("fqty"));
                    }
                }

            }
        }
        JSONObject object = new JSONObject();
        object.put("classname", "getIcinvbal");
        object.put("method", "getMsg");

        JSONObject content = new JSONObject();
        content.put("fitemnos",  rows.toJsonArray("itemno"));
        object.put("content", content);

         WebRequest request = new WebRequest();
         String result ="";
//        String result = request.doPost(object.toString(),
//                "http://60.190.151.198:8089/BYESB/jaxrs/webclientrest");
        for (Row row : rows) {
            JSONArray jsonArrayResult = new JSONArray();
            if(isJSONArray(result)){
                 jsonArrayResult = JSONArray.parseArray(result);
            }
            if(!jsonArrayResult.isEmpty()){
                row.put("invbal_qty",((JSONObject)jsonArrayResult.get(0)).getBigDecimal("FQty"));
            }else{
                row.put("invbal_qty",0);
            }
            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()));
            row.put("tradefield", tradefieldRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
        }

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

    @API(title = "货品档案库存查询", apiversion = R.ID20230913154803.v1.class)
    public String queryInvbalQty() throws YosException {

        Long itemid = content.getLong("itemid");
        Rows rows = dbConnect.runSqlQuery("select itemno from plm_item where siteid='"+siteid+"' and itemid="+itemid);

        JSONObject object = new JSONObject();
        object.put("classname", "getIcinvbal");
        object.put("method", "getMsg");

        JSONObject content = new JSONObject();
        content.put("fitemnos",  rows.toJsonArray("itemno"));
        object.put("content", content);

        WebRequest request = new WebRequest();
        String result = request.doPost(object.toString(),
                "http://60.190.151.198:8089/BYESB/jaxrs/webclientrest");
        for (Row row : rows) {
            JSONArray jsonArrayResult = new JSONArray();
            if(isJSONArray(result)){
                jsonArrayResult = JSONArray.parseArray(result);
            }
            if(!jsonArrayResult.isEmpty()){
                row.put("invbal_qty",((JSONObject)jsonArrayResult.get(0)).getBigDecimal("FQty"));
            }else{
                row.put("invbal_qty",0);
            }
        }
        return getSucReturnObject().setData(rows.isNotEmpty() ? rows.get(0) : new Row()).toString();
    }

    @API(title = "货品档案列表", apiversion = R.ID20220923140602.v1.class)
    @CACHEING
    public String queryList() throws YosException, IOException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        boolean isExport = content.getBooleanValue("isExport");
        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 t8.erpitemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t8.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("isservice") && !"".equals(whereObject.getString("isservice"))) {
                where.append(" and(");
                where.append("t1.isservice ='").append(whereObject.getString("isservice")).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("isonsalebutnotingroup") && !"".equals(whereObject.getString("isonsalebutnotingroup"))) {
                if (whereObject.getBooleanValue("isonsalebutnotingroup")) {
                    where.append(" and(");
                    where.append("t1.isonsale =1 and t1.itemid not in (select t1.itemid from sa_itemgroupmx t1 inner join sa_itemgroup t2 on t1.sa_itemgroupid=t2.sa_itemgroupid and t1.siteid=t2.siteid where t2.isonsale=1 )");
                    where.append(")");
                }
            }
            if (whereObject.containsKey("hasprice") && !"".equals(whereObject.getString("hasprice"))) {
                if(whereObject.getBooleanValue("hasprice")){
                    where.append(" and(");
                    where.append(" exists(select * from sa_itemprice where sa_itemprice.siteid=t1.siteid and sa_itemprice.itemid=t1.itemid and price>0)");
                    where.append(")");
                }

            }

            if (whereObject.containsKey("itemclassid") && !"".equals(whereObject.getString("itemclassid"))) {
                ArrayList<Long> list = ItemClass.getSubItemClassIds(this, whereObject.getLong("itemclassid"));
                list.add(whereObject.getLong("itemclassid"));
                String sql = " and t1.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + list + "  and siteid='" + siteid + "')";
                sql = sql.replace("[", "(").replace("]", ")");
                where.append(sql);
            }

            if (whereObject.containsKey("tradefield") && !whereObject.getString("tradefield").isEmpty()) {
                where.append(" and exists(");
                where.append(" select 1 from plm_item_tradefield t3 where t3.siteid=t1.siteid and t3.itemid=t1.itemid and t3.tradefield='").append(whereObject.getString("tradefield")).append("' ");
                where.append(")");
            }

        }


//        SQLFactory sqlFactory = new SQLFactory(this, "货品档案列表", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        System.out.println(sqlFactory.getSQL());
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        QuerySQL querySQL = queryList(where.toString());
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        // 默认商品图片
        Rows defaultImageRows = getAttachmentUrl("system", (long) 1, "defaultImage");
        // 附件
        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("tradefield", tradefieldRowsMap.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()));
        }


//        if (isExport) {
//            //去除不需要导出项
//            rows.getFieldList().remove("itemid");
//            rows.getFieldList().remove("marketprice");
//            rows.getFieldList().remove("unitid");
//            rows.getFieldList().remove("createdate");
//            rows.getFieldList().remove("barcode");
//            rows.getFieldList().remove("skucontrol");
//            rows.getFieldList().remove("batchcontrol");
//            rows.getFieldList().remove("grossweight");
//            rows.getFieldList().remove("weight");
//            rows.getFieldList().remove("height");
//            rows.getFieldList().remove("width");
//            rows.getFieldList().remove("length");
//            rows.getFieldList().remove("delivery");
//            rows.getFieldList().remove("istool");
//            rows.getFieldList().remove("volume");
//            rows.getFieldList().remove("marketingcategory");
//            rows.getFieldList().remove("ismodule");
//            rows.getFieldList().remove("cheek");
//            rows.getFieldList().remove("material");
//            rows.getFieldList().remove("iswoodproducts");
//            rows.getFieldList().remove("financeclasstype");
//            rows.getFieldList().remove("delistingstatus");
//            rows.getFieldList().remove("stockno");
//            rows.getFieldList().remove("widthschemeid");
//            rows.getFieldList().remove("lengthschemeid");
//            rows.getFieldList().remove("iscustomsize");
//            rows.getFieldList().remove("customprice");
//            rows.getFieldList().remove("auxunitid");
//            rows.getFieldList().remove("unitgroupname");
//            rows.getFieldList().remove("axunitname");
//            rows.getFieldList().remove("caliber");
//            rows.getFieldList().remove("pressure");
//            rows.getFieldList().remove("butterflyplatedrive");
//            rows.getFieldList().remove("valveplatematerial");
//            rows.getFieldList().remove("bodymaterial");
//            rows.getFieldList().remove("actuatortype");
//            rows.getFieldList().remove("actuatorbrand");
//            rows.getFieldList().remove("isbutterfly");
//            rows.getFieldList().remove("erpitemno");
//            rows.getFieldList().remove("erpitemname");
//            rows.getFieldList().remove("specalnote");
//            rows.getFieldList().remove("prodline");
//            rows.getFieldList().remove("device");
//            rows.getFieldList().remove("widthschemename");
//            rows.getFieldList().remove("lengthschemename");
//            rows.getFieldList().remove("brand");
//            rows.getFieldList().remove("connection");
//            rows.getFieldList().remove("siteid");
//            rows.getFieldList().remove("createby");
//            rows.getFieldList().remove("changeuserid");
//            rows.getFieldList().remove("changeby");
//            rows.getFieldList().remove("changedate");
//            rows.getFieldList().remove("isauxunit");
//            rows.getFieldList().remove("unitgroupid");
//
//            rows.getFieldList().remove("status");
//            rows.getFieldList().remove("orderminqty_auxunit");
//            rows.getFieldList().remove("orderaddqty_auxunit");
//            rows.getFieldList().remove("checkby");
//            rows.getFieldList().remove("checkdate");
//            rows.getFieldList().remove("onsaledate");
//            rows.getFieldList().remove("onsaleby");
//            rows.getFieldList().remove("remarks");
//            rows.getFieldList().remove("standards");
//            rows.getFieldList().remove("stockstatus2");
//            rows.getFieldList().remove("stockstatus1");
//            rows.getFieldList().remove("warrantyday");
//            rows.getFieldList().remove("iswuliao");
//            rows.getFieldList().remove("isservice");
//            rows.getFieldList().remove("sa_agentsid");
//            rows.getFieldList().remove("sa_customschemeid");
//            rows.getFieldList().remove("k3outcode");
//            rows.getFieldList().remove("description");
//            rows.getFieldList().remove("price1");
//            rows.getFieldList().remove("price2");
//            rows.getFieldList().remove("price3");
//            rows.getFieldList().remove("price4");
//            rows.getFieldList().remove("price5");
//
//
//            Rows uploadRows = uploadExcelToObs("item", "商品列表", rows, getTitleMap());
//            return getSucReturnObject().setData(uploadRows).toString();
//        }

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

    //返回导出的标题
    public HashMap<String, String> getTitleMap() {
        HashMap<String, String> titleMap = new HashMap<>();
        titleMap.put("itemno", "产品编号");
        titleMap.put("itemname", "产品名称");
        titleMap.put("isonsale", "上/下架");
        titleMap.put("tradefield", "领域");
        titleMap.put("packageqty", "包装数量");
        titleMap.put("itemclass", "营销类别");
        titleMap.put("unitname", "单位");
        titleMap.put("orderminqty", "起订量");
        titleMap.put("orderaddqty", "增量");
        titleMap.put("model", "型号");
        titleMap.put("spec", "规格");
        titleMap.put("color", "颜色");

        return titleMap;
    }

    public QuerySQL queryList(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t2", "t2.unitid = t1.unitid AND t2.siteid = t1.siteid",
                "unitname");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unitgroup", "t6", "t6.unitgroupid = t1.unitgroupid",
                "auxunitid", "unitgroupname");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t7", "t7.unitid = t6.unitid AND t7.siteid = t6.siteid");
        querySQL.addQueryFields("axunitname", "t7.unitname");
        querySQL.addJoinTable(JOINTYPE.left, "plm_itemextend", "t8", "t8.itemid = t1.itemid AND t8.siteid = t1.siteid",
                "caliber", "pressure", "material", "butterflyplatedrive", "connection", "valveplatematerial", "bodymaterial", "actuatortype",
                "actuatorbrand", "isbutterfly", "erpitemno", "erpitemname", "specalnote", "prodline", "device");
        querySQL.addJoinTable(JOINTYPE.left, "sa_sizecustomizedscheme", "t4", "t1.widthschemeid=t4.sa_sizecustomizedschemeid and t1.siteid=t4.siteid");
        querySQL.addQueryFields("widthschemename", "t4.schemename ");
        querySQL.addJoinTable(JOINTYPE.left, "sa_sizecustomizedscheme", "t5", "t1.lengthschemeid=t5.sa_sizecustomizedschemeid and t1.siteid=t5.siteid");
        querySQL.addQueryFields("lengthschemename", "t5.schemename ");

        querySQL.addJoinTable(JOINTYPE.left, "sa_itemprice", "t9", "t9.itemid = t1.itemid AND t9.siteid = t1.siteid and t9.pricegrade=1");
        querySQL.addQueryFields("price1", "t9.price ");
        querySQL.addJoinTable(JOINTYPE.left, "sa_itemprice", "t10", "t10.itemid = t1.itemid AND t10.siteid = t1.siteid and t10.pricegrade=2");
        querySQL.addQueryFields("price2", "t10.price ");
        querySQL.addJoinTable(JOINTYPE.left, "sa_itemprice", "t11", "t11.itemid = t1.itemid AND t11.siteid = t1.siteid and t11.pricegrade=3");
        querySQL.addQueryFields("price3", "t11.price ");
        querySQL.addJoinTable(JOINTYPE.left, "sa_itemprice", "t12", "t12.itemid = t1.itemid AND t12.siteid = t1.siteid and t12.pricegrade=4");
        querySQL.addQueryFields("price4", "t12.price ");
        querySQL.addJoinTable(JOINTYPE.left, "sa_itemprice", "t13", "t13.itemid = t1.itemid AND t13.siteid = t1.siteid and t13.pricegrade=5");
        querySQL.addQueryFields("price5", "t13.price ");
        querySQL.addJoinTable(JOINTYPE.left, "plm_itemclass", "t14", "t14.itemclassid = t1.marketingcategory AND t14.siteid = t1.siteid");
        querySQL.addQueryFields("topitemclassname", "t14.itemclassname");

        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        return querySQL;
    }


    @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 getErrReturnObject().setErrMsg("存在已审核的数据，无法删除。").toString();
        }

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

        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");
        Rows itemRows =new Rows();
        Rows itemclassRows=new Rows();
        if(!jsonArray.isEmpty()){
             itemRows = dbConnect.runSqlQuery("SELECT t1.*,t2.material from plm_item t1 left join plm_itemextend t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.siteid='" + siteid + "' and t1.itemid in"+jsonArray.toJSONString().replace("[", "(").replace("]", ")"));
             itemclassRows = dbConnect.runSqlQuery("select itemid,itemclassid from sa_itemsaleclass  where siteid='" + siteid + "' and itemid in"+jsonArray.toJSONString().replace("[", "(").replace("]", ")"));
        }else{
            return getErrReturnObject().setErrMsg("未选择商品，无法审核").toString();
        }
        RowsMap itemRowsMap = itemRows.toRowsMap("itemid");
        RowsMap itemclassRowsMap = itemclassRows.toRowsMap("itemid");
        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : jsonArray) {
            int id = (int) obj;
            SQLFactory sqlFactory = new SQLFactory(this, "货品档案审核");
            sqlFactory.addParameter("itemid", id);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("status", status.equals("0") ? "新建" : "审核");
            if (itemRowsMap.containsKey(String.valueOf(id))) {
                if (!itemRowsMap.get(String.valueOf(id)).isEmpty()) {
                    if (itemRowsMap.get(String.valueOf(id)).get(0).getLong("sa_customschemeid")>0) {
                        Rows customschemeRows = dbConnect.runSqlQuery("SELECT  t1.*,t2.schemename from sa_customscheme_items t1 left join sa_customscheme t2 on t1.sa_customschemeid=t2.sa_customschemeid and t1.siteid=t2.siteid WHERE t1.sa_customschemeid=" + itemRowsMap.get(String.valueOf(id)).get(0).getLong("sa_customschemeid") + " and  t1.siteid ='" + siteid + "'");
                        String where = " and 1=1 and t1.itemid != " + id + " and sa_customschemeid=" + itemRowsMap.get(String.valueOf(id)).get(0).getLong("sa_customschemeid");
                        if (!customschemeRows.isEmpty()) {
                            for (Row row : customschemeRows) {

                                if (StringUtils.isBlank(itemRowsMap.get(String.valueOf(id)).get(0).getString(row.getString("value")))) {
                                    return getErrReturnObject().setErrMsg("该商品【"+itemRowsMap.get(String.valueOf(id)).get(0).getString("itemname")+"】定制方案" + row.getString("schemename") + "下" + row.getString("description") + "不可为空").toString();
                                } else {
                                    where = where + " and " + row.getString("value") + "='" +  itemRowsMap.get(String.valueOf(id)).get(0).getString(row.getString("value")) + "'";
                                }
                            }
                            //System.out.println("select * from plm_item where siteid='" + siteid + "'" + where);
                            Rows items = dbConnect.runSqlQuery("select t1.*,t2.material from plm_item t1 left join plm_itemextend t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "'" + where);
                            if (items.isNotEmpty()) {
                                return getErrReturnObject().setErrMsg("该商品与定制方案下的" + items.get(0).getString("itemno") + "商品重复，请修改").toString();
                            }
                        }
                    }
                }

            }

            if(itemclassRowsMap.containsKey(String.valueOf(id))){
                if(itemclassRowsMap.get(String.valueOf(id)).isNotEmpty()){
                    SQLFactory sqlFactory1 = new SQLFactory(this, "获取最上级营销类别名称");
                    sqlFactory1.addParameter_in("itemclassid",  itemclassRowsMap.get(String.valueOf(id)).get(0).getLong("itemclassid"));
                    sqlFactory1.addParameter("siteid", siteid);
                    Rows rows = dbConnect.runSqlQuery(sqlFactory1.getSQL());
                    if(rows.isNotEmpty()){
                        sqlList.add("update plm_item set marketingcategory='"+ rows.get(0).getString("itemclassid")+"' where siteid='"+siteid+"' and itemid="+id);
                    }
                }
            }


            if (status.equals("0")) {
                if (itemRowsMap.containsKey(String.valueOf(id))) {
                    if (!itemRowsMap.get(String.valueOf(id)).isEmpty()) {
                        if (itemRowsMap.get(String.valueOf(id)).get(0).getBoolean("isonsale")) {
                            return getErrReturnObject().setErrMsg("上架状态无法反审核").toString();
                        }
                    }

                }

            }
            sqlList.add(sqlFactory.getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

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

        JSONArray jsonArray = content.getJSONArray("itemids");
        String isonsale = content.getString("isonsale");
        if (jsonArray.size() == 0) {
            return getErrReturnObject().setErrMsg("未选择商品").toString();
        }

        if (isonsale.equals("1")) {

            SQLFactory isHas = new SQLFactory(this, "查询是否存在新建货品");
            isHas.addParameter_in("itemid", jsonArray.toArray());
            isHas.addParameter("siteid", siteid);
            Rows hasRows = dbConnect.runSqlQuery(isHas.getSQL(false));

            SQLFactory isHasOnSale = new SQLFactory(this, "查询是否存在上架货品");
            isHasOnSale.addParameter_in("itemid", jsonArray.toArray());
            isHasOnSale.addParameter("siteid", siteid);
            Rows hasOnSaleRows = dbConnect.runSqlQuery(isHasOnSale.getSQL(false));
            if (hasRows.isNotEmpty()) {
                return getErrReturnObject().setErrMsg("存在未审核的货品，无法上架！").toString();
            }
            if (hasOnSaleRows.isNotEmpty()) {
                return getErrReturnObject().setErrMsg("存在已上架的货品，无法上架！").toString();
            }
            //System.out.println("select * from sa_itemsaleclass where siteid='" + siteid + "' and itemid in " + jsonArray.toArray());
            String sql = "select * from sa_itemsaleclass where siteid='" + siteid + "' and itemid in " + jsonArray.toJSONString();
            sql = sql.replace("[", "(").replace("]", ")");
            Rows itemsaleclassRows = dbConnect.runSqlQuery(sql);

            if (itemsaleclassRows.isEmpty()) {
                return getErrReturnObject().setErrMsg("商品未维护营销类别，无法上架！").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 getErrReturnObject().setErrMsg("存在未维护价格的货品，无法上架！").toString();
            }
        }
        Rows brandRows = dbConnect.runSqlQuery("select sa_brandid from sa_brand where siteid='" + siteid + "'");
        long sa_brandid = 0;
        if (!brandRows.isEmpty()) {
            sa_brandid = brandRows.get(0).getLong("sa_brandid");
        }
        Rows itemRows = dbConnect.runSqlQuery("select itemid,itemno,itemname,sa_customschemeid,isonsale from plm_item where siteid='" + siteid + "'");
        RowsMap itemRowsMap = itemRows.toRowsMap("itemid");

        Rows tradefieldRows = dbConnect.runSqlQuery("select itemid,tradefield from plm_item_tradefield where siteid='" + siteid + "'");
        RowsMap tradefieldRowsMap = tradefieldRows.toRowsMap("itemid");

        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : jsonArray) {
            Long id = Long.valueOf(obj.toString());
            SQLFactory sqlFactory = new SQLFactory(this, "货品档案上架");
            sqlFactory.addParameter("itemid", id);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("username", username);
            sqlFactory.addParameter("isonsale", isonsale);
            sqlList.add(sqlFactory.getSQL(false));
            if (isonsale.equals("1")) {
                sqlList.add(DataContrlLog.createLog(this, "plm_item", id, "商品上架", "商品上架成功" ).getSQL());
            }else{
                sqlList.add(DataContrlLog.createLog(this, "plm_item", id, "商品下架", "商品下架成功").getSQL());
            }

            //审核商品时，自动添加至商品组中
            if (itemRowsMap.containsKey(String.valueOf(obj))) {
                if (!itemRowsMap.get(String.valueOf(obj)).isEmpty()) {
                    if (itemRowsMap.get(String.valueOf(obj)).get(0).getLong("sa_customschemeid") != 0) {
                        if (isonsale.equals("1")) {
                            Rows rowsCount = dbConnect.runSqlQuery("select count(1) count,min(groupnum) groupnum from (select ifnull(t3.sa_customschemeid,0) count,t1.sa_itemgroupid,t1.groupnum,t1.siteid from sa_itemgroup t1 inner join sa_itemgroupmx t2 on t1.siteid=t2.siteid and t1.sa_itemgroupid=t2.sa_itemgroupid inner join plm_item t3 on t2.itemid=t3.itemid and t2.siteid=t3.siteid group by t1.sa_itemgroupid,t1.groupnum,t1.siteid,ifnull(t3.sa_customschemeid,0))t where t.siteid='" + siteid + "' and t.sa_itemgroupid in (select sa_itemgroupid from sa_itemgroupmx where itemid=" + id + ")");
                            if (rowsCount.get(0).getLong("count") > 1) {
                                //return getErrReturnObject().setErrMsg("该商品存在与商品组"+rowsCount.get(0).getString("groupnum")+",请在商品组删除该商品后进行审核").toString();
                            }
                        }
                    } else {
                        if (isonsale.equals("0")) {
                            sqlList.add("delete from sa_itemgroup where sa_itemgroupid in(select sa_itemgroupid from sa_itemgroupmx where itemid=" + id + " and siteid='" + siteid + "')");
                            sqlList.add("delete from sa_itemgroupmx where itemid=" + id + " and siteid='" + siteid + "'");
                        } else {
                            sqlList.addAll(Itemgroup.createItemGroupSql(this, id, itemRowsMap.get(String.valueOf(obj)).get(0).getString("itemname"), itemRowsMap.get(String.valueOf(obj)).get(0).getString("itemno"), tradefieldRowsMap.get(String.valueOf(obj)).toJsonArray("tradefield"), sa_brandid));
                        }
                    }


                } else {
                    return getErrReturnObject().setErrMsg("商品不存在").toString();
                }

            }


        }

        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "查询商品组", apiversion = R.ID20221216102302.v1.class)
    public String selectItemGroup() 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.groupname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.brandname 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(")");
            }

        }

        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.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 = getAttachmentUrl("system", (long) 1, "defaultImage");
        // 封面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 {
        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 <= 31; 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.batchDetail(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.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("model");
            keys.add("unitname");
            keys.add("erpitemname");
            keys.add("erpitemno");
            keys.add("standards");
            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");

            Rows rows = e.getSheetRows(0, keys, 3);
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            RowsMap itemnoRowsMap = dbConnect.runSqlQuery("select itemno from plm_item t1  where siteid='" + siteid + "'").toRowsMap("itemno");
            RowsMap unitnameRowsMap = dbConnect.runSqlQuery("select unitid,unitname from plm_unit where siteid='" + siteid + "'").toRowsMap("unitname");
            ArrayList<String> tradefieldList = dbConnect.runSqlQuery("select t2.value from sys_optiontype t1 left join sys_optiontypemx t2 on t1.optiontypeid=t2.optiontypeid and t2.siteid='" + siteid + "' where t1.remarks='领域' and t2.isused=1").toArrayList("value");
            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");

            for (Row row : rows) {
                String brandname = row.getString("brandname");
                String itemclassnum = row.getString("itemclassnum");
                String itemno = row.getString("itemno");
                String unitname = row.getString("unitname");
                try {
                    row.getBigDecimal("orderminqty");
                    row.getBigDecimal("orderaddqty");
                    row.getInteger("stockstatus1");
                    row.getInteger("stockstatus2");
                    row.getInteger("delivery");
                } catch (Exception e1) {
                    iserr = true;
                    row.put("msg", "错误信息:销售起订量/销售增量/库存充足标准/库存缺货标准/销售交期填写格式错误");
                    rowserr.add(row);
                    continue;
                }
                if (StringUtils.isEmpty(itemno)
                        || StringUtils.isEmpty(row.getString("itemname"))
                        || StringUtils.isEmpty(unitname)
                        || StringUtils.isEmpty(row.getString("erpitemname"))
                        || StringUtils.isEmpty(row.getString("erpitemno"))) {
                    iserr = true;
                    row.put("msg", "错误信息:产品编号/产品名称/计量单位/品号/品号不能为空");
                    rowserr.add(row);
                    continue;
                }
                if ((StringUtils.isEmpty(brandname) && StringUtils.isNotEmpty(itemclassnum)) || (StringUtils.isNotEmpty(brandname) && StringUtils.isEmpty(itemclassnum))) {
                    iserr = true;
                    row.put("msg", "错误信息:不能单独填写品牌和营销类别");
                    rowserr.add(row);
                    continue;
                }
                if (imitemnoRowsMap.containsKey(itemno) && imitemnoRowsMap.get(itemno).size() > 1) {
                    iserr = true;
                    row.put("msg", "错误信息:本次导入商品中存在重复的商品编号");
                    rowserr.add(row);
                    continue;
                }
                if (itemnoRowsMap.containsKey(itemno)) {
                    iserr = true;
                    row.put("msg", "错误信息:商品编号已存在");
                    rowserr.add(row);
                    continue;
                }
                if (!unitnameRowsMap.containsKey(unitname)) {
                    iserr = true;
                    row.put("msg", "错误信息:计量单位不存在");
                    rowserr.add(row);
                    continue;
                }
                if (!tradefieldList.contains(row.getString("tradefield"))) {
                    iserr = true;
                    row.put("msg", "错误信息:领域不存在");
                    rowserr.add(row);
                    continue;
                }
                if (!itemclassRowsMap.containsKey(brandname) || !itemclassRowsMap.get(brandname).toArrayList("itemclassnum").contains(itemclassnum)) {
                    iserr = true;
                    row.put("msg", "错误信息:品牌不存在或该品牌下没有对应的营销类别");
                    rowserr.add(row);
                }
                row.put("unitid", unitnameRowsMap.get(unitname).get(0).getLong("unitid"));
                for (Row row1 : itemclassRowsMap.get(brandname)) {
                    if (row1.getString("itemclassnum").equals(itemclassnum)) {
                        row.put("sa_brandid", row1.getLong("sa_brandid"));
                        row.put("itemclassid", row1.getLong("itemclassid"));
                        break;
                    }
                }
                rowssuc.add(row);
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("货品档案导入错误信息");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "产品编码");
                map.put("itemname", "产品名称");
                map.put("spec", "型号");
                map.put("model", "规格尺寸");
                map.put("unitname", "计量单位");
                map.put("erpitemname", "品名");
                map.put("erpitemno", "品号");
                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("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("model");
                colNameList.add("unitname");
                colNameList.add("erpitemname");
                colNameList.add("erpitemno");
                colNameList.add("standards");
                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("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("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("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr, map);

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

            if (!rowssuc.isEmpty()) {
                for (Row row : rowssuc) {
                    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"));
                    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", row.getBigDecimal("orderminqty"));
                    // 订单增量
                    sqlFactory.addParameter("orderaddqty", row.getBigDecimal("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", row.getLong("delivery"));
                    // 牌价
                    sqlFactory.addParameter("marketprice", 0);
                    // 是否工具
                    sqlFactory.addParameter("istool", 0);
                    // 产品标准
                    sqlFactory.addParameter("standards", row.getString("standards"));
                    // 库存缺货标准
                    sqlFactory.addParameter("stockstatus2", row.getInteger("stockstatus2"));
                    // 库存充足标准
                    sqlFactory.addParameter("stockstatus1", row.getInteger("stockstatus1"));
                    // 保修期（年）
                    sqlFactory.addParameter("warrantyday", "null");
                    // 是否营销物料
                    sqlFactory.addParameter("iswuliao", 0);
                    // 是否服务物料
                    sqlFactory.addParameter("isservice", row.getString("isservice").equals("是") ? 1 : 0);
                    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("caliber", "null");
                    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").equals("是") ? 1 : 0);
                    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"));
                    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());

                    Long sa_itemsaleclassid = createTableID("sa_itemsaleclass");
                    sqlFactory = new SQLFactory(this, "新增营销类别");
                    sqlFactory.addParameter("siteid", siteid);
                    sqlFactory.addParameter("sa_itemsaleclassid", sa_itemsaleclassid);
                    sqlFactory.addParameter("itemclassid", row.getString("itemclassid"));
                    sqlFactory.addParameter("itemno", row.getString("itemno"));
                    sqlFactory.addParameter("itemid", itemid);
                    sqlList.add(sqlFactory.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());
                    sqlList.add(DataContrlLog.createLog(this, "sa_itemsaleclass", sa_itemsaleclassid, "新增", "货品档案导入").getSQL());
                }
            }

            if (!sqlList.isEmpty()) {
                dbConnect.runSqlUpdate(sqlList);
            }
        } 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) {
        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 <= 32; i++) {
            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);// 写入标题

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

}