Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /**
- * Database functions. You need to modify each of these to interact with the database and return appropriate results.
- */
- /**
- * Connect to database
- * This function does not need to be edited - just update config.ini with your own
- * database connection details.
- * @param string $file Location of configuration data
- * @return PDO database object
- * @throws exception
- */
- function connect($file = 'config.ini') {
- // read database seetings from config file
- if ( !$settings = parse_ini_file($file, TRUE) )
- throw new exception('Unable to open ' . $file);
- // parse contents of config.ini
- $dns = $settings['database']['driver'] . ':' .
- 'host=' . $settings['database']['host'] .
- ((!empty($settings['database']['port'])) ? (';port=' . $settings['database']['port']) : '') .
- ';dbname=' . $settings['database']['schema'];
- $user= $settings['db_user']['username'];
- $pw = $settings['db_user']['password'];
- // create new database connection
- try {
- $dbh=new PDO($dns, $user, $pw);
- $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- } catch (PDOException $e) {
- print "Error Connecting to Database: " . $e->getMessage() . "<br/>";
- die();
- }
- return $dbh;
- }
- /**
- * Check login details
- * @param string $name Login name
- * @param string $pass Password
- * @return boolean True is login details are correct
- */
- // FINISHED.
- function checkLogin($name,$pass) {
- $db = connect();
- try {
- $hashed = md5($pass);
- $hashed = $pass;
- $stmt = $db->prepare('SELECT password FROM PeerPark.Member WHERE email=:username');
- $stmt->bindValue(':username', $name, PDO::PARAM_STR);
- $stmt->execute();
- $result = $stmt->fetchColumn();
- $stmt->closeCursor();
- if($result) {
- if($result == $pass) {
- return True;
- }
- }
- return False;
- } catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- return False;
- }
- /**
- * Get details of the current user
- * @param string $user login name user
- * @return array Details of user - see index.php
- */
- function getUserDetails($user) {
- // HALF DONE
- // TODO:
- // JOIN WITH BAY AND BILLING FOR NAME.
- // WHICH NAME?
- // MEMBERNO DOESNT WORK?
- $db = connect();
- try {
- $stmt = $db->prepare("SELECT memberNo AS memberNo, nickName AS name,
- adrStreetNo || ' ' || adrStreet || ', ' || adrCity AS address,
- email,
- prefBillingNo, 'lol' as prefBillingName,
- prefBay, 'bay' as prefBayName,
- stat_nrOfBookings AS nbookings
- FROM PeerPark.Member
- WHERE email=:username");
- $stmt->bindValue(':username', $user, PDO::PARAM_STR);
- $stmt->execute();
- } catch (PDOException $e) {
- print "Error in getting user details " . $e->getMessage();
- return False;
- }
- $results = $stmt->fetch();
- // Example user data - this should come from a query
- //$results['memberNo'] = 32;
- /*$results['name'] = $stmt->fetchColumn();
- $results['address'] = $stmt->fetchColumn();
- $results['email'] = $stmt->fetchColumn();
- $results['prefBillingNo'] = $stmt->fetchColumn();
- $results['prefBillingName'] = $stmt->fetchColumn();
- $results['prefBay'] = $stmt->fetchColumn();
- $results['prefBayName'] = $stmt->fetchColumn();
- $results['nbookings'] = $stmt->fetchColumn();*/
- $stmt->closeCursor();
- return $results;
- }
- /**
- * Get list of bays with silimar address
- * @param string $address address to be look up
- * @return array Various details of each bay - see baylist.php
- */
- function searchBay($address) {
- // QUERY DONE
- // TODO: logic to figure out if available or not.
- $db = connect();
- try {
- $stmt = $db->prepare("SELECT
- bayId, site, address, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
- FROM PeerPark.ParkBay
- WHERE address LIKE '%' || :address || '%'");
- $stmt->bindValue(':address', $address, PDO::PARAM_STR);
- $stmt->execute();
- // Fill up the array row by row.
- $result = array();
- while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
- array_push($result, array('bayID'=>$row['bayID'], 'site'=>$row['site'], 'address'=>$row['address'], 'avail'=>true));
- }
- $stmt->closeCursor();
- return $result;
- }
- catch (PDOException $e) {
- print "Error searching bays " . $e->getMessage();
- return False;
- }
- }
- /**
- * Retrieve information of all bays
- * @return array Various details of each bay - see baylist.php
- * @throws Exception
- */
- function getBays() {
- // Example booking info - this should come from a query. Format is
- // (bay ID, site, address, availability of the bay)
- // MOSTLY DONE
- // TODO: LOGIC For availability.
- $db = connect();
- try{
- $stmt = $db->prepare("SELECT
- bayId, site, address, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
- FROM PeerPark.ParkBay");
- $stmt->execute();
- // Fill up the array row by row.
- $result = array();
- while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
- array_push($result, array('bayID'=>$row['bayID'], 'site'=>$row['site'], 'address'=>$row['address'], 'avail'=>true));
- }
- $stmt->closeCursor();
- return $result;
- } catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- }
- /**
- * Retrieve information on bays
- * @param string $memberNo ID of the member
- * @return array details of the member preferred bay - see baylist.php
- * @throws Exception
- */
- function getPrefBayInformation($memberNo) {
- // Replace lines below with code to get the information about the owner preferred bay from the database
- // Example bay info - this should come from a query. Format is
- // (bay ID, Owner, Latitude, Longitude, Address, width, height, length, pod, site, week start, week end, weekend start, weekend end)
- // TODO: Nearly done.
- // TODO: Do we have to get owner name or just id?
- // TODO: Join with Member for PrefBay.
- $db = connect();
- try {
- $stmt = $db->prepare("SELECT
- bayID, address, pod, site, avail_wk_start, avail_wk_end, avail_wend_start, avail_wend_end
- FROM PeerPark.ParkBay
- JOIN PeerPark.Member ON PeerPark.ParkBay.bayID = PeerPark.Member.prefBay
- WHERE PeerPark.Member.email=:member");
- $stmt->bindValue(':member', $memberNo, PDO::PARAM_STR);
- $stmt->execute();
- $results = array();
- $results = $stmt->fetch(PDO::FETCH_ASSOC);
- //$results = array('bayID'=>$row['bayID'], 'site'=>$row['site'], 'address'=>$row['address'], 'avail'=>true);
- return $results;
- }
- catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- return false;
- }
- /**
- * Retrieve information on bays
- * @param string $BayID ID of the bay
- * @return array Various details of the bay - see baydetail.php
- * @throws Exception
- */
- function getBayInformation($BayID) {
- // TODO:
- // Check right data is in query.
- // Fill array
- // NOT DONE.
- // Replace lines below with code to get the information about a specific bay from the database
- // Example bay info - this should come from a query. Format is
- // (bay ID, Owner, Latitude, Longitude, Address, width, height, length, pod, site, week start, week end, weekend start, weekend end)
- $db = connect();
- try {
- $stmt = $db->prepare('SELECT * FROM PeerPark.Booking WHERE bookingID=:book');
- $stmt->bindValue(':member', $memberNo, PDO::PARAM_STR);
- $stmt->execute();
- $stmt->closeCursor();
- return
- array('bayID'=>954673,'site'=>'Glebe Public School 1', 'owner'=>'Toni Collette', 'address'=> '25 Glebe Point Road','description'=>'Bay located next to Glebe Public School','gps_lat'=>456,'gps_long'=>5689,'locatedAt'=>19,'mapURL'=>'https://www.google.com.au/maps/place/Glebe+Public+School/@-33.8896527,151.186376,16z/data=!4m6!1m3!3m2!1s0x6b12b1d4ab9ef1d9:0x1d017d69037a07c0!2sThe+University+of+Sydney!3m1!1s0x0000000000000000:0x365d99076bb74a6d',
- 'width'=> 45,'height'=>56,'length'=>56,'pod'=>45,'avail_wk_start'=>9,'avail_wk_end'=>17,'avail_wend_start'=>2,'avail_wend_end'=>3);
- } catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- return False;
- }
- /**
- * Retrieve information on active bookings for a member
- * @param string $memberNo ID of member
- * @return array Various details of each booking - see bookings.php
- * @throws Exception
- */
- function getOpenBookings($memberNo) {
- // Replace lines below with code to get list of bookings from the database
- // Example booking info - this should come from a query. Format is
- // (booking ID, bay ID, Car Name, Booking start date, booking start time, booking duration)
- // TODO: query. get the bookings for a time past NOW owned by :member. Get the data as above.
- // TODO: fill the array.
- // NOT DONE.
- $db = connect();
- try {
- $stmt = $db->prepare('SELECT * FROM PeerPark.Booking WHERE bookingID=:book');
- $stmt->bindValue(':member', $memberNo, PDO::PARAM_STR);
- $stmt->execute();
- $stmt->closeCursor();
- $results = array(
- array('bookingID'=>1,'bayLocation'=>'CBD','car'=>'Jenny the Yaris','bookingDate'=>'05/03/15' ),
- array('bookingID'=>2,'bayLocation'=>'Glebe','car'=>'Garry the Getz','bookingDate'=>'11/04/15')
- );
- return $results;
- } catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- return False;
- }
- /**
- * Make a new booking for a bay
- * @param string $memberNo Member booking the bay
- * @param string $car Name of the car
- * @param string $bayID ID of the bay to book
- * @param string $bookingDate the date of the booking
- * @param string $bookingHour the time of the booking
- * @param string $duration the duration of the booking
- * @return array Various details of current visit - see newbooking.php
- * @throws Exception
- */
- function makeBooking($memberNo,$car,$bayID,$bookingDate,$bookingHour,$duration) {
- // TODO: TODO: Cost
- // NOT DONE
- $db = connect();
- try {
- $stmt = $db->prepare('INSERT INTO PeerPark.Booking VALUES ((SELECT MAX(bookingID) + 1 FROM PeerPark.Booking), :bayID, :bookingDate, :bookingHour, :duration,
- (SELECT memberNo FROM PeerPark.Member WHERE email=:memberNo),
- :car);');
- $stmt->bindValue(':bayID',$bayID, PDO::PARAM_INT);
- $stmt->bindValue(':bookingDate',$bookingDate, PDO::PARAM_STR);
- $stmt->bindValue(':bookingHour', $bookingHour, PDO::PARAM_INT);
- $stmt->bindValue(':duration',$duration, PDO::PARAM_INT);
- $stmt->bindValue(':memberNo',$memberNo, PDO::PARAM_STR);
- $stmt->bindValue(':car',$car, PDO::PARAM_STR);
- $stmt->execute();
- $stmt = $db->prepare('SELECT MAX(bookingID) as id FROM PeerPark.Booking');
- $stmt->execute();
- $row = $stmt->fetch();
- $bookingID = $row['id'];
- print($bookingID);
- $stmt->closeCursor();
- return array(
- 'status'=>'success',
- 'bookingID'=>$bookingID,
- 'bayID'=>$bayID,
- 'car'=>$car,
- 'bookingDate'=>$bookingDate,
- 'bookingHour'=>$bookingHour,
- 'duration'=>$duration,
- 'cost'=>1000
- );
- } catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- }
- /**
- * Retrieve information on the booking
- * @param string $bookingID ID of the bay
- * @return array Various details of the booking - see bookingDetail.php
- * @throws Exception
- */
- function getBookingInfo($bookingID) {
- // Replace lines below with code to get the detail about the booking.
- // Example booking info - this should come from a query. Format is
- // (bookingID, bay Location, booking Date, booking Hour, duration, car, member Name)
- // NOT DONE.
- // TODO: The query
- // TODO: Fill the array.
- $db = connect();
- try {
- $stmt = $db->prepare('SELECT * FROM PeerPark.Booking WHERE bookingID=:book');
- $stmt->bindValue(':book', $bookingID, PDO::PARAM_STR);
- $stmt->execute();
- $stmt->closeCursor();
- return array('bookingID'=>1, 'bayLocation'=>'CBD', 'bookingDate'=> '10/05/2015','bookingHour'=>'10:01','duration'=>2,'car'=> 'Harry the Goat','memberName'=>'Uwe');
- } catch (PDOException $e) {
- print "Error checking login: " . $e->getMessage();
- return False;
- }
- }
- /**
- * Get details of the cars of the member
- * @param string $user ID of member
- * @return Name of the cars owned by the member - see index.php
- */
- function getCars($email) {
- // DONE.
- $db = connect();
- try {
- $stmt = $db->prepare("SELECT name FROM PeerPark.Car
- NATURAL JOIN PeerPark.Member
- WHERE email=:email");
- $stmt->bindValue(':email', $email, PDO::PARAM_INT);
- $stmt->execute();
- // Fill up the array row by row.
- $result = array();
- while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
- array_push($result, array('car'=>$row['name']));
- }
- $stmt->closeCursor();
- return $result;
- } catch (PDOException $e) {
- print "Error getting cars";
- return False;
- }
- return false;
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement