# zclass helper
# reads the propertyMap and
# makes some crude dtml forms and zsql methods
# ross lazarus March 20 2000
# add this external method to any zclass
# view the makemethods method
# lot's of todo's !
# Originally written for zformulator
# but it seems easier to extend the zclass
# if the zclass propertysheet were extended to include things like
# prompt text and formorder for all fields
# min/max for numerics
# maxlen for strings
# prikey field combination and sqlconnection_id for each zclass
# then the zsql could be much smarter and it wouldn't be all that hard
# to write some validating javascript for the dtml forms...
# added some code to generate the tedious stuff for external methods
# to convert zclasses to sql march 22 rml

import string,time,operator
from Products.ZSQLMethods.SQL import SQL ### needed for zsql method creation 


# you need to set things here until we get them from
# an extended zclass property sheet.
# these work with sybase
myname = 'ExtendZClass'
version = '0.001 of March 27 2000'
prikeyfield = 0 # field number for primary key in zsql - needs to be more flexible !
sqlconnection_id = 'AAASinSybase'
methodsfoldername = 'MethodsFolder'
defstringlen = 60
defdatelen = 35
defnumlen = 10
proptypes = ['string',    'int',    'float','long','boolean','text','tokens','lines','selection','date']
sqltypes = ['varchar(80)','integer','float','long','char(1)','text','text',  'text','varchar(80)','varchar(80)']
zopesqltypes = ['string',    'int',    'float','long','boolean','string','tokens','string','string','string']

def makedate(t):
     return time.strftime('%d/%m/%Y %H:%M',time.localtime(t))

def sqltype(t):
     """
     return the sql var type for each of the possible prop sheet types
     """
     retval = t
     try:
          i = operator.indexOf(proptypes,t)
          retval = sqltypes[i]
     except:
          pass
     return retval

def zsqltype(t):
     """
     return the zope sql var type for each of the possible prop sheet types
     """
     retval = t
     try:
          i = operator.indexOf(proptypes,t)
          retval = zopesqltypes[i]
     except:
          pass
     return retval

def view_widget_for_type(self, t, id):
   """
   originally lifted from zclass PropertySheets.py
   """
   if t not in ('lines', 'tokens'):
       return '<dtml-var %s>' % id
   return """
   <dtml-in %s>
      <dtml-var sequence-item>
   </dtml-in %s>
   """ % (id, id)

def add_widget_for_type(self, t, id, p):
   """
   like edit_widget but no value yet
   originally lifted from zclass PropertySheets.py
   """
   if t == 'string':
       q=' html_quote'
       return ('''  <input name="%s:%s" size="%d">'''
               % (id, t, defstringlen)
               )
   if t == 'date':
       q=' html_quote'
       return ('''
       <input name="%s:%s" size="%d">'''
               % (id, t, defdatelen)
               )
   if t in ['int', 'long', 'float']:
       return ('''
       <input name="%s:%s" size="%d">'''
               % (id, t, defnumlen)
               )
   if t=='boolean':
       return ('''
       <input type="checkbox" name="%s:boolean">'''
               % (id)
               )
               
   if t=='tokens':
       return ('''
       <input type="text" name="%s:tokens" size="35">'''
               % (id)
               )
               
   if t=='text':
       return ('''
       <textarea name="%s:text" rows="6" cols="%d" wrap="virtual"></textarea>'''
               % (id, defstringlen)
               )
               
   if t=='lines':
       return ('''
       <textarea name="%s:lines" rows="6" cols="%d"></textarea>'''
               % (id, defstringlen) 
               )

               
   if t=='selection':
       return ('''
       <dtml-if "_.has_key('%(id)s')">
       <select name="%(id)s">
         <dtml-in "_.string.split('%(select_variable)s')">
           <option
             <dtml-if "_['sequence-item']=='%(id)s'">
             SELECTED</dtml-if>
             ><dtml-var sequence-item></option>
         </dtml-in>
       </select>
       <dtml-else>
         No value for %(id)s
       </dtml-if>'''            
               % p
               )

   return ''

def edit_widget_for_type(self, t, id, p):
   if t == 'string':
       q=' html_quote'
       return ('''
       <input name="%s:%s" size="%d"
              value="<dtml-var %s%s>">'''
               % (id, t, defstringlen, id, q)
               )
   if t == 'date':
       q=' html_quote'
       return ('''
       <input name="%s:%s" size="%d"
              value="<dtml-var %s%s>">'''
               % (id, t, defdatelen, id, q)
               )
   if t in ['int', 'long', 'float']:
       return ('''
       <input name="%s:%s" size="%d"
              value="<dtml-var %s>">'''
               % (id, t, defnumlen, id)
               )
   if t=='boolean':
       return ('''
       <input type="checkbox" name="%s:boolean" 
           <dtml-if %s>CHECKED</dtml-if>>'''
               % (id, id)
               )
               
   if t=='tokens':
       return ('''
       <input type="text" name="%s:tokens" size="35"
          value="<dtml-in %s><dtml-var sequence-item> </dtml-in>">'''
               % (id, id)
               )
               
   if t=='text':
       return ('''
       <textarea name="%s:text" rows="6" cols="%d" wrap="virtual"><dtml-var %s></textarea>'''
               % (id, defstringlen, id)
               )
               
   if t=='lines':
       return ('''
       <textarea name="%s:lines" rows="6" cols="%d"><dtml-in %s
       ><dtml-var sequence-item>\n</dtml-in></textarea>'''
               % (id, defstringlen, id) 
               )

               
   if t=='selection':
       return ('''
       <dtml-if "_.has_key('%(id)s')">
       <select name="%(id)s">
         <dtml-in "_.string.split('%(select_variable)s')">
           <option
             <dtml-if "_['sequence-item']=='%(id)s'">
             SELECTED</dtml-if>
             ><dtml-var sequence-item></option>
         </dtml-in>
       </select>
       <dtml-else>
         No value for %(id)s
       </dtml-if>'''            
               % p
               )

   return ''


     
