from OFS.Folder import Folder
from Globals import Persistent
from Acquisition import Implicit
from Globals import DTMLFile
import urllib
import re
import string
from pyPgSQL import libpq
from AccessControl import getSecurityManager

def quoteString(name):
    #return re.sub(r'([\(\)\?])',"\\\1",name)
    #return "Euklid"
    return name

class ZSQLExtendFolder(Persistent, Implicit, Folder):
    """Folder"""
    meta_type="ZSQLExtendFolder"
    
    def ZSQLInlineSearch(self,**argv):
        """inlinesearch"""
        qs=[]
        
    

    
        for a in argv.keys():
            qs.append(a+"="+urllib.quote(str(argv[a])))

        return self.parseQueryString(string.join(qs,","),"_")
    
    def ZSQLAdd(self):
        """Neuer Eintrag"""
        qs=self.REQUEST['QUERY_STRING']
        addList={}
        for q in qs.split("&"):
            name=re.sub("r'+'"," ",q.split("=")[0].lower())
            value=q.split("=")[1]
	    value=re.sub(r'\+'," ",value)
	    value=urllib.unquote(value)	
            if name=="-table":
                table=urllib.unquote(value)
            elif name=="-format":
                format=urllib.unquote(value)
            elif (not name[0]=="-") and (not len(value)==0):
                addList[urllib.unquote(name)]=urllib.unquote(value)

        keyList=[]
        valueList=[]
        for x in addList.keys():
            keyList.append("\""+x+"\"")
            valueList.append(libpq.PgQuoteString(addList[x]))

        keyString=string.join(keyList,",")
        valueString=string.join(valueList,",")
        
        queryString="INSERT INTO %s (%s) VALUES (%s)"%(table,keyString,valueString)
        self.search(var=queryString)
        return self.REQUEST.RESPONSE.redirect(format)
        
    def ZSQLChange(self):
        """Ändern von Einträgen"""
        qs=self.REQUEST['QUERY_STRING']
        #print "CHANGE QS",self.REQUEST
        #return self.REQUEST
        changeList=[]
        for q in qs.split("&"):
            name=urllib.unquote(re.sub("r'+'"," ",q.split("=")[0].lower()))
            value=q.split("=")[1]
            value=re.sub(r'\+'," ",value)
	    value=urllib.unquote(value)
	    if name=="-table":
                table=urllib.unquote(value)
            elif name=="-identify":
                identify=urllib.unquote(value)
                identify=identify.split("=")[0]+"="+libpq.PgQuoteString(identify.split("=")[1])
            elif name=="-format":
                format=urllib.unquote(value)
            elif (not name[0]=="-") and (not len(value)==0):
                changeList.append("\""+name+"\"="+libpq.PgQuoteString(urllib.unquote(value)))
        changeString=string.join(changeList,",")
        queryString="UPDATE %s SET %s WHERE %s"%(table,changeString,identify)
        self.search(var=queryString)
        return self.REQUEST.RESPONSE.redirect(format)
    
    def ZSQLFind(self,qs="",select="*"):
        """Find"""


        if qs=="":
           if self.REQUEST['QUERY_STRING']:
               qs=self.REQUEST['QUERY_STRING']
               qs=string.join(qs.split("&"),",")
           else:
               qs=self.REQUEST.SESSION['query']
        else:
            qs=string.join(qs.split("&"),",")
        return self.parseQueryString(qs,"-",select=select,storemax="yes")

    def ZSQLFoundCount(self,founds):
        try:
            return len(founds)
        except:
            return 0

    def parseQueryString(self,qs,iCT,storemax="no",select=None):
        """analysierren den QueryString"""
        lop="AND" # standardsuche mit and
        max="ALL" #standard alle auswählen
        whereList=[]
        sort=""
        op="bw"
        opfields={}
        
        if not select:
            select="*"

        #check for op in the case of inline search
        if iCT=="_":            
            for q in qs.split(","):
                name=re.sub("r'+'"," ",q.split("=")[0].lower())
                value=urllib.unquote(q.split("=")[1])

                if name[0:3]==iCT+"op":
                    op=value
                    field=name[4:]
                    opfields[field]=op

        #now analyse the querystring
        for q in qs.split(","):
            try:

                name=re.sub("r'+'"," ",q.split("=")[0].lower())
                value=urllib.unquote(q.split("=")[1])
                value=quoteString(value)
                if name==iCT+"lop":
                    lop=value
                elif name==iCT+"table":
                    table=value
                elif name==iCT+"select":
                    select=value
                elif name==iCT+"max":
                    max=str(value)

                elif name==iCT+"join":
                    whereList.append(value)
                elif name==iCT+"sort":
                    sort="ORDER BY "+value
                elif name==iCT+"token":
                    self.REQUEST.SESSION['token']=value

                elif name==iCT+"op":
                    op=value
                    
                

                elif (not name[0]==iCT) and (not len(value)==0):
                    if opfields.has_key(name):
                        op=opfields[name]
                    if op=="ct":
                        whereList.append(name+"~\'.*"+value+".*\'")
                    elif op=="gt":
                        whereList.append(name+">\'"+value+"\'")
                    elif op=="lt":
                        whereList.append(name+"<\'"+value+"\'")
                    elif op=="eq":
                        whereList.append(name+"=\'"+value+"\'")
                    elif op=="bw":
                        whereList.append(name+"~\'"+value+".*\'")
                    elif op=="ew":
                        whereList.append(name+"~\'.*"+value+"\'")
                    op="ct"
            except:
                print "END"        
        if len(whereList)>0:
            where="WHERE "+string.join(whereList," "+lop+" ")
        else:
            where=""
        #print "QE",table
        
        query="SELECT %s FROM %s %s %s"%(select,table,where,sort)

        self.REQUEST.SESSION['qs']=opfields
        return self.search(var=query)
    
    def ZSQLSearch(self):
        """To be done"""
        rq=self.REQUEST['QUERY_STRING']
        querys=rq.split("&")
        

        for querytemp in querys:
            query=querytemp.split("=")
            try:
                if query[0].lower()=="-format":
                    formatfile=query[1]
            except:
                """nothing"""
        #print formatfile        
        self.REQUEST.SESSION['query']=string.join(self.REQUEST['QUERY_STRING'].split("&"),",")
        return self.REQUEST.RESPONSE.redirect(urllib.unquote(formatfile))

    
    def ZSQLint(self,string):
        try:

            return(int(string))
        except:
            return 0
    def prevLink(self,html):
        """prev link"""
        if self.REQUEST['QUERY_STRING']=="":
            qs=self.REQUEST.SESSION['query']
        else:
            qs=self.REQUEST['QUERY_STRING']
        max=re.search(r'max\=(.*)\,',qs.lower())
        offset=re.search(r'offset\=(.*)\,',qs.lower())
        if not offset:
            offsetnew=0
        else:
            offsetnew=int(offset)-max
            if offsetnew<0:
                offsetnew=0
        queries=string.split(qs,",")
        newquery=[]
        if offset:
            for query in queries:
                if query.split("=")[0].lower()=="offset":
                    query="-offset=%i"%offsetnew
                newquery.append(query)
                newquerystring=string.join(newquery,"&")
        else:
            queries.append("-offset=%i"%offsetnew)
            newquerystring=string.join(queries,"&")
        return "<a href='%s'>%s</a>"%(self.REQUEST['URL0']+"?"+newquerystring,html)
        
    def nextLink(self,html):
        """prev link"""
        if self.REQUEST['QUERY_STRING']=="":
            qs=self.REQUEST.SESSION['query']
        else:
            qs=self.REQUEST['QUERY_STRING']
        max=re.search(r'max\=(.*)\,',qs.lower())
    
        offset=re.search(r'offset\=(.*)\,',qs.lower())
        if not offset:
            offsetnew=1
        else:
            offsetnew=int(offset)+int(max)
            if offsetnew<0:
                offsetnew=0
        queries=string.split(qs,",")
        newquery=[]
        if offset:
            for query in queries:

                if query.split("=")[0].lower()=="-offset":
                    query="-offset=%i"%offsetnew
                newquery.append(query)
                newquerystring=string.join(newquery,"&")
        else:
            queries.append("-offset=%i"%offsetnew)
            newquerystring=string.join(queries,"&")
        
        return "<a href='%s'>%s</a>"%(self.REQUEST['URL0']+"?"+newquerystring,html)
        
    def ZSQLrangeStart(self):
        if self.REQUEST['QUERY_STRING']=="":
            qs=self.REQUEST.SESSION['query']
        else:
            qs=self.REQUEST['QUERY_STRING']
        offset=re.search(r'offset\=(.*)\,',qs.lower())
        if not offset:
            offset=1
        return offset
#    def search(self,**argv):
#        """to be done"""



manage_addZSQLExtendFolderForm=DTMLFile('ZSQLExtendFolderAdd', globals())

def manage_addZSQLExtendFolder(self, id, title='',
                     createPublic=0,
                     createUserF=0,
                     REQUEST=None):
    """Add a new Folder object with id *id*.

    If the 'createPublic' and 'createUserF' parameters are set to any true
    value, an 'index_html' and a 'UserFolder' objects are created respectively
    in the new folder.
    """
    
    
    ob=ZSQLExtendFolder()
    ob.id=str(id)
    ob.title=title
    self._setObject(id, ob)
    ob=self._getOb(id)

    checkPermission=getSecurityManager().checkPermission

    if createUserF:
        if not checkPermission('Add User Folders', ob):
            raise Unauthorized, (
                  'You are not authorized to add User Folders.'
                  )
        ob.manage_addUserFolder()

    if createPublic:
        if not checkPermission('Add Page Templates', ob):
            raise Unauthorized, (
                  'You are not authorized to add Page Templates.'
                  )
        ob.manage_addProduct['PageTemplates'].manage_addPageTemplate(
            id='index_html', title='')

    if REQUEST is not None:
        return self.manage_main(self, REQUEST, update_menu=1)