"""
    ZDataCombiner
    =============

Allow multiple datasources to be combined (In SQL Terms 'joined') to form
a single datasource.
"""
# $Id: ZDataCombiner.py,v 1.5 2002/08/18 15:27:16 adrian Exp $
#
# $Log: ZDataCombiner.py,v $
# Revision 1.5  2002/08/18 15:27:16  adrian
# Changed (Fixed) ZDC.
# * It now uses temporary, in memory, indexes
# * It now reverses joins to ensure consistancy
# * It now generates "virtual SQL" so you can see what you are asking.
#
# Revision 1.4  2002/05/17 13:26:11  ahungate
# *** empty log message ***
#
# Revision 1.3  2002/05/15 16:30:52  ahungate
# *** empty log message ***
#
# Revision 1.2  2002/05/14 18:56:22  ahungate
# *** empty log message ***
#
# Revision 1.1.1.1  2002/02/03 17:07:16  root
#
#
# Revision 1.5  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.4  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.3  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.2  2001/10/21 15:03:36  adrian
# no message
#
#
__version__ = '$Revision: 1.5 $'[11:-2]

import Globals
import urllib
import OFS.SimpleItem
import OFS.PropertyManager
import DataSourceReader
import VisualQueryMixin
import DateTime
import string
from DocumentTemplate import DT_HTML
import sys
import Shared.DC.ZRDB.DA
import Shared.DC.ZRDB.Results
import QueryTools

manage_addZDataCombinerForm = Globals.HTMLFile('www/addZDataCombiner', globals())

def manage_addZDataCombiner(self, id, title='', submit='', REQUEST=None):
    """ """
    obj = ZDataCombiner(id, title=title)
    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 ZDataCombiner(
        OFS.PropertyManager.PropertyManager,
        VisualQueryMixin._VisualQueryMixin,
        QueryTools._RelationalLogicMixin,
        Shared.DC.ZRDB.DA.DA,
    ):
    """ """
    meta_type = 'Z Data Combiner'
    title = 'Z Data Combiner'
    id = 'ZDataCombiner'

    manage_combinerOptions = Globals.HTMLFile('www/combinerOptions', globals())
    manage_OutputColumns = Globals.HTMLFile('www/queryOutputColumns', globals())
    manage_options = (
        (
            {'label': 'Combiner Options', 'action': 'manage_combinerOptions',
             'help': ('ZDataQueryKit', 'CombinerOptions.stx'), },
#            {'label': 'Debugging Info', 'action': 'manage_debug'},
        ) +
        OFS.PropertyManager.PropertyManager.manage_options +
        VisualQueryMixin._VisualQueryMixin.manage_options[2:4] +
        Shared.DC.ZRDB.DA.DA.manage_options[1:]
    )
    _properties	= (
        {'id': 'title', 'type': 'string', 'mode': 'w'},
    )

    def __init__(self, id, title=''):
        """ """
        self.id = id
        self.title = title
        self.datasources = {}
        self.columnCache = []
        self.columns = {}
        self.class_name_ = ""
        self.class_file_ = ""
        self._v_brain = None
        self.arguments_src = ""

    def manage_debug(self):
        " Display debugging data "
        html = self.manage_page_header(self) + self.manage_tabs(self)

        html = html + "<p>Datasources:</p><ul>"
        for ds in self.datasources.keys():
            html = html + "<li>%s = %s</li>" % (ds, self.datasources[ds].DSR_columns, )
        html = html + "</ul>"

        html = html + "<p>Simulated SQL:</p><pre>%s</pre>" % (self.template(), )

        html = html + self.manage_page_footer(self)
        return html

    def manage_addDataSource(self, datasource, REQUEST=None):
        """ Add a data source to the mix """
        self._addDataSource(datasource)
        if REQUEST:
            message = "Datasource added."
            return self.manage_combinerOptions(self, REQUEST, manage_tabs_message=message)

    def _addDataSource(self, dsID):
        """ Add a new datasource """
        newDS = None
        if hasattr(self, dsID) or hasattr(dsID, 'getId'):
            try:
                newDS = DataSourceReader.DataSourceReader(acquirer=self, datasource=dsID)
                dsID = newDS.DSR_data_source_id
            except:
                newDS = None
                dsID = None
        if dsID and newDS:
            ds = self.datasources
            ds[dsID] = newDS
            self.datasources = ds
            clist = self.columnCache
            for column in self.datasources[dsID]._getColumnList():
                clist.append((dsID, column))
            self.columnCache = clist
        self._updateTemplate()

    def manage_removeDataSources(self, REQUEST):
        """ Remove a data source from the mix """
        list = self.datasources.keys()
        for item in list:
            if REQUEST.has_key('ds_%s' % item):
                self._removeDataSource(item)
        if REQUEST:
            message = "Datasource removed."
            return self.manage_combinerOptions(self, REQUEST, manage_tabs_message=message)

    def _removeDataSource(self, dsID):
        """ Remove a single datasource """
        self._RJM_RemoveTable(dsID)
        chash = self.columns
        for column in self.columns.keys():
            if QueryTools._getTableName(column) == dsID:
                del chash[column]
        self.columns = chash
        clist = []
        for column in self.columnCache:
            if not column[0] == dsID:
                clist.append(column)
        self.columnCache = clist
        ds = self.datasources
        del ds[dsID]
        self.datasources = ds
        self._updateTemplate()

    def manage_recacheDataSources(self, REQUEST):
        """ Reload the columnCache, and clear the output columns """
        self.columns = {}
        list = self.datasources.keys()
        for item in list:
            self._removeDataSource(item)
            self._addDataSource(item)
        self._updateTemplate()
        if REQUEST:
            message = "Column caches updated, joins and output-columns cleared."
            return self.manage_combinerOptions(self, REQUEST, manage_tabs_message=message)

    def manage_changeOutputColumns(self, REQUEST):
        """ """
        chash = {}
        for column in self.columnCache:
            sqlName = "%s.%s" % (column[0], column[1])
            cbFormName = "c_%s_%s" % (column[0], column[1])
            txtFormName = "a_%s_%s" % (column[0], column[1])
            if REQUEST.get(cbFormName, ''):
                chash[sqlName] = REQUEST.get(txtFormName)
        self.columns = chash
        self._updateTemplate()
        if REQUEST:
            message = "Output columns updated."
            return self.manage_OutputColumns(self, REQUEST, manage_tabs_message=message)

    def tables(self):
        """ Return a list of datasource names for
            Joins and OutputColumns """
        return self.datasources.keys()

    def names(self):
        """ Get the names of the (output) columns in the mix """
        list = []
        for item in self.columns.keys():
            list.append(self.columns[item])
        return tuple(list)

    def _data_dictionary(self):
        """ Get the data dictionary for the mix """
        dd = []
        for item in self.columns.keys():
            dsID = QueryTools._getTableName(item)
            column = QueryTools._getColumnName(item)
            DDE = self.datasources[dsID]._getDataDictionary()[column]
            dd.append({'name': '%s' % self.columns[item], 'type': '%s' % DDE['type']})
        return tuple(dd)

    def _rows(self):
        """ Return the data rows """
        rows = []
        if not self.columns:
            raise "Database error", "No output columns specified"
        rows = self._join(self.joins, self.datasources)
# Now remove unwanted columns, and rename wanted ones
        out = []
        outColumns = self.columns.keys()
        for row in rows:
            newRow = {}
            for field in outColumns:
                newRow[self.columns[field]] = row.get(field, None)
            out.append(newRow)
        return tuple(out)

    def __call__(self, REQUEST=None, __ick__=None, src__=0, test__=0, **kw):
        """Call the database method

        The arguments to the method should be passed via keyword
        arguments, or in a single mapping object. If no arguments are
        given, and if the method was invoked through the Web, then the
        method will try to acquire and use the Web REQUEST object as
        the argument mapping.

        The returned value is a sequence of record objects.
        """

        if REQUEST is None:
            if kw: REQUEST=kw
            else:
                if hasattr(self, 'REQUEST'): REQUEST=self.REQUEST
                else: REQUEST={}

        if hasattr(self, 'aq_parent'):
            p=self.aq_parent
            if self._isBeingAccessedAsZClassDefinedInstanceMethod():
                p=p.aq_parent
        else: p=None

        argdata=self._argdata(REQUEST)
        argdata['sql_delimiter'] = '\0'
        argdata['sql_quote__'] = self.sql_quote__

        query = self.template()

        if src__: return query

#        if self.cache_time_ > 0 and self.max_cache_ > 0:
#            result=self._cached_result(DB__, (query, self.max_rows_))
#       else:
#           result=DB__.query(query, self.max_rows_)

        result = (self._data_dictionary(), self._rows())

        if hasattr(self, '_v_brain'): brain=self._v_brain
        else:
            brain=self._v_brain=Shared.DC.ZRDB.DA.getBrain(self.class_file_, self.class_name_)

        zc=self._zclass
        if zc is not None: zc=zc._zclass_

        if type(result) is type(''):
            f=StringIO()
            f.write(result)
            f.seek(0)
            result=RDB.File(f,brain,p, zc)
        else:
            result=Shared.DC.ZRDB.Results.Results(result, brain, p, zc)
        columns=result._searchable_result_columns()
        if test__ and columns != self._col: self._col=columns

        # If run in test mode, return both the query and results so
        # that the template doesn't have to be rendered twice!
        if test__: return query, result

        return result

    def sql_quote__(self, v):
        if string.find(v, "\'") >=0:
            v=string.join(string.split(v,"\'"), "''")
        return "'%s'" % v
