#diese klasse fragt die postgres db ab und erzeugt ein kml file aus den abfragen

import psycopg2
import harvestDataFromEvents

CONNECTION_STRING="dbname='mmpermits' user='www' host='localhost' password='3333'"

class DBQuery:
    def __init__(self):
        self.conn=psycopg2.connect(CONNECTION_STRING)
        self.cur=self.conn.cursor()



    def sql(self,query):
  
        
        self.cur.execute(query)
      
       
        results= self.cur.fetchall()
        
        ret=""
        for result in results:
            ret+="%s\n"%result[0]
            
        
       
        return ret
    
    def searchXPaths(self,queries,query="data",asList=False):
        
        qs=[]
        for q in queries:
            qs.append(""" '%s' = ANY (cast (xpath('%s', data) as text[]))"""%(q[1],q[0]))
            
        query="""select  %s from locations where"""%query
        
        query+=" AND ".join(qs)
        
        self.cur.execute(query)
      
        print query
       
        results= self.cur.fetchall()
        if asList:
            return results
        
        ret=""
        for result in results:
            ret+="%s\n"%result[0]
            
        
       
        return ret
        
            
    def searchXPath(self,xpath,searchTerm,query="data",asList=False):
        query="""select  %s from locations
        where
        '%s' = ANY (cast (xpath('%s', data) as text[])) ;
        """%(query,searchTerm,xpath)
        
        
        self.cur.execute(query)
      
       
        results= self.cur.fetchall()
        if asList:
            return results
        
        ret=""
        for result in results:
            ret+="%s\n"%result[0]
            
        
       
        return ret
    
    def query(self,q):
        self.cur.execute(q)
        results= self.cur.fetchall()
        
        return results
        

if __name__ == '__main__':
    db = DBQuery()
    res= "<events>"+db.searchXPath("//name/text()", "Brian L. Cypher")+"</events>"
    
    
    
    hv=harvestDataFromEvents.EventKMLTransformer()
    
    
    x,cnt=hv.readString(res, ".//research_location/place_information", 0)
    
    hv.close()
    
    s = hv.toKML(x)
    
    print s