www.pudn.com > aaa.zip > Booking.java
import java.io.*;
import java.util.*;
import java.sql.*;
import java.sql.Date;
import java.text.*;
class Admin_Database {
Connection con = null;
Statement stmt = null;
public Admin_Database() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@db00.cse.cuhk.edu.hk:1521:db00", "db037", "a123b123"); // may not work
stmt = con.createStatement();
}
catch(ClassNotFoundException e) {
System.out.println("Class not found: " + e);
} catch(SQLException e) {
System.out.println("SQL Exception: " + e);
}
}
public void create() {
try {
stmt.executeUpdate("CREATE TABLE Flight (FlightID VARCHAR(10), Airline VARCHAR(200), DepartCity VARCHAR(100), ArrivalCity VARCHAR(100), DepartTime DATE, ArrivalTime DATE, Price FLOAT, No_Of_Seats INT, PRIMARY KEY(FlightID))");
stmt.executeUpdate("CREATE TABLE Booking (BookingID VARCHAR(10), PassengerName VARCHAR(100), FromCity VARCHAR(100), ToCity VARCHAR(100), DepartAfter DATE, ArrivalBefore DATE, PassengerPhone VARCHAR(20), CreditCardNo VARCHAR(50), PRIMARY KEY(BookingID))");
stmt.executeUpdate("CREATE TABLE Ticket (BookingID VARCHAR(10), FlightID VARCHAR(10), IssueDate DATE, Fee FLOAT, PRIMARY KEY(FlightID, BookingID), FOREIGN KEY(FlightID) references Flight(FlightID), FOREIGN KEY(BookingID) references Booking(BookingID))");
System.out.println("Tables are created successfully!!!");
} catch (SQLException e){
System.out.println("SQL Exception: " + e);
System.out.println("NO tables are created!");
}
}
public void insert() throws Exception {
PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO Flight VALUES (?, ?, ?, ?, to_date(?, 'yyyy-mm-dd hh24:mi'), to_date(?, 'yyyy-mm-dd hh24:mi'), ?, ?)");
String[] ss1=new String[10];
try{
FileReader fr1 = new FileReader("Flights.txt");
BufferedReader br2 = new BufferedReader(fr1);
String line = br2.readLine();
while(line != null){
ss1 = line.split("\t", 8);
pstmt1.setString(1, ss1[0]);
pstmt1.setString(2, ss1[1]);
pstmt1.setString(3, ss1[2]);
pstmt1.setString(4, ss1[3]);
pstmt1.setString(5, ss1[4]);
pstmt1.setString(6, ss1[5]);
pstmt1.setFloat(7, Float.parseFloat(ss1[6]));
pstmt1.setInt(8, Integer.parseInt(ss1[7]));
pstmt1.executeUpdate();
line = br2.readLine();
}
br2.close();
fr1.close();
pstmt1.close();
} catch (IOException e) {
System.out.println("IO Error:" + e.getMessage());
}
PreparedStatement pstmt2 = con.prepareStatement("INSERT INTO Booking VALUES (?, ?, ?, ?, to_date(?, 'yyyy-mm-dd hh24:mi'), to_date(?, 'yyyy-mm-dd hh24:mi'), ?, ?)");
String[] ss2=new String[10];
try{
FileReader fr2 = new FileReader("Bookings.txt");
BufferedReader br2 = new BufferedReader(fr2);
String line = br2.readLine();
while(line != null){
ss2 = line.split("\t", 8);
pstmt2.setString(1, ss2[0]);
pstmt2.setString(2, ss2[1]);
pstmt2.setString(3, ss2[2]);
pstmt2.setString(4, ss2[3]);
pstmt2.setString(5, ss2[4]);
pstmt2.setString(6, ss2[5]);
pstmt2.setString(7, ss2[6]);
pstmt2.setString(8, ss2[7]);
pstmt2.executeUpdate();
line = br2.readLine();
}
br2.close();
fr2.close();
pstmt2.close();
} catch (IOException e) {
System.out.println("IO Error:" + e.getMessage());
}
PreparedStatement pstmt3 = con.prepareStatement("INSERT INTO Ticket VALUES (?, ?, to_date(?, 'yyyy-mm-dd hh24:mi'), ?)");
String[] ss3=new String[10];
try{
FileReader fr3 = new FileReader("Books.txt");
BufferedReader br3 = new BufferedReader(fr3);
String line = br3.readLine();
while(line != null){
ss3 = line.split("\t", 4);
pstmt3.setString(1, ss3[0]);
pstmt3.setString(2, ss3[1]);
pstmt3.setString(3, ss3[2]);
pstmt3.setFloat(4, Float.parseFloat(ss3[3]));
pstmt3.executeUpdate();
line = br3.readLine();
}
br3.close();
fr3.close();
pstmt3.close();
} catch (IOException e) {
System.out.println("IO Error:" + e.getMessage());
}
}
public void drop() {
try {
stmt.executeUpdate("DROP TABLE Ticket");
stmt.executeUpdate("DROP TABLE Booking");
stmt.executeUpdate("DROP TABLE Flight");
System.out.println("Tables are dropped successfully!!!");
}
catch (SQLException e) {
System.out.println("SQL Exception: " + e);
System.out.println("NO tables are dropped!");
}
}
public ResultSet exeQuery(String Query) throws SQLException {
ResultSet rs=null;
rs=stmt.executeQuery(Query);
return rs;
}
public void closeConn() {
try {
stmt.close();
con.close();
} catch(SQLException e) {
System.out.println("SQL Exception");
}
}
}
public class Booking
{
public static void main(String[] args) throws SQLException
{
Admin_Database Admin_Database = new Admin_Database();
Scanner in = new Scanner(System.in);
for(;;) {
System.out.println();
System.out.print("Welcome to our travel agency!!!\n");
System.out.print("1. Administrator Interface\n");
System.out.print("2. Booking Interface\n");
System.out.print("3. Evaluation Interface\n");
System.out.print("Please select the interface you need: ");
int num = in.nextInt();
if (num==1) {
for(;;) {
System.out.println();
System.out.print("Welcome to Administrator Interface!!!\n");
System.out.print("1. Create table schemas in the database\n");
System.out.print("2. Delete table schemas in the database\n");
System.out.print("3. Return to the last selection page\n");
System.out.print("Please select your choice: ");
int num1 = in.nextInt();
if (num1==1) {
for(;;) {
System.out.println();
System.out.print("1. Create table schemas in the database\n");
System.out.print("2. Insert data into table schemas in the database\n");
System.out.print("3. Return to the last selection page\n");
System.out.print("Please select your choice: ");
int num11 = in.nextInt();
if (num11==1)
Admin_Database.create();
if (num11==2) {
try {
Admin_Database.insert();
System.out.println("Data are inserted successfully!!!");
} catch (Exception e) {
System.out.println("IO Error:" + e.getMessage());
System.out.println("No data inserted into tables!");
}
}
if (num11==3)
break;
else {
System.out.print("You entered a wrong number!\n");
continue;
}
}
}
if (num1==2) {
Admin_Database.drop();
}
if (num1==3)
break;
}
}
if (num==2){
Scanner in2 = new Scanner(System.in);
System.out.println();
System.out.print("Welcome to the booking system!!!\n");
System.out.print("Please enter the names of places and time duration of the flight you wanted:\n");
System.out.print("From City: ");
String DepartCity = in2.nextLine();
System.out.print("To City: ");
String ArrivalCity = in2.nextLine();
System.out.print("Depart After(YYYY-MM-DD HH:mm): ");
String DepartTime = in2.nextLine();
System.out.print("Arrival Before(YYYY-MM-DD HH:mm): ");
String ArrivalTime = in2.nextLine();
System.out.println();
System.out.println("1. Table of itinerary sorted by fee");
System.out.println("2. Table of itinerary sorted by duration");
System.out.print("Please select one of the sorted tables of itinerary above: ");
int num2 = in.nextInt();
int ino=0;
if (num2==1) {
try {
String query = ("SELECT FlightID, FlightID2, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, price , (ArrivalTime-departtime)*24 as duration from(" +
"SELECT A.FlightID, B.FlightID as FlightID2, A.DepartCity, A.ArrivalCity, A.DepartTime, A.ArrivalTime, A.price" +
" FROM Flight A, Flight B WHERE A.DepartCity = '" + DepartCity + "' AND A.ArrivalCity = '" + ArrivalCity+"' AND A.DepartTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('"+ ArrivalTime + "', 'yyyy-mm-dd hh24:mi') AND A.ArrivalTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('"+ ArrivalTime + "', 'yyyy-mm-dd hh24:mi') " +
"AND A.FlightID = B.FlightID UNION \n" +
"SELECT A.FlightID, B.FlightID as FlightID2, A.DepartCity, B.ArrivalCity, A.DepartTime, \n"+
"B.ArrivalTime, (A.price + B.price) as price FROM Flight A, Flight B WHERE A.Arrivalcity = B.DepartCity \n" +
"AND A.DepartCity = '" + DepartCity + "' AND B.ArrivalCity = '" + ArrivalCity +"' AND A.DepartTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('" + ArrivalTime + "', 'yyyy-mm-dd hh24:mi')" +
"AND B.ArrivalTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('" + ArrivalTime + "', 'yyyy-mm-dd hh24:mi')" +
"AND A.ArrivalTime Between A.Departtime AND B.Departtime) ORDER BY PRICE");
ResultSet rs = Admin_Database.exeQuery(query);
System.out.println();
while (rs.next()) {ino++;
if (rs.getInt(1) == rs.getInt(2)) {
System.out.print("Itinerary " + ino);
System.out.print(" (" + rs.getString(5));
System.out.print(" - " + rs.getString(6));
System.out.println(", Fee: $" + rs.getInt(7) + ", Duration: " + rs.getInt(8) + "hrs)");
System.out.print("FlightID " + rs.getInt(1));
System.out.println(" (" + rs.getString(3) + " - " + rs.getString(4) + ")");
single(rs.getInt(1));
}
else {
System.out.print("Itinerary " + ino);
System.out.print(" (" + rs.getString(5));
System.out.print(" - " + rs.getString(6));
System.out.println(", Fee: $" + rs.getInt(7) + ", Duration: " + rs.getInt(8) + "hrs)");
transit(rs.getInt(1), 1);
System.out.println();
transit(rs.getInt(2), 2);
}
System.out.println();
System.out.println();
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
}
if (num2==2) {
try {
String query4 = ("SELECT FlightID, FlightID2, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, price , (ArrivalTime-departtime)*24 as duration from(" +
"SELECT A.FlightID, B.FlightID as FlightID2, A.DepartCity, A.ArrivalCity, A.DepartTime, A.ArrivalTime, A.price" +
" FROM Flight A, Flight B WHERE A.DepartCity = '" + DepartCity + "' AND A.ArrivalCity = '" + ArrivalCity+"' AND A.DepartTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('"+ ArrivalTime + "', 'yyyy-mm-dd hh24:mi') AND A.ArrivalTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('"+ ArrivalTime + "', 'yyyy-mm-dd hh24:mi') " +
"AND A.FlightID = B.FlightID UNION \n" +
"SELECT A.FlightID, B.FlightID as FlightID2, A.DepartCity, B.ArrivalCity, A.DepartTime, \n"+
"B.ArrivalTime, (A.price + B.price) as price FROM Flight A, Flight B WHERE A.Arrivalcity = B.DepartCity \n" +
"AND A.DepartCity = '" + DepartCity + "' AND B.ArrivalCity = '" + ArrivalCity +"' AND A.DepartTime between to_date('" + DepartTime + "', 'yyyy-mm-dd hh24:mi') AND to_date('" + ArrivalTime + "', 'yyyy-mm-dd hh24:mi')" +
"AND B.ArrivalTime between to_date('2007-12-01 16:00', 'yyyy-mm-dd hh24:mi') AND to_date('2007-12-03 16:00', 'yyyy-mm-dd hh24:mi')" +
"AND A.ArrivalTime Between A.Departtime AND B.Departtime) ORDER BY duration");
ResultSet rs4 = Admin_Database.exeQuery(query4);
System.out.println();
while (rs4.next()) {
if (rs4.getInt(1) == rs4.getInt(2)) {
System.out.print("Itinerary " + ino);
System.out.print(" (" + rs4.getString(5));
System.out.print(" - " + rs4.getString(6));
System.out.println(", Fee: $" + rs4.getInt(7) + ", Duration: " + rs4.getInt(8) + "hrs)");
System.out.print("FlightID " + rs4.getInt(1));
System.out.println(" (" + rs4.getString(3) + " - " + rs4.getString(4) + ")");
single(rs4.getInt(1));
}
else {
System.out.print("Itinerary " + ino);
System.out.print(" (" + rs4.getString(5));
System.out.print(" - " + rs4.getString(6));
System.out.println(", Fee: $" + rs4.getInt(7) + ", Duration: " + rs4.getInt(8) + "hrs)");
transit(rs4.getInt(1), 1);
System.out.println();
transit(rs4.getInt(2), 2);
}
System.out.println();
System.out.println();
ino++;
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
}
if (ino!=1) {
Scanner in3 = new Scanner(System.in);
System.out.println();
System.out.print("Create booking (1:yes 2:no)? ");
int ans = in3.nextInt();
if (ans==1) {
Scanner in5 = new Scanner(System.in);
System.out.println();
System.out.print("Please enter your personal information:\n");
System.out.print("Name: ");
String pname = in5.next();
System.out.print("Phone No: ");
String pphoneno = in5.next();
System.out.print("Credit Card No: ");
String pcardno = in5.next();
System.out.print("Depart City: ");
String enter = in5.nextLine();
String FromCity = in5.nextLine();
System.out.print("Arrival City: ");
String ToCity = in5.nextLine();
System.out.print("Depart After(YYYY-MM-DD HH:mm): ");
String DepartAfter = in5.nextLine();
System.out.print("Arrive Before(YYYY-MM-DD HH:mm): ");
String ArrivalBefore = in5.nextLine();
int i=1;
try {
ResultSet rs5 = Admin_Database.exeQuery("SELECT BookingID FROM Booking");
while (rs5.next()) {
i++;
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
String BookingID = String.valueOf(i);
PreparedStatement pstmt = Admin_Database.con.prepareStatement("INSERT INTO Booking VALUES (?, ?, ?, ?, to_date(?, 'yyyy-mm-dd hh24:mi'), to_date(?, 'yyyy-mm-dd hh24:mi'), ?, ?)");
pstmt.setString(1, BookingID);
pstmt.setString(2, pname);
pstmt.setString(3, FromCity);
pstmt.setString(4, ToCity);
pstmt.setString(5, DepartAfter);
pstmt.setString(6, ArrivalBefore);
pstmt.setString(7, pphoneno);
pstmt.setString(8, pcardno);
pstmt.executeUpdate();
pstmt.close();
System.out.println("Your Booking ID is " + BookingID);
} else {
}
Scanner in4 = new Scanner(System.in);
System.out.println();
System.out.print("Issue tickets (1:yes 2:no)? ");
int ans2 = in4.nextInt();
if (ans2==1) {
System.out.println("1. Direct Flight");
System.out.println("2. Transit Flight");
System.out.print("Please select your choice: ");
int num22 = in4.nextInt();
if (num22==1) {
System.out.print("Please enter the following information:\n");
System.out.print("Booking ID: ");
String BookingID = in4.next();
System.out.print("Flight ID: ");
String FlightID = in4.next();
int seatno = 0;
String fee = null;
try {
ResultSet rs6 = Admin_Database.exeQuery("SELECT No_of_Seats, Price FROM Flight WHERE FlightID =" + FlightID);
while (rs6.next())
{
seatno = rs6.getInt(1);
fee = rs6.getString(2);
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
if (seatno > 0) {
System.out.println();
System.out.println("A ticket is issued!");
System.out.println("Ticket: ");
System.out.println("Booking ID: " + BookingID);
PreparedStatement pstmt2 = Admin_Database.con.prepareStatement("INSERT INTO Ticket VALUES (?, ?, SYSDATE, ?)");
pstmt2.setString(1, BookingID);
pstmt2.setString(2, FlightID);
pstmt2.setString(3, fee);
pstmt2.executeUpdate();
pstmt2.close();
PreparedStatement pstmt3 = Admin_Database.con.prepareStatement("UPDATE Flight SET No_of_Seats = No_of_Seats - 1 WHERE FlightID = ?");
pstmt3.setString(1, FlightID);
pstmt3.executeUpdate();
pstmt3.close();
try{
String query5 = "SELECT FlightID, Airline, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, " +
"Price, PassengerName, PassengerPhone, CreditCardNo FROM Flight, Booking WHERE FlightID = " + FlightID + " AND BookingID =" + BookingID;
ResultSet rs7 = Admin_Database.exeQuery(query5);
while (rs7.next())
{
System.out.println("FlightID: " + rs7.getString(1));
System.out.println("Airline: " + rs7.getString(2));
System.out.println("From City: " + rs7.getString(3));
System.out.println("To City: " + rs7.getString(4));
System.out.println("Depart After: " + rs7.getString(5));
System.out.println("Arrival Before: " + rs7.getString(6));
System.out.println("Fee: $" + rs7.getString(7));
System.out.println("Passenger name: " + rs7.getString(8));
System.out.println("Passenger phone no.: " + rs7.getString(9));
System.out.println("Passenger cerdit card no.: " + rs7.getString(10));
System.out.println();
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
} else
{
System.out.println();
System.out.println("Ticket is not issued!");
System.out.println("There is not enough seats in Flight " + FlightID);
System.out.println("Please select the other Flight");
System.out.println();
}
}
if (num22==2){
System.out.println();
System.out.print("Please enter the following information:\n");
System.out.print("Booking ID: ");
String BookingID = in4.next();
System.out.print("1st Flight ID: ");
String FlightID1 = in4.next();
System.out.print("2nd Flight ID: ");
String FlightID2 = in4.next();
int seatno1 = 0;
int seatno2 = 0;
String fee1 = null;
String fee2 = null;
try {
ResultSet rs8 = Admin_Database.exeQuery("SELECT No_of_Seats, price FROM Flight WHERE FlightID =" + FlightID1);
while (rs8.next())
{
seatno1 = rs8.getInt(1);
fee1 = rs8.getString(2);
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
try {
ResultSet rs8 = Admin_Database.exeQuery("SELECT No_of_Seats, price FROM Flight WHERE FlightID =" + FlightID2);
while (rs8.next())
{
seatno2 = rs8.getInt(1);
fee2 = rs8.getString(2);
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
if (seatno1 > 0 && seatno2 > 0)
{
System.out.println();
System.out.println("A ticket is issued!");
System.out.println("Ticket 1: ");
System.out.println("Booking ID: " + BookingID);
PreparedStatement pstmt2 = Admin_Database.con.prepareStatement("INSERT INTO Ticket VALUES (?, ?, SYSDATE, ?)");
pstmt2.setString(1, BookingID);
pstmt2.setString(2, FlightID1);
pstmt2.setString(3, fee1);
pstmt2.executeUpdate();
pstmt2.close();
PreparedStatement pstmt3 = Admin_Database.con.prepareStatement("UPDATE Flight SET No_of_Seats = No_of_Seats - 1 WHERE FlightID = ?");
pstmt3.setString(1, FlightID1);
pstmt3.executeUpdate();
pstmt3.close();
try{
String query5 = "SELECT FlightID, Airline, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, " +
"Price, PassengerName, PassengerPhone, CreditCardNo FROM Flight, Booking WHERE FlightID = " + FlightID1 + " AND BookingID =" + BookingID;
ResultSet rs7 = Admin_Database.exeQuery(query5);
while (rs7.next())
{
System.out.println("FlightID: " + rs7.getString(1));
System.out.println("Airline: " + rs7.getString(2));
System.out.println("From City: " + rs7.getString(3));
System.out.println("To City: " + rs7.getString(4));
System.out.println("Depart After: " + rs7.getString(5));
System.out.println("Arrival Before: " + rs7.getString(6));
System.out.println("Fee: $" + rs7.getString(7));
System.out.println("Passenger name: " + rs7.getString(8));
System.out.println("Passenger phone no.: " + rs7.getString(9));
System.out.println("Passenger cerdit card no.: " + rs7.getString(10));
System.out.println();
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
System.out.println("Ticket 2: ");
System.out.println("Booking ID: " + BookingID);
PreparedStatement pstmt4 = Admin_Database.con.prepareStatement("INSERT INTO Ticket VALUES (?, ?, SYSDATE, ?)");
pstmt2.setString(1, BookingID);
pstmt2.setString(2, FlightID2);
pstmt2.setString(3, fee2);
pstmt2.executeUpdate();
pstmt2.close();
PreparedStatement pstmt5 = Admin_Database.con.prepareStatement("UPDATE Flight SET No_of_Seats = No_of_Seats - 1 WHERE FlightID = ?");
pstmt3.setString(1, FlightID2);
pstmt3.executeUpdate();
pstmt3.close();
try{
String query5 = "SELECT FlightID, Airline, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, " +
"Price, PassengerName, PassengerPhone, CreditCardNo FROM Flight, Booking WHERE FlightID = " + FlightID2 + " AND BookingID =" + BookingID;
ResultSet rs7 = Admin_Database.exeQuery(query5);
while (rs7.next())
{
System.out.println("FlightID: " + rs7.getString(1));
System.out.println("Airline: " + rs7.getString(2));
System.out.println("From City: " + rs7.getString(3));
System.out.println("To City: " + rs7.getString(4));
System.out.println("Depart After: " + rs7.getString(5));
System.out.println("Arrival Before: " + rs7.getString(6));
System.out.println("Fee: $" + rs7.getString(7));
System.out.println("Passenger name: " + rs7.getString(8));
System.out.println("Passenger phone no.: " + rs7.getString(9));
System.out.println("Passenger cerdit card no.: " + rs7.getString(10));
System.out.println();
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
}
else if (seatno1 <= 0)
{
System.out.println();
System.out.println("Ticket is not issued!");
System.out.println("There is not enough seats of Flight" + FlightID1);
System.out.println("Please select another Flight");
System.out.println();
}
else if (seatno2 <= 0)
{
System.out.println();
System.out.println("Ticket is not issued!");
System.out.println("There is not enough seats of Flight" + FlightID2);
System.out.println("Please select another Flight");
System.out.println();
}
}
}
}else {
System.out.println();
System.out.println("There are no valid itinerary for your requirement!");
}
}
if (num==3) {
System.out.println();
System.out.print("Welcome to Evaluation Interface!!!\n");
System.out.print("1. Show all information in the database tables\n");
System.out.print("2. Show the total sales within a period\n");
System.out.print("3. Show the N most popular cities according to their total sales\n");
System.out.print("Please select your choice: ");
int num3 = in.nextInt();
switch (num3) {
case 1:
System.out.println();
System.out.println("Here are the 3 tables:");
System.out.println("1. Flight");
System.out.println("2. Booking");
System.out.println("3. Ticket");
System.out.print("Please choose the table which you want to show: ");
Scanner tablechoice = new Scanner(System.in);
int table_choice = tablechoice.nextInt();
switch (table_choice) {
case 1:
System.out.println("FilghtID Airline DepartCity ArrivalCity");
System.out.println("DepartTime ArrivalTIme Price No_Of_Seats");
System.out.println("----------------------------------------------");
ResultSet flight = Admin_Database.exeQuery("SELECT FlightID, Airline, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, Price, No_Of_Seats FROM Flight");
while (flight.next())
{
System.out.println(flight.getString(1) + " " + flight.getString(2) + " " + flight.getString(3) + " " + flight.getString(4) + "\n" + flight.getString(5) + " " + flight.getString(6) + " " + flight.getFloat(7) + " " + flight.getInt(8));
}
flight.close();
break;
case 2:
System.out.println("BookingID PassengerName FromCity ToCity");
System.out.println("DepartAfter ArrivalBefore PassengerPhone CreditCardNo");
System.out.println("-----------------------------------------------------------");
ResultSet booking = Admin_Database.exeQuery("SELECT BookingID, PassengerName, FromCity, ToCity, to_char(DepartAfter, ('yyyy-mm-dd hh24:mi')) as DepartAfter, to_char(ArrivalBefore, ('yyyy-mm-dd hh24:mi')) as ArrivalBefore, PassengerName, CreditCardNo FROM Booking");
while (booking.next())
{
System.out.println(booking.getString(1) + " " + booking.getString(2) + " " + booking.getString(3) + " " + booking.getString(4) + "\n" + booking.getString(5) + " " + booking.getString(6) + " " + booking.getString(7) + " " + booking.getString(8));
}
booking.close();
break;
case 3:
System.out.println("FlightID BookingID IssueDate Fee");
System.out.println("--------------------------------------");
ResultSet ticket = Admin_Database.exeQuery("SELECT FlightID, BookingID, to_char(IssueDate, ('yyyy-mm-dd hh24:mi')) as IssueDate, Fee FROM Ticket");
while (ticket.next())
{
System.out.println(ticket.getString(1) + " " + ticket.getString(2) + " " + ticket.getString(3) + " " + ticket.getFloat(4));
}
ticket.close();
}
break;
case 2:
System.out.println("Which period?");
System.out.print("From (YYYY-MM-DD): ");
Scanner date = new Scanner(System.in);
String from_date = date.next();
Date fromdate = Date.valueOf(from_date);
System.out.print("To (YYYY-MM-DD): ");
String to_date = date.next();
System.out.print("Total sale ($) from "+from_date+" to "+to_date+ ":\n");
Date todate = Date.valueOf(to_date);
long thisday = todate.getTime();
long nextday = thisday + Long.parseLong("1") * 24 * 3600 * 1000;
Date thenextday = new Date(nextday);
SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
String thenext = dateformat.format(thenextday);
Date finaldate = Date.valueOf(thenext);
ResultSet totalsales = Admin_Database.exeQuery("SELECT SUM(Fee) FROM Ticket WHERE IssueDate between to_date('" + fromdate + "', 'yyyy-mm-dd') AND to_date('"+ finaldate + "', 'yyyy-mm-dd')");
while (totalsales.next())
{
System.out.println(totalsales.getFloat(1));
}
totalsales.close();
break;
case 3:
System.out.println("How many popular city do you want to see?");
Scanner popular = new Scanner(System.in);
int popularcity = popular.nextInt();
String[] city=new String[10];
Float[] sale=new Float[10];
int i = 0;
ResultSet popularcom = Admin_Database.exeQuery("SELECT Booking.ToCity, SUM(Ticket.Fee) as total FROM Booking, Ticket WHERE Booking.BookingID = Ticket.BookingID Group by Booking.ToCity ORDER BY total DESC");
while (popularcom.next())
{
if (i < popularcity)
{
i++;
city[i] = popularcom.getString(1);
sale[i] = popularcom.getFloat(2);
System.out.println(i + ". " + city[i] + " with a total sales of $" + sale[i]);
}
}
popularcom.close();
}
}
}
}
public static void single(int i)
{
Admin_Database Admin_Database = new Admin_Database();
try {
String query2 = "SELECT FlightID, Airline, DepartCity, ArrivalCity, " +
"to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, " +
"Price, No_Of_Seats FROM Flight WHERE FlightID = " + i;
ResultSet rs2 = Admin_Database.exeQuery(query2);
while (rs2.next()) {
System.out.println("FlightID " + rs2.getString(1) + "'s Info:");
System.out.println("Airline: " + rs2.getString(2));
System.out.println("DepartCity: " + rs2.getString(3) + "\t\t ArrivalCity: " + rs2.getString(4));
System.out.println("DepartTime: " + rs2.getString(5) + "\t ArrivalTime: " + rs2.getString(6));
System.out.println("Price: $" + rs2.getFloat(7) + "\t\t No of Seats available: " + rs2.getInt(8));
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
}
public static void transit(int i, int j)
{
Admin_Database Admin_Database = new Admin_Database();
try {
String query3 = "SELECT FlightID, Airline, DepartCity, ArrivalCity, " +
"to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime, to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, " +
"Price, No_Of_Seats FROM Flight WHERE FlightID = " + i;
ResultSet rs3 = Admin_Database.exeQuery(query3);
while (rs3.next()) {
if (j == 1) {
System.out.print("1st FlightID " + rs3.getInt(1));
}else if (j == 2) {
System.out.print("2nd FlightID " + rs3.getInt(1));
}
System.out.println(" (" + rs3.getString(3) + " - " + rs3.getString(4) +")");
System.out.println("FlightID "+ rs3.getString(1) + "'s Info: ");
System.out.println("Airline: " + rs3.getString(2));
System.out.println("DepartCity: " + rs3.getString(3) + "\t\t ArrivalCity: " + rs3.getString(4));
System.out.println("DepartTime: " + rs3.getString(5) + "\t ArrivalTime: " + rs3.getString(6));
System.out.println("Price: $" + rs3.getFloat(7) + " \t\t No of Seats available: " + rs3.getInt(8));
}
} catch(SQLException e) {
System.out.println("SQL Exception\n"+e);
}
}
}