def inicio(request):
#Modificaciones a nivels de tarifas horarias
aenergia=[]
acoste=[]
hoy = datetime.datetime.now()
dia = hoy.day
mes = hoy.month
ano = hoy.year
nsemana =hoy.strftime("%W")
sentencia = sentenciaSensores()
cur = connection.cursor()
cur_dia = cur.execute('select sum(energia) from lecturas_consumoshoras where dia=%s and per=%s and ejer=%s %s' % (dia,mes,ano,sentencia) )
ediaria = validaEnergia(cur_dia)
cur_semana = cur.execute(" " "select sum(energia) from lecturas_consumosdias where semana =" " "+str(nsemana)+" " " and ejer=" " "+str(ano)+" %s" % sentencia )
esemana = validaEnergia(cur_semana)
cur_mes_encurso = cur.execute(' select sum(energia) from lecturas_consumosdias where per=%s and ejer=%s %s' %(mes,ano,sentencia))
emescurso = validaEnergia(cur_mes_encurso)
cur_mes = cur.execute('select sum(energia) from lecturas_consumosmes where per=%s and ejer=%s %s' % (mes,ano,sentencia))
emens = validaEnergia(cur_mes)
cur_ano_encurso = cur.execute( 'select sum(energia) from lecturas_consumosmes where ejer=%s %s' % (ano,sentencia))
eanocurso = validaEnergia(cur_ano_encurso)
# cur_ano = cur.execute('select sum(energia) from lecturas_consumos_anos where ejer=%s ' % ano )
# eano = validaEnergia(cur_ano)
#Buscar conste segun contrato en base de datos
coste =0.00015 #Buscar a nivel de configuracion
cur_hora_max = cur.execute('select max(energia) ,hora from (select sum(energia) energia,hora from lecturas_consumoshoras where dia=%s and per=%s and ejer=%s %s group by hora)' % (dia,mes,ano,sentencia) )
ehoramax,horamax = avalidaEnergia(cur_hora_max)
cur_semana_max = cur.execute('select max(energia) , semana from (select semana, sum(energia) energia from lecturas_consumosdias where per =%s and ejer=%s %s group by semana) ' %( mes,ano,sentencia) )
esemanamax,semanamax = avalidaEnergia(cur_semana_max)
cur_dia_max = cur.execute(' select max(energia) ,dia from (select sum(energia) energia,dia from lecturas_consumosdias where per=%s and ejer=%s %s group by dia) ' %(mes,ano,sentencia))
ediamax,diamax = avalidaEnergia(cur_dia_max)
cur_mes_max = cur.execute('select max(energia), per from (select sum(energia) energia,per from lecturas_consumosmes where per=%s and ejer=%s %s group by per) ' % (mes,ano,sentencia))
emensmax,mesmax = avalidaEnergia(cur_mes_max)
aenergiamax=(dict(ehoramax=ehoramax,esemanamax=esemanamax,ediamax=ediamax,emesmax=emensmax))
momentos =(dict(horamax =horamax, semanamax=semanamax,diamax=diamax,mesmax=mesmax))
aenergia=(dict(diaria=ediaria,semanal=esemana+ediaria,mensual=emens+emescurso+ediaria,anual=eanocurso+emescurso+ediaria))
acoste=( dict(diaria=round(aenergia["diaria"]*coste,2),semanal=round(aenergia["semanal"]*coste,2), mensual=round(aenergia["mensual"]*coste,2),anual=round(aenergia["anual"]*coste,2) ))
return render_to_response("web/secciones/panelcontrol/estadisticas.html",{'energia':aenergia, 'coste': acoste, 'maximos' : aenergiamax, 'momento' : momentos },context_instance=RequestContext(request))
def grafico_ano(request):
hoy = datetime.datetime.now()
dia = hoy.day
mes = hoy.month
ano = hoy.year
nsemana =hoy.strftime("%W")
sql = ('select per, sum(energia) promedio from (select dia,per,ejer,hora,avg(energia) energia from lecturas_consumosmes where ejer=%s %s group by hora,dia,per,ejer) group by per order by 1' % (ano,sentenciaSensores()))
cur = connection.cursor()
cur.execute(sql)
entries = [([[row[0], ponCero(row[1])]]) for row in cur.fetchall()]
return simplejson.dumps(entries)
def grafico_semana(request):
hoy = datetime.datetime.now()
dia = hoy.day
mes = hoy.month
ano = hoy.year
nsemana = noSemana(dia,mes,ano)
sql = (""" select dia,per,ejer,sum(energia) from lecturas_consumosdias where per = """+str(mes)+""" and ejer="""+str(ano)+ """ %s group by semana """ % sentenciaSensores() )
print sql
#sql=("""select strftime('%%w',date(cast(ejer as varchar(2)) || "-"|| cast(per as varchar(2)) ||"-"|| cast(dia as varchar(2)) ) ) ,sum(energia) promedio from (select dia,per,ejer,hora,avg(energia) energia from lecturas_consumos where strftime('%%W',date(cast(ejer as varchar(2)) || "-"|| cast(per as varchar(2)) ||"-"|| cast(dia as varchar(2)) ) ) ="%s" and ejer=%s group by hora,dia,per,ejer) group by strftime('%%w',date(cast(ejer as varchar(2)) || "-"|| cast(per as varchar(2)) ||"-"|| cast(dia as varchar(2)) ) ) order by 1 """ % (nsemana,ano))
cur = connection.cursor()
cur.execute(sql)
entries = [([[ tiempoenMil(row[0],row[1],row[2],0,0), ponCero(row[3])] ]) for row in cur.fetchall()]
return simplejson.dumps(entries)
def grafico_mes(request):
hoy = datetime.datetime.now()
dia = hoy.day
mes = hoy.month
ano = hoy.year
nsemana =hoy.strftime("%W")
sql =('select dia,per,ejer,sum(energia) promedio from lecturas_consumosdias where per=%s and ejer=%s %s group by dia,per,ejer order by dia ' % (mes,ano,sentenciaSensores()))
cur = connection.cursor()
cur.execute(sql)
entries = [([[tiempoenMil(row[0],row[1],row[2],0,0) , ponCero(row[3])]]) for row in cur.fetchall()]
return simplejson.dumps(entries)
def grafico_dia(request):
hoy = datetime.datetime.now()
dia = hoy.day
mes = hoy.month
ano = hoy.year
sql=('select dia,per,ejer,hora, sum(energia) from lecturas_consumoshoras where dia=%s and per=%s and ejer=%s %s group by dia,per,ejer,hora order by hora' % (dia,mes,ano,sentenciaSensores() ) )
cur = connection.cursor()
cur.execute(sql)
entries = [([[tiempoenMil(row[0],row[1],row[2],row[3],0), ponCero(row[4])]]) for row in cur.fetchall()]
return simplejson.dumps(entries)