s3.s3grouped

1 # -*- coding: utf-8 -*- 2 3 """ S3 Grouped Items Report Method 4 5 @copyright: 2015-2019 (c) Sahana Software Foundation 6 @license: MIT 7 8 Permission is hereby granted, free of charge, to any person 9 obtaining a copy of this software and associated documentation 10 files (the "Software"), to deal in the Software without 11 restriction, including without limitation the rights to use, 12 copy, modify, merge, publish, distribute, sublicense, and/or sell 13 copies of the Software, and to permit persons to whom the 14 Software is furnished to do so, subject to the following 15 conditions: 16 17 The above copyright notice and this permission notice shall be 18 included in all copies or substantial portions of the Software. 19 20 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 21 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 22 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 23 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 24 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 25 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 26 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 27 OTHER DEALINGS IN THE SOFTWARE. 28 """ 29 30 __all__ = ("S3GroupedItemsReport", 31 "S3GroupedItemsTable", 32 "S3GroupedItems", 33 "S3GroupAggregate", 34 ) 35 36 import json 37 import math 38 39 from gluon import current, DIV, H2, INPUT, SPAN, TABLE, TBODY, TD, TFOOT, TH, THEAD, TR 40 41 from s3rest import S3Method 42 from s3utils import s3_strip_markup, s3_unicode 43 44 # Compact JSON encoding 45 SEPARATORS = (",", ":") 46 47 DEFAULT = lambda: None

48 49 # ============================================================================= 50 -class S3GroupedItemsReport(S3Method):

51 """ 52 REST Method Handler for Grouped Items Reports 53 54 @todo: widget method 55 """ 56 57 # -------------------------------------------------------------------------

58 - def apply_method(self, r, **attr):

59 """ 60 Page-render entry point for REST interface. 61 62 @param r: the S3Request instance 63 @param attr: controller attributes 64 """ 65 66 output = {} 67 if r.http == "GET": 68 return self.report(r, **attr) 69 else: 70 r.error(405, current.ERROR.BAD_METHOD) 71 return output

72 73 # -------------------------------------------------------------------------

75 """ 76 Summary widget method 77 78 @param r: the S3Request 79 @param method: the widget method 80 @param widget_id: the widget ID 81 @param visible: whether the widget is initially visible 82 @param attr: controller attributes 83 """ 84 85 output = {} 86 if r.http == "GET": 87 r.error(501, current.ERROR.NOT_IMPLEMENTED) 88 else: 89 r.error(405, current.ERROR.BAD_METHOD) 90 return output

91 92 # -------------------------------------------------------------------------

93 - def report(self, r, **attr):

94 """ 95 Report generator 96 97 @param r: the S3Request instance 98 @param attr: controller attributes 99 """ 100 101 T = current.T 102 output = {} 103 104 resource = self.resource 105 tablename = resource.tablename 106 107 get_config = resource.get_config 108 109 representation = r.representation 110 111 # Apply filter defaults before rendering the data 112 show_filter_form = False 113 if representation in ("html", "iframe"): 114 filter_widgets = get_config("filter_widgets", None) 115 if filter_widgets and not self.hide_filter: 116 show_filter_form = True 117 from s3filter import S3FilterForm 118 S3FilterForm.apply_filter_defaults(r, resource) 119 120 # Get the report configuration 121 report_config = self.get_report_config() 122 123 # Resolve selectors in the report configuration 124 fields = self.resolve(report_config) 125 126 # Get extraction method 127 extract = report_config.get("extract") 128 if not callable(extract): 129 extract = self.extract 130 131 selectors = [s for s in fields if fields[s] is not None] 132 orderby = report_config.get("orderby_cols") 133 134 # Extract the data 135 items = extract(self.resource, selectors, orderby) 136 137 # Group and aggregate 138 groupby = report_config.get("groupby_cols") 139 aggregate = report_config.get("aggregate_cols") 140 141 gi = S3GroupedItems(items, groupby=groupby, aggregate=aggregate) 142 143 # Report title 144 title = report_config.get("title") 145 if title is None: 146 title = self.crud_string(tablename, "title_report") 147 148 # Generate JSON data 149 display_cols = report_config.get("display_cols") 150 labels = report_config.get("labels") 151 represent = report_config.get("groupby_represent") 152 153 if representation in ("pdf", "xls"): 154 as_dict = True 155 else: 156 as_dict = False 157 158 data = gi.json(fields = display_cols, 159 labels = labels, 160 represent = represent, 161 as_dict = as_dict, 162 ) 163 164 group_headers = report_config.get("group_headers", False) 165 166 # Widget ID 167 widget_id = "groupeditems" 168 169 # Render output 170 if representation in ("html", "iframe"): 171 # Page load 172 output["report_type"] = "groupeditems" 173 output["widget_id"] = widget_id 174 output["title"] = title 175 176 # Filter form 177 if show_filter_form: 178 179 settings = current.deployment_settings 180 181 # Filter form options 182 filter_formstyle = get_config("filter_formstyle", None) 183 filter_clear = get_config("filter_clear", 184 settings.get_ui_filter_clear()) 185 filter_submit = get_config("filter_submit", True) 186 187 # Instantiate form 188 filter_form = S3FilterForm(filter_widgets, 189 formstyle=filter_formstyle, 190 submit=filter_submit, 191 clear=filter_clear, 192 ajax=True, 193 _class="filter-form", 194 _id="%s-filter-form" % widget_id, 195 ) 196 197 # Render against unfiltered resource 198 fresource = current.s3db.resource(tablename) 199 alias = resource.alias if resource.parent else None 200 output["filter_form"] = filter_form.html(fresource, 201 r.get_vars, 202 target = widget_id, 203 alias = alias 204 ) 205 206 # Inject data 207 items = INPUT(_type = "hidden", 208 _id = "%s-data" % widget_id, 209 _class = "gi-data", 210 _value = data, 211 ) 212 output["items"] = items 213 214 # Empty section 215 output["empty"] = T("No data available") 216 217 # Export formats 218 output["formats"] = self.export_links(r) 219 220 # Script options 221 ajaxurl = attr.get("ajaxurl", r.url(method = "grouped", 222 representation = "json", 223 vars = r.get_vars, 224 )) 225 totals_label = report_config.get("totals_label", T("Total")) 226 227 options = {"ajaxURL": ajaxurl, 228 "totalsLabel": str(totals_label).upper(), 229 "renderGroupHeaders": group_headers, 230 } 231 232 # Inject script 233 self.inject_script(widget_id, options=options) 234 235 # Detect and store theme-specific inner layout 236 self._view(r, "grouped.html") 237 238 # View 239 response = current.response 240 response.view = self._view(r, "report.html") 241 242 elif representation == "json": 243 # Ajax reload 244 output = data 245 246 elif representation == "pdf": 247 # PDF Export 248 totals_label = report_config.get("totals_label", T("Total")) 249 pdf_header = report_config.get("pdf_header", DEFAULT) 250 gi_table = S3GroupedItemsTable(resource, 251 title = title, 252 data = data, 253 group_headers = group_headers, 254 totals_label = totals_label, 255 pdf_header = pdf_header, 256 ) 257 return gi_table.pdf(r) 258 259 elif representation == "xls": 260 # XLS Export 261 field_types = report_config.get("ftypes") 262 totals_label = report_config.get("totals_label", T("Total")) 263 gi_table = S3GroupedItemsTable(resource, 264 title = title, 265 data = data, 266 field_types = field_types, 267 aggregate = aggregate, 268 group_headers = group_headers, 269 totals_label = totals_label, 270 ) 271 return gi_table.xls(r) 272 273 else: 274 r.error(415, current.ERROR.BAD_FORMAT) 275 276 return output

277 278 # -------------------------------------------------------------------------

279 - def get_report_config(self):

280 """ 281 Get the configuration for the requested report, updated 282 with URL options 283 """ 284 285 r = self.request 286 get_vars = r.get_vars 287 288 # Get the resource configuration 289 config = self.resource.get_config("grouped") 290 if not config: 291 # No reports implemented for this resource 292 r.error(405, current.ERROR.NOT_IMPLEMENTED) 293 294 # Which report? 295 report = get_vars.get("report", "default") 296 if isinstance(report, list): 297 report = report[-1] 298 299 # Get the report config 300 report_config = config.get(report) 301 if not report_config: 302 # This report is not implemented 303 r.error(405, current.ERROR.NOT_IMPLEMENTED) 304 else: 305 report_config = dict(report_config) 306 307 # Orderby 308 orderby = get_vars.get("orderby") 309 if isinstance(orderby, list): 310 orderby = ",".join(orderby).split(",") 311 if not orderby: 312 orderby = report_config.get("orderby") 313 if not orderby: 314 orderby = report_config.get("groupby") 315 report_config["orderby"] = orderby 316 317 return report_config

318 319 # -------------------------------------------------------------------------

320 - def resolve(self, report_config):

321 """ 322 Get all field selectors for the report, and resolve them 323 against the resource 324 325 @param resource: the resource 326 @param config: the report config (will be updated) 327 328 @return: a dict {selector: rfield}, where rfield can be None 329 if the selector does not resolve against the resource 330 """ 331 332 resource = self.resource 333 334 # Get selectors for visible fields 335 fields = report_config.get("fields") 336 if not fields: 337 # Fall back to list_fields 338 selectors = resource.list_fields("grouped_fields") 339 fields = list(selectors) 340 else: 341 selectors = list(fields) 342 343 # Get selectors for grouping axes 344 groupby = report_config.get("groupby") 345 if isinstance(groupby, (list, tuple)): 346 selectors.extend(groupby) 347 elif groupby: 348 selectors.append(groupby) 349 350 # Get selectors for aggregation 351 aggregate = report_config.get("aggregate") 352 if aggregate: 353 for method, selector in aggregate: 354 selectors.append(selector) 355 else: 356 report_config["group_headers"] = True 357 358 # Get selectors for orderby 359 orderby = report_config.get("orderby") 360 if orderby: 361 for selector in orderby: 362 s, d = ("%s asc" % selector).split(" ")[:2] 363 selectors.append(s) 364 365 # Resolve all selectors against the resource, 366 # collect S3ResourceFields, labels and field types 367 rfields = {} 368 labels = {} 369 ftypes = {} 370 id_field = str(resource._id) 371 for f in selectors: 372 label, selector = f if type(f) is tuple else (None, f) 373 if selector in rfields: 374 # Already resolved 375 continue 376 try: 377 rfield = resource.resolve_selector(selector) 378 except (SyntaxError, AttributeError): 379 rfield = None 380 if label and rfield: 381 rfield.label = label 382 if id_field and rfield and rfield.colname == id_field: 383 id_field = None 384 rfields[selector] = rfield 385 if rfield: 386 labels[rfield.colname] = rfield.label 387 ftypes[rfield.colname] = rfield.ftype 388 elif label: 389 labels[selector] = label 390 ftypes[selector] = "virtual" 391 report_config["labels"] = labels 392 report_config["ftypes"] = ftypes 393 394 # Make sure id field is always included 395 if id_field: 396 id_name = resource._id.name 397 rfields[id_name] = resource.resolve_selector(id_name) 398 399 # Get column names for vsibile fields 400 display_cols = [] 401 for f in fields: 402 label, selector = f if type(f) is tuple else (None, f) 403 rfield = rfields.get(selector) 404 colname = rfield.colname if rfield else selector 405 if colname: 406 display_cols.append(colname) 407 report_config["display_cols"] = display_cols 408 409 # Get column names for orderby 410 orderby_cols = [] 411 orderby = report_config.get("orderby") 412 if orderby: 413 for selector in orderby: 414 s, d = ("%s asc" % selector).split(" ")[:2] 415 rfield = rfields.get(s) 416 colname = rfield.colname if rfield else None 417 if colname: 418 orderby_cols.append("%s %s" % (colname, d)) 419 if not orderby_cols: 420 orderby_cols = None 421 report_config["orderby_cols"] = orderby_cols 422 423 # Get column names for grouping 424 groupby_cols = [] 425 groupby_represent = {} 426 groupby = report_config.get("groupby") 427 if groupby: 428 for selector in groupby: 429 rfield = rfields.get(selector) 430 if rfield: 431 colname = rfield.colname 432 field = rfield.field 433 if field: 434 groupby_represent[colname] = field.represent 435 else: 436 colname = selector 437 groupby_cols.append(colname) 438 report_config["groupby_cols"] = groupby_cols 439 report_config["groupby_represent"] = groupby_represent 440 441 # Get columns names for aggregation 442 aggregate_cols = [] 443 aggregate = report_config.get("aggregate") 444 if aggregate: 445 for method, selector in aggregate: 446 rfield = rfields.get(selector) 447 colname = rfield.colname if rfield else selector 448 aggregate_cols.append((method, colname)) 449 report_config["aggregate_cols"] = aggregate_cols 450 451 return rfields

452 453 # ------------------------------------------------------------------------- 454 @staticmethod

455 - def extract(resource, selectors, orderby):

456 """ 457 Extract the data from the resource (default method, can be 458 overridden in report config) 459 460 @param resource: the resource 461 @param selectors: the field selectors 462 463 @returns: list of dicts {colname: value} including 464 raw data (_row) 465 """ 466 467 data = resource.select(selectors, 468 limit = None, 469 orderby = orderby, 470 raw_data = True, 471 represent = True, 472 ) 473 return data.rows

474 475 # ------------------------------------------------------------------------- 476 @staticmethod

477 - def export_links(r):

478 """ 479 Render export links for the report 480 481 @param r: the S3Request 482 """ 483 484 T = current.T 485 486 formats = DIV(DIV(_title = T("Export as PDF"), 487 _class = "gi-export export_pdf", 488 data = {"url": r.url(method = "grouped", 489 representation = "pdf", 490 vars = r.get_vars, 491 ), 492 }, 493 ), 494 DIV(_title = T("Export as XLS"), 495 _class = "gi-export export_xls", 496 data = {"url": r.url(method = "grouped", 497 representation = "xls", 498 vars = r.get_vars, 499 ), 500 }, 501 ), 502 _class="gi-export-formats", 503 ) 504 505 return formats

506 507 # ------------------------------------------------------------------------- 508 @staticmethod

509 - def inject_script(widget_id, options=None):

510 """ 511 Inject the groupedItems script and bind it to the container 512 513 @param widget_id: the widget container DOM ID 514 @param options: dict with options for the widget 515 516 @note: options dict must be JSON-serializable 517 """ 518 519 s3 = current.response.s3 520 521 scripts = s3.scripts 522 appname = current.request.application 523 524 # Inject UI widget script 525 if s3.debug: 526 script = "/%s/static/scripts/S3/s3.ui.groupeditems.js" % appname 527 if script not in scripts: 528 scripts.append(script) 529 else: 530 script = "/%s/static/scripts/S3/s3.groupeditems.min.js" % appname 531 if script not in scripts: 532 scripts.append(script) 533 534 # Inject widget instantiation 535 if not options: 536 options = {} 537 script = """$("#%(widget_id)s").groupedItems(%(options)s)""" % \ 538 {"widget_id": widget_id, 539 "options": json.dumps(options), 540 } 541 s3.jquery_ready.append(script)

542

543 # ============================================================================= 544 -class S3GroupedItemsTable(object):

545 """ 546 Helper class to render representations of a grouped items report 547 """ 548

549 - def __init__(self, 550 resource, 551 title = None, 552 data = None, 553 aggregate = None, 554 field_types = None, 555 group_headers = False, 556 totals_label = None, 557 pdf_header = DEFAULT, 558 pdf_footer = None, 559 ):

