Skip to content

Instantly share code, notes, and snippets.

@cosacog

cosacog/0readme.md

Last active January 28, 2022 13:46
Show Gist options
  • Save cosacog/76b87fae7ea7c9489a29f67c7f936e33 to your computer and use it in GitHub Desktop.
Save cosacog/76b87fae7ea7c9489a29f67c7f936e33 to your computer and use it in GitHub Desktop.
Download ZIP
Allocate nurses daily for a week
Raw
0readme.md

window_allocate_nurse.py

これは何

  • 1週間の勤務を振り分けするスクリプトです
  • 3つ(*.pyのファイル)分かれてますが、window_allocate_nurse.pyがメインのスクリプトです

必要な環境

  • python: anacondaがよいでしょう。
  • pysimplegui: pipで入れないと入らないと思います。
  • xlwings: エクセルファイルを操作するのに使ってます。condaかpipで入るでしょう。

使い方

  • 3つを1つのディレクトリにまとめて、勤務を振り分けする表のエクセルファイルのパスを設定します。
  • window_allocate_nurse.pyを走らせると、エクセルファイルとGUIのウィンドウが立ち上がります。
  • "配置"をクリックすると配置します。
  • windows限定ですが、nurse_daily_allocate.batでwindow_allocate_nurse.pyのパスを設定できると、こちらをダブルクリックで利用でき、通常のアプリっぽく使えます。

使い方2: 休み希望の入力

  • 勤務表のファイルを読み込んで振り分け時に反映させることができます。
  • 画面が立ち上がった状態でだいたい分かることを期待します。
