import helper_regex
from helper_mysql import db
import _mysql
view_ids=[]
db.query(r'''
SELECT *
FROM (
SELECT *
FROM VIEW WHERE name like '%Telk_Armor Daily Paid User%'
) view
ORDER BY id ASC
''')
result_view = db.store_result()
columns_to_rename={
'总有效包日用户数':'Effective Daily Subscribers',
'总有效包周用户数':'Effective Weekly Subscribers',
'总有效包月用户数':'Effective Monthly Subscribers',
'新增包日用户':'New Daily Package Subscriptions',
'退订包日用户':'Daily Package Unsubscription',
'新增包周用户':'New Weekly Package Subscriptions',
'退订包周用户':'Weekly Package Unsubscription',
'新增包月用户':'New Monthly Package Subscriptions',
'退订包月用户':'Monthly Package Unsubscription',
'在线用户峰值':'Peak Online User',
'平均数据流量 kB (基于总有效用户)':'GPRS Data per User per Day',
'历史总用户（含退订）':'Total User Including Unsub User',
def do(target_date,patch_date_start,patch_date_end,sql):
add_offset_as_a_flag_for_trace='0'
# define data template to guide patch
sql=sql % (patch_date_end,)
db.query(sql)
result_view = db.store_result()
while 1:
row_view = result_view.fetch_row(how=2)
if not row_view:
break
row_view=row_view[0]
print str(row_view)
sql=(r'''
update raw_data
set `key`=concat(`key`,'_expired')
where
`oem_name`='%s'
and `category`='%s'
and `key`='%s'
and `sub_key`='%s'
and `date`='%s'
limit 1
''' % (row_view['raw_data.oem_name'],row_view['raw_data.category'],row_view['raw_data.key'],row_view['raw_data.sub_key'].replace("\'","\\\'"),target_date))
print sql
db.query(sql)
sql=(r'''
insert into raw_data (
`oem_name`
,`category`
,`key`
,`sub_key`
,`date`
,`value`
) select
'%s' as `oem_name`
,'%s' as `category`
,'%s' as `key`
,'%s' as `sub_key`
,'%s' as `date`
,ceil(1.0*sum(if(value is null,0,value))/count(*)) + %s as `value`
from raw_data
where
`oem_name`='%s'
and `category`='%s'
and `key`='%s'
and `sub_key`='%s'
and `date`>='%s' and `date`<='%s'
''' % (row_view['raw_data.oem_name'],row_view['raw_data.category'],row_view['raw_data.key'],row_view['raw_data.sub_key'].replace("\'","\\\'"),target_date,add_offset_as_a_flag_for_trace, \
row_view['raw_data.oem_name'],row_view['raw_data.category'],row_view['raw_data.key'],row_view['raw_data.sub_key'].replace("\'","\\\'"),patch_date_start,patch_date_end))
print sql
db.query(sql)
current_date=helper_regex.date_add(target_date_start,offset)
print 'current_date: '+current_date
#get reference value of target date
reference_value_target=helper_mysql.get_one_value_string(
sql_reference_columns_tpl
% (current_date,current_date))
print 'reference_value_target: '+reference_value_target
#get base columns
db.query(sql_base_columns)
result_view = db.store_result()
while 1:
row_view = result_view.fetch_row(how=1)
if not row_view:
break
row_view=row_view[0]
print str(row_view)
#exit()
sql=r'''
update `%s`
set `key`=concat(`key`,'_expired')