CUCU2/src/custom/restcontroller/sale/itemgroup/itemgroup.java

package restcontroller.sale.itemgroup;

import beans.Item.Item;
import beans.datatag.DataTag;
import beans.enterprise.Enterprise;
import beans.itemclass.ItemClass;
import beans.itemgroup.Itemgroup;
import beans.itemprice.ItemPrice;
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.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;

import org.apache.commons.lang.StringUtils;
import restcontroller.R;
import restcontroller.sale.promotion.promotion;

import java.math.BigDecimal;
import java.util.*;

@API(title = "商品组")
public class itemgroup extends Controller {

    public itemgroup(JSONObject arg0) throws YosException {
        super(arg0);
        // TODO Auto-generated constructor stub
    }

    /**
     * * 查询商品组
     *
     * @return
     */
    @API(title = "查询", apiversion = R.ID20221219113503.v1.class)
    @CACHEING
    public String queryItemSaleClasses() throws YosException {
        Long istool = content.getLongValue("istool");
        Rows itemclassRows = Enterprise.getItemSaleClasses(this, sys_enterpriseid, true, istool);
        return getSucReturnObject().setData(itemclassRows).toString();

    }

    /**
     * * 查询商品组
     *
     * @return
     */
    @API(title = "查询", apiversion = R.ID20220926142203.v1.class)
    @CACHEING
    public String queryItemgroup() throws YosException {
        Long istool = content.getLongValue("istool");
        //Rows itemclassRows = Enterprise.getItemSaleClasses(this, sys_enterpriseid, true, istool);
        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        }
        /*
         *  * 过滤条件设置
         */
        String where = " 1=1 ";
        String where1 = "";
        String itemclasssql = "select itemclassid from plm_itemclass where siteid='" + siteid + "'";
        if (sys_enterpriseid > 0) {
            Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid);
            if (!enterpriseRow.isEmpty()) {
                //企业营销类别
                boolean saleclassauth = enterpriseRow.getBoolean("saleclassauth");
                if (saleclassauth) {
                    itemclasssql = "select itemclassid from plm_itemclass where siteid='" + siteid + "' and itemclassid in ( SELECT itemclassid from sys_enterprise_saleclass WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "')";
                }
                //企业领域
                Rows tradefieldrows = dbConnect.runSqlQuery("SELECT tradefield from sys_enterprise_tradefield WHERE sys_enterpriseid =" + sys_enterpriseid + " and siteid ='" + siteid + "'");
                if (!tradefieldrows.isEmpty()) {
                    for (Row row : tradefieldrows) {
                        where1 = where1 + " (t1.tradefield like'%" + row.getString("tradefield") + "%') or ";
                    }
                } else {
                    where1 = where1 + " 1=2 ";
                }
            } else {
                where1 = " 1=2 ";
            }

        } else {
            where1 = " 1=1 ";
        }
        if (where1.contains("or")) {
            where1 = where1.substring(0, where1.lastIndexOf("or"));
        }
        Rows itemclassRows = dbConnect.runSqlQuery(itemclasssql);

        ArrayList<Long> itemclassList = new ArrayList<Long>();
        for (Row row : itemclassRows) {
            //System.out.println(row.getLong("itemclassid"));
            itemclassList.add(row.getLong("itemclassid"));
            itemclassList.addAll(ItemClass.getSubItemClassIds(this, row.getLong("itemclassid")));
        }

        SQLFactory factorygroupid = new SQLFactory(this, "查询授权范围内的商品组商品");
        factorygroupid.addParameter("siteid", siteid);

        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");

            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and (t1.groupname like'%" + whereObject.getString("condition")
                        + "%' or t1.groupnum like'%" + whereObject.getString("condition")
                        + "%' or exists(select 1 from sa_itemgroupmx s1 left join plm_item s2 on s1.itemid=s2.itemid and  s1.siteid=s2.siteid where (s2.itemname like '%"
                        + whereObject.getString("condition") + "%' or s2.itemno like '%" + whereObject.getString("condition")
                        + "%' or s2.standards like '%" + whereObject.getString("condition")
                        + "%' or s2.spec like '%" + whereObject.getString("condition")
                        + "%' or s2.byname like '%" + whereObject.getString("condition")
                        + "%' or s2.model like '%" + whereObject.getString("condition")
                        + "%') and s1.sa_itemgroupid=t1.sa_itemgroupid) ) ";
            }
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where = where + " and (t1.tradefield like'%" + whereObject.getString("tradefield") + "%') ";
            }
