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 get_reports():
return run_select_for_json('select * from app_reports')
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 get_work_list():
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 get_monthly_stats():
return run_select_for_json('select * from app_monthly_stats')
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;')
def get_work_list_estimate_for_resident(username):
return run_select_for_json(f'select username, estimate, tank_type from '
f'app_get_estimates_for_all_residents '
f'where username = \'{username}\'')
def get_latest_message():
return run_select_for_json(
"select message.messages from message order by timestamp desc limit 1")
def get_user(username):
return run_select_for_json(
f'select * from app_login where username = \'{username}\';')
def get_tank_info(username):
return run_select_for_json(
f"select username, status as sewage_tank_status, to_char(str.timestamp, 'DD Mon YYYY HH:MI:SSPM') as sewage_tank_timestamp, wtr.current_height/wtm.tank_height * 100 as water_tank_height_percentage, wtr.current_height as water_tank_height, to_char(wtr.timestamp, 'DD Mon YYYY HH:MI:SSPM') as water_tank_timestamp from residents join sewage_tank_readings str on residents.pk = str.tank_owner_fk join water_tank_readings wtr on residents.pk = wtr.tank_owner_fk join water_tanks_models wtm on residents.water_tank_fk = wtm.pk where username = '******' order by wtr.timestamp desc limit 1;"
)
