def get_free_hotel_room(hid, rtid, arrival, departure): return DBQuery( """SELECT room.rid FROM room JOIN hotel_rooms ON hotel_rooms.rid = room.rid WHERE room.rid IN (SELECT free_rooms_in_time_set(%s,%s)) AND hid = %s AND rtid = %s LIMIT 1""", (arrival, departure, hid, rtid))
def get_io_index_performance(): return DBQuery("""SELECT relname, sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read) + 1),2) as heap_ratio FROM pg_statio_user_tables GROUP BY relname ORDER BY relname;""")
def delete_person_order(username, bid): person_query = User.get_person_id(username) if person_query.code == DBQuery.CODE_OK and person_query.result: pid = person_query.result[0][0] else: return person_query return DBQuery("DELETE FROM booked WHERE bid = %s AND pid = %s", (bid, pid))
def get_io_cache_performance(): return DBQuery("""SELECT relname, sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, round((sum(idx_blks_hit) - sum(idx_blks_read)) / (sum(idx_blks_hit)+1), 2) as idx_ratio FROM pg_statio_user_tables GROUP BY relname ORDER BY relname;""")
def query_check_out(hid): return DBQuery( """SELECT bid, fname, lname, checkout, booked.rid FROM booked JOIN person ON (booked.pid = person.pid) JOIN hotel_rooms ON (booked.rid = hotel_rooms.rid) WHERE hid = %s AND bid IN (SELECT bid FROM registration WHERE regout IS NULL) ORDER BY checkout""", (hid, ))
def query_check_in(hid): now = strftime("%Y-%m-%d %H:%M:%S") return DBQuery( """SELECT bid, fname, lname, checkin, booked.rid FROM booked JOIN person ON (booked.pid = person.pid) JOIN hotel_rooms ON (booked.rid = hotel_rooms.rid) WHERE hid = %s AND checkin < %s AND checkout > %s AND bid NOT IN (SELECT bid FROM registration) ORDER BY checkin""", (hid, now, now))
def search_hotels(arrival, departure, city, people): return DBQuery( """SELECT hotel_rooms.hid,title,rating,room.rtid,rtype,price FROM room JOIN roomtype ON room.rtid = roomtype.rtid JOIN hotel_rooms ON hotel_rooms.rid = room.rid JOIN hotel ON hotel_rooms.hid = hotel.hid JOIN address ON hotel.aid = address.aid WHERE room.rid IN (SELECT free_rooms_in_time_set(%s,%s)) AND city = %s AND capacity >= %s GROUP BY hotel_rooms.hid, rtype, room.rtid, price,title,rating ORDER BY price ASC""", (arrival, departure, city, people))
def get_orders(username): person_query = User.get_person_id(username) if person_query.code == DBQuery.CODE_OK and person_query.result: pid = person_query.result[0][0] else: return person_query return DBQuery("""SELECT booked.bid,title,checkin,checkout, rtype,room.rid,cost,rating,regid FROM booked JOIN room ON booked.rid = room.rid JOIN roomtype ON room.rtid = roomtype.rtid JOIN hotel_rooms ON room.rid = hotel_rooms.rid LEFT OUTER JOIN registration ON booked.bid = registration.bid JOIN hotel ON hotel_rooms.hid = hotel.hid WHERE pid = %s""", (pid,))
def query_update_person(fname, lname, email, username): userid_query = User.get_user_id(username) if userid_query.code == DBQuery.CODE_OK and userid_query.result: uid = userid_query.result[0][0] else: return userid_query today = strftime("%Y-%m-%d") return DBQuery("""UPDATE person SET (fname, lname, email) = (%(fname)s, %(lname)s, %(email)s) WHERE uid = %(uid)s; INSERT INTO person (uid, fname, lname, email, reg_date) SELECT %(uid)s, %(fname)s, %(lname)s, %(email)s, %(today)s WHERE NOT EXISTS (SELECT 1 FROM person WHERE uid = %(uid)s)""", {'fname': fname, 'lname': lname, 'email': email, 'uid': uid, 'today': today})
def get_disk_performance(): return DBQuery("""SELECT TABLE_NAME, pg_size_pretty(index_bytes) AS INDEX, pg_size_pretty(table_bytes) AS TABLE, pg_size_pretty(total_bytes) AS total FROM ( SELECT *, total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes FROM ( SELECT relname AS TABLE_NAME, pg_total_relation_size(c.oid) AS total_bytes, pg_indexes_size(c.oid) AS index_bytes, pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' AND nspname = 'public' ) a ) a ORDER BY TABLE_NAME;""")
def get_person(username): return DBQuery("""SELECT fname, lname, email FROM person WHERE uid = (SELECT uid FROM users WHERE username = %s)""", (username,))
def get_manager_hotel(username): return DBQuery("""SELECT hotel.hid, title FROM users JOIN person ON (person.uid = users.uid) JOIN employee ON (employee.pid = person.pid) JOIN hotel ON (employee.hid = hotel.hid) WHERE username = %s""", (username,))
def query_hash_role(username): return DBQuery("SELECT hash, role FROM users WHERE username = %s", (username,))
def query_username(username): return DBQuery("SELECT * FROM users WHERE username = %s", (username,))
def query_add(username, password, role): return DBQuery("""INSERT INTO users (username, hash, role) VALUES (%s, %s, %s)""", (username, password, role))
def get_person_id(username): return DBQuery("""SELECT pid FROM person JOIN users ON person.uid = users.uid WHERE username = %s""", (username,))
def query_register_check_in(bid): now = strftime("%Y-%m-%d %H:%M:%S") return DBQuery( """INSERT INTO registration (bid, regin) VALUES (%s, %s)""", (bid, now))
def get_role(username): return DBQuery("SELECT role FROM users WHERE username = %s", (username,))
def registered_check_query(bid): return DBQuery("SELECT * FROM registration WHERE bid = %s", (bid,))
def get_roomtype(rtid): return DBQuery("SELECT rtype, price FROM roomtype WHERE rtid = %s", (rtid, ))
def get_cities(): return DBQuery("""SELECT DISTINCT city FROM hotel JOIN hotel_rooms ON hotel.hid = hotel_rooms.hid JOIN address ON hotel.aid = address.aid ORDER BY city""")
def query_add(rid, pid, arrival, departure): return DBQuery( """INSERT INTO booked (rid,pid,checkin,checkout) VALUES (%s, %s, %s, %s)""", (rid, pid, arrival, departure))
def query_register_check_out(bid): now = strftime("%Y-%m-%d %H:%M:%S") return DBQuery( """UPDATE registration SET regout = %s WHERE bid = %s""", (now, bid))
def get_order_extra(hid): return DBQuery( """SELECT title, rating, country, city, street, appartment FROM hotel JOIN address ON hotel.aid = address.aid WHERE hid = %s""", (hid, ))
def get_user_id(username): return DBQuery("SELECT uid FROM users WHERE username = %s", (username,))