def __mail (self, mailingID): #{{{
try:
rc = self.mailingInfo[mailingID]
except KeyError:
r = self.cursor.querys ('SELECT company_id, shortname, deleted FROM mailing_tbl WHERE mailing_id = :mid', {'mid': mailingID})
if r is not None:
rc = agn.struct (companyID = r[0], name = self.__toiso (r[1]), deleted = (r[2] != 0))
else:
rc = agn.struct (companyID = 0, name = '#%d not found' % mailingID, deleted = False)
self.mailingInfo[mailingID] = rc
return rc
def readData (self): #{{{
fname = os.path.sep.join ([agn.base, 'conf', 'upgrade', 'upgrade.template'])
try:
fd = open (fname)
content = fd.read ()
fd.close ()
except IOError:
content = ''
Request.template = agn.Template (content)
try:
path = os.path.sep.join ([agn.base, 'conf', 'upgrade'])
for (fpath, fname) in [(os.path.sep.join ([path, _f]), _f) for _f in os.listdir (path) if not _f.startswith ('.') and _f != 'upgrade.template']:
try:
fd = open (fpath)
content = fd.read ()
fd.close ()
page = agn.struct ()
page.content = content
page.mtype = mimetypes.guess_type (fname)[0]
if not page.mtype:
page.mtype = 'text/html'
Request.pages[fname] = page
except IOError:
pass
except OSError:
pass
def readData (self): #{{{
fname = os.path.sep.join ([agn.base, 'conf', 'upgrade', 'upgrade.template'])
try:
fd = open (fname)
content = fd.read ()
fd.close ()
except IOError:
content = ''
Request.template = agn.Template (content)
try:
path = os.path.sep.join ([agn.base, 'conf', 'upgrade'])
for (fpath, fname) in [(os.path.sep.join ([path, _f]), _f) for _f in os.listdir (path) if not _f.startswith ('.') and _f != 'upgrade.template']:
try:
fd = open (fpath)
content = fd.read ()
fd.close ()
page = agn.struct ()
page.content = content
page.mtype = mimetypes.guess_type (fname)[0]
if not page.mtype:
page.mtype = 'text/html'
Request.pages[fname] = page
except IOError:
pass
except OSError:
pass
def do_GET (self): #{{{
global datafile, term
if Request.template is None:
self.readData ()
path = self.path
n = path.find ('?')
if n != -1:
query = cgi.parse_qs (path[n + 1:], True)
path = path[:n]
else:
query = None
if path == '/':
try:
fd = open (datafile)
data = fd.read ()
fd.close ()
except IOError:
data = ''
report = []
done = False
status = 'unset'
for line in data.split ('\n'):
if line:
cmd = line[0]
line = line[1:]
if cmd in ('.', '!', '>'):
r = agn.struct ()
r.id = cmd
if line:
r.text = line
else:
r.text = ' '
report.append (r)
elif cmd == 'X':
done = True
status = line
vrs = { 'report': report,
'done': int (done),
'status': status
}
self.answer (200, vrs)
if done:
signal.alarm (30)
else:
try:
page = Request.pages[os.path.basename (path)]
self.answerStatic (page.mtype, page.content)
except KeyError:
self.answer (404, None)
def find(key, defaultValue):
rc = agn.struct(path=None,
content=[],
modified=False,
hash=None)
try:
for element in pc.getlist(key):
try:
(hash, path) = element.split(':', 1)
except ValueError:
hash = None
path = element
if path.startswith(agn.base):
if rc.path is None:
rc.path = path
rc.hash = hash
if not os.path.isfile(path):
agn.createPath(os.path.dirname(path))
open(path, 'w').close()
if hash is not None:
make(rc)
if rc.path is not None:
try:
fd = open(rc.path)
for line in (_l.strip() for _l in fd):
try:
(var, val) = [
_v.strip()
for _v in line.split(None, 1)
]
except ValueError:
var = line
val = defaultValue
rc.modified = True
if var not in [_c[0] for _c in rc.content]:
rc.content.append((var, val))
else:
rc.modified = True
fd.close()
agn.log(
agn.LV_VERBOSE, 'data',
'Read %d lines from %s' %
(len(rc.content), rc.path))
except OSError, e:
agn.log(
agn.LV_ERROR, 'data',
'Failed to read %s: %s' % (rc.path, str(e)))
else:
def do_GET (self): #{{{
global datafile, term
if Request.template is None:
self.readData ()
path = self.path
n = path.find ('?')
if n != -1:
query = cgi.parse_qs (path[n + 1:], True)
path = path[:n]
else:
query = None
if path == '/':
try:
fd = open (datafile)
data = fd.read ()
fd.close ()
except IOError:
data = ''
report = []
done = False
status = 'unset'
for line in data.split ('\n'):
if line:
cmd = line[0]
line = line[1:]
if cmd in ('.', '!', '>'):
r = agn.struct ()
r.id = cmd
r.text = line
report.append (r)
elif cmd == 'X':
done = True
status = line
vrs = { 'report': report,
'done': int (done),
'status': status
}
self.answer (200, vrs)
if done:
signal.alarm (30)
else:
try:
page = Request.pages[os.path.basename (path)]
self.answerStatic (page.mtype, page.content)
except KeyError:
self.answer (404, None)
def find (key, defaultValue):
rc = agn.struct (path = None, content = [], modified = False, hash = None)
try:
for element in pc.getlist (key):
try:
(hash, path) = element.split (':', 1)
except ValueError:
hash = None
path = element
if path.startswith (agn.base):
if rc.path is None:
rc.path = path
rc.hash = hash
if not os.path.isfile (path):
agn.createPath (os.path.dirname (path))
open (path, 'w').close ()
if hash is not None:
make (rc)
if rc.path is not None:
try:
fd = open (rc.path)
for line in (_l.strip () for _l in fd):
try:
(var, val) = [_v.strip () for _v in line.split (None, 1)]
except ValueError:
var = line
val = defaultValue
rc.modified = True
if var not in [_c[0] for _c in rc.content]:
rc.content.append ((var, val))
else:
rc.modified = True
fd.close ()
agn.log (agn.LV_VERBOSE, 'data', 'Read %d lines from %s' % (len (rc.content), rc.path))
except OSError, e:
agn.log (agn.LV_ERROR, 'data', 'Failed to read %s: %s' % (rc.path, str (e)))
else:
raise agn.error ('Failed to setup curses')
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 = 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])
old = time.localtime (time.time () - 30 * 24 * 60 * 60)
query = 'SELECT customer_id, count(*) FROM rdir_log_tbl WHERE %s AND company_id = %d' % (cclause, company)
query += ' AND change_date > \'%04d-%02d-%02d\'' % (old[0], old[1], old[2])
query += ' GROUP BY customer_id'
for r in curs.queryc (query, parm, cleanup = True):
for c in custs:
if c.id == r[0]:
c.click += r[1]
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')
raise agn.error('Failed to setup curses')
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 = 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])
old = time.localtime(time.time() - 30 * 24 * 60 * 60)
query = 'SELECT customer_id, count(*) FROM rdir_log_tbl WHERE %s AND company_id = %d' % (
cclause, company)
query += ' AND change_date > \'%04d-%02d-%02d\'' % (
old[0], old[1], old[2])
query += ' GROUP BY customer_id'
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')
