package com.gxx.record_app.base.dao;

import org.apache.commons.lang3.StringUtils;

import com.gxx.record_app.dto.MonitorDto;

/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	none
 *    </dd>
 *    <dt><b>Description:交易监控查询SQL提供类</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Gxx
 * @version 1.0, 2017年06月22日
 * @since record_app
 *
 */
public class TransMonitorSqlProvider {
	/**
	 * 查询交易监控
	 * @param monitorDto
	 * @return
	 */
	public String queryTransMonitor(MonitorDto monitorDto){
		String sql = "SELECT * FROM TRANS_MONITOR WHERE 1=1";
		//方法路径
		if(StringUtils.isNotBlank(monitorDto.getMethodRoute())){
			sql += " AND METHOD_ROUTE = '" + (monitorDto.getMethodRoute()) + "'";
		}
		//方法类型
		if(StringUtils.isNotBlank(monitorDto.getMethodType())){
			sql += " AND METHOD_TYPE = '" + (monitorDto.getMethodType()) + "'";
		}
		//外部系统
		if(StringUtils.isNotBlank(monitorDto.getOuterSystem())){
			sql += " AND OUTER_SYSTEM = '" + (monitorDto.getOuterSystem()) + "'";
		}
		//是否异常发生
		if(monitorDto.getIsException() >= 0){
			sql += " AND IS_EXCEPTION = " + (monitorDto.getIsException());
		}
		//开始时间
		if(StringUtils.isNotBlank(monitorDto.getBeginTime())){
			sql += " AND BEGIN_TIME >= '" + (monitorDto.getBeginTime()) + "'";
		}
		//结束时间
		if(StringUtils.isNotBlank(monitorDto.getEndTime())){
			sql += " AND END_TIME <= '" + (monitorDto.getEndTime()) + "'";
		}
		//最小耗时(毫秒)(>=)
		if(monitorDto.getBeginUsedTime() >= 0){
			sql += " AND USED_TIME >= " + (monitorDto.getBeginUsedTime());
		}
		//最大耗时(毫秒)(<=)
		if(monitorDto.getEndUsedTime() >= 0){
			sql += " AND USED_TIME <= " + (monitorDto.getEndUsedTime());
		}
		//机器ip
		if(StringUtils.isNotBlank(monitorDto.getIp())){
			sql += " AND IP = '" + (monitorDto.getIp()) + "'";
		}
		//备注字段1
		if(StringUtils.isNotBlank(monitorDto.getResv1())){
			sql += " AND RESV1 LIKE '%" + (monitorDto.getResv1()) + "%'";
		}
		//备注字段2
		if(StringUtils.isNotBlank(monitorDto.getResv2())){
			sql += " AND RESV2 LIKE '%" + (monitorDto.getResv2()) + "%'";
		}
		//备注字段3
		if(StringUtils.isNotBlank(monitorDto.getResv3())){
			sql += " AND RESV3 LIKE '%" + (monitorDto.getResv3()) + "%'";
		}
		//id倒序
		sql += " ORDER BY ID DESC";
		//分页
		if(monitorDto.getActivePage() > 0 && monitorDto.getPageSize() > 0){
			sql += " LIMIT " + (monitorDto.getActivePage()-1)*monitorDto.getPageSize() + "," + monitorDto.getPageSize();
		}
		return sql;
	}
	
