from base_resource import BaseResource,SendBaseResource
from leave_off_work_model import LeaveOffWorkModel
from leave_signing_status_model import LeaveSigningStatusModel
from mysql_model import PersonModel
from werkzeug.datastructures import FileStorage
import os, werkzeug, base64, pandas as pd
from docx import Document
from io import BytesIO
import datetime
from mysql_model import UserModel
from punch_card_resource import PunchCardRecord,PunchCardRecordVerson2

os.makedirs(os.path.join('static', 'leave_off_work'), 0o777, True)

# ========== 新增請假紀錄 ==========
class LeaveOffWorkInsert(BaseResource):
    def __init__(self):
        super().__init__("InsertLeaveOffWork")
        self.parser.add_argument('depart_leave_unit_department', required=True, help="隸屬部門，上限10字")
        self.parser.add_argument('person_id', required=True, help="人員ID，上限10字")
        self.parser.add_argument('category', help="請假類型，上限10字")
        self.parser.add_argument('reason', help="請假原因，上限15字")
        self.parser.add_argument('start', help="開始時間，上限15字")
        self.parser.add_argument('end', help="結束時間，上限15字")
        self.parser.add_argument('leave_day', help="請假天數")
        self.parser.add_argument('leave_time', help="請假時長")
        self.parser.add_argument('substitute', help="代理人")
        self.parser.add_argument('upload_file',type=FileStorage, location='files', help="證明文件")
    def post(self):
        args = self.parser.parse_args()
        model = LeaveOffWorkModel.FromDBByStartAndEnd(args['person_id'],args['start'],args['end'], self.log.SetMessage)
        if not model.isExist:
            model.SetDepartLeaveUnitDepartment(args['depart_leave_unit_department'])
            model.SetPersonId(args['person_id'])
            model.SetCategory(args['category'])
            model.SetReason(args['reason'])
            model.SetStart(args['start'])
            model.SetEnd(args['end'])
            model.SetLeaveDay(args['leave_day'])
            model.SetLeaveTime(args['leave_time'])
            model.SetSubstitute(args['substitute'])
            if args['upload_file'] is not None:
                filename = "{0}.jpg".format(os.path.join("static", "leave_off_work", args['person_id']))
                args['upload_file'].save(filename)
                model.SetUploadFile(filename)
            if model.InsertToDB(self.log.SetMessage):
                self.message = "person：{0} start：{1},end：{2} inserted".format(args['person_id'],args['start'],args['end'])
            else:
                self.message = "person：{0} start：{1},end：{2} insert failed".format(args['person_id'],args['start'],args['end'])
        else:
            self.message = "person：{0} start：{1},end：{2} is exist in database".format(args['person_id'],args['start'],args['end'])
        return self.GetResponse()

# ========== 更新請假紀錄 ==========
class LeaveOffWorkUpdate(BaseResource):
    def __init__(self):
        super().__init__("UpdateLeaveOffWork")
        self.parser.add_argument('no', required=True, help="請假編號")
        self.parser.add_argument('depart_leave_unit_department', required=True, help="隸屬部門，上限10字")
        self.parser.add_argument('person_id', required=True, help="人員ID，上限10字")
        self.parser.add_argument('category', help="請假類型，上限10字")
        self.parser.add_argument('reason', help="請假原因，上限15字")
        self.parser.add_argument('start', help="開始時間，上限15字")
        self.parser.add_argument('end', help="結束時間，上限15字")
        self.parser.add_argument('leave_day', help="請假天數")
        self.parser.add_argument('leave_time', help="請假時長")
        self.parser.add_argument('substitute', help="代理人")
        self.parser.add_argument('upload_file',type=FileStorage, location='files', help="證明文件")
    def post(self):
        args = self.parser.parse_args()
        check = LeaveOffWorkModel.FromDBByStartAndEnd(args['person_id'],args['start'],args['end'], self.log.SetMessage)
        model = LeaveOffWorkModel.FromDB(args['no'], self.log.SetMessage)
        if model.isExist:
            model.SetDepartLeaveUnitDepartment(args['depart_leave_unit_department'])
            model.SetPersonId(args['person_id'])
            model.SetCategory(args['category'])
            model.SetReason(args['reason'])
            model.SetStart(args['start'])
            model.SetEnd(args['end'])
            model.SetLeaveDay(args['leave_day'])
            model.SetLeaveTime(args['leave_time'])
            model.SetSubstitute(args['substitute'])
            if args['upload_file'] is not None:
                filename = "{0}.jpg".format(os.path.join("static", "leave_off_work", args['no']))
                args['upload_file'].save(filename)
                model.SetUploadFile(filename)
            if model.UpdateToDB(self.log.SetMessage):
                self.message = []
                if model.HasChangeDepartLeaveUnitDepartment():
                    self.message.append(
                        "DepartLeaveUnitDepartment: {0} -> {1}".format(model.GetOldDepartLeaveUnitDepartment(), model.GetDepartLeaveUnitDepartment())
                    )
                if model.HasChangePersonId():
                    self.message.append(
                        "PersonId: {0} -> {1}".format(model.GetOldPersonId(), model.GetPersonId())
                    )
                if model.HasChangeCategory():
                    self.message.append(
                        "Category: {0} -> {1}".format(model.GetOldCategory(), model.GetCategory())
                    )
                if model.HasChangeReason():
                    self.message.append(
                        "Reason: {0} -> {1}".format(model.GetOldReason(), model.GetReason())
                    )
                if model.HasChangeStart():
                    self.message.append(
                        "Start: {0} -> {1}".format(model.GetOldStart(), model.GetStart())
                    )    
                if model.HasChangeEnd():
                    self.message.append(
                        "End: {0} -> {1}".format(model.GetOldEnd(), model.GetEnd())
                    )
                if model.HasChangeLeaveDay():
                    self.message.append(
                        "LeaveDay: {0} -> {1}".format(model.GetOldLeaveDay(), model.GetLeaveDay())
                    )
                if model.HasChangeLeaveTime():
                    self.message.append(
                        "LeaveTime: {0} -> {1}".format(model.GetOldLeaveTime(), model.GetLeaveTime())
                    )
                if model.HasChangeSubstitute():
                    self.message.append(
                        "Substitute: {0} -> {1}".format(model.GetOldSubstitute(), model.GetSubstitute())
                    )
                if model.HasChangeUploadFile():
                    self.message.append(
                        "UploadFile: {0} -> {1}".format(model.GetOldUploadFile(), model.GetUploadFile())
                    )
                self.message = ", ".join(self.message) if len(self.message) > 0 else ""
            else:
                self.message = "person：{0} update LeaveOffWork failed".format(args['person_id'])
        else:
            self.message = "person：{0}'s LeaveOffWork isn't exist in database".format(args['person_id'])
        return self.GetResponse()
# ========== 刪除請假紀錄 ==========
class LeaveOffWorkDelete(BaseResource):
    def __init__(self):
        super().__init__("DeleteLeaveOffWork")
        self.parser.add_argument('no', required=True)
    def post(self):
        args = self.parser.parse_args()
        model = LeaveOffWorkModel.FromDB(args['no'], self.log.SetMessage)
        if model.isExist:
            if model.DeleteFromDB(self.log.SetMessage):
                self.message = "{0} deleted".format(args['no'], model.GetNow())
            else:
                self.message = "{0} delete failed".format(args['no'])
        else:
            self.message = "{0} isn't exist in database".format(args['no'])
        return self.GetResponse()    
# ========== 查詢所有請假紀錄 ==========
class LeaveOffWorkSelectAll(BaseResource):
    def __init__(self):
        super().__init__("SelectLeaveOffWork")
    def post(self, ID=None):
        models = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        if len(models) > 0:
            self.data = []
            for model in models:
                self.data.append(model.ToDict())
            self.message = "{0} selected".format(len(self.data))
        else:
            self.message = "no data in database"
        return self.GetResponse()    
