terom@180: from pvl.web import html
terom@180: from pvl.verkko import web, db
terom@180: 
terom@183: import math
terom@183: 
terom@180: import logging; log = logging.getLogger('pvl.verkko.table')
terom@180: 
terom@180: class Column (object) :
terom@180:     """
terom@180:         web.Table column spec, representing a property of an SQLAlchemy ORM model class.
terom@180:     """
terom@180: 
terom@180:     def __init__ (self, attr, title, column, rowhtml=None, sort=True, filter=True, colcss=True, rowfilter=False, rowtitle=None, rowcss=None) :
terom@180:         """
terom@180:             attr        - name of column value, used for http query arg, html css class, model getattr()
terom@180:             title       - title of column
terom@180:             column      - the model column property, used to build queries
terom@180:             rowhtml     - function returning column value as HTML for model
terom@180:             sort        - allow sorting by column
terom@180:             filter      - allow filtering by column
terom@180:             colcss      - apply css class for column; True -> attr
terom@180:             rowfilter   - allow filtering by row value
terom@180:             rowtitle    - function returning column title for model
terom@180:             rowcss      - function returning column class for model
terom@180:         """
terom@180: 
terom@180:         if colcss is True :
terom@180:             colcss = attr
terom@180: 
terom@180:         self.attr = attr
terom@180:         self.title = title
terom@180:         self.column = column
terom@180: 
terom@180:         # column attrs
terom@180:         self.sort = sort
terom@180:         self.filter = filter
terom@180:         self.colcss = colcss
terom@180:         
terom@180:         # row attrs
terom@180:         self.rowhtml = rowhtml
terom@180:         self.rowfilter = rowfilter
terom@180:         self.rowtitle = rowtitle
terom@180:         self.rowcss = rowcss
terom@180: 
terom@180:     def render_header (self, sorturl) :
terom@180:         """
terom@180:             Render <th> for <thead> in given Table.
terom@180:         """
terom@180: 
terom@180:         header = self.title
terom@180: 
terom@180:         if self.sort :
terom@180:             header = html.a(href=sorturl)(header)
terom@180: 
terom@180:         return html.th(header)
terom@180: 
terom@180:     def render_filter_input (self, filters) :
terom@180:         """
terom@180:             Render filter <input> in given Table.
terom@180:         """
terom@180:         
terom@180:         value = filters.get(self.attr)
terom@180: 
terom@180:         if value :
terom@180:             # XXX: multi-valued filters?
terom@180:             value = value[0]
terom@180:         else :
terom@180:             value = None
terom@180: 
terom@180:         return html.input(type='text', name=self.attr, value=value)
terom@180: 
terom@180:     def render_header_filter (self, filters) :
terom@180:         """
terom@180:             Render <td><input> for <thead> in given Table.
terom@180:         """
terom@180: 
terom@180:         if self.filter :
terom@180:             input = self.render_filter_input(filters)
terom@180:         else  :
terom@180:             input = None
terom@180: 
terom@180:         return html.td(class_=self.colcss)(input)
terom@180: 
terom@204:     def cell_html (self, item) :
terom@204:         """
terom@204:             Render contents for <td>.
terom@204:         """
terom@204: 
terom@204:         # XXX: this is sometimes broken, figure out how to index by column
terom@204:         value = getattr(item, self.attr)
terom@204: 
terom@204:         if self.rowhtml :
terom@204:             return self.rowhtml(item)
terom@204:         else :
terom@204:             return value
terom@204: 
terom@204:     def cell_css (self, item, hilight=None) :
terom@204:         """
terom@204:             Return CSS classes for <td>.
terom@204:         """
terom@204: 
terom@204:         if self.colcss :
terom@204:             yield self.colcss
terom@204: 
terom@204:         if self.rowcss :
terom@204:             yield self.rowcss(item)
terom@204: 
terom@204:         if hilight :
terom@204:             # lookup attr/value
terom@204:             hilight = self.attr in hilight and value in hilight[self.attr]
terom@204: 
terom@204:         if hilight :
terom@204:             yield 'hilight'
terom@204: 
terom@204:     def cell_title (self, item) :
terom@204:         """
terom@204:             Return title= for <td>
terom@204:         """
terom@204: 
terom@204:         if self.rowtitle :
terom@204:             return self.rowtitle(item)
terom@204: 
terom@204:     def render_cell (self, item, table, filters=None, hilight=None) :
terom@180:         """
terom@180:             Render <td> for item in <tbody> in given Table.
terom@180: 
terom@180:                 hilight     - optionally higlight given { attr: value }'s using CSS.
terom@180:         """
terom@180: 
terom@180:         # XXX: this is sometimes broken, figure out how to index by column
terom@180:         value = getattr(item, self.attr)
terom@180: 
terom@204:         if self.rowfilter and filters is not None :
terom@180:             # filter-link by row-value
terom@180:             filters = { self.attr: value }
terom@180:         else :
terom@180:             filters = None
terom@180: 
terom@204:         yield table.render_cell(self.cell_html(item),
terom@204:                 css         = tuple(self.cell_css(item, hilight=hilight)),
terom@180:                 filters     = filters,
terom@204:                 title       = self.cell_title(item),
terom@180:         )
terom@180: 
terom@180: class Table (object) :
terom@180:     """
terom@180:         Render <table> with Columns from SQLAlchemy ORM model class.
terom@180:     """
terom@180: 
terom@180:     COLUMNS = ()
terom@183:     ITEMS = None
terom@180:     
terom@180:     # attr -> column
terom@180:     ATTRS = dict((col.attr, col) for col in COLUMNS)
terom@180:  
terom@180:     # items per page
terom@180:     PAGE = 10
terom@180:     
terom@180:     def __init__ (self, url, columns=None, table_url=None, item_url=None, caption=None, page=None) :
terom@180:         """
terom@180:                 url         - pvl.web.Handler.url()
terom@180:                 table_url   - ListHandler, or self?
terom@180:                 item_url    - ItemHandler, or self#id
terom@180:                 columns     - sequence of Columns
terom@180:                 caption     - optional <caption>
terom@180:                 page        - items per page
terom@180:         """
terom@180:         
terom@180:         self.url = url
terom@180: 
terom@180:         self.columns = columns or self.COLUMNS
terom@180:         self.table_url = table_url
terom@180:         self.item_url = item_url
terom@180: 
terom@180:         self.caption = caption
terom@180:         self.page = page or self.PAGE
terom@180: 
terom@180:     def tableurl (self, filters=None, **opts) :
terom@180:         """
terom@180:             URL for table with given opts, keeping our sorting/filtering unless overriden.
terom@180:         """
terom@180: 
terom@180:         args = dict()
terom@180: 
terom@180:         # apply
terom@180:         if filters :
terom@180:             args.update(filters)
terom@180:         
terom@180:         if opts :
terom@180:             args.update(opts)
terom@180: 
terom@180:         return self.url(self.table_url, **args)
terom@180: 
terom@180:     def sorturl (self, attr, sort=None, **opts) :
terom@180:         """
terom@180:             URL for table sorted by given column, reversing direction if already sorting by given column.
terom@180:         """
terom@180: 
terom@180:         if not sort :
terom@180:             sort = attr
terom@180:         elif sort.lstrip('+-') != attr :
terom@180:             sort = attr
terom@180:         elif sort.startswith('-') :
terom@180:             sort = "+" + attr
terom@180:         else :
terom@180:             sort = "-" + attr
terom@180: 
terom@180:         return self.tableurl(sort=sort, **opts)
terom@180: 
terom@180:     def itemurl (self, item) :
terom@180:         """
terom@180:             URL for given item, by id.
terom@180:         """
terom@180: 
terom@180:         if self.item_url :
terom@180:             # separate page
terom@180:             return self.url(self.item_url, id=item.id)
terom@180:         else :
terom@180:             # to our table
terom@180:             return '#{id}'.format(id=item.id)
terom@180: 
terom@180:     def render_head (self, filters=None, sort=None) :
terom@180:         """
terom@180:             Yield header columns in table header.
terom@180:         """
terom@180:         
terom@180:         # id
terom@180:         yield html.th('#')
terom@180:         
terom@180:         for column in self.columns :
terom@180:             yield column.render_header(sorturl=self.sorturl(column.attr, sort=sort, filters=filters))
terom@180: 
terom@180:     def render_head_filters (self, filters=None) :
terom@180:         """
terom@180:             Yield filter columns in table header.
terom@180:         """
terom@180: 
terom@180:         # id
terom@180:         yield html.td(html.input(type='submit', value=u'\u00BF'))
terom@180:         
terom@180:         for column in self.columns :
terom@180:             yield column.render_header_filter(filters)
terom@180: 
terom@204:     def render_cell (self, rowhtml, css=(), filters=None, title=None) :
terom@180:         """
terom@180:             Render a single cell.
terom@180: 
terom@180:                 htmlvalue   - rendered value
terom@180:                 css         - css classes to apply
terom@180:                 filters     - render filter link for filter-values?
terom@180:                 title       - mouseover title for cell
terom@180:         """
terom@180: 
terom@180:         if filters :
terom@180:             cell = html.a(href=self.tableurl(**filters))(rowhtml)
terom@180:         else :
terom@180:             cell = rowhtml
terom@180: 
terom@180:         css = ' '.join(cls for cls in css if cls)
terom@180:         
terom@204:         return html.td(class_=css, title=title)(cell)
terom@180:    
terom@180:     def render_row (self, item, **opts) :
terom@180:         """
terom@180:             Yield columns for row.
terom@180:         """
terom@204: 
terom@204:         yield html.th(
terom@204:             html.a(href=self.itemurl(item))("#")
terom@204:         ),
terom@204: 
terom@180:         for column in self.columns :
terom@180:             yield column.render_cell(item, self, **opts)
terom@180: 
terom@180:     def render_body (self, rows, **opts) :
terom@180:         """
terom@180:             Yield body rows.
terom@180:         """
terom@180: 
terom@180:         for i, item in enumerate(rows) :
terom@180:             yield html.tr(class_=('alternate' if i % 2 else None), id=item.id)(
terom@180:                 self.render_row(item, **opts)
terom@180:             )
terom@180: 
terom@183:     def render_pagination (self, page, count=None, **opts) :
terom@180:         """
terom@180:             Render pagination links.
terom@180:         """
terom@180: 
terom@180:         if count is not None :
terom@180:             pages = int(math.ceil(count / self.page))
terom@180:         else :
terom@180:             pages = None
terom@180: 
terom@180:         if page > 0 :
terom@183:             yield html.a(href=self.tableurl(page=0, **opts))(html("&laquo;&laquo; First"))
terom@183:             yield html.a(href=self.tableurl(page=(page - 1), **opts))(html("&laquo; Prev"))
terom@180:         
terom@183:         if pages :
terom@183:             yield html.span("Page {page} of {pages}".format(page=(page + 1), pages=pages))
terom@183:         else :
terom@183:             yield html.span("Page {page}".format(page=(page + 1)))
terom@180: 
terom@183:         yield html.a(href=self.tableurl(page=(page + 1), **opts))(html("&raquo; Next"))
terom@180: 
terom@183:     def render_foot (self, query, page, **opts) :
terom@180:         """
terom@180:             Render pagination/host count in footer.
terom@180:         """
terom@180: 
terom@180:         # XXX: does separate SELECT count()
terom@180:         count = query.count()
terom@180: 
terom@183:         if page is None :
terom@183:             return "{count} {items}".format(count=count, items=self.ITEMS)
terom@180:         else :
terom@183:             # XXX: count is just the count we got..
terom@183:             return self.render_pagination(page, count=None, **opts)
terom@180: 
terom@180:     def render (self, query, filters=None, sort=None, page=None, hilight=None) :
terom@180:         """
terom@180:             Return <table> element. Wrapped in <form> if filters.
terom@180:                 query   - filter()'d sort()'d SELECT query()
terom@180:                 filters - None for no filtering ui, dict of filters otherwise.
terom@180:                 sort    - None for no sorting ui, sort-attr otherwise.
terom@180:                 page    - display pagination for given page
terom@180:                 hilight - { attr: value } cells to hilight
terom@180:         """
terom@180: 
terom@180:         # render table
terom@180:         table = html.table(
terom@180:             html.caption(self.caption) if self.caption else None,
terom@180:             html.thead(
terom@180:                 html.tr(
terom@180:                     self.render_head(filters=filters, sort=sort)
terom@180:                 ),
terom@180:                 (
terom@180:                     html.tr(class_='filter')(self.render_head_filters(filters=filters))
terom@180:                 ) if filters is not None else None,
terom@180:             ),
terom@180:             html.tbody(
terom@180:                 self.render_body(query,
terom@204:                     filters = filters,
terom@180:                     hilight = hilight,
terom@180:                 )
terom@180:             ),
terom@180:             html.tfoot(
terom@180:                 html.tr(
terom@180:                     html.td(colspan=(1 + len(self.columns)))(
terom@183:                         self.render_foot(query, page, filters=filters, sort=sort)
terom@180:                     )
terom@180:                 )
terom@180:             )
terom@180:         )
terom@180:         
terom@180:         # filters form?
terom@180:         if filters is None :
terom@180:             return table
terom@180:         else :
terom@180:             return html.form(method='get', action=self.tableurl())(
terom@180:                 html.input(type='hidden', name='sort', value=sort),
terom@180:                 table,
terom@180:             )
terom@180: 
terom@204:     def json (self, item) :
terom@204:         """
terom@204:             Yield JSON params for given item.
terom@204:         """
terom@204: 
terom@204:         yield 'id', item.id
terom@204: 
terom@204:         for column in self.columns :
terom@204:             yield column.attr, dict(
terom@204:                     html    = unicode(html(column.cell_html(item))),
terom@204:                     css     = tuple(column.cell_css(item)),
terom@204:                     title   = column.cell_title(item),
terom@204:             )
terom@204: 
terom@180: class TableHandler (object) :
terom@180:     """
terom@180:         Mixin for handling Table args/rendering.
terom@180:     """
terom@180: 
terom@180:     CSS = (
terom@180:         "/static/dhcp/table.css", 
terom@180:     )
terom@180:     
terom@180:     TABLE = None
terom@180: 
terom@180:     # target Handlers for table links
terom@180:     TABLE_URL = None
terom@180:     TABLE_ITEM_URL = None
terom@204: 
terom@204:     def init (self) :
terom@204:         """
terom@204:             Bind self.table
terom@204:         """
terom@204: 
terom@204:         super(TableHandler, self).init()
terom@204: 
terom@204:         self.table = self.TABLE(self.url,
terom@204:                 table_url   = self.TABLE_URL,
terom@204:                 item_url    = self.TABLE_ITEM_URL,
terom@204:         )
terom@204: 
terom@180:     def query (self) :
terom@180:         """
terom@180:             Database SELECT query.
terom@180:         """
terom@180: 
terom@180:         raise NotImplementedError()
terom@180:     
terom@180:     def sort (self, query) :
terom@180:         """
terom@180:             Apply ?sort= from requset args to query.
terom@180: 
terom@180:             Return { attr: sort }, query
terom@180:         """
terom@180: 
terom@180:         sort = self.request.args.get('sort')
terom@180: 
terom@180:         if sort :
terom@180:             name = sort.lstrip('+-')
terom@180:         else :
terom@180:             name = None
terom@180: 
terom@180:         if name :
terom@180:             order_by = self.TABLE.ATTRS[name].column
terom@180:         else :
terom@180:             order_by = self.TABLE.SORT # default
terom@180:         
terom@180:         # prefix -> ordering
terom@180:         if not sort :
terom@180:             pass
terom@180:         elif sort.startswith('+') :
terom@180:             order_by = order_by.asc()
terom@180:         elif sort.startswith('-') :
terom@180:             order_by = order_by.desc()
terom@180:         else :
terom@180:             pass
terom@180:         
terom@180:         # apply
terom@180:         log.debug("sort: %s", order_by)
terom@180:         
terom@180:         query = query.order_by(order_by)
terom@180: 
terom@180:         return sort, query
terom@180:     
terom@180:     def filter_attr (self, attr, value) :
terom@180:         """
terom@180:             Return filter expression for given attr == value
terom@180:         """
terom@180: 
terom@180:         # preprocess
terom@180:         like = False
terom@180: 
terom@180:         if value.endswith('*') :
terom@180:             like = value.replace('*', '%')
terom@180: 
terom@180:         # filter
terom@180:         column = self.TABLE.ATTRS[attr].column
terom@180: 
terom@180:         if like :
terom@180:             return (column.like(like))
terom@180:         else :
terom@180:             return (column == value)
terom@180:  
terom@180:     def _filter (self, attr, values) :
terom@180:         """
terom@180:             Apply filters for given attr -> (value, expression)
terom@180:         """
terom@180: 
terom@180:         for value in values :
terom@180:             value = value.strip()
terom@180:             
terom@180:             # ignore empty fields
terom@180:             if not value :
terom@180:                 continue
terom@180: 
terom@180:             # lookup attr-specific filter
terom@180:             filter = getattr(self, 'filter_{attr}'.format(attr=attr), None)
terom@180: 
terom@180:             if filter :
terom@180:                 filter = filter(value)
terom@180:             else :
terom@180:                 # use generic
terom@180:                 filter = self.filter_attr(attr, value)
terom@180:             
terom@180:             log.debug("%s: %s: %s", attr, value, filter)
terom@180:             
terom@180:             yield value, filter
terom@180: 
terom@180:     def filter (self, query) :
terom@180:         """
terom@180:             Apply filters from request.args against given hosts.
terom@180: 
terom@180:             Returns (filters, hosts).
terom@180:         """
terom@180: 
terom@180:         # filter?
terom@180:         filters = {}
terom@180: 
terom@180:         for attr in self.TABLE.ATTRS :
terom@180:             # from request args
terom@180:             values = self.request.args.getlist(attr)
terom@180: 
terom@180:             # lookup attr filters as expressions
terom@180:             value_filters = list(self._filter(attr, values))
terom@180: 
terom@180:             # ignore empty fields
terom@180:             if not value_filters :
terom@180:                 continue
terom@180:         
terom@180:             # filtering values, and filter expressions
terom@180:             values, expressions = zip(*value_filters)
terom@180: 
terom@180:             # apply
terom@180:             query = query.filter(db.or_(*expressions))
terom@180:             filters[attr] = values
terom@180: 
terom@180:         return filters, query
terom@180: 
terom@180:     def filters_title (self) :
terom@180:         """
terom@180:             Return a string representing the applied filters.
terom@180:         """
terom@180: 
terom@180:         return ', '.join(value for values in self.filters.itervalues() for value in values)
terom@180: 
terom@180:     def process (self) :
terom@180:         """
terom@180:             Process request args -> self.filters, self.sorts, self.page, self.query
terom@180:         """
terom@180: 
terom@180:         query = self.query()
terom@180: 
terom@180:         # filter
terom@180:         self.filters, query = self.filter(query)
terom@180: 
terom@180:         # sort
terom@180:         # TODO: sort per filter column by default?
terom@180:         self.sorts, query = self.sort(query)
terom@180:         
terom@180:         # page?
terom@180:         self.page = self.request.args.get('page')
terom@180: 
terom@180:         if self.page :
terom@180:             self.page = int(self.page)
terom@180: 
terom@180:             query = query.offset(self.page * self.TABLE.PAGE).limit(self.TABLE.PAGE)
terom@180: 
terom@180:         self.query = query
terom@180:  
terom@180:     def render_table (self, query, filters=None, sort=None, page=None, hilight=None) :
terom@180:         """
terom@180:             Render table
terom@180:                 query       - SELECT query for rows
terom@180:                 filters     - applied filters
terom@180:                 sort        - applied sort
terom@180:                 page        - applied page
terom@180:                 hilight     - hilight given { attr: value } cells
terom@180:         """
terom@180: 
terom@204:         return self.table.render(query,
terom@180:                 filters = filters,
terom@180:                 sort    = sort,
terom@180:                 page    = page,
terom@180:                 hilight = hilight,
terom@180:         )
terom@180: