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.ProtocolProgressDao;
import com.ps.visualization.vo.ProtocolProgress;

@Repository(value = "protocolProgressDao")
public class ProtocolProgressDaoImpl extends
		BaseDaoImpl<ProtocolProgress, Integer> implements ProtocolProgressDao {

	@Override
	public List getScanProgress(String protocolName) {
		// TODO Auto-generated method stub
		String sql = "";
		if (protocolName.equalsIgnoreCase("all")) {
			sql = "select protocol,ip_num_done/ip_num_total b from protocol_progress where rounds='"
					+ getScanRounds() + "'order by protocol asc";
		} else {
			sql = "select protocol,ip_num_done/ip_num_total b from protocol_progress where protocol =\""
					+ protocolName
					+ "\" and rounds='"
					+ getScanRounds()
					+ "' order by protocol asc";
		}
		List<Object[]> spList = new ArrayList<Object[]>();
		spList = findNativeSqlQuery(sql);
		return spList;
	}

	@Override
	public List getScanTopKey(String protocolName, String[] protocolGroup) {
		// TODO Auto-generated method stub
		Integer num = 10;
		String sql = "";
		if (protocolName.equalsIgnoreCase("all")) {
			sql = "select protocol,ip_num_exist b from protocol_progress where rounds='"
					+ getScanRounds()
					+ "' and protocol<>'"
					+ protocolGroup[0]
					+ "' and protocol<>'"
					+ protocolGroup[1]
					+ "' order by b desc limit " + num;
		} else {
			sql = "select protocol,ip_num_exist b from protocol_progress where protocol = \""
					+ protocolName
					+ "\" and rounds='"
					+ getScanRounds()
					+ "' and protocol<>'"
					+ protocolGroup[0]
					+ "' and protocol<>'"
					+ protocolGroup[1]
					+ "' order by b desc limit " + num;
		}
		List<Object[]> stList = new ArrayList<Object[]>();
		stList = findNativeSqlQuery(sql);
		return stList;
	}

	@Override
	public List getProtocolInfo() {
		// TODO Auto-generated method stub
		String sql = "select protocol,ip_num_done,ip_num_exist from protocol_progress";
		List<Object[]> stList = new ArrayList<Object[]>();
		stList = findNativeSqlQuery(sql);
		return stList;

	}

	@Override
	public List getAllScanRoundLog(String[] protocolGroup) {
		// TODO Auto-generated method stub
		String sql = "SELECT( SELECT SUM(ps1.ip_num_done) sumdone FROM protocol_progress ps1 where ps1.rounds = '"
				+ getScanRounds()
				+ "' and ps1.protocol<>'"
				+ protocolGroup[0]
				+ "' and ps1.protocol<>'"
				+ protocolGroup[1]
				+ "') sumdone, ( SELECT SUM(ps1.ip_num_exist) sumexist FROM protocol_progress ps1 where ps1.rounds = '"
				+ getScanRounds()
				+ "' and ps1.protocol<>'"
				+ protocolGroup[0]
				+ "' and ps1.protocol<>'"
				+ protocolGroup[1]
				+ "') sumexist, ps.time_start,"
				+ "ps.rounds, ss.end_time FROM protocol_progress ps, scan_speed ss WHERE ps.protocol = 'Siemens S7' AND ps.rounds = '"
				+ getScanRounds()
				+ "' AND ss.id = ( SELECT max(s.id) FROM scan_speed AS s )";
		// String sql =
		// "select SUM(ps.ip_num_done),SUM(ps.ip_num_exist),ps.time_start,ps.rounds,ss.end_time from protocol_progress ps,scan_speed ss WHERE ps.protocol = 'Siemens S7' AND ps.rounds='2' and ss.id=(select max(s.id) from scan_speed as s)";
		List<Object[]> stList = new ArrayList<Object[]>();

		stList = findNativeSqlQuery(sql);
		return stList;
	}

	@Override
	public List getProtocolRate() {
		// TODO Auto-generated method stub
		String sql = "SELECT pp.protocol,pp.ip_num_exist,pp.ip_num_exist/(SELECT SUM(ip_num_exist) FROM protocol_progress) FROM protocol_progress pp";
		List<Object[]> stList = new ArrayList<Object[]>();
		stList = findNativeSqlQuery(sql);
		return stList;
	}

	@Override
	public List getProtocolName() {
		// TODO Auto-generated method stub
		String sql = "SELECT protocol FROM protocol_progress WHERE rounds = '"
				+ getScanRounds() + "'";
		List<Object[]> stList = new ArrayList<Object[]>();
		stList = findNativeSqlQuery(sql);
		return stList;
	}

	@Override
	public List getTotalScanProcess() {
		// TODO Auto-generated method stub
		// SELECT SUM(ip_num_done)/SUM(ip_num_total) FROM protocol_progress
		// WHERE rounds = '2';

		String sql = "SELECT SUM(ip_num_done)/SUM(ip_num_total) FROM protocol_progress WHERE rounds = '"
				+ getScanRounds() + "'";
		List<Object[]> stList = new ArrayList<Object[]>();
		stList = findNativeSqlQuery(sql);
		return stList;
	}

	@Override
	public List getProtocolNumRounds() {
		// TODO Auto-generated method stub
		String sql = "SELECT rounds,sum(ip_num_exist) FROM protocol_progress GROUP BY rounds ORDER BY rounds asc";
		List<Object[]> stList = new ArrayList<Object[]>();
		stList = findNativeSqlQuery(sql);
		return stList;
	}

}