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.annotation.CACHEING;
import common.data.*;
import restcontroller.R;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * 考试表头
 */
public class coursewaretesthead extends Controller {

    String sat_courseware_testhead = "sat_courseware_testhead";

    public coursewaretesthead(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "考试表头新增或更新", apiversion = R.ID20240323112202.v1.class)
    public String insertOrUpdate() throws YosException {

        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        int testaddmode = content.getIntValue("testaddmode", 1);
        int num = content.getIntValue("num", 0);
        JSONArray sat_courseware_classids = new JSONArray();
        if (content.containsKey("sat_courseware_classids")) {
            sat_courseware_classids = content.getJSONArray("sat_courseware_classids");
        }

        ArrayList<Long> sat_courseware_testlibraryids = new ArrayList<>();

        if (testaddmode == 1) {
            if (num <= 0) {
                return getErrReturnObject().setErrMsg("出题方式为系统随机时题目设置数量需大于0").toString();
            }
            if (sat_courseware_classids.size() <= 0) {
                return getErrReturnObject().setErrMsg("请选择分类").toString();
            }

            Rows rows = CoursewareHelper.getTestlibraryWithCoursewareClassRows(this, sat_courseware_classids);
            if (num > rows.size()) {
                return getErrReturnObject().setErrMsg("当前分类下题目数量小于系统随机出题数").toString();
            }
            sat_courseware_testlibraryids = rows.toArrayList("sat_courseware_testlibraryid", new ArrayList<>());
        }


        if (sat_courseware_testheadid <= 0) {
            sat_courseware_testheadid = createTableID(sat_courseware_testhead);
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, sat_courseware_testhead);
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(sat_courseware_testheadid);
            insertSQL.setValue("title", content.getStringValue("title"));
            insertSQL.setValue("status", "新建");
            insertSQL.setValue("testaddmode", content.getIntValue("testaddmode", 1));
            insertSQL.setValue("passingscore", content.getBigDecimalValue("passingscore", 60));
            insertSQL.setValue("num", content.getIntValue("num", 0));
            insertSQL.setValue("sat_courseware_classids", sat_courseware_classids);
            insertSQL.insert();

            content.put("sat_courseware_testheadid", sat_courseware_testheadid);
        }

        if (sat_courseware_testheadid > 0) {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, sat_courseware_testhead);
            updateSQL.setSiteid(siteid);
            updateSQL.setUniqueid(sat_courseware_testheadid);
            updateSQL.setValue("title", content.getStringValue("title"));
            updateSQL.setValue("testaddmode", content.getIntValue("testaddmode", 1));
            updateSQL.setValue("passingscore", content.getBigDecimalValue("passingscore", 60));
            updateSQL.setValue("num", content.getIntValue("num", 0));
            updateSQL.setValue("sat_courseware_classids", sat_courseware_classids);
            if (testaddmode == 1) {//系统随机
                updateSQL.setValue("testquestions", new JSONArray());
            }
            updateSQL.update();
        }
        if (testaddmode == 1) {////系统随机
            content.put("sat_courseware_testlibraryids", sat_courseware_testlibraryids);
            return addtestquestions();
        }
        return detail();
    }

    @API(title = "考试表头详情", apiversion = R.ID20240323112302.v1.class)
    public String detail() throws YosException {
        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, sat_courseware_testhead).setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sat_courseware_testheadid", sat_courseware_testheadid);
        Rows rows = querySQL.query();
        Row detailRow = rows.isNotEmpty() ? rows.get(0) : new Row();

        JSONArray jsonArray = detailRow.getJSONArray("testquestions");
        detailRow.put("testquestions", jsonArray);

        JSONArray sat_courseware_classids = detailRow.getJSONArray("sat_courseware_classids");
        if (sat_courseware_classids.size() == 0) {
            detailRow.put("classnames", "");
        } else {
            detailRow.put("classnames", CoursewareHelper.getClassnames(this, sat_courseware_classids));
        }

        if (detailRow.getLong("testaddmode") == 1) {
            detailRow.put("testaddmodestr", "系统随机");
        }
        if (detailRow.getLong("testaddmode") == 2) {
            detailRow.put("testaddmodestr", "自选题目");
        }


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

    @API(title = "考试表头删除", apiversion = R.ID20240323112402.v1.class)
    public String delete() throws YosException {


        JSONArray sat_courseware_testheadids = content.getJSONArray("sat_courseware_testheadids");

        DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, sat_courseware_testhead);
        deleteSQL.setSiteid(siteid);
        deleteSQL.setWhere("sat_courseware_testheadid", sat_courseware_testheadids.toArray());
        deleteSQL.delete();

        return getSucReturnObject().toString();
    }

    @API(title = "考试表头发布", apiversion = R.ID20240323141002.v1.class)
    public String release() throws YosException {

        JSONArray sat_courseware_testheadids = content.getJSONArray("sat_courseware_testheadids");
        int status = content.getIntValue("status", 0);

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, sat_courseware_testhead);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("status", status == 0 ? "新建" : "发布");
        updateSQL.setValue("sendby", username);
        updateSQL.setValue("senddate", getDateTime_Str());
        updateSQL.setWhere("sat_courseware_testheadid", sat_courseware_testheadids.toArray());
        updateSQL.update();

        return getSucReturnObject().toString();
    }

    @API(title = "添加考试题目", apiversion = R.ID20240323141602.v1.class)
    public String addtestquestions() throws YosException {
        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        JSONArray sat_courseware_testlibraryids = content.getJSONArray("sat_courseware_testlibraryids");

        Rows testheadRows = dbConnect.runSqlQuery("SELECT * from sat_courseware_testhead WHERE sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "'");
        if (testheadRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("数据不存在").toString();
        }
        if (sat_courseware_testlibraryids.size() <= 0) {
            return getErrReturnObject().setErrMsg("请选择题目").toString();
        }
        JSONArray testquestions = testheadRows.get(0).getJSONArray("testquestions");
        //移除考试题目
        testquestions.removeAll(CoursewareHelper.getRemoveArray(testquestions, sat_courseware_testlibraryids));
        //添加考试题目
        testquestions.addAll(CoursewareHelper.getTestlibraryRows(this, sat_courseware_testlibraryids));


        int testaddmode = testheadRows.get(0).getInteger("testaddmode");

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, sat_courseware_testhead);
        updateSQL.setUniqueid(sat_courseware_testheadid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("testquestions", testquestions);
        if (testaddmode == 2) {
            updateSQL.setValue("num", testquestions.size());
        }
        updateSQL.update();
        return detail();
    }


    @API(title = "删除考试题目", apiversion = R.ID20240323155602.v1.class)
    public String deletetestquestions() throws YosException {

        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        JSONArray sat_courseware_testlibraryids = content.getJSONArray("sat_courseware_testlibraryids");


        Rows testheadRows = dbConnect.runSqlQuery("SELECT * from sat_courseware_testhead WHERE sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "'");
        if (testheadRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("数据不存在").toString();
        }
        if (sat_courseware_testlibraryids.size() <= 0) {
            return getErrReturnObject().setErrMsg("请选择题目").toString();
        }
        JSONArray testquestions = testheadRows.get(0).getJSONArray("testquestions");
        //移除考试题目
        testquestions.removeAll(CoursewareHelper.getRemoveArray(testquestions, sat_courseware_testlibraryids));


        int testaddmode = testheadRows.get(0).getInteger("testaddmode");

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, sat_courseware_testhead);
        updateSQL.setUniqueid(sat_courseware_testheadid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("testquestions", testquestions);
        if (testaddmode == 2) {
            updateSQL.setValue("num", testquestions.size());
        }
        updateSQL.update();
        return detail();
    }


    @API(title = "考试表头列表", apiversion = R.ID20240323112502.v1.class)
    public String list() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and (");
                where.append("t1.title like'%").append(whereObject.getString("condition")).append("%' ");
//                where.append("or t1.notes like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and (");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("testaddmode") && !"".equals(whereObject.getString("testaddmode"))) {
                where.append(" and (");
                where.append("t1.testaddmode ='").append(whereObject.getString("testaddmode")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("sat_courseware_classids") && !"".equals(whereObject.getString("sat_courseware_classids"))) {
                JSONArray sat_courseware_classids = whereObject.getJSONArray("sat_courseware_classids");
                if (sat_courseware_classids.size() > 0) {
                    where.append(" and (1=2");
                    for (Object obj : sat_courseware_classids) {
                        JSONArray array = (JSONArray) obj;
                        for (Object obj2 : array) {
                            where.append(" or (");
                            where.append("JSON_CONTAINS(t1.sat_courseware_classids,'" + obj2 + "')");
                            where.append(")");
                        }
                    }
                    where.append(")");
                }
            }
            if (whereObject.containsKey("begindate_create") && !"".equals(whereObject.getString("begindate_create"))) {
                where.append(" and (");
                where.append("t1.createdate >='").append(whereObject.getString("begindate_create")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate_create") && !"".equals(whereObject.getString("enddate_create"))) {
                where.append(" and (");
                where.append("t1.createdate <='").append(whereObject.getString("enddate_create")).append(" 23:59:59' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and (");
                where.append("t1.senddate >='").append(whereObject.getString("begindate")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and (");
                where.append("t1.senddate <='").append(whereObject.getString("enddate")).append(" 23:59:59' ");
                where.append(")");
            }

        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, sat_courseware_testhead)
                .setTableAlias("t1");

        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        for (Row row : rows) {
            if (row.getLong("testaddmode") == 1) {
                row.put("testaddmodestr", "系统随机");
            }
            if (row.getLong("testaddmode") == 2) {
                row.put("testaddmodestr", "自选题目");
            }

            JSONArray jsonArray = row.getJSONArray("testquestions");
            row.put("testquestions", jsonArray);

        }

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


    @API(title = "添加人员和试卷", apiversion = R.ID20240325102202.v1.class)
    public String addTest() throws YosException {
        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        JSONArray userids = content.getJSONArray("userids");

        Rows testheadRows = dbConnect.runSqlQuery("SELECT * from sat_courseware_testhead WHERE sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "'");
        if (testheadRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("考试不存在").toString();
        }
        int testaddmode = testheadRows.get(0).getInteger("testaddmode");
        int num = testheadRows.get(0).getInteger("num");
        JSONArray testquestions = testheadRows.get(0).getJSONArray("testquestions");
        if (testaddmode == 1) {
            if (num > testquestions.size()) {
                return getErrReturnObject().setErrMsg("无法添加，题库数量小于考试题目数量").toString();
            }
            Collections.shuffle(testquestions);
            testquestions = new JSONArray(
                    IntStream.range(0, num)
                            .mapToObj(testquestions::get)
                            .collect(Collectors.toList())
            );
        }
        ArrayList<String> sqlList = CoursewareHelper.getCoursewareTestSql(this, sat_courseware_testheadid, userids, testquestions);
        dbConnect.runSqlUpdate(sqlList);

        return getSucReturnObject().toString();
    }

    @API(title = "删除人员和试卷", apiversion = R.ID20240325105502.v1.class)
    public String deleteTest() throws YosException {
        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        JSONArray userids = content.getJSONArray("userids");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_courseware_test", "sat_courseware_testid");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sat_courseware_testheadid", sat_courseware_testheadid);
        querySQL.setWhere("userid", userids.toArray());
        ArrayList ids = querySQL.query().toArrayList("sat_courseware_testid");

        DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sat_courseware_test");
        deleteSQL.setSiteid(siteid);
        deleteSQL.setWhere("sat_courseware_testid", ids);
        deleteSQL.delete();

        DeleteSQL deleteSQL2 = SQLFactory.createDeleteSQL(this, "sat_courseware_testitems");
        deleteSQL2.setSiteid(siteid);
        deleteSQL2.setWhere("sat_courseware_testid", ids);
        deleteSQL2.delete();

        return getSucReturnObject().toString();
    }

    @API(title = "选择员工列表", apiversion = R.ID20240325110702.v1.class)
    public String query_hrList() throws YosException {
        /*
        过滤条件设置
       */
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.position like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.hrcode like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        boolean containssub = content.getBoolean("containssub");
        JSONArray departmentidsArray = content.getJSONArray("departmentids");
        ArrayList<Long> departmentidsList = new ArrayList<>();
        for (Object o : departmentidsArray) {
            long departmentid = Long.parseLong(o.toString());
            departmentidsList.add(departmentid);
            if (containssub) {
                departmentidsList.addAll(Department.getSubDepartmentIds(this, departmentid));
            }
        }
        where.append(" and not exists(select 1 from  sat_courseware_test where userid = t1.userid and sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "' )");
        SQLFactory sqlFactory = new SQLFactory(this, "员工列表查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("departmentid", departmentidsList);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());


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


    @API(title = "选择营销区域查询经销商成员", apiversion = R.ID20240325130602.v1.class)
    public String query_agent() throws YosException, IOException {
        /*
          过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.position like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");

        boolean containssub = content.getBoolean("containssub");
        JSONArray sa_saleareaidsArray = content.getJSONArray("sa_saleareaids");
        ArrayList<Long> sa_saleareaidsList = new ArrayList<>();
        for (Object o : sa_saleareaidsArray) {
            Long sa_saleareaid = Long.valueOf(o.toString());
            sa_saleareaidsList.add(sa_saleareaid);
            if (containssub) {
                sa_saleareaidsList.addAll(SaleArea.getSubSaleAreaIds(this, sa_saleareaid));
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_enterprise_hr",
                        "userid", "name", "position")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid",
                "enterprisename", "province", "city", "county", "address");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t3.sys_enterpriseid=t1.sys_enterpriseid and t3.siteid=t1.siteid",
                "type", "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_tradefield", "t4", "t4.sys_enterpriseid=t1.sys_enterpriseid and t3.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t5", "t5.sa_saleareaid=t4.sa_saleareaid and t5.siteid=t4.siteid",
                "areaname");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere("t4.sa_saleareaid", sa_saleareaidsList);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("not exists(select 1 from  sat_courseware_test where userid = t1.userid and sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "' )");
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

        for (Row row : rows) {
            row.put("pcc", row.getString("province") + row.getString("city") + row.getString("county"));

        }


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

    @API(title = "（部门）人员列表", apiversion = R.ID20240325134302.v1.class)
    public String queryhrList() throws YosException {

        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_hr",
                        "hrid", "hrcode", "name", "phonenumber", "position", "userid")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_department", "t2", "t1.siteid = t2.siteid and t1.departmentid = t2.departmentid",
                "depname", "depfullname");
        querySQL.setWhere(" exists(select 1 from  sat_courseware_test where userid = t1.userid and sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "' )");
        Rows rows = querySQL.query();


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

    @API(title = "（区域）人员列表", apiversion = R.ID20240325135402.v1.class)
    public String queryagent() throws YosException, IOException {

        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_enterprise_hr",
                        "userid", "name", "position")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sys_enterprise", "t2", "t1.siteid = t2.siteid and t1.sys_enterpriseid = t2.sys_enterpriseid",
                "enterprisename");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t3.sys_enterpriseid=t1.sys_enterpriseid and t3.siteid=t1.siteid",
                "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_tradefield", "t4", "t4.sys_enterpriseid=t1.sys_enterpriseid and t3.siteid=t1.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_salearea", "t5", "t5.sa_saleareaid=t4.sa_saleareaid and t5.siteid=t4.siteid",
                "areaname");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(" exists(select 1 from  sat_courseware_test where userid = t1.userid and sat_courseware_testheadid=" + sat_courseware_testheadid + " and siteid='" + siteid + "' )");
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();


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


    @API(title = "考试概况列表", apiversion = R.ID20240325141602.v1.class)
    public String testlist() throws YosException {

        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.name like'%").append(whereObject.getString("condition")).append("%' ");

                where.append(")");
            }
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_courseware_test").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sat_courseware_testhead", "t2", "t2.sat_courseware_testheadid=t1.sat_courseware_testheadid and t2.siteid=t1.siteid"
                , "num");
        querySQL.setWhere(where.toString());
        querySQL.setSiteid(siteid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        for (Row row : rows) {
            row.put("answercount", row.getString("answercount") + "/" + row.getString("num"));
            row.putIfAbsent("score", 0);
            row.putIfAbsent("begdate", "");
            row.putIfAbsent("submitdate", "");
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "考试概况列表", apiversion = R.ID20240325143202.v1.class)
    public String testinfo() throws YosException {

        Long sat_courseware_testheadid = content.getLongValue("sat_courseware_testheadid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_courseware_test").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sat_courseware_testhead", "t2", "t2.sat_courseware_testheadid=t1.sat_courseware_testheadid and t2.siteid=t1.siteid"
                , "num", "passingscore");
        querySQL.setWhere("sat_courseware_testheadid", sat_courseware_testheadid);
        querySQL.setSiteid(siteid);
        Rows rows = querySQL.query();
        Row detailRow = rows.isNotEmpty() ? rows.get(0) : new Row();
        detailRow.putIfAbsent("score", 0);
        detailRow.putIfAbsent("begdate", "");
        detailRow.putIfAbsent("submitdate", "");
        BigDecimal passingscore = detailRow.getBigDecimal("passingscore");
        BigDecimal score = detailRow.getBigDecimal("score");
        if (score.compareTo(passingscore) >= 0) {
            detailRow.put("ispassingscore", "及格");
        } else {
            detailRow.put("ispassingscore", "不及格");
        }

        QuerySQL querySQL2 = SQLFactory.createQuerySQL(this, "sat_courseware_testitems").setTableAlias("t1");
        querySQL2.setSiteid(siteid)
                .setWhere("sat_courseware_testid", detailRow.getLong("sat_courseware_testid"));

        Rows rows2 = querySQL2.query();

        detailRow.put("testquestions", rows2);
        return getSucReturnObject().setData(detailRow).toString();
    }

    @API(title = "查询我的考试列表", apiversion = R.ID20240326133302.v1.class)
    public String queryUserTestList() throws YosException {


        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.title like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t2.status='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("sat_courseware_classids") && !"".equals(whereObject.getString("sat_courseware_classids"))) {
                JSONArray sat_courseware_classids = whereObject.getJSONArray("sat_courseware_classids");
                if (sat_courseware_classids.size() > 0) {
                    where.append(" and (1=2");
                    for (Object obj : sat_courseware_classids) {
                        JSONArray array = (JSONArray) obj;
                        for (Object obj2 : array) {
                            where.append(" or (");
                            where.append("JSON_CONTAINS(t1.sat_courseware_classids,'" + obj2 + "')");
                            where.append(")");
                        }
                    }
                    where.append(")");
                }
            }
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_courseware_testhead"
                        , "sat_courseware_testheadid", "title", "num")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.inner, "sat_courseware_test", "t2", "t2.sat_courseware_testheadid=t1.sat_courseware_testheadid and t2.siteid=t1.siteid"
                , "sat_courseware_testid", "status", "score", "answercount");
        querySQL.setWhere(where.toString());
        if (!userInfo.isSysAdministrator()) {
            querySQL.setWhere("t2.userid", userid);
        }

        querySQL.setSiteid(siteid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        for (Row row : rows) {
            row.put("answerinfo", row.getString("answercount") + "/" + row.getString("num"));
            row.putIfAbsent("score", 0.0);
        }
        return getSucReturnObject().setData(rows).toString();
    }

}