/src/baruwa/reports/utils.py
Python | 290 lines | 264 code | 6 blank | 20 comment | 2 complexity | b251635e6cd1b8dbb733c7330070ccaf MD5 | raw file
Possible License(s): GPL-2.0
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)