'''
Created on 14.2.2011

@author: casties
'''

from OFS.Folder import Folder
from Products.PageTemplates.PageTemplateFile import PageTemplateFile
from AccessControl import getSecurityManager, Unauthorized
import logging
import re

from DBInterface import *


class ZDBInterfaceFolder(DBInterface, Folder):
    """Folder for database queries
    """
    
    meta_type="ZDBInterfaceFolder"
    manage_options=Folder.manage_options+(
        {'label':'Config','action':'manage_editZDBInterfaceFolderForm'},
        )

    # management templates
    manage_editZDBInterfaceFolderForm=PageTemplateFile('zpt/editZDBInterfaceFolder',globals())

    
    def __init__(self, id, title, connection_id=None):
        """init"""
        self.id = id
        self.title = title
        # database connection id
        self.connection_id = connection_id


    def getSQLQuery(self, table, argv):
        """returns dict with SQL query string and args"""
        logging.debug("getSQLquery table=%s argv=%s"%(table,argv))
        args = []
        select = "*"
        order = None
        wheres = []
        whereOp = "AND"
        done = {}

        def doOp(op, param, val):
            # handles comparison operations in WHERE clause
            if isinstance(val, list): 
                # join multiple parameters with spaces (makes sense with checkbox and -op=all)
                val = " ".join(val)
            logging.debug("doop op=%s param=%s val=%s"%(op,param,val))
            if op == "eq":
                wheres.append(param + " = %s")
                args.append(val)
            elif op == "lt":
                wheres.append(param + " < %s")
                args.append(val)
            elif op == "gt":
                wheres.append(param + " > %s")
                args.append(val)
            elif op == "bw":
                wheres.append(param + " LIKE %s")
                args.append("%" + val)
            elif op == "ew":
                wheres.append(param + " LIKE %s")
                args.append(val + "%")
            elif op == "ct":
                wheres.append(param + " LIKE %s")
                args.append("%" + val + "%")
            elif op == "all":
                words = []
                for word in val.split(" "):
                    words.append(param + " LIKE %s")
                    args.append("%" + word + "%")
                wheres.append("(" + " AND ".join(words) + ")")
            else:
                logging.error("getSQLquery: unknown op=%s!"%op)
                
            return

        # evaluate argv
        for (key, val) in argv.items():
            if key in done:
                # parameter has been processed
                continue
            
            # beginning of a command should always be "_"
            if key[0] == "-":
                key = "_" + key[1:]
      
            if key == "_select":
                # SELECT expression
                select = sqlName(val, more="*,")
            elif key == "_sort":
                # sort i.e. ORDER BY expression
                order = sqlName(val, more=",")
            elif key == "_lop":
                # logical operation joining WHERE clauses
                whereOp = sqlName(val)
            elif key[:3] == "_op":
                # operation parameters _op_param=option
                param = sqlName(key[4:])
                logging.debug("param=%s key=%s val=%s"%(param,key,val))
                if param in argv:
                    doOp(val, param, argv[param])
                    done[param] = True
                else:
                    # no corresponding parameter
                    logging.error("getSQLquery: param=%s for op not found!"%param)
                
        # process remaining parameters (without _op)
        for (key, val) in argv.items():
            if key not in done and key[0] not in ("_", "-"):
                param = sqlName(key)
                # default is begins-with
                doOp("bw", param, val)

        # join it all
        query = "SELECT %s FROM %s"%(select, table)
        if wheres:
            query += " WHERE " + (" " + whereOp + " ").join(wheres)
        
        if order:
            query += " ORDER BY " + order

        return {'query' : query, 'args' : args}

    def ZDBInlineSearch(self, _table=None, **argv):
        """returns result set from search"""
        query = self.getSQLQuery(_table, argv)
        result = self.executeZSQL(query['query'], query['args'])
        return result
    
    def manage_editZDBInterfaceFolder(self, title=None, connection_id=None,
                     REQUEST=None):
        """Change the object"""
        if title is not None:
            self.title = title
            
        if connection_id is not None:
            self.connection_id = connection_id
                
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')


manage_addZDBInterfaceFolderForm=PageTemplateFile('zpt/addZDBInterfaceFolder',globals())

def manage_addZDBInterfaceFolder(self, id, title='', label='', description='',
                     createPublic=0,
                     createUserF=0,
                     REQUEST=None):
        """Add a new object with id *id*."""
    
        ob=ZDBInterfaceFolder(str(id),title)
        self._setObject(id, ob)
        
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')