Example #1
0
    def all(self, user, filters_list=None, addrs=None, act=3):
        "message totals"
        from django.db import connection
        from baruwa.utils.misc import raw_user_filter
        from baruwa.reports.utils import gen_dynamic_raw_query
        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:
                conn.execute(query + " WHERE " + sub[0] +
                " GROUP BY date ORDER BY date DESC", sub[1])
            else:
                sql = raw_user_filter(user, addrs, act)
                conn.execute(query + " WHERE " + sql + " AND " + sub[0] +
                " GROUP BY date ORDER BY date DESC", sub[1])
        else:
            if user.is_superuser:
                query = "%s GROUP BY date ORDER BY date DESC" % query
                conn.execute(query)
            else:
                sql = raw_user_filter(user, addrs, act)
                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
Example #2
0
    def all(self, user, filters_list=None, addrs=None, act=3):
        "message totals"
        from django.db import connection
        from baruwa.utils.misc import raw_user_filter
        from baruwa.reports.utils import gen_dynamic_raw_query

        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:
                conn.execute(query + " WHERE " + sub[0] +
                " GROUP BY date ORDER BY date DESC",sub[1])
            else:
                sql = raw_user_filter(user, addrs, act)
                conn.execute(query + " WHERE " + sql +" AND "+ sub[0] +
                " GROUP BY date ORDER BY date DESC", sub[1])
        else:
            if user.is_superuser:
                query = "%s GROUP BY date ORDER BY date DESC" % query
                conn.execute(query)
            else:
                sql = raw_user_filter(user, addrs, act)
                query = """%s WHERE %s GROUP BY date ORDER BY date
                DESC""" % (query, sql)
                conn.execute(query)
        result_list = []
        for i, row in enumerate(conn.fetchall()):
            index = i
            index += 1
            vpct = "%.1f" % ((1.0 * int(row[2])/int(row[1]))*100)
            spct = "%.1f" % ((1.0 * int(row[3])/int(row[1]))*100)
            obj = self.model(id=index, date=str(row[0]),
                mail_total=int(row[1]), virus_total=int(row[2]),
                virus_percent=vpct, spam_total=int(row[3]),
                spam_percent=spct, size_total=int(row[4]))
            obj.total = row[1]
            result_list.append(obj)
        return result_list
Example #3
0
    def all(self, user, filters_list=None, addrs=None, act=3):
        "spam scores"
        from django.db import connection
        from baruwa.utils.misc import raw_user_filter
        from baruwa.reports.utils import gen_dynamic_raw_query

        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)
        #rows = c.fetchall()
        result_list = []
        for i, row in enumerate(conn.fetchall()):
            index = i
            index += 1
            obj = self.model(id=index, score=row[0], count=int(row[1]))
            result_list.append(obj)

        return result_list
Example #4
0
    def all(self, user, filters_list=None, addrs=None, act=3):
        "spam scores"
        from django.db import connection
        from baruwa.utils.misc import raw_user_filter
        from baruwa.reports.utils import gen_dynamic_raw_query

        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
Example #5
0
    def all(self, user, filters_list=None, addrs=None, act=3):
        "message totals"
        from django.db import connection
        from baruwa.utils.misc import raw_user_filter
        from baruwa.reports.utils import gen_dynamic_raw_query
        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:
                conn.execute(
                    query + " WHERE " + sub[0] +
                    " GROUP BY date ORDER BY date DESC", sub[1])
            else:
                sql = raw_user_filter(user, addrs, act)
                conn.execute(
                    query + " WHERE " + sql + " AND " + sub[0] +
                    " GROUP BY date ORDER BY date DESC", sub[1])
        else:
            if user.is_superuser:
                query = "%s GROUP BY date ORDER BY date DESC" % query
                conn.execute(query)
            else:
                sql = raw_user_filter(user, addrs, act)
                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
Example #6
0
    def get(self, user, addrs=None, act=3):
        "message stats"
        from django.db import connection
        import datetime
        from baruwa.utils.misc import raw_user_filter

        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])
Example #7
0
    def get(self, user, addrs=None, act=3):
        "message stats"
        from django.db import connection
        import datetime
        from baruwa.utils.misc import raw_user_filter

        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])