#!/usr/bin/python
# -*- coding: UTF-8 -*-

import xlrd
import xlwt
import time
import datetime


# 判断是不是工作日
# 工作日返回 1
# 节假日返回 2
# 双休日返回 3
def isWorkDay(cell, workbook):
    print(type(cell), cell)
    if cell.ctype == 3:
        tuple = xlrd.xldate_as_tuple(cell.value, workbook.datemode)
        date = datetime.datetime(*tuple[:3])
        str_date = date.strftime('%Y-%m-%d')
    else:
        str_date = cell.value
    # 本来是周六、周日。因为节假日调班，调整为上班时间。
    workdays = []
    # 本来是周一、周二、周三、周四、周五。因为节假日，调整为放假时间。
    holidays = ["2022-06-03", "2022-09-12"]
    workday = 1
    if str_date in workdays:
        workday = 1
    elif str_date in holidays:
        workday = 2
    else:
        week = datetime.datetime.strptime(str_date, "%Y-%m-%d").weekday()
        # 周六、周日（双休日）
        if (week == 5 or week == 6):
            workday = 3
        else:
            workday = 1
    return workday


# 判断有没有加班
# 如果是工作日，加班到20:00以后，即算做加班。
# 如果是节假日/双休日，加班时间超过4小时，即算做加班。
# 没有加班返回 0，工作日加班返回 1，节假日加班返回 2，双休日加班返回 3
def isOvertimeWork(date, leave, working, workbook):
    overtime = 0
    # 判断是不是工作日
    workday = isWorkDay(date, workbook)
    if workday == 1:
        # 工作日，可能请假、可能调休，总之当天没有下班打卡，肯定不算加班
        # 有下班打卡记录，并且到20:00以后，算加班
        if leave.value == '':
            return overtime
        if leave.ctype == 3:
            tuple = xlrd.xldate_as_tuple(leave.value, workbook.datemode)
            time = datetime.datetime(*tuple[:6])
        else:
            time = datetime.datetime.strptime(leave.value, "%Y-%m-%d %H:%M:%S")
        # 工作日 判断是不是20:00以后
        if time.hour >= 20:
            overtime = 1
    else:
        # 节假日/双休日，工作时间为空，肯定没加班
        # 工作时间大于 4小时，算加班
        if working.value == '':
            return overtime
        if int(working.value) > 240:
            if workday == 2:
                # 节假日
                overtime = 2
            else:
                # 双休日
                overtime = 3
    
    return overtime
    
    
# 表单首行，写标题
def write_a_title(sheet):
    # 样式
    style = xlwt.easyxf('font: bold on')
    
    # 第1行写标题
    sheet.write(0, 0, '员工工号', style)
    sheet.write(0, 1, '员工姓名', style)
    sheet.write(0, 2, '考勤类型', style)
    sheet.write(0, 3, '一级部门', style)
    sheet.write(0, 4, '二级部门', style)
    
    sheet.write(0, 5, '三级部门', style)
    sheet.write(0, 6, '考勤日期', style)
    sheet.write(0, 7, '状态', style)
    sheet.write(0, 8, '状态描述', style)
    sheet.write(0, 9, '上班打卡时间', style)
    
    sheet.write(0, 10, '上班描述', style)
    sheet.write(0, 11, '下班打卡时间', style)
    sheet.write(0, 12, '加班打卡时间', style)
    sheet.write(0, 13, '下班描述', style)
    sheet.write(0, 14, '迟到分钟数', style)
    
    sheet.write(0, 15, '早退分钟数', style)
    sheet.write(0, 16, '加班分钟数', style)
    sheet.write(0, 17, '上班分钟数', style)
    sheet.write(0, 18, '备注', style)
    sheet.write(0, 19, '是否加班', style)
    pass
    
    
# 表单首行，写标题
def write_b_title(sheet):
    # 样式
    style = xlwt.easyxf('font: bold on')
    
    # 第1行写标题
    sheet.write(0, 0, '姓名', style)
    sheet.write(0, 1, '工号', style)
    sheet.write(0, 2, '加班次数', style)
    sheet.write(0, 3, '报销参数', style)
    sheet.write(0, 4, '银行卡', style)

    sheet.write(0, 5, '开户行', style)
    sheet.write(0, 6, '所属项目', style)
    sheet.write(0, 7, '项目编号', style)
    pass
    
    
# 表单首行，写标题
def write_c_title(sheet):
    # 样式
    style = xlwt.easyxf('font: bold on')
    
    # 第1行写标题
    sheet.write(0, 0, '项目名称', style)
    sheet.write(0, 1, '项目编号', style)
    sheet.write(0, 2, '承担本次报销比例', style)
    sheet.write(0, 3, '承担本次报销金额', style)
    
    # 第4行写标题
    sheet.write(4, 0, '总计', style)
    pass
    
    
