def test_nested_joins(self):
        task, Task_Type, Joined, prj, task_type, msg = (self.tables.task,
                                                        self.classes.Task_Type,
                                                        self.classes.Joined,
                                                        self.tables.prj,
                                                        self.tables.task_type,
                                                        self.tables.msg)

        # this is testing some subtle column resolution stuff,
        # concerning corresponding_column() being extremely accurate
        # as well as how mapper sets up its column properties

        mapper(Task_Type, task_type)

        tsk_cnt_join = sa.outerjoin(prj, task, task.c.prj_id == prj.c.id)

        j = sa.outerjoin(task, msg, task.c.id == msg.c.task_id)
        jj = sa.select([task.c.id.label('task_id'),
                        sa.func.count(msg.c.id).label('props_cnt')],
                       from_obj=[j],
                       group_by=[task.c.id]).alias('prop_c_s')
        jjj = sa.join(task, jj, task.c.id == jj.c.task_id)

        mapper(Joined, jjj, properties=dict(
            type=relationship(Task_Type, lazy='joined')))

        session = create_session()

        eq_(session.query(Joined).limit(10).offset(0).one(),
            Joined(id=1, title='task 1', props_cnt=0))

    def GetJoinTable(self, searchInfo):
        startDate = datetime.now()
        if self.startDate:
            startDate = self.startDate

        StatusTable = Base.metadata.tables['IndividualStatus']
        EquipmentTable = Base.metadata.tables['IndividualEquipment']

        joinTable = super().GetJoinTable(searchInfo)

        releaseFilter = list(filter(lambda x: x['Column'] == 'LastImported', searchInfo['criteria']))
        if len(releaseFilter) > 0:
            return joinTable

        joinTable = outerjoin(joinTable, StatusTable, StatusTable.c[
                              'FK_Individual'] == Individual.ID)

        self.selectable.append(StatusTable.c['Status_'].label('Status_'))

        joinTable = outerjoin(joinTable, EquipmentTable,
                              and_(Individual.ID == EquipmentTable.c['FK_Individual'],
                                   and_(or_(EquipmentTable.c['EndDate'] >= startDate,
                                            EquipmentTable.c['EndDate'] == None),
                                        EquipmentTable.c['StartDate'] <= startDate)))

        joinTable = outerjoin(joinTable, Sensor,
                              Sensor.ID == EquipmentTable.c['FK_Sensor'])
        joinTable = outerjoin(joinTable, SensorType,
                              Sensor.FK_SensorType == SensorType.ID)

        self.selectable.append(Sensor.UnicIdentifier.label('FK_Sensor'))
        self.selectable.append(SensorType.Name.label('FK_SensorType'))
        self.selectable.append(Sensor.Model.label('FK_SensorModel'))

        return joinTable

def load_top_40(post):
    a = sa_post.alias()
    expr =  outerjoin(a, sa_post, a.c.parent_id == sa_post.c.id)
    for i in range(1):
        new_a = sa_post.alias()
        expr = outerjoin(expr, new_a, expr.c.v0_post_id == new_a.c.parent_id)
    return expr

    def GetJoinTable(self, searchInfo):
        startDate = datetime.now()
        if self.startDate:
            startDate = self.startDate

        StatusTable = Base.metadata.tables['IndividualStatus']
        EquipmentTable = Base.metadata.tables['IndividualEquipment']

        joinTable = super().GetJoinTable(searchInfo)

        joinTable = outerjoin(joinTable, StatusTable, StatusTable.c[
                              'FK_Individual'] == Individual.ID)

        self.selectable.append(StatusTable.c['Status_'].label('Status_'))

        joinTable = outerjoin(joinTable, EquipmentTable, and_(Individual.ID == EquipmentTable.c['FK_Individual'], and_(or_(
            EquipmentTable.c['EndDate'] >= startDate, EquipmentTable.c['EndDate'] == None), EquipmentTable.c['StartDate'] <= startDate)))
        # EquipmentTable.c['EndDate'] >= func.isnull(EquipmentTable.c['EndDate'
        joinTable = outerjoin(joinTable, Sensor, Sensor.ID ==
                              EquipmentTable.c['FK_Sensor'])
        joinTable = outerjoin(joinTable, SensorType,
                              Sensor.FK_SensorType == SensorType.ID)

        self.selectable.append(Sensor.UnicIdentifier.label('FK_Sensor'))
        self.selectable.append(SensorType.Name.label('FK_SensorType'))
        self.selectable.append(Sensor.Model.label('FK_SensorModel'))

        return joinTable

    def GetJoinTable(self, searchInfo):
        ''' build join table and select statement over all dynamic properties and foreign keys in filter query'''
        joinTable = self.ObjWithDynProp
        view = self.GetDynPropValueView()
        selectable = [self.ObjWithDynProp.ID]
        objTable = self.ObjWithDynProp.__table__
        self.firstStartDate = None

        ##### get all foreign keys #####
        self.fk_list = {
            fk.parent.name: fk for fk in self.ObjWithDynProp.__table__.foreign_keys}
        self.searchInFK = {}
        for objConf in self.GetAllPropNameInConf():
            curDynProp = self.GetDynProp(objConf.Name)

            if objConf.Name in self.fk_list and objConf.QueryName is not None and objConf.QueryName != 'Forced':
                tableRef = self.fk_list[objConf.Name].column.table
                nameRef = self.fk_list[objConf.Name].column.name
                self.searchInFK[objConf.Name] = {
                    'nameProp': objConf.QueryName, 'table': tableRef, 'nameFK': nameRef}

                joinTable = outerjoin(joinTable, tableRef, objTable.c[
                                      objConf.Name] == tableRef.c[nameRef])
                selectable.append(tableRef.c[objConf.QueryName].label(
                    objConf.Name + '_' + objConf.QueryName))

            elif curDynProp != None:
                v = view.alias('v' + curDynProp['Name'])
                self.vAliasList['v' + curDynProp['Name']] = v

                if self.history is False or self.firstStartDate is None:  # firstDate depricated ?
                    joinTable = outerjoin(
                        joinTable, v, and_(self.ObjWithDynProp.ID == v.c[self.ObjWithDynProp().GetSelfFKNameInValueTable()], v.c[
                                           self.ObjWithDynProp().GetDynPropFKName()] == curDynProp['ID'])
                    )
                    selectable.append(
                        v.c['Value' + curDynProp['TypeProp']].label(curDynProp['Name']))
                else:
                    tmpV = self.vAliasList[self.firstStartDate]
                    joinTable = outerjoin(
                        joinTable, v, and_(v.c['StartDate'] == tmpV.c['StartDate'], and_(self.ObjWithDynProp.ID == v.c[self.ObjWithDynProp(
                        ).GetSelfFKNameInValueTable()], v.c[self.ObjWithDynProp().GetDynPropFKName()] == curDynProp['ID']))
                    )
                    selectable.append(
                        v.c['Value' + curDynProp['TypeProp']].label(curDynProp['Name']))

            elif self.optionView is not None and objConf.Name in self.optionView.c:
                if self.optionView.name not in self.vAliasList:
                    joinTable = outerjoin(joinTable, self.optionView, self.ObjWithDynProp.ID == self.optionView.c[
                                          'FK_' + self.ObjWithDynProp.__tablename__])
                    self.vAliasList[self.optionView.name] = self.optionView
                selectable.append(self.optionView.c[objConf.Name])

            elif hasattr(self.ObjWithDynProp, objConf.Name):
                selectable.append(objTable.c[objConf.Name])
        self.selectable = selectable
        return joinTable

    def GetJoinTable (self,searchInfo) :
        curEquipmentTable = Base.metadata.tables['CurrentlySensorEquiped']
        MonitoredSiteTable = Base.metadata.tables['MonitoredSite']
        joinTable = super().GetJoinTable(searchInfo)

        joinTable = outerjoin(joinTable,curEquipmentTable,curEquipmentTable.c['FK_Sensor'] == Sensor.ID)

        joinTable = outerjoin(joinTable,MonitoredSite,MonitoredSiteTable.c['ID'] == curEquipmentTable.c['FK_MonitoredSite'])

        self.selectable.append(MonitoredSiteTable.c['Name'].label('FK_MonitoredSiteName'))
        self.selectable.append(curEquipmentTable.c['FK_Individual'].label('FK_Individual'))

        return joinTable

    def get_query(self):
        tables = set()
        columns = []

        for i in self.fields_to_export:
            table = "movies"
            column = i.split(".")
            if len(column) > 1:
                table = column[0]
                column = column[1]
                if table not in db.tables:
                    log.warning("Wrong table name: %s", table)
                    continue
                tables.add(table)  # will be used to generate JOIN
            else:
                column = column[0]

            if column in db.tables[table].columns:
                columns.append(db.tables[table].columns[column])
            else:
                log.warning("Wrong field name: %s", i)

        joins = []
        if "media" in tables:
            joins.append((db.tables["media"], db.tables["movies"].c.medium_id == db.tables["media"].c.medium_id))
        if "collections" in tables:
            joins.append(
                (
                    db.tables["collections"],
                    db.tables["movies"].c.collection_id == db.tables["collections"].c.collection_id,
                )
            )
        if "volumes" in tables:
            joins.append((db.tables["volumes"], db.tables["movies"].c.volume_id == db.tables["volumes"].c.volume_id))
        if "vcodecs" in tables:
            joins.append((db.tables["vcodecs"], db.tables["movies"].c.vcodec_id == db.tables["vcodecs"].c.vcodec_id))

        if joins:
            from_obj = [outerjoin(db.tables["movies"], *(joins[0]))]
            for j in joins[1:]:
                from_obj.append(outerjoin(from_obj[-1], *j))
            query = select(columns=columns, bind=self.db.session.bind, from_obj=from_obj, use_labels=True)
        else:
            query = select(columns=columns, bind=self.db.session.bind)

        query = update_whereclause(query, self.search_conditions)

        # save column names (will contain 'movies_title' or 'title' depending on how many tables were requested)
        self.exported_columns = query.columns

        return query

