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