def getFields(self,REQUEST):
      """
      Get all fields in this zclass into a dictionary with the type and the
      actual object
      """
      fields = []
      ftypes = []
      props = []
      fields.append('id') # add the id field !
      ftypes.append('string')
      props.append('')
      ps = REQUEST['PARENTS'][1]
      common = getattr(ps,'common')
      basic = getattr(common,'Basic')
      pm = basic.propertyMap()
      for prop in pm:
                fields.append(prop['id'])
                ftypes.append(prop['type'])
                props.append(prop)
      return fields,ftypes,props


### rml dec 1999
def makezsql(self,REQUEST,newid='',newtitle='',zsqlargs_str='',sql=''):
   '''
   method to make a methodsfolder if necessary and add a
   zsql method or replace the existing one
   rml dec 1999
   '''
   meth = REQUEST['PARENTS'][0]
   testid = newid + ' ' # wierd - ids have an extra space at the end ?
   if testid in meth.objectIds(): # not sure why, but this works
        for zname,zthing in meth.objectItems():
             if string.strip(zname) == newid:
                  zthing.manage_edit( title=newtitle, connection_id = sqlconnection_id, arguments = zsqlargs_str, template = sql)
   else:
        method = SQL(newid, newtitle, sqlconnection_id, zsqlargs_str, sql)
        self._setObject(newid, method)

def makedtml(self,REQUEST,newid='',newtitle='',dtml=''):
   '''
   make a dtml method
   '''
   testid = newid + ' ' # wierd - ids have an extra space at the end ?
   if testid in self.objectIds('DTML Method'):
         self.manage_delObjects(testid)               
   self.manage_addDTMLMethod(newid,newtitle,dtml)

def getDTMLAddNew(self,REQUEST):
   """
   make a dtml screen to add a new blank record - crude tabular layout..
   todo: fancy footwork with auto id's ?
   """
   formaction = zcname + '_add'
   formname = zcname + 'DTMLAddNewForm'

   fields,ftypes,fprops = getFields(self,REQUEST)
   res = []
   res.append('<dtml-var standard_html_header>')
   res.append('<form name="%s" action="%s" method="POST">' % (formname, formaction))
   res.append('<table>')
   for i in range(len(fields)):
       field = fields[i]
       ftype = ftypes[i]
       prop = fprops[i]
       res.append('<tr><th>%s</th><td>%s</td></tr>' % (field, add_widget_for_type(self,ftype, field, prop)))
   res.append('<tr><td colspan="2"><input type="submit" value=" Add New "></td></tr>')
   res.append('</table></form>')
   res.append('<dtml-var standard_html_footer>')
   res_str = string.join(res,'\n')
   makedtml(self,REQUEST,formname,'Add new record form of '+ makedate(time.time()),res_str)
   return res_str

def getDTMLUpdate(self,REQUEST):
   """
   make a dtml screen to update an existing record - crude tabular layout..
   todo: fancy footwork with auto id's ?
   """
   formaction = zcname + '_update'
   formname = zcname + 'DTMLUpdateForm'

   fields,ftypes,fprops = getFields(self,REQUEST)
   res = []
   res.append('<dtml-var standard_html_header>')
   res.append('<form name="%s" action="%s"  method="POST">' % (formname, formaction))
   res.append('<table>')
   for i in range(len(fields)):
       field = fields[i]
       ftype = ftypes[i]
       prop = fprops[i]
       valuestr = '<dtml-var %s>' % field
       res.append('<tr><td>%s</td><td>%s</td></tr>' % (field, edit_widget_for_type(self, ftype, field, prop)))
   res.append('<tr><td colspan="2"><input type="submit" value=" Add New "></td></tr>')
   res.append('</table></form>')
   res.append('<dtml-var standard_html_footer>')
   res_str = string.join(res,'\n')
   makedtml(self,REQUEST,formname,'Update record of ' + makedate(time.time()),res_str)
   return res_str

