def dump_csv(self, id):
        dbFacade = self.dbFacade()

        if "all" == id:
            balance_uids = [userBalance.balance_uid for userBalance in h.authenticated_user().balances]
        else:
            c.balance = dbFacade.balances.balanceDao.find_by_uid(id)
            if not c.balance:
                abort(404)

            if not c.balance.can_modify_balance(h.authenticated_user().uid):
                abort(403)
            balance_uids = [c.balance.uid]
        
        charset = request.params.get('charset', 'cp1250')

        changes = dbFacade.db.query(dbFacade.model.BalanceChange) \
            .options(eagerload('change_category'), eagerload('tags')).join(dbFacade.model.BalanceChange.change_category) \
            .filter(or_(*[dbFacade.model.BalanceChange.balance.has(dbFacade.model.Balance.uid == uid) for uid in balance_uids])) \
            .order_by(dbFacade.model.BalanceChange.occurred_on, dbFacade.model.BalanceChange.uid).all()

        response.headers['Content-Type'] = str('text/csv; charset=%s' % charset)
        response.headers['Content-Disposition'] = str("attachment; filename = %s.csv" % self.filename((hasattr(c, 'balance') and c.balance.name or "all")))
        output = StringIO()
        csv = UnicodeWriter(output, encoding=charset)
        csv.writerows([
            [unicode(change.occurred_on), unicode(change.amount), change.change_category.name, change.description, change.tags_as_string() ] for change in changes
        ])
        return output.getvalue()

def _topic_feed(request, title, query, order_by):
    # non moderators cannot see deleted posts, so we filter them out first
    # for moderators we mark the posts up as deleted so that
    # they can be kept apart from non-deleted ones.
    if not request.user or not request.user.is_moderator:
        query = query.filter_by(is_deleted=False)
    query = query.order_by(_topic_order[order_by])
    query = query.options(eagerload('author'), eagerload('question'))
    query = query.limit(max(0, min(50, request.args.get('num', 10, type=int))))

    feed = AtomFeed(u'%s — %s' % (title, settings.WEBSITE_TITLE),
                    subtitle=settings.WEBSITE_TAGLINE,
                    feed_url=request.url,
                    url=request.url_root)

    for topic in query.all():
        title = topic.title
        if topic.is_deleted:
            title += u' ' + _(u'(deleted)')
        feed.add(title, topic.question.rendered_text, content_type='html',
                 author=topic.author.display_name,
                 url=url_for(topic, _external=True),
                 id=topic.guid, updated=topic.last_change, published=topic.date)

    return feed.get_response()

    def test_weakref_with_cycles_o2o(self):
        s = sessionmaker()()
        mapper(User, users, properties={
            "address":relation(Address, backref="user", uselist=False)
        })
        mapper(Address, addresses)
        s.add(User(name="ed", address=Address(email_address="ed1")))
        s.commit()

        user = s.query(User).options(eagerload(User.address)).one()
        user.address.user
        eq_(user, User(name="ed", address=Address(email_address="ed1")))

        del user
        gc.collect()
        assert len(s.identity_map) == 0

        user = s.query(User).options(eagerload(User.address)).one()
        user.address.email_address='ed2'
        user.address.user # lazyload

        del user
        gc.collect()
        assert len(s.identity_map) == 2
        
        s.commit()
        user = s.query(User).options(eagerload(User.address)).one()
        eq_(user, User(name="ed", address=Address(email_address="ed2")))

 def get_game(cls, game):
     return DBSession.query(cls)\
                     .options(eagerload('player'),
                              eagerload('team'))\
                     .with_polymorphic('*')\
                     .filter(cls.game_id==game.id)\
                     .all()

    def schedule(self, schedule_id=None, eager=True):
        """
        Get educator's full week schedule.

        :param schedule_id: Optional schedule's id to work on.
        :type schedule_id: :class:`int`

        :param eager: Whether or not to eagerly load lesson's group
                      and group's year.
        :type eager: :class:`bool`

        """
        q = Lesson.query_current(schedule_id)
        q = q.filter(Lesson.teacher_id == self.id)

        if eager:
            q = q.options(eagerload('group'), eagerload('group.year'))

        days = {}
        for x in range(0,5):
            days[x] = []
        for lesson in q.all():
            days[lesson.day].append(lesson)
        schedule = []
        for day in days.values():
            schedule.append(self._process_schedule(day))
        return schedule

