def execute(self, request, user):
     
     planet, galaxy = (user.planet, user.planet.galaxy,) if user.planet else (Planet(), Galaxy(),)
     
     planets = session.query(Planet).filter(Planet.active == True)
     galaxies = session.query(Galaxy).filter(Galaxy.active == True)
     alliances = session.query(Alliance).filter(Alliance.active == True)
     
     dup = lambda l,o,c=True: l+[o] if o in session and c and o not in l else l
     
     return render("index.tpl", request,
                  topplanets = dup(planets.order_by(asc(Planet.score_rank))[:20], 
                                   planet),
              roidingplanets = dup(planets.filter(Planet.size_growth > 0).order_by(desc(Planet.size_growth))[:5],
                                   planet, planet.size_growth > 0),
               roidedplanets = dup(planets.filter(Planet.size_growth < 0).order_by(asc(Planet.size_growth))[:5],
                                   planet, planet.size_growth < 0),
                   xpplanets = dup(planets.filter(Planet.xp_growth > 0).order_by(desc(Planet.xp_growth))[:5],
                                   planet, planet.xp_growth > 0),
               bashedplanets = dup(planets.filter(Planet.value_growth < 0).order_by(asc(Planet.value_growth))[:5],
                                   planet, planet.value_growth < 0),
             
                 topgalaxies = dup(galaxies.order_by(asc(Galaxy.score_rank))[:10],
                                   galaxy),
             roidinggalaxies = dup(galaxies.filter(Galaxy.size_growth > 0).order_by(desc(Galaxy.size_growth))[:5],
                                   galaxy, galaxy.size_growth > 0),
              roidedgalaxies = dup(galaxies.filter(Galaxy.size_growth < 0).order_by(asc(Galaxy.size_growth))[:5],
                                   galaxy, galaxy.size_growth < 0),
                  xpgalaxies = dup(galaxies.filter(Galaxy.xp_growth > 0).order_by(desc(Galaxy.xp_growth))[:5],
                                   galaxy, galaxy.xp_growth > 0),
              bashedgalaxies = dup(galaxies.filter(Galaxy.value_growth < 0).order_by(asc(Galaxy.value_growth))[:5],
                                   galaxy, galaxy.value_growth < 0),
             
                topalliances =     alliances.order_by(asc(Alliance.score_rank))[:8],
                         )

def organizations_and_counters():
    '''Query organizations with their counters'''
    query = DB.query(Group,
        func.count(distinct(Package.id)).label('nb_datasets'),
        func.count(distinct(Member.id)).label('nb_members')
    )
    query = query.outerjoin(CertifiedPublicService)
    query = query.outerjoin(Package, and_(
        Group.id == Package.owner_org,
        ~Package.private,
        Package.state == 'active',
    ))
    query = query.outerjoin(Member, and_(
        Member.group_id == Group.id,
        Member.state == 'active',
        Member.table_name == 'user'
    ))
    query = query.filter(Group.state == 'active')
    query = query.filter(Group.approval_status == 'approved')
    query = query.filter(Group.is_organization == True)
    query = query.group_by(Group.id, CertifiedPublicService.organization_id)
    query = query.order_by(
        CertifiedPublicService.organization_id == null(),
        desc('nb_datasets'),
        desc('nb_members'),
        Group.title
    )
    query = query.options(orm.joinedload(Group.certified_public_service))
    return query

def user(request, info, session, userid):
    try:
        u = session.query(User).options(joinedload("permissions")).options(joinedload("tokens")).get(int(userid))
    except NoResultFound:
        raise Http404
    sales = (
        session.query(Transline)
        .filter(Transline.user == u)
        .options(joinedload("transaction"))
        .options(joinedload_all("stockref.stockitem.stocktype.unit"))
        .order_by(desc(Transline.time))[:50]
    )
    payments = (
        session.query(Payment)
        .filter(Payment.user == u)
        .options(joinedload("transaction"))
        .options(joinedload("paytype"))
        .order_by(desc(Payment.time))[:50]
    )
    annotations = (
        session.query(StockAnnotation)
        .options(joinedload_all("stockitem.stocktype"))
        .options(joinedload("type"))
        .filter(StockAnnotation.user == u)
        .order_by(desc(StockAnnotation.time))[:50]
    )
    return ("user.html", {"tuser": u, "sales": sales, "payments": payments, "annotations": annotations})

 def execute(self, request, user, sort=None):
     
     levels = [] + User.levels
     if "galmate" in Config.options("Access"):
         levels.append(("Galaxy", Config.getint("Access","galmate"),))
     else:
         levels.append(("Galaxy", 0,))
     
     if sort is not None:
         levels = [("All", 0,),]
     
     order =  {"name"  : (asc(Channel.name),),
               "userlevel" : (desc(Channel.userlevel),),
               "maxlevel" : (desc(Channel.maxlevel),),
               }
     if sort not in order.keys():
         sort = "name"
     order = order.get(sort)
     
     channels = []
     for level in levels:
         Q = session.query(Channel.name, Channel.userlevel, Channel.maxlevel)
         Q = Q.filter(Channel.userlevel >= level[1])
         Q = Q.filter(Channel.userlevel < levels[levels.index(level)-1][1]) if levels.index(level) > 0 else Q
         for o in order:
             Q = Q.order_by(o)
         
         channels.append((level[0], Q.all(),))
     
     return render("channels.tpl", request, accesslist=channels)

def index():
    campaigns = Campaign.query.order_by(desc(Campaign.status_code), desc(Campaign.id)).all()
    calls = (db.session.query(Campaign.id, func.count(Call.id))
            .filter(Call.status == 'completed')
            .join(Call).group_by(Campaign.id))
    return render_template('campaign/list.html',
        campaigns=campaigns, calls=dict(calls.all()))

    def newer_submissions(self):
        class Newer(object):
            '''You may use me like a list'''
            user = []
            team = []

            def __iter__(self):
                for i in self.user + self.team:
                    yield i

            def __len__(self):
                return len(self.user) + len(self.team)

            def __getitem__(self, i):
                return sorted(self.user + self.team, key=lambda s: s.modified, reverse=True)[0]

        newer = Newer()

        newer.user = (Submission.by_assignment_and_user(self.assignment, self.user)
            .filter(Submission.modified > self.modified).order_by(desc(Submission.modified)).all())
        newer.team = []
        if hasattr(self.user, 'teams'):
            for team in self.teams:
                for member in team.members:
                    if member != self.user:
                        newer.team.extend(Submission.by_assignment_and_user(self.assignment, member)
                            .filter(Submission.modified > self.modified).order_by(desc(Submission.modified)).all())
        return newer

    def execute(self, message, alliance=None, race=None, sortby="score"):
        planet = aliased(Planet)
        planet_intel = aliased(Intel)

        Q = session.query(planet.x, planet.y, planet.z, planet.score, planet.value, planet.size, planet.xp, planet.race, planet_intel.nick)
        Q = Q.outerjoin(planet.intel, planet_intel)
        if alliance:
            Q = Q.filter(planet_intel.alliance == alliance)
        if race:
            Q = Q.filter(planet.race == race)
        Q = Q.group_by(planet.x, planet.y, planet.z, planet.score, planet.value, planet.size, planet.xp, planet.race, planet_intel.nick)
        if sortby == "xp":
            Q = Q.order_by(desc(planet.xp))
        elif sortby == "size":
            Q = Q.order_by(desc(planet.size))
        elif sortby == "value":
            Q = Q.order_by(desc(planet.value))
        else:
            Q = Q.order_by(desc(planet.score))
        result = Q.all()
        
        reply = "Top%s planets" % (" "+race if race is not None else "")
        if alliance:
            reply+=" in %s"%(alliance.name,)
        reply+=" by %s:\n"%(sortby)
        prev = []
        i=0
        for x, y, z, score, value, size, xp, race, nick in result[:10]:
            i+=1
            line = "#%s - %s:%s:%s (%s) - Score: %s Value: %s Size: %s XP: %s"%(i,x,y,z,race,score,value,size,xp)
            if nick:
                line = "%s Nick: %s"%(line,nick)
            prev.append(line)
        print(prev)
        message.reply(reply+"\n".join(prev))

    def execute(self, message, alliance=None, race=None, sortby="score"):
        planet = aliased(Planet)
        planet_intel = aliased(Intel)
        

        Q = session.query(planet.x, planet.y, planet.z, planet_intel.nick)
        Q = Q.outerjoin((planet.intel, planet_intel))
        if alliance:
            Q = Q.filter(planet_intel.alliance == alliance)
        if race:
            Q = Q.filter(planet.race == race)
        if sortby == "xp":
            Q = Q.order_by(desc(planet.xp))
        elif sortby == "size":
            Q = Q.order_by(desc(planet.size))
        elif sortby == "value":
            Q = Q.order_by(desc(planet.value))
        else:
            Q = Q.order_by(desc(planet.score))
        result = Q.all()
        
        reply = "Top%s planets" % (" "+race if race is not None else "")
        if alliance:
            reply+=" in %s"%(alliance.name,)
        reply+=" by %s:\n"%(sortby)
        prev = []
        i=0
        for x, y, z, nick in result[:10]:
            i+=1
            line = "#%2s %12s%s"%(i, "["+nick+"] " if nick else "", Planet.load(x,y,z))
            prev.append(line)
        message.reply(reply+"\n".join(prev))

 def execute(self, request, user, x, y, z, fleets=False):
     week = Updates.week_tick()
     
     planet = Planet.load(x,y,z)
     if planet is None:
         return HttpResponseRedirect(reverse("planet_ranks"))
     
     Q = session.query(FleetScan, Planet, Alliance)
     Q = Q.join(FleetScan.target)
     Q = Q.outerjoin(Planet.intel).outerjoin(Intel.alliance)
     Q = Q.filter(FleetScan.owner == planet)
     Q = Q.order_by(desc(FleetScan.landing_tick))
     if not fleets:
         Q = Q.filter(FleetScan.landing_tick >= week)
     outgoing = Q.all()
     
     Q = session.query(FleetScan, Planet, Alliance)
     Q = Q.join(FleetScan.owner)
     Q = Q.outerjoin(Planet.intel).outerjoin(Intel.alliance)
     Q = Q.filter(FleetScan.target == planet)
     Q = Q.order_by(desc(FleetScan.landing_tick))
     if not fleets:
         Q = Q.filter(FleetScan.landing_tick >= week)
     incoming = Q.all()
     
     scan = planet.scan("A") or planet.scan("U")
     
     return render("iplanet.tpl", request, planet=planet, scan=scan, outgoing=outgoing, incoming=incoming)

 def execute(self, request, user, sort=None):
     
     levels = [] + User.levels
     
     if sort is not None:
         levels = [("All member", levels[-1][1],),]
     
     order =  {"name"  : (asc(User.name),),
               "sponsor" : (asc(User.sponsor),),
               "access" : (desc(User.access),desc(User.carebears),asc(User.name),),
               "carebears" : (desc(User.carebears),),
               "planet" : (asc(Planet.x),asc(Planet.y),asc(Planet.z),),
               "defage" : (asc(User.fleetupdated),),
               }
     if sort not in order.keys():
         sort = "name"
     order = order.get(sort)
     
     members = []
     for level in levels:
         Q = session.query(User.name, User.alias, User.sponsor, User.access, User.carebears, Planet, User.fleetupdated,
                           User.phone, User.pubphone, User._smsmode,  or_(User.id == user.id, User.id.in_(session.query(PhoneFriend.user_id).filter_by(friend=user))))
         Q = Q.outerjoin(User.planet)
         Q = Q.filter(User.active == True)
         Q = Q.filter(User.access >= level[1])
         Q = Q.filter(User.access < levels[levels.index(level)-1][1]) if levels.index(level) > 0 else Q
         for o in order:
             Q = Q.order_by(o)
         
         members.append((level[0], Q.all(),))
     
     return render("members.tpl", request, accesslist=members)

