package com.javaniu;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class java牛2012贡献排行版 {

	public static float 总牛币数(int user_id) {
		float 总牛币数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select niu_count from user where status=2";
			if (user_id != 0) {
				sql += " and id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总牛币数 += rs.getInt("niu_count");
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总牛币数;
	}

	public static float 总评论活动数(int user_id) {
		float 总评论活动数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from activity_comment";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总评论活动数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总评论活动数;
	}

	public static float 总评论活动博客数(int user_id) {
		float 总评论活动博客数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from activity_blog_comment";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总评论活动博客数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总评论活动博客数;
	}

	public static float 总评论代码数(int user_id) {
		float 总评论代码数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from comment_code";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总评论代码数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总评论代码数;
	}

	public static float 总评论寻求数(int user_id) {
		float 总评论寻求数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from comment_explore";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总评论寻求数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总评论寻求数;
	}

	public static float 总上传代码数(int user_id) {
		float 总上传代码数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from code where status=2";
			if (user_id != 0) {
				sql += " and user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总上传代码数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总上传代码数;
	}

	public static float 总寻求数(int user_id) {
		float 总寻求数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from explore where status=2";
			if (user_id != 0) {
				sql += " and user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总寻求数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总寻求数;
	}

	public static float 总心情数(int user_id) {
		float 总心情数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from mood";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总心情数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总心情数;
	}

	public static float 总同求数(int user_id) {
		float 总同求数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from reexplore where status=2";
			if (user_id != 0) {
				sql += " and user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总同求数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总同求数;
	}

	public static float 总下载代码数(int user_id) {
		float 总下载代码数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from user_download";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总下载代码数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总下载代码数;
	}

	public static float 总浏览寻求数(int user_id) {
		float 总浏览寻求数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(id) from user_view";
			if (user_id != 0) {
				sql += " where user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总浏览寻求数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总浏览寻求数;
	}

	public static float 总代码被下载数(int user_id) {
		float 总代码被下载数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(ud.id) from user_download ud left join code c on ud.code_id=c.id where c.status=2";
			if (user_id != 0) {
				sql += " and c.user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总代码被下载数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总代码被下载数;
	}

	public static float 总寻求被浏览数(int user_id) {
		float 总寻求被浏览数 = 0;
		Connection conn = null;
		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select count(uv.id) from user_view uv left join explore e on uv.explore_id=e.id where e.status=2";
			if (user_id != 0) {
				sql += " and e.user_id=" + user_id;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				总寻求被浏览数 += rs.getInt(1);
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 总寻求被浏览数;
	}

	public static List<Integer> 所有用户id() {
		List<Integer> 所有用户id = new ArrayList<Integer>();
		Connection conn = null;

		try {
			String userName = "root";
			String password = "111111";
			String jdbcurl = "jdbc:mysql://localhost:3306/javaniu2?useUnicode=true&characterEncoding=utf-8";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(jdbcurl, userName, password);
			String sql = "select id from user where status=2";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				所有用户id.add(rs.getInt("id"));
			}
			pstmt.close();
		} catch (Exception e) {
			System.err.println("Cannot connect to database server,Exception:"
					+ e.getMessage());
		} finally {
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
		return 所有用户id;
	}

	public static void main(String[] args) {
		int 贡献值 = 0;
		float 牛币数 = 0;
		float 总牛币数 = 总牛币数(0);
		if (总牛币数 == 0) {
			总牛币数 = 1;
		}
		float 评论活动数 = 0;
		float 总评论活动数 = 总评论活动数(0);
		if (总评论活动数 == 0) {
			总评论活动数 = 1;
		}
		float 评论活动博客数 = 0;
		float 总评论活动博客数 = 总评论活动博客数(0);
		if (总评论活动博客数 == 0) {
			总评论活动博客数 = 1;
		}
		float 评论代码数 = 0;
		float 总评论代码数 = 总评论代码数(0);
		if (总评论代码数 == 0) {
			总评论代码数 = 1;
		}
		float 评论寻求数 = 0;
		float 总评论寻求数 = 总评论寻求数(0);
		if (总评论寻求数 == 0) {
			总评论寻求数 = 1;
		}
		float 上传代码数 = 0;
		float 总上传代码数 = 总上传代码数(0);
		if (总上传代码数 == 0) {
			总上传代码数 = 1;
		}
		float 寻求数 = 0;
		float 总寻求数 = 总寻求数(0);
		if (总寻求数 == 0) {
			总寻求数 = 1;
		}
		float 心情数 = 0;
		float 总心情数 = 总心情数(0);
		if (总心情数 == 0) {
			总心情数 = 1;
		}
		float 同求数 = 0;
		float 总同求数 = 总同求数(0);
		if (总同求数 == 0) {
			总同求数 = 1;
		}
		float 下载代码数 = 0;
		float 总下载代码数 = 总下载代码数(0);
		if (总下载代码数 == 0) {
			总下载代码数 = 1;
		}
		float 浏览寻求数 = 0;
		float 总浏览寻求数 = 总浏览寻求数(0);
		if (总浏览寻求数 == 0) {
			总浏览寻求数 = 1;
		}
		float 代码被下载数 = 0;
		float 总代码被下载数 = 总代码被下载数(0);
		if (总代码被下载数 == 0) {
			总代码被下载数 = 1;
		}
		float 寻求被浏览数 = 0;
		float 总寻求被浏览数 = 总寻求被浏览数(0);
		if (总寻求被浏览数 == 0) {
			总寻求被浏览数 = 1;
		}
		List<Integer> 所有用户id = 所有用户id();
		Map<String, Integer> ret = new HashMap<String, Integer>();

		int i = 1;
		for (Integer user_id : 所有用户id) {
			牛币数 = 总牛币数(user_id);
			评论活动数 = 总评论活动数(user_id);
			评论活动博客数 = 总评论活动博客数(user_id);
			评论代码数 = 总评论代码数(user_id);
			评论寻求数 = 总评论寻求数(user_id);
			上传代码数 = 总上传代码数(user_id);
			寻求数 = 总寻求数(user_id);
			心情数 = 总心情数(user_id);
			同求数 = 总同求数(user_id);
			下载代码数 = 总下载代码数(user_id);
			浏览寻求数 = 总浏览寻求数(user_id);
			代码被下载数 = 总代码被下载数(user_id);
			寻求被浏览数 = 总寻求被浏览数(user_id);
			贡献值 = (int) (((牛币数 / 总牛币数) + (评论活动数 / 总评论活动数)
					+ (评论活动博客数 / 总评论活动博客数) + (评论代码数 / 总评论代码数)
					+ (评论寻求数 / 总评论寻求数) + (上传代码数 / 总上传代码数) + (寻求数 / 总寻求数)
					+ (心情数 / 总心情数) + (同求数 / 总同求数) + (下载代码数 / 总下载代码数)
					+ (浏览寻求数 / 总浏览寻求数) + (代码被下载数 / 总代码被下载数) * 2 + (寻求被浏览数 / 总寻求被浏览数)) * 100);
			System.out.println("用户" + user_id + "的贡献值" + 贡献值 + "," + (i++)
					+ " in " + 所有用户id.size());
			ret.put(user_id + "", 贡献值);
		}
		ValueComparator bvc = new ValueComparator(ret);
		TreeMap<String, Integer> sortedMap = new TreeMap<String, Integer>(bvc);
		sortedMap.putAll(ret);
		for (String user_id : sortedMap.keySet()) {
			System.out.println("用户" + user_id + "的贡献值" + ret.get(user_id));
		}

	}
}

class ValueComparator implements Comparator<String> {

	Map<String, Integer> base;

	public ValueComparator(Map<String, Integer> base) {
		this.base = base;
	}

	public int compare(String a, String b) {
		if (base.get(a) >= base.get(b)) {
			return -1;
		} else {
			return 1;
		}
	}
}