# ========== word匯出假單 ==========
class LeaveToWord(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('leave_id', required=True, help="假單編號")
        self.parser.add_argument('person_id', required=True, help="員工編號")
        self.parser.add_argument('person_name', required=True, help="員工姓名")
        self.parser.add_argument('department', required=True, help="部門名稱")
        self.parser.add_argument('category', required=True, help="請假類別")
        self.parser.add_argument('substitute', required=True, help="代理人")
        self.parser.add_argument('start_date', required=True, help="開始日期")
        self.parser.add_argument('end_date', required=True, help="結束日期")
        self.parser.add_argument('start_time', required=True, help="開始時間")
        self.parser.add_argument('end_time', required=True, help="結束時間")
        self.parser.add_argument('hour', required=True, help="請假時數")
        self.parser.add_argument('reason', required=True, help="請假原因")
    def post(self):
        args = self.parser.parse_args()
        path1=os.path.abspath('./')
        doc = Document('resources/test/員工請假單.docx')
        now = datetime.datetime.now()
        doc.paragraphs[2].text='請假單編號:{0}                            列印日期:{1}年 {2}月 {3}日'.format(args['leave_id'],now.year,now.month,now.day)
        tables = doc.tables
        table = tables[0]
        table.cell(0,1).text=args['person_id']
        table.cell(1,1).text=args['person_name']
        table.cell(2,1).text=args['department']
        table.cell(3,1).text=args['category']
        table.cell(3,3).text=args['substitute']
        table.cell(4,1).text=args['start_date']
        table.cell(4,3).text=args['start_time']
        table.cell(5,1).text=args['end_date']
        table.cell(5,3).text=args['end_time']
        table.cell(6,1).text=args['hour']
        table.cell(7,1).text=args['reason']
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        for l in leave:
            if(l.GetNo()==args['leave_id'] and l.GetUploadFile()!=None):
                doc.add_picture(r'{1}/static/leave_off_work/{0}.jpg'.format(args['leave_id'],path1))
        stream = BytesIO()
        doc.save(stream)
        stream.seek(0)
        #doc.save('/home/shuochen/Documents/punch_card_api/flask/notebook/resources/test/申請書{0}.docx'.format(args['name']))
        return self.GetWordResponse("請假單{0}".format(args['leave_id']), stream)
# ========== excel匯出區間假單 ==========
class LeaveToExcel(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('start', required=True, help="開始時間")
        self.parser.add_argument('end', required=True, help="結束時間")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        time_leave=[]
        StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
        EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
        for l in leave:
            leave_start=l.GetStart()[0:10]
            StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
            leave_end=l.GetEnd()[0:10]
            EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
            if(StartTime<=StartDay and EndTime>=EndDay):
                time_leave.append(l)
        person = PersonModel.AllFromDB(self.log.SetMessage)
        check = LeaveSigningStatusModel.AllFromDB(self.log.SetMessage)
        stream = LeaveToExcel.GetExcel(time_leave,person,check,args['start'],args['end'])
        return self.GetExcelResponse("{0}至{1}出勤".format(args['start'], args['end']), stream)
    @staticmethod
    def GetExcel(leave,person,check,start,end):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 , 'underline':True })
        cell_format_left = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_with_border = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 , 'font_color': '#FF0000'})
        now = datetime.datetime.now()
        sheet = book.add_worksheet('請假資料')
        sheet.merge_range('A1:G1', "昱通資訊事業股份有限公司", cell_format)
        sheet.merge_range('A2:G2', "員工請假資料明細表", cell_format)
        sheet.merge_range('A4:C4', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format_left)
        sheet.merge_range('A5:C5', "查詢區間：{0}-{1}".format(start, end), cell_format_left)
        sheet.write_string(7, 0, "員工編號", cell_format_line)
        sheet.write_string(7, 1, "員工姓名", cell_format_line)
        sheet.write_string(7, 2, "請假起始日期", cell_format_line)
        sheet.write_string(7, 3, "請假結束日期", cell_format_line)
        sheet.write_string(7, 4, "開始時間", cell_format_line)
        sheet.write_string(7, 5, "結束時間", cell_format_line)
        sheet.write_string(7, 6, "請假時數", cell_format_line)
        sheet.write_string(7, 7, "請假類別", cell_format_line)
        sheet.write_string(7, 8, "核准", cell_format_line)
        sheet.set_column('A:A', 13)
        sheet.set_column('B:B', 13)
        sheet.set_column('C:C', 20)
        sheet.set_column('D:D', 20)
        sheet.set_column('E:E', 13)
        sheet.set_column('F:F', 13)
        sheet.set_column('G:G', 13)
        sheet.set_column('H:H', 13)
        sheet.set_column('I:I', 13)
        row=8
        count=0
        for l in leave:
            if(l.GetCategory()!="出差"):
                name=""
                for p in person:
                    if(p.GetID()==l.GetPersonId()):
                        name=p.GetName()
                status="不通過"
                for c in check:
                    if(c.GetLeaveOffWorkNo()==l.GetNo()):
                        if(c.GetSigningStatus1()=="1" and c.GetSigningStatus2()=="1" and c.GetSigningStatus3()=="1" and c.GetSigningStatus4()=="1"):
                            status="通過"
                sheet.write_string(row, 0, l.GetPersonId(), cell_format_with_border)
                sheet.write_string(row, 1, name, cell_format_with_border)
                sheet.write_string(row, 2, l.GetStart()[0:10], cell_format_with_border)
                sheet.write_string(row, 3, l.GetEnd()[0:10], cell_format_with_border)
                sheet.write_string(row, 4, l.GetStart()[11:16], cell_format_with_border)
                sheet.write_string(row, 5, l.GetEnd()[11:16], cell_format_with_border)
                sheet.write_string(row, 6, str(l.GetLeaveTime()), cell_format_with_border)
                sheet.write_string(row, 7, l.GetCategory(), cell_format_with_border)
                sheet.write_string(row, 8, status, cell_format_with_border)
                row=row+1
                count=count+1
        sheet.write_string(row, 0, "共{0}筆".format(count), cell_format)
        writer.close()
        stream.seek(0)
        return stream
# ========== excel匯出個人假單統計 ==========
class LeaveToExcelPersonal(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('start', required=True, help="開始時間")
        self.parser.add_argument('end', required=True, help="結束時間")
        self.parser.add_argument('person_id', required=True, help="員工編號")
        self.parser.add_argument('user_id', required=True, help="使用者編號")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
        user =  UserModel.FromDB(args['user_id'], self.log.SetMessage)
        now = datetime.datetime.now()
        role = [0,7,10,14,14,15,15,15,15,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
        annual_day=0
        on_board = user.GetOnBoard()
        on_board_s = on_board.split("/")
        year = int(now.year) - int(on_board_s[0])
        month = 12 - int(on_board_s[1])
        if(year==0 and month>5):
            annual_day=3
        elif(year>24):
            annual_day=30
        else:
            annual_day=role[year]
        annual_hour=annual_day*8
#         StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
#         EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
        time_leave=[]
        for l in leave:
#             leave_start=l.GetStart()[0:10]
#             StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
#             leave_end=l.GetEnd()[0:10]
#             EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
            if(l.GetPersonId()==args['person_id']):
                time_leave.append(l)
        stream = LeaveToExcelPersonal.GetExcel(time_leave,person,args['person_id'],annual_hour,args['start'],args['end'])
        return self.GetExcelResponse("{0}請假統計".format(args['person_id']), stream)
    @staticmethod
    def GetExcel(leave,person,person_id,annual_hour,start,end):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_fg = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 ,'fg_color': '#BFE5FD'})
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 , 'underline':True })
        cell_format_left = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_with_border = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 , 'font_color': '#FF0000'})
        sheet = book.add_worksheet('請假統計')
        sheet.merge_range('A1:L1', "昱通資訊事業股份有限公司", cell_format)
        sheet.merge_range('A2:L2', "員工請假統計", cell_format)
        sheet.write_string(3, 0, "日期", cell_format_fg)
#         sheet.write_string(3, 1, "員工編號", cell_format)
#         sheet.write_string(3, 2, "員工姓名", cell_format)
        sheet.write_string(3, 1, "特休", cell_format_fg)
        sheet.write_string(3, 2, "公假", cell_format_fg)
        sheet.write_string(3, 3, "事假", cell_format_fg)
        sheet.write_string(3, 4, "病假", cell_format_fg)
        sheet.write_string(3, 5, "公傷病假", cell_format_fg)
        sheet.write_string(3, 6, "婚假", cell_format_fg)
        sheet.write_string(3, 7, "產假", cell_format_fg)
        sheet.write_string(3, 8, "陪產假", cell_format_fg)
        sheet.write_string(3, 9, "喪假", cell_format_fg)
        sheet.write_string(3, 10, "生理假", cell_format_fg)
        sheet.write_string(3, 11, "補假", cell_format_fg)
        sheet.set_column('A:A', 20)
        sheet.set_column('B:B', 13)
        sheet.set_column('C:C', 13)
        sheet.set_column('D:D', 13)
        sheet.set_column('E:E', 13)
        sheet.set_column('F:F', 13)
        sheet.set_column('G:G', 13)
        sheet.set_column('H:H', 13)
        sheet.set_column('I:I', 13)
        sheet.set_column('J:J', 13)
        sheet.set_column('K:K', 13)
        sheet.set_column('L:L', 13)
#         sheet.set_column('M:M', 13)
#         sheet.set_column('N:N', 13)
        name=""
        for p in person:
            if(p.GetID()==person_id):
                name=p.GetName()
        sheet.write_string(2, 0, "員工編號、姓名", cell_format)
        sheet.write_string(2, 1, person_id, cell_format)
        sheet.write_string(2, 2, name, cell_format)
        now = datetime.datetime.now()
        sheet.merge_range('J3:L3', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format)
        sheet.merge_range('G3:I3', "查詢區間：{0}-{1}".format(start, end), cell_format)
        row=4
        category=["特休","公假","事假","病假","公傷病假","婚假","產假","陪產假","喪假","生理假","補假"]     #假別對照(假別名稱對照陣列位置)
        category_number=[0,0,0,0,0,0,0,0,0,0,0]     #統計總數
        StartTime=datetime.datetime.strptime(start, "%Y/%m/%d")
        EndTime=datetime.datetime.strptime(end, "%Y/%m/%d")
        for l in leave:
            if(l.GetCategory()!="出差"):
                start=l.GetStart()[0:10]
                end=l.GetEnd()[0:10]
                category_location=category.index(l.GetCategory())
