/**
*
*/
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;
}
}