def add_resident(user_name, house_number, user_pin, water_tank_fk,
sewage_tank_fk):
execute_command(
f"insert into residents (username, house_number, pin, water_tank_fk, sewage_tank_fk) "
f"values ('{user_name}', {house_number}, '{user_pin}', {water_tank_fk}, {sewage_tank_fk})"
)
return run_select_for_json('select * from residents;')
def add_manual_demand(username, demand_type):
if get_query_result_as_df(
f"select 1 from residents where resident_disabled = true and username = '******';"
).empty:
execute_command(
f"with resident as (select pk from residents where username = '******') insert into manager_worklist(resident_fk, time_estimate_fk, tank_type_fk) select pk, 6, {demand_type} from resident;"
)
return run_select_for_json('select * from app_worklist')
def demand_completed(pk):
df = get_query_result_as_df(
f"select resident_fk, tank_type_fk, timestamp from manager_worklist where pk = {pk}"
)
resident_fk = df['resident_fk'][0]
tank_type_fk = df['tank_type_fk'][0]
timestamp = df['timestamp'][0]
print(timestamp)
execute_command(
f"insert into completed_worklist (resident_fk, tank_type_fk, time_at_worklist_added) "
f"values ({resident_fk}, {tank_type_fk}, '{timestamp}');")
execute_command(f"delete from manager_worklist where pk = {pk}")
return run_select_for_json("select * from app_completed_worklist")
def add_message(message):
execute_command(f"insert into message (messages) values ('{message}')")
return run_select_for_json("select * from message")
def add_report(complaint_type_fk, company_fk, complaint):
execute_command(
f"insert into reports (complaint_type_fk, company_fk, complaint) "
f"values ({complaint_type_fk}, {company_fk}, '{complaint}')")
return run_select_for_json("select * from app_reports;")
def enable_resident(resident_username):
execute_command(
f"update residents set resident_disabled = false where username = '******';"
)
return run_select_for_json('select * from residents;')
def update_demand(pk, time_estimate_fk):
execute_command(
f'update manager_worklist set time_estimate_fk = {time_estimate_fk} where pk = {pk};'
)
return run_select_for_json('select * from app_worklist')
def remove_driver(driver_user_name):
execute_command(
f'delete from drivers where username = \'{driver_user_name}\'')
return run_select_for_json('select * from drivers;')
def remove_resident(resident_user_name):
execute_command(
f'delete from residents where username = \'{resident_user_name}\'')
return run_select_for_json('select * from residents;')
def add_driver(user_name, user_pin):
execute_command(
f"insert into drivers (username, pin) values ('{user_name}', '{user_pin}')"
)
return run_select_for_json('select * from drivers;')