from base_resource import BaseResource, SendBaseResource
from punch_card_model import PunchCardModel
from punch_card2_model import PunchCard2Model
from mysql_model import DeviceModel, PersonModel,PersonGroupRelationModel
from influx_model import BaseInfluxModel, PunchInfluxModel
# from docker_model import RTMPDockerModel
from line_model import PunchNotifyModel
from flask_restful import request
from datetime import datetime, timedelta
from io import BytesIO
import os, math, calendar, pandas as pd, numpy as np, json, base64, pytz
from broadcast_resource import broadcast_with_temp
from holiday_model import HolidayModel
from leave_off_work_model import LeaveOffWorkModel
from overtime_model import OvertimeModel
from docx import Document
from docx.enum.style import WD_STYLE_TYPE
from device_shift_relation_model import DeviceShiftRelationModel
from shift_model import ShiftModel


env = os.environ
rtmp_host = env['RTMP_HOST']      if 'RTMP_HOST' in env else "localhost"
rtmp_port = int(env['RTMP_PORT']) if 'RTMP_PORT' in env else 1935

tw = pytz.timezone('Asia/Taipei')
utc = pytz.timezone('UTC')

Devices = {}
Persons = {}
Influxs = {}
Dockers = {}
class PunchCardUpload(BaseResource):
    def __init__(self):
        super().__init__('UploadRecord')
        self.parser.add_argument('UUID')
        self.parser.add_argument('Host')
        self.parser.add_argument('Port')
        self.parser.add_argument('ID')
        self.parser.add_argument('Name')
        self.parser.add_argument('Temperature')
        # self.parser.add_argument('Position') # { X: int, Y: int, W: int, H: int }
        self.parser.add_argument('Image')
        self.parser.add_argument('Similarity')
        self.parser.add_argument('Type')
        self.parser.add_argument('Timestamp')
        self.parser.add_argument('Time') # yyyy-MM-dd HH:mm:ss
    def post(self):
        args = self.parser.parse_args()
#         document = Document()
#         p = document.add_paragraph('UUID：{0}'.format(args['UUID']))
#         p = document.add_paragraph('Host：{0}'.format(args['Host']))
#         p = document.add_paragraph('Port：{0}'.format(args['Port']))
#         p = document.add_paragraph('ID：{0}'.format(args['ID']))
#         p = document.add_paragraph('Name：{0}'.format(args['Name']))
#         p = document.add_paragraph('Temperature：{0}'.format(args['Temperature']))
#         p = document.add_paragraph('Similarity：{0}'.format(args['Similarity']))
#         p = document.add_paragraph('Type：{0}'.format(args['Type']))
#         p = document.add_paragraph('Timestamp：{0}'.format(args['Timestamp']))
#         p = document.add_paragraph('Time：{0}'.format(args['Time']))
#         document.save("punch_test.docx")
        # Device Data
        device_uuid = args['UUID']
        device_host = args['Host']
        device_port = args['Port']
        device_type = args['Type']
#         print(device_uuid, device_host, device_port, device_type)
        device = Devices[device_uuid] if device_uuid in Devices else DeviceModel.FromDB(device_uuid, self.log.SetMessage)
        device.SetHost(device_host)
        device.SetPort(device_port)
        device.SetType(device_type)
        if not device.isExist:
            self.message = "{0} inserted".format(device_uuid) if device.InsertToDB(self.log.SetMessage) else "{0} insert failed".format(device_uuid)
        message = []
        if device.HasChangeHost():
            message.append(
                "Host: {0} -> {1}".format(device.GetOldHost(), device.GetHost())
            )
        if device.HasChangePort():
            message.append(
                "Port: {0} -> {1}".format(device.GetOldPort(), device.GetPort())
            )
        self.message = "{0}, {1}".format(self.message, ",".join(message)) if len(message) > 0 else self.message
        if device.isExist: Devices[device_uuid] = device
        # Person Data
        person_id = args['ID']
        person_name = args['Name']
        person_temperature = args['Temperature']
        person_temperature = float(person_temperature) if isinstance(person_temperature, str) else person_temperature
        person_temperature = person_temperature / 100 if person_temperature > 100 else person_temperature
        person_image = args['Image']
#         print(args['Time'])
        person_time = datetime.strptime(args['Time'], '%Y-%m-%d %H:%M:%S').replace(tzinfo=utc)
        print(person_time)
        person_time = person_time.astimezone(tw)
        print(person_time)
        person = Persons[person_id] if person_id in Persons else PersonModel.FromDB(person_id, self.log.SetMessage)
        if not person.isExist:
            person = PersonModel.FromDB("unknown", self.log.SetMessage)
            person.SetName("Guest")
        person_id = person.GetID()
        person_name = person.GetName()
        now = datetime.now()
        filename = '{0}_{1}_{2}'.format(person_id, person_time.strftime('%Y_%m_%d_%H_%M_%S'),now.microsecond)
        filename = '{0}.jpg'.format(os.path.join('static', 'face', filename))
        # 寫入之前檢查空間是否足夠
        max_images = 90000
        face_save_path = os.path.join('static', 'face')
        list_of_files = os.listdir(face_save_path)
        full_path = ["{}/{}".format(face_save_path,x) for x in list_of_files]
        if(len(full_path) > max_images):
            oldest_file = min(full_path, key=os.path.getctime)
            print(oldest_file)
            os.remove(oldest_file)
        # 寫入檔案
        if not os.path.isfile(filename) and len(person_image) > 0:
            with open(filename, 'wb') as f:
                f.write(base64.b64decode(person_image))
        person_image = filename
        if person.HasToken():
            token = person.GetToken()
            notify = PunchNotifyModel(token)
            self.message = "{0} notify failed".format(token) if not notify.Broadcast(person_time, person_name, person_temperature, filename) else self.message
        if person.isExist: Persons[person_id] = person
        # Record Data
        device_location = device.GetLocation()
#         position_x = int(args['Position']['X'])
#         position_Y = int(args['Position']['Y'])
#         position_W = int(args['Position']['W'])
#         position_H = int(args['Position']['H'])
        person_similarity = float(args['Similarity'])
        person_similarity = person_similarity / 100 if person_similarity > 100 else person_similarity
#         person_position = [ position_x, position_Y, position_W, position_H ]
        person_type = person.GetType()
        record = PunchInfluxModel(device_uuid, device_host, device_location, person_time)
        record.SetUID(person_id)
        record.SetID(person_id)
        record.SetName(person_name)
        record.SetType(person_type)
        record.SetSimilarity(person_similarity)
        record.SetTemperature(person_temperature)
#         record.SetPosition(person_position)
        record.SetImage(person_image)
        influx = Influxs[device_host] if device_host in Influxs else BaseInfluxModel()
        influx.Add(record)
        self.message = "influxdb upload failed" if not influx.Upload() else "insert {0} to db from {1}".format(person_id, device_host)
        Influxs[device_host] = influx
        return self.GetResponse()
class PunchCard1Upload(BaseResource):
    def __init__(self):
        super().__init__()
        self.parser.add_argument('Name')
        self.parser.add_argument('Data')
        self.parser.add_argument('TimeStamp')
    def post(self):
        args = self.parser.parse_args()
        device = PunchCardModel()
        device.SetTimeStamp(args['TimeStamp'])
        if device.SetData(args['Data']):
            # 建立裝置物件
            uuid = device.GetDUUID()
            host = device.GetHost()
            # print(device.DeviceInfo.__dict__)
            model = Devices[uuid] if uuid in Devices else DeviceModel.FromDB(uuid, self.log.SetMessage)
            model.SetHost(host)
            model.SetPort(8011)
            model.SetType(5)
            if not model.isExist:
                if model.InsertToDB(self.log.SetMessage):
                    self.message = "{0} inserted".format(uuid)
                else:
                    model.ResultCode = 0
                    self.message = "{0} insert failed".format(uuid)
            if model.HasChangeHost():
                self.message = []
                self.message.append(
                    "Host: {0} -> {1}".format(model.GetOldHost(), model.GetHost())
                )
                if model.HasChangePort():
                    self.message.append(
                        "Port: {0} -> {1}".format(model.GetOldPort(), model.GetPort())
                    )
                    if model.UpdateToDB(self.log.SetMessage):
                        self.message = ", ".join(self.message)
                    else:
                        model.ResultCode = 0
                        self.message = "{0} update failed".format(uuid)
            Devices[uuid] = model
            # 建立人員物件
            ID = device.GetID()
            image = device.GetImagePath()
            name = device.GetName()
            temperature = device.GetTemperature()
            time = device.GetTime()
            person = Persons[ID] if ID in Persons else PersonModel.FromDB(ID, self.log.SetMessage)
            if person.HasToken():
                token = person.GetToken()
                notify = PunchNotifyModel(token)
                if not notify.Broadcast(time, name, temperature, image):
                    model.ResultCode = 0
                    self.message = "{0} notify failed".format(token)
            Persons[ID] = person
            # 建立資料庫物件
            location = model.GetLocation()
            position = device.GetPosition()
            ptype = person.GetType()
            similarity = device.GetSimilarity()
            print(uuid, host, location)
            punch = PunchInfluxModel(uuid, host, location, time)
            punch.SetID(ID)
            punch.SetUID(ID)
            punch.SetName(name)
            punch.SetType(ptype)
            punch.SetSimilarity(similarity)
            punch.SetTemperature(temperature)
            punch.SetPosition(position)
            punch.SetImage(image)
            influx = Influxs[host] if host in Influxs else BaseInfluxModel()
            influx.Add(punch)
            Influxs[host] = influx
            if not influx.Upload():
                model.ResultCode = 0
                self.message = "influxdb upload failed"
            # 建立串流拋轉容器物件
            key = "{0}/{1}".format("RD", uuid)
#             docker = Dockers[uuid] if uuid in Dockers else RTMPDockerModel(uuid)
#             if docker.Build() and not docker.Run(host, 554, "", rtmp_host, rtmp_port, key):
#                 device.ResultCode = 0
#             Dockers[uuid] = docker
    #         device.ResultCode = 0
            # echo server 廣播
            snapshot = device.GetImagePath()
            snapshot = snapshot.split('.')[0].split('/')[-1]
            snapshot = "/punch/card/image/{0}".format(snapshot)
            payload = {
                'time': time.strftime("%Y-%m-%d %H:%M:%S"),
                'name': name,
                'temperature': temperature,
                'snapshot': snapshot,
                'location': location,
                'type': ptype
            }
            #broadcast_status_code = broadcast_with_temp(payload)
            if self.message is not None and len(self.message) > 0:
                print(self.message)
            return device.GetResponse()
class PunchCard2Upload(BaseResource):
    def __init__(self):
        super().__init__()
        self.parser.add_argument('method')
        self.parser.add_argument('uuid')
        self.parser.add_argument('mac')
        self.parser.add_argument('host')
        self.parser.add_argument('data')
    def post(self):
        args = self.parser.parse_args()
        try:
            model = PunchCard2Model(args['method'], args['uuid'], args['mac'], args['host'], args['data'])
        except:
            name = datetime.now().strftime("%Y-%m-%d-%H:%M:%S")
            with open('{0}.txt'.format(name), 'w') as f:
                f.write(args['data'])
                f.flush()
                args['data'] = None
        if args['data'] is not None:
            # ----- Process Device Data -----
            a1 = model.UUID
            a2 = model.Host
            device = Devices[a1] if a1 in Devices else DeviceModel.FromDB(a1, self.log.SetMessage)
            device.SetHost(a2)
            device.SetPort(80)
            if not device.isExist:
                if device.InsertToDB(self.log.SetMessage):
                    self.message = "{0} inserted".format(a1)
                else:
                    self.message = "{0} insert failed".format(a1)
                    model.SetResponse(-1001, self.message)
            # if device.HasChangeHost():
            #     self.message = []
            #     self.message.append(
            #         "Host: {0} -> {1}".format(device.GetOldHost(), device.GetHost())
            #     )
            #     if model.HasChangePort():
            #         self.message.append(
            #             "Port: {0} -> {1}".format(device.GetOldPort(), device.GetPort())
            #         )
            #         if device.UpdateToDB(self.log.SetMessage):
            #             self.message = ", ".join(self.message)
            #         else:
            #             self.message = "{0} update failed".format(a1)
            #             model.SetResponse(-1001, self.message)
            Devices[a1] = device
            # ----- Process Person Data -----
            a3 = model.GetPersonID()
            a4 = model.GetPersonName()
            a5 = model.GetPersonFace()
            a6 = model.GetCaptureTime()
            a7 = model.GetTemperature()
            person = Persons[a3] if a3 in Persons else PersonModel.FromDB(a3, self.log.SetMessage)
            if person.HasToken():
                a8 = person.GetToken()
                notify = PunchNotifyModel(a8)
                if not notify.Broadcast(a6, a4, a7, a5):
                    self.message = "{0} notify failed".format(a8)
                    # model.SetResponse(-1001, self.message)
            Persons[a3] = person
            # ----- Process Influx Data -----
            a9 = device.GetLocation()
            a10 = person.GetType()
            a11 = model.GetPersonSimilarity()
            a12 = model.GetPosition()
            punch = PunchInfluxModel(a1, a2, a9, a6)
            punch.SetID(a3)
            punch.SetUID(a3)
            punch.SetName(a4)
            punch.SetType(a10)
            punch.SetSimilarity(a11)
            punch.SetTemperature(a7)
            punch.SetPosition(a12)
            punch.SetImage(a5)
            # ----- Upload Influx Data -----
            influx = Influxs[a2] if a2 in Influxs else BaseInfluxModel()
            influx.Add(punch)
            Influxs[a2] = influx
            if not influx.Upload():
                self.message = "influxdb upload failed"
                model.SetResponse(-1001, self.message)
