package QCM.COMMUNAL.QCM02;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;


public class SqlJoint {

	public static String resultSetToJson(ResultSet rs) throws SQLException,JSONException  
	{  
	   // json����  
	   JSONArray array = new JSONArray();  
	    
	   // ��ȡ����  
	   ResultSetMetaData metaData = rs.getMetaData();  
	   int columnCount = metaData.getColumnCount();  
	    
	   // ����ResultSet�е�ÿ�����  
	    while (rs.next()) {  
	        JSONObject jsonObj = new JSONObject();  
	         
	        // ����ÿһ��  
	        for (int i = 1; i <= columnCount; i++) {  
	            String columnName =metaData.getColumnLabel(i);  
	            String value = rs.getString(columnName);  
	            jsonObj.put(columnName, value);  
	        }   
	        array.add(jsonObj);
	    }  
	    
	   return array.toString();  
	}
	
	/** 
     *  ����ʵ����֮�����ͬ���Ը�ֵ
     * @param source �����Ƶ�ʵ������� 
     * @param to ��������ʵ�������   
     * @throws Exception 
     */  
    public static void CopyEntity(Object source, Object to) throws Exception {    
        // ��ȡ����    

    	BeanInfo sourceBean = Introspector.getBeanInfo(source.getClass(),java.lang.Object.class);    
        PropertyDescriptor[] sourceProperty = sourceBean.getPropertyDescriptors();    
    
        BeanInfo destBean = Introspector.getBeanInfo(to.getClass(),java.lang.Object.class);    
        PropertyDescriptor[] destProperty = destBean.getPropertyDescriptors();    
        String sourceName="",destName="";
        try {    
            for (int i = 0; i < sourceProperty.length; i++) {    
                for (int j = 0; j < destProperty.length; j++) {    
                	sourceName=sourceProperty[i].getName().replaceAll("_", "").toLowerCase();
                	destName=destProperty[j].getName().replaceAll("_", "").toLowerCase();
                    if (sourceName.equals(destName)) {    
                    	System.out.print(sourceName+"=="+destName);
                        // ����source��getter������dest��setter����    
                        destProperty[j].getWriteMethod().invoke(to,sourceProperty[i].getReadMethod().invoke(source));    
                        break;    
                    }    
                }    
            }    
        } catch (Exception e) {    
            throw new Exception("����"+sourceName+"����ʧ��");    
        }    
    }
	
