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

https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 197 lines · 165 code · 18 blank · 14 comment · 17 complexity · 3f17f16ef47d2d9d3fcad11a282e6d89 MD5 · raw file

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