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

package restcontroller.webmanage.sale.itemgroup;

import beans.Item.Item;
import beans.brand.Brand;
import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import beans.datatag.DataTag;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.annotation.cm;
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 java.util.ArrayList;
import java.util.List;

@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.ID20220922164303.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(cms = {@cm(clazz = itemgroup.class, method = {"queryItemgroup"})})
    public String insertormodify_itemgroup() throws YosException {
        long sa_itemgroupid = content.getLongValue("sa_itemgroupid");
        long sa_brandid = content.getLong("sa_brandid");
        String groupname = content.getString("groupname");
        String itemno = content.getString("itemno");
        String tradefield = content.getStringValue("tradefield");
        long sa_customschemeid = content.getLong("sa_customschemeid");
        Long sequence = content.getLongValue("sequence");
        JSONArray tagArray = content.getJSONArray("tag");
        ArrayList<String> list = (ArrayList<String>) JSONObject.parseArray(tagArray.toJSONString(), String.class);
        Rows itemgroupRows = dbConnect.runSqlQuery("select sa_itemgroupid,sequence,isonsale from sa_itemgroup where sa_itemgroupid=" + sa_itemgroupid + " and siteid='" + siteid + "'");
        ArrayList<String> sqllist = new ArrayList<>();
        if (sa_itemgroupid <= 0 || itemgroupRows.isEmpty()) {

//            Rows rows = dbConnect.runSqlQuery("SELECT COUNT(*) count FROM sa_itemgroup WHERE sa_brandid='" + sa_brandid + "' and groupname = '" + groupname + "' and siteid='" + siteid + "'");
//            if (!rows.isEmpty() && rows.get(0).getLong("count") > 0) {
//                return getErrReturnObject().setErrMsg("商品组已存在").toString();
//            }
            Rows rows1 = dbConnect.runSqlQuery("select sequence from sa_itemgroup where siteid = '" + siteid + "' order by sequence asc");
            if (rows1.isNotEmpty() && rows1.toArrayList("sequence").contains(sequence.toString())) {
                if (sequence <= rows1.getLastRow().getLong("sequence")) {
                    sqllist.add("update sa_itemgroup set sequence = sequence+1 where siteid = '" + siteid + "' and sequence >= " + sequence);
                }
            }
            SQLFactory sqlAddFactory = new SQLFactory(this, "商品组新增");
            //String billcode=createBillCode("");
            //String billcode="123456789";
            String billcode = createBillCode("itemgroup");
            sa_itemgroupid = createTableID("sa_itemgroup");
            sqlAddFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
            sqlAddFactory.addParameter("siteid", siteid);
            sqlAddFactory.addParameter("groupname", groupname);
            sqlAddFactory.addParameter("itemno", itemno);
            sqlAddFactory.addParameter("username", username);
            sqlAddFactory.addParameter("groupnum", billcode);
            sqlAddFactory.addParameter("tradefield", tradefield);
            sqlAddFactory.addParameter("sa_brandid", sa_brandid);
            sqlAddFactory.addParameter("sa_customschemeid", sa_customschemeid);
            sqlAddFactory.addParameter("sequence", sequence);
            content.put("sa_itemgroupid", sa_itemgroupid);

            DataTag.updateTag(this, "sa_itemgroup", sa_itemgroupid, list);

            sqllist.add(sqlAddFactory.getSQL());
        } else {
//            Rows rows = dbConnect.runSqlQuery("SELECT COUNT(*) count FROM sa_itemgroup WHERE sa_brandid='" + sa_brandid + "' and sa_itemgroupid!=" + content.getLong("sa_itemgroupid") + " and groupname = '" + groupname + "' and siteid='" + siteid + "'");
//            if (!rows.isEmpty() && rows.get(0).getLong("count") > 0) {
//                return getErrReturnObject().setErrMsg("商品组已存在").toString();
//            }
            if (!itemgroupRows.isEmpty() && itemgroupRows.get(0).getBoolean("isonsale")) {
                return getErrReturnObject().setErrMsg("商品组已上架，无法修改").toString();
            }
            if (sequence < itemgroupRows.get(0).getLong("sequence")) {
                sqllist.add("update sa_itemgroup set sequence = sequence+1 where siteid = '" + siteid + "' and sequence between " + sequence + " and " + itemgroupRows.get(0).getLong("sequence"));
            } else if (sequence > itemgroupRows.get(0).getLong("sequence")) {
                sqllist.add("update sa_itemgroup set sequence = sequence-1 where siteid = '" + siteid + "' and sequence between " + itemgroupRows.get(0).getLong("sequence") + " and " + sequence);
            }
            SQLFactory sqlUpdateFactory = new SQLFactory(this, "商品组更新");
            sqlUpdateFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
            sqlUpdateFactory.addParameter("groupname", groupname);
            sqlUpdateFactory.addParameter("itemno", itemno);
            sqlUpdateFactory.addParameter("tradefield", tradefield);
            sqlUpdateFactory.addParameter("sa_customschemeid", sa_customschemeid);
            sqlUpdateFactory.addParameter("sequence", sequence);
            sqlUpdateFactory.addParameter("username", username);

            DataTag.updateTag(this, "sa_itemgroup", sa_itemgroupid, list);
            sqllist.add(sqlUpdateFactory.getSQL());
        }
        dbConnect.runSqlUpdate(sqllist);

        return queryItemgroupMain();
    }

    /**
     * 查询商品组
     *
     * @return
     */
    @API(title = "查询", apiversion = R.ID20220922164403.v1.class)
    @CACHEING
    public String queryItemgroup() throws YosException {
       


        /*
          过滤条件设置
         */
        String where = " 1=1 ";
        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")
                        + "%') and s1.sa_itemgroupid=t1.sa_itemgroupid) ) ";
            }
            if (whereObject.containsKey("brandname") && !"".equals(whereObject.getString("brandname"))) {
                where = where + " and (t3.brandname ='" + whereObject.getString("brandname") + "') ";
            }
            if (whereObject.containsKey("tradefield") && !"".equals(whereObject.getString("tradefield"))) {
                where = where + " and t1.sa_itemgroupid in(select t1.sa_itemgroupid from sa_itemgroupmx t1 left join plm_item_tradefield t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t2.tradefield like '%" + whereObject.getString("tradefield") + "%') ";
            }
            if (whereObject.containsKey("itemclassname") && !"".equals(whereObject.getString("itemclassname"))) {
                where = where + " and (t5.itemclassname ='" + whereObject.getString("itemclassname") + "') ";
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where = where + " and (t1.isonsale ='" + whereObject.getString("isonsale") + "') ";
            }
            if (whereObject.containsKey("iteminfo") && !"".equals(whereObject.getString("iteminfo"))) {
                where = where + " and t1.sa_itemgroupid in(select t1.sa_itemgroupid from sa_itemgroupmx t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where (t2.itemno like '%" + whereObject.getString("iteminfo") + "%' or t2.itemname like '%" + whereObject.getString("iteminfo") + "%')) ";
            }
        }
        /*
          SQL查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "商品组列表查询", pageSize, pageNumber, pageSorting);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        String sql = factory.getSQL();
        Rows rows = dbConnect.runSqlQuery(sql);
        for (Row row2 : rows) {
            ArrayList<String> list = DataTag.queryTag(this, "sa_itemgroup", row2.getLong("sa_itemgroupid"), false);
            row2.put("tag1", JSONArray.parseArray(JSON.toJSONString(list)));
        }


        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);
        // 附件
        RowsMap itemgroupRowsMap = getAttachmentUrl("sa_itemgroup", rows.toArrayList("sa_itemgroupid", new ArrayList<Long>()));

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

        for (Row row : rows) {
            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"));
            }

            row.put("itemgroupinfos", itemgroupRowsMap.getOrDefault(row.getString("sa_itemgroupid"), new Rows()));
            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.ID20220923091903.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 + "'");

        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);
        // 附件
        RowsMap itemgroupRowsMap = getAttachmentUrl("sa_itemgroup", rows.toArrayList("sa_itemgroupid", new ArrayList<Long>()));

        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");
        for (Row row : rows) {
            ArrayList<String> list = DataTag.queryTag(this, "sa_itemgroup", row.getLong("sa_itemgroupid"), false);
            row.put("tag1", JSONArray.parseArray(JSON.toJSONString(list)));
            row.put("itemclass", itemclassRowsMap.get(row.getString("itemid")));

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

            row.put("itemgroupinfos", itemgroupRowsMap.getOrDefault(row.getString("sa_itemgroupid"), new Rows()));
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
        }
        return getSucReturnObject().setData(rows.size() > 0 ? rows.get(0) : new Row()).toString();
    }

    /**
     * 商品组上架
     *
     * @return
     */
    @API(title = "商品组上架", apiversion = R.ID20220923143603.v1.class)
    @CACHEING_CLEAN(apiClass = {itemgroup.class, restcontroller.sale.itemgroup.itemgroup.class})
    public String release() throws YosException {
        JSONArray sa_itemgroupids = content.getJSONArray("sa_itemgroupids");
        String[] s = {"sa_itemgroupids"};
        for (String s1 : s) {
            if (!content.containsKey(s1))
                return getErrReturnObject().setErrMsg("缺少" + s1 + "参数").toString();
        }
        List<Long> list = sa_itemgroupids.toJavaList(Long.class);
        Long[] stringArray = list.toArray(new Long[0]);
//        SQLFactory sqlFactoryquery = new SQLFactory(this, "商品组状态查询");
//        sqlFactoryquery.addParameter("siteid", siteid);
//        sqlFactoryquery.addParameter_in("sa_itemgroupids", stringArray);
        String whereSql = "select isonsale,groupname,sa_itemgroupid,itemno from sa_itemgroup where sa_itemgroupid in " + list + " and  siteid='" + siteid + "'";
        whereSql = whereSql.replace("[", "(").replace("]", ")");
        Rows rows = dbConnect.runSqlQuery(whereSql);
        for (Row row : rows) {
            if (row.getBoolean("isonsale")) {
                return getErrReturnObject().setErrMsg("【" + row.getString("groupname") + "】商品组已上架，无法再次上架").toString();
            }
            if (StringUtils.isBlank(row.getString("itemno"))) {
                return getErrReturnObject().setErrMsg("【" + row.getString("groupname") + "】商品组不存在默认商品，无法再次上架").toString();
            }
            Rows rowscount = dbConnect.runSqlQuery("select distinct ifnull(t2.sa_customschemeid,0) 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=" + row.getLong("sa_itemgroupid"));
            if (rowscount.size() > 1) {
                return getErrReturnObject().setErrMsg("【" + row.getString("groupname") + "】商品组存在两个以上方案的商品，无法上架").toString();
            }
            Rows rowsdetailcount = dbConnect.runSqlQuery("select * from sa_itemgroupmx t1 where t1.sa_itemgroupid=" + row.getLong("sa_itemgroupid"));
            if (rowsdetailcount.size() == 0) {
                return getErrReturnObject().setErrMsg("【" + row.getString("groupname") + "】商品组下没有商品，无法上架").toString();
            }

        }


        SQLFactory sqlFactoryupdate = new SQLFactory(this, "商品组上架");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter_in("sa_itemgroupids", stringArray);
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add(sqlFactoryupdate.getSQL());
        for (long sa_itemgroupid:list) {
            sqlList.add(DataContrlLog.createLog(this, "sa_itemgroup", sa_itemgroupid, "上架", "商品组上架成功").getSQL());
        }

        dbConnect.runSqlUpdate(sqlList);
//        //发送消息
//        for (String id : stringArray) {
//            sendMsg(Long.parseLong(id));
//        }
        return getSucReturnObject().toString();
    }

    /**
     * 商品组设置置顶
     *
     * @return
     */
    @API(title = "商品组设置置顶", apiversion = R.ID20230918155003.v1.class)
    @CACHEING_CLEAN(apiClass = {itemgroup.class, restcontroller.sale.itemgroup.itemgroup.class})
    public String isTopping() throws YosException {
        JSONArray sa_itemgroupids = content.getJSONArray("sa_itemgroupids");
        boolean isTopping = content.getBooleanValue("isTopping");
        List<Long> list = sa_itemgroupids.toJavaList(Long.class);
        Long[] stringArray = list.toArray(new Long[0]);
        SQLFactory sqlFactoryupdate = new SQLFactory(this, "商品组取消置顶");
        if (isTopping) {
            sqlFactoryupdate = new SQLFactory(this, "商品组置顶");
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter_in("sa_itemgroupids", stringArray);
        dbConnect.runSqlUpdate(sqlFactoryupdate);

        return getSucReturnObject().toString();
    }

    /**
     * 商品组设置优先展示
     *
     * @return
     */
    @API(title = "商品组设置优先展示", apiversion = R.ID20230918155103.v1.class)
    @CACHEING_CLEAN(apiClass = {itemgroup.class, restcontroller.sale.itemgroup.itemgroup.class})
    public String isPriorityshow() throws YosException {
        JSONArray sa_itemgroupids = content.getJSONArray("sa_itemgroupids");
        List<Long> list = sa_itemgroupids.toJavaList(Long.class);
        boolean isPriorityshow = content.getBooleanValue("isPriorityshow");
        Long[] stringArray = list.toArray(new Long[0]);
        SQLFactory sqlFactoryupdate = new SQLFactory(this, "商品组取消优先展示");
        if (isPriorityshow) {
            sqlFactoryupdate = new SQLFactory(this, "商品组优先展示");
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter_in("sa_itemgroupids", stringArray);
        dbConnect.runSqlUpdate(sqlFactoryupdate);

        return getSucReturnObject().toString();
    }


    /**
     * 商品组下架
     *
     * @return
     */
    @API(title = "下架", apiversion = R.ID20220923143703.v1.class)
    @CACHEING_CLEAN(apiClass = {itemgroup.class, restcontroller.sale.itemgroup.itemgroup.class})
    public String undercarriage() throws YosException {
        JSONArray sa_itemgroupids = content.getJSONArray("sa_itemgroupids");
        String[] s = {"sa_itemgroupids"};
        for (String s1 : s) {
            if (!content.containsKey(s1))
                return getErrReturnObject().setErrMsg("缺少" + s1 + "参数").toString();
        }
        List<Long> list = sa_itemgroupids.toJavaList(Long.class);
        Long[] stringArray = list.toArray(new Long[0]);
        String whereSql = "select isonsale,groupname from sa_itemgroup where sa_itemgroupid in " + list + " and  siteid='" + siteid + "'";
        whereSql = whereSql.replace("[", "(").replace("]", ")");
        Rows rows = dbConnect.runSqlQuery(whereSql);
        for (Row row : rows) {
            if (!row.getBoolean("isonsale")) {
                return getErrReturnObject().setErrMsg("【" + row.getString("groupname") + "】商品组已下架，无法再次下架").toString();
            }

        }

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "商品组下架");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter_in("sa_itemgroupids", stringArray);
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add(sqlFactoryupdate.getSQL());
        for (long sa_itemgroupid:list) {
            sqlList.add(DataContrlLog.createLog(this, "sa_itemgroup", sa_itemgroupid, "下架", "商品组下架成功").getSQL());
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "新建或修改商品组商品明细", apiversion = R.ID20220923110303.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(cms = {
            @cm(clazz = itemgroup.class, method = {"queryItemgroupMxList", "queryItemgList"})})
    public String insertormodify_itemgroupmx() throws YosException {
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
        JSONArray iteminfos = content.getJSONArray("itemclassinfos");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rowscount = dbConnect.runSqlQuery(
                "select isonsale from sa_itemgroup where sa_itemgroupid=" + sa_itemgroupid);
        if (!rowscount.isEmpty()) {
            if (rowscount.get(0).getBoolean("isonsale")) {
                return getErrReturnObject().setErrMsg("上架状态的商品组无法新建及修改").toString();
            }
        }
        int i = 0;
        long maxid = 0;
        long[] sa_itemgroupmxid = createTableID("sa_itemgroupmx", iteminfos.size());
        Rows maxidRows = dbConnect.runSqlQuery("select MAX(sequence) sequence from sa_itemgroupmx where sa_itemgroupid=" + sa_itemgroupid);
        if (!maxidRows.isEmpty()) {
            maxid = maxidRows.get(0).getLong("sequence");
        }
        for (Object obj : iteminfos) {
            JSONObject iteminfo = (JSONObject) obj;
            if (iteminfo.getLong("sa_itemgroupmxid") <= 0
                    || dbConnect.runSqlQuery("select sa_itemgroupmxid from sa_itemgroupmx where sa_itemgroupmxid="
                    + iteminfo.getLong("sa_itemgroupmxid")).isEmpty()) {

                SQLFactory saleFactory = new SQLFactory(this, "商品组商品明细新增");
                saleFactory.addParameter("siteid", siteid);
                saleFactory.addParameter("sequence", maxid + i + 1);
                saleFactory.addParameter("sa_itemgroupmxid", sa_itemgroupmxid[i]);
                //saleFactory.addParameter("itemno", iteminfo.getString("itemno"));
                saleFactory.addParameter("itemid", iteminfo.getString("itemid"));
                Rows rows = dbConnect.runSqlQuery("select itemno from plm_item where itemid=" + iteminfo.getLong("itemid"));
                if (rows.isEmpty()) {
                    saleFactory.addParameter("itemno", "null");
                } else {
                    saleFactory.addParameter("itemno", rows.get(0).getString("itemno"));
                }
                saleFactory.addParameter("sa_itemgroupid", sa_itemgroupid);
                sqlList.add(saleFactory.getSQL());
                i++;
            } else {
                SQLFactory saleFactory = new SQLFactory(this, "商品组商品明细更新");
                saleFactory.addParameter("sa_itemgroupmxid", iteminfo.getLong("sa_itemgroupmxid"));
                saleFactory.addParameter("sequence", iteminfo.getLongValue("sequence"));
                //saleFactory.addParameter("itemno", iteminfo.getString("itemno"));
                saleFactory.addParameter("itemid", iteminfo.getString("itemid"));
                Rows rows = dbConnect.runSqlQuery("select itemno from plm_item where itemid=" + iteminfo.getLong("itemid"));
                if (rows.isEmpty()) {
                    saleFactory.addParameter("itemno", "null");
                } else {
                    saleFactory.addParameter("itemno", rows.get(0).getString("itemno"));
                }
                sqlList.add(saleFactory.getSQL());
            }

        }

        dbConnect.runSqlUpdate(sqlList);

        updateTradefield(sa_itemgroupid);
        return queryItemgroupMxList();
    }

    @API(title = "商品列表查询", apiversion = R.ID20220923112503.v1.class)
    @CACHEING
    public String queryItemgList() throws YosException {
        /*
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and  (t1.itemname like '%" + whereObject.getString("condition") + "%'"
                        + " or  t1.itemno like '%" + whereObject.getString("condition") + "%'"
                        + " or  t4.material like '%" + whereObject.getString("condition") + "%'"
                        + " or  t4.material like '%" + whereObject.getString("condition") + "%'"
                        + " or  t1.cheek like '%" + whereObject.getString("condition") + "%'"
                        + " or  t1.standards like '%" + whereObject.getString("condition") + "%'"
                        + " or  t1.spec like '%" + whereObject.getString("condition") + "%'"
                        + " or  t1.color like '%" + whereObject.getString("condition") + "%')";

            }
        }
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
        long sa_customschemeid = 0;
        Rows rowscount = dbConnect.runSqlQuery(
                "select sa_customschemeid from sa_itemgroup where siteid='" + siteid + "' and sa_itemgroupid=" + sa_itemgroupid);
        if (rowscount.isNotEmpty()) {
            sa_customschemeid = rowscount.get(0).getLong("sa_customschemeid");
        }
        if (sa_customschemeid != 0) {
            where = where + " and  t1.sa_customschemeid ='" + sa_customschemeid + "'";
        }
//		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.getSQL(false));

        //附件
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        RowsMap brandRowsMap = Brand.getBrandRowsMap(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");
        for (Row row : rows) {
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemclassRowsMap.get(row.getString("itemid")));
        }


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

    @API(title = "商品组商品明细列表", apiversion = R.ID20220923101603.v1.class)
    @CACHEING
    public String queryItemgroupMxList() throws YosException {
        /*
         * 过滤条件设置
         */
        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);

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


        for (Row row : rows) {
            JSONArray jsonArray = new JSONArray();
            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"));
            }
            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.add(plm_technicalinfoRowsMap.get(row2.getString("plm_technicalinfoid")));
            }
            row.put("technicalinfo", jsonArray);
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "删除明细", apiversion = R.ID20220923105103.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = itemgroup.class, method = {"queryItemgroupMxList", "queryItemgList"})})
    public String deletemx() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Long sa_itemgroupmxid = content.getLong("sa_itemgroupmxid");
        Long sa_itemgroupid = content.getLong("sa_itemgroupid");
        Rows rowscount = dbConnect.runSqlQuery(
                "select isonsale from sa_itemgroup where sa_itemgroupid=" + sa_itemgroupid);
        if (!rowscount.isEmpty()) {
            if (rowscount.get(0).getBoolean("isonsale")) {
                return getErrReturnObject().setErrMsg("上架状态的商品组明细无法删除").toString();
            }
        }
        String sql = "DELETE FROM sa_itemgroupmx WHERE sa_itemgroupmxid = '" + sa_itemgroupmxid + "'";
        sqlList.add(sql);