#                 category_number[category_location]=category_number[category_location]+float(l.GetLeaveTime())
                if(start==end):
                    ThisDay = datetime.datetime.strptime(start, "%Y/%m/%d")
                    if(StartTime<=ThisDay and ThisDay<=EndTime):
                        category_number[category_location]=category_number[category_location]+float(l.GetLeaveTime())
                        sheet.write_string(row, 0, l.GetStart()[0:10], cell_format)
                        sheet.write_string(row, category_location+1, str(l.GetLeaveTime()), cell_format)
                        row=row+1
                else:
                    s_time=l.GetStart()
                    thisday=start
                    end_day=end
                    all_hour=float(l.GetLeaveTime())
                    while(thisday!=end_day):
                        e_time=thisday+" 17:30"
                        c_time=thisday+" 12:00"
                        dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                        if(datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")<datetime.datetime.strptime(c_time, "%Y/%m/%d %H:%M")):
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600-1
                        else:
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600
                        if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                            category_number[category_location]=category_number[category_location]+this_hour
                            sheet.write_string(row, 0, thisday, cell_format)
                            sheet.write_string(row, category_location+1, str(this_hour), cell_format)
                            row=row+1
                        all_hour=all_hour-this_hour
                        t=datetime.timedelta(days=1)
                        dt_thisday=dt_thisday+t
                        if(dt_thisday.strftime('%Y/%m/%d')!=end_day):
                            while(dt_thisday.weekday()==5 or dt_thisday.weekday()==6):
                                dt_thisday=dt_thisday+t
                        thisday=dt_thisday.strftime('%Y/%m/%d')
                        s_time=thisday+" 08:30"
                    dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                    if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                        category_number[category_location]=category_number[category_location]+all_hour
                        sheet.write_string(row, 0, thisday, cell_format)
                        sheet.write_string(row, category_location+1, str(all_hour), cell_format)
                        row=row+1
        sheet.write_string(row, 0, "總計", cell_format_fg)
        sheet.write_string(row, 1, str(category_number[0])+" hr", cell_format_fg)
        sheet.write_string(row, 2, str(category_number[1])+" hr", cell_format_fg)
        sheet.write_string(row, 3, str(category_number[2])+" hr", cell_format_fg)
        sheet.write_string(row, 4, str(category_number[3])+" hr", cell_format_fg)
        sheet.write_string(row, 5, str(category_number[4])+" hr", cell_format_fg)
        sheet.write_string(row, 6, str(category_number[5])+" hr", cell_format_fg)
        sheet.write_string(row, 7, str(category_number[6])+" hr", cell_format_fg)
        sheet.write_string(row, 8, str(category_number[7])+" hr", cell_format_fg)
        sheet.write_string(row, 9, str(category_number[8])+" hr", cell_format_fg)
        sheet.write_string(row, 10, str(category_number[9])+" hr", cell_format_fg)
        sheet.write_string(row, 11, str(category_number[10])+" hr", cell_format_fg)
        sheet.write_string(row+1, 0, "剩於特休", cell_format_fg)
        sheet.write_string(row+1, 1, str(annual_hour-category_number[0])+" hr", cell_format_fg)
        writer.close()
        stream.seek(0)
        return stream

# ========== excel匯出全部假單統計 ==========
class LeaveToExcelAll(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('start', required=True, help="開始時間")
        self.parser.add_argument('end', required=True, help="結束時間")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
#         StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
#         EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
#         time_leave=[]
#         for l in leave:
#             leave_start=l.GetStart()[0:10]
#             StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
#             leave_end=l.GetEnd()[0:10]
#             EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
#             if(l.GetPersonId()==args['person_id']):
#                 time_leave.append(l)
        stream = LeaveToExcelAll.GetExcel(leave,person,args['start'],args['end'])
        return self.GetExcelResponse("{0}-{1}請假統計".format(args['start'],args['end']), stream)
    @staticmethod
    def GetExcel(leave,person,start,end):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12 })
        cell_format_fg = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12 ,'fg_color': '#BFE5FD', 'border': 2 })
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12 , 'underline':True })
        cell_format_left = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 12 })
        cell_format_with_border = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12, 'border': 1 })
        cell_format_with_border_2 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12, 'border': 2 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12, 'border': 1 , 'font_color': '#FF0000'})
        now = datetime.datetime.now()
        sheet = book.add_worksheet('請假統計')
        sheet.merge_range('A1:O1', "昱通資訊事業股份有限公司", cell_format)
        sheet.merge_range('A2:O2', "員工請假統計", cell_format)
#         sheet.write_string(3, 0, "日期", cell_format_fg)
        sheet.write_string(3, 0, "員工編號", cell_format_fg)
        sheet.write_string(3, 1, "員工姓名", cell_format_fg)
        sheet.write_string(3, 2, "事假", cell_format_fg)
        sheet.write_string(3, 3, "病假", cell_format_fg)
        sheet.write_string(3, 4, "喪假", cell_format_fg)
        sheet.write_string(3, 5, "公假", cell_format_fg)
        sheet.write_string(3, 6, "特休", cell_format_fg)
        sheet.write_string(3, 7, "公傷病假", cell_format_fg)
        sheet.write_string(3, 8, "婚假", cell_format_fg)
        sheet.write_string(3, 9, "產假", cell_format_fg)
        sheet.write_string(3, 10, "陪產假", cell_format_fg)
        sheet.write_string(3, 11, "生理假", cell_format_fg)
        sheet.write_string(3, 12, "補假", cell_format_fg)
        sheet.write_string(3, 13, "遲到", cell_format_fg)
        sheet.write_string(3, 14, "備註", cell_format_fg)
        sheet.merge_range('K3:M3', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format)
        sheet.merge_range('G3:J3', "查詢區間：{0}-{1}".format(start, end), cell_format)
        sheet.set_column('A:A', 10)
        sheet.set_column('B:B', 10)
        sheet.set_column('C:C', 10)
        sheet.set_column('D:D', 10)
        sheet.set_column('E:E', 10)
        sheet.set_column('F:F', 10)
        sheet.set_column('G:G', 10)
        sheet.set_column('H:H', 10)
        sheet.set_column('I:I', 10)
        sheet.set_column('J:J', 10)
        sheet.set_column('K:K', 10)
        sheet.set_column('L:L', 10)
        sheet.set_column('M:M', 10)
        sheet.set_column('N:N', 10)
        sheet.set_column('O:O', 10)
