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 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 __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())