TicketQueries.java

package database;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import entities.Ticket;

/**
 * This class is responsible for performing most queries corresponding
 * to ticket actions.
 * @author Brianna Boyce, Connor Jackson, Adam Claxton
 */
public class TicketQueries {

	private static String database = "jdbc:mysql://us-cdbr-iron-east-04.cleardb.net/ad_15a989204c2ff8a?user=b372dfe7409692&password=74f6e317";
	private static String user = "b372dfe7409692";
	private static String password = "74f6e317";
	
	/**
	 * This function generates a ticket entry in the database.
	 * @author Brianna Boyce
	 */
	public static int generateTicket(int requester, int location, int device, String returnDate, int perm) 
			throws ClassNotFoundException, SQLException, InterruptedException
	{
		long time = new Date().getTime();	//Using milliseconds because sql is annoying with dates.
		int ticketID = (int)((time+requester+device+location)%10000);
		String stat = "Requested";
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		String query = "";
		query+="INSERT INTO ticket (Ticket_ID, Requestor, Request_Date, Location, Device_ID, Status, Status_Date_Fields, Return_Date, Permanent_Order) ";
		query+="VALUES (";
		query+= ticketID +", ";
		query+= requester +", ";
		query+= "'"+time+"'" +", ";
		query+= location +", ";
		query+= device +", ";
		query+= "'"+stat+"'" +", ";
		query+= "'"+time+"'" +", ";
		query+= "'"+returnDate+"'" + ", ";
		query+= perm + ");";
		stmt.executeUpdate(query);
		System.out.println("Executing query: "+query);
		stmt.close();
		connection.close();
		return ticketID;
	}
	
	/**
	 * This function retrieves all tickets that need to be reviewed by an administrator for approval.
	 * @author Brianna Boyce
	 */
	public static Ticket[] getRequestedTickets() 
			throws SQLException, ClassNotFoundException, InterruptedException{
		int i = 0;
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		String query = "SELECT ticket.*, employee.Name AS 'username', devices.Device_Name, location.Name AS 'locationname' FROM ticket INNER JOIN employee ON ticket.Requestor = employee.Employee_ID INNER JOIN devices ON ticket.Device_ID = devices.Device_ID INNER JOIN location ON ticket.Location = location.Location_ID WHERE ticket.Status = 'Requested'";
		ResultSet resultSet = stmt.executeQuery(query);
		System.out.println("Executing query: "+query);
		resultSet.last();
		int rows = resultSet.getRow();
		resultSet.beforeFirst();
		Ticket[] tickets = new Ticket[rows];
			
		while(resultSet.next()){
			tickets[i] = new Ticket(
					resultSet.getInt("Ticket_ID"),
					resultSet.getInt("Requestor"),
					resultSet.getLong("Request_Date"),
					resultSet.getInt("Location"),
					resultSet.getInt("Device_ID"),
					"Requested",
					resultSet.getLong("Status_Date_Fields"),
					resultSet.getString("Return_Date"),
					resultSet.getString("username"),
					resultSet.getString("Device_Name"),
					resultSet.getString("locationname"),
					resultSet.getInt("Permanent_Order")
					);
			i++;
		}
		stmt.close();
		connection.close();
		return tickets;
	}
	
	/**
	 * This function retrieves all tickets. Ever.
	 * @author Connor Jackson
	 */
	public static Ticket[] getAllTickets() 
			throws SQLException, ClassNotFoundException, InterruptedException{
		int i = 0;
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		String query = "SELECT ticket.*, employee.Name AS 'username', devices.Device_Name, location.Name AS 'locationname' FROM ticket INNER JOIN employee ON ticket.Requestor = employee.Employee_ID INNER JOIN devices ON ticket.Device_ID = devices.Device_ID INNER JOIN location ON ticket.Location = location.Location_ID";
		ResultSet resultSet = stmt.executeQuery(query);
		System.out.println("Executing query: "+query);
		resultSet.last();
		int rows = resultSet.getRow();
		resultSet.beforeFirst();
		Ticket[] tickets = new Ticket[rows];
			
		while(resultSet.next()){
			tickets[i] = new Ticket(
					resultSet.getInt("Ticket_ID"),
					resultSet.getInt("Requestor"),
					resultSet.getLong("Request_Date"),
					resultSet.getInt("Location"),
					resultSet.getInt("Device_ID"),
					resultSet.getString("Status"),
					resultSet.getLong("Status_Date_Fields"),
					resultSet.getString("Return_Date"),
					resultSet.getString("username"),
					resultSet.getString("Device_Name"),
					resultSet.getString("locationname"),
					resultSet.getInt("Permanent_Order")
					);
			i++;
		}
		stmt.close();
		connection.close();
		return tickets;
	}
	
