/**
*
*/
package test;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author huangqin
*
*/
public class QuestString {
private int curPage;//當前頁數
private int maxPage;//最大頁數
private int maxRowCount;//總記錄數
private int pageSize=2;//每頁顯示的記錄數
private DBResult db;//記錄集對象
private String httpfile;//當前地址欄的文件，即具體的jsp文件
private String cif;//選擇的查詢字段
private String ccif;//選擇的查詢運算符
private String qvalue;//查詢關鍵字
private String countSql=null;//用來存儲select count（*）。。。。語句
private String topSql=null;//用來存儲select top2.。。。語句
private String nowPage=null;//初始化當前頁curPage變量，即獲得當前頁的具體頁號
private String str_parameter;//在做翻頁時，傳遞除pages外的其他參數
private String andor;//查詢的與/或條件
private String sdate;//查詢其實時間
private String edate;//查詢結束時間
private String paixu;//排序方法
private String orderby;//排序條件
public QuestString() throws Exception{
db=new DBResult();
}
public void setCurPage(int curPage){
this.curPage=curPage;
}
public void setQuerySql(String httpfile,String pages,String strCount){
this.httpfile=httpfile;
this.nowPage=pages;
this.countSql=strCount;
try{
querySql(countSql);
}catch(Exception e){
e.printStackTrace();
}
}
public void querySql(String countSql)throws SQLException {
// TODO Auto-generated method stub
if(this.nowPage==null){
this.curPage=1;
}else{
this.curPage=Integer.parseInt(this.nowPage);
if(this.curPage<1){
this.curPage=1;
}
}
ResultSet rsCount=db.getResult(countSql);
if(rsCount.next()){
this.maxRowCount=rsCount.getInt(1);//獲取記錄總數，即所要查詢記錄的總行
}
//余數為0則總頁數=兩數整除的結果，若不為0則總頁數=兩數整除結果+1
this.maxPage=(this.maxRowCount%this.pageSize==0)?(this.maxRowCount/this.pageSize):
(this.maxRowCount/this.pageSize+1);
if(this.curPage>this.maxPage){
this.curPage=this.maxPage;
}
rsCount.close();
}
public String pageFooter()
{
String str="<form action="+this.httpfile+"name=formBean method=post>";
int prev=this.curPage-1;//前一頁
int next=this.curPage+1;//后一頁
str=str+"<font style='font-size:10pt'>總計<font color='red'>"+this.getMaxRowCount()+
"</font>條記錄，"+"“共<font color='red'>"+this.getMaxPage()+"</font>頁”";
str=str+" ”<font color='red'>"+this.pageSize+"</font>條/頁”當前頁<font color='red'>"+
this.getMaxPage()+"</font>頁 ";
if(this.curPage>1)
str=str+"<A href="+this.httpfile+"?pages=1"+str_parameter+">首頁</A>";
else
str=str+"首頁";
if(this.curPage>1)
str=str+"<A href="+this.httpfile+"?pages="+prev+str_parameter+">上一頁</A>";
else
str=str+"上一頁";
if(this.curPage<this.maxPage)
str=str+"<A href="+this.httpfile+"?pages="+next+str_parameter+">下一頁</A>";
else
str=str+"下一頁";
if(this.maxPage>1&&this.curPage!=this.maxPage)
str=str+"<A href="+this.httpfile+"?pages="+this.maxPage+str_parameter+">尾頁</A>";
else
str=str+"尾頁</font>";
//在頁面跳轉間設置隱藏表單，來保存不同的請求
str=str+"轉到<input type='text' name='pages' size=2>頁"+
"<input type='hidden' name='ccif' value='"+this.ccif+"'>" +
"input type='hidden' name='cif' value='"+this.cif+
"'><input type='hidden' name='qvalue' value='"+this.qvalue+
"'><input type='hidden' name=andor' value='"+this.andor+
"'><input type='hidden name='sdate' value='"+this.sdate+
"'><input type='hidden name='edate' value='"+this.edate+
"'><input type='hidden' name='orderby' value='"+this.orderby+
"'><input type='hidden' name='paixu' value='"+this.paixu+
"'><input type='submit' name='submit' value='go'></form>";
return str;
}
private int getMaxPage() {
// TODO Auto-generated method stub
return maxPage;
}
private int getMaxRowCount() {
// TODO Auto-generated method stub
return maxRowCount;
}
//根據不同條件獲取不同查詢前N條的SQL語句
public String getString(String table){
if(ccif.equals("="))
{
String strSql="select top"+this.pageSize*this.curPage+"*from"+table+"where"+
""+cif+"="+"'"+qvalue+"'";
return strSql;
}
else if(ccif.equals("LIKE"))
{
String strSql="select top"+this.pageSize*this.curPage+"*from"+table+
"where"+""+cif+""+"like"+""+"'%"+qvalue+"%'";
return strSql;
}
else if(ccif.equals("ALL")){
String strSql="select top"+this.pageSize*this.curPage+"*from"+table;
return strSql;
}
else if(ccif.equals("<"))
{
String strSql="select top"+this.pageSize*this.curPage+"*from"+table+
"where"+cif+"<'"+qvalue+"'";
return strSql;
}
return null;
}
//根據不同條件獲取不同的計算記錄總數的SQL語句
public String getCount(String table){
if(ccif.equals("=")){
String strSql="select count(*) from"+table+"where"+""+cif+"="+"'"+qvalue+"'";
return strSql;
}
else if(ccif.equals("LIKE")){
String strSql="select count(*) from"+table+"where"+""+cif+""+"like"+""+"'%"+qvalue+"%'";
return strSql;
}
else if(ccif.equals("ALL")){
String strSql="select count(*) from"+table;
return strSql;
}
else if(ccif.equals("<")){
String strSql="select count(*) from "+table+"where"+cif+"<'"+qvalue+"'";
return strSql;
}
return null;
}
//根據不同條件和不同的起始日期和結束日期獲得不同的計算記錄總數的SQL語句
public String getDateCount(String table){
if(ccif.equals("=")){
String strSql="select count(*) from"+table+"where"+""+cif+"="+"'"+qvalue+"'"+
andor+"xsdate between'"+sdate+"'and'"+edate+"'";
return strSql;
}
else if(ccif.equals("LIKE")){
String strSql="select count(*) from"+table+"where"+""+cif+""+"like"+""+"'%"+qvalue+"%'"+
andor+"xsdate between'"+sdate+"'and'"+edate+"'";
return strSql;
}
else if(ccif.equals("ALL")){
String strSql="select count(*) from"+table;
return strSql;
}
return null;
}
//根據不同條件和不同的起始日期和結束日期獲得不同的查詢，前N條的SQL語句
public String getDateString(String table){
if(ccif.equals("="))
{
String strSql="select top"+this.pageSize*this.curPage+"*from"+table+"where"+
""+cif+"="+"'"+qvalue+"'"+andor+"xsdate between'"+sdate+"'and'"+edate+"'";
return strSql;
}
else if(ccif.equals("LIKE"))
{
String strSql="select top"+this.pageSize*this.curPage+"*from"+table+
"where"+""+cif+""+"like"+""+"'%"+qvalue+"%'"+andor+"xsdate between'"+sdate+
"'and'"+edate+"'";
return strSql;
}
else if(ccif.equals("ALL")){
String strSql="select top"+this.pageSize*this.curPage+"*from"+table;
return strSql;
}
return null;
}
//子查詢中得到從起始日期到結束日期這段時間所有不重復的spid（商品id），并返回不重復的spid的總數
//其中spid是一個數據庫中一張表中的一個屬性（一列），元組（一行）
public String getOrderCount(String table){
String strSql="select count(*) from (select spid from"+table+"where xsdate between'"+
sdate+"'and'"+edate+"'group by spid) as aa";
return strSql;
}
public String getOrderString(String table){
String strSql="select top"+this.pageSize*this.curPage+"* from tb_brand a inner join" +
"(select spid,sum(sl)as sl,sum(je)as je"+
"from "+table+"where xsdate between'"+sdate+"'and'"+edate+"'group by spid)"+
"as b"+"on a.id=b.spid order by"+orderby+""+paixu;
return strSql;
}
}