#         # 建立串流拋轉容器物件
#         key = "{0}/{1}".format("RD", uuid)
#         docker = Dockers[uuid] if uuid in Dockers else RTMPDockerModel(uuid)
#         if docker.Build() and not docker.Run(host, 554, "", rtmp_host, rtmp_port, key):
#             device.ResultCode = 0
#         Dockers[uuid] = docker
# #         device.ResultCode = 0
            snapshot = a5.split('.')[0].split('/')[-1]
            snapshot = "/punch/card/image/{0}".format(snapshot)
            payload = {
                'time': a6.strftime("%Y-%m-%d %H:%M:%S"),
                'name': a4,
                'temperature': a7,
                'snapshot': snapshot,
                'location': a9,
                'type': a10
            }
            broadcast_status_code = broadcast_with_temp(payload)
        if self.message is not None and len(self.message) > 0:
            print(self.message)
        return model.GetResponse()
class PunchCardInsert(BaseResource):
    def __init__(self):
        super().__init__("InsertRecordOfPunchCard")
        self.parser.add_argument('Time', required=True, help="yyyy-MM-dd HH:mm:ss")
        self.parser.add_argument('User', required=True, help="上限 10 字")
        self.parser.add_argument('Temperature')
    def post(self, ID):
        args = self.parser.parse_args()
        person = PersonModel.FromDB(ID, self.log.SetMessage)
        if person.isExist:
            user = args['User']
            time = datetime.strptime(args['Time'], '%Y-%m-%d %H:%M:%S')
            host = self.log.GetHost()
            punch = PunchInfluxModel(user, host, user, time)
            temperature = args['Temperature']
            temperature = temperature if isinstance(temperature, float) else float(temperature)
            # 1000_2020_09_03_15_04_11.jpg
            time = punch.GetTime()
            filename = "{0}_{1}.jpg".format(os.path.join('static', 'face', ID), time.strftime("%Y_%m_%d_%H_%M_%S"))
            punch.SetID(ID)
            punch.SetName(person.GetName())
            punch.SetType(person.GetType())
            punch.SetSimilarity(100)
            punch.SetTemperature(temperature)
            punch.SetPosition([ 0, 0, 0, 0 ])
            punch.SetImage(filename)
            influx = BaseInfluxModel()
            influx.Add(punch)
            if influx.Upload():
                self.message = "{0} inserted".format(ID)
            else:
                self.message = "{0} insert failed".format(ID)
        else:
            self.message = "{0} is exist in database".format(ID)
        return self.GetResponse()
class PunchCardRecord(BaseResource):
    def __init__(self):
        super().__init__("SelectRecordOfPunchCard")
        self.parser.add_argument('personID', help="上限 10 字")
        self.parser.add_argument('start', required=True, help="yyyy-MM-dd HH:mm:ss")
        self.parser.add_argument('end', required=True, help="yyyy-MM-dd HH:mm:ss")
        self.parser.add_argument('inHour', required=True, help="0~23")
        self.parser.add_argument('inMinute', required=True, help="0~59")
        self.parser.add_argument('outHour', required=True, help="0~23")
        self.parser.add_argument('outMinute', required=True, help="0~59")
        self.parser.add_argument('lunchBreak', required=True)
    def post(self):
        args = self.parser.parse_args()
        personID = args['personID']
        try:
            start = datetime.strptime(args['start'], '%Y-%m-%d %H:%M:%S')
            try:
                end = datetime.strptime(args['end'], '%Y-%m-%d %H:%M:%S')
                try:
                    i1 = int(args['inHour'])
                    i2 = int(args['inMinute'])
                    i3 = int(args['outHour'])
                    i4 = int(args['outMinute'])
                    i5 = int(args['lunchBreak'])
                    models = PersonModel.AllFromDB(self.log.SetMessage)
                    if len(models) > 0:
                        self.data = PunchCardRecord.GetFullTable(start, end, i1, i2, i3, i4, i5, models, personID)
                        if personID is not None:
                            self.message = "{0} has {1} selected".format(personID, len(self.data[personID]))
                        else:
                            self.message = "{0} has {1} selected".format(len(self.data), np.sum([ len(self.data[x]) for x in self.data ]))
                    else:
                        self.message = "{0} isn't exist data in database".format(personID)
                except:
                    self.message = "parameters error"
            except:
                self.message = "the format of end time {0} isn't match".format(args['end'])
        except:
            self.message = "the format of start time {0} isn't match".format(args['start'])
        return self.GetResponse()
    @staticmethod
    def GetTable(start, end, person, temperature=None):
        table = {}
        for p in person:
            table[p.GetID()] = {}
        data = PunchInfluxModel.AllFromRaw(start, end, temperature)
        for row in data:
            ID = row.GetID()
            if ID in table:
                time = row.GetTime()
                date = time.strftime('%Y-%m-%d')
                if date not in table[ID]:
                    table[ID][date] = {}
                time = time.strftime('%H:%M:%S')
                image = row.GetImage().split('.')[0].split('/')[2]
                # 計算當日最高溫
                if 'MaxTemperature' not in table[ID][date]:
                    table[ID][date]['MaxTemperature']=row.GetTemperature()
                else:
                    table[ID][date]['MaxTemperature']=row.GetTemperature() if row.GetTemperature()> table[ID][date]['MaxTemperature'] else table[ID][date]['MaxTemperature']
                if 'Start' not in table[ID][date]:
                    table[ID][date]['Start'] = {}
                    table[ID][date]['Start']['Time'] = time
                    table[ID][date]['Start']['Image'] = image
                    table[ID][date]['Start']['Temperature'] = row.GetTemperature()
                table[ID][date]['End'] = {}
                table[ID][date]['End']['Time'] = time
                table[ID][date]['End']['Image'] = image
                table[ID][date]['End']['Temperature'] = row.GetTemperature()
        return table
    @staticmethod
    def GetFullTable(start, end, inHour, inMinute, outHour, outMinute, lunchBreak, person, personID=None):
        table = PunchCardRecord.GetTable(start, end, person)
        table = { personID: table[personID] } if personID is not None else table
        date = {}
        for ID in table.keys():
            dateKey = list(table[ID].keys())
            for dateString in dateKey:
                i1 = datetime.strptime(dateString, '%Y-%m-%d')
                i2 = table[ID][dateString]['Start']['Time']
                i2 = "{0} {1}".format(dateString, i2)
                i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                i3 = table[ID][dateString]['End']['Time']
                i3 = "{0} {1}".format(dateString, i3)
                i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                i4 = inHour
                i5 = inMinute
                i6 = outHour
                i7 = outMinute
                i8 = lunchBreak
                if ID not in date:
                    date[ID] = start
                while i1 > date[ID] and end > i1:
                    tmp = date[ID].strftime('%Y-%m-%d')
                    table[ID][tmp] = {}
                    table[ID][tmp]['Start'] = {}
                    table[ID][tmp]['Start']['Image'] = ""
                    table[ID][tmp]['Start']['Temperature'] = 0.0
                    table[ID][tmp]['Start']['Time'] = ""
                    table[ID][tmp]['End'] = {}
                    table[ID][tmp]['End']['Image'] = ""
                    table[ID][tmp]['End']['Temperature'] = 0.0
                    table[ID][tmp]['End']['Time'] = ""
                    table[ID][tmp]['Over'] = 0.0
                    table[ID][tmp]['WorkBeLate'] = 0
                    table[ID][tmp]['WorkLeaveEarlier'] = 0
                    date[ID] = date[ID] + timedelta(days=1)
                date[ID] = date[ID] + timedelta(days=1)
                # start, end, startOver, endOver, work, over
                o1, o2, o3, o4, o5, o6 = PunchCardRecord.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                late = datetime(i1.year, i1.month, i1.day, inHour, inMinute)
                late = (i2 - late).total_seconds() if i2 > late else 0
                earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                o1 = o1.strftime('%H:%M:%S')
                o2 = o2.strftime('%H:%M:%S') if o1 != o2 else ""
                o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                o4 = o4.strftime('%H:%M:%S') if o4 is not None and o3 != o4 else ""
                table[ID][dateString]['WorkBeLate'] = int(late / 60)
                table[ID][dateString]['WorkLeaveEarlier'] = int(earlier / 60) if o1 != o2 else 0
                table[ID][dateString]['Over'] = o6
            if ID not in date:
                date[ID] = start
            while end > date[ID]:
                tmp = date[ID].strftime('%Y-%m-%d')
                table[ID][tmp] = {}
                table[ID][tmp]['Start'] = {}
                table[ID][tmp]['Start']['Image'] = ""
                table[ID][tmp]['Start']['Temperature'] = 0.0
                table[ID][tmp]['Start']['Time'] = ""
                table[ID][tmp]['End'] = {}
                table[ID][tmp]['End']['Image'] = ""
                table[ID][tmp]['End']['Temperature'] = 0.0
                table[ID][tmp]['End']['Time'] = ""
                table[ID][tmp]['Over'] = 0.0
                table[ID][tmp]['WorkBeLate'] = 0
                table[ID][tmp]['WorkLeaveEarlier'] = 0
                date[ID] = date[ID] + timedelta(days=1)
        return table
    @staticmethod
    def CalculateTime(date, start, end, inHour, inMinute, outHour, outMinute, lunchBreak, workBuffer, overBuffer):
        work = 0.0
        over = 0.0
        startWork = start
        # endWork = end
        startOver = None
        endOver = None
        if (inHour > start.hour) or (start.hour == inHour and inMinute > start.minute):
            start = datetime(date.year, date.month, date.day, inHour, inMinute+workBuffer)
        over = datetime(date.year, date.month, date.day, outHour, outMinute)
        if start > over:
            over = datetime(date.year, date.month, date.day, start.hour, start.minute, start.second)
        if over > end:
            over = 0
        else:
            over = (end - over - timedelta(minutes=overBuffer)).total_seconds() / 60 # second -> minute
            # over = (end - over).total_seconds() / 60
            if over > 0:
                startOver = datetime(date.year, date.month, date.day, outHour, outMinute+overBuffer)
                endOver = end
                end = datetime(date.year, date.month, date.day, outHour, outMinute+overBuffer)
        if end > start:
            work = (end - start).total_seconds() / 60 # second -> minute
        if end >= date and date >= start:
            work = work - lunchBreak
        endWork = end
        return startWork, endWork, startOver, endOver, round(work / 60), math.floor(over / 30) / 2
        # return startWork, endWork, startOver, endOver, round(work / 60), over
class PunchCardRecordAll(BaseResource):
    def __init__(self):
        super().__init__("SelectAllRecordOfPunchCard")
        self.parser.add_argument('start', required=True, help="yyyy-MM-dd HH:mm:ss")
        self.parser.add_argument('end', required=True, help="yyyy-MM-dd HH:mm:ss")
    def post(self):
        args = self.parser.parse_args()
        try:
            start = datetime.strptime(args['start'], '%Y-%m-%d %H:%M:%S')
            try:
                end = datetime.strptime(args['end'], '%Y-%m-%d %H:%M:%S')
                self.data = PunchCardRecordAll.GetList(start, end)
                self.message = "{0} selected".format(len(self.data))
            except:
                self.message = "the format of end time {0} isn't match".format(args['end'])
        except:
            self.message = "the format of start time {0} isn't match".format(args['start'])
        return self.GetResponse()
    @staticmethod
    def GetList(start, end):
        data = PunchInfluxModel.AllFromRaw(start, end)
        dataList = []
        for row in data:
            dataImage = row.GetImage()
            image = dataImage.split('.')[0].split('/')[2] if '.' in dataImage else dataImage
            record = {}
            record['ID'] = row.GetID()
            record['Name'] = row.GetName()
            record['Type'] = row.GetType()
            record['Location'] = row.GetLocation()
            record['similarity'] = row.GetSimilarity()
            record['Temperature'] = row.GetTemperature()
            record['Image'] = image
            dataList.append(record)
        return dataList
