from run import app
from flask_restful import Resource, reqparse
from flask import redirect,jsonify
from werkzeug import datastructures
import datetime
import time
import cv2
import pytz
import os
import facenet_predict
import face_cascade
import svm_classification
import train
from influxdb import InfluxDBClient
import pymysql
import xlwt
import calendar
import json

class Whois(Resource):
    def __init__(self):
        self.reqparse = reqparse.RequestParser()
        self.reqparse.add_argument('file',type=datastructures.FileStorage, location='files',required=True,help="Please slecet a image which incloud car to upload.")
        
    @staticmethod
    def save(self):
        save_time=time.time()
        dt = datetime.datetime.now(pytz.timezone('Asia/Taipei'))
        dt = dt.strftime('%Y_%m_%d_%H_%M_%S')+'.jpg'
        print(dt)
        filepath = os.path.abspath(os.path.join(app.config['UPLOAD_FOLDER'], dt))
        args =self.reqparse.parse_args()
        args['file'].save(filepath)
        return save_time,filepath
    
    def get(self):
        return "hello world"
    
    def post(self):
        save_time,img_path = self.save(self)
#         print(img_path)
        img=cv2.imread(img_path)
        faces=face_cascade.get_face(img)
        if(len(faces)!=2):
            return {'error':faces}
        embs=facenet_predict.calc_embs(faces[1])
        result=svm_classification.result(embs)
        
        # 建立mysql連線
        db=pymysql.connect('192.168.0.5','root','xMd2k5aK','rd')
        cursor = db.cursor()
        sql="SELECT name FROM `employee` WHERE id='{0}'".format(result[0][0])
        results=cursor.execute(sql)
        if(results==0):
            return jsonify({'error':'查詢錯誤請重新輸入'})
        name=cursor.fetchall()[0][0]
        db.close()
        
        return {'time':save_time,'face_location':str(faces[0]),'uid':result[0],'name':name,'confidence':result[1]}

#         if(len(result)!=3):
#             return {'error':result}
#         return {'face':str(result[0]),'name':result[1],'confidence':result[2]}
#         result=train.get_result(img_path)
#         return {'message':str(result)}

    
        
    
class Upload_image(Resource):
    def __init__(self):
        self.reqparse = reqparse.RequestParser()
        self.reqparse.add_argument('uid',required=True,help="Please write down your name and we will save your image to folder of train")
        self.reqparse.add_argument('file',type=datastructures.FileStorage, location='files',required=True,help="Please slecet a image which incloud car to upload.")
    @staticmethod
    def save(self):
        dt = datetime.datetime.now(pytz.timezone('Asia/Taipei'))
        dt = dt.strftime('%Y_%m_%d_%H_%M_%S')+'.jpg'
        args =self.reqparse.parse_args()
        
        # 建立mysql連線
        db=pymysql.connect('192.168.0.5','root','xMd2k5aK','rd')
        cursor = db.cursor()
        sql="SELECT name FROM `employee` WHERE id='{0}'".format(args['uid'])
        results=cursor.execute(sql)
        if(results==0):
            return 'error','查詢錯誤請重新輸入'
        name=cursor.fetchall()[0][0]
        db.close()
        
        fileroot=os.path.abspath(os.path.join('./nas/Face/train',args['uid']))
        filepath = os.path.join(fileroot,dt)
        if(not os.path.isdir(fileroot)):
            os.mkdir(fileroot)
        args['file'].save(filepath)
        return name,filepath
    
    def post(self):
        name,img_path=self.save(self)
        if(name=='error'):
            return {'error':'查詢錯誤請重新輸入'}
        img=cv2.imread(img_path)
        faces=face_cascade.get_face(img)
        if(len(faces)!=2):
            return {'error':'偵測不到人臉 請重新上傳'}
        else:
            return {'message':name+'已新增訓練樣本'}
        
class Train(Resource):
    def get(self):
        train.go_train()
        return {'message':'training is done'}
    
