def create_strategy(ref_type, area, description, days):
try:
c = d.cursor()
statement = '''INSERT INTO Strategy(ref_type_id, area, description)
VALUES(%s, "%s", "%s");''' % (ref_type, area, description)
c.execute(statement)
d.commit()
statement = '''SELECT strategy_id FROM Strategy
WHERE area = "%s" and ref_type_id = %s;''' % (area, ref_type)
c.execute(statement)
rows = c.fetchall()
for row in rows:
areaid = row[0]
print "areaid"
print areaid
print ref_type
print days
for day in days:
statement = '''INSERT INTO Rota (ref_strategy_id,
day) VALUES
(%s, %s);''' % (areaid, day)
c.execute(statement)
d.commit()
print "rota added"
return True
except:
return False
def get_complete():
complete = {}
c = d.cursor()
statement = '''SELECT COUNT(Batch_id) FROM Food_Processing
where Activity = "Cooking";'''
c.execute(statement)
i = c.fetchall()
if i == 1:
complete['Cooking'] = True
else:
complete['Cooking'] = False
statement = '''SELECT COUNT(Batch_id) FROM Food_Processing
where Activity = "Cooling";'''
c.execute(statement)
i = c.fetchall()
if i == 1:
complete['Cooling'] = True
else:
complete['Cooling'] = False
statement = '''SELECT COUNT(Batch_id) FROM Food_Processing
where Activity = "Hot Hold";'''
c.execute(statement)
i = c.fetchall()
if i == 1:
complete['HotHold'] = True
else:
complete['HotHold'] = False
return complete
def get_user_stat(u_id):
user_stat = {}
c = d.cursor()
statement = '''select count(Ref_Staff_id) from Temperature
where Ref_Staff_id ="%s" and date(datetime) between
date(CURDATE())-INTERVAL 30 DAY AND CURDATE()
;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
user_stat['Temperature'] = row[0]
statement = '''select count(Ref_Staff_id) from Checks c
where Ref_Staff_id = "%s" and ref_type_id = 1 and
date(c.date) between
date(CURDATE())-INTERVAL 30 DAY AND CURDATE()
;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
user_stat['Pest'] = row[0]
statement = '''select count(Ref_Staff_id) from Checks c
where Ref_Staff_id = "%s" and ref_type_id=2 and
DATE(c.date) BETWEEN
DATE(CURDATE())-INTERVAL 30 DAY AND CURDATE()
;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
user_stat['Cleaning'] = row[0]
return user_stat
def validate(firstname, lastname, email, passwd):
c = d.cursor()
statement = '''select staff_id from Staff
where first_name ="%s" and last_name ="%s";'''\
% (firstname, lastname)
try:
c.execute(statement)
rows = c.fetchall()
for row in rows:
staff_id = int(row[0])
print staff_id
statement = '''select count(ref_staff_id) from
Login where ref_staff_id = "%s";''' % (staff_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
num = int(row[0])
if num == 0:
passwd = generate_password_hash(passwd)
statement = '''INSERT INTO Login VALUES
("%s","%s","%s");''' % (email, passwd, staff_id)
c.execute(statement)
d.commit()
return True
return False
except:
return False
def get_user_contract(u_id):
c = d.cursor()
statement = '''SELECT contract from Staff
where Staff_id = %s;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
return row[0]
def get_name(u_id):
c = d.cursor()
statement = '''SELECT First_Name from Staff
where Staff_id = %s;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
return row[0]
def get_admin(u_id):
c = d.cursor()
statement = '''SELECT Admin FROM Login WHERE
Ref_Staff_Id = %s;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
return row[0]
def get_email(u_id):
c = d.cursor()
statement = '''select email from Login
WHERE ref_staff_id = %s;''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
return row[0]
def get_user_id(email):
c = d.cursor()
statement = "SELECT Staff_Id FROM Staff join Login\
ON Staff_id = Ref_Staff_id\
WHERE email = '%s';" % (email)
c.execute(statement)
rows = c.fetchall()
for row in rows:
return row[0]
def set_notes(u_id, notes):
c = d.cursor()
statement = '''delete from Notes where
Ref_Staff_id = %s;''' % (u_id)
c.execute(statement)
statement = '''INSERT INTO Notes VALUES (%s, CURRENT_TIMESTAMP,
"%s");''' % (u_id, notes)
c.execute(statement)
d.commit()
def get_devices():
c = d.cursor()
statement = '''SELECT Appliance_id, temp_stat from Appliance
WHERE CURRENT = True and temp_stat != "Hot";'''
c.execute(statement)
rows = c.fetchall()
devices = {}
for row in rows:
devices[row[0]] = {}
devices[row[0]]["device"] = row[0]
devices[row[0]]["range"] = row[1]
return devices
def get_notes(u_id):
c = d.cursor()
statement = '''SELECT notes FROM Notes n
WHERE Ref_Staff_id = %s and DATE(n.date) =
DATE(CURRENT_TIMESTAMP);''' % (u_id)
c.execute(statement)
rows = c.fetchall()
for row in rows:
notes = row[0]
if notes:
return notes
else:
return "failed"
def get_count_remaining_tasks(ref_type):
c = d.cursor()
statement = '''SELECT COUNT(s.Strategy_id) FROM Strategy s JOIN
Rota r on s.strategy_id = r.Ref_Strategy_Id LEFT JOIN Checks c
ON c.Ref_Strategy_Id = s.Strategy_id
WHERE live=true AND Day=DAYOFWEEK(CURRENT_TIMESTAMP)
AND s.ref_type_id =%s AND s.Strategy_id NOT IN (SELECT Ref_strategy_id
FROM Checks c WHERE DATE(c.date) = DATE(CURRENT_TIMESTAMP));''' % (ref_type)
c.execute(statement)
rows = c.fetchall()
for row in rows:
count = row[0]
return count
def validate(self, passwd, email):
c = d.cursor()
statement = '''select password from Login
where email ="%s";'''\
% (email)
try:
c.execute(statement)
rows = c.fetchall()
for row in rows:
password = row[0]
if check_password_hash(password, passwd):
return True
except:
return False
def get_overview(ref_type):
c = d.cursor()
overview = {}
statement = '''SELECT s.strategy_id, s.area, s.description, c.ref_staff_id
FROM Strategy s join Rota r ON s.strategy_id=r.ref_strategy_id
LEFT JOIN Checks c on c.ref_strategy_id = s.strategy_id
WHERE DAYOFWEEK(CURRENT_TIMESTAMP) = r.day and s.ref_type_id = %s
AND s.strategy_id not in (select ref_strategy_id from Checks c where DATE(c.date)<
DATE(CURRENT_TIMESTAMP));'''\
% (ref_type)
c.execute(statement)
rows = c.fetchall()
for row in rows:
overview[row[0]] = {}
overview[row[0]]["area_id"] = row[0]
overview[row[0]]["area"] = row[1]
overview[row[0]]["description"] = row[2]
overview[row[0]]["staff_id"] = row[3]
return overview
def get_todays_tasks(ref_type):
try:
c = d.cursor()
tasks = {}
statement = '''SELECT strategy_id, Area FROM Strategy JOIN
Rota ON strategy_id = Ref_Strategy_Id
WHERE live=true and Day=DAYOFWEEK(CURRENT_TIMESTAMP)
and ref_type_id =%s;''' % (ref_type)
c.execute(statement)
rows = c.fetchall()
for row in rows:
tasks[row[0]] = {}
tasks[row[0]]['area_id'] = row[0]
tasks[row[0]]['area'] = row[1]
return tasks
except:
return "None"
def create_check(ref_type, area, comment, ref_staff_id):
print ref_type
print area
print comment
print int(ref_staff_id)
try:
c = d.cursor()
statement = '''SELECT Strategy_Id from Strategy WHERE
Area = "%s" and ref_type_id= %s;''' % (area, ref_type)
c.execute(statement)
rows = c.fetchall()
for row in rows:
ref_area_id = int(row[0])
print ref_area_id
statement = '''INSERT INTO Checks(Ref_Type_id, ref_strategy_id,
ref_staff_id, comment, complete) VALUES (%s, %s, %s, "%s", True);''' \
% (ref_type, ref_area_id, int(ref_staff_id), comment)
c.execute(statement)
d.commit()
return True
except:
return False