def registerNewCustomer(customer):
"""Inserts a new customer in the customer table"""
sql = 'CALL public.registernewcustomer(%s, %s, %s, %s, %s);'
params = (customer['username'], customer['phone'],
int(customer['distance']), float(customer['longitude']),
float(customer['latitude']))
executeSQL(sql, params, getDBConnection())
def insertIncidentRecord(incident, useDotEnvFlag=False):
"""Inserts a new incident record into inciweb table"""
sql = 'CALL public.insertnewincident(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
params = (str(incident['name']), str(incident['type']),
str(incident['summary']), str(incident['state']),
str(incident['updated']), float(incident['lat']),
float(incident['lng']), str(incident['size']),
str(incident['url']), str(incident['id']),
str(incident['contained']))
executeSQL(sql, params, getDBConnection(useDotEnvFlag))
def getAllSmsHistory(useDotEnvFlag=False):
"""Returns all sms history records"""
sql = 'SELECT feed_id, cust_id, CAST(distance AS TEXT), msg_sid, modified FROM sms_history;'
sms_history = executeSQL(sql,
None,
getDBConnection(useDotEnvFlag),
fetchAllFlag=True)
return sms_history
def getAllIncidentIds(useDotEnvFlag=False):
"""Returns all incident ids from inciweb table"""
sql = 'SELECT feed_id FROM inciweb;'
incidents = executeSQL(sql,
None,
getDBConnection(useDotEnvFlag),
fetchAllFlag=True)
return incidents
def distanceBetweenPointsInMiles(lng1, lat1, lng2, lat2, useDotEnvFlag=False):
"""Returns distance in miles between two coordinates in WGS84 geographic spatial reference"""
sql = 'SELECT public.getdistancebetweenpoints(%s, %s, %s, %s);'
params = (lng1, lat1, lng2, lat2)
distanceResult = executeSQL(sql,
params,
getDBConnection(useDotEnvFlag),
fetchAllFlag=True)
for row in distanceResult:
distanceMeters = row['getdistancebetweenpoints']
distanceMiles = float(distanceMeters) * 0.00062
return round(distanceMiles, 1)
def getAllCustomers(hidePhoneNumber=True, useDotEnvFlag=False):
"""Returns all customer data and locations from customer table"""
if hidePhoneNumber:
sql = """SELECT user_id, user_name, '(XXX)XXX-' || RIGHT(user_phone, 4) as userphone, """
sql += """user_distance, ST_Y(geom) as latitude, ST_X(geom) as longitude, modified """
sql += """FROM customer;"""
else:
sql = """SELECT user_id, user_name, user_phone, """
sql += """user_distance, ST_Y(geom) as latitude, ST_X(geom) as longitude, modified """
sql += """FROM customer;"""
customers = executeSQL(sql,
None,
getDBConnection(useDotEnvFlag),
fetchAllFlag=True)
return customers
def insertSmsHistoryRecord(history, useDotEnvFlag=False):
"""Inserts a new history record into sms_history table"""
sql = 'CALL public.insertnewhistory(%s, %s, %s, %s);'
params = (int(history['feed_id']), int(history['cust_id']),
float(history['distance']), str(history['msg_sid']))
executeSQL(sql, params, getDBConnection(useDotEnvFlag))