def getall(start,end,area,ver,wap):
qs = " and day >='"+start+"' and day<= '" + end + "'"
if area !="all":
qs +=" and area = " + area
if ver != "all" :
qs +=" and ver =" + ver
if wap !='all' :
qs +=" and wapportal=" + wap
sql = "select pv.`day`,pv.pvcount,cv.cvcount from (select sum(count) pvcount ,day from tdwap_api where 1=1 "+ qs+ " GROUP BY day) pv \
, (select sum(count) cvcount ,day from tdwap_api where api in ('play','down') " + qs+ " GROUP BY day) cv where pv.`day` = cv.`day`"
print sql
conn = db.getmysqlconn()
c = conn.cursor()
c.execute(sql)
l=[]
for o in c.fetchall():
d={}
d['day']=str(o[0]).split(' ')[0]
d['pv'] = int(o[1])
d['cv'] = int(o[2])
l.append(d)
c.close()
conn.close()
return l
def getcvbycvtype(start,end,area,ver,wap,api):
typedict = {1:"流畅(500)版",2:"清晰(51)版",3:"高清(52)版",4:"超清(53)版",5:"超清(54)版",6:"在线(FLV)版",-1:"其他"}
qs = " and day >='"+start+"' and day<= '" + end + "'"
if area !="all":
qs +=" and area = " + area
if ver != "all" :
qs +=" and ver =" + ver
if wap !='all' :
qs +=" and wapportal=" + wap
if api !='all' :
if api =='1':
qs +=" and api='play'"
else:
qs +=" and api='down'"
sql = "select sum(count) c ,day,cvtype from tdwap_cv t where 1=1 "+ qs +" GROUP BY t.`day`,t.cvtype ORDER BY day,cvtype"
# print sql
tsql = "select tt.day ,\
max(case when tt.cvtype =1 THEN tt.c else 0 end) as '500',\
max(case when tt.cvtype =2 THEN tt.c else 0 END) as '51',\
max(case when tt.cvtype =3 THEN tt.c else 0 END) as '52',\
max(case when tt.cvtype =4 THEN tt.c else 0 END) as '53',\
max(case when tt.cvtype =5 THEN tt.c else 0 END) as '54',\
max(case when tt.cvtype =6 THEN tt.c else 0 END) as 'FLV',\
max(case when tt.cvtype =-1 THEN tt.c else 0 END) as 'other',\
sum(tt.c) as 'count' \
from (" +sql + ") tt GROUP BY tt.day"
print tsql
conn = db.getmysqlconn()
c = conn.cursor()
c.execute(tsql)
pvsql = "select day,sum(count) c from tdwap_api GROUP BY day"
c1 = conn.cursor()
c1.execute(pvsql)
pd={}
for p in c1.fetchall():
pd[str(p[0]).split(' ')[0]]=int(p[1])
c1.close()
l=[]
for o in c.fetchall():
d={}
d['day']=str(o[0]).split(' ')[0]
d['_500']=int(o[1])
d['_51']=int(o[2])
d['_52']=int(o[3])
d['_53']=int(o[4])
d['_54']=int(o[5])
d['FLV']=int(o[6])
d['other']=int(o[7])
d['count']=int(o[8])
d['convert']=round(float(o[8]) / float(pd[str(o[0]).split(' ')[0]])*100,2)
l.append(d)
return l
c.close()
conn.close()
def gotochannel():
cid = request.query.get('cid')
page = request.query.get('page',1)
vid = request.query.get('id')
vtitle = request.query.get('title')
vproduct = request.query.get('product')
vcp = request.query.get('cp')
vstart=request.query.get('start')
vend=request.query.get('end')
page = int(page)
start = (page-1)*pagesize
end = (page)*pagesize
conn=db.getmysqlconn()
c= conn.cursor()
qs = " and t.channel_id="+str(cid)
querylist= []
query_args={"title":'',"id":'',"start":'',"end":'',"channelid":str(cid)}
if vid and len(vid)>0:
qs += " and itemid='"+vid + "'"
query_args['id']=vid
if vtitle and len(vtitle)>0:
qs += " and title='"+ vtitle + "'"
query_args['title']=vtitle
if vstart and vend and len(vstart)>0 and len(vend)>0:
qs += " and addtime >='"+vstart + "' and addtime <='" + vend +"'"
query_args['start']=vstart
query_args['end']=vend
print cid,start,end
sql = "select t1.id,t1.itemcode,t1.title,t1.addtime,t1.pics,t1.playtimes,t2.channel_name \
from gkgp_channel_item_link t ,gkgp_item t1 ,gkgp_channel t2 \
where t.item_id = t1.id and t.channel_id = t2.id "+qs+" limit %s,%s"
print sql
c.execute(sql,(start,pagesize))
l=[]
for one in c.fetchall():
l.append(one)
sqlcount = "select count(*) \
from gkgp_channel_item_link t ,gkgp_item t1 ,gkgp_channel t2 \
where t.item_id = t1.id and t.channel_id = t2.id "+qs
c.execute(sqlcount,())
totalrecord = c.fetchone()[0]
c.close()
conn.close()
totalpage=0
if totalrecord/pagesize <1:
totalpage=1
else:
if totalrecord % pagesize ==0:
totalpage=totalrecord/pagesize
else:
totalpage = totalrecord/pagesize + 1
return dict(all=l,total=totalpage,domain=SITE_DOMAIN,startpage=page,query_args=query_args)
def deluser(menuids):
conn= db.getmysqlconn()
c=conn.cursor()
for one in menuids.split(','):
if len(one) > 0:
sql="delete from HNSJ_TDCMS_MENU where id="+one
c.execute(sql)
conn.commit()
c.close()
conn.close()
def addproject():
projectname = request.forms.get('projectname')
conn = db.getmysqlconn()
c=conn.cursor()
sql = "insert into HNSJ_TDCMS_MENU (menuname,parent) values(%s,%s)"
c.execute(sql,(projectname,'-1'))
conn.commit()
c.close()
conn.close()
redirect('projectmanage')
def addproject():
projectname = request.forms.get('projectname')
conn = db.getmysqlconn()
c=conn.cursor()
sql = "insert into project (projectname) values(%s)"
c.execute(sql,(projectname))
conn.commit()
c.close()
conn.close()
redirect('projectmanage')
def deluser(userids):
conn= db.getmysqlconn()
c=conn.cursor()
for one in userids.split(','):
if len(one) > 0:
sql="delete from users where id="+one
c.execute(sql)
conn.commit()
c.close()
conn.close()
def deluser(userids):
conn= db.getmysqlconn()
c=conn.cursor()
for one in userids.split(','):
if len(one) > 0:
sql="delete from HNSJ_TDCMS_USER where id=%s"
c.execute(sql,(one))
conn.commit()
c.close()
conn.close()
def delproject(projectid):
conn = db.getmysqlconn()
c = conn.cursor()
for one in projectid.split(','):
if len(one)>0:
sql = "delete from HNSJ_TDCMS_MENU where id = %s"
c.execute(sql,(one))
conn.commit()
c.close()
conn.close()
def adduser():
username = request.forms.get('username')
password = request.forms.get('password')
conn= db.getmysqlconn()
c=conn.cursor()
sql="insert into HNSJ_TDCMS_USER (username,password) values(%s,%s) "
c.execute(sql,(username,dest(password.strip())))
conn.commit()
c.close()
conn.close()
redirect ('usermanage')
def getitems():
page = request.query.get('page',1)
vid = request.query.get('id')
vtitle = request.query.get('title')
vproduct = request.query.get('product')
vcp = request.query.get('cp')
vstart=request.query.get('start')
vend=request.query.get('end')
page = int(page)
start = (page-1)*pagesize
end = (page)*pagesize
print start,end
channellist=getallchannel()
conn=db.getmysqlconn()
c= conn.cursor()
qs = " 1=1"
querylist= []
query_args={"title":'',"id":'',"start":'',"end":''}
if vid and len(vid)>0:
qs += " and itemid='"+vid + "'"
query_args['itemid']=vid
if vtitle and len(vtitle)>0:
qs += " and title='"+ vtitle + "'"
query_args['title']=vtitle
if vstart and vend and len(vstart)>0 and len(vend)>0:
qs += " and addtime >='"+vstart + "' and addtime <='" + vend +"'"
query_args['start']=vstart
query_args['end']=vend
sql = "select * from gkgp_item where "+qs+" limit %s,%s"
c.execute(sql,(start,pagesize))
l=[]
for one in c.fetchall():
l.append(one)
print len(l)
sqlcount = "select count(*) from gkgp_item where " + qs
c.execute(sqlcount)
totalrecord = c.fetchone()[0]
c.close()
conn.close()
totalpage=0
if totalrecord/pagesize <1:
totalpage=1
else:
if totalrecord % pagesize ==0:
totalpage=totalrecord/pagesize
else:
totalpage = totalrecord/pagesize + 1
return dict(all=l,total=totalpage,domain=SITE_DOMAIN,startpage=page,channellist=channellist,query_args=query_args,play_domain=PLAY_VIDEO_DOMAIN)
def getproject():
conn= db.getmysqlconn()
sql = "select * from project"
c = conn.cursor()
c.execute(sql)
l=[]
for o in c.fetchall():
l.append(o)
c.close()
conn.close()
return l
def adduser():
username = request.forms.get('username')
password = request.forms.get('password')
conn= db.getmysqlconn()
c=conn.cursor()
sql="insert into users (username,password) values('"+username+"','"+password+"') "
c.execute(sql)
conn.commit()
c.close()
conn.close()
redirect ('usermanage')
def getmenutype():
conn= db.getmysqlconn()
sql = "select * from HNSJ_TDCMS_MENU where parent=-1"
c = conn.cursor()
c.execute(sql)
l=[]
for o in c.fetchall():
l.append(o)
c.close()
conn.close()
return l
def getallmenu():
conn = db.getmysqlconn()
sql = "select t.id,t.menuname,t.path,t.parent from HNSJ_TDCMS_MENU t "
c = conn.cursor()
l=[]
c.execute(sql)
for o in c.fetchall():
l.append(o)
c.close()
conn.close()
return l
def getusermenu(userid):
conn= db.getmysqlconn()
sql = "select menuid from HNSJ_TDCMS_MENU_USER where userid = " + userid
c = conn.cursor()
c.execute(sql)
l=[]
for o in c.fetchall():
l.append(o[0])
c.close()
conn.close()
return l
def getalluser():
conn = db.getmysqlconn()
sql = "select * from HNSJ_TDCMS_USER"
c=conn.cursor()
c.execute(sql)
l=[]
for o in c.fetchall():
l.append(o)
c.close()
conn.close()
return l
def getallmenu():
conn = db.getmysqlconn()
sql = "select t.id,t.menuname,t.url,t1.projectname from menulist t ,project t1 where t.projectid = t1.id ORDER BY t1.projectname desc"
c = conn.cursor()
l=[]
c.execute(sql)
for o in c.fetchall():
l.append(o)
c.close()
conn.close()
return l
def goto_edit_channel():
parent_channel = db.query_list('select id,channel_name from gkgp_channel where pid=-1',())
cid= request.query.get('id')
conn=db.getmysqlconn()
c = conn.cursor()
sql = 'select * from gkgp_channel where id=%s'
c.execute(sql,(cid))
res = c.fetchone()
c.close()
conn.close()
return dict(channel=res,domain=SITE_DOMAIN,cid=cid,parent_channel_list= parent_channel)
def getusermenu(userid):
conn= db.getmysqlconn()
sql = "select menuid from gkgp_cms_menu_user where userid = " + userid
c = conn.cursor()
c.execute(sql)
l=[]
for o in c.fetchall():
l.append(o[0])
c.close()
conn.close()
return l
def getallchannel():
conn = db.getmysqlconn()
c=conn.cursor()
sql = "select * from gkgp_channel"
c.execute(sql)
l=[]
for one in c.fetchall():
l.append(one)
c.close()
conn.close()
return l
def getmenutype():
conn= db.getmysqlconn()
sql = "select * from HNSJ_TDCMS_MENU where parent=-1"
c=conn.cursor()
c.execute(sql)
rs = c.fetchall()
menutype = {}
for o in rs:
menutype[o[0]]=o[1]
c.close()
conn.close()
return menutype
def login(username):
conn=db.getmysqlconn()
sql="select id,username,password from HNSJ_TDCMS_USER where username='******'"
c = conn.cursor()
c.execute(sql)
res = c.fetchone()
if res :
return res
else:
return res
c.close()
conn.close()
def getmethodall(start):
sql = "select * from wl_api_all where day ='"+start+"' "
conn = db.getmysqlconn()
c = conn.cursor()
c.execute(sql)
res = c.fetchone()
if res :
pv = res[1]
avg= res[4]
return (pv,avg)
c.close()
conn.close()
def login(username):
conn=db.getmysqlconn()
sql="select id,username,password from gkgp_cms_user where username=%s"
c = conn.cursor()
c.execute(sql,(username))
res = c.fetchone()
if res :
return res
else:
return res
c.close()
conn.close()
def getpower(userid,menuids):
conn = db.getmysqlconn()
c=conn.cursor()
delsql = "delete from HNSJ_TDCMS_MENU_USER where userid="+userid
c.execute(delsql)
for menuid in menuids.split(','):
if len(menuid)>0:
sql = "insert into HNSJ_TDCMS_MENU_USER (userid,menuid) values(%s,%s)"
c.execute(sql,(userid,menuid ))
conn.commit()
c.close()
conn.close()
def delete():
ids=request.query.get('ids')
conn = db.getmysqlconn()
c = conn.cursor()
for one in ids.split('_'):
print one
sql = "delete from gkgp_channel where id = %s"
c.execute(sql,(one))
conn.commit()
c.close()
conn.close()
return getitems()
def adduser():
menuname = request.forms.get('menuname')
url = request.forms.get('url')
projectid = request.forms.get('projectid')
conn= db.getmysqlconn()
c=conn.cursor()
sql="insert into HNSJ_TDCMS_MENU (menuname,path,parent) values(%s,%s,%s) "
c.execute(sql,(menuname,url,projectid))
conn.commit()
c.close()
conn.close()
redirect ('menumanage')
def getpower(userid,menuids):
conn = db.getmysqlconn()
c=conn.cursor()
delsql = "delete from usermenu where userid="+userid
c.execute(delsql)
for menuid in menuids.split(','):
if len(menuid)>0:
sql = "insert into usermenu (userid,menuid) values("+userid+","+menuid + ")"
print sql
c.execute(sql)
conn.commit()
c.close()
conn.close()
def getarea():
conn = db.getmysqlconn()
c = conn.cursor()
c.execute("select DISTINCT province_id , province_name from area")
l=[]
for o in c.fetchall():
d={}
d['id']=o[0]
d['name']=o[1]
l.append(d)
c.close()
conn.close()
return l
