勤務表半自動更新ツール（Python）
update_attendance_sheet.py
# update_attendance.py
#! /usr/bin/env python
from calendar import week
from encodings import utf_8
from math import nan
import os
from symbol import pass_stmt
import sys
from time import time
from unittest import result
import pandas as pd
from ast import JoinedStr
from distutils.command import config
from ntpath import join
import configparser
import datetime
import re
class MemberType():
PROPER = 0
PARTNER = 1
class Member:
def __init__(self, member_type:MemberType):
self.member_type = member_type # 0:Proper, 1:Partner
def round_time(self, update_list:list, update_idx:int, time_table_list:list, time_table_idx:int, week_list:list):
if time_table_idx%5 == 0:
update_list[update_idx][week_list[0]] = time_table_list[time_table_idx]
elif time_table_idx%5 == 1:
update_list[update_idx][week_list[1]] = time_table_list[time_table_idx]
elif time_table_idx%5 == 2:
update_list[update_idx][week_list[2]] = time_table_list[time_table_idx]
elif time_table_idx%5 == 3:
update_list[update_idx][week_list[3]] = time_table_list[time_table_idx]
elif time_table_idx%5 == 4:
update_list[update_idx][week_list[4]] = time_table_list[time_table_idx]
else:
pass
def remove_ravel(self, update_list:list, update_idx:int, week_list:list):
for i in range(len(week_list)):
if type(update_list[update_idx][week_list[i]]) == datetime.datetime:
pass
elif type(update_list[update_idx][week_list[i]]) == float:
update_list[update_idx][week_list[i]] = nan
elif type(update_list[update_idx][week_list[i]]) == str:
if re.match(pattern=r'((0?[0-9]|1[0-9])|2[0-3]):[0-5][0-9]', string=update_list[update_idx][week_list[i]]) != None:
update_item = re.match(pattern=r'((0?[0-9]|1[0-9])|2[0-3]):[0-5][0-9]', string=update_list[update_idx][week_list[i]]).group()
update_list[update_idx][week_list[i]] = DateTime().conv_datetime('1900-01-01 '+ update_item + ':00')
else:
update_list[update_idx][week_list[i]] = nan
else:
update_list[update_idx][week_list[i]] = nan
return update_list
def update_attendance_time(self, member_list:list, time_table_list:list):
week_list:list = [5, 9, 13, 17, 21]
time_table_attendance_list:list = []
member_attendance_time_list:list = []
# error case
if (len(time_table_list) != len(member_list)):
return
# time table type change
for j in range(len(time_table_list)):
time_table_attendance = []
for i in range(len(time_table_list[j].values)):
time_table_attendance.append(DateTime().conv_datetime(date_str='1900-01-01 '+ time_table_list[j].values[i][0]+ ':00'))
time_table_attendance_list.append(time_table_attendance)
# stamping time extract datetime
for j in range(len(member_list)):
member_attendance_time = []
for i in range(len(week_list)):
member_attendance_time = DateTime().judge_datetime(member_list=member_attendance_time, datetime_str=member_list[j][week_list[i]])
member_attendance_time_list.append(member_attendance_time)
# update attendance time
for j in range(len(member_list)):
self.remove_ravel(update_list=member_list, update_idx=j, week_list=week_list)
if self.member_type == MemberType.PARTNER:
self.round_up_15_min(update_list=member_list, update_idx=j,week_list=week_list)
for i in range(len(time_table_attendance_list[j])):
if type(member_attendance_time_list[j][i]) == float:
continue
# case stamping time <= table time
if member_attendance_time_list[j][i].time() <= time_table_attendance_list[j][i].time():
# round to time table hours
self.round_time(update_list=member_list, update_idx=j, time_table_list=time_table_attendance_list[j],time_table_idx=i, week_list=week_list)
return member_list
def update_leaving_work(self, member_list:list, time_table_list:list):
week_list:list = [8, 12, 16, 20, 24]
time_table_leaving_work_list:list = []
time_table_overtime_start_list:list = []
member_leaving_work_time_list:list = []
# error case
if (len(time_table_list) != len(member_list)):
return
# time table type change
for j in range(len(time_table_list)):
time_table_leaving_work = []
time_table_overtime_start = []
for i in range(len(time_table_list[j].values)):
time_table_leaving_work.append(DateTime().conv_datetime(date_str='1900-01-01 '+ time_table_list[j].values[i][1]+ ':00'))
time_table_overtime_start.append(DateTime().conv_datetime(date_str='1900-01-01 '+ time_table_list[j].values[i][2]+ ':00'))
time_table_leaving_work_list.append(time_table_leaving_work)
time_table_overtime_start_list.append(time_table_overtime_start)
# stamping time extract datetime
for j in range(len(member_list)):
member_leaving_work_time = []
for i in range(len(week_list)):
member_leaving_work_time = DateTime().judge_datetime(member_list=member_leaving_work_time, datetime_str=member_list[j][week_list[i]])
member_leaving_work_time_list.append(member_leaving_work_time)
# update leaving work time
for j in range(len(member_list)):
self.remove_ravel(update_list=member_list, update_idx=j, week_list=week_list)
if self.member_type == MemberType.PARTNER:
self.round_down_15_min(update_list=member_list, update_idx=j,week_list=week_list)
for i in range(len(time_table_leaving_work_list[j])):
# irregular case
if type(member_leaving_work_time_list[j][i]) == float:
continue
# case stamping time >= table time
elif member_leaving_work_time_list[j][i].time() >= time_table_leaving_work_list[j][i].time():
# case stamping time < overtime start time
if member_leaving_work_time_list[j][i].time() < time_table_overtime_start_list[j][i].time():
# round to time table hours
self.round_time(update_list=member_list, update_idx=j, time_table_list=time_table_leaving_work_list[j],time_table_idx=i, week_list=week_list)
return member_list
def round_down_15_min(self, update_list:list, update_idx:int, week_list:list):
INTERVAL_MINUTE:int = 15
for i in range(len(week_list)):
if type(update_list[update_idx][week_list[i]]) == datetime.datetime:
if update_list[update_idx][week_list[i]].minute%15 == 0:
pass
else:
tmp = update_list[update_idx][week_list[i]].replace(minute = update_list[update_idx][week_list[i]].minute - (update_list[update_idx][week_list[i]].minute % INTERVAL_MINUTE), second=0, microsecond=0)
update_list[update_idx][week_list[i]] = tmp
return update_list
def round_up_15_min(self, update_list:list, update_idx:int, week_list:list):
INTERVAL_MINUTE:int = 15
for i in range(len(week_list)):
if type(update_list[update_idx][week_list[i]]) == datetime.datetime:
if update_list[update_idx][week_list[i]].minute%15 == 0:
pass
elif ((update_list[update_idx][week_list[i]].minute // INTERVAL_MINUTE) +1)*INTERVAL_MINUTE == 60:
tmp = update_list[update_idx][week_list[i]].replace(hour = update_list[update_idx][week_list[i]].hour+1 ,minute = (((update_list[update_idx][week_list[i]].minute // INTERVAL_MINUTE) +1)*INTERVAL_MINUTE)%60, second=0, microsecond=0)
update_list[update_idx][week_list[i]] = tmp
else:
tmp = update_list[update_idx][week_list[i]].replace(minute = (((update_list[update_idx][week_list[i]].minute // INTERVAL_MINUTE) +1)*INTERVAL_MINUTE), second=0, microsecond=0)
update_list[update_idx][week_list[i]] = tmp
return update_list
class DateTime:
def __init__(self):
pass
def conv_datetime(self, date_str:str):
return datetime.datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
def judge_datetime(self, member_list:list, datetime_str:str):
if type(datetime_str) == datetime.datetime:
member_list.append(datetime_str)
elif type(datetime_str) == float:
member_list.append(nan)
else:
if re.match(pattern=r'((0?[0-9]|1[0-9])|2[0-3]):[0-5][0-9]', string=datetime_str) != None:
member_item = re.match(pattern=r'((0?[0-9]|1[0-9])|2[0-3]):[0-5][0-9]', string=datetime_str).group()
member_list.append(self.conv_datetime('1900-01-01 '+ member_item + ':00'))
else:
member_list.append(nan)
return member_list
class CsvController:
def __init__(self, csv_path:str):
self.csv_path = csv_path
# read csv
def read_csv(self):
root = os.path.dirname(os.path.abspath(sys.argv[0]))
return pd.read_csv(os.path.join(root, self.csv_path), header=0, index_col=0)
class ExcelController:
def __init__(self, elsx_path:str):
self.elsx_path = elsx_path
# read elsx
def read_elsx(self):
root = os.path.dirname(os.path.abspath(sys.argv[0]))
return pd.read_excel(os.path.join(root, self.elsx_path), sheet_name=None, header=0)
# write elsx
def write_elsx(self, data_list:list, sheet_name:str):
root = os.path.dirname(os.path.abspath(sys.argv[0]))
df = pd.DataFrame(data_list)
return df.to_excel(os.path.join(root, self.elsx_path),sheet_name=sheet_name, index=False, header=2)
class ConfigController:
def __init__(self, config_file_path:str):
self.config_file_path = config_file_path
def read_config(self):
root = os.path.dirname(os.path.abspath(sys.argv[0]))
config = configparser.ConfigParser()
# join root directory
config.read(os.path.join(root, self.config_file_path), encoding='utf-8')
return config
def extract_weekly_clocking_path(self, config):
clocking_path = config.get('SETTING', 'WeeklyClockingPath')
return clocking_path
def extract_target_list_path(self, config):
list_path = config.get('SETTING', 'TargetListPath')
return list_path
def extract_target_sheet(self, config):
target_sheet = config.get('DEFAULT', 'TargetListSheetNane')
return target_sheet
def extract_weekly_proper_sheet(self, config):
proper_sheet = config.get('DEFAULT', 'WeeklyClockingProperSheetNane')
return proper_sheet
def extract_weekly_partner_sheet(self, config):
partner_sheet = config.get('DEFAULT', 'WeeklyClockingPartnerSheetNane')
return partner_sheet
def extract_output_proper_sheet(self, config):
proper_sheet = config.get('DEFAULT', 'OutputProperSheetName')
return proper_sheet
def extract_output_partner_sheet(self, config):
partner_sheet = config.get('DEFAULT', 'OutputPartnerSheetName')
return partner_sheet
def extract_output_proper_attendance_coloumn(self, config):
attendance_coloumn = config.get('DEFAULT', 'OutputProperAttendanceColumn')
return attendance_coloumn
def extract_output_proper_leaving_coloumn(self, config):
leaving_coloumn = config.get('DEFAULT', 'OutputProperLeavingColumn')
return leaving_coloumn
def extract_output_partner_attendance_coloumn(self, config):
attendance_coloumn = config.get('DEFAULT', 'OutputPartnerAttendanceColumn')
return attendance_coloumn
def extract_output_partner_leaving_coloumn(self, config):
leaving_coloumn = config.get('DEFAULT', 'OutputPartnerLeavingColumn')
return leaving_coloumn
class DataFrameController:
def __init__(self, sheetName:str):
self.sheetName = sheetName
def get_name(self, df):
name_list = []
target_list = df[self.sheetName]._values
# get name roop
for i in range(len(target_list)):
name_list.append(target_list[i][0])
return name_list
def get_id(self, df):
id_list = []
target_list = df[self.sheetName]._values
# get id roop
for i in range(len(target_list)):
id_list.append(target_list[i][1])
return id_list
def get_proper_id(self, df):
id_list = []
target_list = df[self.sheetName]._values
# get id roop
for i in range(len(target_list)):
if ('S' in target_list[i][1]):
id_list.append(target_list[i][1])
return id_list
def get_partner_id(self, df):
id_list = []
target_list = df[self.sheetName]._values
# get id roop
for i in range(len(target_list)):
if ('S' not in target_list[i][1]):
id_list.append(target_list[i][1])
return id_list
def get_time_table(self, df, id_list):
time_table_list= df[self.sheetName]._values
extract_list = []
# get time table roop
for j in range(len(id_list)):
for i in range(len(time_table_list)):
result = id_list[j] in time_table_list[i]
if result == True:
extract_list.append(time_table_list[i][2])
return extract_list
def get_proper_current_elsx(self, df):
elsx_list = []
target_list = df[self.sheetName]._values
# get elsx list roop
for i in range(len(target_list)):
if ('S' in target_list[i][1]):
elsx_list.append(target_list[i][3])
return elsx_list
def get_proper_next_elsx(self, df):
elsx_list = []
target_list = df[self.sheetName]._values
# get elsx list roop
for i in range(len(target_list)):
if ('S' in target_list[i][1]):
elsx_list.append(target_list[i][4])
return elsx_list
def get_partner_current_elsx(self, df):
elsx_list = []
target_list = df[self.sheetName]._values
# get elsx list roop
for i in range(len(target_list)):
if ('S' not in target_list[i][1]):
elsx_list.append(target_list[i][3])
return elsx_list
def get_partner_next_elsx(self, df):
elsx_list = []
target_list = df[self.sheetName]._values
# get elsx list roop
for i in range(len(target_list)):
if ('S' not in target_list[i][1]):
elsx_list.append(target_list[i][4])
return elsx_list
def extract_weekly_clocking(self, df, id_list:list):
weekly_clocking_list = df[self.sheetName]._values
extract_list = []
# extract list with id
for i in range(len(id_list)):
for j in range(len(weekly_clocking_list)):
result = id_list[i] in weekly_clocking_list[j]
if result == True:
extract_list.append(weekly_clocking_list[j])
return extract_list
def get_period(self, df):
weekly_clocking_list = df[self.sheetName].columns
period_list = []
# get period roop
for i in range(len(weekly_clocking_list)):
if re.match(pattern=r'([1-9]|[12][0-9]|3[01])日', string=weekly_clocking_list[i]) != None:
period_item = re.sub(pattern = r"\D", repl="", string=weekly_clocking_list[i])
period_list.append(period_item)
return period_list
def update_data_frame(self, sheet_name:str, attendance_column:int, leaving_column:int, period_list:list, member_list, current_month_df, next_month_df):
attendance_week_list = [5, 9, 13, 17, 21]
leaving_week_list = [8, 12, 16, 20, 24]
current_month_list:list = []
next_month_list:list = []
all_current_month_list:list = []
all_next_month_list:list = []
next_month_flag:bool = False
start_period:int = 0
# error case
if (len(current_month_df) != len(member_list)):
return
for k in range(len(period_list)):
if len(period_list) == k+1:
break
if period_list[k] > period_list[k+1]:
next_month_flag = True
start_period = period_list[k+1]
break
for j in range(len(current_month_df)):
current_row = []
current_month_list = []
for i in range(len(current_month_df[j][sheet_name])):
current_row = current_month_df[j][sheet_name].values[i]
# match period
if float(current_month_df[j][sheet_name].values[i][0]) == float(period_list[0]):
current_row[int(attendance_column)] = member_list[j][attendance_week_list[0]]
current_row[int(leaving_column)] = member_list[j][leaving_week_list[0]]
elif float(current_month_df[j][sheet_name].values[i][0]) == float(period_list[1]) and float(period_list[1]) < float(start_period):
current_row[int(attendance_column)] = member_list[j][attendance_week_list[1]]
current_row[int(leaving_column)] = member_list[j][leaving_week_list[1]]
elif float(current_month_df[j][sheet_name].values[i][0]) == float(period_list[2]) and float(period_list[2]) < float(start_period):
current_row[int(attendance_column)] = member_list[j][attendance_week_list[2]]
current_row[int(leaving_column)] = member_list[j][leaving_week_list[2]]
elif float(current_month_df[j][sheet_name].values[i][0]) == float(period_list[3]) and float(period_list[3]) < float(start_period):
current_row[int(attendance_column)] = member_list[j][attendance_week_list[3]]
current_row[int(leaving_column)] = member_list[j][leaving_week_list[3]]
elif float(current_month_df[j][sheet_name].values[i][0]) == float(period_list[4]) and float(period_list[4]) < float(start_period):
current_row[int(attendance_column)] = member_list[j][attendance_week_list[4]]
current_row[int(leaving_column)] = member_list[j][leaving_week_list[4]]
else:
pass
current_month_list.append(current_row)
all_current_month_list.append(current_month_list)
for j in range(len(next_month_df)):
next_row = []
next_month_list = []
for i in range(len(next_month_df[j][sheet_name])):
next_row = next_month_df[j][sheet_name].values[i]
# match period
if True == next_month_flag:
if float(next_month_df[j][sheet_name].values[i][0]) == float(period_list[1]) and float(period_list[1]) >= float(start_period):
next_row[int(attendance_column)] = member_list[j][attendance_week_list[1]]
next_row[int(leaving_column)] = member_list[j][leaving_week_list[1]]
elif float(next_month_df[j][sheet_name].values[i][0]) == float(period_list[2]) and float(period_list[2]) >= float(start_period):
next_row[int(attendance_column)] = member_list[j][attendance_week_list[2]]
next_row[int(leaving_column)] = member_list[j][leaving_week_list[2]]
elif float(next_month_df[j][sheet_name].values[i][0]) == float(period_list[3]) and float(period_list[3]) >= float(start_period):
next_row[int(attendance_column)] = member_list[j][attendance_week_list[3]]
next_row[int(leaving_column)] = member_list[j][leaving_week_list[3]]
elif float(next_month_df[j][sheet_name].values[i][0]) == float(period_list[4]) and float(period_list[4]) >= float(start_period):
next_row[int(attendance_column)] = member_list[j][attendance_week_list[4]]
next_row[int(leaving_column)] = member_list[j][leaving_week_list[4]]
else:
pass
next_month_list.append(next_row)
all_next_month_list.append(next_month_list)
return all_current_month_list, all_next_month_list
def main():
proper_current_excel_df:list = []
proper_next_excel_df:list = []
partner_current_excel_df:list = []
partner_next_excel_df:list = []
## ConfigController class field
config_controller = ConfigController(config_file_path="config.ini")
config = config_controller.read_config()
# config item parameter
clocking_path = config_controller.extract_weekly_clocking_path(config=config)
list_path = config_controller.extract_target_list_path(config=config)
target_sheet_name = config_controller.extract_target_sheet(config=config)
weekly_proper_sheet_name = config_controller.extract_weekly_proper_sheet(config=config)
weekly_partner_sheet_name = config_controller.extract_weekly_partner_sheet(config=config)
output_proper_sheet_name = config_controller.extract_output_proper_sheet(config=config)
output_proper_attendance_column = config_controller.extract_output_proper_attendance_coloumn(config=config)
output_proper_leaving_column = config_controller.extract_output_proper_leaving_coloumn(config=config)
output_partner_sheet_name = config_controller.extract_output_partner_sheet(config=config)
output_partner_attendance_column = config_controller.extract_output_partner_attendance_coloumn(config=config)
output_partner_leaving_column = config_controller.extract_output_partner_leaving_coloumn(config=config)
## ExcelController class field
weekly_clocking_df = ExcelController(elsx_path=clocking_path).read_elsx()
target_list_df = ExcelController(elsx_path=list_path).read_elsx()
## DataFrameController class field
id_list = DataFrameController(sheetName=target_sheet_name).get_id(df=target_list_df)
proper_id_list = DataFrameController(sheetName=target_sheet_name).get_proper_id(df=target_list_df)
partner_id_list = DataFrameController(sheetName=target_sheet_name).get_partner_id(df=target_list_df)
proper_time_table_list = DataFrameController(sheetName=target_sheet_name).get_time_table(df=target_list_df, id_list=proper_id_list)
partner_time_table_list = DataFrameController(sheetName=target_sheet_name).get_time_table(df=target_list_df, id_list=partner_id_list)
proper_current_elsx_list = DataFrameController(sheetName=target_sheet_name).get_proper_current_elsx(df=target_list_df)
proper_next_elsx_list = DataFrameController(sheetName=target_sheet_name).get_proper_next_elsx(df=target_list_df)
partner_current_elsx_list = DataFrameController(sheetName=target_sheet_name).get_partner_current_elsx(df=target_list_df)
partner_next_elsx_list = DataFrameController(sheetName=target_sheet_name).get_partner_next_elsx(df=target_list_df)
weekly_proper_list = DataFrameController(sheetName=weekly_proper_sheet_name).extract_weekly_clocking(df=weekly_clocking_df, id_list=id_list)
weekly_partner_list = DataFrameController(sheetName=weekly_partner_sheet_name).extract_weekly_clocking(df=weekly_clocking_df, id_list=id_list)
proper_period = DataFrameController(sheetName=weekly_proper_sheet_name).get_period(df=weekly_clocking_df)
partner_period = DataFrameController(sheetName=weekly_partner_sheet_name).get_period(df=weekly_clocking_df)
## ExcelController class field
for i in range(len(proper_current_elsx_list)):
proper_current_excel_df.append(ExcelController(elsx_path=proper_current_elsx_list[i]).read_elsx())
proper_next_excel_df.append(ExcelController(elsx_path=proper_next_elsx_list[i]).read_elsx())
for i in range(len(partner_current_elsx_list)):
partner_current_excel_df.append(ExcelController(elsx_path=partner_current_elsx_list[i]).read_elsx())
partner_next_excel_df.append(ExcelController(elsx_path=partner_next_elsx_list[i]).read_elsx())
## CsvController class field
proper_time_table:list = []
for i in range(len(proper_time_table_list)):
proper_time_table.append(CsvController(csv_path=proper_time_table_list[i]).read_csv())
partner_time_table:list = []
for i in range(len(partner_time_table_list)):
partner_time_table.append(CsvController(csv_path=partner_time_table_list[i]).read_csv())
## Member class field
proper_controller = Member(member_type=MemberType.PROPER)
proper_controller.update_attendance_time(member_list=weekly_proper_list, time_table_list=proper_time_table)
proper_controller.update_leaving_work(member_list=weekly_proper_list, time_table_list=proper_time_table)
partner_controller = Member(member_type=MemberType.PARTNER)
partner_controller.update_attendance_time(member_list=weekly_partner_list, time_table_list=partner_time_table)
partner_controller.update_leaving_work(member_list=weekly_partner_list, time_table_list=partner_time_table)
current_proper_list, next_proper_list = DataFrameController(sheetName='').update_data_frame(sheet_name=output_proper_sheet_name, attendance_column=output_proper_attendance_column, leaving_column=output_proper_leaving_column, period_list=proper_period, member_list=weekly_proper_list, current_month_df=proper_current_excel_df, next_month_df=proper_next_excel_df)
current_partner_list, next_partner_list = DataFrameController(sheetName='').update_data_frame(sheet_name=output_partner_sheet_name, attendance_column=output_partner_attendance_column, leaving_column=output_partner_leaving_column, period_list=partner_period, member_list=weekly_partner_list, current_month_df=partner_current_excel_df, next_month_df=partner_next_excel_df)
## ExcelController class field
for i in range(len(proper_current_elsx_list)):
ExcelController(elsx_path=proper_current_elsx_list[i]).write_elsx(data_list=current_proper_list[i], sheet_name=output_proper_sheet_name)
ExcelController(elsx_path=proper_next_elsx_list[i]).write_elsx(data_list=next_proper_list[i], sheet_name=output_proper_sheet_name)
for i in range(len(partner_current_elsx_list)):
ExcelController(elsx_path=partner_current_elsx_list[i]).write_elsx(data_list=current_partner_list[i], sheet_name=output_partner_sheet_name)
ExcelController(elsx_path=partner_next_elsx_list[i]).write_elsx(data_list=next_partner_list[i], sheet_name=output_partner_sheet_name)
main()[SETTING]
WeeklyClockingPath = input\一週間の打刻状況.xlsx
TargetListPath = input\対象者リスト.xlsx
[DEFAULT]
WeeklyClockingPartnerSheetNane = パートナー
WeeklyClockingProperSheetNane = プロパ
TargetListSheetNane = シート1
OutputProperSheetName = シート1
OutputProperAttendanceColumn = 2
OutputProperLeavingColumn = 3
OutputPartnerSheetName = シート1
OutputPartnerAttendanceColumn = 2
OutputPartnerLeavingColumn = 3input配下
まだまだ作りかけですねー。
config周りは参考になるかと思います。