Raw
allocate_nurse.py
#%% import
import os
import pandas as pd
import xlwings as xw
import numpy as np
import PySimpleGUI as sg
import copy
import sys
from datetime import datetime as dt
#% function
def set_leader(staffing_am, staffing_pm):
'''
set leader using 2 staffing info, usually am and pm info
'''
'''
rules
1. 休暇は外す
2. opeの時は外す
3. delegateの時外す:外せない時は兼任
4. 午前と午後は可能なときは揃える
5. 誰も残らない時はNoneを返す
'''
_staffing = {}
_staffing['am'] = staffing_am
_staffing['pm'] = staffing_pm
ampm_list = ['am','pm']
if _staffing['am'].is_dayoff:
for ap in ampm_list:
_staffing[ap].set_nurses_free()
_staffing[ap].is_freenurse_set = True
_staffing[ap].leader = ''
return
leader_list = _staffing['am'].leader_list
leaders = dict.fromkeys(ampm_list)
leaders['am'] = copy.deepcopy(leader_list)
leaders['pm'] = copy.deepcopy(leader_list)
ns_onleave = {}
ope_ns = {}
delegate = {}
# 以下例外時繰り返し
all_capable = False
cnt = 0
while not all_capable:
if cnt>100:
break
cnt +=1
for ap in ampm_list:
ns_onleave[ap] = copy.deepcopy(_staffing[ap].nurses_onleave)
ope_ns[ap] = copy.deepcopy(_staffing[ap].nurses_ope)
if _staffing[ap].delegate == '':
raise Exception('delegateが未設定. 一旦終了')
delegate[ap] = copy.deepcopy(_staffing[ap].delegate['name'])
for ap in ampm_list:
# remove off nurse
for ns in ns_onleave[ap]:
if ns in leaders[ap]:
# print('休み希望Ns: %s' %(ns))
leaders[ap].remove(ns)
# remove ope nurse
for ns in ope_ns[ap]:
if ns in leaders[ap]:
leaders[ap].remove(ns)
# remove delegate when more than 1 leaders remain
is_delegate_removable = (delegate[ap] in leaders[ap]) and (len(leaders[ap])>1)
if is_delegate_removable:
leaders[ap].remove(delegate[ap])
leaders_common = list(set(leaders['am']) & set(leaders['pm']))
leader = dict.fromkeys(ampm_list, '')
if len(leaders_common)>0:
leader['am'] = np.random.choice(leaders_common)
leader['pm'] = leader['am']
else:
try:
leader['am'] = np.random.choice(leaders['am'])
except:
leader['am'] = ''
try:
leader['pm'] = np.random.choice(leaders['pm'])
except:
leader['pm'] = ''
is_capable = []
for ap in ampm_list:
_staffing[ap].leader = leader[ap]
_staffing[ap].set_nurses_free()
_staffing[ap].is_freenurse_set = True
is_capable += [_staffing[ap].is_nurses_capable()]
all_capable = all(is_capable)
if not all_capable:
print('リーダーがうまく決まりません。ドクターに合わせた配置が困難です。')
return all_capable
#% class
class Booth:
def __init__(self, row, doctors):
self.row = row
self.doctors = doctors
self.nurse = ''
self.is_fit = True
def __str__(self):
if len(self.doctors)<2:
docs = self.doctors[0]
else:
docs = ','.join(self.doctors)
nurse = ''
if len(self.nurse) == 0:
nurse = 'unset'
else:
nurse = self.nurse
return('row:%s, doctors:%s, nurse:%s' %(self.row, docs, nurse))
def set_nurse(self, ns):
self.nurse = ns
class Booths:
doctors_need_selected_ns = ['吉光', '樋口']
nurses4dc = dict.fromkeys(doctors_need_selected_ns)
is_initialized = False
def __init__(self, braces, doctors, sheet, nurses):
self.booths: list[Booth] = []
self.nurses_intreat: list[str] = []
self.doctors: list[str] = []
if len(braces)==0:
for item in doctors:
self.set_booth(Booth(item['row'], [item['name']]))
else:
idxs_inbrace = []
for brace in braces:
row_top = brace['top']
row_btm = brace['btm']
row_booth = int(np.ceil(np.mean([row_top, row_btm])))
booth = Booth(row_booth, [])
for idx, doc in enumerate(doctors):
if (row_top <= doc['row'] <= row_btm):
booth.doctors.append(doc['name'])
idxs_inbrace.append(idx)
self.set_booth(booth)
for idx, doc in enumerate(doctors):
if idx in idxs_inbrace:continue
booth = Booth(doc['row'], [doc['name']])
self.set_booth(booth)
self.set_doctors()
self.set_nurses4dc(sheet, nurses)
Booths.is_initialized = True
def get_nurse_of_doctor(self, doctor):
'''
'''
for booth in self.booths:
if doctor in booth.doctors:
return booth.nurse
raise Exception('%sが外来担当医師に含まれていません' %(doctor))
return
def get_nurses(self):
'''
'''
nurses = [booth.nurse for booth in self.booths]
return nurses
def is_nurses_capable(self, ns_free):
'''
'''
cls = Booths
docs_need_ns = set(self.doctors) & set(cls.doctors_need_selected_ns)
nurses_capable = []
min_nurse_capable = 10
for doc in docs_need_ns:
nurse_capable = set(ns_free) & set(cls.nurses4dc[doc])
nurses_capable += list(nurse_capable)
min_nurse_capable = min(min_nurse_capable, len(nurse_capable))
can_allocate = False
if (len(set(nurses_capable)) >= len(docs_need_ns)) and (min_nurse_capable>0):
can_allocate = True
return can_allocate
def print(self):
for booth in self.booths:
print(booth)
def remove_booth_by_name(self, doctor):
for booth in self.booths:
if doctor in booth.doctors:
self.booths.remove(booth)
break
self.set_doctors()
def replace_nurse(self, nurse_pre, nurse_pst):
'''
'''
is_undone = True
for booth in self.booths:
if booth.nurse == nurse_pre:
booth.nurse = nurse_pst
is_undone = False
if is_undone:
raise Exception('外来の看護師に%sが含まれていません.' %(nurse_pre))
return
def set_booth(self, booth):
self.booths.append(booth)
rows = [booth.row for booth in self.booths]
idxs_sort = np.argsort(rows)
booths_sorted = list(np.array(self.booths)[idxs_sort])
self.booths = booths_sorted
def set_doctors(self):
for booth in self.booths:
self.doctors += booth.doctors
return
def set_nurses4dc(self, sheet, nurses):
'''
set selected nurses for each doctor
'''
cls = Booths
if cls.is_initialized:
return
for key in cls.doctors_need_selected_ns:
title_row = sheet.range((1,1),(1,10)).value
col = [idx for idx, ttl in enumerate(title_row) if (ttl !=None) and (key in ttl)][0] + 1
ns_list = list(filter(None, sheet.range((2, col), (20, col)).value))
include = sheet.range((2, col+1)).value
if include:
cls.nurses4dc[key] = ns_list
else:
# remaining nurses
nurses_unique = list(set(nurses['am']+nurses['pm']))
[nurses_unique.remove(ns) for ns in ns_list]
cls.nurses4dc[key] = nurses_unique
return
def set_nurse(self, nurses_free):
'''
'''
cls = Booths
if len(self.booths) > len(nurses_free):
raise Exception(
"外来の枠が%s個で、看護師の数が代表,リーダー,オペ担当を除いて%s人なので不足しています。" %(
len(self.booths), len(nurses_free)))
is_nurse_fit = lambda ns, doctor: ns in self.nurses4dc[doctor]
fits = dict.fromkeys(cls.doctors_need_selected_ns, True)
cnt = 0
all_fit = False
while not all_fit:
if cnt > 100:
break
cnt += 1
nurses_intreat = copy.deepcopy(nurses_free)
for booth in self.booths:
ns = np.random.choice(nurses_intreat)
booth.set_nurse(ns)
nurses_intreat.remove(ns)
doc_in = list(set(booth.doctors) & set(cls.doctors_need_selected_ns))
if len(doc_in)>0:
doc = doc_in[0]
if is_nurse_fit(ns, doc):
fits[doc] = True
else:
fits[doc] = False
all_fit = all(list(fits.values()))
if not all_fit:
print('看護師の配置がうまく行きませんでした')
return nurses_intreat
class Staffing:
#% const
WKDAYS = ['mon','tue','wed','thu','fri','sat']
msg_wk = ['月曜', '火曜', '水曜','木曜','金曜','土曜']
colms = [2, 6, 10, 14, 18, 22]
MSG_WKDAYS = dict(zip(WKDAYS, msg_wk))
MSG_AP = {'am':'午前', 'pm':'午後'}
COLM_OF_WK = dict(zip(WKDAYS, colms))
SHEETNAME_SETTINGS = '設定_情報'
ROW_INTREAT = 12 # 処置室の行の最初
ROW_OFF_NS = 23 # 休みのNsの行の最後
ROW_DATE = 2
ROW_DAY = 3
ROWS_DOCTOR = (5,10)
ROWS_OPE_NS = [46, 48]
MAX_COLM_SHAPE = 27
MAX_ROW_SHAPE = 15
#% class member
# workbook
wb = None
# shapes
braces = None
# nurses
nurses_all = {'am':[], 'pm':{}}
leader_list = []
delegate_list = []
# worksheet
active_sheet = None
sheet_setting = None
# constructor
def __init__(self, wb, wkday, ampm, **kwargs):
'''
kwargs: str['path']
'''
self.ampm = ampm
self.wkday = wkday
self.nurses_onleave = []
self.nurses_onduty = []
self.nurses_ope = []
self.nurses_intreat = []
self.delegate = ''
self.leader = ''
self.nurses_free = []
self.booths_info = None
self.is_freenurse_set = False
self.is_dayoff = False
Staffing.wb = wb
if Staffing.wb is None:
raise Exception('excelのファイルが読めていません.')
self._set_settingsheet()
self.set_all_nurses()
self._set_leader_list()
self._set_delegate_list()
if len(self.leader_list)==0:
print('leader_listが取得できていない')
# methods
def is_nurses_capable(self):
'''
'''
return self.booths_info.is_nurses_capable(self.nurses_free)
def set_active_sheet(self):
'''
'''
Staffing.active_sheet = Staffing.wb.sheets.active
def set_all_nurses(self):
'''
set cls.nurses_all
'''
cls = Staffing
sheetnames = [sheet.name for sheet in cls.wb.sheets]
idx = sheetnames.index('1週') + 1
sheet1w = cls.wb.sheets(idx)
rng_ns = {}
rng_ns['am'] = "AA13:AA30"
rng_ns['pm'] = "AC13:AC30"
for ampm in ['am','pm']:
cls.nurses_all[ampm] = list(filter(None,sheet1w.range(rng_ns[ampm]).value))
def set_braces(self):
'''
set cls.braces
'''
cls = Staffing
shapes = cls.active_sheet.shapes
pnts_left = [cls.active_sheet.range(1, i+1).left for i in range(cls.MAX_COLM_SHAPE)]
pnts_top = [cls.active_sheet.range(i+1, 1).top for i in range(cls.MAX_ROW_SHAPE)]
colm_am_ns = 1 # plus
colm_pm_ns = 3 # plus
braces_wk = {wk:{'am':[],'pm':[]} for wk in cls.WKDAYS}
for shape in shapes:
brace = {}
l, t, b = self._get_shape_boundary(shape, pnts_left, pnts_top, print_name=False) # left, top, bottom cells
brace = {
'name':shape.name,
'left':l,
'top':t,
'btm':b
}
wk_brace = [
wk for wk, colm in cls.COLM_OF_WK.items()
if colm+colm_am_ns == l
]
if len(wk_brace)>0:
ap = 'am'
else:
wk_brace = [wk for wk,colm in cls.COLM_OF_WK.items() if colm+colm_pm_ns == l]
if len(wk_brace)>0:
ap = 'pm'
else:
continue
if len(wk_brace)>1:
raise Exception("braceが1箇所以上に存在?しています。")
# print(brace)
braces_wk[wk_brace[0]][ap].append(brace)
Staffing.braces = braces_wk
def set_booths(self):
'''
get booth info
args: doctors, braces
'''
cls = Staffing
braces = cls.braces[self.wkday][self.ampm]
self.booths_info = Booths(braces, self.doctors, cls.sheet_setting, cls.nurses_all)
return
def set_dayoff(self):
'''
'''
cls = Staffing
col = cls.COLM_OF_WK[self.wkday]
label_wkday = cls.active_sheet.range((cls.ROW_DAY, col)).value
self.is_dayoff = True if ('休' in label_wkday) or ('祝' in label_wkday) else False
return
def set_delegate(self):
'''
'''
cls = Staffing
values = cls.delegate_list
deleg_list = [{'name': values[0], 'main':True}]
delg_list_sub = [{'name':item[0], 'main':item[1]} for item in zip(values[1:], [False for idx in range(len(values)-1)])]
deleg_list = deleg_list + delg_list_sub
def narrow_delegate_list(lst):
deleg_delete = []
for deleg in deleg_list:
if deleg['name'] in lst:
deleg_delete.append(deleg)
# deleg_delete = delete_item(self.nurses_onleave)
for deleg in deleg_delete:
deleg_list.remove(deleg)
return deleg_list
deleg_list = narrow_delegate_list(self.nurses_onleave)
deleg_list = narrow_delegate_list(self.nurses_ope)
if (len(deleg_list) == 0):
raise Exception('代表%sが全員休みで代表になる人がいません' %(','.join(values)))
else:
self.delegate = deleg_list[0]
return
def set_doctors(self):
xlDiagonalDown = 5
xlDiagonalUp = 6
blank = -4142
cls = Staffing
cols_ampm = {'am':0, 'pm':2}
colm_wk = cls.COLM_OF_WK[self.wkday]
colmplus = cols_ampm[self.ampm]
colm = colm_wk + colmplus
doctors = []
for r in range(cls.ROWS_DOCTOR[0], cls.ROWS_DOCTOR[1]+1):
doctor = self.active_sheet.range((r,colm)).value
is_onduty = (self.active_sheet.range((r, colm+1)).api.Borders(xlDiagonalDown).LineStyle== blank
) and (self.active_sheet.range((r, colm+1)).api.Borders(xlDiagonalUp).LineStyle== blank)
if (doctor is not None) & (is_onduty):
# print("is_onduty: %s" %(is_onduty))
# if doctor is not None:
doctors.append({'name':doctor,'row':r})
self.doctors = doctors
def set_nurse_inbooth(self):
'''
'''
if self.booths_info is None:
self.nurses_intreat = self.nurses_free
else:
self.nurses_intreat = self.booths_info.set_nurse(self.nurses_free)
return
def set_nurse_onleave(self):
'''
get "off" ns list for each of am and pm
'''
cls = Staffing
colm_plus = {'am':0, 'pm':2}
colm = cls.COLM_OF_WK[self.wkday] + colm_plus[self.ampm]
NROW = 15 # 休暇のnsを調べる行の数
ns = []
for r in reversed(range(cls.ROW_OFF_NS-NROW, cls.ROW_OFF_NS+1)):
val = cls.active_sheet.range((r,colm)).value
if val is None:
break
ns.append(val)
self.nurses_onleave = ns
ns_all = copy.deepcopy(self.nurses_all[self.ampm])
for ns in self.nurses_onleave:
ns_all.remove(ns)
self.nurses_onduty = ns_all
def set_nurse_ope(self):
cls = Staffing
colm_plus = {'am':0, 'pm':2}
colm = cls.COLM_OF_WK[self.wkday] + colm_plus[self.ampm]
r1, r2 = cls.ROWS_OPE_NS
ns_ope = []
for r in range(r1,r2+1):
val_ns = cls.active_sheet.range((r, colm)).value
if val_ns is None:
continue
ns = [n for n in cls.nurses_all[self.ampm] if n in val_ns]
if len(ns)==0:
raise Exception('%sの名前がnsの名前のリストに入っていません' %(val_ns))
ns_ope.append(ns[0])
self.nurses_ope = ns_ope
def set_nurses_free(self):
'''
collect freely available nurse (not delegate, leader, ope, onleave)
'''
ns_free = copy.deepcopy(self.nurses_onduty)
if self.is_dayoff:
self.nurses_free = ns_free
return
for ns in self.nurses_ope:
if ns in ns_free:
ns_free.remove(ns)
self.nurses_free = ns_free
# delegateを外せるときは外す
try:
ns_free.remove(self.delegate['name'])
except:
raise Exception('set_nurses_freeでdelegateがない')
# leaderを外せるときは外す
leader_exist = len(self.leader) > 0
if leader_exist:
# boothの数が多くてリーダーを設定できない時はリーダーなし
is_leader_remove = (self.leader in ns_free) and ((len(ns_free)) <= (len(self.booths_info.booths)))
# print('leader remove: %s' %(is_leader_remove))
if is_leader_remove:
self.leader = ''
else:
if self.leader in ns_free:
ns_free.remove(self.leader)
else:
print("%s(%s)はたぶんdelegate (%s)=leader (%s)" %(
self.wkday,
self.ampm,
self.delegate['name'],
self.leader))
return
def set_workbook(self, path):
'''
workbook読み込み
'''
Staffing.wb = xw.Book(path)
def update(self):
'''
'''
self.set_active_sheet()
self.set_braces()
self.set_dayoff()
self.set_doctors()
self.set_nurse_onleave()
if self.is_dayoff:
# 以下休日の時はスキップ
self.nurses_ope = []
self.delegate = ''
self.booths_info = None
else:
self.set_nurse_ope()
self.set_delegate()
self.set_booths()
return
def _get_shape_boundary(self, shape, pnts_left, pnts_top, print_name=True):
'''
get cell boundary for a shape
'''
cls = Staffing
if print_name:
print(shape.name)
pnt_left = shape.left
pnt_top = shape.top
pnt_btm = pnt_top + shape.height
# column
subtract_left = [abs(item - pnt_left) for item in pnts_left]
colm_shape = subtract_left.index(min(subtract_left)) + 1
# top and bottom
pnts_border_top = [pnts_top[i]-(pnts_top[i+1]-pnts_top[i])*0.15 for i in range(cls.MAX_ROW_SHAPE-1)]
pnts_border_btm = [pnts_top[i]+(pnts_top[i+1]-pnts_top[i])*0.15 for i in range(cls.MAX_ROW_SHAPE-1)]
# top
subtract_top = np.array(pnts_border_top) - pnt_top
subtract_minus_top = subtract_top[subtract_top<0]
row_top_shape = len(subtract_minus_top)
# bottom
subtract_btm = np.array(pnts_border_btm) - pnt_btm
row_btm_shape = len(subtract_btm[subtract_btm<0])
return (colm_shape, row_top_shape, row_btm_shape)
def _set_delegate_list(self):
'''
todo _set_leader_listと共通処理を抜き出す
'''
cls = Staffing
key = '代表'
title_row = cls.sheet_setting.range((1,1),(1,10)).value
col = [idx for idx, ttl in enumerate(title_row) if (ttl !=None) and (key in ttl)][0] + 1
ns_list = list(filter(None, cls.sheet_setting.range((2, col), (20, col)).value))
Staffing.delegate_list = ns_list
return
def _set_leader_list(self):
cls = Staffing
key = 'リーダー'
title_row = cls.sheet_setting.range((1,1),(1,10)).value
col = [idx for idx, ttl in enumerate(title_row) if (ttl !=None) and (key in ttl)][0] + 1
ns_list = list(filter(None, cls.sheet_setting.range((2, col), (20, col)).value))
Staffing.leader_list = ns_list
return
def _set_settingsheet(self):
cls = Staffing
sheetnames = [sheet.name for sheet in cls.wb.sheets]
idx = sheetnames.index(cls.SHEETNAME_SETTINGS) + 1
cls.sheet_setting = cls.wb.sheets(idx)
return
class Staffings:
def __init__(self, wb):
self.wb = wb
self.active_sheet = None
self.staffings = dict.fromkeys(Staffing.WKDAYS)
# for v in self.staffings.values():v={'am':None, 'pm':None}
for wkday in Staffing.WKDAYS:
staffing = {'am':None, 'pm':None}
for ampm in ['am', 'pm']:
staffing[ampm] = Staffing(self.wb, wkday, ampm)
self.staffings[wkday] = staffing
def update(self):
for wkday in Staffing.WKDAYS:
staffing = self.staffings[wkday]
try:
for ampm in ['am','pm']:
staffing[ampm].update() # active_sheet, etc
# 木曜午後の原先生の枠はつけない
if (wkday == 'thu') and (ampm == 'pm'):
# remove_booth(staffing[ampm], '原')
staffing[ampm].booths_info.remove_booth_by_name('原')
# set leader and check if capable to allocate
set_leader(staffing['am'], staffing['pm']) # also set_nurses_free
for ampm in ['am', 'pm']:
staffing[ampm].set_nurse_inbooth() # do not process when is_dayoff
# 午前に樋口先生の外来についたら午後は処置室
dc = '樋口'
replace_nurse = False
if (not staffing['am'].is_dayoff) and (dc in staffing['am'].booths_info.doctors):
# print('樋口先生')
nurse_incharge = staffing['am'].booths_info.get_nurse_of_doctor(dc)
nurses_inbooth_pm = staffing['pm'].booths_info.get_nurses()
replace_nurse = (nurse_incharge in nurses_inbooth_pm) and (len(staffing['pm'].nurses_intreat)>0)
if replace_nurse:
nurse_replace = np.random.choice(staffing['pm'].nurses_intreat)
staffing['pm'].nurses_intreat.remove(nurse_replace)
staffing['pm'].nurses_intreat.append(nurse_incharge)
for booth in staffing['pm'].booths_info.booths:
if '吉光' in booth.doctors:
continue
staffing['pm'].booths_info.replace_nurse(nurse_incharge, nurse_replace)
break
print('%sに%sは午前中樋口先生についたので午後は%sと交代して処置室入り' %(
wkday,
nurse_incharge,
nurse_replace))
self.active_sheet = staffing[ampm].active_sheet
except Exception as e:
raise Exception('%sの%sで%s' %(
Staffing.MSG_WKDAYS[wkday],
Staffing.MSG_AP[ampm],
e))
col = Staffing.COLM_OF_WK[wkday]
# print(self.active_sheet.range((2, col)).value)
# msg = 'yes' if staffing['am'].is_dayoff else 'no'
# print('休み?%s' %(msg))
# def update_active_sheet(self):
class PrintWklySchedule:
# const
COLM_OF_WK = Staffing.COLM_OF_WK
ROW_INTREAT: int = Staffing.ROW_INTREAT # 処置室の行の最初
ROW_OFF_NS: int = Staffing.ROW_OFF_NS# 休みのNsの行の最後
ROW_DATE: int = Staffing.ROW_DATE
ROW_DAY: int = Staffing.ROW_DAY
WKDAYS = Staffing.WKDAYS
ROWS_DOCTOR = Staffing.ROWS_DOCTOR
# color
RED = 3
BLACK = 1
# font style
FONT_SIZE = 12
# classmember
active_sheet = None
dates = {}
def __init__(self):
# if staffings is not None:
# self.set_staffings(staffings)
# self.offschedule = None
self.is_init = True
return
def add_values(self, row_hi, col, values):
'''
'''
cls = PrintWklySchedule
if type(values) is str:
values = [values]
length = len(values)
if length == 0:
return
for idx, r in enumerate(range(row_hi, row_hi+length)):
cls.active_sheet.range((r, col)).value = values[idx]
return
def add_values_inbooth(self, col, booths):
'''
'''
cls = PrintWklySchedule
for booth in booths:
cls.active_sheet.range((booth.row, col)).value = booth.nurse
return
def delete_values(self, row1, row2, col):
'''
'''
cls = PrintWklySchedule
cls.active_sheet.range((row1, col), (row2, col)).value = ''
def get_dates(self):
cls = PrintWklySchedule
if cls.active_sheet is None:
raise Exception('active sheetがセットされていない')
dict_out = {}
for wkday, col in cls.COLM_OF_WK.items():
date = cls.active_sheet.range((cls.ROW_DATE, col)).value
dict_out[wkday] = date
return dict_out
def replace_off_nurses(self, wb, offschedule):
'''
'''
if self.is_init:
self.set_style(wb)
col_plus = {'am':0, 'pm':2}
cls = PrintWklySchedule
cls.active_sheet = wb.sheets.active
if cls.active_sheet is None:
raise Exception('active sheetをセットしたのにうまくいかなかった')
else:
print('replace_off_nurses:active sheetはセットされた')
cls.dates = self.get_dates()
for wkday in cls.WKDAYS:
day = self.dates[wkday].day
month = self.dates[wkday].month
if month != offschedule.year_month['month']:
continue
nurses_off = offschedule.get_off_nurses(day)
for ap in ['am', 'pm']:
col = cls.COLM_OF_WK[wkday] + col_plus[ap]
row_hi = cls.ROW_INTREAT
row_lo = cls.ROW_OFF_NS
self.delete_values(row_hi, row_lo, col)
ns_off = [''] + nurses_off[ap]
row_hi = row_lo - len(ns_off) + 1
self.add_values(row_hi, col, ns_off)
# self.staffings[wkday][ap].nurses_onleave = copy.deepcopy(nurses_off[ap])
return
def replace_nurses(self, wb, staffings, add_values=True):
'''
'''
cls = PrintWklySchedule
cls.active_sheet = wb.sheets.active
# if self.is_init:
self.set_style(wb)
col_plus = {'am':0, 'pm':2}
# delete values
for wkday in cls.WKDAYS:
is_dayoff = staffings.staffings[wkday]['am'].is_dayoff
color_font = cls.RED if is_dayoff else cls.BLACK
cls.active_sheet.range((cls.ROW_DATE, cls.COLM_OF_WK[wkday])).api.Font.ColorIndex = color_font
cls.active_sheet.range((cls.ROW_DAY, cls.COLM_OF_WK[wkday])).api.Font.ColorIndex = color_font
for ap in ['am', 'pm']:
staffing = staffings.staffings[wkday][ap]
# staffing.update()
# intreat
col = cls.COLM_OF_WK[wkday] + col_plus[ap]
row_hi = cls.ROW_INTREAT
row_lo = cls.ROW_OFF_NS - len(staffing.nurses_onleave)
# print('%s[%s], row_lo:%s, 休みの数: %s' %(wkday, ap, row_lo, len(staffing.nurses_onleave)))
self.delete_values(row_hi, row_lo, col)
# print('delete done intreat')
# booth
# col += 1
row_hi_booth, row_lo_booth = cls.ROWS_DOCTOR
self.delete_values(row_hi_booth, row_lo_booth, col+1)
# print('delete done for booth')
# add nurses
# if not is_dayoff:
# add delegate
delegate_value = ''
try:
delegate_value = staffing.delegate['name']
if not staffing.delegate['main']:
delegate_value = "(代)" + delegate_value
print('not true')
if add_values:
self.add_values(row_hi, col, delegate_value)
row_hi +=1
except:
pass
# add leader
leader_value = copy.deepcopy(staffing.leader)
# try:
if (len(leader_value)>0):
if leader_value == staffing.delegate['name']:
leader_value = "L" + delegate_value
row_hi -= 1
else:
leader_value = "L" + leader_value
if add_values:
self.add_values(row_hi, col, leader_value)
row_hi += 1
# except:
# raise Exception('%s[%s]でエラー' %(wkday, ap))
if add_values:
self.add_values(row_hi, col, staffing.nurses_intreat)
if not is_dayoff:
self.add_values_inbooth(col+1, staffing.booths_info.booths)
def set_style(self, wb):
'''
'''
col_plus = {'am':0, 'pm':2}
cls = PrintWklySchedule
cls.active_sheet = wb.sheets.active
# sub function
def _set_init_style(row_hi, row_lo, col):
cls.active_sheet.range(
(row_hi, col), (row_lo, col)
).api.Font.Size = cls.FONT_SIZE
cls.active_sheet.range(
(row_hi, col), (row_lo, col)
).api.Font.ColorIndex = cls.BLACK
for wkday in cls.WKDAYS:
for ap in ['am', 'pm']:
col = cls.COLM_OF_WK[wkday] + col_plus[ap]
row_hi = cls.ROW_INTREAT
row_lo = cls.ROW_OFF_NS
# self.delete_values(row_hi, row_lo, col)
_set_init_style(row_hi, row_lo, col)
# in booth
col +=1
row_hi = cls.ROWS_DOCTOR[0]
row_lo = cls.ROWS_DOCTOR[1]
_set_init_style(row_hi, row_lo, col)
def allocate_nurses(wb):
'''
wb: xw.Book
'''
# staffings = load_wkly_schedule(wb) # model
printwk = PrintWklySchedule()
staffings = Staffings(wb)
# update
staffings.update()
# printwk.set_staffings(staffings)
printwk.replace_nurses(wb, staffings) # view
#% main
if __name__ == '__main__':
#%% interface
dir_xlsx = 'x:/dir/to/xlsx' # need change
dir_library= 'x:/dir/to/scripts' # need change
sys.path.append(dir_library)
import allocate_nurse as an
fname_xlsx = '週間業務表.xls' # need change
path_xlsx = os.path.join(dir_xlsx, fname_xlsx)
wb = xw.Book(path_xlsx)
# an.allocate_nurses(wb)
printwk = PrintWklySchedule() # viewmodel
staffings = Staffings(wb) # model
# update
staffings.update()
printwk.replace_nurses(wb, staffings) # view
Raw
load_duty_table.py
#%% import
import os
import pandas as pd
import xlwings as xw
import numpy as np
import PySimpleGUI as sg
import copy
import re
#%% class
class OffSchedule:
year_month: dict
ROW_TOP: int = 13
code_table: dict
ROW_DATE: int = 11
COL_POSITION = 3
COL_NAME = 4
COL_DAY1 = 10 # 1日
SHEET_CODE_TABLE = '勤務記号一覧'
def __init__(self, workbook):
self.wb = workbook
self.wksheet = self.set_sheet('勤務表')
self.nurses: list[dict] = self.set_nurses()
self.dict_row_nurse:dict = self.set_row_nurse_dict()
self.rows = [nurse['row'] for nurse in self.nurses]
OffSchedule.year_month = self.set_month()
OffSchedule.code_table = self.set_code_table()
def set_sheet(self, sheetname):
'''
'''
sheetnames = [sheet.name for sheet in self.wb.sheets]
idx = sheetnames.index(sheetname)
return self.wb.sheets[idx]
def set_nurses(self):
'''
'''
cls = OffSchedule
r1 = cls.ROW_TOP
r2 = r1 + 50
name_values = list(filter(None,self.wksheet.range((r1, cls.COL_NAME), (r2, cls.COL_NAME)).value))
names = [{'last_name':name.split()[0], 'first_name':name.split()[1]} for name in name_values]
positions = list(filter(None, self.wksheet.range((r1, cls.COL_POSITION), (r2, cls.COL_POSITION)).value))
idxs_nurses_regular = [idx for idx, item in enumerate(positions) if not '看護補助' in item]
nurses_regular = list(np.array(names)[idxs_nurses_regular])
rows_nurses_regular = np.array(idxs_nurses_regular)*2 + cls.ROW_TOP
nurses_out = [{'name':name['last_name'], 'row':row} for name, row in zip(nurses_regular, rows_nurses_regular)]
return nurses_out
def set_row_nurse_dict(self):
'''
'''
rows = [nurse['row'] for nurse in self.nurses]
names = [nurse['name'] for nurse in self.nurses]
dict_out = dict(zip(rows, names))
# for row,name in zip(rows, names):
# dict_out[row] = name
return dict_out
def set_month(self):
'''
'''
value_yr_mth = self.wksheet.range((6,2)).value # e.g. 令和04年01月から1ヵ月
era = value_yr_mth[0:2]
yr = int(re.findall('([0-9]{2})年', value_yr_mth)[0])
mth = int(re.findall('([0-9]){2}月', value_yr_mth)[0])
return {'era':era, 'year':yr, 'month':mth}
def set_code_table(self):
'''
set cls.code_table
'''
cls = OffSchedule
sheet_code_table = self.set_sheet(cls.SHEET_CODE_TABLE)
col_code = 3
col_time_start = 6
col_time_end = 7
row_init = 3
row_end = 78
values = lambda c: list(sheet_code_table.range((row_init, c), (row_end, c)).value)
starts = values(col_time_start)
ends = values(col_time_end)
codes = values(col_code)
dict_code = dict.fromkeys(codes)
for idx, code in enumerate(codes):
start_time:int
end_time: int
if starts[idx] is None:
start_time = None
end_time = None
else:
start_time = int(starts[idx][:2] + starts[idx][3:])
end_time = int(ends[idx][:2] + ends[idx][3:])
dict_code[code] = {'start':start_time, 'end':end_time}
dict_out = {}
for k, v in dict_code.items():
ampm = self._get_ampm_from_times(v)
dict_out[k] = ampm
# not
return dict_out
def _get_ampm_from_times(self, dict_times):
'''
get 'am','pm','ampm'
'''
start_time = dict_times['start']
end_time = dict_times['end']
if start_time is None:
return 'off'
elif (0 < start_time <=1000) and (end_time <=1300):
return 'am'
elif (0 < start_time<=1000) and (end_time>1300):
return 'ampm'
elif (start_time > 1000) and (end_time>1300):
return 'pm'
else:
return 'others'
def get_off_nurses(self, day, print_info=True):
'''
'''
cls = OffSchedule
col = cls.COL_DAY1 + day -1
dayofthemonth = int(self.wksheet.range((cls.ROW_DATE, col)).value)
if print_info:
print('%s/%sのスケジュール' %(cls.year_month['month'], dayofthemonth))
dict_off = {'am':[], 'pm':[]}
for row in self.rows:
name = self.dict_row_nurse[row]
value = self.wksheet.range((row, col)).value
duty = cls.code_table[value]
if print_info:
print('%s: %s -> %s' %(name, value, duty))
if duty == 'am':
dict_off['pm'].append(name)
elif duty == 'pm':
dict_off['am'].append(name)
elif duty == 'off':
dict_off['am'].append(name)
dict_off['pm'].append(name)
elif duty == 'ampm':
pass
else:
raise Exception('%sの勤務が%sでam/pm/ampm/offのどれにも当てはまっていません' %(
name, duty
))
# 午後の'中島さん'は外す
try:
dict_off['pm'].remove('中島')
print("中島さんを午後休みのメンバーから外した(勤務のメンバーにそもそも入っていない)")
except:
pass
return dict_off
if __name__=='__main__':
dir_xlsx = 'X:/dir/to/xlsx' # need change
fname_xlsx = '勤務予定表.xls' # need change
path_xlsx = os.path.join(dir_xlsx, fname_xlsx)
wb_duty_table = xw.Book(path_xlsx)
offschedule = OffSchedule(wb_duty_table)
day = 12
# print('%s月%s日の休み希望' %(offschedule.year_month['month'], day))
print(offschedule.get_off_nurses(day, print_info=True))
Raw
nurse_daily_allocate.bat
@echo off
cd /d %~dp0
:最小化状態で実行する
if not "%X_MIMIMIZED%"=="1" (
set X_MIMIMIZED=1
start /min cmd /c,"%~0" %*
exit
)
rem 仮想環境の立ち上げ
call C:\Users\my_user_name\anaconda3\Scripts\activate.bat
rem 環境を変える時
call activate mne
set dt2=%date:~0,10%
set dt2=%dt2:/=%
set dt2=%dt2: =0%
set tm2=%time:~0,8%
set tm2=%tm2::=%
set tm2=%tm2: =0%
set fname2=error_%dt2%_%tm2%.log
set fname=log_%dt2%_%tm2%.log
rem .pyファイルの実行
python x:/python/script/window_allocate_nurses.py 2>> x:/python/script/log/%fname2% >> x:/python/script/log/%fname%
Raw
window_allocate_nurse.py
#%% import
import sys, os
import xlwings as xw
import PySimpleGUI as sg
dir_library = 'x:/dir/to/library' # need change
sys.path.append(dir_library)
import load_duty_table as ld
import allocate_nurse as an
from datetime import datetime as dt
import shutil, time
#% main: interface
dir_xlsx = 'x:/dir/to/xlsx' # need change
dir_xlsx_backup = 'x:/dir/to/excel_backup' # need change
fname_xlsx = '週間業務表.xls' # need change
path_xlsx = os.path.join(dir_xlsx, fname_xlsx)
base, ext = fname_xlsx.split('.')
fname_backup = base + dt.now().strftime('%Y%m%d%H%M%S.')
fname_backup_xlsx = fname_backup + ext
fname_backup_pdf = fname_backup + 'pdf'
path_xlsx_backup = os.path.join(dir_xlsx_backup, fname_backup_xlsx)
path_pdf = os.path.join(dir_xlsx_backup, fname_backup_pdf)
if not os.path.exists(path_xlsx):
raise Exception('ファイルが存在しない')
# self.set_workbook(path_xlsx)
wb = xw.Book(path_xlsx)
''' if backup is necessary
shutil.copyfile(path_xlsx, path_xlsx_backup)
wb.to_pdf(path_pdf)
'''
thm = 'SystemDefault1'
sg.theme(thm)
font = ('YuGothic Medium', 18)
def layout_main():
layout = [
[sg.Text("週間業務予定表を作成したい週を表示して、'配置'ボタンをクリックしてください", font=font)],
[sg.Button('配置', font=font), sg.Button('キャンセル', font=font)],
[sg.Text(' ')],
[sg.Text('休み希望を入力したい時は下のボタンをクリックしてください', font=font)],
[sg.Button('休み希望の入力', key='input_off', disabled=True, font=font),
sg.Button('勤務表ファイルの選択', font=font)],
[sg.Text('勤務表ファイルのパス: 未選択', key='path_duty_table', font=font)]
]
return layout
def layout_exception():
layout = [
[sg.Text(e, key='msg_err')],
[sg.Text("一旦終了します。")],
[sg.Button('OK')]
]
return layout
win_main = sg.Window('看護師配置', layout_main(), keep_on_top=True)
printwk = an.PrintWklySchedule() # viewmodel
staffings = an.Staffings(wb) # model
offschedule = None
try:
while True:
event, values = win_main.read()
print("value is %s" %(values))
print("イベントは%s" %(event))
if event in (sg.WIN_CLOSED, 'キャンセル'):
break
if event in ('勤務表ファイルの選択'):
path_duty_table = sg.popup_get_file('勤務表のエクセルファイルを選択',
initial_folder=dir_xlsx, keep_on_top=True, font=font)
if path_duty_table is not None:
wb_duty_table = xw.Book(path_duty_table)
offschedule = ld.OffSchedule(wb_duty_table)
time.sleep(1)
wb.activate(steal_focus=True)
win_main['path_duty_table'].update("勤務表ファイルのパス: "+ path_duty_table)
win_main['input_off'].update(disabled=False)
if event in ('input_off'):
printwk.replace_off_nurses(wb, offschedule)
if event in ('配置'):
staffings.update()
printwk.replace_nurses(wb, staffings) # view an.allocate_nurses(wb)
win_main.close()
except Exception as e:
win_main.close()
window_exception = sg.Window(
'エラー発生',layout_exception(), size=(800,200),
# auto_size_buttons=True
keep_on_top=True,
resizable=True
)
evnt_exception, values_exception = window_exception.read()
window_exception.close()
raise Exception(e)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment