def get(self):
user = users.get_current_user()
if not user: # if user isn't logged in, redirect to walker register
self.redirect(users.create_login_url('/walker_register'))
return
else:
email = users.get_current_user().email()
db = db_handler.DbHandler()
db.connectDb()
# check if an entry exists in the DB, (value,column,table)
if db.exists(email, "Walkers_Email", "Dog_Walker"):
template = jinja_environment.get_template(
'welcome_walker.html')
self.response.write(template.render())
db.disconnectDb()
return
if db.exists(
user.email(), "Owners_Email",
"Dog_Owner"): # makes sure email isn't registered as Owner
template = jinja_environment.get_template('not_walker.html')
self.response.write(template.render())
db.disconnectDb()
return
else: # email isn't registered in DB
template = jinja_environment.get_template(
'walker_register.html')
parameters_for_template = {'user_email': email}
self.response.write(template.render(parameters_for_template))
db.disconnectDb()
def get(self):
user = users.get_current_user() # get current user
db = db_handler.DbHandler()
db.connectDb() # connect to DB
if not user: # if user isn't logged in, redirect to log in
self.redirect(users.create_login_url('/show_clients'))
return
if db.exists(user.email(), "Walkers_Email", "Dog_Walker"):
walker = DogWalker.DogWalker()
walker.walker_email = user.email()
lst = list(filter(None, walker.get_clients()))
# creates a list of lists, each internal list represents a client, last element in list is his age
client_list = [[
client[0], client[1], client[2],
int((date.today() - client[3]).days / 365.2425)
] for client in lst]
template = jinja_environment.get_template(
'show_clients_for_walker.html')
parameters_for_template = {'clients': client_list}
self.response.write(template.render(parameters_for_template))
db.disconnectDb()
return
if db.exists(user.email(), "Owners_Email", "Dog_Owner"):
template = jinja_environment.get_template('not_walker.html')
self.response.write(template.render())
db.disconnectDb()
return
else:
template = jinja_environment.get_template('not_registered.html')
self.response.write(template.render())
db.disconnectDb()
def register_dog(data):
try:
logging.info("data contains: " + str(data))
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
### SQL QUERY AND EXECUTE: ####
### INSERT TO TAKING_DOGS ###
sql = """ INSERT INTO Taking_Dogs VALUES (%s,%s,%s) """
cursor.execute(sql, (
data[0],
data[1],
data[2],
))
## COMMIT ##
db_connection.commit()
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False #return False in case of an error
finally:
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return True #return True if registration was successful
def get(self):
user = users.get_current_user()
db = db_handler.DbHandler()
db.connectDb()
if not user: # if user isn't logged in, redirect to log in
self.redirect(users.create_login_url('/dog_register'))
return
if db.exists(user.email(), "Owners_Email",
"Dog_Owner"): # checks if owner's email exists in DB
# Display the form - class home_or_another_dog will handle it with its post method
template = jinja_environment.get_template('dog_register.html')
self.response.write(template.render())
db.disconnectDb()
return
if db.exists(user.email(), "Walkers_Email",
"Dog_Walker"): # checks if email exists as a walker in DB
template = jinja_environment.get_template('not_owner.html')
self.response.write(template.render())
db.disconnectDb()
return
else:
template = jinja_environment.get_template(
'not_registered.html') # if email doesn't exist in DB
self.response.write(template.render())
db.disconnectDb()
def get(self):
user = users.get_current_user()
db = db_handler.DbHandler()
db.connectDb()
if not user: # if user isn't logged in, redirect back to page
self.redirect(users.create_login_url('/walk_register'))
return
if db.exists(user.email(), "Owners_Email", "Dog_Owner"):
email = users.get_current_user().email()
cursor = db.get_cursor()
query = """
SELECT Dog_ID, Breeds_Name
FROM Dog
Where Owners_Email = \'{}\'
""".format(email)
cursor.execute(query)
dogs_tuples = cursor.fetchall()
template = jinja_environment.get_template('walk_register.html')
parameters_for_template = {'dogs_tuples': dogs_tuples}
self.response.out.write(template.render(parameters_for_template))
db.disconnectDb()
return
if db.exists(user.email(), "Walkers_Email", "Dog_Walker"):
template = jinja_environment.get_template('not_owner.html')
self.response.write(template.render())
db.disconnectDb()
return
else:
template = jinja_environment.get_template('not_registered.html')
self.response.write(template.render())
db.disconnectDb()
def __init__(self):
self.w_DbHandler = db_handler.DbHandler()
self.w_email = ""
self.w_name = ""
self.w_telephone = ""
self.w_city = ""
self.w_breeds = []
self.w_days = []
self.w_price = {
'sun': None,
'mon': None,
'tue': None,
'wed': None,
'thu': None,
'fri': None,
'sat': None
} # price for eavery week day
self.w_dog_num = {
'sun': None,
'mon': None,
'tue': None,
'wed': None,
'thu': None,
'fri': None,
'sat': None
} # dog capacity for each day of week
def getUnregisteredDogs(email):
try:
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### GET LIST OF ALL UNREGISTERED DOGS ####
sql = """ SELECT Dogs.Dog_Name, hd.Dog_ID, Dogs.Type_ID
FROM Has_Dogs as hd LEFT JOIN Dogs ON hd.Dog_ID = Dogs.Dog_ID
LEFT JOIN Taking_Dogs as td ON Dogs.Dog_ID = td.Dog_ID
WHERE hd.email = %s AND NOT EXISTS (SELECT Dog_ID
FROM Taking_Dogs as td WHERE td.Dog_ID = hd.Dog_ID)"""
cursor.execute(sql, (email, ))
data = cursor.fetchall()
logging.info("Unregistered Dogs: " + str(data))
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False
finally: #### IMPORTANT ####
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return data
def get(self):
user = users.get_current_user()
if not user: # if user isn't logged in, sends user back to register page (this class) after he logs in
self.redirect(users.create_login_url('/owner_register'))
return
email = users.get_current_user().email() # gets user's email
db = db_handler.DbHandler()
db.connectDb()
# check if an entry exists in the DB, (value,column,table)
if db.exists(email, "Walkers_Email", "Dog_Walker"):
template = jinja_environment.get_template('not_owner.html')
self.response.write(template.render())
db.disconnectDb()
return
# if user exists, send user to welcome_owner.html (registered owner page)
if db.exists(email, "Owners_Email", "Dog_Owner"):
template = jinja_environment.get_template('welcome_owner.html')
self.response.write(template.render())
db.disconnectDb()
return
else: # user isn't registered in DB, send user to owner_register.html
template = jinja_environment.get_template('owner_register.html')
parameters_for_template = {'user_email': email}
self.response.write(template.render(parameters_for_template))
db.disconnectDb()
def __init__(self, start_date, end_date,
dog_id): # creating the constructor with default values
self.start_date = start_date
self.end_date = end_date
self.dog_id = dog_id
self.m_DbHandler = db_handler.DbHandler()
def getByCity(self): #apply fliter
try:
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### SQL QUERY AND EXECUTE: ####
sql_query = """ SELECT Person.p_name, Person.email, Person.city, swd.Day_Of_Work, dw.Day_Price, dw.Max_DogsPerDay, takeo.Type_ID, COUNT(td.dog_id)
FROM ((Specific_Working_Days as swd LEFT JOIN Taking_Dogs td on swd.email=td.email and swd.day_of_work=td.day_of_work) join Dog_Walker as dw on dw.email=swd.email) join Person on Person.email=dw.email
join Takes_Only as takeo ON dw.email = takeo.email
WHERE Person.city=%s AND Type_ID = %s
GROUP BY Person.p_name, Person.email, Person.city, swd.Day_Of_Work, dw.Day_Price, dw.Max_DogsPerDay, takeo.Type_ID """
self.row_count = cursor.execute(sql_query, (self.city,self.type_id,))
self.data = cursor.fetchall()
logging.info("filter: "+str(self.data))
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: "+str(e))
return False
finally: #### IMPORTANT ####
if db_connection.status(): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return self.data
def __init__(self, price, num_of_dogs, day_name,
walker_email): # creating the constructor with default values
self.price = price
self.num_of_dogs = num_of_dogs
self.day_name = day_name
self.walker_email = walker_email
self.m_DbHandler = db_handler.DbHandler()
def __init__(self): # creating the constructor with default values
self.owner_name = ''
self.owner_phone = ''
self.owner_city = ''
self.owner_dogs = ''
self.owner_email = ''
self.owner_birth_date = ''
self.m_DbHandler = db_handler.DbHandler()
def __init__(self):
self.d_DbHandler = db_handler.DbHandler()
self.d_id = 0
self.d_name = ""
self.d_gender = ""
self.d_age = ""
self.d_o_email = ""
self.d_breed = ""
def __init__(self):
self.o_DbHandler = db_handler.DbHandler()
self.o_email = ""
self.o_name = ""
self.o_city = ""
self.o_bday = ""
self.o_telephone = ""
self.o_age = ""
def __init__(self):
logging.info('email')
self.u_DbHandler=db_handler.DbHandler()
# create data members of the class Email
self.u_City = ""
self.u_NumberOfRows = 0
self.u_RetrievedCityList=[]
def walker_schedule(email):
data = {}
try:
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
### STEP 1: GET DOG WALKER'S SCHEDULE ####
# receive the list of dog owners that the dog walker scheduled to take for each day of the week.
data = {'day_count': []}
total_dogs_to_take = 0
work_schedule = {}
for each_day in range(1,
8): #from sunday to saturday (excluding num 8)
sql = """ SELECT re1.dog_name, Person.City, Person.p_name, Person.phone_number, Price
FROM (SELECT Taking_Dogs.Email AS Dog_walker_email, Dogs.Dog_ID,Dogs.Dog_Name, Species.Dog_Type, Taking_Dogs.Day_of_Work, dw.Day_Price as Price
FROM Taking_Dogs JOIN Dogs ON Taking_Dogs.Dog_ID=Dogs.dog_ID
JOIN Species ON Dogs.type_id=Species.type_id
JOIN Dog_Walker as dw ON Taking_Dogs.email = dw.email) as re1
JOIN Has_Dogs ON re1.dog_id=Has_Dogs.Dog_ID
JOIN Person ON Has_Dogs.Email= Person.Email
WHERE re1.dog_walker_email=%s AND re1.day_of_work=%s """
row_count = cursor.execute(sql, (
email,
each_day,
))
list_of_dogs_to_take = cursor.fetchall()
work_schedule[each_day] = list_of_dogs_to_take
total_dogs_to_take += int(row_count)
data['day_count'].append(row_count)
#### STEP 2: FIND DOG OWNER'S TOTAL WEEK REVENUE ####
sql = """ SELECT SUM(dw.day_price)
FROM Taking_Dogs as td LEFT JOIN Dog_Walker as dw ON dw.email = td.email
WHERE td.email = %s """
cursor.execute(sql, (email, ))
data['total_revenue'] = cursor.fetchone()[0]
#### STEP 3: FIND MAX DOGS PER DAY ####
sql = """ SELECT Max_DogsPerDay FROM Dog_Walker WHERE email = %s """
cursor.execute(sql, (email, ))
data['max_dogsperday'] = cursor.fetchone()[0]
data[
'work_schedule'] = work_schedule # assign work schedule to the data dict
data['days'] = days # assign the list of days to the data dict
data['total_dogs_to_take'] = total_dogs_to_take
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False
finally: #### IMPORTANT ####
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return data
def __init__(self): # creating the constructor with default values
self.breed = ''
self.breed_name = ''
self.dog_id = ''
self.dog_name = ''
self.dog_age = ''
self.dog_gender = ''
self.owner_email = ''
self.m_DbHandler = db_handler.DbHandler()
def register(self): #apply registration
try:
#### DATABASE CONNECTION OBJECT ####
db_connection = db_handler.DbHandler()
### CONNECT TO DB ###
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### SQL QUERY AND EXECUTE: ####
### STEP 1: first insert: person table ###
sql = """ INSERT INTO Person VALUES (%s,%s,%s,%s) """
cursor.execute(sql, (
self.email,
self.name,
self.phone,
self.city,
))
### STEP 2: second insert: Dog Walker table
sql = """ INSERT INTO Dog_Walker VALUES (%s,%s,%s) """
cursor.execute(sql, (
self.email,
self.day_price,
self.max_dogsperday,
))
## STEP 3: third insert: Specific working days table
for each_day in self.specific_working_days:
sql = """ INSERT INTO Specific_Working_Days VALUES (%s,%s) """
cursor.execute(sql, (
self.email,
each_day,
))
## COMMIT ##
db_connection.commit()
## STEP 4: forth insert: Takes only table
for each_dogtype in self.takes_only:
sql = """ INSERT INTO Takes_Only VALUES (%s,%s) """
cursor.execute(sql, (
self.email,
each_dogtype,
))
## COMMIT ##
db_connection.commit()
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False #return False in case of an error
finally:
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return True #return True if registration was successful
def __init__(self):
self.t_DbHandler = db_handler.DbHandler()
self.dogID = ""
self.d_name = ""
self.t_date = ""
self.d_breed = ""
self.d_age = ""
self.t_fromHome = False
self.t_day = ""
self.w_email = ""
def __init__(self, name, phone, city, email,
sign_date): # creating the constructor with default values
self.walker_name = name
self.walker_phone = phone
self.walker_city = city
self.walker_breeds = ""
self.walker_monthly_fee = ""
self.walker_email = email
self.walker_sign_date = sign_date
self.walker_monthly_fee = ""
self.m_DbHandler = db_handler.DbHandler()
def match_walkers(start_date,end_date,owner_days,dog_breed):
db = db_handler.DbHandler()
db.connectDb()
cursor = db.get_cursor()
# query to get table of relevant walkers depending of breed and days that the owner wants
query = """
select Walking_Days,Dog_Walker.Walkers_Email
from Dog_Walker join Willing_To_Take on Dog_Walker.Walkers_Email=Willing_To_Take.Walkers_Email join
Day_Of_The_Week on Day_Of_The_Week.Walkers_Email=Willing_To_Take.Walkers_Email
Where Breeds_Name = \'{}\'
""".format(dog_breed)
cursor.execute(query)
dog_walkers = cursor.fetchall()
start_dt = datetime.strptime(start_date, '%Y-%m-%d').date() # creating the starting date string as a Date object
end_dt = datetime.strptime(end_date, '%Y-%m-%d').date() # creating the ending date string as a Date object
trip_dates = Date_Range.daterange(start_dt, end_dt) # creates a list of dates in the range requested
day_date = [(day.strftime("%A"), day) for day in
trip_dates] # creates a list of tuples containing (day,date) format
week_days = []
# match walkers to desired trip days - working with regular list:
# loop for every date the owner wants, to get the a list of (day,date) tuple format
# every tuple in the list represents the date and email of the relevant walker for this date in which the owner wants a walk
for date in day_date:
for day in dog_walkers: # for every walker, find his working days
# if the day matches the desired day by the owner, add the tuple to the list
if date[0] == day[0] and day[0] in owner_days:
week_days.append((date[1].strftime('%Y-%m-%d'), day[1]))
relevant_walkers = []
for tuple in week_days:
date = tuple[0]
email = tuple[1]
query = """
select DW.Walkers_Name,DW.Walkers_Phone,DW.Walkers_Email,Days_Price,\"{}\"
from Day_Of_The_Week DOF join Dog_Walker DW on DW.Walkers_Email=DOF.Walkers_Email
Where dayname(\"{}\")= DOF.Walking_Days and DW.Walkers_Email=\"{}\"
and (Max_Numbers_of_Dogs>
((select count(Walks_Specific_date)
from Walk W
where W.Walkers_Email=\"{}\" and W.Walks_Specific_date=\"{}\"
group by W.Walks_Specific_date)) or \"{}\" NOT IN(select WO.Walks_Specific_date
from Walk WO
where WO.Walkers_Email=\"{}\"));
""".format(date,date,email,email,date,date,email)
cursor.execute(query)
data = cursor.fetchall()
# retrieve walker information(Name,phone,email and price) and add it to relevant walkers list
relevant_walkers.append(data)
db.disconnectDb()
return relevant_walkers # return a list of relevant walkers with their info
def __init__(self):
logging.info('Initializing Users Details')
self.u_DbHandler=db_handler.DbHandler()
# create data members of the class Users
self.u_Owner_Email = ""
self.u_OwnerName = ""
self.u_Owner_Phone = 0
self.u_OwnerCity = ""
self.u_Dog_Name = ""
self.u_Dog_Sex = ""
self.u_DogType = ""
self.u_PreferDayForTrip = ""
def get(self):
user = users.get_current_user()
db = db_handler.DbHandler()
db.connectDb()
if not user: # if user isn't logged in, redirect to login page and then to home page
self.redirect(users.create_login_url('/'))
return
if db.exists(user.email(), "Walkers_Email",
"Dog_Walker"): # when we have a walker
email = users.get_current_user().email()
cursor = db.get_cursor()
query = """
SELECT Walkers_Name
FROM Dog_Walker
Where Walkers_Email = \'{}\'
""".format(email)
cursor.execute(query)
name = cursor.fetchone()
db.disconnectDb()
template = jinja_environment.get_template(
'walker_main.html') # shows the walker main page
parameters_for_template = {'name': name[0]}
self.response.write(template.render(parameters_for_template))
return
if db.exists(user.email(), "Walkers_Email",
"Dog_Walker"): # when we have an owner
email = users.get_current_user().email()
db.connectDb()
cursor = db.get_cursor()
query = """
SELECT Owners_Name
FROM Dog_Owner
Where Owners_Email = \'{}\'
""".format(email)
cursor.execute(query)
name = cursor.fetchone()
db.disconnectDb()
template = jinja_environment.get_template(
'owner_main.html') # shows the owner main page
parameters_for_template = {'name': name[0]}
self.response.write(template.render(parameters_for_template))
return
else: # user isn't registered
template = jinja_environment.get_template('not_registered.html')
self.response.write(template.render())
db.disconnectDb()
def __init__(self, email):
self.d_DbHandler = db_handler.DbHandler()
self.owner_email = email
self.owner_first_name = ""
self.owner_last_name = ""
self.owner_full_name = ""
self.owner_phone = ""
self.dog_name = ""
self.dog_gender = ""
self.dog_type = ""
self.owner_city = ""
self.prefered_walking_days = ""
self.dog_info = {}
self.schedule = [[],[],[],[],[],[],[]]
def recognize_person(email):
try:
logging.info("Recognizing person")
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### SQL QUERY AND EXECUTE: ####
sql_query = """ SELECT email FROM Person WHERE email = %s """
person_exists = cursor.execute(
sql_query, (email, )) #returns 0 if no row has been fetched
logging.info(str(person_exists)) # log value
if person_exists == 0:
logging.info("Person isn't in database")
return 0 #return 0 if the user is not registered at all
#### SQL QUERY AND EXECUTE: ####
sql_query = """ SELECT email FROM Dog_Owner WHERE email = %s """
dog_owner_exists = cursor.execute(
sql_query, (email, )) #returns 0 if person isn't a dog owner
sql_query = """ SELECT email FROM Dog_Walker WHERE email = %s """
dog_walker_exists = cursor.execute(
sql_query, (email, )) #returns 0 if person isn't a dog walker
if dog_owner_exists == 1:
### Check if dog owner is a regular user:
sql_query = """ SELECT email FROM Regular_Dog_Owner WHERE email = %s """
reg_dog_owner = cursor.execute(
sql_query,
(email, )) #returns 0 if person isn't a regular dog owner
if reg_dog_owner == 1:
logging.info("Person was recognized as a Regular Dog Owner")
return 1
else:
logging.info("Person was recognized as a Premium Dog Owner")
return 2
if dog_walker_exists == 1:
logging.info("Person was recognized as Dog Walker")
return 3
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False
finally:
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
def add(self):
try:
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### SQL QUERY AND EXECUTE: ####
logging.info("Dog Info: " + self.dog_name + "," + self.dog_gender +
"," + self.dog_age + "," +
self.type_id) # send to the log the new dog details
### STEP 1: first insert: dogs table ###
sql = """ INSERT INTO Dogs(Dog_Name, Dog_Gender, Dog_Age, Type_ID) VALUES (%s,%s,%s,%s) """
cursor.execute(sql, (
self.dog_name,
self.dog_gender,
self.dog_age,
self.type_id,
))
#### COMMIT ####
db_connection.commit() ## ADD THE DOG
### STEP 2: RETRIEVE THE NEW DOG ID ###
sql = """ SELECT Max(Dog_ID) FROM Dogs """
cursor.execute(sql)
dog_id = cursor.fetchone()
logging.info("new dog_id: " + str(dog_id))
### STEP 3: second insert: Has Dogs table ###
sql = """ INSERT INTO Has_Dogs VALUES (%s,%s) """
cursor.execute(sql, (
self.email,
dog_id,
))
## COMMIT ##
db_connection.commit()
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False #return False in case of an error
finally:
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return True #return True if registration was successfull
def owner_main_page(email, owner_type):
data = {}
try:
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### STEP 1: FIND DOG OWNER'S NAME ####
sql = """ SELECT p_name FROM Person WHERE email = %s """
cursor.execute(sql, (email, ))
data['dog_owner_name'] = cursor.fetchone()[0] ## Dog Owner's Name
#### STEP 2: FIND DOG OWNER'S TOTAL NUMBER OF DOGS ####
sql = """ SELECT COUNT(Dog_ID) FROM Has_Dogs WHERE email = %s """
cursor.execute(sql, (email, ))
data['total_number_of_dogs'] = cursor.fetchone()[
0] ## Total number of dogs that belong to the dog owner
#### STEP 3: GET DOG OWNER'S ACCOUNT TYPE ####
if owner_type == 1:
data['dog_owner_type'] = "Regular"
else:
data['dog_owner_type'] = "Premium"
#### STEP 4: GET DOG OWNER'S LIST OF DOGS ####
sql = """ SELECT Dog_Name, Dog_Gender, Dog_Age, s.Dog_Type
FROM Has_Dogs as hd JOIN Dogs as d ON hd.Dog_ID = d.Dog_ID
JOIN Species as s ON d.Type_ID = s.Type_ID
WHERE hd.email = %s
GROUP BY Dog_Name, Dog_Gender, Dog_Age, s.Dog_Type """
row_count = cursor.execute(sql, (email, ))
data['dogs'] = cursor.fetchall()
data[
'num_of_dogs'] = row_count #catch the number of rows received in query --> number of dogs that the owner has
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False
finally: #### IMPORTANT ####
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return data
def __init__(self):
logging.info('Initializing DogWalker')
self.u_DbHandler = db_handler.DbHandler()
# create data members of the class WalkerFinder
self.u_Walker_Email_Adress = ""
self.u_WalkerName = ""
self.u_Phone_Number = ""
self.u_City = ""
self.u_Daily_Price = ""
self.u_Daily_Shift = ""
self.u_chosen_walker = ""
self.u_RetrievedWalkerList = []
self.u_NumberOfRows = 0
self.u_the_chosen_walker = ""
self.u_the_chosen_day = ""
self.u_the_chosen_owner = ""
self.u_the_chosen_name_walker = ""
def register(self): #apply registration
try:
db_connection = db_handler.DbHandler()
### check that the user has entered a full address ###
if not self.full_address:
raise ValueError("Incorrect parameters")
#### DATABASE CONNECTION ####
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### SQL QUERY AND EXECUTE: ####
### STEP 1: first insert: person table ###
sql = """ INSERT INTO Person VALUES (%s,%s,%s,%s) """
cursor.execute(sql, (
self.email,
self.name,
self.phone,
self.city,
))
### STEP 2: second insert: Dog Owner
sql = """ INSERT INTO Dog_Owner VALUES (%s,CURDATE()) """
cursor.execute(sql, (self.email, ))
## STEP 3: third insert: Premium_Dog_Owner
sql = """ INSERT INTO Premium_Dog_Owner VALUES (%s,%s,%s) """
cursor.execute(sql, (
self.email,
self.yearly_comission,
self.full_address,
))
## COMMIT ##
db_connection.commit()
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False #return False in case of an error
finally:
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return True #return True if registration was successful
def getCities(
): #Retrieve currently available dog walkers' cities from the database
try:
#### DATABASE CONNECTION ####
db_connection = db_handler.DbHandler()
db_connection.connectDb()
#### DEFINE CURSOR OBJECT ####
cursor = db_connection.getCursor()
#### GET LIST OF ALL DOGWALKERS ####
sql = """ SELECT DISTINCT city FROM Person """
cursor.execute(sql)
data = cursor.fetchall() #return without column's name
except Exception as e: #in case of an error, catch it and print it to the log
logging.info("Error: " + str(e))
return False
finally: #### IMPORTANT ####
if db_connection.status(
): # Make sure to disconnect from DB no matter what
db_connection.disconnectDb() ##DISCONNECT##
return data
