setexamhelper.java

采用Eclispe开发平台

package com.whatratimes.webedu.teacher;

import com.whatratimes.dbms.QuerySQLExecuter;
import com.whatratimes.dbms.UserSQLExecuter;
import com.whatratimes.tables.t_course;
import com.whatratimes.tables.t_exam;
import com.whatratimes.tables.t_user;
import org.apache.struts.action.DynaActionForm;
import org.apache.struts.util.LabelValueBean;
import org.apache.struts.validator.DynaValidatorActionForm;

import javax.servlet.http.HttpSession;
import java.util.*;

/**
 * User: Tao
 * Date: Jun 12, 2003
 * Time: 10:10:54 PM
 */


/**
 * 增加了getAssignedExam 中的SQL的WHERE子句
 */
public class SetExamHelper
{
    public static void setTeacherCourses(int teacher_id, HttpSession session)
    {

        UserSQLExecuter sql = new UserSQLExecuter (
                "SELECT DISTINCT a.CourseId CourseId, a.CourseName CourseName " +
                "FROM t_course a, t_class_course b, t_class c " +
                "WHERE a.CourseId = b.CourseId AND b.ClassId=c.ClassId AND b.State=1 AND c.State = 1 AND b.TeacherId = " + teacher_id
                );

        ArrayList cn = new ArrayList();

        Object[] objs = sql.executeQuery();

        cn.add(new LabelValueBean("全选", "0"));

        for (int i = 0; i < objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];

            cn.add(new LabelValueBean(h.get("CourseName").toString(), h.get("CourseId").toString()));

        }

        session.setAttribute("SearchCourses", cn);

    }

    public static void setTeacherClasses(int teacher_id, HttpSession session)
    {

        UserSQLExecuter sql = new UserSQLExecuter(
                "SELECT DISTINCT a.ClassId ClassId, a.ClassName ClassName " +
                "FROM t_class a, t_class_course b " +
                "WHERE a.ClassId = b.ClassId and b.State=1 and a.State=1 and b.TeacherId = " + teacher_id
        );

        ArrayList cn = new ArrayList();

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];

            cn.add(new LabelValueBean(h.get("ClassName").toString(), h.get("ClassId").toString()));

        }

        ArrayList scn = new ArrayList(cn);
        scn.add(new LabelValueBean("全选", "0"));
