def setOtherArea(self, excel, lastRow):
row = lastRow + 3
#実出勤日数、就業時間数
excel.setValueR1C1(10, row, '実出勤日数')
excel.setValueR1C1(12, row, '=COUNTA(E6:E' + StringOperationLib.toString(lastRow) + ') & "日"')
row+=1
excel.setValueR1C1(10, row, '就業時間数')
excel.setValueR1C1(12, row, '=L' + StringOperationLib.toString(lastRow + 1))
#連絡欄
row+=2
excel.setValueR1C1(1, row, '連絡欄')
#押印欄
row+=6
excel.setValueR1C1(9, row, '確認')
excel.setValueR1C1(11, row, '承認')
def expensesExcelFormat(self, excel, lastRow):
#列幅
excel.setCellWidth('A', 15)
excel.setCellWidth('B', 15)
excel.setCellWidth('C', 15)
excel.setCellWidth('D', 40)
excel.setCellWidth('E', 15)
excel.setCellWidth('F', 7)
#結合
excel.mergeCell('A1:F1')
excel.mergeCell('B2:C2')
excel.mergeCell('B3:C3')
excel.mergeCell('B4:C4')
excel.mergeCell('E2:F2')
excel.mergeCell('E3:F4')
#フォントサイズ
excel.changeSizeMulti('A7:H' + StringOperationLib.toString(lastRow), 9)
excel.changeSize('E3', 18)
#背景色
excel.changeCellBackColorMulti('A2:A4', 'DBDBDB')
excel.changeCellBackColorMulti('A6:F6', 'DBDBDB')
excel.changeCellBackColorMulti('E2:F2', 'DBDBDB')
#罫線
excel.setBorderMulti('A6:F' + StringOperationLib.toString(lastRow + 1), 'hair')
excel.setBorderMulti('A6:F6', 'thin', '000000', 'top')
excel.setBorderMulti('F6:F' + StringOperationLib.toString(lastRow + 1), 'thin', '000000', 'right')
excel.setBorderMulti('A' + StringOperationLib.toString(lastRow + 1) + ':F' + StringOperationLib.toString(lastRow + 1), 'thin', '000000', 'top')
excel.setBorderMulti('A' + StringOperationLib.toString(lastRow + 1) + ':F' + StringOperationLib.toString(lastRow + 1), 'thin', '000000', 'bottom')
excel.setBorderMulti('A6:A' + StringOperationLib.toString(lastRow + 1), 'thin', '000000', 'left')
excel.setBorderMulti('A2:C4')
excel.setBorderMulti('E2:F4')
#位置
excel.setAlignment('A1', 'top', 'center')
excel.setAlignment('E2', 'top', 'center')
excel.setAlignment('E3', 'center', 'center')
excel.setAlignmentMulti('A6:F6', 'center', 'center')
excel.setAlignmentMulti('D7:D' + StringOperationLib.toString(lastRow), 'center', 'left', True)
#フォーマット
excel.setNumberFormat('E3', '#,##0')
def makeUpdateStatement(self):
sql = 'UPDATE ' + self.table + ' SET '
keys = list(self.colValMap.keys())
for i in range(len(keys)):
if i > 0:
sql += ', '
sql += keys[i] + self.COMP_EQUAL + StringOperationLib.toString(
self.colValMap[keys[i]])
return sql
def deleteWeeklyReportData(self, ids):
dao = WeeklyReportDao(self.db)
dao.addWhereIn(WeeklyReportDao.COL_REGIST_USER_ID, ids)
count = dao.doCount()
self.writeLog('週報データ削除対象件数：' + StringOperationLib.toString(count) +
'件')
dao.doDelete()
return
def deleteTimeRecordConfigData(self, ids):
dao = TimeRecordConfigDao(self.db)
dao.addWhereIn(TimeRecordConfigDao.COL_EMPLOYEE_ID, ids)
count = dao.doCount()
self.writeLog('勤怠設定データ削除対象件数：' + StringOperationLib.toString(count) +
'件')
dao.doDelete()
return
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 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 deletePresenceData(self, ids):
dao = PresenceDao(self.db)
dao.addWhereIn(PresenceDao.COL_NOTICE_ID, ids)
count = dao.doCount()
self.writeLog('出欠状況削除対象データ件数：' + StringOperationLib.toString(count) +
'件')
dao.doDelete()
return
def deleteNoticeData(self, ids):
dao = NoticeDao(self.db)
dao.addWhereIn(NoticeDao.COL_ID, ids)
count = dao.doCount()
self.writeLog('お知らせ削除対象データ件数：' + StringOperationLib.toString(count) +
'件')
dao.doDelete()
return
def deleteData(self, dt):
self.writeLog('削除基準日:' + dt)
dao = ExpensesDao(self.db)
dao.addWhereStr(ExpensesDao.COL_REGIST_YM, dt, ExpensesDao.COMP_LESS)
count = dao.doCount()
self.writeLog('削除対象データ件数:' + StringOperationLib.toString(count) + '件')
dao.doDelete()
return count
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 deleteExpensesData(self, ids):
dao = ExpensesDao(self.db)
dao.addWhereIn(ExpensesDao.COL_EMPLOYEE_ID, ids)
count = dao.doCount()
self.writeLog('勤怠データ削除対象件数：' + StringOperationLib.toString(count) +
'件')
dao.doDelete()
#領収書ファイル
self.deleteReceiptFile(ids)
return
def getTotalList(self, employeeId, fromDt, toDt):
dao = TimeRecordDao(self.db)
dao.addSelectSumAs(TimeRecordDao.COL_BREAK_TIME, 'total_break_time')
dao.addSelectSumAs(TimeRecordDao.COL_PRESCRIBED_TIME, 'total_prescribed_time')
dao.addSelectSumAs(TimeRecordDao.COL_OVER_WORK_TIME, 'total_over_work_time')
dao.addSelectSumAs(TimeRecordDao.COL_MIDNIGHT_TIME, 'total_midnight_time')
dao.addSelectSumAs(TimeRecordDao.COL_MIDNIGHT_BREAK_TIME, 'total_midnight_break_time')
dao.addSelectSumAs(TimeRecordDao.COL_MIDNIGHT_OVER_WORK_TIME, 'total_midnight_over_work_time')
dao.addSelectSumAs(TimeRecordDao.COL_WORK_TIME, 'total_work_time')
dao.addWhere(TimeRecordDao.COL_EMPLOYEE_ID, StringOperationLib.toString(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]) #共通と本社用のみ取得してくる
return dao.doSelectInfo()
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 makeInsertStatement(self):
sql = 'INSERT INTO ' + self.table + '('
keys = list(self.colValMap.keys())
for i in range(len(keys)):
if i > 0:
sql += ', '
sql += keys[i]
sql += ') VALUES ('
values = list(self.colValMap.values())
for i in range(len(values)):
if i > 0:
sql += ', '
sql += StringOperationLib.toString(values[i])
sql += ');'
return sql
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 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 run(self):
date = self.getForm('-date')
if date == '':
self.writeLog('parameter:-date is not set')
return
#基準日を取得(3ヶ月保持)
stdDate = self.getStandardDate(date)
#過去データを削除する
dao = ConferenceAppointDao(self.db)
dao.addWhereStr(ConferenceAppointDao.COL_TARGET_DATE, stdDate,
ConferenceAppointDao.COMP_LESS)
count = dao.doCount()
self.writeLog('削除対象データ件数：' + StringOperationLib.toString(count) + '件')
dao.doDelete()
return
def run(self):
date = self.getForm('-date')
interval = int(self.getForm('-interval', 60))
if date == '':
self.writeLog('parameter:-date is not set')
return
targetDate = self.getTargetDate(date, interval)
#パス取得
dirPath = Config.getConf('LOGinfo', 'log_file_path')
count = 0
for logPath in FileOperationLib.getFileList(dirPath, '*' + targetDate +
'*.log'):
FileOperationLib.deleteFile(logPath)
count += 1
self.writeLog('ログ削除件数:' + StringOperationLib.toString(count) + '件')
return
def run(self):
date = self.getForm('-date')
if date == '':
self.writeLog('parameter:-date is not set')
return
stdDate = self.getStandardDate(date)
self.writeLog('削除基準日：' + stdDate)
dao = WeeklyReportDao(self.db)
dao.addWhereStr(WeeklyReportDao.COL_STANDARD_DATE, stdDate,
WeeklyReportDao.COMP_LESS)
count = dao.doCount()
self.writeLog('削除対象データ件数：' + StringOperationLib.toString(count) + '件')
dao.doDelete()
return
def run(self):
date = self.getForm('-date')
if date == '':
self.writeLog('parameter:-date is not set')
return
#掲載期限切れデータの情報を取得
ids = self.getDeleteTargetData(date)
if len(ids) == 0:
self.writeLog('削除対象データなし')
return
self.writeLog('データ削除実施日：' + StringOperationLib.toString(date))
#掲載期限切れデータを削除
self.deleteNoticeData(ids)
#付随する出欠確認データを削除
self.deletePresenceData(ids)
return
def getStandardDate(self, dt):
date = DateUtilLib.toDateTimeDate(dt)
#文字列で返す
return StringOperationLib.toString(
DateUtilLib.getDateIntervalYear(date, -3))
def writeErrLog(self, e):
self.file.write(StringOperationLib.toString(e) + '\r\n')
def toStringDate(date):
return StringOperationLib.toString(date)
def setCostSumValue(self, excel, lastRow, col):
totalRow = lastRow + 1
excel.setValueA1(col + StringOperationLib.toString(totalRow), '=SUM(' + col + '7:' + col + StringOperationLib.toString(lastRow) + ')')
excel.setValueA1(col + '3', '=' + col + StringOperationLib.toString(totalRow))
def getDay(date):
return StringOperationLib.toInt(date.strftime("%d"))
def getMonth(date):
return StringOperationLib.toInt(date.strftime("%m"))
def getYear(date):
return StringOperationLib.toInt(date.strftime("%Y"))
def getYmFormatJapanese(ym):
year = StringOperationLib.toString(StringOperationLib.left(ym,
4)) + '年'
month = StringOperationLib.toString(
StringOperationLib.toInt(StringOperationLib.right(ym, 2))) + '月'
return year + month
def toHm(time):
if time == None: return None
h, m = divmod(time, 60)
return StringOperationLib.toString(
h) + ':' + StringOperationLib.toString(m).zfill(2)