//            else {
//                if (sys_enterpriseid > 0) {
//                    where = where + " and (t1.tradefield in (SELECT tradefield from sys_enterprise_tradefield WHERE sys_enterpriseid = " + sys_enterpriseid + " and siteid='" + siteid + "') ) ";
//                }
//            }
            if (whereObject.containsKey("standards") && !"".equals(whereObject.getString("standards"))) {
                where = where
                        + " and exists(select 1 from sa_itemgroupmx s1 left join plm_item s2 on s1.itemid=s2.itemid and  s1.siteid=s2.siteid where s2.standards='"
                        + whereObject.getString("standards") + "' and s1.sa_itemgroupid=t1.sa_itemgroupid) ";
            }
            if (whereObject.containsKey("itemclassid") && !"".equals(whereObject.getString("itemclassid"))) {

                ArrayList<Long> list = ItemClass.getSubItemClassIds(this, whereObject.getLong("itemclassid"));
                list.add(whereObject.getLong("itemclassid"));
                ArrayList<Long> listNew = new ArrayList<>();
                listNew.add((long) 0);
                for (long itemclassid : list) {
                    if (itemclassList.contains(itemclassid)) {
                        listNew.add(itemclassid);
                    }
                }

                factorygroupid.addParameter_in("itemclassid", listNew);

            } else {
                factorygroupid.addParameter_in("itemclassid",
                        itemclassList);
            }
        } else {
            factorygroupid.addParameter_in("itemclassid",
                    itemclassList);
        }

        JSONArray brandids = content.getJSONArray("brandids");
        if (brandids.isEmpty()) {
            Rows rows;
            if (sys_enterpriseid > 0) {
                rows = Enterprise.getBrands(this, sys_enterpriseid);
            } else {
                rows = dbConnect.runSqlQuery("select sa_brandid from sa_brand where siteid='" + siteid + "'");
            }
            if (!rows.isEmpty()) {
                for (Row row : rows) {
                    brandids.add(row.getLong("sa_brandid"));
                }
            } else {
                brandids.add(0);
            }

        }

        factorygroupid.addParameter("siteid", siteid);
        Enterprise.EnterpriseGrade enterpriseGrade = Enterprise.getEnterpriseGrade(this, sys_enterpriseid);
        if (enterpriseGrade != null) {
            factorygroupid.addParameter("pricegrade", enterpriseGrade.getPricegrade());
        }else {
            Rows siteParameterRows =dbConnect.runSqlQuery("select defaultlevelprice from sys_site_parameter where siteid='"+siteid+"'");
            if(!siteParameterRows.isEmpty()){
                if(StringUtils.isBlank(siteParameterRows.get(0).getString("defaultlevelprice"))){
                    factorygroupid.addParameter("pricegrade", "1");
                }else{
                    factorygroupid.addParameter("pricegrade", siteParameterRows.get(0).getString("defaultlevelprice"));
                }
            }else{
                factorygroupid.addParameter("pricegrade", "1");
            }
        }
        Rows rowsgroupids = dbConnect.runSqlQuery(factorygroupid.getSQL());
        RowsMap itemgroupRowMap = rowsgroupids.toRowsMap("sa_itemgroupid");
        /*
         * SQL查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "商品组列表查询", pageSize, pageNumber, pageSorting);
        factory.addParameter("siteid", siteid);
        factory.addParameter_in("sa_brandid", brandids.toArray());
        factory.addParameter_in("sa_itemgroupids", rowsgroupids.toArrayList("sa_itemgroupid", new ArrayList<Long>()));
        factory.addParameter_SQL("where", where);
        factory.addParameter_SQL("where1", where1);
        String sql = factory.getSQL();
        //System.out.println(sql);
        Rows rows = dbConnect.runSqlQuery(sql);
        ArrayList<Long> itemgroupids = rows.toArrayList("sa_itemgroupid", new ArrayList<Long>());
        RowsMap itemgroupScemeRowsMap = Itemgroup.getItemgroupScemeid(this, itemgroupids);

        for (Row row2 : rows) {
            long sa_itemgroupid = row2.getLong("sa_itemgroupid");
            ArrayList<String> list = DataTag.queryTag(this, "sa_itemgroup", sa_itemgroupid, false);
            row2.put("tag1", JSONArray.parseArray(JSON.toJSONString(list)));
//            HashMap<Long, ItemPrice> ItemPriceList = ItemPrice.getItemGroupPrice(this, sys_enterpriseid,
//                    row2.getLong("sa_itemgroupid"));
//            BigDecimal max = new BigDecimal(0);
//            BigDecimal min = ((ItemPrice) ItemPriceList.values().toArray()[0]).getPrice();
//            for (ItemPrice itemPrice : ItemPriceList.values()) {
//                BigDecimal itemprice = itemPrice.getPrice();
//                if (itemprice.compareTo(max) >0) {
//                    max = itemprice;
//                }
//                if (itemprice.compareTo(min) <0) {
//                    min = itemprice;
//                }
//            }
            Rows rows1 = itemgroupRowMap.get(String.valueOf(sa_itemgroupid));
            if (rows1.isEmpty()) {
                row2.put("maxprice", 0);
                row2.put("minprice", 0);
            } else {
                row2.put("maxprice", rows1.get(0).getBigDecimal("maxprice"));
                row2.put("minprice", rows1.get(0).getBigDecimal("minprice"));
            }


            Long id = sa_itemgroupid;
            if (itemgroupScemeRowsMap.get(id.toString()).isNotEmpty()) {
                if (itemgroupScemeRowsMap.get(id.toString()).get(0).getLong("sa_customschemeid") > 0) {
                    row2.put("isscheme", true);
                } else {
                    row2.put("isscheme", false);
                }

            } else {
                row2.put("isscheme", false);
            }
        }

        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        /**
         * 查询商品明细
         */
        SQLFactory sqlFactory = new SQLFactory(this, "查询商品列表");
        sqlFactory.addParameter_in("itemids", ids);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        Rows itemrows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        for (Row row : itemrows) {
            ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
            row.put("oldprice", itemPrice.getPrice());
            if (sys_enterpriseid > 0 || (usertype == 21 || usertype == 22)) {
                row.put("gradeprice", itemPrice.getContractprice());
            } else {
                row.put("gradeprice", itemPrice.getMarketprice());
            }
        }
        RowsMap itemRowsMap = itemrows.toRowsMap("itemid");

        //默认商品图片
        Rows defaultImageRows = Item.getItemdefaultImage(this);
        // 封面cover
        RowsMap coverRowsMap = getAttachmentUrl("plm_item", ids, "cover");
        // 附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        // 附件
        RowsMap itemgroupRowsMap = getAttachmentUrl("sa_itemgroup", itemgroupids);


        Rows rowsitemclass = dbConnect.runSqlQuery(
                " select  t7.itemclassname,t6.itemid,t8.brandname,t7.itemclassfullname from sa_itemsaleclass t6 inner JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='"
                        + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");
        for (Row row : rows) {
            Rows itemRows = itemRowsMap.get(row.getString("itemid"));
            row.put("item", itemRows);

            Rows coverRows = coverRowsMap.get(row.getString("itemid"));
            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"));
            }

            if ((itemgroupRowsMap.getOrDefault(row.getString("sa_itemgroupid"), new Rows())).isNotEmpty()) {
                row.put("attinfos", itemgroupRowsMap.getOrDefault(row.getString("sa_itemgroupid"), new Rows()));
            } else {
                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("itemclass", itemclassRowsMap.get(row.getString("itemid")));
        }

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

    }

    @API(title = "商品组详情", apiversion = R.ID20220926142303.v1.class)
    public String queryItemgroupMain() throws YosException {
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
        SQLFactory sqlFactory = new SQLFactory(this, "商品组详情查询");
        sqlFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Rows rowsitemclass = dbConnect.runSqlQuery(
                " select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 inner JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='"
                        + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");
        if (!rows.isEmpty()) {
            ArrayList<String> list = DataTag.queryTag(this, "sa_itemgroup", rows.get(0).getLong("sa_itemgroupid"),
                    false);
            rows.get(0).put("tag1", JSONArray.parseArray(JSON.toJSONString(list)));
            rows.get(0).put("itemclass", itemclassRowsMap.get(rows.get(0).getString("itemid")));
        }
        return getSucReturnObject().setData(rows.size() > 0 ? rows.get(0) : new Row()).toString();
    }

    @API(title = "商品组商品明细列表", apiversion = R.ID20220926142403.v1.class)
    @CACHEING
    public String queryItemgroupMxList() throws YosException {
        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        }
        /*
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + "and  t2.itemname like'%" + whereObject.getString("condition") + "%'";
            }
        }
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
//		String hrid = content.getString("hrid");
        SQLFactory sqlFactory = new SQLFactory(this, "商品组商品明细查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);


        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());

        //默认商品图片
        Rows defaultImageRows = Item.getItemdefaultImage(this);
        // 封面cover
        RowsMap coverRowsMap = getAttachmentUrl("plm_item", ids, "cover");
        // 附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);

        Rows technicalinforows = dbConnect.runSqlQuery("select plm_technicalinfoid,itemid from plm_technicalinfo_item");
        RowsMap technicalinfoRowsMap = technicalinforows.toRowsMap("itemid");
        Rows technicalinforows1 = dbConnect.runSqlQuery("select plm_technicalinfoid from plm_technicalinfo");
        ArrayList<Long> plm_technicalinfoids = technicalinforows1.toArrayList("plm_technicalinfoid", new ArrayList<>());
        // 产品技术资料附件
        RowsMap plm_technicalinfoRowsMap = getAttachmentUrl("plm_technicalinfo", plm_technicalinfoids);
        // 商品领域
        RowsMap tradefieldRowsMap = beans.Item.Item.getTradefieldRowsMap(this, ids);
        Rows rowsitemclass = dbConnect.runSqlQuery(
                " select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 LEFT JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='"
                        + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");

        RowsMap itemextendRowsMap = dbConnect.runSqlQuery("select itemid,material,device,prodline,specalnote,pressure,connection,valveplatematerial,actuatortype,actuatorbrand,butterflyplatedrive from plm_itemextend where siteid='" + siteid + "'").toRowsMap("itemid");

        SQLFactory priceSqlFactory = new SQLFactory(this, "查询商品原价");
        priceSqlFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
        priceSqlFactory.addParameter("siteid", siteid);
        Rows siteParameterRows =dbConnect.runSqlQuery("select defaultlevelprice from sys_site_parameter where siteid='"+siteid+"'");
        if(!siteParameterRows.isEmpty()){
            if(StringUtils.isBlank(siteParameterRows.get(0).getString("defaultlevelprice"))){
                priceSqlFactory.addParameter("pricegrade", "1");
            }else{
                priceSqlFactory.addParameter("pricegrade", siteParameterRows.get(0).getString("defaultlevelprice"));
            }
        }else{
            priceSqlFactory.addParameter("pricegrade", "1");
        }
        Rows pricerows = dbConnect.runSqlQuery(priceSqlFactory);
        RowsMap pricerowsMap = pricerows.toRowsMap("itemid");
        for (Row row : rows) {

            ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
            row.put("oldprice", itemPrice.getMarketprice());
            if (sys_enterpriseid > 0 || (usertype == 21 || usertype == 22)) {
                row.put("gradeprice", itemPrice.getContractprice());
            } else {
                if(pricerowsMap.containsKey(row.getString("itemid"))){
                    if(pricerowsMap.get(row.getString("itemid")).isNotEmpty()){
                        row.put("gradeprice", pricerowsMap.get(row.getString("itemid")).get(0).getBigDecimal("price"));
                    }else{
                        row.put("gradeprice",0);
                    }
                }else {
                    row.put("gradeprice", 0);
                }
            }

            Rows jsonArray = new Rows();
            Rows coverRows = coverRowsMap.get(row.getString("itemid"));
            if (coverRows.isEmpty()) {
                row.put("cover", "");
            } else {
                row.put("cover", coverRows.get(0).getString("url"));
            }
            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("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));

            row.put("itemclass", itemclassRowsMap.get(row.getString("itemid")));
            Rows technicalinforowsforitem = technicalinfoRowsMap.get(row.getString("itemid"));
            for (Row row2 : technicalinforowsforitem) {
                jsonArray.addAll(plm_technicalinfoRowsMap.get(row2.getString("plm_technicalinfoid")));
            }
            row.put("technicalinfo", jsonArray);
            row.put("tradefield", tradefieldRowsMap.getOrDefault(row.getString("itemid"), new Rows()));

            if (itemextendRowsMap.containsKey(row.getString("itemid"))) {
                row.put("itemextend", itemextendRowsMap.get(row.getString("itemid")));
            } else {
                row.put("itemextend", new Rows());
            }
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "商品组列表", apiversion = R.ID20221223165503.v1.class)
    @CACHEING
    public String querymx() throws YosException {
        Set<String> listParmBlank = new LinkedHashSet<String>();
        Set<String> listParmNotBlank = new LinkedHashSet<String>();
        Set<String> firstcolorList = new LinkedHashSet();
        Set<String> firstspecList = new LinkedHashSet();
        Set<String> firstmaterialList = new LinkedHashSet();
        Set<String> firstcheekList = new LinkedHashSet();

        Set<parm> colorList = new LinkedHashSet();
        Set<parm> specList = new LinkedHashSet();
        Set<parm> materialList = new LinkedHashSet();
        Set<parm> cheekList = new LinkedHashSet();
        String color = content.getStringValue("color");
        String spec = content.getStringValue("spec");
        String material = content.getStringValue("material");
        String cheek = content.getStringValue("cheek");


        /**
         * SQL商品组明细查询参数设置并查询
         */
        Rows rows = null;
        SQLFactory factory = new SQLFactory(this, "商品组明细查询");
        factory.addParameter("sa_itemgroupid", content.getString("sa_itemgroupid"));
        factory.addParameter("siteid", siteid);
        String where1 = " 1=1 ";
        Rows tradefieldRows = dbConnect.runSqlQuery("SELECT DISTINCT t1.tradefield from sys_enterprise_tradefield t1  WHERE t1.sys_enterpriseid = " + sys_enterpriseid + " and t1.siteid='" + siteid + "'");

        if (!tradefieldRows.isEmpty()) {
            for (Row row : tradefieldRows) {
                where1 = where1 + " or t4.tradefield like '%" + row.getString("tradefield") + "%' ";
            }
        }
        factory.addParameter_SQL("where1", where1);
        Rows customschemeItems = dbConnect.runSqlQuery("select * from (select distinct value,description,isonlydisplay,sequence from sa_customscheme_items where  sa_customschemeid in(select t2.sa_customschemeid from sa_itemgroupmx t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.sa_itemgroupid=" + content.getString("sa_itemgroupid") + "))t order by t.sequence asc");
        Rows customschemeItems1 = dbConnect.runSqlQuery("select * from (select distinct value,description,isonlydisplay,sequence from sa_customscheme_items where isonlydisplay=1 and sa_customschemeid in(select t2.sa_customschemeid from sa_itemgroupmx t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.sa_itemgroupid=" + content.getString("sa_itemgroupid") + "))t order by t.sequence asc");
        RowsMap customschemeItems1RowsMap = customschemeItems1.toRowsMap("value");
        if (!customschemeItems.isEmpty()) {
            for (Row row : customschemeItems) {

                if (StringUtils.isNotBlank(row.getString("value"))) {
                    if (StringUtils.isNotBlank(content.getString(row.getString("value")))) {
                        listParmNotBlank.add(row.getString("value"));
                    } else {
                        listParmBlank.add(row.getString("value"));
                    }
                }
            }
        }
        Set<String> listAll = new LinkedHashSet<String>();
        listAll.addAll(listParmBlank);
        listAll.addAll(listParmNotBlank);
        factory.addParameter_SQL("where", "1=1");
        rows = dbConnect.runSqlQuery(factory.getSQL());
        Rows rowsall=rows;
        for (Row row : rows) {
            for (String value : listAll) {
                if (value.equals("color")) {
                    firstcolorList.add(row.getString("color"));
                } else if (value.equals("spec")) {
                    firstspecList.add(row.getString("spec"));
                } else if (value.equals("material")) {
                    firstmaterialList.add(row.getString("material"));
                } else if (value.equals("cheek")) {
                    firstcheekList.add(row.getString("cheek"));
                }

            }
        }


        JSONArray colormap = new JSONArray();
        JSONArray specmap = new JSONArray();
        JSONArray materialmap = new JSONArray();
        JSONArray cheekmap = new JSONArray();
        if (listParmNotBlank == null || listParmNotBlank.size() == 0) {
            factory.addParameter_SQL("where", "1=1");
            for (Row row : rows) {
                for (String value : listParmBlank) {
                    if (value.equals("color")) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("color"));
                        jsonObject.put("flag", true);
                        colormap.add(jsonObject);
                    } else if (value.equals("spec")) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("spec"));
                        jsonObject.put("flag", true);
                        specmap.add(jsonObject);
                    } else if (value.equals("material")) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("material"));
                        jsonObject.put("flag", true);
                        materialmap.add(jsonObject);
                    } else if (value.equals("cheek")) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("cheek"));
                        jsonObject.put("flag", true);
                        cheekmap.add(jsonObject);
                    }

                }
            }
        } else if (listParmBlank == null || listParmBlank.size() == 0) {
//            for (Row row : rows) {
//                for (String value : listParmNotBlank) {
//                    if (value.equals("color")) {
//                        JSONObject jsonObject =new JSONObject();
//                        jsonObject.put("parm", row.getString("color"));
//                        jsonObject.put("flag", true);
//                        colormap.add(jsonObject);
//                    } else if (value.equals("spec")) {
//                        JSONObject jsonObject =new JSONObject();
//                        jsonObject.put("parm", row.getString("spec"));
//                        jsonObject.put("flag", true);
//                        specmap.add(jsonObject);
//                    } else if (value.equals("material")) {
//                        JSONObject jsonObject =new JSONObject();
//                        jsonObject.put("parm", row.getString("material"));
//                        jsonObject.put("flag", true);
//                        materialmap.add(jsonObject);
//                    } else if (value.equals("cheek")) {
//                        JSONObject jsonObject =new JSONObject();
//                        jsonObject.put("parm", row.getString("cheek"));
//                        jsonObject.put("flag", true);
//                        cheekmap.add(jsonObject);
//                    }
//
//                }
//            }
            String colorwhere = "";
            String specwhere = "";
            String materialwhere = "";
            String cheekwhere = "";
            for (String value : listParmNotBlank) {
                if (value.equals("color")) {
                    colorwhere = " and " + value + "='" + content.getString(value) + "' ";
                } else if (value.equals("spec")) {
                    specwhere = " and " + value + "='" + content.getString(value) + "' ";
                } else if (value.equals("material")) {
                    materialwhere = " and " + value + "='" + content.getString(value) + "' ";
                } else if (value.equals("cheek")) {
                    cheekwhere = " and " + value + "='" + content.getString(value) + "' ";
                }
            }
            for (String value : listAll) {
                if (value.equals("color")) {
                    factory.addParameter_SQL("where", " 1=1 " + specwhere + materialwhere + cheekwhere);
                    //System.out.println(" 1=1 " + specwhere + materialwhere + cheekwhere);
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("color"));
                            jsonObject.put("flag", true);
                            colormap.add(jsonObject);
                        }
                    }
                } else if (value.equals("spec")) {
                    factory.addParameter_SQL("where", " 1=1 " + colorwhere + materialwhere + cheekwhere);
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("spec"));
                            jsonObject.put("flag", true);
                            specmap.add(jsonObject);
                        }
                    }
                } else if (value.equals("material")) {
                    factory.addParameter_SQL("where", " 1=1 " + colorwhere + specwhere + cheekwhere);
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("material"));
                            jsonObject.put("flag", true);
                            materialmap.add(jsonObject);
                        }
                    }
                } else if (value.equals("cheek")) {
                    factory.addParameter_SQL("where", " 1=1 " + colorwhere + specwhere + materialwhere);
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("cheek"));
                            jsonObject.put("flag", true);
                            cheekmap.add(jsonObject);
                        }
                    }
                }
            }

            for (String firstcolor : firstcolorList) {
                boolean isexistParm = false;
                for (Object object : colormap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstcolor)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", firstcolor);
                    jsonObject.put("flag", false);
                    colormap.add(jsonObject);
                }
            }
            for (String firstspec : firstspecList) {
                boolean isexistParm = false;
                for (Object object : specmap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstspec)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", firstspec);
                    jsonObject.put("flag", false);
                    specmap.add(jsonObject);
                }
            }
            for (String firstmaterial : firstmaterialList) {
                boolean isexistParm = false;
                for (Object object : materialmap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstmaterial)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", firstmaterial);
                    jsonObject.put("flag", false);
                    materialmap.add(jsonObject);
                }
            }
            for (String firstcheek : firstcheekList) {
                boolean isexistParm = false;
                for (Object object : cheekmap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstcheek)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", firstcheek);
                    jsonObject.put("flag", false);
                    cheekmap.add(jsonObject);
                }
            }

            factory.addParameter_SQL("where", " 1=1 " + colorwhere + specwhere + materialwhere + cheekwhere);
        } else if(listParmNotBlank.size()==1){
            String where = " 1=1 ";
            for (String value : listParmNotBlank) {
                if (value.equals("color")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("color"));
                    jsonObject.put("flag", true);
                    colormap.add(jsonObject);
//                    for (String firstcolor : firstcolorList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstcolor.equals(content.getString("color"))) {
//                            jsonObject1.put("parm",firstcolor);
//                            jsonObject1.put("flag", true);
//                            colormap.add(jsonObject1);
//                        }
//                    }
                } else if (value.equals("spec")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("spec"));
                    jsonObject.put("flag", true);
                    specmap.add(jsonObject);
//                    for (String firstspec : firstspecList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstspec.equals(content.getString("spec"))) {
//                            jsonObject1.put("parm",firstspec);
//                            jsonObject1.put("flag", true);
//                            specmap.add(jsonObject1);
//                        }
//                    }
                } else if (value.equals("material")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("material"));
                    jsonObject.put("flag", true);
                    materialmap.add(jsonObject);
