def __last_sync(self):
"""
Given user_id, returns the date of the most recent sync.
"""
last_policy_sync = db.query(
'SELECT date FROM policies WHERE user_id=$self.user_id '
'ORDER BY date DESC LIMIT 1',
vars=locals())
last_event_sync = db.query(
'SELECT date FROM journal WHERE user_id=$self.user_id AND committed=1 '
'ORDER BY date DESC LIMIT 1',
vars=locals())
# Query responses are iterators. As a result every time last_policy_sync[0] is called, it pops that item.
# Next time last_policy_sync[0] is called, the response is different. Using local variables to work around this.
policy_date = last_policy_sync[0].date
#CANT COMPARE DATETIME AND DATE!!!
if len(last_event_sync) > 0:
event_date = last_event_sync[0].date
if event_date > policy_date:
return event_date
return policy_date
def find_avg(self):
average_risk = db.query(
"SELECT AVG(score_value)as avg FROM scores WHERE score_type =1;"
)[0]
average_cost = db.query(
"SELECT AVG(score_value)as avg FROM scores WHERE score_type =2;"
)[0]
return average_risk.avg, average_cost.avg
def find_avg(self, your_score):
# score_type = your_score.score_type
# average = db.select('scores', where="score_type=$score_type", vars=locals())
average_risk = db.query("SELECT AVG(score_value)as avg FROM scores WHERE score_type =1;")[0]
average_cost = db.query("SELECT AVG(score_value)as avg FROM scores WHERE score_type =2;")[0]
# avg = average[0].avg
return average_risk.avg, average_cost.avg
def clear_prophecy(self, date, policy_update):
"""
Clears uncommitted entries in the journal for specified user_id on or after the specified date. Returns None.
"""
policy_update = policies_model().nested_obj_to_list_of_dict(policies_model().parse_policy(policy_update))
for policy in policy_update:
employee = policy['data']['employee']
location = policy['data']['location']
device = policy['data']['device']
p_context = 'AND employee=$employee AND location=$location AND device=$device'
db.query('DELETE FROM journal WHERE user_id=$self.user_id AND committed=false AND date>=$date '+p_context, vars=locals())
def clear_prophecy(self, date, policy_update):
"""
Clears uncommitted entries in the journal for specified user_id on or after the specified date. Returns None.
"""
policy_update = policies_model().nested_obj_to_list_of_dict(
policies_model().parse_policy(policy_update))
for policy in policy_update:
employee = policy['data']['employee']
location = policy['data']['location']
device = policy['data']['device']
p_context = 'AND employee=$employee AND location=$location AND device=$device'
db.query(
'DELETE FROM journal WHERE user_id=$self.user_id AND committed=false AND date>=$date '
+ p_context,
vars=locals())
def last_sync(cls, user_id):
"""
Given user_id, returns the date of the most recent sync.
"""
last_policy_sync = db.query('SELECT date FROM policies WHERE user_id=$user_id '
'ORDER BY date DESC LIMIT 1', vars=locals())
last_event_sync = db.query('SELECT date FROM journal WHERE user_id=$user_id AND committed=true '
'ORDER BY date DESC LIMIT 1', vars=locals())
if len(last_event_sync) > 0 and last_event_sync[0].date > last_policy_sync[0].date:
return last_event_sync[0].date
return last_policy_sync[0].date
def update(cls, where, values):
"""
Generates query string using db_helper.update_helper.stringify, and runs db.query.
"""
return db.query(db_helper.update_helper.stringify(
'pw_policy', where, values),
vars=locals())
def get_calendar(cls, user_id, sync_date):
"""
Retrieve all events (past or future) for given user_id for month that the specified date falls on.
Returns a custom dictionary-based data structure based on the REST API JSON spec.
:param user_id:
:param sync_date:
"""
start_date = datetime.date(sync_date.year, sync_date.month, 1)
end_date = (start_date + timedelta(days=32)).replace(day=1)
raw_calendar = db.query('SELECT * FROM journal '
'WHERE user_id=$user_id AND date>=$start_date AND date<$end_date', vars=locals())
calendar = {}
# Converts database results into dictionary
for event in raw_calendar:
if event.date not in calendar:
calendar[event.date] = {
'date': event.date.isoformat(),
'events': []
}
calendar[event.date].events.append({
'incdt_id': event.incident_id,
'cost': event.cost
})
calendar_array = []
# Converts calendar dictionary into array
for date, agenda in sorted(calendar.iteritems()):
calendar_array.append(agenda)
return calendar_array
def get(self, policy, context, risk):
query = "SELECT `name`, `risk_prob` FROM `risks` WHERE"
query = query + "`risk_type` = '" + risk + "'"
for next in simulation.ordered_context:
query = query + " AND `" + next + "` = '" + context[next] + "'"
for next in simulation.ordered_policy:
query = query + " AND `" + next + "` = " + str(policy[next])
query = query + " LIMIT 1"
return db.query(query)
def get(self, policy, context, risk):
query = "SELECT `name`, `risk_prob` FROM `risks` WHERE"
query = query + "`risk_type` = '" + risk + "'"
for next in simulation.ordered_context:
query = query + " AND `" + next + "` = '" + context[next] + "'"
for next in simulation.ordered_policy:
query = query + " AND `" + next + "` = " + str(policy[next])
query = query + " LIMIT 1"
return db.query(query)
def set(self, policy, context, risk, name, value):
if len(self.get(policy, context, risk)) > 0:
raise Exception('Policy already exists in database')
query = "INSERT INTO `risks` VALUES ('" + risk + "'"
for next in simulation.ordered_context:
query = query + ", '" + context[next] + "'"
for next in simulation.ordered_policy:
query = query + ", '" + str(policy[next]) + "'"
query = query + ", '" + name + "', '" + str(value) + "')"
return db.query(query)
def set(self, policy, context, risk, name, value):
if len(self.get(policy, context, risk)) > 0:
raise Exception('Policy already exists in database')
query = "INSERT INTO `risks` VALUES ('" + risk + "'"
for next in simulation.ordered_context:
query = query + ", '" + context[next] + "'"
for next in simulation.ordered_policy:
query = query + ", '" + str(policy[next]) + "'"
query = query + ", '" + name + "', '" + str(value) + "')"
return db.query(query)
def __next_due_event_date(self):
"""
Given user_id, returns the date for the first event due after previous sync. If no event found, returns none.
"""
result = db.query('SELECT date FROM journal WHERE user_id=$self.user_id AND committed=false '
'GROUP BY date ORDER BY date ASC LIMIT 1', vars=locals())
if len(result) > 0:
return result[0].date
return None
def __next_due_event_date(self):
"""
Given user_id, returns the date for the first event due after previous sync. If no event found, returns none.
"""
result = db.query(
'SELECT date FROM journal WHERE user_id=$self.user_id AND committed=false '
'GROUP BY date ORDER BY date ASC LIMIT 1',
vars=locals())
if len(result) > 0:
return result[0].date
return None
def __last_sync(self):
"""
Given user_id, returns the date of the most recent sync.
"""
last_policy_sync = db.query('SELECT date FROM policies WHERE user_id=$self.user_id '
'ORDER BY date DESC LIMIT 1', vars=locals())
last_event_sync = db.query('SELECT date FROM journal WHERE user_id=$self.user_id AND committed=1 '
'ORDER BY date DESC LIMIT 1', vars=locals())
# Query responses are iterators. As a result every time last_policy_sync[0] is called, it pops that item.
# Next time last_policy_sync[0] is called, the response is different. Using local variables to work around this.
policy_date = last_policy_sync[0].date
#CANT COMPARE DATETIME AND DATE!!!
if len(last_event_sync) > 0:
event_date = last_event_sync[0].date
if event_date > policy_date:
return event_date
return policy_date
def get_policy_history(cls, user_id, latest=False):
"""
Returns list of past policies set by user.
:param user_id: user_id of user to get policies for
:param latest: flag limits to only getting the latest set of policies
"""
restrict_latest = "AND policies.date=(SELECT MAX(date) FROM policies WHERE user_id=$user_id) " if latest else ""
return db.query(
"SELECT * FROM policies "
"LEFT OUTER JOIN biometrics ON policies.bio_id = biometrics.id "
"LEFT OUTER JOIN passfaces ON policies.pass_id = passfaces.id "
"LEFT OUTER JOIN pw_policy ON policies.pw_id = pw_policy.id "
"WHERE policies.user_id=$user_id " + restrict_latest + "ORDER BY policies.date DESC LIMIT 54",
vars=locals(),
)
def get_calendar(self, sync_date):
"""
Retrieve all events (past or future) for given user_id for month that the specified date falls on.
Returns a custom dictionary-based data structure based on the REST API JSON spec.
:param sync_date:
"""
start_date = datetime.date(sync_date.year, sync_date.month, 1)
end_date = (start_date + timedelta(days=32)).replace(day=1)
raw_calendar = db.query(
'SELECT * FROM journal '
'WHERE user_id=$self.user_id AND date>=$start_date AND date<$end_date',
vars=locals())
calendar = {}
# Converts database results into dictionary
for event in raw_calendar:
if event.date not in calendar:
calendar[event.date] = {
'date': event.date.isoformat(),
'events': []
}
calendar[event.date]['events'].append({
'incdt_id': event.incident_id,
'cost': event.cost,
'employee': event.employee,
'location': event.location,
'device': event.device
})
calendar_array = []
# Converts calendar dictionary into array
for date, agenda in sorted(calendar.iteritems()):
calendar_array.append(agenda)
print "calendar"
print calendar_array
return calendar_array
def get_policy_history(cls, user_id, latest=False):
"""
Returns list of past policies set by user.
:param user_id: user_id of user to get policies for
:param latest: flag limits to only getting the latest set of policies
"""
results_list = []
restrict_latest = 'AND policies.date=(SELECT MAX(date) FROM policies WHERE user_id=$user_id) ' if latest else ''
res = db.query(
'SELECT * FROM policies '
'LEFT OUTER JOIN biometrics ON policies.bio_id = biometrics.id '
'LEFT OUTER JOIN passfaces ON policies.pass_id = passfaces.id '
'LEFT OUTER JOIN pw_policy ON policies.pw_id = pw_policy.id '
'WHERE policies.user_id=$user_id ' + restrict_latest +
'ORDER BY policies.date DESC LIMIT 324', vars=locals())
# Why are policies limited to 54? Shouldn't they be 27 (3x3x3)?
for row in res:
tmp = {}
for key, value in row.iteritems():
tmp[key] = str(value)
results_list.append(tmp)
return results_list
def __recent_events(self):
"""
Returns the latest events for current user, ordering them by date and choosing the committed events only.
"""
events_list = []
#restrict to events which have already happened
restrict_committed = 'AND journal.committed=1 '
#restrict to the latest events
#assuming that max 1 event/day can happen and there are max 31 days in 1 month
restrict_latest = 'LIMIT 31'
result = db.query(
'SELECT * FROM journal '
'WHERE journal.user_id=$self.user_id ' + restrict_committed +
'ORDER BY journal.date DESC ' + restrict_latest, vars=locals())
for row in result:
tmp = {}
for key, value in row.iteritems():
tmp[key] = str(value)
events_list.append(tmp)
return events_list
def get_policy_history(cls, user_id, latest=False):
"""
Returns list of past policies set by user.
:param user_id: user_id of user to get policies for
:param latest: flag limits to only getting the latest set of policies
"""
results_list = []
restrict_latest = 'AND policies.date=(SELECT MAX(date) FROM policies WHERE user_id=$user_id) ' if latest else ''
res = db.query(
'SELECT * FROM policies '
'LEFT OUTER JOIN biometrics ON policies.bio_id = biometrics.id '
'LEFT OUTER JOIN passfaces ON policies.pass_id = passfaces.id '
'LEFT OUTER JOIN pw_policy ON policies.pw_id = pw_policy.id '
'WHERE policies.user_id=$user_id ' + restrict_latest +
'ORDER BY policies.date DESC LIMIT 324',
vars=locals())
# Why are policies limited to 54? Shouldn't they be 27 (3x3x3)?
for row in res:
tmp = {}
for key, value in row.iteritems():
tmp[key] = str(value)
results_list.append(tmp)
return results_list
def __recent_events(self):
"""
Returns the latest events for current user, ordering them by date and choosing the committed events only.
"""
events_list = []
#restrict to events which have already happened
restrict_committed = 'AND journal.committed=1 '
#restrict to the latest events
#assuming that max 1 event/day can happen and there are max 31 days in 1 month
restrict_latest = 'LIMIT 31'
result = db.query('SELECT * FROM journal '
'WHERE journal.user_id=$self.user_id ' +
restrict_committed + 'ORDER BY journal.date DESC ' +
restrict_latest,
vars=locals())
for row in result:
tmp = {}
for key, value in row.iteritems():
tmp[key] = str(value)
events_list.append(tmp)
return events_list
def update(cls, where, values):
"""
Generates query string using db_helper.update_helper.stringify, and runs db.query.
"""
return db.query(db_helper.update_helper.stringify('pw_policy', where, values), vars=locals())
def find_avg(self):
average_risk = db.query("SELECT AVG(score_value)as avg FROM scores WHERE score_type =1;")[0]
average_cost = db.query("SELECT AVG(score_value)as avg FROM scores WHERE score_type =2;")[0]
return average_risk.avg, average_cost.avg
def clear_prophecy(cls, user_id, date):
"""
Clears uncommitted entries in the journal for specified user_id on or after the specified date. Returns None.
"""
db.query('DELETE FROM journal WHERE user_id=$user_id AND committed=false AND date>=$date', vars=locals())