def organizations_and_counters():
    '''Query organizations with their counters'''
    memberships = aliased(model.Member)

    query = DB.query(model.Group,
        func.count(distinct(model.Package.id)).label('nb_datasets'),
        func.count(distinct(memberships.id)).label('nb_members')
    )
    query = query.outerjoin(CertifiedPublicService)
    query = query.outerjoin(model.Package, and_(
        model.Group.id == model.Package.owner_org,
        ~model.Package.private,
        model.Package.state == 'active',
    ))
    query = query.outerjoin(memberships, and_(
        memberships.group_id == model.Group.id,
        memberships.state == 'active',
        memberships.table_name == 'user'
    ))
    query = query.filter(model.Group.state == 'active')
    query = query.filter(model.Group.approval_status == 'approved')
    query = query.filter(model.Group.is_organization == True)
    query = query.group_by(model.Group.id, CertifiedPublicService.organization_id)
    query = query.order_by(
        CertifiedPublicService.organization_id == null(),
        desc('nb_datasets'),
        desc('nb_members'),
        model.Group.title
    )
    return query

    def load_plasmids(self, tsession = None, engine = None):

        engine = engine or self.engine
        tsession = tsession or self.tsession

        for p in tsession.query(Plasmid).order_by(desc(Plasmid.date), desc(Plasmid.creator_entry_number)): # todo: parameterize sorting
            k = (p.creator, p.creator_entry_number)
            self.plasmid_order.append(k)
            self.plasmids[k] = p

 def execute(self, request, user, x, y, z, h=False, hs=False, ticks=None):
     planet = Planet.load(x,y,z)
     if planet is None:
         return HttpResponseRedirect(reverse("planet_ranks"))
     
     ticks = int(ticks or 0) if (h or hs) else 12
     
     if not hs:
         sizediffvalue = PlanetHistory.rdiff * PA.getint("numbers", "roid_value")
         valuediffwsizevalue = PlanetHistory.vdiff - sizediffvalue
         resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value")
         shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value")
         xpvalue = PlanetHistory.xdiff * PA.getint("numbers", "xp_value")
         Q = session.query(PlanetHistory,
                             sizediffvalue,
                             valuediffwsizevalue,
                             resvalue, shipvalue,
                             xpvalue,
                             )
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         history = Q[:ticks] if ticks else Q.all()
     else:
         history = None
     
     if not (h or hs):
         landings = session.query(PlanetLandings.hour, count()).filter(PlanetLandings.planet==planet).group_by(PlanetLandings.hour).all()
         landed = session.query(PlanetLandedOn.hour, count()).filter(PlanetLandedOn.planet==planet).group_by(PlanetLandedOn.hour).all()
         vdrops = session.query(PlanetValueDrops.hour, count()).filter(PlanetValueDrops.planet==planet).group_by(PlanetValueDrops.hour).all()
         idles = session.query(PlanetIdles.hour, count()).filter(PlanetIdles.planet==planet).group_by(PlanetIdles.hour).all()
         hourstats = {
                         'landings' : dict(landings), 'landingsT' : sum([c for hour,c in landings]),
                         'landed'   : dict(landed),   'landedT'   : sum([c for hour,c in landed]),
                         'vdrops'   : dict(vdrops),   'vdropsT'   : sum([c for hour,c in vdrops]),
                         'idles'    : dict(idles),    'idlesT'    : sum([c for hour,c in idles]),
                         }
     else:
         hourstats = None
     
     if not h:
         Q = session.query(PlanetHistory)
         Q = Q.filter(or_(PlanetHistory.hour == 23, PlanetHistory.tick == Updates.current_tick()))
         Q = Q.filter(PlanetHistory.current == planet)
         Q = Q.order_by(desc(PlanetHistory.tick))
         hsummary = Q.all() if hs else Q[:14]
     else:
         hsummary = None
     
     return render(["planet.tpl",["hplanet.tpl","hsplanet.tpl"][hs]][h or hs],
                     request,
                     planet = planet,
                     history = history,
                     hour = datetime.utcnow().hour, hourstats = hourstats,
                     hsummary = hsummary,
                     ticks = ticks,
                   )

 def list_inactive(self):
     """Returns a list of bans that are currently inactive."""
     return list(
         self.dbsession.query(Ban)
         .filter(
             or_(Ban.active == False, Ban.active_until <= func.now())  # noqa: E712
         )
         .order_by(desc(Ban.active_until), desc(Ban.created_at))
         .all()
     )

 def execute(self, request, user, x, y, h=False, hs=False, ticks=None):
     galaxy = Galaxy.load(x,y)
     if galaxy is None:
         return HttpResponseRedirect(reverse("galaxy_ranks"))
     
     ticks = int(ticks or 0) if (h or hs) else 12
     
     if not (h or hs):
         Q = session.query(Planet, Intel.nick, Alliance.name)
         Q = Q.outerjoin(Planet.intel)
         Q = Q.outerjoin(Intel.alliance)
         Q = Q.filter(Planet.active == True)
         Q = Q.filter(Planet.galaxy == galaxy)
         Q = Q.order_by(asc(Planet.z))
         planets = Q.all()
         exiles = galaxy.exiles[:10]
     else:
         planets, exiles = None, None
     
     if not hs:
         sizediffvalue = GalaxyHistory.rdiff * PA.getint("numbers", "roid_value")
         valuediffwsizevalue = GalaxyHistory.vdiff - sizediffvalue
         resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value")
         shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value")
         xpvalue = GalaxyHistory.xdiff * PA.getint("numbers", "xp_value")
         Q = session.query(GalaxyHistory,
                             sizediffvalue,
                             valuediffwsizevalue,
                             resvalue, shipvalue,
                             xpvalue,
                             )
         Q = Q.filter(GalaxyHistory.current == galaxy)
         Q = Q.order_by(desc(GalaxyHistory.tick))
         history = Q[:ticks] if ticks else Q.all()
     else:
         history = None
     
     if not h:
         Q = session.query(GalaxyHistory)
         Q = Q.filter(or_(GalaxyHistory.hour == 23, GalaxyHistory.tick == Updates.current_tick()))
         Q = Q.filter(GalaxyHistory.current == galaxy)
         Q = Q.order_by(desc(GalaxyHistory.tick))
         hsummary = Q.all() if hs else Q[:14]
     else:
         hsummary = None
     
     return render(["galaxy.tpl",["hgalaxy.tpl","hsgalaxy.tpl"][hs]][h or hs],
                     request,
                     galaxy = galaxy,
                     planets = planets,
                     exiles = exiles,
                     history = history,
                     hsummary = hsummary,
                     ticks = ticks,
                   )

 def execute(self, request, user, page="1", sort="score"):
     page = int(page)
     offset = (page - 1)*50
     order =  {"score" : (asc(Alliance.score_rank),),
               "size"  : (asc(Alliance.size_rank),),
               "ratio" : (desc(Alliance.ratio),),
               "avg_score" : (asc(Alliance.score_avg_rank),),
               "avg_size"  : (asc(Alliance.size_avg_rank),),
               "members"   : (asc(Alliance.members_rank),),
               "score_growth" : (desc(Alliance.score_growth),),
               "size_growth"  : (desc(Alliance.size_growth),),
               "avg_score_growth" : (desc(Alliance.score_avg_growth),),
               "avg_size_growth"  : (desc(Alliance.size_avg_growth),),
               "score_growth_pc" : (desc(Alliance.score_growth_pc),),
               "size_growth_pc"  : (desc(Alliance.size_growth_pc),),
               "avg_score_growth_pc" : (desc(Alliance.score_avg_growth_pc),),
               "avg_size_growth_pc"  : (desc(Alliance.size_avg_growth_pc),),
               } 
     if sort not in order.keys():
         sort = "score"
     order = order.get(sort)
     
     Q = session.query(Alliance)
     Q = Q.filter(Alliance.active == True)
     
     count = Q.count()
     pages = count/50 + int(count%50 > 0)
     pages = range(1, 1+pages)
     
     for o in order:
         Q = Q.order_by(o)
     Q = Q.limit(50).offset(offset)
     return render("alliances.tpl", request, alliances=Q.all(), offset=offset, pages=pages, page=page, sort=sort)

 def last_observation(self):
     session = object_session(self)
     q = session.query(ObservedMac)
     q = q.filter_by(mac_address=self.mac)
     # Group the results into 'any port number but zero' and 'port 0'.
     # This prioritizes any port over the uplink port.
     # Saying that port 0 is an uplink port isn't very elegant, also
     # with real port names it's not even true.
     q = q.order_by(desc(case([(ObservedMac.port == "0", 0)], else_=1)))
     q = q.order_by(desc(ObservedMac.last_seen))
     return q.first()

    def get_feed_entries(self):
        max_count=20
        entries = Session.query(BlogEntry)[:max_count]
        myor = or_()
        for friend in self.friends:
            myor.append(Spotlight.uid == friend.id)

        if len(myor)>0:
            entries.extend(Session.query(Spotlight).filter(
                and_(myor, Spotlight.active==True)).\
                order_by(sql.desc(Spotlight.timestamp))\
                    [:max_count])

        commentor = or_()
        spotlightor = or_()
        for friend in self.friends:
            commentor.append(SpotlightComment.uid == friend.id)
            spotlightor.append(Spotlight.uid == friend.id)


        if len(commentor)>0 and len(spotlightor)>0:
            entries.extend(Session.query(SpotlightComment).\
                    join((Spotlight, SpotlightComment.spotlight)).\
                    filter(and_(
                        SpotlightComment.uid!=session['userid'],
                        or_(Spotlight.uid==session['userid'],
                            and_(commentor, spotlightor)),
                        Spotlight.active == True)).\
                    order_by(sql.desc(SpotlightComment.timestamp))[:max_count])

        entries.extend(Session.query(Recommendation).\
                filter(and_(
                    Recommendation.recommendeefbid == self.fbid,
                    Recommendation.active == True))[:max_count])

        def sort_by_timestamp(x, y):
            if x.timestamp == None:
                if y.timestamp == None:
                    return 0
                return 1
            elif y.timestamp == None:
                return -1
            elif x.timestamp > y.timestamp:
                return -1
            elif x.timestamp == y.timestamp:
                return 0
            else:
                return 1

        entries.sort(sort_by_timestamp)
        return entries[:max_count]

