def collectNewBounces(self): #{{{
agn.log(agn.LV_INFO, 'collect', 'Start collecting new bounces')
iquery = 'INSERT INTO bounce_collect_tbl (customer_id, company_id, mailing_id, change_date) VALUES (:customer, :company, :mailing, current_timestamp)'
insert = self.db.cursor()
if insert is None:
raise agn.error(
'collectNewBounces: Failed to get new cursor for insertion')
bquery = self.db.cursor()
if bquery is None:
raise agn.error(
'collectNewBounces: Failed to get new cursor for bounce query')
data = {}
query = 'SELECT customer_id, company_id, mailing_id, detail FROM bounce_tbl WHERE %s ORDER BY company_id, customer_id' % self.timestamp.makeBetweenClause(
'change_date', data)
cur = [0, 0, 0, 0]
(records, uniques, inserts) = (0, 0, 0)
if bquery.query(query, data) is None:
raise agn.error(
'collectNewBounces: Failed to query bounce_tbl using: %s' %
query)
while cur is not None:
try:
record = bquery.next()
records += 1
except StopIteration:
record = None
if record is None or cur[0] != record[0] or cur[1] != record[1]:
if record is not None:
uniques += 1
if cur[0] > 0 and cur[3] >= 400 and cur[3] < 510:
parm = {
'customer': cur[0],
'company': cur[1],
'mailing': cur[2]
}
insert.update(iquery, parm)
inserts += 1
if inserts % 10000 == 0 or record is None:
agn.log(
agn.LV_DEBUG, 'collect',
'Inserted now %s record%s' %
(agn.numfmt(inserts), exts(inserts)))
insert.sync()
cur = record
elif record[3] > cur[3]:
cur = list(record)
self.db.commit()
bquery.close()
insert.close()
agn.log(
agn.LV_INFO, 'collect',
'Read %d records (%d uniques) and inserted %d' %
(records, uniques, inserts))
def readConfiguration(self):
config = None
db = eagn.DB()
try:
if db.isopen():
config = (db.cursor.stream(
'SELECT name, value FROM config_tbl WHERE class = :class',
{
'class': 'generate'
}).filter(lambda nv: nv[0] is not None).dict())
else:
raise agn.error('access to database: %s' % db.lastError())
except Exception as e:
agn.log(agn.LV_ERROR, 'sched',
'Failed to read configuration: %s' % e)
finally:
db.done()
if config is not None:
self.config = config
self.companies = self.configuration(
'companies', convert=lambda v: agn.Stream.ifelse(agn.Range(v)))
if self.companies:
agn.log(
agn.LV_INFO, 'sched',
'Limit operations on these companies: %s' % self.companies)
def closeNode(self, name=None):
"""close an open node, if ``name'' is None, close the
last opened node, otherwise search for the node with the ``name'' and
close it (implicit close all inner nodes as well)"""
if not self.backlog:
raise agn.error('Empty backlog')
if name is None:
name = self.backlog.pop()
self.__endNode(name)
else:
if not name in self.backlog:
raise agn.error('%s not found in backlog' % name)
while self.backlog:
pname = self.backlog.pop()
self.__endNode(pname)
if pname == name:
break
def collectNewBounces (self): #{{{
agn.log (agn.LV_INFO, 'collect', 'Start collecting new bounces')
iquery = 'INSERT INTO bounce_collect_tbl (customer_id, company_id, mailing_id, change_date) VALUES (:customer, :company, :mailing, current_timestamp)'
insert = self.db.cursor ()
if insert is None:
raise agn.error ('collectNewBounces: Failed to get new cursor for insertion')
bquery = self.db.cursor ()
if bquery is None:
raise agn.error ('collectNewBounces: Failed to get new cursor for bounce query')
data = {}
query = 'SELECT customer_id, company_id, mailing_id, detail FROM bounce_tbl WHERE %s ORDER BY company_id, customer_id' % self.timestamp.makeBetweenClause ('change_date', data)
cur = [0, 0, 0, 0]
(records, uniques, inserts) = (0, 0, 0)
if bquery.query (query, data) is None:
raise agn.error ('collectNewBounces: Failed to query bounce_tbl using: %s' % query)
while cur is not None:
try:
record = bquery.next ()
records += 1
except StopIteration:
record = None
if record is None or cur[0] != record[0] or cur[1] != record[1]:
if record is not None:
uniques += 1
if cur[0] > 0 and cur[3] >= 400 and cur[3] < 510:
parm = {
'customer': cur[0],
'company': cur[1],
'mailing': cur[2]
}
insert.update (iquery, parm)
inserts += 1
if inserts % 10000 == 0 or record is None:
agn.log (agn.LV_DEBUG, 'collect', 'Inserted now %s record%s' % (agn.numfmt (inserts), exts (inserts)))
insert.sync ()
cur = record
elif record[3] > cur[3]:
cur = list (record)
self.db.commit ()
bquery.close ()
insert.close ()
agn.log (agn.LV_INFO, 'collect', 'Read %d records (%d uniques) and inserted %d' % (records, uniques, inserts))
def updateConfigfile (self, new):
if new != self.last:
temp = configFilename + '.%d' % os.getpid ()
try:
fd = open (temp, 'w')
fd.write (new)
fd.close ()
self.renameFile (temp, configFilename)
self.last = new
except IOError, e:
agn.log (agn.LV_ERROR, 'data', 'Unable to write %s %s' % (temp, `e.args`))
raise agn.error ('updateConfigfile.open')
def updateConfigfile (self, new):
if new != self.last:
temp = '%s.%d' % (self.configFilename, os.getpid ())
try:
fd = open (temp, 'w')
fd.write (new)
fd.close ()
self.renameFile (temp, self.configFilename)
self.last = new
except IOError, e:
agn.log (agn.LV_ERROR, 'data', 'Unable to write %s %s' % (temp, `e.args`))
raise agn.error ('updateConfigfile.open')
def mergeNewBounces(self): #{{{
agn.log(agn.LV_INFO, 'merge',
'Start merging new bounces into softbounce_email_tbl')
iquery = 'INSERT INTO softbounce_email_tbl (company_id, email, bnccnt, mailing_id, creation_date, change_date) VALUES (:company, :email, 1, :mailing, now(), now())'
uquery = 'UPDATE softbounce_email_tbl SET mailing_id = :mailing, change_date = now(), bnccnt=bnccnt+1 WHERE company_id = :company AND email = :email'
icurs = self.db.cursor()
ucurs = self.db.cursor()
squery = 'SELECT count(*) FROM softbounce_email_tbl WHERE company_id = :company AND email = :email'
scurs = self.db.cursor()
dquery = 'DELETE FROM bounce_collect_tbl WHERE company_id = :company'
dcurs = self.db.cursor()
if None in [icurs, ucurs, scurs, dcurs]:
raise agn.error(
'mergeNewBounces: Unable to setup curses for merging')
coll = [1]
for company in sorted(coll):
agn.log(agn.LV_INFO, 'merge', 'Working on %d' % company)
query = 'SELECT mt.customer_id, mt.mailing_id, cust.email '
query += 'FROM bounce_collect_tbl mt, customer_%d_tbl cust ' % company
query += 'WHERE cust.customer_id = mt.customer_id '
query += 'AND mt.company_id = %d ' % company
query += 'ORDER BY cust.email, mt.mailing_id'
for record in self.curs.query(query):
(cuid, mid, eml) = record
parm = {
'company': company,
'customer': cuid,
'mailing': mid,
'email': eml
}
data = scurs.querys(squery, parm, cleanup=True)
if not data is None:
if data[0] == 0:
icurs.update(iquery, parm, cleanup=True)
else:
ucurs.update(uquery, parm, cleanup=True)
parm = {'company': company}
dcurs.update(dquery, parm, cleanup=True)
self.db.commit()
icurs.close()
ucurs.close()
scurs.close()
dcurs.close()
agn.log(agn.LV_INFO, 'merge', 'Merging of new bounces done')
def mergeNewBounces (self): #{{{
agn.log (agn.LV_INFO, 'merge', 'Start merging new bounces into softbounce_email_tbl')
iquery = 'INSERT INTO softbounce_email_tbl (company_id, email, bnccnt, mailing_id, creation_date, change_date) VALUES (:company, :email, 1, :mailing, now(), now())'
uquery = 'UPDATE softbounce_email_tbl SET mailing_id = :mailing, change_date = now(), bnccnt=bnccnt+1 WHERE company_id = :company AND email = :email'
icurs = self.db.cursor ()
ucurs = self.db.cursor ()
squery = 'SELECT count(*) FROM softbounce_email_tbl WHERE company_id = :company AND email = :email'
scurs = self.db.cursor ()
dquery = 'DELETE FROM bounce_collect_tbl WHERE company_id = :company'
dcurs = self.db.cursor ()
if None in [ icurs, ucurs, scurs, dcurs ]:
raise agn.error ('mergeNewBounces: Unable to setup curses for merging')
coll = [1]
for company in sorted (coll):
agn.log (agn.LV_INFO, 'merge', 'Working on %d' % company)
query = 'SELECT mt.customer_id, mt.mailing_id, cust.email '
query += 'FROM bounce_collect_tbl mt, customer_%d_tbl cust ' % company
query += 'WHERE cust.customer_id = mt.customer_id '
query += 'AND mt.company_id = %d ' % company
query += 'ORDER BY cust.email, mt.mailing_id'
for record in self.curs.query (query):
(cuid, mid, eml) = record
parm = {
'company': company,
'customer': cuid,
'mailing': mid,
'email': eml
}
data = scurs.querys (squery, parm, cleanup = True)
if not data is None:
if data[0] == 0:
icurs.update (iquery, parm, cleanup = True)
else:
ucurs.update (uquery, parm, cleanup = True)
parm = {
'company': company
}
dcurs.update (dquery, parm, cleanup = True)
self.db.commit ()
icurs.close ()
ucurs.close ()
scurs.close ()
dcurs.close ()
agn.log (agn.LV_INFO, 'merge', 'Merging of new bounces done')
def main():
background = False
restartDelay = '60'
terminationDelay = '10'
output = False
prior = None
limit = 100
(opts, param) = getopt.getopt(sys.argv[1:], 'vi:br:t:op:l:')
for opt in opts:
if opt[0] == '-v':
agn.outlevel = agn.LV_DEBUG
agn.outstream = sys.stderr
elif opt[0] == '-i':
if opt[1] == '-':
if param:
agn.logname = '%s-wd' % (os.path.basename(param).split(
'.', 1)[0], )
else:
agn.logname = opt[1]
elif opt[0] == '-b':
background = True
elif opt[0] == '-r':
restartDelay = opt[1]
elif opt[0] == '-t':
terminationDelay = opt[1]
elif opt[0] == '-o':
output = True
elif opt[0] == '-p':
prior = opt[1]
elif opt[0] == '-l':
limit = int(opt[1]) if opt[1] != '-' else None
if not len(param):
raise agn.error('No command given to run under watchdog control')
agn.lock()
agn.log(agn.LV_INFO, 'main', 'Starting up')
wd = EWatchdog(param, output)
if background and wd.pushToBackground():
return
if prior:
wd.setPrior(prior)
wd.setLimit(limit)
wd.mstart(wd.Job(agn.logname, wd.run, ()), restartDelay, terminationDelay)
agn.log(agn.LV_INFO, 'main', 'Going down')
agn.unlock()
def main():
(opts, param) = getopt.getopt(sys.argv[1:], '')
pt = eagn.Processtable()
for comm in 'cron', 'crond':
p = pt.select(comm=comm, user='******')
if p:
break
if not p:
raise agn.error(
'cron: no such process, please install and activate service cron or crond (depending on your system)'
)
c = eagn.Crontab()
c.update([
'10 2 * * * /home/openemm/bin/janitor.sh',
'45 20 * * * /home/openemm/bin/bouncemanagement.sh',
],
user=None,
runas='openemm',
remove=[])
def updateEnd(self, inst):
raise agn.error('Need to overwrite updateEnd in your subclass')
def updateLine(self, inst, line):
raise agn.error('Need to overwrite updateLine in your subclass')
def convertToHardbounce (self): #{{{
agn.log (agn.LV_INFO, 'conv', 'Start converting softbounces to hardbounce')
coll = [1]
stats = []
for company in sorted (coll):
cstat = [company, 0, 0]
stats.append (cstat)
agn.log (agn.LV_INFO, 'conv', 'Working on %d' % company)
dquery = 'DELETE FROM softbounce_email_tbl WHERE company_id = %d AND email = :email' % company
dcurs = self.db.cursor ()
uquery = self.curs.rselect ('UPDATE customer_%d_binding_tbl SET user_status = 2, user_remark = \'Softbounce\', exit_mailing_id = :mailing, change_date = %%(sysdate)s WHERE customer_id = :customer AND user_status = 1' % company)
bquery = self.curs.rselect ('INSERT INTO bounce_tbl (company_id, customer_id, detail, mailing_id, change_date, dsn) VALUES (%d, :customer, 510, :mailing, %%(sysdate)s, 599)' % company)
ucurs = self.db.cursor ()
squery = 'SELECT email, mailing_id, bnccnt, creation_date, change_date FROM softbounce_email_tbl WHERE company_id = %d AND bnccnt > 7 AND DATEDIFF(change_date,creation_date) > 30' % company
scurs = self.db.cursor ()
if None in [dcurs, ucurs, scurs]:
raise agn.error ('Failed to setup curses')
lastClick = 30
lastOpen = 30
def toDatetime (offset):
tm = time.localtime (time.time () -offset * 24 * 60 * 60)
return datetime.datetime (tm.tm_year, tm.tm_mon, tm.tm_mday)
lastClickTS = toDatetime (lastClick)
lastOpenTS = toDatetime (lastOpen)
ccount = 0
for record in scurs.query (squery):
parm = {
'email': record[0],
'mailing': record[1],
'bouncecount': record[2],
'creationdate': record[3],
'timestamp': record[4],
'customer': None
}
query = 'SELECT customer_id FROM customer_%d_tbl WHERE email = :email ' % company
data = self.curs.querys (query, parm, cleanup = True)
if data is None:
continue
custs = [agn.struct (id = _d, click = 0, open = 0) for _d in data if _d]
if not custs:
continue
if len (custs) == 1:
cclause = 'customer_id = %d' % custs[0].id
else:
cclause = 'customer_id IN (%s)' % ', '.join ([str (_c.id) for _c in custs])
parm['ts'] = lastClickTS
query = 'SELECT customer_id, count(*) FROM rdir_log_tbl WHERE %s AND company_id = %d' % (cclause, company)
query += ' AND change_date > :ts GROUP BY customer_id'
for r in self.curs.queryc (query, parm, cleanup = True):
for c in custs:
if c.id == r[0]:
c.click += r[1]
parm['ts'] = lastOpenTS
query = 'SELECT customer_id, count(*) FROM onepixel_log_tbl WHERE %s AND company_id = %d' % (cclause, company)
query += ' AND change_date > :ts GROUP BY customer_id'
for r in self.curs.queryc (query, parm, cleanup = True):
for c in custs:
if c.id == r[0]:
c.open += r[1]
for c in custs:
if c.click > 0 or c.open > 0:
cstat[1] += 1
agn.log (agn.LV_INFO, 'conv', 'Email %s [%d] has %d klick(s) and %d onepix(es) -> active' % (parm['email'], c.id, c.click, c.open))
else:
cstat[2] += 1
agn.log (agn.LV_INFO, 'conv', 'Email %s [%d] has no klicks and no onepixes -> hardbounce' % (parm['email'], c.id))
parm['customer'] = c.id
ucurs.update (uquery, parm, cleanup = True)
ucurs.execute (bquery, parm, cleanup = True)
dcurs.update (dquery, parm, cleanup = True)
ccount += 1
if ccount % 1000 == 0:
agn.log (agn.LV_INFO, 'conv', 'Commiting at %s' % agn.numfmt (ccount))
self.db.commit ()
self.db.commit ()
scurs.close ()
ucurs.close ()
dcurs.close ()
for cstat in stats:
agn.log (agn.LV_INFO, 'conv', 'Company %d has %d active and %d marked as hardbounced users' % tuple (cstat))
agn.log (agn.LV_INFO, 'conv', 'Converting softbounces to hardbounce done')
def parse(self, info, line):
raise agn.error('Subclass must implement parse()')
def updateEnd (self, inst):
raise agn.error ('Need to overwrite updateEnd in your subclass')
def updateLine (self, inst, line):
raise agn.error ('Need to overwrite updateLine in your subclass')
def triggerMessage (self, cinfo, parm, dryrun):
try:
mailingID = int (parm['armid'])
companyID = int (parm['cid'])
if cinfo is None:
raise agn.error ('failed to determinate origin of mail')
if not cinfo.valid:
raise agn.error ('uid from foreign instance %d (expected from %d)' % (cinfo.licenceID, agn.licence))
customerID = cinfo.customerID
rdirDomain = None
with eagn.DB () as db:
rq = db.cursor.querys (
'SELECT mailinglist_id, company_id, deleted '
'FROM mailing_tbl '
'WHERE mailing_id = :mailingID',
{'mailingID': mailingID}
)
if rq is None:
raise agn.error ('mailing %d not found' % mailingID)
if rq.company_id != companyID:
raise agn.error ('mailing %d belongs to company %d, but mailloop belongs to company %d' % (mailingID, rq[1], companyID))
if rq.deleted:
agn.log (agn.LV_INFO, 'ar', 'mailing %d is marked as deleted' % mailingID)
mailinglistID = rq.mailinglist_id
#
rq = db.cursor.querys (
'SELECT user_type '
'FROM customer_%d_binding_tbl '
'WHERE customer_id = :customerID AND mailinglist_id = :mailinglistID AND mediatype = 0'
% companyID,
{
'customerID': customerID,
'mailinglistID': mailinglistID
}
)
if rq is None or rq.user_type not in ('A', 'T', 't'):
if not self.allow (parm, dryrun):
raise agn.error ('recipient is not allowed to received (again) an autoresponder')
else:
agn.log (agn.LV_INFO, 'ar', 'recipient %d on %d for %d is admin/test recipient and not blocked' % (customerID, mailinglistID, mailingID))
#
rq = db.cursor.querys (
'SELECT rdir_domain '
'FROM mailinglist_tbl '
'WHERE mailinglist_id = :mailinglistID',
{'mailinglistID': mailinglistID}
)
if rq is not None and rq.rdir_domain is not None:
rdirDomain = rq.rdir_domain
else:
rq = db.cursor.querys (
'SELECT rdir_domain '
'FROM company_tbl '
'WHERE company_id = :companyID',
{'companyID': companyID}
)
if rq is not None:
rdirDomain = rq.rdir_domain
if rdirDomain is None:
raise agn.error ('failed to determinate rdir-domain')
#
rq = db.cursor.querys (
'SELECT security_token '
'FROM mailloop_tbl '
'WHERE rid = :rid',
{'rid': int (self.aid)}
)
if rq is None:
raise agn.error ('no entry in mailloop_tbl for %s found' % self.aid)
securityToken = rq.security_token if rq.security_token else ''
url = ('%s/sendMailloopAutoresponder.do?'
'mailloopID=%s&companyID=%d&customerID=%d&securityToken=%s'
% (rdirDomain, self.aid, companyID, customerID, urllib.quote (securityToken)))
if dryrun:
print ('Would trigger mail using %s' % url)
else:
try:
uh = urllib2.urlopen (url)
response = uh.read ()
uh.close ()
agn.log (agn.LV_INFO, 'ar', 'Autoresponder mailing %d for customer %d triggered: %s' % (mailingID, customerID, response))
except (urllib2.URLError, urllib2.HTTPError), e:
agn.log (agn.LV_ERROR, 'ar', 'Failed to trigger %s: %s' % (url, str (e)))
except agn.error, e:
agn.log (agn.LV_INFO, 'ar', 'Failed to send autoresponder: %s' % str (e))
def convertToHardbounce(self): #{{{
agn.log(agn.LV_INFO, 'conv',
'Start converting softbounces to hardbounce')
coll = self.__ccoll(
'SELECT distinct company_id FROM softbounce_email_tbl WHERE company_id IN (SELECT company_id FROM company_tbl WHERE status = \'active\')',
None, None, None)
stats = []
for company in sorted(coll):
cstat = [company, 0, 0]
stats.append(cstat)
agn.log(agn.LV_INFO, 'conv', 'Working on %d' % company)
dquery = 'DELETE FROM softbounce_email_tbl WHERE company_id = %d AND email = :email' % company
dcurs = self.db.cursor()
uquery = self.curs.rselect(
'UPDATE customer_%d_binding_tbl SET user_status = 2, user_remark = \'bounce:soft\', exit_mailing_id = :mailing, timestamp = %%(sysdate)s WHERE customer_id = :customer AND user_status = 1'
% company)
bquery = self.curs.rselect(
'INSERT INTO bounce_tbl (company_id, customer_id, detail, mailing_id, timestamp, dsn) VALUES (%d, :customer, 510, :mailing, %%(sysdate)s, 599)'
% company)
ucurs = self.db.cursor()
squery = 'SELECT email, mailing_id, bnccnt, creation_date, timestamp FROM softbounce_email_tbl '
bnccnt = self.__cfg(company, 'convert-bounce-count', 40)
daydiff = self.__cfg(company, 'convert-bounce-duration', 30)
squery += self.curs.qselect(
oracle=
'WHERE company_id = %d AND bnccnt > %d AND timestamp-creation_date > %d'
% (company, bnccnt, daydiff),
mysql=
'WHERE company_id = %d AND bnccnt > %d AND DATEDIFF(timestamp,creation_date) > %d'
% (company, bnccnt, daydiff))
scurs = self.db.cursor()
if None in [dcurs, ucurs, scurs]:
raise agn.error('Failed to setup curses')
lastClick = self.__cfg(company, 'last-click', 30)
lastOpen = self.__cfg(company, 'last-open', 30)
def toDatetime(offset):
tm = time.localtime(time.time() - offset * 24 * 60 * 60)
return datetime.datetime(tm.tm_year, tm.tm_mon, tm.tm_mday)
lastClickTS = toDatetime(lastClick)
lastOpenTS = toDatetime(lastOpen)
if Cache is not None:
rcache = Cache(1000)
ocache = Cache(1000 * 1000)
ccurs = self.db.cursor()
if None in [rcache, ocache, ccurs
] or not rcache.valid() or not ocache.valid():
raise agn.error('Failed to setup caching')
agn.log(agn.LV_INFO, 'cache',
'Setup rdir log cache for %d' % company)
query = 'SELECT customer_id FROM rdirlog_%d_tbl WHERE timestamp > :ts' % company
parm = {'ts': lastClickTS}
for record in ccurs.query(query, parm):
rcache.add(record[0])
agn.log(agn.LV_INFO, 'cache',
'Setup one pixel log cache for %d' % company)
query = 'SELECT customer_id FROM onepixellog_%d_tbl WHERE timestamp > :ts' % company
parm = {'ts': lastOpenTS}
for record in ccurs.query(query, parm):
ocache.add(record[0])
ccurs.close()
agn.log(agn.LV_INFO, 'cache', 'Setup completed')
ccount = 0
for record in scurs.query(squery):
parm = {
'email': record[0],
'mailing': record[1],
'bouncecount': record[2],
'creationdate': record[3],
'timestamp': record[4],
'customer': None
}
query = 'SELECT customer_id FROM customer_%d_tbl WHERE email = :email ' % company
data = self.curs.querys(query, parm, cleanup=True)
if data is None:
continue
custs = [
agn.mutable(id=_d, click=0, open=0) for _d in data if _d
]
if not custs:
continue
if len(custs) == 1:
cclause = 'customer_id = %d' % custs[0].id
else:
cclause = 'customer_id IN (%s)' % ', '.join(
[str(_c.id) for _c in custs])
if Cache is not None:
for c in custs:
c.click += rcache.get(c.id, 0)
c.open += ocache.get(c.id, 0)
else:
parm['ts'] = lastClickTS
query = 'SELECT customer_id, count(*) FROM rdirlog_%d_tbl WHERE %s AND timestamp > :ts GROUP BY customer_id' % (
company, cclause)
for r in self.curs.queryc(query, parm, cleanup=True):
for c in custs:
if c.id == r[0]:
c.click += r[1]
parm['ts'] = lastOpenTS
query = 'SELECT customer_id, count(*) FROM onepixellog_%d_tbl WHERE %s AND timestamp > :ts GROUP BY customer_id' % (
company, cclause)
for r in self.curs.queryc(query, parm, cleanup=True):
for c in custs:
if c.id == r[0]:
c.open += r[1]
for c in custs:
if c.click > 0 or c.open > 0:
cstat[1] += 1
agn.log(
agn.LV_INFO, 'conv',
'Email %s [%d] has %d klick(s) and %d onepix(es) -> active'
% (parm['email'], c.id, c.click, c.open))
else:
cstat[2] += 1
agn.log(
agn.LV_INFO, 'conv',
'Email %s [%d] has no klicks and no onepixes -> hardbounce'
% (parm['email'], c.id))
parm['customer'] = c.id
ucurs.update(uquery, parm, cleanup=True)
ucurs.execute(bquery, parm, cleanup=True)
dcurs.update(dquery, parm, cleanup=True)
ccount += 1
if ccount % 1000 == 0:
agn.log(agn.LV_INFO, 'conv',
'Commiting at %s' % agn.numfmt(ccount))
self.db.commit()
if Cache is not None:
rcache.done()
ocache.done()
self.db.commit()
scurs.close()
ucurs.close()
dcurs.close()
for cstat in stats:
agn.log(
agn.LV_INFO, 'conv',
'Company %d has %d active and %d marked as hardbounced users' %
tuple(cstat))
agn.log(agn.LV_INFO, 'conv',
'Converting softbounces to hardbounce done')
def readDatabase (self, auto):
rc = ''
db = agn.DBase ()
if not db:
agn.log (agn.LV_ERROR, 'data', 'Unable to create database connection')
raise agn.error ('readDatabase.open')
try:
i = db.cursor ()
if not i:
agn.log (agn.LV_ERROR, 'data', 'Unable to get database cursor')
raise agn.error ('readDatabase.cursor')
try:
ctab = {}
query = 'SELECT company_id, mailloop_domain FROM company_tbl WHERE status = \'active\''
missing = []
for record in i.query (query):
if record[1]:
ctab[record[0]] = record[1]
else:
missing.append (record[0])
if missing:
missing.sort ()
agn.log (agn.LV_VERBOSE, 'data', 'Missing mailloop_domain for %s' % ', '.join ([str (m) for m in missing]))
query = 'SELECT rid, shortname, company_id, forward_enable, forward, ar_enable, ar_sender, ar_subject, ar_text, ar_html, subscribe_enable, mailinglist_id, form_id, date_format(change_date,\'%Y%m%d%H%i%S\') FROM mailloop_tbl'
for record in i.query (query):
subscribe_enable = None
mailinglist_id = None
form_id = None
(rid, shortname, company_id, forward_enable, forward, ar_enable, ar_sender, ar_subject, ar_text, ar_html, subscribe_enable, mailinglist_id, form_id, timestamp) = record
if not rid is None:
rid = str (rid)
domains = None
if timestamp is None:
timestamp = time.time ()
else:
timestamp = int (timestamp)
if ar_enable and not ar_text:
ar_enable = False
if ar_enable:
def nvl (s):
if s is not None:
return str (s)
return s
auto.append (Autoresponder (rid, timestamp, ar_sender, ar_subject, nvl (ar_text), nvl (ar_html)))
if domains is None:
try:
cdomain = ctab[company_id]
if self.domains and cdomain != self.domains[0]:
domains = [self.domains[0]]
else:
domains = []
if agn.iswin and not cdomain in self.domains:
self.domains.append (cdomain)
if not self.domains or cdomain in self.domains:
domains.append (cdomain)
else:
agn.log (agn.LV_WARNING, 'data', 'Domain "%s" not known' % cdomain)
except KeyError:
agn.log (agn.LV_DEBUG, 'data', 'No domain for company found, further processing')
if domains is None:
domains = self.domains
elif not self.domains[0] in domains:
domains.insert (0, self.domains[0])
extra = 'rid=%s' % rid
if company_id:
extra += ',cid=%d' % company_id
if forward_enable and forward:
extra += ',fwd=%s' % forward
if ar_enable:
extra += ',ar=%s' % rid
if subscribe_enable and mailinglist_id and form_id:
extra += ',sub=%d:%d' % (mailinglist_id, form_id)
for domain in domains:
line = '%s%s@%s\taccept:%s' % (self.prefix, rid, domain, extra)
agn.log (agn.LV_VERBOSE, 'data', 'Add line: ' + line)
rc += line + '\n'
finally:
i.close ()
finally:
db.close ()
return rc
def collectNewBounces(self): #{{{
agn.log(agn.LV_INFO, 'collect', 'Start collecting new bounces')
cursor = self.db.cursor()
if cursor is None:
raise agn.error(
'collectNewBounces: Failed to get new cursor for collecting')
#
Update = collections.namedtuple(
'Update', ['customer_id', 'company_id', 'mailing_id', 'detail'])
data = {}
query = 'SELECT customer_id, company_id, mailing_id, detail FROM bounce_tbl WHERE %s ORDER BY company_id, customer_id' % self.timestamp.makeBetweenClause(
'timestamp', data)
class Collect(agn.Stream.Collector):
def supplier(self):
self.data = {}
self.uniques = 0
def accumulator(self, supplier, element):
update = Update(*element)
if update.detail >= 400 and update.detail < 520:
key = (update.company_id, update.customer_id)
try:
if update.detail > self.data[key].detail:
self.data[key] = update
except KeyError:
self.data[key] = update
self.uniques += 1
def finisher(self, supplier, count):
return (count, self.uniques, self.data)
(records, uniques, updates) = cursor.stream(query,
data).collect(Collect())
agn.log(
agn.LV_INFO, 'collect',
'Read %s records (%s uniques) and have %s for insert' %
(agn.numfmt(records), agn.numfmt(uniques), agn.numfmt(
len(updates))))
#
inserts = 0
query = self.curs.rselect(
'INSERT INTO bounce_collect_tbl (customer_id, company_id, mailing_id, timestamp) VALUES (:customer, :company, :mailing, %(sysdate)s)'
)
for update in (agn.Stream(updates.itervalues()).sorted()):
cursor.update(
query, {
'customer': update.customer_id,
'company': update.company_id,
'mailing': update.mailing_id
})
inserts += 1
if inserts % 10000 == 0:
cursor.sync()
agn.log(
agn.LV_INFO, 'collect',
'Inserted %s records into bounce_collect_tbl' %
agn.numfmt(inserts))
cursor.sync()
cursor.close()
#
agn.log(
agn.LV_INFO, 'collect',
'Read %d records (%d uniques) and inserted %d' %
(records, uniques, inserts))
companyIDs = []
query = 'SELECT distinct company_id FROM bounce_collect_tbl'
for record in self.curs.query(query):
if record[0] is not None and record[0] > 0:
companyIDs.append(record[0])
agn.log(
agn.LV_INFO, 'collect',
'Remove active receivers from being watched for %d compan%s' %
(len(companyIDs), exty(len(companyIDs))))
allCount = 0
for companyID in companyIDs:
table = 'success_%d_tbl' % companyID
if table in self.tables:
data = {'companyID': companyID}
query = self.curs.qselect (
oracle = 'DELETE FROM bounce_collect_tbl mail WHERE EXISTS (SELECT 1 FROM %s su WHERE ' % table + \
'mail.customer_id = su.customer_id AND mail.company_id = :companyID AND %s)' % self.timestamp.makeBetweenClause ('timestamp', data),
mysql = 'DELETE mail.* FROM bounce_collect_tbl mail, %s su WHERE ' % table + \
'mail.customer_id = su.customer_id AND mail.company_id = :companyID AND %s' % self.timestamp.makeBetweenClause ('su.timestamp', data)
)
count = self.curs.execute(query, data, commit=True)
agn.log(
agn.LV_INFO, 'collect',
'Removed %s active receiver%s for companyID %d' %
(agn.numfmt(count), exts(count), companyID))
allCount += count
else:
agn.log(
agn.LV_INFO, 'collect',
'Skip removing active receivers for companyID %d due to missing table %s'
% (companyID, table))
agn.log(
agn.LV_INFO, 'collect', 'Finished removing %s receiver%s' %
(agn.numfmt(allCount), exts(allCount)))
def mergeNewBounces(self): #{{{
agn.log(agn.LV_INFO, 'merge',
'Start merging new bounces into softbounce_email_tbl')
iquery = self.curs.rselect(
'INSERT INTO softbounce_email_tbl (company_id, email, bnccnt, mailing_id, creation_date, timestamp) VALUES (:company, :email, 1, :mailing, %(sysdate)s, %(sysdate)s)'
)
uquery = self.curs.rselect(
'UPDATE softbounce_email_tbl SET mailing_id = :mailing, timestamp = %(sysdate)s, bnccnt=bnccnt+1 WHERE company_id = :company AND email = :email'
)
icurs = self.db.cursor()
ucurs = self.db.cursor()
squery = 'SELECT count(*) FROM softbounce_email_tbl WHERE company_id = :company AND email = :email'
scurs = self.db.cursor()
dquery = 'DELETE FROM bounce_collect_tbl WHERE company_id = :company'
dcurs = self.db.cursor()
if None in [icurs, ucurs, scurs, dcurs]:
raise agn.error(
'mergeNewBounces: Unable to setup curses for merging')
coll = self.__ccoll(
'SELECT distinct company_id FROM bounce_collect_tbl WHERE company_id IN (SELECT company_id FROM company_tbl WHERE status = \'active\')',
None, None, None)
for company in sorted(coll):
agn.log(agn.LV_INFO, 'merge', 'Working on %d' % company)
query = 'SELECT mt.customer_id, mt.mailing_id, cust.email '
query += 'FROM bounce_collect_tbl mt, customer_%d_tbl cust ' % company
query += 'WHERE cust.customer_id = mt.customer_id '
query += 'AND mt.company_id = %d ' % company
query += 'ORDER BY cust.email, mt.mailing_id'
for record in self.curs.query(query):
(cuid, mid, eml) = record
parm = {
'company': company,
'customer': cuid,
'mailing': mid,
'email': eml
}
data = scurs.querys(squery, parm, cleanup=True)
if not data is None:
if data[0] == 0:
icurs.update(iquery, parm, cleanup=True)
else:
ucurs.update(uquery, parm, cleanup=True)
parm = {'company': company}
dcurs.update(dquery, parm, cleanup=True)
self.db.commit()
icurs.close()
ucurs.close()
scurs.close()
dcurs.close()
agn.log(agn.LV_INFO, 'merge', 'Merging of new bounces done')
agn.log(agn.LV_INFO, 'merge', 'Fade out addresses')
coll = self.__ccoll(
'SELECT distinct company_id FROM softbounce_email_tbl', None,
('fade-out', ), (14, ))
def queryData(key):
if key[0] > 0:
return 'UPDATE softbounce_email_tbl SET bnccnt = bnccnt - 1 WHERE timestamp <= :fade AND bnccnt > 0 AND company_id'
return None
def fillData(data, key):
ts = time.localtime(time.time() - key[0] * 24 * 60 * 60)
data['fade'] = datetime.datetime(ts.tm_year, ts.tm_mon, ts.tm_mday)
self.__cdo(coll, queryData, None, fillData,
'Fade out non bounced watched')
query = 'DELETE FROM softbounce_email_tbl WHERE bnccnt = 0'
self.__do(query, None,
'Remove faded out addresses from softbounce_email_tbl')
agn.log(agn.LV_INFO, 'merge', 'Fade out completed')
except OSError:
pass
try:
os.rename(oldFile, newFile)
except OSError, e:
agn.log(
agn.LV_ERROR, 'data', 'Unable to rename %s to %s %s' %
(oldFile, newFile, ` e.args `))
try:
os.unlink(oldFile)
except OSError, e:
agn.log(
agn.LV_WARNING, 'data',
'Failed to remove temp. file %s %s' %
(oldFile, ` e.args `))
raise agn.error('renameFile')
def updateConfigfile(self, new):
if new != self.last:
temp = configFilename + '.%d' % os.getpid()
try:
fd = open(temp, 'w')
fd.write(new)
fd.close()
self.renameFile(temp, configFilename)
self.last = new
except IOError, e:
agn.log(agn.LV_ERROR, 'data',
'Unable to write %s %s' % (temp, ` e.args `))
raise agn.error('updateConfigfile.open')
def convertToHardbounce(self): #{{{
agn.log(agn.LV_INFO, 'conv',
'Start converting softbounces to hardbounce')
coll = [1]
stats = []
for company in sorted(coll):
cstat = [company, 0, 0]
stats.append(cstat)
agn.log(agn.LV_INFO, 'conv', 'Working on %d' % company)
dquery = 'DELETE FROM softbounce_email_tbl WHERE company_id = %d AND email = :email' % company
dcurs = self.db.cursor()
uquery = self.curs.rselect(
'UPDATE customer_%d_binding_tbl SET user_status = 2, user_remark = \'Softbounce\', exit_mailing_id = :mailing, change_date = %%(sysdate)s WHERE customer_id = :customer AND user_status = 1'
% company)
bquery = self.curs.rselect(
'INSERT INTO bounce_tbl (company_id, customer_id, detail, mailing_id, change_date, dsn) VALUES (%d, :customer, 510, :mailing, %%(sysdate)s, 599)'
% company)
ucurs = self.db.cursor()
squery = 'SELECT email, mailing_id, bnccnt, creation_date, change_date FROM softbounce_email_tbl WHERE company_id = %d AND bnccnt > 7 AND DATEDIFF(change_date,creation_date) > 30' % company
scurs = self.db.cursor()
if None in [dcurs, ucurs, scurs]:
raise agn.error('Failed to setup curses')
lastClick = 30
lastOpen = 30
def toDatetime(offset):
tm = time.localtime(time.time() - offset * 24 * 60 * 60)
return datetime.datetime(tm.tm_year, tm.tm_mon, tm.tm_mday)
lastClickTS = toDatetime(lastClick)
lastOpenTS = toDatetime(lastOpen)
ccount = 0
for record in scurs.query(squery):
parm = {
'email': record[0],
'mailing': record[1],
'bouncecount': record[2],
'creationdate': record[3],
'timestamp': record[4],
'customer': None
}
query = 'SELECT customer_id FROM customer_%d_tbl WHERE email = :email ' % company
data = self.curs.querys(query, parm, cleanup=True)
if data is None:
continue
custs = [
agn.struct(id=_d, click=0, open=0) for _d in data if _d
]
if not custs:
continue
if len(custs) == 1:
cclause = 'customer_id = %d' % custs[0].id
else:
cclause = 'customer_id IN (%s)' % ', '.join(
[str(_c.id) for _c in custs])
parm['ts'] = lastClickTS
query = 'SELECT customer_id, count(*) FROM rdir_log_tbl WHERE %s AND company_id = %d' % (
cclause, company)
query += ' AND change_date > :ts GROUP BY customer_id'
for r in self.curs.queryc(query, parm, cleanup=True):
for c in custs:
if c.id == r[0]:
c.click += r[1]
parm['ts'] = lastOpenTS
query = 'SELECT customer_id, count(*) FROM onepixel_log_tbl WHERE %s AND company_id = %d' % (
cclause, company)
query += ' AND change_date > :ts GROUP BY customer_id'
for r in self.curs.queryc(query, parm, cleanup=True):
for c in custs:
if c.id == r[0]:
c.open += r[1]
for c in custs:
if c.click > 0 or c.open > 0:
cstat[1] += 1
agn.log(
agn.LV_INFO, 'conv',
'Email %s [%d] has %d klick(s) and %d onepix(es) -> active'
% (parm['email'], c.id, c.click, c.open))
else:
cstat[2] += 1
agn.log(
agn.LV_INFO, 'conv',
'Email %s [%d] has no klicks and no onepixes -> hardbounce'
% (parm['email'], c.id))
parm['customer'] = c.id
ucurs.update(uquery, parm, cleanup=True)
ucurs.execute(bquery, parm, cleanup=True)
dcurs.update(dquery, parm, cleanup=True)
ccount += 1
if ccount % 1000 == 0:
agn.log(agn.LV_INFO, 'conv',
'Commiting at %s' % agn.numfmt(ccount))
self.db.commit()
self.db.commit()
scurs.close()
ucurs.close()
dcurs.close()
for cstat in stats:
agn.log(
agn.LV_INFO, 'conv',
'Company %d has %d active and %d marked as hardbounced users' %
tuple(cstat))
agn.log(agn.LV_INFO, 'conv',
'Converting softbounces to hardbounce done')
def readDatabase (self, auto):
rc = []
db = agn.DBaseID ()
if not db:
agn.log (agn.LV_ERROR, 'data', 'Unable to create database connection')
raise agn.error ('readDatabase.open')
try:
i = db.cursor ()
if not i:
agn.log (agn.LV_ERROR, 'data', 'Unable to get database cursor')
raise agn.error ('readDatabase.cursor')
try:
fqdn = socket.getfqdn ().lower ()
company_list = []
newDomains = {}
forwards = []
seen = set ()
acceptedForwards = set ()
ctab = {}
if self.domains:
domain = self.domains[0]
else:
domain = self.fixdomain
seen.add (domain)
rc.append ('fbl@%s\taccept:rid=unsubscribe' % domain)
query = 'SELECT company_id, mailloop_domain FROM company_tbl WHERE status = \'active\''
missing = []
for record in i.query (query):
if record[1]:
ctab[record[0]] = record[1]
if record[1] not in seen:
rc.append ('fbl@%s\talias:fbl@%s' % (record[1], domain))
if record[1] not in self.mtdom and record[1].lower () != fqdn:
newDomains[record[1]] = agn.mutable (rid = 0, domain = record[1])
seen.add (record[1])
else:
missing.append (record[0])
company_list.append (record[0])
if missing:
missing.sort ()
agn.log (agn.LV_VERBOSE, 'data', 'Missing mailloop_domain for %s' % ', '.join ([str (m) for m in missing]))
seen.clear ()
#
query = 'SELECT rid, shortname, company_id, filter_address, forward_enable, forward, ar_enable, ar_sender, ar_subject, ar_text, ar_html, subscribe_enable, mailinglist_id, form_id, timestamp, spam_email, spam_required, spam_forward, autoresponder_mailing_id, security_token FROM mailloop_tbl'
apattern = re.compile ('alias(es)?="([^"]+)"')
dpattern = re.compile ('domains?="([^"]+)"')
for record in i.query (query):
subscribe_enable = None
mailinglist_id = None
form_id = None
(rid, shortname, company_id, filteraddr, forward_enable, forward, ar_enable, ar_sender, ar_subject, ar_text, ar_html, subscribe_enable, mailinglist_id, form_id, timestamp, spam_email, spam_required, spam_forward, autoresponder_mailing_id, security_token) = record
if not company_id in company_list:
continue
if not rid is None:
seen.add (rid)
rid = str (rid)
domains = None
if timestamp is None:
now = time.localtime ()
timestamp = now[5] + now[4] * 100 + now[3] * 10000 + now[2] * 1000000 + now[1] * 100000000 + now[0] * 10000000000
else:
timestamp = timestamp.second + timestamp.minute * 100 + timestamp.hour * 10000 + timestamp.day * 1000000 + timestamp.month * 100000000 + timestamp.year * 10000000000
aliases = None
if not filteraddr is None:
aliases = filteraddr.split ()
if not aliases:
aliases = None
collect_domains = False
if not shortname is None:
mtch = apattern.search (shortname)
if not mtch is None:
tempaliases = (mtch.groups ()[1]).split ()
if aliases is None:
aliases = tempaliases
else:
aliases += tempaliases
mtch = dpattern.search (shortname)
if not mtch is None:
domains = (mtch.groups ()[0]).split ()
for domain in domains:
if not self.mtdom.has_key (domain) and not domain in newDomains:
newDomains[domain] = agn.mutable (rid = rid, domain = domain)
else:
collect_domains = True
if not aliases is None:
for alias in aliases:
if not alias.startswith (self.prefix):
parts = alias.split ('@')
if len (parts) == 2:
if collect_domains:
if domains is None:
domains = [parts[1]]
else:
domains.append (parts[1])
if not self.mtdom.has_key (parts[1]) and not parts[1] in newDomains:
newDomains[parts[1]] = agn.mutable (rid = rid, domain = parts[1])
if autoresponder_mailing_id:
try:
autoresponder_mailing_id = int (autoresponder_mailing_id)
if not security_token:
agn.log (agn.LV_ERROR, 'data', '%s: Autoresponder has mailing id, but no security token' % rid)
autoresponder_mailing_id = None
except ValueError, e:
agn.log (agn.LV_ERROR, 'data', '%s: Failed to parse autoresponder_mailing_id %r: %s' % (rid, autoresponder_mailing_id, str (e)))
autoresponder_mailing_id = None
if ar_enable and not ar_text and not autoresponder_mailing_id:
ar_enable = False
if ar_enable:
def nvl (s):
if s is not None and type (s) not in (str, unicode):
return str (s)
return s
auto.append (Autoresponder (rid, timestamp, ar_sender, ar_subject, nvl (ar_text), nvl (ar_html), autoresponder_mailing_id, security_token))
if domains is None:
try:
cdomain = ctab[company_id]
if self.domains and cdomain != self.domains[0]:
domains = [self.domains[0]]
else:
domains = []
if not self.domains or cdomain in self.domains:
domains.append (cdomain)
else:
agn.log (agn.LV_ERROR, 'data', 'Companys domain "%s" not found in mailertable' % cdomain)
except KeyError:
agn.log (agn.LV_DEBUG, 'data', 'No domain for company found, further processing')
if domains is None:
domains = self.domains
elif self.domains and self.domains[0] not in domains:
domains.insert (0, self.domains[0])
extra = 'rid=%s' % rid
if company_id:
extra += ',cid=%d' % company_id
if forward_enable and forward:
extra += ',fwd=%s' % forward
forwards.append (agn.mutable (rid = rid, address = forward))
if spam_email:
extra += ',spam_email=%s' % spam_email
if spam_forward is not None:
extra += ',spam_fwd=%d' % spam_forward
if spam_required is not None:
extra += ',spam_req=%d' % spam_required
if ar_enable:
extra += ',ar=%s' % rid
if autoresponder_mailing_id:
extra += ',armid=%d' % autoresponder_mailing_id
if subscribe_enable and mailinglist_id and form_id:
extra += ',sub=%d:%d' % (mailinglist_id, form_id)
for domain in domains:
line = '%s%s@%s\taccept:%s' % (self.prefix, rid, domain, extra)
agn.log (agn.LV_VERBOSE, 'data', 'Add line: ' + line)
rc.append (line)
if aliases and domains:
for alias in aliases:
rc.append ('%s\talias:%s%s@%s' % (alias, self.prefix, rid, domains[0]))
acceptedForwards.add (alias)
if seen:
rules = {}
query = 'SELECT rid, section, pattern FROM mailloop_rule_tbl'
for (rid, section, pattern) in i.query (query):
if rid in seen:
try:
rule = rules[rid]
except KeyError:
rule = {}
rules[rid] = rule
try:
sect = rule[section]
except KeyError:
sect = []
rule[section] = sect
sect.append (pattern)
self.updateRules (rules)
for forward in forwards:
parts = forward.address.split ('@')
if len (parts) == 2:
fdomain = parts[1].lower ()
for domain in self.mtdom:
if domain == fdomain and forward not in acceptedForwards:
agn.log (agn.LV_WARNING, 'db', '%s: using address "%s" with local handled domain "%s"' % (forward.rid, forward.address, domain))
refuse = []
for domain in newDomains:
if domain == fdomain:
nd = newDomains[domain]
agn.log (agn.LV_WARNING, 'db', '%s: try to add new domain for already existing forward address "%s" in %s, refused' % (nd.rid, forward.address, forward.rid))
refuse.append (domain)
for domain in refuse:
del newDomains[domain]
if newDomains:
if agn.sDNS is not None:
try:
dns = agn.sDNS ()
laddrs = dns.getAllAddresses (fqdn)
for domain in newDomains:
checks = dns.getMailexchanger (domain)
if not checks:
checks = [domain]
if checks:
for check in checks:
caddrs = dns.getAllAddresses (check)
for caddr in caddrs:
if caddr not in laddrs:
agn.log (agn.LV_ERROR, 'db', '%s: checked address "%s" for "%s" does not point to local machines address %s' % (domain, caddr, check, ', '.join (laddrs)))
else:
agn.log (agn.LV_ERROR, 'db', '%s: failed to resolve' % domain)
except Exception, e:
agn.log (agn.LV_ERROR, 'db', 'Failed to verify new domains: %s' % str (e))
if self.mta.mta == 'sendmail':
chandler = signal.signal (signal.SIGUSR1, signal.SIG_IGN)
cmd = [self.controlSendmail, 'add']
for domain in newDomains:
cmd.append (domain)
agn.log (agn.LV_INFO, 'db', 'Found new domains, add them using ' + `cmd`)
subprocess.call (cmd)
agn.log (agn.LV_INFO, 'db', 'Restarting sendmail due to domain update')
subprocess.call ([self.restartSendmail])
signal.signal (signal.SIGUSR1, chandler)
self.readMailertable (newDomains)
finally:
i.close ()
except agn.error, e:
agn.log (agn.LV_ERROR, 'kill', 'Failed to remove old entries using %s: %s (%s)' % (query, e.msg, db.lastError ()))
#
#
# 2.) Collect bounces to mailtrack_tbl
query = '*unset collect*'
try:
query = 'UPDATE timestamp_tbl SET prev = cur, temp = current_timestamp WHERE timestamp_id = 2'
rows = curs.update (query)
if rows == 0:
agn.log (agn.LV_INFO, 'collect', 'Missing entry in timestamp_tbl, try to create one')
query = 'INSERT INTO timestamp_tbl (timestamp_id, description, cur, prev, temp) VALUES (2, \'Softbounce collection marker\', now(), \'1980-01-01\', now())'
rows = curs.update (query)
if rows != 1:
raise agn.error ('Failed to set timestamp using %s' % query)
db.commit ()
agn.log (agn.LV_INFO, 'collect', 'Updated timestamps')
time.sleep (1)
iquery = 'INSERT INTO %s (customer_id, company_id, mailing_id, status_id, change_date) ' % mailtrackTable
iquery += 'VALUES (:customer, :company, :mailing, 90, now())'
insert = db.cursor ()
if insert is None:
raise agn.error ('Failed to get new cursor for insertion')
bquery = db.cursor ()
if bquery is None:
raise agn.error ('Failed to get new cursor for bounce query')
query = 'SELECT customer_id, company_id, mailing_id, detail '
query += 'FROM bounce_tbl '
def main():
help = None
id = agn.user
configFilename = None
configSource = None
options = []
try:
(opts, param) = getopt.getopt(sys.argv[1:], 'vh?i:c:C:o:')
for opt in opts:
if opt[0] == '-v':
agn.loglevel = agn.LV_DEBUG
agn.outlevel = agn.LV_DEBUG
agn.outstream = sys.stderr
elif opt[0] in ('-h', '-?'):
help = ''
elif opt[0] == '-i':
id = opt[1]
elif opt[0] == '-c':
configFilename = opt[1]
elif opt[0] == '-C':
configSource = opt[1]
elif opt[0] == '-o':
parts = opt[1].split('=', 1)
if len(parts) != 2:
help = 'Option -o requires <var>=<val> expression, not "%s"' % opt[
1]
else:
options.append(parts)
except getopt.GetoptError as e:
help = str(e)
try:
try:
cur = os.getcwd()
except OSError:
cur = None
if agn.base and agn.base != '.' and agn.base != cur:
os.chdir(agn.base)
except OSError as e:
raise agn.error('Cannot change to home directory %s: %s' %
(agn.base, str(e)))
cfg = eagn.Config()
cfg.setupNamespace(id=id)
cfg.enableSubstitution()
if os.path.isfile(cfg.filename()):
cfg.read()
if configSource is not None:
cfg.read(filedesc=eagn.StringIO.StringIO(configSource))
elif configFilename is not None:
cfg.read(configFilename)
if help is not None:
print(cfg.tget('help', 'no help available', id=id, cfg=cfg))
if help:
print('\n%s' % help)
else:
for (var, val) in options:
cfg[var] = val
svc = Service(cfg, id)
if svc.sanityCheck():
svcfdName = 'SVCFD'
fd = None
stderr = sys.stderr.fileno()
if stderr is not None:
try:
fd = os.dup(stderr)
os.environ[svcfdName] = str(fd)
except OSError as e:
agn.log(agn.LV_WARNING, 'main',
'Failed to setup reporting FD: %s' % str(e))
svc.execute(param)
if fd is not None:
os.close(fd)
del os.environ[svcfdName]
sys.exit(svc.ec)
def addHandler(cls, path, handler):
if path not in cls.getHandler:
cls.getHandler[path] = handler
else:
raise agn.error('path "%s" already handled' % path)
def readDatabase(self, auto):
rc = ''
db = agn.DBase()
if not db:
agn.log(agn.LV_ERROR, 'data',
'Unable to create database connection')
raise agn.error('readDatabase.open')
try:
i = db.cursor()
if not i:
agn.log(agn.LV_ERROR, 'data', 'Unable to get database cursor')
raise agn.error('readDatabase.cursor')
try:
ctab = {}
query = 'SELECT company_id, mailloop_domain FROM company_tbl WHERE status = \'active\''
missing = []
for record in i.query(query):
if record[1]:
ctab[record[0]] = record[1]
else:
missing.append(record[0])
if missing:
missing.sort()
agn.log(
agn.LV_VERBOSE, 'data',
'Missing mailloop_domain for %s' %
', '.join([str(m) for m in missing]))
query = 'SELECT rid, shortname, company_id, forward_enable, forward, ar_enable, ar_sender, ar_subject, ar_text, ar_html, subscribe_enable, mailinglist_id, form_id, date_format(change_date,\'%Y%m%d%H%i%S\') FROM mailloop_tbl'
for record in i.query(query):
subscribe_enable = None
mailinglist_id = None
form_id = None
(rid, shortname, company_id, forward_enable, forward,
ar_enable, ar_sender, ar_subject, ar_text, ar_html,
subscribe_enable, mailinglist_id, form_id,
timestamp) = record
if not rid is None:
rid = str(rid)
domains = None
if timestamp is None:
timestamp = time.time()
else:
timestamp = int(timestamp)
if ar_enable and not ar_text:
ar_enable = False
if ar_enable:
def nvl(s):
if s is not None:
return str(s)
return s
auto.append(
Autoresponder(rid, timestamp, ar_sender,
ar_subject, nvl(ar_text),
nvl(ar_html)))
if domains is None:
try:
cdomain = ctab[company_id]
if self.domains and cdomain != self.domains[0]:
domains = [self.domains[0]]
else:
domains = []
if agn.iswin and not cdomain in self.domains:
self.domains.append(cdomain)
if not self.domains or cdomain in self.domains:
domains.append(cdomain)
else:
agn.log(agn.LV_WARNING, 'data',
'Domain "%s" not known' % cdomain)
except KeyError:
agn.log(
agn.LV_DEBUG, 'data',
'No domain for company found, further processing'
)
if domains is None:
domains = self.domains
elif not self.domains[0] in domains:
domains.insert(0, self.domains[0])
extra = 'rid=%s' % rid
if company_id:
extra += ',cid=%d' % company_id
if forward_enable and forward:
extra += ',fwd=%s' % forward
if ar_enable:
extra += ',ar=%s' % rid
if subscribe_enable and mailinglist_id and form_id:
extra += ',sub=%d:%d' % (mailinglist_id, form_id)
for domain in domains:
line = '%s%s@%s\taccept:%s' % (self.prefix, rid,
domain, extra)
agn.log(agn.LV_VERBOSE, 'data',
'Add line: ' + line)
rc += line + '\n'
finally:
i.close()
finally:
db.close()
return rc
def renameFile (self, oldFile, newFile):
if agn.iswin:
try:
os.unlink (newFile)
except OSError:
pass
try:
os.rename (oldFile, newFile)
except OSError, e:
agn.log (agn.LV_ERROR, 'data', 'Unable to rename %s to %s %s' % (oldFile, newFile, `e.args`))
try:
os.unlink (oldFile)
except OSError, e:
agn.log (agn.LV_WARNING, 'data', 'Failed to remove temp. file %s %s' % (oldFile, `e.args`))
raise agn.error ('renameFile')
def updateConfigfile (self, new):
if new != self.last:
temp = configFilename + '.%d' % os.getpid ()
try:
fd = open (temp, 'w')
fd.write (new)
fd.close ()
self.renameFile (temp, configFilename)
self.last = new
except IOError, e:
agn.log (agn.LV_ERROR, 'data', 'Unable to write %s %s' % (temp, `e.args`))
raise agn.error ('updateConfigfile.open')
def writeUpdateLog (self, text):