import re
import string
from mx import DateTime
import math
import random
from comp import *
def SetConnection(cnx):
global connection
connection = cnx
def GetCursor():
global connection
return connection.cursor()
def sum(list):
retval = 0
for item in list:
retval += float(item)
return int(retval)
class customer:
"""A basic customer class.
Syntax: customer(username, f_name, l_name, address, city, state, zip,
country, phone, email)
All arguments are strings
"""
def __init__(self, username, f_name, l_name, address, city, state,
zip, country, phone, email):
self.id = 0
self.username = username
self.f_name = f_name
self.l_name = l_name
self.address = address
self.city = city
self.state = state
self.zip = zip
self.country = country
self.phone = ""
self.phone_format = ""
self.SetPhone(phone)
self.email = email
def SetPhone(self, phone):
"""A method for setting the phone and phone-format attributes.
Syntax: customer.SetPhone(string phone)
result is that customer.phone will be set to the phone string with
non-word characters stripped, and phone_format will be a string
representation of the format. This is designed to make searching
easier.
Please use this rather than setting the phone attribute directly.
"""
phone_str = phone
self.phone = ''
self.phone_format = ''
phone_list = re.split('(\w+)', phone_str)
index = 0
for elem in phone_list:
if (index % 2):
self.phone_format += str(len(elem))
self.phone += elem
else:
self.phone_format += elem
index += 1
def FormatPhone(self):
"""A method for returning a formatted phone number.
Syntax: customer.FormatPhone()
Returns a formated string representation of the phone number
"""
phone_str = ''
format_list = re.split('(\d)', self.phone_format)
index = 0
form_index = 0
for elem in format_list:
if (index % 2) == 0:
phone_str += elem
else:
phone_str += self.phone[form_index : form_index\
+ string.atoi(elem)]
form_index += string.atoi(elem)
index += 1
return phone_str
def FromRow(self, row):
"""A semi-private constructor of object from dictionary.
Please don't use unless you know what it does.
"""
if row == None:
return None
retval = customer(row["username"], row["f_name"], row["l_name"],
row["address"], row["city"], row["state"], row["zip"],
row["country"], row["phone"], row["email"])
retval.phone_format = row["phone_format"]
retval.id = row["customer_id"]
return retval
def GetByID(self, id):
"""A method of constructing the object from the customer_id.
Syntax: customer.GetByID(id)
returns customer object.
Hint: You can use the default interface object _customer to
call this method if you do not with to use a pre-existing
instantiation
"""
cursor = GetCursor()
cursor.execute(
"SELECT * FROM customers WHERE customer_id ='%s'" % id
)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def GetByUsername(self, username):
"""A method for constructing the object from a known username.
Syntax: customer.GetByUsername(username)
returns customer object.
Hint: You can use the default interface object _customer to
call this method if you do not with to use a pre-existing
instantiation
"""
cursor = GetCursor()
cursor.execute(
"SELECT * FROM customers WHERE username='%s'" % username
)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def search(self):
"""A method for searching for customers.
Syntax: customer.search()
returns an array of customer objects.
Hint: The standard way of using this is to instantiate a
customer object and then use this method to retrieve similar
objects. Note that we do not escape the % signs in the
attributes, so you can use these as SQL wildcards!
"""
cursor = GetCursor()
query = """
SELECT * FROM customers
WHERE l_name LIKE '%s'
AND phone LIKE '%s'
AND address LIKE '%s'
AND city LIKE '%s'
AND state LIKE '%s'
AND zip LIKE '%s'
AND country LIKE '%s'\n""" % (
self.l_name + '%', '%' + self.phone + '%',
self.address + '%', self.city + '%', self.state + '%',
self.zip + '%', self.country + '%')
if self.f_name:
query += ("AND f_name LIKE '%s'\n" %
(self.f_name + '%',))
if self.email:
query += ("AND email LIKE '%s'\n" %
('%' + self.email + '%'))
if self.username:
query += ("AND username LIKE '%s'" %
(self.username + '%'))
cursor.execute(query)
retval = []
row = DBAL.GetRowDict(cursor)
while (row):
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def commit(self):
"""Saves customer object to the database.
Syntax: customer.commit()
returns None
Hints: Use this method rather than calling insert() or update()
directly since this is far safer. Commit does attempt conflict
resolution by attempting to get the current object by either
username or id.
"""
if self.GetByUsername(self.username) or\
self.GetByID(self.id):
self.update()
else:
self.insert()
def insert(self):
"""Inserts object into database.
Syntax: customer.insert()
returns none.
Hints: Can test whether successful by checking to see if the id
value is set when it is returned.
CAUTION: Does not attempt conflict resolution. It is safer to
use commit() instead.
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
if self.username == None:
username = 'NULL'
else:
username = "'%s'" % self.username
cursor.execute("""
INSERT INTO customers
(f_name, l_name, phone, phone_format, address, city,
state, zip, country, email, username)
VALUES
('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
'%s', %s)
""" % (self.f_name, self.l_name, self.phone, self.phone_format,
self.address, self.city, self.state, self.zip,
self.country, self.email, username))
cursor.execute(DBAL.COMMIT)
cursor.close()
temp = self.search();
if len(temp) == 0:
raise "Can't locate inserted row!"
self.id = temp[0].id
def update(self):
"""Updates stored object in database by ID or username.
syntax: customer.update()
Caution: Does not attempt conflict resolution, so it is safer
to use commit() instead
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
if self.id:
cursor.execute("""
UPDATE customers
SET username = '%s', f_name = '%s',
l_name = '%s', address = '%s',
city = '%s', state = '%s', zip = '%s',
country = '%s', phone = '%s',
phone_format = '%s', email = '%s'
WHERE customer_id = '%s'
""" % (self.username, self.f_name,
self.l_name, self.address,
self.city, self.state, self.zip,
self.country, self.phone,
self.phone_format, self.email,
self.id))
else:
cursor.execute("""
UPDATE customers
SET f_name = '%s', l_name = '%s',
address = '%s', city = '%s',
state = '%s', zip = '%s',
country = '%s', phone = '%s',
phone_format = '%s', email = '%s'
WHERE username = '%s'
""" % (self.f_name, self.l_name,
self.address, self.city,
self.state, self.zip,
self.country, self.phone,
self.phone_format, self.email,
self.username))
cursor.execute(DBAL.COMMIT)
cursor.close()
_customer = customer('','','','','','','','','','')
class bed_type:
"""A class for bed types.
Syntax: bed_type(id, label, description)
returns Bed Type Object
Hint: For new objects, set the id to 0 so that they will be assigned
one by the database. The labels must also be unique.
"""
def __init__(self, id, label, description):
self.id = id
self.label = label
self.description = description
def FromRow(self, row):
"""A semi-private constructor for database rows.
Please don't use if you don't know what it does.
"""
if row == None:
return None
return bed_type(row["bed_type"], row["label"],
row["description"])
def GetByLabel(self, name):
"""Retrieves stored object by its label.
syntax: bed_type.GetByLabel(label)
returns bed_type object
Hint: Use the supplied _bed_type object if you do not one to
construct a dummy object first
"""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM bed_types WHERE label = '%s'
""" % name)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def GetByID(self, id):
"""Retrieves stored object by its ID.
syntax: bed_type.GetByID(id)
returns bed_type object
Hint: Use the supplied _bed_type object if you do not one to
construct a dummy object first
"""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM bed_types WHERE bed_type = '%s'
""" % id)
row = DBAL.GetRowDict(cursor)
cursor.close
return self.FromRow(row)
def GetTypes(self):
"""Return an array of all stored bed_type objects.
syntax: bed_type.GetTypes()
Hint: Use the supplied _bed_type object if you do not one to
construct a dummy object first
"""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM bed_types
""")
row = DBAL.GetRowDict(cursor)
retval = []
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def ReturnLabelArray(self):
"""Generates an indexed dictionary of the labels of bed_types.
syntax: bed_type.ReturnLabelArray()
Hint: Use the supplied _bed_type object if you do not one to
construct a dummy object first
"""
types = self.GetTypes()
retval = {}
for type in types:
retval[type.id] = type.label
return retval
def commit(self):
"""Saves the object to the database.
Syntax: bed_type.commit()
Note: Please use commit() instead of insert(), and update() as
it performs some conflict resolution and will more likely
succeed (and otherwise is safer).
"""
if self.id:
self.update()
else:
self.insert()
def insert(self):
"""Inserts the object into the database.
Syntax: bed_type.insert()
Note: Please use commit() instead of insert(), and update() as
it performs some conflict resolution and will more likely
succeed (and otherwise is safer).
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
INSERT INTO bed_types
(label, description)
VALUES
('%s', '%s')
""" % (self.label, self.description))
cursor.execute(DBAL.COMMIT)
cursor.close()
temp = self.GetByLabel(self.label)
self.id = temp.id
def update(self):
"""Updates the saved object in the database.
Syntax: bed_type.update()
Note: Please use commit() instead of insert(), and update() as
it performs some conflict resolution and will more likely
succeed (and otherwise is safer).
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
UPDATE bed_types
SET label = '%s', description = '%s'
WHERE bed_type = '%s'
""" % (self.label, self.description, self.id))
cursor.execute(DBAL.COMMIT)
cursor.close()
def delete(self):
"""Deletes the bed type and moves it to the archive """
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
DELETE FROM bed_types WHERE bed_type = '%s'
""" % (self.id))
cursor.execute(DBAL.COMMIT)
cursor.close()
_bed_type = bed_type('','','')
class r_class:
"""A class for room types.
syntax: r_class(id, smoking, bed_type, price, priv_bath,
precent_overbooking, description)
Hints: Use 0 or None as an id for new classes.
"""
def __init__(self, id, smoking, bed_type, price, priv_bath,
percent_overbooking, description):
self.id = id
self.smoking = smoking
self.bed_type = bed_type
self.price = price
self.priv_bath = priv_bath
self.percent_overbooking = percent_overbooking
self.description = description
def FromRow(self, row):
"""A semi-private constructor for using database rows.
Please do not use this unless you know what you are doing.
"""
if row == None:
return None
retval = r_class(row["class_id"], row["smoking"],
row["bed_type"], row["price"], row["priv_bath"],
row["percent_overbooking"], row["description"]
)
return retval
def CheckPrice(self, date = DateTime.today()):
"Checks for price updates and sets price accordingly."
global _rate_change
changes = _rate_change.GetByClassAndDay(self.id, date)
price = self.price
for change in changes:
price = change.adjust_price(price)
if (bool(change.recurring) == False and
bool(change.only_wkdays) == False and
bool(change.only_wkends) == False
):
change.MakePermanent()
self.price = price
return price
def GetByID(self, id):
"""Fetches a class by ID.
syntax: r_class.GetByID(id)
Returns a r_class object.
Hint: Use the _r_class instance for instantiating other
objects!
"""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM classes WHERE class_id = '%s'
""" % id)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def SearchByFeatures(self):
"""Searches for matching classes based on features selected.
Syntax: r_class.SearchByFeatures()
returns a sequence of r_class objects
Hint: Instantiate a new instance with your search features
first (smoking, private bath, and bed type) and then use this
method to bring in matches.
"""
_and = None
cursor = GetCursor()
query = 'SELECT * FROM classes '
if self.smoking or self.priv_bath or self.bed_type:
query += "WHERE "
if self.smoking:
query += "smoking = '%s' " % self.smoking
_and = 1
if self.priv_bath:
if _and:
query += "AND "
query += "priv_bath = '%s' " % self.priv_bath
_and = 1
if self.bed_type:
if _and:
query += "AND "
query += "bed_type = '%s'" % self.bed_type
cursor.execute(query)
row = DBAL.GetRowDict(cursor)
retval = []
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def SearchByPrice(self, op):
"""Return classes based on price criteria.
syntax: r_class.SearchByPrice(op)
op can be any SQL operator (=, <, >, etc)
requires an instance with a numeric price to be meaningful.
"""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM classes
WHERE price %s %s
""" % (op, self.price))
row = DBAL.GetRowDict(cursor)
retval = []
index = 0
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetNumRooms(self):
"""Get the number of bookable rooms.
syntax: r_class.GetNumRooms()
Requires that the r_class instance have a meaningful ID.
Return result includes allowed overbookings.
"""
avail = math.floor(_room.CountByClass(self.id) \
* (self.percent_overbooking/100.0 + 1))
return avail;
def GetBedType(self):
"""Get the associated bed_type object.
syntax: r_class.GetBedType()
assumes that the instance is meaningful and that the bed_type
exists.
"""
global _bed_type
type = _bed_type.GetByID(self.bed_type)
return type
def commit(self):
"""Saves the object to the database.
syntax r_class.commit()
Please use this instead of insert() or update() as commit()
attempt to resolve any conflicts. It can be considered safer
than the direct use of insert() or update()
"""
if self.id:
self.update()
else:
self.insert()
def insert(self):
"""Inserts the object into the database.
syntax r_class.insert()
Please use commit() instead of insert() or update() as it
attempts to resolve any conflicts. It can be considered safer
than the direct use of insert() or update()
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
INSERT INTO classes
(smoking, priv_bath, bed_type, price,
percent_overbooking, description)
VALUES
('%s', '%s', '%s', '%s', '%s', '%s')
""" % (self.smoking, self.priv_bath, self.bed_type, self.price,
self.percent_overbooking, self.description))
cursor.execute(DBAL.COMMIT)
cursor.close()
array = self.SearchByFeatures()
for elem in array:
if elem.description == self.description:
#and elem.price == self.price:
self.id = elem.id
def update(self):
"""Updates the object in the database.
syntax r_class.update()
Please use commit() instead of insert() or update() as it
attempts to resolve any conflicts. It can be considered safer
than the direct use of insert() or update()
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
UPDATE classes
SET percent_overbooking = '%s', smoking = '%s',
priv_bath = '%s', price = '%s',
description = '%s'
WHERE class_id = '%s'
""" % (self.percent_overbooking, self.smoking,
self.priv_bath, self.price,
self.description, self.id))
cursor.execute(DBAL.COMMIT)
cursor.close()
def delete(self):
""" Deletes the room class. No arguments necessary """
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
DELETE FROM classes WHERE class_id = '%s'
""" % (self.id))
cursor.execute(DBAL.COMMIT)
cursor.close()
_r_class = r_class('','','','','','','')
class rate_change:
"""A class for rate changes.
syntax:
rate_change(int ID, string Name, int class_id, DateTime effective_date,
bool recurring, DateTime rec_start, DateTime rec_end,
bool only_wkdays, bool only_wkends, bool is_percent
int change)
"""
def __init__(self, id, name, class_id, effective_date, recurring,
rec_start, rec_end, only_wkdays, only_wkends,
is_percent, change):
self.id = id
self.name = name
self.class_id = class_id
self.effective_date = effective_date
self.recurring = recurring
self.rec_start = rec_start
self.rec_end = rec_end
self.only_wkdays = only_wkdays
self.only_wkends = only_wkends
self.is_percent = is_percent
self.change = change
def FromRow(self, row):
"Internal database binding for select statements."
return rate_change(row['rate_change_id'], row['name'],
row['class_id'], row['effective_date'],
row['recurring'], row['recurring_start'],
row['recurring_end'], row['only_weekdays'],
row['only_weekends'], row['is_percent'],
row['change_by']
)
def GetByClassAndDay(self, class_id, date):
"Returns a list of rate_changes for CLASS_ID on date DATE."
cursor = GetCursor()
cursor.execute(DBAL.parse("""
SELECT * FROM rate_change
WHERE effective_date < %s AND (class_id = %s OR
class_id IS NULL)
"""), (date, class_id))
row = DBAL.GetRowDict(cursor)
retval = []
if date.iso_week[2] == 6 or date.iso_week[2] == 7:
is_weekend = True
else:
is_weekend = False
while row:
append = True
if row['recurring']:
if self.date_in_recurrance(date,
row['recurring_start'],
row['recurring_end']
):
pass
else:
append = False
if row['only_weekday'] and is_weekend == True:
append = False
if row['only_weekend'] and is_weekend == False:
append = False
if append:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def date_in_recurrance(self, date, start, end):
"Is DATE between START and END in the appropriate year?"
start_doy = start.day_of_year
end_doy = end.day_of_year
if start.is_leapyear and bool(date.is_leapyear) == False:
if start_doy > 58:
start_doy -= 1
elif date.is_leapyear and start_doy > 58:
start_doy += 1
if end.is_leapyear and bool(date.is_leapyear) == False:
if end_doy > 58:
end_doy -= 1
elif date.is_leapyear and end_doy > 58:
end_doy += 1
if date.day_of_year >= start_doy and date.day_of_year < end_doy:
return True
else:
return False
def MakePermanent(self):
"Applies the rate change to the class and deletes it."
if self.is_percent:
set_str = ("SET price = price * 1 + (%.1f/100)" %
self.change)
else:
set_str = "SET price = price + %s" % self.change
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
if self.class_id:
where_str = "WHERE class_id = '%s'" % self.class_id
else:
where_str = ''
cursor.execute("""
UPDATE classes
%s
%s
""" % (set_str, where_str))
cursor.execute("""
DELETE FROM rate_change
WHERE rate_change_id = '%s'
""" % self.id)
cursor.execute(DBAL.COMMIT)
cursor.close()
def adjust_price(self, price):
"Returns the price adjusted for the rate change"
if self.is_percent:
price *= (1 + self.change/100.0)
else:
price += self.change
return price
def delete(self):
"Deletes the rate change from the database."
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
DELETE FROM rate_change
WHERE rate_change_id = '%s'
""" % self.id)
def GetAll(self):
"Returns a list of all rate changes in the database."
cursor = GetCursor()
cursor.execute("SELECT * FROM rate_change")
retval = []
row = DBAL.GetRowDict(cursor)
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def commit(self):
"Saves to the database."
if self.id:
self.update()
else:
self.insert()
def insert(self):
"Internal function"
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute(DBAL.parse("""
INSERT INTO rate_change
(class_id, effective_date, recurring, recurring_start,
recurring_end, only_weekdays, only_weekends,
is_percent, change_by)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""), (self.class_id, self.effective_date,
self.recurring, self.rec_start, self.rec_end,
self.only_wkdays, self.only_wkends,
self.is_percent, self.change)
)
cursor.execute(DBAL.parse("""
SELECT * FROM rate_change
WHERE class_id = %s AND effective_date = %s
AND recurring = %s AND recurring_start = %s
AND recurring_end = %s AND only_weekdays = %s
AND only_weekends = %s AND is_percent = %s
AND change_by = %s
"""), (self.class_id, self.effective_date,
self.recurring, self.rec_start, self.rec_end,
self.only_wkdays, self.only_wkends,
self.is_percent, self.change)
)
cursor.execute(DBAL.COMMIT)
row = DBAL.GetRowDict(cursor)
self = self.FromRow(row)
cursor.close()
def update(self):
"Internal function"
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute(DBAL.parse("""
UPDATE rate_change
SET class_id = %s, effective_date = %s
recurring = %s, recurring_start = %s,
recurring_end = %s, only_weekdays = %s,
only_weekends = %s, is_percent = %s
change_by = %s
WHERE rate_change_id = %s
"""), (self.class_id, self.effective_date,
self.recurring, self.rec_start, self.rec_end,
self.only_wkdays, self.only_wkends,
self.is_percent, self.change, self.id)
)
cursor.execute(DBAL.COMMIT)
cursor.close()
_rate_change = rate_change('','','','','','','','','','','')
class room:
"""A class for rooms.
Syntax: room(room_id, class_id)
Room_id is presumed to be a string, and class_id is the id of an
r_class.
"""
def __init__(self, room_id, class_id):
self.room_id = room_id
self.class_id = class_id
def CountByClass(self, class_id):
"""Get the number of rooms in a given r_class.
syntax: room.CountByClass(class_id)
Hint: To avoid instantiating a full object, use the generic
_room object.
"""
cursor = GetCursor()
cursor.execute("""
SELECT count(room_id) AS count
FROM rooms
WHERE class_id = '%s'
""" % class_id)
row = DBAL.GetRowDict(cursor)
cursor.close()
return row['count']
def FromRow(self, row):
"""A semi-private method used to generate from db rows.
Please do not use this unless you know what you are doing."""
if row == None:
return None
return room(row["room_id"], row["class_id"])
def GetByClass(self, class_id):
"""Get rooms in an r_class (by class_id).
syntax: room.GetByClass(class_id)
It is safe to use the _room object for this method
"""
cursor = GetCursor()
cursor.execute("SELECT * FROM rooms WHERE class_id = '%s'" %
class_id)
row = DBAL.GetRowDict(cursor);
retval = []
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor);
cursor.close()
return retval
def GetByID(self, id):
"""Get room by room_id.
syntax: room.GetByID(id)
The generic _room object may be used with this method.
"""
cursor = GetCursor()
cursor.execute("SELECT * FROM rooms WHERE room_id = '%s'" % id)
row = DBAL.GetRowDict(cursor);
cursor.close()
return self.FromRow(row)
def GetRClass(self):
"""Get the associated r_class object.
syntax: room.GetRClass()
It is NOT safe to use the generic _room object with this method.
"""
global _r_class
return _r_class.GetByID(self.class_id)
def commit(self):
"""Save the object to the database.
Syntax: room.commit()
Please use commit() instead of insert() or update() because it
attempts to do some conflict resolution prior to committing to
the database.
"""
temp = self.GetByID(self.room_id)
if temp == None:
self.insert()
elif temp.room_id:
self.update()
else:
self.insert()
def insert(self):
"""Inserts the object into the database.
Syntax: room.insert()
Please use commit() instead of insert() or update() because it
attempts to do some conflict resolution prior to committing to
the database.
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
INSERT INTO rooms
(room_id, class_id)
VALUES
('%s','%s')
""" % (self.room_id, self.class_id))
cursor.execute(DBAL.COMMIT)
cursor.close()
def update(self):
"""Updates the object in the database.
Syntax: room.update()
Please use commit() instead of insert() or update() because it
attempts to do
some conflict resolution prior to committing to the database.
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
UPDATE rooms
SET class_id = '%s'
WHERE room_id = '%s'
""" % (self.class_id, self.room_id))
cursor.execute(DBAL.COMMIT)
cursor.close()
def delete(self):
""" Deletes room (left in archives). No args."""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
DELETE FROM rooms WHERE room_id = '%s'
""" % (self.room_id))
cursor.execute(DBAL.COMMIT)
cursor.close()
_room = room('', '')
class r_status:
"""A class for reservation status types.
Syntax: r_status(id, name, description)
0, and None are acceptible ID's for new objects, and valid ID's will
be assigned when committing new status's to the database.
"""
def __init__(self, id, name, description, bookable = 0):
self.id = id
self.name = name
self.description = description
self.bookable = bookable
def FromRow(self, row):
"""Returns an r_status from a dict-based database row.
Considered semiprivate. Avoid using from outside class.
"""
if row == None:
return None
return r_status(row["status_id"], row["name"],
row["description"], row['bookable'])
def GetAll(self):
"""Returns a sequence of status objects.
Syntax: r_status.GetAll()
Returns all valid status objects from database in a sequence.
The generic object _r_status can be used for this call.
"""
cursor = GetCursor()
cursor.execute("SELECT * FROM status ORDER BY status_id")
row = DBAL.GetRowDict(cursor)
retval = []
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetByID(self, id):
"""Returns a r_status object by its database id.
syntax: r_status.GetByID(id)
The generic object _r_status can be used for this call.
"""
cursor = GetCursor()
cursor.execute("SELECT * FROM status WHERE status_id = '%s'" %
id)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def GetByName(self, name):
"""Fetches an r_status based on the unique name.
syntax: r_status.GetByName(name)
The generic object _r_status can be used for this call.
"""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM status
WHERE name = '%s'
""" % name)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def GetAllArrayName(self):
"""Returns a dictionary of r_status names keyed by ID.
syntax: r_status.GetAllArrayName()
The generic object _r_status can be used for this call.
"""
all_status = self.GetAll()
retval = {}
for _status in all_status:
retval[_status.id] = _status.name
return retval
def GetBookArray(self):
"""Returns a list of status_ids that should be considered nonbookable.
Syntax _r_status.GetBookArray()
"""
cursor = GetCursor()
cursor.execute("""
SELECT status_id FROM status WHERE bookable = '%s'
""" % DBAL.FALSE)
retval = []
row = DBAL.GetRowDict(cursor)
while row:
retval.append(row['status_id'])
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def commit(self):
"""Saves the object in the database with conflict resolution.
Syntax: r_status.commit()
Please use commit() instead of insert() or update() because it
attempts conflict resolution before deteriming whether to
insert() or update().
"""
if self.id:
if self.GetByID(self.id):
self.update()
else:
self.insert()
else:
self.insert()
def insert(self):
"""Inserts the object into the database.
Syntax: r_status.insert()
Please use commit() instead of insert() or update() because it
attempts conflict resolution before deteriming whether to
insert() or update().
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
if self.id:
cursor.execute(DBAL.parse("""
INSERT INTO status
(status_id, name, description, bookable)
VALUES
(%s, %s, %s, %s)
"""), (self.id, self.name, self.bookable,
self.description))
else:
cursor.execute(DBAL.parse("""
INSERT INTO status
(status_id, name, description, bookable)
VALUES
((select max(status_id) + 1 from status),
%s, %s, %s)
"""), (self.name, self.description, self.bookable))
cursor.execute(DBAL.COMMIT)
cursor.close()
if self.id == '' or self.id == None or self.id == 0:
temp = self.GetByName(self.name)
self.id = temp.id
def update(self):
"""Attempts to update the object in the database.
Syntax: r_status.update()
Please use commit() instead of insert() or update() because it
attempts conflict resolution before deteriming whether to
insert() or update().
"""
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
UPDATE status
SET name = '%s', description = '%s', bookable = '%s'
WHERE status_id = '%s'
""" % (self.name, self.description, self.bookable, self.id))
cursor.execute(DBAL.COMMIT)
cursor.close()
_r_status = r_status('','','')
class reservation:
"""A class for hotel reservations.
Syntax: reservation(int id, int confirmation, int customer_id,
mx.DateTime date_in, mx.DateTime date_out)
return reservation object.
"""
def __init__(self, id, conf_id, customer_id, date_in, date_out):
self.reslines = None
self.id = id
self.conf = conf_id
self.customer = customer_id
self.date_in = date_in
self.date_out = date_out
def GetDateOut(self):
"""Returns a formated date-string (ISO) for the date out."""
date_array = string.split(str(self.date_out), ' ')
return date_array.pop(0)
def GetDateIn(self):
"""Returns an ISO formated date-string for the date in."""
date_array = string.split(str(self.date_in), ' ')
return date_array.pop(0)
def FromRow(self, row):
"""Semiprivate method for creating object from a dictionary."""
if row == None:
return None
retval = reservation(row["reservation_id"],
row["confirmation_id"], row["customer_id"],
row["date_in"], row["date_out"]
)
return retval
def GetUnconfirmed(self, conf_status):
"Returns list of objects with a (line) status other than VALUE."
cursor = GetCursor()
cursor.execute("""
SELECT * FROM reservations
WHERE confirmation_id IN (
SELECT confirmation_id
FROM class_reserv
WHERE status_id <> '%s'
)
""" % conf_status)
retval = []
row = DBAL.GetRowDict(cursor)
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetTotal(self):
"Returns the total price of reservation."
res_total = []
if bool(self.reslines) == False:
self.GetReslines()
for resline in self.reslines:
res_total.append(resline.GetTotalPrice())
return sum(res_total)
def Autobook(self):
"""Try to book all unconfirmed reservations.
If any line item of a reservation is not bookable, set all
line items to "Waiting List" status.
"""
global _r_status
nobook = _r_status.GetBookArray()
reservations = self.GetUnconfirmed(5)
booked = []
for reservation in reservations:
bookable = True;
reservation.GetReslines()
for resline in reservation.reslines:
if nobook.count(resline.status):
bookable = False
if bookable:
for resline in reservation.reslines:
resline.status = 5
resline.commit()
booked.append(reservation.id)
else:
for resline in reservation.reslines:
resline.status = 1
resline.commit()
for reservation in reservations:
if booked.count(reservation.id):
pass
else:
is_booked = True
for resline in reservation.reslines:
resline.status = 5
resline.commit()
if resline.status != 5:
is_booked = False
if is_booked == False:
for resline in reservation.reslines:
resline.status = 1
resline.commit()
def SearchByDateRange(self, date_from = None, date_to = None):
cursor = GetCursor()
cursor.execute(DBAL.parse(
"""
SELECT * FROM reservations WHERE
(date_in > %s AND date_in < %s) OR
(date_out > %s AND date_out < %s) OR
(date_in < %s AND date_out > %s)
"""), (date_from, date_to,
date_from, date_to,
date_from, date_to)
)
retval = []
row = DBAL.GetRowDict(cursor)
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def SearchByClassAndStatus(self, class_id = None, status = None):
'Return a list of reservations with matching class and status'
cursor = GetCursor()
query = "SELECT * FROM reservations "
Add_Paren = False
if class_id or status:
query += """WHERE confirmation_id IN (
SELECT confirmation_id
FROM class_reserv
WHERE """
Add_Paren = True
if class_id:
query += "class_id = '%s' " % class_id
if status:
if class_id:
query += "AND "
query += "status_id = '%s'" % status
retval = []
if Add_Paren:
query += ")"
cursor.execute(query)
row = DBAL.GetRowDict(cursor)
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetByCustomerID(self, cust_id):
"""Returns customer object associated with reservation."""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM reservations WHERE customer = '%s'
""" % cust_id)
retval = []
row = DBAL.GetRowDict(cursor)
while row:
temp = self.FromRow(row)
temp.GetReslines()
retval.append(temp)
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetByID(self, id):
"""Returns reservation with its associated ID.
NOTE: This ID is only for reservations entered in the
web-site. It is safer to use GetByConfID instead for most
uses. ID may be None for locally created reservations, but
self.conf will not be."""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM reservations WHERE reservation_id = '%s'
""" % id)
row = DBAL.GetRowDict(cursor)
retval = self.FromRow(row)
cursor.close()
return retval
def GetByConfID(self, conf):
"""Returns reservation object by its confirmation ID.
Note: This is the preferred way to locate a record for the
hotel-side application."""
cursor = GetCursor()
cursor.execute("""
SELECT * FROM reservations
WHERE confirmation_id = '%s'
""" % conf)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def GetByCustomerID(self, customer, archive = False):
"""Returns a sequence of objects for CUSTOMER_ID.
Second optional argument ARCHIVE can be set to true to locate
past reservations.
"""
cursor = GetCursor()
query = """
SELECT * FROM reservations WHERE customer_id = '%s'
""" % customer
if archive:
query += """
UNION SELECT * FROM archive
WHERE customer_id = '%s'
""" % customer
cursor.execute(query)
row = DBAL.GetRowDict(cursor)
retval = []
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetCustomer(self):
"Returns the associated customer for the reservation."
global _customer
return _customer.GetByID(self.customer)
def VerifyReslines(self):
"Verify vacancy for all reslines and update status as needed."
if bool(self.reslines) == False:
self.GetReslines()
for resline in self.reslines:
if (resline.CheckVacancy() < resline.quantity):
resline.status = 2
resline.commit()
def GetReslines(self):
"Sets self.reslines as a list of reslines assoc. with res."
global _res_line
self.reslines = _res_line.GetByConfID(self.conf, self)
return self.reslines
def commit(self):
"Saved object in database."
if self.conf:
if self.GetByConfID(self.conf):
self.update()
else:
self.insert()
else:
self.insert()
self.VerifyReslines();
def insert(self):
"Inserts the object into the database."
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
if self.id == None or self.id == 0:
self.id = None
cursor.execute(DBAL.parse("""
INSERT INTO reservations
(reservation_id, customer_id,
date_in, date_out)
VALUES
(%s, %s, %s, %s)
"""), (self.id, self.customer,
self.date_in, self.date_out))
cursor.execute("""
SELECT confirmation_id FROM reservations
ORDER BY confirmation_id DESC
""")
row = DBAL.GetRowDict(cursor)
self.conf = row['confirmation_id']
cursor.execute(DBAL.COMMIT)
cursor.close()
cust_seq = self.GetByCustomerID(self.customer)
return 0
def update(self):
"Updates the saved object in the database."
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute(DBAL.parse("""
UPDATE reservations
SET date_in = %s, date_out = %s
WHERE confirmation_id = %s
"""), (self.date_in, self.date_out,
self.conf))
cursor.execute(DBAL.COMMIT)
cursor.close()
def archive(self):
"Archives old members of the table (for performance reasons)."
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
today = DateTime.today()
date_archived = DateTime.DateTimeFromAbsDays(today.absdays -
60)
if DBAL.can_begin:
transaction = 1
DBAL.begin(cursor)
DBAL.RunProceedure(cursor, 'archive', [date_archived])
cursor.execute(DBAL.COMMIT)
cursor.close()
def autobook(self):
"""Tries to book all rooms in reservation.
Unbookable rooms require manual intervention.
"""
if bool(self.reslines) == False:
self.GetReslines()
for resline in self.reslines:
resline.autobook()
_reservation = reservation('', '', '', '', '')
class resline:
def __init__(self, id, res, conf, class_id, quantity, price,
will_upgrade, will_downgrade, status):
self.id = id
self.res = res
self.conf = conf
self.class_id = class_id
self.quantity = quantity
self.price = price
self.will_upgrade = will_upgrade
self.will_downgrade = will_downgrade
self.prices = []
self.day_price = {}
self.status = status
self.reservation = None
def FromRow(self, row):
retval = resline(row['class_res_id'], row['reservation_id'],
row['confirmation_id'], row['class_id'],
row['quantity'], row['price'],
row['willing_to_upgrade'], row['willing_to_downgrade'],
row['status_id'])
retval.GetPrices()
return retval
def GetTotalPrice(self):
if self.price:
return float(str(self.price))
global _reservation
if bool(self.reservation) == False:
self.reservation = _reservation.GetByConfID(self.conf)
res = self.reservation
start_date = res.date_in
end_date = res.date_out
rclass = self.GetRClass()
price = rclass.price
cur_date = start_date
if len(self.prices) == 0:
while cur_date.absdays < end_date.absdays:
self.prices.append(rclass.CheckPrice(cur_date))
rclass.price = price
cur_date = DateTime.DateTimeFromAbsDays(
cur_date.absdays + 1
)
total = sum(self.prices) * self.quantity
return total
def GetPrices(self):
global _reservation
global _r_class
if bool(self.reservation) == False:
res = _reservation.GetByConfID(self.conf)
self.reservation = res
rclass = _r_class.GetByID(self.class_id)
cprice = rclass.price
cursor = GetCursor()
delcursor = GetCursor()
self.prices = [];
self.day_price = {};
day_price = {}
delcursor.execute(DBAL.BEGIN)
cursor.execute("""
SELECT * FROM room_date_price
WHERE class_res_id = '%s'
""" % self.id)
row = DBAL.GetRowDict(cursor)
while row:
if row['res_date'].absdays < res.date_in.absdays:
delcursor.execute(DBAL.parse("""
DELETE FROM room_date_price
WHERE class_res_id = %s
AND res_date = %s
"""), (self.id, row['res_date']))
else:
self.day_price[row['res_date'].absdays] = \
row['day_price']
day_price[math.floor(
row['res_date'].absdays)] = \
row['day_price']
row = DBAL.GetRowDict(cursor)
delcursor.execute(DBAL.COMMIT)
cursor.close()
date = DateTime.DateTimeFromAbsDays(res.date_in.absdays)
while date.absdays <= (res.date_out.absdays - 0.5):
if (bool(day_price.keys().count(
math.floor(date.absdays)
))== False):
rclass.CheckPrice(date)
self.day_price[date.absdays] = rclass.price
self.SavePrice(date, rclass.price)
rclass.price = cprice
date = DateTime.DateTimeFromAbsDays(
date.absdays + 1
)
self.prices = day_price.values()
def PriceRecalc(self):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
DELETE FROM room_date_price WHERE class_res_id = '%s'
""" % self.id)
cursor.execute(DBAL.COMMIT)
cursor.close()
self.GetPrices()
def SavePrice(self, date, price):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute(DBAL.parse("""
INSERT INTO room_date_price
(class_res_id, res_date, day_price)
VALUES
(%s, %s, %s)
""") , (self.id, date, price))
cursor.execute(DBAL.COMMIT)
cursor.close()
def commit(self):
global _r_status
nobook = _r_status.GetBookArray()
if bool(nobook.count(self.status)) == False:
vacancy = self.CheckVacancy()
if vacancy < self.quantity:
self.status = 1
if self.id:
self.update()
else:
self.insert()
def insert(self):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute(DBAL.parse("""
INSERT INTO class_reserv
(reservation_id, confirmation_id, class_id, quantity,
price, willing_to_upgrade, willing_to_downgrade,
status_id)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s)
"""), (self.res, self.conf, self.class_id,
self.quantity, self.price, self.will_upgrade,
self.will_downgrade, self.status)
)
cursor.execute("""
SELECT max(class_res_id) AS id
FROM class_reserv WHERE confirmation_id = '%s'
""" % self.conf)
row = DBAL.GetRowDict(cursor)
self.id = row['id']
cursor.execute(DBAL.COMMIT)
cursor.close()
def update(self):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute(DBAL.parse("""
UPDATE class_reserv
SET price = %s,
quantity = %s,
class_id = %s,
willing_to_upgrade = %s,
willing_to_downgrade = %s,
status_id = %s
WHERE class_res_id = %s
"""), (self.price, self.quantity, self.class_id,
self.will_upgrade, self.will_downgrade, self.status,
self.id)
)
cursor.execute(DBAL.COMMIT)
cursor.close()
def GetStatus(self):
"Returns the associated status of the reservation line item."
if self.status == None:
return None
global _r_status
return _r_status.GetByID(self.status)
def GetRClass(self):
"Returns the associated r_class for the reservation line item."
global _r_class
return _r_class.GetByID(self.class_id)
def CheckVacancy(self):
"Returns the number of vacancies available for resline."
global _r_class
global _reservation
my_r_class = self.GetRClass()
if bool(self.reservation) == False:
self.reservation = _reservation.GetByConfID(self.conf)
res = self.reservation
avail = my_r_class.GetNumRooms()
test = 1
min_vacancy = avail
date = res.date_in
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
while date.absdays <= res.date_out.absdays:
query = DBAL.parse("""
SELECT sum(quantity) AS count
FROM class_reserv
WHERE class_id = %s
AND class_res_id <> %s
AND confirmation_id IN (
SELECT confirmation_id
FROM reservations
WHERE date_in <= %s
AND date_out > %s
)
AND status_id IN (
SELECT status_id
FROM status
WHERE bookable = '1'
)
""")
cursor.execute(query, (self.class_id, self.id, date,
date))
row = DBAL.GetRowDict(cursor)
vacancy = avail
if row['count']:
vacancy -= row['count']
if vacancy < min_vacancy:
min_vacancy = vacancy
date = DateTime.DateTimeFromAbsDays(date.absdays + 1)
cursor.execute(DBAL.COMMIT)
cursor.close()
return min_vacancy
def GetByID(self, id):
cursor = GetCursor()
cursor.execute("""
SELECT * from class_reserv WHERE class_res_id = '%s'
""" % id)
row = DBAL.GetRowDict(cursor)
cursor.close()
return self.FromRow(row)
def GetByConfID(self, conf, reservation = None):
if reservation:
res = reservation
retval = []
cursor = GetCursor()
cursor.execute("""
SELECT * FROM class_reserv
WHERE confirmation_id = '%s'
""" % conf)
row = DBAL.GetRowDict(cursor)
while row:
temp = self.FromRow(row)
temp.reservation = res
retval.append(temp)
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetByResID(self, res):
retval = []
cursor = GetCursor()
cursor.execute("""
SELECT * FROM class_reserv
WHERE reservation_id = '%s'
""" % res)
row = DBAL.GetRowDict(cursor)
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def autobook(self):
global _room_assign
rooms = self.GetRooms()
roomlen = len(rooms)
avail = _room_assign.GetAvailByClassID(self.class_id)
while roomlen < self.quantity and len(avail):
maxlen = len(avail)
key = math.floor(random.random() * maxlen)
room = avail.pop(key)
booking = room_assign(None, self.id, room)
booking.commit()
roomlen += 1
def GetRooms(self):
global _room_assign
return _room_assign.GetByResline(self.id)
_res_line = resline('','', '','','','','','','')
_resline = _res_line
class room_assign:
def __init__(self, id, class_res_id, room):
self.id = id
self.resline = class_res_id
self.room = room
def FromRow(self, row):
return room_assign(row['room_res_id'], row['class_res_id'],
row['room_id']
)
def remove(self):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
DELETE FROM room_reserv WHERE room_res_id = '%s'
""" % self.id)
cursor.execute(DBAL.COMMIT)
cursor.close()
def GetAvailByClassID (self, class_id):
cursor = GetCursor()
retval = []
cursor.execute("""
SELECT * FROM rooms
WHERE class_id = '%s' AND room_id NOT IN (
SELECT room_id FROM room_reserv
WHERE class_res_id IN (
SELECT class_res_id FROM class_reserv
WHERE confirmation_id IN (
SELECT confirmation_id
FROM reservations
WHERE date_in <= current_date
AND
date_out > current_date
)
)
)
""" % class_id)
row = DBAL.GetRowDict(cursor)
while row:
retval.append(row['room_id'])
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetAllAvail(self):
cursor = GetCursor()
retval = []
cursor.execute("""
SELECT * FROM rooms
WHERE room_id NOT IN (
SELECT room_id FROM room_reserv
WHERE class_res_id IN (
SELECT class_res_id FROM class_reserv
WHERE confirmation_id IN (
SELECT confirmation_id
FROM reservations
WHERE date_in <= current_date
AND
date_out > current_date
)
)
)
""")
row = DBAL.GetRowDict(cursor)
while row:
retval.append(row['room_id'])
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def GetByResline(self, resline_id):
cursor = GetCursor()
cursor.execute("""
SELECT * FROM room_reserv WHERE class_res_id = '%s'
""" % resline_id)
retval = []
row = DBAL.GetRowDict(cursor)
while row:
retval.append(self.FromRow(row))
row = DBAL.GetRowDict(cursor)
cursor.close()
return retval
def commit(self):
if self.id:
self.update()
else:
self.insert()
def insert(self):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
INSERT INTO room_reserv
(class_res_id, room_id)
VALUES
('%s', '%s')
""" % (self.resline, self.room))
cursor.execute("""
SELECT max(room_res_id) AS id
FROM room_reserv
WHERE class_res_id = '%s'
""" % self.resline)
row = DBAL.GetRowDict(cursor)
self.id = row['id']
cursor.execute(DBAL.COMMIT)
cursor.close()
def update(self):
cursor = GetCursor()
cursor.execute(DBAL.BEGIN)
cursor.execute("""
UPDATE room_reserv
SET class_res_id = '%s', room_id = '%s'
WHERE room_res_id = '%s'
""" % (self.resline, self.room, self.id))
cursor.execute(DBAL.COMMIT)
cursor.close()
def GetRoom(self):
global _room
return _room.GetByID(self.room)
def GetLastRoomByCustomerID(self, cust):
global _room
cursor = GetCursor()
cursor.execute("""
SELECT room_id FROM room_reserv
WHERE confirmation_id IN (
SELECT confirmation_id FROM reservations
WHERE customer_id = '%s'
ORDER BY date_out DESC
)
UNION
SELECT room_id FROM archive WHERE customer_id = '%s'
ORDER BY date_out DESC
""" % (cust, cust))
row = DBAL.GetRowDict(cursor)
retval = _room.GetByID(row['room_id'])
cursor.close()
return retval
_room_assign = room_assign('','','')
|