/src/baruwa/reports/utils.py

  1#
  2# Baruwa - Web 2.0 MailScanner front-end.
  3# Copyright (C) 2010-2011  Andrew Colin Kissa <andrew@topdog.za.net>
  4#
  5# This program is free software; you can redistribute it and/or modify
  6# it under the terms of the GNU General Public License as published by
  7# the Free Software Foundation; either version 2 of the License, or
  8# (at your option) any later version.
  9#
 10# This program is distributed in the hope that it will be useful,
 11# but WITHOUT ANY WARRANTY; without even the implied warranty of
 12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 13# GNU General Public License for more details.
 14#
 15# You should have received a copy of the GNU General Public License along
 16# with this program; if not, write to the Free Software Foundation, Inc.,
 17# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 18#
 19# vim: ai ts=4 sts=4 et sw=4
 20#
 21
 22from django.db.models import Count, Sum, Q
 23from baruwa.messages.models import Message
 24from baruwa.utils.misc import apply_filter
 25from baruwa.utils.graphs import PIE_COLORS
 26
 27
 28def pack_json_data(data, arg1, arg2):
 29    "creates the json for the svn pie charts"
 30    ret = []
 31
 32    for index, item in enumerate(data):
 33        pie_data = {}
 34        pie_data['y'] = item[arg2]
 35        pie_data['color'] = PIE_COLORS[index]
 36        pie_data['stroke'] = 'black'
 37        pie_data['tooltip'] = item[arg1]
 38        ret.append(pie_data)
 39    return ret
 40
 41
 42def run_hosts_query(request, active_filters):
 43    "run the top hosts query"
 44    data = Message.messages.for_user(request).values('clientip').exclude(
 45        Q(clientip__exact = '') | Q(clientip__exact = '127.0.0.1') |
 46        Q(clientip__isnull=True)).annotate(num_count=Count('clientip'),
 47        total_size=Sum('size'), virus_total=Sum('virusinfected'),
 48        spam_total=Sum('spam')).order_by('-num_count')
 49    data = apply_filter(data, request, active_filters)
 50    data = data[:10]
 51    return data
 52
 53
 54def run_query(query_field, exclude_kwargs, order_by, request, active_filters):
 55    "run a query"
 56    data = Message.messages.for_user(request).values(query_field).exclude(
 57    **exclude_kwargs).annotate(num_count=Count(query_field),
 58    total_size=Sum('size')).order_by(order_by)
 59    data = apply_filter(data, request, active_filters)
 60    data = data[:10]
 61    return data
 62
 63
 64def gen_dynamic_raw_query(filter_list):
 65    "generates a dynamic query"
 66    sql = []
 67    asql = []
 68    avals = []
 69    osql = []
 70    ovals = []
 71    nosql = []
 72    novals = [] 
 73
 74    for filter_item in filter_list:
 75        if filter_item['filter'] == 1:
 76            tmp = "%s = %%s" % filter_item['field']
 77            if tmp in asql:
 78                inx = asql.index(tmp)
 79                tvl = avals[inx]
 80
 81                osql.append(asql[inx])
 82                ovals.append(tvl)
 83
 84                asql.remove(tmp)
 85                avals.remove(tvl)
 86
 87                osql.append(tmp)
 88                ovals.append(filter_item['value'])
 89            else:
 90                if tmp in osql:
 91                    osql.append(tmp)
 92                    ovals.append(filter_item['value'])
 93                else:
 94                    asql.append(tmp)
 95                    avals.append(filter_item['value'])
 96        if filter_item['filter'] == 2:
 97            tmp = "%s != %%s" % filter_item['field']
 98            if tmp in asql:
 99                inx = asql.index(tmp)
