gradepaperhelper.java

采用Eclispe开发平台

package com.whatratimes.webedu.gradepaper;

import com.whatratimes.dbms.QuerySQLExecuter;
import com.whatratimes.dbms.UserSQLExecuter;
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 javax.servlet.http.HttpSession;
import java.util.ArrayList;
import java.util.Hashtable;

/**
 * User: Tao
 * Date: Jun 20, 2003
 * Time: 7:31:04 AM
 */
public class GradePaperHelper {
    public static void setExams(Integer class_id, Integer course_id, HttpSession session) {

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_exam");

        sql.appendTableName("t_exam", "a");
        sql.appendTableName("t_class_course", "b");

        sql.setDistinct();
        sql.appendColumn("a.ExamId", "ExamId");
        sql.appendColumn("a.ExamName", "ExamName");

        sql.appendWhereClause("b.State = 1 ");

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

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

        t_user user = (t_user) session.getAttribute("Teacher");

        sql.appendWhereCondition("b.TeacherId", "=", new Integer(user.UserId));

        sql.appendWhereClause(
                "a.CourseId = b.CourseId AND a.QuestionType <> 1"
        );

        Object[] objs = sql.executeQuery();

        ArrayList en = new ArrayList();

        en.add(new LabelValueBean("ȫѡ", "0"));

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

            en.add(new LabelValueBean(e.ExamName, String.valueOf(e.ExamId)));

        }

        session.setAttribute("SearchExams", en);

    }

    public static StringBuffer SP = new StringBuffer(
            "SELECT " +
                    "a.SubmitId SubmitId, " +
                    "a.PaperId PaperId, " +
                    "f.UserName UserName, " +
                    "f.UserId UserId, " +
                    "f.Email Email, " +
                    "g.ClassName ClassName, " +
                    "e.CourseName CourseName, " +
                    "d.ExamName ExamName, " +
                    "a.State State, " +
                    "a.Mark Mark, " +
                    "a.InstanceId InstanceId, " +
                    "a.StartTime StartTime, " +
                    "a.SubmitTime SubmitTime " +
                    "FROM " +
                    "t_exam_submit a, " +
                    "t_student b, " +
                    "t_exam_assigned c, " +
                    "t_exam d, " +
                    "t_course e, " +
                    "t_user f, " +
                    "t_class g, " +
                    "t_class_course h " +
                    "WHERE " +
                    "a.State > 1 AND " +
                    "g.State = 1 AND " +
                    "h.State = 1 AND " +
                    "a.UserId = b.UserId AND " +
                    "a.UserId = f.UserId AND " +
                    "b.ClassId = g.ClassId AND " +
                    "a.AssignmentId = c.AssignmentId AND " +
                    "c.ExamId = d.ExamId AND " +
                    "d.CourseId = e.CourseId AND " +
                    "d.QuestionType <> 1 AND " +
                    "e.CourseId = h.CourseId AND " +
                    "b.ClassId = h.ClassId AND "
    );

    public static void setSearchedPapers(DynaActionForm form, HttpSession session) {

        Integer class_id = (Integer) form.get("ClassId");
        Integer course_id = (Integer) form.get("CourseId");
        Integer exam_id = (Integer) form.get("ExamId");
        Integer state = (Integer) form.get("GradeState");

        t_user user = (t_user) session.getAttribute("Teacher");

        StringBuffer sb = new StringBuffer(SP.toString());

        sb.append("h.TeacherId = " + user.UserId);

        if (class_id.intValue() != 0) {
            sb.append(" AND b.ClassId = " + class_id);
        }

        if (course_id.intValue() != 0) {
            sb.append(" AND e.CourseId = " + course_id);
        }

        if (exam_id.intValue() != 0) {
            sb.append(" AND d.ExamId = " + exam_id);
        }

        if (state.intValue() != 0) {
            sb.append(" AND a.State = " + state);
        }

        Object[] objs = UserSQLExecuter.executeQuery(sb.toString());

        session.setAttribute("Papers", objs);

    }

    public static void setPaper(Integer submit_id, HttpSession session) {
        t_user user = (t_user) session.getAttribute("Teacher");

        StringBuffer sb = new StringBuffer(SP.toString());

        sb.append("h.TeacherId = " + user.UserId);

        if (submit_id != null) {
            sb.append(" AND a.SubmitId = " + submit_id);
        }

        Object[] objs = UserSQLExecuter.executeQuery(sb.toString());

        if (objs.length > 0) {
            session.setAttribute("Paper", objs[0]);
        }

    }


    public static void setQuestionInstances(DynaActionForm form, Integer question_id, HttpSession session) {

        t_user user = (t_user) session.getAttribute("Teacher");

        StringBuffer sb = new StringBuffer(
                "SELECT " +
                        "a.SubmitId SubmitId, " +
                        "a.QuestionSeq QuestionSeq, " +
                        "e.QuestionId QuestionId, " +
                        "h.ExamName ExamName, " +
                        "b.PaperId PaperId, " +
                        "a.Mark Mark, " +
                        "g.UserId UserId, " +
                        "g.Email Email, " +
                        "g.UserName UserName " +
                        "FROM " +
                        "t_exam_question_submit a," +
                        "t_exam_submit b, " +
                        "t_exam_assigned c, " +
                        "t_exam_question d, " +
                        "t_question e, " +
                        "t_class_course f, " +
                        "t_user g, " +
                        "t_exam h, " +
                        "t_student i, " +
                        "t_class j " +
                        "WHERE " +
                        "b.State < 5 AND b.State > 1 AND " +
                        "f.State = 1 AND " +
                        "j.State = 1 AND " +
                        "a.SubmitId = b.SubmitId AND " +
                        "b.AssignmentId = c.AssignmentId AND " +
                        "c.ExamId = d.ExamId AND " +
                        "c.ExamId = h.ExamId AND " +
                        "h.QuestionType <> 1 AND " +
                        "b.PaperId = d.PaperId AND " +
                        "b.UserId = g.UserId AND " +
                        "a.QuestionSeq = d.QuestionSeq AND " +
                        "d.QuestionId = e.QuestionId AND " +
                        "b.UserId = i.UserId AND " +
                        "i.ClassId = f.ClassId AND " +
                        "j.ClassId = f.ClassId AND " +
                        "f.CourseId = e.CourseId "
        );

        sb.append("AND f.TeacherId = " + user.UserId);

        Integer class_id = (Integer) form.get("ClassId");
        Integer course_id = (Integer) form.get("CourseId");
        Integer exam_id = (Integer) form.get("ExamId");
        Integer state = (Integer) form.get("GradeState");

        if (class_id.intValue() != 0) {
            sb.append(" AND f.ClassId = " + class_id);
        }

        if (course_id.intValue() != 0) {
            sb.append(" AND f.CourseId = " + course_id);
        }

        if (exam_id.intValue() != 0) {
            sb.append(" AND d.ExamId = " + exam_id);
        }


        if (state.intValue() == 1) {
            sb.append(" AND a.Mark = -1 ");
        }

        if (state.intValue() == 2) {
            sb.append(" AND a.Mark > -1 ");
        }