package com.unis.crk.utils;

import com.unis.crk.enums.DataBaseType;
import org.apache.commons.lang.StringUtils;

import java.io.FileInputStream;
import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public class FunUtils {
	private static Properties p = new Properties();
	public static DataBaseType dbType=DataBaseType.Mysql;
	public static String DATE_FORMAT_SHORT="%Y-%m-%d";

	public static String DATE_FORMAT_LONG;

	public static String DATE_FORMAT_BLONG;
	public static String DataBaseSplitChar;

	// 读取配置文件
	  static {
		  FileInputStream fis = null;
		    try {
				ClassLoader classLoader = FunUtils.class.getClassLoader();
				URL uurl = classLoader.getResource("application.properties");
				URI uri = null;//利用url类的成员方法直接获取uri对象
				String path ="";
				try {
					uri = uurl.toURI();
					path = uri.getPath();
				} catch (URISyntaxException e) {
					//e.printStackTrace();
				}
		    	//System.out.println(path);
				fis = new FileInputStream(path);//属性文件流
			  p.load(fis);
			  String dvName=DriveName();
			  if (dvName.indexOf("sqlserver")!=-1) {
				  dbType=DataBaseType.Sqlserver;
			  }else if (dvName.indexOf("oracle")!=-1) {
				  dbType=DataBaseType.Oracle;
			  }else if (dvName.indexOf("mysql")!=-1) {
				  dbType=DataBaseType.Mysql;
			  }else if (dvName.indexOf("gbase")!=-1) {
				  dbType=DataBaseType.GBase8s;
			  }
			  DataBaseSplitChar=FunUtils.dbType==DataBaseType.GBase8s?"/*'*/:/*'*/":".";
			  DATE_FORMAT_SHORT = (FunUtils.dbType==DataBaseType.GBase8s?"%Y-%m-%d":
				(FunUtils.dbType==DataBaseType.Mysql?"%Y-%m-%d":
					(FunUtils.dbType==DataBaseType.Oracle?"YYYY-MM-DD":"")));
			  DATE_FORMAT_LONG = (FunUtils.dbType==DataBaseType.GBase8s?"%Y-%m-%d %H:%M:%S":
				(FunUtils.dbType==DataBaseType.Mysql?"%Y-%m-%d %T":
					(FunUtils.dbType==DataBaseType.Oracle?"YYYY-MM-DD HH24:MI:SS":"")));
			  DATE_FORMAT_BLONG = (FunUtils.dbType==DataBaseType.GBase8s?"%Y-%m-%d %H:%M":
				(FunUtils.dbType==DataBaseType.Mysql?"%Y-%m-%d %H:%i":
					(FunUtils.dbType==DataBaseType.Oracle?"YYYY-MM-DD HH24:MI":"")));
	    } catch (IOException e) {
	      e.printStackTrace();
	    }finally {
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	  }
	  public static String DriveName(){
		  String ret="mysql";
		  try {
				ret=p.getProperty("spring.datasource.driver-class-name")==null?"":p.getProperty("spring.datasource.driver-class-name");
			}catch (Exception e) {
			}
			return ret.toLowerCase();
	  }
	  public static String DBName(){
		  String ret="SYSTEM";
		  try {
				ret=p.getProperty("spring.datasource.username")==null?"":p.getProperty("spring.datasource.username");
			}catch (Exception e) {
			}
			return ret;
	  }
	public static String getListFiledMap(List<Map<String, Object>> list,int index,String filedName) {
		if (list != null && list.size() >= index) {
			Map<String, Object> map = list.get(index);
			Object value = map.get(filedName);
			return value != null?value.toString():"";
		}
		return "";
	}
	public static String getListFiledMapToFrist(List<Map<String, Object>> list,String filedName) {
		
		return getListFiledMap(list, 0,filedName);
	}
	public static boolean isNull(Map<String, Object> map,String filedName) {
		Object value = map.get(filedName);
		return value != null&&StringUtils.isBlank(value.toString());
	}
	public static String getMapValue(Map<String, Object> map,String filedName) {
		return GetValue(map.get(filedName));
	}
	public static String pageSql(String sql,int start,int limit) {
		String ret="";
		switch(dbType) {
		case Sqlserver:
			ret="select top "+limit+" * from (select row_number() over(order by sno asc) as rownumber,* from ("+sql+") a) temp_row where rownumber>(("+(start/limit)+"-1)*"+limit+")";
			break;
		case Oracle:
			ret="SELECT *  FROM (SELECT tt.*, ROWNUM AS rowno FROM ( "+sql+" ) tt WHERE ROWNUM <= "+(limit+start)+") table_alias WHERE table_alias.rowno >"+start;
			break;
		case Mysql:
			ret=sql+" LIMIT " + start + ","+limit;
			break;
		case GBase8s:
			ret="SELECT *  FROM (SELECT tt.*, rownum AS rowno FROM ( "+sql+" ) tt WHERE rownum <= "+(limit+start)+") table_alias WHERE table_alias.rowno >"+start;
			break;
		}
		return ret;
	}
	public static String basic_storehouse(String org_id) {
		return "(select org_id,cast(storehouse_id as "+(dbType==DataBaseType.Mysql?"char":"varchar(50)")+") as storehouse_id,storehouse_code,storehouse_name\n" + 
				"from depot"+FunUtils.DataBaseSplitChar+"basic_storehouse where org_id='"+org_id+"'\n" + 
				"UNION \n" + 
				"select org_id,id,storageTank_code,storageTank_name\n" + 
				"from depot"+FunUtils.DataBaseSplitChar+"basic_tank where org_id='"+org_id+"')\n";
	}
	public static String basic_warehouse(String org_id) {
		return "(select org_id,cast(warehouse_id as "+(dbType==DataBaseType.Mysql?"char":"varchar(50)")+") as warehouse_id,warehouse_code,warehouse_name,cast(storehouse_id as "+(dbType==DataBaseType.Mysql?"char":"varchar(50)")+") as storehouse_id\n" + 
				"from depot"+FunUtils.DataBaseSplitChar+"basic_warehouse where org_id='"+org_id+"'\n" + 
				"UNION \n" + 
				"select org_id,id,storageTank_code,storageTank_name,id\n" + 
				"from depot"+FunUtils.DataBaseSplitChar+"basic_tank where org_id='"+org_id+"')\n";
	}
	/**
	 * 空串函数，如：ifnull(**,**)
	 * @return
	 */
	public static String fn_ifnull() {
		String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="isnull";
			break;
		case Oracle:
			ret="nvl";
			break;
		case Mysql:
			ret="ifnull";
			break;
		case GBase8s:
			ret="nvl";
			break;
		}
		return ret;
	  }
	/**
	 * 空串函数，如：ifnull(**,**)
	 * @return
	 */
	public static String fn_ifnull(String filedName,String nullValue) {
		String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="isnull("+filedName+","+nullValue+")";
			break;
		case Oracle:
			ret="nvl("+filedName+","+nullValue+")";
			break;
		case Mysql:
			ret="ifnull("+filedName+","+nullValue+")";
			break;
		case GBase8s:
			ret="nvl("+filedName+","+nullValue+")";
			break;
		}
		return ret;
	}
	public static String fn_if() {
		String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="decode";
			break;
		case Oracle:
			ret="decode";
			break;
		case Mysql:
			ret="if";
			break;
		case GBase8s:
			ret="decode";
			break;
		}
		return ret;
	  }
	  /**
	   * 字符字段:varchar<--->nvarchar
	   * @return
	   */
	  public static String fld_Char() {
		  String ret="";
			switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
			case Sqlserver:
				ret="varchar";
				break;
			case Oracle:
				ret="nvarchar";
				break;
			case Mysql:
				ret="varchar";
				break;
			case GBase8s:
				ret="varchar";
				break;
			}
			return ret;
	  }
	  public static String fn_toChar() {
		  String ret="";
			switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
			case Sqlserver:
				ret="";
				break;
			case Oracle:
				ret="TO_CHAR";
				break;
			case Mysql:
				ret="";
				break;
			case GBase8s:
				ret="";
				break;
			}
			return ret;
	  }
	  public static String fn_length(){
		  String ret="";
			switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
			case Sqlserver:
				ret="len";
				break;
			case Oracle:
				ret="length";
				break;
			case Mysql:
				ret="len";
				break;
			case GBase8s:
				ret="length";
				break;
			}
			return ret;
	  }
	  /**
	   * 实现mysql中Group_CONCAT
	   * @return
	   */
