CUCU2/src/custom/restcontroller/webmanage/saletool/courseware/coursewareauth.java

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<String> sqlList = new ArrayList<>();
        String key;
        //部门
        key = "departmentid";
        if (content.containsKey(key)) {
            JSONArray departmentids = content.getJSONArray("departmentid");
            ArrayList<Long> 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<Long> 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<String> getSqlList(String key, Long sat_coursewareid, boolean isonlymanager, String SQLMODELNAME, String areaType) throws YosException {
        ArrayList<String> 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<Long> departmentid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>());
        //成员
        key = "hrid";
        ArrayList<Long> hrid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>());
        //区域
        key = "sa_saleareaid";
        ArrayList<Long> sa_saleareaid = dbConnect.runSqlQuery(getSelectSql(id, key)).toArrayList(key, new ArrayList<>());
        //经销商
        key = "sa_agentsid";
        ArrayList<Long> 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<String> 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<String> 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<String> 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();
    }


}