import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.*;

public class Practicum
{

	//for keyboard input
	static BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
	static String custID = "";
	static String dbURL		="jdbc:oracle:thin:@127.0.0.1:1521:XE";
	static String user 		="projects";
	static String password	="projects";
	static Connection conn	=null;
	static Statement stmt = null;

	public static void main(String[] args) throws SQLException
	{
		/*
		 * Register the driver with the Class manager
		 */
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		/* Open the connection */
		try
		{
			conn = DriverManager.getConnection(dbURL, user, password);
			conn.clearWarnings();
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}

		try
		{
			while(!custID.equalsIgnoreCase("X"))
			{
				System.out.print("Enter Customer ID: ");
				custID = br.readLine();
				System.out.println("\n");
				doQ2(conn);
				doQ1(conn);

			}
		}
			catch(Exception e)
			{
				System.err.println(e.getMessage());
			}
	}

	public static void doQ1(Connection conn)
	{
		try
		{
		conn = DriverManager.getConnection(dbURL, user, password);
		conn.clearWarnings();
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT o.orderid, o.orderdate, NVL2(o.shippeddate, 'Shipped', 'Not Shipped') AS \"Status\", TO_CHAR(SUM((od.unitprice - (od.unitprice * od.discount)) * od.quantity), '$9,999.99') AS \"Order Total\" FROM Orders o INNER JOIN OrderDetails od ON o.orderid = od.orderid WHERE o.customerid = '" +custID +"' GROUP BY o.orderid, o.orderdate, o.shippeddate, o.customerid ORDER BY 4 DESC");
		ResultSetMetaData meta = rs.getMetaData();
		System.out.printf("%1s %17s %15s %23s \n", meta.getColumnName(1), meta.getColumnName(2), meta.getColumnName(3), meta.getColumnName(4));
		while (rs.next())
			{
				System.out.printf("%1s %20s %15s %20s \n", rs.getString(1),rs.getDate(2),rs.getString(3), rs.getString(4));
			}
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}

	public static void doQ2(Connection conn)
	{
		try
		{
		conn = DriverManager.getConnection(dbURL, user, password);
		conn.clearWarnings();
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT CompanyName, (SELECT sysdate FROM dual) AS \"Date\" FROM Customers WHERE customerID='ERNSH'");
		while (rs.next())
			{
				System.out.println("Statistics for "+rs.getString(1)
						+ " "+rs.getDate(2) +" by Tiberiu Egyed");
			}
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}
}
//end of class