"""
    ZReportTool
    ===========

This product is intended to take a list of dictionaries as its input,
and output an nicely formatted HTML table.
"""
# $Id: ZReportTool.py,v 1.5 2002/05/15 18:23:30 adrian Exp $
#
# $Log: ZReportTool.py,v $
# Revision 1.5  2002/05/15 18:23:30  adrian
# *** empty log message ***
#
# Revision 1.4  2002/04/15 16:29:39  ahungate
# Changed ZRT to allow table width as a property.
#
# Revision 1.3  2002/04/10 15:14:42  ahungate
# Fixed another bug that prevented associating ZRT with a datasource.
# Fixed bug that prevented displaying with standard headers
# if the headers where ZPT objects (CMF/Plone).
#
# Revision 1.2  2002/04/02 11:38:03  adrian
# Fixed bug in manage_changeDataSource that prevented associating a
# Report Tool with a datasource.
#
# Revision 1.1.1.1  2002/02/03 17:07:16  root
#
#
# Revision 1.12  2002/01/24 10:58:44  adrian
# no message
#
# Revision 1.11  2002/01/22 08:22:00  adrian
# Fixed a persistence bug
# Fixed a url quoting bug
#
# Revision 1.10  2002/01/21 17:15:41  adrian
# Implemented auto-hyperlinking for fields containing URLs or EMail addresses
# Resized titles column for vertical reports
#
# Revision 1.9  2002/01/21 15:41:11  adrian
# Corrected Zope 2.3.x compatibility issue in VisualQueryMixin
# Implimented Child-Reports as Drill-Downs with a property turned off
# Added support for vertical "Record Card" type reports
#
# Revision 1.8  2002/01/21 00:58:08  adrian
# Rebased all queries and datacombiner on the Aqueduct DA
# Added argument support
# Added Drill-Down support to ZReportTool
#
# Revision 1.7  2002/01/15 17:32:37  adrian
# Factored out common code in all-QT and ZDC
# Added support for comparing against constants in all-QT
# Added support for multi-column joins in all-QT
# Added sorting to ZRT
# Added no-print group type to ZRT
# You now set the connection ID when creating a ZVQ
# added property support to ZVQ
# Corrected display of calculated columns in ZRT
#
# Revision 1.6  2002/01/14 19:25:07  adrian
# Removed dubugging code from ZReportTool that prevented object creation.
#
# Revision 1.5  2002/01/13 21:08:58  adrian
# Completed relational logic in ZDataCombiner.
# Fixed up compatibility bugs in combiner and report.
# Started online help pages.
#
# Revision 1.4  2002/01/13 09:36:32  adrian
# Fixed bug in ZReportTool that prevented object creation
#
# Revision 1.3  2001/10/29 09:29:56  adrian
# Cleaned up a number of "Ambiguous name..." warnings
#
# Revision 1.2  2001/10/21 15:03:36  adrian
# no message
#
#
__version__ = '$Revision: 1.5 $'[11:-2]

# Todo
#   Change calculated columns not to use eval()

import urllib
import string
import Globals
import OFS.SimpleItem
import OFS.PropertyManager
import AccessControl.Role
import App.Management
import Acquisition
import Persistence
from DocumentTemplate import DT_HTML
import DataSourceReader

manage_addZReportToolForm = Globals.HTMLFile('www/addZReportTool', globals())
def manage_addZReportTool(self, id, title='', report_class='report', heading_class='heading', data_class='data', grand_total_class='grand_total', data_source='', submit='', REQUEST=None):
    """ """
    if data_source:
        data = getattr(self, data_source)
    else:
        data = ''
    obj = ZReportTool(str(id),
        title=str(title),
        report_class=str(report_class),
        heading_class=str(heading_class),
        data_class=str(data_class),
        grand_total_class=str(grand_total_class),
        data_source=data,
    )
    self._setObject(obj.getId(), obj)
    if REQUEST is not None:
        try: u = self.DestinationURL()
        except: u = REQUEST['URL1']
        if submit==" Add and Edit ": u="%s/%s" % (u, urllib.quote(id))
        REQUEST.RESPONSE.redirect(u+'/manage_workspace')
    return ''

