package neu.integration.ctic.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import neu.integration.base.dao.impl.BaseDaoImpl;
import neu.integration.ctic.vo.WatcherlabBotnet;

import org.hibernate.Criteria;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.springframework.stereotype.Repository;

//import neu.cse.ctic.base.dao.impl.BaseDaoImpl;
//import neu.cse.ctic.supervise.dao.BotnetDao;
//import neu.cse.ctic.supervise.vo.WatcherlabBotnet;
@Repository(value="botnetDao")
public class BotnetDaoImpl extends BaseDaoImpl<WatcherlabBotnet, Integer> implements BotnetDao{
	@Resource(name = "sessionFactoryScada")
	private SessionFactory sessionFactoryScada;
	public List findNativeSqlQueryScada(String sql) {
		// sessionFactoryScada.getCurrentSession().createSQLQuery(sql).list();
		return sessionFactoryScada.getCurrentSession().createSQLQuery(sql)
				.list();
	}
	private static final String countryNum = "select country,number  from countryNumber where tables = \"botnet\"";
	private static final String protocalNum = "select protocal,count(*) num  from feed.watcherlab_botnet group by protocal";
//	private static final String countAllCountry = "select count(*) num from (select nation  from feed.watcherlab_botnet group by nation) as tb1";
	private static final String updateNum = "select dates,number from updateNumber where tables=\"botnet\"";
	@SuppressWarnings("unchecked")
	@Override
	public List countryNum() {
		List<Object[]> cnList = new ArrayList<Object[]>();
		// TODO Auto-generated method stub
		cnList = findNativeSqlQuery(countryNum);
		return cnList;
	}
	@Override
	public List protocalNum() {
		List<Object[]> pnList = new ArrayList<Object[]>();
		// TODO Auto-generated method stub
		pnList = findNativeSqlQuery(protocalNum);
		return pnList;
	}
	@Override
	public long countAllCountry() {
		// TODO Auto-generated method stub
		List<Object[]> cnList = new ArrayList<Object[]>();
		cnList = findNativeSqlQuery("select country,number  from countryNumber where tables = \"botnet\"");
		long count = cnList.size();
		return count;
	}
	@Override
	public long countAllProtocal() {
		// TODO Auto-generated method stub
		List<Object[]> pnList = new ArrayList<Object[]>();
		pnList = findNativeSqlQuery(protocalNum);
		long count = pnList.size();
		return count;
	}
	@Override
	public List updateNum() {
		List<Object[]> unList = new ArrayList<Object[]>();
		// TODO Auto-generated method stub
		unList = findNativeSqlQuery(updateNum);
		return unList;
	}
	//	select * from watcherlab_cn_ipv4 where protocal like "%'http'%"
	@Override
	public String createFindSql(String beginTime, String endTime,
			String protocal1, String protocal2, String protocal3,
			String nation1, String nation2, String nation3, int page, int rows) {
		// TODO Auto-generated method stub
		StringBuffer sb = new StringBuffer();
		sb.append("select * from feed.watcherlab_botnet where 1=1");
		if (beginTime != null && beginTime.trim().length() > 0) {
			sb.append(" and date(discover_time) >= \""+beginTime +"\"");
			
		}
		if (endTime != null && endTime.trim().length() > 0) {
			sb.append(" and date(discover_time) <= \""+endTime+"\"");
			
		}
		if (protocal3 != null && protocal3.trim().length() > 0) {
			sb.append(" and (protocal like \"%'"+protocal1+"'%\" or protocal like \"%'"+protocal2+"'%\" or protocal like \"%'"+protocal3+"'%\")");
			
		}
		else if(protocal2 != null && protocal2.trim().length() > 0){
			sb.append(" and (protocal like \"%'"+protocal1+"'%\" or protocal like \"%'"+protocal2+"'%\")");
		}
		else if(protocal1 != null && protocal1.trim().length() > 0){
			sb.append(" and (protocal like \"%'"+protocal1+"'%\")");
		}
		
		if (nation3 != null && nation3.trim().length() > 0) {
			sb.append(" and (nation like \"%"+nation1+"%\" or nation like \"%"+nation2+"%\" or nation like \"%"+nation3+"%\")");
			
		}
		else if(nation2 != null && nation2.trim().length() > 0){
			sb.append(" and (nation like \"%"+nation1+"%\" or nation like \"%"+nation2+"%\")");
		}
		else if(nation1 != null && nation1.trim().length() > 0){
			sb.append(" and (nation like \"%"+nation1+"%\")");
		}
		int start = (page - 1) * rows;
		sb.append(" limit "+ start+"," +rows);
		return sb.toString();
	}
	@Override
	public String createFindTotalSqlByCondition(String beginTime,
			String endTime, String protocal1, String protocal2,
			String protocal3, String nation1, String nation2, String nation3) {
		// TODO Auto-generated method stub
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) num,as_lable from feed.watcherlab_botnet where 1=1");
		if (beginTime != null && beginTime.trim().length() > 0) {
			sb.append(" and date(discover_time) >= \""+beginTime +"\"");
			
		}
		if (endTime != null && endTime.trim().length() > 0) {
			sb.append(" and date(discover_time) <= \""+endTime+"\"");
			
		}
		if (protocal3 != null && protocal3.trim().length() > 0) {
			sb.append(" and (protocal like \"%'"+protocal1+"'%\" or protocal like \"%'"+protocal2+"'%\" or protocal like \"%'"+protocal3+"'%\")");
			
		}
		else if(protocal2 != null && protocal2.trim().length() > 0){
			sb.append(" and (protocal like \"%'"+protocal1+"'%\" or protocal like \"%'"+protocal2+"'%\")");
		}
		else if(protocal1 != null && protocal1.trim().length() > 0){
			sb.append(" and (protocal like \"%'"+protocal1+"'%\")");
		}
		
