/****************************
 * PROJECT NAME: JdbcEx1.java
 * PURPOSE:
 * CODER: David Birnie
 * DATE: Mar 29, 2016
 ****************************
 */
package exercises;
import java.util.*;
import java.sql.*;
import java.text.DecimalFormat;


public class JdbcEx2
{
	static Connection conn = null;
	static String dbURL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
	static String user = "projects";
	static String password = "projects";
	static Statement stmt = null;

	public JdbcEx2()
	{
	}

	public static void main (String args[]) throws SQLException
	{
		Scanner input = new Scanner(System.in);
		DecimalFormat moneyFormat = new DecimalFormat("$###,###,##0.00 ");
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch (ClassNotFoundException e)
		{
			System.err.println(e.getMessage());
		}

		/*Open and Close
		 * Connection
		 */

		try
		{
			conn = DriverManager.getConnection(dbURL,user,password);
			conn.clearWarnings();
			System.out.println("Connection Opened! For driver ==> Oracle 11XE");
			stmt = conn.createStatement();
			String userInput;
		for(;;)
			{
				System.out.print("\n\n" + "Please enter 1 to print VQ1, 2 to print VQ2 or X to exit the program: ");
				userInput = input.nextLine();

				if(userInput.equals("1"))
				{
					ResultSet rs = stmt.executeQuery("select * from VQ1");
					System.out.println("\n*********David Birnie's Lab**********\n");
					System.out.printf("%10s\t%34s\t%s\n", "PRODUCT ID", "PRODUCTNAME","UNITPRICE");
					System.out.printf("%10s\t%34s\t%s\n","----------","------------------------", "----------");
					while (rs.next())
					{
					System.out.printf("%10s\t%34s\t%s\n", rs.getString("productid"),
							rs.getString("productname"), moneyFormat.format(Double.parseDouble(rs.getString("unitprice"))));
					}
					rs.close();
				}

				else if (userInput.equals("2"))
				{
					ResultSet rs2 = stmt.executeQuery("select * from VQ2");
					System.out.println("\n*********David Birnie's Lab**********\n");
					System.out.printf("%10s\t%34s\t%s\n", "ORDERID", "CUSTOMERID", "COMPANY NAME");
					System.out.printf("%10s\t%34s\t%s\n","----------","------------------------", "----------");
					while (rs2.next())
					{
						System.out.printf("%10s\t%34s\t%s\n", rs2.getString("orderid"), rs2.getDate("orderdate"), rs2.getString("companyname"));

					}
					rs2.close();
				}
				else if (userInput.equals("X") || (userInput.equals("x")))
				{
					System.out.println("'X' Entered, Terminating program... \n\n");
					break;
				}
				else
				{
					System.out.println("Incorrect input, please try again!");
				}
			}
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
		finally
		{
			if (!conn.isClosed())
			{
				conn.close();
				System.out.println("Connection Closed! Oracle");
			}
		}
	}
}