def merge_ho_ct(d_cid, d_mid, s_cid, s_mid):
insert_ho_sql = '''
INSERT INTO
msg_%s_%s_ho (`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`, `op_system`, `browser`)
select
`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`, `op_system`, `browser`
from
%s.msg_%s_%s_ho ;
'''
insert_ct_sql = '''
INSERT INTO
`msg_%s_%s_ct` (`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `url_id`, `url_value`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`)
select
`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `url_id`, `url_value`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`
from
%s.msg_%s_%s_ct ;
'''
# report_conn = mdb.get_report_conn(d_cid)
# report_conn = MySQLdb.connect( host='10.10.11.202', user='******', passwd='EpCAre!@#', db='reportDB_new' )
reportdb = grid.get_report_conn(d_cid)
report_conn = MySQLdb.connect( host= mdb_ip, user= mdb_user, passwd= mdb_pass, db=reportdb ,charset='utf8')
cursor = report_conn.cursor()
# reportdb = 'reportDB_new'
print insert_ho_sql % (d_cid, d_mid, reportdb, s_cid, s_mid)
print insert_ct_sql % (d_cid, d_mid, reportdb, s_cid, s_mid)
cursor.execute(insert_ho_sql % (d_cid, d_mid, reportdb, s_cid, s_mid))
cursor.execute(insert_ct_sql % (d_cid, d_mid, reportdb, s_cid, s_mid))
report_conn.commit()
cursor.close()
report_conn.close()
def insert_ho_ct(cid, mid, action_time, ho_dist_num, ho_num, ct_dist_num, ct_num):
domain_name = 'qq.com'
insert_ho_sql = '''INSERT INTO msg_%s_%s_ho (`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`, `op_system`, `browser`) values ''' % (cid, mid)
insert_ho_sql += '''( %s, %s, 35811, 2, %s, 1, %s, %s, %s, '58.23.3.163', 974586787, 214, '中国', '福建', '厦门市', NULL, 'Windows 7', 'Unknown' );'''
insert_ct_sql = '''INSERT INTO msg_%s_%s_ct (`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `url_id`, `url_value`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`) values ''' % (cid, mid)
insert_ct_sql += '''(%s, %s, 35811, 2, %s, 1, %s, %s, %s, %s, %s, '58.23.3.163', 974586787, 214, '中国', '福建', '厦门市', NULL );'''
reportdb = grid.get_report_conn(cid)
report_conn = MySQLdb.connect( host=mdb_ip, user=mdb_user, passwd=mdb_pass, db=reportdb ,charset='utf8')
cursor = report_conn.cursor()
url = geturl(mid)
url_id = url['url_id']
url_value = (url['value']).encode('utf-8')
src_data = src_dist_ho_ct(mid)
src_dist_ho = int(src_data['dist_opens'])
src_ho_num = int(src_data['html_opens'])
src_dist_ct = int(src_data['dist_url_clicks'])
src_ct_num = int(src_data['url_clicks'])
if src_dist_ho >= ho_dist_num:
ho_dist_diff = 1
else:
ho_dist_diff = ho_dist_num - src_dist_ho
if src_ho_num >= ho_num:
ho_diff = 1
else:
ho_diff = ho_num - src_ho_num
if src_dist_ct >= ct_dist_num:
ct_dist_diff = 1
else:
ct_dist_diff = ct_dist_num - src_dist_ct
if src_ct_num >= ct_num:
ct_diff = 1
else:
ct_diff = ct_num - src_ct_num
ho_args_list = []
for i in range(ho_dist_diff):
unique_id = string.join(random.sample('0123456789abcdefghijklmnopqrstuvwxyz',8)).replace(" ","")
email_address = '*****@*****.**' % i
ho_args_list.append((unique_id, mid, cid, email_address, domain_name, action_time))
for i in range(ho_diff):
unique_id = 'h6djz8c5'
email_address = '*****@*****.**' % i
ho_args_list.append((unique_id, mid, cid, email_address, domain_name, action_time))
ct_args_list = []
for i in range(int(ct_dist_diff)):
unique_id = string.join(random.sample('0123456789abcdefghijklmnopqrstuvwxyz',8)).replace(" ","")
email_address = '*****@*****.**' % i
ct_args_list.append((unique_id, mid, cid, email_address, domain_name, url_id, url_value, action_time))
for i in range(int(ct_diff)):
unique_id = 'h6djz8c5'
email_address = '*****@*****.**' % i
ct_args_list.append((unique_id, mid, cid, email_address, domain_name, url_id, url_value, action_time))
try:
cursor.executemany(insert_ho_sql, ho_args_list)
cursor.executemany(insert_ct_sql, ct_args_list)
report_conn.commit()
except Exception,e:
report_conn.rollback()
print e
def insert_to_hoct(cid, mid, data_file):
unique_id = string.join(random.sample('0123456789abcdefghijklmnopqrstuvwxyz',8)).replace(" ","")
ho_args_array, ct_args_array = read_info(mid, data_file)
insert_ho_sql = '''INSERT INTO msg_%s_%s_ho (`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`, `op_system`, `browser`) values ''' % (cid, mid)
insert_ho_sql += '''( %s, %s, 35811, 2, %s, 1, %s, %s, %s, %s, 974586787, %s, %s, %s, %s, NULL, 'Windows 7', 'Unknown' );'''
insert_ct_sql = '''INSERT INTO msg_%s_%s_ct (`unique_id`, `message_id`, `list_id`, `subscriber_id`, `client_id`, `user_id`, `email_address`, `domain_name`, `url_id`, `url_value`, `action_time`, `ip`, `ip_long`, `place_id`, `country`, `province`, `city`, `point`) values ''' % (cid, mid)
insert_ct_sql += '''(%s, %s, 35811, 2, %s, 1, %s, %s, %s, %s, %s, %s, 974586787, %s, %s, %s, %s, NULL );'''
reportdb = grid.get_report_conn(cid)
report_conn = MySQLdb.connect( host=mdb_ip, user=mdb_user, passwd=mdb_pass, db=reportdb ,charset='utf8')
cursor = report_conn.cursor()
try:
cursor.executemany(insert_ho_sql, ho_args_array)
cursor.executemany(insert_ct_sql, ct_args_array)
report_conn.commit()
except Exception,e:
report_conn.rollback()
print e