	/**
	   *ResultSetת��ΪJSON����
	   * 
	   * @param ResultSet
	   * @return JSONArray
	   */
	     public static JSONArray resultSetToJsonArry(ResultSet rs) throws SQLException, JSONException {
	         JSONArray array = new JSONArray();
	         ResultSetMetaData metaData = rs.getMetaData();
	         int columnCount = metaData.getColumnCount();
	         while (rs.next()) {
	             JSONObject jsonObj = new JSONObject();
	             for (int i = 1; i <= columnCount; i++) {
	                 String columnName = metaData.getColumnLabel(i);
	                 String value = rs.getString(columnName);
	                 jsonObj.put(columnName, value);
	             }
	             array.add(jsonObj);
	         }
	         return array;
	     }
	     public static boolean IsNullOrSpace(String value){
	 		if(value==null){
	 			return true;
	 		}else{
	 			return value.trim().equals("");
	 		}
	 	}
	     /**
		 	 * ����Ƿ��ϱ�׼
		 	 * @param sign ���
		 	 * @param signValue ��׼ֵ
		 	 * @param value �Ա�ֵ
		 	 * @return
		 	 */
		 	public static boolean checkSignAndValue1(String sign,Double signValue,String value){
		 		if(sign==null){
		 			return true;
		 		}else if(value==null||value==""){
		 			return false;
		 		}
		 		/*switch(sign){
		 			case ">":
		 				return signValue.doubleValue()<Double.parseDouble(value);
		 			case ">=":
		 				return signValue.doubleValue()<=Double.parseDouble(value);
		 			case "<":
		 				return signValue.doubleValue()>Double.parseDouble(value);
		 			case "<=":
		 				return signValue.doubleValue()>=Double.parseDouble(value);
		 			case "=":
		 				return signValue.doubleValue()==Double.parseDouble(value);
		 			default:
		 				return true;
		 		}*/
		 		if(sign.equals(">"))
		 			return signValue.doubleValue()<Double.parseDouble(value);
		 		else if(sign.equals(">="))
	 				return signValue.doubleValue()<=Double.parseDouble(value);
		 		else if(sign.equals("<"))
	 				return signValue.doubleValue()>Double.parseDouble(value);
	 			else if(sign.equals("<="))
	 				return signValue.doubleValue()>=Double.parseDouble(value);
	 			else if(sign.equals("="))
	 				return signValue.doubleValue()==Double.parseDouble(value);
	 			else 
	 				return true;
		 	}
	     /**
	 	 * ����Ƿ��ϱ�׼
	 	 * @param sign ���
	 	 * @param signValue ��׼ֵ
	 	 * @param value �Ա�ֵ
	 	 * @return
	 	 */
	 	public static boolean checkSignAndValue(String sign,Double signValue,String value){
	 		if(value==null||value==""){
	 			return true;
	 		}
	 		if(sign==null){
	 			return true;
	 		}
	 		/*switch(sign){
	 			case ">":
	 				return signValue.doubleValue()<Double.parseDouble(value);
	 			case ">=":
	 				return signValue.doubleValue()<=Double.parseDouble(value);
	 			case "<":
	 				return signValue.doubleValue()>Double.parseDouble(value);
	 			case "<=":
	 				return signValue.doubleValue()>=Double.parseDouble(value);
	 			case "=":
	 				return signValue.doubleValue()==Double.parseDouble(value);
	 			default:
	 				return true;
	 		}*/
	 		if(sign.equals(">"))
	 			return signValue.doubleValue()<Double.parseDouble(value);
	 		else if(sign.equals(">="))
 				return signValue.doubleValue()<=Double.parseDouble(value);
	 		else if(sign.equals("<"))
 				return signValue.doubleValue()>Double.parseDouble(value);
 			else if(sign.equals("<="))
 				return signValue.doubleValue()>=Double.parseDouble(value);
 			else if(sign.equals("="))
 				return signValue.doubleValue()==Double.parseDouble(value);
 			else 
 				return true;
	 	}
	 	
	 	/**
		 * ��������
		 * @param filed  ��ݿ��ֶ���
		 * @param start ��ʼ����
		 * @param end ��������
		 * @return
		 */
		public static String CreateSqlConditionDate(String filed,Date start,Date end){
		    Calendar  calendar=new   GregorianCalendar(); 
		    calendar.setTime(end); 
		    calendar.add(Calendar.DATE,1);
		    end=calendar.getTime();
			String condition=filed+" BeTween to_Date('" +SqlJoint.GetDateFormatString(start, Enum_DateType.Date_long)+ "','yyyy-MM-dd hh24:mi:ss') "+
					" And to_Date('" + SqlJoint.GetDateFormatString(end, Enum_DateType.Date_long) + "','yyyy-MM-dd hh24:mi:ss')";
			return condition;
		}
		
		/**
		 * ����תString����
		 * @param date ����
		 * @param dateType ��ʽ������
		 * @return
		 */
		public static String GetDateFormatString(Date date,Enum_DateType dateType){
			SimpleDateFormat simpleFormat=GetDateFormat(dateType.toString());
			return simpleFormat.format(date);
		}
		
		/**
		 * ���ڸ�ʽ����
		 * @author ZQ
		 *
		 */
		public enum Enum_DateType{
			/**
			 * yyyyMMdd
			 */
			Date_Customer,
			
			/**
			 * ������ yyyy-MM-dd HH:mm:ss
			 */
			Date_long,
			/**
			 * ������ yyyy-MM-dd
			 */
			Date_Short,
			/**
			 * �ꡢ�����  yyyy-MM
			 */
			Date_YearJoinMonth,
			/**
			 * �¡������ MM-dd
			 */
			Date_MonthJoinDay
			
		}
		