//                    for (String firstmaterial : firstmaterialList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstmaterial.equals(content.getString("material"))) {
//                            jsonObject1.put("parm",firstmaterial);
//                            jsonObject1.put("flag", true);
//                            materialmap.add(jsonObject1);
//                        }
//                    }
                } else if (value.equals("cheek")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("cheek"));
                    jsonObject.put("flag", true);
                    cheekmap.add(jsonObject);
//                    for (String firstcheek : firstcheekList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstcheek.equals(content.getString("cheek"))) {
//                            jsonObject1.put("parm", firstcheek);
//                            jsonObject1.put("flag", true);
//                            cheekmap.add(jsonObject1);
//                        }
//                    }
                }
                where = where + " and " + value + "='" + content.getString(value) + "' ";
            }
            factory.addParameter_SQL("where", where);
            for (String value : listAll) {
                if (value.equals("color")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("color"));
                            jsonObject.put("flag", true);
                            colormap.add(jsonObject);
                        }
                    }

                } else if (value.equals("spec")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("spec"));
                            jsonObject.put("flag", true);
                            specmap.add(jsonObject);
                        }
                    }
                } else if (value.equals("material")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("material"));
                            jsonObject.put("flag", true);
                            materialmap.add(jsonObject);
                        }
                    }
                } else if (value.equals("cheek")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("cheek"));
                            jsonObject.put("flag", true);
                            cheekmap.add(jsonObject);
                        }
                    }
                }

            }

            List<String> list = new ArrayList<>(listParmNotBlank);
            for (String firstcolor : firstcolorList) {
                boolean isexistParm = false;
                for (Object object : colormap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstcolor)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    if(list.get(0).equals("color")){
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcolor);
                        jsonObject.put("flag", true);
                        colormap.add(jsonObject);
                    }else{
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcolor);
                        jsonObject.put("flag", false);
                        colormap.add(jsonObject);
                    }
                }
            }
            for (String firstspec : firstspecList) {
                boolean isexistParm = false;
                for (Object object : specmap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstspec)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    if(list.get(0).equals("spec")){
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstspec);
                        jsonObject.put("flag", true);
                        specmap.add(jsonObject);
                    }else{
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstspec);
                        jsonObject.put("flag", false);
                        specmap.add(jsonObject);
                    }
                }
            }
            for (String firstmaterial : firstmaterialList) {
                boolean isexistParm = false;
                for (Object object : materialmap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstmaterial)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    if(list.get(0).equals("material")){
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstmaterial);
                        jsonObject.put("flag", true);
                        materialmap.add(jsonObject);
                    }else{
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstmaterial);
                        jsonObject.put("flag", false);
                        materialmap.add(jsonObject);
                    }
                }
            }
            for (String firstcheek : firstcheekList) {
                boolean isexistParm = false;
                for (Object object : cheekmap) {
                    JSONObject jsonObject = (JSONObject) object;
                    if (jsonObject.getString("parm").equals(firstcheek)) {
                        isexistParm = true;
                        break;
                    }
                }
                if (!isexistParm) {
                    if(list.get(0).equals("cheek")){
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcheek);
                        jsonObject.put("flag", true);
                        cheekmap.add(jsonObject);
                    }else{
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcheek);
                        jsonObject.put("flag", false);
                        cheekmap.add(jsonObject);
                    }
                }
            }
        }else {
            String where = " 1=1 ";
            for (String value : listParmNotBlank) {
                if (value.equals("color")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("color"));
                    jsonObject.put("flag", true);
                    colormap.add(jsonObject);
//                    for (String firstcolor : firstcolorList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstcolor.equals(content.getString("color"))) {
//                            jsonObject1.put("parm",firstcolor);
//                            jsonObject1.put("flag", true);
//                            colormap.add(jsonObject1);
//                        }
//                    }
                } else if (value.equals("spec")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("spec"));
                    jsonObject.put("flag", true);
                    specmap.add(jsonObject);
//                    for (String firstspec : firstspecList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstspec.equals(content.getString("spec"))) {
//                            jsonObject1.put("parm",firstspec);
//                            jsonObject1.put("flag", true);
//                            specmap.add(jsonObject1);
//                        }
//                    }
                } else if (value.equals("material")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("material"));
                    jsonObject.put("flag", true);
                    materialmap.add(jsonObject);
