def import_accounts(self, domainid):
"import accounts"
try:
cachekey = u'domain-%s' % domainid
domain = Session.query(Domain.id, Domain.name)\
.filter(Domain.id == domainid)\
.options(FromCache('sql_cache_med', cachekey)).one()
except NoResultFound:
abort(404)
c.form = ImportCSVForm(request.POST, csrf_context=session)
if request.method == 'POST' and c.form.validate():
basedir = config['pylons.cache_dir']
csvdata = request.POST['csvfile']
if hasattr(csvdata, 'filename'):
dstfile = os.path.join(basedir, 'uploads',
csvdata.filename.lstrip(os.sep))
if not os.path.exists(dstfile) and iscsv(csvdata.file):
csvfile = open(dstfile, 'w')
shutil.copyfileobj(csvdata.file, csvfile)
csvdata.file.close()
csvfile.close()
task = importaccounts.apply_async(args=[
domainid, dstfile, c.form.skipfirst.data, c.user.id
])
session['taskids'].append(task.task_id)
session['acimport-count'] = 1
session['acimport-file'] = dstfile
session.save()
msg = _('File uploaded, and is being processed, this page'
' will automatically refresh to show the status')
flash(msg)
log.info(msg)
redirect(url('accounts-import-status',
taskid=task.task_id))
else:
filename = csvdata.filename.lstrip(os.sep)
if not iscsv(csvdata.file):
msg = _('The file: %s is not a CSV file') % filename
flash_alert(msg)
log.info(msg)
else:
msg = _('The file: %s already exists and'
' is being processed.') % filename
flash_alert(msg)
log.info(msg)
csvdata.file.close()
else:
msg = _('No CSV was file uploaded, try again')
flash_alert(msg)
log.info(msg)
c.domain = domain
return self.render('/accounts/importaccounts.html')
def _get_authserver(self, authid):
"Get an auth server"
try:
cachekey = u'authserver-%s' % authid
q = Session.query(AuthServer).filter(AuthServer.id==authid)\
.options(FromCache('sql_cache_med', cachekey))
if self.invalidate:
q.invalidate()
server = q.one()
except NoResultFound:
server = None
return server
def _get_alias(self, aliasid):
"Get a domain alias"
try:
cachekey = u'domainalias-%s' % aliasid
q = Session.query(DomainAlias).filter(DomainAlias.id==aliasid)\
.options(FromCache('sql_cache_med', cachekey))
if self.invalidate:
q.invalidate()
alias = q.one()
except NoResultFound:
alias = None
return alias
def _get_filter(self, filterid):
"utility to return filter object"
try:
cachekey = u'filter-%s' % filterid
q = Session.query(SavedFilter).filter(SavedFilter.id==filterid)\
.options(FromCache('sql_cache_short', cachekey))
if self.invalidate:
q.invalidate()
savedfilter = q.one()
except NoResultFound:
savedfilter = None
return savedfilter
def _get_server(self, destinationid):
"utility"
try:
cachekey = u'deliveryserver-%s' % destinationid
q = Session.query(DeliveryServer)\
.filter(DeliveryServer.id==destinationid)\
.options(FromCache('sql_cache_med', cachekey))
if self.invalidate:
q.invalidate()
server = q.one()
except NoResultFound:
server = None
return server
def _get_user(self, userid):
"utility to return user"
try:
cachekey = 'user-%s' % userid
qry = Session.query(User).filter(User.id == userid)\
.options(joinedload('addresses'))\
.options(FromCache('sql_cache_med', cachekey))
if self.invalidate:
qry.invalidate()
user = qry.one()
except NoResultFound:
user = None
return user
def get(self, direction=1, hostname=None):
"return mailq"
cachekey = 'mq-%s-%s-%s' % (self.user.username, str(hostname),
str(direction))
if hostname:
query = self.query.filter(MailQueueItem.hostname == hostname)
else:
query = self.query
query = query.filter(MailQueueItem.direction == direction)
try:
query = query.options(FromCache('sql_cache_short', cachekey))
except ValueError:
pass
return query.one()
def _get_domain(self, domainid):
"utility to return domain"
try:
cachekey = 'domain-%s' % domainid
qry = Session.query(Domain).filter(Domain.id == domainid)\
.options(joinedload_all(Domain.servers),
joinedload_all(Domain.aliases),
joinedload_all(Domain.authservers))\
.options(FromCache('sql_cache_med', cachekey))
if self.invalidate:
qry.invalidate()
domain = qry.one()
except NoResultFound:
domain = None
return domain
def gen_rules(rule):
"map to generate rules"
rule = rule.strip()
match = SARULE_RE.match(rule)
description = ""
if match:
rule = match.groups()[1]
rule_obj = Session.query(SARule)\
.options(FromCache('sql_cache_long', rule))\
.get(rule)
if rule_obj:
description = rule_obj.description
return dict(rule=rule,
score=match.groups()[3],
description=description)
def _get_message(self, messageid):
"utility to return message"
try:
cachekey = u"msgid-%s" % messageid
q = Session.query(Message).filter(Message.id == messageid)
if not c.user.is_superadmin:
uquery = UserFilter(Session, c.user, q)
q = uquery.filter()
q = q.options(FromCache('sql_cache_long', cachekey))
if self.invalidate:
q.invalidate()
message = q.one()
except (NoResultFound, MultipleResultsFound):
message = None
return message
def _get_archive(self, messageid):
"utility to return message"
try:
cachekey = u"archiveid-%s" % messageid
q = Session.query(Archive).filter(Archive.id == messageid)
if c.user.account_type != 1:
uquery = UserFilter(Session, c.user, q)
q = uquery.filter()
q = q.options(FromCache('sql_cache_long', cachekey))
if self.invalidate:
q.invalidate()
message = q.one()
except (NoResultFound, MultipleResultsFound):
message = None
return message
def get(self, hostname=None):
if not hostname is None:
self.query = self.query.filter(Message.hostname == hostname)
if self.user.is_domain_admin:
dquery = self.dbsession.query(Domain.name).join(downs,
(oa, downs.c.organization_id == oa.c.organization_id))\
.filter(Domain.status == True)\
.filter(oa.c.user_id == self.user.id).all()
self.query = self.query.filter(
func._(
or_(Message.to_domain.in_(dquery),
Message.from_domain.in_(dquery))))
if self.user.is_peleb:
addrs = [addr.address for addr in self.user.addresses]
addrs.append(self.user.email)
self.query = self.query.filter(
func._(
or_(Message.to_address.in_(addrs),
Message.from_address.in_(addrs))))
cachekey = 'dailytotals-%s-%s' % (self.user.username, hostname)
self.query = self.query.options(FromCache('sql_cache_short', cachekey))
return self.query.one()
def display(self, reportid, format=None):
"Display a report"
c.report_title = REPORTS[reportid]['title']
filters = session.get('filter_by', [])
if reportid in ['1', '2', '3', '4', '5', '6', '7', '8', '10']:
rquery = ReportQuery(c.user, reportid, filters)
query = rquery()
cachekey = u'reportquery-%s-%s' % (c.user.username, reportid)
query = query.options(FromCache('sql_cache_short', cachekey))
data = query[:10]
if format == 'png':
return self._generate_png(data, reportid)
if format == 'csv':
info = REPORTDL_MSG % dict(r=c.report_title, f='csv')
audit_log(c.user.username, 1, info, request.host,
request.remote_addr, datetime.now())
return self._generate_csv(data, reportid)
jsondata = [
dict(tooltip=getattr(item, 'address'),
y=getattr(item, REPORTS[reportid]['sort']),
stroke='black',
color=PIE_COLORS[index],
size=getattr(item, 'size'))
for index, item in enumerate(data)
]
template = '/reports/piereport.html'
if reportid == '9':
query = sa_scores(Session, c.user)
if filters:
dynq = DynaQuery(Message, query, filters)
query = dynq.generate()
cachekey = u'sascores-%s' % c.user.username
query = query.options(FromCache('sql_cache_short', cachekey))
data = query.all()
if format == 'json':
scores = []
counts = []
for row in data:
scores.append(
dict(value=int(row.score), text=str(row.score)))
counts.append(
dict(y=int(row.count),
tooltip=(_('Score ') + str(row.score) + ': ' +
str(row.count))))
jsondata = dict(scores=scores, count=counts)
elif format == 'png':
return self._generate_png(data, reportid)
else:
jsondata = {}
jsondata['labels'] = [{
'value': index + 1,
'text': str(item.score)
} for index, item in enumerate(data)]
jsondata['scores'] = [item.count for item in data]
template = '/reports/barreport.html'
if reportid == '10':
if format == 'json':
data = [[
item.address.strip(),
get_hostname(item.address.strip()),
country_flag(item.address.strip()), item.count,
format_byte_size(item.size)
] for item in data]
template = '/reports/relays.html'
if reportid == '11':
query = message_totals(Session, c.user)
if filters:
dynq = DynaQuery(Message, query, filters)
query = dynq.generate()
cachekey = u'msgtotals-%s' % c.user.username
query = query.options(FromCache('sql_cache_short', cachekey))
data = query.all()
if format == 'png':
return self._generate_png(data, reportid)
elif format == 'json':
dates = []
mail_total = []
spam_total = []
size_total = []
virus_total = []
for row in data:
dates.append(str(row.date))
mail_total.append(int(row.mail_total))
spam_total.append(int(row.spam_total))
virus_total.append(int(row.virus_total))
size_total.append(int(row.total_size))
jsondata = dict(
dates=[
dict(value=index + 1, text=date)
for index, date in enumerate(dates)
],
mail=[
dict(y=total,
tooltip=(_('Mail totals on ') + dates[index] +
': ' + str(total)))
for index, total in enumerate(mail_total)
],
spam=[
dict(y=total,
tooltip=(_('Spam totals on ') + dates[index] +
': ' + str(total)))
for index, total in enumerate(spam_total)
],
virii=[
dict(y=total,
tooltip=(_('Virus totals on ') + dates[index] +
': ' + str(total)))
for index, total in enumerate(virus_total)
],
volume=size_total,
mail_total=sum(mail_total),
spam_total=sum(spam_total),
virus_total=sum(virus_total),
volume_total=sum(size_total))
try:
vpct = "%.1f" % (
(1.0 * sum(virus_total) / sum(mail_total)) * 100)
spct = "%.1f" % (
(1.0 * sum(spam_total) / sum(mail_total)) * 100)
except ZeroDivisionError:
vpct = "0.0"
spct = "0.0"
jsondata['vpct'] = vpct
jsondata['spct'] = spct
data = [
dict(date=str(row.date),
mail_total=row.mail_total,
spam_total=row.spam_total,
virus_total=row.virus_total,
size_total=format_byte_size(row.total_size),
virus_percent="%.1f" %
((1.0 * int(row.virus_total) / int(row.mail_total)) *
100),
spam_percent="%.1f" %
((1.0 * int(row.spam_total) / int(row.mail_total)) *
100)) for row in data
]
elif format == 'csv':
info = REPORTDL_MSG % dict(r=c.report_title, f='csv')
audit_log(c.user.username, 1, info, request.host,
request.remote_addr, datetime.now())
return self._generate_csv(data, reportid)
else:
jsondata = dict(mail=[],
spam=[],
virus=[],
volume=[],
labels=[])
for index, item in enumerate(data):
jsondata['spam'].append(item.spam_total)
jsondata['mail'].append(item.mail_total)
jsondata['virus'].append(item.virus_total)
jsondata['volume'].append(item.total_size)
jsondata['labels'].append(
dict(text=str(item.date), value=index))
template = '/reports/listing.html'
if format == 'json':
response.headers['Content-Type'] = JSON_HEADER
return json.dumps(dict(items=list(data), pie_data=jsondata))
if format == 'pdf' and reportid != '9':
info = REPORTDL_MSG % dict(r=c.report_title, f='pdf')
audit_log(c.user.username, 1, info, request.host,
request.remote_addr, datetime.now())
return self._generate_pdf(data, reportid)
c.reportid = reportid
c.chart_data = json.dumps(jsondata)
c.top_items = data
c.active_filters = filters
c.saved_filters = []
c.FILTER_BY = FILTER_BY
c.FILTER_ITEMS = FILTER_ITEMS
c.form = FilterForm(request.POST, csrf_context=session)
info = REPORTVIEW_MSG % dict(r=c.report_title)
audit_log(c.user.username, 1, info, request.host, request.remote_addr,
datetime.now())
return render(template)
def _get_data(self, format=None, success=None, errors=None):
"Get report data"
filters = session.get('filter_by', [])
query = Session.query(
func.max(Message.timestamp).label('oldest'),
func.min(Message.timestamp).label('newest'))
uquery = UserFilter(Session, c.user, query)
query = uquery.filter()
# count = self._get_count()
countq = MsgCount(Session, c.user)
count = countq()
cachekey = u'savedfilters-%s' % c.user.username
sfq = Session.query(SavedFilter)\
.filter(SavedFilter.user == c.user)\
.options(FromCache('sql_cache_short', cachekey))
if self.invalidate:
sfq.invalidate()
savedfilters = sfq.all()
if filters:
dynq = DynaQuery(Message, query, filters)
query = dynq.generate()
dcountq = Session.query(func.count(Message.id).label('count'))
dcountqi = UserFilter(Session, c.user, dcountq)
dcountq = dcountqi.filter()
dyncq = DynaQuery(Message, dcountq, filters)
dcountq = dyncq.generate()
dcount = dcountq.one()
count = dcount.count
cachekey = u'report-aggregates-%s' % c.user.username
query = query.options(FromCache('sql_cache_short', cachekey))
if self.invalidate:
query.invalidate()
data = query.all()
saved_filters = [
processfilters(filt, filters) for filt in savedfilters
]
if format is None:
return data, count, filters, saved_filters
else:
if format == 'json':
data = data[0]
filterdict = dict(FILTER_ITEMS)
filterbydict = dict(FILTER_BY)
active_filters = [
dict(filter_field=filterdict[filt['field']],
filter_by=filterbydict[filt['filter']],
filter_value=filt['value']) for filt in filters
]
try:
newest = data.newest.strftime("%Y-%m-%d %H:%M")
oldest = data.oldest.strftime("%Y-%m-%d %H:%M")
except AttributeError:
newest = ''
oldest = ''
datadict = dict(count=count, newest=newest, oldest=oldest)
jsondata = dict(success=success,
data=datadict,
errors=errors,
active_filters=active_filters,
saved_filters=saved_filters)
return jsondata
def get(self, hostname=None):
"Return the query object"
if hostname is not None:
self.query = self.query.filter(Message.hostname == hostname)
if self.user.is_domain_admin:
domainquery = self.dbsession.query(Domain).join(downs,
(oa, downs.c.organization_id == oa.c.organization_id))\
.filter(Domain.status == true())\
.filter(oa.c.user_id == self.user.id).all()
dquery = []
for domain in domainquery:
dquery.append(domain.name)
if domain.aliases:
for domain_alias in domain.aliases:
dquery.append(domain_alias.name)
if not dquery:
dquery.append('xx')
self.query = self.query.filter(
func._(
or_(Message.to_domain.in_(dquery),
Message.from_domain.in_(dquery))))
if self.user.is_peleb:
addrs = [
addr.address for addr in self.user.addresses
if '+*' not in addr.address and '-*' not in addr.address
]
addrs.append(self.user.email)
tagged_addrs = [
addr.address for addr in self.user.addresses
if '+*' in addr.address or '-*' in addr.address
]
if tagged_addrs:
tagged_to = func._(
or_(*[
Message.to_address.like(
TAGGED_RE.sub(r'\g<one>%', taddr))
for taddr in tagged_addrs
]))
tagged_from = func._(
or_(*[
Message.from_address.like(
TAGGED_RE.sub(r'\g<one>%', taddr))
for taddr in tagged_addrs
]))
self.query = self.query.filter(
func._(
or_(tagged_to, tagged_from,
Message.to_address.in_(addrs),
Message.from_address.in_(addrs))))
else:
self.query = self.query.filter(
func._(
or_(Message.to_address.in_(addrs),
Message.from_address.in_(addrs))))
cachekey = 'dailytotals-%s-%s' % (self.user.username, hostname)
try:
self.query = self.query.\
options(FromCache('sql_cache_short', cachekey))
except PylibmcError:
pass
return self.query.one()
