/lib/galaxy/webapps/reports/controllers/workflows.py

  1import calendar
  2from datetime import date, timedelta
  3from galaxy import eggs
  4from galaxy import model, util
  5from galaxy.model.orm import and_
  6from galaxy.web.base.controller import BaseUIController, web
  7from galaxy.web.framework.helpers import grids
  8eggs.require( "SQLAlchemy >= 0.4" )
  9import sqlalchemy as sa
 10
 11import logging
 12log = logging.getLogger( __name__ )
 13
 14
 15class SpecifiedDateListGrid( grids.Grid ):
 16
 17    class WorkflowNameColumn( grids.TextColumn ):
 18        def get_value( self, trans, grid, stored_workflow ):
 19            return stored_workflow.name
 20
 21    class CreateTimeColumn( grids.DateTimeColumn ):
 22        def get_value( self, trans, grid, stored_workflow ):
 23            return stored_workflow.create_time
 24
 25    class UserColumn( grids.TextColumn ):
 26        def get_value( self, trans, grid, stored_workflow ):
 27            if stored_workflow.user:
 28                return stored_workflow.user.email
 29            return 'unknown'
 30
 31    class EmailColumn( grids.GridColumn ):
 32        def filter( self, trans, user, query, column_filter ):
 33            if column_filter == 'All':
 34                return query
 35            return query.filter( and_( model.StoredWorkflow.table.c.user_id == model.User.table.c.id,
 36                                       model.User.table.c.email == column_filter ) )
 37
 38    class SpecifiedDateColumn( grids.GridColumn ):
 39        def filter( self, trans, user, query, column_filter ):
 40            if column_filter == 'All':
 41                return query
 42            # We are either filtering on a date like YYYY-MM-DD or on a month like YYYY-MM,
 43            # so we need to figure out which type of date we have
 44            if column_filter.count( '-' ) == 2:
 45                # We are filtering on a date like YYYY-MM-DD
 46                year, month, day = map( int, column_filter.split( "-" ) )
 47                start_date = date( year, month, day )
 48                end_date = start_date + timedelta( days=1 )
 49                return query.filter( and_( self.model_class.table.c.create_time >= start_date,
 50                                           self.model_class.table.c.create_time < end_date ) )
 51            if column_filter.count( '-' ) == 1:
 52                # We are filtering on a month like YYYY-MM
 53                year, month = map( int, column_filter.split( "-" ) )
 54                start_date = date( year, month, 1 )
 55                end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] )
 56                return query.filter( and_( self.model_class.table.c.create_time >= start_date,
 57                                           self.model_class.table.c.create_time < end_date ) )
 58
 59    # Grid definition
 60    use_async = False
 61    model_class = model.StoredWorkflow
 62    title = "Workflows"
 63    template='/webapps/reports/grid.mako'
 64    default_sort_key = "name"
 65    columns = [
 66        WorkflowNameColumn( "Name",
 67                            key="name",
 68                            #link=( lambda item: dict( operation="workflow_info", id=item.id, webapp="reports" ) ),
 69                            attach_popup=False,
 70                            filterable="advanced" ),
 71        CreateTimeColumn( "Creation Time",
 72                          key="create_time",
 73                          attach_popup=False ),
 74        UserColumn( "User",
 75                    key="email",
 76                    model_class=model.User,
 77                    link=( lambda item: dict( operation="user_per_month", id=item.id, webapp="reports" ) ),
 78                    attach_popup=False ),
 79        # Columns that are valid for filtering but are not visible.
 80        SpecifiedDateColumn( "Specified Date",
 81                             key="specified_date",
 82                             visible=False ),
 83        EmailColumn( "Email",
 84                     key="email",
 85                     model_class=model.User,
 86                     visible=False ),
 87    ]
 88    columns.append( grids.MulticolFilterColumn( "Search",
 89                                                cols_to_filter=[ columns[0], columns[2] ],
 90                                                key="free-text-search",
 91                                                visible=False,
 92                                                filterable="standard" ) )
 93    standard_filters = []
 94    default_filter = { 'specified_date' : 'All' }
 95    num_rows_per_page = 50
 96    preserve_state = False
 97    use_paging = True
 98    def build_initial_query( self, trans, **kwd ):
 99        return trans.sa_session.query( self.model_class ) \
100                               .join( model.User ) \
101                               .enable_eagerloads( False )
102
103
104class Workflows( BaseUIController ):
105
106    specified_date_list_grid = SpecifiedDateListGrid()
107
108    @web.expose
109    def specified_date_handler( self, trans, **kwd ):
110        # We add params to the keyword dict in this method in order to rename the param
111        # with an "f-" prefix, simulating filtering by clicking a search link.  We have
112        # to take this approach because the "-" character is illegal in HTTP requests.
113        if 'f-specified_date' in kwd and 'specified_date' not in kwd:
114            # The user clicked a State link in the Advanced Search box, so 'specified_date'
115            # will have been eliminated.
116            pass
117        elif 'specified_date' not in kwd:
118            kwd[ 'f-specified_date' ] = 'All'
119        else:
120            kwd[ 'f-specified_date' ] = kwd[ 'specified_date' ]
121        if 'operation' in kwd:
122            operation = kwd['operation'].lower()
123            if operation == "workflow_per_month":
124                # The received id is the stored_workflow id.
125                return trans.response.send_redirect( web.url_for( controller='workflows',
126                                                                  action='workflow_per_month',
127                                                                  **kwd ) )
128            elif operation == "user_per_month":
129                stored_workflow_id = kwd.get( 'id', None )
130                workflow = get_workflow( trans, stored_workflow_id )
131                if workflow.user:
132                    kwd[ 'email' ] = workflow.user.email
133                else:
134                    kwd[ 'email' ] = None # For anonymous users ( shouldn't happen with workflows )
135                return trans.response.send_redirect( web.url_for( controller='workflows',
136                                                                  action='user_per_month',
137                                                                  **kwd ) )
138        return self.specified_date_list_grid( trans, **kwd )
139
140    @web.expose
141    def per_month_all( self, trans, **kwd ):
142        message = ''
143        q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ),sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ),
144                       from_obj = [ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ],
145                       group_by = [ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ],
146                       order_by = [ sa.desc( 'date' ) ] )
147        workflows = []
148        for row in q.execute():
149            workflows.append( ( row.date.strftime( "%Y-%m" ),
150                                row.total_workflows,
151                                row.date.strftime( "%B" ),
152                                row.date.strftime( "%Y" ) ) )
153        return trans.fill_template( '/webapps/reports/workflows_per_month_all.mako',
154                                    workflows=workflows,
155                                    message=message )
156
157    @web.expose
158    def per_user( self, trans, **kwd ):
159        message = ''
160        workflows = []
161        q = sa.select( ( model.User.table.c.email.label( 'user_email' ),
162                         sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ),
163                       from_obj = [ sa.outerjoin( model.StoredWorkflow.table, model.User.table ) ],
164                       group_by = [ 'user_email' ],
165                       order_by = [ sa.desc( 'total_workflows' ), 'user_email' ] )
166        for row in q.execute():
167            workflows.append( ( row.user_email,
168                                row.total_workflows ) )
169        return trans.fill_template( '/webapps/reports/workflows_per_user.mako', workflows=workflows, message=message )
170
171    @web.expose
172    def user_per_month( self, trans, **kwd ):
173        params = util.Params( kwd )
174        message = ''
175        email = util.restore_text( params.get( 'email', '' ) )
176        user_id = trans.security.decode_id( params.get( 'id', '' ) )
177        q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ).label( 'date' ),
178                         sa.func.count( model.StoredWorkflow.table.c.id ).label( 'total_workflows' ) ),
179                       whereclause = model.StoredWorkflow.table.c.user_id == user_id,
180                       from_obj = [ model.StoredWorkflow.table ],
181                       group_by = [ sa.func.date_trunc( 'month', sa.func.date( model.StoredWorkflow.table.c.create_time ) ) ],
182                       order_by = [ sa.desc( 'date' ) ] )
183        workflows = []
184        for row in q.execute():
185            workflows.append( ( row.date.strftime( "%Y-%m" ),
186                                row.total_workflows,
187                                row.date.strftime( "%B" ),
188                                row.date.strftime( "%Y" ) ) )
189        return trans.fill_template( '/webapps/reports/workflows_user_per_month.mako',
190                                    email=util.sanitize_text( email ),
191                                    workflows=workflows,
192                                    message=message )
193
194## ---- Utility methods -------------------------------------------------------
195
196def get_workflow( trans, id ):
197    return trans.sa_session.query( trans.model.Workflow ).get( trans.security.decode_id( id ) )