booking.java

oracle data base booking system (JAVA)

							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);
        }
	}
}