def getformname(custid):
conn = make_conn()
query_cmd = "select TemplateName, TemplateMasterID from TemplateMaster where UserID = " \
"" + str(custid) + ';'
result = fetch_data(conn, query_cmd)
conn.close()
return result
def lambda_handler(event, context):
query_cmd = "select count(*) from prediction"
conn = make_conn()
cur = conn.cursor()
lastUpdateDate = list(
fetch_data(
conn,
"""SELECT submission_date from prediction ORDER BY submission_date DESC Limit 1"""
)[0])[0]
cur.execute("""SELECT * from prediction WHERE submission_date = '%s';""" %
(lastUpdateDate))
rawData = cur.fetchall()
arrAverage = []
for data in rawData:
json_data = list(data)
arrAverage.append({
'year': json_data[0][u'year'],
'debt': json_data[1]
})
conn.close()
return json.dumps({'averages': arrAverage})
def getcustomername(custid):
conn = make_conn()
query_cmd = "select Name from UserMaster where UserID = " + str(
custid) + ';'
result = fetch_one_row_data(conn, query_cmd)
conn.close()
return result
def saveformfields(formid, fieldid, value):
conn = make_conn()
#query_cmd = "UPDATE FieldDetails SET FieldValue = 'PANKAJ' WHERE FieldMasterID = 1 AND TemplateCustomerMappingID = 14"
query_cmd = "UPDATE FieldDetails SET FieldValue = '" + str(value) + "' WHERE " \
"FieldMasterID = " + str(fieldid) + " AND TemplateCustomerMappingID = " + str(formid)
execute_query(conn, query_cmd)
conn.close()
return "OK"
def getformdata(formid):
conn = make_conn()
query_cmd = "select FieldMaster.FieldName, FieldDetails.FieldValue from FieldDetails " \
"INNER JOIN FieldMaster ON FieldMaster.FieldMasterID = FieldDetails.FieldMasterID " \
"WHERE FieldDetails.TemplateCustomerMappingID = " + str(formid) + ";"
result = fetch_data(conn, query_cmd)
conn.close()
return result
def getformfields(formnumber):
conn = make_conn()
query_cmd = "select FieldMasterID, FieldName from FieldMaster where TemplateMasterID" \
" = " + str(formnumber) + ";"
# query_cmd = "select FieldMasterID, FieldName from FieldMaster where TemplateMasterID = 1;"
result = fetch_data(conn, query_cmd)
conn.close()
return result
def lambda_handler(event, context):
averageObj = []
query_cmd = "select count(*) from prediction"
conn = make_conn()
cur = conn.cursor()
events = fetch_data(conn, """SELECT event from prediction""")
dicAverage = {}
for data in events:
json_dict = list(data)[0]
if str(json_dict[u'year']) in dicAverage.keys():
dicAverage[str(json_dict[u'year'])].append(json_dict[u'debt'])
else:
dicAverage[str(json_dict[u'year'])] = [json_dict[u'debt']]
submission_date = datetime.datetime.now()
print("---> submission date\n", submission_date, "\n")
averages = {}
for data in event['data']:
def sum_list(items):
sum_numbers = 0
for x in items:
sum_numbers += x
return sum_numbers
average = data['debt']
if dicAverage.get(str(data['year'])) is not None:
prev_debts = dicAverage[str(data['year'])]
average = (sum_list(prev_debts) +
data['debt']) / (len(prev_debts) + 1)
averages[str(data['year'])] = average
cur.execute(
"""INSERT INTO prediction (event, submission_date, average) VALUES (%s::json, %s, %s);""",
(json.dumps(data), submission_date, average))
conn.commit()
conn.close()
return {
'statusCode': 200,
'headers': {
'Content-Type': 'application/json'
},
'body': averages
}
def lambda_handler(event, context):
conn = make_conn()
cur = conn.cursor()
cur.execute("""TRUNCATE TABLE prediction""");
conn.commit()
conn.close()
return json.dumps({'status': 'success' })
def getformNumber(formnumber):
conn = make_conn()
TemplateCustomerMappingID = ''
query_cmd = "INSERT INTO TemplateCustomerMapping(UniqueFormNumber, TemplateMasterID) " \
"VALUES(0, " + str(formnumber) + ") RETURNING TemplateCustomerMappingID"
# query_cmd = "INSERT INTO TemplateCustomerMapping (UniqueFormNumber, TemplateMasterID) VALUES(0, 1) RETURNING TemplateCustomerMappingID "
TemplateCustomerMappingID = execute_query_value(conn, query_cmd)
query_cmd = "INSERT INTO FieldDetails(FieldValue, FieldMasterID, TemplateCustomerMappingID) " \
"SELECT '', FieldMasterID, " + str(TemplateCustomerMappingID) + \
" from FieldMaster WHERE TemplateMasterID = " + str(formnumber) + ";"
# query_cmd = "INSERT INTO FieldDetails(FieldValue, FieldMasterID, TemplateCustomerMappingID) SELECT '', FieldMasterID, " + str(TemplateCustomerMappingID) + " from FieldMaster WHERE TemplateMasterID = 1;"
result = execute_query(conn, query_cmd)
conn.close()
return TemplateCustomerMappingID
def get_db_conn(db_password):
conn = None
db_host = None
db_name = None
db_user = None
if 'DB_HOST' in os.environ:
db_host = os.environ['DB_HOST']
if 'DB_NAME' in os.environ:
db_name = os.environ['DB_NAME']
if 'DB_USER' in os.environ:
db_user = os.environ['DB_USER']
if db_host and db_name and db_user:
print("Attempting to connect to DB [host={} name={} user={} pass=******]".format(db_host,db_name,db_user))
conn = make_conn(os.environ['DB_HOST'],os.environ['DB_NAME'],os.environ['DB_USER'],db_password)
else:
print("Missing database config. Ensure DB_HOST, DB_NAME and DB_USER are defined in the environment")
return conn
def lambda_handler(event, context):
## TODO: ask for api_key from https://developer-tripadvisor.com
api_key = ""
location_mapper_url_template = "http://api.tripadvisor.com/api/partner/2.0/location_mapper/{},{}?key={}-mapper&category=restaurants&q={}"
url = location_mapper_url_template.format(event['lat'], event['lng'],
api_key, event['name'])
location_info = urllib.request.urlopen(url).read()
location_rating_url_template = "http://api.tripadvisor.com/api/partner/2.0/location/{}?key={}"
url = location_rating_url_template.format(location_info['location_id'],
api_key)
customers_feedback = urllib.request.urlopen(url).read()
#print customers_feedback
query_template = "INSERT INTO table (lat, lng, name, rating) VALUES ({}, {}, {}, {});"
query = query_template.format(event['lat'], event['lng'], event['name'],
customers_feedback[rating])
conn = make_conn()
save_data(conn, query)
conn.close()
return customers_feedback[rating]
def updateOperationCompleted():
conn = make_conn()
query_cmd = "update currentoperation set operationid = 2;"
execute_query(conn, query_cmd)
conn.close()
return "OK"
def updateOperationFormDetails():
conn = make_conn()
query_cmd = "update currentoperation set operationid = 1;"
execute_query(conn, query_cmd)
conn.close()
return "OK"
def updateOperationWelcome():
conn = make_conn()
query_cmd = "update currentoperation set operationid = 0;"
execute_query(conn, query_cmd)
conn.close()
return "OK"