100                tvl = avals[inx]
101
102                nosql.append(asql[inx])
103                novals.append(tvl)
104
105                asql.remove(tmp)
106                avals.remove(tvl)
107
108                nosql.append(tmp)
109                novals.append(filter_item['value'])
110            else:
111                if tmp in nosql:
112                    nosql.append(tmp)
113                    novals.append(filter_item['value'])
114                else:
115                    asql.append(tmp)
116                    avals.append(filter_item['value'])
117        if filter_item['filter'] == 3:
118            tmp = "%s > %%s" % filter_item['field']
119            if tmp in asql:
120                inx = asql.index(tmp)
121                tvl = avals[inx]
122
123                osql.append(asql[inx])
124                ovals.append(tvl)
125
126                asql.remove(tmp)
127                avals.remove(tvl)
128
129                osql.append(tmp)
130                ovals.append(filter_item['value'])
131            else:
132                if tmp in osql:
133                    osql.append(tmp)
134                    ovals.append(filter_item['value'])
135                else:
136                    asql.append(tmp)
137                    avals.append(filter_item['value'])
138        if filter_item['filter'] == 4:
139            tmp = "%s < %%s" % filter_item['field']
140            if tmp in asql:
141                inx = asql.index(tmp)
142                tvl = avals[inx]
143
144                osql.append(asql[inx])
145                ovals.append(tvl)
146
147                asql.remove(tmp)
148                avals.remove(tvl)
149
150                osql.append(tmp)
151                ovals.append(filter_item['value'])
152            else:
153                if tmp in osql:
154                    osql.append(tmp)
155                    ovals.append(filter_item['value'])
156                else:
157                    asql.append(tmp)
158                    avals.append(filter_item['value'])
159        if filter_item['filter'] == 5:
160            tmp = "%s LIKE %%s" % filter_item['field']
161            if tmp in asql:
162                inx = asql.index(tmp)
163                tvl = avals[inx]
164
165                osql.append(asql[inx])
166                ovals.append(tvl)
167
168                asql.remove(tmp)
169                avals.remove(tvl)
170
171                osql.append(tmp)
172                ovals.append(filter_item['value'])
173            else:
174                if tmp in osql:
175                    osql.append(tmp)
176                    ovals.append(filter_item['value'])
177                else:
178                    asql.append(tmp)
179                    avals.append('%' + filter_item['value'] + '%')
180        if filter_item['filter'] == 6:
181            tmp = "%s NOT LIKE %%s" % filter_item['field']
182            if tmp in asql:
183                inx = asql.index(tmp)
184                tvl = avals[inx]
185
186                nosql.append(asql[inx])
187                novals.append(tvl)
188
189                asql.remove(tmp)
190                avals.remove(tvl)
191
192                nosql.append(tmp)
193                novals.append(filter_item['value'])
194            else:
195                if tmp in nosql:
196                    nosql.append(tmp)
197                    novals.append(filter_item['value'])
198                else:
199                    asql.append(tmp)
200                    avals.append('%' + filter_item['value'] + '%')
201        if filter_item['filter'] == 7:
202            tmp = "%s REGEXP %%s" % filter_item['field']
203            if tmp in asql:
204                inx = asql.index(tmp)
205                tvl = avals[inx]
206
207                osql.append(asql[inx])
208                ovals.append(tvl)
209
210                asql.remove(tmp)
211                avals.remove(tvl)
212
213                osql.append(tmp)
214                ovals.append(filter_item['value'])
215            else:
216                if tmp in osql:
217                    osql.append(tmp)
218                    ovals.append(filter_item['value'])
219                else:
220                    asql.append(tmp)
221                    avals.append(filter_item['value'])
222        if filter_item['filter'] == 8:
223            tmp = "%s NOT REGEXP %%s" % filter_item['field']
224            if tmp in asql:
225                inx = asql.index(tmp)
226                tvl = avals[inx]
227
228                nosql.append(asql[inx])
229                novals.append(tvl)
230
231                asql.remove(tmp)
232                avals.remove(tvl)
233
234                nosql.append(tmp)
235                novals.append(filter_item['value'])
236            else:
237                if tmp in nosql:
238                    nosql.append(tmp)
239                    novals.append(filter_item['value'])
240                else:
241                    asql.append(tmp)
242                    avals.append(filter_item['value'])
243        if filter_item['filter'] == 9:
244            tmp = "%s IS NULL" % filter_item['field']
245            sql.append(tmp)
246        if filter_item['filter'] == 10:
247            tmp = "%s IS NOT NULL" % filter_item['field']
248            sql.append(tmp)
249        if filter_item['filter'] == 11:
250            tmp = "%s > 0" % filter_item['field']
251            sql.append(tmp)
252        if filter_item['filter'] == 12:
253            tmp = "%s = 0" % filter_item['field']
254            sql.append(tmp)
255    for item in sql:
256        asql.append(item)
257
258    andsql = ' AND '.join(asql)
259    orsql = ' OR '.join(osql)
260    nsql = ' AND '.join(nosql)
261
262    for item in ovals:
263        avals.append(item)
264
265    for item in novals:
266        avals.append(item)
267
268    if andsql != '':
269        if orsql != '':
270            if nsql != '':
271                sql = andsql + ' AND ( ' + orsql + ' ) AND ( ' + nsql + ' )'
272            else:
273                sql = andsql + ' AND ( ' + orsql + ' )'
274        else:
275            if nsql != '':
276                sql = andsql + ' AND ( ' + nsql + ' )'
277            else:
278                sql = andsql
279    else:
280        if orsql != '':
281            if nsql != '':
282                sql = '( ' + orsql + ' ) AND ( ' + nsql + ' )'
283            else:
284                sql = '( ' + orsql + ' )'
285        else:
286            if nsql != '':
287                sql = '( ' + nsql + ' )'
288            else:
289                sql = ' 1=1 '
290    return (sql, avals)