Guest User

cs166

a guest
Dec 6th, 2019
254
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  * Template JAVA User Interface
  3.  * =============================
  4.  *
  5.  * Database Management Systems
  6.  * Department of Computer Science & Engineering
  7.  * University of California - Riverside
  8.  *
  9.  * Target DBMS: 'Postgres'
  10.  *
  11.  */
  12.  
  13. import java.sql.DriverManager;
  14. import java.sql.Connection;
  15. import java.sql.Statement;
  16. import java.sql.ResultSet;
  17. import java.sql.ResultSetMetaData;
  18. import java.sql.SQLException;
  19. import java.io.File;
  20. import java.io.FileReader;
  21. import java.io.BufferedReader;
  22. import java.io.InputStreamReader;
  23. import java.util.Date;
  24. import java.util.Calendar;
  25. import java.text.SimpleDateFormat;
  26. import java.text.DateFormat;
  27.  
  28.  
  29. /**
  30.  * This class defines a simple embedded SQL utility class that is designed to
  31.  * work with PostgreSQL JDBC drivers.
  32.  *
  33.  */
  34. public class DBProject {
  35.  
  36.     // reference to physical database connection.
  37.     private Connection _connection = null;
  38.  
  39.     // handling the keyboard inputs through a BufferedReader
  40.     // This variable can be global for convenience.
  41.     static BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
  42.  
  43.     /**
  44.      * Creates a new instance of DBProject
  45.      *
  46.      * @param hostname the MySQL or PostgreSQL server hostname
  47.      * @param database the name of the database
  48.      * @param username the user name used to login to the database
  49.      * @param password the user login password
  50.      * @throws java.sql.SQLException when failed to make a connection.
  51.      */
  52.     public DBProject(String dbname, String dbport, String user, String passwd) throws SQLException {
  53.  
  54.         System.out.print("Connecting to database...");
  55.         try {
  56.             // constructs the connection URL
  57.             String url = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
  58.             System.out.println("Connection URL: " + url + "\n");
  59.  
  60.             // obtain a physical connection
  61.             this._connection = DriverManager.getConnection(url, user, passwd);
  62.             System.out.println("Done");
  63.         } catch (Exception e) {
  64.             System.err.println("Error - Unable to Connect to Database: " + e.getMessage());
  65.             System.out.println("Make sure you started postgres on this machine");
  66.             System.exit(-1);
  67.         } // end catch
  68.     }// end DBProject
  69.  
  70.     /**
  71.      * Method to execute an update SQL statement. Update SQL instructions includes
  72.      * CREATE, INSERT, UPDATE, DELETE, and DROP.
  73.      *
  74.      * @param sql the input SQL string
  75.      * @throws java.sql.SQLException when update failed
  76.      */
  77.     public void executeUpdate(String sql) throws SQLException {
  78.         // creates a statement object
  79.         Statement stmt = this._connection.createStatement();
  80.  
  81.         // issues the update instruction
  82.         stmt.executeUpdate(sql);
  83.  
  84.         // close the instruction
  85.         stmt.close();
  86.     }// end executeUpdate
  87.  
  88.     /**
  89.      * Method to execute an input query SQL instruction (i.e. SELECT). This method
  90.      * issues the query to the DBMS and outputs the results to standard out.
  91.      *
  92.      * @param query the input query string
  93.      * @return the number of rows returned
  94.      * @throws java.sql.SQLException when failed to execute the query
  95.      */
  96.     public int executeQuery(String query) throws SQLException {
  97.         // creates a statement object
  98.         Statement stmt = this._connection.createStatement();
  99.  
  100.         // issues the query instruction
  101.         ResultSet rs = stmt.executeQuery(query);
  102.  
  103.         /*
  104.          ** obtains the metadata object for the returned result set. The metadata
  105.          ** contains row and column info.
  106.          */
  107.         ResultSetMetaData rsmd = rs.getMetaData();
  108.         int numCol = rsmd.getColumnCount();
  109.         int rowCount = 0;
  110.  
  111.         // iterates through the result set and output them to standard out.
  112.         boolean outputHeader = true;
  113.         while (rs.next()) {
  114.             if (outputHeader) {
  115.                 for (int i = 1; i <= numCol; i++) {
  116.                     System.out.print(rsmd.getColumnName(i) + "\t");
  117.                 }
  118.                 System.out.println();
  119.                 outputHeader = false;
  120.             }
  121.             for (int i = 1; i <= numCol; ++i)
  122.                 System.out.print(rs.getString(i) + "\t");
  123.             System.out.println();
  124.             ++rowCount;
  125.         } // end while
  126.         stmt.close();
  127.         return rowCount;
  128.     }// end executeQuery
  129.  
  130.     /**
  131.      * Method to close the physical connection if it is open.
  132.      */
  133.     public void cleanup() {
  134.         try {
  135.             if (this._connection != null) {
  136.                 this._connection.close();
  137.             } // end if
  138.         } catch (SQLException e) {
  139.             // ignored.
  140.         } // end try
  141.     }// end cleanup
  142.  
  143.     /**
  144.      * The main execution method
  145.      *
  146.      * @param args the command line arguments this inclues the <mysql|pgsql> <login
  147.      *             file>
  148.      */
  149.     public static void main(String[] args) {
  150.         if (args.length != 3) {
  151.             System.err.println("Usage: " + "java [-classpath <classpath>] " + DBProject.class.getName()
  152.                     + " <dbname> <port> <user>");
  153.             return;
  154.         } // end if
  155.  
  156.         Greeting();
  157.         DBProject esql = null;
  158.         try {
  159.             // use postgres JDBC driver.
  160.             Class.forName ("org.postgresql.Driver");
  161.             // instantiate the DBProject object and creates a physical
  162.             // connection.
  163.             String dbname = args[0];
  164.             String dbport = args[1];
  165.             String user = args[2];
  166.             esql = new DBProject(dbname, dbport, user, "12345");
  167.  
  168.             boolean keepon = true;
  169.             while (keepon) {
  170.                 // These are sample SQL statements
  171.                 System.out.println("MAIN MENU");
  172.                 System.out.println("---------");
  173.                 System.out.println("1. Add new customer");
  174.                 System.out.println("2. Add new room");
  175.                 System.out.println("3. Add new maintenance company");
  176.                 System.out.println("4. Add new repair");
  177.                 System.out.println("5. Add new Booking");
  178.                 System.out.println("6. Assign house cleaning staff to a room");
  179.                 System.out.println("7. Raise a repair request");
  180.                 System.out.println("8. Get number of available rooms");
  181.                 System.out.println("9. Get number of booked rooms");
  182.                 System.out.println("10. Get hotel bookings for a week");
  183.                 System.out.println("11. Get top k rooms with highest price for a date range");
  184.                 System.out.println("12. Get top k highest booking price for a customer");
  185.                 System.out.println("13. Get customer total cost occurred for a give date range");
  186.                 System.out.println("14. List the repairs made by maintenance company");
  187.                 System.out.println("15. Get top k maintenance companies based on repair count");
  188.                 System.out.println("16. Get number of repairs occurred per year for a given hotel room");
  189.                 System.out.println("17. < EXIT");
  190.  
  191.                 switch (readChoice()) {
  192.                 case 1:
  193.                     addCustomer(esql);
  194.                     break;
  195.                 case 2:
  196.                     addRoom(esql);
  197.                     break;
  198.                 case 3:
  199.                     addMaintenanceCompany(esql);
  200.                     break;
  201.                 case 4:
  202.                     addRepair(esql);
  203.                     break;
  204.                 case 5:
  205.                     bookRoom(esql);
  206.                     break;
  207.                 case 6:
  208.                     assignHouseCleaningToRoom(esql);
  209.                     break;
  210.                 case 7:
  211.                     repairRequest(esql);
  212.                     break;
  213.                 case 8:
  214.                     numberOfAvailableRooms(esql);
  215.                     break;
  216.                 case 9:
  217.                     numberOfBookedRooms(esql);
  218.                     break;
  219.                 case 10:
  220.                     listHotelRoomBookingsForAWeek(esql);
  221.                     break;
  222.                 case 11:
  223.                     topKHighestRoomPriceForADateRange(esql);
  224.                     break;
  225.                 case 12:
  226.                     topKHighestPriceBookingsForACustomer(esql);
  227.                     break;
  228.                 case 13:
  229.                     totalCostForCustomer(esql);
  230.                     break;
  231.                 case 14:
  232.                     listRepairsMade(esql);
  233.                     break;
  234.                 case 15:
  235.                     topKMaintenanceCompany(esql);
  236.                     break;
  237.                 case 16:
  238.                     numberOfRepairsForEachRoomPerYear(esql);
  239.                     break;
  240.                 case 17:
  241.                     keepon = false;
  242.                     break;
  243.                 default:
  244.                     System.out.println("Unrecognized choice!");
  245.                     break;
  246.                 }// end switch
  247.             } // end while
  248.         } catch (Exception e) {
  249.             System.err.println(e.getMessage());
  250.         } finally {
  251.             // make sure to cleanup the created table and close the connection.
  252.             try {
  253.                 if (esql != null) {
  254.                     System.out.print("Disconnecting from database...");
  255.                     esql.cleanup();
  256.                     System.out.println("Done\n\nBye !");
  257.                 } // end if
  258.             } catch (Exception e) {
  259.                 // ignored.
  260.             } // end try
  261.         } // end try
  262.     }// end main
  263.  
  264.     public static void Greeting() {
  265.         System.out.println("\n\n*******************************************************\n"
  266.                 + "              User Interface                        \n"
  267.                 + "*******************************************************\n");
  268.     }// end Greeting
  269.  
  270.     /*
  271.      * Reads the users choice given from the keyboard
  272.      *
  273.      * @int
  274.      **/
  275.     public static int readChoice() {
  276.         int input;
  277.         // returns only if a correct value is given.
  278.         do {
  279.             System.out.print("Please make your choice: ");
  280.             try { // read the integer, parse it and break.
  281.                 input = Integer.parseInt(in.readLine());
  282.                 break;
  283.             } catch (Exception e) {
  284.                 System.out.println("Your input is invalid!");
  285.                 continue;
  286.             } // end try
  287.         } while (true);
  288.         return input;
  289.     }// end readChoice
  290.  
  291.     /*
  292.      * Reads the users input from the keyboard
  293.      *
  294.      * @String
  295.      **/
  296.     public static String readText() {
  297.         String input;
  298.         // returns only if a correct value is given.
  299.         do {
  300.             try { // read line
  301.                 input = in.readLine();
  302.                 break;
  303.             } catch (Exception e) {
  304.                 System.out.println("Your input is invalid!");
  305.                 continue;
  306.             } // end try
  307.         } while (true);
  308.         return input;
  309.     }// end readText
  310.  
  311.     public static int readInt() {
  312.         int input;
  313.         // returns only if a correct value is given.
  314.         do {
  315.             try { // read the integer, parse it and break.
  316.                 input = Integer.parseInt(in.readLine());
  317.                 break;
  318.             } catch (Exception e) {
  319.                 System.out.println("Your input is invalid!");
  320.                 continue;
  321.             } // end try
  322.         } while (true);
  323.         return input;
  324.     }// end readInt
  325.  
  326.     public static double readDouble() {
  327.         double input;
  328.         // returns only if a correct value is given.
  329.         do {
  330.             try { // read the integer, parse it and break.
  331.                 input = Double.parseDouble(in.readLine());
  332.                 break;
  333.             } catch (Exception e) {
  334.                 System.out.println("Your input is invalid!");
  335.                 continue;
  336.             } // end try
  337.         } while (true);
  338.         return input;
  339.     }// end readInt
  340.  
  341.     public int getNextId(DBProject esql, String fieldName, String tableName) {
  342.         String query = "Select Max(" + fieldName + ") from " + tableName;
  343.         int next = 0;
  344.  
  345.         try {
  346.             // creates a statement object
  347.             Statement stmt = this._connection.createStatement();
  348.  
  349.             // issues the query instruction
  350.             ResultSet rs = stmt.executeQuery(query);
  351.  
  352.             while (rs.next()) {
  353.                 next = rs.getInt(1) + 1;
  354.             } // end while
  355.  
  356.             stmt.close();
  357.         } catch (Exception e) {
  358.             System.out.println(e);
  359.         }
  360.  
  361.         return next;
  362.     }
  363.  
  364.     public static void addCustomer(DBProject esql) {
  365.         // Given customer details add the customer in the DB
  366.         String name, lastName, address, dob, gender;
  367.         int phone;
  368.         System.out.print("Enter name: ");
  369.         name = readText();
  370.  
  371.         System.out.print("Enter last name: ");
  372.         lastName = readText();
  373.  
  374.         System.out.print("Enter Address: ");
  375.         address = readText();
  376.  
  377.         System.out.print("Enter phone number: ");
  378.         phone = readInt();
  379.  
  380.         System.out.print("Enter date of birth: ");
  381.         dob = readText();
  382.  
  383.         System.out.print("Enter Gender: ");
  384.         gender = readText();
  385.  
  386.         String sql = String.format(
  387.                 "INSERT INTO CUSTOMER(CUSTOMERID,FNAME,LNAME,ADDRESS,PHNO,DOB,GENDER)"
  388.                         + " VALUES(%s,'%s','%s','%s',%s,'%s','%s') ",
  389.                 esql.getNextId(esql, "customerid", "customer"), name, lastName, address, phone, dob, gender);
  390.  
  391.         try {
  392.             esql.executeUpdate(sql);
  393.             System.out.println("The customer was succesfully added!");
  394.         } catch (Exception e) {
  395.             System.out.println("The customer couldn't be added.\nMake sure that "
  396.                     + "name and last name are at most 30 characters long, phone number contains only numbers, date of birth"
  397.                     + " is in the format MM/DD/YYYY and gender is Male, Female or Other.");
  398.         }
  399.  
  400.     }// end addCustomer
  401.  
  402.     public static void addRoom(DBProject esql) {
  403.         // Given room details add the room in the DB
  404.         try {
  405.             System.out.print("\tEnter hotelID: ");
  406.             String hotelid = in.readLine();
  407.  
  408.             System.out.print("\tEnter room type: ");
  409.             String roomType = in.readLine();
  410.  
  411.             String sql = String.format("INSERT INTO Room VALUES ( %s, %s, '%s')", hotelid,
  412.                     esql.getNextId(esql, "roomno", "room"), roomType);
  413.  
  414.             esql.executeUpdate(sql);
  415.             System.out.println("The room was succesfully added!");
  416.         } catch (Exception e) {
  417.             System.err.println(e.getMessage());
  418.         }
  419.     }// end addRoom
  420.  
  421.     public static void addMaintenanceCompany(DBProject esql) {
  422.         // Given maintenance Company details add the maintenance company in the DB
  423.         String name, address, cert;
  424.         boolean isCertified;
  425.  
  426.         System.out.print("Name of the Company: ");
  427.         name = readText();
  428.  
  429.         System.out.print("Address: ");
  430.         address = readText();
  431.  
  432.         System.out.print("Is it certified? (yes/no): ");
  433.         cert = readText();
  434.         while (!(cert.equalsIgnoreCase("yes") || cert.equalsIgnoreCase("no"))) {
  435.             System.out.print("Wrong format.\nIs it certified? (yes/no):");
  436.             cert = readText();
  437.         }
  438.         isCertified = true ? "yes".equalsIgnoreCase(cert) : false;
  439.  
  440.         String sql = String.format(
  441.                 "INSERT INTO MaintenanceCompany(cmpID, name, address,isCertified) VALUES(%s,'%s','%s',%s)",
  442.                 esql.getNextId(esql, "cmpID", "MaintenanceCompany"), name, address, isCertified);
  443.  
  444.         try {
  445.             esql.executeUpdate(sql);
  446.             System.out.println(name + " was succesfully added!");
  447.         } catch (Exception e) {
  448.             System.out.println(e);
  449.             System.out.println("The company couldn't be added.\nMake sure that "
  450.                     + " the name is at most 30 characters and you provide a yes or no to is certified field.");
  451.         }
  452.  
  453.     }// end addMaintenanceCompany
  454.  
  455.     public static void addRepair(DBProject esql) {
  456.         // Given repair details add repair in the DB
  457.         try {
  458.  
  459.             System.out.print("\tEnter hotelID: ");
  460.             String hotelid = in.readLine();
  461.  
  462.             System.out.print("\tEnter room number: ");
  463.             String roomNo = in.readLine();
  464.  
  465.             System.out.print("\tEnter maintenance company id: ");
  466.             String compID = in.readLine();
  467.  
  468.             System.out.print("\tEnter repair date: ");
  469.             String repDate = in.readLine();
  470.  
  471.             System.out.print("\tEnter repair description: ");
  472.             String repairDescr = in.readLine();
  473.  
  474.             System.out.print("\tEnter repair type: ");
  475.             String repairType = in.readLine();
  476.  
  477.             String sql = String.format("INSERT INTO Repair VALUES ( %s, %s, %s, %s, '%s', '%s', '%s')",
  478.                     esql.getNextId(esql, "rID", "Repair"), hotelid, roomNo, compID, repDate, repairDescr, repairType);
  479.  
  480.             esql.executeUpdate(sql);
  481.             System.out.println("The repair was succesfully added!");
  482.         } catch (Exception e) {
  483.             System.err.println(e.getMessage());
  484.         }
  485.     }// end addRepair
  486.  
  487.     public static void bookRoom(DBProject esql) {
  488.         // Given hotelID, roomNo and customer Name create a booking in the DB
  489.         int hotelID, roomNo, numberOfPeople;
  490.         double price;
  491.         String date, fname, lname;
  492.  
  493.         System.out.print("Enter hotel ID: ");
  494.         hotelID = readInt();
  495.  
  496.         System.out.print("Enter room number");
  497.         roomNo = readInt();
  498.  
  499.         System.out.print("Enter Customer first name: ");
  500.         fname = readText();
  501.  
  502.         System.out.print("Enter Customer last name: ");
  503.         lname = readText();
  504.  
  505.         System.out.print("Enter booking date: ");
  506.         date = readText();
  507.  
  508.         System.out.print("Enter number of people: ");
  509.         numberOfPeople = readInt();
  510.  
  511.         System.out.print("Enter price: ");
  512.         price = readDouble();
  513.  
  514.         String sql = String.format(
  515.                 "insert into booking select %s, c.customerid, %s,%s,'%s',%s,%s from customer c where c.fname = '%s' and c.lname = '%s' limit 1",
  516.                 esql.getNextId(esql, "bID", "booking"), hotelID, roomNo, date, numberOfPeople,
  517.                 String.format("%.2f", price), fname, lname);
  518.  
  519.         try {
  520.             esql.executeUpdate(sql);
  521.             System.out.println("The booking was succesfully created!");
  522.         } catch (Exception e) {
  523.             System.out.println(e);
  524.             System.out.println("The booking couldn't be created.");
  525.         }
  526.  
  527.     }// end bookRoom
  528.  
  529.     public static void assignHouseCleaningToRoom(DBProject esql) {
  530.         // Given Staff SSN, HotelID, roomNo Assign the staff to the room
  531.         try {
  532.  
  533.             System.out.print("\tEnter staff id: ");
  534.             String staffID = in.readLine();
  535.  
  536.             System.out.print("\tEnter hotelID: ");
  537.             String hotelid = in.readLine();
  538.  
  539.             System.out.print("\tEnter room number: ");
  540.             String roomNo = in.readLine();
  541.  
  542.             String sql = String.format("INSERT INTO Assigned VALUES ('%s','%s','%s','%s')",
  543.                     esql.getNextId(esql, "asgID", "Assigned"), staffID, hotelid, roomNo);
  544.  
  545.             esql.executeUpdate(sql);
  546.  
  547.         } catch (Exception e) {
  548.             System.err.println(e.getMessage());
  549.         }
  550.     }// end assignHouseCleaningToRoom
  551.  
  552.     public static void repairRequest(DBProject esql) {
  553.         // Given a hotelID, Staff SSN, roomNo, repairID , date create a repair request
  554.         // in the DB
  555.         int ssn, repairID;
  556.         String date, description;
  557.  
  558.         System.out.print("Enter SSN: ");
  559.         ssn = readInt();
  560.  
  561.         System.out.print("Enter repair ID: ");
  562.         repairID = readInt();
  563.  
  564.         System.out.print("Enter date: ");
  565.         date = readText();
  566.  
  567.         System.out.print("Enter description: ");
  568.         description = readText();
  569.  
  570.         String sql = String.format("INSERT INTO request VALUES(%s,%s,%s,'%s','%s')",
  571.                 esql.getNextId(esql, "reqID", "request"), ssn, repairID, date, description);
  572.  
  573.         try {
  574.             esql.executeUpdate(sql);
  575.             System.out.println("The request was succesfully created!");
  576.         } catch (Exception e) {
  577.             System.out.println("The request couldn't be created.");
  578.         }
  579.  
  580.     }// end repairRequest
  581.  
  582.     public static void numberOfAvailableRooms(DBProject esql) {
  583.         // Given a hotelID, get the count of rooms available
  584.         try {
  585.             System.out.print("\tEnter Hotel ID: ");
  586.             String hotelid = in.readLine();
  587.  
  588.             String query = String.format("SELECT COUNT(*) FROM Room R "
  589.                     + "WHERE R.hotelid = '%s' and R.roomNo not in (SELECT roomNo FROM Booking B WHERE B.hotelid = '%s');",
  590.                     hotelid, hotelid);
  591.  
  592.             esql.executeQuery(query);
  593.  
  594.         } catch (Exception e) {
  595.             System.err.println(e.getMessage());
  596.         }
  597.     }// end numberOfAvailableRooms
  598.  
  599.     public static void numberOfBookedRooms(DBProject esql) {
  600.         // Given a hotelID, get the count of rooms booked
  601.         int hotelID;
  602.         System.out.print("Enter hotel ID: ");
  603.         hotelID = readInt();
  604.  
  605.         String sql = String.format("Select count(*) as TotalBookings from booking where hotelid=%s", hotelID);
  606.  
  607.         try {
  608.             esql.executeQuery(sql);
  609.         } catch (Exception e) {
  610.             System.out.println("There was an error.");
  611.         }
  612.     }// end numberOfBookedRooms
  613.  
  614.     public static void listHotelRoomBookingsForAWeek(DBProject esql) {
  615.         // Given a hotelID, date - list all the rooms available for a week(including the
  616.         // input date)
  617.         try {
  618.  
  619.             System.out.print("\tEnter hotelID: ");
  620.             String hotelid = in.readLine();
  621.  
  622.             System.out.print("\tEnter date(mm/dd/yyyy): ");
  623.             String date = in.readLine();
  624.  
  625.             Date date1 = new SimpleDateFormat("MM/dd/yyyy").parse(date);
  626.             Calendar c = Calendar.getInstance();
  627.             c.setTime(date1);
  628.             c.add(Calendar.DATE, 7);
  629.             DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
  630.             String weeklater = df.format(c.getTime());
  631.  
  632.             String sql = String.format(
  633.                     "SELECT * from Booking WHERE hotelid = '%s' and bookingdate BETWEEN '%s' AND '%s'", hotelid, date,
  634.                     weeklater);
  635.             esql.executeQuery(sql);
  636.         } catch (Exception e) {
  637.             System.err.println(e.getMessage());
  638.         }
  639.     }// end listHotelRoomBookingsForAWeek
  640.  
  641.     public static void topKHighestRoomPriceForADateRange(DBProject esql) {
  642.         // List Top K Rooms with the highest price for a given date range
  643.         int k;
  644.         String from, to;
  645.  
  646.         System.out.print("Enter k: ");
  647.         k = readInt();
  648.  
  649.         System.out.print("Enter starting date: ");
  650.         from = readText();
  651.  
  652.         System.out.print("Enter ending date: ");
  653.         to = readText();
  654.  
  655.         String sql = String.format("select hotelID, roomNo, price, bookingDate from booking where bookingDate between"
  656.                 + " '%s' and '%s' order by price DESC limit %s", from, to, k);
  657.  
  658.         try {
  659.             esql.executeQuery(sql);
  660.         } catch (Exception e) {
  661.             System.out.println("There was an error.");
  662.         }
  663.  
  664.     }// end topKHighestRoomPriceForADateRange
  665.  
  666.     public static void topKHighestPriceBookingsForACustomer(DBProject esql) {
  667.         // Given a customer Name, List Top K highest booking price for a customer
  668.         try {
  669.  
  670.             System.out.print("\tEnter customer's first name: ");
  671.             String fName = in.readLine();
  672.             System.out.print("\tEnter customer's last name: ");
  673.             String lName = in.readLine();
  674.             System.out.print("\tEnter k: ");
  675.             String k = in.readLine();
  676.  
  677.             String sql = String.format("SELECT B.price FROM Booking B, Customer C "
  678.                     + "WHERE C.customerid = B.customer AND C.fName = '%s' AND C.lName = '%s' "
  679.                     + "ORDER BY B.price DESC LIMIT %s; ", fName, lName, k);
  680.  
  681.             esql.executeQuery(sql);
  682.  
  683.         } catch (Exception e) {
  684.             System.err.println(e.getMessage());
  685.         }
  686.     }// end topKHighestPriceBookingsForACustomer
  687.  
  688.     public static void totalCostForCustomer(DBProject esql) {
  689.         // Given a hotelID, customer Name and date range get the total cost incurred by
  690.         // the customer
  691.         int hotelID;
  692.         String fname, lname, start, end;
  693.  
  694.         System.out.print("Enter hotel ID: ");
  695.         hotelID = readInt();
  696.  
  697.         System.out.println("Enter customer's first name: ");
  698.         fname = readText();
  699.  
  700.         System.out.println("Enter customer's last name: ");
  701.         lname = readText();
  702.  
  703.         System.out.println("Enter start date: ");
  704.         start = readText();
  705.  
  706.         System.out.println("Enter end date: ");
  707.         end = readText();
  708.  
  709.         try {
  710.             String sql = String.format("select customerid from customer where fname = '%s' and lname='%s' limit 1",
  711.                     fname, lname);
  712.             int customerid = 0;
  713.  
  714.             Statement stmt = esql._connection.createStatement();
  715.             ResultSet rs = stmt.executeQuery(sql);
  716.             if (rs.next())
  717.                 customerid = rs.getInt("customerid");
  718.  
  719.             sql = String.format(
  720.                     "select COALESCE(sum(price), 0) as TotalIncurred from booking  where hotelID = %s and customer = %s and bookingdate between '%s' and '%s' ",
  721.                     hotelID, customerid, start, end);
  722.             esql.executeQuery(sql);
  723.         } catch (Exception e) {
  724.             System.out.println("There was an error.");
  725.             System.out.println(e.getMessage());
  726.         }
  727.  
  728.     }// end totalCostForCustomer
  729.  
  730.     public static void listRepairsMade(DBProject esql) {
  731.         // Given a Maintenance company name list all the repairs along with repairType,
  732.         // hotelID and roomNo
  733.         try {
  734.             String mcomp = "";
  735.  
  736.             System.out.print("\tEnter Maintenance company name: ");
  737.             mcomp = in.readLine();
  738.  
  739.             String sql = String
  740.                     .format("SELECT r.rid, r.hotelid, r.roomNo, r.repairType FROM maintenancecompany mc, repair r "
  741.                             + "WHERE mc.name = '%s' AND mc.cmpid = r.mcompany;", mcomp);
  742.  
  743.             esql.executeQuery(sql);
  744.  
  745.         } catch (Exception e) {
  746.             System.err.println(e.getMessage());
  747.         }
  748.     }// end listRepairsMade
  749.  
  750.     public static void topKMaintenanceCompany(DBProject esql) {
  751.         // List Top K Maintenance Company Names based on total repair count (descending
  752.         // order)
  753.         int k;
  754.  
  755.         System.out.print("Enter k: ");
  756.         k = readInt();
  757.  
  758.         String sql = String.format(
  759.                 "select m.name, count(m.cmpID) as RepairCount from MaintenanceCompany m, repair r where m.cmpID = r.mCompany \r\n"
  760.                         + "group by m.cmpID\r\n" + "order by RepairCount DESC\r\n" + "limit 5",
  761.                 k);
  762.  
  763.         try {
  764.             esql.executeQuery(sql);
  765.         } catch (Exception e) {
  766.             System.out.println("There was an error.");
  767.         }
  768.  
  769.     }// end topKMaintenanceCompany
  770.  
  771.     public static void numberOfRepairsForEachRoomPerYear(DBProject esql) {
  772.         // Given a hotelID, roomNo, get the count of repairs per year
  773.         try {
  774.             System.out.print("\tEnter Hotel ID: ");
  775.             String hotelid = in.readLine();
  776.  
  777.             System.out.print("\tEnter room number: ");
  778.             String roomno = in.readLine();
  779.  
  780.             String sql = String.format(
  781.                     "SELECT COUNT(DATE_PART('year', repairdate)) as number_of_repairs, DATE_PART('year', repairdate) as year "
  782.                             + "FROM repair r WHERE r.hotelid = %s and r.roomno = %s GROUP BY DATE_PART('year', repairdate);",
  783.                     hotelid, roomno);
  784.  
  785.             esql.executeQuery(sql);
  786.  
  787.         } catch (Exception e) {
  788.             System.err.println(e.getMessage());
  789.         }
  790.  
  791.     }// end listRepairsMade
  792.  
  793. }// end DBProject
RAW Paste Data