def set_init_data(self,today_pre,today):
revenue_list=self.get(True)
if revenue_list is not None:
if today_pre not in revenue_list:
sqlres=DB_G.query("select sum(total_num) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' and ad_status=14 and campaign_id<1000000" % (today_pre,today))
qres=sqlres[0]
m_ins=int(qres['num'])
sqlres=DB_G.query("select sum(total_num) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' and ad_status=14" % (today_pre,today))
qres=sqlres[0]
t_ins=int(qres['num'])
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s'" % (today_pre,today))
qres=sqlres[0]
t_cov=qres['num']
t_rev=qres['rev']
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s' and campaign_id<1000000" % (today_pre,today))
qres=sqlres[0]
m_cov=qres['num']
m_rev=qres['rev']
m_cov_r=CUtils.rate_it(m_cov,m_ins)
a_cov_r=CUtils.rate_it(t_cov-m_cov,t_ins-m_ins)
revenue_list[today_pre]={"t_rev":t_rev,"a_rev":t_rev-m_rev,"m_rev":m_rev,"t_cov":t_cov,"a_cov":t_cov-m_cov,"m_cov":m_cov,"m_cov_r":m_cov_r,"a_cov_r":a_cov_r,"t_ins":t_ins,"a_ins":t_ins-m_ins,"m_ins":m_ins}
else:
revenue_list={}
sqlres=DB_G.query("select sum(total_num) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' and ad_status=14 and campaign_id<1000000" % (today_pre,today))
qres=sqlres[0]
m_ins=int(qres['num'])
sqlres=DB_G.query("select sum(total_num) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' and ad_status=14" % (today_pre,today))
qres=sqlres[0]
t_ins=int(qres['num'])
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s'" % (today_pre,today))
qres=sqlres[0]
t_cov=qres['num']
t_rev=qres['rev']
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s' and campaign_id<1000000" % (today_pre,today))
qres=sqlres[0]
m_cov=qres['num']
m_rev=qres['rev']
m_cov_r=CUtils.rate_it(m_cov,m_ins)
a_cov_r=CUtils.rate_it(t_cov-m_cov,t_ins-m_ins)
revenue_list[today_pre]={"t_rev":t_rev,"a_rev":t_rev-m_rev,"m_rev":m_rev,"t_cov":t_cov,"a_cov":t_cov-m_cov,"m_cov":m_cov,"m_cov_r":m_cov_r,"a_cov_r":a_cov_r,"t_ins":t_ins,"a_ins":t_ins-m_ins,"m_ins":m_ins}
self.set(revenue_list)
def get(self):
offer_info=[]
sql="select T2.* from adex_campaign T1 LEFT JOIN adex_ironsouce_campaign T2 on T1.parterner_campaign_id=T2.campaign_id where T1.campaign_id=%s and T2.is_valid=1 order by T2.create_time" % self.id
sqlres=DB3_G.query(sql)
for offres in sqlres:
offer_info.append(offres)
return offer_info
def read_from_db(self,subwhere=None):
today=self.date
timespan=timedelta(days=1)
today_date=datetime.strptime(today, "%Y-%m-%d")
today_pre=(today_date-timespan).strftime("%Y-%m-%d")
print today
sqlres=DB_G.query("select sum(total_num) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' and ad_status=14 and campaign_id<1000000" % (today_pre,today))
qres=sqlres[0]
m_ins=int(CUtils.none2zero(qres['num']))
sqlres=DB_G.query("select sum(total_num) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' and ad_status=14" % (today_pre,today))
qres=sqlres[0]
t_ins=int(CUtils.none2zero(qres['num']))
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s'" % (today_pre,today))
qres=sqlres[0]
t_cov=int(CUtils.none2zero(qres['num']))
t_rev=CUtils.none2zero(qres['rev'])
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s' and campaign_id<1000000" % (today_pre,today))
qres=sqlres[0]
m_cov=int(CUtils.none2zero(qres['num']))
m_rev=CUtils.none2zero(qres['rev'])
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where create_time>'%s' and create_time<'%s' and source_type=1" % (today_pre,today))
qres=sqlres[0]
r_cov=int(CUtils.none2zero(qres['num']))
r_rev=CUtils.none2zero(qres['rev'])
m_cov_r=CUtils.rate_it(m_cov,m_ins)
a_cov_r=CUtils.rate_it(t_cov-m_cov,t_ins-m_ins)
revenue_report={"t_rev":t_rev,"a_rev":t_rev-m_rev,"m_rev":m_rev,"r_rev":r_rev,"t_cov":t_cov,"a_cov":t_cov-m_cov,"m_cov":m_cov,"r_cov":r_cov,"m_cov_r":m_cov_r,"a_cov_r":a_cov_r,"t_ins":t_ins,"a_ins":t_ins-m_ins,"m_ins":m_ins}
return revenue_report
def read_from_db(self):
date_report={}
date_report["new_user"]=0
date_report["active_user"]=0
date_report["total_impression"]=0
date_report["total_impression_unique"]=0
date_report["total_click"]=0
date_report["total_click_unique"]=0
date_report["total_download"]=0
date_report["total_download_unique"]=0
date_report["total_install"]=0
date_report["total_install_unique"]=0
date_report["auto_install"]=0
date_report["manual_install"]=0
date_report["auto_conversion"]=0
date_report["manual_conversion"]=0
date_report["auto_rev"]=0
date_report["manual_rev"]=0
date_report["div1000_ip"]=0
date_report["div100_ip"]=0
date_report["rob_conversion"]=0
date_report["rob_rev"]=0
today=self.date
timespan=timedelta(days=1)
today_date=datetime.strptime(today, "%Y-%m-%d")
timespan_long=timedelta(days=30)
today_long_pre=today_date-timespan_long
#datetime.strptime(today-timespan_long, "%Y-%m-%d")
if today_date<datetime.today() and today_date>today_long_pre:
today_pre=(today_date-timespan).strftime("%Y-%m-%d")
sqlres=DB_G.query("select ifnull(sum(total_num),0) as num,ifnull(sum(unicode_count),0) as unum from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' %s and ad_status=11" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["total_download"]=int(qres['num'])
date_report["total_download_unique"]=int(qres['unum'])
else:
date_report["total_download"]=0
date_report["total_download_unique"]=0
sqlres=DB_G.query("select ifnull(sum(total_num),0) as num,ifnull(sum(unicode_count),0) as unum from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' %s and ad_status=14" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["total_install"]=int(qres['num'])
date_report["total_install_unique"]=int(qres['unum'])
else:
date_report["total_install"]=0
date_report["total_install_unique"]=0
sqlres=DB_G.query("select ifnull(sum(total_num),0) as num,ifnull(sum(unicode_count),0) as unum from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' %s and ad_status=19" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["total_impression"]=int(qres['num'])
date_report["total_impression_unique"]=int(qres['unum'])
else:
date_report["total_impression"]=0
date_report["total_impression_unique"]=0
sqlres=DB_G.query("select ifnull(sum(total_num),0) as num,ifnull(sum(unicode_count),0) as unum from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' %s and ad_status=20" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["total_click"]=int(qres['num'])
date_report["total_click_unique"]=int(qres['unum'])
else:
date_report["total_click"]=0
date_report["total_click_unique"]=0
sqlres=DB_G.query("select ifnull(sum(total_num),0) as num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<'%s' %s and ad_status=14 and campaign_id<1000000" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["manual_install"]=int(qres['num'])
date_report["auto_install"]=date_report["total_install"]-date_report["manual_install"]
else:
date_report["manual_install"]=0
date_report["auto_install"]=0
sqlres=DB_G.query("select count(client_id) as num from sdk2.sdk_clients where create_time>='%s' and create_time<'%s' %s" % (today_pre,today,self.subwhere))
if len(sqlres)>0:
qres=sqlres[0]
date_report["new_user"]=qres['num']
else:
date_report["new_user"]=0
#print "DateReport:new user:%s" % subwhere
sqlres=DB_G.query("select count(distinct(client_id)) as num from ad_pull_log where cur_day='%s' %s" % (today_pre,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["active_user"]=qres['num']
else:
date_report["active_user"]=0
div1000=max(date_report["active_user"]/10000,1)
div100=max(date_report["active_user"]/1000,1)
div1000_sum=0
div100_sum=0
if len(self.subwhere)>1:
sqlres=DB_G.query("select client_ip,count(client_id) as num from ad_pull_log where cur_day='%s' %s group by client_ip order by count(client_id) desc limit 0,%s" % (today_pre,self.subwhere,div100))
for i,qres in enumerate(sqlres):
div100_sum+=qres['num']
if i==(div1000-1):
div1000_sum=div100_sum
date_report["div1000_ip"]=div1000_sum/div1000
date_report["div100_ip"]=div100_sum/div100
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where campaign_id>=1000000 and create_time>'%s' and create_time<'%s' %s" % (today_pre,today,self.subwhere))
if len(sqlres)>0:
qres=sqlres[0]
date_report["auto_conversion"]=qres['num']
if qres['rev'] is not None:
date_report["auto_rev"]=qres['rev']
else:
date_report["auto_rev"]=0
else:
date_report["auto_conversion"]=0
date_report["auto_rev"]=0
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where campaign_id<1000000 and create_time>'%s' and create_time<'%s' %s" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["manual_conversion"]=qres['num']
if qres['rev'] is not None:
date_report["manual_rev"]=qres['rev']
else:
date_report["manual_rev"]=0
else:
date_report["manual_conversion"]=0
date_report["manual_rev"]=0
sqlres=DB3_G.query("select count(DISTINCT(guid)) as num,sum(bid) as rev from adex_postback_partner where source_type=1 and create_time>'%s' and create_time<'%s' %s" % (today_pre,today,self.subwhere))
if(len(sqlres)>0):
qres=sqlres[0]
date_report["rob_conversion"]=qres['num']
if qres['rev'] is not None:
date_report["rob_rev"]=qres['rev']
else:
date_report["rob_rev"]=0
else:
date_report["rob_conversion"]=0
date_report["rob_rev"]=0
else:
date_report=None
#print "DateReport:active user:%s" % subwhere
return date_report
def read_from_db(self):
offer_active_list={}
today=self.date
timespan=timedelta(days=1)
today_date=datetime.strptime(today, "%Y-%m-%d")
today_pre=(today_date-timespan).strftime("%Y-%m-%d")
sql="select T2.package_name,T1.partner,T1.campaign_id,count(0) as cov,sum(T1.bid) as rev,T1.app_id,T1.country_code from adex_postback_partner T1 inner join adex_campaign T2 on T1.campaign_id=T2.campaign_id where T1.create_time>='%s' and T1.create_time<'%s' and T1.campaign_id>=1000000 group by T2.package_name,T1.country_code,T1.app_id order by sum(T1.bid) desc limit 0,500" % (today_pre,today)
sqlres=DB3_G.query(sql)
camp_list={}
for offres in sqlres:
ores_temp={}
ores_temp['campaign_id']=offres['campaign_id']
ores_temp['partner']=offres['partner']
ores_temp['app_id']=offres['app_id']
ores_temp['country_code']=offres['country_code']
ores_temp['cov']=offres['cov']
ores_temp['rev']=offres['rev']
ores_temp['dld']=0
ores_temp['clc']=0
ores_temp['imp']=0
ores_temp['ins']=0
sql="select ad_status,total_num from ad_stats.ad_daily_report where sta_date>='%s' and sta_date<='%s' and app_id='%s' and country_code='%s' and campaign_id='%s' and ad_status in (14,11,19,20)" % (today_pre,today,offres['app_id'],offres['country_code'],offres['campaign_id'])
sqlres2=DB_G.query(sql)
for ores in sqlres2:
if int(ores['ad_status'])==11:
ores_temp['dld']=int(ores['total_num'])
elif int(ores['ad_status'])==19:
ores_temp['imp']=int(ores['total_num'])
elif int(ores['ad_status'])==20:
ores_temp['clc']=int(ores['total_num'])
elif int(ores['ad_status'])==14:
ores_temp['ins']=int(ores['total_num'])
if offres['package_name'] in offer_active_list:
offer_active_list[offres['package_name']]['cov']+=ores_temp['cov']
offer_active_list[offres['package_name']]['rev']+=ores_temp['rev']
offer_active_list[offres['package_name']]['ins']+=ores_temp['ins']
offer_active_list[offres['package_name']]['clc']+=ores_temp['clc']
offer_active_list[offres['package_name']]['dld']+=ores_temp['dld']
offer_active_list[offres['package_name']]['imp']+=ores_temp['imp']
#if(len(offer_active_list[offres['package_name']]['camp_list'])<9):
# offer_active_list[offres['package_name']]['camp_list'].append(ores_temp)
camp_list[offres['package_name']].append(ores_temp)
else:
offer_active_list[offres['package_name']]={}
camp_list[offres['package_name']]=[]
offer_active_list[offres['package_name']]['cov']=ores_temp['cov']
offer_active_list[offres['package_name']]['rev']=ores_temp['rev']
offer_active_list[offres['package_name']]['ins']=ores_temp['ins']
offer_active_list[offres['package_name']]['clc']=ores_temp['clc']
offer_active_list[offres['package_name']]['dld']=ores_temp['dld']
offer_active_list[offres['package_name']]['imp']=ores_temp['imp']
camp_list[offres['package_name']].append(ores_temp)
for key,val in camp_list.items():
camp_info=CampInfo(key,self.date,val)
camp_info.set(val)
#offer_active_list[offres['package_name']]['camp_list']=[]
#offer_active_list[offres['package_name']]['camp_list'].append(ores_temp)
#CampInfo(offres['package_name'],self.date,ores_temp)
return offer_active_list