#  写加班数据
def write_overtime_work(sheet, path, begin=0):
    # 读取Excel
    read_workbook = xlrd.open_workbook(path)
    read_sheet = read_workbook.sheets()[0]
    dict = employeesDictionary()
    index = begin
    # 日期格式
    style1 = xlwt.XFStyle()
    style1.num_format_str = 'YYYY-MM-DD'
    style2 = xlwt.XFStyle()
    style2.num_format_str = 'YYYY-MM-DD hh:mm:ss'
    # 循环遍历行，从第1行开始，因为头行是标题
    for row in range(1, read_sheet.nrows):
        # 员工工号
        c0 = read_sheet.cell_value(row, 0)
        # 如果员工工号不在统计范围，就不统计了
        if c0 not in dict:
            continue
        # 考勤日期
        c6 = read_sheet.cell(row, 6)
        # 下班打卡
        c11 = read_sheet.cell(row, 11)
        # 上班分钟数
        c17 = read_sheet.cell(row, 17)
        
        # 根据日期+下班打卡+上班分钟数 判断是否加班
        overtime = isOvertimeWork(c6, c11, c17, read_workbook)
        # 如果加班时间不为0，才写入表单
        if overtime != 0:
            for col in range(0, read_sheet.ncols):
                cell = read_sheet.cell(row, col)
                if (col == 6 or col == 12) and cell.ctype == 3:
                    sheet.write(index, col, cell.value, style1)
                elif (col == 9 or col == 11) and cell.ctype == 3:
                    sheet.write(index, col, cell.value, style2)
                    print(cell.value)
                else:
                    sheet.write(index, col, cell.value)
                pass
            if overtime == 2:
                sheet.write(index, 18, '节假日')
            elif overtime == 3:
                sheet.write(index, 18, '双休日')
            # 自增行数
            sheet.write(index, 19, '加班')
            index = index + 1
        pass
    return index


# 银行卡信息
def employeesDictionary():
    dictionary = {}
    dictionary["工号"] = {"name":'姓名', "card number":'银行卡号', "bank name":'银行名称'}

    return dictionary
    

# 按工号计算加班次数
def countByGroup(path, wsheet):
    # 读取Excel
    read_workbook = xlrd.open_workbook(path)
    rsheet = read_workbook.sheets()[0]
    index = 0
    count = 0
    number = ''
    dict = employeesDictionary()
    # 循环遍历行，从第1行开始，因为头行是标题
    for row in range(1, rsheet.nrows):
        # 员工工号
        temp = rsheet.cell_value(row, 0)
        if number == temp:
            count = count + 1
        else:
            number = temp
            index = index + 1
            count = 1
            # 员工姓名
            name = rsheet.cell_value(row, 1)
            # 姓名
            wsheet.write(index, 0, name)
            # 工号
            wsheet.write(index, 1, temp)
        # 加班次数
        wsheet.write(index, 2, count)
        # 报销金额
        wsheet.write(index, 3, count*25)
        # 判断员工工号在数组中
        if temp in dict:
            info = dict[temp]
            # 银行卡
            wsheet.write(index, 4, info["card number"])
            # 开户行
            wsheet.write(index, 5, info["bank name"])
        else:
            print(temp + "不在部门内！")
    pass
     

# 筛选加班
def fitlerOvertimeWork():
    # 写入Excel
    write_workbook = xlwt.Workbook()
    # 创建表单
    w1_sheet = write_workbook.add_sheet('打卡考勤信息', cell_overwrite_ok=True)
    # 创建表单
    w2_sheet = write_workbook.add_sheet('加班统计与账号信息', cell_overwrite_ok=True)
    # 创建表单
    w3_sheet = write_workbook.add_sheet('项目分担比例与金额', cell_overwrite_ok=True)
    
    # 表单首行，写标题
    write_a_title(w1_sheet)
    # 表单首行，写标题
    write_b_title(w2_sheet)
    # 表单首行，写标题
    write_c_title(w3_sheet)
    
    # 考勤原始数据路径
    data1_path = '/Users/ll/Desktop/Python/Fee/data/考勤.xls'
    # 加班筛选后路径
    save_path = '/Users/ll/Desktop/Python/Fee/考勤-筛选后.xls'
    
    # 写加班数据
    begin = 1
    begin = write_overtime_work(w1_sheet, data1_path, begin)
    # 保存Excel
    write_workbook.save(save_path)

    # 按工号分组计算加班次数
    # 写入到sheet 2
    countByGroup(save_path, w2_sheet)
    # 保存Excel
    write_workbook.save(save_path)
    pass


# 主方法
if __name__ == "__main__":
    fitlerOvertimeWork()
    pass