예제 #1
0
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)
예제 #2
0
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)
예제 #3
0
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)