studyhelper.java

JAVA编写的远程教育程序,包含了教育学的基本思想和网络应用程序的基本开发模式

package com.whatratimes.webedu.student;

import com.whatratimes.tables.t_user;
import com.whatratimes.tables.t_class;
import com.whatratimes.tables.t_student;
import com.whatratimes.dbms.UserSQLExecuter;
import com.whatratimes.dbms.QuerySQLExecuter;
import com.whatratimes.dbms.InsertSQLExecuter;
import com.whatratimes.webedu.gradepaper.GradePaperHelper;

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

/**
 * User: Tao
 * Date: Jun 14, 2003
 * Time: 9:08:13 PM
 */
public class StudyHelper
{

    public static void setCourses(HttpSession session)
    {

        t_class myClass = (t_class) session.getAttribute("Class");

        Object[] objs = UserSQLExecuter.executeQuery(
                "SELECT " +
                "c.CourseId CourseId, " +
                "c.CourseName CourseName, " +
                "c.CourseUrl CourseUrl, " +
                "t.UserName TeacherName " +
                "FROM " +
                "t_class_course b, " +
                "t_course c, " +
                "t_user t " +
                "WHERE " +
                "b.CourseId = c.CourseId AND " +
                "b.TeacherId = t.UserId AND " +
                "b.ClassId = " + myClass.ClassId
        );

        session.setAttribute("Courses", objs);

    }


    public static t_class setClass(t_student student, HttpSession session)
    {

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_class");
        sql.appendTableName("t_class");
        sql.appendColumn("*");
        sql.appendWhereCondition("ClassId", "=", new Integer(student.ClassId));

        Object[] objs = sql.executeQuery();
        if (objs.length > 0)
        {
            session.setAttribute("Class", objs[0]);
            return (t_class) objs[0];
        } else
        {
            return null;
        }
    }

    public static t_student setStudent(t_user user, HttpSession session)
    {

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_student");
        sql.appendTableName("t_student");
        sql.appendColumn("*");
        sql.appendWhereCondition("UserId", "=", new Integer(user.UserId));

        Object[] objs = sql.executeQuery();
        if (objs.length > 0)
        {
            session.setAttribute("Student", objs[0]);
            return (t_student) objs[0];
        } else
        {
            return null;
        }
    }

    public static Hashtable getMaxInstanceOfAssignment( int user_id)
    {
        Object[] objs = UserSQLExecuter.executeQuery(
                "SELECT " +
                    "AssignmentId AssignmentId, " +
                    "max(InstanceId) InstanceId, " +
                    "count(InstanceId) nCount, " +
                    "max(Mark) Mark " +
                "FROM " +
                    "t_exam_submit " +
                "WHERE " +
                    "UserId = " + user_id + " AND " +
                    "State > 1  " +
                "GROUP BY " +
                    "AssignmentId"
        );

        Hashtable hash = new Hashtable();
        for (int i = 0; i < objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            hash.put(h.get("AssignmentId"), objs[i]);
        }

        return hash;

    }