	/**
	 * 查询交易监控总数
	 * @param monitorDto
	 * @return
	 */
	public String countTransMonitor(MonitorDto monitorDto){
		String sql = "SELECT COUNT(1) FROM TRANS_MONITOR WHERE 1=1";
		//方法路径
		if(StringUtils.isNotBlank(monitorDto.getMethodRoute())){
			sql += " AND METHOD_ROUTE = '" + (monitorDto.getMethodRoute()) + "'";
		}
		//方法类型
		if(StringUtils.isNotBlank(monitorDto.getMethodType())){
			sql += " AND METHOD_TYPE = '" + (monitorDto.getMethodType()) + "'";
		}
		//外部系统
		if(StringUtils.isNotBlank(monitorDto.getOuterSystem())){
			sql += " AND OUTER_SYSTEM = '" + (monitorDto.getOuterSystem()) + "'";
		}
		//是否异常发生
		if(monitorDto.getIsException() >= 0){
			sql += " AND IS_EXCEPTION = " + (monitorDto.getIsException());
		}
		//开始时间
		if(StringUtils.isNotBlank(monitorDto.getBeginTime())){
			sql += " AND BEGIN_TIME >= '" + (monitorDto.getBeginTime()) + "'";
		}
		//结束时间
		if(StringUtils.isNotBlank(monitorDto.getEndTime())){
			sql += " AND END_TIME <= '" + (monitorDto.getEndTime()) + "'";
		}
		//最小耗时(毫秒)(>=)
		if(monitorDto.getBeginUsedTime() >= 0){
			sql += " AND USED_TIME >= " + (monitorDto.getBeginUsedTime());
		}
		//最大耗时(毫秒)(<=)
		if(monitorDto.getEndUsedTime() >= 0){
			sql += " AND USED_TIME <= " + (monitorDto.getEndUsedTime());
		}
		//机器ip
		if(StringUtils.isNotBlank(monitorDto.getIp())){
			sql += " AND IP = '" + (monitorDto.getIp()) + "'";
		}
		//备注字段1
		if(StringUtils.isNotBlank(monitorDto.getResv1())){
			sql += " AND RESV1 LIKE '%" + (monitorDto.getResv1()) + "%'";
		}
		//备注字段2
		if(StringUtils.isNotBlank(monitorDto.getResv2())){
			sql += " AND RESV2 LIKE '%" + (monitorDto.getResv2()) + "%'";
		}
		//备注字段3
		if(StringUtils.isNotBlank(monitorDto.getResv3())){
			sql += " AND RESV3 LIKE '%" + (monitorDto.getResv3()) + "%'";
		}
		return sql;
	}
	
	/**
	 * 查询平均耗时
	 * @param monitorDto
	 * @return
	 */
	public String queryAvgUsedTime(MonitorDto monitorDto){
		String sql = "SELECT AVG(USED_TIME) FROM TRANS_MONITOR WHERE 1=1";
		//方法路径
		if(StringUtils.isNotBlank(monitorDto.getMethodRoute())){
			sql += " AND METHOD_ROUTE = '" + (monitorDto.getMethodRoute()) + "'";
		}
		//方法类型
		if(StringUtils.isNotBlank(monitorDto.getMethodType())){
			sql += " AND METHOD_TYPE = '" + (monitorDto.getMethodType()) + "'";
		}
		//外部系统
		if(StringUtils.isNotBlank(monitorDto.getOuterSystem())){
			sql += " AND OUTER_SYSTEM = '" + (monitorDto.getOuterSystem()) + "'";
		}
		//是否异常发生
		if(monitorDto.getIsException() >= 0){
			sql += " AND IS_EXCEPTION = " + (monitorDto.getIsException());
		}
		//开始时间
		if(StringUtils.isNotBlank(monitorDto.getBeginTime())){
			sql += " AND BEGIN_TIME >= '" + (monitorDto.getBeginTime()) + "'";
		}
		//结束时间
		if(StringUtils.isNotBlank(monitorDto.getEndTime())){
			sql += " AND END_TIME <= '" + (monitorDto.getEndTime()) + "'";
		}
		//最小耗时(毫秒)(>=)
		if(monitorDto.getBeginUsedTime() >= 0){
			sql += " AND USED_TIME >= " + (monitorDto.getBeginUsedTime());
		}
		//最大耗时(毫秒)(<=)
		if(monitorDto.getEndUsedTime() >= 0){
			sql += " AND USED_TIME <= " + (monitorDto.getEndUsedTime());
		}
		//机器ip
		if(StringUtils.isNotBlank(monitorDto.getIp())){
			sql += " AND IP = '" + (monitorDto.getIp()) + "'";
		}
		//备注字段1
		if(StringUtils.isNotBlank(monitorDto.getResv1())){
			sql += " AND RESV1 LIKE '%" + (monitorDto.getResv1()) + "%'";
		}
		//备注字段2
		if(StringUtils.isNotBlank(monitorDto.getResv2())){
			sql += " AND RESV2 LIKE '%" + (monitorDto.getResv2()) + "%'";
		}
		//备注字段3
		if(StringUtils.isNotBlank(monitorDto.getResv3())){
			sql += " AND RESV3 LIKE '%" + (monitorDto.getResv3()) + "%'";
		}
		return sql;
	}
}