		/**
		 * �������ڸ�ʽ����
		 * @param dateType ��������
		 * @return
		 */
		public static SimpleDateFormat GetDateFormat(String dateType){
			SimpleDateFormat simpleFormat=new SimpleDateFormat();
			Enum_DateType _dateType=Enum_DateType.valueOf(dateType);
			switch (_dateType) {
			case Date_Customer:
				simpleFormat.applyPattern("yyyyMMdd");
				break;
			case Date_long:
				simpleFormat.applyPattern("yyyy-MM-dd HH:mm:ss");
				break;
			case Date_Short:
				simpleFormat.applyPattern("yyyy-MM-dd");
				break;
			case Date_YearJoinMonth:
				simpleFormat.applyPattern("yyyy-MM");
				break;
			case Date_MonthJoinDay:
				simpleFormat.applyPattern("MM-dd");
				break;
			default:
				simpleFormat.applyPattern("yyyy-MM-dd HH:mm:ss");
				break;
			}
			return simpleFormat;
			
		}
		
		/**
		 * ִ�б��ʽ
		 * @param expression ���ʽ
		 * @return ������
		 * @throws ScriptException
		 */
		public static double GetFormulaResult(String expression) throws ScriptException{
				ScriptEngineManager _mgr=new ScriptEngineManager();
				ScriptEngine _enginer=_mgr.getEngineByName("javascript");
				Object result=_enginer.eval(expression);
				double value=Double.parseDouble(result.toString());
				return value;
		}
		
		/**
		 * String ת double
		 * @param value
		 * @return
		 */
		public static Double GetDouble(String value){
			if(value==null){
				return null;
			}else{
				return Double.parseDouble(value);
			}
		}
		
		/**
		 *  Double ת String
		 * @param value
		 * @return
		 */
		public static String GetString(Double value){
			if(value==null){
				return null;
			}else{
				return value.toString();
			}
		}
		
		/**
		 * ���ʹ��� ��sqlע��
		 * @param value
		 * @return
		 */
		public static String TrimSqlDangerMark(String value){
			if(IsNullOrSpace(value)){
				return value;
			}else{
				return value.trim().replaceAll("'", "");
			}
		}
		
		/**
		 * �����ѯcondition
		 * @param listValue ԭ�ַ�ֵ��
		 * @param field ��ݿ��ֶ���
		 * @return
		 */
		public static String CreateSqlCondition(List<String> listValue,String field){
			if(listValue==null||listValue.size()<1){
				return null;
			}else{
				StringBuilder condition=new StringBuilder(field+" in(");
				for (String value : listValue) {
					condition.append("'"+value+"',");
				}
				return condition.substring(0, condition.length()-1)+")";
			}
		}
		
		/**
		 * �����ѯcondition
		 * @param values ԭ�ַ�ֵ����
		 * @param field ��ݿ��ֶ���
		 * @param flag �ָ��ַ�
		 * @return
		 */
		public static String CreateSqlCondition(String values,String field,String flag){
			
			if(IsNullOrSpace(values)){
				return null;
			}else{
				String[] valueArray=values.split(flag);
				StringBuilder condition=new StringBuilder(field+" in(");
				for (String value : valueArray) {
					condition.append("'"+value+"',");
				}
				return condition.substring(0, condition.length()-1)+")";
			}
		}
		
		/**
		 * �����ѯcondition
		 * @param valueArray ԭ�ַ�ֵ����
		 * @param field ��ݿ��ֶ���
		 * @return
		 */
		public static String CreateSqlCondition(String[] valueArray,String field){
			if(valueArray==null||valueArray.length<1){
				return null;
			}else{
				StringBuilder condition=new StringBuilder(field+" in(");
				for (String value : valueArray) {
					condition.append("'"+value+"',");
				}
				return condition.substring(0, condition.length()-1)+")";
			}
		}
}