# ========== 全部人匯出月份 ==========
class PunchCardRecordDownload(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
    def post(self, year, month):
        args = self.parser.parse_args()
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        # Add 1911
        year = year if year > 1911 else year + 1911
        # Get the first day of this month and the first day of next month
        first = datetime(year, month, 1)
        last = datetime(year if 12 > month else year + 1, month + 1 if 12 > month else 1, 1)
        # Get person data
        person = PersonModel.AllFromDB(self.log.SetMessage)
        stream = PunchCardRecordDownload.GetExcel(self, person, first, last, inHour, inMinute, outHour, outMinute, lunchBreak)
        year = str(year)
        month = str(month).zfill(2)
        return self.GetExcelResponse("{0}-{1}".format(year, month), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, outHour, outMinute, lunchBreak):
        record = PunchCardRecord.GetTable(first, last, person)
        # create excel
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
#         column_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bold': True, 'font_size': 16 })
#         row_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_center = book.add_format({ 'align': 'center', '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'})
#         column1 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column2 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column3 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column4 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column5 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column6 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
        sheet = {}
        count = {}
        day = {}
        for p in person:
            sheet[p.GetID()] = book.add_worksheet(p.GetName())
            sheet[p.GetID()].merge_range('A1:G1', "{0}年{1}月份   出勤表".format(first.year-1911, first.month), cell_format_center)
            sheet[p.GetID()].write_string(1, 0, "員工編號", cell_format)
            sheet[p.GetID()].write_string(1, 1, p.GetID(), cell_format)
            sheet[p.GetID()].write_string(1, 2, "員工姓名", cell_format)
            sheet[p.GetID()].write_string(1, 3, p.GetName(), cell_format)
            sheet[p.GetID()].merge_range('A4:A5', "出勤日期", cell_format_with_border)
            sheet[p.GetID()].merge_range('B4:C4', "出勤時間", cell_format_with_border)
            sheet[p.GetID()].merge_range('D4:F4', "加班", cell_format_with_border)
            sheet[p.GetID()].merge_range('G4:G5', "備註", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 1, "上班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 2, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 3, "上班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 4, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 5, "時數", cell_format_with_border)
            sheet[p.GetID()].set_column('A:A', 13)
            sheet[p.GetID()].set_column('B:B', 13)
            sheet[p.GetID()].set_column('C:C', 13)
            sheet[p.GetID()].set_column('D:D', 13)
            sheet[p.GetID()].set_column('E:E', 13)
            sheet[p.GetID()].set_column('F:F', 10)
            sheet[p.GetID()].set_column('G:G', 20)
            sheet[p.GetID()].set_row(0, 24)
            sheet[p.GetID()].set_row(1, 24)
            sheet[p.GetID()].set_row(3, 24)
            sheet[p.GetID()].set_row(4, 24)
            count[p.GetID()] = 5
            day[p.GetID()] = 1
        #---------- holiday ----------
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        for ID in record.keys():
            for dateString in record[ID].keys():
                i1 = datetime.strptime(dateString, '%Y-%m-%d')
                i1 = i1 + timedelta(hours=12)
                i2 = record[ID][dateString]['Start']['Time']
                i2 = "{0} {1}".format(dateString, i2)
                i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                i3 = record[ID][dateString]['End']['Time']
                i3 = "{0} {1}".format(dateString, i3)
                i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                i4 = inHour
                i5 = inMinute
                i6 = outHour
                i7 = outMinute
                i8 = lunchBreak
                # start, end, startOver, endOver, work, over
                o1, o2, o3, o4, o5, o6 = PunchCardRecord.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                o1 = o1.strftime('%H:%M:%S')
                o2 = o2.strftime('%H:%M:%S')
                o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""

#                 work = 0.0
#                 over = 0.0
#                 workBuffer = 10
#                 overBuffer = 15
#                 note = ""
#                 date = datetime.strptime(dateString, '%Y-%m-%d')
#                 date = date + timedelta(hours=12)
#                 startString = record[ID][dateString]['Start']['Time']
#                 startString = "{0} {1}".format(dateString, startString)
#                 start = datetime.strptime(startString, '%Y-%m-%d %H:%M:%S')
#                 startOver = ""
#                 endString = record[ID][dateString]['End']['Time']
#                 endString = "{0} {1}".format(dateString, endString)
#                 end = datetime.strptime(endString, '%Y-%m-%d %H:%M:%S')
#                 endOver = ""
#                 if (inHour > start.hour) or (start.hour == inHour and inMinute > start.minute):
#                     start = datetime(date.year, date.month, date.day, inHour, inMinute+workBuffer)
#                 else:
#                     note = "遲到"
#                 over = datetime(date.year, date.month, date.day, outHour, outMinute)
#                 if start > over:
#                     over = datetime(date.year, date.month, date.day, start.hour, start.minute, start.second)
#                 if over > end:
#                     note = "早退"
#                     over = 0
#                 else:
#                     over = (end - over - timedelta(minutes=overBuffer)).total_seconds() / 60 # second -> minute
#                     if over > 0:
#                         startOver = datetime(date.year, date.month, date.day, outHour, outMinute+overBuffer)
#                         startOver = startOver.strftime('%H:%M:%S')
#                         endOver = end.strftime('%H:%M:%S')
#                         end = datetime(date.year, date.month, date.day, outHour, outMinute+overBuffer-1)
#                         record[ID][dateString]['End']['Time'] = end.strftime('%H:%M:%S')
#                 if end > start:
#                     work = (end - start).total_seconds() / 60 # second -> minute
#                 if end >= date and date >= start:
#                     work = work - lunchBreak
                for x in range(day[ID], i1.day):
                    note=""
                    # --- 註記請假資料 ---
                    leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                    for leave in leave_all:
                        leave_start=leave.GetStart()[0:10]
                        StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                        leave_end=leave.GetEnd()[0:10]
                        EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                        ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                        if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                            note = leave.GetCategory()
                    # --- 取得 holiday資料註記 ---
                    note_type=0
                    for holiday in holidays_all:
                        if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                            if(holiday.GetIsWork()==False):
                                note_type=1
                                note = holiday.GetName()
                            else:
                                if(note==""):
                                    note_type=2
                                    note = "曠職 "+holiday.GetName()
                    # --- 計算假日(若無打卡資料則為假日或曠職) ---
                    thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                    theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                    theWeek = theDay.weekday()
                    if(str(theWeek)=="5" or str(theWeek)=="6"):
                        if(note==""):
                            note = "假日"
                    else:
                        if(note==""):
                            note = "曠職"
                    sheet[ID].set_row(count[ID], 24)
                    sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                    sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                    if(note=="假日" or note_type==1):
                        sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                        sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                    else:
                        sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                        sheet[ID].write_string(count[ID], 6, note, cell_format_with_border_red)
                    count[ID] = count[ID] + 1
                    day[ID] = day[ID] + 1
                sheet[ID].set_row(count[ID], 24)
                # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                late = (i2 - late).total_seconds() if i2 > late else 0
                earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                note = ""
                if(late > 0):
                    # 遲到
                    note += "遲到 "
                if(earlier > 0):
                    # 早退
                    note += "早退"
                # ------- 2020 08 07 Alle 新增結尾 --------
                # --- 標示加班 ---
                o_day = str(first.year)+"/"+str(first.month).zfill(2)+"/"+str(day[ID]).zfill(2)
                check_o=0
                if(o6!=0):
                    check_o=1
                    for overtime in overtime_all:
                        if(ID==overtime.GetPersonId() and o_day==overtime.GetDate()):
                            check_o=0
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==True):
                            note += holiday.GetName()
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 1, o1, cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, o2, cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, o3, cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, o4, cell_format_with_border)
                if(check_o==0):
                    sheet[ID].write_number(count[ID], 5, o6, cell_format_with_border)
                else:
                    sheet[ID].write_number(count[ID], 5, o6, cell_format_with_border_red)
                sheet[ID].write_string(count[ID], 6, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
        # --- 判斷閏年、月份天數 ---
        if((first.year%4==0 and first.year%100!=0) or first.year%400==0):
            monthday = [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        else:
            monthday = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        for p in person:
            ID = p.GetID()
            for x in range(day[ID], monthday[first.month-1]+1):
                note=""
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                note_type=0
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==False):
                            note_type=1
                            note = holiday.GetName()
                        else:
                            if(note==""):
                                note_type=2
                                note = "曠職 "+holiday.GetName()
                # --- 計算假日(若無打卡資料則為假日或曠職) ---
                thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                theWeek = theDay.weekday()
                if(str(theWeek)=="5" or str(theWeek)=="6"):
                    if(note==""):
                        note = "假日"
                else:
                    if(note==""):
                        note = "曠職"
                sheet[ID].set_row(count[ID], 24)
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                if(note=="假日" or note_type==1):
                    sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                    sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                else:
                    sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 6, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
        writer.close()
        stream.seek(0)
        return stream
    
# ========== 單一群組匯出月份 ==========
class PunchCardRecordDownloadGroup(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
        self.parser.add_argument('GroupId')
    def post(self, year, month):
        args = self.parser.parse_args()
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        # Add 1911
        year = year if year > 1911 else year + 1911
        # Get the first day of this month and the first day of next month
        first = datetime(year, month, 1)
        last = datetime(year if 12 > month else year + 1, month + 1 if 12 > month else 1, 1)
        # Get group_person data
        group = PersonGroupRelationModel.AllPersonFromDB(args['GroupId'],self.log.SetMessage)
        group_pereson = []
        # Get person data
        person = PersonModel.AllFromDB(self.log.SetMessage)
        for p in person:
            for g in group:
                if(p.GetID()==g.GetPersonID()):
                    group_pereson.append(p)
        stream = PunchCardRecordDownloadGroup.GetExcel(self, group_pereson, first, last, inHour, inMinute, outHour, outMinute, lunchBreak)
        year = str(year)
        month = str(month).zfill(2)
        return self.GetExcelResponse("{0}-{1}".format(year, month), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, outHour, outMinute, lunchBreak):
        record = PunchCardRecord.GetTable(first, last, person)
        # create excel
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
#         column_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bold': True, 'font_size': 16 })
#         row_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_center = book.add_format({ 'align': 'center', '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'})
#         column1 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column2 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column3 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column4 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column5 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
#         column6 = book.add_format({ 'align': 'center', 'valign': 'vcenter', 'bg_color': '#f4cccc' })
        sheet = {}
        count = {}
        day = {}
        for p in person:
            sheet[p.GetID()] = book.add_worksheet(p.GetName())
            sheet[p.GetID()].merge_range('A1:G1', "{0}年{1}月份   出勤表".format(first.year-1911, first.month), cell_format_center)
            sheet[p.GetID()].write_string(1, 0, "員工編號", cell_format)
            sheet[p.GetID()].write_string(1, 1, p.GetID(), cell_format)
            sheet[p.GetID()].write_string(1, 2, "員工姓名", cell_format)
            sheet[p.GetID()].write_string(1, 3, p.GetName(), cell_format)
            sheet[p.GetID()].merge_range('A4:A5', "出勤日期", cell_format_with_border)
            sheet[p.GetID()].merge_range('B4:C4', "出勤時間", cell_format_with_border)
            sheet[p.GetID()].merge_range('D4:F4', "加班", cell_format_with_border)
            sheet[p.GetID()].merge_range('G4:G5', "備註", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 1, "上班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 2, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 3, "上班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 4, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 5, "時數", cell_format_with_border)
            sheet[p.GetID()].set_column('A:A', 13)
            sheet[p.GetID()].set_column('B:B', 13)
            sheet[p.GetID()].set_column('C:C', 13)
            sheet[p.GetID()].set_column('D:D', 13)
            sheet[p.GetID()].set_column('E:E', 13)
            sheet[p.GetID()].set_column('F:F', 10)
            sheet[p.GetID()].set_column('G:G', 20)
            sheet[p.GetID()].set_row(0, 24)
            sheet[p.GetID()].set_row(1, 24)
            sheet[p.GetID()].set_row(3, 24)
            sheet[p.GetID()].set_row(4, 24)
            count[p.GetID()] = 5
            day[p.GetID()] = 1
        #---------- holiday ----------
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        for ID in record.keys():
            for dateString in record[ID].keys():
                i1 = datetime.strptime(dateString, '%Y-%m-%d')
                i1 = i1 + timedelta(hours=12)
                i2 = record[ID][dateString]['Start']['Time']
                i2 = "{0} {1}".format(dateString, i2)
                i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                i3 = record[ID][dateString]['End']['Time']
                i3 = "{0} {1}".format(dateString, i3)
                i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                i4 = inHour
                i5 = inMinute
                i6 = outHour
                i7 = outMinute
                i8 = lunchBreak
                # start, end, startOver, endOver, work, over
                o1, o2, o3, o4, o5, o6 = PunchCardRecord.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                o1 = o1.strftime('%H:%M:%S')
                o2 = o2.strftime('%H:%M:%S')
                o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""

                for x in range(day[ID], i1.day):
                    note=""
                    # --- 註記請假資料 ---
                    leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                    for leave in leave_all:
                        leave_start=leave.GetStart()[0:10]
                        StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                        leave_end=leave.GetEnd()[0:10]
                        EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                        ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                        if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                            note = leave.GetCategory()
                    # --- 取得 holiday資料註記 ---
                    note_type=0
                    for holiday in holidays_all:
                        if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                            if(holiday.GetIsWork()==False):
                                note_type=1
                                note = holiday.GetName()
                            else:
                                if(note==""):
                                    note_type=2
                                    note = "曠職 "+holiday.GetName()
                    # --- 計算假日(若無打卡資料則為假日或曠職) ---
                    thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                    theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                    theWeek = theDay.weekday()
                    if(str(theWeek)=="5" or str(theWeek)=="6"):
                        if(note==""):
                            note = "假日"
                    else:
                        if(note==""):
                            note = "曠職"
                    sheet[ID].set_row(count[ID], 24)
                    sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                    sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                    if(note=="假日" or note_type==1):
                        sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                        sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                    else:
                        sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                        sheet[ID].write_string(count[ID], 6, note, cell_format_with_border_red)
                    count[ID] = count[ID] + 1
                    day[ID] = day[ID] + 1
                sheet[ID].set_row(count[ID], 24)
                # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                late = (i2 - late).total_seconds() if i2 > late else 0
                earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                note = ""
                if(late > 0):
                    # 遲到
                    note += "遲到 "
                if(earlier > 0):
                    # 早退
                    note += "早退 "
                # ------- 2020 08 07 Alle 新增結尾 --------
                # --- 標示加班 ---
                o_day = str(first.year)+"/"+str(first.month).zfill(2)+"/"+str(day[ID]).zfill(2)
                check_o=0
                if(o6!=0):
                    check_o=1
                    for overtime in overtime_all:
                        if(ID==overtime.GetPersonId() and o_day==overtime.GetDate()):
                            check_o=0
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==True):
                            note += holiday.GetName()
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 1, o1, cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, o2, cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, o3, cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, o4, cell_format_with_border)
                if(check_o==0):
                    sheet[ID].write_number(count[ID], 5, o6, cell_format_with_border)
                else:
                    sheet[ID].write_number(count[ID], 5, o6, cell_format_with_border_red)
                sheet[ID].write_string(count[ID], 6, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
        # --- 判斷閏年、月份天數 ---
        if((first.year%4==0 and first.year%100!=0) or first.year%400==0):
            monthday = [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        else:
            monthday = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        for p in person:
            ID = p.GetID()
            for x in range(day[ID], monthday[first.month-1]+1):
                note=""
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                note_type=0
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==False):
                            note_type=1
                            note = holiday.GetName()
                        else:
                            if(note==""):
                                note_type=2
                                note = "曠職 "+holiday.GetName()
                # --- 計算假日(若無打卡資料則為假日或曠職) ---
                thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                theWeek = theDay.weekday()
                if(str(theWeek)=="5" or str(theWeek)=="6"):
                    if(note==""):
                        note = "假日"
                else:
                    if(note==""):
                        note = "曠職"
                sheet[ID].set_row(count[ID], 24)
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                if(note=="假日" or note_type==1):
                    sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                    sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                else:
                    sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 6, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
        writer.close()
        stream.seek(0)
        return stream
# ========== 全部人單日匯出 ==========
class PunchCardRecordDownload2(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('Start')
        self.parser.add_argument('End')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
    def post(self):
        args = self.parser.parse_args()
        dt_from=datetime.strptime(str(args['Start']),"%Y-%m-%d %H:%M:%S")
        dt_to=datetime.strptime(str(args['End']),"%Y-%m-%d %H:%M:%S")
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        person = PersonModel.AllFromDB(self.log.SetMessage)
        stream = PunchCardRecordDownload2.GetExcel(self, person, dt_from, dt_to, inHour, inMinute, outHour, outMinute, lunchBreak)
        return self.GetExcelResponse("{0}至{1}溫度統計".format(args['Start'], args['End']), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, outHour, outMinute, lunchBreak):
        record = PunchCardRecord.GetFullTable(first, last,inHour,inMinute,outHour,outMinute,lunchBreak, person)# create excel
        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_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 = {}
        count = {}
        day = {}
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        if(len(person)>0):
            if(person[0].GetID() in record):
                for d in record[person[0].GetID()].keys():
                    sheet[d] = book.add_worksheet(d)
                    sheet[d].merge_range('A1:K1', "{}年{}月{}日  出勤表".format(d.split('-')[0], d.split('-')[1],d.split('-')[2]), cell_format)
                    sheet[d].merge_range('A2:A3', "編號", cell_format_with_border)
                    sheet[d].merge_range('B2:B3', "姓名", cell_format_with_border)
                    sheet[d].merge_range('C2:D2', "簽到", cell_format_with_border)
                    sheet[d].merge_range('E2:F2', "簽退", cell_format_with_border)
                    sheet[d].merge_range('G2:G3', "遲到", cell_format_with_border)
                    sheet[d].merge_range('H2:H3', "早退", cell_format_with_border)
                    sheet[d].merge_range('I2:I3', "加班", cell_format_with_border)
                    sheet[d].merge_range('J2:J3', "當日最高溫", cell_format_with_border)
                    sheet[d].merge_range('K2:K3', "備註", cell_format_with_border)
                    sheet[d].write_string(2, 2, "時間", cell_format_with_border)
                    sheet[d].write_string(2, 3, "溫度", cell_format_with_border)
                    sheet[d].write_string(2, 4, "時間", cell_format_with_border)
                    sheet[d].write_string(2, 5, "溫度", cell_format_with_border)
                    sheet[d].set_column('A:A', 13)
                    sheet[d].set_column('B:B', 13)
                    sheet[d].set_column('C:C', 13)
                    sheet[d].set_column('D:D', 10)
                    sheet[d].set_column('E:E', 13)
                    sheet[d].set_column('F:F', 10)
                    sheet[d].set_column('G:G', 10)
                    sheet[d].set_column('H:H', 10)
                    sheet[d].set_column('I:I', 10)
                    sheet[d].set_column('J:J', 15)
                    sheet[d].set_column('K:K', 20)
                    count[d] = 3
                for p in person:
                    for dateString in record[p.GetID()].keys():
                        maxtemp=record[p.GetID()][dateString]["MaxTemperature"] if "MaxTemperature" in record[p.GetID()][dateString] else ""
                        note_check=0
                        note=""
                        this_day = str(dateString.split('-')[0])+"/"+dateString.split('-')[1]+"/"+dateString.split('-')[2]
                        ThisDay = datetime.strptime(this_day, "%Y/%m/%d")
                        # --- 註記請假資料 ---
                        for leave in leave_all:
                            leave_start=leave.GetStart()[0:10]
                            StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                            leave_end=leave.GetEnd()[0:10]
                            EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                            if(p.GetID()==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                                note_check=1
                                note = leave.GetCategory()
                        # --- 計算假日 ---
                        theWeek = ThisDay.weekday()
                        if(str(theWeek)=="5" or str(theWeek)=="6"):
                            note_check=1
                            note = "假日"
                        # --- 取得 holiday資料註記 ---
                        for holiday in holidays_all:
                            holiday_this=str(int(holiday.GetYear())+1911)+"/"+holiday.GetMonth()+"/"+holiday.GetDay()
                            if(holiday_this==this_day):
                                if(holiday.GetIsWork()==False):
                                    note_check=1
                                    note = holiday.GetName()
                                else:
                                    note = holiday.GetName()
                        if(note_check==0):
                            note="曠職"
                        sheet[dateString].write_string(count[dateString], 0, p.GetID(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 1, p.GetName(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 2, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 3, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 4, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 5, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 6, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 7, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 8, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 9, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 10, note, cell_format_with_border)
                        if(record[p.GetID()][dateString]["Start"]["Time"]!="" and record[p.GetID()][dateString]["End"]["Time"]!=""):
                            i1 = datetime.strptime(dateString, '%Y-%m-%d')
                            i1 = i1 + timedelta(hours=12)
                            i2 = record[p.GetID()][dateString]['Start']['Time']
                            i2 = "{0} {1}".format(dateString, i2)
                            i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                            i3 = record[p.GetID()][dateString]['End']['Time']
                            i3 = "{0} {1}".format(dateString, i3)
                            i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                            i4 = inHour
                            i5 = inMinute
                            i6 = outHour
                            i7 = outMinute
                            i8 = lunchBreak
                            # start, end, startOver, endOver, work, over
                            o1, o2, o3, o4, o5, o6 = PunchCardRecord.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                            o1 = o1.strftime('%H:%M:%S')
                            o2 = o2.strftime('%H:%M:%S')
                            o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                            o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""
                            # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                            late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                            late = (i2 - late).total_seconds() if i2 > late else 0
                            earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                            earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
#                             note=""
#                             if(late > 0):
#                                 # 遲到
#                                 note += "遲到 "
#                             if(earlier > 0):
#                                 # 早退
#                                 note += "早退"
                            # --- 標示加班 ---
                            check_o=0
                            if(o6!=0):
                                check_o=1
                                for overtime in overtime_all:
                                    if(p.GetID()==overtime.GetPersonId() and this_day==overtime.GetDate()):
                                        check_o=0
                            if(note_check==0):
                                note=""
                            sheet[dateString].write_string(count[dateString], 2, record[p.GetID()][dateString]["Start"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 3, str(record[p.GetID()][dateString]["Start"]["Temperature"]), cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 4, record[p.GetID()][dateString]["End"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 5, str(record[p.GetID()][dateString]["End"]["Temperature"]), cell_format_with_border)
                            if(late > 0):
                                sheet[dateString].write_string(count[dateString], 6, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 6, "", cell_format_with_border)
                            if(earlier > 0):
                                sheet[dateString].write_string(count[dateString], 7, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 7, "", cell_format_with_border)
                            if(check_o==0):
                                sheet[dateString].write_number(count[dateString], 8, o6, cell_format_with_border)
                            else:
                                sheet[dateString].write_number(count[dateString], 8, o6, cell_format_with_border_red)
                            sheet[dateString].write_string(count[dateString], 9, str(maxtemp) , cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 10, note , cell_format_with_border)
                        count[dateString]+=1
        writer.close()
        stream.seek(0)
        return stream
# ========== 單日匯出單一群組 ==========
class PunchCardRecordDownload2Group(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('Start')
        self.parser.add_argument('End')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
        self.parser.add_argument('GroupId')
    def post(self):
        args = self.parser.parse_args()
        dt_from=datetime.strptime(str(args['Start']),"%Y-%m-%d %H:%M:%S")
        dt_to=datetime.strptime(str(args['End']),"%Y-%m-%d %H:%M:%S")
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        # Get group_person data
        group = PersonGroupRelationModel.AllPersonFromDB(args['GroupId'],self.log.SetMessage)
        group_pereson = []
        # Get person data
        person = PersonModel.AllFromDB(self.log.SetMessage)
        for p in person:
            for g in group:
                if(p.GetID()==g.GetPersonID()):
                    group_pereson.append(p)
        stream = PunchCardRecordDownload2Group.GetExcel(self, group_pereson, dt_from, dt_to, inHour, inMinute, outHour, outMinute, lunchBreak)
        return self.GetExcelResponse("{0}至{1}溫度統計".format(args['Start'], args['End']), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, outHour, outMinute, lunchBreak):
        record = PunchCardRecord.GetFullTable(first, last,inHour,inMinute,outHour,outMinute,lunchBreak, person)# create excel
        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_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 = {}
        count = {}
        day = {}
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        if(len(person)>0):
            if(person[0].GetID() in record):
                for d in record[person[0].GetID()].keys():
                    sheet[d] = book.add_worksheet(d)
                    sheet[d].merge_range('A1:K1', "{}年{}月{}日  出勤表".format(d.split('-')[0], d.split('-')[1],d.split('-')[2]), cell_format)
                    sheet[d].merge_range('A2:A3', "編號", cell_format_with_border)
                    sheet[d].merge_range('B2:B3', "姓名", cell_format_with_border)
                    sheet[d].merge_range('C2:D2', "簽到", cell_format_with_border)
                    sheet[d].merge_range('E2:F2', "簽退", cell_format_with_border)
                    sheet[d].merge_range('G2:G3', "遲到", cell_format_with_border)
                    sheet[d].merge_range('H2:H3', "早退", cell_format_with_border)
                    sheet[d].merge_range('I2:I3', "加班", cell_format_with_border)
                    sheet[d].merge_range('J2:J3', "當日最高溫", cell_format_with_border)
                    sheet[d].merge_range('K2:K3', "備註", cell_format_with_border)
                    sheet[d].write_string(2, 2, "時間", cell_format_with_border)
                    sheet[d].write_string(2, 3, "溫度", cell_format_with_border)
                    sheet[d].write_string(2, 4, "時間", cell_format_with_border)
                    sheet[d].write_string(2, 5, "溫度", cell_format_with_border)
                    sheet[d].set_column('A:A', 13)
                    sheet[d].set_column('B:B', 13)
                    sheet[d].set_column('C:C', 13)
                    sheet[d].set_column('D:D', 10)
                    sheet[d].set_column('E:E', 13)
                    sheet[d].set_column('F:F', 10)
                    sheet[d].set_column('G:G', 10)
                    sheet[d].set_column('H:H', 10)
                    sheet[d].set_column('I:I', 10)
                    sheet[d].set_column('J:J', 15)
                    sheet[d].set_column('K:K', 20)
                    count[d] = 3
                for p in person:
                    for dateString in record[p.GetID()].keys():
                        maxtemp=record[p.GetID()][dateString]["MaxTemperature"] if "MaxTemperature" in record[p.GetID()][dateString] else ""
                        note_check=0
                        note=""
                        this_day = str(dateString.split('-')[0])+"/"+dateString.split('-')[1]+"/"+dateString.split('-')[2]
                        ThisDay = datetime.strptime(this_day, "%Y/%m/%d")
                        # --- 註記請假資料 ---
                        for leave in leave_all:
                            leave_start=leave.GetStart()[0:10]
                            StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                            leave_end=leave.GetEnd()[0:10]
                            EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                            if(p.GetID()==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                                note_check=1
                                note = leave.GetCategory()
                        # --- 計算假日 ---
                        theWeek = ThisDay.weekday()
                        if(str(theWeek)=="5" or str(theWeek)=="6"):
                            note_check=1
                            note = "假日"
                        # --- 取得 holiday資料註記 ---
                        for holiday in holidays_all:
                            holiday_this=str(int(holiday.GetYear())+1911)+"/"+holiday.GetMonth()+"/"+holiday.GetDay()
                            if(holiday_this==this_day):
                                if(holiday.GetIsWork()==False):
                                    note_check=1
                                    note = holiday.GetName()
                                else:
                                    note = holiday.GetName()
                        if(note_check==0):
                            note="曠職"
                        sheet[dateString].write_string(count[dateString], 0, p.GetID(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 1, p.GetName(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 2, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 3, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 4, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 5, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 6, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 7, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 8, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 9, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 10, note, cell_format_with_border)
                        if(record[p.GetID()][dateString]["Start"]["Time"]!="" and record[p.GetID()][dateString]["End"]["Time"]!=""):
                            i1 = datetime.strptime(dateString, '%Y-%m-%d')
                            i1 = i1 + timedelta(hours=12)
                            i2 = record[p.GetID()][dateString]['Start']['Time']
                            i2 = "{0} {1}".format(dateString, i2)
                            i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                            i3 = record[p.GetID()][dateString]['End']['Time']
                            i3 = "{0} {1}".format(dateString, i3)
                            i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                            i4 = inHour
                            i5 = inMinute
                            i6 = outHour
                            i7 = outMinute
                            i8 = lunchBreak
                            # start, end, startOver, endOver, work, over
                            o1, o2, o3, o4, o5, o6 = PunchCardRecord.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                            o1 = o1.strftime('%H:%M:%S')
                            o2 = o2.strftime('%H:%M:%S')
                            o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                            o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""
                            # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                            late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                            late = (i2 - late).total_seconds() if i2 > late else 0
                            earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                            earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
#                             note=""
#                             if(late > 0):
#                                 # 遲到
#                                 note += "遲到 "
#                             if(earlier > 0):
#                                 # 早退
#                                 note += "早退"
                            # --- 標示加班 ---
                            check_o=0
                            if(o6!=0):
                                check_o=1
                                for overtime in overtime_all:
                                    if(p.GetID()==overtime.GetPersonId() and this_day==overtime.GetDate()):
                                        check_o=0
                            if(note_check==0):
                                note=""
                            sheet[dateString].write_string(count[dateString], 2, record[p.GetID()][dateString]["Start"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 3, str(record[p.GetID()][dateString]["Start"]["Temperature"]), cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 4, record[p.GetID()][dateString]["End"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 5, str(record[p.GetID()][dateString]["End"]["Temperature"]), cell_format_with_border)
                            if(late > 0):
                                sheet[dateString].write_string(count[dateString], 6, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 6, "", cell_format_with_border)
                            if(earlier > 0):
                                sheet[dateString].write_string(count[dateString], 7, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 7, "", cell_format_with_border)
                            if(check_o==0):
                                sheet[dateString].write_number(count[dateString], 8, o6, cell_format_with_border)
                            else:
                                sheet[dateString].write_number(count[dateString], 8, o6, cell_format_with_border_red)
                            sheet[dateString].write_string(count[dateString], 9, str(maxtemp) , cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 10, note , cell_format_with_border)
                        count[dateString]+=1
        writer.close()
        stream.seek(0)
        return stream
    
# ========== *新版本-中午休息有打卡 ==========
class PunchCardRecordVerson2(BaseResource):
    def __init__(self):
        super().__init__("SelectRecordOfPunchCard")
        self.parser.add_argument('personID', help="上限 10 字")
        self.parser.add_argument('start', required=True, help="yyyy-MM-dd HH:mm:ss")
        self.parser.add_argument('end', required=True, help="yyyy-MM-dd HH:mm:ss")
        self.parser.add_argument('inHour', required=True, help="0~23")
        self.parser.add_argument('inMinute', required=True, help="0~59")
        self.parser.add_argument('inHour2', required=True, help="0~23")
        self.parser.add_argument('inMinute2', required=True, help="0~59")
        self.parser.add_argument('outHour', required=True, help="0~23")
        self.parser.add_argument('outMinute', required=True, help="0~59")
        self.parser.add_argument('lunchBreak', required=True)
    def post(self):
        args = self.parser.parse_args()
        personID = args['personID']
        try:
            start = datetime.strptime(args['start'], '%Y-%m-%d %H:%M:%S')
            try:
                end = datetime.strptime(args['end'], '%Y-%m-%d %H:%M:%S')
                try:
                    inHour_2=int(args['inHour2'])
                    inMinute_2 = int(args['inMinute2'])
                    i1 = int(args['inHour'])
                    i2 = int(args['inMinute'])
                    i3 = int(args['outHour'])
                    i4 = int(args['outMinute'])
                    i5 = int(args['lunchBreak'])
                    models = PersonModel.AllFromDB(self.log.SetMessage)
                    if len(models) > 0:
                        self.data = PunchCardRecordVerson2.GetFullTable(start, end, i1, i2, inHour_2, inMinute_2, i3, i4, i5, models, personID)
                        if personID is not None:
                            self.message = "{0} has {1} selected".format(personID, len(self.data[personID]))
                        else:
                            self.message = "{0} has {1} selected".format(len(self.data), np.sum([ len(self.data[x]) for x in self.data ]))
                    else:
                        self.message = "{0} isn't exist data in database".format(personID)
                except:
                    self.message = "parameters error"
            except:
                self.message = "the format of end time {0} isn't match".format(args['end'])
        except:
            self.message = "the format of start time {0} isn't match".format(args['start'])
        return self.GetResponse()
    @staticmethod    #取得時間區間內每天的的打卡上班時間、打卡下班時間
    def GetTable(start, end, person, temperature=None):
        table = {}
        for p in person:
            table[p.GetID()] = {}
        data = PunchInfluxModel.AllFromRaw(start, end, temperature)    #data是那段時間內所有的打卡資料
        for row in data:
            ID = row.GetID()
            if ID in table:
                time = row.GetTime()
                date = time.strftime('%Y-%m-%d')
                if date not in table[ID]:
                    table[ID][date] = {}
                time = time.strftime('%H:%M:%S')
                image = row.GetImage().split('.')[0].split('/')[2]
                # 計算當日最高溫
                if 'MaxTemperature' not in table[ID][date]:
                    table[ID][date]['MaxTemperature']=row.GetTemperature()
                else:
                    table[ID][date]['MaxTemperature']=row.GetTemperature() if row.GetTemperature()> table[ID][date]['MaxTemperature'] else table[ID][date]['MaxTemperature']
                if 'Start' not in table[ID][date]:    #將第一筆資料設為那天的打卡上班時間
                    table[ID][date]['Start'] = {}
                    table[ID][date]['Start']['Time'] = time
                    table[ID][date]['Start']['Image'] = image
                    table[ID][date]['Start']['Temperature'] = row.GetTemperature()
                if 'Start_2' not in table[ID][date]:
                    checkTime = " 13:00:00"
                    check=datetime.strptime(date+checkTime, '%Y-%m-%d %H:%M:%S').replace(tzinfo=tw)
                    row_time_dt=row.GetTime()
                    if(row_time_dt>check):
                        table[ID][date]['Start_2'] = {}
                        table[ID][date]['Start_2']['Time'] = time
                        table[ID][date]['Start_2']['Image'] = image
                        table[ID][date]['Start_2']['Temperature'] = row.GetTemperature()
                table[ID][date]['End'] = {}
                table[ID][date]['End']['Time'] = time    #將資料設為那天的打卡下班時間，每一筆都會設，執行到最後就會是最後一筆變成下班時間
                table[ID][date]['End']['Image'] = image
                table[ID][date]['End']['Temperature'] = row.GetTemperature()
        return table
    @staticmethod
    def GetFullTable(start, end, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak, person, personID=None):
        table = PunchCardRecordVerson2.GetTable(start, end, person)    #取得日期的打卡上班、下班
        table = { personID: table[personID] } if personID is not None else table
        date = {}
        for ID in table.keys():
            dateKey = list(table[ID].keys())
            for dateString in dateKey:
                i1 = datetime.strptime(dateString, '%Y-%m-%d')  # i1=datetime格式的日期
                i2 = table[ID][dateString]['Start']['Time']
                i2 = "{0} {1}".format(dateString, i2)
                i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')  # i2=datetime格式的日期+時間(打卡上班)
                i3 = table[ID][dateString]['End']['Time']
                i3 = "{0} {1}".format(dateString, i3)
                i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')  # i2=datetime格式的日期+時間(打卡下班)
                i4 = inHour
                i5 = inMinute  # i4:i5=8:30(上班時間)
                i6 = outHour
                i7 = outMinute  # i6:i7=17:30(下班時間)
                i8 = lunchBreak  # i8=午休(60分鐘)
                if ID not in date:
                    date[ID] = start
                while i1 > date[ID] and end > i1:  #這個while是在補有打卡紀錄之前的資料變成空的
                    tmp = date[ID].strftime('%Y-%m-%d')
                    table[ID][tmp] = {}
                    table[ID][tmp]['Start'] = {}
                    table[ID][tmp]['Start']['Image'] = ""
                    table[ID][tmp]['Start']['Temperature'] = 0.0
                    table[ID][tmp]['Start']['Time'] = ""
                    table[ID][tmp]['Start_2'] = {}
                    table[ID][tmp]['Start_2']['Image'] = ""
                    table[ID][tmp]['Start_2']['Temperature'] = 0.0
                    table[ID][tmp]['Start_2']['Time'] = ""
                    table[ID][tmp]['End'] = {}
                    table[ID][tmp]['End']['Image'] = ""
                    table[ID][tmp]['End']['Temperature'] = 0.0
                    table[ID][tmp]['End']['Time'] = ""
                    table[ID][tmp]['Over'] = 0.0
                    table[ID][tmp]['WorkBeLate'] = 0
                    table[ID][tmp]['WorkBeLate_2'] = 0
                    table[ID][tmp]['WorkLeaveEarlier'] = 0
                    date[ID] = date[ID] + timedelta(days=1)
                date[ID] = date[ID] + timedelta(days=1)
                # o1:start(打卡上班時間), o2:end(上班結束 *正常為17:30), o3:startOver(開始加班 *正常為17:30), o4:endOver(結束加班 *打卡下班間), o5:work, o6:over(加班時數)
                o1, o2, o3, o4, o5, o6 = PunchCardRecordVerson2.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                late = datetime(i1.year, i1.month, i1.day, inHour, inMinute)
                late = (i2 - late).total_seconds() if i2 > late else 0
                # ===== 下午的遲到處理 =====
                if 'Start_2' not in table[ID][dateString]:
                    table[ID][dateString]['Start_2'] = {}
                    table[ID][dateString]['Start_2']['Image'] = ""
                    table[ID][dateString]['Start_2']['Temperature'] = 0.0
                    table[ID][dateString]['Start_2']['Time'] = ""
                    late_2 = 0
                else:
                    punch_in_2 = table[ID][dateString]['Start_2']['Time']
                    punch_in_2 = "{0} {1}".format(dateString, punch_in_2)
                    punch_in_2 = datetime.strptime(punch_in_2, '%Y-%m-%d %H:%M:%S')  # punch_in_2=datetime格式的日期+時間(打卡上班-下午)
                    late_2 = datetime(i1.year, i1.month, i1.day, inHour2, inMinute2)
                    late_2 = (punch_in_2 - late_2).total_seconds() if punch_in_2 > late_2 else 0
                earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                o1 = o1.strftime('%H:%M:%S')
                o2 = o2.strftime('%H:%M:%S') if o1 != o2 else ""
                o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                o4 = o4.strftime('%H:%M:%S') if o4 is not None and o3 != o4 else ""
                table[ID][dateString]['WorkBeLate'] = int(late / 60)
                table[ID][dateString]['WorkBeLate_2'] = int(late_2 / 60)
                table[ID][dateString]['WorkLeaveEarlier'] = int(earlier / 60) if o1 != o2 else 0
                table[ID][dateString]['Over'] = o6
            if ID not in date:
                date[ID] = start
            while end > date[ID]:
                tmp = date[ID].strftime('%Y-%m-%d')
                table[ID][tmp] = {}
                table[ID][tmp]['Start'] = {}
                table[ID][tmp]['Start']['Image'] = ""
                table[ID][tmp]['Start']['Temperature'] = 0.0
                table[ID][tmp]['Start']['Time'] = ""
                table[ID][tmp]['Start_2'] = {}
                table[ID][tmp]['Start_2']['Image'] = ""
                table[ID][tmp]['Start_2']['Temperature'] = 0.0
                table[ID][tmp]['Start_2']['Time'] = ""
                table[ID][tmp]['End'] = {}
                table[ID][tmp]['End']['Image'] = ""
                table[ID][tmp]['End']['Temperature'] = 0.0
                table[ID][tmp]['End']['Time'] = ""
                table[ID][tmp]['Over'] = 0.0
                table[ID][tmp]['WorkBeLate'] = 0
                table[ID][tmp]['WorkBeLate_2'] = 0
                table[ID][tmp]['WorkLeaveEarlier'] = 0
                date[ID] = date[ID] + timedelta(days=1)
        return table
    @staticmethod
    def CalculateTime(date, start, end, inHour, inMinute, outHour, outMinute, lunchBreak, workBuffer, overBuffer):
        work = 0.0
        over = 0.0
        startWork = start
        # endWork = end
        startOver = None
        endOver = None
        if (inHour > start.hour) or (start.hour == inHour and inMinute > start.minute):
            start = datetime(date.year, date.month, date.day, inHour, inMinute+workBuffer)
        over = datetime(date.year, date.month, date.day, outHour, outMinute)
        if start > over:
            over = datetime(date.year, date.month, date.day, start.hour, start.minute, start.second)
        if over > end:
            over = 0
        else:
            over = (end - over - timedelta(minutes=overBuffer)).total_seconds() / 60 # second -> minute
            # over = (end - over).total_seconds() / 60
            if over > 0:
                startOver = datetime(date.year, date.month, date.day, outHour, outMinute+overBuffer)
                endOver = end
                end = datetime(date.year, date.month, date.day, outHour, outMinute+overBuffer)
        if end > start:
            work = (end - start).total_seconds() / 60 # second -> minute
        if end >= date and date >= start:
            work = work - lunchBreak
        endWork = end
        return startWork, endWork, startOver, endOver, round(work / 60), math.floor(over / 30) / 2
        # return startWork, endWork, startOver, endOver, round(work / 60), over
    
# ========== *新版本-全部人匯出月份 ==========
class PunchCardRecordDownloadVerson2(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('InHour2')
        self.parser.add_argument('InMinute2')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
    def post(self, year, month):
        args = self.parser.parse_args()
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch In Time in the afternoon
        inHour2 = int(args['InHour2'])
        inMinute2 = int(args['InMinute2'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        # Add 1911
        year = year if year > 1911 else year + 1911
        # Get the first day of this month and the first day of next month
        first = datetime(year, month, 1)
        last = datetime(year if 12 > month else year + 1, month + 1 if 12 > month else 1, 1)
        # Get person data
        person = PersonModel.AllFromDB(self.log.SetMessage)
        stream = PunchCardRecordDownloadVerson2.GetExcel(self, person, first, last, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak)
        year = str(year)
        month = str(month).zfill(2)
        return self.GetExcelResponse("{0}-{1}".format(year, month), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak):
        record = PunchCardRecordVerson2.GetTable(first, last, person)
        # create excel
        stream = BytesIO()
        writer = pd.ExcelWriter(stream, engine='xlsxwriter')
        book = writer.book
        cell_format = book.add_format({ 'align': 'left', 'valign': 'vcenter', 'font_size': 14 })
        cell_format_center = book.add_format({ 'align': 'center', '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 = {}
        count = {}
        day = {}
        late_count = {}
        for p in person:
            late_count[p.GetID()]={'late1':0,'late2':0}
            sheet[p.GetID()] = book.add_worksheet(p.GetName())
            sheet[p.GetID()].merge_range('A1:H1', "{0}年{1}月份   出勤表".format(first.year-1911, first.month), cell_format_center)
            sheet[p.GetID()].write_string(1, 0, "員工編號", cell_format)
            sheet[p.GetID()].write_string(1, 1, p.GetID(), cell_format)
            sheet[p.GetID()].write_string(1, 2, "員工姓名", cell_format)
            sheet[p.GetID()].write_string(1, 3, p.GetName(), cell_format)
            sheet[p.GetID()].merge_range('A4:A5', "出勤日期", cell_format_with_border)
            sheet[p.GetID()].merge_range('B4:D4', "出勤時間", cell_format_with_border)
            sheet[p.GetID()].merge_range('E4:G4', "加班", cell_format_with_border)
            sheet[p.GetID()].merge_range('H4:H5', "備註", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 1, "上班時間(上午)", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 2, "上班時間(下午)", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 3, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 4, "上班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 5, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 6, "時數", cell_format_with_border)
            sheet[p.GetID()].set_column('A:A', 13)
            sheet[p.GetID()].set_column('B:B', 20)
            sheet[p.GetID()].set_column('C:C', 20)
            sheet[p.GetID()].set_column('D:D', 13)
            sheet[p.GetID()].set_column('E:E', 13)
            sheet[p.GetID()].set_column('F:F', 13)
            sheet[p.GetID()].set_column('G:G', 10)
            sheet[p.GetID()].set_column('H:H', 40)
            sheet[p.GetID()].set_row(0, 24)
            sheet[p.GetID()].set_row(1, 24)
            sheet[p.GetID()].set_row(3, 24)
            sheet[p.GetID()].set_row(4, 24)
            count[p.GetID()] = 5
            day[p.GetID()] = 1
        #---------- holiday ----------
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        for ID in record.keys():
            for dateString in record[ID].keys():
                i1 = datetime.strptime(dateString, '%Y-%m-%d')
                i1 = i1 + timedelta(hours=12)
                i2 = record[ID][dateString]['Start']['Time']
                i2 = "{0} {1}".format(dateString, i2)
                i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                i3 = record[ID][dateString]['End']['Time']
                i3 = "{0} {1}".format(dateString, i3)
                i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                i4 = inHour
                i5 = inMinute
                i6 = outHour
                i7 = outMinute
                i8 = lunchBreak
                # start, end, startOver, endOver, work, over
                o1, o2, o3, o4, o5, o6 = PunchCardRecordVerson2.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                o1 = o1.strftime('%H:%M:%S')
                o2 = o2.strftime('%H:%M:%S')
                o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""
                for x in range(day[ID], i1.day):
                    note=""
                    # --- 註記請假資料 ---
                    leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                    for leave in leave_all:
                        leave_start=leave.GetStart()[0:10]
                        StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                        leave_end=leave.GetEnd()[0:10]
                        EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                        ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                        if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                            note = leave.GetCategory()
                    # --- 取得 holiday資料註記 ---
                    note_type=0
                    for holiday in holidays_all:
                        if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                            if(holiday.GetIsWork()==False):
                                note_type=1
                                note = holiday.GetName()
                            else:
                                if(note==""):
                                    note_type=2
                                    note = "曠職 "+holiday.GetName()
                    # --- 計算假日(若無打卡資料則為假日或曠職) ---
                    thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                    theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                    theWeek = theDay.weekday()
                    if(str(theWeek)=="5" or str(theWeek)=="6"):
                        if(note==""):
                            note = "假日"
                    else:
                        if(note==""):
                            note = "曠職"
                    sheet[ID].set_row(count[ID], 24)
                    sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                    sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                    if(note=="假日" or note_type==1):
                        sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                        sheet[ID].write_string(count[ID], 7, "", cell_format_with_border)
                    else:
                        sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                        sheet[ID].write_string(count[ID], 7, note, cell_format_with_border_red)
                    count[ID] = count[ID] + 1
                    day[ID] = day[ID] + 1
                sheet[ID].set_row(count[ID], 24)
                # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                late = (i2 - late).total_seconds() if i2 > late else 0
                earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                # ===== 下午的遲到處理 =====
                if 'Start_2' not in record[ID][dateString]:
                    record[ID][dateString]['Start_2'] = {}
                    record[ID][dateString]['Start_2']['Time'] = ""
                    record[ID][dateString]['Start_2']['Image'] = 0.0
                    record[ID][dateString]['Start_2']['Temperature'] = ""
                    late_2 = 0
                else:
                    punch_in_2 = record[ID][dateString]['Start_2']['Time']
                    punch_in_2 = "{0} {1}".format(dateString, punch_in_2)
                    punch_in_2 = datetime.strptime(punch_in_2, '%Y-%m-%d %H:%M:%S')  # punch_in_2=datetime格式的日期+時間(打卡上班-下午)
                    late_2 = datetime(i1.year, i1.month, i1.day, inHour2, inMinute2)
                    late_2 = (punch_in_2 - late_2).total_seconds() if punch_in_2 > late_2 else 0
                in_2=record[ID][dateString]['Start_2']['Time']
                note = ""
                if(late > 0):
                    # 遲到
                    note += "遲到(上午) "
                if(late_2 > 0):
                    # 遲到
                    note += "遲到(下午) "
                if(earlier > 0):
                    # 早退
                    note += "早退"
                # ------- 2020 08 07 Alle 新增結尾 --------
                # --- 標示加班 ---
                o_day = str(first.year)+"/"+str(first.month).zfill(2)+"/"+str(day[ID]).zfill(2)
                check_o=0
                if(o6!=0):
                    check_o=1
                    for overtime in overtime_all:
                        if(ID==overtime.GetPersonId() and o_day==overtime.GetDate()):
                            check_o=0
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==True):
                            note += holiday.GetName()
                if('遲到(上午)' in note):
                    late_count[ID]['late1']=late_count[ID]['late1']+1
                if('遲到(下午)' in note):
                    late_count[ID]['late2']=late_count[ID]['late2']+1
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 1, o1, cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, in_2, cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, o2, cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, o3, cell_format_with_border)
                sheet[ID].write_string(count[ID], 5, o4, cell_format_with_border)
                if(check_o==0):
                    sheet[ID].write_number(count[ID], 6, o6, cell_format_with_border)
                else:
                    sheet[ID].write_number(count[ID], 6, o6, cell_format_with_border_red)
                sheet[ID].write_string(count[ID], 7, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
        # --- 判斷閏年、月份天數 ---
        if((first.year%4==0 and first.year%100!=0) or first.year%400==0):
            monthday = [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        else:
            monthday = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        for p in person:
            ID = p.GetID()
            for x in range(day[ID], monthday[first.month-1]+1):
                note=""
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                note_type=0
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==False):
                            note_type=1
                            note = holiday.GetName()
                        else:
                            if(note==""):
                                note_type=2
                                note = "曠職 "+holiday.GetName()
                # --- 計算假日(若無打卡資料則為假日或曠職) ---
                thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                theWeek = theDay.weekday()
                if(str(theWeek)=="5" or str(theWeek)=="6"):
                    if(note==""):
                        note = "假日"
                else:
                    if(note==""):
                        note = "曠職"
                sheet[ID].set_row(count[ID], 24)
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                if(note=="假日" or note_type==1):
                    sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                    sheet[ID].write_string(count[ID], 7, "", cell_format_with_border)
                else:
                    sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 7, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
            late_note='遲到(上午)：'+str(late_count[ID]['late1'])+'，遲到(下午)：'+str(late_count[ID]['late2'])
            sheet[ID].write_string(count[ID], 7, late_note, cell_format_with_border_red)
        writer.close()
        stream.seek(0)
        return stream
# ========== *新版本-單一群組匯出月份 ==========
class PunchCardRecordDownloadGroupVerson2(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('InHour2')
        self.parser.add_argument('InMinute2')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
        self.parser.add_argument('GroupId')
    def post(self, year, month):
        args = self.parser.parse_args()
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch In Time in the afternoon
        inHour2 = int(args['InHour2'])
        inMinute2 = int(args['InMinute2'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        # Add 1911
        year = year if year > 1911 else year + 1911
        # Get the first day of this month and the first day of next month
        first = datetime(year, month, 1)
        last = datetime(year if 12 > month else year + 1, month + 1 if 12 > month else 1, 1)
        # Get group_person data
        group = PersonGroupRelationModel.AllPersonFromDB(args['GroupId'],self.log.SetMessage)
        group_pereson = []
        # Get person data
        person = PersonModel.AllFromDB(self.log.SetMessage)
        for p in person:
            for g in group:
                if(p.GetID()==g.GetPersonID()):
                    group_pereson.append(p)
        stream = PunchCardRecordDownloadGroupVerson2.GetExcel(self, group_pereson, first, last, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak)
        year = str(year)
        month = str(month).zfill(2)
        return self.GetExcelResponse("{0}-{1}".format(year, month), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak):
        record = PunchCardRecordVerson2.GetTable(first, last, person)
        # create excel
        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_center = book.add_format({ 'align': 'center', '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 = {}
        count = {}
        day = {}
        late_count = {}
        for p in person:
            late_count[p.GetID()]={'late1':0,'late2':0}
            sheet[p.GetID()] = book.add_worksheet(p.GetName())
            sheet[p.GetID()].merge_range('A1:H1', "{0}年{1}月份   出勤表".format(first.year-1911, first.month), cell_format_center)
            sheet[p.GetID()].write_string(1, 0, "員工編號", cell_format)
            sheet[p.GetID()].write_string(1, 1, p.GetID(), cell_format)
            sheet[p.GetID()].write_string(1, 2, "員工姓名", cell_format)
            sheet[p.GetID()].write_string(1, 3, p.GetName(), cell_format)
            sheet[p.GetID()].merge_range('A4:A5', "出勤日期", cell_format_with_border)
            sheet[p.GetID()].merge_range('B4:D4', "出勤時間", cell_format_with_border)
            sheet[p.GetID()].merge_range('E4:G4', "加班", cell_format_with_border)
            sheet[p.GetID()].merge_range('H4:H5', "備註", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 1, "上班時間(上午)", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 2, "上班時間(下午)", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 3, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 4, "上班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 5, "下班時間", cell_format_with_border)
            sheet[p.GetID()].write_string(4, 6, "時數", cell_format_with_border)
            sheet[p.GetID()].set_column('A:A', 13)
            sheet[p.GetID()].set_column('B:B', 20)
            sheet[p.GetID()].set_column('C:C', 20)
            sheet[p.GetID()].set_column('D:D', 13)
            sheet[p.GetID()].set_column('E:E', 13)
            sheet[p.GetID()].set_column('F:F', 13)
            sheet[p.GetID()].set_column('G:G', 10)
            sheet[p.GetID()].set_column('H:H', 40)
            sheet[p.GetID()].set_row(0, 24)
            sheet[p.GetID()].set_row(1, 24)
            sheet[p.GetID()].set_row(3, 24)
            sheet[p.GetID()].set_row(4, 24)
            count[p.GetID()] = 5
            day[p.GetID()] = 1
        #---------- holiday ----------
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        for ID in record.keys():
            for dateString in record[ID].keys():
                i1 = datetime.strptime(dateString, '%Y-%m-%d')
                i1 = i1 + timedelta(hours=12)
                i2 = record[ID][dateString]['Start']['Time']
                i2 = "{0} {1}".format(dateString, i2)
                i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                i3 = record[ID][dateString]['End']['Time']
                i3 = "{0} {1}".format(dateString, i3)
                i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                i4 = inHour
                i5 = inMinute
                i6 = outHour
                i7 = outMinute
                i8 = lunchBreak
                # start, end, startOver, endOver, work, over
                o1, o2, o3, o4, o5, o6 = PunchCardRecordVerson2.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                o1 = o1.strftime('%H:%M:%S')
                o2 = o2.strftime('%H:%M:%S')
                o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""

                for x in range(day[ID], i1.day):
                    note=""
                    # --- 註記請假資料 ---
                    leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                    for leave in leave_all:
                        leave_start=leave.GetStart()[0:10]
                        StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                        leave_end=leave.GetEnd()[0:10]
                        EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                        ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                        if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                            note = leave.GetCategory()
                    # --- 取得 holiday資料註記 ---
                    note_type=0
                    for holiday in holidays_all:
                        if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                            if(holiday.GetIsWork()==False):
                                note_type=1
                                note = holiday.GetName()
                            else:
                                if(note==""):
                                    note_type=2
                                    note = "曠職 "+holiday.GetName()
                    # --- 計算假日(若無打卡資料則為假日或曠職) ---
                    thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                    theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                    theWeek = theDay.weekday()
                    if(str(theWeek)=="5" or str(theWeek)=="6"):
                        if(note==""):
                            note = "假日"
                    else:
                        if(note==""):
                            note = "曠職"
                    sheet[ID].set_row(count[ID], 24)
                    sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                    sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                    if(note=="假日" or note_type==1):
                        sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                        sheet[ID].write_string(count[ID], 7, "", cell_format_with_border)
                    else:
                        sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                        sheet[ID].write_string(count[ID], 7, note, cell_format_with_border_red)
                    count[ID] = count[ID] + 1
                    day[ID] = day[ID] + 1
                sheet[ID].set_row(count[ID], 24)
                # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                late = (i2 - late).total_seconds() if i2 > late else 0
                earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                # ===== 下午的遲到處理 =====
                if 'Start_2' not in record[ID][dateString]:
                    record[ID][dateString]['Start_2'] = {}
                    record[ID][dateString]['Start_2']['Time'] = ""
                    record[ID][dateString]['Start_2']['Image'] = 0.0
                    record[ID][dateString]['Start_2']['Temperature'] = ""
                    late_2 = 0
                else:
                    punch_in_2 = record[ID][dateString]['Start_2']['Time']
                    punch_in_2 = "{0} {1}".format(dateString, punch_in_2)
                    punch_in_2 = datetime.strptime(punch_in_2, '%Y-%m-%d %H:%M:%S')  # punch_in_2=datetime格式的日期+時間(打卡上班-下午)
                    late_2 = datetime(i1.year, i1.month, i1.day, inHour2, inMinute2)
                    late_2 = (punch_in_2 - late_2).total_seconds() if punch_in_2 > late_2 else 0
                in_2=record[ID][dateString]['Start_2']['Time']
                note = ""
                if(late > 0):
                    # 遲到
                    note += "遲到(上午) "
                if(late_2 > 0):
                    # 下午遲到
                    note += "遲到(下午) "
                if(earlier > 0):
                    # 早退
                    note += "早退 "
                # ------- 2020 08 07 Alle 新增結尾 --------
                # --- 標示加班 ---
                o_day = str(first.year)+"/"+str(first.month).zfill(2)+"/"+str(day[ID]).zfill(2)
                check_o=0
                if(o6!=0):
                    check_o=1
                    for overtime in overtime_all:
                        if(ID==overtime.GetPersonId() and o_day==overtime.GetDate()):
                            check_o=0
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==True):
                            note += holiday.GetName()
                if('遲到(上午)' in note):
                    late_count[ID]['late1']=late_count[ID]['late1']+1
                if('遲到(下午)' in note):
                    late_count[ID]['late2']=late_count[ID]['late2']+1
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 1, o1, cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, in_2, cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, o2, cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, o3, cell_format_with_border)
                sheet[ID].write_string(count[ID], 5, o4, cell_format_with_border)
                if(check_o==0):
                    sheet[ID].write_number(count[ID], 6, o6, cell_format_with_border)
                else:
                    sheet[ID].write_number(count[ID], 6, o6, cell_format_with_border_red)
                sheet[ID].write_string(count[ID], 7, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
        # --- 判斷閏年、月份天數 ---
        if((first.year%4==0 and first.year%100!=0) or first.year%400==0):
            monthday = [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        else:
            monthday = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        for p in person:
            ID = p.GetID()
            for x in range(day[ID], monthday[first.month-1]+1):
                note=""
                # --- 註記請假資料 ---
                leave_day = str(first.year)+"/"+str(first.month)+"/"+str(day[ID]).zfill(2)
                for leave in leave_all:
                    leave_start=leave.GetStart()[0:10]
                    StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                    leave_end=leave.GetEnd()[0:10]
                    EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                    ThisDay = datetime.strptime(leave_day, "%Y/%m/%d")
                    if(ID==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                        note = leave.GetCategory()
                # --- 取得 holiday資料註記 ---
                note_type=0
                for holiday in holidays_all:
                    if(holiday.GetYear()==str(first.year-1911) and holiday.GetMonth()==str(first.month).zfill(2) and holiday.GetDay()==str(day[ID]).zfill(2)):
                        if(holiday.GetIsWork()==False):
                            note_type=1
                            note = holiday.GetName()
                        else:
                            if(note==""):
                                note_type=2
                                note = "曠職 "+holiday.GetName()
                # --- 計算假日(若無打卡資料則為假日或曠職) ---
                thatDay = str(first.year)+"-"+str(first.month)+"-"+str(day[ID]).zfill(2)
                theDay = datetime.strptime(thatDay, "%Y-%m-%d")
                theWeek = theDay.weekday()
                if(str(theWeek)=="5" or str(theWeek)=="6"):
                    if(note==""):
                        note = "假日"
                else:
                    if(note==""):
                        note = "曠職"
                sheet[ID].set_row(count[ID], 24)
                sheet[ID].write_string(count[ID], 0, str(day[ID]).zfill(2), cell_format_with_border)
                sheet[ID].write_string(count[ID], 2, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 3, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 4, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 5, "", cell_format_with_border)
                sheet[ID].write_string(count[ID], 6, "", cell_format_with_border)
                if(note=="假日" or note_type==1):
                    sheet[ID].write_string(count[ID], 1, note, cell_format_with_border_red)
                    sheet[ID].write_string(count[ID], 7, "", cell_format_with_border)
                else:
                    sheet[ID].write_string(count[ID], 1, "", cell_format_with_border)
                    sheet[ID].write_string(count[ID], 7, note, cell_format_with_border_red)
                count[ID] = count[ID] + 1
                day[ID] = day[ID] + 1
            late_note='遲到(上午)：'+str(late_count[ID]['late1'])+'，遲到(下午)：'+str(late_count[ID]['late2'])
            sheet[ID].write_string(count[ID], 7, late_note, cell_format_with_border_red)
        writer.close()
        stream.seek(0)
        return stream
    
# ========== *新版本-全部人單日匯出 ==========
class PunchCardRecordDownload2Verson2(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('Start')
        self.parser.add_argument('End')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('InHour2')
        self.parser.add_argument('InMinute2')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
    def post(self):
        args = self.parser.parse_args()
        dt_from=datetime.strptime(str(args['Start']),"%Y-%m-%d %H:%M:%S")
        dt_to=datetime.strptime(str(args['End']),"%Y-%m-%d %H:%M:%S")
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch In Time in the afternoon
        inHour2 = int(args['InHour2'])
        inMinute2 = int(args['InMinute2'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        person = PersonModel.AllFromDB(self.log.SetMessage)
        stream = PunchCardRecordDownload2Verson2.GetExcel(self, person, dt_from, dt_to, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak)
        return self.GetExcelResponse("{0}至{1}溫度統計".format(args['Start'], args['End']), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak):
        record = PunchCardRecordVerson2.GetFullTable(first, last,inHour,inMinute,inHour2,inMinute2,outHour,outMinute,lunchBreak, person)
        # create excel
        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_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 = {}
        count = {}
        day = {}
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        if(len(person)>0):
            if(person[0].GetID() in record):
                for d in record[person[0].GetID()].keys():
                    sheet[d] = book.add_worksheet(d)
                    sheet[d].merge_range('A1:N1', "{}年{}月{}日  出勤表".format(d.split('-')[0], d.split('-')[1],d.split('-')[2]), cell_format)
                    sheet[d].merge_range('A2:A3', "編號", cell_format_with_border)
                    sheet[d].merge_range('B2:B3', "姓名", cell_format_with_border)
                    sheet[d].merge_range('C2:F2', "簽到", cell_format_with_border)
                    sheet[d].merge_range('G2:H2', "簽退", cell_format_with_border)
                    sheet[d].merge_range('I2:I3', "遲到(上午)", cell_format_with_border)
                    sheet[d].merge_range('J2:J3', "遲到(下午)", cell_format_with_border)
                    sheet[d].merge_range('K2:K3', "早退", cell_format_with_border)
                    sheet[d].merge_range('L2:L3', "加班", cell_format_with_border)
                    sheet[d].merge_range('M2:M3', "當日最高溫", cell_format_with_border)
                    sheet[d].merge_range('N2:N3', "備註", cell_format_with_border)
                    sheet[d].write_string(2, 2, "上午", cell_format_with_border)
                    sheet[d].write_string(2, 3, "溫度", cell_format_with_border)
                    sheet[d].write_string(2, 4, "下午", cell_format_with_border)
                    sheet[d].write_string(2, 5, "溫度", cell_format_with_border)
                    sheet[d].write_string(2, 6, "時間", cell_format_with_border)
                    sheet[d].write_string(2, 7, "溫度", cell_format_with_border)
                    sheet[d].set_column('A:A', 13)
                    sheet[d].set_column('B:B', 13)
                    sheet[d].set_column('C:C', 13)
                    sheet[d].set_column('D:D', 10)
                    sheet[d].set_column('E:E', 13)
                    sheet[d].set_column('F:F', 10)
                    sheet[d].set_column('G:G', 13)
                    sheet[d].set_column('H:H', 10)
                    sheet[d].set_column('I:I', 13)
                    sheet[d].set_column('J:J', 13)
                    sheet[d].set_column('K:K', 10)
                    sheet[d].set_column('L:L', 10)
                    sheet[d].set_column('M:M', 15)
                    sheet[d].set_column('N:N', 20)
                    count[d] = 3
                for p in person:
                    for dateString in record[p.GetID()].keys():
                        maxtemp=record[p.GetID()][dateString]["MaxTemperature"] if "MaxTemperature" in record[p.GetID()][dateString] else ""
                        note_check=0
                        note=""
                        this_day = str(dateString.split('-')[0])+"/"+dateString.split('-')[1]+"/"+dateString.split('-')[2]
                        ThisDay = datetime.strptime(this_day, "%Y/%m/%d")
                        # --- 註記請假資料 ---
                        for leave in leave_all:
                            leave_start=leave.GetStart()[0:10]
                            StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                            leave_end=leave.GetEnd()[0:10]
                            EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                            if(p.GetID()==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                                note_check=1
                                note = leave.GetCategory()
                        # --- 計算假日 ---
                        theWeek = ThisDay.weekday()
                        if(str(theWeek)=="5" or str(theWeek)=="6"):
                            note_check=1
                            note = "假日"
                        # --- 取得 holiday資料註記 ---
                        for holiday in holidays_all:
                            holiday_this=str(int(holiday.GetYear())+1911)+"/"+holiday.GetMonth()+"/"+holiday.GetDay()
                            if(holiday_this==this_day):
                                if(holiday.GetIsWork()==False):
                                    note_check=1
                                    note = holiday.GetName()
                                else:
                                    note = holiday.GetName()
                        if(note_check==0):
                            note="曠職"
                        sheet[dateString].write_string(count[dateString], 0, p.GetID(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 1, p.GetName(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 2, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 3, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 4, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 5, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 6, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 7, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 8, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 9, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 10, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 11, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 12, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 13, note, cell_format_with_border)
                        if(record[p.GetID()][dateString]["Start"]["Time"]!="" and record[p.GetID()][dateString]["End"]["Time"]!=""):
                            i1 = datetime.strptime(dateString, '%Y-%m-%d')
                            i1 = i1 + timedelta(hours=12)
                            i2 = record[p.GetID()][dateString]['Start']['Time']
                            i2 = "{0} {1}".format(dateString, i2)
                            i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                            i3 = record[p.GetID()][dateString]['End']['Time']
                            i3 = "{0} {1}".format(dateString, i3)
                            i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                            i4 = inHour
                            i5 = inMinute
                            i6 = outHour
                            i7 = outMinute
                            i8 = lunchBreak
                            # start, end, startOver, endOver, work, over
                            o1, o2, o3, o4, o5, o6 = PunchCardRecordVerson2.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                            o1 = o1.strftime('%H:%M:%S')
                            o2 = o2.strftime('%H:%M:%S')
                            o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                            o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""
                            # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                            late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                            late = (i2 - late).total_seconds() if i2 > late else 0
                            earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                            earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                            late_2 = record[p.GetID()][dateString]["WorkBeLate_2"]
#                             note=""
#                             if(late > 0):
#                                 # 遲到
#                                 note += "遲到 "
#                             if(earlier > 0):
#                                 # 早退
#                                 note += "早退"
                            # --- 標示加班 ---
                            check_o=0
                            if(o6!=0):
                                check_o=1
                                for overtime in overtime_all:
                                    if(p.GetID()==overtime.GetPersonId() and this_day==overtime.GetDate()):
                                        check_o=0
                            if(note_check==0):
                                note=""
                            sheet[dateString].write_string(count[dateString], 2, record[p.GetID()][dateString]["Start"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 3, str(record[p.GetID()][dateString]["Start"]["Temperature"]), cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 4, record[p.GetID()][dateString]["Start_2"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 5, str(record[p.GetID()][dateString]["Start_2"]["Temperature"]), cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 6, record[p.GetID()][dateString]["End"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 7, str(record[p.GetID()][dateString]["End"]["Temperature"]), cell_format_with_border)
                            if(late > 0):
                                sheet[dateString].write_string(count[dateString], 8, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 8, "", cell_format_with_border)
                            if(late_2 > 0):
                                sheet[dateString].write_string(count[dateString], 9, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 9, "", cell_format_with_border)
                            if(earlier > 0):
                                sheet[dateString].write_string(count[dateString], 10, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 10, "", cell_format_with_border)
                            if(check_o==0):
                                sheet[dateString].write_number(count[dateString], 11, o6, cell_format_with_border)
                            else:
                                sheet[dateString].write_number(count[dateString], 11, o6, cell_format_with_border_red)
                            sheet[dateString].write_string(count[dateString], 12, str(maxtemp) , cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 13, note , cell_format_with_border)
                        count[dateString]+=1
        writer.close()
        stream.seek(0)
        return stream
    
# ========== *新版本-單日匯出單一群組 ==========
class PunchCardRecordDownload2GroupVerson2(SendBaseResource):
    def __init__(self):
        super().__init__('xlsx')
        self.parser.add_argument('Start')
        self.parser.add_argument('End')
        self.parser.add_argument('InHour')
        self.parser.add_argument('InMinute')
        self.parser.add_argument('InHour2')
        self.parser.add_argument('InMinute2')
        self.parser.add_argument('OutHour')
        self.parser.add_argument('OutMinute')
        self.parser.add_argument('LunchBreak')
        self.parser.add_argument('GroupId')
    def post(self):
        args = self.parser.parse_args()
        dt_from=datetime.strptime(str(args['Start']),"%Y-%m-%d %H:%M:%S")
        dt_to=datetime.strptime(str(args['End']),"%Y-%m-%d %H:%M:%S")
        # Punch In Time
        inHour = int(args['InHour'])
        inMinute = int(args['InMinute'])
        # Punch In Time in the afternoon
        inHour2 = int(args['InHour2'])
        inMinute2 = int(args['InMinute2'])
        # Punch Out Time
        outHour = int(args['OutHour'])
        outMinute = int(args['OutMinute'])
        # Lunch Break Time (Minutes)
        lunchBreak = int(args['LunchBreak'])
        # Get group_person data
        group = PersonGroupRelationModel.AllPersonFromDB(args['GroupId'],self.log.SetMessage)
        group_pereson = []
        # Get person data
        person = PersonModel.AllFromDB(self.log.SetMessage)
        for p in person:
            for g in group:
                if(p.GetID()==g.GetPersonID()):
                    group_pereson.append(p)
        stream = PunchCardRecordDownload2GroupVerson2.GetExcel(self, group_pereson, dt_from, dt_to, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak)
        return self.GetExcelResponse("{0}至{1}溫度統計".format(args['Start'], args['End']), stream)
    @staticmethod
    def GetExcel(self, person, first, last, inHour, inMinute, inHour2, inMinute2, outHour, outMinute, lunchBreak):
        record = PunchCardRecordVerson2.GetFullTable(first, last,inHour,inMinute,inHour2,inMinute2,outHour,outMinute,lunchBreak, person)
        # create excel
        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_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 = {}
        count = {}
        day = {}
        holidays_all = HolidayModel.AllFromDB(self.log.SetMessage)
        leave_all = LeaveOffWorkModel.AllFromDB(self.log.SetMessage)
        overtime_all = OvertimeModel.AllFromDB(self.log.SetMessage)
        if(len(person)>0):
            if(person[0].GetID() in record):
                for d in record[person[0].GetID()].keys():
                    sheet[d] = book.add_worksheet(d)
                    sheet[d].merge_range('A1:N1', "{}年{}月{}日  出勤表".format(d.split('-')[0], d.split('-')[1],d.split('-')[2]), cell_format)
                    sheet[d].merge_range('A2:A3', "編號", cell_format_with_border)
                    sheet[d].merge_range('B2:B3', "姓名", cell_format_with_border)
                    sheet[d].merge_range('C2:F2', "簽到", cell_format_with_border)
                    sheet[d].merge_range('G2:H2', "簽退", cell_format_with_border)
                    sheet[d].merge_range('I2:I3', "遲到(上午)", cell_format_with_border)
                    sheet[d].merge_range('J2:J3', "遲到(下午)", cell_format_with_border)
                    sheet[d].merge_range('K2:K3', "早退", cell_format_with_border)
                    sheet[d].merge_range('L2:L3', "加班", cell_format_with_border)
                    sheet[d].merge_range('M2:M3', "當日最高溫", cell_format_with_border)
                    sheet[d].merge_range('N2:N3', "備註", cell_format_with_border)
                    sheet[d].write_string(2, 2, "上午", cell_format_with_border)
                    sheet[d].write_string(2, 3, "溫度", cell_format_with_border)
                    sheet[d].write_string(2, 4, "下午", cell_format_with_border)
                    sheet[d].write_string(2, 5, "溫度", cell_format_with_border)
                    sheet[d].write_string(2, 6, "時間", cell_format_with_border)
                    sheet[d].write_string(2, 7, "溫度", cell_format_with_border)
                    sheet[d].set_column('A:A', 13)
                    sheet[d].set_column('B:B', 13)
                    sheet[d].set_column('C:C', 13)
                    sheet[d].set_column('D:D', 10)
                    sheet[d].set_column('E:E', 13)
                    sheet[d].set_column('F:F', 10)
                    sheet[d].set_column('G:G', 13)
                    sheet[d].set_column('H:H', 10)
                    sheet[d].set_column('I:I', 13)
                    sheet[d].set_column('J:J', 13)
                    sheet[d].set_column('K:K', 10)
                    sheet[d].set_column('L:L', 10)
                    sheet[d].set_column('M:M', 15)
                    sheet[d].set_column('N:N', 20)
                    count[d] = 3
                for p in person:
                    for dateString in record[p.GetID()].keys():
                        maxtemp=record[p.GetID()][dateString]["MaxTemperature"] if "MaxTemperature" in record[p.GetID()][dateString] else ""
                        note_check=0
                        note=""
                        this_day = str(dateString.split('-')[0])+"/"+dateString.split('-')[1]+"/"+dateString.split('-')[2]
                        ThisDay = datetime.strptime(this_day, "%Y/%m/%d")
                        # --- 註記請假資料 ---
                        for leave in leave_all:
                            leave_start=leave.GetStart()[0:10]
                            StartDay = datetime.strptime(leave_start, "%Y/%m/%d")
                            leave_end=leave.GetEnd()[0:10]
                            EndDay = datetime.strptime(leave_end, "%Y/%m/%d")
                            if(p.GetID()==leave.GetPersonId() and ThisDay>=StartDay and ThisDay<=EndDay):
                                note_check=1
                                note = leave.GetCategory()
                        # --- 計算假日 ---
                        theWeek = ThisDay.weekday()
                        if(str(theWeek)=="5" or str(theWeek)=="6"):
                            note_check=1
                            note = "假日"
                        # --- 取得 holiday資料註記 ---
                        for holiday in holidays_all:
                            holiday_this=str(int(holiday.GetYear())+1911)+"/"+holiday.GetMonth()+"/"+holiday.GetDay()
                            if(holiday_this==this_day):
                                if(holiday.GetIsWork()==False):
                                    note_check=1
                                    note = holiday.GetName()
                                else:
                                    note = holiday.GetName()
                        if(note_check==0):
                            note="曠職"
                        sheet[dateString].write_string(count[dateString], 0, p.GetID(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 1, p.GetName(), cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 2, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 3, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 4, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 5, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 6, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 7, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 8, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 9, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 10, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 11, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 12, "", cell_format_with_border)
                        sheet[dateString].write_string(count[dateString], 13, note, cell_format_with_border)
                        if(record[p.GetID()][dateString]["Start"]["Time"]!="" and record[p.GetID()][dateString]["End"]["Time"]!=""):
                            i1 = datetime.strptime(dateString, '%Y-%m-%d')
                            i1 = i1 + timedelta(hours=12)
                            i2 = record[p.GetID()][dateString]['Start']['Time']
                            i2 = "{0} {1}".format(dateString, i2)
                            i2 = datetime.strptime(i2, '%Y-%m-%d %H:%M:%S')
                            i3 = record[p.GetID()][dateString]['End']['Time']
                            i3 = "{0} {1}".format(dateString, i3)
                            i3 = datetime.strptime(i3, '%Y-%m-%d %H:%M:%S')
                            i4 = inHour
                            i5 = inMinute
                            i6 = outHour
                            i7 = outMinute
                            i8 = lunchBreak
                            # start, end, startOver, endOver, work, over
                            o1, o2, o3, o4, o5, o6 = PunchCardRecord.CalculateTime(i1, i2, i3, i4, i5, i6, i7, i8, 0, 0)
                            o1 = o1.strftime('%H:%M:%S')
                            o2 = o2.strftime('%H:%M:%S')
                            o3 = o3.strftime('%H:%M:%S') if o3 is not None else ""
                            o4 = o4.strftime('%H:%M:%S') if o4 is not None else ""
                            # ------- 2020 08 07 Allen 新增備註，標記遲到早退 --------
                            late = datetime(i1.year, i1.month, i1.day, inHour, inMinute+1)
                            late = (i2 - late).total_seconds() if i2 > late else 0
                            earlier = datetime(i1.year, i1.month, i1.day, outHour, outMinute)
                            earlier = (earlier - i3).total_seconds() if earlier > i3 else 0
                            late_2 = record[p.GetID()][dateString]["WorkBeLate_2"]
#                             note=""
#                             if(late > 0):
#                                 # 遲到
#                                 note += "遲到 "
#                             if(earlier > 0):
#                                 # 早退
#                                 note += "早退"
                            # --- 標示加班 ---
                            check_o=0
                            if(o6!=0):
                                check_o=1
                                for overtime in overtime_all:
                                    if(p.GetID()==overtime.GetPersonId() and this_day==overtime.GetDate()):
                                        check_o=0
                            if(note_check==0):
                                note=""
                            sheet[dateString].write_string(count[dateString], 2, record[p.GetID()][dateString]["Start"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 3, str(record[p.GetID()][dateString]["Start"]["Temperature"]), cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 4, record[p.GetID()][dateString]["Start_2"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 5, str(record[p.GetID()][dateString]["Start_2"]["Temperature"]), cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 6, record[p.GetID()][dateString]["End"]["Time"], cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 7, str(record[p.GetID()][dateString]["End"]["Temperature"]), cell_format_with_border)
                            if(late > 0):
                                sheet[dateString].write_string(count[dateString], 8, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 8, "", cell_format_with_border)
                            if(late_2 > 0):
                                sheet[dateString].write_string(count[dateString], 9, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 9, "", cell_format_with_border)
                            if(earlier > 0):
                                sheet[dateString].write_string(count[dateString], 10, "V", cell_format_with_border_red)
                            else:
                                sheet[dateString].write_string(count[dateString], 10, "", cell_format_with_border)
                            if(check_o==0):
                                sheet[dateString].write_number(count[dateString], 11, o6, cell_format_with_border)
                            else:
                                sheet[dateString].write_number(count[dateString], 11, o6, cell_format_with_border_red)
                            sheet[dateString].write_string(count[dateString], 12, str(maxtemp) , cell_format_with_border)
                            sheet[dateString].write_string(count[dateString], 13, note , cell_format_with_border)
                        count[dateString]+=1
        writer.close()
        stream.seek(0)
        return stream
    
# ========== send person photo resource ==========
class PunchCardSend(SendBaseResource):
    def __init__(self):
        super().__init__('image/jpeg')
    def get(self, ID):
        file = "{0}.jpg".format(os.path.join('./static', 'face', ID))
        return self.GetFileResponse(file)
# ========== send person photo resource for now ==========
class PunchCardFirst(BaseResource):
    def __init__(self):
        super().__init__('PunchCardFirst')
    def get(self):
        data = PunchInfluxModel.FirstFromRaw()
        dt = data.GetDateTime()
        self.data = {
            "Date": dt.strftime('%Y-%m-%d'),
            "Time": dt.strftime('%H:%M:%S'),
            "Name": data.GetName(),
            "Temp": data.GetTemperature(),
            "Location": data.GetLocation(),
            "Image": data.GetImage().split('.')[0].split('/')[2],
        }
        return self.GetResponse()
# ========== send person photo resource for today ==========
class PunchCardRecordToday(BaseResource):
    def __init__(self):
        super().__init__("SelectTodayRecordOfPunchCard")
        #self.parser.add_argument('start', required=True, help="yyyy-MM-dd HH:mm:ss")
        #self.parser.add_argument('end', required=True, help="yyyy-MM-dd HH:mm:ss")
    def get(self):
        args = self.parser.parse_args()
        now = datetime.now()
        try:
            start = datetime(now.year, now.month, now.day, 0, 0, 0)
            try:
                end = datetime(now.year, now.month, now.day, 23, 59, 59)
                data = PunchCardRecordToday.GetList(start, end)
                data.reverse()
                self.data = data
                self.message = "{0} selected".format(len(self.data))
            except:
                self.message = "the format of end time {0} isn't match".format(args['end'])
        except:
            self.message = "the format of start time {0} isn't match".format(args['start'])
        return self.GetResponse()
    @staticmethod
    def GetList(start, end):
        data = PunchInfluxModel.AllFromRaw(start, end)
        dataList = []
        for row in data:
            dataImage = row.GetImage()
            image = dataImage.split('.')[0].split('/')[2] if '.' in dataImage else dataImage
            dt = row.GetDateTime()
            record = {
                "Time": dt.strftime('%Y-%m-%d %H:%M:%S'),
                "ID": row.GetID(),
                "Name": row.GetName(),
                "Temperature" : row.GetTemperature(),
                "Location": row.GetLocation(),
                "Image": image
            }
            dataList.append(record)
        return dataList

# ========== 班別 ==========
class ExportShiftPunchCard(SendBaseResource):
    def __init__(self):
        super().__init__('txt')
        self.parser.add_argument('start')
        self.parser.add_argument('end')
    def post(self):
        args = self.parser.parse_args()
        devices = DeviceModel.AllFromDB(self.log.SetMessage)
        person = PersonModel.AllFromDB(self.log.SetMessage)
        start = datetime.strptime(args['start'], '%Y-%m-%d %H:%M:%S')
        end = datetime.strptime(args['end'], '%Y-%m-%d %H:%M:%S')
        temperature = None
        punch_card_data = PunchInfluxModel.AllFromRaw(start, end, temperature)
        stream = ExportShiftPunchCard.GetFile(self,devices,person,punch_card_data,start)
        return self.GetTxtResponse("{0}".format(start.strftime('%Y%m%d')), stream)
    @staticmethod
    def GetFile(self,devices,person,punch_card_data,start):
        path = './static/File/{0}.txt'.format(start.strftime('%Y%m%d'))
        file = open(path, 'w')
        person_punch_card = {}
        for p in person:
            person_punch_card[p.GetID()] = {}
        device_shift = {}
        for d in devices:
            device_shift[d.GetID()] = {}
        #班別
        shift_note = {}
        shifts = ShiftModel.AllFromDB(self.log.SetMessage)
        for s in shifts:
            shift_note[s.GetNo()] = {}
            shift_note[s.GetNo()]['name']=s.GetName()
            shift_note[s.GetNo()]['start']=s.GetStart()
            shift_note[s.GetNo()]['end']=s.GetEnd()
        #設備班別關係
        device_shift_relation = DeviceShiftRelationModel.AllFromDB(self.log.SetMessage)
        for r in device_shift_relation:
            device_shift[r.GetDeviceId()][r.GetShiftNo()] = {}
            device_shift[r.GetDeviceId()][r.GetShiftNo()]['name'] = shift_note[r.GetShiftNo()]['name']
            device_shift[r.GetDeviceId()][r.GetShiftNo()]['start'] = shift_note[r.GetShiftNo()]['start']
            device_shift[r.GetDeviceId()][r.GetShiftNo()]['end'] = shift_note[r.GetShiftNo()]['end']
        for data in punch_card_data:
            ID = data.GetID()
            if ID in person_punch_card:
                time = data.GetTime()
                date = time.strftime('%Y-%m-%d')
                if date not in person_punch_card[ID]:
                    person_punch_card[ID][date] = {}
                datetime = time.strftime('%Y%m%d%H%M')
                only_time = time.strftime('%H:%M')
                device_id = data.GetDevice()
                shift_name = "ERROR"
                if device_id in device_shift:
                    for device_shift_no in device_shift[device_id]:
                        shift_start = device_shift[device_id][device_shift_no]['start']
                        shift_end = device_shift[device_id][device_shift_no]['end']
                        if(shift_start<=only_time and only_time<=shift_end):
                            shift_name = device_shift[device_id][device_shift_no]['name']
                if 'start' not in person_punch_card[ID][date]:
                    person_punch_card[ID][date]['start']='{0}{1}{2}\n'.format(ID,datetime,shift_name)
                    person_punch_card[ID][date]['start_shift']=shift_name
                person_punch_card[ID][date]['end']='{0}{1}{2}\n'.format(ID,datetime,shift_name)
                person_punch_card[ID][date]['end_shift']=shift_name
                # file.write('{0}{1}{2}\n'.format(ID,datetime,shift_name))
        write_list = []
        for p in person_punch_card:
            for d in person_punch_card[p]:
                if person_punch_card[p][d]['start_shift'] != 'ERROR':
                    write_list.append(person_punch_card[p][d]['start'])
                else:
                    write_list.insert(0,person_punch_card[p][d]['start'])
                
                if person_punch_card[p][d]['start'] != person_punch_card[p][d]['end']:
                    if person_punch_card[p][d]['end_shift'] != 'ERROR':
                        write_list.append(person_punch_card[p][d]['end'])
                    else:
                        write_list.insert(0,person_punch_card[p][d]['end'])
                # file.write(person_punch_card[p][d]['start'])
                # file.write(person_punch_card[p][d]['end'])
        file.writelines(write_list)
        file.close()
        s_file = open(path, 'rb')
        return s_file