SHARE
TWEET

cs166

a guest Dec 6th, 2019 174 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top