def all(self, user, filters_list=None, addrs=None, act=3):
"spam scores"
conn = connection.cursor()
query = """
SELECT round(sascore) AS score, count(*) AS count FROM messages
"""
if filters_list:
sub = gen_dynamic_raw_query(filters_list)
if user.is_superuser:
conn.execute(query + " WHERE " + sub[0] +
" AND whitelisted=0 AND scaned = 1 GROUP" +
" BY score ORDER BY score", sub[1])
else:
sql = raw_user_filter(user, addrs, act)
conn.execute(query + " WHERE " + sql + " AND " + sub[0] +
" AND whitelisted=0 AND scaned = 1 GROUP BY" +
" score ORDER BY score", sub[1])
else:
if user.is_superuser:
query = """%s WHERE whitelisted=0 AND scaned = 1 GROUP BY
score ORDER BY score""" % query
conn.execute(query)
else:
sql = raw_user_filter(user, addrs, act)
gql = """WHERE %s AND whitelisted=0 AND scaned = 1 GROUP
BY score ORDER BY score""" % sql
query = "%s %s" % (query, gql)
conn.execute(query)
result_list = [self.model(id=i + 1, score=row[0], count=int(row[1]))
for i, row in enumerate(conn.fetchall())]
return result_list
def all(self, user, filters_list=None, addrs=None, act=3, daterange=None):
"message totals"
conn = connection.cursor()
query = """
SELECT date, count(*) AS mail_total,
SUM(CASE WHEN virusinfected>0 THEN 1 ELSE 0 END)
AS virus_total, SUM(CASE WHEN (virusinfected=0)
AND spam>0 THEN 1 ELSE 0 END) AS spam_total,
SUM(size) AS size_total FROM messages
"""
if filters_list:
sub = gen_dynamic_raw_query(filters_list)
if user.is_superuser:
if daterange:
conn.execute(
query + " WHERE (date BETWEEN '%s' AND '%s')"
" AND " + sub[0] + " GROUP BY date ORDER BY date DESC",
daterange[0], daterange[1], sub[1])
else:
conn.execute(
query + " WHERE " + sub[0] +
" GROUP BY date ORDER BY date DESC", sub[1])
else:
sql = raw_user_filter(user, addrs, act)
if daterange:
conn.execute(
query + " WHERE (date BETWEEN '%s' AND '%s')"
" AND " + sql + " AND " + sub[0] +
" GROUP BY date ORDER BY date DESC", daterange[0],
daterange[1], sub[1])
else:
conn.execute(
query + " WHERE " + sql + " AND " + sub[0] +
" GROUP BY date ORDER BY date DESC", sub[1])
else:
if user.is_superuser:
if daterange:
query = """%s WHERE date BETWEEN '%s' AND '%s' GROUP BY
date ORDER BY date DESC""" % (query, daterange[0],
daterange[1])
else:
query = "%s GROUP BY date ORDER BY date DESC" % query
conn.execute(query)
else:
sql = raw_user_filter(user, addrs, act)
if daterange:
query = """%s WHERE (date BETWEEN '%s' AND '%s') AND %s
GROUP BY date ORDER BY date
DESC""" % (query, daterange[0], daterange[1], sql)
else:
query = """%s WHERE %s GROUP BY date ORDER BY date
DESC""" % (query, sql)
conn.execute(query)
result_list = map(self.makevals, enumerate(conn.fetchall()))
return result_list
def all(self, user, filters_list=None, addrs=None, act=3, daterange=None):
"message totals"
conn = connection.cursor()
query = """
SELECT date, count(*) AS mail_total,
SUM(CASE WHEN virusinfected>0 THEN 1 ELSE 0 END)
AS virus_total, SUM(CASE WHEN (virusinfected=0)
AND spam>0 THEN 1 ELSE 0 END) AS spam_total,
SUM(size) AS size_total FROM messages
"""
if filters_list:
sub = gen_dynamic_raw_query(filters_list)
if user.is_superuser:
if daterange:
conn.execute(query + " WHERE (date BETWEEN '%s' AND '%s')"
" AND " + sub[0] + " GROUP BY date ORDER BY date DESC",
daterange[0], daterange[1], sub[1])
else:
conn.execute(query + " WHERE " + sub[0] +
" GROUP BY date ORDER BY date DESC", sub[1])
else:
sql = raw_user_filter(user, addrs, act)
if daterange:
conn.execute(query + " WHERE (date BETWEEN '%s' AND '%s')"
" AND " + sql + " AND " + sub[0] +
" GROUP BY date ORDER BY date DESC", daterange[0],
daterange[1], sub[1])
else:
conn.execute(query + " WHERE " + sql + " AND " + sub[0] +
" GROUP BY date ORDER BY date DESC", sub[1])
else:
if user.is_superuser:
if daterange:
query = """%s WHERE date BETWEEN '%s' AND '%s' GROUP BY
date ORDER BY date DESC""" % (query, daterange[0],
daterange[1])
else:
query = "%s GROUP BY date ORDER BY date DESC" % query
conn.execute(query)
else:
sql = raw_user_filter(user, addrs, act)
if daterange:
query = """%s WHERE (date BETWEEN '%s' AND '%s') AND %s
GROUP BY date ORDER BY date
DESC""" % (query, daterange[0], daterange[1],
sql)
else:
query = """%s WHERE %s GROUP BY date ORDER BY date
DESC""" % (query, sql)
conn.execute(query)
result_list = map(self.makevals, enumerate(conn.fetchall()))
return result_list
def get(self, user, addrs=None, act=3):
"message stats"
conn = connection.cursor()
today = datetime.date.today()
query = """
SELECT COUNT(*) AS mail, SUM(CASE WHEN virusinfected=0 AND
nameinfected=0 AND otherinfected=0 AND spam=0 AND
highspam=0 THEN 1 ELSE 0 END) AS clean_mail, SUM(CASE WHEN
virusinfected>0 THEN 1 ELSE 0 END) AS virii, SUM(CASE WHEN
nameinfected>0 AND virusinfected=0 AND otherinfected=0
AND spam=0 AND highspam=0 THEN 1 ELSE 0 END) AS infected,
SUM(CASE WHEN otherinfected>0 OR nameinfected>0 AND
virusinfected=0 AND spam=0 AND highspam=0 THEN 1 ELSE 0 END)
AS otherinfected, SUM(CASE WHEN spam>0 AND virusinfected=0
AND nameinfected=0 AND otherinfected=0 AND highspam=0 THEN
1 ELSE 0 END) AS spam_mail, SUM(CASE WHEN highspam>0 AND
virusinfected=0 AND nameinfected=0 AND otherinfected=0
THEN 1 ELSE 0 END) AS high_spam, SUM(size) AS size FROM
messages WHERE date = '%s'
""" % today
if user.is_superuser:
conn.execute(query)
else:
sql = raw_user_filter(user, addrs, act)
conn.execute(query + " AND " + sql)
row = conn.fetchone()
return self.model(total=row[0], clean_mail=row[1], virii=row[2],
infected=row[3], otherinfected=row[4], spam_mail=row[5],
high_spam=row[6], size=row[7])
def all(self, user, filters_list=None, addrs=None, act=3):
"spam scores"
conn = connection.cursor()
query = """
SELECT round(sascore) AS score, count(*) AS count FROM messages
"""
if filters_list:
sub = gen_dynamic_raw_query(filters_list)
if user.is_superuser:
conn.execute(
query + " WHERE " + sub[0] +
" AND whitelisted=0 AND scaned = 1 GROUP" +
" BY score ORDER BY score", sub[1])
else:
sql = raw_user_filter(user, addrs, act)
conn.execute(
query + " WHERE " + sql + " AND " + sub[0] +
" AND whitelisted=0 AND scaned = 1 GROUP BY" +
" score ORDER BY score", sub[1])
else:
if user.is_superuser:
query = """%s WHERE whitelisted=0 AND scaned = 1 GROUP BY
score ORDER BY score""" % query
conn.execute(query)
else:
sql = raw_user_filter(user, addrs, act)
gql = """WHERE %s AND whitelisted=0 AND scaned = 1 GROUP
BY score ORDER BY score""" % sql
query = "%s %s" % (query, gql)
conn.execute(query)
result_list = [
self.model(id=i + 1, score=row[0], count=int(row[1]))
for i, row in enumerate(conn.fetchall())
]
return result_list
def get(self, user, addrs=None, act=3):
"message stats"
conn = connection.cursor()
today = datetime.date.today()
query = """
SELECT COUNT(*) AS mail, SUM(CASE WHEN virusinfected=0 AND
nameinfected=0 AND otherinfected=0 AND spam=0 AND
highspam=0 THEN 1 ELSE 0 END) AS clean_mail, SUM(CASE WHEN
virusinfected>0 THEN 1 ELSE 0 END) AS virii, SUM(CASE WHEN
nameinfected>0 AND virusinfected=0 AND otherinfected=0
AND spam=0 AND highspam=0 THEN 1 ELSE 0 END) AS infected,
SUM(CASE WHEN otherinfected>0 OR nameinfected>0 AND
virusinfected=0 AND spam=0 AND highspam=0 THEN 1 ELSE 0 END)
AS otherinfected, SUM(CASE WHEN spam>0 AND virusinfected=0
AND nameinfected=0 AND otherinfected=0 AND highspam=0 THEN
1 ELSE 0 END) AS spam_mail, SUM(CASE WHEN highspam>0 AND
virusinfected=0 AND nameinfected=0 AND otherinfected=0
THEN 1 ELSE 0 END) AS high_spam, SUM(size) AS size FROM
messages WHERE date = '%s'
""" % today
if user.is_superuser:
conn.execute(query)
else:
sql = raw_user_filter(user, addrs, act)
conn.execute(query + " AND " + sql)
row = conn.fetchone()
return self.model(total=row[0],
clean_mail=row[1],
virii=row[2],
infected=row[3],
otherinfected=row[4],
spam_mail=row[5],
high_spam=row[6],
size=row[7])
