package huc.blog.op;

import huc.blog.util.DB;
import huc.blog.util.PageObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseOp {
	
	protected DB db = null;
	
	protected Connection conn = null;
	
	protected PreparedStatement pst = null;
	
	protected ResultSet rs = null;
	
	public BaseOp(){
		this.db = new DB();
	}
	
	/**
	 * 關閉打開的ResultSet,PreparedStatement,Connection
	 *
	 */
	public void close(){
		try {
			if(rs != null)
				rs.close();
			if(pst != null)
				pst.close();
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 得到分頁的sql語句
	 * @param page 		頁面對象
	 * @param keySQL	查詢的字段和數據庫	like(" * from table ")
	 * @param otherSQL	查詢條件
	 * @param orderBy	排序方式			like(" order by time desc")
	 * @return pageCount 當前分頁開始的記錄號
	 */
	public StringBuffer getSQL(PageObject page, String keySQL, String otherSQL, String orderBy){
		StringBuffer sql = new StringBuffer();
		
		//檢查Page對象的頁號是否合法,根據toatl和pageSize合理設置頁號
		if(page.getPageNo() < 1)
			page.setPageNo(1);
		int count = (page.getPageNo() - 1 ) * page.getPageSize();
		if(count >= page.getTotal()){
			if(page.getTotal() % page.getPageSize() == 0)
				page.setPageNo(page.getTotal()/page.getPageSize());
			else
				page.setPageNo((int)page.getTotal()/page.getPageSize() + 1);
			
			count = (page.getPageNo() - 1 ) * page.getPageSize();
		}

		if(orderBy == null || orderBy.trim().equals("")){
			orderBy = " order by id asc";
		}		
		
		StringBuffer tempOrderBy = new StringBuffer();
		if(orderBy.indexOf("desc") != -1){
			tempOrderBy.append(" ");
			tempOrderBy.append(orderBy.substring(0,orderBy.indexOf("desc")));
			tempOrderBy.append(" asc");
		}
		else if(orderBy.indexOf("asc") != -1){
			tempOrderBy.append(" ");
			tempOrderBy.append(orderBy.substring(0,orderBy.indexOf("asc")));
			tempOrderBy.append(" desc");
		}
		else{
			orderBy = (new StringBuffer(orderBy)).append(" asc").toString();
			tempOrderBy.append(" ");
			tempOrderBy.append(orderBy.substring(0,orderBy.indexOf("asc")));
			tempOrderBy.append(" desc");
		}
		sql.append("select top ");
		sql.append(page.getPageSize());
		sql.append(" page.* from (");
		sql.append(" select top ");
		sql.append(page.getTotal() - count);
		sql.append(keySQL);
		if(otherSQL != null && !otherSQL.trim().equals("")){
			sql.append(" where 1 = 1");
			sql.append(otherSQL);
		}
		sql.append(tempOrderBy);
		sql.append(") page ");
		sql.append(orderBy);
		
		return sql;
	}

}