python html_python 生成html文件

#!/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)