package com.espeed.reading.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import net.sf.json.JSONObject;

/**
 * 
 * 
 * @项目名称: 邮件跟踪系统
 * @版权所有: 深圳市科飞时速网络技术有限公司(0755-88843776)
 * @技术支持: info@21gmail.com
 * @单元名称: 邮件跟踪记录统计工具类
 * @开始时间: 2018-5-28
 * @开发人员: 杨志钊
 */
public class StatisticsUtil {

	/** 获取记录详情 */
	public static Map<String, Object> getRecord(Long id) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		if (id == null) {
			resultMap.put("code", 300);
			resultMap.put("msg", "id不能为空");
			return resultMap;
		}

		String sql = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData metaData = null;

		try {
			conn = DBUtil.getConnection();

			sql = "select * from ts_customer_access where id = ?";

			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);

			rs = ps.executeQuery();

			if (rs.first()) {
				resultMap.put("code", 200);
				metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				String name = null;
				Object value = null;
				for (int i = 1; i <= count; i++) {
					name = metaData.getColumnName(i);
					value = rs.getObject(name);

					resultMap.put(name, value);
				}

				resultMap
						.put("language_details", LanguageUtil
								.getLanguage(String.valueOf(resultMap
										.get("language"))));

			} else {
				resultMap.put("code", 300);
				resultMap.put("msg", "不存在该记录");
				return resultMap;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}

