def calculate_leave_day(request):
"""
计算请假开始日期至结束日期之间的工作日天数
:param request:
:return:
"""
start_date = request.POST.get('start_date', '').replace('-', '')
end_date = request.POST.get('end_date', '').replace('-', '')
start_time = request.POST.get('start_time', '')
end_time = request.POST.get('end_time', '')
if start_date and end_date and start_time and end_time:
start_datetime = datetime.datetime.strptime(start_date, "%Y%m%d").date()
end_datetime = datetime.datetime.strptime(end_date, "%Y%m%d").date()
day_list = [str(start_datetime+datetime.timedelta(days=i)).replace('-', '')
for i in xrange((end_datetime - start_datetime).days+1)]
day_status = get_work_days(day_list)
print(day_status)
if isinstance(day_status, dict): # several days
counter = Counter(v for k, v in day_status.items())
print(counter.get('1'), counter.get('2'))
# 不以周末或者法定节假日开始或者结束的情况
leave_days_count = counter.get('0')
if not day_status.get(day_list[0]) in (1, 2) and not day_status.get(day_list[-1]) in (1, 2):
if (start_time == '08:30' and end_time == '13:30') or (start_time == '11:00' and end_time == '17:00'):
leave_days_count -= 0.5
elif start_time == '11:00' and end_time == '13:30':
leave_days_count -= 1
elif day_status.get(day_list[0]) in (1, 2) and day_status.get(day_list[-1]) == 0: # 以周末或者法定节假日开始的情况
if end_time == '13:30':
leave_days_count -= 0.5
elif day_status.get(day_list[-1]) in (1, 2) and day_status.get(day_list[0]) == 0: # 以周末或者法定节假日结束的情况
if start_time == '11:00':
leave_days_count -= 0.5
else: # 开始结束 全都是周末或者法定节假日
leave_days_count = 0
else: # single day
if day_status in (1, 2): # single day (weekend or vacation)
leave_days_count = 0
else: # working day
if (start_time == '08:30' and end_time == '13:30') or (start_time == '11:00' and end_time == '17:00'):
leave_days_count = 0.5
elif start_time == '11:00' and end_time == '13:30':
leave_days_count = 0
elif start_time == '08:30' and end_time == '17:00':
leave_days_count = 1
else:
leave_days_count = 0
else:
leave_days_count = 0
return HttpResponse(leave_days_count)
def deal_original_data(year, month, excel_obj):
"""
处理原始考勤总记录，补充公休假日，法定假日，请假/外出记录，迟到 晚签到 早退 缺勤
:return:
"""
month_days = calendar.monthrange(int(year), int(month))[1]
start_datetime = datetime.datetime.strptime('%s%s01' % (year, month), "%Y%m%d").date()
end_datetime = datetime.datetime.strptime('%s%s%s' % (year, month, month_days), "%Y%m%d").date()
day_list = [str(start_datetime+datetime.timedelta(days=i)).replace('-', '')
for i in xrange((end_datetime - start_datetime).days+1)]
day_status = get_work_days(day_list)
# print day_status
# excel_obj = xlrd.open_workbook('/Users/cai/Documents/考勤系统需求说明书及附件/new_test_data.xls')
wb = excel_copy(excel_obj)
# 0: 合同制; 1: 项目合作; 2: 实习生
for j in [0, 1, 2]:
table0 = excel_obj.sheet_by_index(j) # 通过索引顺序获取
ws = wb.get_sheet(j)
ws.write(0, 5 if j != 1 else 6, '备注1')
tmp = 0 # 晚签到计数器
for i in xrange(1, table0.nrows):
if table0.cell(i, 1).value != table0.cell(i-1, 1).value:
tmp = 0 # 不同员工的 晚签到计数器重置为0
duty_time = table0.cell(i, 4 if j != 1 else 5).value
name = table0.cell(i, 1).value.replace(' ', '')
# print(duty_time)
day = table0.cell(i, 3 if j != 1 else 4).value.split('-') # 2016-1-1 need to change to 20160101
format_day = '%s%s%s' % (day[0], day[1] if int(day[1]) >= 10 else '0%s' % day[1],
day[2] if int(day[2]) >= 10 else '0%s' % day[2])
if day_status[format_day] == '1':
ws.write(i, 5 if j != 1 else 6, '公休假日')
elif day_status[format_day] == '2':
# print i
ws.write(i, 5 if j != 1 else 6, '法定假日')
else: # 工作日
start_time = datetime.datetime(int(day[0]), int(day[1]), int(day[2]), 8, 30) # todo 这里要修改 不是按整天请假的查询不出
end_time = datetime.datetime(int(day[0]), int(day[1]), int(day[2]), 17, 00)
if duty_time == '': # 没考勤记录
leaves = Leave.objects.filter(leave_start_datetime__lte=start_time,
leave_end_datetime__gte=end_time,
applicant_name=name).exclude(status=0)
if leaves: # 当天存在请假/外出记录
leave_days = leaves.first().leave_days
if leaves.first().group == 1: # 请假
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else: # 外出
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else:
ws.write(i, 5 if j != 1 else 6, '缺勤一天')
elif len(duty_time.split(' ')) == 1: # 只有一次考勤记录
# 查询当天的记录
leaves = Leave.objects.filter(leave_start_datetime__gte=start_time, leave_end_datetime__lte=end_time,
applicant_name=name)
# 查询多天的记录
if not leaves:
leaves = Leave.objects.filter(leave_start_datetime__lte=start_time, leave_end_datetime__gte=end_time,
applicant_name=name)
if leaves:
leave_days = leaves.first().leave_days
if leaves.first().group == 1: # 请假
if leaves.first().leave_start_datetime.hour == 11: # 代表请了下午
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天(下午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
elif leaves.first().leave_end_datetime.hour == 13: # 代表请了上午
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天(上午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else:
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else: # 外出
if leaves.first().leave_start_datetime.hour == 11: # 代表请了下午
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天(下午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
elif leaves.first().leave_end_datetime.hour == 13: # 代表请了上午
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天(上午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else:
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else: # 当天没有请假/外出记录
ws.write(i, 5 if j != 1 else 6, '缺勤一天')
else: # 有两次以上的考勤记录
on_duty_time = duty_time.split(' ')[0].split(':') # ['08', '23']
off_duty_time = duty_time.split(' ')[-1].split(':') # ['08', '23']
content = ''
# print tmp
if datetime.time(8, 30) < datetime.time(int(on_duty_time[0]), int(on_duty_time[1])) <= datetime.time(8, 45):
content = '晚签到'
tmp += 1
if tmp > 2: # 晚签第三次以后为迟到
content = '迟到'
elif datetime.time(8, 46) <= datetime.time(int(on_duty_time[0]), int(on_duty_time[1])) <= datetime.time(10, 30):
content = '迟到'
elif datetime.time(10, 31) <= datetime.time(int(on_duty_time[0]), int(on_duty_time[1])) <= datetime.time(12, 30):
content = '缺勤上午'
elif datetime.time(12, 31) <= datetime.time(int(on_duty_time[0]), int(on_duty_time[1])):
content = '缺勤一天'
if datetime.time(15, 00) <= datetime.time(int(off_duty_time[0]), int(off_duty_time[1])) < datetime.time(17, 00):
content += '早退'
elif datetime.time(13, 00) <= datetime.time(int(off_duty_time[0]), int(off_duty_time[1])) < datetime.time(15, 00):
content += '缺勤下午'
elif datetime.time(int(off_duty_time[0]), int(off_duty_time[1])) < datetime.time(13, 00):
content = '缺勤一天'
if content == '缺勤上午缺勤下午':
content = '缺勤一天'
if '缺勤一天' in content:
content = '缺勤一天'
# 增加异常考勤状态时, 再次查询有无考勤记录, 特别是请假或者外出半天的情况
if content in ('迟到', '晚签到', '缺勤上午', '缺勤下午', '缺勤一天', '早退'):
leaves = Leave.objects.filter(leave_start_datetime__gte=start_time, leave_end_datetime__lte=end_time,
applicant_name=name).exclude(status=0)
if leaves:
leave_days = leaves.first().leave_days
if leaves.first().group == 1: # 请假
if leaves.first().leave_start_datetime.hour == 11: # 代表请了下午
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天(下午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
elif leaves.first().leave_end_datetime.hour == 13: # 代表请了上午
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天(上午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else:
ws.write(i, 5 if j != 1 else 6, '请假(%s)%s天' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else: # 外出
if leaves.first().leave_start_datetime.hour == 11: # 代表请了下午
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天(下午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
elif leaves.first().leave_end_datetime.hour == 13: # 代表请了上午
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天(上午)' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else:
ws.write(i, 5 if j != 1 else 6, '外出(%s)%s天' % (LEAVE_TYPE_DICT[leaves.first().type], leave_days))
else: # 当天没有请假/外出记录
ws.write(i, 5 if j != 1 else 6, content)
pass
ws.col(4).width = 5000 # 第五列的宽度
ws.col(5).width = 5000
ws.col(6).width = 5000
return wb, '%s年%s月原始记录总表.xls' % (year, month)
def deal_original_data(year, month, excel_obj):
"""
处理原始考勤总记录，补充公休假日，法定假日，请假/外出记录，迟到 晚签到 早退 缺勤
:return:
"""
month_days = calendar.monthrange(int(year), int(month))[1]
start_datetime = datetime.datetime.strptime('%s%s01' % (year, month),
"%Y%m%d").date()
end_datetime = datetime.datetime.strptime(
'%s%s%s' % (year, month, month_days), "%Y%m%d").date()
day_list = [
str(start_datetime + datetime.timedelta(days=i)).replace('-', '')
for i in xrange((end_datetime - start_datetime).days + 1)
]
day_status = get_work_days(day_list)
# print day_status
# excel_obj = xlrd.open_workbook('/Users/cai/Documents/考勤系统需求说明书及附件/new_test_data.xls')
wb = excel_copy(excel_obj)
# 0: 合同制; 1: 项目合作; 2: 实习生
for j in [0, 1, 2]:
table0 = excel_obj.sheet_by_index(j) # 通过索引顺序获取
ws = wb.get_sheet(j)
ws.write(0, 5 if j != 1 else 6, '备注1')
tmp = 0 # 晚签到计数器
for i in xrange(1, table0.nrows):
if table0.cell(i, 1).value != table0.cell(i - 1, 1).value:
tmp = 0 # 不同员工的 晚签到计数器重置为0
duty_time = table0.cell(i, 4 if j != 1 else 5).value
name = table0.cell(i, 1).value.replace(' ', '')
# print(duty_time)
day = table0.cell(i, 3 if j != 1 else 4).value.split(
'-') # 2016-1-1 need to change to 20160101
format_day = '%s%s%s' % (
day[0], day[1] if int(day[1]) >= 10 else '0%s' % day[1],
day[2] if int(day[2]) >= 10 else '0%s' % day[2])
if day_status[format_day] == '1':
ws.write(i, 5 if j != 1 else 6, '公休假日')
elif day_status[format_day] == '2':
# print i
ws.write(i, 5 if j != 1 else 6, '法定假日')
else: # 工作日
start_time = datetime.datetime(int(day[0]), int(day[1]),
int(day[2]), 8,
30) # todo 这里要修改 不是按整天请假的查询不出
end_time = datetime.datetime(int(day[0]), int(day[1]),
int(day[2]), 17, 00)
if duty_time == '': # 没考勤记录
leaves = Leave.objects.filter(
leave_start_datetime__lte=start_time,
leave_end_datetime__gte=end_time,
applicant_name=name).exclude(status=0)
if leaves: # 当天存在请假/外出记录
leave_days = leaves.first().leave_days
if leaves.first().group == 1: # 请假
ws.write(
i, 5 if j != 1 else 6, '请假(%s)%s天' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else: # 外出
ws.write(
i, 5 if j != 1 else 6, '外出(%s)%s天' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else:
ws.write(i, 5 if j != 1 else 6, '缺勤一天')
elif len(duty_time.split(' ')) == 1: # 只有一次考勤记录
# 查询当天的记录
leaves = Leave.objects.filter(
leave_start_datetime__gte=start_time,
leave_end_datetime__lte=end_time,
applicant_name=name)
# 查询多天的记录
if not leaves:
leaves = Leave.objects.filter(
leave_start_datetime__lte=start_time,
leave_end_datetime__gte=end_time,
applicant_name=name)
if leaves:
leave_days = leaves.first().leave_days
if leaves.first().group == 1: # 请假
if leaves.first(
).leave_start_datetime.hour == 11: # 代表请了下午
ws.write(
i, 5 if j != 1 else 6, '请假(%s)%s天(下午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
elif leaves.first(
).leave_end_datetime.hour == 13: # 代表请了上午
ws.write(
i, 5 if j != 1 else 6, '请假(%s)%s天(上午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else:
ws.write(
i, 5 if j != 1 else 6, '请假(%s)%s天' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else: # 外出
if leaves.first(
).leave_start_datetime.hour == 11: # 代表请了下午
ws.write(
i, 5 if j != 1 else 6, '外出(%s)%s天(下午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
elif leaves.first(
).leave_end_datetime.hour == 13: # 代表请了上午
ws.write(
i, 5 if j != 1 else 6, '外出(%s)%s天(上午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else:
ws.write(
i, 5 if j != 1 else 6, '外出(%s)%s天' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else: # 当天没有请假/外出记录
ws.write(i, 5 if j != 1 else 6, '缺勤一天')
else: # 有两次以上的考勤记录
on_duty_time = duty_time.split(' ')[0].split(
':') # ['08', '23']
off_duty_time = duty_time.split(' ')[-1].split(
':') # ['08', '23']
content = ''
# print tmp
if datetime.time(8, 30) < datetime.time(
int(on_duty_time[0]), int(
on_duty_time[1])) <= datetime.time(8, 45):
content = '晚签到'
tmp += 1
if tmp > 2: # 晚签第三次以后为迟到
content = '迟到'
elif datetime.time(8, 46) <= datetime.time(
int(on_duty_time[0]), int(
on_duty_time[1])) <= datetime.time(10, 30):
content = '迟到'
elif datetime.time(10, 31) <= datetime.time(
int(on_duty_time[0]), int(
on_duty_time[1])) <= datetime.time(12, 30):
content = '缺勤上午'
elif datetime.time(12, 31) <= datetime.time(
int(on_duty_time[0]), int(on_duty_time[1])):
content = '缺勤一天'
if datetime.time(15, 00) <= datetime.time(
int(off_duty_time[0]), int(
off_duty_time[1])) < datetime.time(17, 00):
content += '早退'
elif datetime.time(13, 00) <= datetime.time(
int(off_duty_time[0]), int(
off_duty_time[1])) < datetime.time(15, 00):
content += '缺勤下午'
elif datetime.time(int(off_duty_time[0]),
int(off_duty_time[1])) < datetime.time(
13, 00):
content = '缺勤一天'
if content == '缺勤上午缺勤下午':
content = '缺勤一天'
if '缺勤一天' in content:
content = '缺勤一天'
# 增加异常考勤状态时, 再次查询有无考勤记录, 特别是请假或者外出半天的情况
if content in ('迟到', '晚签到', '缺勤上午', '缺勤下午', '缺勤一天', '早退'):
leaves = Leave.objects.filter(
leave_start_datetime__gte=start_time,
leave_end_datetime__lte=end_time,
applicant_name=name).exclude(status=0)
if leaves:
leave_days = leaves.first().leave_days
if leaves.first().group == 1: # 请假
if leaves.first(
).leave_start_datetime.hour == 11: # 代表请了下午
ws.write(
i, 5 if j != 1 else 6,
'请假(%s)%s天(下午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
elif leaves.first(
).leave_end_datetime.hour == 13: # 代表请了上午
ws.write(
i, 5 if j != 1 else 6,
'请假(%s)%s天(上午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else:
ws.write(
i, 5 if j != 1 else 6, '请假(%s)%s天' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else: # 外出
if leaves.first(
).leave_start_datetime.hour == 11: # 代表请了下午
ws.write(
i, 5 if j != 1 else 6,
'外出(%s)%s天(下午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
elif leaves.first(
).leave_end_datetime.hour == 13: # 代表请了上午
ws.write(
i, 5 if j != 1 else 6,
'外出(%s)%s天(上午)' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else:
ws.write(
i, 5 if j != 1 else 6, '外出(%s)%s天' %
(LEAVE_TYPE_DICT[leaves.first().type],
leave_days))
else: # 当天没有请假/外出记录
ws.write(i, 5 if j != 1 else 6, content)
pass
ws.col(4).width = 5000 # 第五列的宽度
ws.col(5).width = 5000
ws.col(6).width = 5000
return wb, '%s年%s月原始记录总表.xls' % (year, month)