content: package com.etao.adhoc.analyse.dao;
  
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.text.SimpleDateFormat;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Map;
  
  import org.apache.log4j.Logger;
  
  import com.etao.adhoc.analyse.common.util.YamlUtils;
  import com.etao.adhoc.analyse.vo.DayUserPv;
  import com.etao.adhoc.analyse.vo.ModuleInfo;
  import com.etao.adhoc.analyse.vo.QueryLog;
  import com.etao.adhoc.analyse.vo.StartDay;
  import com.etao.adhoc.analyse.vo.TotalUserPv;
  
  
  public class MysqlService {
  	private static Logger LOG = Logger.getLogger(MysqlService.class);
  
  	private static final String JDBC_DRIVER = "org.gjt.mm.mysql.Driver";
  	Map conf; 
  	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  	String url =null;
  	String username = null;
  	String password = null;
  	//jps初始化问题
  	public MysqlService()  {
  		try {
  			conf = YamlUtils.getConfigFromYamlFile("query-analyser.yaml");
  			this.url = (String) conf.get("mysql.url");
  			this.username = (String) conf.get("mysql.username");
  			this.password = (String) conf.get("mysql.password");
  			Class.forName(JDBC_DRIVER);
  
  		} catch (Throwable e) {
  			LOG.error("error",e);
  
  		} 
  		
  	}
  	public void insertQueryLog(QueryLog queryLog) {
  		if(isValid(queryLog)){
  			String sql = "INSERT INTO query_log" +
  					"(query_date,nick,email,set_name,dimvalue,filter,bizdate)" +
  					" VALUES(?,?,?,?,?,?,?) ";
  			PreparedStatement pstmt=null;
  			try {
  
  				Connection conn = DriverManager.getConnection(url, username, password);
  
  				pstmt = conn.prepareStatement(sql);
  				pstmt.setTimestamp(1, new java.sql.Timestamp(queryLog.getDate().getTime())); 
  				pstmt.setString(2, queryLog.getNick());
  				pstmt.setString(3, queryLog.getEmail());
  				pstmt.setString(4, queryLog.getSetName());
  				pstmt.setString(5, queryLog.getDimvalue());
  				pstmt.setString(6, queryLog.getFilter());
  				pstmt.setString(7, queryLog.getBizdate());
  				pstmt.executeUpdate();
  				LOG.info(pstmt.toString());
  				pstmt.close();
  				conn.close();
  			} catch (SQLException e) {
  				String debugsql="";
  				if(pstmt!=null)
  				{
  					debugsql=pstmt.toString();
  				}
  				LOG.error(debugsql,e);
  			}
  		}
  	}
  	private boolean isValid(QueryLog queryLog){
  		if(queryLog.getNick().length() > 3)
  			return false;
  		return true;
  	}
  	public ModuleInfo getModuleInfo(String queryDay, String moduleName) {
  		String sql = "SELECT query_cnt,uv FROM module_info" +
  				" WHERE queryday=? " +
  				" and module_name=? ";
  		PreparedStatement pstmt=null;
  		ModuleInfo moduleInfo = null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, queryDay); 
  			pstmt.setString(2, moduleName);
  			ResultSet rs = pstmt.executeQuery();
  			if(rs.next()){
  				moduleInfo = new ModuleInfo();
  				moduleInfo.setQueryDay(queryDay);
  				moduleInfo.setModuleName(moduleName);
  				moduleInfo.setQueryCnt(rs.getInt(1));
  				moduleInfo.setUv(rs.getInt(2));
  
  			}
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return moduleInfo;
  	}
  	
  	public ModuleInfo[] getModuleInfos(String queryDay) {
  		String sql = "SELECT sum(query_cnt) as query_cnt,sum(uv) as uv,module_name,nicklist FROM module_info" +
  				" WHERE queryday=? " +
  				" group by module_name order by module_name ";
  		PreparedStatement pstmt=null;
  		ArrayList<ModuleInfo> list=new ArrayList<ModuleInfo>();
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, queryDay); 
  			ResultSet rs = pstmt.executeQuery();
  			while(rs.next()){
  				ModuleInfo moduleInfo = new ModuleInfo();
  				moduleInfo.setQueryDay(queryDay);
  				moduleInfo.setQueryCnt(rs.getInt(1));
  				moduleInfo.setUv(rs.getInt(2));
  				moduleInfo.setModuleName(rs.getString(3));
  				moduleInfo.setNicklist(rs.getString(4));
  				list.add(moduleInfo);
  
  			}
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return list.toArray(new ModuleInfo[list.size()]);
  	}
  	public StartDay getStartDay(){
  		String sql = "SELECT startday FROM start_day";
  		StartDay startDay = null;
  		Statement stmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			stmt = conn.createStatement();
  			ResultSet rs = stmt.executeQuery(sql);
  			if(rs.next()){
  				startDay = new StartDay();
  				startDay.setStartDay(rs.getString(1));
  			}
  			LOG.info(stmt.toString());
  			stmt.close();
  			conn.close();
  		} catch (SQLException e) {
  			String debugsql="";
  			if(stmt!=null)
  			{
  				debugsql=stmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return startDay;
  	}
  	public List<TotalUserPv> getTotalTopUsers(int length) {
  		String sql = "select a.nick as nick,a.query_cnt as query_cnt, b.department as department" +
  				" from (" +
  				"SELECT nick,query_cnt FROM total_user_pv" +
  				" ORDER BY query_cnt" +
  				" DESC LIMIT ?" +
  				") a left join user_info b on (a.nick=b.nick) ORDER BY a.query_cnt desc";
  		List<TotalUserPv> totalUserPvList = null;
  		PreparedStatement pstmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setInt(1, length); 
  			ResultSet rs = pstmt.executeQuery();
  			totalUserPvList = new ArrayList<TotalUserPv>();
  			while(rs.next()){
  				TotalUserPv totalUserPv = new TotalUserPv();
  				totalUserPv.setNick(rs.getString(1));
  				totalUserPv.setQueryCnt(rs.getInt(2));
  				totalUserPv.setDepartment(String.valueOf(rs.getString(3)));
  				totalUserPvList.add(totalUserPv);
  			}
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return totalUserPvList;
  	}
  	public List<DayUserPv> getDayTopUsers(String queryDay,int length) {
  		String sql = "select a.nick as nick,a.query_cnt as query_cnt, b.department as department" +
  				" from (" +
  				"" +
  				"SELECT nick,query_cnt FROM day_user_pv " +
  				" WHERE queryday = ?" +
  				" ORDER BY query_cnt" +
  				" DESC LIMIT ?" +
  				"" +
  				") a left join user_info b on (a.nick=b.nick) ORDER BY a.query_cnt desc";
  		List<DayUserPv> dayUserPvList = null;
  		PreparedStatement pstmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, queryDay);
  			pstmt.setInt(2, length); 
  			ResultSet rs = pstmt.executeQuery();
  			dayUserPvList = new ArrayList<DayUserPv>();
  			while(rs.next()){
  				DayUserPv dayUserPv = new DayUserPv();
  				dayUserPv.setQueryDay(queryDay);
  				dayUserPv.setNick(rs.getString(1));
  				dayUserPv.setQueryCnt(rs.getInt(2));
  				dayUserPv.setDepartment(String.valueOf(rs.getString(3)));
  				dayUserPvList.add(dayUserPv);
  			}
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return dayUserPvList;
  	}
  	public List<DayUserPv> getUserPV(String nick) {
  		String sql = "SELECT queryday,query_cnt FROM day_user_pv " +
  				" WHERE nick = ?" +
  				" ORDER BY queryday" +
  				" DESC LIMIT ?";
  		PreparedStatement pstmt=null;
  		List<DayUserPv> dayUserPvList = null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, nick);
  			pstmt.setInt(2, 100); 
  			ResultSet rs = pstmt.executeQuery();
  			dayUserPvList = new ArrayList<DayUserPv>();
  			while(rs.next()){
  				DayUserPv dayUserPv = new DayUserPv();
  				dayUserPv.setQueryDay(rs.getString(1));
  				dayUserPv.setNick(nick);
  				dayUserPv.setQueryCnt(rs.getInt(2));
  				dayUserPvList.add(dayUserPv);
  			}
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return dayUserPvList;
  	}
  	private static String mysqlDayFormat = "%Y%m%d";
  	public void calModuleInfo(String queryDay){
  		String sqldelete = "DELETE FROM module_info WHERE queryday='"+queryDay+"'";
  		
  		String sql = "INSERT INTO module_info(queryday,module_name,query_cnt,uv,nicklist) " +
  		        "select ? AS queryday,set_name,sum(query_cnt) AS query_cnt,COUNT(distinct nick) AS uv,GROUP_CONCAT(CONCAT(nick,':',query_cnt)  order by query_cnt desc)  from ( "+
  				" SELECT  set_name,nick ,COUNT(*) AS query_cnt " +
  				" FROM query_log " +
  				" WHERE DATE_FORMAT(query_date,?)=? " +
  				" AND nick NOT IN (SELECT nick FROM dev_nicks) " +
  				" GROUP BY set_name,nick ) a GROUP BY queryday,set_name" +
  				"";
  		PreparedStatement pstmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			Statement stmt = conn.createStatement();
  			stmt.executeUpdate(sqldelete);
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, queryDay); 
  			pstmt.setString(2, mysqlDayFormat);
  			pstmt.setString(3, queryDay); 
  			pstmt.executeUpdate();
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  	}
  	
  	
  	public void calDayUserPv(String queryDay) {
  		String sqldelete = "DELETE FROM day_user_pv WHERE queryday='"+queryDay+"'";
  
  		String sql = "INSERT INTO day_user_pv(queryday,nick,query_cnt)" +
  				" SELECT ? AS queryday, nick ,COUNT(*) AS query_cnt" +
  				" FROM query_log " +
  				" WHERE DATE_FORMAT(query_date,?)=?" +
  				//" AND nick NOT IN (SELECT nick FROM dev_nicks) " +
  				" GROUP BY queryday,nick " ;
  		PreparedStatement pstmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			Statement stmt = conn.createStatement();
  			stmt.executeUpdate(sqldelete);
  			pstmt = conn.prepareStatement(sql);
  			pstmt.setString(1, queryDay); 
  			pstmt.setString(2, mysqlDayFormat);
  			pstmt.setString(3, queryDay); 
  			pstmt.executeUpdate();
  			LOG.info(pstmt.toString());
  			pstmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(pstmt!=null)
  			{
  				debugsql=pstmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  	}
  	public void calTotalUserPv() {
  		String sql = "DELETE FROM total_user_pv WHERE 1=1";
  		Statement stmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			stmt = conn.createStatement();
  			stmt.executeUpdate(sql);
  			sql = "INSERT INTO total_user_pv (nick, query_cnt )" +
  					" SELECT nick, SUM(query_cnt)" +
  					" FROM day_user_pv"  +
  					" GROUP BY nick";
  			stmt.executeUpdate(sql);
  			LOG.info(stmt.toString());
  			stmt.close();
  			conn.close();
  
  		} catch (SQLException e) {
  			String debugsql="";
  			if(stmt!=null)
  			{
  				debugsql=stmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		
  	}
  	
  	public List<String> getRecentDays(String tableName, String fieldName, int n) {
  		List<String> days = null;
  		String sql = "SELECT DISTINCT %s" +
  				" FROM %s" +
  				" ORDER BY %s" +
  				" DESC LIMIT %d";
  		Statement stmt=null;
  		try {
  			Connection conn = DriverManager.getConnection(url, username, password);
  
  			stmt = conn.createStatement();
  			ResultSet rs = stmt.executeQuery(String.format(sql, fieldName,tableName,fieldName,n));
  			days = new ArrayList<String>();
  			while(rs.next()) {	
  				days.add(rs.getString(1));
  			} 
  			LOG.info(stmt.toString());
  			stmt.close();
  			conn.close();
  		} catch (SQLException e) {
  			String debugsql="";
  			if(stmt!=null)
  			{
  				debugsql=stmt.toString();
  			}
  			LOG.error(debugsql,e);
  		}
  		return days;
  	}
  	
  	public void close() {
  	}
  	
  	public static void main(String[] args) {
  		MysqlService server = new MysqlService();
  		//server.calTotalUserPv();
  		List<TotalUserPv> list = server.getTotalTopUsers(Integer.parseInt(args[0]));
  		for(TotalUserPv pv : list)
  			System.out.println(pv);
  		List<DayUserPv>list2 = server.getUserPV(args[1]);
  		for(DayUserPv pv : list2)
  			System.out.println(pv);
  		List<String> days = server.getRecentDays("module_info","queryday",5);
  		for(String day : days)
  			System.out.println(day);	
  		System.out.println("=======");
  		days = server.getRecentDays("day_user_pv","queryday",5);
  		for(String day : days)
  			System.out.println(day);	
  	}
  }
  