package sei;

import java.io.*;
import org.dom4j.*;
import org.dom4j.io.*;

import java.sql.*;

//import javax.naming.*;
//import javax.sql.DataSource;
public  class SqlBean {

    public Connection con = null;

    static String DatabaseDriver;
    static String CnnStr;
	static String username;
	static String password;
   
    private int curPage=1;//當前頁
    private int pageSize=25;//每頁顯示的記錄數
    private long rowsCount=0;//記錄行數
    private int pageCount=1;//頁數
    private String SQL="";//查詢條件
    
    
    public SqlBean() {/////構造函數
       if ((username==null)|(password==null)){
    	   String[] p=readLoginParam();
    	   DatabaseDriver=p[0];
    	   CnnStr=p[1];
    	   username=p[2];
    	   password=p[3];
     	   p=null;
       }
       con=Connect(con);
    }
    
    public Connection Connect(Connection con)
    {
        try {
            if (con==null)
            {
                Class.forName(DatabaseDriver);
                con = DriverManager.getConnection(CnnStr,username,password);                
            }else if (con.isClosed()){
                Class.forName(DatabaseDriver);
                con = DriverManager.getConnection(CnnStr,username, password);
            }
        } catch (Exception e) {}
        return con;
     }
	public String[] readLoginParam()// throws Exception
	{
		String Pa[]={"","","","",""};
		try { 
			File f = new File(getClass().getResource("runConfig.xml").getFile()); 
			SAXReader reader = new SAXReader(); 
			Document doc = reader.read(f); 
			Element root = doc.getRootElement();
			Pa[0]=root.elementText("DatabaseDriver");
			Pa[1]=root.elementText("CnnStr");			
			Pa[2]=root.elementText("username");
			Pa[3]=root.elementText("password");	
		} catch (Exception e) {}
		return Pa;
	}
    //獲取查詢記錄總數
	public int executeCount(String sql) {
        int num = -1;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) num=rs.getInt(1);
            rs.close();
            stmt.close();
        } catch (SQLException ex) {}
        return num;
    }
 
    public ResultSet executeQuery(String sql) {
        ResultSet rs = null;
        try {
            Statement stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException ex) {}
        return rs;
    }
 
  
    //關閉數據庫連接
    public void CloseDataBase() {
        try {
            con.close();
        } catch (Exception end) {}
    }
 
    public String getSQL(String SQL,String AndOr,String Pr)
    {
    	if (SQL==null)SQL="";
    	if ((SQL.trim().equals(""))&(!Pr.trim().equals(""))) return SQL=Pr;
    	if ((!SQL.trim().equals(""))&(!Pr.trim().equals(""))) return SQL=SQL+" "+AndOr+" "+Pr;
    	//SQL=getSQLLike(SQL);
    	return SQL;
    }
    public String getAddSQL(String AndOr,String SQL)
    {
   		if (!SQL.trim().equals("")){SQL=" "+AndOr+" "+SQL;}
    	return SQL;
    }
    
    //處理Like查詢中的％號問題,說明:將Like中~!字符的還原未％
    public String getSQLLike(String SQL)
    {
   		if (!SQL.equals("")){SQL=SQL.replace("Like '~!","Like '%");SQL=SQL.replace("~!')","%')");}
    	return SQL;
    }    

    public static String getLimitLenStr(String s,int LimitStrlen){
        s=s.replace("'","");
        char[] cc=s.toCharArray();
        int intLen=0;
        int i;
        //if("中國".length()==4){return s.substring(Maxlen/2);}
        for(i=0;i<cc.length;i++){
            if((int)cc[i]>255){intLen=intLen+2;}else{intLen++;}
            if (intLen>=LimitStrlen){break;}
        }        
        if (intLen==LimitStrlen)i++;
        return s.substring(0,i);
    }
    
    public static String getLimitChinese(String s,int MaxLen){
        if(s == null)return "";
        try{
           // String convert = new String(s.getBytes("ISO8859_1"), "GB2312");
           // convert=getLimitLenStr(convert,MaxLen);
            String convert=getLimitLenStr(s,MaxLen);
            return convert;
        }catch(Exception e){}
        return "";
    }
   
    
    /**
     * 獲取分頁信息。
     * SELECT * FROM T_BOOK,T_STOCK WHERE T_BOOK.BOOK_ID=STOCK.BOOK_ID ORDER BY T_BOOK.BOOK ASC
     * @param SelectFileds 需要顯示的字段 如：*
     * @param IdFiled 關鍵字段，用于排序查找 可以不要
     * @param Table 表名 如：T_BOOK,T_STOCK
     * @param sql 條件語句 如：T_BOOK.BOOK_ID=STOCK.BOOK_ID
     * @param OrderOrGroup 排序 如：ORDER BY T_BOOK.BOOK ASC
     * @param CurPage 要顯示第幾頁
     * @return 分頁結果
     */   
    public ResultSet getData(String SelectFileds,String IdFiled,String Table,String sql,String OrderOrGroup,String CurPage,String SQL_ALL) throws Exception 
    {
    	sql=sql.trim();
        if (CurPage==null){
        	CurPage="1";
        	this.SQL=SelectFileds+" _!!_ "+IdFiled+" _!!_ "+Table+" _!!_ "+sql+" _!!_ "+OrderOrGroup;
        }else{
        	String SS[]=SQL_ALL.split("_!!_");
        	SelectFileds=SS[0];
        	IdFiled=SS[1];
        	Table=SS[2];
        	sql=SS[3];
        	OrderOrGroup=SS[4];
        	this.SQL=SQL_ALL;
        }
        this.curPage=Integer.parseInt(CurPage);
        
        if (SelectFileds.trim().equals(""))SelectFileds="*";
        sql=getSQLLike(sql);
        if(IdFiled.trim().equals(""))IdFiled=SelectFileds.replace(",","_--_") ;  

        String IsWhere="";        
        if(sql.trim().equals("")){IsWhere="Where";}else{sql="Where "+sql;IsWhere="And";}        
        
        ResultSet rs=null;
        try {
            rs = executeQuery("select count(*) as A FROM "+Table+" "+sql);
            if (rs.next()) rowsCount=rs.getInt("A");	 
            
            pageCount = (int)Math.ceil((rowsCount + pageSize-1) / pageSize);
            rs=executeQuery("SELECT TOP "+pageSize+" "+SelectFileds+" FROM "+Table+" "+sql+" "+IsWhere+" "+IdFiled+" Not In (Select Top "+((curPage-1)*pageSize)+" "+IdFiled+" FROM "+Table+" "+sql+" "+OrderOrGroup+") "+OrderOrGroup);            
       } catch (Exception e){this.SQL="SELECT TOP "+pageSize+" "+SelectFileds+" FROM "+Table+" "+sql+" "+IsWhere+" "+IdFiled+" Not In (Select Top "+((curPage-1)*pageSize)+" "+IdFiled+" FROM "+Table+" "+sql+" "+OrderOrGroup+") "+OrderOrGroup;}
       //this.SQL="SELECT TOP "+pageSize+" "+SelectFileds+" FROM "+Table+" "+sql+" "+IsWhere+" "+IdFiled+" Not In (Select Top "+((curPage-1)*pageSize)+" "+IdFiled+" FROM "+Table+" "+sql+" "+OrderOrGroup+") "+OrderOrGroup;
       return rs;
    }

    
    /**
     * 獲取工具條
     * @return String
     */
    public String getToolBar(String fileName){
        this.CloseDataBase();
        
        String str="<table border=0 align=center id='TB_Buttom'><input type=hidden name=PAGE_SQL value =\""+this.SQL+"\"><input type=hidden name=cur_page value ="+curPage+">";
        str=str+"<tr align=center valign=middle>";
        str=str+"<td>【每頁"+pageSize+"條記錄】</td>";
        str=str+"<td>【共"+pageCount+"頁/"+rowsCount+"條記錄】</td>";
        
        if(curPage<=1)
        {
            str=str+"<td>【首頁】</a></td>";
            str=str+"<td>【上一頁】</td>";            
        }else{
            str=str+"<td><a href=javascript:document.frm.cur_page.value=1;document.frm.submit();>【首頁】</a></td>";
            str=str+"<td><a href=javascript:document.frm.cur_page.value="+(curPage-1)+";document.frm.submit();>【上一頁】</a></td>";
        }
        if (curPage<pageCount)
        {
            str=str+"<td><a href=javascript:document.frm.cur_page.value="+(curPage+1)+";document.frm.submit();>【下一頁】</a></td>";
            str=str+"<td><a href=javascript:document.frm.cur_page.value="+pageCount+";document.frm.submit();>【尾頁】</a></td>";            
        }else{
            str=str+"<td>【下一頁】</td>";
            str=str+"<td>【尾頁】</td>";                
        }
        //str=str+"<td>每頁<input type='text' name='pageSize' STYLE='{width:40}' value="+pageSize+"></td>";
         str+="<td>轉到<select name='page' onChange='javascript:document.frm.cur_page.value=document.all.page.value;document.frm.submit();'>";
        for(int i=1;i<=pageCount;i++)
        {
            if(i==curPage)
                str+="<option value='"+i+"' selected>第"+i+"頁</option>";
            else
                str+="<option value='"+i+"'>第"+i+"頁</option>";
        }
        str+="</select></td>";
        str+="</tr></table>";
        return str;
    }
}