def getMinAndMaxByDatetimeBytable(databasename, originaltablename):
minsql = '''select min(gmt_create) as gmt_create from {databasename}.{tablename};'''.format(
databasename=databasename, tablename=originaltablename)
maxsql = '''select max(gmt_create) as gmt_create from {databasename}.{tablename};'''.format(
databasename=databasename, tablename=originaltablename)
mintime = select_by_mcp_with_dict(minsql)
maxtime = select_by_mcp_with_dict(maxsql)
# print(type(mintime[0]['gmt_create']),maxtime[0]['gmt_create'])
mintimestart, mintimeend = get_month_range(mintime[0]['gmt_create'])
maxtimestart, maxtimeend = get_month_range(maxtime[0]['gmt_create'])
# print(mintimestart.strftime('%Y-%m-%d'),maxtimeend.strftime('%Y-%m-%d'))
return mintimestart.strftime('%Y-%m-%d'), maxtimeend.strftime('%Y-%m-%d')
def get_user_dict_by_ids(sql, ids):
users = select_by_mcp_with_dict(sql.format(','.join([str(i)
for i in ids])))
u_list = {i['id']: i for i in users}
return u_list
def get_prescription_order3():
sql = '''
SELECT `month`,`uid`,`date`
FROM ih.doctor_active_prescription
'''
month_uid = select_by_mcp_with_dict(sql)
return month_uid
def get_ym_orders(ym_date: datetime.date):
"""
按月份返回订单
{gmt_create月份: [row,row]}
{ 201905: {123,124} }
:param dtype: 0 非医院 1 医院
:returns
"""
ym_date = ym_date.replace(day=1)
next_ym_date = ym_date + relativedelta(months=1)
next_ym_date = next_ym_date.replace(day=1)
order_result = select_by_mcp_with_dict(
'''select user_id,order_id,gmt_create,gmt_modify
from orders
-- order_type 2 6 处方订单
where order_type in (2,6) and gmt_create >= '{ym_date:%Y-%m-%d}' and gmt_create < '{next_ym_date:%Y-%m-%d}'
''')
result = []
for order in order_result:
result.append(order)
return result
def get_prescription_order():
sql = '''
SELECT `month`,uid ,total
FROM ih.doctor_month_uid
'''
month_uid = select_by_mcp_with_dict(sql)
return month_uid
def get_prescription_order2(month,uid,total):
sql = '''
SELECT *
FROM ih.doctor_active_prescription
where `month`={month} and uid={uid} order by rand() limit {total}
'''
month_uid = select_by_mcp_with_dict(sql)
return month_uid
def get_all_active_doctor():
get_all_active_doctor_sql = '''SELECT d.date,u.* from user_dataset.base_user_info u,ih.doctor_active_prescription d
WHERE u.id=d.uid and d.mark=1'''
result = select_by_mcp_with_dict(get_all_active_doctor_sql)
all_active_doctor_dick = {}
for value in result:
if not all_active_doctor_dick.keys().__contains__(value['date']):
all_active_doctor_dick[value['date']] = []
all_active_doctor_dick[value['date']].append(value)
return all_active_doctor_dick
def get_doctor_from_prescription(year_month_time):
start_date_str, end_date_str = get_month_start_end_time(year_month_time)
sql = '''
SELECT DISTINCT(doctor_id)
FROM ih.prescription
WHERE gmt_create >= '{start_date_str}'
AND gmt_create <= '{end_date_str}'
'''
result = select_by_mcp_with_dict(sql)
return [value['doctor_id'] for value in result]
def get_add_doctor():
get_add_doctor_sql = '''SELECT * FROM `doctor_add_tmp` where doctor_id not in (106699041,106699133,117799702);'''
# get_add_doctor_sql = '''SELECT * FROM `doctor_add_tmp` where doctor_id = 106699041;'''
# get_add_doctor_sql = '''SELECT * FROM `doctor_add_tmp` where doctor_id = 106699133;'''
result = select_by_mcp_with_dict(get_add_doctor_sql)
doctor_dick = {}
for value in result:
doctor_id = value['doctor_id']
num = value['num']
doctor_dick[doctor_id] = num
return doctor_dick
def get_change_prescription(month_start, month_end):
get_change_prescription_sql = '''SELECT p.id FROM prescription p,user_dataset.base_user_info u
WHERE p.doctor_id not in(select CONCAT(d.doctor_id,'') from doctor_add_tmp d)
and p.patient_id = u.id and u.referer is null AND p.gmt_create >= \'{}\' AND p.gmt_create < \'{}\' order by RAND() LIMIT 6500;;'''.format(
month_start, month_end)
result = select_by_mcp_with_dict(get_change_prescription_sql)
prescription_list = []
for value in result:
id = value['id']
prescription_list.append(id)
return prescription_list
def get_drp_by_ids(patient_ids):
"""
医患关系 查询
:param patient_ids:
:return:
"""
result = select_by_mcp_with_dict("""
SELECT patient_id,doctor_id FROM doctor_patient_relationship
where status = 0 and patient_id in ( {','.join([str(i) for i in patient_ids])} )
""")
return {dpr['patient_id']: dpr['doctor_id'] for dpr in result}
def get_doctor_id(start_day, end_day):
get_doctor_id_sql = '''
SELECT DISTINCT doctor_id FROM ih.prescription WHERE
gmt_create >= \'{}\'
AND gmt_create <= \'{}\'
AND redundancy in (113,1)'''.format(start_day, end_day)
result = select_by_mcp_with_dict(get_doctor_id_sql)
doctor_list=[]
for value in result:
id = int(value['doctor_id'])
doctor_list.append(id)
return doctor_list
def get_prescription_order(date):
sql = '''
SELECT o.order_id AS order_id, o.gmt_create AS order_time, a.`id` AS patient_id, a.`name` AS patient_name,
a.`sex`, a.`birthday`, a.`disease_name`, a.`referer`, a.`tag` AS patient_tag
, convert(a.biz_id, SIGNED) AS hospital_id, a.province
, a.city, a.gmt_create AS patient_register_time, a.mobile
FROM mall.orders o
INNER JOIN base_user_info a ON o.user_id = a.id
WHERE o.order_type IN (2, 6) AND DATE_FORMAT(o.`gmt_create`, '%Y-%m-%d') = '{date}'
AND o.delete_status = 0
order by o.gmt_create
'''
order_result = select_by_mcp_with_dict(sql)
return order_result
def get_change_prescription_v2(start_day, end_day):
get_change_prescription_sql = '''SELECT
id
FROM
ih.prescription
WHERE
gmt_create >= \'{}\'
AND gmt_create <= \'{}\'
AND redundancy = 112'''.format(start_day, end_day)
result = select_by_mcp_with_dict(get_change_prescription_sql)
prescription_list = []
for value in result:
id = value['id']
prescription_list.append(id)
return prescription_list
def get_change_prescription(start_day, end_day):
get_change_prescription_sql = '''SELECT
id,gmt_create
FROM
ih.prescription
WHERE
gmt_create >= \'{}\'
AND gmt_create <= \'{}\'
AND redundancy = 112'''.format(start_day, end_day)
result = select_by_mcp_with_dict(get_change_prescription_sql)
prescription_dick = {}
for value in result:
id = value['id']
gmt_create = value['gmt_create']
gmt_day = datetime.datetime.strftime(gmt_create, '%Y-%m-%d')
if gmt_day not in prescription_dick:
prescription_dick[gmt_day] = []
prescription_dick[gmt_day].append(id)
return prescription_dick
def get_doctor_by_reg_month():
"""
处方医生 ym 注册 row
:return:
"""
sql = """select bui.id as id,bui.name as name,bui.dept as dept,bui.title as title,
h.name as hospital_name,convert(bui.biz_id , SIGNED) as hospital_id,bui.gmt_create
from base_user_info bui left join hospital h on bui.biz_id = h.id
where utype = 1
"""
doctor_list = select_by_mcp_with_dict(sql)
result = {}
for d in doctor_list:
ym = get_ym(d['gmt_create'])
if ym not in result:
result[ym] = []
result[ym].append(d)
return result
def get_patient_doctor_relation(patient_ids):
patient_doctor_id_dict = {}
sql = '''
SELECT a.`id` AS patient_id,
b.`id` AS doctor_id
FROM `base_user_info` a
INNER JOIN `base_user_info` b ON a.`referer`= b.code
WHERE a.`id` IN ({patient_ids})
'''
result = select_by_mcp_with_dict(sql)
for value in result:
patient_id = value['patient_id']
doctor_id = value['doctor_id']
if patient_doctor_id_dict.get(patient_id) is None:
doctor_id_set = set()
doctor_id_set.add(doctor_id)
patient_doctor_id_dict[patient_id] = doctor_id_set
else:
doctor_id_set = patient_doctor_id_dict[patient_id]
doctor_id_set.add(doctor_id)
patient_doctor_id_dict[patient_id] = doctor_id_set
return patient_doctor_id_dict
def gen_prescription_detail(this_year):
"""
处方详情数据
:return:
"""
drug_dict = read_drug_detail()
drug_online_miss_match_number = 0
for row in xls_reader(INPUT_DIR + 'prescription/处方数据all.xlsx', True, 0):
pres_detail_list = []
# 时间轴
yearmonth_datetime = cell_to_datetime(row[0])
ym = get_ym(yearmonth_datetime)
ym_date = yearmonth_datetime.date()
ym_date = ym_date.replace(day=1)
next_ym_date = yearmonth_datetime.date() + relativedelta(months=1)
next_ym_date = next_ym_date.replace(day=1)
logging.info("generate data: {}".format(ym))
# origin_pres_detail_list = select_by_mcp_with_dict(
# f"""SELECT
# p.id AS id,
# p.gmt_create AS gmt_create,
# i.item_id AS item_id,
# i.name AS name,
# i.drug_name AS drug_name,
# i.drug_unit as unit,
# i.drug_usage as `usage`,
# i.chemical_name as chemical_name,
# i.drug_spec as spec
#
# FROM
# ih.prescription p
# INNER JOIN
# mall.orders_detail od ON p.t_order_id = od.order_id
# LEFT JOIN
# mall.item i ON od.item_id = i.item_id
# where p.gmt_create >= '{ym_date:%Y-%m-%d}' and p.gmt_create < '{next_ym_date:%Y-%m-%d}'
# -- flag&1=1 处方药
# and i.flag is not null
# """)
# origin_pres_detail_list = select_by_mcp_with_dict(
# f"""SELECT
# p.id AS id,
# p.gmt_create AS gmt_create,
# i.item_id AS item_id,
# i.name AS name,
# i.drug_name AS drug_name,
# i.drug_unit as unit,
# i.drug_usage as `usage`,
# i.chemical_name as chemical_name,
# i.drug_spec as spec
#
# FROM
# ih.prescription p
# INNER JOIN
# mall.orders_detail od ON p.t_order_id = od.order_id
# LEFT JOIN
# mall.item i ON od.item_id = i.item_id
# where p.gmt_create >= '{ym_date:%Y-%m-%d}' and p.gmt_create < '{next_ym_date:%Y-%m-%d}'
# -- flag&1=1 处方药
# and i.is_drug = 1
# """)
origin_pres_detail_list = select_by_mcp_with_dict("""SELECT
p.id AS id,
p.gmt_create AS gmt_create,
i.item_id AS item_id,
i.name AS name,
i.drug_name AS drug_name,
i.drug_unit AS unit,
i.drug_usage AS `usage`,
i.chemical_name AS chemical_name,
i.drug_spec AS spec
FROM
ih.prescription p,mall.orders o ,mall.orders_detail od ,mall.item i
WHERE p.t_order_id = o.parent_id and o.order_id = od.order_id and od.item_id = i.item_id and
p.gmt_create >= '{ym_date:%Y-%m-%d}'
AND p.gmt_create < '{next_ym_date:%Y-%m-%d}' -- flag&1=1 处方药
AND i.is_drug = 1
UNION
SELECT
p.id AS id,
p.gmt_create AS gmt_create,
i.item_id AS item_id,
i.name AS name,
i.drug_name AS drug_name,
i.drug_unit AS unit,
i.drug_usage AS `usage`,
i.chemical_name AS chemical_name,
i.drug_spec AS spec
FROM
ih.prescription p ,mall.orders_detail od ,mall.item i
WHERE p.t_order_id = od.order_id and od.item_id = i.item_id
and p.gmt_create >= '{ym_date:%Y-%m-%d}'
AND p.gmt_create < '{next_ym_date:%Y-%m-%d}' -- flag&1=1 处方药
AND i.is_drug = 1
""")
ym_pres_detail_count = len(origin_pres_detail_list)
precent_10 = (ym_pres_detail_count * 0.1)
logging.info('month total pres detail {ym_pres_detail_count}')
ym_pres_process_count = 0
for pres_detail in origin_pres_detail_list:
detail_dict = {}
# detail_dict['id'] = PRES_DETAIL_ID
# PRES_DETAIL_ID += 1
detail_dict['prescription_id'] = pres_detail['id']
detail_dict['sku_id'] = pres_detail['item_id']
# detail_dict['chemical_name'] = str(add_quote(pres_detail['chemical_name']))
detail_dict['chemical_name'] = quote_or_MYSQL_NULL(
pres_detail['chemical_name'])
online_drug = drug_dict.get(pres_detail['chemical_name'])
if online_drug:
# detail_dict['name'] = add_quote(online_drug['name'])
# detail_dict['drug_name'] = add_quote(online_drug['drug_name'])
# detail_dict['num'] = MYSQL_NULL if not online_drug['num'] else online_drug['num']
# detail_dict['unit'] = add_quote(online_drug['unit'])
# detail_dict['usage'] = add_quote(online_drug['usage'])
# detail_dict['per_amount'] = MYSQL_NULL if not online_drug['per_amount'] else online_drug['per_amount']
# detail_dict['times'] = MYSQL_NULL if not online_drug['times'] else online_drug['times']
# detail_dict['remark'] = add_quote(online_drug['remark'])
# detail_dict['usage_frequency_code'] = MYSQL_NULL if not online_drug['usage_frequency_code'] else \
# online_drug['usage_frequency_code']
# detail_dict['usage_frequency_name'] = add_quote(online_drug['usage_frequency_name'])
# detail_dict['per_num'] = add_quote(online_drug['per_num'])
# detail_dict['per_unit'] = add_quote(online_drug['per_unit'])
# detail_dict['dose'] = MYSQL_NULL if not online_drug['dose'] else online_drug['dose']
# detail_dict['spec'] = add_quote(online_drug['spec'])
detail_dict['name'] = quote_or_MYSQL_NULL(online_drug['name'])
detail_dict['drug_name'] = quote_or_MYSQL_NULL(
online_drug['drug_name'])
detail_dict['num'] = MYSQL_NULL if not online_drug[
'num'] else online_drug['num']
detail_dict['unit'] = quote_or_MYSQL_NULL(online_drug['unit'])
detail_dict['usage'] = quote_or_MYSQL_NULL(
online_drug['usage'])
detail_dict['per_amount'] = MYSQL_NULL if not online_drug[
'per_amount'] else online_drug['per_amount']
detail_dict['times'] = MYSQL_NULL if not online_drug[
'times'] else online_drug['times']
detail_dict['remark'] = quote_or_MYSQL_NULL(
online_drug['remark'])
detail_dict['usage_frequency_code'] = MYSQL_NULL if not online_drug['usage_frequency_code'] else \
online_drug['usage_frequency_code']
detail_dict['usage_frequency_name'] = quote_or_MYSQL_NULL(
online_drug['usage_frequency_name'])
detail_dict['per_num'] = quote_or_MYSQL_NULL(
online_drug['per_num'])
detail_dict['per_unit'] = quote_or_MYSQL_NULL(
online_drug['per_unit'])
detail_dict['dose'] = MYSQL_NULL if not online_drug[
'dose'] else online_drug['dose']
detail_dict['spec'] = quote_or_MYSQL_NULL(online_drug['spec'])
else:
drug_online_miss_match_number += 1
# detail_dict['name'] = add_quote(pres_detail['name'])
# detail_dict['drug_name'] = add_quote(pres_detail['drug_name'])
# detail_dict['num'] = random_pick([1, 2, 3, 4, 5], [0.9, 0.05, 0.03, 0.01, 0.01])
# detail_dict['unit'] = add_quote(pres_detail['unit'])
# detail_dict['usage'] = add_quote(pres_detail['usage'])
# detail_dict['per_amount'] = MYSQL_NULL
# detail_dict['times'] = MYSQL_NULL
# detail_dict['remark'] = MYSQL_NULL
# detail_dict['usage_frequency_code'] = MYSQL_NULL
# detail_dict['usage_frequency_name'] = add_quote(random_pick(['1次/天', '3次/天', '2次/天'], [0.5, 0.4, 0.1]))
# detail_dict['per_num'] = MYSQL_NULL
# detail_dict['per_unit'] = MYSQL_NULL
# detail_dict['dose'] = MYSQL_NULL
# detail_dict['spec'] = add_quote(pres_detail['spec'])
detail_dict['name'] = quote_or_MYSQL_NULL(pres_detail['name'])
detail_dict['drug_name'] = quote_or_MYSQL_NULL(
pres_detail['drug_name'])
detail_dict['num'] = random_pick([1, 2, 3, 4, 5],
[0.9, 0.05, 0.03, 0.01, 0.01])
detail_dict['unit'] = quote_or_MYSQL_NULL(pres_detail['unit'])
detail_dict['usage'] = quote_or_MYSQL_NULL(
pres_detail['usage'])
detail_dict['per_amount'] = MYSQL_NULL
detail_dict['times'] = MYSQL_NULL
detail_dict['remark'] = MYSQL_NULL
detail_dict['usage_frequency_code'] = MYSQL_NULL
detail_dict['usage_frequency_name'] = quote_or_MYSQL_NULL(
random_pick(['1次/天', '3次/天', '2次/天'], [0.5, 0.4, 0.1]))
detail_dict['per_num'] = MYSQL_NULL
detail_dict['per_unit'] = MYSQL_NULL
detail_dict['dose'] = MYSQL_NULL
detail_dict['spec'] = quote_or_MYSQL_NULL(pres_detail['spec'])
# gmt_create_f = f"{pres_detail['gmt_create']:%Y-%m-%d %H:%M:%S}"
# detail_dict['gmt_create'] = add_quote(gmt_create_f)
# detail_dict['gmt_modify'] = add_quote(gmt_create_f)
gmt_create_f = "{pres_detail['gmt_create']:%Y-%m-%d %H:%M:%S}"
detail_dict['gmt_create'] = quote_or_MYSQL_NULL(gmt_create_f)
detail_dict['gmt_modify'] = quote_or_MYSQL_NULL(gmt_create_f)
pres_detail_list.append(detail_dict)
ym_pres_process_count += 1
if ym_pres_process_count % precent_10 == 0:
logging.info(
'{ym} process month pres detail {ym_pres_process_count} precent {ym_pres_process_count / ym_pres_detail_count :.1%}'
)
pres_detail_list.sort(key=lambda x: x['gmt_create'])
logging.info('{ym} has {len(pres_detail_list)} pres detail')
iterate_elements_page(pres_detail_list,
write_pres_detail_sql,
DEFAULT_PAGE_SIZE,
ym_date=yearmonth_datetime.date())
#iterate_elements_page(pres_detail_list, write_pres_detail_sql, DEFAULT_PAGE_SIZE)
# , ym_date = row[0]
logging.info(
'{this_year} miss match pres detail {drug_online_miss_match_number}')
def gen_prescription_detail(this_year):
"""
处方详情数据
:return:
"""
for row in xls_reader(INPUT_DIR + 'prescription/处方数据all.xlsx', True, 0):
pres_detail_list = []
# 时间轴
yearmonth_datetime = cell_to_datetime(row[0])
ym = get_ym(yearmonth_datetime)
ym_date = yearmonth_datetime.date()
ym_date = ym_date.replace(day=1)
next_ym_date = yearmonth_datetime.date() + relativedelta(months=1)
next_ym_date = next_ym_date.replace(day=1)
logging.info("generate data: {}".format(ym))
origin_pres_detail_list = select_by_mcp_with_dict(
"""SELECT
p.id AS id,
p.gmt_create AS gmt_create,
i.item_id AS item_id,
od.item_name AS item_name,
i.drug_name AS drug_name,
i.drug_unit AS unit,
i.drug_usage AS `usage`,
i.chemical_name AS chemical_name,
i.drug_spec AS spec,
od.item_num as item_num
FROM
ih.prescription p,mall.orders o ,mall.orders_detail od ,mall.item i
WHERE p.t_order_id = o.parent_id and o.order_id = od.order_id and od.item_id = i.item_id and
p.gmt_create >= '{ym_date:%Y-%m-%d}'
AND p.gmt_create < '{next_ym_date:%Y-%m-%d}' -- flag&1=1 处方药
AND i.is_drug = 1
UNION
SELECT
p.id AS id,
p.gmt_create AS gmt_create,
i.item_id AS item_id,
od.item_name AS item_name,
i.drug_name AS drug_name,
i.drug_unit AS unit,
i.drug_usage AS `usage`,
i.chemical_name AS chemical_name,
i.drug_spec AS spec,
od.item_num as item_num
FROM
ih.prescription p ,mall.orders_detail od ,mall.item i
WHERE p.t_order_id = od.order_id and od.item_id = i.item_id
and p.gmt_create >= '{ym_date:%Y-%m-%d}'
AND p.gmt_create < '{next_ym_date:%Y-%m-%d}' -- flag&1=1 处方药
AND i.is_drug = 1
""")
ym_pres_detail_count = len(origin_pres_detail_list)
precent_10 = (ym_pres_detail_count * 0.1)
logging.info('month total pres detail {ym_pres_detail_count}')
ym_pres_process_count = 0
for pres_detail in origin_pres_detail_list:
detail_dict = {}
detail_dict['prescription_id'] = pres_detail['id']
detail_dict['sku_id'] = pres_detail['item_id']
detail_dict['chemical_name'] = quote_or_MYSQL_NULL(pres_detail['chemical_name'])
detail_dict['name'] = quote_or_MYSQL_NULL(pres_detail['item_name'])
detail_dict['drug_name'] = quote_or_MYSQL_NULL(pres_detail['drug_name'])
if pres_detail['item_num']:
detail_dict['num'] = pres_detail['item_num']
else:
detail_dict['num'] = random_pick([1, 2, 3, 4, 5], [0.9, 0.05, 0.03, 0.01, 0.01])
detail_dict['unit'] = quote_or_MYSQL_NULL(pres_detail['unit'])
detail_dict['usage'] = quote_or_MYSQL_NULL(pres_detail['usage'])
detail_dict['per_amount'] = MYSQL_NULL
detail_dict['times'] = MYSQL_NULL
detail_dict['remark'] = MYSQL_NULL
detail_dict['usage_frequency_code'] = MYSQL_NULL
detail_dict['usage_frequency_name'] = quote_or_MYSQL_NULL(
random_pick(['1次/天', '3次/天', '2次/天'], [0.5, 0.4, 0.1]))
detail_dict['per_num'] = MYSQL_NULL
detail_dict['per_unit'] = MYSQL_NULL
detail_dict['dose'] = MYSQL_NULL
detail_dict['spec'] = quote_or_MYSQL_NULL(pres_detail['spec'])
gmt_create_f = "{pres_detail['gmt_create']:%Y-%m-%d %H:%M:%S}"
detail_dict['gmt_create'] = quote_or_MYSQL_NULL(gmt_create_f)
detail_dict['gmt_modify'] = quote_or_MYSQL_NULL(gmt_create_f)
pres_detail_list.append(detail_dict)
ym_pres_process_count += 1
if ym_pres_process_count % precent_10 == 0:
logging.info(
'{ym} process month pres detail {ym_pres_process_count} precent {ym_pres_process_count / ym_pres_detail_count :.1%}')
pres_detail_list.sort(key=lambda x: x['gmt_create'])
logging.info('{ym} has {len(pres_detail_list)} pres detail')
iterate_elements_page(pres_detail_list, write_pres_detail_sql, DEFAULT_PAGE_SIZE,
ym_date=yearmonth_datetime.date())
t_time = id_time_dict_list[i]['gmt_create']
id_time_tuple_list.append((t_id, t_time))
return id_time_tuple_list
def insert_prescription_id_sql(old_id, new_id):
insert_prescription_id_reorder_2 = 'insert into prescription_id_reorder_2(`old_id`, new_id) values ({doctor_id},{id});'.format(
doctor_id=old_id, id=new_id)
return insert_prescription_id_reorder_2
if __name__ == '__main__':
sql_old = """
select id from ih.prescription where gmt_create<"2020-07-01"
"""
id_list_dict_old = select_by_mcp_with_dict(sql_old)
id_list_old = [p['id'] for p in id_list_dict_old]
sql_new = """
select id,gmt_create from ih.prescription_7891011_bak order by gmt_create asc
"""
id_list_dict_new = select_by_mcp_with_dict(sql_new)
id_list_new = [p['id'] for p in id_list_dict_new]
# 重新生成id
# """
# 处方表：
# Max_id = 1916357
# Gap = 989623
#
# 处方详情表：
# Max_id = 1919927
def get_id_time_tuple(id_time_dict_list, num):
id_time_tuple_list = []
for i in range(num):
print(i)
t_id = id_time_dict_list[i]['id']
t_time = id_time_dict_list[i]['gmt_create']
id_time_tuple_list.append((t_id, t_time))
return id_time_tuple_list
if __name__ == '__main__':
sql = """
select id, gmt_create from ih.prescription_detail
""" # where DATE_FORMAT(gmt_create,'%Y') >= '2019' and DATE_FORMAT(gmt_create,'%Y') < '2020'
id_time_dict_list = select_by_mcp_with_dict(sql)
# print(get_id_time_tuple(id_time_dict_list, len(id_time_dict_list)))
data = get_id_time_tuple(id_time_dict_list, len(id_time_dict_list))
# 重新生成id
"""
处方表：
Max_id = 1916357
Gap = 989623
处方详情表：
Max_id = 1919927
Gap = 993635
"""
old_time_new_list = reorder(1919927, 993635, data)
# pres_detail_list = []
def get_active_doctor(active_table_name):
get_active_doctor_sql = '''SELECT uid FROM user_dataset.{active_table_name} WHERE utype =1 AND uid NOT IN
(SELECT DISTINCT(uid) FROM ih.doctor_active_prescription)'''.format(
active_table_name=active_table_name)
result = select_by_mcp_with_dict(get_active_doctor_sql)
return [value['uid'] for value in result]
def get_doctor_list_by_date():
sql = '''
select * from user_dataset.base_user_info where id in (SELECT DISTINCT(uid) FROM ih.doctor_active_prescription)
'''
return select_by_mcp_with_dict(sql)
def get_doctor_active_prescription_len(ym):
sql = '''
SELECT DISTINCT(uid) FROM ih.doctor_active_prescription where `month`= '{ym}'
'''
return select_by_mcp_with_dict(sql)
def get_user_list_by_ids(ids):
sql = '''
select * from base_user_info where id in ({})
'''
return select_by_mcp_with_dict(sql.format(','.join([str(i) for i in ids])),
database='user_dataset')
