最近做的项目的页面部分报表的自动化测试验证
算是第一个如果可以称作自动化的东西，其实内容很简单，全是if else 加上for。难度后续慢慢熟练了在说。里面很多代码是冗余的，后续有机会在优化吧。只看别评价，指导请私信，十分感激！！
#encoding:utf-8
'''
Created on 2017年6月19日
@author: user
'''
import requests
import json
import MySQLdb
import ConfigParser #读入配置文件
import datetime
import re
import torndb
import decimal
class Project_web(object):
def __init__(self):
self.user='user'
self.pwd='password'
self.headers={'user-agent':"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.143 Safari/537.36"}
self.user_id=80000
self.agent_id=90000
self.session=requests.Session()
#数据库配置
conf_path='F:\\path\\conf.ini' #路径要用双\
config = ConfigParser.ConfigParser() ##读配置文件
config.read(conf_path) #读配置文件
self.mysql_host = config.get("data","host")
self.mysql_port = int(config.get("data","port"))
self.mysql_user = config.get("data","user");
self.mysql_passwd = config.get("data","passwd")
self.mysql_db = config.get("data","db")
self.conn = MySQLdb.connect(host = self.mysql_host, port =self.mysql_port, user = self.mysql_user, passwd = self.mysql_passwd, db = self.mysql_db, charset='utf8')
self.db=torndb.Connection(self.mysql_host,self.mysql_db,self.mysql_user,self.mysql_passwd, charset='utf8')
#查询日期
#self.endtime= (datetime.datetime.now()).strftime("%Y-%m-%d")
# self.endtime= (datetime.datetime.now() - datetime.timedelta(days = 0)).strftime("%Y-%m-%d") #当前日期前推一天
self.endtime= (datetime.datetime.now()).strftime("%Y-%m-%d") #当前日期前推一天
self.starttime= (datetime.datetime.now() - datetime.timedelta(days = 29)).strftime("%Y-%m-%d")
print '开始测试，\n验证时间范围为',self.starttime,self.endtime,'\n\n'
def login(self):
login_url='http://www.test.com/api/user/login'
login_data=json.dumps({"username":"user","password":"pwd","vcode":'dddd'})
login_post=self.session.post(login_url,headers=self.headers,data=login_data)
def dashboard(self):
print '\n---start 验证dashboard页面概览数据\n'
try:
'''获取页面请求数据'''
url='http://www.test.com/api/agent/dashboard?chart_type=sum_cost&start_time=%s&end_time=%s' %(self.starttime,self.endtime)
dashboard_data=self.session.get(url,headers=self.headers).text
overview=json.loads(dashboard_data)['overview']
'''获取数据库数据'''
dashboard_gl_sql="SELECT COUNT(DISTINCT a.id) as sum_advertiser,\
SUM(social_spend) as sum_cost,\
SUM(impressions) as sum_pv,\
SUM(clicks) as sum_clicks \
FROM ad_user a \
LEFT JOIN `ad_daily_report` b \
ON (a.id=b.user_id AND b.DAY >= %s AND b.day<=%s)\
WHERE a.agent_id=%s AND a.`is_del`=0 \
AND DATE_FORMAT(a.`create_time`,'%%Y-%%m-%%d') BETWEEN %s AND %s" #%(self.starttime,self.endtime,self.agent_id,self.starttime,self.endtime,)
dashboard_data_sql=self.db.query(dashboard_gl_sql,self.starttime,self.endtime,self.agent_id,self.starttime,self.endtime)
sqldata_tmp={}
for item in dashboard_data_sql[0]:
sqldata_tmp[item]=overview[item]
dashboard_tag=True
for item in sqldata_tmp:
sql_data=sqldata_tmp[item]
page_data=overview[item]
self.contrast(sql_data, page_data)
if self.tag==True:
dashboard_tag=True
else:
dashboard_tag=False
print 'ERROR',item,'异常，对应的值分别为',page_data,sql_data
break
if dashboard_tag==True:
print '首页概览验证通过'
else:
print '首页概览验证失败'
except Exception,e:
print Exception, ":",e
def dashboard_line(self):
print '\n---start 验证dashboard页面曲线图数据\n'
try:
url='http://www.test.com/api/agent/dashboard?chart_type=sum_cost&start_time=%s&end_time=%s' %(self.starttime,self.endtime)
aa=self.session.get(url,headers=self.headers)
item= aa.text
overview=json.loads(item)['overview']
for x_item in overview:
url='http://www.test.com/api/agent/dashboard?chart_type=%s&start_time=%s&end_time=%s' %(x_item,self.starttime,self.endtime)
x_item_get=self.session.get(url,headers=self.headers)
x_item_datatmp=x_item_get.text
x_item_date=json.loads(x_item_datatmp)['line']['xdata']
x_item_data=json.loads(x_item_datatmp)['line']['series'][0]['data']
x_item_all={}
for i in range(len(json.loads(x_item_datatmp)['line']['xdata'])):
x_item_all[x_item_date[i]]=x_item_data[i]
line_tmp={}
for i in range(1,30):
date= (datetime.datetime.now() - datetime.timedelta(days = i)).strftime("%Y-%m-%d")
line_tmp[date]=0
if x_item =='sum_advertiser':
x_item_sql="SELECT COUNT(1) as total FROM `ad_user` WHERE `agent_id`=%s AND `is_del`=0 AND DATE_FORMAT(`create_time`,'%%Y-%%m-%%d') <= %s"
elif x_item =='sum_cost':
x_item_sql="SELECT DATE_FORMAT(DAY,'%%Y-%%m-%%d') as date,IFNULL(SUM(social_spend),0) as total FROM `ad_daily_report` a LEFT JOIN ad_user b ON (b.id=a.user_id) WHERE b.agent_id=%s AND b.`is_del`=0 AND DAY BETWEEN %s AND %s GROUP BY DATE_FORMAT(DAY,'%%Y-%%m-%%d')"
elif x_item =='sum_pv':
x_item_sql="SELECT DATE_FORMAT(DAY,'%%Y-%%m-%%d') as date,IFNULL(SUM(impressions),0) as total FROM `ad_daily_report` a LEFT JOIN ad_user b ON (b.id=a.user_id) WHERE b.agent_id=%s AND b.`is_del`=0 AND DAY BETWEEN %s AND %s GROUP BY DATE_FORMAT(DAY,'%%Y-%%m-%%d')" # %(self.agent_id,self.starttime,self.endtime)
elif x_item == 'sum_clicks':
x_item_sql="SELECT DATE_FORMAT(DAY,'%%Y-%%m-%%d') as date,IFNULL(SUM(clicks),0) as total FROM `ad_daily_report` a LEFT JOIN ad_user b ON (b.id=a.user_id) WHERE b.agent_id=%s AND b.`is_del`=0 AND DAY BETWEEN %s AND %s GROUP BY DATE_FORMAT(DAY,'%%Y-%%m-%%d')" #%(self.agent_id,self.starttime,self.endtime)
else:
x_item_sql="SELECT DATE_FORMAT(b.DAY,'%%Y-%%m-%%d') as date,SUM(spend) as total FROM `ad_set` a LEFT JOIN ad_daily_report b ON (a.id=b.adset_id) LEFT JOIN ad_user c ON (c.id=b.user_id) WHERE c.agent_id=%s AND c.`is_del`=0 AND b.DAY BETWEEN %s AND %s group by DATE_FORMAT(b.DAY,'%%Y-%%m-%%d')" # %(self.agent_id,self.starttime,self.endtime)
if x_item =='sum_advertiser':
for i in range(1,30):
endtime= (datetime.datetime.now() - datetime.timedelta(days = i)).strftime("%Y-%m-%d")
line_data=self.db.query(x_item_sql,self.agent_id,endtime)
line_tmp[endtime]=line_data[0]['total']
else:
line_data=self.db.query(x_item_sql,self.agent_id,self.starttime,self.endtime)
for item in line_data:
line_tmp[item['date']]=item['total']
total_tag=True
for date in line_tmp.keys():
sql_data=line_tmp[date]
page_data=x_item_all[date]
self.contrast(sql_data,page_data)
if self.tag==True:
total_tag=True
else:
total_tag=False
break
if total_tag==True:
print x_item ,':曲线图验证通过'
else:
print x_item,':曲线图验证失败','数据库及页面数据分别为:',date,sql_data,page_data
except Exception,e:
print Exception, ":",e
def ader_overview(self):
try:
print "\n---start 验证单一广告主概览数\n"
ader_url='http://www.test.com/api/advertiser/dashboard?user_id=%s&chart_type=pv&start_time=%s&end_time=%s' %(self.user_id,self.starttime,self.endtime)
ader_text=self.session.get(ader_url,headers=self.headers).text
ader_data=json.loads(ader_text)['data']['detail']
view_campaign={}
view_ad={}
for item in ader_data:
if item.split('_')[0]=='ad':
view_ad[item.split('_')[1]]=ader_data[item]
elif item.split('_')[0]=='campaign':
view_campaign[item.split('_')[1]]=ader_data[item]
''' 广告主详情数据汇总及曲线图'''
'''广告系列概览数据'''
ader_data_campaign_sql="SELECT STATUS,COUNT(1) FROM `ad_campaign` a LEFT JOIN ad_user b ON(a.user_id=b.id) WHERE b.agent_id=%s AND a.is_del=0 AND DATE_FORMAT(a.create_time,'%%Y-%%m-%%d') BETWEEN %s AND %s GROUP BY STATUS"
ader_data_campaign_reult=self.db.query(ader_data_campaign_sql,self.agent_id,self.starttime,self.endtime)
view_campaign_sql={'COUNT':'0','ARCHIVED':'0','PAUSED':'0','PENDING_REVIEW':'0','DISAPPROVED':'0','DRAFT':'0','ARCHIVED':'0','DELETED':'0'}
view_campaign_sql['COUNT']=0
for item in ader_data_campaign_reult:
view_campaign_sql[item['STATUS']]=item['COUNT(1)']
view_campaign_sql['COUNT']=view_campaign_sql['COUNT']+item['COUNT(1)']
if (view_campaign_sql['COUNT']==view_campaign['count'] or\
view_campaign_sql['ARCHIVED']==view_campaign['archived'] or \
view_campaign_sql['PAUSED']==view_campaign['pause'] or \
view_campaign_sql['PENDING_REVIEW']==view_campaign['review'] or\
view_campaign_sql['DELETED']==view_campaign['delete'] or\
view_campaign_sql['DISAPPROVED']==view_campaign['disapprove'] or\
view_campaign_sql['DRAFT']==view_campaign['draft'] or \
view_campaign_sql['ARCHIVED']==view_campaign['active']):
print "广告主概览-广告系列验证通过"
else:
print "广告主概览-广告系列验证失败"
'''广告概览数据'''
ader_data_ad_sql="SELECT STATUS,\
COUNT(1) \
FROM `ad` a \
LEFT JOIN ad_user b \
ON(a.user_id=b.id) \
WHERE b.agent_id=%s and b.id=%s AND a.is_del=0 AND DATE_FORMAT(a.create_time,'%%Y-%%m-%%d') BETWEEN %s AND %s \
GROUP BY STATUS"
ader_data_ad_reult=self.db.query(ader_data_ad_sql,self.agent_id,self.user_id,self.starttime,self.endtime)
view_ad_sql={'COUNT':'0','ARCHIVED':'0','PAUSED':'0','PENDING_REVIEW':'0','DISAPPROVED':'0','DRAFT':'0','ARCHIVED':'0','DELETED':'0'}
view_ad_sql['COUNT']=0
for item in ader_data_ad_reult:
view_ad_sql[item['STATUS']]=item['COUNT(1)']
view_ad_sql['COUNT']=view_campaign_sql['COUNT']+item['COUNT(1)']
if (view_ad_sql['COUNT']==view_ad['count'] or\
view_ad_sql['ARCHIVED']==view_ad['archived'] or \
view_ad_sql['PAUSED']==view_ad['pause'] or \
view_ad_sql['PENDING_REVIEW']==view_ad['review'] or\
view_ad_sql['DELETED']==view_ad['delete'] or\
view_ad_sql['DISAPPROVED']==view_ad['disapprove'] or\
view_ad_sql['DRAFT']==view_ad['draft'] or \
view_ad_sql['ARCHIVED']==view_ad['active']):
print "广告主概览-广告数据验证通过"
else:
print "ERROR:广告主概览-广告数据验证失败"
''' 广告主详情数据汇总及曲线图'''
''' 广告主详情汇总'''
print "\n---start 验证单一广告主汇总数据\n"
ader_data_overview_detail=json.loads(ader_text)['data']['overview']
ader_data_overview_detail_sql="SELECT SUM(impressions) AS pv,\
ROUND(SUM(social_spend)/SUM(impressions)*1000,2) AS cpm,\
ROUND(SUM(social_spend),2) AS sum_cost,\
SUM(clicks) AS clicks,\
ROUND(SUM(social_spend)/SUM(clicks),2) AS cpc,\
SUM(total_actions) AS conv,\
ROUND(SUM(total_action_value)/SUM(total_actions),2) AS conv_cost\
FROM `ad_daily_report` a \
LEFT JOIN ad_user b \
ON (a.`user_id`=b.id)\
WHERE DAY BETWEEN %s AND %s AND b.id=%s AND b.`is_del`=0"
ader_data_overview_detail_sql_data=self.db.query(ader_data_overview_detail_sql,self.starttime,self.endtime,self.user_id)
detail_tag=True
for item in ader_data_overview_detail_sql_data[0].keys():
if ader_data_overview_detail_sql_data[0][item] == float(ader_data_overview_detail[item]):
detail_tag=True
else:
detail_tag=False
break
if detail_tag==True:
print '广告主概览-广告主细分数据汇总验证通过'
else:
print 'ERROR:广告主概览-广告主细分数据汇总' ,item ,'验证失败', ader_data_overview_detail_sql_data[0][item], float(ader_data_overview_detail[item])
except Exception,e:
print Exception, ":",e
''' 验证广告主的曲线图汇总数据'''
''' 数据库查询数据'''
print "\n---start 验证单一广告主曲线图数据\n"
try:
ader_detail_sql_tmp={}
for i in range(1,30):
date= (datetime.datetime.now() - datetime.timedelta(days = i)).strftime("%Y-%m-%d")
ader_detail_sql_tmp[date]=0
ader_data_overview_line_sql="SELECT day,\
SUM(impressions) AS pv,\
ROUND(SUM(social_spend)/SUM(impressions)*1000,2) AS cpm,\
ROUND(SUM(social_spend),2) AS sum_cost,\
SUM(clicks) AS clicks,\
ROUND(SUM(social_spend)/SUM(clicks),2) AS cpc,\
ROUND(SUM(clicks)/SUM(impressions)*100,2) AS ctr,\
SUM(total_actions) AS conv,\
ROUND(SUM(total_action_value)/SUM(total_actions),2) AS conv_cost\
FROM `ad_daily_report` a\
LEFT JOIN ad_user b \
ON (a.`user_id`=b.id)\
WHERE DAY BETWEEN %s AND %s AND user_id=%s AND b.`is_del`=0 group by day"
ader_data_overview_line_sql_data=self.db.query(ader_data_overview_line_sql,self.starttime,self.endtime,self.user_id)
for item_sql in ader_data_overview_line_sql_data:
ader_detail_sql_tmp[datetime.datetime.strftime(item_sql['day'],'%Y-%m-%d')]=item_sql
'''获取曲线图数据'''
for item_name in ader_data_overview_detail.keys():
print item_name,'start'
ader_detail_url='http://www.test.com/api/advertiser/dashboard?user_id=%s&chart_type=%s&start_time=%s&end_time=%s' %(self.user_id,item_name,self.starttime,self.endtime)
ader_detail_text=self.session.get(ader_detail_url,headers=self.headers).text
ader_detail_data=json.loads(ader_detail_text)['data']['line']
line_tmp={}
for i in range(len(ader_detail_data['series'][0]['data'])):
line_tmp[ader_detail_data['xdata'][i]]=ader_detail_data['series'][0]['data'][i]
line_tag=True
for line_date in ader_detail_sql_tmp.keys():
'''正向验证用数据库数据对比页面数据'''
if isinstance(ader_detail_sql_tmp[line_date],int):
if (line_tmp[line_date]==0 and ader_detail_sql_tmp[line_date]==0):
line_tag=True
else:
page_data=line_tmp[line_date]
sql_data=ader_detail_sql_tmp[line_date][item_name]
self.contrast(sql_data, page_data)
if self.tag==True:
line_tag=True
else:
line_tag=False
print ' ','ERROR:',line_date,page_data,sql_data
break
'''反向验证用页面数据对比数据库数据'''
if isinstance(line_tmp[line_date],int):
if (line_tmp[line_date]==0 and ader_detail_sql_tmp[line_date]==0):
line_tag=True
else:
page_data=line_tmp[line_date]
sql_data=ader_detail_sql_tmp[line_date][item_name]
self.contrast(sql_data, page_data)
if self.tag==True:
line_tag=True
else:
line_tag=False
print ' ','ERROR:',line_date,'数据异常分别为',page_data,sql_data
break
if line_tag==True:
print ' 广告主汇总',item_name,'曲线图 验证通过'
else:
print '广告主汇总',item_name,'曲线图 验证失败'
except Exception,e:
print Exception, ":",e
def ader_campaignlist(self):
print '\n---start 再投广告系列开始测试......'
try:
get_list_url = 'http://www.test.com/api/advertiser/campaignlist?user_id=%s&status=%%5B%%22ACTIVE%%22%%5D' % (self.user_id)
get_list_data_tmp = self.session.get(get_list_url, headers=self.headers).text
get_list_data = json.loads(get_list_data_tmp)
if get_list_data['total'] !=0:
campaign_list = {}
for item in get_list_data['data']:
campaign_list[item['name']] = item
else:
print "无数据"
except Exception,e:
print Exception, ":",e,' is wrong'
try:
get_list_sql="SELECT b.id,b.name,b.`start_time` ,b.create_time,b.status,b.update_time,b.`end_time`,\
IFNULL(SUM(c.spend),0) as spend,\
IFNULL(ROUND(SUM(social_spend),2) ,0)AS sum_cost,\
IFNULL(SUM(impressions),0)AS pv,\
IFNULL(ROUND(SUM(social_spend)/SUM(impressions)*1000,2),0) AS cpm,\
IFNULL(SUM(clicks),0)AS clicks,\
IFNULL(ROUND(SUM(clicks)/SUM(impressions)*100,2),'-') AS ctr,\
IFNULL(ROUND(SUM(social_spend)/SUM(clicks),2),0) AS cpc,\
IFNULL(SUM(total_actions),0) AS conv, \
IFNULL(ROUND(SUM(total_action_value)/SUM(total_actions),2),0) AS conv_cost \
FROM ad_user a \
LEFT JOIN ad_campaign b \
ON (a.id=b.`user_id`) \
LEFT JOIN ad_daily_report c \
ON(c.`campaign_id`=b.id) \
WHERE a.`agent_id`=%s AND a.is_del=0 AND b.`is_del`=0 AND b.`status`='ACTIVE' GROUP BY b.name,b.`start_time`,b.`end_time`"
get_list_sql_data=self.db.query(get_list_sql,self.agent_id,)
campaign_list_sql={}
for item in get_list_sql_data:
campaign_list_sql[item['name']]=item
for campaign_name in campaign_list.keys():
print campaign_name,'开始对比...\n'
for campaign_name_item in campaign_list[campaign_name].keys():
page_data=campaign_list[campaign_name][campaign_name_item]
sql_data=campaign_list_sql[campaign_name][campaign_name_item]
self.contrast(sql_data, page_data)
if self.tag==True:
print 'pass', campaign_name_item,'验证通过'
else:
print campaign_name_item,'验证失败,数据分别为:',sql_data,page_data
# if campaign_list_sql[campaign_name]==campaign_list[campaign_name]:
except Exception,e:
print Exception, ":",e
def all_campaign_list(self):
print '\n---start 所有系列开始测试......\n'
try:
all_campaign_list_url='http://www.test.com/api/advertiser/campaignlist?user_id=%s&start_time=%s&end_time=%s&filter=%%5B%%5D&status=%%5B%%5D&offset=0&limit=8' %(self.user_id,self.starttime,self.endtime)
self.all_campaign_list_data=self.session.get(all_campaign_list_url,headers=self.headers).text
list_data_tmp={}
for list_data in json.loads(self.all_campaign_list_data)['data']:
list_data_tmp[list_data['name']]=list_data
all_campaign_list_sql="SELECT a.name,a.`status`,a.`start_time`,\
IFNULL(a.`end_time`,'Null')AS end_time,\
IFNULL(SUM(b.spend),0) AS spend,\
IFNULL(ROUND(SUM(social_spend),2),0) AS sum_cost,\
IFNULL(SUM(impressions),0) AS pv,\
IFNULL(ROUND(SUM(social_spend)/SUM(impressions)*1000,2),0) AS cpm,\
IFNULL(SUM(clicks),0)AS clicks,\
IFNULL(ROUND(SUM(clicks)/SUM(impressions)*100,2),'-')AS ctr,\
IFNULL(ROUND(SUM(social_spend)/SUM(clicks),2),0) AS cpc,\
IFNULL(SUM(total_actions),0) AS conv,\
IFNULL(ROUND(SUM(total_action_value)/SUM(total_actions),2),0) AS conv_cost\
FROM ad_campaign a\
LEFT JOIN ad_daily_report b\
ON (a.`id`=b.campaign_id and b.day between %s AND %s )\
LEFT JOIN ad_user c\
ON (a.user_id=c.id)\
WHERE c.`agent_id`=%s AND c.id=%s AND a.`status`!='draft' \
AND (\
(a.start_time <= %s AND a.end_time >=%s) \
OR (a.start_time BETWEEN %s AND %s OR a.end_time BETWEEN %s AND %s)\
OR (a.start_time IS NOT NULL AND a.end_time IS NULL)\
)\
GROUP BY a.name,a.`status`,a.`start_time`,a.`end_time`"#%(self.agent_id,self.user_id,self.starttime,self.endtime,self.starttime,self.endtime,self.starttime,self.endtime)
all_campaign_list_sql_data=self.db.query(all_campaign_list_sql,self.starttime,self.endtime,self.agent_id,self.user_id,self.starttime,self.endtime,self.starttime,self.endtime,self.starttime,self.endtime)
sql_data_tmp={}
for sql_data in all_campaign_list_sql_data:
sql_data_tmp[sql_data['name']]=sql_data
list_tag=True
for cp_name in sql_data_tmp.keys():
print cp_name,'\n'
for item in sql_data_tmp[cp_name]:
sql_data=sql_data_tmp[cp_name][item]
if list_data_tmp[cp_name][item]==None:
page_data='Null'
else:
page_data=list_data_tmp[cp_name][item]
self.contrast(sql_data, page_data)
if self.tag==True:
list_tag=True
# print cp_name,'验证通过'
else:
list_tag=False
print cp_name,'-',item,'验证失败','sql:',sql_data,'page:',page_data
if list_tag==True:
print cp_name,'：验证通过'
else:
print cp_name,'：验证失败'
if list_tag==True:
print '\n','所有广告系列验证通过\n'
else:
print '\n', '所有广告系列验证失败!!!\n'
except Exception,e:
print Exception, ":",e
def all_ad(self):
print "\n....start 验证某一系列下全部广告.....\n"
try:
campaign_list=json.loads(self.all_campaign_list_data)['data']
for i in range(len(campaign_list)):
campaign_id=campaign_list[i]['id']
campaign_name=campaign_list[i]['name']
all_add_url="http://www.test.com/api/advertiser/adlist?user_id=%s&start_time=%s&end_time=%s&&campaign_id=%s&filter=%%5B%%5D&status=%%5B%%5D&offset=0&limit=8" %(self.user_id,self.starttime,self.endtime,campaign_id)
ad_data_tmp=json.loads(self.session.get(all_add_url,headers=self.headers).text)
print '\n',campaign_name,' : 开始验证\n'
ad_total=ad_data_tmp['total']
page_ad_name=[]
page_ad_data={}
if ad_total==0:
page_ad_name=[]
else:
ad_data=ad_data_tmp['data']
for item in ad_data:
page_ad_name.append(item['ad_name'])
page_ad_data[item['ad_name']]=item
ad_sql="SELECT b.`campaign_id`,a.name,a.`status`,b.`start_time`,\
IFNULL(b.`end_time`,'')AS end_time,\
IFNULL(SUM(d.spend),0) AS spend,\
IFNULL(ROUND(SUM(social_spend),2),0) AS sum_cost,\
IFNULL(SUM(impressions),0) AS pv,\
IFNULL(ROUND(SUM(social_spend)/SUM(impressions)*1000,5),0) AS cpm,\
IFNULL(SUM(clicks),0)AS clicks,\
IFNULL(ROUND(SUM(clicks)/SUM(impressions)*100,2),'-')AS ctr,\
IFNULL(ROUND(SUM(social_spend)/SUM(clicks),2),0) AS cpc,\
IFNULL(SUM(total_actions),0) AS CONV,\
IFNULL(ROUND(SUM(total_action_value)/SUM(total_actions),2),0) AS conv_cost\
FROM ad a\
LEFT JOIN ad_set b\
ON (a.adset_id=b.id)\
LEFT JOIN ad_daily_report d\
ON (a.`id`=d.ad_id AND d.day BETWEEN %s AND %s)\
LEFT JOIN ad_user c\
ON (a.user_id=c.id)\
WHERE c.`agent_id`=%s AND c.id=%s AND a.`status`<>'draft'\
AND (\
(b.start_time <= %s AND b.end_time >=%s) \
OR (b.start_time BETWEEN %s AND %s OR b.end_time BETWEEN %s AND %s) \
OR (b.start_time <=%s AND b.end_time IS NULL))\
AND b.`campaign_id`=%s \
GROUP BY b.`campaign_id`,a.name,a.`status`,b.`start_time`,b.`end_time`"
ad_sqldata_tmp=self.db.query(ad_sql,self.starttime,self.endtime,self.agent_id,self.user_id, self.starttime,self.endtime,self.starttime,self.endtime,self.starttime,self.endtime,self.endtime,campaign_id)
sql_ad_name=[]
sql_ad_data={}
if len(ad_sqldata_tmp)==0:
sql_ad_name=[]
else:
for item in ad_sqldata_tmp:
sql_ad_name.append(item['name'])
sql_ad_data[item['name']]=item
if (ad_total==0 and len(ad_sqldata_tmp)==0):
print campaign_name,'旗下无广告'
else:
re_tag=True
if sql_ad_name==page_ad_name:
for ad_name in sql_ad_name:
for item in sql_ad_data[ad_name]:
sql_data=sql_ad_data[ad_name][item]
page_data=page_ad_data[ad_name][item]
self.contrast(sql_data, page_data)
if self.tag==True:
re_tag=True
else:
re_tag=False
print ad_name,item,'验证失败','\n'
break
else:
re_tag=False
print campaign_name,'系列旗下广告数目不匹配','page:',ad_total,'sql:',len(ad_sqldata_tmp),'\n'
if re_tag==True:
print campaign_name,'系列下所有广告验证通过'
else:
print campaign_name,'系列下所有广告验证失败'
except Exception,e:
print Exception, ":",e
'''公用方法判断sql查询的数据和页面的数据'''
def contrast(self,sql_data,page_data):
self.tag=True
if isinstance(sql_data,unicode):
if sql_data==page_data:
self.tag=True
# print 'p1'
else:
self.tag=False
# print 'F1'
elif isinstance(sql_data,datetime.date):
if str(sql_data)==page_data:
self.tag=True
# print 'p2'
else:
self.tag=False
# print 'F2'
elif isinstance(sql_data,(float,long,decimal.Decimal)):
if sql_data==float(page_data):
self.tag=True
# print 'p3'
else:
self.tag=False
# print 'F3'
else:
if sql_data==page_data:
self.tag=True
# print 'p4'
else:
self.tag=False
# print 'F4'
return self.tag
已经尽可能隐藏了关键信息，如果有遗漏且您发现了，请即刻告知我，不胜感激。
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
