import java.io.IOException;
import java.io.PrintWriter;
import java.net.Socket;
import java.net.ServerSocket;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.Scanner;
import java.util.TimeZone;
import com.mysql.jdbc.*;
import com.mysql.jdbc.Driver;
import java.sql.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import javax.crypto.SecretKey;
import javax.crypto.spec.SecretKeySpec;
/**
* @author Joshua Wilkins
* @version 1.05 last modified 5/2/2008
* Built for graduate level Networks first semester final project.
* must have the mysql-connector-java-5.1.5-bin.jar set into ide or system environment variables in order for the program to work
* properly. Also a working mysql server must be available.
*/
public class Server {
private int defaultServerSocketport = 19000;
private static GregorianCalendar calendar;
private Connection con;
private byte[] keyBytes = new byte[] {(byte) 0xFF, (byte) 0x00, (byte) 0xFF, (byte) 0x00, (byte) 0xFF, (byte) 0x00, (byte) 0xFF, (byte) 0x00};
private SecretKey key = new SecretKeySpec(keyBytes,"DES");
private DesEncrypter encrypter2 = new DesEncrypter(key);
protected enum Event {
BADCNCTREQ, GOODCNCTREQ, CLOSEREQRCVD, IOREQRCVD, SINGLEMPDATAREQ, CLKDINEMPREQ
}
protected enum State {
WAITCONNECTION, CLIENTCONNECTED
}
/**
* Default constructor. Connects to mysql server and opens a server socket
*/
public Server() throws IOException, SQLException, ClassNotFoundException,
InstantiationException, IllegalAccessException {
ServerSocket serverSocket = null;
Class.forName("com.mysql.jdbc.Driver");
try {
con = DriverManager.getConnection(
"jdbc:mysql://ip_and_port_for_mysql/databasename", "username",
"passwordhere"); //changeme
serverSocket = new ServerSocket(defaultServerSocketport);
System.out.println("success");
}
catch (IOException e) {
System.err.println("Could not listen on port: "
+ defaultServerSocketport);
System.exit(-1);
}
while (true) {
new ServerConnection(serverSocket.accept()).start();
}
}
public Server(int str) throws IOException {
defaultServerSocketport = str;
ServerSocket serverSocket = null;
try {
serverSocket = new ServerSocket(defaultServerSocketport);
}
catch (IOException e) {
System.err.println("Could not listen on port: "
+ defaultServerSocketport);
System.exit(-1);
}
while (true) {
new ServerConnection(serverSocket.accept()).start();
}
}
/**
* @param args
* @throws IOException
* @throws SQLException
* @throws ClassNotFoundException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public static void main(String[] args) throws IOException, SQLException,
ClassNotFoundException, InstantiationException,
IllegalAccessException {
// TODO Auto-generated method stub
Server server;
if (args != null && args.length > 0) {
server = new Server(Integer.parseInt(args[0]));
}
else {
server = new Server();
}
}
/**
* Inner threaded class for the server. Contains the state
* machine.
* @author Joshua Wilkins INNER CLASS
*/
class ServerConnection extends Thread {
private Socket socket = null;
private Scanner clientIn;
private PrintWriter clientOut;
private Server.Event eventType;
private Server.State presentState;
private int eventState = 0;
private int[][] eventStateTable;
private ArrayList<String> clientInputList;
private String clientInput;
private boolean keeprunning;
public ServerConnection(Socket socket) {
super("ServerConnection");
this.socket = socket;
}
public void run() {
initialize();
loop();
}
private void initialize() {
eventType = null;
eventState = 0;
presentState = Server.State.WAITCONNECTION;
buildEventStateTable();
try {
clientIn = new Scanner(socket.getInputStream());
clientOut = new PrintWriter(socket.getOutputStream(), true);
}
catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
clientInputList = new ArrayList<String>();
}
private void loop() {
keeprunning = true;
while (keeprunning) {
if (clientIn.hasNextLine()) {
clientInput = clientIn.nextLine();
catchClientInput(clientInput);
switchCase(eventState);
}
}
}
private void switchCase(int eventState) {
switch (eventState) {
case 0: {// nothing happens
System.out.println("And nothing happens...");
break;
}
case 1: { // badcnctREq && wait connect
clientOut.println("REJECTED");
presentState = Server.State.WAITCONNECTION;
keeprunning = false;
break;
}
case 2: { // good cnct req && wait connect
clientOut.println("ACCEPT");
presentState = Server.State.CLIENTCONNECTED;
break;
}
case 3: { // close req rcvd && client connected
String str = "CLOSEACK";
str = encData(str);
clientOut.println(str);
presentState = Server.State.WAITCONNECTION;
keeprunning = false;
break;
}
case 4: { // IOreq rcvd && client connected
String str;
str = clientIn.nextLine();
String username = decData(str);
str = clientIn.nextLine();
String password = decData(str);
String response = sqlGetClockIN(username, password);
response = encData(response);
String responseflag = encData("IORESPONSE");
clientOut.println(responseflag);
clientOut.println(response);
presentState = Server.State.CLIENTCONNECTED;
break;
}
case 5: { // single emp data req rcvd && client connected
String username = decData(clientIn.nextLine());
String startDate = decData(clientIn.nextLine());
String endDate = decData(clientIn.nextLine());
String output = sqlSingleEmpReq(username, startDate, endDate);
String flag = encData("DATARESPONSE");
clientOut.println(flag);
String frame;
frame = encData(output);
clientOut.println(frame);
String end = "END";
end = encData(end);
clientOut.println(end);
presentState = Server.State.CLIENTCONNECTED;
break;
}
case 6: { // clckd in employees req rcvd && client connected
clientInputList = sqlClockedInEmpReq();
String flag = encData("DATARESPONSE");
clientOut.println(flag);
for (int i = 0; i < clientInputList.size(); i++) {
String frame;
frame = encData(clientInputList.get(i));
clientOut.println(frame);
}
String end = "END";
end = encData(end);
clientOut.println(end);
presentState = Server.State.CLIENTCONNECTED;
break;
}
}
}
private void catchClientInput(String str) {
str = decData(str);
if (str.equals("CONNECT")
&& presentState.equals(Server.State.WAITCONNECTION)) {
eventState = 2;
}
else if (str.equals("REJECTED")
&& presentState.equals(Server.State.WAITCONNECTION)) {
eventState = 1;
}
else if (str.equals("CLOSEREQ")
&& presentState.equals(Server.State.CLIENTCONNECTED)) {
eventState = 3;
}
else if (str.equals("IOREQ")) {
eventState = 4;
}
else if (str.equals("SINGLE")) {
eventState = 5;
}
else if (str.equals("ALLCLOCKEDIN")) {
eventState = 6;
}
else {
eventState = 0;
}
}
@SuppressWarnings("deprecation")
private ArrayList<String> sqlClockedInEmpReq() {
String output = "No Clocked In Employees";
boolean noEmp = false;
ArrayList<String> list = new ArrayList<String>();
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT UserName, Date, Time FROM ClockedIN");
while(rs.next()) {
String str1 = rs.getString("UserName");
Date date = rs.getDate("Date");
Time time = rs.getTime("Time");
String str2 = (str1 + "#"+date.getMonth()+"-"+date.getDay()+"-"+(date.getYear()+1900)+"#"+time.getHours()+"-"+time.getMinutes()+"-"+time.getSeconds()+"#");
list.add(str2);
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(list.isEmpty())
list.add(output);
return list;
}
private String sqlSingleEmpReq(String username, String startDate, String endDate) {
String output = "no hours worked for this employee or Employee not found";
try {
DateFormat format = new SimpleDateFormat("MM-dd-yyyy");
//System.out.println(startDate);
java.util.Date date1 = format.parse(startDate);
java.util.Date date2 = format.parse(endDate);
Statement stmt = con.createStatement();
//System.out.println((date1.getYear()+1900) + " " + (date1.getMonth()+1) + " " + date1.getDate());
ResultSet rs = stmt.executeQuery("SELECT UserName, Date, Hours FROM Hours WHERE UserName = '" + username +"' AND Date >= '" + (date1.getYear()+1900) + "-" + (date1.getMonth()+1) + "-" + (date1.getDate()) +"' AND Date <= '" + (date2.getYear()+1900) + "-" + (date2.getMonth()+1) + "-" + (date2.getDate()) + "'");
ArrayList<Double> list = new ArrayList<Double>();
double total = 0;
while(rs.next()) {
list.add(rs.getDouble("Hours"));
}
if(!list.isEmpty()) {
for(int i = 0; i < list.size(); i++){
total = total + list.get(i);
}
output = (username + "#" + total);
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
output = e.toString();
}
catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return output;
}
@SuppressWarnings("deprecation")
private String sqlGetClockIN(String userID, String password) {
String output = "";
boolean isValidUser = false;
boolean clockIN = false;
boolean clockOUT = false;
boolean isClockedIN = false;
calendar = new GregorianCalendar(TimeZone.getDefault());
int year = calendar.get(calendar.YEAR);
int month = (calendar.get(calendar.MONTH) + 1);
int day = calendar.get(calendar.DAY_OF_MONTH);
int hour = calendar.get(calendar.HOUR_OF_DAY);
int minute = calendar.get(calendar.MINUTE);
int second = calendar.get(calendar.SECOND);
ResultSet rs;
try {
Statement stmt = con.createStatement();
rs = stmt.executeQuery("SELECT UserName, Password "
+ "FROM Users " + "WHERE UserName = " + "'" +userID+ "'"
+ " AND Password = " + "'" + password + "'");
while (rs.next()) {
String user = rs.getString("UserName");
String pass = rs.getString("Password");
if (user.equals(userID) && pass.equals(password)) {
isValidUser = true;
}
else {
output = "UserName does not exist in database or incorrect password.";
}
}
if (isValidUser) {
rs = stmt
.executeQuery("SELECT UserName, Date, Time FROM ClockedIN WHERE UserName = '"
+ userID + "'");
while (rs.next()) {
String user = rs.getString("UserName");
if(user.equals(userID)) {
isClockedIN = true;
}
}
if(isClockedIN) {
clockOUT = true;
rs = stmt
.executeQuery("SELECT UserName, Date, Time FROM ClockedIN WHERE UserName = '"
+ userID + "'");
Date date = null;
Time time = null;
while(rs.next()) {
date = rs.getDate("Date");
time = rs.getTime("Time");
}
stmt.executeUpdate("DELETE FROM ClockedIN " +
"WHERE UserName = '" + userID + "'");
rs = stmt.executeQuery("SELECT UserName, Date, Hours FROM Hours " +
"WHERE UserName = '" + userID + "' AND Date = '" + date.toString() + "'" );
boolean hasClockedInToday = false;
double totalHours = 0;
while(rs.next()) {
hasClockedInToday = true;
totalHours = rs.getDouble("Hours");
}
if(hasClockedInToday) {
Long total = time.getTime();
Time time2 = new Time(hour,minute,second);
Long todayTotal = time2.getTime();;
total = todayTotal - total; //clockin time - clockout time
total = (total / 1000); // to seconds
Double totalTime = new Double(total.doubleValue()); //to double
totalTime = (totalTime / 60); //to minutes
totalTime = (totalTime / 60); //to hours
totalTime = totalTime + totalHours; //total hours
DecimalFormat format = new DecimalFormat();
format.setMaximumFractionDigits(2);
format.setMinimumFractionDigits(2);
format.setMaximumIntegerDigits(3);
format.setMinimumIntegerDigits(3);
String str = format.format(totalTime);
totalTime = Double.parseDouble(str);
stmt.executeUpdate("UPDATE Hours " +
"SET Hours = '" + totalTime + "' " +
"WHERE UserName = '" + userID + "' AND Date = '" + date.toString() + "'");
}
}
else {
clockIN = true;
stmt.executeUpdate("INSERT into ClockedIN (UserName, Date, Time) " +
"VALUES ('" + userID +"', '" + year + "-" + month + "-" + day + "', '" + hour + ":" + minute + ":" + second + "')");
rs = stmt.executeQuery("SELECT UserName, Date, Hours FROM Hours " +
"WHERE UserName = '" + userID + "' AND Date = '" + year + "-" + month + "-" + day + "'" );
boolean hasClockedInToday = false;
double totalHours = 0.0;
while(rs.next()) {
hasClockedInToday = true;
}
if(!hasClockedInToday) {
stmt.executeUpdate("INSERT into Hours (UserName, Date, Hours) " +
"VALUES ('" + userID +"', '" + year + "-" + month + "-" + day + "', " + totalHours + ")");
}
}
if(clockIN) {
output = new String("IN#"+ month +"-" + day + "-" + year + "#" + hour + "-" + minute + "-" + second);
}
if(clockOUT) {
output = new String("OUT#"+ month +"-" + day + "-" + year + "#" + hour + "-" + minute + "-" + second);
}
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return output;
}
private String encData(String str) {
return encrypter2.encrypt(str);
//return str;
}
private String decData(String str) {
return encrypter2.decrypt(str);
//return str;
}
private void buildEventStateTable() {
eventStateTable = new int[6][2];
for (int r = 0; r < eventStateTable.length; r++) {
for (int c = 0; c < eventStateTable[r].length; c++) {
if (r == 0 && c == 0) {
eventStateTable[r][c] = 1;
}
else if (r == 1 && c == 0) {
eventStateTable[r][c] = 2;
}
else if (r == 2 && c == 1) {
eventStateTable[r][c] = 3;
}
else if (r == 3 && c == 1) {
eventStateTable[r][c] = 4;
}
else if (r == 4 && c == 1) {
eventStateTable[r][c] = 5;
}
else if (r == 5 && c == 1) {
eventStateTable[r][c] = 6;
}
else {
eventStateTable[r][c] = 0;
}
}
}
}
}
}