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

package restcontroller.webmanage.sale.itemclass;

import beans.enterprise.Enterprise;
import beans.itemclass.ItemClass;
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.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());
		} 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());

		}
		dbConnect.runSqlUpdate(sqllist);

		return getSucReturnObject().toString();
	}

	/**
	 * 查询营销类别
	 *
	 * @return
	 */
	@API(title = "查询", apiversion = R.ID20220922110403.v1.class)
	@CACHEING
	public String queryItemclass() throws YosException {
		long sa_brandid = content.getLongValue("sa_brandid");
		// long istool = content.getLongValue("istool");
		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 (brandname like'%" + whereObject.getString("condition") + "%') ";
//			}
//		}

		String where = " 1=1 ";
		String where2 = " 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") + "') ";
				where2=where2+  " and (ifnull(t2.istool,0) ='" + whereObject.getString("istool") + "') ";
			}
			if (whereObject.containsKey("ishide") && !"".equals(whereObject.getString("ishide"))) {
				where = where + " and (ifnull(t1.ishide,0) ='" + whereObject.getString("ishide") + "') ";
				where2=where2+  " and (ifnull(t2.ishide,0) ='" + whereObject.getString("ishide") + "') ";
			}
		}
		/*
		 * SQL通告板块查询参数设置并查询
		 */
		Rows rows = Enterprise.getBrands(this, sys_enterpriseid);

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

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

				}


				SQLFactory sqlFactory = new SQLFactory(this, "查询所有分类");
				sqlFactory.addParameter("siteid", siteid);
				sqlFactory.addParameter("sa_brandid", row.getString("sa_brandid"));

				sqlFactory.addParameter_SQL("where", where);
				sqlFactory.addParameter_SQL("where2", where2);

				String sql = sqlFactory.getSQL();
				Rows allrows = dbConnect.runSqlQuery(sql);
				/*
				 * 获取所有一级分类
				 */
				Rows toprows = allrows.toRowsMap("parentid").get("0");
				/*
				 * 递归查询
				 */
				for (Row row1 : toprows) {
					Rows rows1 = getSubItemclass(row1, allrows);
					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.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 itemclassid 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_SQL("where", where);
				sqlFactory.addParameter_SQL("where2", " 1=1 ");
				String sql = sqlFactory.getSQL();
				Rows allrows = dbConnect.runSqlQuery(sql);
				/*
				 * 获取所有一级分类
				 */
				Rows toprows = allrows.toRowsMap("parentid").get("0");
				/*
				 * 递归查询
				 */
				for (Row row1 : toprows) {
					Rows rows1 = getSubItemclass(row1, allrows);
					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 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);

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

	}

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

	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;

	}

}