	/**
	 * This function retrieves all tickets based on user id..
	 * @author Connor Jackson
	 */
	public static Ticket[] getAllTickets(int sso) 
			throws SQLException, ClassNotFoundException, InterruptedException{
		int i = 0;
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		String query = "SELECT ticket.*, employee.Name AS 'username', devices.Device_Name, location.Name AS 'locationname' FROM ticket INNER JOIN employee ON ticket.Requestor = employee.Employee_ID INNER JOIN devices ON ticket.Device_ID = devices.Device_ID INNER JOIN location ON ticket.Location = location.Location_ID WHERE Requestor = " + sso;
		ResultSet resultSet = stmt.executeQuery(query);
		System.out.println("Executing query: "+query);
		resultSet.last();
		int rows = resultSet.getRow();
		resultSet.beforeFirst();
		Ticket[] tickets = new Ticket[rows];
			
		while(resultSet.next()){
			tickets[i] = new Ticket(
					resultSet.getInt("Ticket_ID"),
					resultSet.getInt("Requestor"),
					resultSet.getLong("Request_Date"),
					resultSet.getInt("Location"),
					resultSet.getInt("Device_ID"),
					resultSet.getString("Status"),
					resultSet.getLong("Status_Date_Fields"),
					resultSet.getString("Return_Date"),
					resultSet.getString("username"),
					resultSet.getString("Device_Name"),
					resultSet.getString("locationname"),
					resultSet.getInt("Permanent_Order")
					);
			i++;
		}
		stmt.close();
		connection.close();
		return tickets;
	}
	
	/**
	 * This function accepts a ticket, and assigns that ticket & device to its
	 * corresponding renter and location.
	 * @author Connor Jackson
	 */
	public static void acceptTicket(int ticketid, int deviceid, int locationid, int sso, int perm) 
			throws SQLException, ClassNotFoundException, InterruptedException{
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		long milliseconds = new java.util.Date().getTime();
		String date = new SimpleDateFormat("MM/dd/yyyy").format(new Date());
		System.out.println("Current date is: "+ date);
		String query = "UPDATE ticket, devices SET ticket.Status = \"Approved\""
						+", ticket.Status_Date_Fields = " + milliseconds
						+", devices.Borrow_Date = \"" + date
						+"\", devices.Ticket_ID = " + ticketid
						+", devices.Status = \"Ready to Ship\", devices.Renter = " + sso
						+", devices.Location = " + locationid 
						+", devices.Permanent = " + perm
						+" WHERE ticket.Ticket_ID = " + ticketid + " AND devices.Device_ID = " + deviceid;			
		stmt.executeUpdate(query);
		System.out.println("Executing query: "+query); 	
		stmt.close();
		connection.close();
	}
	
	/**
	 * This function rejects a ticket.
	 * @author Connor Jackson
	 */
	public static void rejectTicket(int id) 
			throws SQLException, ClassNotFoundException, InterruptedException{
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		long milliseconds = new Date().getTime();
		String query="UPDATE ticket SET Status = \"Rejected\", Status_Date_Fields = "+milliseconds+" WHERE Ticket_ID = " + id;
		stmt.executeUpdate(query);
		System.out.println("Executing query: "+query);
		stmt.close();
		connection.close();
	}

