def getmid(lid):
sql_str = '''
select
message_id
from
message_list ml
where
ml.list_id = %s
'''
global_conn = mdb.get_global_conn()
message_id = mdb.exe_sql(global_conn, sql_str % lid, False, True)
return message_id[0][0]
def getcid(mid):
sql_str = '''
select
m.client_id, c.client_name
from
message m, client c
where
m.client_id = c.client_id and m.message_id = %s
'''
global_conn = mdb.get_global_conn();
client_id = mdb.exe_sql(global_conn, sql_str % mid, False, True)
return client_id[0][0]
def get_group_mail(group_id):
get_group_sql = '''
select
mg.group_name,m.client_id,group_concat(m.message_id) as messages
from
message m, message_group mg
where
mg.group_id = m.group_id and m.group_id = %s
group by
m.group_id;
'''
global_conn = mdb.get_global_conn();
datas = mdb.exe_sql(global_conn, get_group_sql % (group_id), True, True);
return datas
def getUserInfo(cid=None, email=None):
get_user_info = '''
select
u.client_id, u.email_address, u.password, c.client_name, c.branch_id
from
users u,client c
where
u.client_id = c.client_id and
'''
if cid:
get_user_info += ''' u.client_id = '%s' ''' % cid
if email :
get_user_info += '''u.email_address = '%s' ''' % email
global_conn = mdb.get_global_conn();
datas = mdb.exe_sql(global_conn, get_user_info, True, True)
return datas
def insertMachine(ip, server_type):
sql_machine = '''
INSERT INTO `machine` (`ip`, `ssh_port`, `machine_group`, `passwd`, `remark`) VALUES
("%s", 22, "%s", 'WcLPHVvGyY327E8fRSlGRV14MBuM0skG0tN/mPpmxvX/L+YyTlSPzWGq/BIhtmyTvPAwqX4ewszLdySr1uqN6q3otreTNBja0pcB2FREdFjzcX31KjuX9u4F6UNUjZHk+bQ7w4fUDQ5VEhmVASsxxtIk/xouRFLPL2LkFagi4OI=', now());
'''
sql_service = '''
INSERT INTO `service` (`server_id`,`machine_ip`,`server_type`,`server_port`,`status`,`pressure`,`launch_on`,`shutdown_on`,`refresh_on`,`doSuspend`,`doStop`) VALUES
("%s", "%s", "%s", %s, 'down', 0, now(), now(), now(), 1, 1);
'''
port=service_dict[server_type]
server_id=ip.replace('.','')+port
try:
global_conn = mdb.get_global_conn();
mdb.exe_insert_sql(global_conn, sql_machine % (ip, server_type), False, False)
mdb.exe_insert_sql(global_conn, sql_service % (server_id, ip, server_type, port), False, True)
except Exception,e:
print "Insert machine info failed!"
sys.exit()
def get_bounceinfo(cid, mid, email=None, test = False):
get_data = '''
select
*
from
msg_%s_%s_h h
where
h.email = '%s'
'''
get_test_data = '''
select
*
from
msg_tester_result mr
where
mr.message_id = %s
'''
if email :
get_test_data += " and mr.email_address = '%s'" % (email)
if test :
sql = get_test_data % (mid)
global_conn = mdb.get_global_conn()
datas = mdb.exe_sql(global_conn, sql, True, True)
else :
sql = get_data % (cid, mid, email)
bounce_conn = mdb.get_bounce_conn(cid);
datas = mdb.exe_sql(bounce_conn, sql, True, True)
# print datas
for data in datas:
for key,values in data.iteritems():
print '%-20s %s' %(key, values)
print '-'*50
def getActiveService(server):
sql_str = '''select group_concat(machine_ip) as ips from service s where s.status="active" and s.server_type = "%s" '''
global_conn = mdb.get_global_conn();
ips = mdb.exe_sql(global_conn, sql_str % server, True, True)
return ips[0]['ips'].split(',')
def getBranchdomain(branch_id):
sql = '''select bd.domain_name from branch_domain bd where bd.branch_id = %s '''
global_conn = mdb.get_global_conn();
datas = mdb.exe_sql(global_conn, sql % branch_id, True, True)
return datas
def getipid_province(province):
sql_str = "select place_id from ip_place ip where province='%s' order by place_id limit 1"
global_conn = mdb.get_global_conn();
ip_place_id = mdb.exe_sql(global_conn, sql_str % province, False, True)
return ip_place_id[0][0]
def getipid_city(city):
sql_str = "select place_id from ip_place ip where city='%s' order by place_id limit 1 "
global_conn = mdb.get_global_conn();
ip_place_id = mdb.exe_sql(global_conn, sql_str % city, False, True)
return ip_place_id[0][0]