def get_activity_info_data(customer_id, start_time, end_time):
"""
Panel对接接口 get_activity_info接口应该返回的data, data结构如下:
"data": {*start_time: 0}
"""
sql = "select ifnull(sum(daily), 0) from boss_daily_activities where bill_date = '{0}' and prefix = '{1}'"
pg_db = PostgresqlDB(host=BOSS_POSTGRESQL_HOST)
if customer_id != "all":
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix as p inner join crm_customer_info as i \
on p.customer = i.id where i.number = '{0}'".format(customer_id)).one_by_one()
else:
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix").one_by_one()
del pg_db
mysql_db = MysqlDB(host=BOSS_MYSQL_HOST)
data = {}
for timestamp in range(start_time, end_time, 86400000):
active = 0
date = time.strftime('%Y-%m-%d', time.localtime(timestamp / 1000))
for prefix in prefix_list:
one_active = mysql_db.execute(sql.format(date, prefix)).only_one()
active += one_active
data[str(timestamp)] = int(active)
del mysql_db
return data
def up_bandwidth_info_data(customer_id, start_time, end_time, step):
"""
上游客户 up_bandwidth_info接口应该返回的data, data结构如下
"data": {*start_time: {"upload": 0}}
"""
# 分api_block
if step == "day":
interval = 86400000
elif step == "hour":
interval = 3600000
elif step == "minute":
interval = 300000
else:
raise ValueError
data = {}
db = MysqlDB(host=BOSS_MYSQL_HOST)
# step为"day"与step为"hour"或"minute"时, 查询的表不同, 表结构也有不同
for timestamp in range(start_time, end_time, interval):
sql_upload = "select ifnull(sum(upload), 0) "
if step == "day":
sql_upload += "from boss_daily_upload where customer_id = '{0}' and report_date = '{1}'"
date = time.strftime('%Y-%m-%d', time.localtime(timestamp / 1000))
upload = db.execute(sql_upload.format(customer_id,
date)).only_one()
else:
sql_upload += "from upload_log_{0} where timestamp >= {1} and timestamp < {2}"
upload = db.execute(
sql_upload.format(customer_id, timestamp,
timestamp + interval)).only_one()
upload = round(upload * 8 / (interval / 1000), 2)
data[str(timestamp)] = {"upload": upload}
del db
return data
def down_traffic_total_data(customer_id, start_time, end_time, domain=None):
"""
下游客户 down_traffic_total接口应该返回的data, data结构如下
"data": {*domain: {"time": 1489507200000, "app_total_ratio": app/(cdn+p2p), "app": 0, "cdn": 0, "p2p": 0}}
"""
if domain is None:
pg_db = PostgresqlDB(host=BOSS_POSTGRESQL_HOST)
domains = pg_db.execute(
"select domain_name from crm_customer_domain as d inner join crm_customer_info as i \
on d.customer = i.id where i.number = '{0}'".format(customer_id)).one_by_one()
del pg_db
else:
domains = [domain]
mysql_db = MysqlDB(host=BOSS_MYSQL_HOST)
data = {}
for dns in domains:
app, cdn, p2p = mysql_db.execute(
"select ifnull(sum(app), 0), ifnull(sum(cdn), 0), ifnull(sum(p2p), 0) \
from download_log_{0} where timestamp >= {1} and timestamp < {2} and DNS = '{3}'"
.format(customer_id, start_time, end_time, dns)).one_by_one()
if (cdn + p2p) != 0:
app_total_ratio = round(app / (cdn + p2p), 4)
else:
app_total_ratio = 0.0
data[dns] = {
"time": start_time,
"app_total_ratio": app_total_ratio,
"app": int(app),
"cdn": int(cdn),
"p2p": int(p2p)
}
del mysql_db
return data
def get_traffic_info_data(customer_id, start_time, end_time, step):
"""
Panel对接接口 get_traffic_info接口应该返回的data, data结构如下
"data": {"download": {*start_time: {"app": 0, "cdn": 0, "p2p": 0, "sum": cdn+p2p}}, "upload": {*start_time: {"upload": 0}}}
"""
# 分api_block
if step == "day":
interval = 86400000
elif step == "hour":
interval = 3600000
elif step == "minute":
interval = 300000
else:
raise ValueError
data = {"download": {}, "upload": {}}
db = MysqlDB(host=BOSS_MYSQL_HOST)
# step为"day"与step为"hour"或"minute"时, 查询的表不同, 表结构也有不同
for timestamp in range(start_time, end_time, interval):
sql_download = "select ifnull(sum(app), 0), ifnull(sum(cdn), 0), ifnull(sum(p2p), 0) "
sql_upload = "select ifnull(sum(upload), 0) "
if step == "day":
sql_download += "from boss_daily_download where customer_id = '{0}' and report_date = '{1}'"
sql_upload += "from boss_daily_upload where customer_id = '{0}' and report_date = '{1}'"
date = time.strftime('%Y-%m-%d', time.localtime(timestamp / 1000))
app, cdn, p2p = db.execute(sql_download.format(customer_id,
date)).one_by_one()
upload = db.execute(sql_upload.format(customer_id,
date)).only_one()
else:
sql_download += "from download_log_{0} where timestamp >= {1} and timestamp < {2}"
sql_upload += "from upload_log_{0} where timestamp >= {1} and timestamp < {2}"
try:
db.execute("select * from download_log_{0} limit 1".format(
customer_id))
db.execute(
"select * from upload_log_{0} limit 1".format(customer_id))
except:
return data
app, cdn, p2p = db.execute(
sql_download.format(customer_id, timestamp,
timestamp + interval)).one_by_one()
upload = db.execute(
sql_upload.format(customer_id, timestamp,
timestamp + interval)).only_one()
data["download"][str(timestamp)] = {
"app": int(app),
"cdn": int(cdn),
"p2p": int(p2p),
"sum": int(cdn + p2p)
}
data["upload"][str(timestamp)] = {"upload": int(upload)}
del db
return data
def clear_logs(customer_id, category):
# 为避免自动化测试累积的数据占用boss自动化服务器的空间, 每次测试结束, 清空无效的数据(logs)
ts_millisecond = (int(time.time()) - 86400 * 5) * 1000
sql = "delete from {0}_log_{1} where timestamp <= {2}".format(
category, customer_id, ts_millisecond)
mysql_db = MysqlDB(host=BOSS_CRM_HOST)
mysql_db.execute(sql)
time.sleep(1)
del mysql_db
def get_activity_number_data(customer_id):
"""
Panel对接接口 get_activity_number接口应该返回的data, data结构如下:
"data": {"active": 0, "online": 0}
"""
millisecond_now = int(time.time()) * 1000
today = time.strftime('%Y-%m-%d', time.localtime())
sql_active = "select count(*) from boss_activity where report_date = '{0}' and prefix = '{1}'"
sql_online = "select info from boss_activity_online where timestamp > {0} and prefix = '{1}' order by timestamp desc limit 1"
pg_db = PostgresqlDB(host=BOSS_POSTGRESQL_HOST)
if customer_id != "all":
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix as p inner join crm_customer_info as i \
on p.customer = i.id where i.number = '{0}'".format(customer_id)).one_by_one()
else:
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix").one_by_one()
del pg_db
mysql_db = MysqlDB(host=BOSS_MYSQL_HOST)
active = 0
online_info_list = []
for prefix in prefix_list:
one_active = mysql_db.execute(sql_active.format(today,
prefix)).only_one()
active += one_active
one_online_info = mysql_db.execute(
sql_online.format(millisecond_now - 3600000, prefix)).one_by_one()
if len(one_online_info) == 1:
online_info_list.append(one_online_info[0])
del mysql_db
online = 0
for online_info in online_info_list:
online_info = eval(
online_info
) # 如 {'provinces': {}, 'isps': {}, 'natTypes': {}, 'versions': {}}
online_province = online_isp = online_nat_type = online_version = 0
for v in online_info["provinces"].values():
online_province += v
for v in online_info["isps"].values():
online_isp += v
for v in online_info["natTypes"].values():
online_nat_type += v
for v in online_info["versions"].values():
online_version += v
assert online_province == online_isp == online_nat_type == online_version
online += online_province
data = {"active": int(active), "online": int(online)}
return data
def compare_results_for_billing(block_count, logs_count, prefix, ts_second,
category, price, unit):
"""
比较预期结果与实际结果是否相符
:param block_count: 将一堆logs记作一个block(块, 与boss的block概念不同), block的数量
:param logs_count: 一个block中包含的logs的数量
:param prefix: log的peer_id的prefix
:param ts_second: log的时间戳(秒级)
:param category: 计费类别, "download"/"upload"
:param price: CRM中设定的计费单价
:param unit: CRM中设定的计价单位, "KB"/"MB"/"GB"
:return:
"""
assert unit in ("KB", "MB", "GB")
account = 0
for i in range(logs_count):
flow = 1000000 + i * 10240
account += flow
print "account one block:", account
total_account = account * block_count
print "account all block:", total_account
total_money = total_account * price
print "money (B):", total_money
if unit == "KB":
expect_account = float(total_account) / 1024
expect_money = float(total_money) / 1024
elif unit == "MB":
expect_account = float(total_account) / 1024 / 1024
expect_money = float(total_money) / 1024 / 1024
else:
expect_account = float(total_account) / 1024 / 1024 / 1024
expect_money = float(total_money) / 1024 / 1024 / 1024
timestamp_end = int(ts_second)
timestamp_start = timestamp_end - 10 * 60
sql = "select sum(account), sum(money) from {0}_billing where ts between {1} and {2} and prefix = '{3}'"\
.format(category, timestamp_start, timestamp_end, prefix)
mysql_db = MysqlDB(host=BOSS_CRM_HOST)
actual_account, actual_money = mysql_db.execute(sql).one_by_one()
del mysql_db
if abs(actual_account - expect_account) <= 0.000001 and abs(
actual_money - expect_money) <= 0.000001:
return True
else:
print "account - expect:", expect_account, "; actual:", actual_account
print "money - expect:", expect_money, "; actual:", actual_money
return False
def get_activity_online_info_data(customer_id, start_time, end_time):
"""
Panel对接接口 get_activity_online_info接口及上游接口activity_online_info接口应该返回的data, data结构如下:
"data": {*start_time: 0}
"""
sql = "select info from boss_activity_online where timestamp >= {0} and timestamp < {1} and \
prefix = '{2}' order by timestamp desc limit 1"
pg_db = PostgresqlDB(host=BOSS_POSTGRESQL_HOST)
if customer_id != "all":
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix as p inner join crm_customer_info as i \
on p.customer = i.id where i.number = '{0}'".format(customer_id)).one_by_one()
else:
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix").one_by_one()
del pg_db
data = {}
if len(prefix_list) == 0:
return data
mysql_db = MysqlDB(host=BOSS_MYSQL_HOST)
for timestamp in range(start_time, end_time, 3600000):
online_info_list = []
for prefix in prefix_list:
one_online_info = mysql_db.execute(
sql.format(timestamp, timestamp + 3600000,
prefix)).one_by_one()
if len(one_online_info) == 1:
online_info_list.append(one_online_info[0])
online = 0
for online_info in online_info_list:
online_info = eval(
online_info
) # 如 {'provinces': {}, 'isps': {}, 'natTypes': {}, 'versions': {}}
online_province = online_isp = online_nat_type = online_version = 0
for v in online_info["provinces"].values():
online_province += v
for v in online_info["isps"].values():
online_isp += v
for v in online_info["natTypes"].values():
online_nat_type += v
for v in online_info["versions"].values():
online_version += v
assert online_province == online_isp == online_nat_type == online_version
online += online_province
data[str(timestamp)] = int(online)
del mysql_db
return data
def up_activity_info_data(customer_id, start_day, end_day):
"""
上游客户 activity_info接口应该返回的data, data结构如下
"data": {*date: 0}
"""
date_list = get_date_list_by_date(start_day, end_day)
sql = "select ifnull(sum(daily), 0) from boss_daily_activities where custom_id = '{0}'and bill_date = '{1}'"
db = MysqlDB(host=BOSS_MYSQL_HOST)
data = {}
for date in date_list:
active = db.execute(sql.format(customer_id, date)).only_one()
data[str(date)] = int(active)
del db
return data
def down_traffic_info_data(customer_id,
start_time,
end_time,
step,
domain=None):
"""
下游客户 down_traffic_info接口应该返回的data, data结构如下
"data": {*start_time: {"app": 0, "cdn": 0, "p2p": 0, "sum": cdn+p2p}}
"""
# 分api_block
if step == "day":
interval = 86400000
elif step == "hour":
interval = 3600000
elif step == "minute":
interval = 300000
else:
raise ValueError
data = {}
db = MysqlDB(host=BOSS_MYSQL_HOST)
# step为"day"与step为"hour"或"minute"时, 查询的表不同, 表结构也有不同
for timestamp in range(start_time, end_time, interval):
sql_download = "select ifnull(sum(app), 0), ifnull(sum(cdn), 0), ifnull(sum(p2p), 0) "
if step == "day":
sql_download += "from boss_daily_download where customer_id = '{0}' and report_date = '{1}' "
if domain is not None:
sql_download += "and DNS = '{0}'".format(domain)
date = time.strftime('%Y-%m-%d', time.localtime(timestamp / 1000))
app, cdn, p2p = db.execute(sql_download.format(customer_id,
date)).one_by_one()
else:
sql_download += "from download_log_{0} where timestamp >= {1} and timestamp < {2} "
if domain is not None:
sql_download += "and DNS = '{0}'".format(domain)
app, cdn, p2p = db.execute(
sql_download.format(customer_id, timestamp,
timestamp + interval)).one_by_one()
data[str(timestamp)] = {
"app": int(app),
"cdn": int(cdn),
"p2p": int(p2p),
"sum": int(cdn + p2p)
}
del db
return data
def get_qos_startup_data(customer_id, start_time, end_time):
"""
Panel对接接口 get_qos_startup接口应该返回的data, data结构如下:
"data": {*start_time: {"middle": 0, "long": 0, "less": 0}}
"""
sql = "select count(*) from qos_startup where "
if customer_id != "all":
pg_db = PostgresqlDB(host=BOSS_POSTGRESQL_HOST)
customer_index = pg_db.execute(
"select id from crm_customer_info where number = '{0}'".format(
customer_id)).only_one()
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix where customer = {0}".
format(customer_index)).one_by_one()
del pg_db
if len(prefix_list) == 0:
sql += "prefix = 'Not Exist' and "
elif len(prefix_list) == 1:
sql += "prefix = '{0}' and ".format(prefix_list[0])
else:
sql += "prefix in {0} and ".format(tuple(prefix_list))
data = {}
sql_less = sql + "duration > 0 and duration < 1000 and timestamp >= {0} and timestamp < {1}"
sql_middle = sql + "duration >= 1000 and duration < 3000 and timestamp >= {0} and timestamp < {1}"
sql_long = sql + "duration >= 3000 and timestamp >= {0} and timestamp < {1}"
mysql_db = MysqlDB(host=BOSS_MYSQL_HOST)
for timestamp in range(start_time, end_time, 300000):
less = mysql_db.execute(sql_less.format(timestamp, timestamp +
300000)).only_one()
middle = mysql_db.execute(
sql_middle.format(timestamp, timestamp + 300000)).only_one()
long = mysql_db.execute(sql_long.format(timestamp, timestamp +
300000)).only_one()
data[str(timestamp)] = {
"less": int(less),
"middle": int(middle),
"long": int(long)
}
del mysql_db
return data
def get_qos_buffer_data(customer_id, start_time, end_time):
"""
Panel对接接口 get_qos_buffer接口应该返回的data, data结构如下:
"data": {*start_time: {"middle": 0, "majority": 0, "less": 0, "zero": 0}}
"""
sql = "select count(*) from qos_buffer where "
if customer_id != "all":
pg_db = PostgresqlDB(host=BOSS_POSTGRESQL_HOST)
customer_index = pg_db.execute(
"select id from crm_customer_info where number = '{0}'".format(
customer_id)).only_one()
prefix_list = pg_db.execute(
"select prefix from crm_customer_prefix where customer = {0}".
format(customer_index)).one_by_one()
del pg_db
if len(prefix_list) == 0:
sql += "prefix = 'Not Exist' and "
elif len(prefix_list) == 1:
sql += "prefix = '{0}' and ".format(prefix_list[0])
else:
sql += "prefix in {0} and ".format(tuple(prefix_list))
data = {}
sql_zero = sql + "buffering_count = 0 and timestamp >= {0} and timestamp < {1}"
sql_less = sql + "buffering_count = 1 and timestamp >= {0} and timestamp < {1}"
sql_middle = sql + "buffering_count in (2, 3) and timestamp >= {0} and timestamp < {1}"
sql_majority = sql + "buffering_count > 3 and timestamp >= {0} and timestamp < {1}"
mysql_db = MysqlDB(host=BOSS_MYSQL_HOST)
for timestamp in range(start_time, end_time, 300000):
zero = mysql_db.execute(sql_zero.format(timestamp, timestamp +
300000)).only_one()
less = mysql_db.execute(sql_less.format(timestamp, timestamp +
300000)).only_one()
middle = mysql_db.execute(
sql_middle.format(timestamp, timestamp + 300000)).only_one()
majority = mysql_db.execute(
sql_majority.format(timestamp, timestamp + 300000)).only_one()
data[str(timestamp)] = {
"zero": int(zero),
"less": int(less),
"middle": int(middle),
"majority": int(majority)
}
del mysql_db
return data