class ZReportTool(
        OFS.PropertyManager.PropertyManager,
        OFS.SimpleItem.SimpleItem,
        Acquisition.Implicit,
        Persistence.Persistent,
        AccessControl.Role.RoleManager,
        DataSourceReader.DataSourceReader,
        ):
    """ Produce an HTML formatted report from a datasource """
    meta_type = 'Z Report Tool'
    id = 'ZReportTool'
    index_html = None

    # Masquerade as function:
    class func_code: pass
    func_code=func_code()
    func_code.co_varnames='self','REQUEST','RESPONSE'
    func_code.co_argcount=3

    manage_main = Globals.HTMLFile('www/reportOptions', globals())
    manage_options  = (
        (
            {'label': 'Column Options', 'action': 'manage_main',
             'help': ('ZDataQueryKit', 'ReportOptions.stx')},
        ) +
        OFS.PropertyManager.PropertyManager.manage_options +
        (
            {'label': 'View', 'action': ''},
        ) +
        AccessControl.Role.RoleManager.manage_options +
        OFS.SimpleItem.SimpleItem.manage_options
    )
    _properties	=   (
        {'id': 'width', 'type': 'string', 'mode': 'w'},
        {'id': 'standard_headers', 'type': 'boolean', 'mode': 'w'},
        {'id': 'horizontal_records', 'type': 'boolean', 'mode': 'w'},
        {'id': 'drill_down', 'type': 'boolean', 'mode': 'w'},
        {'id': 'title', 'type': 'string', 'mode': 'w'},
        {'id': 'report_class', 'type': 'string', 'mode': 'w'},
        {'id': 'heading_class', 'type': 'string', 'mode': 'w'},
        {'id': 'data_class', 'type': 'string', 'mode': 'w'},
        {'id': 'grand_total_class', 'type': 'string', 'mode': 'w'},
    )
    width = '100%'
    standard_headers = 1
    horizontal_records = 1
    drill_down = 1
    # Contains a list of column names in display order
    columnOrder = ()
    # Contains either {'name': ..., 'type': ...} for database columns
    # or {'name': ..., 'type': 'calc', 'formular': ... } for calculated columns
    columnData = {}
    # Contains the printing titles of each column
    columnTitle = {}
    # Contains the alignment of each column
    columnAlign = {}
    # Contains the CSS class of the heading and subtotal/footer rows grouped on this column
    columnClass = {}
    # 0 = Hide, 1 = Normal, 2 = Normal w/ Sub-Totals, 3 = Group hide column, 4 = Group show column
    # 5 = Group hide details, 6 = Group hide column and summary, 7 = Sort Ascending, 8 = Sort Descending
    flagGroup = {}
    # A tuple containing the grouping columns in order
    groupColumns = ()
    # A tuple containing the totals columns in order
    totalColumns = ()
    # A tuple containing the calculated columns in order
    calcColumns = ()
    # The number of visible columns
    displayCount = 0
    # The Dynamic Drill-Downs List
    drillDowns = {}

    def __init__(self, id, title='', report_class='report', heading_class='heading', data_class='data', grand_total_class='grand_total', data_source=''):
        """ """
        self.id = id
        self.title = title
        self.report_class = report_class
        self.heading_class = heading_class
        self.data_class = data_class
        self.grand_total_class = grand_total_class
        self.manage_changeDataSource(data_source)

    def manage_changeDataSource(self, data_source, REQUEST=None):
        """ Change the datasource for this report. Causes a complete reset """
        columnOrder = ()
        columnData = {}
        columnTitle = {}
        columnAlign = {}
        columnClass = {}
        flagGroup = {}
        groupColumns = ()
        totalColumns = ()
        calcColumns = ()
        displayCount = 0
        drillDowns = {}
        try:
            self._setDataSource(datasource=data_source)
            columnOrder = self._getColumnList()
