statdao.java

EasyJForum 是一个基于 Java 技术的免费社区论坛软件系统

package com.hongshee.ejforum.data;

/**
 * <p>Title: StatDAO.java</p>
 * <p>Description: Stat data access object</p>
 * <p>Copyright: hongshee.com (c) 2008</p>
 * @author jackie
 * @version 1.0
 */

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Calendar;

import com.hongshee.ejforum.util.PageUtils;
import com.hongshee.ejforum.util.AppUtils;
import com.hongshee.ejforum.common.CacheManager;
import com.hongshee.ejforum.common.AppContext;
import com.hongshee.ejforum.data.SectionDAO.SectionVO;
import com.hongshee.ejforum.data.BoardDAO.BoardVO;

public class StatDAO extends EntityDAO
{
    private static StatDAO _dao = null;

    private HashMap<String,String> statMap = new HashMap<String,String>();

    protected StatDAO()
    {}

    public static StatDAO getInstance()
    {
        if (_dao == null)
        {
            _dao = new StatDAO();
        }
        return _dao;
    } 

    public HashMap<String,String> getBaseStatInfo() throws SQLException
    {
        if (statMap.size() == 0)
        {
            statVisits();
        }
        return statMap;
    } 
    
    /**
     * 执行访问量统计
     * @param none 
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void statVisits() throws SQLException
    {
        Connection conn = null;
        PreparedStatement pstmtInsert = null;
        conn = dbManager.getConnection();
        try
        {
            // Stat base info
            int digests = 
                this.execSelectCountSql(adapter.VisitStat_GetDigestTopics, null, conn);
            
            int rewards = 
                this.execSelectCountSql(adapter.VisitStat_GetRewardTopics, null, conn);

            int postUsers = 
                this.execSelectCountSql(adapter.VisitStat_GetPostUsers, null, conn);

            int adminUsers = 
                this.execSelectCountSql(adapter.VisitStat_GetAdminUsers, null, conn);

            int userLogins = 
                this.execSelectCountSql(adapter.VisitStat_GetUserLogins, null, conn);

            int attaches = 
                this.execSelectCountSql(adapter.VisitStat_GetAttaches, null, conn);
            
            CacheManager cache = CacheManager.getInstance();
            String moderators = cache.getModerators();
            adminUsers = adminUsers + moderators.split(",").length - 1;

            int boards = 0;
            SectionVO aSection = null;
            ArrayList<SectionVO> sections = cache.getSections();
            
            for (int i=0; i<sections.size(); i++)
            {
                aSection = sections.get(i);
                boards = boards + aSection.boardList.size();
            }
            
            statMap.put("digests", String.valueOf(digests));
            statMap.put("rewards", String.valueOf(rewards));
            statMap.put("postUsers", String.valueOf(postUsers));
            statMap.put("adminUsers", String.valueOf(adminUsers));
            statMap.put("userLogins", String.valueOf(userLogins));
            statMap.put("attaches", String.valueOf(attaches));
            statMap.put("boards", String.valueOf(boards));
            
            // Stat visits info
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.DATE, -1);
            SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
            String statDate = dateFormatter.format(cal.getTime());

            int visits = 
                this.execSelectCountSql(adapter.VisitStat_GetTopicVisits, null, conn);
            
            int topics = 
                this.execSelectCountSql(adapter.VisitStat_GetTopics, null, conn);

            int replies = 
                this.execSelectCountSql(adapter.VisitStat_GetReplies, null, conn);

            int users = 
                this.execSelectCountSql(adapter.VisitStat_GetUsers, null, conn);

            statMap.put("visits", String.valueOf(visits));
            statMap.put("topics", String.valueOf(topics));
            statMap.put("replies", String.valueOf(replies));
            statMap.put("users", String.valueOf(users));

            statMap.put("statTime", AppUtils.getCurrentTimeStr());
            
            ArrayList<Object> paramValues = new ArrayList<Object>();
            paramValues.add(statDate);
            this.execUpdateSql(adapter.VisitStat_Delete, paramValues, conn);
            
            pstmtInsert = conn.prepareStatement(adapter.VisitStat_Insert);
            pstmtInsert.setString(1, statDate);
            pstmtInsert.setInt(2, topics);
            pstmtInsert.setInt(3, replies);
            pstmtInsert.setInt(4, users);
            pstmtInsert.setInt(5, visits);
            
            pstmtInsert.executeUpdate();
        }
        finally
        {
            dbManager.closePStatement(pstmtInsert);
            dbManager.closeConnection(conn);
        }
    }

    /**
     * 查询论坛访问量统计信息
     * @param none
     * @return 统计信息
     * @throws SQLException
     * @since 1.0
     */
    @SuppressWarnings("unchecked")
    public Object[] getVisitStatInfo(int pageNo, int pageRows) throws SQLException
    {
        Object[] result = new Object[2];
        String querySql = adapter.VisitStat_GetList;
        String countSql = adapter.VisitStat_GetMonths;
        
        int totalCount = 0;
        Connection conn = null;
        PreparedStatement pstmtQuery = null;
        ResultSet rs = null;
        try
        {
            conn = dbManager.getConnection();
            pstmtQuery = conn.prepareStatement(countSql);
            rs = pstmtQuery.executeQuery();
            while(rs.next())
            {
                totalCount++;
            }
            
            if (totalCount > 0)
            {
                dbManager.closeResultSet(rs);
                dbManager.closePStatement(pstmtQuery);
             
                pstmtQuery = conn.prepareStatement(querySql);
                rs = pstmtQuery.executeQuery();
            
                ArrayList<HashMap> visitList = new ArrayList<HashMap>();
                HashMap visitMap = null;
                int[] visits = new int[4];
                int offset = (pageNo-1) * pageRows;
                int seq = 0;
                
                while(rs.next())
                {
                    seq++;
                    if (seq < offset) continue;
                    if (seq > offset + pageRows) continue;
                    if (seq == offset)
                    {
                        visits[0] = rs.getInt("tc");
                        visits[1] = rs.getInt("rc");
                        visits[2] = rs.getInt("uc");
                        visits[3] = rs.getInt("vc");
                    }
                    else // seq > offset
                    {
                        visitMap = new HashMap();

                        if (seq == 1)
                        {
                            visits[0] = 0;
                            visits[1] = 0;
                            visits[2] = 0;
                            visits[3] = 0;
                        }
                        
                        visitMap.put("statMonth", rs.getString("ym"));
                        visitMap.put("topics", rs.getInt("tc") - visits[0]);
                        visitMap.put("replies", rs.getInt("rc") - visits[1]);
                        visitMap.put("users", rs.getInt("uc") - visits[2]);
                        visitMap.put("visits", rs.getInt("vc") - visits[3]);
                        visitList.add(visitMap);