		if (nation3 != null && nation3.trim().length() > 0) {
			sb.append(" and (nation like \"%"+nation1+"%\" or nation like \"%"+nation2+"%\" or nation like \"%"+nation3+"%\")");
			
		}
		else if(nation2 != null && nation2.trim().length() > 0){
			sb.append(" and (nation like \"%"+nation1+"%\" or nation like \"%"+nation2+"%\")");
		}
		else if(nation1 != null && nation1.trim().length() > 0){
			sb.append(" and (nation like \"%"+nation1+"%\")");
		}
		
		return sb.toString();
	}
	@Override
	public String createFindTotalSql() {
		// TODO Auto-generated method stub
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) num from feed.watcherlab_botnet where 1=1");
		return sb.toString();
	}
	@Override
	public List<Object[]> baseCountryAndLocationInfo() {
		// TODO Auto-generated method stub
//		String sql = "select DISTINCT city,longitude,latitude  FROM gz_final_result WHERE city<>''";
//		List<Object[]> rtsiList = new ArrayList<Object[]>();
//		rtsiList = findNativeSqlQueryScada(sql);
//		return rtsiList;
	String sql = "select DISTINCT city,longitude,latitude  FROM gz_final_result WHERE city<>''";
		
		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQueryScada(sql);
		return rtsiList;
	}
	@Override
	public List<Object[]> countryAndCountScada() {
		// TODO Auto-generated method stub
		
		String sql = "SELECT city,COUNT(*) FROM ip_address_china_scada where city<>'' GROUP BY city";
		
		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQueryScada(sql);
		return rtsiList;
	}
	@Override
	public List<Object[]> countryAndCountChinaHotScada() {
		// TODO Auto-generated method stub
		String sql = "SELECT city,COUNT(*) FROM gz_ipchina_groupbyservice where city<>'' GROUP BY city";
		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQueryScada(sql);
		return rtsiList;
	}
}