def handleSchedule(object, event):
    """ move scheduled items from to be scheduled state to schedule when draft 
    agenda is finalised and vice versa
    """
    session = Session()
    s = removeSecurityProxy(object)
    sitting = session.query(domain.GroupSitting
        ).options(eagerload("group_sitting_type"), eagerload("item_schedule")
        ).get(s.group_sitting_id)
    schedulings = map(removeSecurityProxy, sitting.item_schedule)
    if sitting.status == "draft_agenda":
        for sch in schedulings:
            if sch.item.type != "heading":
                wfc = IWorkflowController(sch.item)
                wf = wfc.workflow
                next_state = get_states(sch.item.type, tagged=["tobescheduled"])
                for transition_id in wfc.getSystemTransitionIds():
                    t = wf.get_transition(transition_id)
                    if t.destination in next_state:
                        #TODO find out why firetransition fails for reschedule even 
                        #when the user has requisite permissions
                        wfc.fireTransition(transition_id, check_security=False)
                        break
    elif sitting.status == "published_agenda":
        for sch in schedulings:
            if sch.item.type != "heading":
                wfc = IWorkflowController(sch.item)
                wf = wfc.workflow
                next_state = get_states(sch.item.type, tagged=["scheduled"])
                for transition_id in wfc.getSystemTransitionIds():
                    t = wf.get_transition(transition_id)
                    if t.destination in next_state:
                        wfc.fireTransition(transition_id, check_security=False)
                        break

 def get_by_users(self, users):
     user_uids = [user.uid for user in users]
     q = VoteIdeaData.query \
         .filter(VoteIdeaData.user_uid.in_(user_uids)) \
         .options(eagerload(VoteIdeaData.idea),
                  eagerload(VoteIdeaData.user))
     return self._get_by_query(q)

    def lesson(self, day, order, schedule_id=None, eager=True):
        """
        Get scheduled lesson for given day and order.

        :param day: The day
        :type day: :class:`int`

        :param order: The lesson order
        :type order: :class:`int`

        :param schedule_id: Schedule to work on
        :type schedule_id: :class:`int`

        :param eager: Whether or not eager load lesson's
                      group and group's year.
        :type eager: :class`bool`

        """
        q = Lesson.query_current(schedule_id)
        q = q.filter(Lesson.day == day).\
              filter(Lesson.order == order).\
              filter(Lesson.teacher_id == self.id)

        if eager:
            q = q.options(eagerload('group'), eagerload('group.year'))

        return q.all()

 def dnps(cls, game):
     return DBSession.query(cls)\
                     .options(eagerload('player'),
                              eagerload('player.positions'),
                              eagerload('team'),
                              eagerload('team.league'))\
                    .join(GamePlayerDNP)\
                    .filter(cls.game==game)

 def current_players(cls, league):
     return DBSession.query(cls)\
                     .join(Person, PersonName)\
                     .options(eagerload('person.height_weight'),
                              eagerload('person.college'))\
                     .join(TeamPlayer)\
                     .filter(TeamPlayer.current==True, 
                             Player.league==league)\
                     .order_by(PersonName.full_name)

    def list(self, balance_uid):
        model = request.environ['sqlalchemy.model']
        db = request.environ['sqlalchemy.session']

        balance = db.query(model.Balance).filter_by(uid = balance_uid).first()
        if not balance or not balance.can_see_balance(h.authenticated_user().uid):
            return { "failure": Messages.permissionDenied() }

        qr = balance.changes \
            .options(eagerload('expense_category'), eagerload('income_category'), eagerload('tags')) \
            .order_by(model.BalanceChange.occurred_on)

        (start_date, end_date) = self._get_dates()

        if start_date:
            qr = qr.filter(model.BalanceChange.occurred_on >= start_date)

        if end_date:
            qr = qr.filter(model.BalanceChange.occurred_on <= end_date)

        balance_changes = qr.all()

        total = len(balance_changes)
        try:
            page_nr = int(request.params['page_nr'])
        except:
            page_nr = 1

        try:
            items_per_page = int(request.params['items_per_page'])
        except:
            items_per_page = 15
        
        subset = Page(balance_changes, item_count=total, current_page=page_nr, items_per_page=items_per_page)
        return {
            "summary" : {
                "total" : self._total(balance_uid),
                "date_range" : {
                    "expenses" : self._expenses_for_date_range(balance_uid, start_date, end_date),
                    "incomes" : self._incomes_for_date_range(balance_uid, start_date, end_date),
                },
            },
            "changes": { 
            "totalItems" : total,
            "itemsFound" : len(subset),
            "items" : [{ 
                "uid" : item.uid, 
                "category_uid" : item.is_income and item.income_category_uid or item.expense_category_uid, 
                "category" : (item.is_income and item.income_category or item.expense_category).name, 
                "amount" : Decimal(item.amount), 
                "description" : item.description, 
                "occurred_on": str(item.occurred_on), 
                "is_income": item.is_income,
                "tags_as_string": item.tags_as_string()} for item in subset ]
            }
        }

 def player_stats(cls, sport, league, game_type, player):
     stats = DBSession.query(cls)\
                      .options(eagerload('game'), 
                               eagerload('game.season'))\
                      .with_polymorphic('*')\
                      .join(Game)\
                      .filter(cls.player==player,
                              cls.stat_type!='offense',
                              Game.game_type==game_type)
     return stats

 def rankings(self):
     session = DBSession()
     rankings = session.query(FantasyRanking)\
                       .options(eagerload('rankings'),
                                eagerload('rankings.player'),
                                eagerload('rankings.player.positions'))
     self.data.update({
         'rankings': rankings
     })
     return self.data

 def get_sittings(self):
     formatter = self.request.locale.dates.getFormatter("date", "full")
     session = Session()
     query = (
         session.query(domain.GroupSitting)
         .filter(
             sql.and_(
                 schema.sittings.c.status.in_(get_states("groupsitting", tagged=["public"])),
                 sql.between(schema.sittings.c.start_date, self.start_date, self.end_date),
             )
         )
         .order_by(schema.sittings.c.start_date)
         .options(
             eagerload("group"),
             # eagerload('sitting_type'),
             eagerload("item_schedule"),
             eagerload("item_schedule.item"),
         )
     )
     sittings = query.all()
     day = u""
     day_list = []
     s_dict = {}
     for sitting in sittings:
         sday = formatter.format(sitting.start_date)
         if sday != day:
             s_list = []
             day = sday
             if s_dict:
                 day_list.append(s_dict)
             s_dict = {}
         if sitting.group.type == "parliament":
             _url = url.set_url_context("/business/sittings/obj-%i" % (sitting.sitting_id))
         elif sitting.group.type == "committee":
             _url = url.set_url_context(
                 "/business/committees/obj-%i/sittings/obj-%i" % (sitting.group.group_id, sitting.sitting_id)
             )
         else:
             _url = "#"
         s_list.append(
             {
                 "start": sitting.start_date.strftime("%H:%M"),
                 "end": sitting.end_date.strftime("%H:%M"),
                 "type": sitting.group.type,
                 "name": sitting.group.short_name,
                 "url": _url,
                 "items": self.get_sitting_items(sitting),
             }
         )
         s_dict["day"] = day
         s_dict["sittings"] = s_list
     else:
         if s_dict:
             day_list.append(s_dict)
     return day_list

 def team_schedule(cls, team, game_type, year):
     return DBSession.query(cls)\
                     .options(eagerload('away_team'),
                             eagerload('home_team'),
                             eagerload('home_scores'),
                             eagerload('away_scores'),)\
                     .filter(or_(cls.home_team==team, 
                                cls.away_team==team),
                            cls.game_type==game_type,
                            cls.season.has(year=year))\
                     .order_by(Game.game_time)

 def _subcontainer_id_map(self, id_list):
     """Return an id to model map of all subcontainer-type models in the id_list."""
     if not id_list:
         return []
     component_class = self.subcontainer_class
     query = (self._session().query(component_class)
         .filter(component_class.id.in_(id_list))
         .options(eagerload('collection'))
         .options(eagerload('tags'))
         .options(eagerload('annotations')))
     return dict((row.id, row) for row in query.all())

 def _contained_id_map(self, id_list):
     """Return an id to model map of all contained-type models in the id_list."""
     if not id_list:
         return []
     component_class = self.contained_class
     query = (self._session().query(component_class)
         .filter(component_class.id.in_(id_list))
         .options(undefer('_metadata'))
         .options(eagerload('dataset.actions'))
         .options(eagerload('tags'))
         .options(eagerload('annotations')))
     return dict((row.id, row) for row in query.all())

 def get_date(cls, league, date):
     day = datetime.timedelta(days=1)
     return DBSession.query(cls)\
                    .options(eagerload('home_scores'),
                             eagerload('away_scores'),
                             eagerload('home_team'),
                             eagerload('away_team'),
                             eagerload('league'))\
                    .filter(cls.league==league,
                            cls.game_time.between(date, date+day))\
                    .order_by(cls.game_time)\
                    .all()

 def get_sittings(self):
     formatter = self.request.locale.dates.getFormatter('date', 'full') 
     session = Session()
     query = session.query(domain.GroupSitting).filter(
         sql.and_(
             schema.group_sittings.c.status.in_(get_states('groupsitting',
                 tagged=['public'])
             ),
             sql.between(
                 schema.group_sittings.c.start_date,
                 self.start_date,
                 self.end_date))).order_by(
                     schema.group_sittings.c.start_date).options(
                     eagerload('group'), 
                     #eagerload('sitting_type'),
                     eagerload('item_schedule'), 
                     eagerload('item_schedule.item')
         )
     sittings = query.all()
     day = u''
     day_list = []
     s_dict = {}
     for sitting in sittings:
         sday = formatter.format(sitting.start_date)
         if sday != day:
             s_list = []
             day = sday
             if s_dict:
                 day_list.append(s_dict)
             s_dict = {}
         if sitting.group.type == 'parliament':
             _url = url.set_url_context('/business/sittings/obj-%i' % (
                  sitting.group_sitting_id))
         elif sitting.group.type == 'committee':
             _url = url.set_url_context(
                 '/business/committees/obj-%i/sittings/obj-%i'
                 % (sitting.group.group_id, sitting.group_sitting_id))
         else:
             _url ='#'
         s_list.append({
             'start': sitting.start_date.strftime("%H:%M"),
             'end' : sitting.end_date.strftime("%H:%M"),
             'type' : sitting.group.type,
             'name' : sitting.group.short_name,
             'url' : _url, 
             'items' : self.get_sitting_items(sitting),
             })
         s_dict['day'] = day
         s_dict['sittings'] = s_list
     else:
         if s_dict:
             day_list.append(s_dict)
     return day_list

    def export_to_cdn(self, date_from, date_to):
        """Export invoices to OPTIMA"""
        doc = Document()
        from pytis.model import meta
        buffer = StringIO.StringIO()
        
        invoices = Invoice.query.options(eagerload('elements')).filter(Invoice.issueDate.between(date_from , date_to)).order_by(Invoice.series_number).all()
        corrects = InvoiceCorrect.query.options(eagerload('positions')).filter(InvoiceCorrect.correct_date.between(date_from, date_to)).order_by(InvoiceCorrect.series_number).all()

        root = doc.createElement('ROOT')
        root.setAttribute('xmlns', 'http://www.cdn.com.pl/optima/offline')
        doc.appendChild(root)

        companiesElement = doc.createElement('KONTRAHENCI')
        companiesElement.appendChild(self._add_element(doc, 'WERSJA', '2.00'))
        companiesElement.appendChild(self._add_element(doc, 'BAZA_ZRD_ID', 'SPRZ'))
        companiesElement.appendChild(self._add_element(doc, 'BAZA_DOC_ID', 'SPRZ'))
        root.appendChild(companiesElement)

        companies = []
        for invoice in invoices:
            """fetch sets of companies"""
            if invoice.company not in companies:
                companies.append(invoice.company)

        for correct in corrects:
            """fetch sets of companies"""
            if correct.company not in companies:
                companies.append(correct.company)

        for company in companies:            
            companiesElement.appendChild(self._add_company(doc, company))
        root.appendChild(companiesElement)

        invoicesElement = doc.createElement('REJESTRY_SPRZEDAZY_VAT')
        invoicesElement.appendChild(self._add_element(doc, 'WERSJA', '2.00'))
        invoicesElement.appendChild(self._add_element(doc, 'BAZA_ZRD_ID', 'SPRZ'))
        invoicesElement.appendChild(self._add_element(doc, 'BAZA_DOC_ID', 'SPRZ'))
        for invoice in invoices:
            invoicesElement.appendChild(self._add_invoice(doc, invoice))

            if not invoice.is_exported:
                invoice.mark_as_exported()
        meta.Session.commit()
        
        for correct in corrects:
            invoicesElement.appendChild(self._add_correct(doc, correct))

        root.appendChild(invoicesElement)

        buffer.write(doc.toprettyxml(indent='', newl=''))        
        return buffer