stattools.java

Email营销是在用户事先许可的前提下

package com.qq.content;
import javax.sql.rowset.CachedRowSet;
import java.util.ArrayList;
import java.sql.*;

public class StatTools {

	private static DBTool db ;
	public int usersCount = 0;
	private SqlChecker sqlChecker = new SqlChecker();
	public StatTools() throws Exception {
			db=new DBTool();
		}
	
	public int getUsersNum() {
		int count= 0;
		try {

			String sql = "SELECT count(distinct email) from user_detail where status=0";
			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return count;
	}	


	public int getSourceNum() {
		int count= 0;
		try {

			String sql = "SELECT count(distinct source) from user_detail where status=0";
			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return count;
	}	
		

	public int getValidUsersTotalNumByDate(String date_begin, String date_end,int distinct){//根据查询日期查询记录数
		String sql;
		int pagesCount = 0;
		if(distinct==0)//是否重复
			sql = "select count(*) from user_detail where status=0 and (join_date between '"+date_begin+"' and '"+date_end +"')";
		else
			sql = "select count(distinct email) from user_detail where status=0 and (join_date between '"+date_begin+"' and '"+date_end +"')";		
		
		System.out.println(sql);
		try {

			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				pagesCount = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return pagesCount;
	}



	public int getDaysNum(String date_begin, String date_end,int distinct){
		String sql;
		int pagesCount = 0;

		sql = "select count(distinct join_date) from user_detail where join_date between '"+date_begin+"' and '"+date_end +"'";

		System.out.println(sql);
		try {

			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				pagesCount = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return pagesCount;
	}



	public ArrayList getDateListByDateAndLimit(String date_begin, String date_end,int begin,int end){
      //查询期间日期
		String sql;
		int pagesCount = 0;
		ArrayList list = new ArrayList();
		sql = "select join_date from user_detail where join_date between '"+date_begin+"' and '"+date_end +"' order by join_date limit "+begin+","+end;
		
		System.out.println(sql);
		try {

			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				String date = rs.getString("join_date");
				list.add(date);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return new ArrayList();
		}
		return list;
 
	}

	public int getUserNumbyDateAndStatus(String joindate, int status, int distinct){
    //根据日期和状态查询用户数量
		String sql ;
		int count = 0;
		if(distinct==0)
			sql = "select count(email) from user_detail where join_date='"+joindate+"' and status="+status;
		else
			sql = "select count(distinct email) from user_detail where join_date='"+joindate+"' and status="+status;
		System.out.println(sql);
		try {

			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return count;

	}

	public int getAllUserNumbyDate(String joindate, int distinct){
    //根据日期查询所有用户数量,根据Email查询
		String sql ;
		int count = 0;
		if(distinct==0)
			sql = "select count(email) from user_detail where join_date='"+joindate+"' ";
		else
			sql = "select count(distinct email) from user_detail where join_date='"+joindate+"'";
		System.out.println(sql);
		try {

			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return count;

	}



	

	public int getAllUsersTotalNumByDate(String date_begin, String date_end,int distinct){
	    //根据日期查询所有用户数量,根据记录数查询
    	String sql;
		int pagesCount = 0;
		if(distinct==0)
			sql = "select count(*) from user_detail where join_date between '"+date_begin+"' and "+date_end +"' group by join_date";
		else
			sql = "select count(*) from user_detail where join_date between '"+date_begin+"' and "+date_end +"'  group by join_date)";		
		
		System.out.println(sql);
		try {

			db.query(sql);
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				pagesCount = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return pagesCount;
	}


	public CachedRowSet getUserStatusByDate(String date,int distinct){
		//根据日期查询用户状态，返回记录集合类型
		String sql;

		if(distinct==0)
			sql = "select status, count(*) from user_detail where join_date='"+date+"' group by status";
		else
			sql = "select status, count(distinct email) from user_detail where join_date='"+date+"' group by status";
		
		System.out.println(sql);
		try {
			
			CachedRowSet rs = db.query(sql);
			return rs;

		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return null;
		}

	}


	
	public int getValidUsersNum(int distinct) {//返回正常状态下的用户数量:status=0
		int count = 0;
		String sql;
		if(distinct==0)
			sql = "SELECT count(*) from user_detail where status=0";
		else
			sql = "SELECT count(distinct email) from user_detail where status=0";
		System.out.println(sql);
		try {
			CachedRowSet rs = db.query(sql);
			while (rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return count;

	}	
	

	public CachedRowSet getEmailDetail(int begin, int end){//返回email状态详情,以记录集合的形式返回
		String sql;

		sql = "select mail_sent_time,mail_sender_name,mail_sent_times,service_name,mail_title from service_mail_detail order by mail_sent_time DESC limit "+begin+","+end;
		System.out.println(sql);
		try {
			CachedRowSet rs = db.query(sql);
			return rs;
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return null;
		}

	}
	
	public String getEmailContentByTimestamp(String timestamp){
    // 根据时间戳返回EMAIL内容
		String con = "";
		String sql="select mail_content from service_mail_detail where mail_sent_time='"+timestamp+"'";
		try {
			CachedRowSet rs = db.query(sql);
			if(rs.next())
				con = rs.getString("mail_content");
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return null;
		}
		return con;

	}

	public CachedRowSet getBestUsers(int begin,int end){//根据日期区间查询用这个email请求次数最多的用户，以记录集合的形式返回
		String sql;

		sql = "select email, sum(service_mail_sent_times) as dd from user_detail group by email order by dd DESC limit "+begin+","+end;
		System.out.println(sql);
		try {
			CachedRowSet rs = db.query(sql);
			return rs;
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return null;
		}

	}	



	public CachedRowSet getSourceStat(int begin,int end){//返回来源信息的记录集合
		String sql;

		sql = "select source, count(*) as ssum from user_detail group by source order by ssum DESC limit "+begin+","+end;
		System.out.println(sql);
		try {
			CachedRowSet rs = db.query(sql);
			return rs;
		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return null;
		}

	}	
	
	public int getEmailNum(){//得到EMAL数量
		String sql;
		int count = 0;
		sql = "select  count(*)  from service_mail_detail";
		System.out.println(sql);
		try {
			CachedRowSet rs = db.query(sql);
			if(rs.next())
				count = rs.getInt(1);

		} catch (Exception e) {
			System.out.println("error:" + e.getMessage());
			return -1;
		}
		return count;

	}	
	
	
	
	
	
	
	
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {

		
	}

}