def set_notification_settings(projectId, deviceId, weeks):
if not notification_settings_exist():
cmd = "INSERT INTO notification_settings (project_id, device_id, reminder_weeks) VALUES (?, ?, ?)"
else:
cmd = "UPDATE notification_settings SET project_id = ?, device_id = ?, reminder_weeks = ?"
do_command(cmd, [projectId, deviceId, weeks])
def owner_is_active(owner_name):
rslt = do_command('select active from owner where owner_name = ?',
[owner_name])
if len(rslt) > 0:
return rslt[0]['active'] != 0
else:
return False
def get_owner_id_by_name(owner_name):
rslt = do_command("select owner_id from owner where owner_name = ?",
[owner_name])
if len(rslt) > 0:
return rslt[0]['owner_id']
else:
return None
def get_measurements_for_location(location):
rslt = do_command(
'select * from measurement where measurement_location = ?', [location])
if len(rslt) > 0:
return rslt
else:
return None
def get_notification_settings():
if notification_settings_exist():
cmd = "SELECT project_id, device_id, reminder_weeks FROM notification_settings"
rtVal = do_command(cmd)
return rtVal[0]
return None
def get_categories_for_area(area):
rslt = do_command(
'select * from category inner join category_area on category_area.category_id=category.category_id where area_id = ?',
[area])
if len(rslt) > 0:
return rslt
else:
return None
def get_owner_id_for_ticket(ticket):
rslt = do_command(
'''select owner
from ticket
where ticket.value = ?
''', [ticket])
if len(rslt) > 0:
return rslt[0]['owner']
else:
return None
def get_average_measurement_for_area(area):
rslt = do_command(
'select measurement.value from measurement inner join location on measurement_location=location.location_id where location_area = ?',
[area])
if len(rslt) > 0:
ret_val = 0
for entry in rslt:
ret_val += entry['value']
return ret_val / (len(rslt))
else:
return None
def do_drop():
drop_owner = """drop table if exists owner"""
do_command(drop_owner)
drop_loan = """drop table if exists loan"""
do_command(drop_loan)
drop_ticket = """drop table if exists ticket"""
do_command(drop_ticket)
def do_create():
cmd_owner = """
create table owner
(
owner_id integer PRIMARY KEY autoincrement,
owner_name varchar(30) UNIQUE ,
active INTEGER DEFAULT 1
)
"""
do_command(cmd_owner)
cmd_loan = """
create table loan
(
loan_id integer primary key autoincrement,
owner REFERENCES owner(owner_id),
initial_balance real,
number_of_months integer,
annual_rate real,
monthly_payment real
)
"""
do_command(cmd_loan)
cmd_ticket = """
create table ticket (
owner REFERENCES owner(owner_id),
value text UNIQUE,
expires TIMESTAMP
)
"""
do_command(cmd_ticket)
def get_loan_for_loan_id(loan_id):
rslt = do_command('select * from loan where loan_id = ?', [loan_id])
if len(rslt) > 0:
return rslt[0]
else:
return None
def get_loan_ids_for_owner(owner_id):
rslt = do_command('select loan_id from loan where owner = ?', [owner_id])
return rslt
def get_grocery_name(upc):
cmd = "SELECT name FROM grocery WHERE upc = ?"
rtVal = do_command(cmd, [upc])
return bool(len(rtVal))
def notification_settings_exist():
cmd = "SELECT id FROM notification_settings"
rtVal = do_command(cmd)
return bool(len(rtVal))
def get_all_areas():
rslt = do_command('select * from area', )
if len(rslt) > 0:
return rslt
else:
return None
def get_locations_for_area(area):
rslt = do_command('select * from location where location_area = ?', [area])
if len(rslt) > 0:
return rslt
else:
return None