/**
*
*/
package test;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author huangqin
*
*/
public class QuestString {
private int curPage;//當(dāng)前頁(yè)數(shù)
private int maxPage;//最大頁(yè)數(shù)
private int maxRowCount;//總記錄數(shù)
private int pageSize=2;//每頁(yè)顯示的記錄數(shù)
private DBResult db;//記錄集對(duì)象
private String httpfile;//當(dāng)前地址欄的文件，即具體的jsp文件
private String cif;//選擇的查詢字段
private String ccif;//選擇的查詢運(yùn)算符
private String qvalue;//查詢關(guān)鍵字
private String countSql=null;//用來(lái)存儲(chǔ)select count（*）。。。。語(yǔ)句
private String topSql=null;//用來(lái)存儲(chǔ)select top2.。。。語(yǔ)句
private String nowPage=null;//初始化當(dāng)前頁(yè)curPage變量，即獲得當(dāng)前頁(yè)的具體頁(yè)號(hào)
private String str_parameter;//在做翻頁(yè)時(shí)，傳遞除pages外的其他參數(shù)
private String andor;//查詢的與/或條件
private String sdate;//查詢其實(shí)時(shí)間
private String edate;//查詢結(jié)束時(shí)間
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);//獲取記錄總數(shù)，即所要查詢記錄的總行
}
//余數(shù)為0則總頁(yè)數(shù)=兩數(shù)整除的結(jié)果，若不為0則總頁(yè)數(shù)=兩數(shù)整除結(jié)果+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;//前一頁(yè)
int next=this.curPage+1;//后一頁(yè)
str=str+"<font style='font-size:10pt'>總計(jì)<font color='red'>"+this.getMaxRowCount()+
"</font>條記錄，"+"“共<font color='red'>"+this.getMaxPage()+"</font>頁(yè)”";
str=str+" ”<font color='red'>"+this.pageSize+"</font>條/頁(yè)”當(dāng)前頁(yè)<font color='red'>"+
this.getMaxPage()+"</font>頁(yè) ";
if(this.curPage>1)
str=str+"<A href="+this.httpfile+"?pages=1"+str_parameter+">首頁(yè)</A>";
else
str=str+"首頁(yè)";
if(this.curPage>1)
str=str+"<A href="+this.httpfile+"?pages="+prev+str_parameter+">上一頁(yè)</A>";
else
str=str+"上一頁(yè)";
if(this.curPage<this.maxPage)
str=str+"<A href="+this.httpfile+"?pages="+next+str_parameter+">下一頁(yè)</A>";
else
str=str+"下一頁(yè)";
if(this.maxPage>1&&this.curPage!=this.maxPage)
str=str+"<A href="+this.httpfile+"?pages="+this.maxPage+str_parameter+">尾頁(yè)</A>";
else
str=str+"尾頁(yè)</font>";
//在頁(yè)面跳轉(zhuǎn)間設(shè)置隱藏表單，來(lái)保存不同的請(qǐng)求
str=str+"轉(zhuǎn)到<input type='text' name='pages' size=2>頁(yè)"+
"<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;
}
//根據(jù)不同條件獲取不同查詢前N條的SQL語(yǔ)句
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;
}
//根據(jù)不同條件獲取不同的計(jì)算記錄總數(shù)的SQL語(yǔ)句
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;
}
//根據(jù)不同條件和不同的起始日期和結(jié)束日期獲得不同的計(jì)算記錄總數(shù)的SQL語(yǔ)句
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;
}
//根據(jù)不同條件和不同的起始日期和結(jié)束日期獲得不同的查詢，前N條的SQL語(yǔ)句
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;
}
//子查詢中得到從起始日期到結(jié)束日期這段時(shí)間所有不重復(fù)的spid（商品id），并返回不重復(fù)的spid的總數(shù)
//其中spid是一個(gè)數(shù)據(jù)庫(kù)中一張表中的一個(gè)屬性（一列），元組（一行）
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;
}
}