def run(self):
targetYear = self.getForm('-year')
if targetYear == '':
self.writeLog('parameter:-year is not set')
return
#祝祭日一覧
holidayList = DateUtilLib.getHolidayList(
StringOperationLib.toInt(targetYear))
values = []
#BulkInsert処理
for day in holidayList:
holidayDate = StringOperationLib.toString(day[0])
holidayMonth = StringOperationLib.toString(
holidayDate[0:holidayDate.rfind('-')])
holidayName = DateUtilLib.getHolidayName(day[0])
values.append([holidayDate, holidayMonth, holidayName])
dao = HolidayDao(self.db)
dao.addBulkCol(HolidayDao.COL_HOLIDAY_DATE)
dao.addBulkCol(HolidayDao.COL_MONTH)
dao.addBulkCol(HolidayDao.COL_HOLIDAY_NAME)
dao.doBulkInsert(values)
self.writeLog(targetYear + '年祝祭日データ登録完了')
return
def makeExpensesExcel(self, targetYm, expensesList, employeeName, path):
expensesName = '経費精算書'
year, month = DateUtilLib.splitYm(targetYm)
lastDay = DateUtilLib.getLastDay(StringOperationLib.toInt(year), StringOperationLib.toInt(month))
ym = DateUtilLib.getYmFormatJapanese(targetYm)
ymd = ym + StringOperationLib.toString(lastDay) + '日'
name = StringOperationLib.replace(employeeName, " ", "")
excel = PythonExcelLib()
excel.setPageA4
excel.setOrientation()
excel.rename(expensesName)
#タイトル等共通部分
self.setCommon(excel, ym, ymd, name, expensesName)
#項目名
self.setExpensesTitle(excel)
#データ
lastRow = self.setListValue(excel, expensesList)
#合計
self.setCostSumValue(excel, lastRow, 'E')
#体裁
self.expensesExcelFormat(excel, copy(lastRow))
excel.save(path + expensesName + '.xlsx')
#オブジェクト解放
del excel
def __init__(self):
self.appId = self.getAppId()
self.appName = self.getAppName()
self.procLogFileName = self.appId + "_" + StringOperationLib.toString(
DateUtilLib.getToday()) + self.logExt
self.errLogFileName = self.appId + "_" + StringOperationLib.toString(
DateUtilLib.getToday()) + "_Err" + self.logExt
def run(self):
self.writeLog('処理開始')
date = self.getForm('-date')
if date == '':
self.writeLog('parameter:-date is not set')
return
#前回データを削除
self.deleteLastSessionData()
#処理する社員情報を取得
employeeList = self.getEmployeeList()
dt = DateUtilLib.toDateTimeDate(date)
dt = StringOperationLib.toString(DateUtilLib.getDateIntervalMonth(dt, -1))
targetYm = StringOperationLib.mid(dt, 1, dt.rfind('-'))
#ループ内
for i in range(len(employeeList)):
userId = StringOperationLib.toString(employeeList[i][EmployeeDao.COL_ID])
name = StringOperationLib.toString(employeeList[i][EmployeeDao.COL_NAME])
loginId = StringOperationLib.toString(employeeList[i][EmployeeDao.COL_LOGIN_ID])
self.writeLog('処理対象社員名 : ' + name)
#個人ディレクトリを作成
employeeDir = self.makeEmployeeDir(name)
#データを取得
trafficList = self.getExpensesList(userId, targetYm, '1')
expensesList = self.getExpensesList(userId, targetYm, '2')
#1件でもあれば、精算書を作成
if len(trafficList) > 0:
self.makeTrafficExcel(targetYm, trafficList, name, employeeDir)
if len(expensesList) > 0:
self.makeExpensesExcel(targetYm, expensesList, name, employeeDir)
#領収書ファイルがあれば、コピー
self.copyReceiptFile(targetYm, loginId, employeeDir)
#Zipにまとめる
self.makeZipFile()
#メール送信
self.sendCostManage(targetYm)
self.writeLog('処理完了')
return
def makeExcel(self, dt, recordMapList, totalList, name):
dt = StringOperationLib.mid(dt, 1, 7)
ym = DateUtilLib.getYmFormatJapanese(dt)
name = StringOperationLib.replace(name, " ", "")
excel = PythonExcelLib()
excel.setPageA4
excel.rename('月間作業実績報告書')
#タイトル等共通部分
self.setCommon(excel, ym, name)
#勤怠表の項目名
self.setTimeRecordTitle(excel)
#勤怠表
lastRow = self.setTimeRecordValue(excel, recordMapList)
#合計行
self.setTimeRecordTotalValue(excel, totalList, copy(lastRow))
#押印欄等
self.setOtherArea(excel, copy(lastRow))
#体裁
self.setFormat(excel, copy(lastRow))
excel.save(Config.getConf('TimeRecordAutoDLinfo', 'download_path') + '本社提出用_' + ym + '勤怠表_' + name + '.xlsx')
#オブジェクト解放
del excel
def mainProc(self, args):
try:
'''
実行引数を受け取る
'''
for num in range(len(args)):
if num > 0:
if args[num][0] == '-':
key = args[num]
value = args[num + 1]
self.form[key] = value
'''
confファイルを読み込み
'''
Config.confLoad('system')
'''
ロガーオープン
'''
self.logger = BaseLogger(self.procLogFileName)
self.logger.fileOpen()
self.logger.writeLog(
"START_TIME=" +
StringOperationLib.toString(DateUtilLib.getTime()))
self.logger.writeLog("START_BATCH_NAME=" + self.appName)
self.logger.writeLog("PARAM_INFO=" + self.appId + " param:" +
" ".join(args))
'''
DB接続
'''
self.db = DbManager()
self.db.connect()
'''
ロジック実行
'''
logic = self.getLogic(self.db, self.logger, self.form)
logic.run()
except Exception as e:
self.errProc(e)
finally:
self.db.disConnect()
self.logger.writeLog(
"END_TIME=" +
StringOperationLib.toString(DateUtilLib.getTime()) + '\r\n')
self.logger.fileClose()
def deleteReceiptFile(self, arr):
#社員情報を取得
eDao = EmployeeDao(self.db)
eDao.addWhereStr(EmployeeDao.COL_LOGIN_ID, Config.getConf('DBinfo', 'admin_user_id'), EmployeeDao.COMP_NOT_EQUAL) #管理者は除外
eList = eDao.doSelectCol(EmployeeDao.COL_LOGIN_ID)
self.writeLog('ディレクトリ削除開始:' + StringOperationLib.toString(DateUtilLib.getToday()))
for i in range(len(eList)):
for j in range(len(arr)):
ym = StringOperationLib.toString(StringOperationLib.left(arr[j], 4) + StringOperationLib.right(arr[j], 2))
user_id = StringOperationLib.toString(eList[i])
dirPath = Config.getConf('RECEIPTinfo', 'receipt_file_path') + user_id + '/' + ym
if FileOperationLib.existDir(dirPath):
FileOperationLib.deleteDir(dirPath)
self.writeLog('ディレクトリ削除 ユーザーID: ' + user_id + ' 対象年月: ' + ym)
self.writeLog('ディレクトリ削除完了:' + StringOperationLib.toString(DateUtilLib.getToday()))
def run(self):
self.writeLog('処理開始')
date = self.getForm('-date')
if date == '':
self.writeLog('parameter:-date is not set')
return
#前回処理分を削除
self.deleteLastSessionData()
#処理する社員情報を取得
employeeMap = self.getEmployeeMap()
dt = DateUtilLib.toDateTimeDate(date)
dt = StringOperationLib.toString(DateUtilLib.getDateIntervalMonth(dt, -1))
#祝祭日マスタを取得しておく
holidayList = self.getHolidayList(dt, date)
#ループ
for id, name in employeeMap.items():
self.writeLog('処理対象社員名 : ' + name)
#勤怠データを取得
timeRecordMaplist = self.getTimeRecordMapList(id, dt, date, holidayList)
totalList = self.getTotalList(id, dt, date)
#Excel作成
self.makeExcel(dt, timeRecordMaplist, totalList, name)
#作成したExcelをZipにまとめる
self.makeZipFile()
#メールで送信する
self.sendTimeSheet(dt)
self.writeLog('処理完了')
return
def copyReceiptFile(self, targetYm, loginId, toPath):
year, month = DateUtilLib.splitYm(targetYm)
ym = StringOperationLib.toString(year) + StringOperationLib.toString(month)
fromPath = FileOperationLib.getFileList(Config.getConf('RECEIPTinfo', 'receipt_file_path') + loginId + '/' + ym + '/')
for file in fromPath:
if not(StringOperationLib.match(FileOperationLib.getFileName(file), '*.xlsx')):
fileName = FileOperationLib.getFileName(file).encode('utf-8', 'surrogateescape').decode('SJIS', 'surrogateescape')
FileOperationLib.copyFile(file, toPath + fileName)
def deleteReceiptFile(self, ids):
#社員情報を取得
eDao = EmployeeDao(self.db)
eDao.addWhereIn(EmployeeDao.COL_ID, ids)
eList = eDao.doSelectCol(EmployeeDao.COL_LOGIN_ID)
self.writeLog('ディレクトリ削除開始:' +
StringOperationLib.toString(DateUtilLib.getToday()))
for i in range(len(eList)):
user_id = StringOperationLib.toString(eList[i])
dirPath = Config.getConf('RECEIPTinfo',
'receipt_file_path') + user_id
#年月は関係なし(ユーザーIDのディレクトリごとまるっと削除する)
if FileOperationLib.existDir(dirPath):
FileOperationLib.deleteDir(dirPath)
self.writeLog('ディレクトリ削除 ユーザーID: ' + user_id)
self.writeLog('ディレクトリ削除完了:' +
StringOperationLib.toString(DateUtilLib.getToday()))
def sendCostManage(self, dt):
ym = DateUtilLib.getYmFormatJapanese(dt)
mail = SendMailLib()
mail.setMailFrom(Config.getConf('MAILinfo', 'admin_mail_from'))
mail.setMailTo(Config.getConf('CostManageAutoDLinfo', 'kanri_mail'))
mail.setMailSubject('【自動送信】' + ym + '経費精算情報')
mail.setMailText(ym + '分の経費精算一覧を送付します。')
mail.setAttach(Config.getConf('CostManageAutoDLinfo', 'output_path') + 'cost_manage.zip')
mail.send()
def sendTimeSheet(self, dt):
dt = StringOperationLib.mid(dt, 1, 7)
ym = DateUtilLib.getYmFormatJapanese(dt)
mail = SendMailLib()
mail.setMailFrom(Config.getConf('MAILinfo', 'admin_mail_from'))
mail.setMailTo(Config.getConf('TimeRecordAutoDLinfo', 'kanri_mail'))
mail.setMailSubject('【自動送信】' + ym + '勤怠表')
mail.setMailText(ym + '分の勤怠表を送付します。')
mail.setAttach(Config.getConf('TimeRecordAutoDLinfo', 'output_path') + 'time_sheet.zip')
mail.send()
def getDbMap(self, employeeId, fromDt, toDt):
dbMap = {}
dao = TimeRecordDao(self.db)
dao.addSelect(TimeRecordDao.COL_WORK_DATE)
dao.addSelect(TimeRecordDao.COL_CLASSIFICATION)
dao.addSelect(TimeRecordDao.COL_START_TIME)
dao.addSelect(TimeRecordDao.COL_END_TIME)
dao.addSelect(TimeRecordDao.COL_BREAK_TIME)
dao.addSelect(TimeRecordDao.COL_PRESCRIBED_TIME)
dao.addSelect(TimeRecordDao.COL_OVER_WORK_TIME)
dao.addSelect(TimeRecordDao.COL_MIDNIGHT_TIME)
dao.addSelect(TimeRecordDao.COL_MIDNIGHT_BREAK_TIME)
dao.addSelect(TimeRecordDao.COL_MIDNIGHT_OVER_WORK_TIME)
dao.addSelect(TimeRecordDao.COL_WORK_TIME)
dao.addSelect(TimeRecordDao.COL_REMARK)
dao.addWhere(TimeRecordDao.COL_EMPLOYEE_ID, str(employeeId))
dao.addWhereStr(TimeRecordDao.COL_WORK_DATE, fromDt, TimeRecordDao.COMP_GREATER_EQUAL)
dao.addWhereStr(TimeRecordDao.COL_WORK_DATE, toDt, TimeRecordDao.COMP_LESS)
dao.addWhereIn(TimeRecordDao.COL_SCENE, [1,3]) #共通と本社用のみ取得してくる
select = dao.doSelect()
for i in range(len(select)):
key = StringOperationLib.toString(select[i][TimeRecordDao.COL_WORK_DATE])
clsVal = self.classMap[select[i][TimeRecordDao.COL_CLASSIFICATION]]
startTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_START_TIME])
endTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_END_TIME])
breakTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_BREAK_TIME])
prescribedTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_PRESCRIBED_TIME])
overWorkTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_OVER_WORK_TIME])
midnightTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_MIDNIGHT_TIME])
midnightBreakTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_MIDNIGHT_BREAK_TIME])
midnightOverWorkTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_MIDNIGHT_OVER_WORK_TIME])
workTime = DateUtilLib.toHm(select[i][TimeRecordDao.COL_WORK_TIME])
remark = select[i][TimeRecordDao.COL_REMARK]
dbMap[key] = [clsVal, startTime, endTime, breakTime, prescribedTime, overWorkTime, midnightTime, midnightBreakTime, midnightOverWorkTime, workTime, remark]
return dbMap
def setTimeRecordTotalValue(self, excel, totalList, lastRow):
row = lastRow
col = 1
excel.setValueR1C1(col, row, '合計')
col+=5
for value in totalList.values():
if value != None and value != 0:
excel.setValueR1C1(col, row, DateUtilLib.toHm(value))
col+=1
row+=1
col = 1
excel.setValueR1C1(col, row, '総時間')
col+=5
for value in totalList.values():
if value != None and value != 0:
excel.setValueR1C1(col, row, self.intToHMM(value))
col+=1
def getExpensesList(self, userId, registYm, inputType):
dao = ExpensesDao(self.db)
dao.addSelect(ExpensesDao.COL_EXPENSES_YMD)
if inputType == '1':
#交通費の場合
dao.addSelect(ExpensesDao.COL_ROUND_TRIP_TYPE)
dao.addSelect(ExpensesDao.COL_TRANSPORT)
dao.addSelect(ExpensesDao.COL_FROM_PLACE)
dao.addSelect(ExpensesDao.COL_TO_PLACE)
elif inputType == '2':
#経費の場合
dao.addSelect(ExpensesDao.COL_PAY_TYPE)
dao.addSelect(ExpensesDao.COL_EXPENSES_TYPE)
dao.addSelect(ExpensesDao.COL_EXPENSES_DETAIL)
dao.addSelect(ExpensesDao.COL_COST)
dao.addWhere(ExpensesDao.COL_EMPLOYEE_ID, userId)
dao.addWhereStr(ExpensesDao.COL_INPUT_TYPE, inputType)
dao.addWhereStr(ExpensesDao.COL_REGIST_YM, registYm)
select = dao.doSelect()
expensesList = []
for i in range(len(select)):
expensesYmd = StringOperationLib.toString(select[i][ExpensesDao.COL_EXPENSES_YMD])
expensesYmd = DateUtilLib.getDateFormatJapanese(expensesYmd)
if inputType == '1':
roundTrip = ''
#交通費
if select[i][ExpensesDao.COL_ROUND_TRIP_TYPE] != None:
roundTrip = self.roundTripTypeMap[select[i][ExpensesDao.COL_ROUND_TRIP_TYPE]]
expensesList.append([expensesYmd, roundTrip, select[i][ExpensesDao.COL_TRANSPORT], select[i][ExpensesDao.COL_FROM_PLACE], select[i][ExpensesDao.COL_TO_PLACE], select[i][ExpensesDao.COL_EXPENSES_DETAIL], select[i][ExpensesDao.COL_COST]])
elif inputType == '2':
#経費(現金、立替金は固定出力)
expensesList.append([expensesYmd, '現金', '立替金', select[i][ExpensesDao.COL_EXPENSES_DETAIL], select[i][ExpensesDao.COL_COST]])
return expensesList
def makeBaseMap(self, dt, holidayList):
year, month, _ = DateUtilLib.splitYmd(dt)
lastDay = DateUtilLib.getLastDay(StringOperationLib.toInt(year), StringOperationLib.toInt(month))
baseMap = {}
hi = DateUtilLib.toDateTimeDate(dt)
for x in range(lastDay):
weekday = DateUtilLib.getWeekDayName(hi)
day = DateUtilLib.getDay(hi)
if StringOperationLib.toString(hi) in holidayList:
weekday = '祝'
baseMap[StringOperationLib.toString(hi)] = [day, weekday, '', '', '', '', '', '', '', '', '', '', '']
hi = DateUtilLib.getDateIntervalDay(hi, 1)
return baseMap
def getTargetDate(self, dt, interval):
date = DateUtilLib.toDateTimeDate(dt)
return StringOperationLib.toString(
DateUtilLib.getDateIntervalDay(date, -interval))
def getStandardDate(self, dt):
date = DateUtilLib.toDateTimeDate(dt)
#文字列で返す
return StringOperationLib.toString(
DateUtilLib.getDateIntervalYear(date, -3))