def drug_update(source_excel, base_excel, what):
fk = FkocsAPI(server=server,
user=user,
password=password,
database=database)
source_lst = read_excel(source_excel)
base_lst = read_excel(base_excel).select('약품코드', 'EDI코드', '원내/원외 처방구분',
'투여경로')
lst = source_lst.join(base_lst, left_on='보험코드', right_on='EDI코드')
if what == 'inj_to_in':
lst = lst.filter(
lambda row: row['투여경로'] == '3' and row['복지부분류'] != '당뇨병용제')
lst.to_excel('inj_to_in.xlsx')
for record in lst[:]:
code = record.get('약품코드')
component = record['성분자세히'] or record['성분/함량'] or ''
pro_yn = record['구분']
if code:
if what == 'component':
fk.update_component(code, component)
elif what == 'pro_yn':
value = 'Y' if record['구분'] == '전문' else 'N'
fk.update_pro_yn(code, value)
elif what == 'fda_prg':
value = record['임부']
fk.update_fda_prg(code, value)
elif what == 'efcy_cd':
fk.update_efficacy_code(code, record['복지부분류코드'])
elif what == 'cmpnt_cd':
fk.update_component_code(code, record['주성분코드'])
elif what == 'inj_to_in':
fk.update_inout(code, 2)
def compare(before, after, pk, extras, start):
fname_before = os.path.basename(before)
fname_after = os.path.basename(after)
fname_before, ext = os.path.splitext(fname_before)
fname_after, ext = os.path.splitext(fname_after)
filename = '[Source {}][Dest {}].xlsx'.format(fname_before, fname_after)
lst_before = ls.read_excel(before)
lst_after = ls.read_excel(after)
changes = lst_before.get_changes(lst_after, pk=pk)
wb = xlsxwriter.Workbook(filename)
ws_added = wb.add_worksheet('Added')
ws_deleted = wb.add_worksheet('Deleted')
ws_updated = wb.add_worksheet('Updated')
write_CD(changes.added, ws_added, lst_before.column_orders)
write_CD(changes.deleted, ws_deleted, lst_before.column_orders)
write_updated(changes.updated,
ws_updated,
pk,
column_orders=lst_before.column_orders,
extras=extras)
wb.close()
if start:
try:
os.startfile(filename)
except:
return
def is_ocsxl(excel_file_contents):
lst = read_excel(file_contents=excel_file_contents)
if lst:
columns = set(lst[0].keys())
if columns <= set(OCS_MASTER_COLUMNS):
return True
return False
def analize_excel(xl_drug_info=None, xl_gosi_table=None, filename=None):
gosi_tbl = get_tables_from_gosi(xl_gosi_table,
scheme=['제품코드', '제품명', '상한금액'])
gosi_tbl.set_number_type(제품코드='')
drug_info = read_excel(xl_drug_info) if xl_drug_info else get_all_list()
drug_info.set_number_type(보험단가=0)
suga_info = get_all_suga()
join_result = drug_info.join(gosi_tbl, left_on='EDI코드',
right_on='제품코드').join(suga_info,
left_on='약품코드',
right_on='수가코드')
print(join_result)
join_result.add_columns(상한초과=lambda row: row.보험단가 > row.상한금액)
join_result = join_result.map(
**{
'원내/원외 처방구분': lambda key: {
'1': '원외만',
'2': '원내만',
'3': '원외/원내'
}.get(key, key)
})
return join_result.to_excel(selects=[
'시트명', '제품코드', '제품명', '수가코드', '원내/원외 처방구분', '상한금액', '보험단가', '일반단가',
'상한초과', '수가시작일자', '수가종료일자', '시작일자', '종료일자'
])
def get_all_list(test=False, **kwargs):
if test:
return read_excel(DRUG_DB_PATH)
fk = FkocsAPI(server=server,
user=user,
password=password,
database=database)
return fk.get_all_info(**codes)
def get_inj_list(test):
if test:
return read_excel(DRUG_DB_PATH).filter(lambda row: row['투여경로'] == "3")
fk = FkocsAPI(server=server,
user=user,
password=password,
database=database)
return fk.get_inj_info(**codes)
def get_nutfluid_list(test):
if test:
return read_excel(DRUG_DB_PATH).filter(lambda row: row[
'효능코드(보건복지부)'] in ["325"] or "알부민" in row['약품명(한글)'])
fk = FkocsAPI(server=server,
user=user,
password=password,
database=database)
return fk.get_nutfluid_info(**codes)
def get_label_list(test):
if test:
return read_excel(DRUG_DB_PATH).filter(
lambda row: row['단일포장구분'] in ["S", "P"])
fk = FkocsAPI(server=server,
user=user,
password=password,
database=database)
return fk.get_label_info(**codes)
def open_record_file(_file):
lst = Listorm()
if not _file:
return lst
fn, ext = os.path.splitext(_file)
if ext in ['.xls', '.xlsx']:
lst = read_excel(_file)
elif ext == '.csv':
lst = read_csv(_file)
return lst
def compare_contents(content_before,
content_after,
pk,
extras,
to_context=False):
lst_before = ls.read_excel(file_contents=content_before)
lst_after = ls.read_excel(file_contents=content_after)
inout_map = {'1': '원외', '2': '원내', '3': '원내/원외'}
column_orders = lst_before.column_orders
lst_before = lst_before.map(**{'원내/원외 처방구분': inout_map.get})
lst_after = lst_after.map(**{'원내/원외 처방구분': inout_map.get})
if to_context:
lst_before = lst_before.add_columns(
drug_name=lambda row: row['약품명(한글)'],
inout=lambda row: row['원내/원외 처방구분'])
lst_after = lst_after.add_columns(drug_name=lambda row: row['약품명(한글)'],
inout=lambda row: row['원내/원외 처방구분'])
changes = lst_before.get_changes(lst_after, pk=pk)
return changes
changes = lst_before.get_changes(lst_after, pk=pk)
output = BytesIO()
wb = xlsxwriter.Workbook(output)
ws_added = wb.add_worksheet('추가된항목')
ws_deleted = wb.add_worksheet('삭제된항목')
ws_updated = wb.add_worksheet('변경된항목')
write_CD(changes.added, ws_added, column_orders)
write_CD(changes.deleted, ws_deleted, column_orders)
write_updated(changes.updated,
ws_updated,
pk,
column_orders=column_orders,
extras=extras)
wb.close()
return output.getvalue()
def main():
columns = [
"대분류", "중분류", "소분류", "계열분류", "성분명", "제품명", "제조/수입사", "제형", "급여정보",
"전문/일반", "ATC코드", "기타", "식약처분류", "재심사여부", "효능ㆍ효과"
]
try:
xlfile, *_ = filter(
lambda arg: arg.endswith('.xls') or arg.endswith('.xlsx'),
sys.argv)
except:
print('need xlfile')
edis = get_edi_code_from_xl('약품정보.xls')
lst_drug = read_excel('약품정보.xls')
else:
edis = get_edi_code_from_xl(xlfile)
lst_drug = read_excel(xlfile)
finally:
lst = get_info_thread(edis)
lst.column_orders = columns
lst.to_excel('kpic-only.xlsx')
lst = lst.join(lst_drug.select('원내/원외 처방구분', 'EDI코드', '약품코드'),
left_on='급여정보',
right_on='EDI코드',
how='left')
inout = lambda key: {'1': '원외', '2': '원내', '3': '원외/원내'}.get(key, key)
# lst = lst.update(**{"원내/원외 처방구분": inout}, to_rows=False)
lst = lst.update(
**{'원내/원외 처방구분': lambda row: inout(row['원내/원외 처방구분'])})
lst.to_excel('KPIC.xlsx')
df = pd.read_excel('KPIC.xlsx')
df.계열분류 = df.계열분류.fillna('')
groupping = ['대분류', '중분류', '소분류', '계열분류', '성분명', '원내/원외 처방구분']
aggfunc = lambda arr: ', '.join(sorted(set(arr)))
gf = df.groupby(groupping).agg({'제품명': aggfunc})
gf.to_excel('KPIC-Grouped.xlsx')
def get_records(types,
wards,
ord_start_date,
ord_end_date,
start_dt,
end_dt,
test,
shape='po'):
# print(ord_start_date, ord_end_date, wards)
ord_request = OrderSelectApiRequest(ord_start_date, ord_end_date, wards)
if test:
ord_request.set_test_response('response_samples/orderselect')
drug_lst = listorm.read_excel(DRUG_DB_PATH)
ord_lst = listorm.Listorm(ord_request.get_records())
else:
ord_request.api_calls()
ord_lst = listorm.Listorm(ord_request.get_records())
try:
if shape == 'inj':
drug_lst = get_inj_list()
else:
drug_lst = get_label_list()
except:
drug_lst = read_excel(DRUG_DB_PATH)
else:
ord_lst = ord_lst.filter(where=lambda row: row.rcpt_dt and start_dt
<= row['rcpt_dt'] < end_dt)
drug_lst = drug_lst.select('약품코드', '단일포장구분', '효능코드(보건복지부)')
pk_set = drug_lst.unique('약품코드')
ord_lst = ord_lst.filter(
where=lambda row: row.get('ord_cd') in pk_set and row.get(
'rcpt_dt') and row.get('rcpt_ord_tp_nm') in types)
ord_lst = ord_lst.join(drug_lst, left_on='ord_cd', right_on='약품코드')
ord_lst = ord_lst.set_number_type(ord_qty=0.0, ord_frq=0, ord_day=0)
rcpt_dt_list = ord_lst.column_values('rcpt_dt')
if rcpt_dt_list:
f, l = min(rcpt_dt_list), max(rcpt_dt_list)
ord_lst = ord_lst.add_columns(rcpt_dt_min=lambda x: f,
rcpt_dt_max=lambda x: l)
return ord_lst
def get_item_count(excel_file_contents):
if is_ocsxl(excel_file_contents):
lst = read_excel(file_contents=excel_file_contents)
return len(lst)
return 0