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)