#!/usr/local/bin/maxx-python

import sys

if len(sys.argv) > 2:
    pathToRoot = sys.argv[1]
    confFileName = sys.argv[2]
    userId = sys.argv[3]
else:
    print "usage: ... pathToRoot confFileName userId"
    sys.exit(-1)

# This code is to stop the deprecation warning we get regarding the re module
# that is used by DocumentTemplate
import warnings
warnings.filterwarnings('ignore',
                        '.*depreca.*',
                        module='.*')


delete = 1
save = 1
mlTag = 1001

########################################################
# We give ourselves a (command-line) handler and a
# persistent broker; after this, we have a usable
# environment for the script
########################################################
sys.path.append(pathToRoot+"/libs") # first arg is path to co root
sys.path.append(pathToRoot+"/generic")
import framework.CLHandler
h,pb = framework.CLHandler.makeHandlerAndPB(confFileName, userId)
h.readWebselects(pb)

import DateTime
DateTime = DateTime.DateTime

MailingList = pb.loadClass('MailingList')
Individual = pb.loadClass('Individual')

def makeInClause(field, lst):
    inClause = '%s in (' % field
    if len(lst) == 0:
        return inClause + 'NULL)'
    else:
        for item in lst:
            inClause += str(item) + ','
        inClause = inClause[:-1] + ')'
        return inClause
    
def clearPreviousRun():
    print "deleting old records"
    sys.stdout.flush()
    
    mailingListList = []

    sql = "select MailingListId from MailingList where listVolume = %s" % mlTag
    rs = pb.processSql(sql)
    for res in rs:
            mailingListList.append(res['MailingListId'])

    if len(mailingListList):
        inClause = makeInClause('MailingLIstId', mailingListList)
        sql = 'delete from Individual2MailingList where %s' % inClause
        pb.processSql(sql)
        sql = 'delete from Moderator2MailingList where %s' % inClause
        pb.processSql(sql)
        sql = 'delete from Staff2MailingList where %s' % inClause
        pb.processSql(sql)
        sql = 'delete from MailingListInclude2Individual where %s' % inClause
        pb.processSql(sql)
        sql = 'delete from MailingListExclude2Individual where %s' % inClause
        pb.processSql(sql)
        sql = 'delete from Search where %s' % inClause
        pb.processSql(sql)
        sql = 'delete from MailingList where %s' % inClause
        pb.processSql(sql)

def makeIndividualDict():
    visionsDict = {}
    
    sql = 'select * from listmembers;'
    cur.execute(sql)

    sql = 'select IndividualId,visionsId from Individual where visionsId in ('
    for row in cur.fetchall():
        row['OtherContactID'] = row['OtherContactID'].strip()
        visionsId = None
        if row['ContractorContactID']:
            visionsId = "ContractorContacts %s" % row['ContractorContactID']
        elif row['CraftContactID']:
            visionsId = "CraftContacts %s" % row['CraftContactID']
        elif row['LocalContactID']:
            visionsId = "LocalContacts %s" % row['LocalContactID']
        elif row['OwnerContactID']:
            visionsId = "OwnerContacts %s" % row['OwnerContactID']
        elif row['OtherContactID']:
            visionsId = "OtherContacts %s" % row['OtherContactID']
        if visionsId:
            sql += '\"%s\",' % visionsId
    sql = sql[:-1] + ');'
        
    rs = pb.processSql(sql)
    for row in rs:
        individual = Individual(("%X" % row['IndividualId']))
        visionsDict[row['visionsId']] = individual

    return visionsDict

def setMLProperties(ml, name, shortname):
		name = name.replace('/','-')
		name = name.replace('.','')
		name = name.replace('&','and')
		ml.volume = mlTag
		ml.name = name
		ml.shortName = shortname
		ml.shortDescription = name
		ml.description = name
		ml.prepend = ml.PREPEND_NO
		ml.joinRule = ml.JOIN_RULE_CLOSED
		ml.AccessRules = ml.ACCESS_PUBLIC
		ml.deleted = ml.DELETED_NO
		ml.replyDestination = ml.REPLY_DESTIN_LIST
		ml.postRule = ml.POST_RULE_DESIGNATED
		ml.moderated = ml.MODERATED_NO

def setMembers(ml, id, visionsDict):
    sql = "select * from listmembers where ListId = '%s';" % id
    cur.execute(sql)
    for row in cur.fetchall():
        row['OtherContactID'] = row['OtherContactID'].strip()
        visionsId = None
        if row['ContractorContactID']:
            visionsId = "ContractorContacts %s" % row['ContractorContactID']
        elif row['CraftContactID']:
            visionsId = "CraftContacts %s" % row['CraftContactID']
        elif row['LocalContactID']:
            visionsId = "LocalContacts %s" % row['LocalContactID']
        elif row['OwnerContactID']:
            visionsId = "OwnerContacts %s" % row['OwnerContactID']
        elif row['OtherContactID']:
            visionsId = "OtherContacts %s" % row['OtherContactID']

        if visionsId and visionsDict.has_key(visionsId):
            ml.includes.append(visionsDict[visionsId])
        else:
            print '  Failed Add for ID: %s' % visionsId

now = DateTime()
print 'start: %0.2d:%0.2d:%0.2d' %(now.hour(), now.minute(), now.second())

import MySQLdb
import getPassword
#DB User/Pass
dburluser, dburlpassword = getPassword.getPassword()
dburlhost = '192.168.13.69'
dburlname = 'nea_initial'
dburlcon = MySQLdb.connect(user=dburluser, passwd=dburlpassword, 
                           db=dburlname, host=dburlhost)
cur = dburlcon.cursor(MySQLdb.cursors.DictCursor)

if delete:
    clearPreviousRun()
    
visionsDict = makeIndividualDict()

sql = 'select * from lists;'
cur.execute(sql)

for row in cur.fetchall():
    ml = MailingList()
    setMLProperties(ml, row['ListDesc'], row['ShortListDesc'])
    print '%s' % (ml.name)
    setMembers(ml, row['ListID'], visionsDict)

    if save:
        ml.save()


now = DateTime()
print 'end: %0.2d:%0.2d:%0.2d' %(now.hour(), now.minute(), now.second())