def get_rank_result(sql):
rank = {}
rank_list = [
(rank, sql),
]
for each_rank, each_rank_sql in rank_list:
for row in fetchall(base_data_pool, each_rank_sql):
iata_code = row[0]
inner_order = row[1]
city_id = row[2]
continent_id = row[3]
city_name = row[4]
tri_code = row[5]
if city_id in each_rank:
if int(inner_order) > 0:
if int(each_rank[city_id]['inner_order']) < 0 or int(inner_order) > int(
each_rank[city_id]['inner_order']):
each_rank[city_id]['iata_code'] = iata_code
each_rank[city_id]['inner_order'] = inner_order
else:
each_rank[city_id] = {}
each_rank[city_id]['iata_code'] = iata_code
each_rank[city_id]['inner_order'] = inner_order
each_rank[city_id]['continent_id'] = continent_id
each_rank[city_id]['city_name'] = city_name
each_rank[city_id]['tri_code'] = tri_code
each_rank[city_id]['city_id'] = city_id
return each_rank
def generate_rail_base_task_info():
static_rail_task = ['10001_10009', '10009_10001', '10001_10015', '10015_10001', '10001_10007', '10007_10001',
'10001_10006', '10006_10001', '10001_10005', '10005_10001', '10001_10017', '10017_10001',
'10001_10128', '10128_10001', '10001_10013', '10013_10001', '10001_10012', '10012_10001',
'10001_10008', '10008_10001', '10005_10019', '10019_10005', '10005_10068', '10068_10005',
'10005_10011', '10011_10005', '10005_10016', '10016_10005', '10005_10006', '10006_10005',
'10005_10048', '10048_10005', '10003_10010', '10010_10003', '10003_10008', '10008_10003',
'10003_10011', '10011_10003', '10020_10006', '10006_10020', '10020_10048', '10048_10020',
'10019_10008', '10008_10019', '10019_10057', '10057_10019', '10010_10027', '10027_10010',
'10010_10008', '10008_10010', '10010_10011', '10011_10010', '10073_10011', '10011_10073',
'10008_10024', '10024_10008', '10063_10042', '10042_10063', '10063_10007', '10007_10063',
'10104_10051', '10051_10104', '10025_10051', '10051_10025', '10017_10043', '10043_10017',
'10145_10006', '10006_10145', '10009_10015', '10015_10009']
city_data = {}
base_sql = rail_base_task_query['base_sql']
code_sql = rail_base_task_query['rail_code_query']
for row in fetchall(task_db_monitor_db_pool, base_sql):
city_id = row[0]
country_id = row[1]
city_name = row[2]
city_data[city_id] = {}
city_data[city_id]['city_id'] = city_id
city_data[city_id]['country_id'] = country_id
city_data[city_id]['rail_code'] = city_name
items = list(city_data.items())
data = []
city_code = {}
city_id_set = set()
for item1 in items:
for item2 in items:
if item1[1]['city_id'] != item2[1]['city_id'] and item1[1]['country_id'] == item2[1]['country_id']:
data.append([item1[1]['city_id'], item1[1]['rail_code'], item2[1]['city_id'], item2[1]['rail_code'], 10, "Rail"])
for task in static_rail_task:
city_id_set.add(str(task.split('_')[0]))
city_id_set.add(str(task.split('_')[1]))
city_id_set_str = str(city_id_set).lstrip('{')
city_id_set_str = str(city_id_set_str).rstrip('}')
code_sql = code_sql.format(city_id_set_str)
for row in fetchall(task_db_monitor_db_pool, code_sql):
city_code[str(row[0])] = row[1]
for task in static_rail_task:
dept_city_code = task.split('_')[0]
dest_city_code = task.split('_')[1]
data.append([dept_city_code, city_code[dept_city_code], dest_city_code, city_code[dest_city_code], 10, 'Rail'])
yield from data
def generate_ferries_base_task_info():
base_sql = ferries_base_task_query['base_sql']
data = []
filter_sql = ferries_base_task_query['filter_sql']
filter_data = []
for row in fetchall(base_data_pool, filter_sql):
city_name = row[0]
filter_data.append(city_name)
for row in fetchall(source_info_pool, base_sql):
route_id = row[0]
port1 = row[1].split('（')[0]
port2 = row[2].split('（')[0]
if port1 in filter_data:
if port2 in filter_data:
data.append([route_id, 60])
yield from data
def get_average_success_count():
global avg_res
date_list = set()
for i in range(0, 7):
date_list.add((datetime.datetime.now() +
datetime.timedelta(days=-i)).strftime('%Y%m%d'))
date_list = str(date_list).replace('{', '(')
date_list = str(date_list).replace('}', ')')
sql = '''
select source, package_id, avg(success_count), avg(fail_count) from task_day_list_monitor where date in {} group by source,package_id
'''.format(date_list)
count = 0
for line in fetchall(task_db_monitor_db_pool, sql=sql):
count += 1
avg_res[(line[0], line[1])] = (line[2], line[3])
return avg_res
def generate_round_flight_base_task_info():
static_i = 0
rank1_sql = roundflight_base_task_query['rank1_sql']
rank2_sql = roundflight_base_task_query['rank2_sql']
rank3_sql = roundflight_base_task_query['rank3_sql']
rank4_sql = roundflight_base_task_query['rank4_sql']
rank1 = {}
rank2 = {}
rank3 = {}
rank4 = {}
rank_list = [
(rank1, rank1_sql),
(rank2, rank2_sql),
(rank3, rank3_sql),
(rank4, rank4_sql),
]
for each_rank, each_rank_sql in rank_list:
for row in fetchall(base_data_pool, each_rank_sql):
iata_code = row[0]
if iata_code in ['NAY', 'PEK']:
print(row)
inner_order = row[1]
city_id = row[2]
continent_id = row[3]
city_name = row[4]
tri_code = row[5]
if city_id in each_rank:
if int(inner_order) > 0:
if int(each_rank[city_id]['inner_order']) < 0 or int(inner_order) > int(
each_rank[city_id]['inner_order']):
each_rank[city_id]['iata_code'] = iata_code
each_rank[city_id]['inner_order'] = inner_order
else:
each_rank[city_id] = {}
each_rank[city_id]['iata_code'] = iata_code
each_rank[city_id]['inner_order'] = inner_order
each_rank[city_id]['continent_id'] = continent_id
each_rank[city_id]['city_name'] = city_name
each_rank[city_id]['tri_code'] = tri_code
each_rank[city_id]['city_id'] = city_id
logger.info("[rank dict finished]")
target_data = [
# rank0 1-3
("1-3", rank1, rank3, 12),
# rank1 1-4, 2-3
("1-4", rank1, rank4, 13),
("2-3", rank2, rank3, 13),
# rank2 3-3, 3-4
("2-4", rank2, rank4, 14),
]
data = []
judge_data = set()
for report_key, first_rank, last_rank, package_id in target_data:
logger.info(report_key)
for v1 in first_rank.values():
for v2 in last_rank.values():
logger.info(v2['iata_code'])
if v1['iata_code'] == v2['iata_code']:
# iata_code 相同时过滤
continue
static_i += 1
if (v1['iata_code'], v2['iata_code']) not in judge_data:
judge_data.add((v1['iata_code'], v2['iata_code']))
data.append(
[
v1['iata_code'],
v2['iata_code'],
package_id,
# roundflight_source[static_i % len(roundflight_source)],
v2["continent_id"]
]
)
logger.info("[package_id: {}][rank {} finished]".format(package_id, report_key))
yield from data
def generate_flight_base_task_info():
l_chine_city = ["40051", "51517", "51518", "20103", "20104", "20107", "20108", "20111", "20112", "20115", "20116",
"20118", "20119", "20121", "20122", "20123", "20124", "20125", "20126", "20129", "20130", "20133",
"20134", "20271", "20284", "20287", "21381", "21483", "21484", "21485", "21486", "21487", "21488",
"21489", "21490", "21491", "21492", "21493", "21494", "21495", "21496", "21497", "21498", "21499",
"21500", "21501", "21502", "21503", "21504", "21505", "21506", "21507", "21508", "21509", "21510",
"21511", "21512", "21513", "21514", "21515", "21516", "21517", "21518", "21519", "21520", "21521",
"21522", "21523", "21524", "21525", "21526", "21527", "21528", "21529", "21530", "21531", "21532",
"21533", "21534", "21535", "21536", "20371"]
static_i = 0
rank1_sql = flight_base_task_query['rank1_sql']
rank2_sql = flight_base_task_query['rank2_sql']
rank3_sql = flight_base_task_query['rank3_sql']
rank4_sql = flight_base_task_query['rank4_sql']
rank5_sql = flight_base_task_query['rank5_sql']
rank6_sql = '''SELECT
airport.iata_code,
airport.inner_order,
city.id,
country.continent_id,
city.name,
city.tri_code
FROM country, airport, city
WHERE (city.id = airport.belong_city_id) AND airport.status = 'Open' AND city.country_id = country.mid AND
city.trans_degree = -1 AND city.id IN ({});'''.format(','.join(map(lambda x: "'{}'".format(x), l_chine_city)))
rank1 = {}
rank2 = {}
rank3 = {}
rank4 = {}
rank5 = {}
rank6 = {}
rank_list = [
(rank1, rank1_sql),
(rank2, rank2_sql),
(rank3, rank3_sql),
(rank4, rank4_sql),
(rank5, rank5_sql),
(rank6, rank6_sql)
]
for each_rank, each_rank_sql in rank_list:
for row in fetchall(base_data_pool, each_rank_sql):
iata_code = row[0]
inner_order = row[1]
city_id = row[2]
continent_id = row[3]
city_name = row[4]
tri_code = row[5]
if city_id in each_rank:
if int(inner_order) > 0:
if int(each_rank[city_id]['inner_order']) < 0 or int(inner_order) > int(
each_rank[city_id]['inner_order']):
each_rank[city_id]['iata_code'] = iata_code
each_rank[city_id]['inner_order'] = inner_order
else:
each_rank[city_id] = {}
each_rank[city_id]['iata_code'] = iata_code
each_rank[city_id]['inner_order'] = inner_order
each_rank[city_id]['continent_id'] = continent_id
each_rank[city_id]['city_name'] = city_name
each_rank[city_id]['tri_code'] = tri_code
each_rank[city_id]['city_id'] = city_id
logger.info("[rank dict finished]")
data = []
judge_data = set()
target_data = [
# rank0 1-3
("1-3", rank1, rank3, 0, False),
# rank1 1-4, 2-3
("1-4", rank1, rank4, 1, False),
("2-3", rank2, rank3, 1, False),
# rank2 3-3, 3-4
("3-3", rank3, rank3, 2, True),
("3-4", rank3, rank4, 2, True),
# rank3 2-4, 4-4
("2-4", rank2, rank4, 3, True),
("4-4", rank4, rank4, 3, True),
# rank4 3-5，以及到部分国内城市
("3-5", rank3, rank5, 4, True),
("3-6", rank3, rank6, 4, True),
]
logger.info(str(len(rank1)) + ',' + str(len(rank2)) + ',' + str(len(rank3)) + ',' + str(len(rank4)) + ',' + str(len(rank5)) + ',' + str(len(rank6)))
for report_key, first_rank, last_rank, package_id, need_continent_filter in target_data:
for v1 in first_rank.values():
for v2 in last_rank.values():
if v1['iata_code'] == v2['iata_code']:
# iata_code 相同时过滤
continue
if need_continent_filter and v1['continent_id'] != v2['continent_id']:
# 当需要大洲筛选时，v1 v2 不在一个大洲内
continue
static_i += 1
# 去程
if (v1['iata_code'], v2['iata_code']) not in judge_data:
judge_data.add((v1['iata_code'], v2['iata_code']))
data.append(
[
v1['iata_code'],
v2['iata_code'],
package_id,
# flight_source[static_i % len(flight_source)]
]
)
# 返程
if (v2['iata_code'], v1['iata_code']) not in judge_data:
judge_data.add((v2['iata_code'], v1['iata_code']))
data.append(
[
v2['iata_code'],
v1['iata_code'],
package_id,
# flight_source[static_i % len(flight_source)]
]
)
logger.info("[package_id: {}][rank {} finished]".format(package_id, report_key))
logger.info('data:' + str(len(data)))
yield from data
def query_temporary_task():
sql = '''select type,number from task_temporary_monitor'''
query_list = []
for row in fetchall(task_db_monitor_db_pool, sql):
query_list.append((row[0], row[1]))
return query_list