//                    for (String firstmaterial : firstmaterialList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstmaterial.equals(content.getString("material"))) {
//                            jsonObject1.put("parm",firstmaterial);
//                            jsonObject1.put("flag", true);
//                            materialmap.add(jsonObject1);
//                        }
//                    }
                } else if (value.equals("cheek")) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("parm", content.getString("cheek"));
                    jsonObject.put("flag", true);
                    cheekmap.add(jsonObject);
//                    for (String firstcheek : firstcheekList) {
//                        JSONObject jsonObject1 =new JSONObject();
//                        if (!firstcheek.equals(content.getString("cheek"))) {
//                            jsonObject1.put("parm", firstcheek);
//                            jsonObject1.put("flag", true);
//                            cheekmap.add(jsonObject1);
//                        }
//                    }
                }
                where = where + " and " + value + "='" + content.getString(value) + "' ";
            }
            factory.addParameter_SQL("where", where);
            for (String value : listAll) {
                if (value.equals("color")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("color"));
                            jsonObject.put("flag", true);
                            colormap.add(jsonObject);
                        }
                    }

                } else if (value.equals("spec")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("spec"));
                            jsonObject.put("flag", true);
                            specmap.add(jsonObject);
                        }
                    }
                } else if (value.equals("material")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("material"));
                            jsonObject.put("flag", true);
                            materialmap.add(jsonObject);
                        }
                    }
                } else if (value.equals("cheek")) {
                    rows = dbConnect.runSqlQuery(factory.getSQL());
                    if (!rows.isEmpty()) {
                        for (Row row : rows) {
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("parm", row.getString("cheek"));
                            jsonObject.put("flag", true);
                            cheekmap.add(jsonObject);
                        }
                    }
                }

            }

            if(customschemeItems1RowsMap.containsKey("color")){
                where=" 1=1 ";
                for (String value : listParmNotBlank) {
                    if(!value.equals("color")){
                        where = where + " and " + value + "='" + content.getString(value) + "' ";
                    }
                }
                factory.addParameter_SQL("where", where);
                rows = dbConnect.runSqlQuery(factory.getSQL());
                if (!rows.isEmpty()) {
                    for (Row row : rows) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("color"));
                        jsonObject.put("flag", true);
                        colormap.add(jsonObject);
                    }
                }
                for (String firstcolor : firstcolorList) {
                    boolean isexistParm = false;
                    for (Object object : colormap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstcolor)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcolor);
                        jsonObject.put("flag", false);
                        colormap.add(jsonObject);
                    }
                }
            }else{
                for (String firstcolor : firstcolorList) {
                    boolean isexistParm = false;
                    for (Object object : colormap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstcolor)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcolor);
                        jsonObject.put("flag", false);
                        colormap.add(jsonObject);
                    }
                }
            }
            if(customschemeItems1RowsMap.containsKey("spec")){
                where=" 1=1 ";
                for (String value : listParmNotBlank) {
                    if(!value.equals("spec")){
                        where = where + " and " + value + "='" + content.getString(value) + "' ";
                    }
                }
                factory.addParameter_SQL("where", where);
                rows = dbConnect.runSqlQuery(factory.getSQL());
                if (!rows.isEmpty()) {
                    for (Row row : rows) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("spec"));
                        jsonObject.put("flag", true);
                        colormap.add(jsonObject);
                    }
                }
                for (String firstspec : firstspecList) {
                    boolean isexistParm = false;
                    for (Object object : specmap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstspec)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstspec);
                        jsonObject.put("flag", false);
                        specmap.add(jsonObject);
                    }
                }
            }else{
                for (String firstspec : firstspecList) {
                    boolean isexistParm = false;
                    for (Object object : specmap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstspec)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstspec);
                        jsonObject.put("flag", false);
                        specmap.add(jsonObject);
                    }
                }
            }
            if(customschemeItems1RowsMap.containsKey("material")){
                where=" 1=1 ";
                for (String value : listParmNotBlank) {
                    if(!value.equals("material")){
                        where = where + " and " + value + "='" + content.getString(value) + "' ";
                    }
                }
                factory.addParameter_SQL("where", where);
                rows = dbConnect.runSqlQuery(factory.getSQL());
                if (!rows.isEmpty()) {
                    for (Row row : rows) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("material"));
                        jsonObject.put("flag", true);
                        colormap.add(jsonObject);
                    }
                }
                for (String firstmaterial : firstmaterialList) {
                    boolean isexistParm = false;
                    for (Object object : materialmap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstmaterial)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstmaterial);
                        jsonObject.put("flag", false);
                        materialmap.add(jsonObject);
                    }
                }
            }else{
                for (String firstmaterial : firstmaterialList) {
                    boolean isexistParm = false;
                    for (Object object : materialmap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstmaterial)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstmaterial);
                        jsonObject.put("flag", false);
                        materialmap.add(jsonObject);
                    }
                }
            }
            if(customschemeItems1RowsMap.containsKey("cheek")){
                where=" 1=1 ";
                for (String value : listParmNotBlank) {
                    if(!value.equals("cheek")){
                        where = where + " and " + value + "='" + content.getString(value) + "' ";
                    }
                }
                factory.addParameter_SQL("where", where);
                rows = dbConnect.runSqlQuery(factory.getSQL());
                if (!rows.isEmpty()) {
                    for (Row row : rows) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", row.getString("cheek"));
                        jsonObject.put("flag", true);
                        colormap.add(jsonObject);
                    }
                }
                for (String firstcheek : firstcheekList) {
                    boolean isexistParm = false;
                    for (Object object : cheekmap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstcheek)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcheek);
                        jsonObject.put("flag", false);
                        cheekmap.add(jsonObject);
                    }
                }
            }else{
                for (String firstcheek : firstcheekList) {
                    boolean isexistParm = false;
                    for (Object object : cheekmap) {
                        JSONObject jsonObject = (JSONObject) object;
                        if (jsonObject.getString("parm").equals(firstcheek)) {
                            isexistParm = true;
                            break;
                        }
                    }
                    if (!isexistParm) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("parm", firstcheek);
                        jsonObject.put("flag", false);
                        cheekmap.add(jsonObject);
                    }
                }
            }
        }


        colormap = jsonArraySortByField(colormap.toJSONString(), "parm", "0");
        specmap = jsonArraySortByField(specmap.toJSONString(), "parm", "0");
        materialmap = jsonArraySortByField(materialmap.toJSONString(), "parm", "0");
        cheekmap = jsonArraySortByField(cheekmap.toJSONString(), "parm", "0");


        for (Object object : colormap) {
            boolean flag = false;
            parm parm = JSON.parseObject(((JSONObject) object).toJSONString(), parm.class);
            for (parm parm1 : colorList) {
                if (parm1.getParm().equals(parm.getParm())) {
                    flag = true;
                    break;
                }
            }
            if (!flag) {
                colorList.add(parm);
            }
        }
        for (Object object : specmap) {
            boolean flag = false;
            parm parm = JSON.parseObject(((JSONObject) object).toJSONString(), parm.class);
            for (parm parm1 : specList) {
                if (parm1.getParm().equals(parm.getParm())) {
                    flag = true;
                    break;
                }
            }
            if (!flag) {
                specList.add(parm);
            }
        }
        for (Object object : materialmap) {
            boolean flag = false;
            parm parm = JSON.parseObject(((JSONObject) object).toJSONString(), parm.class);
            for (parm parm1 : materialList) {
                if (parm1.getParm().equals(parm.getParm())) {
                    flag = true;
                    break;
                }
            }
            if (!flag) {
                materialList.add(parm);
            }
        }
        for (Object object : cheekmap) {
            boolean flag = false;
            parm parm = JSON.parseObject(((JSONObject) object).toJSONString(), parm.class);
            for (parm parm1 : cheekList) {
                if (parm1.getParm().equals(parm.getParm())) {
                    flag = true;
                    break;
                }
            }
            if (!flag) {
                cheekList.add(parm);
            }
        }