		resultMap.put("code", 200);
		resultMap.put("msg", ConstantUtil.SUCCESS);
		return resultMap;
	}

	public static void main(String[] args) {
		System.out.println(JSONObject.fromObject(getCount(5, "showNew", "7438")));
	}

	/** 获取统计 */
	@SuppressWarnings("unchecked")
	public static Map<String, Object> getCount(Integer type, String value,
			String userIds) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		Map<String, Object> result = null;
		List<Object> resultList = null;
		if (type == null) {
			resultMap.put("code", 300);
			resultMap.put("msg", "type不能为空");
			return resultMap;
		}

		String sql = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData metaData = null;
		JSONObject jsonObject = null;
		int count = 0;
		String date = null;
		String preDate = null;
		String name = null;

		try {
			conn = DBUtil.getConnection();
			switch (type) {
			case 0:// 概况

				resultMap.put("today", StatisticsUtil.getDayCount(0, userIds,
						conn, ps, rs, metaData));
				resultMap.put("yesterday", StatisticsUtil.getDayCount(1,
						userIds, conn, ps, rs, metaData));
				resultMap.put("yesterday_now", StatisticsUtil.getDayCount(2,
						userIds, conn, ps, rs, metaData));
				resultMap.put("predict", StatisticsUtil.getDayCount(3, userIds,
						conn, ps, rs, metaData));
				resultMap.put("day_90", StatisticsUtil.getDayCount(5, userIds,
						conn, ps, rs, metaData));

				resultMap.put("total", StatisticsUtil.getDayCount(7, userIds,
						conn, ps, rs, metaData));

				// 历史最高
				result = new HashMap<String, Object>();
				result.put("pv", StatisticsUtil.getMaxCount(0, userIds, conn,
						ps, rs, metaData));// 浏览次数
				result.put("uv", StatisticsUtil.getMaxCount(1, userIds, conn,
						ps, rs, metaData));// 独立访客
				result.put("ip", StatisticsUtil.getMaxCount(2, userIds, conn,
						ps, rs, metaData));// ip
				result.put("sv", StatisticsUtil.getMaxCount(3, userIds, conn,
						ps, rs, metaData));// 访问次数，session会话数
				result.put("ev", StatisticsUtil.getMaxCount(4, userIds, conn,
						ps, rs, metaData));// 独立邮箱个数

				jsonObject = (JSONObject) result.get("uv");
				date = jsonObject.getString("date");

				// 查询新/旧独立访客
				sql = "SELECT COUNT(DISTINCT identity) from ts_customer_access WHERE create_time < ? "
						+ "and identity in(SELECT DISTINCT identity FROM ts_customer_access "
						+ "where user_id in("
						+ StringEscapeUtils.escapeSql(userIds)
						+ ") and create_time >= '"
						+ date
						+ " 00:00:00' and create_time <= '"
						+ date
						+ " 23:59:59')";

				ps = conn.prepareStatement(sql);
				ps.setString(1, date);

				rs = ps.executeQuery();

				if (rs.first()) {
					count = jsonObject.getInt("count");
					date = jsonObject.getString("date");

					jsonObject = new JSONObject();
					jsonObject.put("date", date);
					jsonObject.put("count", rs.getInt(1));

					result.put("old_uv", jsonObject);// 旧独立访客

					jsonObject = new JSONObject();
					jsonObject.put("date", date);
					jsonObject.put("count", count - rs.getInt(1));
					result.put("new_uv", jsonObject);// 新独立访客

				} else {
					jsonObject = new JSONObject();
					jsonObject.put("date",
							DateFormat.getNowDate().split(" ")[0]);
					jsonObject.put("count", 0);

					result.put("new_uv", jsonObject);// 新独立访客
					result.put("old_uv", jsonObject);// 旧独立访客
				}

				resultMap.put("max", result);

				break;
			case 1:// 今日按小时分组统计
			case 2:// 昨日按小时分组统计
			case 3:// 上周同期按小时分组统计
			case 4:// 近2日按小时分组统计
			case 5:// 近7日按小时分组统计
			case 6:// 近30日按小时分组统计
				if (type == 1) {
					date = "to_days(create_time) = to_days(now())";
					preDate = DateFormat.getNowDate().split(" ")[0];
					sql = "SELECT date_format(create_time, '%H') as date,COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev FROM ts_customer_access WHERE user_id in("
							+ StringEscapeUtils.escapeSql(userIds)
							+ ") and to_days(create_time) = to_days(now()) GROUP BY date_format(create_time, '%H')";
				} else if (type == 2) {
					date = "to_days(now()) - to_days(create_time) <= 1";
					preDate = DateFormat.getDateAfter(-1).split(" ")[0];
					sql = "SELECT date_format(create_time, '%H') as date,COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev FROM ts_customer_access WHERE user_id in("
							+ StringEscapeUtils.escapeSql(userIds)
							+ ") and to_days(now()) - to_days(create_time) <= 1 GROUP BY date_format(create_time, '%H')";
				} else if (type == 3) {
					preDate = DateFormat.getDateAfter(-7).split(" ")[0];

					sql = "SELECT date_format(create_time, '%H') as date,COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev FROM ts_customer_access WHERE user_id in("
							+ StringEscapeUtils.escapeSql(userIds)
							+ ") and create_time >= '" + preDate
							+ " 00:00:00' and create_time <= '" + preDate
							+ " 23:59:59' GROUP BY date_format(create_time, '%H')";
				} else if (type == 4) {
					sql = "SELECT date_format(create_time, '%Y-%m-%d %H') as date,COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev FROM ts_customer_access WHERE user_id in("
							+ StringEscapeUtils.escapeSql(userIds)
							+ ") and DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= date(create_time) GROUP BY date_format(create_time, '%Y-%m-%d %H')";
				} else if (type == 5) {
					sql = "SELECT date_format(create_time, '%Y-%m-%d %H') as date,COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev FROM ts_customer_access WHERE user_id in("
							+ StringEscapeUtils.escapeSql(userIds)
							+ ") and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time) GROUP BY date_format(create_time, '%Y-%m-%d %H')";
				} else if (type == 6) {
					sql = "SELECT date_format(create_time, '%Y-%m-%d') as date,COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev FROM ts_customer_access WHERE user_id in("
							+ StringEscapeUtils.escapeSql(userIds)
							+ ") and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) GROUP BY date_format(create_time, '%Y-%m-%d')";
				}

				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();

				resultList = new ArrayList<Object>();

				while (rs.next()) {
					result = new HashMap<String, Object>();
					metaData = rs.getMetaData();
					count = metaData.getColumnCount();
					for (int i = 1; i <= count; i++) {
						name = metaData.getColumnName(i);

						result.put(name, rs.getObject(name));
					}

					resultList.add(result);
				}
				
				if (value != null && value.equals("showNew") && resultList.size() > 0) {
					List<Object> newList = new ArrayList<Object>();
					
					for (Object object : resultList) {
						result = (Map<String, Object>) object;
						
						switch (type) {
						case 1:
						case 2:
						case 3:
							date = "create_time >= '" + preDate + result.get("date") + ":00:00"
							+ " 00:00:00' and create_time <= '" + preDate + result.get("date") + ":59:59'";
							
							preDate += " " + result.get("date") + ":00:00";
							
							
							break;
						case 4:
						case 5:
							date = "create_time >= '" + result.get("date") + ":00:00"
									+ " 00:00:00' and create_time <= '" + result.get("date") + ":59:59'";
							
							preDate = result.get("date") + ":00:00";
							break;
						case 6:
							date = "create_time >= '" + result.get("date") + " 00:00:00"
									+ " 00:00:00' and create_time <= '" + result.get("date") + " 23:59:59'";
							
							preDate = result.get("date") + " 00:00:00";
							break;
						}

						count = StatisticsUtil.getOldUv(userIds, preDate,
								date, conn, ps, rs);

						result.put("old_uv", count);
						result.put("new_uv", Integer.valueOf(result.get("uv").toString()) - count);
						
						newList.add(result);
					}
					
					resultList = newList;
				}

				resultMap.put("list", resultList);
				break;

			default:
				resultMap.put("code", 300);
				resultMap.put("msg", "不支持该业务类型");
				return resultMap;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, conn);
		}

		resultMap.put("code", 200);
		resultMap.put("msg", ConstantUtil.SUCCESS);
		return resultMap;
	}

	/** 以时间分组获取统计 */
	public static Map<String, Integer> getDayCount(Integer type,
			String userIds, Connection conn, PreparedStatement ps,
			ResultSet rs, ResultSetMetaData metaData) throws Exception {
		Map<String, Integer> resultMap = new HashMap<String, Integer>();

		resultMap.put("pv", 0);// 浏览次数
		resultMap.put("uv", 0);// 独立访客
		resultMap.put("ip", 0);// ip
		resultMap.put("new_uv", 0);// 新独立访客
		resultMap.put("old_uv", 0);// 旧独立访客
		resultMap.put("sv", 0);// 访问次数，session会话数
		resultMap.put("ev", 0);// 独立邮箱个数

		String sql = null;
		int count = 0;
		String name = null;
		String date = null;
		String preDate = null;

		switch (type) {
		case 0:// 今日概况
			date = "to_days(create_time) = to_days(now())";
			preDate = DateFormat.getNowDate().split(" ")[0];
			break;
		case 1:// 昨日概况
			date = "to_days(now()) - to_days(create_time) <= 1";
			preDate = DateFormat.getDateAfter(-1).split(" ")[0];
			break;
		case 2:// 昨日此时
			preDate = DateFormat.getDateAfter(-1);
			date = "create_time >= '" + preDate.split(" ")[0]
					+ " 00:00:00' and create_time <= '" + preDate + "'";
			preDate = preDate.split(" ")[0];
			break;
		case 3:// 前三日（用于预计）不算当天
			preDate = DateFormat.getDateAfter(-3).split(" ")[0];

			date = "create_time >= '" + preDate
					+ " 00:00:00' and create_time <= '"
					+ DateFormat.getDateAfter(-1).split(" ")[0] + " 23:59:59'";
			break;
		case 4:// 近七日
			date = "DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)";
			preDate = DateFormat.getDateAfter(-7).split(" ")[0];
			break;
		case 5:// 近九十日
			date = "DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= date(create_time)";
			preDate = DateFormat.getDateAfter(-90).split(" ")[0];
			break;
		case 6:// 历史最高
			sql = "SELECT date_format(create_time, '%Y-%m-%d') AS create_time,COUNT(*) AS count from ts_customer_access where user_id in("
					+ StringEscapeUtils.escapeSql(userIds)
					+ ") group by date_format(create_time, '%Y-%m-%d') ORDER BY count desc LIMIT 1";

			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery();

			if (rs.first()) {
				preDate = rs.getString("create_time");
				date = "create_time >= '" + preDate
						+ " 00:00:00' and create_time <= '" + preDate
						+ " 23:59:59'";
			} else {
				date = "to_days(create_time) = to_days(now())";
				preDate = DateFormat.getNowDate().split(" ")[0];
			}
			break;
		case 7:// 历史累计
			date = "1 = 1";
			preDate = DateFormat.getDateAfter(1).split(" ")[0];
			break;
		}

		sql = "select COUNT(*) as pv,COUNT(DISTINCT identity) as uv,COUNT(DISTINCT ip) as ip,COUNT(DISTINCT jsession_id) as sv,COUNT(DISTINCT access_email) as ev "
				+ "from ts_customer_access where user_id in("
				+ StringEscapeUtils.escapeSql(userIds) + ") and " + date;

		ps = conn.prepareStatement(sql);

		rs = ps.executeQuery();

		if (rs.first()) {
			metaData = rs.getMetaData();
			count = metaData.getColumnCount();
			for (int i = 1; i <= count; i++) {
				name = metaData.getColumnName(i);

				if (type == 3) {
					resultMap.put(name,
							StatisticsUtil.divided(rs.getInt(name), 3));
				} else if (type == 5) {
					resultMap.put(name,
							StatisticsUtil.divided(rs.getInt(name), 90));
				} else {
					resultMap.put(name, rs.getInt(name));
				}
			}

			// 查询新/旧独立访客
			sql = "SELECT COUNT(DISTINCT identity) from ts_customer_access WHERE create_time < ? "
					+ "and identity in(SELECT DISTINCT identity FROM ts_customer_access "
					+ "where user_id in("
					+ StringEscapeUtils.escapeSql(userIds)
					+ ") and "
					+ date
					+ ")";

			ps = conn.prepareStatement(sql);
			ps.setString(1, preDate);

			rs = ps.executeQuery();

			if (rs.first()) {
				count = rs.getInt(1);

				if (type == 3) {
					resultMap.put("old_uv", StatisticsUtil.divided(count, 3));
				} else if (type == 5) {
					resultMap.put("old_uv", StatisticsUtil.divided(count, 90));
				} else {
					resultMap.put("old_uv", count);
				}

				resultMap.put("new_uv",
						resultMap.get("uv") - resultMap.get("old_uv"));
			}

		}

		return resultMap;
	}

	/** 相除有余数加一 */
	private static int divided(int x, int y) {

		return x % y == 0 ? x / y : (x / y) + 1;
	}

	/** 获取历史最高值 */
	public static JSONObject getMaxCount(Integer type, String userIds,
			Connection conn, PreparedStatement ps, ResultSet rs,
			ResultSetMetaData metaData) throws Exception {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("date", DateFormat.getNowDate().split(" ")[0]);
		jsonObject.put("count", 0);

		String sql = null;
		String countType = null;

		switch (type) {
		case 0:
			countType = "*";
			break;
		case 1:
			countType = "DISTINCT identity";
			break;
		case 2:
			countType = "DISTINCT ip";
			break;
		case 3:
			countType = "DISTINCT jsession_id";
			break;
		case 4:
			countType = "DISTINCT access_email";
			break;

		}

		sql = "SELECT date_format(create_time, '%Y-%m-%d') AS create_time,COUNT("
				+ countType
				+ ") AS count from ts_customer_access where user_id in("
				+ StringEscapeUtils.escapeSql(userIds)
				+ ") group by date_format(create_time, '%Y-%m-%d') ORDER BY count desc LIMIT 1";

		ps = conn.prepareStatement(sql);

		rs = ps.executeQuery();

		if (rs.first()) {
			jsonObject = new JSONObject();
			jsonObject.put("date", rs.getString("create_time"));
			jsonObject.put("count", rs.getInt("count"));
		}

		return jsonObject;
	}

	/** 获取旧访客数量 */
	public static int getOldUv(String userIds, String preDate, String date,
			Connection conn, PreparedStatement ps, ResultSet rs)
			throws Exception {
		int uv = 0;
		// 查询新/旧独立访客
		String sql = "SELECT COUNT(DISTINCT identity) from ts_customer_access WHERE create_time <= ? "
				+ "and identity in(SELECT DISTINCT identity FROM ts_customer_access "
				+ "where user_id in("
				+ StringEscapeUtils.escapeSql(userIds)
				+ ") and " + date + ")";

		ps = conn.prepareStatement(sql);
		ps.setString(1, preDate);

		rs = ps.executeQuery();

		if (rs.first()) {
			uv = rs.getInt(1);
		}

		return uv;
	}

}