def get_ranking(self):
     """Gets the teams and Totalpoints in right order"""
     session = named_scoped_session('footballchallenge')
     
     league = session.query(League).filter(League.id_ == self.league_id).one()
     teams = league.teams
     team_ids = [team.id_ for team in teams]
     ranking = session.query(Teamstatistics).filter(Teamstatistics.team_id.in_(team_ids)).order_by(desc(Teamstatistics.game_id)).all()
     teams_in_ranking = {}
     for rank in ranking:
         teams_in_ranking[rank.team_id] = teams_in_ranking.get(rank.team_id, 0) + rank.points
     clean_ranking = sorted(teams_in_ranking.items(), key=lambda k: k[1], reverse=True)
     correct_ranks = []
     rank = 1
     last_rank = 1
     for index, team in enumerate(clean_ranking):
         if index >= 1:
             if clean_ranking[index-1][1] == team[1]:
                 correct_ranks.append([last_rank, team])
                 rank += 1
             else:
                 correct_ranks.append([rank, team])
                 last_rank = rank
                 rank += 1
         else:
             correct_ranks.append([last_rank, team])
             rank += 1
     return correct_ranks
         
     
     return clean_ranking

def calculate_team_points(game):
    """recalculates the team points after a game"""
    session = named_scoped_session('footballchallenge')
    teams = session.query(Team).filter_by(event_id = game.events_id).filter_by(valid = True).all()
    session.query(Teamstatistics).filter(Teamstatistics.game_id == game.id_).delete()
    points = {}
    for team in teams:
        for player in game.players:
            for teams_players in player.teams:
                if teams_players.team == team:
                    playerstats = session.query(Playerstatistics).filter_by(
                        player_id = player.id_).filter_by(game_id = game.id_).one()
                    team_player = session.query(Teams_Players).filter_by(
                        team_id = team.id_).filter_by(
                        player_id = player.id_).one()
                    if not points.get(team.id_, None):
                        points[team.id_] = 0
                    if not team_player.is_starter:
                        points[team.id_]+= playerstats.points/2
                    else:
                        points[team.id_]+= playerstats.points
        if not points.get(team.id_):
            stats=Teamstatistics(team.id_, game.id_, 0)
            session.add(stats)
            
    for key, value in points.items():
        old_entry = session.query(Teamstatistics).filter_by(team_id=key).filter_by(game_id=game.id_).all()
        if len(old_entry):
            old_entry[0].points = value
        else:
            stats=Teamstatistics(key, game.id_, value)
            session.add(stats)
    game.calculated = True

 def get_userimg(self, team_id):
     session = named_scoped_session('footballchallenge')
     team = session.query(Team).filter(Team.id_ == team_id).one()
     portal_membership = getToolByName(self.context, 'portal_membership')
     userid = team.user_id
     portrait = portal_membership.getPersonalPortrait(userid)
     return portrait.tag()

    def updateWidgets(self):
        super(EditTeamForm, self).updateWidgets()
        membershiptool = getToolByName(self.context, 'portal_membership')
        userid = membershiptool.getAuthenticatedMember().getId()
        session = named_scoped_session('footballchallenge')
        event_id = session.query(Event).filter(Event.deadline > datetime.now()).one().id_
        if not len(session.query(Team).filter_by(user_id=userid).filter_by(event_id=event_id).all()):
            super(EditTeamForm, self).updateWidgets()
            return

        team = session.query(Team).filter_by(user_id=userid).filter_by(event_id=event_id).one()
        starters = session.query(Teams_Players).filter_by(team_id=team.id_).filter_by(is_starter=True).all()
        substitutes = session.query(Teams_Players).filter_by(team_id=team.id_).filter_by(is_starter=False).all()

        self.widgets['name'].value = team.name
        count = {'defender':1, 'midfield':1, 'striker':1}    
        for starter in starters:
            if not starter.player.position=="keeper":
                self.widgets[(starter.player.position + str(count[starter.player.position])).encode('utf-8')].value = str(starter.player.id_),
                count[starter.player.position] += 1
            else:
                self.widgets["keeper"].value = str(starter.player.id_),
        
        
        count = {'defender':1, 'midfield':1, 'striker':1}
        for substitute in substitutes:
            if not substitute.player.position=="keeper":
                self.widgets["substitute_"+(substitute.player.position + str(count[substitute.player.position])).encode('utf-8')].value = str(substitute.player.id_),
                count[substitute.player.position] += 1
            else:
                self.widgets["substitute_keeper"].value = str(substitute.player.id_),

 def handleImport(self, action):
     data, errors = self.extractData()
     if len(errors) == 0:
         urls = data['urls']
         event = data['event']
         session = named_scoped_session('footballchallenge')
         import_team(urls, session, event)
         return self.request.RESPONSE.redirect(self.context.absolute_url())

 def __call__(self,context):
     session = named_scoped_session('footballchallenge')
     terms=[]
     nations = session.query(Nation).all()
     for nation in nations:
         terms.append(vocabulary.SimpleTerm(nation.id_, nation.id_,
                                            nation.name))
     return vocabulary.SimpleVocabulary(terms)

 def updateWidgets(self):
     super(AssignUserForm, self).updateWidgets()
     session = named_scoped_session('footballchallenge')
     league = session.query(League).filter(League.id_ == self.league_id).one()
     if league.teams:
         user_ids = [team.user_id for team in league.teams]
         self.widgets['teams'].value = user_ids
         self.widgets['teams'].update()

 def __call__(self, context):
     """Returns the Events as SimpleVocabulary to use it as Source for fields"""
     session = named_scoped_session('footballchallenge')
     terms=[]
     events = session.query(Event).all()
     for event in events:
         terms.append(vocabulary.SimpleTerm(event.id_, event.id_, event.name))
     return vocabulary.SimpleVocabulary(terms)

 def get_link(self, team_id):
     session = named_scoped_session('footballchallenge')
     team = session.query(Team).filter(Team.id_ == team_id).one()
     portal_url = getToolByName(self.context, 'portal_url')
     portal = portal_url.getPortalObject()
     url = portal.absolute_url()
     link = '<a href="%s/team_overview/%s">%s</a>' % (url, team.id_, team.name)
     return link

 def __call__(self):
     session = named_scoped_session('footballchallenge')
     if not session.query(Event).filter(Event.deadline > datetime.now()).all():
         msg = _(u'label_not_edit', default="The Event has started. You can't edit your Team now.")
         IStatusMessage(self.request).addStatusMessage(
             msg, type='error')
         return self.request.response.redirect(self.context.absolute_url())
     else:
         return super(EditTeamForm, self).__call__()

