package com.ps.visualization.dao.impl;

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

import org.springframework.stereotype.Repository;

import com.ps.base.dao.impl.BaseDaoImpl;
import com.ps.visualization.dao.PortsinfoDao;
import com.ps.visualization.vo.Portsinfo;

@Repository(value = "portsinfoDao")
public class PortsinfoDaoImpl extends BaseDaoImpl<Portsinfo, Integer> implements
		PortsinfoDao {

	@Override
	public List realTimeScanInfo(String[] protocolGroup) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM realtime_scaninfo where country<>\"\" and city<>'' and longitude<>'' and latitude<>'' and service<>'"
				+ protocolGroup[0]
				+ "' and service<>'"
				+ protocolGroup[1]
				+ "' ORDER BY build_date DESC LIMIT 50";

		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

	@Override
	public List realTimeScanInfoChina(String[] protocolGroup) {
		// TODO Auto-generated method stub
		String sql = "SELECT pf.service,pf.`port`,pf.types,pf.build_date,pf.ip,pf.country,pf.regname,pf.latitude,pf.longitude FROM portsinfo pf where pf.country='China' AND pf.regname<>'' and longitude<>'' and latitude<>'' and pf.service<>'"
				+ protocolGroup[0]
				+ "' and pf.service<>'"
				+ protocolGroup[1]
				+ "' ORDER BY pf.build_date DESC LIMIT 50";

		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

	@Override
	public List getScanningProtocol() {
		// TODO Auto-generated method stub

		String sql = "SELECT ps.service FROM portsinfo ps WHERE ps.build_date = (select max(s.build_date) from portsinfo s WHERE s.build_date != '')";

		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

	@Override
	public List getExistProtocolNum() {
		// TODO Auto-generated method stub
		String sql = "select num from view v where v.keys = 'history_num_total'";

		// String sql = "select sum(ip_num_exit) from protocol_progress";
		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

	@Override
	public List getExistProtocolNum_ics() {
		// TODO Auto-generated method stub
		String sql = "select num from view v where v.keys = 'history_num_total_ics'";

		// String sql = "select sum(ip_num_exit) from protocol_progress";
		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

	@Override
	public List getProtocolCountryRelation(String[] protocolGroup) {
		// TODO Auto-generated method stub
		String sql = "SELECT pf.service,pf.country,COUNT(pf.country) FROM portsinfo pf  where service<>'"
				+ protocolGroup[0]
				+ "' and service<>'"
				+ protocolGroup[1]
				+ "' GROUP BY pf.service,pf.country";

		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

	@Override
	public List getProtocolCountry_countryTop9(String[] protocolGroup) {
		// TODO Auto-generated method stub
		String sql = "SELECT pf.country from portsinfo pf WHERE pf.country<>'' and service<>'"
				+ protocolGroup[0]
				+ "' and service<>'"
				+ protocolGroup[1]
				+ "' GROUP BY pf.country ORDER BY COUNT(pf.country) DESC LIMIT 9";

		List<Object[]> rtsiList = new ArrayList<Object[]>();
		rtsiList = findNativeSqlQuery(sql);
		return rtsiList;
	}

}