def __init__(self, log):
self.log = log
self.server = epoll_server()
self.server.init(log)
self.dbpool = conn_pool("dbproxy", dbproxy, mongo_client_chk)
self.users = users(self.log)
self.conn = connections(self.server, log, self.dbpool, self.users)
def myapp(environ, start_response):
# Get QUERY_STRING.
query_string = environ.get("QUERY_STRING")
if query_string == None:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
args = urlparse.parse_qs(query_string)
arg = args.get("script")
if arg == None:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
script = arg[0]
if script == "connections":
return connections.connections(start_response)
elif script == "httplog":
arg = args.get("lines", [DEFAULT_LINES])[0]
try:
nlines = int(arg)
except ValueError:
nlines = DEFAULT_LINES
return httplog.httplog(start_response, nlines)
elif script == "get_next_lines":
arg = args.get("date")
if arg == None:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
date = arg[0]
if len(date) != 8:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
arg = args.get("offset")
if arg == None:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
try:
offset = int(arg[0])
except ValueError:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
arg = args.get("lines", [DEFAULT_LINES])[0]
try:
nlines = int(arg)
except ValueError:
nlines = DEFAULT_LINES
return get_next_lines.get_next_lines(start_response, date, offset, nlines)
else:
start_response("404 Not found", [("Content-Type", "text/html")])
return [NOT_FOUND]
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 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 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'}
def Get_Message():
# now = str(dt.datetime.now().date())
# now_time = str(dt.datetime.now())
response = request.json
print(response)
frm = str(response["messages"][0]["from"])
text = str(response["messages"][0]["text"]["body"])
## query for volunteer
checkState = """SELECT name, timestamp, id from volunteers where mob_number='{mob_number}'""".format(
mob_number=frm[2:])
records_a = pd.read_sql(checkState, connections('prod_db_read'))
## query for requester
checkState = """SELECT name, timestamp, id from requests where mob_number='{mob_number}'""".format(
mob_number=frm[2:])
records_b = pd.read_sql(checkState, connections('prod_db_read'))
## check for volunteer
if records_a.shape[0] > 0:
name = records_a.loc[0, 'name']
add_message(frm, frm, bot_number, text, "text", "whatsapp", "incoming")
send_whatsapp_message(whatsapp_api_url, frm, a.format(v_name=name))
## check for requester
elif records_b.shape[0] > 0:
name = records_b.loc[0, 'name']
add_message(frm, frm, bot_number, text, "text", "whatsapp", "incoming")
send_whatsapp_message(whatsapp_api_url, frm, b.format(r_name=name))
## if new user
else:
print(text)
if text != '1' and text != '2':
send_whatsapp_message(whatsapp_api_url, frm, c)
if text == '1':
send_whatsapp_message(whatsapp_api_url, frm, c1)
if text == '2':
send_whatsapp_message(whatsapp_api_url, frm, c2)
print('\n' + frm + '\n')
def accept_request_page_secure(uuid):
query = """Select r.id as r_id,r.name as name,r.mob_number, r.status as status, r.geoaddress as request_address,r.latitude as latitude, r.longitude as longitude, r.volunteers_reqd as volunteers_reqd,
rv.what as what, rv.why as why, rv.verification_status, rv.urgent as urgent,rv.financial_assistance as financial_assistance
from requests r left join request_verification rv on r.id=rv.r_id
where r.uuid='{uuid}'""".format(uuid=uuid)
df = pd.read_sql(query, connections('prod_db_read'))
df = df[~df['verification_status'].isna()]
if (df.shape[0] > 1):
df = df[0:0]
df['what'] = df['what'].fillna('Please call senior citizen to discuss')
df['why'] = df['why'].fillna('Senior Citizen')
df['financial_assistance'] = df['financial_assistance'].fillna(0)
return df
def check_volunteer_exists(df):
try:
server_con = connections('prod_db_read')
query = """Select id from volunteers where `mob_number`='{mob_number}'""".format(
mob_number=df.loc[0, 'mob_number'])
volunteer_list = pd.read_sql(query, server_con)
if (volunteer_list.shape[0] > 0):
return True, volunteer_list.loc[0, 'id']
else:
return False, None
except:
mailer.send_exception_mail()
return False, None
def send_raven_v1(template_name,
sms_to=919582148040,
send=True,
body_parameters={}):
key = raven_key
url = raven_sms_url
data = {
"event": template_name,
"user": {
"mobile": sms_to
},
"data": body_parameters
}
# data.update(body_parameters)
headers = {'Content-type': 'application/json', 'Authorization': 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': ["Text sent. Check Raven Logs"],
'sms_type': ["Transactional"],
'sms_to': [sms_to],
'sms_status_type': [r.status_code],
'sms_json_response': [str(r.json())]
}
else:
sms_dict = {
'sms_text': ["Failed to send SMS. Check Raven Logs"],
'sms_type': ["Transactional"],
'sms_to': [sms_to],
'sms_status_type': [r.status_code],
'sms_json_response': [str(r.json())]
}
new_sms_df = pd.DataFrame(sms_dict)
engine = connections('prod_db_write')
new_sms_df.loc[0, 'sms_text'] = sanitise_for_sql({
'message':
new_sms_df.loc[0, 'sms_text']
}).get('message', '')
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', flush=True)
mailer.send_exception_mail()
return None
def decode_auth_token(auth_token):
try:
server_con = connections('prod_db_read')
query = f"""select * from token_blacklist where token='{auth_token}'"""
data = pd.read_sql(query, server_con)
token_blacklisted = data.shape[0] > 0
if token_blacklisted:
return 'Invalid token. Please log in again.', False
payload = jwt.decode(auth_token, app.config.get('SECRET_KEY'))
return payload['sub'], True
except jwt.ExpiredSignatureError:
return 'Signature expired. Please log in again.', False
except jwt.InvalidTokenError:
return 'Invalid token. Please log in again.', False
def verify_user(username,password):
server_con = connections('prod_db_read')
query = """Select users.id as id,name as full_name, mob_number,email_id,password,user_access.type as type, organisation as source from users left join user_access on users.access_type=user_access.id"""
user_list = pd.read_sql(query,server_con)
for i in user_list.index:
if(((str(user_list.loc[i,'mob_number'])==username) or (user_list.loc[i,'email_id']==username)) and (user_list.loc[i,'password']==password)):
output = {'Response':{'access_level': user_list.loc[i,'type'],'username':username,'user_id':str(user_list.loc[i,'id']),'full_name':user_list.loc[i,'full_name'],'source':user_list.loc[i,'source']},'string_response': 'Login successful','status':True}
break
elif(((str(user_list.loc[i,'mob_number'])==username) or (user_list.loc[i,'email_id']==username)) and (user_list.loc[i,'password']!=password)):
output = {'Response':{'username':username},'string_response': 'Incorrect Password','status':False}
break
else:
output = {'Response':{'username':username},'string_response': 'Incorrect Username','status':False}
return output
def verify_volunteer_exists(mob_number, v_id=None, country=None):
server_con = connections('prod_db_read')
query = f"""Select id, name, country, source from volunteers where mob_number='{mob_number}'"""
if v_id and country:
query = f"""Select id, name, country, source from volunteers where id='{v_id}' and country='{country}'"""
try:
data = pd.read_sql(query, server_con)
if data.shape[0] > 0:
return {'status': True, 'volunteer_id': data.loc[0, 'id'],'name':data.loc[0,'name'],
'country': data.loc[0, 'country'], 'source': data.loc[0, 'source'] }
return {'status': False, 'volunteer_id': None}
except:
mailer.send_exception_mail()
return {'status': False, 'volunteer_id': None}
def get_user_id(username, password):
server_con = connections('prod_db_read')
query = f"""Select id, access_type from users where mob_number='{username}' or email_id='{username}' and password='******' order by id desc limit 1"""
try:
data = pd.read_sql(query, server_con)
if (data.shape[0] > 0):
user_id = int(data.loc[0, 'id'])
access_type = int(data.loc[0, 'access_type'])
return user_id, access_type
else:
return None, None
except:
mailer.send_exception_mail()
return None, None
def remove_existing_volunteers(df):
try:
server_con = connections('prod_db_read')
query = """Select mob_number from volunteers"""
volunteer_list = pd.read_sql(query, server_con)
df['mob_number'] = df['mob_number'].str.replace(" ", '')
df['mob_number'] = df['mob_number'].str.replace(",", '')
df['mob_number'] = df['mob_number'].str.replace("\+91", '')
df['mob_number'] = df['mob_number'].apply(lambda x: int(x))
df_new = df[df['mob_number'].isin(
volunteer_list['mob_number'].unique()) == False]
return df_new
except:
mailer.send_exception_mail()
return df
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 accept_request_page(uuid):
query = """Select r.id as r_id,r.name as name, r.status as status, r.geoaddress as request_address,r.latitude as latitude, r.longitude as longitude, r.volunteers_reqd as volunteers_reqd,
rv.what as what, rv.why as why, rv.verification_status, rv.urgent as urgent,rv.financial_assistance as financial_assistance, so.organisation_name as source_org, so.logo_url as org_logo
from requests r left join request_verification rv on r.id=rv.r_id
left join support_orgs so on so.org_code = r.source
where r.uuid='{uuid}'""".format(uuid=uuid)
df = pd.read_sql(query,connections('prod_db_read'))
df = df[~df['verification_status'].isna()]
df['source_org'] = df['source_org'].fillna('COVIDSOS')
df['org_logo'] = df['org_logo'].fillna('')
if(df.shape[0]>1):
df = df[0:0]
df['what']=df['what'].fillna('Please call senior citizen to discuss')
df['why']=df['why'].fillna('Senior Citizen')
df['financial_assistance']=df['financial_assistance'].fillna(0)
return df
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 get_unverified_requests():
try:
query = """Select * from requests r where status='received'"""
df = pd.read_sql(query, connections('prod_db_read'))
df = df[~df['uuid'].isna()]
df = df.sort_values(by=['id'], ascending=[False])
df = df.fillna('')
if (server_type == 'prod'):
df['verify_link'] = df['uuid'].apply(
lambda x: 'https://covidsos.org/verify/' + x)
else:
df['verify_link'] = df['uuid'].apply(
lambda x: 'https://stg.covidsos.org/verify/' + x)
return df
except:
mailer.send_exception_mail()
return pd.DataFrame()
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 search():
source_city = request.args.get('from')
destination_city = request.args.get('to')
date_from = request.args.get('departure')
date_to = request.args.get('return')
date_from = datetime.strptime(date_from, '%Y-%m-%dT%H:%M:%S.%f%z')
date_to = datetime.strptime(date_to, '%Y-%m-%dT%H:%M:%S.%f%z')
con = connections()
results = con.get_data(source_city, destination_city,
convert_date(date_from))
result_return = {}
if date_to is not None:
results.extend(
con.get_data(destination_city, source_city, convert_date(date_to)))
return jsonify(results=results)
def get_public_map_data():
try:
server_con = connections('prod_db_read')
v_q = """Select name,latitude,longitude,source from volunteers"""
v_df = pd.read_sql(v_q,server_con)
v_df = v_df[(v_df['latitude']!=0.0)&(v_df['longitude']!=0.0)]
v_df['name']='PRIVATE USER'
# v_df['radius']=0.5
# geometry = v_df.apply(lambda x: Point(x['longitude'],x['latitude']).buffer(buffer_radius*x.radius),axis=1)
# crs = {'init': 'epsg:4326'}
# v_df = gpd.GeoDataFrame(v_df, crs=crs, geometry=geometry)
r_q = """Select name,request,latitude,longitude,source from requests"""
r_df = pd.read_sql(r_q,server_con)
r_df['name']='PRIVATE USER'
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 completed_website_requests_display(org='covidsos'):
org_condition = f"and r.source='{org}'" if org != 'covidsos' else ''
query = f"""Select r.id as r_id,r.name as `requestor_name`, r.uuid as `uuid`, r.timestamp as `request_time`,
r.source as `source`, r.status as `request_status`,r.request, rv.where as `where`,
rv.what as `what`, rv.why as `why`, rv.financial_assistance, rv.urgent,
v.id as v_id, v.name as `volunteer_name`, rm.timestamp as `assignment_time`,
r.city, so.organisation_name as source_org,
so.logo_url as org_logo
from requests r
left join request_verification rv on rv.r_id=r.id
left join request_matching rm on rm.request_id=r.id and rm.is_active=1
left join volunteers v on v.id=rm.volunteer_id
left join users u on u.id = r.managed_by
left join support_orgs so on so.org_code=r.source
where rm.is_active=True and r.status in ('completed') and v.id is not NULL {org_condition}"""
requests_data = pd.read_sql(query, connections('prod_db_read'))
requests_data = requests_data.fillna('')
requests_data = requests_data.sort_values(by=['assignment_time'],ascending=[False])
return requests_data
def assignment_link(r_id):
v_list_q = """Select id as v_id,mob_number,name, latitude,longitude from volunteers where status=1"""
v_list = pd.read_sql(v_list_q, connections('prod_db_read'))
v_list['dist'] = get_haversine_distance(
lat,
lon,
)
v_list = v_list.sort_values(by='dist', ascending=True)
for i in v_list.index:
if (v_list.loc[i, 'dist'] < 'radius'):
sms_text = 'Dear, ' + v_list.loc[
i,
'name'] + ' someone in your area needs help. Click here to help ' + link
send_sms(sms_text,
sms_to=v_list.loc[i, 'mob_number'],
sms_type='transactional',
send=True)
#incomplete
return None
def add_volunteers_to_db(df):
expected_columns = [
'timestamp', 'name', 'mob_number', 'email_id', 'country', 'address',
'geoaddress', 'latitude', 'longitude', 'source', 'status',
'support_type', 'city'
]
try:
df.loc[0, 'address'] = sanitise_for_sql({
'message': df.loc[0, 'address']
}).get('message', '')
df.loc[0, 'geoaddress'] = sanitise_for_sql({
'message':
df.loc[0, 'geoaddress']
}).get('message', '')
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:
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 add_request_verification_db(df):
expected_columns = [
'timestamp', 'r_id', 'what', 'why', 'where', 'verification_status',
'verified_by', 'financial_assistance'
]
#If Request ID does not exist in verification table, create a new row
if (len(df.columns.intersection(expected_columns)) == len(expected_columns)
):
engine = connections('prod_db_write')
df.to_sql(name='request_verification',
con=engine,
schema='covidsos',
if_exists='append',
index=False,
index_label=None)
return_str = 'Request verification data submitted successfully'
return True, return_str
else:
return_str = 'Data Format not matching'
return False, return_str
def get_assigned_requests(org):
org_condition = f"and r.source='{org}'" if org != 'covidsos' else ''
query = f"""Select r.id as r_id, r.uuid as request_uuid, r.name as `requestor_name`, r.mob_number as `requestor_mob_number`, r.volunteers_reqd,r.timestamp as `request_time`,
r.source as `source`, r.status as `request_status`, rv.where as `where`, rv.what as `what`, rv.why as `why`, rv.financial_assistance, rv.urgent,
v.id as v_id, v.name as `volunteer_name`, v.mob_number as `volunteer_mob_number`,rm.timestamp as `assignment_time`, u.name as managed_by
from requests r
left join request_verification rv on rv.r_id=r.id
left join request_matching rm on rm.request_id=r.id
left join volunteers v on v.id=rm.volunteer_id
left join users u on u.id = r.managed_by
where rm.is_active=True and r.status in ('assigned','matched') {org_condition}"""
requests_data = pd.read_sql(query, connections('prod_db_read'))
requests_data = requests_data.fillna('')
requests_data = requests_data.sort_values(by=['assignment_time'],
ascending=[False])
requests_data['requestor_chat'] = requests_data[
'requestor_mob_number'].apply(lambda x: 'http://wa.me/91' + str(x))
requests_data['volunteer_chat'] = requests_data[
'volunteer_mob_number'].apply(lambda x: 'http://wa.me/91' + str(x))
return requests_data
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['full_address'] = v_df['address'].fillna('')+', '+v_df['geoaddress'].fillna('')
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 completed_request_page(uuid,v_id):
query = f"""Select r.id as r_id,r.name as `requestor_name`, r.uuid as `uuid`, r.timestamp as `request_time`,
r.source as `source`, r.status as `request_status`,r.request, rv.where as `where`,
rv.what as `what`, rv.why as `why`, rv.financial_assistance, rv.urgent,
v.id as v_id, v.name as `volunteer_name`,v.city as `volunteer_city`,
rm.timestamp as `assignment_time`, r.city, so.organisation_name as source_org, so.logo_url as org_logo
from requests r
left join request_verification rv on rv.r_id=r.id
left join request_matching rm on rm.request_id=r.id and rm.is_active=1
left join volunteers v on v.id=rm.volunteer_id
left join users u on u.id = r.managed_by
left join support_orgs so on so.org_code=r.source
where rm.is_active=True and r.status in ('completed') and r.uuid={uuid} and v.id={v_id}"""
requests_data = pd.read_sql(query, connections('prod_db_read'))
requests_data['source_org'] = requests_data['source_org'].fillna('COVIDSOS')
requests_data['org_logo'] = requests_data['org_logo'].fillna('')
requests_data['financial_assistance']=requests_data['financial_assistance'].fillna(0)
if(requests_data.shape[0]>1):
requests_data = requests_data[0:0]
return requests_data
def send_sms(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)
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())]
}
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 add_requests(df):
#df with columns [timestamp, name,mob_number, email_id, country, address, geoaddress, latitude, longitude, source, request,age]
expected_columns = [
'timestamp', 'name', 'mob_number', 'email_id', 'country', 'address',
'geoaddress', 'latitude', 'longitude', 'source', 'members_impacted',
'request', 'age', 'status', 'uuid', 'managed_by'
]
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:
return_str = 'Data Format not matching'
return False, return_str
def get_assigned_requests(org):
org_condition = f"and r.source='{org}'" if org != 'covidsos' else ''
query = f"""Select r.id as r_id, r.uuid as `uuid`, r.name as `requestor_name`, r.mob_number as `requestor_mob_number`, r.volunteers_reqd as `volunteer_count`,r.timestamp as `request_time`,
r.source as `source`, r.status as `request_status`,r.request, rv.where as `where`, rv.what as `what`, rv.why as `why`, rv.financial_assistance, rv.urgent,
v.id as v_id, v.name as `volunteer_name`, v.mob_number as `volunteer_mob_number`,rm.timestamp as `assignment_time`, u.name as managed_by, u.id as managed_by_id, r.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 request_matching rm on rm.request_id=r.id and rm.is_active=1
left join volunteers v on v.id=rm.volunteer_id
left join users u on u.id = r.managed_by
left join support_orgs so on so.org_code=r.source
where rm.is_active=True and r.status in ('assigned','matched','verified') and v.id is not NULL {org_condition}"""
requests_data = pd.read_sql(query,connections('prod_db_read'))
requests_data['managed_by'] = requests_data['managed_by'].fillna('admin')
requests_data['managed_by_id'] = requests_data['managed_by_id'].fillna(0)
requests_data = requests_data.fillna('')
requests_data = requests_data.sort_values(by=['assignment_time'],ascending=[False])
requests_data['requestor_chat']=requests_data['requestor_mob_number'].apply(lambda x:'http://wa.me/91'+str(x))
requests_data['volunteer_chat']=requests_data['volunteer_mob_number'].apply(lambda x:'http://wa.me/91'+str(x))
return requests_data
def check_user(table_name, user_id):
server_con = connections('prod_db_read')
errorResponse = {
'Response': {},
'string_response': 'Requesting user ID does not exist in database',
'status': False
}
try:
query = """Select id from {table_name} where id={user_id}""".format(
table_name=table_name, user_id=user_id)
data = pd.read_sql(query, server_con)
if (data.shape[0] > 0):
return {
'Response': {},
'string_response': 'User Existence validated',
'status': True
}
else:
return errorResponse
except:
mailer.send_exception_mail()
return errorResponse
