def retorna_historico_de_delito(self, codigoDelito):
tipo = TipoOcorrencia.objects.get(codigo=codigoDelito)
query = "select extract (month from data) as mes, "\
"extract(year from data) as ano, count(*) "\
" from ocorrencias_ocorrencia "\
" where tipo_id = " + str(tipo.id) +\
" and data::date > (now()::date - 1080)"\
" group by extract (month from data), extract(year from data) "\
" order by ano,mes"
cursor = connection.cursor()
cursor.execute(query)
retorno = dictfetchall(cursor)
connection.close()
historico = []
for ocor in retorno:
data = str(ocor['ano']) + '-' + '{:*>2}'.format(str(ocor['mes']))\
+ '-01' + 'T' + '00:00:00-07:00'
data = "%02d-%02d-01T00:00:00-07:00" % (ocor['ano'], ocor['mes'])
historico.append(dict(count=int(ocor['count']), date=data))
return historico
def get_estatistica(self, tipo_id=None):
query = "select count(ocor.id) as total ,tipo.codigo as codigo, \
tipo.nome_para_listagem as nome, \
extract(hour from data) as hora , \
extract (dow from data) as diadasemana \
from ocorrencias_ocorrencia as ocor \
join ocorrencias_tipoocorrencia as tipo \
on (tipo.id = ocor.tipo_id) \
where tipo.pode_listar = true "
if tipo_id != None:
query = query + " and tipo_id = " + str(tipo_id)
query += " group by ocor.tipo_id,tipo.codigo, " \
"tipo.nome_para_listagem,extract(hour from data) , " \
"extract (dow from data) \
order by ocor.tipo_id"
min_max = Ocorrencia.objects.aggregate(Max('data'), Min('data'))
periodo = 'De ' + min_max['data__min'].strftime("%d/%m/%Y") + \
' a ' + min_max['data__max'].strftime("%d/%m/%Y")
cursor = connection.cursor()
cursor.execute(query)
retorno = dictfetchall(cursor)
return periodo, retorno