//		sqlList.add("DELETE FROM sa_salesforecastbill WHERE sa_salesforecastmodelid = '" + sa_salesforecastmodelid + "'");
//		sqlList.add("DELETE FROM sa_salesforecasthr WHERE sa_salesforecastmodelid = '" + sa_salesforecastmodelid + "'");
        dbConnect.runSqlUpdate(sqlList);

        updateTradefield(sa_itemgroupid);
        return getSucReturnObject().toString();

    }

    @API(title = "删除", apiversion = R.ID20220922164503.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = itemgroup.class, method = {"queryItemgroup"})})
    public String delete() throws YosException {
        JSONArray sa_itemgroupids = content.getJSONArray("sa_itemgroupids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_itemgroupids.size());
        ArrayList<String> sqllist = new ArrayList<>();
        for (Object o : sa_itemgroupids) {
            long sa_itemgroupid = Long.parseLong(o.toString());
            Rows rowscount = dbConnect.runSqlQuery("select isonsale from sa_itemgroup where sa_itemgroupid=" + sa_itemgroupid);
            if (rowscount.isEmpty()) {
                batchDeleteErr.addErr(sa_itemgroupid, "此商品组不存在，无法删除");
            } else {
                if (rowscount.get(0).getBoolean("isonsale")) {
                    batchDeleteErr.addErr(sa_itemgroupid, "此商品组已上架，无法删除");
                }
            }
            String deletesql = "DELETE FROM sa_itemgroup WHERE sa_itemgroupid = '" + sa_itemgroupid + "'";
            sqllist.add(deletesql);
        }
        dbConnect.runSqlUpdate(sqllist);
        return batchDeleteErr.getReturnObject().toString();

    }

    /**
     * 获取商品组领域
     *
     * @return
     * @throws YosException
     */
    public void updateTradefield(Long sa_itemgroupid) throws YosException {
        String sql = "";
        String tradefield = "";
        Rows rows = dbConnect.runSqlQuery("select distinct t2.tradefield  from sa_itemgroupmx t1 inner join plm_item_tradefield t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where sa_itemgroupid=" + sa_itemgroupid);
        if (!rows.isEmpty()) {
            for (Row row : rows) {
                tradefield = tradefield + row.getString("tradefield") + ",";
            }
        }
        if (!tradefield.equals("")) {
            tradefield = tradefield.substring(0, tradefield.length() - 1);
            dbConnect.runSqlUpdate("update sa_itemgroup set tradefield='" + tradefield + "' where sa_itemgroupid=" + sa_itemgroupid);
        }
    }

}