""" card_data CREATE TABLE htp_hr_cdata ( cdata_date date NOT NULL, cdata_time character varying NOT NULL, cdata_time_value double precision NOT NULL, cdata_emp_no character varying NOT NULL, cdata_emp_name character varying, cdata_dept character varying, cdata_sysdate timestamp without time zone NOT NULL, PRIMARY KEY (cdata_date, cdata_time_value, cdata_emp_no) ); working_times CREATE TABLE htp_hr_working_times ( work_id int NOT NULL, work_beg_time timestamp without time zone NOT NULL, work_end_time timestamp without time zone NOT NULL, PRIMARY KEY (work_id, work_beg_time, work_end_time) ); cshift CREATE TABLE htp_hr_cshift ( cshift_emp_no character varying NOT NULL, cshift_dept character varying, cshift_no character varying NOT NULL, cshift_beg_date date NOT NULL, cshift_beg_time character varying NOT NULL, cshift_beg_time_value double precision NOT NULL, cshift_end_date date, cshift_end_time character varying, cshift_end_time_value double precision, cshift_leave_hours double precision, cshift_late character varying, cshift_late_value double precision, cshift_leave character varying, cshift_leave_value double precision, cshift_over character varying, cshift_over_value double precision, cshift_over_amt double precision DEFAULT 0, cshift_over_flag character varying DEFAULT '', cshift_over2 character varying, #加班2 cshift_over2_value double precision, cshift_over2_amt double precision DEFAULT 0, cshift_over2_flag character varying DEFAULT '', cshift_over3 character varying, #加班3 cshift_over3_value double precision, cshift_over3_amt double precision DEFAULT 0, cshift_over3_flag character varying DEFAULT '', cshift_over_beg_time character varying,#早加班開始時間 cshift_over_end_time character varying,#晚加班結束時間 cshift_total character varying, cshift_total_value double precision, cshift_resource_calendar_id int DEFAULT 0, cshift_sysdate timestamp without time zone NOT NULL, cshift_status character varying NOT NULL DEFAULT 'NA', PRIMARY KEY (cshift_emp_no, cshift_no, cshift_beg_date, cshift_beg_time_value) ); ALTER TABLE htp_hr_cshift ADD COLUMN cshift_over2 character varying, #加班2 ADD COLUMN cshift_over2_value double precision, ADD COLUMN cshift_over2_amt double precision DEFAULT 0, ADD COLUMN cshift_over2_flag character varying DEFAULT ''; ALTER TABLE htp_hr_cshift ADD COLUMN cshift_over3 character varying, #加班3 ADD COLUMN cshift_over3_value double precision, ADD COLUMN cshift_over3_amt double precision DEFAULT 0, ADD COLUMN cshift_over3_flag character varying DEFAULT ''; ALTER TABLE htp_hr_cshift ADD COLUMN cshift_over_beg_time character varying DEFAULT '', #早加班開始時間 ADD COLUMN cshift_over_end_time character varying DEFAULT ''; #晚加班結束時間 ALTER TABLE htp_hr_cshift ADD COLUMN cshift_leave_hours double precision DEFAULT 0; odoo 取工作時段 hr_employee.resource_calendar_id resource_calendar.id resource_calendar_attendance.calendar_id resource_calendar_leaves.calendar_id """ # By XZ import xlrd import xlwt import logging import dataset from sqlalchemy.sql import text import datetime import calendar import xmlrpc.client from datetime import tzinfo, timedelta import pytz import math import os import sys import glob from dateutil.relativedelta import relativedelta from shutil import copyfile # ========================================================================== #global odoo_url, odoo_db #odoo_url = 'http://0.0.0.0:8069' #odoo_url = 'http://192.168.80.11:8069' odoo_url = 'http://192.168.80.3:8069' #odoo_db = 'htp_hr' odoo_db = 'htp' # odoo_db = 'htp' odoo_username = 'admin' odoo_password = 'Htqweasd' datetime_fmt = '%Y-%m-%d %H:%M:%S' allow_none = False cust_end_time_days = ['2022-01-28', '2022-02-25'] def common_version(): # provides meta-calls which don’t require authentication common = xmlrpc.client.ServerProxy('{}/xmlrpc/2/common'.format(odoo_url), allow_none=allow_none) # common.version() # print(common.version()) return common def get_uid(): # Logging in common = common_version() uid = common.authenticate(odoo_db, odoo_username, odoo_password, {}) # print('uid:', uid) return uid def endpoint_object(): # is used to call methods of odoo models via the execute_kw RPC function. return xmlrpc.client.ServerProxy('{}/xmlrpc/2/object'.format(odoo_url), allow_none=allow_none) # ========================================================================== prefix = 'htp_hr' # def float_to_datetime(fl): # return datetime.datetime.fromtimestamp(fl) def convert_excel_time(t, hour24=False): if t > 1: t = t % 1 seconds = round(t*86400) minutes, seconds = divmod(seconds, 60) hours, minutes = divmod(minutes, 60) if hour24: if hours > 12: hours -= 12 return "%02d:%02d:%02d PM" % (hours, minutes, seconds) else: return "%02d:%02d:%02d AM" % (hours, minutes, seconds) return "%02d:%02d:%02d" % (hours, minutes, seconds) def datetime2UTC(dt, tz='Asia/Taipei', fmt='%Y-%m-%d %H:%M:%S'): ret = dt if type(ret) == str: ret = datetime.datetime.strptime(ret, fmt) old_tz = pytz.timezone(tz) new_tz = pytz.timezone('UTC') ret = old_tz.localize(ret).astimezone(new_tz) return ret def date_diff2hours(dt, dt2): _dt = dt _dt2 = dt2 if type(_dt) == str: _dt = datetime.datetime.strptime(_dt, '%Y-%m-%d %H:%M:%S') if type(_dt2) == str: _dt2 = datetime.datetime.strptime(_dt2, '%Y-%m-%d %H:%M:%S') return (_dt2-_dt).total_seconds() / 3600 def ignore_sec(): return 0.00011 # 十秒 # return 0.00069 #六十秒 def write_row(emplist, table, data): # print(data) vcdata_date = data['vcdata_date'] vcdata_time_value = data['vcdata_time_value'] vcdata_time = data['vcdata_time'] vemp_no = data['vemp_no'] vcdata_emp_name = data['vcdata_emp_name'] vcdata_dept = data['vcdata_dept'] if vemp_no in emplist: if emplist[vemp_no]+ignore_sec() >= vcdata_time_value: # 跟上筆差十秒內不存入 return emplist[vemp_no] = vcdata_time_value if table.find_one(cdata_date=vcdata_date, cdata_time_value=vcdata_time_value, cdata_emp_no=vemp_no): table.delete(cdata_date=vcdata_date, cdata_time_value=vcdata_time_value, cdata_emp_no=vemp_no) table.insert(dict(cdata_date=vcdata_date, cdata_time=vcdata_time, cdata_time_value=vcdata_time_value, cdata_emp_no=vemp_no, cdata_emp_name=vcdata_emp_name, cdata_dept=vcdata_dept, cdata_sysdate=datetime.datetime.now())) def run_hr_cdata(db, filename): # 日期時間小到大 #logger = logging.getLogger('cdata') _filename = filename wb = xlrd.open_workbook(_filename) sheet = wb.sheet_by_index(0) _db = db table = _db[prefix+'_cdata'] # sheet.cell_value(0, 0) # init emplist = {} data = {} _db.begin() # cdata = [] # for i in range(1, sheet.nrows): # row = sheet.row_values(i) # cdata.insert(0, row) cdata = [sheet.row_values(i) for i in range(1, sheet.nrows)] cdata.sort() try: tagDate = '' for row in cdata: # for i in range(1, sheet.nrows): # row = sheet.row_values(i) logging.info(row) #logging.debug(row) vcdata_date = datetime.datetime( *xlrd.xldate_as_tuple(row[1], wb.datemode)) # 日期 if tagDate != vcdata_date.strftime('%Y-%m-%d'): tagDate = vcdata_date.strftime('%Y-%m-%d') emplist.clear() #print(tagDate) vcdata_time_value = row[2] # 時間 vcdata_time = convert_excel_time(vcdata_time_value) vemp_no = row[3] data['vcdata_date'] = vcdata_date data['vcdata_time_value'] = vcdata_time_value data['vcdata_time'] = vcdata_time data['vemp_no'] = vemp_no data['vcdata_emp_name'] = row[4] data['vcdata_dept'] = row[5] write_row(emplist, table, data) # print(i) _db.commit() except: _db.rollback() def getShitft(): ret = {} lst1 = [] #時間1不要用, 轉換問題, 0.9999取代 sf1 = {'name': '0800-1700', 'begin': 0, 'btime': 0.33333, 'etime': 0.70833, 'end': 0.9999, 'rest_hours': 1, 'rest_btime': 0.50000, 'rest_etime': 0.54167} # rest 休息時數 lst1.append(sf1) ret[1] = lst1 # Standard 40 hours/week # sf1 = {'name': '0800-1200', 'begin': 0, 'btime': 0.33333, 'etime': 0.50000, # 'end': 0.52167,'rest_hours': 0} # lst1.append(sf1) # sf2 = {'name': '1300-1700', 'begin': 0.52168, 'btime': 0.54167, 'etime': 0.70833, # 'end': 0.74000, 'rest_hours': 0} # lst1.append(sf2) # sf3 = {'name': '1800-2100', 'begin': 0.74001, # 'btime': 0.75000, 'etime': 0.87500, 'end': 1,'rest_hours': 0} # lst1.append(sf3) return ret def getShitftTable(sfList): ret = {} for item in sfList: ret[item['name']] = item return ret def time2dict(timelist, sfTable): ret = {} for i in range(len(timelist)): time = timelist[i] for sf in sfTable: if sf['name'] not in ret: ret[sf['name']] = [] if sf['begin'] <= time and sf['end'] >= time: if len(ret[sf['name']]) <= 1: ret[sf['name']].append(time) else: ret[sf['name']][1] = time break return ret def loadXlsEmp(): gXlsEmp = {} _path = '/home/xz/xzwork/htp/xls2table/' _filename = _path+'emplist.xlsx' wb = xlrd.open_workbook(_filename) sheet = wb.sheet_by_index(0) for i in range(1, sheet.nrows): row = sheet.row_values(i) empno = row[1] # 工號 data = {} data['dept'] = row[3] # 部門 data['resource_calendar_id'] = 1 # 工作表 gXlsEmp[empno] = data return gXlsEmp def loadOdooEmp(db): gXlsEmp = {} # t_hr_employee = db['hr_employee'] # data_rows = t_hr_employee.all() #全取， 暫無條件actve flds = 'wage, food_subsidy, allowance1, allowance2, allowance3, allowance4 ' _sql = 'select emp.*, dept.name as dept_name from hr_employee as emp ' _sql += 'left join hr_department as dept on (dept.id = emp.department_id) ' _sql += 'where emp.emp_no is not null and coalesce(emp.no_attendance, false) = false and emp.active = true ' _sql += 'order by emp.emp_no' data_rows = db.query(_sql) for row in data_rows: empno = row['emp_no'] # 工號 #_sql = "SELECT wage FROM hr_contract where active = True and employee_id = (select id from hr_employee where emp_no = '%s') LIMIT 1" % (empno) _sql = "SELECT %s FROM hr_contract where active = True and employee_id = (select id from hr_employee where emp_no = '%s') LIMIT 1" % (flds,empno) _wage = 0 query_data = db.query(_sql) for data in query_data: _wage = float(data['wage'])+float(data['food_subsidy'])+float(data['allowance1'])+float(data['allowance2'])+float(data['allowance3'])+float(data['allowance4']) data = {} data['dept'] = row['dept_name'] # 部門 data['resource_calendar_id'] = row['resource_calendar_id'] # 工作表 data['wage'] = _wage gXlsEmp[empno] = data return gXlsEmp def getEmployee(emplist, run_emp_no=None): ret = emplist # ret['202009001'] = '資訊部' if run_emp_no is not None: ret = {} ret[run_emp_no] = emplist[run_emp_no] return ret def getWorkDays(db, res_cal_id, sdatetime): # 檢核是否上班日 ret = [False, 0] dt = datetime2UTC(sdatetime) wday = dt.weekday() _sql = 'select * from htp_hr_working_times ' _sql += 'where work_id = %d and work_beg_time <= \'%s\' and work_end_time >= \'%s\' ' % ( res_cal_id, sdatetime, sdatetime) data_rows = db.query(_sql) for row in data_rows: ret[0] = True ret[1] = wday break return ret def getHoliday(db, res_cal_id, sdatetime): # 特殊放假 ret = [False, ''] # ret.append('2021-02-10') _sql = 'select rcl.* from resource_calendar_leaves as rcl ' _sql += 'where rcl.resource_id is null and rcl.holiday_id is null and rcl.calendar_id = %d and rcl.date_from <= \'%s\' and rcl.date_to >= \'%s\' ' % ( res_cal_id, sdatetime, sdatetime) data_rows = db.query(_sql) for row in data_rows: ret[0] = True ret[1] = row['name'] if row['name'] == '天災': ret = False break return ret def check_work_date(run_date): # 檢核是否上班日 wday = run_date.weekday() sday = run_date.strftime("%Y-%m-%d") if wday <= 4: # 星期1到星期5上班 ret = True # if sday in getWorkDays(): # ret = True # if sday in getHoliday(): # ret = False return ret def initEmployee(db, run_date, run_emp_no=None): ret = {} _db = db tcshift = _db[prefix+'_cshift'] t_hr_employee = _db['hr_employee'] sday = run_date.strftime("%Y-%m-%d") uid = get_uid() models = endpoint_object() _db.begin() try: emplist = getEmployee(_db.vars['emplist'], run_emp_no=run_emp_no) sfList = getShitft() if len(sfList) <= 0: return ret for emp_no in emplist: _sql = "SELECT date_end FROM hr_contract where active = True and employee_id = (select id from hr_employee where emp_no = '%s') LIMIT 1" % (emp_no) query_data = db.query(_sql) exit_emp = False for data in query_data: if data['date_end']: #離職人員處理 if data['date_end'].strftime("%Y-%m-%d") <= sday: exit_emp = True if exit_emp: continue sfTable = getShitftTable(sfList[emplist[emp_no]['resource_calendar_id']]) # 有上班init Emp if getWorkDays(db, emplist[emp_no]['resource_calendar_id'], sday+' 11:00:00')[0]: for cshift_no in sfTable: cshift_beg_time = convert_excel_time(0) cshift_end_time = convert_excel_time(0) cshift_beg_time_value = 0 cshift_end_time_value = 0 cshift_late_value = 0 cshift_late = convert_excel_time(cshift_late_value) cshift_over_value = 0 cshift_over = convert_excel_time(cshift_over_value) cshift_total_value = 0 cshift_total = convert_excel_time(cshift_total_value) # cshift_leave_value = sfTable[cshift_no]['etime'] - \ # sfTable[cshift_no]['btime'] cshift_leave_value = 0 cshift_leave = convert_excel_time(cshift_leave_value) row = dict(cshift_emp_no=emp_no, cshift_dept=emplist[emp_no]['dept'], cshift_no=cshift_no, cshift_beg_date=run_date, cshift_beg_time=cshift_beg_time, cshift_beg_time_value=cshift_beg_time_value, cshift_end_date=run_date, cshift_end_time=cshift_end_time, cshift_end_time_value=cshift_end_time_value, cshift_late=cshift_late, cshift_late_value=cshift_late_value, cshift_leave=cshift_leave, cshift_leave_value=cshift_leave_value, cshift_over=cshift_over, cshift_over_value=cshift_over_value, cshift_over_amt=0, cshift_over_flag='', cshift_total=cshift_total, cshift_total_value=cshift_total_value, cshift_sysdate=datetime.datetime.now()) #判斷請假單 _btime_str = sday+' '+convert_excel_time(sfTable[cshift_no]['btime']) _etime_str = sday+' '+convert_excel_time(sfTable[cshift_no]['etime']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ '', emp_no, _btime_str, _etime_str]) if data[0]: # 找到假單 hours = date_diff2hours(_btime_str, _etime_str) ret[emp_no] = data[2] if sfTable[cshift_no]['rest_hours'] > 0: hours -= sfTable[cshift_no]['rest_hours'] if data[2] >= hours: continue tcshift.insert(row) _db.commit() return ret except Exception as e: logging.error(str(e)) #print(str(e)) _db.rollback() return {} #加總遲到 def check_odoo_late(db, emp, run_date, btime, etime, late_value): # 找遲到請假單 emp_no = emp['emp_no'] ret = late_value if ret <= 0: return ret uid = get_uid() models = endpoint_object() _date_str = datetime.datetime.strftime(run_date, '%Y-%m-%d') _btime_str = _date_str+' '+convert_excel_time(btime) _etime_str = _date_str+' '+convert_excel_time(etime) if uid: data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ '', emp_no, _btime_str, _etime_str]) if data[0] and ret > 0: # 找到假單 #sum_late_minutes = 0 #sql = 'SELECT sum(cshift_late_value) as late_value FROM '+ prefix + \ # '_cshift where where cshift_emp_no = %s and date_part(\'year\', cshift_beg_date) = %d and date_part(\'month\', cshift_beg_date) = %d ' % (emp_no, run_date.year, run_date.month) #rows = db.query(sql) #for row in rows: # if row > 0: # seconds = round(row*86400) # sum_late_minutes, seconds = divmod(seconds, 60) if data[2] > 0: # 小時 #btime_dt = datetime.datetime.strptime(_btime_str, datetime_fmt) #leave_dt = data[3] #if leave_dt != 0: # leave_dt = datetime.datetime.strptime(data[3], datetime_fmt) # if leave_dt > btime_dt: #大於八點, 一律給30分(只能請30分) # return (0.5 / 24) asSec = data[2] / 24 ret = ret - asSec # 依核准時數 if ret < 0: # 負數歸零 ret = 0 return ret def check_odoo_leaves(db, emp, run_date, btime, etime, late_value): # 找出早退請假單 emp_no = emp['emp_no'] ret = late_value uid = get_uid() models = endpoint_object() _date_str = datetime.datetime.strftime(run_date, '%Y-%m-%d') _btime_str = _date_str+' '+convert_excel_time(btime) _btime_str = _btime_str[0:14]+'00:00' #取小時取整數 _etime_str = _date_str+' '+convert_excel_time(etime) if uid: data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ '', emp_no, _btime_str, _etime_str]) if data[0] and ret > 0: # 找到假單 #if data[1] > 0.5: # 多天 # ret = 0 if data[2] > 0: # 小時 asSec = data[2] / 24 ret = ret - asSec # 依核准時數 if ret < 0: # 負數歸零 ret = 0 return ret def check_odoo_overtime_all2(db, emp, run_date, calendar_id, btime, etime, sfTable): # 全改寫, 依刷卡及加班單 _db = db emp_no = emp['emp_no'] ret = [0, 0, '', ''] #0.一般加班, 1.加班2, 2.最早加班時段 3.最晚加班時段 uid = get_uid() models = endpoint_object() wday = run_date.weekday() sday = run_date.strftime("%Y-%m-%d") _btime = btime #刷起 if sfTable['btime'] > _btime: #早到五十四分內都不算, 依8點整 if (sfTable['btime'] - _btime) <= (0.9 / 24): _btime = sfTable['btime'] _etime = etime #實刷迄 over_value = 0 #實刷 t = _etime-_btime hours = (round(t*86400) / 60) / 60 t = sfTable['etime']-sfTable['btime'] #hours2 = (round(t*86400) / 60) / 60 _date_str = datetime.datetime.strftime(run_date, '%Y-%m-%d') hours3 = 0 #加班 hours32 = 0 #加班2 if hours > 0: if wday == 5 or wday == 6 or getHoliday(_db, calendar_id, sday)[0]:#放假加班(國定假日) _btime_str = _date_str+' '+convert_excel_time(sfTable['begin']) _etime_str = _date_str+' '+convert_excel_time(sfTable['end']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) t = _etime-btime #實刷 hours = (round(t*86400) / 60) / 60 if data[0] and data[2] > 0: hours3 = data[2] if hours < data[2]: hours3 = hours _btime_str = _date_str+' '+convert_excel_time(sfTable['begin']) _etime_str = _date_str+' '+convert_excel_time(sfTable['end']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over2', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: hours32 = data[2] if hours < data[2]: hours32 = hours else: #平日 #早上加班 t = sfTable['btime']-_btime calc_hours = (round(t*86400) / 60) / 60 if calc_hours >= (0.5 / 24): _btime_str = _date_str+' '+convert_excel_time(sfTable['begin']) _etime_str = _date_str+' '+convert_excel_time(sfTable['btime']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: hours3 = data[2] if calc_hours < data[2]: hours3 = calc_hours #晚上加班 t = _etime-sfTable['etime'] calc_hours = (round(t*86400) / 60) / 60 if calc_hours >= (0.5 / 24): _btime_str = _date_str+' '+convert_excel_time(sfTable['etime']) _etime_str = _date_str+' '+convert_excel_time(sfTable['end']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: if calc_hours < data[2]: hours3 += calc_hours else: hours3 += data[2] #中午加班 if sfTable['rest_hours'] > 0 and 'rest_btime' in sfTable: _btime_str = _date_str+' '+convert_excel_time(sfTable['rest_btime']) #中午休息一小時 _etime_str = _date_str+' '+convert_excel_time(sfTable['rest_etime']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: hours3 += data[2] #早上加班2 t = sfTable['btime']-_btime calc_hours = (round(t*86400) / 60) / 60 if calc_hours >= (0.5 / 24): _btime_str = _date_str+' '+convert_excel_time(sfTable['begin']) _etime_str = _date_str+' '+convert_excel_time(sfTable['btime']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over2', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: hours32 = data[2] if calc_hours < data[2]: hours32 = calc_hours #晚上加班2 t = _etime-sfTable['etime'] calc_hours = (round(t*86400) / 60) / 60 if calc_hours >= (0.5 / 24): _btime_str = _date_str+' '+convert_excel_time(sfTable['etime']) _etime_str = _date_str+' '+convert_excel_time(sfTable['end']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over2', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: if calc_hours < data[2]: hours32 += calc_hours else: hours32 += data[2] #中午加班2 if sfTable['rest_hours'] > 0 and 'rest_btime' in sfTable: _btime_str = _date_str+' '+convert_excel_time(sfTable['rest_btime']) #中午休息一小時 _etime_str = _date_str+' '+convert_excel_time(sfTable['rest_etime']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over2', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: hours32 += data[2] if hours3 > 0: over_value = hours3 / 24 ret[0] = over_value if hours32 > 0: over_value = hours32 / 24 ret[1] = over_value _btime_str = _date_str+' '+convert_excel_time(sfTable['begin']) _etime_str = _date_str+' '+convert_excel_time(sfTable['end']) if (ret[0] > 0) or (ret[1] > 0): #取最大最小時間 data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves_over', [ emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: ret[2] = data[3] ret[3] = data[4] if convert_excel_time(sfTable['btime']) < ret[2]: ret[2] = '' return ret def check_odoo_overtime_all(db, emp, run_date, calendar_id, btime, etime, sfTable): # 全改寫, 依刷卡及加班單 _db = db emp_no = emp['emp_no'] ret = [0, 0, '', ''] uid = get_uid() models = endpoint_object() wday = run_date.weekday() sday = run_date.strftime("%Y-%m-%d") _btime = btime #實刷起 if sfTable['btime'] > _btime: #早到五十四分內都不算, 依8點整 if (sfTable['btime'] - _btime) <= (0.9 / 24): _btime = sfTable['btime'] _etime = etime #實刷迄 over_value = 0 #實刷 t = _etime-_btime hours = (round(t*86400) / 60) / 60 t = sfTable['etime']-sfTable['btime'] hours2 = (round(t*86400) / 60) / 60 _date_str = datetime.datetime.strftime(run_date, '%Y-%m-%d') if hours > 0: hours3 = hours if wday == 5 or wday == 6 or getHoliday(_db, calendar_id, sday)[0]:#放假加班(國定假日) pass else: #平日 #必要扣休息時間 if hours3 >= hours2: hours3 -= sfTable['rest_hours'] hours3 = hours3-(hours2-sfTable['rest_hours']) if sfTable['rest_hours'] > 0 and 'rest_btime' in sfTable: _btime_str = _date_str+' '+convert_excel_time(sfTable['rest_btime']) #中午休息一小時 _etime_str = _date_str+' '+convert_excel_time(sfTable['rest_etime']) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: #休息時間,有加班單不扣 hours3 += data[2] if hours3 > 0: over_value = hours3 / 24 #print('加班實刷: '+convert_excel_time(over_value)) _btime_str2 = _date_str+' '+convert_excel_time(_btime) _etime_str2 = _date_str+' '+convert_excel_time(_etime) if uid: #找出忘刷卡假 _st_etime = sfTable['end'] if _st_etime == 1: #不可以24小時, 調整少十分鐘 _st_etime = _st_etime - (0.1 / 24) _btime_str = _date_str+' '+convert_excel_time(sfTable['begin']) _etime_str = _date_str+' '+convert_excel_time(_st_etime) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'nocheck', emp_no, _btime_str, _etime_str]) if data[0]: _btime_str2 = data[2] _etime_str2 = data[3] over_value = data[1] / 24 #實刷 # data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str2, _etime_str2]) if data[0] and data[2] > 0: asSec = data[2] / 24 ret[0] = asSec # 依核准時數 if over_value < (0.5 / 24): # 實刷不滿30分,不計算 ret[0] = 0 elif asSec > over_value: # 小於核准時數， 依實刷時數計算 ret[0] = over_value #加班2(特殊加班) data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over2', emp_no, _btime_str2, _etime_str2]) if data[0] and data[2] > 0: asSec = data[2] / 24 ret[1] = asSec # 依核准時數 if over_value < (0.5 / 24): # 實刷不滿30分,不計算 ret[1] = 0 elif asSec > over_value: # 小於核准時數， 依實刷時數計算 ret[1] = over_value if (ret[0] > 0) or (ret[1] > 0): #取最大最小時間 data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves_over', [ emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: ret[2] = data[3] ret[3] = data[4] if convert_excel_time(sfTable['btime']) < ret[2]: ret[2] = '' return ret def check_odoo_overtime(db, emp, run_date, btime, etime, over_value): # 找出加班單 _db = db emp_no = emp['emp_no'] ret = 0 uid = get_uid() models = endpoint_object() wday = run_date.weekday() _date_str = datetime.datetime.strftime(run_date, '%Y-%m-%d') _btime_str = _date_str+' '+convert_excel_time(btime) _etime_str = _date_str+' '+convert_excel_time(etime) sday = run_date.strftime("%Y-%m-%d")+' '+convert_excel_time(btime) if uid and over_value > 0: data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: asSec = data[2] / 24 ret = asSec # 依核准時數 if over_value < (0.5 / 24): # 實刷不滿30分,不計算 ret = 0 elif asSec > over_value: # 小於核准時數， 依實刷時數計算 ret = over_value return ret def check_odoo_overtime2(db, emp, run_date, btime, etime, over_value): # 平日找出加班單, 下班前的單據 _db = db emp_no = emp['emp_no'] ret = over_value uid = get_uid() models = endpoint_object() wday = run_date.weekday() _date_str = datetime.datetime.strftime(run_date, '%Y-%m-%d') _btime_str = _date_str+' '+convert_excel_time(btime) _etime_str = _date_str+' '+convert_excel_time(etime) sday = run_date.strftime("%Y-%m-%d")+' '+convert_excel_time(btime) if uid: data = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'check_leaves', [ 'over', emp_no, _btime_str, _etime_str]) if data[0] and data[2] > 0: asSec = data[2] / 24 ret = ret+asSec # 依核准時數+下班加班時數 return ret def calc_over_amt(db, emp, run_date, over_value): # 加班計算 _db = db ret = [0, ''] if over_value <= 0: return ret wday = run_date.weekday() sday = run_date.strftime("%Y-%m-%d") hours = (round(over_value*86400) / 60) / 60 if hours > 0: # if wday <= 4: # 星期1到星期5上班(平日) if getWorkDays(_db, emp['resource_calendar_id'], sday+' 11:00:00')[0]: # 假日加班(是為平常日) wday = 0 if getHoliday(_db, emp['resource_calendar_id'], sday)[0]: # 國定假日(是為例假日) wday = 5 ret[0] = hours*1.34 ret[1] = '平日' if hours > 2: ret[0] = 2*1.34 ret[0] += (hours-2) * 1.67 if wday == 5: # 星期6上班(休息日) h = hours ret[0] = h*1.34 ret[1] = '休息日' if h > 2: ret[0] = 2*1.34 h -= 2 if h <= 6: ret[0] += h*1.67 else: ret[0] += 6*1.67 h -= 6 if h > 0: ret[0] += h*2.67 if wday == 6: # 星期日上班(例假日)U h = hours ret[0] = h ret[1] = '例假日' if h >= 1 and h <= 8: # 小於八，給8小時 ret[0] = 8 elif h > 8: ret[0] = 8 h -= 8 if getHoliday(db, emp['resource_calendar_id'], sday)[0]: # 國定假日 if h > 2: ret[0] += 2*1.34 ret[0] += (h-2)*1.67 else: ret[0] += h*1.34 else: ret[0] += h*2 emplist =_db.vars['emplist'] if ret[0] > 0 and emp['emp_no'] in emplist: amt = emplist[emp['emp_no']]['wage'] / 30 / 8 ret[0] = math.ceil(amt*ret[0]) return ret def calc_over2_amt(db, emp, run_date, over_value): # 加班2計算(特殊加班) _db = db ret = [0, ''] if over_value <= 0: return ret wday = run_date.weekday() sday = run_date.strftime("%Y-%m-%d") hours = (round(over_value*86400) / 60) / 60 if hours > 0: if getWorkDays(_db, emp['resource_calendar_id'], sday+' 11:00:00')[0]: # 假日加班(是為平常日) wday = 0 if getHoliday(_db, emp['resource_calendar_id'], sday)[0]: # 國定假日(是為例假日) wday = 5 ret[0] = hours*1.67 ret[1] = '平日' if wday == 5: # 星期6上班(休息日) ret[1] = '休息日' if wday == 6: # 星期日上班(例假日) ret[0] = 0 ret[1] = '例假日' emplist =_db.vars['emplist'] if ret[0] > 0 and emp['emp_no'] in emplist: amt = emplist[emp['emp_no']]['wage'] / 30 / 8 ret[0] = math.ceil(amt*ret[0]) return ret #提前放假時間處理 def custom_end_time_value(sday, emp_no, cshift_end_time_value): ret = cshift_end_time_value if sday in cust_end_time_days: ret = 0.70833 #改下午五點 return ret def run_hr_cshift(db, run_date, run_emp_no=None): #logger = logging.getLogger('cshift') _db = db #tcdata = _db[prefix+'_cdata'] tcshift = _db[prefix+'_cshift'] t_hr_employee = _db['hr_employee'] _orderby = ['cdata_emp_no', 'cdata_date', 'cdata_time_value'] emplist = {} #data_rows = tcdata.find(cdata_date=run_date, order_by=_orderby) #if run_emp_no is not None: # data_rows = tcdata.find(cdata_date=run_date, # cdata_emp_no=run_emp_no, order_by=_orderby) #改成加入htp_checkin忘刷單 run_date_str = run_date.strftime("%Y-%m-%d") _sql = 'SELECT cdata_emp_no, cdata_date, cdata_time_value, cdata_emp_name, cdata_dept ' _sql += 'FROM htp_hr_cdata inner join hr_employee b on (cdata_emp_no = b.emp_no and coalesce(b.no_attendance, false) = false) where cdata_date = \'%s\' ' % (run_date_str) if run_emp_no is not None: _sql += ' and cdata_emp_no = \'%s\' ' % (run_emp_no) _sql += 'UNION ' _sql += 'SELECT b.emp_no, a.ck_date, a.ck_time, b.name, c.name ' _sql += 'FROM htp_checkin a inner join hr_employee b on (a.employee_id = b.id and coalesce(b.no_attendance, false) = false) ' _sql += 'left join hr_department c on (b.department_id = c.id) ' _sql += 'where a.ck_time <> 0 and a.state = \'done\' and a.ck_date = \'%s\' ' % (run_date_str) if run_emp_no is not None: _sql += ' and b.emp_no = \'%s\' ' % (run_emp_no) _sql += 'order by cdata_emp_no, cdata_date, cdata_time_value ' data_rows = _db.query(_sql) if run_emp_no is not None: tcshift.delete(cshift_beg_date=run_date, cshift_emp_no=run_emp_no) else: tcshift.delete(cshift_beg_date=run_date) leave_hours = initEmployee(_db, run_date, run_emp_no=run_emp_no) # init Employee for row in data_rows: if row['cdata_emp_no'] in emplist: if emplist[row['cdata_emp_no']]['timelist'][-1] <= row['cdata_time_value']: emplist[row['cdata_emp_no']]['timelist'].append( row['cdata_time_value']) else: emplist[row['cdata_emp_no']] = {} emplist[row['cdata_emp_no'] ]['emp_no'] = row['cdata_emp_no'] # emp_no emplist[row['cdata_emp_no']]['emp_name'] = row['cdata_emp_name'] emplist[row['cdata_emp_no']]['dept'] = row['cdata_dept'] emplist[row['cdata_emp_no']]['timelist'] = [ row['cdata_time_value']] #emp_row = t_hr_employee.find_one(emp_no=row['cdata_emp_no']) emp_row = t_hr_employee.find_one(emp_no=row['cdata_emp_no'], no_attendance=False, active=True) if emp_row: emplist[row['cdata_emp_no'] ]['resource_calendar_id'] = emp_row['resource_calendar_id'] else: #print(row['cdata_emp_no']) # 找不到員工基本資料, 或已封存及不加入考勤 logging.warning('找不到此員工 '+row['cdata_emp_no']) del emplist[row['cdata_emp_no']] if len(emplist) <= 0: return _db.begin() sfList = getShitft() if len(sfList) <= 0: return wday = run_date.weekday() sday = run_date.strftime("%Y-%m-%d") try: for emp_no in emplist: sfTable = getShitftTable(sfList[emplist[emp_no]['resource_calendar_id']]) timelist = emplist[emp_no]['timelist'] if len(timelist) <= 0: continue ppTimedict = time2dict(timelist, sfList[emplist[emp_no]['resource_calendar_id']]) for cshift_no in ppTimedict: if len(ppTimedict[cshift_no]) == 1: if ppTimedict[cshift_no][0] > sfTable[cshift_no]['etime']: # 早上忘刷 cshift_beg_time_value = sfTable[cshift_no]['etime'] cshift_end_time_value = ppTimedict[cshift_no][0] else: cshift_beg_time_value = ppTimedict[cshift_no][0] cshift_end_time_value = sfTable[cshift_no]['btime'] else: #有刷二筆 cshift_beg_time_value = ppTimedict[cshift_no][0] cshift_end_time_value = ppTimedict[cshift_no][1] cshift_end_time_value = custom_end_time_value(sday, emp_no, cshift_end_time_value) #提前放假時間處理 cshift_beg_time = convert_excel_time(cshift_beg_time_value) cshift_end_time = convert_excel_time(cshift_end_time_value) cshift_late_value = 0 if cshift_beg_time_value > sfTable[cshift_no]['btime']: cshift_late_value = cshift_beg_time_value - \ sfTable[cshift_no]['btime'] if sfTable[cshift_no]['rest_hours'] > 0 and cshift_late_value > (4 / 24): # 扣掉休息時間, 大於四小時 cshift_late_value = cshift_late_value - \ (sfTable[cshift_no]['rest_hours'] / 24) # check odoo 請假單 遲到 cshift_late_value = check_odoo_late(_db, emplist[emp_no], run_date, sfTable[cshift_no]['btime'], cshift_beg_time_value, cshift_late_value) cshift_late = convert_excel_time(cshift_late_value) # cshift_leave_value = 0 if sfTable[cshift_no]['btime'] < cshift_end_time_value and cshift_end_time_value < sfTable[cshift_no]['etime']: cshift_leave_value = sfTable[cshift_no]['etime'] - cshift_end_time_value if sfTable[cshift_no]['rest_hours'] > 0 and cshift_leave_value > (4 / 24): # 扣掉休息時間, 大於四小時 cshift_leave_value = cshift_leave_value - \ (sfTable[cshift_no]['rest_hours'] / 24) # check odoo 請假單 早退 cshift_leave_value = check_odoo_leaves(_db, emplist[emp_no], run_date, cshift_end_time_value, sfTable[cshift_no]['etime'], cshift_leave_value) cshift_leave = convert_excel_time(cshift_leave_value) # cshift_over_value = [0,0] #if cshift_end_time_value > sfTable[cshift_no]['etime']: # cshift_over_value = cshift_end_time_value - \ # sfTable[cshift_no]['etime'] # check odoo 請假單 加班 cshift_over_value = check_odoo_overtime_all2(_db, emplist[emp_no], run_date, emplist[emp_no]['resource_calendar_id'], cshift_beg_time_value, cshift_end_time_value, sfTable[cshift_no]) #特殊加班另外加欄位及check_odoo_overtime_all_167方式 cshift_over_beg_time = cshift_over_value[2] cshift_over_end_time = cshift_over_value[3] # 放假加班 if wday == 5 or wday == 6 or getHoliday(_db, emplist[emp_no]['resource_calendar_id'], sday)[0]: # 遲到早退歸零 cshift_late_value = 0 cshift_late = convert_excel_time(cshift_late_value) cshift_leave_value = 0 cshift_leave = convert_excel_time(cshift_leave_value) # cshift_over_value = check_odoo_overtime2(db, # emplist[emp_no], run_date, sfTable[cshift_no]['begin'], cshift_end_time_value, 0) # 零點開始 #else: # cshift_over_value = check_odoo_overtime(db, # emplist[emp_no], run_date, sfTable[cshift_no]['etime'], cshift_end_time_value, cshift_over_value) # 下班開始 # # 因中午休息時間一小時，再取一次表單(不做出勤比對), 早上到下班時間 # cshift_over_value = check_odoo_overtime2(db, # emplist[emp_no], run_date, sfTable[cshift_no]['btime'], sfTable[cshift_no]['etime'], cshift_over_value) cshift_over = convert_excel_time(cshift_over_value[0]) cshift_over_amt = calc_over_amt(_db, emplist[emp_no], run_date, cshift_over_value[0]) # 加班算金額 #加班2(特殊加班) cshift_over2 = convert_excel_time(cshift_over_value[1]) cshift_over2_amt = calc_over2_amt(_db, emplist[emp_no], run_date, cshift_over_value[1]) # 加班算金額 cshift_total_value = cshift_end_time_value - cshift_beg_time_value if sfTable[cshift_no]['rest_hours'] > 0 and cshift_total_value > 0: # 扣掉休息時間 cshift_total_value = cshift_total_value - \ (sfTable[cshift_no]['rest_hours'] / 24) cshift_total = convert_excel_time(cshift_total_value) #請假總時數 cshift_leave_hours = 0 if emp_no in leave_hours: cshift_leave_hours = leave_hours[emp_no] row = dict(cshift_emp_no=emp_no, cshift_dept=emplist[emp_no]['dept'], cshift_no=cshift_no, cshift_beg_date=run_date, cshift_beg_time=cshift_beg_time, cshift_beg_time_value=cshift_beg_time_value, cshift_end_date=run_date, cshift_end_time=cshift_end_time, cshift_end_time_value=cshift_end_time_value, cshift_leave_hours=cshift_leave_hours, cshift_late=cshift_late, cshift_late_value=cshift_late_value, cshift_leave=cshift_leave, cshift_leave_value=cshift_leave_value, cshift_over=cshift_over, cshift_over_value=cshift_over_value[0], cshift_over_amt=cshift_over_amt[0], cshift_over_flag=cshift_over_amt[1], cshift_over2=cshift_over2, cshift_over2_value=cshift_over_value[1], cshift_over2_amt=cshift_over2_amt[0], cshift_over2_flag=cshift_over2_amt[1], cshift_over_beg_time=cshift_over_beg_time, cshift_over_end_time=cshift_over_end_time, cshift_resource_calendar_id=emplist[emp_no]['resource_calendar_id'], cshift_total=cshift_total, cshift_total_value=cshift_total_value, cshift_sysdate=datetime.datetime.now()) if tcshift.find_one(cshift_emp_no=emp_no, cshift_no=cshift_no, cshift_beg_date=run_date): tcshift.delete( cshift_emp_no=emp_no, cshift_no=cshift_no, cshift_beg_date=run_date) tcshift.insert(row) _db.commit() except Exception as e: logging.error(str(e)) _db.rollback() def run_hr_cmonth(db, year, month, days=None, run_emp_no=None): #logger = logging.getLogger('cmonth') _db = db reload_working_times(_db, year, month) # 工作天數 _days = days if _days is None: _days = calendar.monthrange(year, month)[1] now_date = datetime.datetime.now().strftime("%H:%M:%S") str1 = 'hr_cmonth %d-%d-%d start: %s' % (year, month, _days, now_date) logging.info(str1) for day in range(_days): _day = day+1 run_date = datetime.date(year, month, _day) run_hr_cshift(_db, run_date, run_emp_no=run_emp_no) now_date = datetime.datetime.now().strftime("%H:%M:%S") str1 = 'hr_cmonth %d-%d-%d end: %s' % (year, month, _days, now_date) logging.info(str1) #run_export_cshift_file(_db, year, month, run_emp_no=run_emp_no) def run_hr_attendance(db, year, month, run_emp_no=None): # 刷卡資料轉入odoo考勤 #logger = logging.getLogger('hr_attendance') _db = db models = endpoint_object() uid = get_uid() #thr_attendance = _db['hr_attendance'] days = calendar.monthrange(year, month)[1] date_start = '%04d-%02d-%02d' % (year, month, 1) date_end = '%04d-%02d-%02d' % (year, month, days) now_date = datetime.datetime.now().strftime("%H:%M:%S") logging.info('hr_attendance start: '+now_date) _sql = 'SELECT he.id as emp_id, hhc.* FROM htp_hr_cshift as hhc inner join hr_employee as he on (he.emp_no = hhc.cshift_emp_no) ' _sql += 'where he.id is not null and hhc.cshift_beg_date BETWEEN \'%s\' and \'%s\' and hhc.cshift_beg_time_value > 0 ' % ( date_start, date_end) if run_emp_no: _sql += 'and he.emp_no = \'%s\' ' % (run_emp_no) _sql += 'order by hhc.cshift_beg_date, hhc.cshift_beg_time ' data_rows = _db.query(_sql) if data_rows: datestr = date_start+' 00:00:00' dt = datetime2UTC(datestr) datestr = datetime.datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') datestr2 = date_end+' 00:00:00' dt = datetime2UTC(datestr2) datestr2 = datetime.datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') del_ids = None if run_emp_no: _sql = 'SELECT id as emp_id FROM hr_employee where emp_no = \'%s\' ' % (run_emp_no) emp_row = _db.query(_sql) for emp in emp_row: del_ids = models.execute_kw(odoo_db, uid, odoo_password, 'hr.attendance', 'search', [[ ['employee_id', '=', emp['emp_id']], ['check_in', '>=', datestr], ['check_in', '<=', datestr2]]]) else: del_ids = models.execute_kw(odoo_db, uid, odoo_password, 'hr.attendance', 'search', [[ ['check_in', '>=', datestr], ['check_in', '<=', datestr2]]]) if del_ids: models.execute_kw(odoo_db, uid, odoo_password, 'hr.attendance', 'unlink', [del_ids]) #_sql = 'delete from hr_attendance where date_part(\'year\', check_out) = %d and date_part(\'month\', check_out) = %d ' % (year, month) #if run_emp_no: # _sql += ' and employee_id = (select id from hr_employee where emp_no = \'%s\') ' % (run_emp_no) #stmt = text(_sql) #_db.executable.execute(stmt) for row in data_rows: datestr = '%s %s' % (row['cshift_beg_date'], row['cshift_beg_time']) dt = datetime2UTC(datestr) datestr = datetime.datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') datestr2 = '%s %s' % (row['cshift_beg_date'], row['cshift_end_time']) dt = datetime2UTC(datestr2) datestr2 = datetime.datetime.strftime(dt, '%Y-%m-%d %H:%M:%S') # 找不到寫入 #if (thr_attendance.find_one(employee_id=row['emp_id'], check_in=datestr, check_out=datestr2)) and (datestr <= datestr2): # if thr_attendance.find_one(employee_id=row['emp_id'], check_in=datestr): # thr_attendance.delete(employee_id=row['emp_id'], check_in=datestr) if datestr <= datestr2: models.execute_kw(odoo_db, uid, odoo_password, 'hr.attendance', 'create', [{ 'employee_id': row['emp_id'], 'check_in': datestr, 'check_out': datestr2, }]) now_date = datetime.datetime.now().strftime("%H:%M:%S") #print() logging.info('hr_attendance end: '+now_date) def test(): sfList = getShitft() timelist = [0.2, 0.41, 0.51, 0.53, 0.71, 0.73, 0.8] # timelist = [0.2, 0.41, 0.51, 0.53, 0.71, 0.73, 0.8] # timelist = [0.51, 0.52, 0.71, 0.73] # 前後忘刷 # timelist = [0.2, 0.51, 0.71, 0.73] # 前後忘刷 print(time2dict(timelist, sfList)) def run_export_cshift_file(db, year, month, run_emp_no=None): #logger = logging.getLogger('run_export_cshift_file') _db = db tcdata = _db[prefix+'_cdata'] tcshift = _db[prefix+'_cshift'] _strdate = '%04d%02d' % (year, month) sql = 'SELECT * FROM '+prefix + \ '_cshift where date_part(\'year\', cshift_beg_date) = %d and date_part(\'month\', cshift_beg_date) = %d ORDER BY cshift_beg_date, cshift_emp_no, cshift_no' % ( year, month) if run_emp_no is not None: sql = 'SELECT * FROM '+prefix + \ '_cshift where date_part(\'year\', cshift_beg_date) = %d and date_part(\'month\', cshift_beg_date) = %d and cshift_emp_no = \'%s\' ORDER BY cshift_beg_date, cshift_emp_no, cshift_no' % ( year, month, run_emp_no) data_rows = _db.query(sql) wb = xlwt.Workbook() sheet = wb.add_sheet(_strdate) # sheet.write(0, 0, '123') # sheet.write(0, 1, '567') # sheet.write(1, 0, '999') #第二行 titlelist = ['員工編號', '姓名', '部門', '時段', '開始日期', '開始時間', '結束日期', '結束時間', '遲到時間', '早退時間', '加班時間', '上班時間'] i = 0 for item in titlelist: sheet.write(0, i, item) i += 1 i = 1 for row in data_rows: empno = row['cshift_emp_no'] empname = row['cshift_emp_no'] dept = row['cshift_dept'] cshift_no = row['cshift_no'] cshift_beg_date = row['cshift_beg_date'].strftime("%Y-%m-%d") cshift_beg_time = row['cshift_beg_time'] cshift_end_date = row['cshift_end_date'].strftime("%Y-%m-%d") cshift_end_time = row['cshift_end_time'] cshift_late = row['cshift_late'] cshift_leave = row['cshift_leave'] cshift_over = row['cshift_over'] cshift_total = row['cshift_total'] tcrow = tcdata.find_one(cdata_emp_no=empno) if tcrow: empname = tcrow['cdata_emp_name'] sheet.write(i, 0, empno) sheet.write(i, 1, empname) sheet.write(i, 2, dept) sheet.write(i, 3, cshift_no) sheet.write(i, 4, cshift_beg_date) sheet.write(i, 5, cshift_beg_time) sheet.write(i, 6, cshift_end_date) sheet.write(i, 7, cshift_end_time) sheet.write(i, 8, cshift_late) sheet.write(i, 9, cshift_leave) sheet.write(i, 10, cshift_over) sheet.write(i, 11, cshift_total) i += 1 wb.save('cshitft_'+_strdate+'.xls') def reload_working_times(db, year, month): # 轉存table _db = db uid = get_uid() models = endpoint_object() _table = _db[prefix+'_working_times'] _table.delete() _btime_str = '%04d-%02d-01 00:00:00' % (year, month) days = calendar.monthrange(year, month)[1] _etime_str = '%04d-%02d-%02d 23:59:59' % (year, month, days) main_rows = _db.query('SELECT * FROM resource_calendar ORDER BY id ASC ') if uid and main_rows: _db.begin() try: for row in main_rows: work_times = models.execute_kw(odoo_db, uid, odoo_password, 'hr.payslip', 'get_working_times', [ row['id'], _btime_str, _etime_str]) # print(data) for item in work_times: if getHoliday(db, row['id'], item[0])[0]: # 國定假日 continue data = dict( work_id=row['id'], work_beg_time=item[0], work_end_time=item[1]) _table.insert(data) _db.commit() except Exception as e: logging.error(str(e)) _db.rollback() def test_over_amt(db): # 測試加班 reload_working_times(db, 2021, 3) emp = {} emp['resource_calendar_id'] = 1 hours = 10*60*60 # 小時 price = 100 # _amt = calc_over_amt( # db, emp, datetime.datetime(2021, 3, 18), hours) # 加班算金額 (平日) # print(_amt) # print(_amt[0]*price) # _amt = calc_over_amt( # db, emp, datetime.datetime(2021, 4, 6), hours) # 加班算金額 (例假日) # print(_amt) # print(_amt[0]*price) _amt = calc_over_amt( db, emp, datetime.datetime(2021, 3, 28), hours) # 加班算金額 (休息日) print(_amt) print(_amt[0]*price) def run_hr_cdata_dir(db, base_dir): ret = False _db = db _base_dir = base_dir _bak_dir = _base_dir+'/bak' # if not os.path.exists(_bak_dir): os.makedirs(_bak_dir, exist_ok=True) arr = glob.glob(_base_dir+'/*.xlsx') ret = (len(arr) > 0) for filename in arr: #print(filename) run_hr_cdata(_db, filename) # 輸入 fsa = filename.split('/') copyfile(filename, _bak_dir+'/'+fsa[-1]) os.remove(filename) return ret def run_now(db, filedir=None, now=None): _db = db _filedir = filedir if _filedir is None: _filedir = '/media/Share' # FMT = '%(asctime)s %(filename)s %(funcName)s %(levelname)s %(message)s' # logging.basicConfig(format=FMT, filename=_filedir+'/hr_carddata.log', level=logging.INFO) # sudo mount -t cifs //192.168.80.5/carddata /media/Share -o username='hhli',password='Ht1230136',uid=1000,domain=hantecprecision #fstab #//192.168.80.5/carddata /media/Share cifs username=hhli,password=Ht1230136,uid=1000,domain=hantecprecision #NFS # mount -t nfs 192.168.80.5:/volume1/carddata /media/Share _now = now if _now is None: _now = datetime.datetime.now() if run_hr_cdata_dir(_db, _filedir): # 取目錄資料 if _now.day in [1,2,3,4]: #每月1-3日重新計算上個月 _now2 =_now+relativedelta(months=-1) run_hr_cmonth(_db, _now2.year, _now2.month) # 計算上個月 run_hr_cmonth(_db, _now.year, _now.month, _now.day) # 計算 run_hr_attendance(_db, _now.year, _now.month) # 轉入odoo考勤 def run_month(year, month, days=None, url='http://192.168.80.3:8069', dbname='htp', dburl='postgresql://odoo:odootyughj@192.168.80.3:5432/htp'): global odoo_url, odoo_db odoo_url = url odoo_db = dbname db = dataset.connect(dburl) db.vars = {} db.vars['emplist'] = loadOdooEmp(db) run_hr_cmonth(db, year, month, days) def run_htp(url='http://192.168.80.3:8069', dbname='htp', dburl='postgresql://odoo:odootyughj@192.168.80.3:5432/htp'): global odoo_url, odoo_db #odoo_url = 'http://192.168.80.3:8069' odoo_url = url #odoo_db = 'htp' odoo_db = dbname #db = dataset.connect('postgresql://odoo:1230136@192.168.80.3:5432/htp') db = dataset.connect(dburl) db.vars = {} db.vars['emplist'] = loadOdooEmp(db) #run_now(db, now=datetime.datetime(2021, 10, 31)) run_now(db) if __name__ == "__main__": FMT = '%(asctime)s %(filename)s %(funcName)s %(levelname)s %(message)s' #logging.basicConfig(format=FMT, filename='hr.log', level=logging.DEBUG) logging.basicConfig(format=FMT, level=logging.INFO)#直列 if len(sys.argv) > 1: if sys.argv[1] == 'now': run_htp(url='http://192.168.80.3:8069', dbname='htp', dburl='postgresql://odoo:odootyughj@192.168.80.3:5432/htp') print('Run end.') else: try: dd = datetime.datetime.strptime(sys.argv[1], '%Y%m%d').date() _db = dataset.connect('postgresql://odoo:odootyughj@192.168.80.3:5432/htp') #正式 _db.vars = {} _db.vars['emplist'] = loadOdooEmp(_db) run_hr_cmonth(_db, dd.year, dd.month, dd.day) print('Run end.') except: print('Error date format.') exit() allow_none = True #debug #run_month(2021, 10, url='http://0.0.0.0:8069', dbname='htp', dburl='postgresql://odoo:odoo@192.168.80.94:5432/htp') #exit() # print(math.ceil(1.1)) #無修件進位 #print(math.floor(140.60)) #無條件去位 #run_htp(url='http://192.168.80.3:8069', dbname='htp', dburl='postgresql://odoo:odoo@192.168.80.3:5432/htp') #exit() #_db = dataset.connect('postgresql://odoo:odoo@192.168.80.193:5432/htp') #_db = dataset.connect( # 'postgresql://odoo:odootyughj@192.168.80.11:5432/htp_hr') #htp hr _db = dataset.connect( 'postgresql://odoo:odootyughj@192.168.80.3:5432/htp') #正式 _db.vars = {} # run_hr_attendance(_db, 2021, 6) # 轉入odoo考勤 # exit() # _db.vars['emplist'] = loadXlsEmp() _db.vars['emplist'] = loadOdooEmp(_db) _path = '/home/xz/htp/odoo13/extension/xls2table/tmp/' _filename = _path+'exm20220810.xls' #run_hr_cdata(_db, _filename) # 輸入 # test_over_amt(_db) # run_hr_cshift(_db, datetime.date(2021, 1, 6)) #run_hr_cmonth(_db, 2022, 8, run_emp_no='202008004') # 計算 #run_hr_cmonth(_db, 2022, 8) # 計算 #run_hr_cmonth(_db, 2022, 8, 29) # 計算 # run_export_cshift_file(_db, 2021, 1) #匯出excel #run_hr_attendance(_db, 2021, 12) # 轉入odoo考勤