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 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())