class Clock(Resource):
    def __init__(self):
        self.reqparse = reqparse.RequestParser()
        self.reqparse.add_argument('uid',required=True,help="Please write down your name")
        self.reqparse.add_argument('time',required=True,help="Please write down save time")
        self.reqparse.add_argument('log',required=True,help="Please write down your login or logout")
        self.reqparse.add_argument('status',required=True,help="Please write down your status")
    def post(self):
        args =self.reqparse.parse_args()
        save_time=args['time']
        save_uid=args['uid']
        save_log=args['log']
        status=args['status']
        
        # 建立mysql連線
        db=pymysql.connect('192.168.0.5','root','xMd2k5aK','rd')
        cursor = db.cursor()
        sql="SELECT name FROM `employee` WHERE id='{0}'".format(save_uid)
        results=cursor.execute(sql)
        if(results==0):
            return jsonify({'error':'查詢錯誤請重新輸入'})
        save_name=cursor.fetchall()[0][0]
        db.close()
        
        if(status == 'error_'):
            save_path=status
        else:
            save_path=save_log
        # 轉換程標準UTC時間
        # 在轉換時區
        dt=datetime.datetime.utcfromtimestamp(float(save_time))
        utc_time=dt.replace(tzinfo=pytz.timezone('UTC'))
        local_dt=utc_time.astimezone(pytz.timezone('Asia/Taipei'))
        img_file = local_dt.strftime('%Y_%m_%d_%H_%M_%S')+'.jpg'
        img=cv2.imread(os.path.join('./tmp/upload',img_file))
        print(img_file)
        cv2.imwrite(os.path.abspath(os.path.join('./nas/Face/history',save_uid,save_path,img_file)),img)
                    
        # 建立資料庫連線
        #client=InfluxDBClient(host='192.168.0.6',port=8086,username='root',password='xMd2k5aK',database='rd')
        client=InfluxDBClient(host='192.168.5.17',port=8086,username='root',password='root',database='RD')

        json_body = [
            {
                "measurement": save_log,
                "time": datetime.datetime.utcfromtimestamp(float(save_time)),
                "fields": {
                    "id": save_uid,
                    "name": save_name,
                    'snapshot':save_path+'/'+img_file
                }
            }
        ]
        client.write_points(json_body)
        client.close()
        return {'message':save_name+'打卡成功'}
        

class Insert_employee(Resource):
    def __init__(self):
        self.reqparse = reqparse.RequestParser()
        self.reqparse.add_argument('name',required=True,help="Please write down your name")
        self.reqparse.add_argument('engname',required=True,help="Please write down save time")
        self.reqparse.add_argument('phone',required=True,help="Please write down your login or logout")
        self.reqparse.add_argument('years',required=True,help="Please write down your status")
        
    def post(self):
        args=self.reqparse.parse_args()
        name=args['name']
        engname=args['engname']
        phone=args['phone']
        years=args['years']
        db=pymysql.connect('192.168.0.5','root','xMd2k5aK','rd')
        cursor = db.cursor()
        # 資料庫查詢此類別最後一組id避免重複
        sql="SELECT id FROM employee WHERE id LIKE 'RD{0}%'".format(years)
        a=cursor.execute(sql)
        if(a == 0):
            full_num='RD'+str(years)+'00'
        else:
            result=cursor.fetchall()
            num=int(max(result)[0].split(years)[1])+1
            if(num < 10):
                num='0'+str(num)
                
            full_num='RD'+str(years)+str(num)
        sql="""INSERT INTO `employee` (`id`, `name`, `english_name`, `phone`) VALUES ('{0}', '{1}', '{2}', '{3}')""".format(full_num,name,engname,phone)
        try:
        # 执行SQL语句
            cursor.execute(sql)
        # 提交到数据库执行
            db.commit()
        except:
        # 发生错误时回滚
            db.rollback()
 
        db.close()
    
        save_path=os.path.abspath(os.path.join('./nas/Face/history',full_num))
        if(not os.path.isdir(save_path)):
            os.mkdir(save_path)
            os.mkdir(os.path.join(save_path,'error_'))
            os.mkdir(os.path.join(save_path,'login_'))
            os.mkdir(os.path.join(save_path,'logout_'))
            
        return {'message':full_num}

class Report(Resource):
    def get(self,file_type,year,month,uid):
        # 建立mysql連線
        db=pymysql.connect('192.168.0.5','root','xMd2k5aK','rd')
        cursor = db.cursor()
        sql="SELECT name FROM `employee` WHERE id='{0}'".format(uid)
        results=cursor.execute(sql)
        if(results==0):
            return jsonify({'error':'查詢錯誤請重新輸入'})
        name=cursor.fetchall()[0][0]
        db.close()
#         name = request.args.get('name')
        # 時區設定
        tw = pytz.timezone('Asia/Taipei')
        # 獲取當月第一天的星期與當月的總天數
        firstDayWeekDay, monthRange = calendar.monthrange(int(year), int(month))
        # 獲取當月第一天跟最後一天 localtime
        firstDay = datetime.datetime(year=year, month=month, day=1,hour=0,minute=0,second=0)
        lastDay = datetime.datetime(year=year, month=month, day=monthRange,hour=23,minute=59,second=59)
        # 時區本地化
        tw_firstDay=tw.localize(firstDay)
        tw_lastDay=tw.localize(lastDay)
        # 轉換為utc時間
        utc_firstDay=tw_firstDay.astimezone(pytz.utc)
        utc_lastDay=tw_lastDay.astimezone(pytz.utc)
        # 字串格式化轉為influxdb時間格式
        format_firstDay=utc_firstDay.strftime("%Y-%m-%d %H:%M:%S")
        format_lastDay=utc_lastDay.strftime("%Y-%m-%d %H:%M:%S")
        # 建立influxdb連線
        client=InfluxDBClient(host='192.168.0.6',port=8086,username='root',password='xMd2k5aK',database='rd')
        login_response=client.query("SELECT * FROM login_ WHERE time > '{0}' AND time < '{1}' AND \"id\" = '{2}'".format(format_firstDay,format_lastDay,uid))
        logout_response=client.query("SELECT * FROM logout_ WHERE time > '{0}' AND time < '{1}' AND \"id\" = '{2}'".format(format_firstDay,format_lastDay,uid))
        # 查詢錯誤處理
        if(len(login_response) == 0 or len(logout_response) == 0):
            return jsonify({'error':'查詢錯誤請重新輸入'})
        # influxdb回傳值解析
        login_history=login_response.raw['series'][0]['values']
        logout_history=logout_response.raw['series'][0]['values']
        def utc2local(strtime):
            strtime=strtime.split('Z')[0]
            strtime=strtime.split('.')[0]
            dt=datetime.datetime.strptime(strtime,"%Y-%m-%dT%H:%M:%S")
            loacl_time=dt.astimezone(pytz.timezone('Asia/Taipei'))
            return loacl_time.strftime("%Y-%m-%d %H:%M:%S")
        
        log_json={}
        for login,logout in zip(login_history,logout_history):
            local_login=utc2local(login[0])
            local_logout=utc2local(logout[0])

            login_date,login_time=local_login.split(' ')
            logout_date,logout_time=local_logout.split(' ')

            d1 = datetime.datetime.strptime(local_logout,'%Y-%m-%d %H:%M:%S')

            overtime=0
            if(d1.hour ==17 and d1.minute >= 45):
                overtime=0.5
            elif(d1.hour >17 and d1.minute >=15):
                overtime=d1.hour-17
                if(d1.minute>=45):
                    overtime=overtime+0.5

            date_json={'login':login_time,'logout':logout_time,'overtime':overtime}
            log_json[login_date]=date_json
        if(file_type == 'excel'):
            # 新增Excel檔案
            workbook = xlwt.Workbook(encoding='utf-8')
            # 新建Sheet
            sheet=workbook.add_sheet("{0}月出勤考核表".format(month))
            # 基本欄位
            sheet.write(0,0,"ID")
            sheet.write(0,1,uid)
            sheet.write(0,2,"姓名")
            sheet.write(0,3,name)
            sheet.write(1,0,"日期")
            sheet.write(1,1,"上班打卡時間")
            sheet.write(1,2,"下班打卡時間")
            sheet.write(1,3,"加班時數")
            x,y=2,0
            for k in log_json:
                sheet.write(x,0,k)
                sheet.write(x,1,log_json[k]['login'])
                sheet.write(x,2,log_json[k]['logout'])
                sheet.write(x,3,log_json[k]['overtime'])
                print(log_json[k])
                x=x+1
            # 存儲
            Fname="{0}{1}月出勤紀錄.xlsx".format(name,month)
            workbook.save('docs/'+Fname)
            return redirect('/download/'+Fname)
        else:
            return jsonify({'range':"{0}年{1}月".format(year,month),'name':name,'log':log_json})
        