def statistics():
    campaigns = Campaign.query.order_by(desc(Campaign.status_code), desc(Campaign.id)).all()
    today = datetime.today()
    this_month_start = today.replace(day=1)  # first day of the current month

    last_month = this_month_start - timedelta(days=28) # a day in last month
    next_month = today.replace(day=28) + timedelta(days=4)  # a day in next month (for months with 28,29,30,31)

    last_month_start = last_month - timedelta(days=(last_month.day-1))
    this_month_end = next_month - timedelta(days=next_month.day)  # the last day of the current month
    return render_template('admin/statistics.html',
        campaigns=campaigns, timespans=API_TIMESPANS,
        default_start=last_month_start.strftime('%Y/%m/%d'),
        default_end=this_month_end.strftime('%Y/%m/%d'))

 def execute(self, message, user, params):
     
     # Planet
     if len(params.groups()) > 1:
         planet = Planet.load(*params.group(1,3,5))
         if planet is None:
             message.reply("No planet with coords %s:%s:%s found" % params.group(1,3,5))
             return
         
         # List of last 10 scans
         if params.group(6) == "o":
             scans = planet.scans.filter_by(scantype=self.type).order_by(desc(Scan.id))[:10]
             if len(scans) < 1:
                 message.reply("No %s Scans of %s:%s:%s found"%(PA.get(self.type,"name"),planet.x,planet.y,planet.z))
                 return
             prev = []
             for scan in scans:
                 prev.append("(pt%s %s)" % (scan.tick, scan.pa_id,))
             reply = "Last 10 %s Scans on %s:%s:%s "%(PA.get(self.type,"name"),planet.x,planet.y,planet.z) + " ".join(prev)
             message.reply(reply)
             return
         
         # Latest scan
         scan = planet.scan(self.type)
         if scan is None:
             message.reply("No %s Scans of %s:%s:%s found"%(PA.get(self.type,"name"),planet.x,planet.y,planet.z))
             return
         
         # Link to scan
         if params.group(7) == "l":
             reply = "%s on %s:%s:%s " % (PA.get(self.type,"name"),planet.x,planet.y,planet.z,)
             reply+= Config.get("URL","viewscan") % (scan.pa_id,)
             message.reply(reply)
             return
         
         # Display the scan
         message.reply(str(scan))
     
     # ID
     else:
         Q = session.query(Scan)
         Q = Q.filter(Scan.pa_id.ilike("%"+params.group(1)+"%"))
         Q = Q.order_by(desc(Scan.id))
         scan = Q.first()
         if scan is None:
             message.reply("No Scans matching ID '%s'"%(params.group(1),))
             return
         # Display the scan
         message.reply(str(scan))