//	  public static String fn_GroupConcat() { //针对oracle11
//		  String driverName=DriveName();
//		  return (driverName.indexOf("oracle")!=-1?"WM_CONCAT":"Group_CONCAT");
//	  }
	  /**
	   * 实现mysql中Group_CONCAT
	   * @return
	   */
	  public static String fn_GroupConcat(String str) { //针对oracle12
		  String ret="";
			switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
			case Sqlserver:
				ret="";
				break;
			case Oracle:
				ret="LISTAGG ("+str+",',') WITHIN GROUP(order by null)";
				break;
			case Mysql:
				ret="Group_CONCAT("+str+")";
				break;
			case GBase8s:
				ret="WM_CONCAT("+str+")";
				break;
			}
			return ret;
	  }
	  /**
	   * 实现取UUID功能
	   * @return
	   */
  public static String fn_UUID() {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="REPLACE(UUID(),'-','')";
			break;
		case Oracle:
			ret="rawtohex(sys_guid())";
			break;
		case Mysql:
			ret="REPLACE(UUID(),'-','')";
			break;
		case GBase8s:
			ret="sys_guid()";
			break;
		}
		return ret;
	  }
  /**
   * 日期字段
   * @return
   */
  public static String fld_Date() {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="datetime";
			break;
		case Oracle:
			ret="date";
			break;
		case Mysql:
			ret="datetime";
			break;
		case GBase8s:
			ret="datetime year to fraction(5)";
			break;
		}
		return ret;
  }
  public static String fn_FormatFld(String FieldName) {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret=FieldName;
			break;
		case Oracle:
			ret="\""+FieldName+"\"";
			break;
		case Mysql:
			ret=FieldName;
			break;
		case GBase8s:
			ret="\""+FieldName+"\"";
			break;
		}
		return ret;
  }
  public static String fn_FormatDate(String fmt,String FieldName) {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="convert(varchar(10),"+FieldName+",120)";
			break;
		case Oracle:
			ret="TO_CHAR("+FieldName+",'"+fmt+"')";
			break;
		case Mysql:
			ret="DATE_FORMAT("+FieldName+",'"+fmt+"')";
			break;
		case GBase8s:
			ret="gbase_to_char("+FieldName+",'"+fmt+"')";
			break;
		}
		return ret;
  }
  /**
   * 字段转为整型
   * @param FieldName
   * @return
   */
  public static String fn_CastInt(String FieldName) {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="cast("+FieldName+" as int)";
			break;
		case Oracle:
			ret="cast("+FieldName+" as INTEGER)";
			break;
		case Mysql:
			ret="cast("+FieldName+" as signed)";
			break;
		case GBase8s:
			ret="cast("+FieldName+" as int)";
			break;
		}
		return ret;
  }
  public static String fn_Instr(String str,String substr) {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="CHARINDEX("+substr+","+str+")";
			break;
		case Oracle:
			ret="instr("+str+","+substr+")";
			break;
		case Mysql:
			ret="instr("+str+","+substr+")";
			break;
		case GBase8s:
			ret="instr("+str+","+substr+")";
			break;
		}
		return ret;
  }
  /**
   * 整数字段
   * @return
   */
  public static String fld_Int() {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="int";
			break;
		case Oracle:
			ret="NUMBER(11,0)";
			break;
		case Mysql:
			ret="int(11)";
			break;
		case GBase8s:
			ret="integer";
			break;
		}
		return ret;
  }
  /**
   * 数字型
   * @param Total 总长度
   * @param dig 小数位
   * @return
   */
  public static String fld_Numeric(int Total,int dig) {
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="numeric("+Total+","+dig+")";
			break;
		case Oracle:
			ret="NUMBER("+Total+","+dig+")";
			break;
		case Mysql:
			ret="numeric("+Total+","+dig+")";
			break;
		case GBase8s:
			ret="numeric("+Total+","+dig+")";
			break;
		}
		return ret;
  }
  /**
   * 实现concat字符串拼接功能
   * @param value1
   * @param value2
   * @return
   */
  public static String AddString(String value1,String value2)
	{
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret=value1+"+"+value2;
			break;
		case Oracle:
			ret=value1+"||"+value2;
			break;
		case Mysql:
			ret="concat("+value1+","+value2+")";
			break;
		case GBase8s:
			ret=value1+"||"+value2;
			break;
		}
		return ret;
	}
  /**
   * 数据库中取系统时间
   * @return
   */
  public static String get_sysdate()
	{
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret="getdate()";
			break;
		case Oracle:
			ret="sysdate";
			break;
		case Mysql:
			ret="NOW()";
			break;
		case GBase8s:
			ret="current";
			break;
		}
		return ret;
	}
  public static String get_JDBC()
  {
	  String ret="";
	  switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
	  case Sqlserver:
		  ret="com.microsoft.sqlserver.jdbc.SQLServerDriver";
		  break;
	  case Oracle:
		  ret="oracle.jdbc.driver.OracleDriver";
		  break;
	  case Mysql:
		  ret="com.mysql.jdbc.Driver";
		  break;
	  case GBase8s:
		  ret="com.gbasedbt.jdbc.Driver";
		  break;
	  }
	  return ret;
  }
  public static String SetFieldValue(String fix,String FieldName){
	  String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
		case Sqlserver:
			ret=((fix.equals("")?"":".")+FieldName);
			break;
		case Oracle:
			ret=((fix.equals("")?"":".")+"\""+FieldName+"\"");
			break;
		case Mysql:
			ret=((fix.equals("")?"":".")+FieldName);
			break;
		case GBase8s:
			ret=((fix.equals("")?"":".")+"\""+FieldName+"\"");
			break;
		}
		return ret;
  }
  /**
   * 判断是否是Oracle
 * @return
 */