	/**
	 * Returns all tickets changed to 'status' since 'milliseconds' and associated with 'employee'
	 * @author Adam Claxton 
	 */
	public static Ticket[] getRecentlyChangedTickets(int userID, String status, long milliseconds) throws SQLException, ClassNotFoundException {
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connect = DriverManager.getConnection(database, user, password);
		Statement stmt = connect.createStatement();
		String query= "SELECT ticket.*, employee.Name AS 'username', devices.Device_Name, location.Name AS 'locationname' "
					  +"FROM ticket INNER JOIN employee ON ticket.Requestor = employee.Employee_ID "
					  +"INNER JOIN devices ON ticket.Device_ID = devices.Device_ID "
					  +"INNER JOIN location ON ticket.Location = location.Location_ID "
					  +"WHERE ticket.Status = '"+status+"' AND Requestor = " + userID 
					  +" AND Status_Date_Fields >= " + milliseconds;
		ResultSet results = stmt.executeQuery(query);
		System.out.println("Executing query: "+query);
		results.last();
		int rows = results.getRow();
		results.beforeFirst();
		Ticket[] tickets = new Ticket[rows];
		int i=0;
		while(results.next())
		{
			tickets[i]=new Ticket(	results.getInt("Ticket_ID"),
									results.getInt("Requestor"),
									results.getLong("Request_Date"),
									results.getInt("Location"),
									results.getInt("Device_ID"),
									results.getString("Status"),
									results.getLong("Status_Date_Fields"),
									results.getString("Return_Date"),
									results.getString("username"),
									results.getString("Device_Name"),
									results.getString("locationname"),
									results.getInt("Permanent_Order")
									);
			i++;
		}
		stmt.close();
		connect.close();
		return tickets;
	}
	
	/**
	 * Gets the SSO number from a certain ticket. This is to support sending an email to an employee
	 * when an action is changed on a ticket.
	 * @author Adam Claxton
	 */
	public static int getUserID(int ticketID) throws ClassNotFoundException, SQLException, InterruptedException{
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection;
		Statement stmt;
		for(;;){
			try{
				connection = DriverManager.getConnection(database, user, password);
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		for(;;){
			try{
				stmt = connection.createStatement();
				break;
			}
			catch(SQLException e){
				Thread.sleep(1);
			}
		}
		String query = "SELECT Requestor FROM ticket WHERE Ticket_ID = "+ticketID+";";
		ResultSet results = stmt.executeQuery(query);
		System.out.println("Executing query: "+query);
		results.next();
		int answer = results.getInt("Requestor");
		stmt.close();
		connection.close();
		return answer;
	}

	public static Object[] getRecentClientActivity() throws InterruptedException, ClassNotFoundException, SQLException {
		// This method will be called by several threads simultaneously so I'm trying to stagger them a bit to reduce db load
		Thread.sleep((int)Math.random()*15000);
		System.getenv("VCAP_SERVICES");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connect = DriverManager.getConnection(database, user, password);
		Statement stmt = connect.createStatement();
		String query ="SELECT ticket.*, employee.Name AS 'username', devices.Device_Name, devices.Status AS 'deviceStatus', location.Name AS 'locationname' "
					+ "FROM ticket INNER JOIN employee ON ticket.Requestor = employee.Employee_ID "
					+ "INNER JOIN devices ON ticket.Device_ID = devices.Device_ID "
					+ "INNER JOIN location ON ticket.Location = location.Location_ID "
					+ "WHERE ticket.Status='Requested' OR devices.Status='Returning'";
		System.out.println("Executing query: "+query);
		ResultSet results = stmt.executeQuery(query);
		results.last();
		Ticket[] allTicketAcivity = new Ticket[results.getRow()];
		results.beforeFirst();
		while(results.next())
		{
			allTicketAcivity[results.getRow()-1] = new Ticket(
						results.getInt("Ticket_ID"),
						results.getInt("Requestor"),
						results.getLong("Request_Date"),
						results.getInt("Location"),
						results.getInt("Device_ID"),
						results.getString("Status"),
						results.getLong("Status_Date_Fields"),
						results.getString("Return_Date"),
						results.getString("username"),
						results.getString("Device_Name"),
						results.getString("locationname"),
						results.getInt("Permanent_Order")
				);
		}
		stmt.close();
		connect.close();
		int requestCount = 0;
		for(Ticket tic : allTicketAcivity) {
			if(tic.getStatus().equals("Requested"))
				requestCount++;
		}
		Ticket[] requests = new Ticket[requestCount];
		Ticket[] returns = new Ticket[allTicketAcivity.length-requestCount];
		requestCount=0;
		int returnCount=0;
		for(Ticket t : allTicketAcivity)
		{
			if(t.getStatus().equals("Requested")) requests[requestCount++] = t;
			else returns[returnCount++] = t;
		}
		Object[] finalArray = {requests,returns};
		return finalArray;
	}
}