//        colorList.addAll(JSONObject.parseArray(colormap.toJSONString(), parm.class));
//        specList.addAll(JSONObject.parseArray(specmap.toJSONString(), parm.class));
//        materialList.addAll(JSONObject.parseArray(materialmap.toJSONString(), parm.class));
//        cheekList.addAll(JSONObject.parseArray(cheekmap.toJSONString(), parm.class));
        rows = dbConnect.runSqlQuery(factory.getSQL());

        String itemno = "";
        if (!rows.isEmpty()) {
            itemno = rows.get(0).getString("itemno");
        }

        /**
         * 查询商品明细
         */

        SQLFactory priceSqlFactory = new SQLFactory(this, "查询商品原价");
        priceSqlFactory.addParameter("sa_itemgroupid", content.getString("sa_itemgroupid"));
        priceSqlFactory.addParameter("siteid", siteid);
        Rows siteParameterRows =dbConnect.runSqlQuery("select defaultlevelprice from sys_site_parameter where siteid='"+siteid+"'");
        if(!siteParameterRows.isEmpty()){
            if(StringUtils.isBlank(siteParameterRows.get(0).getString("defaultlevelprice"))){
                priceSqlFactory.addParameter("pricegrade", "1");
            }else{
                priceSqlFactory.addParameter("pricegrade", siteParameterRows.get(0).getString("defaultlevelprice"));
            }
        }else{
            priceSqlFactory.addParameter("pricegrade", "1");
        }
        Rows pricerows = dbConnect.runSqlQuery(priceSqlFactory);
        RowsMap pricerowsMap = pricerows.toRowsMap("itemid");

        SQLFactory sqlFactory = new SQLFactory(this, "查询商品详情", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", "t2.itemno='" + itemno + "' ");
        sqlFactory.addParameter("itemno", itemno);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        Rows itemrows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        for (Row row : itemrows) {
            row.put("customschemeItems",customschemeItems);
            ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
            row.put("oldprice", itemPrice.getPrice());
            if (sys_enterpriseid > 0 || (usertype == 21 || usertype == 22)) {
                row.put("gradeprice", itemPrice.getContractprice());
            } else {
                if(pricerowsMap.containsKey(row.getString("itemid"))){
                    if(pricerowsMap.get(row.getString("itemid")).isNotEmpty()){
                        row.put("gradeprice", pricerowsMap.get(row.getString("itemid")).get(0).getBigDecimal("price"));
                    }else{
                        row.put("gradeprice",0);
                    }
                }else {
                    row.put("gradeprice", 0);
                }
            }


            //判断商品是否参与活动：当前经销商参与的有效的活动
            if(listAll.size()==listParmNotBlank.size()){
                SQLFactory activitysqlFactory = new SQLFactory(new promotion(new JSONObject()), "促销方案列表查询");
                activitysqlFactory.addParameter("siteid", siteid);
                activitysqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                activitysqlFactory.addParameter_SQL("where", "1=1");
                Rows activityrows = dbConnect.runSqlQuery(activitysqlFactory);

                SQLFactory sqlFactory1 = new SQLFactory(new promotion(new JSONObject()), "促销订单金额合计查询");
                sqlFactory1.addParameter("siteid", siteid);
                sqlFactory1.addParameter("sys_enterpriseid", sys_enterpriseid);
                Rows sumAmountRows =  dbConnect.runSqlQuery(sqlFactory1.getSQL());
                RowsMap sumAmountRowsMap = sumAmountRows.toRowsMap("sa_promotionid");

                SQLFactory sqlFactory2 = new SQLFactory(new promotion(new JSONObject()), "促销方案列表查询（触发的促销方案)");
                sqlFactory2.addParameter("siteid", siteid);
                sqlFactory2.addParameter("sys_enterpriseid", sys_enterpriseid);
                Rows openPromotionsRows =  dbConnect.runSqlQuery(sqlFactory2.getSQL());
                RowsMap openPromotionsRowsMap = openPromotionsRows.toRowsMap("sa_promotionid");

                ListIterator<Row> iterator = activityrows.listIterator();
                while (iterator.hasNext()) {
                    Row  activityrow = iterator.next();
                    if(activityrow.getLong("sa_openpromotionid")!=0){
                        if(sumAmountRowsMap.containsKey(activityrow.getString("sa_promotionid"))){
                            BigDecimal sumamount = sumAmountRowsMap.get(activityrow.getString("sa_promotionid")).get(0).getBigDecimal("sumamount");
                            if(sumamount.compareTo(activityrow.getBigDecimal("openamount"))>=0){
                                if(openPromotionsRowsMap.containsKey(activityrow.getString("sa_openpromotionid"))){
                                    iterator.add(openPromotionsRowsMap.get(activityrow.getString("sa_openpromotionid")).get(0));
                                }
                            }
                        }
                    }
                }

                SQLFactory promotionsqlFactory = new SQLFactory(this, "查询商品所在有效活动", pageSize, pageNumber, pageSorting);
                promotionsqlFactory.addParameter("itemno", itemno);
                promotionsqlFactory.addParameter_in("sa_promotionids", activityrows.toArray("sa_promotionid"));
                promotionsqlFactory.addParameter("siteid", siteid);
                Rows promotionrows = dbConnect.runSqlQuery(promotionsqlFactory.getSQL());

                row.put("canuseactivity",promotionrows);
            }else{
                row.put("canuseactivity",new Rows());
            }


        }

        ArrayList<Long> ids = itemrows.toArrayList("itemid", new ArrayList<>());

        //默认商品图片
        Rows defaultImageRows = Item.getItemdefaultImage(this);
        // 封面cover
        RowsMap coverRowsMap = getAttachmentUrl("plm_item", ids, "cover");
        // 附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        Rows rowsitemclass = dbConnect.runSqlQuery(
                " select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 inner JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='"
                        + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");

        // 货品档案领域
        Rows rowstradefield = dbConnect.runSqlQuery(
                " select  t6.itemid,t6.tradefield from plm_item_tradefield t6 where t6.siteid='" + siteid + "'");
        RowsMap tradefieldRowsMap = rowstradefield.toRowsMap("itemid");

        // 品牌
        Rows rowsbrand = dbConnect.runSqlQuery(
                " select  distinct t8.sa_brandid,t6.itemid,t8.brandname from sa_itemsaleclass t6 inner JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='"
                        + siteid + "'");
        RowsMap brandRowsMap = rowsbrand.toRowsMap("itemid");
        for (Row row : itemrows) {
            Rows coverRows = coverRowsMap.get(row.getString("itemid"));
            if (coverRows.isEmpty()) {
                row.put("cover", "");
            } else {
                row.put("cover", coverRows.get(0).getString("url"));
            }

            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("itemclass", itemclassRowsMap.get(row.getString("itemid")));
            row.put("tradefield", tradefieldRowsMap.get(row.getString("itemid")));
            row.put("brand", brandRowsMap.get(row.getString("itemid")));
        }
        JSONObject oject = new JSONObject();
        oject.put("customschemeItems", customschemeItems.toJsonArray());
        if(customschemeItems.size()==1){
            if(customschemeItems.get(0).getString("value").equals("color")){
                RowsMap rowsMap = rowsall.toRowsMap("color");
                for (parm parm : colorList) {
                    if(rowsMap.containsKey(parm.getParm())){
                        parm.setDelistingstatus(rowsMap.get(parm.getParm()).get(0).getString("delistingstatus"));
                    }

                }
            }
            if(customschemeItems.get(0).getString("value").equals("spec")){
                RowsMap rowsMap = rowsall.toRowsMap("spec");
                for (parm parm : specList) {
                    if(rowsMap.containsKey(parm.getParm())) {
                        parm.setDelistingstatus(rowsMap.get(parm.getParm()).get(0).getString("delistingstatus"));
                    }
                }
            }
            if(customschemeItems.get(0).getString("value").equals("material")){
                RowsMap rowsMap = rowsall.toRowsMap("material");
                for (parm parm : materialList) {
                    if(rowsMap.containsKey(parm.getParm())) {
                        parm.setDelistingstatus(rowsMap.get(parm.getParm()).get(0).getString("delistingstatus"));
                    }
                }
            }
            if(customschemeItems.get(0).getString("value").equals("cheek")){
                RowsMap rowsMap = rowsall.toRowsMap("cheek");
                for (parm parm : cheekList) {
                    if(rowsMap.containsKey(parm.getParm())) {
                        parm.setDelistingstatus(rowsMap.get(parm.getParm()).get(0).getString("delistingstatus"));
                    }
                }
            }
        }
        oject.put("colorRows", colorList);
        oject.put("specRows", specList);
        oject.put("materialRows", materialList);
        oject.put("cheekRows", cheekList);
        oject.put("rows", rows);
        oject.put("item", itemrows);
        return getSucReturnObject().setData(oject).toString();
    }


    /**
     * 按照JSONArray中的对象的某个字段进行排序(采用fastJson)
     *
     * @param jsonArrayStr json数组字符串
     * @param sortkeyname  根据哪个字段进行排序
     * @param order        倒序：0；非0顺序
     * @return 排序后的jsonarray
     */
    public JSONArray jsonArraySortByField(String jsonArrayStr, String sortkeyname, String order) throws YosException {

        JSONArray sortedJsonArray = new JSONArray();

        try {

            JSONArray jsonArray = JSON.parseArray(jsonArrayStr);

            List<JSONObject> jsonList = new ArrayList<JSONObject>();

            for (int i = 0; i < jsonArray.size(); i++) {
                jsonList.add(jsonArray.getJSONObject(i));
            }

            Collections.sort(jsonList, new Comparator<JSONObject>() {

                @Override
                public int compare(JSONObject a, JSONObject b) {

                    String strA = new String();
                    String strB = new String();

                    String regEx = "[\n`~!@#$%^&*()+=|{}':;',\\-_\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。， 、？]";

                    String aStr = a.getStringValue(sortkeyname);
                    strA = aStr.replaceAll(regEx, "");
                    String bStr = b.getStringValue(sortkeyname);
                    strB = bStr.replaceAll(regEx, "");

                    if ("0".equals(order)) {
                        return -strA.compareTo(strB);
                    } else {
                        return strA.compareTo(strB);
                    }

                }
            });

            for (int i = 0; i < jsonArray.size(); i++) {
                sortedJsonArray.add(jsonList.get(i));
            }


        } catch (Exception e) {
            e.printStackTrace();
        }


        return sortedJsonArray;
    }

}