def get_requests(org='covidsos',public_page=True,request_status=['unverified','assigned','pending','completed']):
try:
server_con = connections('prod_db_read')
query = """Select r.id as r_id,r.name as 'requestor_name', r.uuid as `uuid`, rv.where as `where`,rv.what as `what`,rv.why as `what`,r.request,
rv.verification_status,r.latitude,r.longitude, r.status as `request_status`,r.timestamp as `request_time`,r.source as source,r.city as city,
so.organisation_name as source_org, so.logo_url as org_logo,CONCAT(r.address, ', ', r.geoaddress) as full_address
from requests r
left join request_verification rv on rv.r_id=r.id
left join support_orgs so on so.org_code=r.source
where rv.r_id is not NULL"""
query_df = pd.read_sql(query,server_con)
query_df['source_org'] = query_df['source_org'].fillna('COVIDSOS')
query_df['org_logo'] = query_df['org_logo'].fillna('')
query_df = query_df.sort_values(by=['r_id'],ascending=[False])
if(org!='covidsos'):
query_df = query_df[query_df['source']==org]
query_df['verification_status'] = query_df['verification_status'].fillna('verified')
query_df['city'] = query_df['city'].fillna('')
if(server_type=='prod'):
query_df['accept_link'] = query_df['uuid'].apply(lambda x:'https://covidsos.org/accept/'+x)
else:
query_df['accept_link'] = query_df['uuid'].apply(lambda x:'https://stg.covidsos.org/accept/'+x)
pending_queries = query_df[(query_df['verification_status']=='verified')&(query_df['request_status'].isin(['received','verified','pending']))]
return pending_queries
except:
mailer.send_exception_mail()
return pd.DataFrame()
def add_volunteers_to_db(df):
expected_columns = [
'timestamp', 'name', 'mob_number', 'email_id', 'country', 'address',
'geoaddress', 'latitude', 'longitude', 'source', 'status',
'support_type'
]
try:
if (len(df.columns.intersection(expected_columns)) == len(
expected_columns)):
exists, v_id = check_volunteer_exists(df)
df['timestamp'] = pd.to_datetime(df['timestamp'])
if (exists):
req_dict = df.loc[0, expected_columns].to_dict()
update_volunteers_db({'id': v_id}, req_dict)
return_str = 'Volunteer already exists. Your information has been updated'
return True, return_str
else:
engine = connections('prod_db_write')
df.to_sql(name='volunteers',
con=engine,
schema='covidsos',
if_exists='append',
index=False,
index_label=None)
return_str = 'Volunteer Data Submitted'
return True, return_str
else:
return_str = 'Data format not matching'
return False, return_str
except Exception as e:
print(e)
return_str = 'Error'
mailer.send_exception_mail()
return False, return_str
def get_private_map_data(org):
try:
server_con = connections('prod_db_read')
v_q = """Select timestamp,id as v_id, name,source,latitude,longitude,geoaddress,address,mob_number,email_id,status from volunteers"""
if org != 'covidsos':
v_q += f" where source='{org}'"
v_df = pd.read_sql(v_q, server_con)
v_df['timestamp'] = pd.to_datetime(
v_df['timestamp']) #.dt.tz_localize(tz='Asia/kolkata')
v_df = v_df[(v_df['latitude'] != 0.0) & (v_df['longitude'] != 0.0) &
(v_df['status'] == 1)]
r_q = """Select timestamp,id as r_id, name,source,latitude,longitude,geoaddress,request,status,address,mob_number,uuid from requests"""
if org != 'covidsos':
r_q += f" where source='{org}'"
r_df = pd.read_sql(r_q, server_con)
r_df['timestamp'] = pd.to_datetime(
r_df['timestamp']) #.dt.tz_localize(tz='Asia/kolkata')
r_df = r_df[(r_df['latitude'] != 0.0) & (r_df['longitude'] != 0.0)]
return {
'Volunteers': v_df.to_dict('records'),
'Requests': r_df.to_dict('records')
}
except:
mailer.send_exception_mail()
return {}
def website_requests_display():
try:
server_con = connections('prod_db_read')
query = """Select r.id as r_id,r.uuid as uuid, rv.where as location,rv.what as requirement,rv.why as reason,r.request,
rv.verification_status, r.status as status,r.timestamp as timestamp from requests r
left join request_verification rv on rv.r_id=r.id where rv.r_id is not NULL"""
query_df = pd.read_sql(query, server_con)
query_df = query_df.sort_values(by=['r_id'], ascending=[False])
query_df['verification_status'] = query_df[
'verification_status'].fillna('verified')
if (server_type == 'prod'):
query_df['accept_link'] = query_df['uuid'].apply(
lambda x: 'https://covidsos.org/accept/' + x)
else:
query_df['accept_link'] = query_df['uuid'].apply(
lambda x: 'https://stg.covidsos.org/accept/' + x)
pending_queries = query_df[
(query_df['verification_status'] == 'verified')
& (query_df['status'].isin(['received', 'verified', 'pending']))]
completed_queries = query_df[
(query_df['verification_status'] == 'verified')
& (query_df['status'].isin(['completed', 'matched', 'assigned']))]
return {
'pending': pending_queries.to_dict('records'),
'completed': completed_queries.to_dict('records')
}
except:
mailer.send_exception_mail()
return {'pending': {}, 'completed': {}}
def update_request_status(r_uuid, status, status_message, volunteer_id):
reqStatus = status
if status == 'completed externally':
reqStatus, status_message = 'completed', 'completed externally'
if status == 'cancelled':
reqStatus = 'verified'
status_message = sanitise_for_sql({
'message': status_message
}).get('message', '')
request_update_query = f""" insert into request_updates (request_uuid, status, status_message,v_id) values ('{r_uuid}', '{status}', '{status_message}','{volunteer_id}'); """
# update request_updates
try:
write_query(request_update_query, 'prod_db_write')
except:
mailer.send_exception_mail()
return 'Failed to add request update', False
update_requests_db({'uuid': r_uuid}, {'status': reqStatus})
# update request_matching
if status == 'cancelled':
r_id_query = f""" select id from requests where uuid = '{r_uuid}'"""
r_id = pd.read_sql(r_id_query, connections('prod_db_read')).loc[0,
'id']
request_matching_query = f""" update request_matching set is_active=False where request_id={r_id}"""
try:
write_query(request_matching_query, 'prod_db_write')
except:
mailer.send_exception_mail()
return 'Failed to cancel request matching', False
return 'Updated request status', True
def get_ticker_counts():
try:
server_con = connections('prod_db_read')
v_q = """Select * from volunteers"""
v_df = pd.read_sql(v_q, server_con)
r_q = """Select * from requests"""
r_df = pd.read_sql(r_q, server_con)
volunteer_count = v_df['mob_number'].nunique()
request_count = r_df.shape[0]
pending_request_count = r_df[r_df['status'].isin(
['received', 'verified', 'pending'])].shape[0]
return {
'Response': {
'volunteer_count': volunteer_count,
'request_count': request_count,
'pending_request_count': pending_request_count
},
'status': True,
'string_response': 'Metrics computed'
}
except:
mailer.send_exception_mail()
return {
'Response': {},
'status': False,
'string_response': 'Connection to DB failed'
}
def add_requests(df):
expected_columns = [
'timestamp', 'name', 'mob_number', 'email_id', 'country', 'address',
'geoaddress', 'latitude', 'longitude', 'source', 'members_impacted',
'request', 'age', 'status', 'uuid', 'managed_by', 'city',
'volunteers_reqd', 'filled_by_name', 'filled_by_mob_number'
]
try:
if (len(df.columns.intersection(expected_columns)) == len(
expected_columns)):
engine = connections('prod_db_write')
df.to_sql(name='requests',
con=engine,
schema='covidsos',
if_exists='append',
index=False,
index_label=None)
return_str = 'Request submitted successfully'
return True, return_str
else:
print(df.loc[0].to_dict(), flush=True)
return_str = 'Data Format not matching'
return False, return_str
except Exception as e:
print(df.loc[0].to_dict(), flush=True)
print(e, flush=True)
return_str = 'Error'
mailer.send_exception_mail()
return False, return_str
def update_request_updates_db(ru_dict_where, ru_dict_set):
try:
ru_dict_where, ru_dict_set = sanitise_for_sql(
ru_dict_where), sanitise_for_sql(ru_dict_set)
set_sql_format = ",".join(
("`{column_name}`='{value}'".format(column_name=x,
value=ru_dict_set[x])
for x in ru_dict_set))
where_sql_format = " and ".join(
("`{column_name}`='{value}'".format(column_name=x,
value=ru_dict_where[x])
for x in ru_dict_where))
query = """update request_updates set {set_str} where {where_str};""".format(
set_str=set_sql_format, where_str=where_sql_format)
write_query(query, 'prod_db_write')
return {
'Response': {},
'string_response': 'Request Update info Updated',
'status': True
}
except:
mailer.send_exception_mail()
return {
'Response': {},
'string_response': 'Request Update info updation failed',
'status': False
}
def blacklist_token(token):
query = f"""insert into token_blacklist (token) values ('{token}');"""
try:
write_query(query, 'prod_db_write')
return True
except:
mailer.send_exception_mail()
return False
def get_source_list():
try:
req_q = """Select id,org_code from support_orgs"""
req_df = pd.read_sql(req_q, connections('prod_db_read'))
return {'Response':req_df.to_dict('records'),'status':True,'string_response':'List retrieved'}
except:
mailer.send_exception_mail()
return {'Response':{},'status':False,'string_response':'List unavailable'}
def volunteer_data_by_id(v_id):
v_id_q = """Select id as v_id,name,mob_number,source from volunteers where id='{v_id}'""".format(v_id=v_id)
try:
v_id_df = pd.read_sql(v_id_q,connections('prod_db_read'))
return v_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def list_cron_jobs():
c_id_q = """Select * from schedule where is_deleted=false"""
try:
c_id_df = pd.read_sql(c_id_q,connections('prod_db_read'))
return c_id_df.to_dict('records')
except:
mailer.send_exception_mail()
return pd.DataFrame()
def cron_job_by_id(id):
c_id_q = """Select * from schedule where id='{id}'""".format(id=id)
try:
c_id_df = pd.read_sql(c_id_q,connections('prod_db_read'))
return c_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def request_verification_data_by_id(r_id):
r_id_q = """Select * from request_verification where r_id='{r_id}'""".format(r_id=r_id)
try:
r_id_df = pd.read_sql(r_id_q,connections('prod_db_read'))
return r_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def request_data_by_id(r_id):
r_id_q = """Select id as r_id,name,mob_number,geoaddress,latitude,longitude,request,status,timestamp,source,volunteers_reqd,members_impacted from requests where id='{r_id}'""".format(r_id=r_id)
try:
r_id_df = pd.read_sql(r_id_q,connections('prod_db_read'))
return r_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def user_data_by_id(user_id):
u_id_q = """Select id as user_id,name,mob_number from users where id='{user_id}'""".format(user_id=user_id)
try:
u_id_df = pd.read_sql(u_id_q,connections('prod_db_read'))
return u_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def volunteer_data_by_mob(mob_number):
v_id_q = """Select id as v_id,name,mob_number,source,whatsapp_id from volunteers where mob_number='{mob_number}'""".format(mob_number=mob_number)
try:
v_id_df = pd.read_sql(v_id_q,connections('prod_db_read'))
return v_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def save_request_sms_url(request_uuid, url_type, url):
query = f"""insert into request_sms_urls (r_uuid, url_type, url) values ('{request_uuid}', '{url_type}', '{url}');"""
try:
write_query(query, 'prod_db_write')
return True
except:
mailer.send_exception_mail()
return False
def get_moderator_list():
try:
req_q = """Select mob_number from users where verification_team=1"""
req_df = pd.read_sql(req_q, connections('prod_db_read'))
return req_df['mob_number'].unique().tolist()
except:
mailer.send_exception_mail()
return []
def get_type_list(table_type='volunteer'):
try:
req_q = """Select id,support_type,table_type from support_list where is_active=1"""
req_df = pd.read_sql(req_q, connections('prod_db_read'))
req_df = req_df[req_df['table_type']==table_type]
return {'Response':req_df[['id','support_type']].to_dict('records'),'status':True,'string_response':'List retrieved'}
except:
mailer.send_exception_mail()
return {'Response':{},'status':False,'string_response':'List unavailable'}
def request_data_by_uuid(uuid):
r_id_q = """Select id as r_id,name,mob_number,geoaddress,latitude,longitude,request,status,timestamp,source from requests where uuid='{uuid_str}'""".format(
uuid_str=uuid)
try:
r_id_df = pd.read_sql(r_id_q, connections('prod_db_read'))
return r_id_df
except:
mailer.send_exception_mail()
return pd.DataFrame()
def verify_user_exists(user_id, access_type):
server_con = connections('prod_db_read')
query = f"""Select id from users where id='{user_id}' and access_type='{access_type}' order by id desc limit 1"""
try:
data = pd.read_sql(query, server_con)
return (True if data.shape[0] > 0 else False)
except:
mailer.send_exception_mail()
return False
def website_success_stories():
try:
server_con = connections('prod_db_read')
query = """Select * from success_stories"""
query_df = pd.read_sql(query, server_con)
return {'instagram': query_df.to_dict('records')}
except:
mailer.send_exception_mail()
return {'instagram': {}}
def save_request_sms_url(request_uuid, url_type, url):
try:
url = sanitise_for_sql({'message': url[:1000]}).get('message', '')
query = f"""insert into request_sms_urls (r_uuid, url_type, url) values ('{request_uuid}', '{url_type}', '{url}');"""
write_query(query, 'prod_db_write')
return True
except:
print(url, flush=True)
mailer.send_exception_mail()
return False
def decorated_function(*args, **kwargs):
try:
return f(*args, **kwargs)
except:
mailer.send_exception_mail()
return {
'Response': {},
'status': False,
'string_response': 'Api Failure Occurred'
}
def check_past_verification(r_id):
try:
query = f"""Select id,r_id from request_verification where r_id='{r_id}'"""
df_check = pd.read_sql(query, connections('prod_db_read'))
if (df_check.shape[0] > 0):
return df_check.loc[0, 'id'], True
else:
return None, False
except:
mailer.send_exception_mail()
return None, False
def verify_user_exists(user_id, access_type):
server_con = connections('prod_db_read')
query = f"""Select id, organisation from users where id='{user_id}' and access_type='{access_type}' order by id desc limit 1"""
try:
data = pd.read_sql(query, server_con)
if data.shape[0] > 0:
return data.loc[0, 'organisation'], True
else:
return '', False
except:
mailer.send_exception_mail()
return '', False
def check_past_verification(r_id):
try:
query = f"""Select `id`,`r_id`,`why`,`what`,`where` as request_address,`verification_status`, `urgent`,`financial_assistance`
from request_verification where r_id='{r_id}'"""
df_check = pd.read_sql(query,connections('prod_db_read'))
if(df_check.shape[0]>0):
return df_check,True
else:
return pd.DataFrame(),False
except:
mailer.send_exception_mail()
return pd.DataFrame(),False
def send_sms_to_phone(sms_text,
sms_to=9582148040,
sms_type='transactional',
send=True):
sid = sms_sid
key = sms_key
url = sms_url
if (sms_type == 'transactional'):
route = "4"
elif (sms_type == 'promotional'):
route = "1"
data = {
"sender": "SOCKET",
"route": route,
"country": "91",
"sms": [{
"message": sms_text,
"to": [sms_to]
}]
}
headers = {'Content-type': 'application/json', 'authkey': key}
if ((send) & (server_type != 'local')):
try:
r = requests.post(url, data=json.dumps(data), headers=headers)
if (r.status_code == 200):
sms_dict = {
'sms_text': [sms_text],
'sms_type': [sms_type],
'sms_to': [sms_to],
'sms_status_type': [r.status_code],
'sms_json_response': [str(r.json())]
}
else:
sms_dict = {
'sms_text': [sms_text],
'sms_type': [sms_type],
'sms_to': [sms_to],
'sms_status_type': [r.status_code],
'sms_json_response': ['{}']
}
new_sms_df = pd.DataFrame(sms_dict)
engine = connections('prod_db_write')
new_sms_df.to_sql(name='sms_log',
con=engine,
schema='covidsos',
if_exists='append',
index=False,
index_label=None)
return None
except:
print('SMS API error')
mailer.send_exception_mail()
return None
def get_user_list(org='covidsos'):
try:
if(org=='covidsos'):
req_q = """Select id,name,organisation as source from users where verification_team=1"""
else:
req_q = """Select id,name,organisation as source from users where verification_team=1 and organisation='{source}'""".format(source=org)
req_df = pd.read_sql(req_q, connections('prod_db_read'))
return {'Response':req_df.to_dict('records'),'status':True,'string_response':'List retrieved'}
except:
mailer.send_exception_mail()
return {'Response':{},'status':False,'string_response':'List unavailable'}