public static Boolean is_oracle()
  {
	  return dbType==DataBaseType.Oracle;
  }
  public static String GetValue(Object v){
	  String ret="";
	  try
	  {
		  if (v!=null) {
			  if (!v.toString().toLowerCase().equals("null")) {
				  ret=v.toString();
			  }
		  }
	  }catch(Exception e){
	  }
	  return ret;
  }

  /**
	 * 字符时间类型变时间类型
	 * @return
	 */
	public static String fn_todate(String date) {
		String ret="";
		switch(dbType) {//0 sqlserver 1 oracle 2 mysql 3 gbase
			case Sqlserver:
				ret="";
				break;
			case Oracle:
				ret="";
				break;
			case Mysql:
				ret=date;
				break;
			case GBase8s:
				ret="to_date("+date+")";
				break;
		}
		return ret;
	}

	/**
	 * gbase字段类型对应
	 */
	public static String fn_dataTyped(Object GbasedataNum){
		int message=-1;
		try {
			message=Integer.parseInt(String.valueOf(GbasedataNum));
		}catch (Exception e){
			return "传入类型错误";
		}
		String ret="";
		switch (message){
			case 0:
				ret="char";
				break;
			case 2:
				ret="int";
				break;
			case 3:
				ret="float";
				break;
			case 7:
				ret="date";
				break;
			case 10:
				ret="datetime";
				break;
			case 12:
				ret = "text";
				break;
			case 13:
				ret = "varchar";
				break;
			case 17:
				ret = "int";
				break;
			case 45:
				ret = "boolean";
				break;
			case 52:
				ret = "bigint";
				break;
			case 258:
				ret = "int";
				break;
			case 269:
				ret = "varchar";
				break;
			case 5:
				ret = "decimal";
				break;
			default:
				ret="";
				break;
		}
		return ret;
	}


}
