def getBookings(argsDict,likeElems=[]):
# Returns a list with all the bookings matching the parameters provided in the
# dictionary. If no bookings are found it will return an empty list. This fuction
# doesn't check whether the parameters are allowed in config.ini
valsList = []
if len(argsDict) == 0:
statement = "SELECT * FROM bookings"
else:
i = 0
statement = "SELECT * FROM bookings WHERE "
for key,value in argsDict.items():
if i >= 1:
#If i needs to be queried by like then put 'LIKE' in statement else use normal '='
statement += "AND " + key +("LIKE ? " if (i in likeElems) else'=? ')
else:
statement += key + (" LIKE ? " if (i in likeElems) else"=? ")
valsList.append(value)
i += 1
try:
conn = dbConnection.connect()
if len(valsList) == 0:
cursor = conn.execute(statement)
else:
cursor = conn.execute(
statement,
valsList)
return cursor.fetchall()
except sqlite3.Error:
raise
def makeBooking(customerId, bookingDateTime, reason):
conn = dbConnection.connect()
try:
curson = conn.execute(
"INSERT INTO bookings (customerId,timeStampBook,reason) VALUES(?,?,?)",
(customerId, bookingDateTime, reason),
)
conn.commit()
except sqlite3.Error as e:
print("Error: {}".format(e))
finally:
conn.close()
def makeCustomer(name,address,telephone):
# Insert Customer into database
conn=dbConnection.connect()
try:
conn.execute("INSERT INTO customers (name,address,telephone) VALUES(?,?,?)",
(name, address,telephone))
conn.commit()
print("Successfully created {}".format(name))
except sqlite3.Error as e:
print('Error: {}'.format(e))
finally:
conn.close()
def getCustomerId(name,address=None,limit=2):
conn=dbConnection.connect()
if address == None:
try:
# count(*) used as sqlite doesn't know rows returned without iterating
# over it first
cursor = conn.execute(
"SELECT count(*) FROM customers WHERE name=? LIMIT ?",
(name,limit)
)
rowsReturned = cursor.fetchone()[0]
if rowsReturned > 0:
# Get actual customer id
cursor = conn.execute(
"SELECT customerId FROM customers WHERE name=? LIMIT ?",
(name,limit)
)
return cursor.fetchall()
else:
raise LookupError("No customer found")
except sqlite3.Error as e:
raise sqlite3.Error(e)
finally:
conn.close()
elif address != None:
try:
# Basically the same as above just with an address, same checks
# But less likely to happen as it's v. unlikely two customers
# will have the same name and address
cursor = conn.execute(
"SELECT count(*) FROM customers WHERE name=? AND address=? LIMIT ?",
(name,address,limit)
)
rowsReturned = cursor.fetchone()[0]
if rowsReturned > 0:
# Get actual customer id
# cursor = dbConnection.connect('bookings.db')
cursor = conn.execute(
"SELECT customerId FROM customers WHERE name=? AND address=? LIMIT ?",
(name,address,limit)
)
return cursor.fetchall()
else:
raise LookupError("No customer found")
except sqlite3.Error as e:
raise sqlite3.Error(e)
finally:
conn.close()
def getCustomerInfoFromId(customerId,attributesWanted=['name','address']):
# Get a customers information, by default name and address
# From their id, the attryibutes wanted are passed in through
# a list
statement = "SELECT "
if len(attributesWanted)==1:
statement += attributesWanted[0]
elif len(attributesWanted)==0:
raise ValueError("No attributes to select")
else:
i=0
while i<len(attributesWanted):
# check the values provided by the user against
# customers -> list by, in config, as '?' can't be used
# for column names in sql
searchableArgs = getArgsBy(getConfPart('listBy','customers').strip(),',')
if attributesWanted[i] in searchableArgs:
if i != len(attributesWanted)-1 :
statement += attributesWanted[i] + ","
else:
statement += attributesWanted[i]
else:
raise ValueError("Unknown attribute required")
i += 1
# No need to check if customer is unique as id is used
# And is marked so in the db
statement += " FROM customers WHERE customerId=?"
conn = dbConnection.connect()
try:
cursor = conn.execute(
statement,
(customerId,)
)
customerInfo = cursor.fetchone()
if customerInfo == [''] or customerInfo == None:
raise LookupError("No matching customer found")
else:
return customerInfo
except sqlite3.Error as e:
print("Error: {}".format(e))
def listAvailable(argsString):
# If args is blank then assume that today was wanted
args = getArgsBy(argsString,',|=| ')
conn = dbConnection.connect()
openTimes = getArgsBy(getConfPart('openTimes'),',')
bookingLength = getConfPart('bookingLength')
try:
#Sets both variable to be equal to the original dateTime
openTime, absOpenTime = (datetime.strptime(openTimes[0],'%H:%M'),)*2
closeTime, absCloseTime = (datetime.strptime(openTimes[1],'%H:%M'),)*2
#If 'today' is supplied as an argument then change it to be equal to the actual date
if 'today' in args:
args = [arg.replace('today',datetime.today().strftime('%d/%m/%Y')) for arg in args]
if args == ['']:
date = datetime.today()
elif len(args) == 1:
#Use date from args, ValueError will be made if invalid date is used
date = datetime.strptime(args[0],'%d/%m/%Y').date()
elif len(args) == 2:
#Just 'times' supplied, so use that and todays date
date = datetime.today()
if args[0] == 'times':
openTime = datetime.strptime(getArgsBy(args[1],'-')[0],'%H:%M')
closeTime = datetime.strptime(getArgsBy(args[1],'-')[1],'%H:%M')
else:
print("Invalid argument '{}' supplied, was expecting times".format(args[0]))
outputs.decideWhatToDo()
elif len(args) == 3:
#Use date from args and get and use time args
if args[1] == 'times':
# In form `lba 1/1/1970 times=0:00-5:00`
date = datetime.strptime(args[0],'%d/%m/%Y').date()
openTime = datetime.strptime(getArgsBy(args[2],'-')[0],'%H:%M')
closeTime = datetime.strptime(getArgsBy(args[2],'-')[1],'%H:%M')
elif args[0] == 'times':
# In form `lba times=0:00-5:00 1/1/1970`
date = datetime.strptime(args[2],'%d/%m/%Y').date()
openTime = datetime.strptime(getArgsBy(args[1],'-')[0],'%H:%M')
closeTime = datetime.strptime(getArgsBy(args[1],'-')[1],'%H:%M')
else:
print("Expected times argument, none given")
outputs.decideWhatToDo()
else:
#If nothing usable was supplied then give an error
print("Invalid arguments supplied")
outputs.decideWhatToDo()
except ValueError as e:
print("Error getting date: {}".format(e))
outputs.decideWhatToDo()
print(date.strftime('%Y-%m-%d'))
#timeStampBook is to be queried by LIKE hence the [0] as it's the first elem in the dict
bookings = getBookings({'timeStampBook':str(date.strftime('%Y-%m-%d'))+'%'},[0])
bookingTimes = []
for i in range(0,len(bookings)):
#Remove date and append to booking times
bookingTimes.append(bookings[i-1][2][11:])
curTime = openTime
if openTime <= absOpenTime:
curTime = absOpenTime
if closeTime >= absCloseTime:
closeTime = absCloseTime
while curTime <= closeTime:
#Loop through bookings, increment 1 bookingLength at a time
#If it matches a booking then don't add that time to the list
if str(curTime.time()) not in bookingTimes:
print("Aavailable: {}".format(curTime.time()))
curTime = curTime + timedelta(minutes=int(bookingLength))
outputs.decideWhatToDo()
def remove(argsDict,limit=1,confirm=True):
#Deletes bookings matching parameters given, if the amount of bookings matching the
#parameters exceedes the limit then a ValueError will be thrown
#Also by defualt a confirm message will come up
i = 0
whereClause = ""
valsList = []
#First check generate a where clause that can be used across both the select and
#remove statements
for key,value in argsDict.items():
if i >=1:
whereClause += "AND " + key + "=? "
else:
whereClause += key + "=? "
valsList.append(value)
i += 1
#Append limit +1 to check if too many results are returned
valsList.append(str(limit+1))
conn = dbConnection.connect()
statement = "SELECT customerId FROM customers WHERE " + whereClause + " LIMIT ?"
print(statement)
#print(valsList)
try:
cursor = conn.execute(
statement,
valsList
)
customerIds = cursor.fetchall()
print(customerIds)
#outputs.decideWhatToDo()
if len(customerIds) > limit:
raise LookupError("More customers found than specified limit")
elif len(customerIds) == 0:
raise LookupError("No customers found")
else:
confirmDelete=None
#Loop through all the bookings returned and as it's a nested tuple list
#it's in the form customerIds[i][0]
for i in range(0,len(customerIds)):
# By defualt this will happen as confirm defualts to True
# However in the future this may not be desired and so can
# be set to remove without customer detail confrimation
if confirm:
try:
#Loop through
customerInfo = getCustomerInfoFromId(customerIds[i][0])
except ValueError as e:
print("Value error: {}".format(e))
outputs.decideWhatToDo()
except LookupError as e:
print("Lookup error: {}".format(e))
outputs.decideWhatToDo()
except Exception as e:
print("Unexpected error: {}".format(e))
outputs.decideWhatToDo()
print("Delete customer {}, address {}".format(customerInfo[0],customerInfo[1]))
confirmDelete = input("(y/n): ")
if confirmDelete.lower() == "y" or (confirm==False and confirmDelete==None):
print(customerIds[i][0])
whereClause = "customerId=?"
statement = "DELETE FROM customers WHERE " + whereClause
print(statement)
conn.execute(
statement,
(str(customerIds[i][0]),)
)
conn.commit()
else:
print("Booking not deleted")
return 1
except sqlite3.Error as e:
raise sqlite3.Error(e)