def getMakeRequestSpace(self,REQUEST):
     '''
     make some python !
     '''
     res = []
     formname = zcname + 'PythonCode'
     fields,ftypes,fprops = getFields(self,REQUEST)
     for i in range(len(fields)):
          field = fields[i]
          res.append("REQUEST['%s'] = getattr(zc,'%s')" % (field,field))
     res_str = string.join(res,'\n')
     makedtml(self,REQUEST,formname,'Python code - Zclass to REQUEST. ' + makedate(time.time()),res_str)

                  

def getSQLCreate(self,REQUEST):
 """Get SQL create statement for this form.
 """
 # create list of types
 typeslist = []
 fields,ftypes,fprops = getFields(self,REQUEST)
 for i in range(len(fields)):
     field = fields[i]
     ftype = ftypes[i]
     if i > 0:
          typeslist.append("%s %s" % (field, sqltype(ftype)))
     else: # add unique constraint for primary key
          typeslist.append("%s %s unique" % (field, sqltype(ftype)))
 typesstr = string.join(typeslist, ",\n")

 # construct create statement
 sql =\
"""
create table %s (
%s
)
""" % (zcname, typesstr)
 newid = zcname + 'ZSQLcreatetable' # name for new method
 newtitle = 'CAREFUL ! Create an EMPTY ' + zcname + ' table, ' + makedate(time.time())
 makezsql(self,REQUEST,newid,newtitle,'',sql)
 
 return sql

def getSQLcheckid(self,REQUEST):
 """Get SQL check existing id for this form.
 """
 # create list of types
 typeslist = []
 fields,ftypes,fprops = getFields(self,REQUEST)
 checks = 'where %s = <dtml-sqlvar %s type=%s>' % (fields[prikeyfield],fields[prikeyfield],zsqltype(ftypes[prikeyfield]))
 zsqlargs= '%s:string ' % fields[prikeyfield]
 # construct create statement
 sql =\
"""
select * from %s 
%s
""" % (zcname, checks)
 newid = zcname + 'ZSQLcheckid' # name for new method
 newtitle = 'Check id exists in ' + zcname + ' table of ' + makedate(time.time())
 makezsql(self,REQUEST,newid,newtitle,zsqlargs,sql)
 
 return sql


def getSQLInsert(self,REQUEST):
 """Get SQL insert statement for this form.
 """

 # create fieldnames and fieldvalues
 fieldnames = []
 fieldvalues = []
 zsqlargs = []
 fields,ftypes,fprops = getFields(self,REQUEST)
 for i in range(len(fields)):
     field = fields[i]
     ftype = ftypes[i]
     zsqlargs.append('%s:%s ' % (field,ftype)) 
     ### what zope zsql tags to use? - string will probably work....
     fieldnames.append(field)
     fieldvalues.append('<dtml-sqlvar %s type=%s>' % (field,zsqltype(ftype)))
 zsqlargs_str = string.join(zsqlargs,'\n')
 fieldnames_str = string.join(fieldnames, ",\n")
 fieldvalues_str = string.join(fieldvalues, ",\n")
 
 sql =\
"""
insert into %s (
%s
)
values (
%s
) 
""" % (zcname, fieldnames_str, fieldvalues_str)
 newid = zcname + 'ZSQLinsert' # name for new method
 newtitle = 'Insert new ' + zcname + ' record of ' + makedate(time.time())
 makezsql(self,REQUEST,newid,newtitle,zsqlargs_str,sql)

 return sql

### Update method added dec 99 rossl 
def getSQLUpdate(self,REQUEST):
 """Get SQL update statement for this form.
 """
 fields,ftypes,props = getFields(self,REQUEST)
 zsqlargs = []
 # create fieldnames and fieldvalues
 setstatement = ['set ']
 for i in range(len(fields)):
   field = fields[i]
   ftype = ftypes[i]
   setstatement.append('%s = <dtml-sqlvar %s type=%s>,' % (field,field,zsqltype(ftype)))
   zsqlargs.append('%s:%s ' % (field,ftype))
 lastone = setstatement[-1] 
 setstatement[-1] = lastone[:-1] # drop last ,
 setstatement.append('where %s = <dtml-sqlvar %s type=%s>' % (fields[prikeyfield],fields[prikeyfield],zsqltype(ftypes[prikeyfield])))                       
 setstatement_str = string.join(setstatement, "\n")
 zsqlargs_str = string.join(zsqlargs,'\n')
 sql =\
"""
update %s
%s
""" % (zcname, setstatement_str)
 newid = zcname + 'ZSQLupdate' # name for new method
 newtitle = 'Update existing ' + zcname + ' record of ' + makedate(time.time())
 makezsql(self,REQUEST,newid,newtitle,zsqlargs_str,sql)
 return sql


def makemethods(self,REQUEST):
     """
     make dtml and sql methods for this zclass
     """

     global zcname
     zc = REQUEST['PARENTS'][2]
     zcname = zc.id
     getDTMLAddNew(self,REQUEST)
     getDTMLUpdate(self,REQUEST)
     getSQLCreate(self,REQUEST)
     getSQLInsert(self,REQUEST)
     getSQLUpdate(self,REQUEST)
     getSQLcheckid(self,REQUEST)
     getMakeRequestSpace(self,REQUEST)
     return '<a href="manage_workspace">Click here</a> to continue'