"""This file contains the classes for the organization of the staff"""

from OFS.Folder import Folder
from Products.PageTemplates.PageTemplateFile import PageTemplateFile

from zope.interface import implements
from zope.publisher.interfaces import IPublishTraverse
from ZPublisher.BaseRequest import DefaultPublishTraverse
from Acquisition import aq_parent

from AccessControl import ClassSecurityInfo
from App.class_init import InitializeClass
from App.ImageFile import ImageFile
from Products.PythonScripts.standard import sql_quote
from Products.ExtFile import ExtFile
import logging
import email
import re
import datetime

from Products.ZDBInterface.ZDBInterfaceFolder import ZDBInterfaceFolder
from Products.MPIWGManager import MPIWGIcsManager

from SrvTxtUtils import getInt, unicodify, utf8ify, sqlName, getPlaintext
import MPIWGHelper

#
# compatibility
# TODO: should be removed when done
import MPIWGStaff_old

#createNewDBEntry = MPIWGStaff_old.createNewDBEntry
    
class MPIWGStaff(MPIWGStaff_old.MPIWGStaff):
    """Staff"""
    pass
    
manage_addMPIWGStaffForm = MPIWGStaff_old.manage_addMPIWGStaffForm
manage_addMPIWGStaff = MPIWGStaff_old.manage_addMPIWGStaff

    
class MPIWGStaffFolder(ZDBInterfaceFolder):
    """Folder of staff objects"""
    implements(IPublishTraverse)

    meta_type="MPIWGStaffFolder"
    security=ClassSecurityInfo()

    #
    # templates
    #
    member_index_html = PageTemplateFile('zpt/staff/member_index_html', globals())
    edit_css = ImageFile('css/edit.css', globals())


    #
    # hook into traversal to create folder of virtual staff objects
    # like /members/$username/index_html
    # 
    def publishTraverse(self, request, name):
        """change the traversal"""
        # get stored path
        logging.debug("publishtraverse: name=%s"%(name))
        
        # try name as username
        member = self.getMember(username=name)
        if member is not None:
            # traverse to MPIWGStaffMember object
            member = member.__of__(self)
            return member
        
        # use default traverser
        tr = DefaultPublishTraverse(self, request)
        ob = tr.publishTraverse(request, name)
        return ob
        

    def getMember(self, username=None, key=None):
        """returns a MPIWGStaffMember object if the username exists"""
        member = None
        if username is not None:
            # TODO: we should have a username column
            email = '%s@mpiwg-berlin.mpg.de'%username
            content = self.executeZSQL("select * from personal_www where e_mail = %s", [email])
            if len(content) > 0:
                member = MPIWGStaffMember(self, dbresult=content[0])
        
        elif key is not None:
            content = self.executeZSQL("select * from personal_www where key = %s", [key])
            if len(content) > 0:
                member = MPIWGStaffMember(self, dbresult=content[0])             
         
        return member
    

    def isActiveMember(self, key):
        """returns if member key is active"""
        res = self.executeZSQL("select * from personal_www where key = %s and publish_the_data = 'yes'", [key])
        return len(res) > 0


    def getMemberList(self, department=None, sortBy='last_name', onlyCurrent=False, arrivedWithin=None, 
                      onlyPublished=True, onlyWithEmail=True, onlyScholar=True, status=None, limit=0):
        """Return the list of members.
        
        Returns a list of MPIWGStaffMember objects.
        """
        members = []
        args = []
        wheres = []
        
        if onlyPublished:
            wheres.append("publish_the_data = 'yes'")

        if onlyWithEmail:
            wheres.append("e_mail <> ''")

        if onlyScholar:
            wheres.append("is_scholar = 'yes'")

        if department is not None:
            wheres.append("department ilike %s")
            args.append('%%%s%%'%department)
        
        if onlyCurrent:
            wheres.append("date_from < CURRENT_DATE")
            
        if arrivedWithin is not None:
            wheres.append("date_from > CURRENT_DATE - interval %s")
            args.append(arrivedWithin)
            
        if status is not None:
            wheres.append("status ilike %s")
            args.append('%%%s%%'%status)
        
        # assemble query
        query = "SELECT * FROM personal_www_list"
        if len(wheres) > 0:
            query += " WHERE " + " AND ".join(wheres)
            
        if sortBy == 'last_name':
            query += " ORDER BY lower(last_name)"
        elif sortBy == 'date_from':
            query += " ORDER BY date_from DESC"
        elif sortBy:
            query += ' ORDER BY "%s"'%sqlName(sortBy)
            
        if limit > 0:
            query += " LIMIT %s"%int(limit)
            
        result = self.executeZSQL(query, args)
        for res in result:
            members.append(MPIWGStaffMember(self, dbresult=res))
            
        return members
        

    def sortPriority(self,list):
        def sort(x,y):
            try:
                xInt=int(x.priority)
            except:
                xInt=0
            try:
                yInt=int(y.priority)
            except:
                yInt=0

            return cmp(xInt,yInt)

        if not list:
            return []
        tmp=[x for x in list]
        tmp.sort(sort)           
        
        return tmp
    
    
    def importSortingModeFromOldStaff(self):
        """ only used for the migration to the new website """
        ret=[]
        for member in self.getMemberList():
            email =  member.content.e_mail
            un = email.split("@")[0]
            
            logging.debug(un)
            olduser = self.members_old.get(un)
            
            if not olduser is None:
                mode =olduser.getSortingMode()
            
              
                if mode.startswith("year"):
                    mode="year"
            
                query="UPDATE personal_www SET publications_mode=%s WHERE key=%s" 
              
                self.executeZSQL(query,[mode,member.getKey()])
            
        return ret
    
    
    def importPublishFotoFromOldStaff(self):
        """ only used for the migration to the new website """
        ret=[]
        for member in self.getMemberList():
            email =  member.content.e_mail
            un = email.split("@")[0]
            
            logging.debug(un)
            olduser = self.members_old.get(un)
            
            if not olduser is None:
                mode =olduser.getPublishImage()
            
              
            
                query="UPDATE personal_www SET image_p=%s WHERE key=%s" 
              
                self.executeZSQL(query,[mode,member.getKey()])
            
        return ret
        
    def showDownloadableFiles(self):
        """copy df to the new"""
        logging.debug("huh")
        ret=[]
       
        for member in self.getMemberList(onlyCurrent=True):
            email =  member.content.e_mail
            un = email.split("@")[0]
            
            logging.debug(un)
            olduser = self.www_neu.members.get(un)
            if olduser is None:
                continue;
            df = olduser.get('downloadableFiles')
            if df is not None:
                ret.append(olduser)
            
        return ret,len(ret)
                
    
    security.declareProtected('View management screens','createNewDBEntry')    
    def createNewDBEntry(self,publish_the_data,key,name,vorname,titles_new,position,e_mail,e_mail_p,date_from,date_to,abteilung,heimat_inst,
                         funded_by="",e_mail2="",txt="",txt_p="no",stay_at_mpiwg="",group="",web_object_created="no",current_work=""):
        """lege person in der datenbank an"""

        if date_to=="": # wenn date_to leer
            date_to="date_none"
        
        if date_from=="": # wenn date_fromleer
            date_from="date_none"
        msg=""
        #test ob id schon existiert
        #if self.ZSQLQuery("select key from personal_www where key='%s'"%id):
        if self.getMember(key=key) is not None:
            return False,"ERROR:key%s already exists"%key
        
        #eintragen
        columnlist="""publish_the_data,key,last_name,first_name,titles_new,status,e_mail,e_mail_p,date_from,date_to,department,home_inst,funded_by,e_mail2,date_stay_at_mpiwg,web_object_created,"group",current_work,current_work_p """
        insertTuple=(publish_the_data,key,name,vorname,titles_new,position,e_mail,e_mail_p,date_from,date_to,abteilung,heimat_inst,
                     funded_by,e_mail2,stay_at_mpiwg,web_object_created,group,current_work,"yes")
        
        insert=[]
        for element in insertTuple:
            if element=="date_none": # date_none eintrag wird zu null uebersetzt
                insert.append(None)
            else:
                insert.append(element)
            
        #insertStr=",".join(insert)
        insertStr=("%s," * len(insert))[:-1]
        queryStr="INSERT INTO personal_www (%s) VALUES (%s)"%(columnlist,insertStr)
        #self.ZSQLQuery("SET DATESTYLE TO 'German'")
        #self.ZSQLQuery(queryStr)
        # TODO: is there a better way for dates?
        self.executeZSQL("SET DATESTYLE TO 'German'")
        self.executeZSQL(queryStr, insert)
        logging.info("createNewDBEntry %s:"%queryStr)
        #currentwork
        #if not (current_work==""):
        #    queryStr="INSERT INTO current_work (key_main,current,publish) VALUES ('%s',%s,'%s')"%(key,self.ZSQLQuote(current_work),"yes")

        #  self.ZSQLQuery(queryStr)
        
        return True,msg
    

    security.declareProtected('View management screens','addEmails')    
    def addEmails(self,RESPONSE):
        """add emails to the members from form"""        
        args=self.REQUEST.form
        arg_k=args.keys()
        arg_k.remove("submit")
        ret="<html><body>"
        for key in arg_k:
            if args[key]!="": #email-adresse wurde eingetragen
                #create the object
                e_mail=args[key]
                qstr="update personal_www set web_object_created='yes', e_mail=%s where key=%s"
                self.executeZSQL(qstr, ["%s@mpiwg-berlin.mpg.de"%e_mail, key])
        
                ret+="<p>Added: %s</p>"%key
        
        return ret+"</body></html>"

    
    def updateConeIds(self,REQUEST):
        """holt alle coneids von allen member, die methode getconeid traegt diese dann auch all in die key tabelle ein, falls sie ich fehlen"""

        mems= self.getMemberList()
        cids=[]
        for mem in mems:
            cids.append(mem.getConeId())

        return cids
            
    
    def getConeIDsFromDB(self,REQUEST):
        """holt die cone ids aus personalwww und gibt sie als RDF fuer den cone import zurueck"""
        
        qs = "select personal_www.last_name, personal_www.first_name, keys.coneid from personal_www inner join keys on personal_www.key = keys.key_main;"
        coneIds = self.executeZSQL(qs)
        
        ret="""<?xml version="1.0" encoding="UTF-8"?>
<rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:dc="http://purl.org/dc/elements/1.1/"  xmlns:escidoc="http://purl.org/escidoc/metadata/terms/0.1/" xmlns:eprints="http://purl.org/eprint/terms/">
"""
        
        templ="""
        <rdf:Description rdf:about="%s">
        <dc:title>%s, %s</dc:title>
        
        <escidoc:position>
        <rdf:Description>
        <dc:identifier>escidoc:14002</dc:identifier>
        <eprints:affiliatedInstitution>Max Planck Institute for the History of Science, Max Planck Society</eprints:affiliatedInstitution>
        </rdf:Description>
        </escidoc:position>
        
        
        
        </rdf:Description>
        """
        print coneIds.names()
        for coneId in coneIds:
           
            ret+=templ%(coneId["coneid"],coneId["last_name"],coneId["first_name"])
            
        ret+="""
        </rdf:RDF>
        """
        if REQUEST is not None:
            REQUEST.response.setHeader("Content-type", "text/XML")
        return ret

def manage_addMPIWGStaffFolderForm(self):
    """form for adding the project"""
    pt=PageTemplateFile('zpt/addMPIWGStaffFolderForm', globals()).__of__(self)
    return pt()

def manage_addMPIWGStaffFolder(self,id,title,RESPONSE=None):
    """add it"""
    newObj=MPIWGStaffFolder(id,title)

    self._setObject(id,newObj)

    if RESPONSE is not None:
        RESPONSE.redirect('manage_main')


class MPIWGStaffMember(Folder):
    """MPIWG staff member object from database"""
    
    security = ClassSecurityInfo()
    
    # templates
    mainEditFile=PageTemplateFile('zpt/staff/edit_main', globals())    
    talks_full_html = PageTemplateFile('zpt/staff/talks_full_html', globals())
    teaching_full_html = PageTemplateFile('zpt/staff/teaching_full_html', globals())


    def __init__(self, folder, dbresult):
        """constructor: takes parent MPIWGStaffFolder and content (DB row)"""
        self.folder = folder
        self.content = dbresult
            

    def index_html(self, REQUEST, RESPONSE):
        """show homepage"""
        # get template /template/member_index_html (with acquisition)
        pt = getattr(self.template, 'member_index_html', None)
        if pt is not None:
            return pt(member=self)
        
        return "No template /template/member_index_html"

            
    def isValid(self):
        """returns if this member exists"""
        return len(self.content) > 0

    def isActive(self):
        """Return if this member is visible (published)."""
        return (self.content.publish_the_data == 'yes')

    def getKey(self):
        """returns the db key"""
        return self.content.key

    def getUsername(self):
        """returns the username"""
        id = re.sub('@mpiwg-berlin\.mpg\.de', '', self.content.e_mail)
        return str(id)
    
    getId = getUsername
    def getConeId(self):
        """return cone ID"""
        results= self.folder.executeZSQL("SELECT coneid FROM keys WHERE key_main = %s",[self.content.key])
         for res in results:
            return res.coneid
         
        newConeId="http://pubman.mpiwg-berlin.mpg.de/cone/persons/resource/%s"%self.content.key
         
        results = self.folder.executeZSQL("INSERT  INTO keys (coneid,key_main) values(%s,%s)",[newConeId,self.content.key])
         
        return newConeId

 
    def getPublishedImageUrl(self, width=None, height=None, showAlways=False):
        """returns the URL to the image if it is published"""
        if self.content.image_p == 'yes' or showAlways:
            url = 'http://digilib.mpiwg-berlin.mpg.de/digitallibrary/Scaler?fn=permanent/mpiwg/staff/%s'%self.getUsername()
            if width is not None:
                url += '&dw=%s'%width
                
            if height is not None:
                url += '&dh=%s'%height
                
            return url
        
        return None
    
    def getContent(self):
        """returns the db content of this object"""
        return self.content
    
    
    # TODO: ugly!
    security.declarePublic('sortBibliography')    
    def sortBibliography(self, bib, sortingMode=None, max=None):
        """sort bibliography"""
        if not sortingMode:
            sortingMode= "priority"
    
        l = [x for x in bib]
        
        if sortingMode == "year":
            l.sort(key=lambda x: getInt(x.year))
        else:
            l.sort(key=lambda x: getInt(x.priority))
        
        if max:
            return l[0:max]
        else:
            return l

    
    getUrl = MPIWGHelper.getUrl
    

    def getProfile(self, filter=None, length=0):
        """Return the profile"""
        logging.debug("getProfile: self.content=%s"%(repr(self.content)))
        text = self.content.profile
        if filter == 'plaintext':
            # filter out any tags, keep only text
            text = getPlaintext(text, length=length, wordwrap=True, ignoretags=['style'])
        
        return text
            
        
    def getTalks(self, published=True, sortBy='priority'):
        """Return the list of talks"""
        query = "SELECT oid,* FROM talks WHERE key_main = %s"
        if published:
            query += " and published = 'yes'"
            
        if sortBy == 'priority':
            query += " order by priority"
            
        return self.folder.executeZSQL(query, [self.getKey()])
    
    
    def getTalksCal(self):
        """Return the talks calendar object (MPIWGIcsManager)"""
        try:
            cal = self.folder.get('calendars').get("%s_talks"%self.getUsername())
            return cal
        except:
            return None
    
    
    def getTeaching(self, published=True, sortBy='priority'):
        """Return the list of teaching activities"""
        query = "SELECT oid,* FROM teaching WHERE key_main = %s"
        if published:
            query += " AND published = 'yes'"
            
        if sortBy == 'priority':
            query += " ORDER BY priority"
            
        return self.folder.executeZSQL(query,[self.getKey()]) 
    
    
    def getLastUpdateCV(self):
        """getDate of Last Update"""
        try:
            ob=self.folder.get("downloadableFiles").get("%s_cv.pdf"%self.getUsername())
            return datetime.datetime.fromtimestamp(ob._p_mtime)
        except:
            return "No file yet!"

            
    def getLastUpdatePublications(self):
        """getDate of Last Update"""
        try:
            ob=self.folder.get("downloadableFiles").get("%s_publications.pdf"%self.getUsername())
            return datetime.datetime.fromtimestamp(ob._p_mtime)
        except:
            return "No file yet!"
    
    
    def downloadCV(self,REQUEST):
        """download the cv"""
        ob=self.folder.get("downloadableFiles").get("%s_cv.pdf" % self.getUsername())
        REQUEST.RESPONSE.redirect(ob.absolute_url())


    def downloadPublications(self,REQUEST):
        """donwload the publication list"""
        ob=self.folder.get("downloadableFiles").get("%s_publications.pdf"%self.getUsername())
        REQUEST.RESPONSE.redirect(ob.absolute_url())


    def getAdditionalLinks(self):        
        return self.folder.executeZSQL("SELECT oid,* FROM additionallink  WHERE  key_main = %s ORDER BY priority",[self.content.key]) 

    
    def getPathStyle(self, path, selected, style=""):
        """returns a string with the given style + 'sel' if path == selected."""
        if path == selected:
            return style + 'sel'
        else:
            return style    


    security.declareProtected('View management screens','edit')    
    def edit(self,REQUEST=None):
        """Edit the  basic information"""
        if REQUEST:
            argv=REQUEST.form
            
            if argv.has_key('last_name'): #got data to change
                self.invalidate_cache()
                self.changeData(argv);
                       
        pt=PageTemplateFile('zpt/staff/edit_basic', globals()).__of__(self)
        return pt()
        
   
    security.declareProtected('View management screens','editShortEntry')    
    def editShortEntry(self,REQUEST=None):
        """Edit the  basic information"""
        if REQUEST:
            argv=REQUEST.form
            
            if argv.has_key('current_work'): #got data to change
                self.invalidate_cache()
                self.changeData(argv);
                       
        pt=PageTemplateFile('zpt/staff/edit_shortEntry', globals()).__of__(self)
        return pt()
  
   
    security.declareProtected('View management screens', 'editProfile')        
    def editProfile(self, REQUEST=None):
        """edit Profile, new entry replaces CD, current work and research interests"""
        if REQUEST:
            kupu = REQUEST.form.get('kupu', None);
            preview = REQUEST.form.get('preview', None);
       
        if kupu:
            start = kupu.find("<body>")
            end = kupu.find("</body>")
            newcontent = kupu[start+6:end]
            query = "UPDATE personal_www SET profile=%s WHERE key=%s"       
            self.executeZSQL(query, [newcontent, self.content.key])
        
        if preview: 
            pass
            # TODO: not supported yet
            # if RESPONSE:
            #    self.redirect(RESPONSE,"editProfile")
            # return self.preview(newcontent)
          
        pt = PageTemplateFile('zpt/staff/edit_profile.zpt', globals()).__of__(self)
        return pt()
    

    security.declareProtected('View management screens','editTalks')    
    def editTalks(self,REQUEST):
        """edit talks"""
        pt=PageTemplateFile('zpt/staff/edit_talks.zpt', globals()).__of__(self)

        if REQUEST:
            argv=REQUEST.form
            
            url = argv.get('ics_url', None)
            if url:
                #
                # ICS URL driven talks
                #
                cal_id = "%s_talks"%self.getUsername()
                # use ICS calendar
                cal = self.getTalksCal()
                if cal is None:
                    # create new calendar
                    cal = MPIWGIcsManager.MPIWGIcsManager(cal_id, '', url, defaultProps=MPIWGIcsManager.calendar_props)
                    self.folder.get('calendars')[cal_id] = cal
                    
                else:
                    # calendar exists
                    if cal.url != url:
                        # remove and re-create
                        del self.folder.get('calendars')[cal_id]
                        cal = MPIWGIcsManager.MPIWGIcsManager(cal_id, '', url, defaultProps=MPIWGIcsManager.calendar_props)
                        self.folder.get('calendars')[cal_id] = cal

                # show only upcoming
                upcoming = argv.get('only_upcoming', None)
                cal.setFlag('only_upcoming', (upcoming == 'yes'))
                # number of events
                talks_num = int(argv.get('talks_num', 5))
                self.changeData({'talks_num': talks_num})
                
            elif argv.has_key('only_upcoming'):
                # delete calendar if it exists
                if self.getTalksCal():
                    cal_id = "%s_talks"%self.getUsername()
                    del self.folder.get('calendars')[cal_id]
                    
            elif argv.has_key('main_fields'): #got data to change
                    #
                    # manually edited talks
                    #
                    self.invalidate_cache()
                    self.changeAdditionalData(argv);
                    # number of events
                    talks_num = int(argv.get('talks_num', 5))
                    self.changeData({'talks_num': talks_num})
                       
        return pt()
     
   
    security.declareProtected('View management screens','editTeaching')    
    def editTeaching(self,REQUEST):
        """edit teaching"""
        if REQUEST:
            argv=REQUEST.form
            
            if argv.has_key('main_fields'): #got data to change
                self.invalidate_cache()
                self.changeAdditionalData(argv);
                # number of items
                teachings_num = int(argv.get('teachings_num', 5))
                self.changeData({'teachings_num': teachings_num})
                       
        pt=PageTemplateFile('zpt/staff/edit_teaching.zpt', globals()).__of__(self)
        return pt()
     
   
    security.declareProtected('View management screens','editAdditionalLinks')
    def editAdditionalLinks(self,REQUEST):    
        """editiere die additional link von der Webseite"""
        if REQUEST:
            argv=REQUEST.form
            
            if argv.has_key('main_fields'): #got data to change
                self.invalidate_cache()
                self.changeAdditionalData(argv);
                       
        pt=PageTemplateFile('zpt/staff/edit_additionalLinks.zpt', globals()).__of__(self)
        return pt()
    
    
    security.declareProtected('View management screens','editDownloads')
    def editDownloads(self,REQUEST):    
        """editiere die Downloads von der Webseite"""
        
        pt=PageTemplateFile('zpt/staff/edit_downloads.zpt', globals()).__of__(self)
        return pt()
   
    def editPublications(self,REQUEST):    
        """editiere die Publications von der Webseite"""
        data=REQUEST.form
        newData = {}
        
        if 'selectionMode' in data:
            newData['publications_mode'] = data['selectionMode']
            
        if 'publications_num' in data:
            newData['publications_num'] = int(data['publications_num'])

        if len(newData) > 0:
            self.changeData(newData)
            
        pt=PageTemplateFile('zpt/staff/edit_publications.zpt', globals()).__of__(self)
        return pt()
   

    security.declareProtected('View management screens','editImage')    
    def editImage(self,REQUEST=None):
        """Edit the image information"""
        if REQUEST:
            argv=REQUEST.form
            
            if argv.has_key('image_p'): #got data to change
                self.invalidate_cache()
                self.changeData(argv);
                       
        pt=PageTemplateFile('zpt/staff/edit_image', globals()).__of__(self)
        return pt()
  

    def refresh_content(self,):
        self.content = self.folder.executeZSQL("select * from personal_www where key = %s", [self.getKey()])[0]
        
        
    def changeDownloads(self,REQUEST):
        """"change the downloadable files"""
        self.invalidate_cache();
        
        data=REQUEST.form
       
        ob = self.folder.get("downloadableFiles")
        
        if data.get('cv_publish',None):
           
            self.changeData({'cv_p':data['cv_publish']})
       
        if data.get('publications_publish',None):
            self.changeData({'publications_p':data['publications_publish']})
        
            
        if data.get('cv_pdf',None):
            cvName ="%s_cv.pdf"%self.getUsername()
            cvName=cvName.encode('utf-8')
        
            if not hasattr(ob,cvName):
                cvFile = ExtFile.ExtFile(cvName,cvName)
                ob._setObject(cvName,cvFile)
          
            cvFile = getattr(ob,cvName)
                
            cvFile.manage_upload(file=data['cv_pdf'])
        
        if data.get('publications_pdf',None):
            pdfName="%s_publications.pdf"%self.getUsername()
            pdfName=pdfName.encode('utf-8')
            
            if not hasattr(ob,pdfName):
                cvFile = ExtFile.ExtFile(pdfName,pdfName)
                ob._setObject(pdfName,cvFile)
         
            cvFile = getattr(ob,pdfName)
                
            cvFile.manage_upload(file=data['publications_pdf'])
            
      
        REQUEST.response.redirect("editDownloads");
    

    security.declareProtected('View management screens','changeData')
    def changeData(self, changeSet):
        """changes the data in the database, changeset expects field --> value."""
        fields = []
        values = []
        for field in changeSet.keys():
            if hasattr(self.content, field):
                logging.debug("Changing: %s"%field)
                fields.append('"'+sqlName(field)+'" = %s')
                values.append(changeSet.get(field))
                
        if len(fields) == 0:
            return
        
        query = "update personal_www set " + ', '.join(fields) + " where key = %s"
        values.append(self.getKey())
        results = self.folder.executeZSQL(query, values)
        logging.debug(results)
                
        self.refresh_content()
   
   
    security.declareProtected('View management screens','changeAdditionalData')
    def changeAdditionalData(self,data):
        """change the research entries"""
        
        self.invalidate_cache();
        
        newEntries={}
        

        mainfieldL=data['main_fields'].split(",") #fields to be changed
        # format DATABASE__FIELDNAME
        
        mainfield={}
        for x in mainfieldL:
            tmp=x.split('__')
            mainfield[tmp[0]]=tmp[1]
        for field in data:
            splittedField=field.split("__")
            if len(splittedField)<3:
                pass #kein datenbank eintrag

            elif splittedField[2]=='new': # store new entries
                if not newEntries.has_key(splittedField[0]):
                    newEntries[splittedField[0]]={}
                    
                newEntries[splittedField[0]][splittedField[1]]=data[field]

            else:
                query="UPDATE %s "%splittedField[0]
                query+="SET %s = '%s' "%(splittedField[1],sql_quote(data[field]))
                query+="WHERE oid = '%s' "%sql_quote(splittedField[2])
                
                self.executeZSQL(query)


        #new entries
        for newEntry in newEntries.keys():
            query="INSERT INTO %s "%newEntry
            keys=['key_main']
            values=["'"+sql_quote(self.getKey())+"'"]
            for key in newEntries[newEntry].keys():
                keys.append(key)
                
                val = sql_quote(newEntries[newEntry][key])
                if not isinstance(val, unicode):
                    val = val.decode("utf-8")
                    
                values.append("'"+val+"'")


            keystring=",".join(keys)
                
            valuestring=",".join(values)
                
            query+=" (%s) "%keystring
            query+="VALUES (%s)"%valuestring
            if not (newEntries[newEntry][mainfield[newEntry]].lstrip().rstrip()==""):
                self.executeZSQL(query)

            

    security.declareProtected('View management screens','deleteField')    
    def deleteField(self,REQUEST):
        """delete entry"""
        # TODO: this is ugly
        CHANGEABLE=['talks','teaching','pubmanbiblio','additionallink']
        
        table = REQUEST.form.get('table',None);
        oid = REQUEST.form.get('oid',None);
        
        
        if not table in CHANGEABLE:
            return
        if oid is None:
            oid = REQUEST.form.get('key',None);
        
        if table is None or oid is None:
            return
        
        query="DELETE FROM %s WHERE oid = %%s"%(table)
        self.executeZSQL(query, [oid])
        
        REQUEST.response.redirect(self.REQUEST['HTTP_REFERER'])
 
   
    def invalidate_cache(self):
        #TODO: How to invalidate the varnish cache from the member object
        pass;
        
    
    # TODO: compat, is this used?
    getStaffURL = getUsername
        

    def getBreadcrumbs(self):
        """return list of breadcrumbs from here to the root"""
        crumbs = []
        label = '%s %s'%(self.content.first_name, self.content.last_name)
        baseUrl = None
        parent = aq_parent(self)
        # get parents breadcrumbs
        if hasattr(parent, 'getBreadcrumbs'):
            crumbs = parent.getBreadcrumbs()
            if len(crumbs) > 0:
                # get base url from crumbs
                baseUrl = crumbs[-1]['url'] + '/members'
        
        if baseUrl is None:
            baseUrl = '/en/staff/members'
            
        # add this
        crumbs.append({'text':label, 'url':self.getUrl(baseUrl=baseUrl), 'object':self})
            
        return crumbs

        
    def getPublicationsFromPubman(self,limit=None,publicationType=None):
        """Return list of publications."""
        if self.content.publications_mode=="year": 
            coneId = self.getConeId();
            if coneId:
                pubs = self.folder.mpiwgPubman.getPublications(coneId,limit=limit,publicationType=publicationType)
                #pubs= self.folder.getPublicationsFromPubman(coneId,limit=limit,publicationType=publicationType)
                return pubs
        
        elif self.content.publications_mode=="priority":
            selPubs= self.getSelectedPublications()
            
            pubs=[]
            count =0
            for selPub in selPubs:
                if limit and count >= limit:
                    break
                
                logging.debug("searchFor:%s"%selPub.escidocid)
                entry = self.mpiwgPubman.getEntryFromPubman(selPub.escidocid,extendedData=True);
                
                #TODO getEntryFromPubmanShould return long texts
                typesLongShort={'http://purl.org/eprint/type/Book':'book',
                                'http://purl.org/eprint/type/BookItem':'book-item',
                                'http://purl.org/escidoc/metadata/ves/publication-types/article':'article'};
                
                if publicationType is not None: 
                    #publicaitions typ ist gesetzt 
                   
                    if not ((entry[1] == publicationType) or  (entry[1] == typesLongShort.get(publicationType,''))) : 
                        #stimmt nicht dann weiter
                        continue;
                  
                if len(entry)>4:  
                    pubs.append((selPub.escidocid,entry[0],entry[2],entry[3],entry[4]));
                    count+=1
                
            return pubs

        return []
    
    
    def publications_full_html(self, REQUEST):
        """show publication"""
        pt=PageTemplateFile('zpt/staff/pubman/show_publications.zpt', globals()).__of__(self)
        return pt(member=self.content)

      
    security.declareProtected('View management screens','addPublicationsFromPubman')   
    def addPublicationsFromPubman(self,REQUEST):
        """addPublications from pubman"""
        
        data=REQUEST.form
       
        if data.get("method",None) is None:
            pt=PageTemplateFile('zpt/staff/pubman/add_publications.zpt', globals()).__of__(self)
            return pt()
        
        
        
        if data.get("method") == "search":
            
    
            
            entries= self.mpiwgPubman.search(data,contexts=["escidoc:85274","escidoc:38279","escidoc:644427"],resultWithContext=True)
            pt=PageTemplateFile('zpt/staff/pubman/add_publications.zpt', globals()).__of__(self)
            
           
            return pt(values=entries)
     
     
        
        if data.get("method") == "add":
            
            return self.addEntriesToPublicationList(data)
            #pt=PageTemplateFile(os.path.join(package_home(globals()),'zpt/staff/pubman','add_publications.zpt')).__of__(self)
          
            
            
    def addEntriesToPublicationList(self,data):
        """fuege eintrage aus data zur publications liste,
        @param data Map mit escidocID --> value
        value muss "add" sein damit hinzugefuegt wird"""
        
        for key in data.keys():
            
            if key.startswith('escidoc:'):
            
            
                query="INSERT INTO pubmanbiblio (key_main,escidocId) values (%s,%s)"
                
                if data.get(key)=="add":
                    self.executeZSQL(query,[self.getKey(),key])
                    
            
        
        #selectedPublications = self.getSelectedPublications()
        
        #pt = PageTemplateFile(os.path.join(package_home(globals()),'zpt/staff/pubman','change_publications.zpt')).__of__(self)
        
        #return pt()
        if hasattr(self,'REQUEST'):
            return self.REQUEST.response.redirect("changePublications")
    
    
    def changePublications(self,REQUEST):
        """change published publications"""
        
        data=REQUEST.form
       
        if data.get("method","") == "change":
           
            for key in data.keys():
                splitted=key.split("__") #format escidoc_id__p fuer priority, nur escidocid
                value=data[key]
                if len(splitted)==1:
                    self.deleteFromPublicationList(key);
                
                elif(splitted[1]) == "p":
                    self.setPublicationPriority(splitted[0],value);
                    
        
        pt = PageTemplateFile('zpt/staff/pubman/change_publications', globals()).__of__(self) 
        return pt()
    
    
    def deleteFromPublicationList(self,escidocid):
        """Loessche publication with escidoc id from publication list"""
        
        query ="DELETE FROM pubmanbiblio WHERE escidocid=%s and lower(key_main)=%s"
        
        self.executeZSQL(query,[escidocid,self.getKey().lower()]);
        
    
    def setPublicationPriority(self,escidocid,value):
        try:
            query="update pubmanbiblio set priority=%s where escidocid=%s and lower(key_main)=%s"
            
            try:
                x=int(value)
            except:
                logging.error("priority not a number")
                return
            
            self.executeZSQL(query,[value,escidocid,self.getKey().lower()]);
           
        except:
            logging.error("couldn't change:")
            logging.error(escidocid)
            logging.error(value)
            

    def getSelectedPublications(self):
        """hole publications aus der datenbank"""
        query="select * from pubmanbiblio where lower(key_main) = lower(%s) order by priority ASC"
        return self.executeZSQL(query,[self.getKey()])
    
    
    def getProfileHtml(self, REQUEST):
        """get the profile"""
        REQUEST.RESPONSE.setHeader('Last-Modified',email.Utils.formatdate().split("-")[0]+'GMT')
        html="""<html><body>%s</body></html>"""
        if self.content.profile and self.content.profile != "":
            return html%self.content.profile
        else:
            return html%""
   
            
    def generateProfileForPerson(self,REQUEST=None):
        """erzeugt ein automatisches Profil aus den alten Eintraegen  CV, Current work, und research interests"""
        
        ret=""
        #founds=self.ZSQLInlineSearch(_table='research_interest',key_main=person.getKeyUTF8())
        founds=self.executeZSQL('select * from research_interest where lower(key_main) = %s', [self.getKey().lower()])
        if founds:
            ret+='<h3>Research interests</h3>\n<p>'
        for found in self.sortPriority(founds):
            ret+=found.interest+'<br/>'
        if founds:
            ret+='</p>\n'
        if self.content.current_work:
            ret+="<h3>Current work</h3>"
            ret+='<p>'+self.content.current_work+'</p>\n'
        if (self.content.cv_p == 'yes') and self.content.cv:
            ret+="<h3>Curriculum Vitae</h3>"
            ret+='<p>'+self.formatAscii(self.content.cv)+'</p>'
        
        return ret
           
           

# initialize security
InitializeClass(MPIWGStaffFolder) 
InitializeClass(MPIWGStaffMember)