def usernameUnique(self, username):
functionName = 'usernameUnique'
validateUN =[]
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
validateUN = cursor.execute('SELECT cust_id FROM Customers WHERE username = ?', (username,))
if validateUN != 0:
validateUN = 'unique'
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
return validateUN
def Rent(self, tool_id, descriptionToolInput):
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'SELECT book_id FROM bookings WHERE tool_id = ?',
tool_id,
)
numberOfBookings = cursor.fetchone()
previousBooking = int(numberOfBookings) - 1
if previousBooking >= 0:
printNoteStateOfItem = (
'This tool was recently added, please see item description:\n'
+ descriptionToolInput)
else:
cursor.execute(
'SELECT note_in FROM bookings WHERE tool_id = ? AND book_id = ?',
tool_id,
previousBooking,
)
printNoteStateOfItem = cursor.fetchone()
DatabaseConnection.CloseDBConnection(self.databaseFilename)
return printNoteStateOfItem
def getData(self, customer_email):
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'SELECT cust_id, F_name, L_name, email FROM customers WHERE email = ?',
customer_email,
)
customer_row = cursor.fetchone()
if (customer_row != None):
customer_id = customer_row[0]
customer_firstname = customer_row[1]
customer_lastname = customer_row[2]
customer_email = customer_row[3]
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
return customer_id, customer_firstname, customer_lastname, customer_email
def createBooking(self, tool, returnedUser):
functionName = 'createBooking'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
# get IDs
cust_id = UserManager.LoadUserId(self, returnedUser)
tool_id = Tools.getId(tool)
cursor.execute('INSERT INTO Bookings (tool_id, cust_id) VALUES (?, ?)', (tool_id, cust_id))
databaseConnection.commit()
book_id = cursor.lastrowid
returnedBooking = Bookings(book_id, tool, returnedUser)
# Diconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedBooking
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def book_out(self, tool_id, dateOfBooking, lengthOfBookingInput, Delivery):
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databasefilename)
cursor = databaseConnection.cursor()
#creates list of dates to take the availability out for
booking_dates = [dateOfBooking]
i = 1
while i >= lengthOfBookingInput:
booking_dates.append(dateOfBooking + datetime.timedelta(days=1))
i += 1
#sets availability to 0 (rented) for the number of days
for dateOfBooking in booking_dates:
cursor.execute(
'UPDATE Bookings SET available = 0 WHERE tool_id = ? AND date = ?',
tool_id,
dateOfBooking,
)
#sets delivery status
if Delivery == 1:
for dateOfBooking in booking_dates:
cursor.execute(
'UPDATE Bookings SET Delivery = 1 WHERE tool_id = ? AND date = ?',
tool_id,
dateOfBooking,
)
databaseConnection.commit()
DatabaseConnection.CloseDBConnection(self.databasefilename)
def createTool(self, user, tool_name, tool_cat, tool_desc, price, halfDayPrice):
functionName = 'createTool'
try:
# get ID
cust_id = #'1'
tool_id = #'33'
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute('INSERT INTO Tools (tool_id, cust_id, tool_name, tool_cat, tool_desc, price, half_price) VALUES (?, ?, ?, ?, ?, ?, ?)', (tool_id, cust_id, tool_name, tool_cat, tool_desc, price, halfDayPrice))
databaseConnection.commit()
# create tool object
returnedTool = Tools(tool_id, user, tool_name, tool_cat, tool_desc, price, halfDayPrice)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
AvailabilityChecker.get_availability(cust_id, tool_id, int(0))
return returnedTool
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def Return(self, noteOnReturn, bookingIdReturnInput):
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'UPDATE bookings SET note_in = ? WHERE booking_id = ?',
noteOnReturn,
bookingIdReturnInput,
)
databaseConnection.commit()
DatabaseConnection.CloseDBConnection(self.databaseFilename)
def loadFutureTools(self, range_start, range_end_days = 42):
functionName = 'loadFutureTools'
# empty list
returnedToolList = []
try:
# calculationg range_end
range_end = range_start + timedelta(days = range_end_days)
userManager = UserManager(self.registeredUser, self.databaseFilename)
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT tool_id, duration FROM Bookings WHERE start_date BETWEEN ? AND ?", (range_start, range_end))
tool_rows = cursor.fetchall()
for tool in tool_rows:
# Read our values from the record
tool_id = tool[0]
cust_id = tool[1]
tool_name = tool[2]
tool_cat = tool[3]
tool_desc = tool[4]
price = tool[5]
halfDayPrice = tool[6]
# get ID
user = userManager.LoadUserId(cust_id)
# create tool
singleTool = Tools(tool_id, user, tool_name, tool_cat, tool_desc, price, halfDayPrice)
returnedToolList.append(singleTool)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedToolList
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def getList(self):
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute('SELECT customer_email FROM customers;')
mailing_list = cursor.fetchall()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
#presenting the Customer list back to us
return mailing_list
def getTool(self, customer_id):
rental_list = [
'Tool Name', 'Day Price', 'Rental Duration', 'Total Price'
]
grand_total = 0
date = (datetime.date.today().replace(day=1) -
datetime.timedelta(days=1)).strftime("%Y-%m") + "-01"
date_end = datetime.date.today().replace(day=1) - datetime.timedelta(
days=1)
delivery_charge = 5
insurance_charge = 5
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'''SELECT tool_id, price, duration, cust_id, delivery, late_return FROM Bookings WHERE cust_id= ? AND strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)'''
), (
customer_id,
date,
date_end,
)
tool_row = cursor.fetchall()
for booking in tool_row:
#checking if customer with given id borrowed any tools and if it was more than one creating a list
customer_id = booking[0]
tool_id = booking[1]
price = booking[2]
duration = booking[3]
deliveries_charge = booking[4] * delivery_charge
late_charge = booking[5]
total_price = int(price) * int(duration) + int(late_charge)
rental_list.append(tool_id, price, duration, total_price)
#rental lines creation based on retrieved data
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
grand_total = grand_total + total_price + deliveries_charge + insurance_charge
return rental_list, grand_total
def searchToolByCategory(self, search_criteria):
functionName = 'searchToolByCategory'
# empty list
returnedToolList = []
try:
userManager = UserManager(self.registeredUser, self.databaseFilename)
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT tool_id, cust_id, tool_name, tool_cat, price, tool_desc, half_price FROM Tools WHERE tool_cat = ?", (search_criteria))
tool_rows = cursor.fetchall()
for tool in tool_rows:
tool_id = tool[0]
cust_id = tool[1]
tool_name = tool[2]
tool_cat = tool[3]
price = tool[4]
tool_desc = tool[5]
half_price = tool[6]
# get user
user = userManager.LoadUserId(cust_id)
# create tool
single_tool = Tools(tool_id, user, tool_name, tool_cat, price, tool_desc, half_price)
returnedToolList.append(single_tool)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedToolList
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def searchFutureBookings(self, registeredUser):
functionName = 'SearchFutureBookings'
# empty list
RetBookingList = []
try:
toolManager = ToolManager(self.databaseFilename, registeredUser)
start_date = datetime.now()
cust_id = #'1'
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT Bookings.book_id, Bookings.tool_id FROM Bookings INNER JOIN Tools ON Bookings.tool_id = Tools.tool_id WHERE Tools.tool_start > ? AND Bookings.cust_id = ?", (start_date, cust_id))
booking_rows = cursor.fetchall()
# crete bookings in the DB
for Booking in booking_rows:
book_id = booking_rows[0]
tool_id = booking_rows[1]
# load the correct tool
tool = toolManager.loadToolId(tool_id)
# create booking
singleBooking = Booking(book_id, tool, registeredUser)
RetBookingList.append(singleBooking)
# Disconnect from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return RetBookingList
except Error as e:
# Catch and display any errors that occur
print(__name__, ':', functionName, ':', e)
raise
def add_delivery_charge(self, book_id):
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
#cursor creation for talking to db
cursor = databaseConnection.cursor()
cursor.execute(
'UPDATE bookings SET delivery = delivery + 1 WHERE book_id = ?',
book_id,
)
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
def checkIfLate(self, book_id):
return_date = datetime.datetime.today()
charge_ratio = 2
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('SELECT end_date FROM Bookings WHERE book_id = ?', book_id,)
booking_dates = cursor.fetchone()
#late_days = 1
late_days = (return_date - booking_dates).days
#counts how many days overdue
late_charge = charge_ratio * late_days
#adds the ratio for later calculation
DatabaseConnection.CloseDBConnection(databaseConnection)
return late_charge
def Broken(self, maToolInput, brokenNoteInput):
today = datetime.date.today()
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute('SELECT tool_id FROM bookings WHERE book_id = ?',
maToolInput)
tool_id = cursor.fetchone()
cursor.execute(
'UPDATE bookings SET note_out = ? AND available = 0 WHERE tool_id = ? AND date >= ?',
brokenNoteInput,
tool_id,
today,
)
databaseConnection.commit()
DatabaseConnection.CloseDBConnection(self.databaseFilename)
def markAvailability(self, tool_id):
functionName = 'markAvailability'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('UPDATE Tools SET available = 0 WHERE tool_id = ?', (tool_id))
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def bookOutNotes(self, book_id, brokenNoteInput):
functionName = 'bookOutNotes'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('UPDATE Bookings SET note_out = ? WHERE book_id = ?', (brokenNoteInput, book_id))
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def searchToolByName(self, tool_name):
functionName = 'searchToolByName'
# empty list
returnedToolList = []
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT tool_id, cust_id, tool_name, tool_cat, price FROM Tools WHERE tool_name LIKE ?", ( tool_name))
tool_rows = cursor.fetchall()
for tool in tool_rows:
tool_id = tool[0]
cust_id = tool[1]
tool_name = tool[2]
tool_cat = tool[3]
price = tool[4]
# create tool
single_tool = tool(tool_id, cust_id, tool_name, tool_cat, price)
returnedToolList.append(single_tool)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedToolList
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def loadToolName(self, tool_name):
functionName = 'loadToolName'
# empty list
returnedTool = None
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT cust_id, tool_id, tool_name, tool_desc, tool_cat, price, half_price FROM Tools WHERE tool_name = ?", (tool_name))
tool_row = cursor.fetchone()
if (tool_row != None):
tool_id = tool_row[1]
cust_id = tool_row[0]
tool_name = tool_row[2]
tool_desc = tool_row[3]
tool_cat = tool_row[4]
price = tool_row[5]
halfDayPrice = tool_row[6]
# create tool
returnedTool = Tools(tool_id, cust_id, tool_name, tool_desc, tool_cat, price, halfDayPrice)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedTool
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def returnItem(self, book_id):
functionName = 'confirms the return and adds late charges if needed'
late_charge = LateCharge.checkIfLate(self, book_id)
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('UPDATE Bookings SET late_return = ? WHERE book_id = ?', (late_charge, book_id,))
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def LoadUser(databasefilename, criteria, value):
functionName = 'LoadUser'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
databasefilename)
cursor = databaseConnection.cursor()
cursor.execute(
'SELECT cust_id, F_name, L_name, email, username FROM Customers WHERE ? = ?',
(criteria, value))
user_row = cursor.fetchone()
if (user_row != None):
cust_id = user_row[0]
F_name = user_row[1]
L_name = user_row[2]
email = user_row[3]
username = user_row[4]
# create user
returnedUser = User(cust_id, F_name, L_name, email, username)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedUser
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def get_availability(self, tool_id, lengthOfBookingInput):
#checks what date it is today
today = datetime.date.today()
#checks how many days out of 6 weeks have availability set up for specific tool
databaseConnection = DatabaseConnection.CreateDBConnection(
'SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute(
'SELECT count(*) FROM bookings WHERE tool_id = ? AND start_date > ?',
(
tool_id,
today,
))
missing_days = cursor.fetchone()
#counting from which date forward availability must be set
missing_availability = today + datetime.timedelta(days=missing_days)
#brings back all arguments for a tool from a day when it is not rented - cust_id is empty
cursor.execute(
'SELECT tool_id, price, date FROM bookings WHERE tool_id = ? AND available = 1',
tool_id,
)
availability_row = cursor.fetchone()
if (availability_row != None):
tool_id = availability_row[0]
price = availability_row[1]
date = availability_row[2]
#generates a list of dates that do not have availability set
new_dates = []
i = 1
while i < (43 - missing_days):
new_dates.append(missing_availability + datetime.timedelta(days=i))
i += 1
#sets up availability for up to 6 weeks ahead for days that weren't set
for missing_availability in new_dates:
cursor.execute(
'INSERT INTO Bookings (tool_id, price, start_date, cust_id, delivery, available) VALUES(?, ?, ?, ?, ?, ?)',
(
tool_id,
price,
missing_availability,
0,
0,
1,
))
databaseConnection.commit()
#fetches days the tool is actually available for the upcoming 6 weeks
self.date_generator()
for from_date in new_dates:
end_date = from_date + datetime.timedelta(
days=lengthOfBookingInput)
cursor.execute(
'SELECT date FROM bookings WHERE tool_id = ? AND (date BETWEEN ? AND ?) AND available = 1',
(
tool_id,
from_date,
end_date,
))
days_available = cursor.fetchall()
DatabaseConnection.CloseDBConnection(self.databasefilename)
days_available = []
for date in quick_dates:
quick_dates = date[0]
days_available.append(quick_dates)
#reforges the list into list
return days_available
