i am in the starting of making this program that inserts records in sql database from another master sql database. i have the following important fields in master database like usrnme t_date and t_time
and in the another table i wanna have usrnme,T_date , starttime and endtime
the problem is that i can't to get endtime to work, the endtime depends on two things: on user, meaning starting and ending of users time and it also depnds on interval provided in the constant field. that means for ex if the interval is for 10 min then user "sim" started time on 19:00:00 and end time would be 19:10:00. If no interval is provided and then just started and end time for user,
i can't get end time to work , it is giving me the wrong end time.
all the help i can get is appreciated
and if u have any ideas of doing it some other way please let me know
and i am giving a copy of my code and the result from sql
and it is a a little messy because i am stilll
testing it and i changed it
alot and now i can't even figure it out so i don't think it is going to big of help and i am sorry about it
thanks in advance
here is the code
import java.sql.*;
import java.io.*;
import java.text.DateFormat;
import java.util.Date;
import java.util.*;
import javax.mail.Message.*;
import javax.mail.internet.*;
import javax.mail.*;
import java.text.*;
import java.io.File;
import java.text.DateFormat;
public class NewSession
{
private int Session = 0;
public NewSession()
{
CreateNewSession();
}
public void CreateNewSession()
{
try{
Connection con;
Class.forName(Testing.FINAL_CLASSNAME).newInstance();
String p_url = Testing.FINAL_URL;
con = DriverManager.getConnection(p_url,"","");
String Query1 = "Select usrnme, t_date, t_time from weblogs where SessionId = '0' order by usrnme, t_date, t_time";
String Query2 = "Select max(SessionId) as count from NewSession";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(Query1);
String usrnme = "******";
String last_Time = "00:00:00";
String time = " ";
String Last_Min = last_Time.substring(3,5);
System.out.println("LastTime " +Last_Min);
Statement stmt1 = con.createStatement();
ResultSet rs1 = stmt1.executeQuery(Query2);
while (rs1.next())
{
Session = rs1.getInt("count");
System.out.println("SessIONID " + Session);
}
while (rs.next())
{
//String Query3 = "Select max(t_time) as EndTime from weblogs where usrnme = '" + rs.getString("usrnme") + "'";
//Statement stmt2 = con.createStatement();
//ResultSet rs2 = stmt2.executeQuery(Query3);
String t_Time = rs.getString("t_Time");
System.out.println("THE REAL TIME " + t_Time);
String sub_Hours = t_Time.substring(0,2);
String sub_Min = t_Time.substring(3,5);
String sub_Sec = t_Time.substring(6,8);
String sTime;
GregorianCalendar cal = new GregorianCalendar();
SimpleDateFormat fmt = new SimpleDateFormat("mm");
cal.set(Calendar.HOUR,Integer.parseInt(sub_Hours));
cal.set(Calendar.MINUTE,Integer.parseInt(sub_Min));
cal.set(Calendar.SECOND,Integer.parseInt(sub_Sec));
Date tmpDate = cal.getTime();
//cal.add(Calendar.MINUTE, - Testing.timeEx);
//tmpDate = cal.getTime();
//fmt.setCalendar(cal);
sTime = fmt.format( cal.getTime());
System.out.println("getTIme "+sTime);
System.out.println(rs.getString("usrnme"));
System.out.println("username" +usrnme);
if (!(rs.getString("usrnme").equals(usrnme)))
{
Session = Session +1;
PreparedStatement pStmt = con.prepareStatement("insert into NewSession()"+
"values(?,?,?,?,?,?)");
pStmt.setString(1, rs.getString("t_date"));
pStmt.setString(2, rs.getString("usrnme"));
pStmt.setInt(3,Session);
pStmt.setString(4,rs.getString("t_Time"));
//while (rs2.next())
//{
//pStmt.setString(5,rs2.getString("EndTime"));
//}
pStmt.setString(5,"");
pStmt.setString(6,"");
pStmt.execute();
usrnme =rs.getString("usrnme");
//cal.add(Calendar.MINUTE, + Testing.timeEx);
///sim = fmt.format( cal.getTime());
Last_Min = sTime;
System.out.println("LasTMIN " +Last_Min);
}
else
{
PreparedStatement pStmt1 = con.prepareStatement("update NewSession"+
" Set EndTime = '" + rs.getString("t_Time") + "' where SessionId = '" + Session +"'");
pStmt1.executeUpdate();
System.out.println("Session " +Session);
System.out.println(rs.getString("t_Time"));
if(!(time.equals(sTime)))
{
Session = Session +1;
PreparedStatement pStmt = con.prepareStatement("insert into NewSession()"+
"values(?,?,?,?,?,?)");
pStmt.setString(1, rs.getString("t_date"));
pStmt.setString(2, rs.getString("usrnme"));
pStmt.setInt(3,Session);
pStmt.setString(4,rs.getString("t_Time"));
//while (rs2.next())
//{
//pStmt.setString(5,rs2.getString("EndTime"));
//}
pStmt.setString(5,"");
pStmt.setString(6,"");
pStmt.execute();
usrnme =rs.getString("usrnme");
//cal.add(Calendar.MINUTE, + Testing.timeEx);
Last_Min = sTime;
time = Last_Min;
System.out.println("Last MIn " +Last_Min);
System.out.println(time);
}
}
} //End While
con.close();
}//EndTry
catch (Exception e) {
System.out.println(e);
} //End Catch
} //End read
public static void main(String[] args)
{
NewSession dt = new NewSession();
}
}
RIGHT NOW THE INTERVAL IS FOR 10 MIN
and look and the result
t_date | USRNME | sessionId | StartTime | EndTime | TotalTime
-----------+--------+-----------+-----------+----------+-----------
2003-12-05 | henry | 1 | 19:01:01 | 19:01:03 | 0
2003-12-05 | henry | 2 | 19:01:03 | 19:02:00 | 0
2003-12-05 | henry | 3 | 19:02:00 | 19:03:06 | 0
2003-12-05 | henry | 4 | 19:03:06 | 19:04:00 | 0
2003-12-05 | henry | 5 | 19:04:00 | 19:05:07 | 0
2003-12-05 | henry | 6 | 19:05:07 | 19:06:00 | 0
2003-12-05 | henry | 7 | 19:06:00 | 19:07:02 | 0
2003-12-05 | henry | 8 | 19:07:02 | 07:12:00 | 0
2003-12-06 | henry | 9 | 07:12:00 | 07:12:54 | 0