"""
    Visual Query Mixin
    ====== ===== =====

Mixin class for ZDataQueryKit query tools that adds visual query desiging"""
# $Id: VisualQueryMixin.py,v 1.4 2002/08/18 15:44:27 adrian Exp $
#
# $Log: VisualQueryMixin.py,v $
# Revision 1.4  2002/08/18 15:44:27  adrian
# Corrected INNER/OUTER testing
# Corrected adding parens round FROM clause terms
#
# Revision 1.3  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.2  2002/05/15 16:30:52  ahungate
# *** empty log message ***
#
# Revision 1.1.1.1  2002/02/03 17:07:16  root
#
#
# Revision 1.2  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.1  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
#
#
__version__ = '$Revision: 1.4 $'[11:-2]

import Globals
import string
from DocumentTemplate import DT_HTML
import QueryTools
import Shared.DC.ZRDB.Aqueduct
try: from IOBTree import Bucket
except: Bucket=lambda:{}

class _VisualQueryMixin(
        QueryTools._RelationalJoinsMixin,
    ):
    """  """
    manage_args = Globals.HTMLFile('www/editArgs', globals())
    manage_OutputColumns = Globals.HTMLFile('www/queryOutputColumns', globals())
    manage_options = (
        (
            {'label': 'Tables', 'action': 'manage_Tables',
             'help': ('ZDataQueryKit', 'QueryTables.stx'), },
            {'label': 'Arguments', 'action': 'manage_args',
             'help': ('ZDataQueryKit', 'QueryArgs.stx'), },
        ) +
        QueryTools._RelationalJoinsMixin.manage_options +
        (
            {'label': 'Output Columns', 'action': 'manage_OutputColumns',
             'help': ('ZDataQueryKit', 'QueryColumns.stx'), },
            {'label': 'Preview SQL', 'action': 'manage_PreviewSQL',
             'help': ('ZDataQueryKit', 'QuerySQL.stx'), },
        )
    )
    src = ""

    def __init__(self, id, title=""):
        """ Initialize this Visual Query """
        self.manage_resetQuery()

    def manage_resetQuery(self, REQUEST=None):
        """ Reset the query """
        self._RJM_Reset()
        self.tables = []
        self.columns = {}
        self.tableCache = []
        self.columnCache = []
        self._updateTemplate()
        if REQUEST:
            message = "Query reset."
            return self.manage_main(self, REQUEST, manage_tabs_message=message)

    def listTables(self):
        """ Return a list of table data in the select connection """
        raise "Override me"

    def listColumns(self, table):
        """ """
        raise "Override me"

    def manage_edit(self, arguments, SUBMIT='Change',
                    sql_pref__cols='50', sql_pref__rows='20',
                    REQUEST=None):
        """Query/Arguments

        The 'arguments' argument is a string containing an arguments
        specification, as would be given in the SQL method cration form.
        """

        if hasattr(self, 'wl_isLocked') and self.wl_isLocked():
            raise ResourceLockedError, 'SQL Method is locked via WebDAV'
        
        arguments=str(arguments)
        self.arguments_src=arguments
        self._arg=Shared.DC.ZRDB.Aqueduct.parse(arguments)
        self._v_cache={}, Bucket()
        if REQUEST:
            message='%s content changed' % self.meta_type
            return self.manage_args(self, REQUEST, manage_tabs_message=message)
        return ''

    def manage_addTable(self, table, REQUEST=None):
        """ Add a table to the query """
        tables = self.tables
        tables.append(table)
        self.tables = tables
        clist = self.columnCache
        for column in self.listColumns(table):
            clist.append((table, column['Name']))
        self.columnCache = clist
        self._updateTemplate()
        if REQUEST:
            message = "Table added."
            return self.manage_Tables(self, REQUEST, manage_tabs_message=message)

    def manage_removeTables(self, REQUEST):
        """ Remove selected tables from the query """
        list = []
        for table in self.tables:
            if REQUEST.has_key('t_%s' % table):
                self._RJM_RemoveTable(table)
                chash = self.columns
                for column in self.columns.keys():
                    if QueryTools._getTableName(column) == table:
                        del chash[column]
                self.columns = chash
                clist = []
                for column in self.columnCache:
                    if not column[0] == table:
                        clist.append(column)
                self.columnCache = clist
            else:
                list.append(table)
        self.tables = list
        self._updateTemplate()
        if REQUEST:
            message = "Table(s) removed."
            return self.manage_Tables(self, REQUEST, manage_tabs_message=message)

    def manage_recacheColumns(self, REQUEST):
        """ Reload the column lists for all the selected tables """
        clist = []
        self.columns = {}
        for table in self.tables:
            for column in self.listColumns(table):
                clist.append((table, column['Name']))
        self.columnCache = clist
        self._updateTemplate()
        if REQUEST:
            message = "Columns recached."
            return self.manage_Tables(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 arglist(self, REQUEST=None):
        """ Return a list of argument names """
        if not self._arg:
            self._arg=Shared.DC.ZRDB.Aqueduct.parse(self.arguments_src)
        return tuple(self._arg._keys)

    def _updateTemplate(self):
        """ Update the template in the underlying SQLMethod """
        self.src = str(self._generateSQL())
        self.template=t=self.template_class(self.src)
        t.cook()
        self._v_cache={}, Bucket()

    def _leftOnly(self):
        """ List the tables that appear only on the
            left hand side of a join, or appear in
            no joins at all. Exclude items that are not
            tables """
        tabs = {}
        tables = self.tables
        if callable(tables):
            tables = tables()
        for item in tables:
            tabs[item] = item
        for item in self._joinTypes()[0]:
            rtab = item['rightColumn']
            rtab = QueryTools._getTableName(rtab)
            if tabs.has_key(rtab):
                del tabs[rtab]
        if self.tables and not tabs:
            raise "SQL Generation Error", "At least one table must not appear on the right"
        return tabs.keys()

    def _joinTypes(self):
        """ Split the list of joins into 'FROM joins' and 'WHERE joins' """
        joins = [[], []]
        for item in self.joins:
            if QueryTools._getTableName(item['leftColumn']) and QueryTools._getTableName(item['rightColumn']):
                joins[0].append(item)
            else:
                joins[1].append(item)
        return tuple([tuple(joins[0]), tuple(joins[1])])

    def _buildJoins(self):
        """ """
        jList = map(None, self._joinTypes()[0])
        retVal = []
        while jList:
            thisJoin = jList[0]
            del jList[0]
            joinList = [thisJoin]
            InnerOuter = self._InnerOuter(thisJoin['relation'])
            i = 0
            while i<len(jList):
                item = jList[i]
                if QueryTools._getTableName(thisJoin['rightColumn'])==QueryTools._getTableName(item['rightColumn']):
                    if self._InnerOuter(item['relation']) != InnerOuter:
                        raise "Inner/Outer join conflict."
                    joinList.append(item)
                    del jList[i]
                else:
                    i = i + 1
            line =  "%s JOIN %s on\n\t" % (InnerOuter, QueryTools._getTableName(thisJoin['rightColumn']), )
            lines = []
            for item in joinList:
                lines.append("%s %s %s" % (item['leftColumn'], self._RJM_removeStar(item['relation']), item['rightColumn']))
            retVal.append(line + "(" + string.join(lines, "\nAND\t") + ")")
        return retVal

    def _FromClause(self):
        """ Compose the FROM clause """
        retVal= ""
# Time to find the left-only table(s) (More than one is a product)
        tabs = string.join(self._leftOnly(), ',\n\t')
        retVal = retVal + "FROM\t"
        joins = self._buildJoins()
# Insert the left-only tables(s)
        retVal = retVal + tabs
# Now insert the joins
        if joins:
            retVal = retVal + "\n" + string.join(joins, "\n")
        return retVal

    def _interpretParameter(self, param):
        """ Go fetch the value for a parameter of this query """
        REQUEST = self.REQUEST
        if param[:5]=="@arg:":
            thisArg = self._arg._data[param[5:]]
            if thisArg.has_key('type'):
                t = thisArg['type']
            else:
                t = 'nb'
            return "<dtml-sqlvar %s type=%s>" % (param[5:], t)
        return param

    def _WhereClause(self):
        """ Compose the WHERE clause """
        retVal = "WHERE\t"
        joins = self._joinTypes()[1]
        if not joins:
            return ""
        jList = []
        for join in joins:
            if '*' in join['relation']:
                raise "Outer Join Syntax Error", "Can not join '%s' to '%s' using outer join operator '%s'" % (join['leftColumn'], join['rightColumn'], join['relation'])
            if QueryTools._getTableName(join['leftColumn']):
                left = join['leftColumn']
            else:
                left = parameters[join['leftColumn']]
            if QueryTools._getTableName(join['rightColumn']):
                right = join['rightColumn']
            else:
                right = self._interpretParameter(join['rightColumn'])
            jList.append("%s %s %s" % (left, join['relation'], right))
        retVal = retVal + string.join(jList, "\nAND\t")
        return retVal

    def _generateSQL(self):
        """ Generate the SQL for this Visual Query """
        columns = []
        for column in self.columns.keys():
            sc = column
            oc = self.columns[column]
            if string.find(sc, ' ')>0:
                sc = "[%s]" % sc
            if string.find(oc, ' ')>0:
                oc = "[%s]" % oc
            columns.append("%s as %s" % (sc, oc))
        cols = string.join(columns, ',\n\t')
        SQL = "SELECT\t"
        if hasattr(self, 'enable_max_rows_'):
            if self.enable_max_rows_ and self.max_rows_>0:
                SQL = SQL + " TOP %d" % (self.max_rows_,)
        SQL = SQL + cols + "\n"
        SQL = SQL + self._FromClause() + "\n"
        SQL = SQL + self._WhereClause() + "\n"
        return SQL

    def manage_PreviewSQL(self):
        """ Display the generated SQL without executing it """
        dtml = "<dtml-var manage_page_header><dtml-var manage_tabs>"
        dtml = dtml + "<h2>Your Visual Query is currently</h2>"
        dtml = dtml + "<blockquote><pre>%s</pre></blockquote>" % DT_HTML.HTML(self.src, self.REQUEST, 'ZVisualQuery - preview - %s' % self.title_and_id())
        dtml = dtml + "<h2>This will be executed as</h2>"
        dtml = dtml + "<blockquote><pre>%s</pre></blockquote>" % self(src__=1)
        dtml = dtml + "<dtml-var manage_page_footer>"
        obj = DT_HTML.HTML(dtml, self.REQUEST, 'ZVisualQuery - preview - %s' % self.title_and_id())
        return obj(self.aq_inner, self.REQUEST)
