def accounts():
month_year = request.args.get('month_year')
month_years = (db.session.query(func.strftime("%m-%Y", Transaction.date))
.select_from(Transaction)
.distinct()
.order_by(func.strftime("%Y-%m", Transaction.date))
.all()
)
month_years = [each[0] for each in month_years]
accounts_query = (db.session.query(
func.sum(Transaction.withdrawal),
func.sum(Transaction.deposit),
Account.full_title)
.select_from(Account)
.outerjoin(Transaction)
.group_by(Account.id)
.order_by(Account.full_title)
)
if month_year:
accounts_query = accounts_query.filter(func.strftime("%m-%Y", Transaction.date) == month_year)
accounts = accounts_query.all()
def relabel(account):
return {"withdrawal": account[0],
"deposit": account[1],
"full_title": account[2]}
accounts = [relabel(account) for account in accounts]
return render_template('accounts.html', accounts=accounts, title="Accounts", month_year=month_year, month_years=month_years)
def indirect_friends(self):
logger.debug("Looking for indirect friends")
self.supp_owner_friend_map = {}
for auth_owner in self.authorizer_owners:
if not self.auth_owner_friend_map.has_key(auth_owner):
self.auth_owner_friend_map[auth_owner] = self.fetch_friends(auth_owner)
for supp_owner in reversed(self.supplicant_owners):
if not self.supp_owner_friend_map.has_key(supp_owner):
self.supp_owner_friend_map[supp_owner] = self.fetch_friends(supp_owner)
if self.supp_uri not in self.supp_owner_friend_map[supp_owner]: # it seems that supplicant device is making up new artificial owners.
self.supp_owner_friend_map.remove(supp_owner)
self.supplicant_owners.remove(supp_owner)
continue
common_friends = filter(lambda x : x in self.auth_owner_friend_map[auth_owner],
self.supp_owner_friend_map[supp_owner])
logger.debug("common indirect friends: %s",common_friends)
logger.debug("supp uri: %s \n supp_owner_friends:%s ",self.supp_uri,self.supp_owner_friend_map[supp_owner])
common_friends_around = []
# For each possible supplicant mac device, we will find the possible common friends who are around now (at least in last 30 minutes) and we will order them according to their
# temporal closeness to supplicant device based on first_seen time.
for target_mac in self.mac_list:
supp_device = sniffer.session.query(mac_sniffer.Device).filter(mac_sniffer.Device.mac == target_mac).first()
friends_around = sniffer.session.query(Person.uri).join(Person.devices).\
filter( \
and_( \
Person.uri.in_(common_friends), \
((func.strftime('%s','now') - func.strftime('%s', Device.last_seen )) <= 2*30*60) \
) \
).\
order_by(func.abs(func.strftime('%s',supp_device.first_seen) - func.strftime('%s',Device.first_seen))).\
all()
# Unfortunately sql returns a weird list like [(u'https://',), (u'https://',)], but we need only list of Uris not list of list of uris
common_friends_around.append(map(lambda x: x[0],friends_around))
sorted_commmon_friends_around = ordered_set.OrderedSet([])
for i in range(reduce(lambda acc,x: max(len(x),acc) ,common_friends_around,0 )):
for friend_list in common_friends_around:
if len(friend_list): sorted_commmon_friends_around.add(friend_list.pop(0))
logger.debug("Sorted Common friends that are around: %s",sorted_commmon_friends_around)
if not self.bounded_search:
for x in common_friends:
sorted_commmon_friends_around.add(x)
for common in sorted_commmon_friends_around:
if self.check_for_link(webid_from_cache(common,fresh=True,verify_server_cert=False), supp_owner, False):
logger.info("InDIRECT_FRIEND: Auth:%s and Supp:%s has indirect_friend:%s",self.auth_uri,self.supp_uri, common)
# TODO: we should directly return TRUE, we changed the below value only for testing
#retvalue = True
return True
else: # supplicant owner to common friend there is one directional relation. common person does not know supp owner.
self.supp_owner_friend_map[supp_owner].remove(common)
return False
def indirect_friends(self):
logger.debug("Looking for indirect friends")
self.supp_owner_friend_map = {}
for auth_owner in self.authorizer_owners:
if auth_owner not in self.auth_owner_friend_map:
self.auth_owner_friend_map[auth_owner] = self.fetch_friends(
auth_owner)
for supp_owner in reversed(self.supplicant_owners):
if supp_owner not in self.supp_owner_friend_map:
self.supp_owner_friend_map[
supp_owner] = self.fetch_friends(supp_owner)
# it seems that supplicant device is making up new artificial owners.
if self.supp_uri not in self.supp_owner_friend_map[supp_owner]:
self.supp_owner_friend_map.remove(supp_owner)
self.supplicant_owners.remove(supp_owner)
continue
common_friends = [
x for x in self.supp_owner_friend_map[supp_owner]
if x in self.auth_owner_friend_map[auth_owner]
]
logger.debug("common indirect friends: %s", common_friends)
logger.debug("supp uri: %s \n supp_owner_friends:%s ",
self.supp_uri,
self.supp_owner_friend_map[supp_owner])
common_friends_around = []
# For each possible supplicant mac device, we will find the possible common friends who are
# around now (at least in last 30 minutes) and we will order them according to their
# temporal closeness to supplicant device based on first_seen time.
for target_mac in self.mac_list:
supp_device = sniffer.session.query(
mac_sniffer.Device).filter(
mac_sniffer.Device.mac == target_mac).first()
friends_around = sniffer.session.query(Person.uri).join(Person.devices).\
filter( \
and_( \
Person.uri.in_(common_friends), \
((func.strftime('%s','now') - func.strftime('%s', Device.last_seen )) <= 2*30*60) \
) \
).\
order_by(func.abs(func.strftime('%s',supp_device.first_seen) - func.strftime('%s',Device.first_seen))).\
all()
# Unfortunately sql returns a weird list like [(u'https://',), (u'https://',)], but we need only list of Uris not list of list of uris
common_friends_around.append(
map(lambda x: x[0], friends_around))
sorted_commmon_friends_around = ordered_set.OrderedSet([])
for i in range(
reduce(lambda acc, x: max(len(x), acc),
common_friends_around, 0)):
for friend_list in common_friends_around:
if len(friend_list):
sorted_commmon_friends_around.add(
friend_list.pop(0))
logger.debug("Sorted Common friends that are around: %s",
sorted_commmon_friends_around)
if not self.bounded_search:
for x in common_friends:
sorted_commmon_friends_around.add(x)
for common in sorted_commmon_friends_around:
if self.check_for_link(
webid_from_cache(common,
fresh=True,
verify_server_cert=False),
supp_owner, False):
logger.info(
"InDIRECT_FRIEND: Auth:%s and Supp:%s has indirect_friend:%s",
self.auth_uri, self.supp_uri, common)
# TODO: we should directly return TRUE, we changed the below value only for testing
#retvalue = True
return True
else: # supplicant owner to common friend there is one directional relation. common person does not know supp owner.
self.supp_owner_friend_map[supp_owner].remove(common)
return False
def makeParam(key, value):
""" Takes a value list as input and concatenates with OR.
This means that {age: [1, 12, 13]} will yield a result if
age == 1 OR age == 12 OR age == 13.
"""
if not (isinstance(value, list) or isinstance(value, tuple)):
value = [value]
or_clause = []
# Ask for the type of the parameter according to the entity
parameter_class = parameter_for_type(entity.declared_params[key])
for val in value:
if callable(val):
# we’ve been given a function
or_clause.append(val(parameter_class.value))
elif parameter_class == StringParameter:
# test string using ‘like’
if not options["strict"]:
val = "%" + val + "%"
or_clause.append(parameter_class.value.like(val))
else:
if options["convert_string"]:
try:
val = parameter_class.from_string(val)
except StringConversionError:
if parameter_class == DateParameter:
# Here, we want to match a certain YEAR, a certain
# combination of YEAR-MONTH or a certain combination
# YEAR-MONTH-DAY from a date.
# Therefore, we need to extract YEAR, MONTH and DAY
# from a date and match those separately.
# Unfortunately, there is no common SQL function for
# this task, so we're left with ``date_part('year', date)``
# for Postgres and ``strftime('%Y', date)`` for Sqlite.
# We check the `engine_name` and generate the respective
# methods.
# get year month day
ymd = val.split('-')
clauses = []
from sqlalchemy.sql.expression import func
if self.connection.engine_name == "postgresql":
year_part = lambda value: func.date_part('year', value)
month_part = lambda value: func.date_part('month', value)
day_part = lambda value: func.date_part('day', value)
elif self.connection.engine_name == "sqlite":
year_part = lambda value: func.strftime('%Y', value)
month_part = lambda value: func.strftime('%m', value)
day_part = lambda value: func.strftime('%d', value)
else:
raise ValueError("Unsupported operation: Unknown engine name %r." %
self.connection.engine_name)
if len(ymd) > 0:
clauses.append(year_part(parameter_class.value) == ymd[0])
if len(ymd) > 1:
clauses.append(month_part(parameter_class.value) == ymd[1])
if len(ymd) > 2:
clauses.append(day_part(parameter_class.value) == ymd[2])
clause = (and_(*clauses))
or_clause.append(clause)
else:
raise
else:
or_clause.append(parameter_class.value == val)
# FIXME
return entity._params.of_type(parameter_class).any(or_(*or_clause))