| Home | Trees | Indices | Help |
|
|---|
|
|
1 # -*- coding: utf-8 -*-
2
3 """ S3 Pivot Table Reports Method
4
5 @copyright: 2011-2019 (c) Sahana Software Foundation
6 @license: MIT
7
8 @requires: U{B{I{Python 2.6}} <http://www.python.org>}
9
10 Permission is hereby granted, free of charge, to any person
11 obtaining a copy of this software and associated documentation
12 files (the "Software"), to deal in the Software without
13 restriction, including without limitation the rights to use,
14 copy, modify, merge, publish, distribute, sublicense, and/or sell
15 copies of the Software, and to permit persons to whom the
16 Software is furnished to do so, subject to the following
17 conditions:
18
19 The above copyright notice and this permission notice shall be
20 included in all copies or substantial portions of the Software.
21
22 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
23 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
24 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
25 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
26 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
27 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
28 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
29 OTHER DEALINGS IN THE SOFTWARE.
30 """
31
32 __all__ = ("S3Report",
33 "S3PivotTable",
34 "S3ReportRepresent",
35 )
36
37 import datetime
38 import json
39 import os
40 import re
41 import sys
42
43 from itertools import product
44
45 from gluon import current
46 from gluon.contenttype import contenttype
47 from gluon.html import BUTTON, DIV, FIELDSET, FORM, INPUT, LABEL, LEGEND, TAG, XML
48 from gluon.languages import regex_translate
49 from gluon.sqlhtml import OptionsWidget
50 from gluon.storage import Storage
51 from gluon.validators import IS_IN_SET, IS_EMPTY_OR
52
53 from s3query import FS
54 from s3rest import S3Method
55 from s3utils import s3_flatlist, s3_has_foreign_key, s3_str, S3MarkupStripper, s3_represent_value
56 from s3xml import S3XMLFormat
57 from s3validators import IS_NUMBER, JSONERRORS
58
59 # Compact JSON encoding
60 DEFAULT = lambda: None
61 SEPARATORS = (",", ":")
62
63 LAYER = re.compile(r"([a-zA-Z]+)\((.*)\)\Z")
64 FACT = re.compile(r"([a-zA-Z]+)\(([a-zA-Z0-9_.$:\,~]+)\),*(.*)\Z")
65 SELECTOR = re.compile(r"^[a-zA-Z0-9_.$:\~]+\Z")
66
67 # =============================================================================
68 -class S3Report(S3Method):
69 """ RESTful method for pivot table reports """
70
71 # -------------------------------------------------------------------------
73 """
74 Page-render entry point for REST interface.
75
76 @param r: the S3Request instance
77 @param attr: controller attributes for the request
78 """
79
80 if r.http == "GET":
81 if r.representation == "geojson":
82 output = self.geojson(r, **attr)
83 else:
84 output = self.report(r, **attr)
85 elif r.http == "POST":
86 if r.representation == "json":
87 # NB can additionally check for ?explore=1 to
88 # distinguish from other POSTs (if necessary)
89 output = self.explore(r, **attr)
90 else:
91 r.error(415, current.ERROR.BAD_FORMAT)
92 else:
93 r.error(405, current.ERROR.BAD_METHOD)
94 return output
95
96 # -------------------------------------------------------------------------
98 """
99 Pivot table report page
100
101 @param r: the S3Request instance
102 @param attr: controller attributes for the request
103 """
104
105 output = {}
106
107 resource = self.resource
108 get_config = resource.get_config
109
110 show_filter_form = False
111 if r.representation in ("html", "iframe"):
112 filter_widgets = get_config("filter_widgets", None)
113 if filter_widgets and not self.hide_filter:
114 # Apply filter defaults (before rendering the data!)
115 from s3filter import S3FilterForm
116 show_filter_form = True
117 S3FilterForm.apply_filter_defaults(r, resource)
118
119 widget_id = "pivottable"
120
121 # @todo: make configurable:
122 maxrows = 20
123 maxcols = 20
124
125 # Extract the relevant GET vars
126 report_vars = ("rows", "cols", "fact", "totals")
127 get_vars = dict((k, v) for k, v in r.get_vars.iteritems()
128 if k in report_vars)
129
130 # Fall back to report options defaults
131 report_options = get_config("report_options", {})
132 defaults = report_options.get("defaults", {})
133
134 if not any (k in get_vars for k in ("rows", "cols", "fact")):
135 get_vars = defaults
136 get_vars["chart"] = r.get_vars.get("chart",
137 defaults.get("chart", None))
138 get_vars["table"] = r.get_vars.get("table",
139 defaults.get("table", None))
140
141 # Generate the pivot table
142 if get_vars:
143
144 rows = get_vars.get("rows", None)
145 if type(rows) is list:
146 rows = rows[-1]
147 cols = get_vars.get("cols", None)
148 if type(cols) is list:
149 cols = cols[-1]
150
151 layer = get_vars.get("fact", "id")
152 try:
153 facts = S3PivotTableFact.parse(layer)
154 except SyntaxError:
155 current.log.error(sys.exc_info()[1])
156 facts = None
157 if not facts or not any([rows, cols]):
158 pivottable = None
159 else:
160 prefix = resource.prefix_selector
161 get_vars["rows"] = prefix(rows) if rows else None
162 get_vars["cols"] = prefix(cols) if cols else None
163 get_vars["fact"] = ",".join("%s(%s)" % (fact.method, fact.selector) for fact in facts)
164
165 pivottable = S3PivotTable(resource, rows, cols, facts,
166 precision = report_options.get("precision"),
167 )
168 else:
169 pivottable = None
170
171 representation = r.representation
172 if representation in ("html", "iframe", "json"):
173
174 # Generate JSON-serializable dict
175 if pivottable is not None:
176 pivotdata = pivottable.json(maxrows=maxrows, maxcols=maxcols)
177 else:
178 pivotdata = None
179
180 if r.representation in ("html", "iframe"):
181
182 tablename = resource.tablename
183
184 # Filter widgets
185 if show_filter_form:
186 advanced = False
187 for widget in filter_widgets:
188 if not widget:
189 continue
190 if "hidden" in widget.opts and widget.opts.hidden:
191 advanced = resource.get_config("report_advanced", True)
192 break
193
194 filter_formstyle = get_config("filter_formstyle", None)
195 filter_form = S3FilterForm(filter_widgets,
196 formstyle = filter_formstyle,
197 advanced = advanced,
198 submit = False,
199 _class = "filter-form",
200 _id = "%s-filter-form" % widget_id,
201 )
202 fresource = current.s3db.resource(tablename)
203 alias = resource.alias if r.component else None
204 filter_widgets = filter_form.fields(fresource,
205 r.get_vars,
206 alias = alias,
207 )
208 else:
209 # Render as empty string to avoid the exception in the view
210 filter_widgets = None
211
212 # Generate the report form
213 ajax_vars = Storage(r.get_vars)
214 ajax_vars.update(get_vars)
215 filter_url = r.url(method = "",
216 representation = "",
217 vars = ajax_vars.fromkeys((k for k in ajax_vars
218 if k not in report_vars)))
219 ajaxurl = attr.get("ajaxurl", r.url(method = "report",
220 representation = "json",
221 vars = ajax_vars,
222 ))
223
224 output = S3ReportForm(resource).html(pivotdata,
225 get_vars = get_vars,
226 filter_widgets = filter_widgets,
227 ajaxurl = ajaxurl,
228 filter_url = filter_url,
229 widget_id = widget_id,
230 )
231
232 output["title"] = self.crud_string(tablename, "title_report")
233 output["report_type"] = "pivottable"
234
235 # Detect and store theme-specific inner layout
236 self._view(r, "pivottable.html")
237
238 # View
239 current.response.view = self._view(r, "report.html")
240
241 elif r.representation == "json":
242
243 output = json.dumps(pivotdata, separators=SEPARATORS)
244
245 elif r.representation == "xls":
246
247 if pivottable:
248
249 # Report title
250 title = self.crud_string(r.tablename, "title_report")
251 if title is None:
252 title = current.T("Report")
253
254 # TODO: include current date?
255 filename = "%s_%s.xls" % (r.env.server_name,
256 s3_str(title).replace(" ", "_"),
257 )
258 disposition = "attachment; filename=\"%s\"" % filename
259
260 # Response headers
261 response = current.response
262 response.headers["Content-Type"] = contenttype(".xls")
263 response.headers["Content-disposition"] = disposition
264
265 # Convert pivot table to XLS
266 stream = pivottable.xls(title)
267 #stream.seek(0) # already done in encoder
268 output = stream.read()
269
270 else:
271 r.error(400, "No report parameters specified")
272
273 else:
274 r.error(415, current.ERROR.BAD_FORMAT)
275
276 return output
277
278 # -------------------------------------------------------------------------
280 """
281 Render the pivot table data as a dict ready to be exported as
282 GeoJSON for display on a Map.
283
284 @param r: the S3Request instance
285 @param attr: controller attributes for the request
286 """
287
288 resource = self.resource
289 response = current.response
290 s3 = response.s3
291
292 # Set response headers
293 response.headers["Content-Type"] = s3.content_type.get("geojson",
294 "application/json")
295 if not resource.count():
296 # No Data
297 return json.dumps({})
298
299 # Extract the relevant GET vars
300 get_vars = r.get_vars
301 layer_id = r.get_vars.get("layer", None)
302 level = get_vars.get("level", "L0")
303
304 # Fall back to report options defaults
305 get_config = resource.get_config
306 report_options = get_config("report_options", {})
307 defaults = report_options.get("defaults", {})
308
309 # The rows dimension
310 context = get_config("context")
311 if context and "location" in context:
312 # @ToDo: We can add sanity-checking using resource.parse_bbox_query() as a guide if-desired
313 rows = "(location)$%s" % level
314 else:
315 # Fallback to location_id
316 rows = "location_id$%s" % level
317 # Fallback we can add if-required
318 #rows = "site_id$location_id$%s" % level
319
320 # Filter out null values
321 resource.add_filter(FS(rows) != None)
322
323 # Set XSLT stylesheet
324 stylesheet = os.path.join(r.folder, r.XSLT_PATH, "geojson", "export.xsl")
325
326 # Do we have any data at this level of aggregation?
327 fallback_to_points = True # @ToDo: deployment_setting?
328 output = None
329 if fallback_to_points:
330 if resource.count() == 0:
331 # Show Points
332 resource.clear_query()
333 # Apply URL filters (especially BBOX)
334 resource.build_query(filter=s3.filter, vars=get_vars)
335
336 # Extract the Location Data
337 xmlformat = S3XMLFormat(stylesheet)
338 include, exclude = xmlformat.get_fields(resource.tablename)
339 resource.load(fields=include,
340 skip=exclude,
341 start=0,
342 limit=None,
343 orderby=None,
344 virtual=False,
345 cacheable=True)
346 gis = current.gis
347 attr_fields = []
348 style = gis.get_style(layer_id=layer_id,
349 aggregate=False)
350 popup_format = style.popup_format
351 if popup_format:
352 if "T(" in popup_format:
353 # i18n
354 T = current.T
355 items = regex_translate.findall(popup_format)
356 for item in items:
357 titem = str(T(item[1:-1]))
358 popup_format = popup_format.replace("T(%s)" % item,
359 titem)
360 style.popup_format = popup_format
361 # Extract the attr_fields
362 parts = popup_format.split("{")
363 # Skip the first part
364 parts = parts[1:]
365 for part in parts:
366 attribute = part.split("}")[0]
367 attr_fields.append(attribute)
368 attr_fields = ",".join(attr_fields)
369
370 location_data = gis.get_location_data(resource,
371 attr_fields=attr_fields)
372
373 # Export as GeoJSON
374 current.xml.show_ids = True
375 output = resource.export_xml(fields=include,
376 mcomponents=None,
377 references=[],
378 stylesheet=stylesheet,
379 as_json=True,
380 location_data=location_data,
381 map_data=dict(style=style),
382 )
383 # Transformation error?
384 if not output:
385 r.error(400, "XSLT Transformation Error: %s " % current.xml.error)
386
387 else:
388 while resource.count() == 0:
389 # Try a lower level of aggregation
390 level = int(level[1:])
391 if level == 0:
392 # Nothing we can display
393 return json.dumps({})
394 resource.clear_query()
395 # Apply URL filters (especially BBOX)
396 resource.build_query(filter=s3.filter, vars=get_vars)
397 level = "L%s" % (level - 1)
398 if context and "location" in context:
399 # @ToDo: We can add sanity-checking using resource.parse_bbox_query() as a guide if-desired
400 rows = "(location)$%s" % level
401 else:
402 # Fallback to location_id
403 rows = "location_id$%s" % level
404 # Fallback we can add if-required
405 #rows = "site_id$location_id$%s" % level
406 resource.add_filter(FS(rows) != None)
407
408 if not output:
409 # Build the Pivot Table
410 cols = None
411 layer = get_vars.get("fact", defaults.get("fact", "count(id)"))
412 facts = S3PivotTableFact.parse(layer)[:1]
413 pivottable = S3PivotTable(resource, rows, cols, facts,
414 precision = report_options.get("precision"),
415 )
416
417 # Extract the Location Data
418 #attr_fields = []
419 style = current.gis.get_style(layer_id=layer_id,
420 aggregate=True)
421 popup_format = style.popup_format
422 if popup_format:
423 if"T(" in popup_format:
424 # i18n
425 T = current.T
426 items = regex_translate.findall(popup_format)
427 for item in items:
428 titem = str(T(item[1:-1]))
429 popup_format = popup_format.replace("T(%s)" % item,
430 titem)
431 style.popup_format = popup_format
432 # Extract the attr_fields
433 # No need as defaulted inside S3PivotTable.geojson()
434 #parts = popup_format.split("{")
435 ## Skip the first part
436 #parts = parts[1:]
437 #for part in parts:
438 # attribute = part.split("}")[0]
439 # attr_fields.append(attribute)
440 #attr_fields = ",".join(attr_fields)
441
442 ids, location_data = pivottable.geojson(fact=facts[0], level=level)
443
444 # Export as GeoJSON
445 current.xml.show_ids = True
446 gresource = current.s3db.resource("gis_location", id=ids)
447 output = gresource.export_xml(fields=[],
448 mcomponents=None,
449 references=[],
450 stylesheet=stylesheet,
451 as_json=True,
452 location_data=location_data,
453 # Tell the client that we are
454 # displaying aggregated data and
455 # the level it is aggregated at
456 map_data=dict(level=int(level[1:]),
457 style=style),
458 )
459 # Transformation error?
460 if not output:
461 r.error(400, "XSLT Transformation Error: %s " % current.xml.error)
462
463 return output
464
465 # -------------------------------------------------------------------------
467 """
468 Pivot table report widget
469
470 @param r: the S3Request
471 @param method: the widget method
472 @param widget_id: the widget ID
473 @param visible: whether the widget is initially visible
474 @param attr: controller attributes
475 """
476
477 output = {}
478
479 resource = self.resource
480 get_config = resource.get_config
481
482 # @todo: make configurable:
483 maxrows = 20
484 maxcols = 20
485
486 # Extract the relevant GET vars
487 report_vars = ("rows", "cols", "fact", "totals")
488 get_vars = dict((k, v) for k, v in r.get_vars.iteritems()
489 if k in report_vars)
490
491 # Fall back to report options defaults
492 report_options = get_config("report_options", {})
493 defaults = report_options.get("defaults", {})
494
495 if not any (k in get_vars for k in ("rows", "cols", "fact")):
496 get_vars = defaults
497 get_vars["chart"] = r.get_vars.get("chart",
498 defaults.get("chart", None))
499 get_vars["table"] = r.get_vars.get("table",
500 defaults.get("table", None))
501
502 # Generate the pivot table
503 if get_vars:
504
505 rows = get_vars.get("rows", None)
506 cols = get_vars.get("cols", None)
507
508 layer = get_vars.get("fact", "id")
509 try:
510 facts = S3PivotTableFact.parse(layer)
511 except SyntaxError:
512 current.log.error(sys.exc_info()[1])
513 facts = None
514 if not facts or not any([rows, cols]):
515 pivottable = None
516 else:
517 prefix = resource.prefix_selector
518 get_vars["rows"] = prefix(rows) if rows else None
519 get_vars["cols"] = prefix(cols) if cols else None
520 get_vars["fact"] = ",".join("%s(%s)" % (fact.method, fact.selector) for fact in facts)
521
522 if visible:
523 pivottable = S3PivotTable(resource, rows, cols, facts,
524 precision = report_options.get("precision"),
525 )
526 else:
527 pivottable = None
528 else:
529 pivottable = None
530
531 # Render as JSON-serializable dict
532 if pivottable is not None:
533 pivotdata = pivottable.json(maxrows=maxrows, maxcols=maxcols)
534 else:
535 pivotdata = None
536
537 if r.representation in ("html", "iframe"):
538
539 # Generate the report form
540 ajax_vars = Storage(r.get_vars)
541 ajax_vars.update(get_vars)
542 filter_form = attr.get("filter_form", None)
543 filter_tab = attr.get("filter_tab", None)
544 filter_url = r.url(method="",
545 representation="",
546 vars=ajax_vars.fromkeys((k for k in ajax_vars
547 if k not in report_vars)),
548 )
549 ajaxurl = attr.get("ajaxurl", r.url(method="report",
550 representation="json",
551 vars=ajax_vars))
552 output = S3ReportForm(resource).html(pivotdata,
553 get_vars = get_vars,
554 filter_widgets = None,
555 ajaxurl = ajaxurl,
556 filter_url = filter_url,
557 filter_form = filter_form,
558 filter_tab = filter_tab,
559 widget_id = widget_id)
560
561 # Detect and store theme-specific inner layout
562 view = self._view(r, "pivottable.html")
563
564 # Render inner layout (outer page layout is set by S3Summary)
565 output["title"] = None
566 output = XML(current.response.render(view, output))
567
568 else:
569 r.error(415, current.ERROR.BAD_FORMAT)
570
571 return output
572
573 # -------------------------------------------------------------------------
575 """
576 Ajax-lookup of representations for items contributing to the
577 aggregate value in a pivot table cell (cell explore)
578 - called with a body JSON containing the record IDs to represent,
579 and the URL params for the pivot table (rows, cols, fact)
580
581 @param r: the S3Request instance
582 @param attr: controller attributes for the request
583 """
584
585 # Read+parse body JSON
586 s = r.body
587 s.seek(0)
588 try:
589 record_ids = json.load(s)
590 except JSONERRORS:
591 record_ids = None
592
593 # Must be a list of record IDs
594 if not isinstance(record_ids, list):
595 r.error(404, current.ERROR.BAD_RECORD)
596
597 # Create filtered resource
598 resource = current.s3db.resource(self.tablename, id=record_ids)
599
600 prefix = resource.prefix_selector
601 pkey = prefix(resource._id.name)
602 pkey_colname = str(resource._id)
603
604 # Parse the facts
605 get_vars = r.get_vars
606 facts = S3PivotTableFact.parse(get_vars.get("fact"))
607
608 selectors = set() # all fact selectors other than "id"
609 ofacts = [] # all facts other than "count(id)"
610
611 for fact in facts:
612 selector = prefix(fact.selector)
613 is_pkey = selector == pkey
614 if not is_pkey:
615 selectors.add(selector)
616 if not is_pkey or fact.method != "count":
617 ofacts.append(fact)
618
619 # Extract the data
620 if len(selectors):
621 selectors.add(pkey)
622 records = resource.select(selectors,
623 raw_data = True,
624 represent = True,
625 limit = None,
626 ).rows
627 else:
628 # All we need is the record IDs, so skip the select and
629 # construct some pseudo-rows
630 records = []
631 for record_id in record_ids:
632 record = Storage({pkey_colname: record_id})
633 record._row = record
634 records.append(record)
635
636 # Get the record representation method and initialize it with the
637 # report context (rows, cols, facts)
638 represent = resource.get_config("report_represent", S3ReportRepresent)
639 if represent:
640 rows = get_vars.get("rows")
641 cols = get_vars.get("cols")
642 represent = represent(resource, rows=rows, cols=cols, facts=facts)
643
644 # Determine what the items list should contain
645
646 rfields = {} # resolved fact selectors
647
648 key = None
649 aggregate = True
650 if len(ofacts) == 1:
651
652 fact = ofacts[0]
653
654 if fact.method == "count":
655
656 # When counting foreign keys in the master record, then
657 # show a list of all unique values of that foreign key
658 # rather than the number of unique values per master
659 # record (as that would always be 1)
660
661 selector = prefix(fact.selector)
662 rfield = resource.resolve_selector(selector)
663 field = rfield.field
664 if field and s3_has_foreign_key(field):
665 multiple = True
666 if rfield.tname == resource.tablename or \
667 selector[:2] == "~." and "." not in selector[2:]:
668 multiple = False
669 else:
670 # Get the component prefix
671 alias = selector.split("$", 1)[0].split(".", 1)[0]
672 component = resource.components.get(alias)
673 if component:
674 multiple = component.multiple
675
676 if not multiple:
677 represent = None
678 key = rfield.colname
679 aggregate = False
680 rfields[selector] = rfield
681
682 # Get the record representations
683 records_repr = represent(record_ids) if represent else None
684
685 # Build the output items (as dict, will be alpha-sorted on client-side)
686 output = {}
687 UNKNOWN_OPT = current.messages.UNKNOWN_OPT
688 for record in records:
689
690 raw = record._row
691 record_id = raw[pkey_colname]
692
693 values = []
694 for fact in ofacts:
695
696 # Resolve the selector
697 selector = prefix(fact.selector)
698 rfield = rfields.get(selector)
699 if not rfield:
700 rfield = rfields[selector] = resource.resolve_selector(selector)
701
702 # Get the value, sub-aggregate
703 if aggregate:
704 value = raw[rfield.colname]
705 if type(value) is list:
706 value = fact.compute(value)
707 else:
708 value = fact.compute([value])
709 if fact.method != "count":
710 field = rfield.field
711 if field and field.represent:
712 value = field.represent(value)
713 else:
714 value = record[rfield.colname]
715
716 # Extend values list
717 if len(values):
718 values.extend([" / ", value])
719 else:
720 values.append(value)
721
722 repr_items = [TAG[""](values)] if values else []
723
724 # Add the record representation
725 if records_repr is not None:
726 repr_items.insert(0, records_repr.get(record_id, UNKNOWN_OPT))
727 if len(repr_items) == 2:
728 repr_items.insert(1, ": ")
729
730 # Build output item
731 # - using TAG not str.join() to allow representations to contain
732 # XML helpers like A, SPAN or DIV
733 repr_str = TAG[""](repr_items).xml()
734 if key:
735 # Include raw field value for client-side de-duplication
736 output[record_id] = [repr_str, s3_str(raw[key])]
737 else:
738 output[record_id] = repr_str
739
740 current.response.headers["Content-Type"] = "application/json"
741 return json.dumps(output, separators=SEPARATORS)
742
743 # -------------------------------------------------------------------------
744 @staticmethod
746 """
747 Re-usable helper function to inject D3/NVD3 scripts
748 into the current page
749 """
750
751 appname = current.request.application
752 s3 = current.response.s3
753
754 scripts_append = s3.scripts.append
755 if s3.debug:
756 if s3.cdn:
757 scripts_append("https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.17/d3.js")
758 # We use a patched v1.8.5 currently, so can't use the CDN version
759 #scripts_append("https://cdnjs.cloudflare.com/ajax/libs/nvd3/1.8.5/nv.d3.js")
760 else:
761 scripts_append("/%s/static/scripts/d3/d3.js" % appname)
762 scripts_append("/%s/static/scripts/d3/nv.d3.js" % appname)
763 else:
764 if s3.cdn:
765 scripts_append("https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.17/d3.min.js")
766 # We use a patched v1.8.5 currently, so can't use the CDN version
767 #scripts_append("https://cdnjs.cloudflare.com/ajax/libs/nvd3/1.8.5/nv.d3.min.js")
768 else:
769 scripts_append("/%s/static/scripts/d3/d3.min.js" % appname)
770 scripts_append("/%s/static/scripts/d3/nv.d3.min.js" % appname)
771
772 # =============================================================================
773 -class S3ReportForm(object):
774 """ Helper class to render a report form """
775
780
781 # -------------------------------------------------------------------------
782 - def html(self,
783 pivotdata,
784 filter_widgets=None,
785 get_vars=None,
786 ajaxurl=None,
787 filter_url=None,
788 filter_form=None,
789 filter_tab=None,
790 widget_id=None):
791 """
792 Render the form for the report
793
794 @param get_vars: the GET vars if the request (as dict)
795 @param widget_id: the HTML element base ID for the widgets
796 """
797
798 T = current.T
799 appname = current.request.application
800
801 # Report options
802 report_options = self.report_options(get_vars = get_vars,
803 widget_id = widget_id,
804 )
805
806 # Pivot data
807 hidden = {"pivotdata": json.dumps(pivotdata, separators=SEPARATORS)}
808
809 empty = T("No report specified.")
810 hide = T("Hide Table")
811 show = T("Show Table")
812
813 throbber = "/%s/static/img/indicator.gif" % appname
814
815 # Filter options
816 if filter_widgets is not None:
817 filter_options = self._fieldset(T("Filter Options"),
818 filter_widgets,
819 _id="%s-filters" % widget_id,
820 _class="filter-form")
821 else:
822 filter_options = ""
823
824 # Report form submit element
825 resource = self.resource
826 submit = resource.get_config("report_submit", True)
827 if submit:
828 _class = "pt-submit"
829 if submit is True:
830 label = T("Update Report")
831 elif isinstance(submit, (list, tuple)):
832 label = submit[0]
833 _class = "%s %s" % (submit[1], _class)
834 else:
835 label = submit
836 submit = TAG[""](
837 INPUT(_type="button",
838 _value=label,
839 _class=_class))
840 else:
841 submit = ""
842
843 # Form
844 form = FORM(filter_options,
845 report_options,
846 submit,
847 hidden = hidden,
848 _class = "pt-form",
849 _id = "%s-pt-form" % widget_id,
850 )
851
852 # View variables
853 output = {"form": form,
854 "throbber": throbber,
855 "hide": hide,
856 "show": show,
857 "empty": empty,
858 "widget_id": widget_id,
859 }
860
861 # Script options
862 settings = current.deployment_settings
863 opts = {
864 #"renderFilter": True,
865 #"collapseFilter": False,
866
867 #"renderOptions": True,
868 "collapseOptions": settings.get_ui_hide_report_options(),
869
870 "renderTable": True,
871 "collapseTable": False,
872 "showTotals": self.show_totals,
873
874 "ajaxURL": ajaxurl,
875
876 "renderChart": True,
877 "collapseChart": True,
878 "defaultChart": None,
879
880 "exploreChart": True,
881 "filterURL": filter_url,
882 "filterTab": filter_tab,
883 "filterForm": filter_form,
884
885 "autoSubmit": settings.get_ui_report_auto_submit(),
886 "timeout": settings.get_ui_report_timeout(),
887
888 "thousandSeparator": settings.get_L10n_thousands_separator(),
889 "thousandGrouping": settings.get_L10n_thousands_grouping(),
890 "textAll": str(T("All")),
891 "textRecords": str(T("Records")),
892 }
893 chart_opt = get_vars["chart"]
894 if chart_opt is not None:
895 if str(chart_opt).lower() in ("0", "off", "false"):
896 opts["renderChart"] = False
897 elif ":" in chart_opt:
898 opts["collapseChart"] = False
899 ctype, caxis = chart_opt.split(":", 1)
900 opts["defaultChart"] = {"type": ctype, "axis": caxis}
901
902 table_opt = get_vars["table"]
903 if table_opt is not None:
904 table_opt = str(table_opt).lower()
905 if table_opt in ("0", "off", "false"):
906 opts["renderTable"] = False
907 elif table_opt == "collapse":
908 opts["collapseTable"] = True
909
910 # Scripts
911 S3Report.inject_d3()
912 s3 = current.response.s3
913 scripts = s3.scripts
914 if s3.debug:
915 script = "/%s/static/scripts/S3/s3.ui.pivottable.js" % appname
916 if script not in scripts:
917 scripts.append(script)
918 else:
919 script = "/%s/static/scripts/S3/s3.ui.pivottable.min.js" % appname
920 if script not in scripts:
921 scripts.append(script)
922
923 # Instantiate widget
924 script = '''$('#%(widget_id)s').pivottable(%(opts)s)''' % \
925 {"widget_id": widget_id,
926 "opts": json.dumps(opts,
927 separators=SEPARATORS,
928 ),
929 }
930 s3.jquery_ready.append(script)
931
932 return output
933
934 # -------------------------------------------------------------------------
936 """
937 Render the widgets for the report options form
938
939 @param get_vars: the GET vars if the request (as dict)
940 @param widget_id: the HTML element base ID for the widgets
941 """
942
943 T = current.T
944
945 SHOW_TOTALS = T("Show totals")
946 REPORT = T("Report of")
947 ROWS = T("Grouped by")
948 COLS = T("and")
949
950 resource = self.resource
951 get_config = resource.get_config
952 options = get_config("report_options")
953
954 # Specific formstyle?
955 settings = current.deployment_settings
956 formstyle = settings.get_ui_report_formstyle()
957 # Fall back to inline-variant of current formstyle
958 if formstyle is None:
959 formstyle = settings.get_ui_inline_formstyle()
960
961 # Helper for labels
962 label = lambda s, **attr: LABEL("%s:" % s, **attr)
963
964 formfields = []
965
966 # Layer selector
967 layer_id = "%s-fact" % widget_id
968 layer_widget = self.layer_options(options=options,
969 get_vars=get_vars,
970 widget_id=layer_id)
971 formfields.append((layer_id + "-row",
972 label(REPORT, _for=layer_id),
973 layer_widget,
974 "",
975 ))
976
977 # Rows/Columns selectors
978 axis_options = self.axis_options
979 rows_id = "%s-rows" % widget_id
980 cols_id = "%s-cols" % widget_id
981 rows_options = axis_options("rows",
982 options=options,
983 get_vars=get_vars,
984 widget_id=rows_id)
985 cols_options = axis_options("cols",
986 options=options,
987 get_vars=get_vars,
988 widget_id=cols_id)
989 axis_widget = DIV(rows_options,
990 label(COLS, _for=cols_id),
991 cols_options,
992 _class="pt-axis-options",
993 )
994 formfields.append(("%s-axis-row" % widget_id,
995 label(ROWS, _for=rows_id),
996 axis_widget,
997 "",
998 ))
999
1000 # Show Totals switch
1001 show_totals = True
1002 if get_vars and "totals" in get_vars and \
1003 str(get_vars["totals"]).lower() in ("0", "false", "off"):
1004 show_totals = False
1005 self.show_totals = show_totals
1006
1007 show_totals_id = "%s-totals" % widget_id
1008 totals_widget = INPUT(_type="checkbox",
1009 _id=show_totals_id,
1010 _name="totals",
1011 _class="pt-totals",
1012 value=show_totals
1013 )
1014
1015 formfields.append(("%s-show-totals-row" % widget_id,
1016 label(SHOW_TOTALS, _for=show_totals_id),
1017 totals_widget,
1018 "",
1019 ))
1020
1021 try:
1022 widgets = formstyle(FIELDSET(), formfields)
1023 except:
1024 # Old style (should be avoided)
1025 widgets = TAG[""]([formstyle(*formfield) for formfield in formfields])
1026
1027 # Render fieldset
1028 fieldset = self._fieldset(T("Report Options"),
1029 widgets,
1030 _id="%s-options" % widget_id,
1031 _class="report-options")
1032
1033 return fieldset
1034
1035 # -------------------------------------------------------------------------
1040 """
1041 Construct an OptionsWidget for rows or cols axis
1042
1043 @param axis: "rows" or "cols"
1044 @param options: the report options
1045 @param get_vars: the GET vars if the request (as dict)
1046 @param widget_id: the HTML element ID for the widget
1047 """
1048
1049 resource = self.resource
1050 prefix = resource.prefix_selector
1051
1052 # Get all selectors
1053 if options and axis in options:
1054 fields = options[axis]
1055 else:
1056 fields = resource.get_config("list_fields")
1057 if not fields:
1058 fields = [f.name for f in resource.readable_fields()]
1059
1060 # Resolve the selectors
1061 pkey = str(resource._id)
1062 resolve_selector = resource.resolve_selector
1063 rfields = []
1064 append = rfields.append
1065 for f in fields:
1066 if not f:
1067 continue
1068 elif isinstance(f, (tuple, list)):
1069 label, selector = f[:2]
1070 else:
1071 label, selector = None, f
1072 rfield = resolve_selector(selector)
1073 if rfield.colname == pkey:
1074 continue
1075 if label:
1076 rfield.label = label
1077 append(rfield)
1078
1079 # Get current value
1080 if get_vars and axis in get_vars:
1081 value = get_vars[axis]
1082 else:
1083 value = ""
1084 if value:
1085 value = prefix(value)
1086
1087 # Dummy field
1088 opts = [(prefix(rfield.selector), rfield.label) for rfield in rfields]
1089 dummy_field = Storage(name=axis, requires=IS_IN_SET(opts))
1090
1091 # Construct widget
1092 return OptionsWidget.widget(dummy_field,
1093 value,
1094 _id=widget_id,
1095 _name=axis,
1096 _class="pt-%s" % axis)
1097
1098 # -------------------------------------------------------------------------
1103 """
1104 Construct an OptionsWidget for the fact layer
1105
1106 @param options: the report options
1107 @param get_vars: the GET vars if the request (as dict)
1108 @param widget_id: the HTML element ID for the widget
1109 """
1110
1111 resource = self.resource
1112
1113 all_methods = S3PivotTableFact.METHODS
1114
1115 # Get all layers
1116 layers = None
1117 methods = None
1118 if options:
1119 if "methods" in options:
1120 methods = options["methods"]
1121 if "fact" in options:
1122 layers = options["fact"]
1123 if not layers:
1124 layers = resource.get_config("list_fields")
1125 if not layers:
1126 layers = [f.name for f in resource.readable_fields()]
1127 if not methods:
1128 methods = all_methods
1129
1130 # Resolve layer options
1131 T = current.T
1132 RECORDS = T("Records")
1133 mname = S3PivotTableFact._get_method_label
1134
1135 def layer_label(rfield, method):
1136 """ Helper to construct a layer label """
1137 mlabel = mname(method)
1138 flabel = rfield.label if rfield.label != "Id" else RECORDS
1139 # @ToDo: Exclude this string from admin/translate exports
1140 return T("%s (%s)") % (flabel, mlabel)
1141
1142 prefix = resource.prefix_selector
1143
1144 layer_opts = []
1145 for option in layers:
1146
1147 if not option:
1148 continue
1149 elif isinstance(option, tuple):
1150 title, layer = option
1151 else:
1152 title, layer = None, option
1153
1154 try:
1155 facts = S3PivotTableFact.parse(layer)
1156 except SyntaxError:
1157 continue
1158
1159 if len(facts) > 1:
1160 # Multi-fact layer
1161 labels = []
1162 expressions = []
1163 for fact in facts:
1164 if not title:
1165 rfield = resource.resolve_selector(fact.selector)
1166 labels.append(fact.get_label(rfield, layers))
1167 expressions.append("%s(%s)" % (fact.method, fact.selector))
1168 if not title:
1169 title = " / ".join(labels)
1170 layer_opts.append((",".join(expressions), title))
1171 continue
1172 else:
1173 fact = facts[0]
1174
1175 label = fact.label or title
1176 if fact.default_method:
1177 s, m = fact.selector, None
1178 else:
1179 s, m = fact.selector, fact.method
1180
1181 # Resolve the selector
1182 selector = prefix(s)
1183 rfield = resource.resolve_selector(selector)
1184 if not rfield.field and not rfield.virtual:
1185 continue
1186 if m is None and label:
1187 rfield.label = label
1188
1189 if m is None:
1190 # Only field given -> auto-detect aggregation methods
1191 is_amount = None
1192 ftype = rfield.ftype
1193 if ftype == "integer":
1194 is_amount = True
1195 requires = rfield.requires
1196 if not isinstance(requires, (list, tuple)):
1197 requires = [requires]
1198 for r in requires:
1199 if isinstance(r, IS_IN_SET) or \
1200 isinstance(r, IS_EMPTY_OR) and \
1201 isinstance(r.other, IS_IN_SET):
1202 is_amount = False
1203 elif ftype == "double":
1204 is_amount = True
1205 elif ftype[:9] == "reference" or \
1206 ftype[:5] == "list:" or \
1207 ftype in ("id", "string", "text"):
1208 is_amount = False
1209 if ftype in ("datetime", "date", "time"):
1210 mopts = ["min", "max", "list"]
1211 elif is_amount is None:
1212 mopts = ["sum", "min", "max", "avg", "count", "list"]
1213 elif is_amount:
1214 mopts = ["sum", "min", "max", "avg"]
1215 else:
1216 mopts = ["count", "list"]
1217 for method in mopts:
1218 if method in methods:
1219 label = layer_label(rfield, method)
1220 layer_opts.append(("%s(%s)" % (method, selector), label))
1221 else:
1222 # Explicit method specified
1223 if label is None:
1224 label = layer_label(rfield, m)
1225 layer_opts.append(("%s(%s)" % (m, selector), label))
1226
1227 # Get current value
1228 if get_vars and "fact" in get_vars:
1229 layer = get_vars["fact"]
1230 else:
1231 layer = ""
1232 if layer:
1233 match = LAYER.match(layer)
1234 if match is None:
1235 layer = ""
1236 else:
1237 selector, method = match.group(2), match.group(1)
1238 selector = prefix(selector)
1239 layer = "%s(%s)" % (method, selector)
1240
1241 if len(layer_opts) == 1:
1242 # Field is read-only if there is only 1 option
1243 default = layer_opts[0]
1244 widget = TAG[""](default[1],
1245 INPUT(_type="hidden",
1246 _id=widget_id,
1247 _name=widget_id,
1248 _value=default[0],
1249 _class="pt-fact-single-option"))
1250 else:
1251 # Render Selector
1252 dummy_field = Storage(name="fact",
1253 requires=IS_IN_SET(layer_opts))
1254 widget = OptionsWidget.widget(dummy_field,
1255 layer,
1256 _id=widget_id,
1257 _name="fact",
1258 _class="pt-fact")
1259
1260 return widget
1261
1262 # -------------------------------------------------------------------------
1263 @staticmethod
1265 """
1266 Helper method to wrap widgets in a FIELDSET container with
1267 show/hide option
1268
1269 @param title: the title for the field set
1270 @param widgets: the widgets
1271 @param attr: HTML attributes for the field set
1272 """
1273
1274 T = current.T
1275 SHOW = T("Show")
1276 HIDE = T("Hide")
1277
1278 return FIELDSET(LEGEND(title,
1279 BUTTON(SHOW,
1280 _type="button",
1281 _class="toggle-text",
1282 ),
1283 BUTTON(HIDE,
1284 _type="button",
1285 _class="toggle-text",
1286 )
1287 ),
1288 widgets,
1289 **attr)
1290
1291 # =============================================================================
1292 -class S3ReportRepresent(object):
1293 """
1294 Method to represent the contributing records in a pivot table
1295 cell (cell explore)
1296
1297 The cell-explore record list will typically look like:
1298 - <record representation>: <fact value(s)>
1299 - ...
1300 This method controls the first part of each entry.
1301
1302 For customization, configure for the table as:
1303 report_represent = <subclass>
1304 """
1305
1307 """
1308 Constructor, initializes the method with the report context
1309 to allow it to adapt the representation (e.g. it may often
1310 be desirable to not repeat the report axes in the record list)
1311
1312 @param resource: the resource of the report
1313 @param rows: the rows-selector (can be None)
1314 @param cols: the columns-selector (can be None)
1315 @param facts: the list of S3PivotTableFacts showing in
1316 the pivot table
1317 """
1318
1319 self.resource = resource
1320 self.rows = rows
1321 self.cols = cols
1322 self.facts = facts
1323
1324 # -------------------------------------------------------------------------
1326 """
1327 Represent record IDs, can be overloaded in subclasses
1328
1329 @param record_ids: list of record IDs
1330
1331 @returns: a JSON-serializable dict {recordID: representation},
1332 or None to suppress recordID representation in the
1333 cell explorer
1334
1335 NB default behavior is not sensitive for report axes
1336 """
1337
1338 # Take a list of record ids
1339 resource = self.resource
1340 table = resource.table
1341
1342 represent = self.repr_method()
1343 if represent:
1344 if hasattr(represent, "bulk"):
1345 # Bulk-represent the record IDs
1346 output = represent.bulk(record_ids)
1347 else:
1348 # Represent the record IDs one by one
1349 output = {record_id: represent(record_id)
1350 for record_id in record_ids}
1351
1352 elif "name" in table.fields:
1353 # Extract the names and return dict {id: name}
1354 query = table._id.belongs(record_ids)
1355 rows = current.db(query).select(table._id, table.name)
1356
1357 output = {}
1358 UNKNOWN_OPT = current.messages.UNKNOWN_OPT
1359 for row in rows:
1360 name = row.name
1361 if not name:
1362 name = UNKNOWN_OPT
1363 output[row[table._id]] = s3_str(row.name)
1364 else:
1365 # No reasonable default
1366
1367 # Render as record IDs (just as useful as nothing):
1368 #output = {record_id: s3_str(record_id) for record_id in record_ids}
1369
1370 # Return None to reduces the list to the fact values
1371 # NB if fact is ID, this will suppress the record list
1372 # altogether and show the number of records instead
1373 output = None
1374
1375 return output
1376
1377 # -------------------------------------------------------------------------
1379 """
1380 Return a representation method for the id-field of
1381 self.resource, can be overloaded in subclasses (simpler
1382 than implementing __call__ if producing a representation
1383 method is sufficient)
1384
1385 @returns: a representation method (preferrably a S3Represent)
1386 """
1387
1388 s3db = current.s3db
1389
1390 resource = self.resource
1391 pkey = resource._id
1392
1393 represent = pkey.represent
1394 if not represent:
1395 # Standard representation methods can be listed here
1396 # (if they don't normally depend on the report context)
1397 if resource.tablename == "pr_person":
1398 represent = s3db.pr_PersonRepresent()
1399
1400 return represent
1401
1402 # =============================================================================
1403 -class S3PivotTableFact(object):
1404 """ Class representing a fact layer """
1405
1406 #: Supported aggregation methods
1407 METHODS = {"list": "List",
1408 "count": "Count",
1409 "min": "Minimum",
1410 "max": "Maximum",
1411 "sum": "Total",
1412 "avg": "Average",
1413 #"std": "Standard Deviation"
1414 }
1415
1416
1418 """
1419 Constructor
1420
1421 @param method: the aggregation method
1422 @param selector: the field selector
1423 @param label: the fact label
1424 @param default_method: using default method (used by parser)
1425 """
1426
1427 if method is None:
1428 method = "count"
1429 default_method = True
1430 if method not in self.METHODS:
1431 raise SyntaxError("Unsupported aggregation function: %s" % method)
1432
1433 self.method = method
1434 self.selector = selector
1435
1436 self._layer = None
1437 self.label = label
1438
1439 self.resource = None
1440 self.rfield = None
1441 self.column = selector
1442
1443 self.default_method = default_method
1444
1445 # -------------------------------------------------------------------------
1446 @property
1448 """
1449 @todo: docstring
1450 """
1451
1452 layer = self._layer
1453 if not layer:
1454 layer = self._layer = (self.selector, self.method)
1455 return layer
1456
1457 # -------------------------------------------------------------------------
1459 """
1460 Aggregate a list of values.
1461
1462 @param values: iterable of values
1463 @param method: the aggregation method
1464 @param totals: this call is computing row/column/grand totals
1465 @param precision: limit the precision of the computation to this
1466 number of decimals (@todo: consider a default of 6)
1467 """
1468
1469 if values is None:
1470 return None
1471
1472 if method is DEFAULT:
1473 method = self.method
1474 if totals and method == "list":
1475 method = "count"
1476
1477 if method is None or method == "list":
1478 return values if values else None
1479
1480 if method == "count":
1481 # Count all non-null values
1482 return len([v for v in values if v is not None])
1483 else:
1484 # Numeric values required - some virtual fields
1485 # return '-' for None, so must type-check here:
1486 values = [v for v in values if isinstance(v, (int, long, float))]
1487
1488 if method == "min":
1489 try:
1490 result = min(values)
1491 except (TypeError, ValueError):
1492 return None
1493
1494 elif method == "max":
1495 try:
1496 result = max(values)
1497 except (TypeError, ValueError):
1498 return None
1499
1500 elif method == "sum":
1501 try:
1502 result = sum(values)
1503 except (TypeError, ValueError):
1504 return None
1505
1506 elif method == "avg":
1507 try:
1508 number = len(values)
1509 if number:
1510 result = sum(values) / float(number)
1511 else:
1512 return 0.0
1513 except (TypeError, ValueError):
1514 return None
1515
1516 #elif method == "std":
1517 #import numpy
1518 #if not values:
1519 #return 0.0
1520 #try:
1521 #result = numpy.std(values)
1522 #except (TypeError, ValueError):
1523 #return None
1524
1525 if type(result) is float and precision is not None:
1526 return round(result, precision)
1527 else:
1528 return result
1529
1530 return None
1531
1532 # -------------------------------------------------------------------------
1534 """
1535 Aggregate totals for this fact (hyper-aggregation)
1536
1537 @param totals: iterable of totals
1538 """
1539
1540 if self.method in ("list", "count"):
1541 total = self.compute(totals, method="sum")
1542 else:
1543 total = self.compute(totals)
1544 return total
1545
1546 # -------------------------------------------------------------------------
1547 @classmethod
1549 """
1550 Parse fact expression
1551
1552 @param fact: the fact expression
1553 """
1554
1555 if isinstance(fact, tuple):
1556 label, fact = fact
1557 else:
1558 label = None
1559
1560 if isinstance(fact, list):
1561 facts = []
1562 for f in fact:
1563 facts.extend(cls.parse(f))
1564 if not facts:
1565 raise SyntaxError("Invalid fact expression: %s" % fact)
1566 return facts
1567
1568 # Parse the fact
1569 other = None
1570 default_method = False
1571 if not fact:
1572 method, parameters = "count", "id"
1573 else:
1574 match = FACT.match(fact)
1575 if match:
1576 method, parameters, other = match.groups()
1577 if other:
1578 other = cls.parse((label, other) if label else other)
1579 elif SELECTOR.match(fact):
1580 method, parameters, other = "count", fact, None
1581 default_method = True
1582 else:
1583 raise SyntaxError("Invalid fact expression: %s" % fact)
1584
1585 # Validate method
1586 if method not in cls.METHODS:
1587 raise SyntaxError("Unsupported aggregation method: %s" % method)
1588
1589 # Extract parameters
1590 parameters = parameters.split(",")
1591
1592 selector = parameters[0]
1593
1594 facts = [cls(method,
1595 selector,
1596 label=label,
1597 default_method=default_method,
1598 ),
1599 ]
1600 if other:
1601 facts.extend(other)
1602 return facts
1603
1604 # -------------------------------------------------------------------------
1605 @classmethod
1607 """
1608 Get a label for a method
1609
1610 @param code: the method code
1611 @return: the label (lazyT), or None for unsupported methods
1612 """
1613
1614 methods = cls.METHODS
1615
1616 if code is None:
1617 code = "list"
1618 if code in methods:
1619 return current.T(methods[code])
1620 else:
1621 return None
1622
1623 # -------------------------------------------------------------------------
1624 @staticmethod
1626 """
1627 Get the label for a field
1628
1629 @param rfield: the S3ResourceField
1630 @param fact_options: the corresponding subset of the report
1631 options ("fact", "rows" or "cols")
1632 """
1633
1634 label = None
1635
1636 if not rfield:
1637 return ""
1638 resource = rfield.resource
1639
1640 fields = list(fact_options) if fact_options else []
1641
1642 list_fields = resource.get_config("list_fields")
1643 if list_fields:
1644 fields.extend(list_fields)
1645
1646 prefix = resource.prefix_selector
1647
1648 # Search through the field labels in report options
1649 selector = prefix(rfield.selector)
1650 for f in fields:
1651 if type(f) is tuple and \
1652 isinstance(f[1], basestring) and \
1653 prefix(f[1]) == selector:
1654 label = f[0]
1655 break
1656
1657 if not label and rfield:
1658 if rfield.ftype == "id":
1659 label = current.T("Records")
1660 else:
1661 label = rfield.label
1662
1663 return label if label else ""
1664
1665 # -------------------------------------------------------------------------
1667 """
1668 Get a label for this fact
1669
1670 @param rfield: the S3ResourceField
1671 @param fact_options: the "fact" list of the report options
1672 """
1673
1674 label = self.label
1675 if label:
1676 # Already set
1677 return label
1678
1679
1680 if fact_options:
1681 # Lookup the label from the fact options
1682 prefix = rfield.resource.prefix_selector
1683 for fact_option in fact_options:
1684 facts = self.parse(fact_option)
1685 for fact in facts:
1686 if fact.method == self.method and \
1687 prefix(fact.selector) == prefix(self.selector):
1688 label = fact.label
1689 break
1690 if label:
1691 break
1692
1693 if not label:
1694 # Construct a label from the field label and the method name
1695 field_label = self._get_field_label(rfield, fact_options)
1696 method_label = self._get_method_label(self.method)
1697 label = "%s (%s)" % (field_label, method_label)
1698
1699 self.label = label
1700 return label
1701
1702 # =============================================================================
1703 -class S3PivotTable(object):
1704 """ Class representing a pivot table of a resource """
1705
1707 """
1708 Constructor - extracts all unique records, generates a
1709 pivot table from them with the given dimensions and
1710 computes the aggregated values for each cell.
1711
1712 @param resource: the S3Resource
1713 @param rows: field selector for the rows dimension
1714 @param cols: field selector for the columns dimension
1715 @param facts: list of S3PivotTableFacts to compute
1716 @param strict: filter out dimension values which don't match
1717 the resource filter
1718 @param precision: maximum precision of aggregate computations,
1719 a dict {selector:number_of_decimals}
1720 """
1721
1722 # Initialize ----------------------------------------------------------
1723 #
1724 if not rows and not cols:
1725 raise SyntaxError("No rows or columns specified for pivot table")
1726
1727 self.resource = resource
1728
1729 self.lfields = None
1730 self.dfields = None
1731 self.rfields = None
1732
1733 self.rows = rows
1734 self.cols = cols
1735 self.facts = facts
1736
1737 self.precision = precision if isinstance(precision, dict) else {}
1738
1739 # API variables -------------------------------------------------------
1740 #
1741 self.records = None
1742 """ All records in the pivot table as a Storage like:
1743 {
1744 <record_id>: <Row>
1745 }
1746 """
1747
1748 self.empty = False
1749 """ Empty-flag (True if no records could be found) """
1750 self.numrows = None
1751 """ The number of rows in the pivot table """
1752 self.numcols = None
1753 """ The number of columns in the pivot table """
1754
1755 self.cell = None
1756 """ Array of pivot table cells in [rows[columns]]-order, each
1757 cell is a Storage like:
1758 {
1759 records: <list_of_record_ids>,
1760 (<fact>, <method>): <aggregated_value>, ...per layer
1761 }
1762 """
1763 self.row = None
1764 """ List of row headers, each header is a Storage like:
1765 {
1766 value: <dimension value>,
1767 records: <list_of_record_ids>,
1768 (<fact>, <method>): <total value>, ...per layer
1769 }
1770 """
1771 self.col = None
1772 """ List of column headers, each header is a Storage like:
1773 {
1774 value: <dimension value>,
1775 records: <list_of_record_ids>,
1776 (<fact>, <method>): <total value>, ...per layer
1777 }
1778 """
1779 self.totals = Storage()
1780 """ The grand total values for each layer, as a Storage like:
1781 {
1782 (<fact>, <method): <total value>, ...per layer
1783 }
1784 """
1785
1786 self.values = {}
1787
1788 # Get the fields ------------------------------------------------------
1789 #
1790 tablename = resource.tablename
1791
1792 # The "report_fields" table setting defines which additional
1793 # fields shall be included in the report base layer. This is
1794 # useful to provide easy access to the record data behind a
1795 # pivot table cell.
1796 fields = current.s3db.get_config(tablename, "report_fields", [])
1797
1798 self._get_fields(fields=fields)
1799 rows = self.rows
1800 cols = self.cols
1801
1802 # Exclude records with empty axis values ------------------------------
1803 #
1804 exclude_empty = current.s3db.get_config(tablename, "report_exclude_empty")
1805 if exclude_empty is True:
1806 # Exclude empty axis values for all fields
1807 query = (FS(rows) != None) & (FS(cols) != None)
1808 resource.add_filter(query)
1809
1810 elif type(exclude_empty) is tuple:
1811 # Exclude empty axis values for some fields
1812 for axis in (cols, rows):
1813 if axis in exclude_empty:
1814 resource.add_filter(FS(axis) != None)
1815
1816 # Retrieve the records ------------------------------------------------
1817 #
1818 data = resource.select(self.rfields.keys(), limit=None)
1819 drows = data["rows"]
1820 if drows:
1821
1822 key = str(resource.table._id)
1823 records = Storage([(i[key], i) for i in drows])
1824
1825 # Generate the data frame -----------------------------------------
1826 #
1827 gfields = self.gfields
1828 pkey_colname = gfields[self.pkey]
1829 rows_colname = gfields[rows]
1830 cols_colname = gfields[cols]
1831
1832 if strict:
1833 rfields = self.rfields
1834 axes = (rfield
1835 for rfield in (rfields[rows], rfields[cols])
1836 if rfield != None)
1837 axisfilter = resource.axisfilter(axes)
1838 else:
1839 axisfilter = None
1840
1841 dataframe = []
1842 extend = dataframe.extend
1843 expand = self._expand
1844
1845 for _id in records:
1846 row = records[_id]
1847 item = {key: _id}
1848 if rows_colname:
1849 item[rows_colname] = row[rows_colname]
1850 if cols_colname:
1851 item[cols_colname] = row[cols_colname]
1852 extend(expand(item, axisfilter=axisfilter))
1853
1854 self.records = records
1855
1856 # Group the records -----------------------------------------------
1857 #
1858 matrix, rnames, cnames = self._pivot(dataframe,
1859 pkey_colname,
1860 rows_colname,
1861 cols_colname)
1862
1863 # Initialize columns and rows -------------------------------------
1864 #
1865 if cols:
1866 self.col = [Storage({"value": v}) for v in cnames]
1867 self.numcols = len(self.col)
1868 else:
1869 self.col = [Storage({"value": None})]
1870 self.numcols = 1
1871
1872 if rows:
1873 self.row = [Storage({"value": v}) for v in rnames]
1874 self.numrows = len(self.row)
1875 else:
1876 self.row = [Storage({"value": None})]
1877 self.numrows = 1
1878
1879 # Add the layers --------------------------------------------------
1880 #
1881 add_layer = self._add_layer
1882 for fact in self.facts:
1883 add_layer(matrix, fact)
1884
1885 else:
1886 # No items to report on -------------------------------------------
1887 #
1888 self.empty = True
1889
1890 # -------------------------------------------------------------------------
1891 # API methods
1892 # -------------------------------------------------------------------------
1894 """ Total number of records in the report """
1895
1896 items = self.records
1897 if items is None:
1898 return 0
1899 else:
1900 return len(self.records)
1901
1902 # -------------------------------------------------------------------------
1906 """
1907 Render the pivot table data as a dict ready to be exported as
1908 GeoJSON for display on a Map.
1909
1910 Called by S3Report.geojson()
1911
1912 @param layer: the layer. e.g. ("id", "count")
1913 - we only support methods "count" & "sum"
1914 - @ToDo: Support density: 'per sqkm' and 'per population'
1915 @param level: the aggregation level (defaults to Country)
1916 """
1917
1918 if fact is None:
1919 fact = self.facts[0]
1920 layer = fact.layer
1921
1922 # The rows dimension
1923 # @ToDo: We can add sanity-checking using resource.parse_bbox_query() if-desired
1924 context = self.resource.get_config("context")
1925 if context and "location" in context:
1926 rows_dim = "(location)$%s" % level
1927 else:
1928 # Fallback to location_id
1929 rows_dim = "location_id$%s" % level
1930 # Fallback we can add if-required
1931 #rows_dim = "site_id$location_id$%s" % level
1932
1933 # The data
1934 attributes = {}
1935 geojsons = {}
1936
1937 if self.empty:
1938 location_ids = []
1939 else:
1940 numeric = lambda x: isinstance(x, (int, long, float))
1941 row_repr = s3_str
1942
1943 ids = {}
1944 irows = self.row
1945 rows = []
1946
1947 # Group and sort the rows
1948 is_numeric = None
1949 for i in xrange(self.numrows):
1950 irow = irows[i]
1951 total = irow[layer]
1952 if is_numeric is None:
1953 is_numeric = numeric(total)
1954 if not is_numeric:
1955 total = len(irow.records)
1956 header = Storage(value = irow.value,
1957 text = irow.text if "text" in irow
1958 else row_repr(irow.value))
1959 rows.append((i, total, header))
1960
1961 self._sortdim(rows, self.rfields[rows_dim])
1962
1963 # Aggregate the grouped values
1964 db = current.db
1965 gtable = current.s3db.gis_location
1966 query = (gtable.level == level) & (gtable.deleted == False)
1967 for _, rtotal, rtitle in rows:
1968 rval = rtitle.value
1969 if rval:
1970 # @ToDo: Handle duplicate names ;)
1971 if rval in ids:
1972 _id = ids[rval]
1973 else:
1974 q = query & (gtable.name == rval)
1975 row = db(q).select(gtable.id,
1976 gtable.parent,
1977 limitby=(0, 1)
1978 ).first()
1979 try:
1980 _id = row.id
1981 except AttributeError:
1982 continue
1983 # Cache
1984 ids[rval] = _id
1985
1986 attribute = dict(name=s3_str(rval),
1987 value=rtotal)
1988 attributes[_id] = attribute
1989
1990 location_ids = [ids[r] for r in ids]
1991 query = (gtable.id.belongs(location_ids))
1992 geojsons = current.gis.get_locations(gtable,
1993 query,
1994 join=False,
1995 geojson=True)
1996
1997 # Prepare for export via xml.gis_encode() and geojson/export.xsl
1998 location_data = {}
1999 geojsons = dict(gis_location = geojsons)
2000 location_data["geojsons"] = geojsons
2001 attributes = dict(gis_location = attributes)
2002 location_data["attributes"] = attributes
2003 return location_ids, location_data
2004
2005 # -------------------------------------------------------------------------
2007 """
2008 Render the pivot table data as JSON-serializable dict
2009
2010 @param layer: the layer
2011 @param maxrows: maximum number of rows (None for all)
2012 @param maxcols: maximum number of columns (None for all)
2013 @param least: render the least n rows/columns rather than
2014 the top n (with maxrows/maxcols)
2015
2016 {
2017 labels: {
2018 layer:
2019 rows:
2020 cols:
2021 total:
2022 },
2023 method: <aggregation method>,
2024 cells: [rows[cols]],
2025 rows: [rows[index, value, label, total]],
2026 cols: [cols[index, value, label, total]],
2027
2028 total: <grand total>,
2029 filter: [rows selector, cols selector]
2030 }
2031 """
2032
2033 rfields = self.rfields
2034 resource = self.resource
2035
2036 T = current.T
2037 OTHER = "__other__"
2038
2039 rows_dim = self.rows
2040 cols_dim = self.cols
2041
2042 # The output data
2043 orows = []
2044 rappend = orows.append
2045 ocols = []
2046 cappend = ocols.append
2047 ocells = []
2048
2049 lookups = {}
2050 facts = self.facts
2051
2052 if not self.empty:
2053
2054 # Representation methods for row and column keys
2055 row_repr = self._represent_method(rows_dim)
2056 col_repr = self._represent_method(cols_dim)
2057
2058 # Label for the "Others" row/columns
2059 others = s3_str(T("Others"))
2060
2061 # Get the layers (fact.selector, fact.method),
2062 # => used as keys to access the pivot data
2063 layers = [fact.layer for fact in facts]
2064 least = facts[0].method == "min"
2065
2066 # Group and sort the rows (grouping = determine "others")
2067 irows = self.row
2068 rows = []
2069 rtail = (None, None)
2070 for i in xrange(self.numrows):
2071 irow = irows[i]
2072 totals = [irow[layer] for layer in layers]
2073 sort_total = totals[0]
2074 header = {"value": irow.value,
2075 "text": irow.text if "text" in irow
2076 else row_repr(irow.value),
2077 }
2078 rows.append((i, sort_total, totals, header))
2079 if maxrows is not None:
2080 rtail = self._tail(rows, maxrows, least=least, facts=facts)
2081 self._sortdim(rows, rfields[rows_dim])
2082 if rtail[1] is not None:
2083 values = [irows[i]["value"] for i in rtail[0]]
2084 rows.append((OTHER,
2085 rtail[1],
2086 rtail[2],
2087 {"value": values, "text":others},
2088 ))
2089
2090 # Group and sort the cols (grouping = determine "others")
2091 icols = self.col
2092 cols = []
2093 ctail = (None, None)
2094 for i in xrange(self.numcols):
2095 icol = icols[i]
2096 totals = [icol[layer] for layer in layers]
2097 sort_total = totals[0]
2098 header = {"value": icol.value,
2099 "text": icol.text if "text" in icol
2100 else col_repr(icol.value),
2101 }
2102 cols.append((i, sort_total, totals, header))
2103 if maxcols is not None:
2104 ctail = self._tail(cols, maxcols, least=least, facts=facts)
2105 self._sortdim(cols, rfields[cols_dim])
2106 if ctail[1] is not None:
2107 values = [icols[i]["value"] for i in ctail[0]]
2108 cols.append((OTHER,
2109 ctail[1],
2110 ctail[2],
2111 {"value": values, "text": others},
2112 ))
2113
2114 rothers = rtail[0] or set()
2115 cothers = ctail[0] or set()
2116
2117 # Group and sort the cells accordingly
2118 # @todo: break up into subfunctions
2119 icell = self.cell
2120 cells = {}
2121 for i in xrange(self.numrows):
2122 irow = icell[i]
2123 ridx = (i, OTHER) if rothers and i in rothers else (i,)
2124
2125 for j in xrange(self.numcols):
2126 cell = irow[j]
2127 cidx = (j, OTHER) if cothers and j in cothers else (j,)
2128
2129 cell_records = cell["records"]
2130
2131 for layer_index, layer in enumerate(layers):
2132
2133 # Get cell items for the layer
2134 # => items can be a single numeric value, or a list
2135 items = cell[layer]
2136
2137 # Get cell value for the layer
2138 if isinstance(items, list):
2139 value = len(items)
2140 else:
2141 value = items
2142
2143 for ri in ridx:
2144 if ri not in cells:
2145 orow = cells[ri] = {}
2146 else:
2147 orow = cells[ri]
2148 for ci in cidx:
2149
2150 if ci not in orow:
2151 # Create a new output cell
2152 ocell = orow[ci] = {"values": [],
2153 "items": [],
2154 "records": [],
2155 }
2156 else:
2157 ocell = orow[ci]
2158
2159 if layer_index == 0:
2160 # Extend the list of records
2161 ocell["records"].extend(cell_records)
2162
2163 value_array = ocell["values"]
2164 items_array = ocell["items"]
2165 if len(value_array) <= layer_index:
2166 value_array.append(value)
2167 items_array.append(items)
2168 else:
2169 ovalue = value_array[layer_index]
2170 oitems = items_array[layer_index]
2171 if isinstance(ovalue, list):
2172 ovalue.append(value)
2173 oitems.append(items)
2174 else:
2175 value_array[layer_index] = [ovalue, value]
2176 items_array[layer_index] = [oitems, items]
2177
2178 # Get field representation methods
2179 represents = self._represents(layers)
2180
2181 # Aggregate the grouped values
2182 add_columns = True # do this only once
2183 for rindex, rtotal, rtotals, rtitle in rows:
2184
2185 orow = []
2186
2187 # Row value for filter construction
2188 rval = rtitle["value"]
2189 if rindex == OTHER and isinstance(rval, list):
2190 rval = ",".join(s3_str(v) for v in rval)
2191 elif rval is not None:
2192 rval = s3_str(rval)
2193
2194 # The output row summary
2195 rappend((rindex,
2196 rindex in rothers,
2197 rtotals,
2198 rval,
2199 rtitle["text"],
2200 ))
2201
2202 for cindex, ctotal, ctotals, ctitle in cols:
2203
2204 # Get the corresponding cell
2205 cell = cells[rindex][cindex]
2206
2207 value_array = cell["values"]
2208 items_array = cell["items"]
2209
2210 # Initialize the output cell
2211 ocell = {"i": [], "v": []}
2212 okeys = None
2213
2214 for layer_index, fact in enumerate(facts):
2215
2216 selector, method = fact.layer
2217
2218 # The value(s) to render in this cell
2219 items = items_array[layer_index]
2220
2221 # The cell total for this layer (for charts)
2222 value = value_array[layer_index]
2223 if type(value) is list:
2224 # "Others" cell with multiple totals
2225 value = fact.aggregate_totals(value)
2226 ocell["v"].append(value)
2227
2228 rfield = self.rfields[selector]
2229
2230 if method == "list":
2231 # Build a look-up table with field value representations
2232 if selector not in lookups:
2233 lookup = lookups[selector] = {}
2234 else:
2235 lookup = lookups[selector]
2236
2237 represent = represents[selector]
2238
2239 keys = []
2240 for record_id in cell["records"]:
2241
2242 record = self.records[record_id]
2243 try:
2244 fvalue = record[rfield.colname]
2245 except AttributeError:
2246 continue
2247 if fvalue is None:
2248 continue
2249 if type(fvalue) is not list:
2250 fvalue = [fvalue]
2251
2252 for v in fvalue:
2253 if v is None:
2254 continue
2255 if v not in keys:
2256 keys.append(v)
2257 if v not in lookup:
2258 lookup[v] = represent(v)
2259
2260 # Sort the keys by their representations
2261 keys.sort(key=lambda i: lookup[i])
2262 items = [lookup[key] for key in keys if key in lookup]
2263
2264 elif method in ("sum", "count") and okeys is None:
2265 # Include only cell records in okeys which actually
2266 # contribute to the aggregate
2267 okeys = []
2268 for record_id in cell["records"]:
2269 record = self.records[record_id]
2270 try:
2271 fvalue = record[rfield.colname]
2272 except AttributeError:
2273 continue
2274 if method == "sum" and \
2275 isinstance(fvalue, (int, long, float)) and fvalue:
2276 okeys.append(record_id)
2277 elif method == "count" and \
2278 fvalue is not None:
2279 okeys.append(record_id)
2280 else:
2281 # Include all cell records in okeys
2282 okeys = cell["records"]
2283
2284 ocell["i"].append(items)
2285
2286 if okeys:
2287 ocell["k"] = okeys
2288 orow.append(ocell)
2289
2290 if add_columns:
2291
2292 # Column value for filter construction
2293 cval = ctitle["value"]
2294 if cindex == OTHER and isinstance(cval, list):
2295 cval = ",".join(s3_str(v) for v in cval)
2296 elif cval is not None:
2297 cval = s3_str(cval)
2298
2299 # The output column summary
2300 cappend((cindex,
2301 cindex in cothers,
2302 ctotals,
2303 cval,
2304 ctitle["text"],
2305 ))
2306
2307 add_columns = False
2308 ocells.append(orow)
2309
2310 # Lookup labels
2311 report_options = resource.get_config("report_options", {})
2312 if report_options:
2313 fact_options = report_options.get("fact")
2314 else:
2315 fact_options = ()
2316
2317 # @todo: lookup report title before constructing from fact labels
2318
2319 fact_data = []
2320 fact_labels = []
2321 for fact in facts:
2322 rfield = rfields[fact.selector]
2323 fact_label = str(fact.get_label(rfield, fact_options))
2324 fact_data.append((fact.selector, fact.method, fact_label))
2325 fact_labels.append(fact_label)
2326
2327 get_label = S3PivotTableFact._get_field_label
2328 if rows_dim:
2329 rows_label = str(get_label(rfields[rows_dim], report_options.get("rows")))
2330 else:
2331 rows_label = ""
2332
2333 if cols_dim:
2334 cols_label = str(get_label(rfields[cols_dim], report_options.get("cols")))
2335 else:
2336 cols_label = ""
2337
2338 labels = {"total": str(T("Total")),
2339 "none": str(current.messages["NONE"]),
2340 "per": str(T("per")),
2341 "breakdown": str(T("Breakdown")),
2342 # @todo: use report title:
2343 "layer": " / ".join(fact_labels),
2344 "rows": rows_label,
2345 "cols": cols_label,
2346 }
2347
2348 # Compile the output dict
2349 output = {"rows": orows,
2350 "cols": ocols,
2351 "facts": fact_data,
2352 "cells": ocells,
2353 "total": self._totals(self.totals, [fact]),
2354 "nodata": None if not self.empty else str(T("No data available")),
2355 "labels": labels,
2356 }
2357
2358 # Add axis selectors for filter-URL construction
2359 prefix = resource.prefix_selector
2360 output["filter"] = (prefix(rows_dim) if rows_dim else None,
2361 prefix(cols_dim) if cols_dim else None,
2362 )
2363
2364 return output
2365
2366 # -------------------------------------------------------------------------
2368 """
2369 Convert this pivot table into an XLS file
2370
2371 @param title: the title of the report
2372
2373 @returns: the XLS file as stream
2374 """
2375
2376 from s3codec import S3Codec
2377 exporter = S3Codec.get_codec("xls")
2378
2379 return exporter.encode_pt(self, title)
2380
2381 # -------------------------------------------------------------------------
2383 """
2384 Get the representation functions per fact field
2385
2386 @param layers: the list of layers, tuples (selector, method)
2387 """
2388
2389 rfields = self.rfields
2390 represents = {}
2391
2392 values = self.values
2393
2394 for selector, method in layers:
2395 if selector in represents:
2396 continue
2397
2398 # Get the field
2399 rfield = rfields[selector]
2400 f = rfield.field
2401
2402 # Utilize bulk-representation for field values
2403 if method == "list" and \
2404 f is not None and hasattr(f.represent, "bulk"):
2405 all_values = values[(selector, method)]
2406 if all_values:
2407 f.represent.bulk(list(s3_flatlist(all_values)))
2408
2409 # Get the representation method
2410 has_fk = f is not None and s3_has_foreign_key(f)
2411 if has_fk:
2412 represent = lambda v, f=f: s3_str(f.represent(v))
2413 else:
2414 m = self._represent_method(selector)
2415 represent = lambda v, m=m: s3_str(m(v))
2416
2417 represents[selector] = represent
2418
2419 return represents
2420
2421 # -------------------------------------------------------------------------
2422 @staticmethod
2424 """
2425 Sort a dimension (sorts items in-place)
2426
2427 @param items: the items as list of tuples
2428 (index, sort-total, totals, header)
2429 @param rfield: the dimension (S3ResourceField)
2430 @param index: alternative index of the value/text dict
2431 within each item
2432 """
2433
2434 if not rfield:
2435 return
2436
2437 ftype = rfield.ftype
2438
2439 sortby = "value"
2440 key = lambda item: item[index][sortby]
2441
2442 if ftype in ("integer", "string"):
2443 # Sort option keys by their representation
2444 requires = rfield.requires
2445 if isinstance(requires, (tuple, list)):
2446 requires = requires[0]
2447 if isinstance(requires, IS_EMPTY_OR):
2448 requires = requires.other
2449 if isinstance(requires, IS_IN_SET):
2450 sortby = "text"
2451
2452 elif ftype[:9] == "reference":
2453 # Sort foreign keys by their representation
2454 sortby = "text"
2455
2456 elif ftype == "date":
2457 # Can't compare date objects to None
2458 mindate = datetime.date.min
2459 key = lambda item: item[index][sortby] or mindate
2460
2461 elif ftype == "datetime":
2462 # Can't compare datetime objects to None
2463 mindate = datetime.datetime.min
2464 key = lambda item: item[index][sortby] or mindate
2465
2466 items.sort(key=key)
2467
2468 # -------------------------------------------------------------------------
2469 @classmethod
2471 """
2472 Find the top/least <length> items (by total)
2473
2474 @param items: the items as list of tuples
2475 (index, sort-total, totals, header)
2476 @param length: the maximum number of items
2477 @param least: find least rather than top
2478 @param facts: the facts to aggregate the tail totals
2479 """
2480
2481 try:
2482 if len(items) > length:
2483 l = list(items)
2484 l.sort(lambda x, y: int(y[1]-x[1]))
2485 if least:
2486 l.reverse()
2487 keys = [item[0] for item in l[length-1:]]
2488 totals = []
2489 for i, fact in enumerate(facts):
2490 subtotals = [item[2][i] for item in l[length-1:]]
2491 totals.append(fact.aggregate_totals(subtotals))
2492 return (keys, totals[0], totals)
2493 except (TypeError, ValueError):
2494 pass
2495 return (None, None)
2496
2497 # -------------------------------------------------------------------------
2498 @staticmethod
2500 """
2501 Get the totals of a row/column/report
2502
2503 @param values: the values dictionary
2504 @param facts: the facts
2505 @param append: callback to collect the totals for JSON data
2506 (currently only collects the first layer)
2507 """
2508
2509 totals = []
2510 number_represent = IS_NUMBER.represent
2511 for fact in facts:
2512 value = values[fact.layer]
2513 #if fact.method == "list":
2514 #value = value and len(value) or 0
2515 if not len(totals) and append is not None:
2516 append(value)
2517 totals.append(s3_str(number_represent(value)))
2518 totals = " / ".join(totals)
2519 return totals
2520
2521 # -------------------------------------------------------------------------
2522 # Internal methods
2523 # -------------------------------------------------------------------------
2524 @staticmethod
2526 """
2527 2-dimensional pivoting of a list of unique items
2528
2529 @param items: list of unique items as dicts
2530 @param pkey_colname: column name of the primary key
2531 @param rows_colname: column name of the row dimension
2532 @param cols_colname: column name of the column dimension
2533
2534 @return: tuple of (cell matrix, row headers, column headers),
2535 where cell matrix is a 2-dimensional array [rows[columns]]
2536 and row headers and column headers each are lists (in the
2537 same order as the cell matrix)
2538 """
2539
2540 rvalues = Storage()
2541 cvalues = Storage()
2542 cells = Storage()
2543
2544 # All unique rows values
2545 rindex = 0
2546 cindex = 0
2547 for item in items:
2548
2549 rvalue = item[rows_colname] if rows_colname else None
2550 cvalue = item[cols_colname] if cols_colname else None
2551
2552 if rvalue not in rvalues:
2553 r = rvalues[rvalue] = rindex
2554 rindex += 1
2555 else:
2556 r = rvalues[rvalue]
2557 if cvalue not in cvalues:
2558 c = cvalues[cvalue] = cindex
2559 cindex += 1
2560 else:
2561 c = cvalues[cvalue]
2562
2563 if (r, c) not in cells:
2564 cells[(r, c)] = [item[pkey_colname]]
2565 else:
2566 cells[(r, c)].append(item[pkey_colname])
2567
2568 matrix = []
2569 for r in xrange(len(rvalues)):
2570 row = []
2571 for c in xrange(len(cvalues)):
2572 row.append(cells[(r, c)])
2573 matrix.append(row)
2574
2575 rnames = [None] * len(rvalues)
2576 for k, v in rvalues.items():
2577 rnames[v] = k
2578
2579 cnames = [None] * len(cvalues)
2580 for k, v in cvalues.items():
2581 cnames[v] = k
2582
2583 return matrix, rnames, cnames
2584
2585 # -------------------------------------------------------------------------
2587 """
2588 Compute an aggregation layer, updates:
2589
2590 - self.cell: the aggregated values per cell
2591 - self.row: the totals per row
2592 - self.col: the totals per column
2593 - self.totals: the overall totals per layer
2594
2595 @param matrix: the cell matrix
2596 @param fact: the fact field
2597 @param method: the aggregation method
2598 """
2599
2600 rows = self.row
2601 cols = self.col
2602 records = self.records
2603 extract = self._extract
2604 resource = self.resource
2605
2606 RECORDS = "records"
2607 VALUES = "values"
2608
2609 table = resource.table
2610 pkey = table._id.name
2611
2612 layer = fact.layer
2613 precision = self.precision.get(fact.selector)
2614
2615 numcols = len(self.col)
2616 numrows = len(self.row)
2617
2618 # Initialize cells
2619 if self.cell is None:
2620 self.cell = [[Storage()
2621 for i in xrange(numcols)]
2622 for j in xrange(numrows)]
2623 cells = self.cell
2624
2625 all_values = []
2626 for r in xrange(numrows):
2627
2628 # Initialize row header
2629 row = rows[r]
2630 row[RECORDS] = []
2631 row[VALUES] = []
2632
2633 row_records = row[RECORDS]
2634 row_values = row[VALUES]
2635
2636 for c in xrange(numcols):
2637
2638 # Initialize column header
2639 col = cols[c]
2640 if RECORDS not in col:
2641 col[RECORDS] = []
2642 col_records = col[RECORDS]
2643 if VALUES not in col:
2644 col[VALUES] = []
2645 col_values = col[VALUES]
2646
2647 # Get the records
2648 cell = cells[r][c]
2649 if RECORDS in cell and cell[RECORDS] is not None:
2650 ids = cell[RECORDS]
2651 else:
2652 data = matrix[r][c]
2653 if data:
2654 remove = data.remove
2655 while None in data:
2656 remove(None)
2657 ids = data
2658 else:
2659 ids = []
2660 cell[RECORDS] = ids
2661 row_records.extend(ids)
2662 col_records.extend(ids)
2663
2664 # Get the values
2665 if fact.selector is None:
2666 fact.selector = pkey
2667 values = ids
2668 row_values = row_records
2669 col_values = row_records
2670 all_values = records.keys()
2671 else:
2672 values = []
2673 append = values.append
2674 for i in ids:
2675 value = extract(records[i], fact.selector)
2676 if value is None:
2677 continue
2678 append(value)
2679 values = list(s3_flatlist(values))
2680 if fact.method in ("list", "count"):
2681 values = list(set(values))
2682 row_values.extend(values)
2683 col_values.extend(values)
2684 all_values.extend(values)
2685
2686 # Aggregate values
2687 value = fact.compute(values, precision=precision)
2688 cell[layer] = value
2689
2690 # Compute row total
2691 row[layer] = fact.compute(row_values,
2692 totals = True,
2693 precision = precision,
2694 )
2695 del row[VALUES]
2696
2697 # Compute column total
2698 for c in xrange(numcols):
2699 col = cols[c]
2700 col[layer] = fact.compute(col[VALUES],
2701 totals = True,
2702 precision = precision,
2703 )
2704 del col[VALUES]
2705
2706 # Compute overall total
2707 self.totals[layer] = fact.compute(all_values,
2708 totals = True,
2709 precision = precision,
2710 )
2711 self.values[layer] = all_values
2712
2713 # -------------------------------------------------------------------------
2715 """
2716 Determine the fields needed to generate the report
2717
2718 @param fields: fields to include in the report (all fields)
2719 """
2720
2721 resource = self.resource
2722 table = resource.table
2723
2724 # Lambda to prefix all field selectors
2725 alias = resource.alias
2726 def prefix(s):
2727 if isinstance(s, (tuple, list)):
2728 return prefix(s[-1])
2729 if "." not in s.split("$", 1)[0]:
2730 return "%s.%s" % (alias, s)
2731 elif s[:2] == "~.":
2732 return "%s.%s" % (alias, s[2:])
2733 else:
2734 return s
2735
2736 self.pkey = pkey = prefix(table._id.name)
2737 self.rows = rows = prefix(self.rows) if self.rows else None
2738 self.cols = cols = prefix(self.cols) if self.cols else None
2739
2740 if not fields:
2741 fields = ()
2742
2743 # dfields (data-fields): fields to generate the layers
2744 dfields = [prefix(s) for s in fields]
2745 if rows and rows not in dfields:
2746 dfields.append(rows)
2747 if cols and cols not in dfields:
2748 dfields.append(cols)
2749 if pkey not in dfields:
2750 dfields.append(pkey)
2751
2752 # Normalize fact selectors
2753 for fact in self.facts:
2754 fact.selector = selector = prefix(fact.selector)
2755 if selector not in dfields:
2756 dfields.append(selector)
2757 self.dfields = dfields
2758
2759 # Normalize precision selectors
2760 precision = {}
2761 for selector, decimals in self.precision.items():
2762 precision[prefix(selector)] = decimals
2763 self.precision = precision
2764
2765 # rfields (resource-fields): dfields resolved into a ResourceFields map
2766 rfields = resource.resolve_selectors(dfields)[0]
2767 rfields = Storage([(f.selector.replace("~", alias), f) for f in rfields])
2768 self.rfields = rfields
2769
2770 # gfields (grouping-fields): fields to group the records by
2771 self.gfields = {pkey: rfields[pkey].colname,
2772 rows: rfields[rows].colname
2773 if rows and rows in rfields else None,
2774 cols: rfields[cols].colname
2775 if cols and cols in rfields else None,
2776 }
2777
2778 # -------------------------------------------------------------------------
2780 """
2781 Get the representation method for a field in the report
2782
2783 @param field: the field selector
2784 """
2785
2786 rfields = self.rfields
2787 default = lambda value: None
2788
2789 if field and field in rfields:
2790
2791 rfield = rfields[field]
2792 if rfield.field:
2793 def repr_method(value):
2794 return s3_represent_value(rfield.field,
2795 value,
2796 strip_markup = True,
2797 )
2798 elif rfield.virtual:
2799
2800 # If rfield defines a represent, use it
2801 represent = rfield.represent
2802 if not represent:
2803 represent = s3_str
2804
2805 # Wrap with markup stripper
2806 stripper = S3MarkupStripper()
2807 def repr_method(val):
2808 if val is None:
2809 return "-"
2810 text = represent(val)
2811 if "<" in text:
2812 stripper.feed(text)
2813 return stripper.stripped()
2814 else:
2815 return text
2816 else:
2817 repr_method = default
2818 else:
2819 repr_method = default
2820
2821 return repr_method
2822
2823 # -------------------------------------------------------------------------
2825 """
2826 Extract a field value from a DAL row
2827
2828 @param row: the row
2829 @param field: the fieldname (list_fields syntax)
2830 """
2831
2832 rfields = self.rfields
2833 if field not in rfields:
2834 raise KeyError("Invalid field name: %s" % field)
2835 rfield = rfields[field]
2836 try:
2837 return rfield.extract(row)
2838 except AttributeError:
2839 return None
2840
2841 # -------------------------------------------------------------------------
2843 """
2844 Expand a data frame row into a list of rows for list:type values
2845
2846 @param row: the row
2847 @param field: the field to expand (None for all fields)
2848 @param axisfilter: dict of filtered field values by column names
2849 """
2850
2851 pairs = []
2852 append = pairs.append
2853 for colname in self.gfields.values():
2854 if not colname:
2855 continue
2856 value = row[colname]
2857 if type(value) is list:
2858 if not value:
2859 value = [None]
2860 if axisfilter and colname in axisfilter:
2861 p = [(colname, v) for v in value
2862 if v in axisfilter[colname]]
2863 if not p:
2864 raise RuntimeError("record does not match query")
2865 else:
2866 append(p)
2867 else:
2868 append([(colname, v) for v in value])
2869 else:
2870 append([(colname, value)])
2871 result = [dict(i) for i in product(*pairs)]
2872 return result
2873
2874 # END =========================================================================
2875
| Home | Trees | Indices | Help |
|
|---|
| Generated by Epydoc 3.0.1 on Fri Mar 15 08:52:01 2019 | http://epydoc.sourceforge.net |