# Ensure that this is a deep copy
            dd = self._getDataDictionary()
            for columnName in columnOrder:
                columnData[columnName] = {'name': '%s' % dd[columnName]['name'], 'type': '%s' % dd[columnName]['type']}
                flagGroup[columnName] = '1'
                columnTitle[columnName] = columnName
                columnAlign[columnName] = 'LEFT'
                if columnData[columnName]['type'] == 'i' or columnData[columnName]['type'] == 'l' or columnData[columnName]['type'] == 'n':
                    columnAlign[columnName] = 'RIGHT'
                columnClass[columnName] = columnName
                drillDowns[columnName] = ""
                displayCount = displayCount + 1
            self.columnOrder = tuple(columnOrder)
            self.columnData = columnData
            self.columnTitle = columnTitle
            self.columnAlign = columnAlign
            self.columnClass = columnClass
            self.flagGroup = flagGroup
            self.groupColumns = tuple(groupColumns)
            self.totalColumns = tuple(totalColumns)
            self.calcColumns = tuple(calcColumns)
            self.displayCount = displayCount
            self.drillDowns = drillDowns
            return self.manage_saveDataOptions(REQUEST=REQUEST, manage_saveDataOptions='Move First')
        except:
            if data_source != '':
                self.manage_changeDataSource('')
            if REQUEST:
                message="Data Definitions cleared."
                return self.manage_main(self, REQUEST, manage_tabs_message=message)

    def manage_saveDataOptions(self, REQUEST=None, manage_saveDataOptions=None):
        """ """
        submit = manage_saveDataOptions
        # Get object state
        columnOrder = map(None, self.columnOrder)
        columnData = self.columnData
        columnTitle = self.columnTitle
        columnAlign = self.columnAlign
        columnClass = self.columnClass
        flagGroup = self.flagGroup
        drillDowns = self.drillDowns
        groupColumns = self.groupColumns
        totalColumns = self.totalColumns
        calcColumns = self.calcColumns
        displayCount = self.displayCount
        # And now to work...
        cols1 = []
        cols2 = []
        for columnName in self.columnOrder:
            if submit == 'Move First':
                if REQUEST and REQUEST.get('f_%s' % columnName, ''):
                    cols1.append(columnName)
                else:
                    cols2.append(columnName)
            elif submit == 'Delete':
                if REQUEST.get('f_%s' % columnName, ''):
                    del columnData[columnName]
                    del columnTitle[columnName]
                    del columnAlign[columnName]
                    del columnClass[columnName]
                    del flagGroup[columnName]
                    del drillDowns[columnName]
                else:
                    cols1.append(columnName)
            elif submit == 'Save Changes':
                columnTitle[columnName] = REQUEST.get('l_%s' % columnName, columnName)
                columnAlign[columnName] = REQUEST.get('a_%s' % columnName, 'LEFT')
                columnClass[columnName] = REQUEST.get('c_%s' % columnName, columnName)
                if columnData[columnName]['type'] == 'calc':
                    columnData[columnName]['formular'] = REQUEST.get('cf_%s' % columnName, "'N/A'")
                else:
                    flagGroup[columnName] = REQUEST.get('g_%s' % columnName, '1')
                drillDowns[columnName] = REQUEST.get('d_%s' % columnName, '')
            else:
                raise "Parameter error", "Don't know how to %s on this form" % submit
        if submit in ['Move First', 'Delete']:
            columnOrder = cols1 + cols2
        groupColumns = []
        totalColumns = []
        calcColumns = []
        displayCount = 0
        for columnName in columnOrder:
            if flagGroup[columnName] in ['3', '4', '5', '6']:
                groupColumns.append(columnName)
            if flagGroup[columnName] in ['2']:
                totalColumns.append(columnName)
            if columnData[columnName]['type'] == 'calc':
                calcColumns.append(columnName)
            if not flagGroup[columnName] in ['0', '3', '6']:
                displayCount = displayCount + 1
        # Store object state
        self.columnOrder = tuple(columnOrder)
        self.columnData = columnData
        self.columnTitle = columnTitle
        self.columnAlign = columnAlign
        self.columnClass = columnClass
        self.flagGroup = flagGroup
        self.drillDowns = drillDowns
        self.groupColumns = tuple(groupColumns)
        self.totalColumns = tuple(totalColumns)
        self.calcColumns = tuple(calcColumns)
        self.displayCount = displayCount
        # Return success to the browser
        if REQUEST:
            message="Data Definitions Updated."
            return self.manage_main(self, REQUEST, manage_tabs_message=message)

    def manage_addColumn(self, columnName='', columnTitle='', columnFormular='', REQUEST=None):
        """ Add a new calculated column to every record """
        if columnName in self.columnOrder:
            raise "Column already exists", "The requested column name already exists in this dataset"
        # Get object state
        columnOrder = map(None, self.columnOrder)
        columnData = self.columnData
        columnTitle = self.columnTitle
        columnAlign = self.columnAlign
        columnClass = self.columnClass
        flagGroup = self.flagGroup
        drillDowns = self.drillDowns
        displayCount = self.displayCount
        # And now to work...
        columnOrder.append(columnName)
        columnData[columnName] = {'name': columnName, 'type': 'calc', 'formular': columnFormular}
        columnTitle[columnName] = columnTitle
        columnAlign[columnName] = 'LEFT'
        columnClass[columnName] = columnName
        flagGroup[columnName] = '1'
        drillDowns[columnName] = ''
        displayCount = displayCount + 1
        # Store object state
        self.columnOrder = tuple(columnOrder)
        self.columnData = columnData
        self.columnTitle = columnTitle
        self.columnAlign = columnAlign
        self.columnClass = columnClass
        self.flagGroup = flagGroup
        self.drillDowns = drillDowns
        self.displayCount = displayCount
        # Return success to the browser
        if REQUEST:
            message="Column Added."
            return self.manage_main(self,REQUEST, manage_tabs_message=message)

    def _record(self, record, CSS):
        """ Take a database record and output a record
            containing only the display columns """
# At the moment I use eval() to calculate the formular of a calculated column
# This is very-unsafe, so I will need to dig out the one dtml-var (etc) uses
        rec = {'class': CSS}
        for columnName in self.columnOrder:
            try:
                if self.columnData[columnName]['type'] == 'calc':
                    rec[columnName] = eval(self.columnData[columnName]['formular'], record)
                else:
                    rec[columnName] = record[columnName]
            except:
                rec[columnName] = ""
        return rec

    def _chewGroup(self, branch, groups):
        """ Recurse down the dataTree, moving data rows to the
            detail branches and creating totals rows """
        if not groups:
            return
        this_group = groups[0]
        remaining_groups = groups[1:]
        rows = map(None, branch['~ rows'])
        branch['~ rows'] = []
        for record in rows:
            this_value = record[this_group]
            if not branch.has_key(this_value):
                branch[this_value] = {}
            if not branch[this_value].has_key('~ rows'):
                branch[this_value]['~ rows'] = []
            branch[this_value]['~ rows'].append(record)
        for this_value in branch.keys():
            if this_value != '~ rows':
                self._chewGroup(branch[this_value], remaining_groups)

# Node total for leaf node == total of rows
# Node total for non-leaf node == total of child-totals
    def _makeTotals(self, group, branch, groups):
        """ Add up the totals in this branch """
        rList = []
        tRec = {}
        kList = branch.keys()
        if branch.has_key('~ rows'):
            kList.remove('~ rows')
            rList = branch['~ rows']
        if branch.has_key('~ total'):
            kList.remove('~ total')
            tRec = branch['~ total']
        kList.sort()
        for subBranch in kList:
            self._makeTotals(groups[0], branch[subBranch], groups[1:])

        newRecord = {}
        if group:
            css = self.columnClass[group]
        else:
            css = self.grand_total_class
        for columnName in self.totalColumns:
            newRecord[columnName] = 0
        if kList:
# Total child-totals
            for subBranch in kList:
                if branch[subBranch].has_key('~ total'):
                    record = branch[subBranch]['~ total']
                    for columnName in self.totalColumns:
                        newRecord[columnName] = newRecord[columnName] + record[columnName]
        else:
# Total of rows
            for record in branch['~ rows']:
                for columnName in self.totalColumns:
                    newRecord[columnName] = newRecord[columnName] + record[columnName]
        branch['~ total'] = self._record(newRecord, css)

    def _chewData(self):
        """ Return the dataTree """
        dataTree = {}
        dataTree['~ rows'] = []
        data = self._getData()
        for record in data:
            dataTree['~ rows'].append(self._record(record, self.data_class))
        self._chewGroup(dataTree, self.groupColumns)
        self._makeTotals('', dataTree, self.groupColumns)
        return dataTree

    def _Header(self):
        """ Print the table header row """
        if not self.horizontal_records:
            return ''
        ret = "<tr>\n"
        for columnName in self.columnOrder:
            if not self.flagGroup[columnName] in ['0', '3', '6']:
                ret = ret + '<th align="' + self.columnAlign[columnName] + '" class="' + self.heading_class + '">' + self.columnTitle[columnName] + '</th>\n'
        ret = ret + '</tr>\n'
        return ret

    def _Field(self, Record, columnName, _dataOnly=0):
        """ Format field according to datatype and content """
        str = '<td align="' + self.columnAlign[columnName] + '" class="' + Record['class'] + '">'
        if Record.has_key(columnName):
            value = Record[columnName]
            dataType = self.columnData[columnName]['type']
            format = '%s'
            if dataType == 'i':
                format = '%d'
            elif dataType == 'l' or dataType == 'n':
                format = '%.2f'
            if value:
                data = format % value
                if _dataOnly:
                    return data
                if self.drill_down and self.drillDowns.has_key(columnName) and self.drillDowns[columnName]:
                    argName = string.replace(self.columnTitle[columnName], ' ', '_')
                    if self.REQUEST.has_key('QUERY_STRING'):
                        qs = self.REQUEST['QUERY_STRING']
                        if qs:
                            qs = string.split(qs, '&')
                        else:
                            qs = []
                    else:
                        qs = []
                    qs.append('%s=%s' % (urllib.quote(argName), urllib.quote(data)))
                    args = string.join(qs, '&')
                    str = str + '<a href="%s?%s">%s</a>' % (self.drillDowns[columnName], args, data)
                elif format == "%s" and string.find(data, '@')>-1 and string.find(data, ' ') == -1:
                    str = str + '<a href="mailto:%s">%s</a>' % (data, data)
                elif format == "%s" and (data[:5] == 'http:' or data[:6] == 'https:'):
                    str = str + '<a href="%s">%s</a>' % (data, data)
                elif format == "%s" and data[:4] == 'www.':
                    str = str + '<a href="http://%s">http://%s</a>' % (data, data)
                else:
                    str = str + data
            else:
                if _dataOnly:
                    return ''
                str = str + '&nbsp;'
        else:
            if _dataOnly:
                return ''
            str = str + '&nbsp;'
        str = str + '</td>\n'
        return str

    def _Row(self, Record):
        """ Display a formatted row of data """
        str = ""
        if self.horizontal_records:
            str = "<tr>\n"
            for columnName in self.columnOrder:
                if not self.flagGroup[columnName] in ['0', '3', '6']:
                    str = str + self._Field(Record, columnName)
            str = str + "</tr>\n"
        else:
            str = str + '<table border="0" cellpadding="2" cellspacing="0" width="100%%" class="%s">\n' % self.report_class
            for columnName in self.columnOrder:
                str = str + '<tr valign="bottom">\n'
                if not self.flagGroup[columnName] in ['0', '3', '6']:
                    str = str + '<th align="right" width="15%" class="' + self.heading_class + '">' + self.columnTitle[columnName] + '</th>\n'
                    str = str + self._Field(Record, columnName)
                str = str + "</tr>\n"
            if not self.drill_down:
                for columnName in self.columnOrder:
                    if self.drillDowns.has_key(columnName) and self.drillDowns[columnName]:
                        argName = string.replace(self.columnTitle[columnName], ' ', '_')
                        self.REQUEST.set(argName, self._Field(Record, columnName, _dataOnly=1))
                        subrep = getattr(self, self.drillDowns[columnName])
                        str = str + '<tr valign="top">\n'
                        str = str + '<th align="right" width="15%" class="' + self.heading_class + '">%s</th>\n' % (subrep.title_or_id(), )
                        str = str + "<td>%s</td>\n</tr>\n" % (subrep(self.REQUEST), )
            str = str + "</table><br />\n"
        return str

    def _GroupHeader(self, prefix, thisGroup, thisValue):
        """ Display a header for this group. """
        if not self.horizontal_records:
            return ''
        if thisGroup and self.flagGroup[thisGroup] in ['3', '4', '5']:
            css = self.columnClass[thisGroup]
        else:
            return ''
        if not thisValue:
            thisValue = '[None]'
        str = '<tr>\n'
        str = str + '<td align="LEFT" colspan="%d" class="%s">%s %s</td>\n' % (self.displayCount, css, prefix, thisValue)
        str = str + '</tr>\n'
        return str

    def _GroupFooter(self, prefix, thisGroup, thisValue):
        """ Display a footer for this group. """
        if not self.horizontal_records:
            return ''
        if thisGroup and self.flagGroup[thisGroup] == '6':
            return ''
        str = '<tr>\n'
        if not thisValue:
            thisValue = '[None]'
        if thisGroup:
            if self.totalColumns:
                str = str + '<td align="LEFT" colspan="%d" class="%s">Totals for %s %s</td>\n' % (self.displayCount, self.columnClass[thisGroup], prefix, thisValue)
            else:
                return '' #str = str + '<td align="LEFT" colspan="%d" class="%s">End of %s %s</td>\n' % (self.displayCount, self.columnClass[thisGroup], prefix, thisValue)
        else:
            if self.totalColumns:
                str = str + '<td align="LEFT" colspan="%d" class="%s">Grand Totals</td>\n' % (self.displayCount, self.grand_total_class)
            else:
                return ''
        str = str + '</tr>\n'
        return str

    def _sortFunc(self, rec1, rec2):
        """ Sort the two rows based on the fields set to be sorted """
        for columnName in self.columnOrder:
            if self.flagGroup[columnName] == '7':
                v = cmp(rec1[columnName], rec2[columnName])
            elif self.flagGroup[columnName] == '8':
                v = cmp(rec2[columnName], rec1[columnName])
            else:
                v = 0
            if v:
                return v
        return 0

    def _DescendRows(self, branch, prefix, thisGroup, thisValue, groups):
        """ """
        retStr = self._GroupHeader(prefix, thisGroup, thisValue)
        rList = []
        tRec = {}
        kList = branch.keys()
        if branch.has_key('~ rows'):
            kList.remove('~ rows')
            rList = branch['~ rows']
        if branch.has_key('~ total'):
            kList.remove('~ total')
            tRec = branch['~ total']
        if thisGroup == '' or self.flagGroup[thisGroup] not in ['5']:
            kList.sort()
            for k in kList:
                retStr = retStr + self._DescendRows(branch=branch[k], prefix="%s %s" % (prefix, thisValue), thisGroup=groups[0], thisValue=k, groups=groups[1:])
# Sort the data records
            rList.sort(self._sortFunc)
            for Record in rList:
                retStr = retStr + self._Row(Record)
        retStr = retStr + self._GroupFooter(prefix, thisGroup, thisValue)
        if self.totalColumns:
            retStr = retStr + self._Row(tRec)
        return retStr

    def __call__(self, REQUEST=None):
        """ Call the report from a webpage (Or anywhere). """
# Setup a temporary data-tree
        dataTree = self._chewData()
# Print the data from the data-tree
        dtml = ""
        if self.standard_headers:
            dtml = dtml + "<dtml-var standard_html_header>\n"
        dtml = dtml + "<!-- Report Generated using ZReportTool written by Adrian 'Haqa' Hungate - http://www.zope.org/Members/haqa/ZReportTool -->\n"
        if self.horizontal_records:
            dtml = dtml + '<center><table border="0" cellpadding="2" cellspacing="1" '
	    if self.width:
	        dtml = dtml + 'width="%s" ' % (self.width, )
	    dtml = dtml + 'class="%s">\n' % self.report_class
            dtml = dtml + self._Header()
        dtml = dtml + self._DescendRows(branch=dataTree, prefix='', thisGroup='', thisValue='', groups=self.groupColumns)
        if self.horizontal_records:
            dtml = dtml + '</table></center>\n'
        if self.standard_headers:
            dtml = dtml + "<dtml-var standard_html_footer>\n"
        obj = DT_HTML.HTML(dtml, self.REQUEST, 'ZVisualQuery - %s' % self.title_and_id())
        return obj(self.aq_parent, self.REQUEST)