#         sheet.conditional_format('A4:M4',{'type':'cell','criteria': '>=','value':5,'format':cell_format_with_border_2)
#         sheet.set_column('N:N', 13)
        row=4
        all_leave={}
        for p in person:
            all_leave[p.GetID()]={'名字':p.GetName(),'特休':0,'公假':0,'事假':0,'病假':0,'公傷病假':0,'婚假':0,'產假':0,'陪產假':0,'產檢假':0,'喪假':0,'生理假':0,'補假':0}
        StartTime=datetime.datetime.strptime(start, "%Y/%m/%d")
        EndTime=datetime.datetime.strptime(end, "%Y/%m/%d")
        for l in leave:
            if(l.GetCategory()!='出差'):
                l_start=l.GetStart()[0:10]
                l_end=l.GetEnd()[0:10]
                if(l_start==l_end):
                    ThisDay = datetime.datetime.strptime(l_start, "%Y/%m/%d")
                    if(StartTime<=ThisDay and ThisDay<=EndTime):
                        all_leave[l.GetPersonId()][l.GetCategory()]+=float(l.GetLeaveTime())
                else:
                    s_time=l.GetStart()
                    thisday=l_start
                    end_day=l_end
                    all_hour=float(l.GetLeaveTime())
                    while(thisday!=end_day):
                        e_time=thisday+" 17:30"
                        c_time=thisday+" 12:00"
                        dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                        if(datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")<datetime.datetime.strptime(c_time, "%Y/%m/%d %H:%M")):
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600-1
                        else:
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600
                        if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                            all_leave[l.GetPersonId()][l.GetCategory()]+=this_hour
                        all_hour=all_hour-this_hour
                        t=datetime.timedelta(days=1)
                        dt_thisday=dt_thisday+t
                        if(dt_thisday.strftime('%Y/%m/%d')!=end_day):
                            while(dt_thisday.weekday()==5 or dt_thisday.weekday()==6):
                                dt_thisday=dt_thisday+t
                        thisday=dt_thisday.strftime('%Y/%m/%d')
                        s_time=thisday+" 08:30"
                    dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                    if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                        all_leave[l.GetPersonId()][l.GetCategory()]+=all_hour
        start_time=start+" 00:00:00"
        end_time=end+" 23:59:59"
        start_dt = datetime.datetime.strptime(start_time, '%Y/%m/%d %H:%M:%S')
        end_dt = datetime.datetime.strptime(end_time, '%Y/%m/%d %H:%M:%S')
        inHour=8
        inMinute=30
        outHour=17
        outMinute=30
        lunch=60
        for p in person:
            data = PunchCardRecord.GetFullTable(start_dt, end_dt, inHour, inMinute, outHour, outMinute, lunch, person, p.GetID())
            thisday=start_dt.strftime('%Y-%m-%d')
            end_day=end_dt.strftime('%Y-%m-%d')
            Late=0
            late_note=""
            while(thisday!=end_day):
                LateType=0
                dt_thisday=datetime.datetime.strptime(thisday, "%Y-%m-%d")
                if(data[p.GetID()][thisday]['WorkBeLate']!=0):
                    for l in leave:
                        if(l.GetPersonId()==p.GetID()):
                            l_start=datetime.datetime.strptime(l.GetStart()[0:10], "%Y/%m/%d")
                            l_end=datetime.datetime.strptime(l.GetEnd()[0:10], "%Y/%m/%d")
                            if (l_start<=dt_thisday and dt_thisday<=l_end):
                                LateType=1
                    if(LateType==0):
                        late_note=late_note+thisday+","
                        Late=Late+1
                t=datetime.timedelta(days=1)
                dt_thisday=dt_thisday+t
                thisday=dt_thisday.strftime('%Y-%m-%d')
            if(data[p.GetID()][end_day]['WorkBeLate']!=0):
                Late=Late+1
                late_note=late_note+end_day+","
            if(all_leave[p.GetID()]['特休']==0):
                all_leave[p.GetID()]['特休']=""
            if(all_leave[p.GetID()]['公假']==0):
                all_leave[p.GetID()]['公假']=""
            if(all_leave[p.GetID()]['事假']==0):
                all_leave[p.GetID()]['事假']=""
            if(all_leave[p.GetID()]['病假']==0):
                all_leave[p.GetID()]['病假']=""
            if(all_leave[p.GetID()]['公傷病假']==0):
                all_leave[p.GetID()]['公傷病假']=""
            if(all_leave[p.GetID()]['婚假']==0):
                all_leave[p.GetID()]['婚假']=""
            if(all_leave[p.GetID()]['產假']==0):
                all_leave[p.GetID()]['產假']=""
            if(all_leave[p.GetID()]['陪產假']==0):
                all_leave[p.GetID()]['陪產假']=""
            if(all_leave[p.GetID()]['喪假']==0):
                all_leave[p.GetID()]['喪假']=""
            if(all_leave[p.GetID()]['生理假']==0):
                all_leave[p.GetID()]['生理假']=""
            if(all_leave[p.GetID()]['補假']==0):
                all_leave[p.GetID()]['補假']=""
            sheet.write_string(row, 0, p.GetID(), cell_format_with_border)
            sheet.write_string(row, 1, all_leave[p.GetID()]['名字'], cell_format_with_border)
            sheet.write_string(row, 2, str(all_leave[p.GetID()]['事假']), cell_format_with_border)
            sheet.write_string(row, 3, str(all_leave[p.GetID()]['病假']), cell_format_with_border)
            sheet.write_string(row, 4, str(all_leave[p.GetID()]['喪假']), cell_format_with_border)
            sheet.write_string(row, 5, str(all_leave[p.GetID()]['公假']), cell_format_with_border)
            sheet.write_string(row, 6, str(all_leave[p.GetID()]['特休']), cell_format_with_border)
            sheet.write_string(row, 7, str(all_leave[p.GetID()]['公傷病假']), cell_format_with_border)
            sheet.write_string(row, 8, str(all_leave[p.GetID()]['婚假']), cell_format_with_border)
            sheet.write_string(row, 9, str(all_leave[p.GetID()]['產假']), cell_format_with_border)
            sheet.write_string(row, 10, str(all_leave[p.GetID()]['陪產假']), cell_format_with_border)
            sheet.write_string(row, 11, str(all_leave[p.GetID()]['生理假']), cell_format_with_border)
            sheet.write_string(row, 12, str(all_leave[p.GetID()]['補假']), cell_format_with_border)
            sheet.write_string(row, 13, str(Late), cell_format_with_border)
            sheet.write_string(row, 14, late_note, cell_format_with_border)
            row+=1
        writer.close()
        stream.seek(0)
        return stream

# ========== excel統計特休 ==========
class AnnualLeaveToExcel(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('year', required=True, help="年份(西元)")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
#         StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
#         EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
#         time_leave=[]
#         for l in leave:
#             leave_start=l.GetStart()[0:10]
#             StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
#             leave_end=l.GetEnd()[0:10]
#             EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
#             if(l.GetPersonId()==args['person_id']):
#                 time_leave.append(l)
        stream = AnnualLeaveToExcel.GetExcel(self,leave,person,args['year'])
        return self.GetExcelResponse("{0}年特休統計".format(args['year']), stream)
    @staticmethod
    def GetExcel(self,leave,person,year):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_fg = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 ,'fg_color': '#BFE5FD', 'border': 2 })
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 , 'underline':True })
        cell_format_with_border_center = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1  })
        cell_format_with_border = book.add_format({ 'align': 'right', 'valign': 'vcenter', 'font_size': 14, 'border': 1 })
        cell_format_with_border_2 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 2 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 , 'font_color': '#FF0000'})
        now = datetime.datetime.now()
        sheet = book.add_worksheet('請假統計')
        sheet.merge_range('A1:H1', "昱通資訊事業股份有限公司", cell_format)
        sheet.merge_range('A2:H2', "員工特休統計", cell_format)
#         sheet.write_string(3, 0, "日期", cell_format_fg)
        sheet.write_string(3, 0, "員工編號", cell_format_fg)
        sheet.write_string(3, 1, "員工姓名", cell_format_fg)
        sheet.write_string(3, 2, "特休天數", cell_format_fg)
        sheet.write_string(3, 3, "第一季", cell_format_fg)
        sheet.write_string(3, 4, "第二季", cell_format_fg)
        sheet.write_string(3, 5, "第三季", cell_format_fg)
        sheet.write_string(3, 6, "第四季", cell_format_fg)
        sheet.write_string(3, 7, "剩餘特休", cell_format_fg)
        sheet.merge_range('F3:H3', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format)
        sheet.merge_range('D3:E3', "查詢年份：{0}".format(year), cell_format)
        sheet.set_column('A:A', 13)
        sheet.set_column('B:B', 13)
        sheet.set_column('C:C', 13)
        sheet.set_column('D:D', 17)
        sheet.set_column('E:E', 17)
        sheet.set_column('F:F', 17)
        sheet.set_column('G:G', 17)
        sheet.set_column('H:H', 17)
        sheet.set_row(1, 24)
        sheet.set_row(2, 24)
        sheet.set_row(3, 24)
        sheet.set_row(4, 24)
        row=4
        all_leave={}
        role = [0,7,10,14,14,15,15,15,15,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
        for p in person:
            user =  UserModel.FromDB(p.GetID(), self.log.SetMessage)
            if user.isExist:
                annual_day=0
                on_board = user.GetOnBoard()
                on_board_s = on_board.split("/")
                s_year = int(year) - int(on_board_s[0])
                s_month = 12 - int(on_board_s[1])
                if(s_year==0 and s_month>5):
                    annual_day=3
                elif(s_year>24):
                    annual_day=30
                else:
                    annual_day=role[s_year]
                annual_hour=annual_day*8
                all_leave[p.GetID()]={'名字':p.GetName(),'特休天數':annual_day,'特休小時數':annual_hour,'第一季':0,'第二季':0,'第三季':0,'第四季':0,'剩餘':annual_hour}
            else:
                all_leave[p.GetID()]={'名字':p.GetName(),'特休天數':0,'特休小時數':0,'第一季':0,'第二季':0,'第三季':0,'第四季':0,'剩餘':0}
        Q1_start=datetime.datetime.strptime(year+"/01/01", "%Y/%m/%d")
        Q1_end=datetime.datetime.strptime(year+"/03/31", "%Y/%m/%d")
        Q2_start=datetime.datetime.strptime(year+"/04/01", "%Y/%m/%d")
        Q2_end=datetime.datetime.strptime(year+"/06/30", "%Y/%m/%d")
        Q3_start=datetime.datetime.strptime(year+"/07/01", "%Y/%m/%d")
        Q3_end=datetime.datetime.strptime(year+"/09/30", "%Y/%m/%d")
        Q4_start=datetime.datetime.strptime(year+"/10/01", "%Y/%m/%d")
        Q4_end=datetime.datetime.strptime(year+"/12/31", "%Y/%m/%d")
        for l in leave:
            if(l.GetCategory()=='特休'):
                l_start=l.GetStart()[0:10]
                l_end=l.GetEnd()[0:10]
                if(l_start==l_end):
                    ThisDay = datetime.datetime.strptime(l_start, "%Y/%m/%d")
                    if(Q1_start<=ThisDay and ThisDay<=Q1_end):
                        all_leave[l.GetPersonId()]['第一季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                    if(Q2_start<=ThisDay and ThisDay<=Q2_end):
                        all_leave[l.GetPersonId()]['第二季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                    if(Q3_start<=ThisDay and ThisDay<=Q3_end):
                        all_leave[l.GetPersonId()]['第三季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                    if(Q4_start<=ThisDay and ThisDay<=Q4_end):
                        all_leave[l.GetPersonId()]['第四季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                else:
                    s_time=l.GetStart()
                    thisday=l_start
                    end_day=l_end
                    all_hour=float(l.GetLeaveTime())
                    while(thisday!=end_day):
                        e_time=thisday+" 17:30"
                        c_time=thisday+" 12:00"
                        dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                        if(datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")<datetime.datetime.strptime(c_time, "%Y/%m/%d %H:%M")):
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600-1
                        else:
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600
                        if(Q1_start<=dt_thisday and dt_thisday<=Q1_end):
                            all_leave[l.GetPersonId()]['第一季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        if(Q2_start<=dt_thisday and dt_thisday<=Q2_end):
                            all_leave[l.GetPersonId()]['第二季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        if(Q3_start<=dt_thisday and dt_thisday<=Q3_end):
                            all_leave[l.GetPersonId()]['第三季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        if(Q4_start<=dt_thisday and dt_thisday<=Q4_end):
                            all_leave[l.GetPersonId()]['第四季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        all_hour=all_hour-this_hour
                        t=datetime.timedelta(days=1)
                        dt_thisday=dt_thisday+t
                        if(dt_thisday.strftime('%Y/%m/%d')!=end_day):
                            while(dt_thisday.weekday()==5 or dt_thisday.weekday()==6):
                                dt_thisday=dt_thisday+t
                        thisday=dt_thisday.strftime('%Y/%m/%d')
                        s_time=thisday+" 08:30"
                    dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                    if(Q1_start<=dt_thisday and dt_thisday<=Q1_end):
                        all_leave[l.GetPersonId()]['第一季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
                    if(Q2_start<=dt_thisday and dt_thisday<=Q2_end):
                        all_leave[l.GetPersonId()]['第二季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
                    if(Q3_start<=dt_thisday and dt_thisday<=Q3_end):
                        all_leave[l.GetPersonId()]['第三季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
                    if(Q4_start<=dt_thisday and dt_thisday<=Q4_end):
                        all_leave[l.GetPersonId()]['第四季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
        for p in person:
            Q1_change=str(int(all_leave[p.GetID()]['第一季']//8))+"天"+str(float(all_leave[p.GetID()]['第一季']%8))+"小時"
            Q2_change=str(int(all_leave[p.GetID()]['第二季']//8))+"天"+str(float(all_leave[p.GetID()]['第二季']%8))+"小時"
            Q3_change=str(int(all_leave[p.GetID()]['第三季']//8))+"天"+str(float(all_leave[p.GetID()]['第三季']%8))+"小時"
            Q4_change=str(int(all_leave[p.GetID()]['第四季']//8))+"天"+str(float(all_leave[p.GetID()]['第四季']%8))+"小時"
            Last_change=str(int(all_leave[p.GetID()]['剩餘']//8))+"天"+str(float(all_leave[p.GetID()]['剩餘']%8))+"小時"
            sheet.write_string(row, 0, p.GetID(), cell_format_with_border_center)
            sheet.write_string(row, 1, all_leave[p.GetID()]['名字'], cell_format_with_border_center)
            sheet.write_string(row, 2, str(all_leave[p.GetID()]['特休天數']), cell_format_with_border_center)
            sheet.write_string(row, 3, Q1_change, cell_format_with_border)
            sheet.write_string(row, 4, Q2_change, cell_format_with_border)
            sheet.write_string(row, 5, Q3_change, cell_format_with_border)
            sheet.write_string(row, 6, Q4_change, cell_format_with_border)
            sheet.write_string(row, 7, Last_change, cell_format_with_border)
            sheet.set_row(row, 24)
            row+=1
        writer.close()
        stream.seek(0)
        return stream

    
# ========== *新版本-word匯出假單(吉鑫) ==========
class LeaveToWordVerson2(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('leave_id', required=True, help="假單編號")
        self.parser.add_argument('person_id', required=True, help="員工編號")
        self.parser.add_argument('person_name', required=True, help="員工姓名")
        self.parser.add_argument('department', required=True, help="部門名稱")
        self.parser.add_argument('category', required=True, help="請假類別")
        self.parser.add_argument('substitute', required=True, help="代理人")
        self.parser.add_argument('start_date', required=True, help="開始日期")
        self.parser.add_argument('end_date', required=True, help="結束日期")
        self.parser.add_argument('start_time', required=True, help="開始時間")
        self.parser.add_argument('end_time', required=True, help="結束時間")
        self.parser.add_argument('hour', required=True, help="請假時數")
        self.parser.add_argument('reason', required=True, help="請假原因")
    def post(self):
        args = self.parser.parse_args()
        path1=os.path.abspath('./')
        doc = Document('resources/test/員工請假單_吉鑫.docx')
        now = datetime.datetime.now()
        doc.paragraphs[2].text='請假單編號:{0}                                   列印日期:{1}年 {2}月 {3}日'.format(args['leave_id'],now.year,now.month,now.day)
        tables = doc.tables
        table = tables[0]
        table.cell(0,1).text=args['person_id']
        table.cell(1,1).text=args['person_name']
        table.cell(2,1).text=args['department']
        table.cell(3,1).text=args['category']
        table.cell(3,3).text=args['substitute']
        table.cell(4,1).text=args['start_date']
        table.cell(4,3).text=args['start_time']
        table.cell(5,1).text=args['end_date']
        table.cell(5,3).text=args['end_time']
        table.cell(6,1).text=args['hour']
        table.cell(7,1).text=args['reason']
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        for l in leave:
            if(l.GetNo()==args['leave_id'] and l.GetUploadFile()!=None):
                doc.add_picture(r'{1}/static/leave_off_work/{0}.jpg'.format(args['leave_id'],path1))
        stream = BytesIO()
        doc.save(stream)
        stream.seek(0)
        #doc.save('/home/shuochen/Documents/punch_card_api/flask/notebook/resources/test/申請書{0}.docx'.format(args['name']))
        return self.GetWordResponse("請假單{0}".format(args['leave_id']), stream)
    
# ========== *新版本-excel匯出區間假單 ==========
class LeaveToExcelVerson2(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('start', required=True, help="開始時間")
        self.parser.add_argument('end', required=True, help="結束時間")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        time_leave=[]
        StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
        EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
        for l in leave:
            leave_start=l.GetStart()[0:10]
            StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
            leave_end=l.GetEnd()[0:10]
            EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
            if(StartTime<=StartDay and EndTime>=EndDay):
                time_leave.append(l)
        person = PersonModel.AllFromDB(self.log.SetMessage)
        check = LeaveSigningStatusModel.AllFromDB(self.log.SetMessage)
        stream = LeaveToExcelVerson2.GetExcel(time_leave,person,check,args['start'],args['end'])
        return self.GetExcelResponse("{0}至{1}出勤".format(args['start'], args['end']), stream)
    @staticmethod
    def GetExcel(leave,person,check,start,end):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 , 'underline':True })
        cell_format_left = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_with_border = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 , 'font_color': '#FF0000'})
        now = datetime.datetime.now()
        sheet = book.add_worksheet('請假資料')
        sheet.merge_range('A1:I1', "吉鑫營造股份有限公司", cell_format)
        sheet.merge_range('A2:I2', "員工請假資料明細表", cell_format)
        sheet.merge_range('A4:C4', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format_left)
        sheet.merge_range('A5:C5', "查詢區間：{0}-{1}".format(start, end), cell_format_left)
        sheet.write_string(7, 0, "員工編號", cell_format_line)
        sheet.write_string(7, 1, "員工姓名", cell_format_line)
        sheet.write_string(7, 2, "請假起始日期", cell_format_line)
        sheet.write_string(7, 3, "請假結束日期", cell_format_line)
        sheet.write_string(7, 4, "開始時間", cell_format_line)
        sheet.write_string(7, 5, "結束時間", cell_format_line)
        sheet.write_string(7, 6, "請假時數", cell_format_line)
        sheet.write_string(7, 7, "請假類別", cell_format_line)
        sheet.write_string(7, 8, "核准", cell_format_line)
        sheet.set_column('A:A', 13)
        sheet.set_column('B:B', 13)
        sheet.set_column('C:C', 20)
        sheet.set_column('D:D', 20)
        sheet.set_column('E:E', 13)
        sheet.set_column('F:F', 13)
        sheet.set_column('G:G', 13)
        sheet.set_column('H:H', 13)
        sheet.set_column('I:I', 13)
        row=8
        count=0
        for l in leave:
            if(l.GetCategory()!="出差"):
                name=""
                for p in person:
                    if(p.GetID()==l.GetPersonId()):
                        name=p.GetName()
                status="不通過"
                for c in check:
                    if(c.GetLeaveOffWorkNo()==l.GetNo()):
                        if(c.GetSigningStatus1()=="1" and c.GetSigningStatus2()=="1" and c.GetSigningStatus3()=="1" and c.GetSigningStatus4()=="1"):
                            status="通過"
                sheet.write_string(row, 0, l.GetPersonId(), cell_format_with_border)
                sheet.write_string(row, 1, name, cell_format_with_border)
                sheet.write_string(row, 2, l.GetStart()[0:10], cell_format_with_border)
                sheet.write_string(row, 3, l.GetEnd()[0:10], cell_format_with_border)
                sheet.write_string(row, 4, l.GetStart()[11:16], cell_format_with_border)
                sheet.write_string(row, 5, l.GetEnd()[11:16], cell_format_with_border)
                sheet.write_string(row, 6, str(l.GetLeaveTime()), cell_format_with_border)
                sheet.write_string(row, 7, l.GetCategory(), cell_format_with_border)
                sheet.write_string(row, 8, status, cell_format_with_border)
                row=row+1
                count=count+1
        sheet.write_string(row, 0, "共{0}筆".format(count), cell_format)
        writer.close()
        stream.seek(0)
        return stream
# ========== *新版本-excel匯出個人假單統計 ==========
class LeaveToExcelPersonalVerson2(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('start', required=True, help="開始時間")
        self.parser.add_argument('end', required=True, help="結束時間")
        self.parser.add_argument('person_id', required=True, help="員工編號")
        self.parser.add_argument('user_id', required=True, help="使用者編號")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
        user =  UserModel.FromDB(args['user_id'], self.log.SetMessage)
        now = datetime.datetime.now()
        role = [0,7,10,14,14,15,15,15,15,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
        annual_day=0
        on_board = user.GetOnBoard()
        on_board_s = on_board.split("/")
        year = int(now.year) - int(on_board_s[0])
        month = 12 - int(on_board_s[1])
        if(year==0 and month>5):
            annual_day=3
        elif(year>24):
            annual_day=30
        else:
            annual_day=role[year]
        annual_hour=annual_day*8
#         StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
#         EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
        time_leave=[]
        for l in leave:
#             leave_start=l.GetStart()[0:10]
#             StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
#             leave_end=l.GetEnd()[0:10]
#             EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
            if(l.GetPersonId()==args['person_id']):
                time_leave.append(l)
        stream = LeaveToExcelPersonalVerson2.GetExcel(time_leave,person,args['person_id'],annual_hour,args['start'],args['end'])
        return self.GetExcelResponse("{0}請假統計".format(args['person_id']), stream)
    @staticmethod
    def GetExcel(leave,person,person_id,annual_hour,start,end):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_fg = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 ,'fg_color': '#BFE5FD'})
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 , 'underline':True })
        cell_format_left = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_with_border = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 , 'font_color': '#FF0000'})
        sheet = book.add_worksheet('請假統計')
        sheet.merge_range('A1:L1', "吉鑫營造股份有限公司", cell_format)
        sheet.merge_range('A2:L2', "員工請假統計", cell_format)
        sheet.write_string(3, 0, "日期", cell_format_fg)
#         sheet.write_string(3, 1, "員工編號", cell_format)
#         sheet.write_string(3, 2, "員工姓名", cell_format)
        sheet.write_string(3, 1, "特休", cell_format_fg)
        sheet.write_string(3, 2, "公假", cell_format_fg)
        sheet.write_string(3, 3, "事假", cell_format_fg)
        sheet.write_string(3, 4, "病假", cell_format_fg)
        sheet.write_string(3, 5, "公傷病假", cell_format_fg)
        sheet.write_string(3, 6, "婚假", cell_format_fg)
        sheet.write_string(3, 7, "產假", cell_format_fg)
        sheet.write_string(3, 8, "陪產假", cell_format_fg)
        sheet.write_string(3, 9, "喪假", cell_format_fg)
        sheet.write_string(3, 10, "生理假", cell_format_fg)
        sheet.write_string(3, 11, "補假", cell_format_fg)
        sheet.set_column('A:A', 20)
        sheet.set_column('B:B', 13)
        sheet.set_column('C:C', 13)
        sheet.set_column('D:D', 13)
        sheet.set_column('E:E', 13)
        sheet.set_column('F:F', 13)
        sheet.set_column('G:G', 13)
        sheet.set_column('H:H', 13)
        sheet.set_column('I:I', 13)
        sheet.set_column('J:J', 13)
        sheet.set_column('K:K', 13)
        sheet.set_column('L:L', 13)
#         sheet.set_column('M:M', 13)
#         sheet.set_column('N:N', 13)
        name=""
        for p in person:
            if(p.GetID()==person_id):
                name=p.GetName()
        sheet.write_string(2, 0, "員工編號、姓名", cell_format)
        sheet.write_string(2, 1, person_id, cell_format)
        sheet.write_string(2, 2, name, cell_format)
        now = datetime.datetime.now()
        sheet.merge_range('J3:L3', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format)
        sheet.merge_range('G3:I3', "查詢區間：{0}-{1}".format(start, end), cell_format)
        row=4
        category=["特休","公假","事假","病假","公傷病假","婚假","產假","陪產假","喪假","生理假","補假"]     #假別對照(假別名稱對照陣列位置)
        category_number=[0,0,0,0,0,0,0,0,0,0,0]     #統計總數
        StartTime=datetime.datetime.strptime(start, "%Y/%m/%d")
        EndTime=datetime.datetime.strptime(end, "%Y/%m/%d")
        for l in leave:
            if(l.GetCategory()!="出差"):
                start=l.GetStart()[0:10]
                end=l.GetEnd()[0:10]
                category_location=category.index(l.GetCategory())
#                 category_number[category_location]=category_number[category_location]+float(l.GetLeaveTime())
                if(start==end):
                    ThisDay = datetime.datetime.strptime(start, "%Y/%m/%d")
                    if(StartTime<=ThisDay and ThisDay<=EndTime):
                        category_number[category_location]=category_number[category_location]+float(l.GetLeaveTime())
                        sheet.write_string(row, 0, l.GetStart()[0:10], cell_format)
                        sheet.write_string(row, category_location+1, str(l.GetLeaveTime()), cell_format)
                        row=row+1
                else:
                    s_time=l.GetStart()
                    thisday=start
                    end_day=end
                    all_hour=float(l.GetLeaveTime())
                    while(thisday!=end_day):
                        e_time=thisday+" 18:00"
                        c_time=thisday+" 12:00"
                        dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                        if(datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")<datetime.datetime.strptime(c_time, "%Y/%m/%d %H:%M")):
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600-1.5
                        else:
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600
                        if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                            category_number[category_location]=category_number[category_location]+this_hour
                            sheet.write_string(row, 0, thisday, cell_format)
                            sheet.write_string(row, category_location+1, str(this_hour), cell_format)
                            row=row+1
                        all_hour=all_hour-this_hour
                        t=datetime.timedelta(days=1)
                        dt_thisday=dt_thisday+t
                        if(dt_thisday.strftime('%Y/%m/%d')!=end_day):
                            while(dt_thisday.weekday()==5 or dt_thisday.weekday()==6):
                                dt_thisday=dt_thisday+t
                        thisday=dt_thisday.strftime('%Y/%m/%d')
                        s_time=thisday+" 08:30"
                    dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                    if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                        category_number[category_location]=category_number[category_location]+all_hour
                        sheet.write_string(row, 0, thisday, cell_format)
                        sheet.write_string(row, category_location+1, str(all_hour), cell_format)
                        row=row+1
        sheet.write_string(row, 0, "總計", cell_format_fg)
        sheet.write_string(row, 1, str(category_number[0])+" hr", cell_format_fg)
        sheet.write_string(row, 2, str(category_number[1])+" hr", cell_format_fg)
        sheet.write_string(row, 3, str(category_number[2])+" hr", cell_format_fg)
        sheet.write_string(row, 4, str(category_number[3])+" hr", cell_format_fg)
        sheet.write_string(row, 5, str(category_number[4])+" hr", cell_format_fg)
        sheet.write_string(row, 6, str(category_number[5])+" hr", cell_format_fg)
        sheet.write_string(row, 7, str(category_number[6])+" hr", cell_format_fg)
        sheet.write_string(row, 8, str(category_number[7])+" hr", cell_format_fg)
        sheet.write_string(row, 9, str(category_number[8])+" hr", cell_format_fg)
        sheet.write_string(row, 10, str(category_number[9])+" hr", cell_format_fg)
        sheet.write_string(row, 11, str(category_number[10])+" hr", cell_format_fg)
        sheet.write_string(row+1, 0, "剩於特休", cell_format_fg)
        sheet.write_string(row+1, 1, str(annual_hour-category_number[0])+" hr", cell_format_fg)
        writer.close()
        stream.seek(0)
        return stream
# ========== *新版本-excel匯出全部假單統計 ==========
class LeaveToExcelAllVerson2(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('start', required=True, help="開始時間")
        self.parser.add_argument('end', required=True, help="結束時間")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
        stream = LeaveToExcelAllVerson2.GetExcel(leave,person,args['start'],args['end'])
        return self.GetExcelResponse("{0}-{1}請假統計".format(args['start'],args['end']), stream)
    @staticmethod
    def GetExcel(leave,person,start,end):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12 })
        cell_format_fg = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12 ,'fg_color': '#BFE5FD', 'border': 2 })
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12 , 'underline':True })
        cell_format_left = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 12 })
        cell_format_with_border = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12, 'border': 1 })
        cell_format_with_border_2 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12, 'border': 2 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 12, 'border': 1 , 'font_color': '#FF0000'})
        now = datetime.datetime.now()
        sheet = book.add_worksheet('請假統計')
        sheet.merge_range('A1:O1', "吉鑫營造股份有限公司", cell_format)
        sheet.merge_range('A2:O2', "員工請假統計", cell_format)
#         sheet.write_string(3, 0, "日期", cell_format_fg)
        sheet.write_string(3, 0, "員工編號", cell_format_fg)
        sheet.write_string(3, 1, "員工姓名", cell_format_fg)
        sheet.write_string(3, 2, "事假", cell_format_fg)
        sheet.write_string(3, 3, "病假", cell_format_fg)
        sheet.write_string(3, 4, "喪假", cell_format_fg)
        sheet.write_string(3, 5, "公假", cell_format_fg)
        sheet.write_string(3, 6, "特休", cell_format_fg)
        sheet.write_string(3, 7, "公傷病假", cell_format_fg)
        sheet.write_string(3, 8, "婚假", cell_format_fg)
        sheet.write_string(3, 9, "產假", cell_format_fg)
        sheet.write_string(3, 10, "陪產假", cell_format_fg)
        sheet.write_string(3, 11, "生理假", cell_format_fg)
        sheet.write_string(3, 12, "補假", cell_format_fg)
        sheet.write_string(3, 13, "遲到(上午)", cell_format_fg)
        sheet.write_string(3, 14, "遲到(下午)", cell_format_fg)
        sheet.write_string(3, 15, "備註", cell_format_fg)
        sheet.merge_range('K3:M3', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format)
        sheet.merge_range('N3:P3', "查詢區間：{0}-{1}".format(start, end), cell_format)
        sheet.set_column('A:A', 10)
        sheet.set_column('B:B', 10)
        sheet.set_column('C:C', 10)
        sheet.set_column('D:D', 10)
        sheet.set_column('E:E', 10)
        sheet.set_column('F:F', 10)
        sheet.set_column('G:G', 10)
        sheet.set_column('H:H', 10)
        sheet.set_column('I:I', 10)
        sheet.set_column('J:J', 10)
        sheet.set_column('K:K', 10)
        sheet.set_column('L:L', 10)
        sheet.set_column('M:M', 10)
        sheet.set_column('N:N', 13)
        sheet.set_column('O:O', 13)
        sheet.set_column('P:P', 10)
#         sheet.conditional_format('A4:M4',{'type':'cell','criteria': '>=','value':5,'format':cell_format_with_border_2)
#         sheet.set_column('N:N', 13)
        row=4
        all_leave={}
        for p in person:
            all_leave[p.GetID()]={'名字':p.GetName(),'特休':0,'公假':0,'事假':0,'病假':0,'公傷病假':0,'婚假':0,'產假':0,'陪產假':0,'產檢假':0,'喪假':0,'生理假':0,'補假':0}
        StartTime=datetime.datetime.strptime(start, "%Y/%m/%d")
        EndTime=datetime.datetime.strptime(end, "%Y/%m/%d")
        for l in leave:
            if(l.GetCategory()!='出差'):
                l_start=l.GetStart()[0:10]
                l_end=l.GetEnd()[0:10]
                if(l_start==l_end):
                    ThisDay = datetime.datetime.strptime(l_start, "%Y/%m/%d")
                    if(StartTime<=ThisDay and ThisDay<=EndTime):
                        all_leave[l.GetPersonId()][l.GetCategory()]+=float(l.GetLeaveTime())
                else:
                    s_time=l.GetStart()
                    thisday=l_start
                    end_day=l_end
                    all_hour=float(l.GetLeaveTime())
                    while(thisday!=end_day):
                        e_time=thisday+" 18:00"
                        c_time=thisday+" 12:00"
                        dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                        if(datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")<datetime.datetime.strptime(c_time, "%Y/%m/%d %H:%M")):
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600-1.5
                        else:
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600
                        if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                            all_leave[l.GetPersonId()][l.GetCategory()]+=this_hour
                        all_hour=all_hour-this_hour
                        t=datetime.timedelta(days=1)
                        dt_thisday=dt_thisday+t
                        if(dt_thisday.strftime('%Y/%m/%d')!=end_day):
                            while(dt_thisday.weekday()==5 or dt_thisday.weekday()==6):
                                dt_thisday=dt_thisday+t
                        thisday=dt_thisday.strftime('%Y/%m/%d')
                        s_time=thisday+" 08:30"
                    dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                    if(StartTime<=dt_thisday and dt_thisday<=EndTime):
                        all_leave[l.GetPersonId()][l.GetCategory()]+=all_hour
        start_time=start+" 00:00:00"
        end_time=end+" 23:59:59"
        start_dt = datetime.datetime.strptime(start_time, '%Y/%m/%d %H:%M:%S')
        end_dt = datetime.datetime.strptime(end_time, '%Y/%m/%d %H:%M:%S')
        inHour=8
        inMinute=30
        inHour2=13
        inMinute2=30
        outHour=18
        outMinute=0
        lunch=90
        for p in person:
            data = PunchCardRecordVerson2.GetFullTable(start_dt, end_dt, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunch, person, p.GetID())
            thisday=start_dt.strftime('%Y-%m-%d')
            end_day=end_dt.strftime('%Y-%m-%d')
            Late=0
            Late_2=0
            late_note=""
            while(thisday!=end_day):
                LateType=0
                Late_t1=0
                Late_t2=0
                dt_thisday=datetime.datetime.strptime(thisday, "%Y-%m-%d")
                if(data[p.GetID()][thisday]['WorkBeLate_2']!=0):
                    Late_t2=1
                if(data[p.GetID()][thisday]['WorkBeLate']!=0):
                    Late_t1=1
                if(Late_t1==1 or Late_t2==1):
                    for l in leave:
                        if(l.GetPersonId()==p.GetID()):
                            l_start=datetime.datetime.strptime(l.GetStart()[0:10], "%Y/%m/%d")
                            l_end=datetime.datetime.strptime(l.GetEnd()[0:10], "%Y/%m/%d")
                            if (l_start<=dt_thisday and dt_thisday<=l_end):
                                LateType=1
                    if(LateType!=1):
                        late_note=late_note+thisday+","
                        if(Late_t1==1):
                            Late=Late+1
                        if(Late_t2==1):
                            Late_2=Late_2+1
                t=datetime.timedelta(days=1)
                dt_thisday=dt_thisday+t
                thisday=dt_thisday.strftime('%Y-%m-%d')
            LateType=0
            Late_t1=0
            Late_t2=0
            if(data[p.GetID()][thisday]['WorkBeLate_2']!=0):
                Late_t2=1
            if(data[p.GetID()][thisday]['WorkBeLate']!=0):
                Late_t1=1
            if(Late_t1==1 or Late_t2==1):
                for l in leave:
                    if(l.GetPersonId()==p.GetID()):
                        l_start=datetime.datetime.strptime(l.GetStart()[0:10], "%Y/%m/%d")
                        l_end=datetime.datetime.strptime(l.GetEnd()[0:10], "%Y/%m/%d")
                        if (l_start<=dt_thisday and dt_thisday<=l_end):
                            LateType=1
                if(LateType!=1):
                    late_note=late_note+thisday+","
                    if(Late_t1==1):
                        Late=Late+1
                    if(Late_t2==1):
                        Late_2=Late_2+1
            if(all_leave[p.GetID()]['特休']==0):
                all_leave[p.GetID()]['特休']=""
            if(all_leave[p.GetID()]['公假']==0):
                all_leave[p.GetID()]['公假']=""
            if(all_leave[p.GetID()]['事假']==0):
                all_leave[p.GetID()]['事假']=""
            if(all_leave[p.GetID()]['病假']==0):
                all_leave[p.GetID()]['病假']=""
            if(all_leave[p.GetID()]['公傷病假']==0):
                all_leave[p.GetID()]['公傷病假']=""
            if(all_leave[p.GetID()]['婚假']==0):
                all_leave[p.GetID()]['婚假']=""
            if(all_leave[p.GetID()]['產假']==0):
                all_leave[p.GetID()]['產假']=""
            if(all_leave[p.GetID()]['陪產假']==0):
                all_leave[p.GetID()]['陪產假']=""
            if(all_leave[p.GetID()]['喪假']==0):
                all_leave[p.GetID()]['喪假']=""
            if(all_leave[p.GetID()]['生理假']==0):
                all_leave[p.GetID()]['生理假']=""
            if(all_leave[p.GetID()]['補假']==0):
                all_leave[p.GetID()]['補假']=""
            sheet.write_string(row, 0, p.GetID(), cell_format_with_border)
            sheet.write_string(row, 1, all_leave[p.GetID()]['名字'], cell_format_with_border)
            sheet.write_string(row, 2, str(all_leave[p.GetID()]['事假']), cell_format_with_border)
            sheet.write_string(row, 3, str(all_leave[p.GetID()]['病假']), cell_format_with_border)
            sheet.write_string(row, 4, str(all_leave[p.GetID()]['喪假']), cell_format_with_border)
            sheet.write_string(row, 5, str(all_leave[p.GetID()]['公假']), cell_format_with_border)
            sheet.write_string(row, 6, str(all_leave[p.GetID()]['特休']), cell_format_with_border)
            sheet.write_string(row, 7, str(all_leave[p.GetID()]['公傷病假']), cell_format_with_border)
            sheet.write_string(row, 8, str(all_leave[p.GetID()]['婚假']), cell_format_with_border)
            sheet.write_string(row, 9, str(all_leave[p.GetID()]['產假']), cell_format_with_border)
            sheet.write_string(row, 10, str(all_leave[p.GetID()]['陪產假']), cell_format_with_border)
            sheet.write_string(row, 11, str(all_leave[p.GetID()]['生理假']), cell_format_with_border)
            sheet.write_string(row, 12, str(all_leave[p.GetID()]['補假']), cell_format_with_border)
            sheet.write_string(row, 13, str(Late), cell_format_with_border)
            sheet.write_string(row, 14, str(Late_2), cell_format_with_border)
            sheet.write_string(row, 15, late_note, cell_format_with_border)
            row+=1
        writer.close()
        stream.seek(0)
        return stream
    
# ========== *新版本-excel統計特休 ==========
class AnnualLeaveToExcelVerson2(SendBaseResource):
    def __init__(self):
        super().__init__(self)
        self.parser.add_argument('year', required=True, help="年份(西元)")
    def post(self):
        args = self.parser.parse_args()
        leave = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
#         StartTime=datetime.datetime.strptime(args['start'], "%Y/%m/%d")
#         EndTime=datetime.datetime.strptime(args['end'], "%Y/%m/%d")
#         time_leave=[]
#         for l in leave:
#             leave_start=l.GetStart()[0:10]
#             StartDay = datetime.datetime.strptime(leave_start, "%Y/%m/%d")
#             leave_end=l.GetEnd()[0:10]
#             EndDay = datetime.datetime.strptime(leave_end, "%Y/%m/%d")
#             if(l.GetPersonId()==args['person_id']):
#                 time_leave.append(l)
        stream = AnnualLeaveToExcelVerson2.GetExcel(self,leave,person,args['year'])
        return self.GetExcelResponse("{0}年特休統計".format(args['year']), stream)
    @staticmethod
    def GetExcel(self,leave,person,year):
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_fg = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 ,'fg_color': '#BFE5FD', 'border': 2 })
        cell_format_line = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 , 'underline':True })
        cell_format_with_border_center = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1  })
        cell_format_with_border = book.add_format({ 'align': 'right', 'valign': 'vcenter', 'font_size': 14, 'border': 1 })
        cell_format_with_border_2 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 2 })
        cell_format_with_border_red = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14, 'border': 1 , 'font_color': '#FF0000'})
        now = datetime.datetime.now()
        sheet = book.add_worksheet('請假統計')
        sheet.merge_range('A1:H1', "吉鑫營造股份有限公司", cell_format)
        sheet.merge_range('A2:H2', "員工特休統計", cell_format)
#         sheet.write_string(3, 0, "日期", cell_format_fg)
        sheet.write_string(3, 0, "員工編號", cell_format_fg)
        sheet.write_string(3, 1, "員工姓名", cell_format_fg)
        sheet.write_string(3, 2, "特休天數", cell_format_fg)
        sheet.write_string(3, 3, "第一季", cell_format_fg)
        sheet.write_string(3, 4, "第二季", cell_format_fg)
        sheet.write_string(3, 5, "第三季", cell_format_fg)
        sheet.write_string(3, 6, "第四季", cell_format_fg)
        sheet.write_string(3, 7, "剩餘特休", cell_format_fg)
        sheet.merge_range('F3:H3', "查詢日期：{0}年 {1}月 {2}日".format(now.year,now.month,now.day), cell_format)
        sheet.merge_range('D3:E3', "查詢年份：{0}".format(year), cell_format)
        sheet.set_column('A:A', 13)
        sheet.set_column('B:B', 13)
        sheet.set_column('C:C', 13)
        sheet.set_column('D:D', 17)
        sheet.set_column('E:E', 17)
        sheet.set_column('F:F', 17)
        sheet.set_column('G:G', 17)
        sheet.set_column('H:H', 17)
        sheet.set_row(1, 24)
        sheet.set_row(2, 24)
        sheet.set_row(3, 24)
        sheet.set_row(4, 24)
        row=4
        all_leave={}
        role = [0,7,10,14,14,15,15,15,15,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
        for p in person:
            user =  UserModel.FromDB(p.GetID(), self.log.SetMessage)
            if user.isExist:
                annual_day=0
                on_board = user.GetOnBoard()
                on_board_s = on_board.split("/")
                s_year = int(year) - int(on_board_s[0])
                s_month = 12 - int(on_board_s[1])
                if(s_year==0 and s_month>5):
                    annual_day=3
                elif(s_year>24):
                    annual_day=30
                else:
                    annual_day=role[s_year]
                annual_hour=annual_day*8
                all_leave[p.GetID()]={'名字':p.GetName(),'特休天數':annual_day,'特休小時數':annual_hour,'第一季':0,'第二季':0,'第三季':0,'第四季':0,'剩餘':annual_hour}
            else:
                all_leave[p.GetID()]={'名字':p.GetName(),'特休天數':0,'特休小時數':0,'第一季':0,'第二季':0,'第三季':0,'第四季':0,'剩餘':0}
        Q1_start=datetime.datetime.strptime(year+"/01/01", "%Y/%m/%d")
        Q1_end=datetime.datetime.strptime(year+"/03/31", "%Y/%m/%d")
        Q2_start=datetime.datetime.strptime(year+"/04/01", "%Y/%m/%d")
        Q2_end=datetime.datetime.strptime(year+"/06/30", "%Y/%m/%d")
        Q3_start=datetime.datetime.strptime(year+"/07/01", "%Y/%m/%d")
        Q3_end=datetime.datetime.strptime(year+"/09/30", "%Y/%m/%d")
        Q4_start=datetime.datetime.strptime(year+"/10/01", "%Y/%m/%d")
        Q4_end=datetime.datetime.strptime(year+"/12/31", "%Y/%m/%d")
        for l in leave:
            if(l.GetCategory()=='特休'):
                l_start=l.GetStart()[0:10]
                l_end=l.GetEnd()[0:10]
                if(l_start==l_end):
                    ThisDay = datetime.datetime.strptime(l_start, "%Y/%m/%d")
                    if(Q1_start<=ThisDay and ThisDay<=Q1_end):
                        all_leave[l.GetPersonId()]['第一季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                    if(Q2_start<=ThisDay and ThisDay<=Q2_end):
                        all_leave[l.GetPersonId()]['第二季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                    if(Q3_start<=ThisDay and ThisDay<=Q3_end):
                        all_leave[l.GetPersonId()]['第三季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                    if(Q4_start<=ThisDay and ThisDay<=Q4_end):
                        all_leave[l.GetPersonId()]['第四季']+=float(l.GetLeaveTime())
                        all_leave[l.GetPersonId()]['剩餘']-=float(l.GetLeaveTime())
                else:
                    s_time=l.GetStart()
                    thisday=l_start
                    end_day=l_end
                    all_hour=float(l.GetLeaveTime())
                    while(thisday!=end_day):
                        e_time=thisday+" 17:30"
                        c_time=thisday+" 12:00"
                        dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                        if(datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")<datetime.datetime.strptime(c_time, "%Y/%m/%d %H:%M")):
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600-1
                        else:
                            this_hour=(datetime.datetime.strptime(e_time, "%Y/%m/%d %H:%M")-datetime.datetime.strptime(s_time, "%Y/%m/%d %H:%M")).seconds/3600
                        if(Q1_start<=dt_thisday and dt_thisday<=Q1_end):
                            all_leave[l.GetPersonId()]['第一季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        if(Q2_start<=dt_thisday and dt_thisday<=Q2_end):
                            all_leave[l.GetPersonId()]['第二季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        if(Q3_start<=dt_thisday and dt_thisday<=Q3_end):
                            all_leave[l.GetPersonId()]['第三季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        if(Q4_start<=dt_thisday and dt_thisday<=Q4_end):
                            all_leave[l.GetPersonId()]['第四季']+=this_hour
                            all_leave[l.GetPersonId()]['剩餘']-=this_hour
                        all_hour=all_hour-this_hour
                        t=datetime.timedelta(days=1)
                        dt_thisday=dt_thisday+t
                        if(dt_thisday.strftime('%Y/%m/%d')!=end_day):
                            while(dt_thisday.weekday()==5 or dt_thisday.weekday()==6):
                                dt_thisday=dt_thisday+t
                        thisday=dt_thisday.strftime('%Y/%m/%d')
                        s_time=thisday+" 08:30"
                    dt_thisday=datetime.datetime.strptime(thisday, "%Y/%m/%d")
                    if(Q1_start<=dt_thisday and dt_thisday<=Q1_end):
                        all_leave[l.GetPersonId()]['第一季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
                    if(Q2_start<=dt_thisday and dt_thisday<=Q2_end):
                        all_leave[l.GetPersonId()]['第二季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
                    if(Q3_start<=dt_thisday and dt_thisday<=Q3_end):
                        all_leave[l.GetPersonId()]['第三季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
                    if(Q4_start<=dt_thisday and dt_thisday<=Q4_end):
                        all_leave[l.GetPersonId()]['第四季']+=all_hour
                        all_leave[l.GetPersonId()]['剩餘']-=all_hour
        for p in person:
            Q1_change=str(int(all_leave[p.GetID()]['第一季']//8))+"天"+str(float(all_leave[p.GetID()]['第一季']%8))+"小時"
            Q2_change=str(int(all_leave[p.GetID()]['第二季']//8))+"天"+str(float(all_leave[p.GetID()]['第二季']%8))+"小時"
            Q3_change=str(int(all_leave[p.GetID()]['第三季']//8))+"天"+str(float(all_leave[p.GetID()]['第三季']%8))+"小時"
            Q4_change=str(int(all_leave[p.GetID()]['第四季']//8))+"天"+str(float(all_leave[p.GetID()]['第四季']%8))+"小時"
            Last_change=str(int(all_leave[p.GetID()]['剩餘']//8))+"天"+str(float(all_leave[p.GetID()]['剩餘']%8))+"小時"
            sheet.write_string(row, 0, p.GetID(), cell_format_with_border_center)
            sheet.write_string(row, 1, all_leave[p.GetID()]['名字'], cell_format_with_border_center)
            sheet.write_string(row, 2, str(all_leave[p.GetID()]['特休天數']), cell_format_with_border_center)
            sheet.write_string(row, 3, Q1_change, cell_format_with_border)
            sheet.write_string(row, 4, Q2_change, cell_format_with_border)
            sheet.write_string(row, 5, Q3_change, cell_format_with_border)
            sheet.write_string(row, 6, Q4_change, cell_format_with_border)
            sheet.write_string(row, 7, Last_change, cell_format_with_border)
            sheet.set_row(row, 24)
            row+=1
        writer.close()
        stream.seek(0)
        return stream
