package restcontroller.webmanage.saletool.courseware; import beans.department.Department; import beans.salearea.SaleArea; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import common.Controller; import common.YosException; import common.annotation.API; import common.data.*; import org.apache.commons.lang.StringUtils; import restcontroller.R; import java.util.ArrayList; @API(title = "商学院-范围授权") public class coursewareauth extends Controller { public coursewareauth(JSONObject content) throws YosException { super(content); } @API(title = "新增", intervaltime = 2000) public String insert() throws YosException { boolean isonlymanager_dept = content.getBoolean("isonlymanager_dept"); boolean isonlymanager_agent = content.getBoolean("isonlymanager_agent"); Long sat_coursewareid = content.getLong("sat_coursewareid"); //存放执行sql ArrayList sqlList = new ArrayList<>(); String key; //部门 key = "departmentid"; if (content.containsKey(key)) { JSONArray departmentids = content.getJSONArray("departmentid"); ArrayList tempDepartmentids = new ArrayList<>(); for (Object value : departmentids) { tempDepartmentids.addAll(Department.getSubDepartmentIds(this, Long.parseLong(value.toString()))); } content.put("departmentid", tempDepartmentids); sqlList.addAll(getSqlList(key, sat_coursewareid, isonlymanager_dept, "删除多余部门授权", "内部")); } //用户 key = "hrid"; if (content.containsKey(key)) { sqlList.addAll(getSqlList(key, sat_coursewareid, false, "删除多余成员授权", "内部")); } //营销区域 key = "sa_saleareaid"; if (content.containsKey(key)) { JSONArray ids = content.getJSONArray("sa_saleareaid"); ArrayList temp = new ArrayList<>(); for (Object value : ids) { temp.add(Long.valueOf(value.toString())); temp.addAll(SaleArea.getSubSaleAreaIds(this, Long.parseLong(value.toString()))); } content.put("sa_saleareaid", temp); sqlList.addAll(getSqlList(key, sat_coursewareid, isonlymanager_agent, "删除多余经销商区域授权", "外部")); } //经销商 key = "sa_agentsid"; if (content.containsKey(key)) { sqlList.addAll(getSqlList(key, sat_coursewareid, false, "删除多余经销商授权", "外部")); } dbConnect.runSqlUpdate(sqlList); return getSucReturnObject().toString(); } /** * 组装sql */ public ArrayList getSqlList(String key, Long sat_coursewareid, boolean isonlymanager, String SQLMODELNAME, String areaType) throws YosException { ArrayList sqlList = new ArrayList<>(); JSONArray array = content.getJSONArray(key); for (Object value : array) { sqlList.add(getInsertSql(isonlymanager, areaType, sat_coursewareid, key, value, getSelectAuthSqlStr(sat_coursewareid, key, value))); if (key.equals("departmentid") || key.equals("sa_saleareaid")) { sqlList.add(getUpdateSql(isonlymanager, sat_coursewareid, key, value)); } } sqlList.add(getDeleteSqlStr(SQLMODELNAME, sat_coursewareid, key, array.toArray())); return sqlList; } /** * 简单封装-获取更新授权是否是负责人语句 * * @param isonlymanager * @param sat_coursewareid * @param key * @param value * @return */ public String getUpdateSql(boolean isonlymanager, Long sat_coursewareid, String key, Object value) { return "UPDATE sat_courseware_auth SET isonlymanager=" + isonlymanager + " WHERE siteid='" + siteid + "' and sat_coursewareid = " + sat_coursewareid + " AND " + key + " = " + value; } /** * 简单封装-组装sql语句，用来查询是否有授权：部门，成员，经销商，经销商区域 * * @param sat_coursewareid * @param key * @return */ public String getSelectAuthSqlStr(Long sat_coursewareid, String key, Object value) { Long id = sat_coursewareid; String defSqlStr = "SELECT 1 FROM sat_courseware_auth WHERE siteid = '" + siteid + "' AND sat_coursewareid = '" + id; return defSqlStr + "' AND " + key + " = '" + value + "' limit 1"; } /** * 简单封装-授权删除 * * @param SQLMODELNAME * @param sat_coursewareid * @param key * @param value * @return * @throws YosException */ public String getDeleteSqlStr(String SQLMODELNAME, Long sat_coursewareid, String key, Object value) throws YosException { SQLFactory sqlFactory = new SQLFactory(this, SQLMODELNAME); sqlFactory.addParameter("sat_coursewareid", sat_coursewareid); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in(key, value); return sqlFactory.getSQL(); } /** * 简单封装，返回新增sql */ public String getInsertSql(boolean isonlymanager, String areatype, Long sat_coursewareid, String key, Object vlaue, String whereSql) throws YosException { SQLFactory sqlFactory = new SQLFactory(this, "课程-新增范围授权"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sat_courseware_authid", createTableID("sat_courseware_auth")); sqlFactory.addParameter("createby", username); sqlFactory.addParameter("changeby", username); //是否仅部门负责人 sqlFactory.addParameter("isonlymanager", isonlymanager); //范围：内部，外部 sqlFactory.addParameter("areatype", areatype); //课件id sqlFactory.addParameter("sat_coursewareid", sat_coursewareid); if (key.equals("departmentid")) { sqlFactory.addParameter("departmentid", vlaue); } else { sqlFactory.addParameter("departmentid", "null"); } if (key.equals("hrid")) { sqlFactory.addParameter("hrid", vlaue); } else { sqlFactory.addParameter("hrid", "null"); } if (key.equals("sa_saleareaid")) { sqlFactory.addParameter("sa_saleareaid", vlaue); } else { sqlFactory.addParameter("sa_saleareaid", "null"); } if (key.equals("sa_agentsid")) { sqlFactory.addParameter("sa_agentsid", vlaue); } else { sqlFactory.addParameter("sa_agentsid", "null"); } sqlFactory.addParameter_SQL("whereSql", whereSql); String sql = sqlFactory.getSQL(); return sql; } @API(title = "查询列表") public String selectList() throws YosException { Long id = content.getLong("sat_coursewareid"); String key; //部门 key = "departmentid"; ArrayList departmentid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>()); //成员 key = "hrid"; ArrayList hrid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>()); //区域 key = "sa_saleareaid"; ArrayList sa_saleareaid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>()); //经销商 key = "sa_agentsid"; ArrayList sa_agentsid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>()); //部门负责人 Rows isonlymanagerDeptRows = dbConnect.runSqlQuery(getSelectIsonlymanagerSql(id, "departmentid")); Boolean isonlymanager_dept = false; if (isonlymanagerDeptRows.isNotEmpty()) { isonlymanager_dept = isonlymanagerDeptRows.get(0).getBoolean("isonlymanager"); } //经销商主账号负责人 Rows isonlymanagerAgentRows = dbConnect.runSqlQuery(getSelectIsonlymanagerSql(id, "sa_saleareaid")); Boolean isonlymanager_agent = false; if (isonlymanagerAgentRows.isNotEmpty()) { isonlymanager_agent = isonlymanagerAgentRows.get(0).getBoolean("isonlymanager"); } JSONObject res = new JSONObject(); res.put("sat_coursewareid", id); res.put("departmentid", departmentid); res.put("hrid", hrid); res.put("sa_saleareaid", sa_saleareaid); res.put("sa_agentsid", sa_agentsid); res.put("isonlymanager_dept", isonlymanager_dept); res.put("isonlymanager_agent", isonlymanager_agent); return getSucReturnObject().setData(res).toString(); } /** * 简单封装，返回查询授权sql */ public String getSelectSql(Long sat_coursewareid, String key) { return "SELECT distinct " + key + " FROM sat_courseware_auth where siteid='" + siteid + "' and sat_coursewareid = '" + sat_coursewareid + "' AND " + key + " is not null"; } /** * 简单封装，返回查询是否负责人sql */ public String getSelectIsonlymanagerSql(Long sat_coursewareid, String key) { return "SELECT isonlymanager FROM sat_courseware_auth where siteid='" + siteid + "' and sat_coursewareid = '" + sat_coursewareid + "' AND " + key + " is not null LIMIT 1"; } @API(title = "课程权限新增", apiversion = R.ID20240314145102.v1.class) public String add() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); ArrayList sqlList = new ArrayList<>(); if (content.containsKey("sys_enterpriseids")) { JSONArray sys_enterpriseids = content.getJSONArray("sys_enterpriseids"); for (Object obj : sys_enterpriseids) { InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sat_courseware_auth"); insertSQL.setUniqueid(createTableID("sat_courseware_auth")); insertSQL.setSiteid(siteid); insertSQL.setValue("sat_coursewareid", sat_coursewareid); insertSQL.setValue("sys_enterpriseid", obj); insertSQL.setWhere("not exists(select 1 from sat_courseware_auth where sys_enterpriseid=" + obj + " and sat_coursewareid=" + sat_coursewareid + " and siteid='" + siteid + "' )"); sqlList.add(insertSQL.getSQL()); } } if (content.containsKey("departmentids")) { JSONArray departmentids = content.getJSONArray("departmentids"); for (Object obj : departmentids) { InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sat_courseware_auth"); insertSQL.setUniqueid(createTableID("sat_courseware_auth")); insertSQL.setSiteid(siteid); insertSQL.setValue("sat_coursewareid", sat_coursewareid); insertSQL.setValue("departmentid", obj); insertSQL.setWhere("not exists(select 1 from sat_courseware_auth where departmentid=" + obj + " and sat_coursewareid=" + sat_coursewareid + " and siteid='" + siteid + "' )"); sqlList.add(insertSQL.getSQL()); } } if (content.containsKey("hrids")) { JSONArray hrids = content.getJSONArray("hrids"); for (Object obj : hrids) { InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sat_courseware_auth"); insertSQL.setUniqueid(createTableID("sat_courseware_auth")); insertSQL.setSiteid(siteid); insertSQL.setValue("sat_coursewareid", sat_coursewareid); insertSQL.setValue("hrid", obj); insertSQL.setWhere("not exists(select 1 from sat_courseware_auth where hrid=" + obj + " and sat_coursewareid=" + sat_coursewareid + " and siteid='" + siteid + "' )"); sqlList.add(insertSQL.getSQL()); } } if (content.containsKey("sa_saleareaids")) { JSONArray sa_saleareaids = content.getJSONArray("sa_saleareaids"); for (Object obj : sa_saleareaids) { InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sat_courseware_auth"); insertSQL.setUniqueid(createTableID("sat_courseware_auth")); insertSQL.setSiteid(siteid); insertSQL.setValue("sat_coursewareid", sat_coursewareid); insertSQL.setValue("sa_saleareaid", obj); insertSQL.setWhere("not exists(select 1 from sat_courseware_auth where sa_saleareaid=" + obj + " and sat_coursewareid=" + sat_coursewareid + " and siteid='" + siteid + "' )"); sqlList.add(insertSQL.getSQL()); } } if (sqlList.size() > 0) { dbConnect.runSqlUpdate(sqlList); } return getSucReturnObject().toString(); } @API(title = "课程角色权限新增", apiversion = R.ID20240314145202.v1.class) public String addrole() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); ArrayList sqlList = new ArrayList<>(); JSONArray roleids = content.getJSONArray("roleids"); for (Object obj : roleids) { InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sat_courseware_auth"); insertSQL.setUniqueid(createTableID("sat_courseware_auth")); insertSQL.setSiteid(siteid); insertSQL.setValue("sat_coursewareid", sat_coursewareid); insertSQL.setValue("roleid", obj); insertSQL.setValue("roleid", obj); insertSQL.setWhere("not exists(select 1 from sat_courseware_auth where roleid=" + obj + " and sat_coursewareid=" + sat_coursewareid + " and siteid='" + siteid + "' )"); sqlList.add(insertSQL.getSQL()); } if (sqlList.size() > 0) { dbConnect.runSqlUpdate(sqlList); } DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sat_courseware_auth"); deleteSQL.setSiteid(siteid); deleteSQL.setWhere("sat_coursewareid", sat_coursewareid); deleteSQL.setWhere("roleid not in " + roleids.toString().replace("[", "(").replace("]", ")") + ""); deleteSQL.delete(); return getSucReturnObject().toString(); } @API(title = "课程权限删除", apiversion = R.ID20240314145302.v1.class) public String deleteQuanXian() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); ArrayList sqlList = new ArrayList<>(); if (content.containsKey("sys_enterpriseids")) { JSONArray sys_enterpriseids = content.getJSONArray("sys_enterpriseids"); DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sat_courseware_auth"); deleteSQL.setSiteid(siteid); deleteSQL.setWhere("sat_coursewareid", sat_coursewareid); deleteSQL.setWhere("sys_enterpriseid ", sys_enterpriseids.toArray()); sqlList.add(deleteSQL.getSQL()); } if (content.containsKey("departmentids")) { JSONArray departmentids = content.getJSONArray("departmentids"); DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sat_courseware_auth"); deleteSQL.setSiteid(siteid); deleteSQL.setWhere("sat_coursewareid", sat_coursewareid); deleteSQL.setWhere("departmentid ", departmentids); sqlList.add(deleteSQL.getSQL()); } if (content.containsKey("hrids")) { JSONArray hrids = content.getJSONArray("hrids"); DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sat_courseware_auth"); deleteSQL.setSiteid(siteid); deleteSQL.setWhere("sat_coursewareid", sat_coursewareid); deleteSQL.setWhere("hrid ", hrids); sqlList.add(deleteSQL.getSQL()); } if (content.containsKey("sa_saleareaids")) { JSONArray sa_saleareaids = content.getJSONArray("sa_saleareaids"); DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sat_courseware_auth"); deleteSQL.setSiteid(siteid); deleteSQL.setWhere("sat_coursewareid", sat_coursewareid); deleteSQL.setWhere("sa_saleareaid ", sa_saleareaids); sqlList.add(deleteSQL.getSQL()); } if (sqlList.size() > 0) { dbConnect.runSqlUpdate(sqlList); } return getSucReturnObject().toString(); } @API(title = "课程部门列表",apiversion = R.ID20240314145402.v1.class) public String depList() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_department", "departmentid", "depname", "depno"); querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t2", "t2.departmentid=t1.parentid and t2.siteid=t1.siteid"); querySQL.addJoinTable(JOINTYPE.left, "sat_courseware_auth", "t3", "t1.siteid=t3.siteid and t1.departmentid=t3.departmentid"); querySQL.addQueryFields("parentdepname","t2.depname"); querySQL.setWhere("t1.siteid", siteid); querySQL.setWhere("t3.sat_coursewareid",sat_coursewareid); querySQL.setTableAlias("t1").setPage(pageSize, pageNumber); Rows rows = querySQL.query(); return getSucReturnObject().setData(rows).toString(); } @API(title = "课程人员列表",apiversion = R.ID20240314145502.v1.class) public String hrList() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr", "hrid", "hrcode", "name", "position"); querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t2", "t1.siteid=t2.siteid and t1.departmentid=t2.departmentid", "depname"); querySQL.addJoinTable(JOINTYPE.left, "sat_courseware_auth", "t3", "t1.siteid=t3.siteid and t1.hrid=t3.hrid"); querySQL.setWhere("t1.siteid", siteid); querySQL.setWhere("t3.sat_coursewareid",sat_coursewareid); querySQL.setTableAlias("t1").setPage(pageSize, pageNumber); Rows rows = querySQL.query(); return getSucReturnObject().setData(rows).toString(); } @API(title = "课程区域列表",apiversion = R.ID20240314145602.v1.class) public String areaList() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_salearea", "areaname", "sa_saleareaid"); querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t2", "t2.sa_saleareaid=t1.parentid and t2.siteid=t1.siteid"); querySQL.addJoinTable(JOINTYPE.left, "sat_courseware_auth", "t3", "t1.siteid=t3.siteid and t1.sa_saleareaid=t3.sa_saleareaid"); querySQL.addQueryFields("parentareaname","t2.areaname"); querySQL.setWhere("t1.siteid", siteid); querySQL.setWhere("t3.sat_coursewareid",sat_coursewareid); querySQL.setTableAlias("t1").setPage(pageSize, pageNumber); Rows rows = querySQL.query(); return getSucReturnObject().setData(rows).toString(); } @API(title = "课程合作伙伴列表",apiversion = R.ID20240314145702.v1.class) public String enterpriseList() throws YosException { Long sat_coursewareid = content.getLongValue("sat_coursewareid"); QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_agents", "agentnum", "sys_enterpriseid", "type"); querySQL.addJoinTable(JOINTYPE.inner, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid", "enterprisename"); querySQL.addJoinTable(JOINTYPE.left, "sat_courseware_auth", "t3", "t1.siteid=t3.siteid and t1.sys_enterpriseid=t3.sys_enterpriseid"); querySQL.setWhere("t1.siteid", siteid); querySQL.setWhere("t3.sat_coursewareid",sat_coursewareid); querySQL.setTableAlias("t1").setPage(pageSize, pageNumber); Rows rows = querySQL.query(); SQLFactory sqlFactory = new SQLFactory(this, "查询经销商区域"); sqlFactory.addParameter_in("sys_enterpriseid", rows.toArray("sys_enterpriseid")); sqlFactory.addParameter("siteid", siteid); RowsMap areaRows = dbConnect.runSqlQuery(sqlFactory).toRowsMap("sys_enterpriseid"); for (Row row : rows) { Rows areas = areaRows.getOrDefault(row.getString("sys_enterpriseid"), new Rows()); row.put("areaname", StringUtils.join(areas.toArray("areaname"),",")); } return getSucReturnObject().setData(rows).toString(); } }