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'}