def delete_datasets( app, cutoff_time, remove_from_disk, info_only = False, force_retry = False ):
    # Marks datasets as deleted if associated items are all deleted.
    start = time.time()
    if force_retry:
        history_dataset_ids_query = sa.select( ( app.model.Dataset.table.c.id,
                                                 app.model.Dataset.table.c.state ),
                                               whereclause = app.model.HistoryDatasetAssociation.table.c.update_time < cutoff_time,
                                               from_obj = [ sa.outerjoin( app.model.Dataset.table,
                                                                          app.model.HistoryDatasetAssociation.table ) ] )
        library_dataset_ids_query = sa.select( ( app.model.Dataset.table.c.id,
                                                 app.model.Dataset.table.c.state ),
                                                whereclause = app.model.LibraryDatasetDatasetAssociation.table.c.update_time < cutoff_time,
                                                from_obj = [ sa.outerjoin( app.model.Dataset.table,
                                                                           app.model.LibraryDatasetDatasetAssociation.table ) ] )
    else:                                  
        # We really only need the id column here, but sqlalchemy barfs when trying to select only 1 column
        history_dataset_ids_query = sa.select( ( app.model.Dataset.table.c.id,
                                                 app.model.Dataset.table.c.state ),
                                               whereclause = sa.and_( app.model.Dataset.table.c.deleted == False,
                                                                      app.model.HistoryDatasetAssociation.table.c.update_time < cutoff_time,
                                                                      app.model.HistoryDatasetAssociation.table.c.deleted == True ),
                                               from_obj = [ sa.outerjoin( app.model.Dataset.table,
                                                                          app.model.HistoryDatasetAssociation.table ) ] )
        library_dataset_ids_query = sa.select( ( app.model.Dataset.table.c.id,
                                                 app.model.Dataset.table.c.state ),
                                                whereclause = sa.and_( app.model.Dataset.table.c.deleted == False,
                                                                       app.model.LibraryDatasetDatasetAssociation.table.c.update_time < cutoff_time,
                                                                       app.model.LibraryDatasetDatasetAssociation.table.c.deleted == True ),
                                                from_obj = [ sa.outerjoin( app.model.Dataset.table,
                                                                           app.model.LibraryDatasetDatasetAssociation.table ) ] )                       
    history_dataset_ids = [ row.id for row in history_dataset_ids_query.execute() ]
    library_dataset_ids = [ row.id for row in library_dataset_ids_query.execute() ]
    dataset_ids = history_dataset_ids + library_dataset_ids
    skip = []
    deleted_dataset_count = 0
    deleted_instance_count = 0
    for dataset_id in dataset_ids:
        print "######### Processing dataset id:", dataset_id
        dataset = app.sa_session.query( app.model.Dataset ).get( dataset_id )
        if dataset.id not in skip and _dataset_is_deletable( dataset ):
            deleted_dataset_count += 1
            for dataset_instance in dataset.history_associations + dataset.library_associations:
                print "Associated Dataset instance: ", dataset_instance.__class__.__name__, dataset_instance.id
                _purge_dataset_instance( dataset_instance, app, remove_from_disk, include_children=True, info_only=info_only, is_deletable=True )
                deleted_instance_count += 1
        skip.append( dataset.id )
    stop = time.time()
    print "Examined %d datasets, marked %d as deleted and purged %d dataset instances" % ( len( skip ), deleted_dataset_count, deleted_instance_count )
    print "Total elapsed time: ", stop - start
    print "##########################################" 

def create_incomplete_consumers(ctx, batch_size):
    """Finds all the consumer records that are missing for allocations and
    creates consumer records for them, using the "incomplete consumer" project
    and user CONF options.

    Returns a tuple containing two identical elements with the number of
    consumer records created, since this is the expected return format for data
    migration routines.
    """
    # Create a record in the projects table for our incomplete project
    incomplete_proj_id = project_obj.ensure_incomplete_project(ctx)

    # Create a record in the users table for our incomplete user
    incomplete_user_id = user_obj.ensure_incomplete_user(ctx)

    # Create a consumer table record for all consumers where
    # allocations.consumer_id doesn't exist in the consumers table. Use the
    # incomplete consumer project and user ID.
    alloc_to_consumer = sa.outerjoin(
        _ALLOC_TBL, CONSUMER_TBL,
        _ALLOC_TBL.c.consumer_id == CONSUMER_TBL.c.uuid)
    cols = [
        _ALLOC_TBL.c.consumer_id,
        incomplete_proj_id,
        incomplete_user_id,
    ]
    sel = sa.select(cols)
    sel = sel.select_from(alloc_to_consumer)
    sel = sel.where(CONSUMER_TBL.c.id.is_(None))
    sel = sel.limit(batch_size)
    target_cols = ['uuid', 'project_id', 'user_id']
    ins_stmt = CONSUMER_TBL.insert().from_select(target_cols, sel)
    res = ctx.session.execute(ins_stmt)
    return res.rowcount, res.rowcount

    def reorient_cdrs(self):
        conn = meta.bind.connect()
        try:
            net_cdr_j = sa.outerjoin(net_table, conductor_table,
                    onclause=net_to_cdr_join)
            unit_fan_order_q = sa.select(
                    [net_table.c.unit,
                     sa.func.count(conductor_table.c.cable)
                         .label('cdr_count')],
                    from_obj=[net_cdr_j],
                    group_by=[net_table.c.unit],
                    order_by=[sa.asc('cdr_count'), net_table.c.unit])
            unit_fan_order = [r[0] for r in conn.execute(unit_fan_order_q)]

        finally:
            conn.close()

        s = self._get_ses()
        s.begin()
        try:
            for unit in unit_fan_order:
                b_cdrs = (s.query(Conductor)
                          .options(orm.joinedload('a_net'),
                                 orm.joinedload('b_net'))
                          .filter(Conductor.b_net_unit==unit))
                for cdr in b_cdrs:
                    cdr.swap_orientation()
                s.flush()
            s.commit()

        except:
            s.rollback()
            raise
        finally:
            self._close_ses()

    def whereInEquipement(self,fullQueryJoin,criteria):
        sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0]
        sensor = sensorObj['Value']

        table = Base.metadata.tables['IndividualEquipment']
        joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID)
        startDate = datetime.now()

        if self.startDate :
            startDate = self.startDate

        subSelect= select([table.c['FK_Individual']]
            ).select_from(joinTable).where(Individual.ID== table.c['FK_Individual']).where(table.c['StartDate'] <= startDate)

        if sensorObj['Operator'].lower() in ['is null','is not null'] :
            if not self.history :
                subSelect = subSelect.where(or_(table.c['EndDate'] >= startDate,table.c['EndDate'] == None))

        else:
            subSelect = subSelect.where(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor))
            if not self.history : 
                subSelect = subSelect.where(or_(table.c['EndDate'] >= startDate,table.c['EndDate'] == None))

        if  'is not' in sensorObj['Operator'].lower():
            if sensorObj['Operator'].lower() == 'is not null' :
                fullQueryJoin = fullQueryJoin.where(exists(subSelect))
            else :
                fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
        else :
            if sensorObj['Operator'].lower() == 'is null' :
                fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
            else:
                fullQueryJoin = fullQueryJoin.where(exists(subSelect))
        return fullQueryJoin

 def specified_month_in_error( self, trans, **kwd ):
     params = util.Params( kwd )
     msg = ''
     year, month = map( int, params.get( 'month', datetime.utcnow().strftime( "%Y-%m" ) ).split( "-" ) )
     start_date = date( year, month, 1 )
     end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] )
     month_label = start_date.strftime( "%B" )
     year_label = start_date.strftime( "%Y" )
     q = sa.select( ( sa.func.date( galaxy.model.Job.table.c.create_time ).label( 'date' ),
                      sa.func.count( galaxy.model.Job.table.c.id ).label( 'total_jobs' ) ),
                    whereclause = sa.and_( galaxy.model.Job.table.c.state == 'error',
                                           galaxy.model.Job.table.c.create_time >= start_date, 
                                           galaxy.model.Job.table.c.create_time < end_date ),
                    from_obj = [ sa.outerjoin( galaxy.model.Job.table, 
                                               galaxy.model.History.table ).outerjoin( galaxy.model.User.table ) ],
                    group_by = [ 'date' ],
                    order_by = [ sa.desc( 'date' ) ] )
     jobs = []
     for row in q.execute():
         jobs.append( ( row.date.strftime( "%A" ),
                        row.date,
                        row.total_jobs,
                        row.date.strftime( "%d" ) ) )
     return trans.fill_template( 'jobs_specified_month_in_error.mako', 
                                 month_label=month_label, 
                                 year_label=year_label, 
                                 month=month, 
                                 jobs=jobs, 
                                 msg=msg )

    def getEquipment(self):
        _id = self.objectDB.ID

        table = Base.metadata.tables['SensorEquipment']
        joinTable = join(table, Sensor, table.c['FK_Sensor'] == Sensor.ID)
        joinTable = outerjoin(joinTable, MonitoredSite, table.c[
                        'FK_MonitoredSite'] == MonitoredSite.ID)
        query = select([table.c['StartDate'],
                        table.c['EndDate'],
                        Sensor.UnicIdentifier,
                        MonitoredSite.Name,
                        MonitoredSite.ID.label('MonitoredSiteID'),
                        table.c['FK_Individual']]
                       ).select_from(joinTable
                                     ).where(table.c['FK_Sensor'] == _id
                                             ).order_by(desc(table.c['StartDate']))

        result = self.session.execute(query).fetchall()
        response = []
        for row in result:
            curRow = OrderedDict(row)
            curRow['StartDate'] = curRow['StartDate'].strftime('%Y-%m-%d %H:%M:%S')
            curRow['EndDate'] = curRow['EndDate'].strftime(
                '%Y-%m-%d %H:%M:%S') if curRow['EndDate'] is not None else None
            curRow['format'] = 'YYYY-MM-DD HH:mm:ss'
            response.append(curRow)

        return response

    def whereInEquipement(self,fullQueryJoin,criteria):
        sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0]
        sensor = sensorObj['Value']

        table = Base.metadata.tables['MonitoredSiteEquipment']
        joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID)

        if sensorObj['Operator'].lower() in ['is','is not'] and sensorObj['Value'].lower() == 'null':
            subSelect = select([table.c['FK_MonitoredSite']]
                ).select_from(joinTable).where(
                and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
                    ,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None)
                        ))
            if sensorObj['Operator'].lower() == 'is':
                fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
            else :
                fullQueryJoin = fullQueryJoin.where(exists(subSelect))
        else :
            subSelect = select([table.c['FK_MonitoredSite']]
                ).select_from(joinTable).where(
                and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
                    ,and_(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor)
                        ,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None))
                        ))
            fullQueryJoin = fullQueryJoin.where(exists(subSelect))
        return fullQueryJoin

 def user_for_month( self, trans, **kwd ):
     params = util.Params( kwd )
     msg = ''
     email = params.get( 'email', None )
     if email is not None:
         # The @ char has been converted to an 'X'
         email = email.replace( 'X', '@' )
     year, month = map( int, params.get( 'month', datetime.utcnow().strftime( "%Y-%m" ) ).split( "-" ) )
     start_date = date( year, month, 1 )
     end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] )
     month_label = start_date.strftime( "%B" )
     year_label = start_date.strftime( "%Y" )
     jobs = []
     q = sa.select( ( galaxy.model.Job.table.c.id,
                      galaxy.model.Job.table.c.state,
                      galaxy.model.Job.table.c.create_time,
                      galaxy.model.Job.table.c.update_time,
                      galaxy.model.Job.table.c.tool_id,
                      galaxy.model.Job.table.c.command_line,
                      galaxy.model.Job.table.c.stderr,
                      galaxy.model.Job.table.c.session_id,
                      ( galaxy.model.Job.table.c.traceback ).label( 'stack_trace' ),
                      galaxy.model.Job.table.c.info,
                      ( galaxy.model.User.table.c.email ).label( 'user_email' ),
                      galaxy.model.GalaxySession.table.c.remote_addr ),
                    whereclause = sa.and_( galaxy.model.User.table.c.email == email, 
                                           galaxy.model.Job.table.c.create_time >= start_date, 
                                           galaxy.model.Job.table.c.create_time < end_date ),
                    from_obj = [ sa.outerjoin( galaxy.model.Job.table, 
                                               galaxy.model.History.table ) \
                                               .outerjoin( galaxy.model.User.table ) \
                                               .outerjoin( galaxy.model.GalaxySession.table,
                                                           galaxy.model.Job.table.c.session_id == galaxy.model.GalaxySession.table.c.id ) ],
                    order_by = [ sa.desc( galaxy.model.Job.table.c.id ) ] )
     for row in q.execute():
         remote_host = row.remote_addr
         if row.remote_addr:
             try:
                 remote_host = socket.gethostbyaddr( row.remote_addr )[0]
             except:
                 pass
         jobs.append( ( row.state,
                        row.id,
                        row.create_time,
                        row.update_time,
                        row.session_id,
                        row.tool_id,
                        row.user_email,
                        remote_host,
                        row.command_line,
                        row.stderr,
                        row.stack_trace,
                        row.info ) )
     return trans.fill_template( 'jobs_user_for_month.mako', 
                                 email=email, 
                                 month=month, 
                                 month_label=month_label, 
                                 year_label=year_label, 
                                 jobs=jobs, 
                                 msg=msg )

 def today_all( self, trans, **kwd ):
     params = util.Params( kwd )
     msg = ''
     monitor_email = params.get( 'monitor_email', '[email protected]' )
     year, month, day = map( int, datetime.utcnow().strftime( "%Y-%m-%d" ).split( "-" ) )
     start_date = date( year, month, day )
     end_date = start_date + timedelta( days=1 )
     day_label = start_date.strftime( "%A" )
     month_label = start_date.strftime( "%B" )
     year_label = start_date.strftime( "%Y" )
     day_of_month = start_date.strftime( "%d" )
     q = sa.select( ( sa.func.date( galaxy.model.Job.table.c.create_time ).label( 'date' ),
                      sa.func.sum( sa.case( [( galaxy.model.User.table.c.email == monitor_email, 1 )], else_=0 ) ).label( 'monitor_jobs' ),
                      sa.func.count( galaxy.model.Job.table.c.id ).label( 'total_jobs' ) ),
                    whereclause = sa.and_( galaxy.model.Job.table.c.create_time >= start_date,
                                           galaxy.model.Job.table.c.create_time < end_date ),
                    from_obj = [ sa.outerjoin( galaxy.model.Job.table, 
                                               galaxy.model.History.table ).outerjoin( galaxy.model.User.table ) ],
                    group_by = [ 'date' ] )
     jobs = []
     for row in q.execute():
         jobs.append( ( row.date.strftime( "%A" ),
                        row.date,
                        row.total_jobs - row.monitor_jobs,
                        row.monitor_jobs,
                        row.total_jobs,
                        row.date.strftime( "%d" ) ) )
     return trans.fill_template( 'jobs_today_all.mako', 
                                 day_label=day_label, 
                                 month_label=month_label, 
                                 year_label=year_label, 
                                 day_of_month=day_of_month, 
                                 month=month, 
                                 jobs=jobs, 
                                 msg=msg )

    def per_user( self, trans, **kwd ):
        params = util.Params( kwd )
        message = ''
        monitor_email = params.get( 'monitor_email', '[email protected]' )
        specs = sorter( 'user_email', kwd )
        sort_id = specs.sort_id
        order = specs.order
        arrow = specs.arrow
        _order = specs.exc_order

        jobs = []
        jobs_per_user = sa.select( ( model.User.table.c.email.label( 'user_email' ),
                                     sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ),
                                   from_obj=[ sa.outerjoin( model.Job.table, model.User.table ) ],
                                   group_by=[ 'user_email' ],
                                   order_by=[ _order ] )
        for row in jobs_per_user.execute():
            if ( row.user_email is None ):
                jobs.append( ( 'Anonymous',
                               row.total_jobs ) )
            elif ( row.user_email == monitor_email ):
                continue
            else:
                jobs.append( ( row.user_email,
                               row.total_jobs ) )
        return trans.fill_template( '/webapps/reports/jobs_per_user.mako',
                                    order=order,
                                    arrow=arrow,
                                    sort_id=sort_id,
                                    jobs=jobs,
                                    message=message )

 def specified_month_in_error( self, trans, **kwd ):
     params = util.Params( kwd )
     message = ''
     # If specified_date is not received, we'll default to the current month
     specified_date = kwd.get( 'specified_date', datetime.utcnow().strftime( "%Y-%m-%d" ) )
     specified_month = specified_date[ :7 ]
     year, month = map( int, specified_month.split( "-" ) )
     start_date = date( year, month, 1 )
     end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] )
     month_label = start_date.strftime( "%B" )
     year_label = start_date.strftime( "%Y" )
     q = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ),
                      sa.func.count( model.Job.table.c.id ).label( 'total_jobs' ) ),
                    whereclause = sa.and_( model.Job.table.c.state == 'error',
                                           model.Job.table.c.create_time >= start_date, 
                                           model.Job.table.c.create_time < end_date ),
                    from_obj = [ sa.outerjoin( model.Job.table, model.User.table ) ],
                    group_by = [ 'date' ],
                    order_by = [ sa.desc( 'date' ) ] )
     jobs = []
     for row in q.execute():
         jobs.append( ( row.date.strftime( "%A" ),
                        row.date,
                        row.total_jobs,
                        row.date.strftime( "%d" ) ) )
     return trans.fill_template( '/webapps/reports/jobs_specified_month_in_error.mako', 
                                 month_label=month_label, 
                                 year_label=year_label, 
                                 month=month, 
                                 jobs=jobs, 
                                 message=message )

 def per_month_all(self, trans, **kwd):
     params = util.Params(kwd)
     message = ""
     monitor_email = params.get("monitor_email", "[email protected]")
     q = sa.select(
         (
             sa.func.date_trunc("month", sa.func.date(model.Job.table.c.create_time)).label("date"),
             sa.func.sum(sa.case([(model.User.table.c.email == monitor_email, 1)], else_=0)).label("monitor_jobs"),
             sa.func.count(model.Job.table.c.id).label("total_jobs"),
         ),
         from_obj=[sa.outerjoin(model.Job.table, model.User.table)],
         group_by=[sa.func.date_trunc("month", sa.func.date(model.Job.table.c.create_time))],
         order_by=[sa.desc("date")],
     )
     jobs = []
     for row in q.execute():
         jobs.append(
             (
                 row.date.strftime("%Y-%m"),
                 row.total_jobs - row.monitor_jobs,
                 row.monitor_jobs,
                 row.total_jobs,
                 row.date.strftime("%B"),
                 row.date.strftime("%Y"),
             )
         )
     return trans.fill_template("/webapps/reports/jobs_per_month_all.mako", jobs=jobs, message=message)

    def history_per_user(self, trans, **kwd):
        message = escape(util.restore_text(kwd.get('message', '')))
        user_cutoff = int(kwd.get('user_cutoff', 60))
        sorting = 0 if kwd.get('sorting', 'User') == 'User' else 1
        descending = 1 if kwd.get('descending', 'desc') == 'desc' else -1
        sorting_functions = [
            lambda first, second: descending if first[0].lower() > second[0].lower() else -descending,
            lambda first, second: descending if first[1] < second[1] else -descending]

        req = sa.select(
            (sa.func.count(galaxy.model.History.table.c.id).label('history'),
             galaxy.model.User.table.c.username.label('username')),
            from_obj=[sa.outerjoin(galaxy.model.History.table, galaxy.model.User.table)],
            whereclause=galaxy.model.History.table.c.user_id == galaxy.model.User.table.c.id,
            group_by=['username'],
            order_by=[sa.desc('username'), 'history'])

        histories = [(_.username if _.username is not None else "Unknown", _.history) for _ in req.execute()]
        histories.sort(sorting_functions[sorting])
        if user_cutoff != 0:
            histories = histories[:user_cutoff]

        return trans.fill_template('/webapps/reports/history_per_user.mako',
                                   histories=histories,
                                   user_cutoff=user_cutoff,
                                   sorting=sorting,
                                   descending=descending,
                                   message=message)