public class DatabaseQuery {
private int numRecords;
public int getNumRecords() {
return numRecords;
}
public void setNumRecords(int numRecords) {
this.numRecords = numRecords;
}
public ArrayList<String[]> getITCModataIterator(String query,int offset,int noOfRecords){
ArrayList<String[]> dataList=new ArrayList<String[]>();
Connection con=null;
PreparedStatement pst=null;
ResultSet rst=null;
try{
con=DBConnection.getConnection();
if(con!=null){
query=query+" order by date limit "+ offset + ", "+noOfRecords;
System.out.println("DatabaseQuery.getITCModataIterator()"+query);
pst=con.prepareStatement(query);
rst=pst.executeQuery();
while(rst.next()){
String data[]=new String[14];
data[0]=rst.getString("mobile");
data[1]=rst.getString("keyword");
data[2]=rst.getString("msg_text");
data[3]=rst.getString("answer");
data[4]=rst.getString("name");
data[5]=rst.getString("age");
data[6]=rst.getString("city");
data[7]=rst.getString("option_status");
data[8]=rst.getString("entry_status");
data[9]=rst.getString("reason");
data[10]=rst.getString("response_message");
data[11]=rst.getString("date");
data[12]=rst.getString("operator");
data[13]=rst.getString("circle");
dataList.add(data);
}
rst.close();
rst = pst.executeQuery("SELECT FOUND_ROWS()");
if(rst.next()){
this.numRecords = rst.getInt(1);
System.out.println("DatabaseQuery.getITCModata()"+this.numRecords);
}
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConnection.closeResource(rst, pst, con);
}
return dataList;
}
}
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@page import="itcmo.*"%>
<%@page import="java.util.*"%>
<%@ include file="session.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>ITC data viewer</title>
<style>
.paginate {
font-family:Arial, Helvetica, sans-serif;
padding: 0px;
margin: 0px;
}
.paginate a {
padding:1px 2px 1px 2px;
margin:2px;
border:1px solid #999;
text-decoration:none;
color: #666;
}
.paginate a:hover, .paginate a:active {
border: 1px solid #999;
color: #000;
}
.paginate span.current {
margin: 1px;
padding: 1px 2px 1px 2px;
border: 1px solid #999;
font-weight: bold;
background-color: #999;
color: #FFF;
}
.paginate span.disabled {
padding:1px 2px 1px 2px;
margin:2px;
border:1px solid #eee;
color:#DDD;
}
li{
padding:1px;
margin-bottom:3px;
background-color:#FCC;
list-style:none;}
ul{margin:6px;
padding:0px;}
</style>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #1199ff;
color:#ffffff;
}
table.gridtable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>
</head>
<body>
<%
int page1 = 1;
int recordsPerPage = 5;
if(request.getParameter("page") != null){
page1 = Integer.parseInt(request.getParameter("page"));
}
String startdate = request.getParameter("startdate");
if(startdate==null){
startdate="";
}
//String enddate = request.getParameter("enddate");
//if(enddate==null){
//enddate="";
//}
String mobileno = request.getParameter("mobileno");
if(mobileno==null){
mobileno="";
}
String operator = request.getParameter("operator");
if(operator==null){
operator="0";
}
String circle = request.getParameter("circle");
if(circle==null){
circle="0";
}
String optionstatus = request.getParameter("option_status");
if(optionstatus==null){
optionstatus="0";
}
String entrystatus = request.getParameter("entry_status");
if(entrystatus==null){
entrystatus="0";
}
StringBuffer buffer=new StringBuffer("SELECT SQL_CALC_FOUND_ROWS mobile, keyword, msg_text, answer, name, age, city, option_status, entry_status, reason, response_message, date, operator, circle from itcmodata where 1=1 ");
if(!"".equals(startdate)){
buffer.append(" and date>='"+startdate+" 00:00:00' and date<='"+startdate+" 23:59:59'");
}
//if(!"".equals(enddate)){
// buffer.append(" and date<='"+enddate+"'");
//}
if(!"".equals(mobileno)){
buffer.append(" and mobile like '%"+mobileno+"%'");
}
if(!"0".equals(operator)){
buffer.append(" and operator='"+operator+"'");
}
if(!"0".equals(circle)){
buffer.append(" and circle='"+circle+"'");
}
if(!"0".equals(optionstatus)){
buffer.append(" and option_status='"+optionstatus+"'");
}
if(!"0".equals(entrystatus)){
buffer.append(" and reason='"+entrystatus+"'");
}
DatabaseQuery queryObj=new DatabaseQuery();
ArrayList<String[]> dataList=queryObj.getITCModataIterator(buffer.toString(),(page1-1)*recordsPerPage,recordsPerPage);
int noOfRecords = queryObj.getNumRecords();
int noOfPages = (int) Math.ceil(noOfRecords * 1.0 / recordsPerPage);
request.setAttribute("statusList", dataList);
request.setAttribute("noOfPages", noOfPages);
request.setAttribute("currentPage", page1);
%>
<table width="100%"><tr><td> <img src="logo.jpg"></img></td><td align="right"><a href="searchdata.jsp">Home</a> <a href="logout.jsp">Logout</a></td></tr>
<tr><td align="right" colspan="2"> </td></tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0"><tr><td> </td></tr></table>
<table id="results" class="gridtable" width="100%" cellspacing="0" cellpadding="0" border="1">
<tr><th>Mobile</th><th>Keyword</th><th>Message Text</th><th>Answer</th><th>Name</th><th>Age</th>
<th>City</th><th>Option Status</th><th>Entry Status</th><th>Reason</th><th>Response Message</th><th>Date</th><th>Operator</th><th>Circle</th></tr>
<c:forEach var="status" items="${statusList}">
<tr>
<td valign="top">${status[0]}</td>
<td valign="top">${status[1]}</td>
<td valign="top">${status[2]}</td>
<td valign="top">${status[3]}</td>
<td valign="top">${status[4]}</td>
<td valign="top">${status[5]}</td>
<td valign="top">${status[6]}</td>
<td valign="top">${status[7]}</td>
<td valign="top">${status[8]}</td>
<td valign="top">${status[9]}</td>
<td valign="top">${status[10]}</td>
<td valign="top">${status[11]}</td>
<td valign="top">${status[12]}</td>
<td valign="top">${status[13]}</td>
</tr>
</c:forEach>
</table>
<div class="paginate">
<table cellspacing="0" cellpadding="0" border="1" width="100%" class="gridtable">
<tr><td>
<c:if test="${currentPage != 1}">
<a href="dataiterator.jsp?page=${currentPage - 1}"> Previous</a>
</c:if>
<%
int stages=3;
int lastpage=(int)Math.ceil(noOfRecords * 1.0 / recordsPerPage);
if(lastpage< 7+(stages*2)){
for (int counter = 1; counter <= lastpage; counter++){
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
}else if(lastpage>5+(stages*2)){
if(page1<1+(stages*2)){
for (int counter = 1; counter < 4 + (stages * 2); counter++)
{
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
%>
<%="..."%>
<a href="dataiterator.jsp?page=<%=(lastpage-1)%>"> <%=(lastpage-1)%></a>
<a href="dataiterator.jsp?page=<%=(lastpage)%>"> <%=(lastpage)%></a>
<%
}else if(lastpage - (stages * 2) > page1 && page1 > (stages * 2)){
%>
<a href="dataiterator.jsp?page=1"> 1</a>
<a href="dataiterator.jsp?page=2"> 2</a>
<%="..."%>
<%
for (int counter = page1 - stages; counter <= page1 + stages; counter++)
{
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
%>
<%="..."%>
<a href="dataiterator.jsp?page=<%=(lastpage-1)%>"> <%=(lastpage-1)%></a>
<a href="dataiterator.jsp?page=<%=(lastpage)%>"> <%=(lastpage)%></a>
<%
}else{
%>
<a href="dataiterator.jsp?page=1"> 1</a>
<a href="dataiterator.jsp?page=2"> 2</a>
<%="..."%>
<%
for (int counter = lastpage - (2 + (stages * 2)); counter <= lastpage; counter++)
{
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
}
}
%>
<c:if test="${currentPage lt noOfPages}">
<a href="dataiterator.jsp?page=${currentPage + 1}"> Next</a>
</c:if>
</td>
</tr>
</table>
</div>
</body>
</html>
private int numRecords;
public int getNumRecords() {
return numRecords;
}
public void setNumRecords(int numRecords) {
this.numRecords = numRecords;
}
public ArrayList<String[]> getITCModataIterator(String query,int offset,int noOfRecords){
ArrayList<String[]> dataList=new ArrayList<String[]>();
Connection con=null;
PreparedStatement pst=null;
ResultSet rst=null;
try{
con=DBConnection.getConnection();
if(con!=null){
query=query+" order by date limit "+ offset + ", "+noOfRecords;
System.out.println("DatabaseQuery.getITCModataIterator()"+query);
pst=con.prepareStatement(query);
rst=pst.executeQuery();
while(rst.next()){
String data[]=new String[14];
data[0]=rst.getString("mobile");
data[1]=rst.getString("keyword");
data[2]=rst.getString("msg_text");
data[3]=rst.getString("answer");
data[4]=rst.getString("name");
data[5]=rst.getString("age");
data[6]=rst.getString("city");
data[7]=rst.getString("option_status");
data[8]=rst.getString("entry_status");
data[9]=rst.getString("reason");
data[10]=rst.getString("response_message");
data[11]=rst.getString("date");
data[12]=rst.getString("operator");
data[13]=rst.getString("circle");
dataList.add(data);
}
rst.close();
rst = pst.executeQuery("SELECT FOUND_ROWS()");
if(rst.next()){
this.numRecords = rst.getInt(1);
System.out.println("DatabaseQuery.getITCModata()"+this.numRecords);
}
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConnection.closeResource(rst, pst, con);
}
return dataList;
}
}
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@page import="itcmo.*"%>
<%@page import="java.util.*"%>
<%@ include file="session.jsp"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>ITC data viewer</title>
<style>
.paginate {
font-family:Arial, Helvetica, sans-serif;
padding: 0px;
margin: 0px;
}
.paginate a {
padding:1px 2px 1px 2px;
margin:2px;
border:1px solid #999;
text-decoration:none;
color: #666;
}
.paginate a:hover, .paginate a:active {
border: 1px solid #999;
color: #000;
}
.paginate span.current {
margin: 1px;
padding: 1px 2px 1px 2px;
border: 1px solid #999;
font-weight: bold;
background-color: #999;
color: #FFF;
}
.paginate span.disabled {
padding:1px 2px 1px 2px;
margin:2px;
border:1px solid #eee;
color:#DDD;
}
li{
padding:1px;
margin-bottom:3px;
background-color:#FCC;
list-style:none;}
ul{margin:6px;
padding:0px;}
</style>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #1199ff;
color:#ffffff;
}
table.gridtable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>
</head>
<body>
<%
int page1 = 1;
int recordsPerPage = 5;
if(request.getParameter("page") != null){
page1 = Integer.parseInt(request.getParameter("page"));
}
String startdate = request.getParameter("startdate");
if(startdate==null){
startdate="";
}
//String enddate = request.getParameter("enddate");
//if(enddate==null){
//enddate="";
//}
String mobileno = request.getParameter("mobileno");
if(mobileno==null){
mobileno="";
}
String operator = request.getParameter("operator");
if(operator==null){
operator="0";
}
String circle = request.getParameter("circle");
if(circle==null){
circle="0";
}
String optionstatus = request.getParameter("option_status");
if(optionstatus==null){
optionstatus="0";
}
String entrystatus = request.getParameter("entry_status");
if(entrystatus==null){
entrystatus="0";
}
StringBuffer buffer=new StringBuffer("SELECT SQL_CALC_FOUND_ROWS mobile, keyword, msg_text, answer, name, age, city, option_status, entry_status, reason, response_message, date, operator, circle from itcmodata where 1=1 ");
if(!"".equals(startdate)){
buffer.append(" and date>='"+startdate+" 00:00:00' and date<='"+startdate+" 23:59:59'");
}
//if(!"".equals(enddate)){
// buffer.append(" and date<='"+enddate+"'");
//}
if(!"".equals(mobileno)){
buffer.append(" and mobile like '%"+mobileno+"%'");
}
if(!"0".equals(operator)){
buffer.append(" and operator='"+operator+"'");
}
if(!"0".equals(circle)){
buffer.append(" and circle='"+circle+"'");
}
if(!"0".equals(optionstatus)){
buffer.append(" and option_status='"+optionstatus+"'");
}
if(!"0".equals(entrystatus)){
buffer.append(" and reason='"+entrystatus+"'");
}
DatabaseQuery queryObj=new DatabaseQuery();
ArrayList<String[]> dataList=queryObj.getITCModataIterator(buffer.toString(),(page1-1)*recordsPerPage,recordsPerPage);
int noOfRecords = queryObj.getNumRecords();
int noOfPages = (int) Math.ceil(noOfRecords * 1.0 / recordsPerPage);
request.setAttribute("statusList", dataList);
request.setAttribute("noOfPages", noOfPages);
request.setAttribute("currentPage", page1);
%>
<table width="100%"><tr><td> <img src="logo.jpg"></img></td><td align="right"><a href="searchdata.jsp">Home</a> <a href="logout.jsp">Logout</a></td></tr>
<tr><td align="right" colspan="2"> </td></tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0"><tr><td> </td></tr></table>
<table id="results" class="gridtable" width="100%" cellspacing="0" cellpadding="0" border="1">
<tr><th>Mobile</th><th>Keyword</th><th>Message Text</th><th>Answer</th><th>Name</th><th>Age</th>
<th>City</th><th>Option Status</th><th>Entry Status</th><th>Reason</th><th>Response Message</th><th>Date</th><th>Operator</th><th>Circle</th></tr>
<c:forEach var="status" items="${statusList}">
<tr>
<td valign="top">${status[0]}</td>
<td valign="top">${status[1]}</td>
<td valign="top">${status[2]}</td>
<td valign="top">${status[3]}</td>
<td valign="top">${status[4]}</td>
<td valign="top">${status[5]}</td>
<td valign="top">${status[6]}</td>
<td valign="top">${status[7]}</td>
<td valign="top">${status[8]}</td>
<td valign="top">${status[9]}</td>
<td valign="top">${status[10]}</td>
<td valign="top">${status[11]}</td>
<td valign="top">${status[12]}</td>
<td valign="top">${status[13]}</td>
</tr>
</c:forEach>
</table>
<div class="paginate">
<table cellspacing="0" cellpadding="0" border="1" width="100%" class="gridtable">
<tr><td>
<c:if test="${currentPage != 1}">
<a href="dataiterator.jsp?page=${currentPage - 1}"> Previous</a>
</c:if>
<%
int stages=3;
int lastpage=(int)Math.ceil(noOfRecords * 1.0 / recordsPerPage);
if(lastpage< 7+(stages*2)){
for (int counter = 1; counter <= lastpage; counter++){
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
}else if(lastpage>5+(stages*2)){
if(page1<1+(stages*2)){
for (int counter = 1; counter < 4 + (stages * 2); counter++)
{
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
%>
<%="..."%>
<a href="dataiterator.jsp?page=<%=(lastpage-1)%>"> <%=(lastpage-1)%></a>
<a href="dataiterator.jsp?page=<%=(lastpage)%>"> <%=(lastpage)%></a>
<%
}else if(lastpage - (stages * 2) > page1 && page1 > (stages * 2)){
%>
<a href="dataiterator.jsp?page=1"> 1</a>
<a href="dataiterator.jsp?page=2"> 2</a>
<%="..."%>
<%
for (int counter = page1 - stages; counter <= page1 + stages; counter++)
{
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
%>
<%="..."%>
<a href="dataiterator.jsp?page=<%=(lastpage-1)%>"> <%=(lastpage-1)%></a>
<a href="dataiterator.jsp?page=<%=(lastpage)%>"> <%=(lastpage)%></a>
<%
}else{
%>
<a href="dataiterator.jsp?page=1"> 1</a>
<a href="dataiterator.jsp?page=2"> 2</a>
<%="..."%>
<%
for (int counter = lastpage - (2 + (stages * 2)); counter <= lastpage; counter++)
{
if (counter == page1){
out.print("<span class='current'>"+counter+"</span>");
}else{
%>
<a href="dataiterator.jsp?page=<%=counter%>"> <%=counter%></a>
<%
}
}
}
}
%>
<c:if test="${currentPage lt noOfPages}">
<a href="dataiterator.jsp?page=${currentPage + 1}"> Next</a>
</c:if>
</td>
</tr>
</table>
</div>
</body>
</html>
No comments:
Post a Comment