package cn.estsh.i3plus.pojo.base.tool;

import cn.estsh.i3plus.pojo.base.bean.BaseBean;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import org.apache.commons.lang3.StringUtils;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Description :
 * @Reference :
 * @Author : alwaysfrin
 * @CreateDate : 2018-10-13 18:17
 * @Modify:
 **/
public class HqlPack {

	/**
	 * 防止sql注入
	 * @param data
	 * @return
	 */
	public static String getSafeParam(Object data){
		return data.toString().replaceAll(";","")
					.replaceAll("'","")
					.replaceAll("\"","")
//					.replaceAll("/","")
					.replaceAll("\\\\","")
					.replaceAll("delete","")
					.replaceAll("update","")
					.replaceAll("insert","");
	}

	/**
	 * 封装日期
	 * @param startDate 开始日期
	 * @param endDate 开始日期和结束日期，以逗号分隔（分为开始时间和结束时间）
	 * @param columnName HQL里对应的时间字段
	 * @param result 封装的HQL
	 * @param isShowTime 是否包含时分秒
	 */
	public static void timeBuilder( String startDate,String endDate, String columnName, StringBuffer result, boolean isShowTime) {
		if (startDate == null || startDate.trim().length() == 0) {
			startDate = "1900-01-01";
		} else {
			startDate = startDate.trim();
		}
		if (isShowTime && startDate.trim().length()<=11) {
			startDate+= " 00:00:00";
		}
		if (endDate == null || endDate.trim().length() == 0) {
			endDate = "2100-01-01";
		} else {
			endDate = endDate.trim();
		}
		if (isShowTime&& endDate.trim().length()<=11) {
			endDate+= " 23:59:59";
		}
		startDate = getSafeParam(startDate);
		endDate = getSafeParam(endDate);

		result.append(" and model." + columnName + " between '" + startDate + "' and '" + endDate + "'");
	}

	/**
	 * 封装日期
	 * @param date 开始日期和结束日期，以逗号分隔（分为开始时间和结束时间）
	 * @param columnName HQL里对应的时间字段
	 * @param result 封装的HQL
	 * @param showTaday 如果没有开始时间和结束时间，是否查询当天时间，还是查询所有时间。true:查询当天时间，false:查询所有
	 * @param isShowTime 是否包含时分秒
	 */
		public static void timeBuilder( String date, String columnName, StringBuffer result, Boolean showTaday,boolean isShowTime) {
		if(date != null && date.trim().length() > 0){
			date = getSafeParam(date);

			String today = (new SimpleDateFormat("yyyy-MM-dd")).format(new Date());
			if(date != null && date.trim().length() > 0 && (date.length() == 1 || ",".equals(date))){
				//只有一个逗号
				date = "";
			}

			String[] time = date.split(",");
			if(time.length == 1){
				//只有开始日期，没有结束日期
				result.append(" and model." + columnName + " like '%" + time[0] + "%'");
			}else if (time.length == 2 && ((time[0] != null && time[0].trim().length() > 0) || (time[1] != null & time[1].trim().length() > 0))) {
				if (time[0] == null || time[0].trim().length() == 0) {
					time[0] = "1900-01-01";
				} else {
					time[0] = time[0].trim();
				}
				if (isShowTime && time[0].trim().length()<=11) {
					time[0]+= " 00:00:00";
				}
				if (time[1] == null || time[1].trim().length() == 0) {
					time[1] = "2100-01-01";
				} else {
					time[1] = time[1].trim();
				}
				if (isShowTime&& time[1].trim().length()<=11) {
					time[1]+= " 23:59:59";
				}
				result.append(" and model." + columnName + " between '" + time[0] + "' and '" + time[1] + "'");
			} else {
				if (showTaday) {
					if (isShowTime) {
						result.append(" and model." + columnName + " between '" + today + " 00:00:00' and '" + today + " 23:59:59'");
					}else{
						result.append(" and model." + columnName + " between '" + today + "' and '" + today + "'");
					}
				}
			}
		}
	}