    public static void setExamTable(t_user user, int course_id, HttpSession session)
    {
        //考试名称、可提交次数、已提交次数、状态、分数
        Object[] objs;

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendTableName("t_exam", "a");
        sql.appendTableName("t_exam_assigned", "b");
        sql.appendTableName("t_exam_assigned_student", "c");

        sql.appendColumn("b.AssignmentId", "AssignmentId");
        sql.appendColumn("b.StartTime", "StartTime");
        sql.appendColumn("b.EndTime", "EndTime");
        sql.appendColumn("a.ExamId", "ExamId");
        sql.appendColumn("a.QuestionType", "QuestionType");
        sql.appendColumn("a.ExamType", "ExamType");
        sql.appendColumn("a.ExamName", "ExamName");
        sql.appendColumn("a.Duration", "Duration");
        sql.appendColumn("a.InstanceLimit", "InstanceLimit");

        sql.appendWhereClause(
                "a.ExamId = b.ExamId AND " +
                "c.AssignmentId = b.AssignmentId AND " +
                "c.UserId = " + user.UserId + " AND " +
                "a.CourseId = " + course_id
        );

        Date now = new Date();
        sql.appendWhereCondition("b.StartTime", "<", now);
        sql.appendWhereCondition("b.EndTime", ">", now);

        objs = sql.executeQuery();

        Hashtable max = getMaxInstanceOfAssignment(user.UserId);

        for (int i = 0; i < objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            if (max.containsKey(h.get("AssignmentId")))
            {
                Integer submitted = (Integer) ((Hashtable) max.get(h.get("AssignmentId"))).get("InstanceId");
                Integer limit = (Integer) h.get("InstanceLimit");

                h.put("Submitted", submitted);
                h.put("Mark", ((Hashtable) max.get(h.get("AssignmentId"))).get("Mark"));

                if (limit.intValue() > submitted.intValue())
                {
                    h.put("NextInstanceId", new Integer(submitted.intValue() + 1));
                    h.put("State", new Integer(3));
                } else
                {
                    h.put("State", new Integer(4));
                }

            } else
            {
                h.put("Submitted", new Integer(0));
                h.put("NextInstanceId", new Integer(1));
                h.put("State", new Integer(1));
                h.put("Mark", new Integer(0));
            }

            Object[] temps = UserSQLExecuter.executeQuery(
                    "select InstanceId  from t_exam_submit " +
                    "where State = 1 AND UserId = " + user.UserId + " AND AssignmentId = " + h.get("AssignmentId")
            );

            if (temps.length > 0)
            {
                h.put("State", new Integer(2));

            }


        }

        session.setAttribute("ExamTable", objs);

    }

    public static void setInstanceTable(t_user user, int course_id, HttpSession session)
    {
        //考试名称、提交次数、状态、分数

        Object[] objs = UserSQLExecuter.executeQuery(
                "SELECT " +
                    "a.SubmitId SubmitId, " +
                    "b.ExamName ExamName, " +
                    "a.InstanceId InstanceId, " +
                    "a.State State, " +
                    "a.Mark Mark, " +
                    "a.AssignmentId AssignmentId " +
                "FROM " +
                    "t_exam_submit a, " +
                    "t_exam b, " +
                    "t_exam_assigned d " +
                "WHERE " +
                    "a.UserId = " + user.UserId + " AND " +
                    "b.CourseId = " + course_id + " AND " +
                    "b.ExamId = d.ExamId AND " +
                    "d.AssignmentId = a.AssignmentId "

        );

        session.setAttribute("InstanceTable", objs);

    }

    public static void setQuestions(Integer submit_id, int question_type, HttpSession session)
    {
        Object objs[] = UserSQLExecuter.executeQuery(
                "SELECT " +
                    "a.QuestionSeq QuestionSeq, " +
                    "b.QuestionId QuestionId, " +
                    "b.ReferenceUrl ReferenceUrl, " +
                    "b.Content Content " +
                "FROM " +
                    "t_exam_question a, " +
                    "t_question b, " +
                    "t_exam_submit c, " +
                    "t_exam_assigned d " +
                "WHERE " +
                    "a.QuestionId = b.QuestionId AND " +
                    "c.AssignmentId = d.AssignmentId AND " +
                    "d.ExamId = a.ExamId AND " +
                    "a.PaperId = c.PaperId AND " +
                    "c.SubmitId = " + submit_id + " " +
                "ORDER BY " +
                    "a.QuestionSeq ASC"
        );

        session.setAttribute("Questions", objs);

        if (question_type == 1) // Choices
        {
            setChoiceQuestion(submit_id, session);

        }
    }

    private static void setChoiceQuestion(Integer submit_id, HttpSession session)
    {
        Object choices[] = UserSQLExecuter.executeQuery(
                "SELECT " +
                    "a.QuestionSeq QuestionSeq, " +
                    "a.QuestionId QuestionId, " +
                    "c.ChoiceId ChoiceId, " +
                    "c.ChoiceContent ChoiceContent, " +
                    "c.IsAnswer IsAnswer " +
                "FROM " +
                    "t_exam_question a, " +
                    "t_question_choice c, " +
                    "t_exam_submit d, " +
                    "t_exam_assigned e " +
                "WHERE " +
                    "a.QuestionId = c.QuestionId AND " +
                    "a.ExamId =   e.ExamId AND " +
                    "a.PaperId = d.PaperId AND " +
                    "d.AssignmentId = e.AssignmentId AND " +
                    "d.SubmitId = " + submit_id
        );

        TreeMap questions = new TreeMap();

        for (int i = 0; i < choices.length; i++)
        {