//        scn.add(new LabelValueBean("请选择", "0"));
        session.setAttribute("SearchClasses", scn);
        session.setAttribute("Classes", cn);
        ArrayList scnc = new ArrayList(cn);
        scnc.add(new LabelValueBean("请选择", "0"));
        session.setAttribute("CreateAssignClasses", scnc);


    }

    public static Hashtable getAssignedExam(Integer assign_id)
    {

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendTableName("t_class", "a");
        sql.appendTableName("t_course", "b");
        sql.appendTableName("t_exam", "c");
        sql.appendTableName("t_exam_assigned", "d");
        sql.appendTableName("t_class_course", "e");

        sql.appendColumn("d.AssignmentId", "AssignmentId");
        sql.appendColumn("d.ExamId", "ExamId");
        sql.appendColumn("d.StartTime", "StartTime");
        sql.appendColumn("d.EndTime", "EndTime");
        sql.appendColumn("d.Tag", "Tag");

        sql.appendColumn("c.ExamName", "ExamName");
        sql.appendColumn("c.ExamType", "ExamType");
        sql.appendColumn("c.QuestionType", "QuestionType");
        sql.appendColumn("a.ClassId", "ClassId");
        sql.appendColumn("b.CourseId", "CourseId");
        sql.appendColumn("a.ClassName", "ClassName");
        sql.appendColumn("b.CourseName", "CourseName");
        sql.appendColumn("c.InstanceLimit", "InstanceLimit");

        sql.appendWhereClause("d.ExamId = c.ExamId");
        sql.appendWhereClause("c.CourseId = e.CourseId");
        sql.appendWhereClause("b.CourseId = e.CourseId");
        sql.appendWhereClause("e.ClassId = a.ClassId ");
        sql.appendWhereClause("e.TeacherId = d.TeacherId ");

        sql.appendWhereCondition("d.AssignmentId", "=", assign_id);

        //增加班级的限制条件,2004-04-07 WangTao
        sql.appendWhereClause( "e.ClassId in ( select distinct a.ClassId ClassId from t_student a, t_exam_assigned_student b where a.UserId = b.UserId AND AssignmentId = " + assign_id + " ) ");


        Object[] objs = sql.executeQuery();

        if (objs.length > 0)
        {
            return (Hashtable) objs[0];
        } else
        {
            return null;
        }

    }

    public static void setSearchedAssignedExams(String attribute, DynaActionForm form, HttpSession session)
    {

        // 修改布置考试列表中出现其它班级已布置的考试 2004-04-10
        // 去掉了 setSearchAssignedExamsConditions 方法,
        // 更改了SQL
        t_user user = (t_user) session.getAttribute("User");

        Integer course_id = (Integer) form.get("CourseId");
        Integer class_id = (Integer) form.get("ClassId");
        Integer exam_type = (Integer) form.get("ExamState");

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendTableName("t_class", "a");
        sql.appendTableName("t_course", "b");
        sql.appendTableName("t_exam", "c");
        sql.appendTableName("t_exam_assigned", "d");
        sql.appendTableName("t_exam_assigned_student", "e");
        sql.appendTableName("t_student", "f");
        sql.appendTableName("t_class_course", "g");


        sql.appendWhereClause("d.ExamId = c.ExamId");
        sql.appendWhereClause("c.CourseId = b.CourseId");
//        d.assignmentid = e.assignmentid AND
//        e.userid = f.userid AND
//        f.classid = a.classid AND
//        g.classid = a.classid AND
//        g.courseid = b.courseid AND
//        g.teacherid = ?
        sql.appendWhereClause("d.AssignmentId = e.AssignmentId ");
        sql.appendWhereClause("e.UserId = f.UserId ");
        sql.appendWhereClause("f.ClassId = a.ClassId ");
        sql.appendWhereClause("g.ClassId = a.ClassId ");
        sql.appendWhereClause("g.CourseId = b.CourseId ");
        sql.appendWhereClause("g.TeacherId = " + user.UserId + " ");

        if (course_id.intValue() > 0)
        {
            sql.appendWhereCondition("b.CourseId", "=", course_id);
        }

        if (class_id.intValue() > 0)
        {
            sql.appendWhereCondition("a.ClassId", "=", class_id);
        }

        Date d = new Date();

        if (exam_type.intValue() == 1)
        {
            sql.appendWhereCondition("d.StartTime", ">", d);
        } else if (exam_type.intValue() == 2)
        {
            sql.appendWhereCondition("d.StartTime", "<", d);
            sql.appendWhereCondition("d.EndTime", ">", d);
        } else if (exam_type.intValue() == 3)
        {
            sql.appendWhereCondition("d.EndTime", "<", d);
        }

        sql.appendColumn("d.AssignmentId", "AssignmentId");
        sql.appendColumn("c.ExamName", "ExamName");
        sql.appendColumn("a.ClassName", "ClassName");
        sql.appendColumn("b.CourseName", "CourseName");
        sql.appendColumn("d.StartTime", "StartTime");
        sql.appendColumn("d.EndTime", "EndTime");
        sql.appendColumn("c.InstanceLimit", "InstanceLimit");


//        setSearchAssignedExamsConditions(form, sql, session );

        sql.setDistinct();

        Object[] objs = sql.executeQuery();

        long now = System.currentTimeMillis();

        for (int i = 0; i < objs.length; i++)
        {
            Hashtable this_exam = (Hashtable) objs[i];
            Date s = (Date) this_exam.get("StartTime");
            Date e = (Date) this_exam.get("EndTime");
            if (now < s.getTime())
            {
                this_exam.put("State", new Integer(1));

            } else if (now < e.getTime())
            {
                this_exam.put("State", new Integer(2));

            } else
            {
                this_exam.put("State", new Integer(3));
            }
        }

        session.setAttribute(attribute, objs);


    }

    private static void setSearchAssignedExamsConditions(DynaActionForm form, QuerySQLExecuter sql, HttpSession session )
    {
    }

    public static int setNumberOfSearchedQuestion(String attribute, DynaValidatorActionForm form, HttpSession session)
    {
        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendTableName("t_question", "q");

        sql.appendColumn("count(*) n");

//        setSearchQuestionsCondition(form, sql);

        Object[] obj = sql.executeQuery();

        int rows = Integer.valueOf(((Hashtable) obj[0]).get("n").toString()).intValue();

        session.setAttribute(attribute, new Integer(rows));

        return rows;

    }

    public static void setStudents(Integer class_id, HttpSession session)
    {

        ArrayList sl = new ArrayList();

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_user");
        sql.appendTableName("t_user", "a");
        sql.appendTableName("t_student", "b");
        sql.appendColumn("a.UserId", "UserId");
        sql.appendColumn("a.UserName", "UserName");
        sql.appendWhereClause("a.UserId = b.UserId");
        sql.appendWhereCondition("b.ClassId", "=", class_id);

        sql.appendOrderByASC("a.UserName");

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            t_user s = (t_user) objs[i];
            sl.add(new LabelValueBean(s.UserName, String.valueOf(s.UserId)));
        }

        session.setAttribute("Students", sl);

    }

    public static void setCourses(Integer class_id, HttpSession session)
    {
        t_user user = (t_user) session.getAttribute("User");

        ArrayList cl = new ArrayList();

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_course");
        sql.appendTableName("t_course", "a");
        sql.appendTableName("t_class_course", "b");
        sql.appendColumn("a.CourseId", "CourseId");
        sql.appendColumn("a.CourseName", "CourseName");
        sql.appendWhereClause("a.CourseId = b.CourseId");
        sql.appendWhereCondition("b.ClassId", "=", class_id);
        sql.appendWhereCondition("b.TeacherId", "=", new Integer(user.UserId));

        sql.appendOrderByASC("a.CourseName");

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            t_course c = (t_course) objs[i];
            cl.add(new LabelValueBean(c.CourseName, String.valueOf(c.CourseId)));
        }

        cl.add(0, new LabelValueBean("请选择", "0"));
        session.setAttribute("Courses", cl);

    }

    public static void setExams(Integer course_id, HttpSession session)
    {

        ArrayList el = new ArrayList();

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_exam");
        sql.appendTableName("t_exam");
        sql.appendColumn("ExamId");
        sql.appendColumn("ExamName");
        sql.appendWhereCondition("CourseId", "=", course_id);
        sql.appendWhereCondition("State", "=", new Integer(1));

        sql.appendOrderByASC("ExamName");

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            t_exam e = (t_exam) objs[i];
            el.add(new LabelValueBean(e.ExamName, String.valueOf(e.ExamId)));
        }

        el.add(0, new LabelValueBean("请选择", "0"));
        session.setAttribute("Exams", el);

    }

    public static void setPapers(Integer exam_id, HttpSession session)
    {

        ArrayList pl = new ArrayList();

        UserSQLExecuter sql = new UserSQLExecuter(
                "SELECT DISTINCT PaperId from t_exam_question " +
                "where ExamId = " + exam_id +
                " ORDER BY PaperId"
        );

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            Hashtable p = (Hashtable) objs[i];
            pl.add(p.get("PaperId"));
        }

        session.setAttribute("Papers", pl);

    }

    public static void clearAssignment(Integer assignment_id)
    {
        UserSQLExecuter sql = new UserSQLExecuter(
                "delete from t_exam_assigned_student where AssignmentId = " + assignment_id
        );

        sql.executeUpdate();

        sql = new UserSQLExecuter(
                "delete from t_exam_assigned_paper where AssignmentId = " + assignment_id
        );
        sql.executeUpdate();
    }

    public static Collection getAssignmentPapers(Integer assign_id)
    {

        Vector r = new Vector();
        UserSQLExecuter sql = new UserSQLExecuter(
                "select PaperId from t_exam_assigned_paper where AssignmentId = " + assign_id
        );

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            r.add(((Hashtable) objs[i]).get("PaperId"));
        }

        return r;

    }

    public static Collection getAssignmentStudents(Integer assign_id)
    {
        Vector r = new Vector();
        UserSQLExecuter sql = new UserSQLExecuter(
                "select UserId from t_exam_assigned_student where AssignmentId = " + assign_id
        );

        Object[] objs = sql.executeQuery();

        for (int i = 0; i < objs.length; i++)
        {
            r.add(((Hashtable) objs[i]).get("UserId").toString());
        }

        return r;

    }
}