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)
Exemple #2
0
    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
Exemple #3
0
    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
Exemple #4
0
            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))