studentshelper.java

采用Eclispe开发平台

package com.whatratimes.edu.webedu.studentmngt;

import org.apache.struts.action.DynaActionForm;
import org.apache.struts.util.LabelValueBean;

import javax.servlet.http.HttpSession;

import com.whatratimes.dbms.QuerySQLExecuter;
import com.whatratimes.dbms.UserSQLExecuter;
import com.whatratimes.edu.webedu.AttributeHelper;
import com.whatratimes.webedu.student.StudyHelper;
import com.whatratimes.tables.t_class;
import com.whatratimes.tables.t_user;

import java.util.Hashtable;
import java.util.ArrayList;

/**
 * User: Tao
 * Date: Jun 30, 2003
 * Time: 9:34:24 PM
 */
public class StudentsHelper
{
    public static void setClassNames(HttpSession session)
    {
        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.setReturnClassName("t_class");
        sql.appendTableName("t_class");
        sql.appendColumn("*");

        Object[] objs = sql.executeQuery();

        Hashtable ss = new Hashtable();
        for (int i = 0; i < objs.length; i++)
        {
            t_class c = (t_class) objs[i];
            ss.put(new Integer(c.ClassId), c.ClassName);
        }

        ss.put(new Integer(0), "");

        session.setAttribute("ClassNames", ss);

    }

    public static void setSearchedStudents(HttpSession session, DynaActionForm form, int page)
    {
        t_user user = (t_user) session.getAttribute("User");

        String school_id = (String) form.get("SchoolId");
        String user_name = (String) form.get("UserName");
        String gender = (String) form.get("Gender");
        String email = (String) form.get("Email");
        Integer course_id = (Integer) form.get("CourseId");
        Integer class_id = (Integer) form.get("ClassId");

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendTableName("t_user", "a");
        sql.appendTableName("t_student", "b");
        sql.appendWhereClause("a.UserId = b.UserId");

        if (school_id.length() > 1)
        {
            sql.appendWhereCondition("a.SchoolId", "=", school_id);
        } else if (session.getAttribute("CNTR") == null)
        {
            sql.appendWhereCondition("a.SchoolId", "=", user.SchoolId);
        }

        if ((!"0".equals(gender)) && (!gender.equals("")))
        {
            sql.appendWhereCondition("a.Gender", "=", gender);
        }

        if (email.length() > 0)
        {
            sql.appendWhereCondition("a.Email", "=", email);
        }

        if (class_id.intValue() > 0)
        {
            sql.appendWhereCondition("b.ClassId", "=", class_id);
        } else
        {
            if (user.UserType == 2)
            {
                sql.appendWhereClause("b.ClassId in ( select distinct ClassId from t_class_course where TeacherId = " + user.UserId + " ) ");
            }

        }

        if (user_name.length() > 0)
        {
            sql.appendWhereClause("(a.UserName LIKE '%" + user_name + "%' OR a.LoginName LIKE '%" + user_name + "%' )");
        }

        if (course_id.intValue() > 0)
        {
            sql.appendTableName("t_class_course", "c");
            sql.appendWhereClause("b.ClassId = c.ClassId ");
            sql.appendWhereCondition("c.CourseId", "=", course_id);
        } else
        {
            if (user.UserType == 2)
            {
                sql.appendTableName("t_class_course", "c");
                sql.appendWhereClause("b.ClassId = c.ClassId ");
                sql.appendWhereCondition("c.TeacherId", "=", new Integer(user.UserId));
            }

        }

        sql.setDistinct();

        if (page == 0)
        {
            // set pages
            sql.appendColumn("count(*)", "nCount");

            Object[] objs = sql.executeQuery();
            Integer rows = (Integer) ((Hashtable) objs[0]).get("nCount");
            session.setAttribute("TotalRows", rows);
            session.setAttribute("TotalPages", new Integer((rows.intValue() - 1 + AttributeHelper.NumberOfRowsInOnePage) / AttributeHelper.NumberOfRowsInOnePage));
            sql.clearAllColumns();
            page = 1;
        }

        session.setAttribute("CurrentPage", new Integer(page));

//        sql.appendTableName("t_class", "d");
//        sql.appendWhereClause(" b.ClassId = d.ClassId");
        sql.appendColumn("a.UserId", "UserId");
        sql.appendColumn("a.UserName", "UserName");
        sql.appendColumn("a.SchoolId", "SchoolId");
        sql.appendColumn("b.ClassId", "ClassId");
        sql.appendColumn("a.LoginName", "LoginName");
        sql.appendColumn("a.Email", "Email");


        sql.setRowLimit((page - 1) * AttributeHelper.NumberOfRowsInOnePage, AttributeHelper.NumberOfRowsInOnePage);

        Object[] objs = sql.executeQuery();

        session.setAttribute("Students", objs);

        setClassNames(session);


    }

    public static void setSearchConditions(HttpSession session)
    {
        Object[] objs = UserSQLExecuter.executeQuery("select ClassId, ClassName from t_class");

        ArrayList classes = new ArrayList();
        for (int i = 0; i < objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            classes.add(new LabelValueBean(h.get("ClassName").toString(), h.get("ClassId").toString()));
        }

        classes.add(0, new LabelValueBean("不限", "0"));
        session.setAttribute("SearchClass", classes);

        objs = UserSQLExecuter.executeQuery("select CourseId, CourseName from t_course");

        ArrayList courses = new ArrayList();
        for (int i = 0; i < objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            courses.add(new LabelValueBean(h.get("CourseName").toString(), h.get("CourseId").toString()));
        }

        courses.add(0, new LabelValueBean("不限", "0"));
        session.setAttribute("SearchCourse", courses);

    }

    public static void setAssignments(int user_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.appendTableName("t_course", "d");

        sql.appendColumn("b.AssignmentId", "AssignmentId");
        sql.appendColumn("a.ExamId", "ExamId");
        sql.appendColumn("d.CourseName", "CourseName");
        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 " +
                "d.CourseId = a.CourseId AND " +
                "c.UserId = " + user_id
        );

        sql.appendOrderByASC("a.CourseId");

        objs = sql.executeQuery();

        Hashtable max = StudyHelper.getMaxInstanceOfAssignment(user_id);

        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_id + " AND AssignmentId = " + h.get("AssignmentId")
            );

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

            }

        }

        session.setAttribute("Assignments", objs);

    }

    public static void setStudent(int user_id, HttpSession session)
    {
        Object[] objs = UserSQLExecuter.executeQuery(
                "select a.UserId UserId, a.UserName UserName, a.LoginName, c.ClassName ClassName " +
                "from t_user a, t_student b, t_class c " +
                "where a.UserId = b.UserId AND b.ClassId = c.ClassId AND " +
                "a.UserId = " + user_id);

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


    }
}