560 """ 561 Constructor 562 563 @param resource: the resource 564 @param title: the report title 565 @param data: the JSON data (as dict) 566 @param aggregate: the aggregation functions as list of tuples 567 (method, colname) 568 @param field_types: the field types as dict {colname: type} 569 @param group_headers: render group header rows 570 @param totals_label: the label for the aggregated rows 571 (default: "Total") 572 @param pdf_header: callable or static HTML to use as 573 document header, function(r, title=title) 574 @param pdf_footer: callable or static HTML to use as 575 document footer, function(r) 576 """ 577 578 self.resource = resource 579 self.title = title 580 self.data = data 581 582 self.aggregate = aggregate 583 self.field_types = field_types 584 585 self.totals_label = totals_label 586 self.group_headers = group_headers 587 588 if pdf_header is DEFAULT: 589 self.pdf_header = self._pdf_header 590 else: 591 self.pdf_header = pdf_header 592 593 self.pdf_footer = pdf_footer

594 595 # -------------------------------------------------------------------------

596 - def html(self):

597 """ 598 Produce a HTML representation of the grouped table 599 600 @return: a TABLE instance 601 """ 602 603 table = TABLE() 604 605 self.html_render_table_header(table) 606 607 tbody = TBODY() 608 self.html_render_group(tbody, self.data) 609 table.append(tbody) 610 611 self.html_render_table_footer(table) 612 613 return table

614 615 # -------------------------------------------------------------------------

616 - def pdf(self, r, filename=None):

617 """ 618 Produce a PDF representation of the grouped table 619 620 @param r: the S3Request 621 @return: the PDF document 622 """ 623 624 # Styles for totals and group totals rows 625 styles = {"tr.gi-column-totals": { 626 "background-color": "black", 627 "color": "white", 628 }, 629 "tr.gi-group-footer.gi-level-1": { 630 "background-color": "lightgrey", 631 }, 632 "tr.gi-group-header.gi-level-1": { 633 "background-color": "lightgrey", 634 }, 635 } 636 637 title = self.title 638 639 pdf_header = self.pdf_header 640 if callable(pdf_header): 641 pdf_header = lambda r, title=title: self.pdf_header(r, title=title) 642 643 pdf_footer = self.pdf_footer 644 645 from s3.s3export import S3Exporter 646 exporter = S3Exporter().pdf 647 return exporter(self.resource, 648 request = r, 649 pdf_title = title, 650 pdf_header = pdf_header, 651 pdf_header_padding = 12, 652 pdf_footer = pdf_footer, 653 pdf_callback = lambda r: self.html(), 654 pdf_table_autogrow = "B", 655 pdf_orientation = "Landscape", 656 pdf_html_styles = styles, 657 pdf_filename = filename, 658 )

659 660 # -------------------------------------------------------------------------

661 - def xls(self, r, filename=None):

662 """ 663 Produce an XLS sheet of the grouped table 664 665 @param r: the S3Request 666 @return: the XLS document 667 """ 668 669 # Prepare the XLS data array 670 field_types = self.field_types 671 672 data = self.data 673 columns = data.get("c") 674 labels = data.get("l") 675 676 aggregate = self.aggregate 677 if aggregate: 678 functions = dict((c, m) for m, c in aggregate) 679 else: 680 functions = {} 681 682 # Get column headers and field types 683 types = {} 684 for column in columns: 685 686 # For virtual fields with numeric aggregation, designate 687 # the field type as "double": 688 field_type = field_types.get(column, "virtual") 689 if field_type == "virtual": 690 method = functions.get(column) 691 if method and method != "count": 692 field_type = "double" 693 types[column] = field_type 694 695 # Append the rows 696 rows = [] 697 self.xls_group_data(rows, data) 698 699 # Footer row 700 self.xls_table_footer(rows) 701 702 xlsdata = {"columns": columns, 703 "headers": labels, 704 "types": types, 705 "rows": rows, 706 } 707 708 # Export as XLS 709 from s3.s3export import S3Exporter 710 exporter = S3Exporter().xls 711 return exporter(xlsdata, 712 title = self.title, 713 use_colour = True, 714 evenodd = False, 715 )

716 717 # -------------------------------------------------------------------------

718 - def xls_group_data(self, rows, group, level=0):

719 """ 720 Append a group to the XLS data 721 722 @param rows: the XLS rows array to append to 723 @param group: the group dict 724 @param level: the grouping level 725 """ 726 727 subgroups = group.get("d") 728 items = group.get("i") 729 730 if self.group_headers and level > 0: 731 self.xls_group_header(rows, group, level=level) 732 733 if subgroups: 734 for subgroup in subgroups: 735 self.xls_group_data(rows, subgroup, level = level + 1) 736 elif items: 737 for item in items: 738 self.xls_item_data(rows, item, level = level) 739 740 if level > 0: 741 self.xls_group_footer(rows, group, level=level)

742 743 # -------------------------------------------------------------------------

744 - def xls_group_header(self, rows, group, level=0):

745 """ 746 Render the group header (=group label) 747 748 @param row: the XLS rows array to append to 749 @param group: the group dict 750 @param level: the grouping level 751 """ 752 753 columns = self.data.get("c") 754 value = group.get("v") 755 756 if not value: 757 value = "" 758 row = {"_group": {"label": s3_unicode(s3_strip_markup(value)), 759 "span": len(columns), 760 "totals": False, 761 }, 762 "_style": "subheader", 763 } 764 rows.append(row)

765 766 # -------------------------------------------------------------------------

767 - def xls_group_footer(self, rows, group, level=0):

768 """ 769 Append a group footer to the XLS data 770 771 @param rows: the XLS rows array to append to 772 @param group: the group dict 773 @param level: the grouping level 774 """ 775 776 columns = self.data.get("c") 777 totals = group.get("t") 778 779 if self.group_headers: 780 value = self.totals_label 781 else: 782 v = group.get("v") 783 value = "%s %s" % (s3_unicode(s3_strip_markup(v)), 784 self.totals_label, 785 ) 786 row = {} 787 footer = {} 788 789 span = 0 790 has_totals = False 791 792 if not totals: 793 if self.group_headers: 794 return 795 label = value 796 elif columns: 797 label = None 798 for column in columns: 799 has_value = column in totals 800 if label is None: 801 if not has_value: 802 span += 1 803 continue 804 else: 805 label = value 806 has_totals = True 807 row[column] = totals[column] if has_value else "" 808 809 row["_group"] = {"label": label, 810 "span": span, 811 "totals": has_totals, 812 } 813 row["_style"] = "subtotals" 814 rows.append(row)

815 816 # -------------------------------------------------------------------------

817 - def xls_table_footer(self, rows):

818 """ 819 Render the table footer 820 821 @param table: the TABLE instance 822 """ 823 824 data = self.data 825 columns = data.get("c") 826 totals = data.get("t") 827 828 if not totals: 829 return 830 831 row = {} 832 if columns: 833 label = None 834 span = 0 835 for column in columns: 836 has_value = column in totals 837 if label is None: 838 if not has_value: 839 span += 1 840 continue 841 else: 842 label = self.totals_label 843 row[column] = totals[column] if has_value else "" 844 845 row["_group"] = {"label": label, 846 "span": span, 847 "totals": True, 848 } 849 row["_style"] = "totals" 850 rows.append(row)

851 852 # -------------------------------------------------------------------------

853 - def xls_item_data(self, rows, item, level=0):

854 """ 855 Append an item to the XLS data 856 857 @param rows: the XLS rows array to append to 858 @param item: the item dict 859 @param level: the grouping level 860 """ 861 862 columns = self.data["c"] 863 cells = {} 864 865 for column in columns: 866 cells[column] = item.get(column) 867 868 rows.append(cells)

869 870 # -------------------------------------------------------------------------

871 - def html_render_table_header(self, table):

872 """ 873 Render the table header 874 875 @param table: the TABLE instance 876 """ 877 878 data = self.data 879 880 columns = data.get("c") 881 labels = data.get("l") 882 883 header_row = TR(_class="gi-column-headers") 884 if columns: 885 for column in columns: 886 label = labels.get(column, column) 887 header_row.append(TH(label)) 888 table.append(THEAD(header_row))

889 890 # -------------------------------------------------------------------------

891 - def html_render_table_footer(self, table):

892 """ 893 Render the table footer 894 895 @param table: the TABLE instance 896 """ 897 898 data = self.data 899 900 columns = data.get("c") 901 totals = data.get("t") 902 903 if not totals: 904 return 905 906 footer_row = TR(_class="gi-column-totals") 907 if columns: 908 label = None 909 span = 0 910 for column in columns: 911 has_value = column in totals 912 if label is None: 913 if not has_value: 914 span += 1 915 continue 916 else: 917 label = TD(SPAN(self.totals_label), 918 _class = "gi-column-totals-label", 919 _colspan = span, 920 ) 921 footer_row.append(label) 922 value = totals[column] if has_value else "" 923 footer_row.append(TD(value)) 924 table.append(TFOOT(footer_row))

925 926 # -------------------------------------------------------------------------

927 - def html_render_group(self, tbody, group, level=0):

928 """ 929 Render a group of rows 930 931 @param tbody: the TBODY or TABLE to append to 932 @param group: the group dict 933 @param level: the grouping level 934 """ 935 936 if self.group_headers and level > 0: 937 self.html_render_group_header(tbody, group, level=level) 938 939 subgroups = group.get("d") 940 items = group.get("i") 941 942 if subgroups: 943 for subgroup in subgroups: 944 self.html_render_group(tbody, subgroup, 945 level = level + 1, 946 ) 947 elif items: 948 for item in items: 949 self.html_render_item(tbody, item, level=level) 950 951 if level > 0: 952 self.html_render_group_footer(tbody, group, level=level)

953 954 # -------------------------------------------------------------------------

955 - def html_render_group_header(self, tbody, group, level=0):

956 """ 957 Render the group header (=group label) 958 959 @param tbody: the TBODY or TABLE to append to 960 @param group: the group dict 961 @param level: the grouping level 962 """ 963 964 data = self.data 965 966 columns = data.get("c") 967 value = group.get("v") 968 969 if not value: 970 value = "" 971 header = TD(s3_unicode(s3_strip_markup(value)), 972 _colspan = len(columns) if columns else None, 973 ) 974 975 tbody.append(TR(header, 976 _class="gi-group-header gi-level-%s" % level, 977 ))

978 979 # -------------------------------------------------------------------------

980 - def html_render_group_footer(self, tbody, group, level=0):

981 """ 982 Render the group footer (=group totals) 983 984 @param tbody: the TBODY or TABLE to append to 985 @param group: the group dict 986 @param level: the grouping level 987 """ 988 989 columns = self.data.get("c") 990 totals = group.get("t") 991 992 if self.group_headers: 993 value = self.totals_label 994 else: 995 v = group.get("v") 996 value = "%s %s" % (s3_unicode(s3_strip_markup(v)), 997 self.totals_label, 998 ) 999 1000 footer_row = TR(_class="gi-group-footer gi-level-%s" % level) 1001 if not totals: 1002 if not self.group_headers: 1003 footer_row.append(TD(value, _colspan = len(columns))) 1004 tbody.append(footer_row) 1005 return 1006 1007 if columns: 1008 label = None 1009 span = 0 1010 for column in columns: 1011 has_value = column in totals 1012 if label is None: 1013 if not has_value: 1014 span += 1 1015 continue 1016 else: 1017 label = TD(value, 1018 _class = "gi-group-footer-label", 1019 _colspan = span, 1020 ) 1021 footer_row.append(label) 1022 total = totals[column] if has_value else "" 1023 footer_row.append(TD(total)) 1024 1025 tbody.append(footer_row)

1026 1027 # -------------------------------------------------------------------------

1028 - def html_render_item(self, tbody, item, level=0):

1029 """ 1030 Render an item 1031 1032 @param tbody: the TBODY or TABLE to append to 1033 @param item: the item dict 1034 @param level: the grouping level 1035 """ 1036 1037 columns = self.data["c"] 1038 cells = [] 1039 1040 for column in columns: 1041 cells.append(TD(item.get(column, ""))) 1042 tbody.append(TR(cells, _class="gi-item gi-level-%s" % level))

1043 1044 # ------------------------------------------------------------------------- 1045 @staticmethod

1046 - def _pdf_header(r, title=None):

1047 """ 1048 Default PDF header (report title as H2) 1049 """ 1050 1051 return H2(title)

1052

1053 # ============================================================================= 1054 -class S3GroupedItems(object):

1055 """ 1056 Helper class representing dict-like items grouped by 1057 attribute values, used by S3GroupedItemsReport 1058 """ 1059

1060 - def __init__(self, items, groupby=None, aggregate=None, values=None):

1061 """ 1062 Constructor 1063 1064 @param items: ordered iterable of items (e.g. list, tuple, 1065 iterator, Rows), grouping tries to maintain 1066 the original item order 1067 @param groupby: attribute key or ordered iterable of 1068 attribute keys (e.g. list, tuple, iterator) 1069 for the items to be grouped by; grouping 1070 happens in order of appearance of the keys 1071 @param aggregate: aggregates to compute, list of tuples 1072 (method, key) 1073 @param value: the grouping values for this group (internal) 1074 """ 1075 1076 self._groups_dict = {} 1077 self._groups_list = [] 1078 1079 self.values = values or {} 1080 1081 self._aggregates = {} 1082 1083 if groupby: 1084 if isinstance(groupby, basestring): 1085 # Single grouping key 1086 groupby = [groupby] 1087 else: 1088 groupby = list(groupby) 1089 1090 self.key = groupby.pop(0) 1091 self.groupby = groupby 1092 self.items = None 1093 for item in items: 1094 self.add(item) 1095 else: 1096 self.key = None 1097 self.groupby = None 1098 self.items = list(items) 1099 1100 if aggregate: 1101 if type(aggregate) is tuple: 1102 aggregate = [aggregate] 1103 for method, key in aggregate: 1104 self.aggregate(method, key)

1105 1106 # ------------------------------------------------------------------------- 1107 @property

1108 - def groups(self):

1109 """ Generator for iteration over subgroups """ 1110 1111 groups = self._groups_dict 1112 for value in self._groups_list: 1113 yield groups.get(value)

1114 1115 # -------------------------------------------------------------------------

1116 - def __getitem__(self, key):

1117 """ 1118 Getter for the grouping values dict 1119 1120 @param key: the grouping key 1121 1122 """ 1123 1124 if type(key) is tuple: 1125 return self.aggregate(key[0], key[1]).result 1126 else: 1127 return self.values.get(key)

1128 1129 # -------------------------------------------------------------------------

1130 - def add(self, item):

1131 """ 1132 Add a new item, either to this group or to a subgroup 1133 1134 @param item: the item 1135 """ 1136 1137 # Remove all aggregates 1138 if self._aggregates: 1139 self._aggregates = {} 1140 1141 key = self.key 1142 if key: 1143 1144 raw = item.get("_row") 1145 if raw is None: 1146 value = item.get(key) 1147 else: 1148 # Prefer raw values for grouping over representations 1149 try: 1150 value = raw.get(key) 1151 except (AttributeError, TypeError): 1152 # _row is not a dict 1153 value = item.get(key) 1154 1155 if type(value) is list: 1156 # list:type => item belongs into multiple groups 1157 add_to_group = self.add_to_group 1158 for v in value: 1159 add_to_group(key, v, item) 1160 else: 1161 self.add_to_group(key, value, item) 1162 else: 1163 # No subgroups 1164 self.items.append(item)

1165 1166 # -------------------------------------------------------------------------

1167 - def add_to_group(self, key, value, item):

1168 """ 1169 Add an item to a subgroup. Create that subgroup if it does not 1170 yet exist. 1171 1172 @param key: the grouping key 1173 @param value: the grouping value for the subgroup 1174 @param item: the item to add to the subgroup 1175 """ 1176 1177 groups = self._groups_dict 1178 if value in groups: 1179 group = groups[value] 1180 group.add(item) 1181 else: 1182 values = dict(self.values) 1183 values[key] = value 1184 group = S3GroupedItems([item], 1185 groupby = self.groupby, 1186 values = values, 1187 ) 1188 groups[value] = group 1189 self._groups_list.append(value) 1190 return group

1191 1192 # -------------------------------------------------------------------------

1193 - def get_values(self, key):

1194 """ 1195 Get a list of attribute values for the items in this group 1196 1197 @param key: the attribute key 1198 @return: the list of values 1199 """ 1200 1201 if self.items is None: 1202 return None 1203 1204 values = [] 1205 append = values.append 1206 extend = values.extend 1207 1208 for item in self.items: 1209 1210 raw = item.get("_row") 1211 if raw is None: 1212 # Prefer raw values for aggregation over representations 1213 value = item.get(key) 1214 else: 1215 try: 1216 value = raw.get(key) 1217 except (AttributeError, TypeError): 1218 # _row is not a dict 1219 value = item.get(key) 1220 1221 if type(value) is list: 1222 extend(value) 1223 else: 1224 append(value) 1225 return values

1226 1227 # -------------------------------------------------------------------------

1228 - def aggregate(self, method, key):

1229 """ 1230 Aggregate item attribute values (recursively over subgroups) 1231 1232 @param method: the aggregation method 1233 @param key: the attribute key 1234 1235 @return: an S3GroupAggregate instance 1236 """ 1237 1238 aggregates = self._aggregates 1239 if (method, key) in aggregates: 1240 # Already computed 1241 return aggregates[(method, key)] 1242 1243 if self.items is not None: 1244 # No subgroups => aggregate values in this group 1245 values = self.get_values(key) 1246 aggregate = S3GroupAggregate(method, key, values) 1247 else: 1248 # Aggregate recursively over subgroups 1249 combine = S3GroupAggregate.aggregate 1250 aggregate = combine(group.aggregate(method, key) 1251 for group in self.groups) 1252 1253 # Store aggregate 1254 aggregates[(method, key)] = aggregate 1255 1256 return aggregate

1257 1258 # -------------------------------------------------------------------------

1259 - def __repr__(self):

1260 """ Represent this group and all its subgroups as string """ 1261 1262 return self.__represent()

1263 1264 # -------------------------------------------------------------------------

1265 - def __represent(self, level=0):

1266 """ 1267 Represent this group and all its subgroups as string 1268 1269 @param level: the hierarchy level of this group (for indentation) 1270 """ 1271 1272 output = "" 1273 indent = " " * level 1274 1275 aggregates = self._aggregates 1276 for aggregate in aggregates.values(): 1277 output = "%s\n%s %s(%s) = %s" % (output, 1278 indent, 1279 aggregate.method, 1280 aggregate.key, 1281 aggregate.result, 1282 ) 1283 if aggregates: 1284 output = "%s\n" % output 1285 1286 key = self.key 1287 if key: 1288 for group in self.groups: 1289 value = group[key] 1290 if group: 1291 group_repr = group.__represent(level = level+1) 1292 else: 1293 group_repr = "[empty group]" 1294 output = "%s\n%s=> %s: %s\n%s" % \ 1295 (output, indent, key, value, group_repr) 1296 else: 1297 for item in self.items: 1298 output = "%s\n%s %s" % (output, indent, item) 1299 output = "%s\n" % output 1300 1301 return output

1302 1303 # -------------------------------------------------------------------------

1304 - def json(self, 1305 fields=None, 1306 labels=None, 1307 represent=None, 1308 as_dict=False, 1309 master=True):

1310 """ 1311 Serialize this group as JSON 1312 1313 @param fields: the columns to include for each item 1314 @param labels: columns labels as dict {key: label}, 1315 including the labels for grouping axes 1316 @param represent: dict of representation methods for grouping 1317 axis values {colname: function} 1318 @param as_dict: return output as dict rather than JSON string 1319 @param master: this is the top-level group (internal) 1320 1321 JSON Format: 1322 1323 {"c": [key, ...], ....... list of keys for visible columns 1324 "g": [key, ...], ....... list of keys for grouping axes 1325 "l": [(key, label), ...], ....... list of key-label pairs 1326 "k": key, ....... grouping key for subgroups 1327 "d": [ ....... list of sub-groups 1328 {"v": string, ....... the grouping value for this subgroup (represented) 1329 "k": key ....... the grouping key for subgroups 1330 "d": [...] ....... list of subgroups (nested) 1331 "i": [ ....... list of items in this group 1332 {key: value, ....... key-value pairs for visible columns 1333 }, ... 1334 ], 1335 "t": { ....... list of group totals 1336 key: value, ....... key-value pairs for totals 1337 } 1338 }, ... 1339 ], 1340 "i": [...], ....... list of items (if no grouping) 1341 "t": [...], ....... list of grand totals 1342 "e": boolean ....... empty-flag 1343 } 1344 """ 1345 1346 T = current.T 1347 1348 output = {} 1349 1350 if not fields: 1351 raise SyntaxError 1352 1353 if master: 1354 # Add columns and grouping information to top level group 1355 if labels is None: 1356 labels = {} 1357 1358 def check_label(colname): 1359 if colname in labels: 1360 label = labels[colname] or "" 1361 else: 1362 fname = colname.split(".", 1)[-1] 1363 label = " ".join([s.strip().capitalize() 1364 for s in fname.split("_") if s]) 1365 label = labels[colname] = T(label) 1366 return str(label)

1367 1368 grouping = [] 1369 groupby = self.groupby 1370 if groupby: 1371 for axis in groupby: 1372 check_label(axis) 1373 grouping.append(axis) 1374 output["g"] = grouping 1375 1376 columns = [] 1377 for colname in fields: 1378 check_label(colname) 1379 columns.append(colname) 1380 output["c"] = columns 1381 1382 output["l"] = dict((c, str(l)) for c, l in labels.items()) 1383 1384 key = self.key 1385 if key: 1386 output["k"] = key 1387 1388 representations = None 1389 renderer = represent.get(key) if represent else None 1390 1391 # Bulk represent? 1392 if renderer and hasattr(renderer, "bulk"): 1393 values = [group[key] for group in self.groups] 1394 representations = renderer.bulk(values) 1395 1396 data = [] 1397 add_group = data.append 1398 for group in self.groups: 1399 # Render subgroup 1400 gdict = group.json(fields, labels, 1401 represent = represent, 1402 as_dict = True, 1403 master = False, 1404 ) 1405 1406 # Add subgroup attribute value 1407 value = group[key] 1408 if representations is not None: 1409 value = representations.get(value) 1410 elif renderer is not None: 1411 value = renderer(value) 1412 value = s3_unicode(value).encode("utf-8") 1413 gdict["v"] = value 1414 add_group(gdict) 1415 1416 if master: 1417 output["e"] = len(data) == 0 1418 output["d"] = data 1419 output["i"] = None 1420 1421 else: 1422 oitems = [] 1423 add_item = oitems.append 1424 for item in self.items: 1425 # Render item 1426 oitem = {} 1427 for colname in fields: 1428 if colname in item: 1429 value = item[colname] or "" 1430 else: 1431 # Fall back to raw value 1432 raw = item.get("_row") 1433 try: 1434 value = raw.get(colname) 1435 except (AttributeError, TypeError): 1436 # _row is not a dict 1437 value = None 1438 if value is None: 1439 value = "" 1440 else: 1441 value = s3_unicode(value).encode("utf-8") 1442 oitem[colname] = value 1443 add_item(oitem) 1444 1445 if master: 1446 output["e"] = len(oitems) == 0 1447 output["d"] = None 1448 output["i"] = oitems 1449 1450 # Render group totals 1451 aggregates = self._aggregates 1452 totals = {} 1453 for k, a in aggregates.items(): 1454 method, colname = k 1455 # @todo: call represent for totals 1456 totals[colname] = s3_unicode(a.result).encode("utf-8") 1457 output["t"] = totals 1458 1459 # Convert to JSON unless requested otherwise 1460 if master and not as_dict: 1461 output = json.dumps(output, separators=SEPARATORS) 1462 return output

1463

1464 # ============================================================================= 1465 -class S3GroupAggregate(object):

1466 """ Class representing aggregated values """ 1467

1468 - def __init__(self, method, key, values):

1469 """ 1470 Constructor 1471 1472 @param method: the aggregation method (count, sum, min, max, avg) 1473 @param key: the attribute key 1474 @param values: the attribute values 1475 """ 1476 1477 self.method = method 1478 self.key = key 1479 1480 self.values = values 1481 self.result = self.__compute(method, values)

1482 1483 # -------------------------------------------------------------------------

1484 - def __compute(self, method, values):

1485 """ 1486 Compute the aggregated value 1487 1488 @param method: the aggregation method 1489 @param values: the values 1490 1491 @return: the aggregated value 1492 """ 1493 1494 result = None 1495 1496 if values is not None: 1497 try: 1498 values = [v for v in values if v is not None] 1499 except TypeError: 1500 result = None 1501 else: 1502 if method == "count": 1503 result = len(set(values)) 1504 else: 1505 values = [v for v in values 1506 if isinstance(v, (float, int, long))] 1507 1508 if method == "sum": 1509 try: 1510 result = math.fsum(values) 1511 except (TypeError, ValueError): 1512 result = None 1513 elif method == "min": 1514 try: 1515 result = min(values) 1516 except (TypeError, ValueError): 1517 result = None 1518 elif method == "max": 1519 try: 1520 result = max(values) 1521 except (TypeError, ValueError): 1522 result = None 1523 elif method == "avg": 1524 num = len(values) 1525 if num: 1526 try: 1527 result = sum(values) / float(num) 1528 except (TypeError, ValueError): 1529 result = None 1530 else: 1531 result = None 1532 return result

1533 1534 # ------------------------------------------------------------------------- 1535 @classmethod

1536 - def aggregate(cls, items):

1537 """ 1538 Combine sub-aggregates 1539 1540 @param items: iterable of sub-aggregates 1541 1542 @return: an S3GroupAggregate instance 1543 """ 1544 1545 method = None 1546 key = None 1547 values = [] 1548 1549 for item in items: 1550 1551 if method is None: 1552 method = item.method 1553 key = item.key 1554 1555 elif key != item.key or method != item.method: 1556 raise TypeError 1557 1558 if item.values: 1559 values.extend(item.values) 1560 1561 return cls(method, key, values)

1562 1563 # END ========================================================================= 1564

Source Code for Module s3.s3grouped