md_YOS/src/custom/restcontroller/webmanage/sale/itemclass/itemclass.java

package restcontroller.webmanage.sale.itemclass;

import beans.datacontrllog.DataContrlLog;
import beans.enterprise.Enterprise;
import beans.itemclass.ItemClass;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.annotation.cm;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import restcontroller.R;

import java.util.ArrayList;
import java.util.Collections;

@API(title = "营销类别")
public class itemclass extends Controller {

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

    /**
     * 新增修改营销类别
     *
     * @return
     */
    @API(title = "营销类别新增修改", apiversion = R.ID20220922110303.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(cms = {@cm(clazz = itemclass.class, method = {"queryItemclass", "queryItemclassLaststage"})})
    public String insertormodify_itemclass() throws YosException {
        long itemclassid = content.getLong("itemclassid");
        long sa_brandid = content.getLong("sa_brandid");
        long parentid = content.getLong("parentid");
        // String itemclassfullname = content.getString("itemclassfullname");
        String itemclassnum = content.getString("itemclassnum");
        String itemclassname = content.getString("itemclassname");
        String classtype = content.getString("classtype");
        String num = content.getStringValue("num");
        boolean ishide = content.getBooleanValue("ishide");
        boolean istool = content.getBooleanValue("istool");
        ArrayList<String> sqllist = new ArrayList<>();
        if (itemclassid <= 0 || dbConnect
                .runSqlQuery("select itemclassid from plm_itemclass where itemclassid=" + itemclassid).isEmpty()) {

            Rows rows = dbConnect.runSqlQuery("SELECT COUNT(*) count FROM plm_itemclass WHERE sa_brandid='" + sa_brandid
                    + "' and itemclassnum = '" + itemclassnum + "' and siteid='" + siteid + "'");
            Rows rowsname = dbConnect.runSqlQuery("SELECT COUNT(*) count FROM plm_itemclass WHERE sa_brandid='"
                    + sa_brandid + "' and itemclassname = '" + itemclassname + "' and siteid='" + siteid + "'");
            if (!rows.isEmpty() && rows.get(0).getLong("count") > 0) {
                return getErrReturnObject().setErrMsg("营销类别已存在").toString();
            }
//            if (!rowsname.isEmpty() && rowsname.get(0).getLong("count") > 0) {
//                return getErrReturnObject().setErrMsg("营销类别已存在").toString();
//            }
            SQLFactory sqlAddFactory = new SQLFactory(this, "营销类别新增");
            itemclassid = createTableID("plm_itemclass");
            sqlAddFactory.addParameter("itemclassid", itemclassid);
            sqlAddFactory.addParameter("siteid", siteid);
            sqlAddFactory.addParameter("itemclassnum", itemclassnum);
            sqlAddFactory.addParameter("itemclassname", itemclassname);
            sqlAddFactory.addParameter("username", username);
            sqlAddFactory.addParameter("userid", userid);
            if (parentid == 0) {
                sqlAddFactory.addParameter("istool", istool);
            } else {
                Rows toolRows = dbConnect.runSqlQuery("select itemclassid,istool from plm_itemclass where itemclassid="
                        + parentid + " and siteid='" + siteid + "'");
                if (!toolRows.isEmpty()) {
                    sqlAddFactory.addParameter("istool", toolRows.get(0).getLong("istool"));
                } else {
                    return getErrReturnObject().setErrMsg("上级类别不存在").toString();
                }

            }
            sqlAddFactory.addParameter("parentid", parentid);
            sqlAddFactory.addParameter("itemclassfullname",
                    getUppeItemclassfullname(itemclassname, parentid, itemclassid));
            sqlAddFactory.addParameter("itemclassfullnum",
                    getUppeItemclassfullnum(itemclassnum, parentid, itemclassid));
            sqlAddFactory.addParameter("classtype", classtype);
            sqlAddFactory.addParameter("num", num);
            sqlAddFactory.addParameter("ishide", ishide);

            sqlAddFactory.addParameter("sa_brandid", sa_brandid);
            sqlAddFactory.addParameter("isdeep", 1);
            sqlAddFactory.addParameter("level", 1);
            if (parentid > 0) {
                sqllist = getItemclasslevel(1, parentid, itemclassid, sqllist);
                sqllist.add("update plm_itemclass set isdeep=0 where itemclassid=" + parentid);
            }
            sqllist.add(sqlAddFactory.getSQL());
            sqllist.add(DataContrlLog.createLog(this, "plm_itemclass", classtype.equals("营销")?1:2, "新增", itemclassnum+classtype+"类别新增成功")
                    .getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery("SELECT COUNT(*) count FROM plm_itemclass WHERE sa_brandid='" + sa_brandid
                    + "' and itemclassid!=" + content.getLong("itemclassid") + " and itemclassnum = '" + itemclassnum
                    + "' and siteid='" + siteid + "'");
            Rows rowsname = dbConnect.runSqlQuery("SELECT COUNT(*) count FROM plm_itemclass WHERE sa_brandid='"
                    + sa_brandid + "' and itemclassid!=" + content.getLong("itemclassid") + " and itemclassname = '"
                    + itemclassname + "' and siteid='" + siteid + "'");
            Rows itemclassnameOldRows = dbConnect.runSqlQuery(
                    "SELECT itemclassname,itemclassfullname,itemclassfullnum FROM plm_itemclass WHERE sa_brandid='"
                            + sa_brandid + "' and itemclassid=" + content.getLong("itemclassid") + " and siteid='"
                            + siteid + "'");
            if (!rows.isEmpty() && rows.get(0).getLong("count") > 0) {
                return getErrReturnObject().setErrMsg("营销类别已存在").toString();
            }
//            if (!rowsname.isEmpty() && rowsname.get(0).getLong("count") > 0) {
//                return getErrReturnObject().setErrMsg("营销类别已存在").toString();
//            }
            String itemclassnameOld = "";
            if (!itemclassnameOldRows.isEmpty()) {
                itemclassnameOld = itemclassnameOldRows.get(0).getString("itemclassname");
            }

            SQLFactory sqlUpdateFactory = new SQLFactory(this, "营销类别更新");
            sqlUpdateFactory.addParameter("itemclassid", itemclassid);
            sqlUpdateFactory.addParameter("siteid", siteid);
            sqlUpdateFactory.addParameter("itemclassname", itemclassname);
            sqlUpdateFactory.addParameter("username", username);
            sqlUpdateFactory.addParameter("userid", userid);
//			sqlUpdateFactory.addParameter("itemclassfullname",
//					getUppeItemclassfullname(itemclassname, parentid, itemclassid));
//			sqlUpdateFactory.addParameter("itemclassfullnum",
//					getUppeItemclassfullnum(itemclassnum, parentid, itemclassid));
            if (!itemclassnameOldRows.get(0).getString("itemclassfullname").contains("/")) {
                sqlUpdateFactory.addParameter("itemclassfullname", itemclassname);
            } else {
                sqlUpdateFactory.addParameter("itemclassfullname",
                        itemclassnameOldRows.get(0).getString("itemclassfullname").substring(0,
                                itemclassnameOldRows.get(0).getString("itemclassfullname").lastIndexOf("/")) + "/"
                                + itemclassname);
            }

            sqlUpdateFactory.addParameter("itemclassfullnum",
                    itemclassnameOldRows.get(0).getString("itemclassfullnum"));
            sqlUpdateFactory.addParameter("classtype", classtype);
            sqlUpdateFactory.addParameter("num", num);
            sqlUpdateFactory.addParameter("ishide", ishide);
            if (parentid == 0) {
                sqlUpdateFactory.addParameter("istool", istool);
                ArrayList<Long> list = new ArrayList<Long>();
                list.add(itemclassid);
                ArrayList<Long> Sublist = ItemClass.getSubItemClassIds(this, list);
                Sublist.add((long) 0);
                String sqlString = "update plm_itemclass set istool=" + istool + " where itemclassid in" + Sublist;
                sqlString = sqlString.replace("[", "(").replace("]", ")");
                sqllist.add(sqlString);
            } else {
                Rows toolRows = dbConnect.runSqlQuery("select itemclassid,istool from plm_itemclass where itemclassid="
                        + parentid + " and siteid='" + siteid + "'");
                if (!toolRows.isEmpty()) {
                    sqlUpdateFactory.addParameter("istool", toolRows.get(0).getLong("istool"));
                } else {
                    return getErrReturnObject().setErrMsg("上级类别不存在").toString();
                }

            }
            if (!itemclassnameOld.equals("")) {
                sqllist.addAll(getLowerItemclassUpdateSql(itemclassid, itemclassnameOld, itemclassname));
            }
            sqllist.add(sqlUpdateFactory.getSQL());
            sqllist.add(DataContrlLog.createLog(this, "plm_itemclass", classtype.equals("营销")?1:2, "更新", itemclassnum+classtype+"类别更新成功")
                    .getSQL());
        }
        dbConnect.runSqlUpdate(sqllist);

        return getSucReturnObject().toString();
    }

    @API(title = "营销类别新增修改", apiversion = R.ID20240902145303.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(cms = {@cm(clazz = itemclass.class, method = {"queryItemclass", "queryItemclassLaststage"})})
    public String modify_itemclassNum() throws YosException {
        JSONArray itemclassinfos = content.getJSONArray("itemclassinfos");
        ArrayList<String> sqlList = new ArrayList<>();
        for (Object obj : itemclassinfos) {
            JSONObject itemclassinfo = (JSONObject) obj;
            sqlList.add("update plm_itemclass set num='" + itemclassinfo.getString("num") + "' where itemclassid=" + itemclassinfo.getLong("itemclassid"));
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    /**
     * 查询营销类别
     *
     * @return
     */
    @API(title = "查询", apiversion = R.ID20220922110403.v1.class)
    @CACHEING(life = 10)
    public String queryItemclass() throws YosException {
        long sa_brandid = content.getLongValue("sa_brandid");
        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLongValue("sys_enterpriseid");
        }

        String where = " 1=1 ";
        String where2 = " 1=1 ";


        if (content_where.containsKey("istool") && !"".equals(content_where.getString("istool"))) {
            where = where + " and (ifnull(t1.istool,0) ='" + content_where.getString("istool") + "') ";
            where2 = where2 + " and (ifnull(t2.istool,0) ='" + content_where.getString("istool") + "') ";
        }

        if (content_where.containsKey("ishide") && !"".equals(content_where.getString("ishide"))) {
            where = where + " and (ifnull(t1.ishide,0) ='" + content_where.getString("ishide") + "') ";
            where2 = where2 + " and (ifnull(t2.ishide,0) ='" + content_where.getString("ishide") + "') ";
        }

        if (sys_enterpriseid > 0 && content.getStringValue("classtype").equalsIgnoreCase("营销")) {
            Row enterpriseRow = Enterprise.getEnterprise(this, sys_enterpriseid);
            if (!enterpriseRow.isEmpty()) {
                if (enterpriseRow.getBoolean("saleclassauth")) {
                    where = where + " and t1.itemclassid in ( SELECT itemclassid from sys_enterprise_saleclass WHERE sys_enterpriseid = "
                            + sys_enterpriseid + " and siteid='" + siteid + "')";
                }
            }
        }

        Rows rows = Enterprise.getBrands(this, sys_enterpriseid);
        if (sa_brandid > 0) {
            rows = rows.filter("sa_brandid", sa_brandid);
        }
        for (Row row : rows) {
            //查询所有分类

            SQLFactory sqlFactory = new SQLFactory(this, "查询所有分类");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_brandid", row.getString("sa_brandid"));
            sqlFactory.addParameter("classtype", content.getStringValue("classtype"));
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter_SQL("where2", where2);
            Rows allrows = dbConnect.runSqlQuery(sqlFactory.getSQL());

            if (content.getBooleanValue("isnottree")) {
                row.put("ttemclass", allrows);
            } else {
                /*
                 * 获取所有一级分类
                 */
                RowsMap allrowsMap = allrows.toRowsMap("parentid");
                Rows toprows = allrows.toRowsMap("parentid").get("0");
                Collections.sort(toprows, new SortByNum());
                /*
                 * 递归查询
                 */
                for (Row row1 : toprows) {
                    Rows rows1 = getSubItemclass(row1, allrowsMap);
                    row1.put("subdep", rows1);
                }
                row.put("ttemclass", toprows);
            }

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


    /**
     * 递归查询下级产品类别
     *
     * @param root
     * @param allrowsMap
     * @return
     */
    private Rows getSubItemclass(Row root, RowsMap allrowsMap) {
        Rows childrenRows = allrowsMap.get(root.getString("itemclassid"));
        Collections.sort(childrenRows, new SortByNum());
        for (Row row : childrenRows) {
            row.put("subdep", getSubItemclass(row, allrowsMap));
        }
        return childrenRows;
    }

    /**
     * 查询单据可添加营销类别
     *
     * @return
     */
    @API(title = "查询单据可添加营销类别", apiversion = R.ID20230818135003.v1.class)
    @CACHEING
    public String queryItemclassAccordingtoId() throws YosException {
        long sa_brandid = content.getLongValue("sa_brandid");
        // long istool = content.getLongValue("istool");


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

            if (whereObject.containsKey("istool") && !"".equals(whereObject.getString("istool"))) {
                where = where + " and (ifnull(t1.istool,0) ='" + whereObject.getString("istool") + "') ";
            }
        }
        if (content.containsKey("sys_enterpriseid")) {
            if (content.getLong("sys_enterpriseid") > 0) {
                where = where + " and t1.itemclassid not in (SELECT ifnull(itemclassid,0) from sys_enterprise_saleclass WHERE sys_enterpriseid = " +
                        content.getLong("sys_enterpriseid") + " and siteid='" + siteid + "' )";
            }
        }

        if (content.containsKey("sa_rebateschemeid")) {
            if (content.getLong("sa_rebateschemeid") > 0) {
                where = where + " and not exists (SELECT 1 from sa_rebatecategory WHERE sa_rebateschemeid = " +
                        content.getLong("sa_rebateschemeid") + " and itemclassid=t1.itemclassid and siteid='" + siteid + "' )";
            }

        }

        /*
         * SQL通告板块查询参数设置并查询
         */
        Rows rows = Enterprise.getBrands(this, sys_enterpriseid);

        if (!rows.isEmpty()) {
            for (Row row : rows) {
                /*
                 * 查询所有分类
                 */

                SQLFactory sqlFactory = new SQLFactory(this, "查询所有分类");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_brandid", row.getString("sa_brandid"));
                sqlFactory.addParameter("classtype", content.getStringValue("classtype"));
                sqlFactory.addParameter_SQL("where", where);
                sqlFactory.addParameter_SQL("where2", " 1=1 ");
                String sql = sqlFactory.getSQL();
                Rows allrows = dbConnect.runSqlQuery(sql);
                /*
                 * 获取所有一级分类
                 */
                RowsMap allrowsMap = allrows.toRowsMap("parentid");
                Rows toprows = allrows.toRowsMap("parentid").get("0");
                Collections.sort(toprows, new SortByNum());
                /*
                 * 递归查询
                 */
                for (Row row1 : toprows) {
                    Rows rows1 = getSubItemclass(row1, allrowsMap);
                    //Collections.sort(rows1, new SortByNum());
                    row1.put("subdep", rows1);
                }
                row.put("ttemclass", toprows);
            }
        }

        if (sa_brandid > 0) {
            Rows rows2 = new Rows();
            if (!rows.isEmpty()) {
                for (Row row : rows) {
                    if (row.getLong("sa_brandid") == sa_brandid) {
                        rows2.add(row);
                    }
                }
            }

            if (rows2.isNotEmpty()) {
                if (((Rows) rows2.get(0).get("ttemclass")).isNotEmpty()) {
                    Collections.sort((Rows) rows2.get(0).get("ttemclass"), new SortByNum());
                }
            }

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

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

    /**
     * 查询末级营销类别
     *
     * @return
     */
    @API(title = "查询", apiversion = R.ID20220927102603.v1.class)
    @CACHEING
    public String queryItemclassLaststage() throws YosException {
        // long sa_brandid = content.getLong("sa_brandid");
        /*
         * 过滤条件设置
         */
        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.itemclassname like'%" + whereObject.getString("condition") + "%') ";
            }
            if (whereObject.containsKey("istool") && !"".equals(whereObject.getString("istool"))) {
                where = where + " and t1.istool=" + whereObject.getLongValue("istool");
            }
        }
        SQLFactory factory = new SQLFactory(this, "营销类别末级列表查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(factory.getSQL());

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

    @API(title = "删除", apiversion = R.ID20220922110503.v1.class)
    @CACHEING_CLEAN(cms = {@cm(clazz = itemclass.class, method = {"queryItemclass"})})
    public String delete() throws YosException {
        Long itemclassid = content.getLong("itemclassid");

        Rows rowscount = dbConnect.runSqlQuery(
                "select isdeep,ifnull(parentid,0) parentid,itemclassnum from plm_itemclass where itemclassid=" + itemclassid);
        if (rowscount.isEmpty()) {
            return getErrReturnObject().setErrMsg("此营销类别不存在，无法删除").toString();
        } else {
            if (!rowscount.get(0).getBoolean("isdeep")) {
                return getErrReturnObject().setErrMsg("此营销类别非末级，无法删除").toString();
            }
        }
        Rows row = dbConnect.runSqlQuery("select itemid from sa_itemsaleclass where itemclassid=" + itemclassid);
        if (!row.isEmpty()) {
            return getErrReturnObject().setErrMsg("此营销类别下存在商品，无法删除").toString();
        }

        ArrayList<String> sqllist = new ArrayList<>();
        sqllist.add("update plm_itemclass set isdeep=1 where itemclassid=" + rowscount.get(0).getLong("parentid"));
        String deletesql = "DELETE FROM plm_itemclass WHERE itemclassid = '" + itemclassid + "'";
        sqllist.add(deletesql);
        sqllist.add(DataContrlLog.createLog(this, "plm_itemclass", rowscount.get(0).getString("classtype").equals("营销")?1:2, "删除", rowscount.get(0).getString("itemclassnum")+rowscount.get(0).getString("classtype")+"类别删除成功")
                .getSQL());

        dbConnect.runSqlUpdate(sqllist);
        return getSucReturnObject().toString();

    }


    private ArrayList<String> getItemclasslevel(int level, long parentid, long itemclassid, ArrayList<String> sqllist)
            throws YosException {
        if (parentid > 0) {
            Rows rows = dbConnect.runSqlQuery(
                    "select ifnull(parentid,0) as parentid from plm_itemclass where classtype='营销'  and itemclassid= '"
                            + parentid + "'");
            if (!rows.isEmpty()) {
                level++;
                sqllist.add("update plm_itemclass set level='" + level + "' where itemclassid= '" + parentid + "'");
                getItemclasslevel(level, rows.get(0).getLong("parentid"), rows.get(0).getLong("itemclassid"), sqllist);
            }

        }
        return sqllist;
    }

    private String getUppeItemclassfullnum(String fullname, long parentid, long itemclassid) throws YosException {
        String fullnum = "";
        if (parentid > 0) {
            Rows rows = dbConnect.runSqlQuery(
                    "select ifnull(parentid,0) as parentid,itemclassfullnum from plm_itemclass where classtype='营销'  and itemclassid= '"
                            + parentid + "'");
            if (!rows.isEmpty()) {
                fullnum = rows.get(0).getString("itemclassfullnum") + "/" + fullname;
            }

        } else {
            fullnum = fullname;

        }

        return fullnum;

    }

    private String getUppeItemclassfullname(String fullname, long parentid, long itemclassid) throws YosException {
        String fullnum = "";
        if (parentid > 0) {
            Rows rows = dbConnect.runSqlQuery(
                    "select ifnull(parentid,0) as parentid,itemclassfullname from plm_itemclass where classtype='营销'  and itemclassid= '"
                            + parentid + "'");
            if (!rows.isEmpty()) {
                fullnum = rows.get(0).getString("itemclassfullname") + "/" + fullname;
            }

        } else {
            fullnum = fullname;

        }

        return fullnum;

    }

    /**
     * 根据指定的营销类别id获取下级营销类别id及营销路径名
     *
     * @param controller
     * @param itemclassid
     * @throws YosException
     */
    public static Rows getSubItemclass(Controller controller, long itemclassid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(controller, "下级营销类别获取");
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter("itemclassid", itemclassid);
        Rows rows = controller.dbConnect.runSqlQuery(sqlFactory.getSQL());

        return rows;
    }

    private ArrayList<String> getLowerItemclassUpdateSql(long itemclassid, String itemclassnameOld,
                                                         String itemclassnameNew) throws YosException {
        ArrayList<String> sqlList = new ArrayList<String>();
        Rows rows = getSubItemclass(this, itemclassid);
        if (!rows.isEmpty()) {
            for (Row row : rows) {
                if (row.getString("itemclassfullname").contains(itemclassnameOld)) {
                    String itemclassfullname = row.getString("itemclassfullname").replace(itemclassnameOld,
                            itemclassnameNew);
                    String sql = "update plm_itemclass set itemclassfullname='" + itemclassfullname
                            + "' where itemclassid=" + row.getLong("itemclassid");
                    sqlList.add(sql);
                }
            }
        }
        return sqlList;

    }

}