#!/usr/bin/python
#coding:utf-8
from pyh import *
import MySQLdb
import datetime
class Getdata:
def __init__(self):
self.conn=MySQLdb.connect(host='urhost',db='urdb',user='user',passwd='urpass')
self.cur=self.conn.cursor()
def getdata(self):
D={} #main 字典
d={} #临时字典
now=datetime.date.today()
delta=datetime.timedelta(days=7)
lastnow=now-delta
load='''
select a.f_name as project,c.avgload,c.f_date from t_project a,t_department b,
(select f_project_id,(sum(f_avg_load)/count(*)) as avgload,f_date from t_load_daily where f_date=date_add(curdate(),interval -7 day) group by f_project_id,f_date order by f_date) c
where c.f_project_id=a.f_pro_id and a.f_depart_id=b.f_depart_id union all select a.f_name as project,c.avgload,c.f_date from t_project a,t_department b,(select f_project_id,(sum(f_avg_load)/count(*)) as avgload,f_date from t_load_daily where f_date=date(now()) group by f_project_id,f_date order by f_date) c
where c.f_project_id=a.f_pro_id and a.f_depart_id=b.f_depart_id
'''
idc='''
select b.f_name as project,c.num,c.f_date from t_department a,t_project b,
(select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=3 and f_date=date(now()) group by f_project_id,f_date) c
where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id union all select b.f_name as project,c.num,c.f_date from t_department a,t_project b,(select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=3 and f_date=date_add(curdate(),interval -7 day)
group by f_project_id,f_date) c where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id;
'''
cdn='''
select b.f_name as project,c.num,c.f_date from t_department a,t_project b,
(select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=2 and f_date=date_add(curdate(),interval -7 day)
group by f_project_id,f_date) c where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id union all select b.f_name as project,c.num,c.f_date from t_department a,t_project b,(select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=2 and f_date=date(now()) group by f_project_id,f_date) c
where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id
'''
dau='''
select f_name,num,date from t_project a,
(select f_project_id as id ,sum(f_num) as num,f_date as date from t_dau where f_date=date(now()) group by f_project_id) b
where a.f_pro_id=b.id union all select f_name,num,date from t_project a,
(select f_project_id as id ,sum(f_num) as num,f_date as date from t_dau where f_date=date_add(curdate(),interval -7 day)
group by f_project_id) b where a.f_pro_id=b.id
'''
srvsum='''
select f_name,num,date from t_project a,
(select f_project_id as id,sum(f_costing_num) as num,f_date as date from t_costing_daily where f_costing_type=1 and f_date=date_add(curdate(),interval -7 day) group by f_project_id) b where a.f_pro_id=b.id
union all select f_name,num,date from t_project a,(select f_project_id as id,sum(f_costing_num) as num,f_date as date from t_costing_daily where f_costing_type=1 and f_date=date(now()) group by f_project_id) b where a.f_pro_id=b.id
'''
self.cur.execute(srvsum)
rows=self.cur.fetchall()
for row in rows:
try:
d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
except KeyError,e:
d[row[0]]=row[1::]
for k,v in d.items():
a=[]
m=list(v)
if now in v:
a.append(m[m.index(now)-1])
else:
a.append(0)
if lastnow in v:
a.append(m[m.index(lastnow)-1])
else:
a.append(0)
D[k]={'num':a}
###
d={} ##置空字典
self.cur.execute(load)
rows=self.cur.fetchall()
for row in rows:
try:
d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
except KeyError,e:
d[row[0]]=row[1::]
for k,v in d.items():
a=[]
m=list(v)
if now in v:
a.append(m[m.index(now)-1])
else:
a.append(0)
if lastnow in v:
a.append(m[m.index(lastnow)-1])
else:
a.append(0)
D[k]['load']=a
####
d={}
self.cur.execute(cdn)
rows=self.cur.fetchall()
for row in rows:
try:
d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
except KeyError,e:
d[row[0]]=row[1::]
for k,v in d.items():
a=[]
m=list(v)
if now in v:
a.append(m[m.index(now)-1])
else:
a.append(0)
if lastnow in v:
a.append(m[m.index(lastnow)-1])
else:
a.append(0)
D[k]['cdn']=a
####
d={}
self.cur.execute(idc)
rows=self.cur.fetchall()
for row in rows:
try:
d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
except KeyError,e:
d[row[0]]=row[1::]
for k,v in d.items():
a=[]
m=list(v)
if now in v:
a.append(m[m.index(now)-1])
else:
a.append(0)
if lastnow in v:
a.append(m[m.index(lastnow)-1])
else:
a.append(0)
D[k]['idc']=a
####
d={}
self.cur.execute(dau)
rows=self.cur.fetchall()
for row in rows:
try:
d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
except KeyError,e:
d[row[0]]=row[1::]
for k,v in d.items():
a=[]
m=list(v)
if now in v:
a.append(m[m.index(now)-1])
else:
a.append(0)
if lastnow in v:
a.append(m[m.index(lastnow)-1])
else:
a.append(0)
D[k]['dau']=a
return D
class ToHtml:
'''
此类主要用来生成html文件
'''
def str3tocoma(self,num):#每三个数字加一个逗号
e = list(str(num))
if len(e)<=3:
return num
else:
for i in range(len(e))[::-3][1:]:
e.insert(i+1,",")
return "".join(e)
def Gentable(self,pro,data):
def k(x,y):
try:
return float(x-y)/float(y)
except ZeroDivisionError:
return 0
t=table(caption='%s' % pro,border="1",cl="table1",cellpadding="0",cellspacing="0")
t<
if data.has_key('dau'):
N=k(data.get('dau')[0],data.get('dau')[1])
if N>0.3:
t<
elif N<0:
t<
else:
t<
else:
t<
if data.has_key('load'):
N=k(data.get('load')[0],data.get('load')[1])
if N>0.2:
t<
elif N<0:
t<
else:
t<
else:
t<
if data.has_key('num'):
N=k(data.get('num')[0],data.get('num')[1])
if N>0.2:
t<
elif N<0:
t<
else:
t<
else:
t<
if data.has_key('cdn'):
N=k(data.get('num')[0],data.get('num')[1])
if N>0.3:
t<
elif N<0:
t<
else:
t<
else:
t<
if data.has_key('idc'):
N=k(data.get('num')[0],data.get('num')[1])
if N>0.3:
t<
elif N<0:
t<
else:
t<
else:
t<
return t
def Tohtml(self,tables):
page=PyH('本周报告')
page.addCSS('common.css')
page<
tab=table(cellpadding="0",cellspacing="0",cl="table0")
for t in range(0,len(tables),2):
tab<
page<
page.printOut()
if __name__=="__main__":
I=Getdata()
d=I.getdata()
tabrows=[]
h=ToHtml()
for k,v in d.items():
t=h.Gentable(k,v)
tabrows.append(t)
h.Tohtml(tabrows)