bbshelper.java

采用Eclispe开发平台

package com.whatratimes.webedu.bbs;

import com.whatratimes.dbms.QuerySQLExecuter;
import com.whatratimes.dbms.UserSQLExecuter;
import com.whatratimes.util.ConstHelper;
import com.whatratimes.tables.t_user;

import javax.servlet.http.HttpSession;
import java.util.Hashtable;
import java.util.ArrayList;
import java.text.SimpleDateFormat;

import org.apache.struts.util.LabelValueBean;

/**
 * User: Tao
 * Date: Jul 20, 2003
 * Time: 3:34:09 PM
 */
public class BBSHelper
{
    public static final SimpleDateFormat SDF = new SimpleDateFormat("MM-dd HH:mm:ss");

    public static void setCourseTopics(HttpSession session, Integer course_id, int page)
    {

        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendWhereCondition("a.CourseId", "=", course_id);
        sql.appendWhereCondition("a.ParentId", "=", new Integer(0));
        Object[] objs = getAnnounces(sql, session, page, "a.ReplyTime", false);

        session.setAttribute("Topics", objs);

        objs = (Object[])session.getAttribute("Courses");

        for ( int i=0; i<objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            if ( course_id.equals( h.get("CourseId")))
            {
                session.setAttribute("BoardName", h.get("CourseName"));
                break;
            }
        }

    }

    public static void setClassTopics(HttpSession session, Integer class_id, int page)
    {
        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendWhereCondition("a.ClassId", "=", class_id);
        sql.appendWhereCondition("a.ParentId", "=", new Integer(0));
        Object[] objs = getAnnounces(sql, session, page, "a.ReplyTime", false);

        session.setAttribute("Topics", objs);

        objs = (Object[])session.getAttribute("Classes");
        for ( int i=0; i<objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            if ( class_id.equals( h.get("ClassId")))
            {
                session.setAttribute("BoardName", h.get("ClassName"));
                break;
            }
        }

    }

    public static void setBoardTopics(HttpSession session, Integer board_id, int page)
    {
        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendWhereCondition("a.BoardId", "=", board_id);
        sql.appendWhereCondition("a.ParentId", "=", new Integer(0));
        Object[] objs = getAnnounces(sql, session, page, "a.ReplyTime", false);

        session.setAttribute("Topics", objs);

        objs = (Object[])session.getAttribute("Boards");
        for ( int i=0; i<objs.length; i++)
        {
            Hashtable h = (Hashtable) objs[i];
            if ( board_id.equals( h.get("BoardId")))
            {
                session.setAttribute("BoardName", h.get("BoardName"));
                break;
            }
        }

    }

    private static Object[] getAnnounces(QuerySQLExecuter sql, HttpSession session, int page, String orderby, boolean asc)
    {

        sql.appendTableName("t_bbs_announce", "a");

        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 + ConstHelper.NumberOfRowsInOnePage) / ConstHelper.NumberOfRowsInOnePage));
            sql.clearAllColumns();
            page = 1;
        }

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

        sql.appendTableName("t_user", "b");
        sql.appendWhereClause("a.UserId = b.UserId");
        sql.appendColumn("a.AnnounceId", "AnnounceId");
        sql.appendColumn("a.ParentId", "ParentId");
        sql.appendColumn("a.BoardId", "BoardId");
        sql.appendColumn("a.CourseId", "CourseId");
        sql.appendColumn("a.ClassId", "ClassId");
        sql.appendColumn("a.UserId", "UserId");
        sql.appendColumn("b.UserName", "UserName");
        sql.appendColumn("b.Email", "Email");
        sql.appendColumn("b.UserType", "UserType");
        sql.appendColumn("a.Topic", "Topic");
        sql.appendColumn("a.Content", "Content");
        sql.appendColumn("a.SentTime", "SentTime");
        sql.appendColumn("a.Address", "Address");
        sql.appendColumn("a.ReplyTime", "ReplyTime");
        sql.appendColumn("a.ReplyCounter", "ReplyCounter");

        if ( asc )
        {
            sql.appendOrderByASC(orderby);
        } else
        {
            sql.appendOrderByDESC(orderby);
        }

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

        Object[] objs = sql.executeQuery();

        return objs;
    }


    public static Object[]  setAnnounces(HttpSession session, Integer announce_id, int page)
    {
        QuerySQLExecuter sql = new QuerySQLExecuter();

        sql.appendWhereClause("(a.ParentId=" + announce_id + " OR a.AnnounceId=" + announce_id + ")");
        Object[] objs = getAnnounces(sql, session, page, "a.SentTime", true);

        session.setAttribute("Announces", objs);

        return objs;

    }

    public static void setBoards(HttpSession session, t_user user)
    {
        Object[] classes = null;
        Object[] courses = null;
        Object[] boards = null;

        switch (user.UserType)
        {
            case 1:
                classes = UserSQLExecuter.executeQuery("SELECT a.ClassId ClassId, a.ClassName ClassName from t_class a, t_student b where a.ClassId = b.ClassId AND a.State=1 AND b.UserId = " + user.UserId);
                courses = (Object[])session.getAttribute("Courses");
                break;
            case 2:
                classes = UserSQLExecuter.executeQuery(
                        "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 = " + user.UserId
                );

                courses = UserSQLExecuter.executeQuery(
                        "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 = " + user.UserId
                );
                break;
            case 3:
                classes = new Object[0];
                courses = UserSQLExecuter.executeQuery(
                        "SELECT * from t_course where CourseState = 2"
                );
                break;
            case 4:
                classes = UserSQLExecuter.executeQuery(
                        "SELECT a.ClassId ClassId, a.ClassName ClassName " +
                        "FROM t_class a " +
                        "WHERE a.SchoolId = '" + user.SchoolId + "' AND a.State=1"
                );

                courses = UserSQLExecuter.executeQuery(
                        "SELECT * from t_course where CourseState = 2"
                );

                break;
            case 5:
                classes = UserSQLExecuter.executeQuery(
                        "SELECT c.SchoolId SchoolId, c.SchoolName SchoolName, a.ClassId ClassId, a.ClassName ClassName " +
                        "FROM t_class a, t_school c " +
                        "WHERE a.SchoolId = c.SchoolId AND c.State = 1 AND a.State=1 " +
                        "ORDER BY c.SchoolId"
                );

                courses = UserSQLExecuter.executeQuery(
                        "SELECT * from t_course where CourseState = 2"
                );

                break;
        }

        boards = UserSQLExecuter.executeQuery("select * from t_bbs_board ");

        session.setAttribute("Classes", classes);
        session.setAttribute("Courses", courses);
        session.setAttribute("Boards", boards);
    }

    public static void setBoardMaster(HttpSession session)
    {
        Object[] masters = null;

        masters = UserSQLExecuter.executeQuery(
                "select UserId, UserName from t_user where UserType in(2,4,5) AND State =1"
        );

        ArrayList al = new ArrayList();
        for ( int i=0; i<masters.length; i++)
        {
            Hashtable h = (Hashtable) masters[i];
            al.add(new LabelValueBean ( (String) h.get("UserName"), h.get("UserId").toString()));
        }

        session.setAttribute("BoardMasterSelect", al );

    }

}