Hi steven Thank you for the scrip

ktsrikanth · Apr 19, 2001

Hi steven
Thank you for the script. I have a another problem now.
I am using Apache, Jserv for the servlets to run.
I get an exception IO Exception: Broken Pipe, randomly, whenever i run the servlets.
I do not know the cause for this exception.
Could any one help me with this.
Please help me with what might be the cause for the Exception.

Thank you

LeonTang · Apr 19, 2001

Hi,

I guess it would be better if possible, if you could copy and paste some portions of your codes into the forum.

It would be quite hard know what is causing the error without any codes.

Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

ktsrikanth · May 17, 2001

Hi LeonTang,
the code is pasted below. Please give me the solution as soon as possible.

package com.vip.portal.systemlevelservices.reporting;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.text.*;

//******************************************************************************
// ClassFile
//******************************************************************************
/**
* <STRONG>
* Description:
* </STRONG><BR>

This class generates a report on portal users

* @version <PRE>
* $History: UsersReport.java $
*
* ***************** Version 1 *****************
* User: Andrew Linton Date: 10-26-2000 Time: 14:00
* User: Srikanth K. changes are made in the code
* *
* </PRE>
**/
//********************************************************************************/

public class UsersReport extends HttpServlet
{
//*****************************************************************************
// Variables
//*****************************************************************************

/**
* Description of stmt: the JDBC statement
**/
Statement stmt=null;
/**
* Description of driver: the JDBC driver
**/
Driver driver;
/**
* Description of conn: the JDBC connection
**/
Connection conn=null;
/**
* Description of results: the JDBC result set
**/
ResultSet results;
/**
* Description of tempString: a temporary String variable
**/
String tempString;
/**
* Description of tempInt: a temporary int variable
**/
int tempInt;
/**
* Description of tempDate: a temporary Date variable
**/
java.sql.Date tempDate;
/**
* Description of sql: the SQL SELECT statement run against the database
**/
String sql;
/**
* Description of driverName: the name of the database driver
**/
String driverName;
/**
* Description of connectionString: the database server IP address and port number
**/
String connectionString;
/**
* Description of username: the username to connect to the database
**/
String username;
/**
* Description of password: the password to connect to the database
**/
String password;
/**
* Description of formatter: used to format and parse dates
**/
SimpleDateFormat formatter;
/**
* Description of formatter1: used to format and parse dates
**/
SimpleDateFormat formatter1;
/**
* Description of outWriter: used to write to the export file
**/
BufferedWriter outWriter;
/**
* Description of tempString1: a temporary String variable
**/
String tempString1;
/**
* Description of filename: the name of the file to be exported to
**/
String filename;
/**
* Description of summary: determines whether to out each individual message
**/
String summary;
/**
* Description of calendar: used to calculate the current date
**/
GregorianCalendar calendar;
/**
* Description of date1: a temporary date value
**/
java.util.Date date1;
/**
* Description of localDate: the local date
*/
java.util.Date localDate;
/**
* Description of months: an array of Strings containing the names of the months
**/
String months[];
/**
* Description of currentMonth: today's month
**/
int currentMonth;
/**
* Description of currentDay: today's day
**/
int currentDay;
/**
* Description of currentYear: today's year
**/
int currentYear;
/**
* Description of startMonth: the month to start reporting on
**/
int startMonth;
/**
* Description of startDay: the day to start reporting on
**/
int startDay;
/**
* Description of startYear: the year to start reporting on
**/
int startYear;
/**
* Description of endMonth: the month to end reporting on
**/
int endMonth;
/**
* Description of endDay: the day to end reporting on
**/
int endDay;
/**
* Description of endYear: the year to end reporting on
**/
int endYear;
/**
* Description of createDate: the date that the current user account
**/
java.sql.Date createDate;
/**
* Description of startDate: the start of the reporting period
**/
java.util.Date startDate;
/**
* Description of endDate: the end of the reporting period
**/
java.util.Date endDate;
/**
* Description of all: indicates whether to filter user accounts based on creation date
**/
String all;
/**
* Description of timeZone: used to calculated NZ time
**/
TimeZone timeZone;
/**
* Description of sortColumn: column to sort by
*/
String sortColumn;
/**
* Description of columnVector: vector of column names
*/
Vector columnVector;
/**
* Description of orginalStartMonth: the original month the report is to start
*/
int originalStartMonth;
/**
* Description of reportDirectory: the directory to export files are to be put
*/
String reportsDirectory;
/**
* Description of userType: the type of the current user
*/
String userType;
/**
* Description of MSISDN : the type of mobile user
*/
String minnumber;
String minnumber1="";
/**
* Description of non_mobileusers: the category of the users
*/
String nonmobusers;
String text;
/**
* init
*
* @param config: the server configuration
*/
public void init(ServletConfig config) throws ServletException
{
super.init(config);
// calculate the current time in NZ
formatter= new SimpleDateFormat("MM/dd/yyyy&quot

;
formatter1= new SimpleDateFormat("yyyy-MM-dd&quot

;
timeZone=TimeZone.getTimeZone("Pacific/Auckland&quot

;
calendar=new GregorianCalendar();
calendar.setTimeZone(timeZone);
date1=new java.util.Date();
calendar.setTime(date1);
formatter.setCalendar(calendar);
localDate=new java.util.Date();

months=new String[12];
months[0]="January";
months[1]="February";
months[2]="March";
months[3]="April";
months[4]="May";
months[5]="June";
months[6]="July";
months[7]="August";
months[8]="September";
months[9]="October";
months[10]="November";
months[11]="December";

//columnVector=new Vector(5,5);

// get database parameters from zone.properties file
driverName=getInitParameter("driverName&quot

;
connectionString=getInitParameter("connectionString1&quot

;
username=getInitParameter("username1&quot

;
password=getInitParameter("password1&quot

;
filename=getInitParameter("filename&quot

;
if (filename==null)
filename="";
//text=getInitParameter("details&quot

;
//if (text==null)
//text="";
sortColumn=getInitParameter("sortColumn&quot

;
if (sortColumn==null)
sortColumn="";
reportsDirectory=getInitParameter("reportsDirectory&quot

;
if (reportsDirectory==null)
reportsDirectory="";

// connect to database
try
{
driver=(Driver)Class.forName(driverName).newInstance();
}
catch (Exception e1)
{
log(e1.getMessage());
}
try
{
conn=DriverManager.getConnection(connectionString,username,password);
}
catch (Exception e2)
{
log(e2.getMessage());
}
try
{
stmt=conn.createStatement();
}
catch (Exception e3)
{
log(e3.getMessage());
}
}

/**
* Responds to a Get request from the user
*
* @param request: the request received from the user
* @param response: the response returned to the user
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
/**
* Description of x: a temporary variable
**/
int x;
/**
* Description of columnName: name of the current column
**/
String columnName;
/**
* Description of columnType: data type of current column
**/
String columnType;
/**
* Description of returnString: temporary String variable
**/
String returnString;
/**
* Description of totalUsers: counter for total users
**/
int totalUsers=0;
/**
* Description of out: used to write html to response page
**/
PrintWriter out;
/**
* Description of monthTo: the ending month for the report passed in via HTTP request
**/
String monthTo;
/**
* Description of dayTo: the ending day for the report passed in via HTTP request
**/
String dayTo;
/**
* Description of yearTo: the ending year for the report passed in via HTTP request
**/
String yearTo;
/**
* Description of monthFrom: the starting month for the report passed in via HTTP request
**/
String monthFrom;
/**
* Description of dayFrom: the starting day for the report passed in via HTTP request
**/
String dayFrom;
/**
* Description of yearFrom: the starting year for the report passed in via HTTP request
**/
String yearFrom;
/**
* Description of metaData: data about the data retrieved from the database
**/
ResultSetMetaData metaData;
/**
* Description of columnCount: number of columns in the result set
**/

columnVector=new Vector(5,5);

int columnCount;

int count021=0;
//int countNon021=0;
int count029=0;
//int countWAP=0;
int countNonMobile=0;
out = new PrintWriter (response.getOutputStream());

sql=getInitParameter("sql&quot

;

// if filename passed in as a HTTP request parameter, override existing value
tempString=request.getParameter("filename&quot

;
if ((tempString!=null) && (tempString.length()>0))
filename=tempString;

// if details passed in as a HTTP request parameter display details of each user
summary=request.getParameter("summary&quot

;
if (summary==null)
summary="";

// if filterCreate passed in as a HTTP request parameter filter users by account creation date
all=request.getParameter("all&quot

;
if (all==null)
all="";

// get the report date range from the HTTP request
monthFrom=request.getParameter("MONTH_FROM&quot

;
if (monthFrom!=null)
startMonth=new Integer(monthFrom).intValue();
else
startMonth=-1;
dayFrom=request.getParameter("DAY_FROM&quot

;
if (dayFrom!=null)
startDay=new Integer(dayFrom).intValue();
else
startDay=-1;
yearFrom=request.getParameter("YEAR_FROM&quot

;
if (yearFrom!=null)
startYear=new Integer(yearFrom).intValue();
else
startYear=-1;
monthTo=request.getParameter("MONTH_TO&quot

;
if (monthTo!=null)
endMonth=new Integer(monthTo).intValue();
else
endMonth=-1;
dayTo=request.getParameter("DAY_TO&quot

;
if (dayTo!=null)
endDay=new Integer(dayTo).intValue();
else
endDay=-1;
yearTo=request.getParameter("YEAR_TO&quot

;
if (yearTo!=null)
endYear=new Integer(yearTo).intValue();
else
endYear=-1;

// get into about the current time
currentYear=calendar.get(Calendar.YEAR);
currentMonth=calendar.get(Calendar.MONTH);
currentDay=calendar.get(Calendar.DATE);

// if no request parameters passed in set the report period to today
originalStartMonth=startMonth;
if (startMonth==-1)
{
startDay=currentDay;
endDay=currentDay;
startMonth=currentMonth;
endMonth=currentMonth;
startYear=currentYear;
endYear=currentYear;
}

calendar.set(startYear,startMonth,startDay);
if (originalStartMonth==-1)
calendar.add(Calendar.DATE,-7);
startDate=calendar.getTime();
//out.println("StartDate: "+startDate+"<p>&quot

;
startDay=calendar.get(Calendar.DATE);
startMonth=calendar.get(Calendar.MONTH);
startYear=calendar.get(Calendar.YEAR);

calendar.set(endYear,endMonth,endDay);
endDate=calendar.getTime();
//out.println(endDate);

// create export file

try
{
if ((filename!=null) && (filename.length()>0))
{
outWriter= new BufferedWriter(new FileWriter(filename, false));
}
else
{
outWriter=null;
}
}
catch (IOException e)
{
outWriter=null;
}

response.setContentType("text/html&quot

;
// response.setDateHeader();
out.println("<HEAD>&quot

;
out.println("<TITLE>Registrations Report</TITLE>&quot

;
out.println("</HEAD>&quot

;
out.println("<BODY BGCOLOR='#FFFFFF'>&quot

;
out.println("<FONT FACE='Arial,Helvetica'>&quot

;
out.println("<IMG SRC='/vodafone_logo.gif' ><BR>&quot

;
out.println("<B>Registrations Report</B><P>&quot

;
if (outWriter!=null)
outWriter.write("Registrations Report\n&quot

;
if (outWriter!=null)
outWriter.write("Report Run: "+localDate+"\n\n&quot

;

// if filtering users by account creation date
if (all=="&quot

{
tempString="Users created from "+formatter.format(startDate);
tempString=tempString+" to "+formatter.format(endDate)+" (New Zealand time)<br>";
out.println(tempString+"<br>&quot

;
out.println("This report includes all users who completed the registration process during a period. This includes all users who are registered on the site including system 'active', 'barred', 'locked', 'inactive' and 'deleted TBC users.<br>&quot

;
//out.println(text+"<br>&quot

;
if (outWriter!=null)
outWriter.write(tempString+"\n&quot

;
//outWriter.write(text+"<br>&quot

;

//tempString=" AND ((UP_REGISTRATION_DATE > TO_DATE('"+formatter1.format(startDate)+"','YYYY-MM-DD')) AND (UP_REGISTRATION_DATE < TO_DATE('"+formatter1.format(endDate)+"','YYYY-MM-DD')))";
tempString=" AND (TRUNC(UP_REGISTRATION_DATE) BETWEEN (TO_DATE('"+formatter1.format(startDate)+"','YYYY-MM-DD')) AND (TO_DATE('"+formatter1.format(endDate)+"','YYYY-MM-DD')))";
//tempString=" WHERE FNAME= 'TOM' ";
sql=sql+tempString;
//out.println("SQL: "+sql+"<br>&quot

;
}

// if sortColumn passed in as a HTTP request parameter override existing value
tempString=request.getParameter("SORT_COLUMN&quot

;
if ((tempString!=null) && (tempString.length()>0))
sortColumn=tempString;
//sql=sql+" ORDER BY "+sortColumn;

try
{
//out.println(sql);
results=stmt.executeQuery(sql);
metaData=results.getMetaData();
columnCount=metaData.getColumnCount();
tempString1="";
// output column headings
tempString="<TABLE BORDER WIDTH='100%'><TR>";
for (x=1;x<=columnCount;x++)
{
columnName=metaData.getColumnName(x);
//out.println("columnnames"+columnName);
tempString1=tempString1+columnName+",";
columnVector.addElement(columnName);
tempString=tempString+"<TD>"+columnName+"</TD>";
}
tempString=tempString+"</TR>";
if (summary=="&quot

out.println(tempString);
tempString1=tempString1+"\n";
if (outWriter!=null)
outWriter.write(tempString1);

// loop through records in resultset
while (results.next())
{
returnString="<TR>";
try
{
// OLD CODE
//userType=results.getString("LO_USER_TYPE&quot

;
//if (userType.indexOf("1&quot

>-1)
//count021++;
// END OF OLD CODE
minnumber=results.getString(4);
totalUsers++;
minnumber=minnumber.substring(1,5);
//minnumber=minnumber.substring(1,4);
if(minnumber.equals(new String("6421&quot

))
//if(minnumber.equals(new String("614&quot

))
count021++;
else if(minnumber.equals(new String("6429&quot

))
count029++;
}
catch (Exception e)
{
//count021++;
}

//out.println(count021++);
//count029++;
//countNonMobile++;

tempString1="";
countNonMobile=totalUsers-count021-count029;
//countNonMobile=totalUsers-count021;
// loop through columns displaying each according to its datatype
for (x=1;x<=columnCount;x++)
{
columnType=metaData.getColumnTypeName(x);
columnName=metaData.getColumnName(x);
if (columnType.indexOf("NUMBER&quot

>-1)
{
tempInt=results.getInt(x);
tempString1=tempString1+tempInt+",";
returnString=returnString+"<TD>"+tempInt+"</TD>";
}
else if (columnType.indexOf("CHAR&quot

>-1)
{
tempString=results.getString(x);
if (tempString==null)
tempString="";
tempString1=tempString1+'"'+tempString+'"'+',';
returnString=returnString+"<TD>"+tempString+"</TD>";
}
else if (columnType.indexOf("DATE&quot

>-1)
{
tempDate=results.getDate(x);
if (tempDate!=null)
{
tempString=tempDate.toString();
if ((tempString!=null) && (tempString.length()>0))
{
tempString1=tempString1+ReportMenu.formatDateString(tempDate)+",";
returnString=returnString+"<TD>"+formatter.format(tempDate)+"</TD>";
}
else
{
tempString1=tempString1+",";
returnString=returnString+"<TD></TD>";
}
}
}
}
tempString1=tempString1+"\n";
returnString=returnString+"</TR>";
if (summary=="&quot

out.println(returnString);
if (outWriter!=null)
outWriter.write(tempString1);
}
returnString="</TABLE>";
if (summary=="&quot

out.println(returnString);
out.println("<P>&quot

;
out.println("<TABLE>&quot

;
tempString="Total Users: "+totalUsers+"<br>";
out.println("<TR><TD>Total Users:</TD><TD>"+totalUsers+"</TD></TR>&quot

;
if (outWriter!=null)
outWriter.write("\n"+tempString);
tempString="Total 021 Users: "+count021+"<br>";
tempString="Total 641 Users: "+count021+"<br>";;
out.println("<TR><TD>Total 641 Users:</TD><TD>"+count021+"</TD></TR>&quot

;
//out.println("<TR><TD>Total 021 Users:</TD><TD>"+count021+"</TD></TR>&quot

;
// out.println(tempString);
if (outWriter!=null)
outWriter.write("\n"+tempString);
//tempString="Total non-021 Users: "+countNon021+"<br>";
//tempString="Total 029 Users: "+count029+"<br>";
//out.println("<TR><TD>Total 029 Users:</TD><TD>"+count029+"</TD></TR>&quot

;
// out.println(tempString);
if (outWriter!=null)
outWriter.write("\n"+tempString);
tempString="Total Non-Mobile Users: "+countNonMobile+"<br>";;
out.println("<TR><TD>Total Non-Mobile Users:</TD><TD>"+countNonMobile+"</TD></TR>&quot

;
// out.println(tempString);
if (outWriter!=null)
outWriter.write("\n"+tempString);
out.println("<TABLE>&quot

;

// output a form on which the users may change the report parameters
out.println("<HR>&quot

;
out.println("<B>Parameters</B><br>&quot

;
out.println("Change the parameters below and press Submit in order to regenerate this report<br>&quot

;
out.println("<FORM method=GET action='/servlet/RegistrationsReport'>&quot

;
out.println("<TABLE>&quot

;
//out.println("<TR><TD>Export Filename:</TD><TD> <INPUT required value='"+filename+"' name='filename'></TD></TR>&quot

;
out.println("<TR><TD>Display Summary Only</TD><TD><input type=CHECKBOX value='CHECKED' name='summary' "+summary+"></TD></TR>&quot

;
out.println("<TR><TD>Display All Records</TD><TD><input type=CHECKBOX value='CHECKED' name='all' "+all+"></TD></TR>&quot

;
out.println("<TR><TD>From Date</TD>&quot

;
out.println("<TD><SELECT NAME=MONTH_FROM>&quot

;
for (x=0;x<12;x++)
{
if (x==(startMonth))
{
out.println("<OPTION VALUE="+x+" SELECTED>"+months[x]);
}
else
{
out.println("<OPTION VALUE="+x+">"+months[x]);
}
}
out.println("</SELECT>&quot

;
out.println("<SELECT NAME=DAY_FROM>&quot

;
for (x=1;x<=31;x++)
{
if (x==startDay)
{
out.println("<OPTION VALUE="+x+" SELECTED>"+x);
}
else
{
out.println("<OPTION VALUE="+x+">"+x);
}
}
out.println("</SELECT>&quot

;
out.println("<INPUT TYPE=TEXT NAME=YEAR_FROM SIZE=5 VALUE="+startYear+"></TD></TR>&quot

;
out.println("<TR><TD>To Date</TD>&quot

;
out.println("<TD><SELECT NAME=MONTH_TO>&quot

;
for (x=0;x<12;x++)
{
if (x==(endMonth))
{
out.println("<OPTION VALUE="+x+" SELECTED>"+months[x]);
}
else
{
out.println("<OPTION VALUE="+x+">"+months[x]);
}
}
out.println("</SELECT>&quot

;
out.println("<SELECT NAME=DAY_TO>&quot

;
for (x=1;x<=31;x++)
{
if (x==endDay)
{
out.println("<OPTION VALUE="+x+" SELECTED>"+x);
}
else
{
out.println("<OPTION VALUE="+x+">"+x);
}
}
out.println("</SELECT>&quot

;
out.println("<INPUT TYPE=TEXT SIZE=5 NAME=YEAR_TO VALUE="+endYear+">&quot

;
out.println("<TR><TD>Order By:</TD><TD><SELECT NAME=SORT_COLUMN>&quot

;
for (x=0;x<columnVector.size();x++)
{
tempString=(String)columnVector.elementAt(x);
if (tempString.indexOf(sortColumn)>-1)
out.println("<OPTION VALUE="+tempString+" SELECTED>"+tempString);
else
out.println("<OPTION VALUE="+tempString+">"+tempString);
}
out.println("</SELECT></TD></TR>&quot

;
out.println("<TR><TD><input type=SUBMIT value='Submit'></TD></TR>&quot

;
out.println("</TABLE>&quot

;
out.println("</FORM>&quot

;

if (outWriter!=null)
outWriter.close();
}
catch (Exception e4)
{
out.println(e4.getMessage());
}
//out.println("<A HREF='"+reportsDirectory+"'>Exported csv files </A><BR>&quot

;
// out.println("<BR><IMG SRC='/poweredby_vgp.gif' ><BR>&quot

;
out.flush();
out.close();
}

/**
* Close database connection when unloaded from memory
*/
public void destroy()
{
try
{
conn.close();
}
catch (SQLException e)
{
}
}
}

Thank you

ktsrikanth · May 17, 2001