def plominoSqlSync(session, plominoDocument, **table_infos):
    """
    table_infos = dict(schema='<schema_table>')
    """

    if isinstance(session, basestring):
        session = named_scoped_session(session)

    engine = session.get_bind()

    db = SqlSoup(engine, session=session)

    table_name = plominoDocument.Form
    main_table = db.entity(table_name, **table_infos)

    values = dict()
    plominoItems = plominoDocument.getItems()
    for column in main_table.c:
        if column.key == u"id":
            continue
        if column.key in plominoItems and (plominoDocument.getItem(column.key, None) != None):
            values[column.key] = plominoDocument.getItem(column.key)

    plominoDatabase = plominoDocument.getParentDatabase()
    values[u"modified_by"] = plominoDatabase.getCurrentUser().id
    values[u"last_update"] = plominoDocument.plomino_modification_time.asdatetime()

    if plominoDocument.isNewDocument():
        values[u"plominoId"] = plominoDocument.id
        main_table.insert(**values)
    else:
        if not main_table.filter(main_table.plominoId == plominoDocument.id).update(values):
            values[u"plominoId"] = plominoDocument.id
            main_table.insert(**values)

    plominoForm = plominoDatabase.getForm(plominoDocument.Form)
    plominoFields = plominoForm.getFormFields(includesubforms=True, applyhidewhen=True)

    for field in plominoFields:
        if field.getFieldType() in ("DATAGRID",) and (plominoDocument.getItem(field.id) != None):
            gridItem = plominoDocument.getItem(field.id)
            grid_table_name = field.id  # oppure field.getSettings(key='associated_form')
            try:
                grid_table = db.entity(grid_table_name, **table_infos)
            except NoSuchTableError, err:
                pass
            else:
                grid_table.filter(grid_table.parentId == plominoDocument.id).delete()

                vals = dict()
                for record in gridItem:
                    field_mapping = field.getSettings(key="field_mapping").split(",")
                    for idx, key in enumerate(field_mapping):
                        if record[idx] != None:
                            vals[key] = record[idx]
                    vals[u"parentId"] = plominoDocument.id
                    grid_table.insert(**vals)

 def __call__(self, context):
     """a Proxy function which returns keeper term"""
     session = named_scoped_session("footballchallenge")
     event_id = session.query(Event).filter(Event.deadline > datetime.now()).one().id_
     leagues = session.query(League).filter(League.event_id == event_id).all()
     terms = []
     for league in leagues:
         terms.append(vocabulary.SimpleTerm(league.id_, league.id_, league.name))
     return vocabulary.SimpleVocabulary(terms)

 def __call__(self):
     session = named_scoped_session('footballchallenge')
     games = session.query(Game).filter(Game.calculated == False).all()
     for game in games:
         calculate_player_points(game)
         # calculate_team_points(game)
         game.calculated = True
     transaction.commit()
     return self.request.response.redirect(self.context.absolute_url())
     

    def session(self):
        if not self._session:
            xmlconfig.xmlconfig(StringIO("""
             <configure xmlns="http://namespaces.zope.org/db">
               <session name="footballchallenge" engine="footballchallenge.db" />
             </configure>"""))
            
            self._session = named_scoped_session("footballchallenge")

        return self._session

    def __call__(self):
        session = named_scoped_session('footballchallenge')
        player = session.query(Player).filter(
            Player.id_ == self.player_id).first()
        if player is None:
            raise NotFound(self, self.player_id, self.request)

        self.request.response.setHeader('Content-Type', 'image/jpeg')
        self.request.response.setHeader('Content-Length', len(player.image))
        self.request.response.write(player.image)

    def get_tabs(self):
        tabs = []
        session = named_scoped_session("footballchallenge")
        event_id = session.query(Event).all()[-1].id_
        leagues = session.query(League).filter(League.event_id == event_id).all()

        for league in leagues:
            self.register_league_tab(league)
            tabs.append({'id': _('league-%i' % league.id_, default=league.name),
                         'class': ''})

        return tabs

    def handleSave(self, action):
        """Handles the Edit action of the form"""
        data, errors = self.extractData()
        #If all validators are ok: proceed
        if len(errors) == 0:
            #get the session from z3c.sacofig
            session = named_scoped_session('footballchallenge')
            event_id = session.query(Event).filter(
                Event.deadline > datetime.now()).one().id_
            #get the userid we need it to find the right team
            membershiptool = getToolByName(self.context, 'portal_membership')
            userid = membershiptool.getAuthenticatedMember().getId()
            if not session.query(Team).filter_by(user_id=userid).filter_by(event_id=event_id).all():
                #create the team if it doesn't exist.
                team = Team(userid, event_id, name=data['name'])
                session.add(team)                
            else:
                team = session.query(Team).filter_by(user_id=userid).filter_by(event_id=event_id).one()
            session.query(Teams_Players).filter(Teams_Players.team_id==\
            team.id_).delete()
            team.name = data['name']
            nations = []
            all_nations = []
            for k, v in data.items():
                if not k == 'name' and v:
                    player = session.query(Player).filter(Player.id_ == v).one()
                    #Create relationsship between Team and Player
                    if bool(not 'substitute' in k):
                        if not player.nation_id in nations:
                            nations.append(player.nation_id)
                        if not player.nation_id in all_nations:
                            all_nations.append(player.nation_id)
                    else:
                        if not player.nation_id in all_nations:
                            all_nations.append(player.nation_id)

                    team.players.append(Teams_Players(team.id_, player,
                                        bool(not 'substitute' in k)))

            if len(nations) >= 6 and len(all_nations) >=12 and len(team.players) == 22:
                team.valid = True
            else:
                msg = _(u'label_not_valid', default=u'Your Team is not valid and will not receive any points')
                IStatusMessage(self.request).addStatusMessage(
                    msg, type='warning')
                team.valid = False

            msg = _(u'label_changes_saved', default=u'Your changes are saved successfully')
            IStatusMessage(self.request).addStatusMessage(
                msg, type='info')

            return self.request.RESPONSE.redirect(self.context.absolute_url()+'/team_overview')

 def handleImport(self, action):
     data, errors = self.extractData()
     if len(errors) == 0:
         name = data['name']
         event = data['event']
         session = named_scoped_session('footballchallenge')
         league = League(name, event)
         session.add(league)
         portal_actions = getToolByName(self.context, 'portal_actions')
         transaction.commit()
         msg = _(u'league_created', default=u'League created successfully')
         IStatusMessage(self.request).addStatusMessage(
             msg, type='information')
         return self.request.RESPONSE.redirect(self.context.absolute_url()+'/ranking/'+str(league.id_))

 def nations(self):
     context = aq_inner(self.context)
     base_url = context.absolute_url()
     session = named_scoped_session('footballchallenge')
     results = []
     for nation in session.query(Nation).order_by(Nation.name).all():
         info = dict(
             title=nation.name,
             url='%s/%s' % (base_url.rstrip('/')+'/nation', nation.id_),
             coach=nation.coach,
             participations=nation.participations,
             rank=nation.fifa_rank,
         )
         results.append(info)
     return results

 def update(self):
     session = named_scoped_session('footballchallenge')
     last_game = session.query(Game).filter(Game.calculated == True).order_by(desc(Game.date)).first()
     self.ranking = None
     if last_game:
         round_ = last_game.round_
         gameids = session.query(Game.id_).filter_by(calculated = True).filter_by(round_ = round_).all()
         teamstats = session.query(Teamstatistics).filter(Teamstatistics.game_id.in_(gameids)).all()
         teams = {}
         for teamstat in teamstats:
             if not teams.get(teamstat.team_id):
                 teams[teamstat.team_id] = {'team': teamstat.team, 'points':teamstat.points}
             else:
                 teams[teamstat.team_id]['points'] += teamstat.points
     
         self.ranking = sorted(teams.iteritems(), key=lambda (k,v): itemgetter(1)(v))