	/**
	 * 封装日期
	 * @param date 日期，需要和时间字段的值格式相同
	 * @param startColumnName HQL里对应的开始时间字段
	 * @param endColumnName HQL里对应的结束时间字段
	 * @param result 封装的HQL
	 * @param isShowTime 是否包含时分秒
	 */
	public static void getTimeBetweenCol(String date, String startColumnName, String endColumnName, StringBuffer result,boolean isShowTime) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if(date != null && date.trim().length() > 0) {
			date = getSafeParam(date);
		}else {
			date = sdf.format(new Date());
		}
		if(!isShowTime) {
			date = date.substring(0,10);
			result.append(" and '"+date+"' between substring(model." + startColumnName + ",1,10) and substring(model." + endColumnName + ",1,10)");
			return;
		}else if(date.trim().length()<=11){
			date+= " 00:00:00";
		}
		result.append(" and '"+date+"' between model." + startColumnName + " and model." + endColumnName);
	}

	/**
	 * 封装String对象成order by排序
	 * @param columnName 列名
	 * @param result
	 */
	public static void getOrderByPack(Object order[],String[] columnName, StringBuffer result) {
		String sqlStr = "";
		String orderByStr = "";
		for(int i=0;i < order.length;i++){
			if (order[i] != null && order[i].toString().trim().length() > 0) {
				if(Integer.parseInt(order[i].toString()) == 1){
					order[i] = "asc";
				}else{
					order[i] = "desc";
				}
				sqlStr += " model."+columnName[i]+" " + order[i].toString() + ",";
			}
		}

		if(sqlStr.length() > 0 && sqlStr.lastIndexOf(",") == sqlStr.length() -1){
			sqlStr = sqlStr.substring(0,sqlStr.length()-1);
			orderByStr = " order by " + sqlStr;
		}

		result.append(orderByStr);
	}

	/**
	 * 对象默认排序
	 */
	public static void getOrderDefault(BaseBean bean) {
		if(bean != null && StringUtils.isBlank(bean.getOrderByParam())){
			bean.setOrderByParam("modifyDatetime");
			bean.setAscOrDesc(CommonEnumUtil.ASC_OR_DESC.DESC.getValue());
		}
	}

	/**
	 * 封装String对象成like语句
	 * @param str 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringLikerPack(String str,String columnName, StringBuffer result) {
		if (str != null && str.trim().length() > 0) {
			str = getSafeParam(str);
			result.append(" and model."+columnName+" like '%" + str + "%'");
		}
	}

	/**
	 * 封装String对象成like语句
	 * @param str 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringLikerPackOr(String str,String columnName, StringBuffer result) {
		if (str != null && str.trim().length() > 0) {
			str = getSafeParam(str);
			result.append(" or model."+columnName+" like '%" + str + "%'");
		}
	}

	/**
	 * 封装String对象成like语句(右侧模糊)
	 * @param str 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringRightLikerPack(String str,String columnName, StringBuffer result) {
		if (str != null && str.trim().length() > 0) {
			str = getSafeParam(str);
			result.append(" and model."+columnName+" like '" + str + "%'");
		}
	}

	/**
	 * 封装String对象成like语句(左侧模糊)
	 * @param str 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringLeftLikerPack(String str,String columnName, StringBuffer result) {
		if (str != null && str.trim().length() > 0) {
			str = getSafeParam(str);
			result.append(" and model."+columnName+" like '%" + str + "'");
		}
	}

	/**
	 * 封装String对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringEqualPack(String data,String columnName, StringBuffer result) {
		if(data != null && data.trim().length() > 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = '" + data + "'");
		}
	}

	/**
	 * 输入日期大于所选字段
	 *
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringBiggerPack(Object data, String columnName, StringBuffer result) {
		if (data != null && StringUtils.isNotBlank(String.valueOf(data))) {
			data = getSafeParam(data);
			result.append(" and model." + columnName + " < '" + data + "'");
		}
	}

	/**
	 * 封装String对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getStringEqualPackOr(String data,String columnName, StringBuffer result) {
		if(data != null && data.trim().length() > 0){
			data = getSafeParam(data);
			result.append(" or model."+columnName+" = '" + data + "'");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumEqualPack(Object data,String columnName, StringBuffer result) {
		if (data != null) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumEqualPackOr(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Long.parseLong(data.toString()) > 0){
			data = getSafeParam(data);
			result.append(" or model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumEqualPackForZero(Object data,String columnName, StringBuffer result) {
		if (data != null && Long.parseLong(data.toString()) > 0) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumWithZeroEqualPack(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Long.parseLong(data.toString()) >= 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成大于语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumBiggerPack(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Long.parseLong(data.toString()) > 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" > " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成小于语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumSmallerPack(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Long.parseLong(data.toString()) > 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" < " + data + "");
		}
	}

	/**
	 * 封装double对象成大于语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getDoubleBiggerPack(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Double.parseDouble(data.toString()) > 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" > " + data + "");
		}
	}

	/**
	 * 封装double对象成小于语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getDoubleSmallerPack(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Double.parseDouble(data.toString()) > 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" < " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumEqualPack(Object data,String columnName, StringBuffer result,Integer expvalue) {
		if(data!=null&&Long.parseLong(data.toString()) > (long)expvalue){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装double对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumEqualPackDouble(Object data,String columnName, StringBuffer result) {
		if(data!=null&&Double.parseDouble(data.toString()) > 0){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumEqualPackDouble(Object data,String columnName, StringBuffer result,Integer expvalue) {
		if(data!=null&&Double.parseDouble(data.toString()) > (double)expvalue){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" = " + data + "");
		}
	}

	/**
	 * 封装long或者int的整数对象成equal语句(不等于)
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumNOEqualPack(Object data,String columnName, StringBuffer result) {
		if(data!=null){
			data = getSafeParam(data);
			result.append(" and model."+columnName+" <> " + data + "");
		}
	}

	/**
	 * 封装in查询语句
	 * @param data
	 * @param columnName
	 * @param result
	 */
	public static void getInPack(String data,String columnName, StringBuffer result){
		if (data!=null&&data.trim().length()>0) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" in ( "+ data+ " )");
		}
	}

	/**
	 * 封装in查询语句
	 * @param data
	 * @param columnName
	 * @param result
	 */
	public static void getOrInPack(String data,String columnName, StringBuffer result){
		if (data!=null&&data.trim().length()>0) {
			data = getSafeParam(data);
			result.append(" or model."+columnName+" in ( "+ data+ " )");
		}
	}

	/**
	 * 封装in String查询语句
	 * @param data
	 * @param columnName
	 * @param result
	 */
	public static void getInPackString(String data,String columnName, StringBuffer result){
		if (data != null && data.trim().length()>0) {
			data = getSafeParam(data);
			//判断最后一位是不是逗号
			if(data.lastIndexOf(",") != (data.length()-1)){
				data += ",";
			}
			String[] dataArray = data.substring(0, data.length()-1).split(",");
			data = "";
			for (int i = 0 ; i < dataArray.length ;i++) {
				if(i ==  dataArray.length -1){
					data += "'" + dataArray[i] + "'";
				}else{
					data += "'" + dataArray[i] + "',";
				}
			}
			result.append(" and model."+columnName+" in ( "+ data+ " )");
		}
	}

	/**
	 * 封装in String查询语句
	 * @param data
	 * @param columnName
	 * @param result
	 */
	public static void getInOrPackString(String data,String columnName, StringBuffer result){
		if (data != null && data.trim().length()>0) {
			data = getSafeParam(data);
			//判断最后一位是不是逗号
			if(data.lastIndexOf(",") != (data.length()-1)){
				data += ",";
			}
			String[] dataArray = data.substring(0, data.length()-1).split(",");
			data = "";
			for (int i = 0 ; i < dataArray.length ;i++) {
				if(i ==  dataArray.length -1){
					data += "'" + dataArray[i] + "'";
				}else{
					data += "'" + dataArray[i] + "',";
				}
			}
			result.append(" or model."+columnName+" in ( "+ data+ " )");
		}
	}

	/**
	 * 封装in String查询语句
	 * @param data
	 * @param columnName
	 * @param result
	 */
	public static void getNotInPackString(String data,String columnName, StringBuffer result){
		if (data != null && data.trim().length()>0) {
			data = getSafeParam(data);
			//判断最后一位是不是逗号
			if(data.lastIndexOf(",") != (data.length()-1)){
				data += ",";
			}
			String[] dataArray = data.substring(0, data.length()-1).split(",");
			data = "";
			for (int i = 0 ; i < dataArray.length ;i++) {
				if(i ==  dataArray.length -1){
					data += "'" + dataArray[i] + "'";
				}else{
					data += "'" + dataArray[i] + "',";
				}
			}
			result.append(" and model."+columnName+" not in ( "+ data+ " )");
		}
	}

	/**
	 * 封装not in查询语句
	 * @param data
	 * @param columnName
	 * @param result
	 */
	public static void getNotInPack(String data,String columnName, StringBuffer result){
		if (data!=null&&data.trim().length()>0) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" not in ( "+ data+ " )");
		}
	}

	public static void getCheckStrInArr(String data,String columnName,StringBuffer result){
		if (data!=null&&data.trim().length()>0) {
			data = getSafeParam(data);
			result.append(" and dbo.CheckStrInArr('"+data+"',model."+columnName+")>0 ");
		}
	}

	/**
	 * 封装long或者int的整数对象成like语句
	 * @param data 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumLikerPack(Object data,String columnName, StringBuffer result) {
		if (data != null) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" like '%" + data + "%'");
		}
	}

	/**
	 * 封装String对象成like语句
	 * @param data 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumLikerPackOr(Object data,String columnName, StringBuffer result) {
		if (data != null) {
			data = getSafeParam(data);
			result.append(" or model."+columnName+" like '%" + data + "%'");
		}
	}

	/**
	 * 封装String对象成like语句(右侧模糊)
	 * @param data 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumRightLikerPack(Object data,String columnName, StringBuffer result) {
		if (data != null) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" like '" + data + "%'");
		}
	}

	/**
	 * 封装String对象成like语句(左侧模糊)
	 * @param data 对象值
	 * @param columnName 列名
	 * @param result
	 */
	public static void getNumLeftLikerPack(Object data,String columnName, StringBuffer result) {
		if (data != null) {
			data = getSafeParam(data);
			result.append(" and model."+columnName+" like '%" + data + "'");
		}
	}
}
