def AddVaccinationRecord():
form = AddVaccinationRecordForm()
if request.method == 'POST' and form.validate() != False:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
insert_stmt = (
"insert into vaccinations ( pet_id, vaccine_name, vaccine_details, vaccination_date, expiration_date)"
"values ( %s, %s, %s, %s, %s);")
data = (request.form["pet_id"], request.form["vaccine_name"],
request.form["vaccine_details"],
request.form["vaccination_date"],
request.form["expiration_date"])
cursor.execute(insert_stmt, data)
mysqlConn.commit()
passed_data = request.form.to_dict()
passed_data.pop("csrf_token", None)
return render_template('success.html', passed_form_data=passed_data)
else:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
sql = 'select * from vaccinations;'
cursor.execute(sql)
result = cursor.fetchall()
params = result
cursor.execute('select id, name from pets')
pets = cursor.fetchall()
return render_template(
'dbInteractionTemplates/addVaccinationRecord.html',
form=form,
params=params,
pets=pets)
def diagnostic():
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
sql = 'SELECT * FROM diagnostic;'
cursor.execute(sql)
result = cursor.fetchall()
return '<h3>' + str(result) + '</h3>'
def pets():
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
sql = """select
id, name, birthdate, pet_type,
(select count(*) from owners_pets where owners_pets.pet_id=pets.id) AS owner_count,
(select scheduled_time from visits where visits.pet_id=pets.id AND scheduled_time > NOW() and checkin_time IS NULL ORDER BY scheduled_time ASC LIMIT 1) AS next_visit,
(select checkin_time from visits where visits.pet_id=pets.id AND scheduled_time <= NOW() and checkin_time IS NOT NULL ORDER BY checkin_time DESC LIMIT 1) AS last_visit
from
pets"""
order_by = " order by pet_type ASC, name ASC"
filter_id = request.args.get('id', '')
filter_name = request.args.get('name', '')
if filter_id != '':
cursor.execute(sql + ' where id = %s' + order_by, filter_id)
elif filter_name != '':
cursor.execute(sql + ' where name like %s' + order_by,
'%' + filter_name + '%')
else:
cursor.execute(sql + order_by)
result = cursor.fetchall()
params = result
return render_template('dbInteractionTemplates/pets.html',
params=params,
filter_name=filter_name)
def AddVisit():
form = AddVisitForm()
if request.method == 'POST':
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
insert_stmt = (
"insert into visits ( owner_id, pet_id, scheduled_time)"
"values ( %s, %s, %s);")
data = ((request.form["ownerid"]), (request.form["petid"]),
request.form["scheduled_time"])
print(data)
cursor.execute(insert_stmt, data)
mysqlConn.commit()
passed_data = request.form.to_dict()
passed_data.pop("csrf_token", None)
return render_template('success.html', passed_form_data=passed_data)
else:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
sql = """select
visits.id,
owners.first_name as owner_name,
pets.name as pet_name,
pets.pet_type as pet_type,
visits.scheduled_time as scheduled_time,
visits.checkin_time as checkin_time,
visits.notes as visit_notes
from
visits left join owners on visits.owner_id=owners.id
left join pets on visits.pet_id=pets.id;"""
cursor.execute(sql)
visit_data = cursor.fetchall()
cursor.execute('select id, name from pets')
pets = cursor.fetchall()
cursor.execute('select id, first_name, last_name from owners')
owners = cursor.fetchall()
return render_template('dbInteractionTemplates/addVisit.html',
params=visit_data,
pets=pets,
owners=owners)
def delete_vaccination(vaccination_id):
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
cursor.execute('delete from vaccinations where id = %s',
vaccination_id)
mysqlConn.commit()
return redirect('/reports/expired_vaccinations', code=302)
def delete_owner_pet(id):
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
cursor.execute('delete from owners_pets where id = %s', id)
mysqlConn.commit()
flash('Owner Pet Relationship deleted')
return redirect('/AddOwnerPet', code=302)
def delete_visit(visit_id):
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
cursor.execute('delete from visits where id = %s', visit_id)
mysqlConn.commit()
flash('Visit Record deleted')
return redirect('/AddVisit', code=302)
def delete_pet(pet_id):
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
cursor.execute('delete from pets where id = %s', pet_id)
mysqlConn.commit()
flash('Pet deleted')
return redirect('/pets', code=302)
def delete_owner(owner_id):
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
cursor.execute('delete from owners where id = %s', owner_id)
mysqlConn.commit()
flash('Owner deleted')
return redirect('/owners', code=302)
def ViewExpiredVaccinations():
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
sql = 'select pets.name as pet_name, vaccinations.* from vaccinations, pets where pets.id=vaccinations.pet_id and expiration_date<=NOW();'
cursor.execute(sql)
result = cursor.fetchall()
params = result
return render_template('dbInteractionTemplates/expired_vaccinations.html',
params=params)
def UpdateVisitCheckin():
form = UpdateVisitCheckinForm()
if request.method == 'POST' and form.validate() != False:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
update_stmt = ("update visits set checkin_time=%s where id=%s;")
data = ((request.form["checkin_time"].replace("T", " ")),
(request.form["visit_id"]))
cursor.execute(update_stmt, data)
mysqlConn.commit()
passed_data = request.form.to_dict()
passed_data.pop("csrf_token", None)
return render_template('success.html', passed_form_data=passed_data)
else:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
sql = """select
visits.id,
owners.first_name as owner_name,
pets.name as pet_name,
pets.pet_type as pet_type,
visits.scheduled_time as scheduled_time,
visits.checkin_time as checkin_time,
visits.notes as visit_notes
from
visits left join owners on visits.owner_id=owners.id
left join pets on visits.pet_id=pets.id;"""
cursor.execute(sql)
result = cursor.fetchall()
params = result
return render_template(
'dbInteractionTemplates/updateVisitCheckin.html',
form=form,
params=params)
def add_owner():
form = AddOwnerForm()
if request.method == 'POST' and form.validate() != False:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
insert_stmt = (
"insert into owners ( first_name, last_name, email, phone)"
"values ( %s, %s, %s, %s);")
data = (request.form["firstname"], request.form["lastname"],
request.form["email"], request.form["phone"])
cursor.execute(insert_stmt, data)
mysqlConn.commit()
passed_data = request.form.to_dict()
passed_data.pop("csrf_token", None)
flash('Successfully added new owner')
return redirect('/owners')
else:
return render_template('dbInteractionTemplates/add_owner.html',
form=form)
def add_pet():
form = AddPetForm()
if request.method == 'POST' and form.validate() != False:
mysqlConn = database.connectMySql()
with mysqlConn.cursor() as cursor:
insert_stmt = (
"insert into pets ( name, birthdate, pet_type, comment)"
"values ( %s, %s, %s, %s);")
data = (request.form["name"], request.form["birthdate"],
request.form["type"], request.form["comment"])
cursor.execute(insert_stmt, data)
mysqlConn.commit()
passed_data = request.form.to_dict()
passed_data.pop("csrf_token", None)
flash('Successfully added new pet')
return redirect('/pets')
else:
return render_template('dbInteractionTemplates/add_pet.html',
form=form)
def AddOwnerPet():
mysqlConn = database.connectMySql()
form = AddOwnerPetForm()
if request.method == 'POST' and form.validate() != False:
with mysqlConn.cursor() as cursor:
insert_stmt = ("insert into owners_pets ( owner_id, pet_id)"
"values ( %s, %s);")
data = ((request.form["ownerid"]), (request.form["petid"]))
cursor.execute(insert_stmt, data)
mysqlConn.commit()
passed_data = request.form.to_dict()
passed_data.pop("csrf_token", None)
return render_template('success.html', passed_form_data=passed_data)
else:
with mysqlConn.cursor() as cursor:
cursor.execute('select id, first_name, last_name from owners')
owners = cursor.fetchall()
cursor.execute('select id, name from pets')
pets = cursor.fetchall()
cursor.execute("""SELECT
owners_pets.id as id,
concat(COALESCE(owners.first_name,' '), ' ', COALESCE(owners.last_name,' ')) as owner_name,
pets.name as pet_name,
pets.pet_type as pet_type,
pets.comment as comment
FROM `owners_pets` inner join owners on owners_pets.owner_id=owners.id
inner join pets on owners_pets.pet_id=pets.id """
)
owner_pets = cursor.fetchall()
return render_template('dbInteractionTemplates/addOwnerPet.html',
form=form,
owners=owners,
pets=pets,
owner_pets=owner_pets)