import cv2
import os
import numpy as np
from PIL import Image #pillow
import pyttsx3
import sys
import test.student_sql as connect #導(dǎo)入py
import test.recognizer_sql as baseConnect #導(dǎo)入py
import time
import json
 
def makeDir(engine):
 flag= 0
 if not os.path.exists("face_trainer"):
  print("創(chuàng)建預(yù)訓(xùn)練環(huán)境")
  engine.say('檢測到第一次啟動，未檢測到環(huán)境，正在創(chuàng)建環(huán)境')
  engine.say('正在創(chuàng)建預(yù)訓(xùn)練環(huán)境')
  os.mkdir("face_trainer")
  engine.say('創(chuàng)建成功')
  engine.runAndWait()
  flag=1
 if not os.path.exists("Facedata"):
  print("創(chuàng)建訓(xùn)練環(huán)境")
  engine.say('正在創(chuàng)建訓(xùn)練環(huán)境')
  os.mkdir("Facedata")
  engine.say('創(chuàng)建成功')
  engine.runAndWait()
  flag=1
 return flag
 
def getFace(cap,path_id):
 # 調(diào)用筆記本內(nèi)置攝像頭，所以參數(shù)為0，如果有其他的攝像頭可以調(diào)整參數(shù)為1，2
 #cap = cv2.VideoCapture(0)
 #xml文件為自己的文件路徑
 face_detector = cv2.CascadeClassifier(r'F:\npyWorkspace\venv\Lib\site-packages\cv2\data\haarcascade_frontalface_default.xml')
 #face_id = input('\n enter user id:')
 print('\n Initializing face capture. Look at the camera and wait ...')
 count = 0
 while True:
  # 從攝像頭讀取圖片
  sucess, img = cap.read()
  # 轉(zhuǎn)為灰度圖片
  gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY)
  # 檢測人臉
  faces = face_detector.detectMultiScale(gray, 1.3, 5)
  for (x, y, w, h) in faces:
   cv2.rectangle(img, (x, y), (x+w, y+w), (255, 0, 0))
   count += 1
   # 保存圖像
   cv2.imwrite("Facedata/User." + str(path_id) + '.' + str(count) + '.jpg', gray[y: y + h, x: x + w])
   cv2.imshow('image', img)
  # 保持畫面的持續(xù)。
  k = cv2.waitKey(1)
  if k == 27: # 通過esc鍵退出攝像
   break
  elif count >= 100: # 得到1000個樣本后退出攝像
   break
 cv2.destroyAllWindows()
 
def getImagesAndLabels(path, detector):
 imagePaths = [os.path.join(path, f) for f in os.listdir(path)] # join函數(shù)的作用
 faceSamples = []
 ids = []
 for imagePath in imagePaths:
  PIL_img = Image.open(imagePath).convert('L') # convert it to grayscale
  img_numpy = np.array(PIL_img, 'uint8')
  id = int(os.path.split(imagePath)[-1].split(".")[1])
  faces = detector.detectMultiScale(img_numpy)
  for (x, y, w, h) in faces:
   faceSamples.append(img_numpy[y:y + h, x: x + w])
   ids.append(id)
 return faceSamples, ids
 
 
def trainFace():
 # 人臉數(shù)據(jù)路徑
 path = 'Facedata'
 recognizer = cv2.face.LBPHFaceRecognizer_create()
 detector = cv2.CascadeClassifier(r'F:\npyWorkspace\venv\Lib\site-packages\cv2\data\haarcascade_frontalface_default.xml')
 print('Training faces. It will take a few seconds. Wait ...')
 faces, ids = getImagesAndLabels(path, detector)
 recognizer.train(faces, np.array(ids))
 recognizer.write(r'face_trainer\trainer.yml')
 print("{0} faces trained. Exiting Program".format(len(np.unique(ids))))
 
def checkFace(cam,names,engine,sign_flag):
 sex = {"female":"女士","male":"先生"}
 recognizer = cv2.face.LBPHFaceRecognizer_create()
 recognizer.read('face_trainer/trainer.yml')
 cascadePath = r"F:\npyWorkspace\venv\Lib\site-packages\cv2\data\haarcascade_frontalface_default.xml"
 faceCascade = cv2.CascadeClassifier(cascadePath)
 font = cv2.FONT_HERSHEY_SIMPLEX
 idnum = 0
 names = ['yumengzhen', 'dujuanjuan','litingting','kangming','wangyizhe']
 #cam = cv2.VideoCapture(0)
 minW = 0.1 * cam.get(3)
 minH = 0.1 * cam.get(4)
 while True:
  ret, img = cam.read()
  gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY)
  faces = faceCascade.detectMultiScale(
   gray,
   scaleFactor=1.2,
   minNeighbors=5,
   minSize=(int(minW), int(minH))
  )
  for (x, y, w, h) in faces:
   cv2.rectangle(img, (x, y), (x + w, y + h), (0, 255, 0), 2)
   idnum, confidence = recognizer.predict(gray[y:y + h, x:x + w])
   if confidence < 100:
    Name =connect.readName(idnum) #connect 傳入ID 學(xué)生信息表找到 返回 name
    Sex =connect.readSex(idnum) #connect ID 學(xué)生信息表找到 返回 Sex
    StudentID =connect.readStudentID(idnum) #connect ID 學(xué)生信息表找到 返回 studentID
    #idnum = names[idnum] #利用數(shù)據(jù)庫 讀取學(xué)生信息表 該id 對應(yīng)的name
    confidence = "{0}%".format(round(100 - confidence))
    if sign_flag=='0': #簽到
     say(engine, "歡迎   "+Name+ sex[Sex]+"   簽到成功 ")
     baseConnect.insertd(idnum,Name,StudentID,Sex) #簽到表中 插入簽到信息
     print("歡迎  "+Name+ sex[Sex] + "簽到成功 ")
    else :
     say(engine, "歡迎   "+Name+ sex[Sex]+"   簽退成功 ")
     baseConnect.insertt(idnum,Name,StudentID,Sex) #簽到表中 插入簽退信息
     print("歡迎  "+Name+ sex[Sex] + "簽退成功 ")
 
    cv2.imshow("img",img)
    os.system("pause")
    return
   else:
    idnum = "unknown"
    confidence = "{0}%".format(round(100 - confidence))
   cv2.putText(img, str(idnum), (x + 5, y - 5), font, 1, (0, 0, 255), 1)
   cv2.putText(img, str(confidence), (x + 5, y + h - 5), font, 1, (0, 0, 0), 1)
  cv2.imshow('camera', img)
  k = cv2.waitKey(10)
  if k == 27:
   break
 cam.release()
 cv2.destroyAllWindows()
 
 
def say(engine,str):
 engine.say(str)
 engine.runAndWait()
 
def admission(): #錄入信息模塊
 #names = {"yumengzhen":0,"dujuanjuan":1,"litingting":2}
 say(engine, "請輸入您的學(xué)號   ")
 StudentID = input("請輸入學(xué)號：")
 # 讀取數(shù)據(jù)庫信息表 取出Name 對應(yīng)ID
 ID=connect.readIDbaseStudentID(StudentID) #connect 傳入name 學(xué)生信息表找到 返回 ID
 if ID==-1:#沒有找到該學(xué)生插入學(xué)生信息
  while True:
   say(engine,"沒有找到該學(xué)生信息 輸人 0 注冊 1重新輸入")
   op=input("\n 沒有找到該學(xué)生信息 輸人數(shù)字 0 注冊學(xué)生信息 1重新輸入")
   if op=='0':
    Name,studentID,Sex=input("輸入學(xué)生信息： Name studentID Sex").split()
    connect.insert(Name,studentID,Sex) #插入學(xué)生信息信息
   else:
    StudentID = input("請輸入學(xué)號：")
   ID=connect.readIDbaseStudentID(StudentID) #connect 傳入name 學(xué)生信息表找到 返回 ID
   if ID!=-1 :
    break
 say(engine, "正在打開攝像頭")
 cam = cv2.VideoCapture(0)
 say(engine, "注視攝像頭，開始采集人臉數(shù)據(jù)")
 getFace(cam, ID) # 實際傳入的是id
 cam.release()
 
 
if __name__ == '__main__':
 names = {"yumengzhen":0,"dujuanjuan":1,"litingting": 2}
 password="123456" #密碼
 engine = pyttsx3.init()
 rate = engine.getProperty('rate')
 engine.setProperty('rate', rate - 20)
 flag=makeDir(engine)
 #trainFace()
 while True:
  if flag==1 :
   flag = 0
   say(engine, "首次使用 沒有人臉信息 ")
   say(engine, "是否要錄入新的人臉信息  ")
   say(engine, "輸入0 代表是 輸入其他表示退出")
   value = input("0：是 or other：否")
   if value=='0':
    while True:
     admission()
     say(engine, "是否要繼續(xù)錄入新的人臉信息  ")
     say(engine, "輸入0 代表是 輸入其他表示退出")
     firstflag = input("0：是 其他：退出")
     if firstflag != '0':
      break
    say(engine, "采集完畢，開始訓(xùn)練")
    trainFace()
    say(engine, "訓(xùn)練完畢   ")
 
  #say(engine, "請選擇登錄方式 ")
  say(engine, "輸入 0管理人員模式  1 進(jìn)入簽到/簽退模式  2 退出學(xué)生簽到系統(tǒng) ")
  user=input("\n0:管理人員模式  1:進(jìn)入簽到/簽退模式 2:退出學(xué)生簽到系統(tǒng)\n")
 
  if user=='0':
   say(engine, "輸入管理員密碼 ")
   pd=input("\n輸入管理員密碼 :\n")
   count=1
   while True:
    if count==3:
     say(engine," 輸入密碼錯誤超過3次 強(qiáng)制退出輸入  ")
     break
 
    if password == pd:
     say(engine, "管理員模式 ")
     #say(engine, "輸入數(shù)字 0 導(dǎo)出簽到表  1 導(dǎo)出個人簽到表  2 導(dǎo)出時長表  3 導(dǎo)出信息表  4 錄入人臉信息 5 退出")
     op = input("\n0:導(dǎo)出所有同學(xué)簽到表 1:導(dǎo)出個人簽到表 2:導(dǎo)出所有人員時長表 3:導(dǎo)出學(xué)生信息表 4 錄入人臉信息 5 退出\n")
     if op == '0':
      baseConnect.sign()#導(dǎo)出簽到表
      say(engine, "導(dǎo)出簽到表成功 ")
      pass
     elif op == '1':
      say(engine,"輸入導(dǎo)出學(xué)生的學(xué)號")
      StudentID=input("輸入導(dǎo)出學(xué)生的學(xué)號")
      ID=connect.readIDbaseStudentID(StudentID)
      if ID==-1:
       say(engine, "沒有該學(xué)生信息 ")
      else:
       baseConnect.peoson_sign(StudentID)#導(dǎo)出個人簽到表
       Name =connect.readName(ID) #connect 傳入ID 學(xué)生信息表找到 返回 name
       say(engine, "導(dǎo)出 "+Name+" 信息成功")
 
     elif op == '2':
      baseConnect.total_time()#導(dǎo)出時長表
      say(engine,"導(dǎo)出時長表成功  ")
     elif op == '3':
      #導(dǎo)出學(xué)生信息表
      connect.find_student_all()
      say(engine, "導(dǎo)出學(xué)生信息成功  ")
     elif op == '4':
      while True:
       admission()
       say(engine, "是否要繼續(xù)錄入新的人臉信息  ")
       say(engine, "輸入0 代表是 輸入其他表示退出")
       secondflag = input("0：是 其他：退出")
       if secondflag != '0':
        break
      say(engine, "采集完畢，開始訓(xùn)練")
      trainFace()
      say(engine, "訓(xùn)練完畢   ")
     elif op == '5':
      say(engine, "已退出 管理員模式 ")
      break
     else:
      say(engine, "輸入形式錯誤 請重新輸入  ")
    else:
     say(engine, "輸入密碼錯誤  請重新輸入 ")
     pd = input("\n輸入管理員密碼 :\n")
     count += 1;
 
  elif user=='1':
   say(engine, "歡迎進(jìn)入學(xué)生系統(tǒng)簽到/簽退模式  ")
   sign_flag=0;
   while True:
    say(engine, "輸入數(shù)字 0 簽到  1 簽退")
    sign_flag = input("\n0: 簽到  1 簽退\n")
    if sign_flag=='1' or sign_flag=='0' :
     break
    else :
     say(engine," 請輸入正確的輸入形式")
   say(engine, "開始人臉識別")
   say(engine, "正在打開攝像頭")
   cam = cv2.VideoCapture(0)
   checkFace(cam, names, engine,sign_flag)
 
  elif user=='2':
   say(engine, "信息已保存")
   say(engine, "再見")
   sys.exit(0)
  else:
   say(engine, "輸入錯誤請重新輸入  ")

import pymssql as py
import pandas as pd
# 連接數(shù)據(jù)庫，創(chuàng)建學(xué)生表，進(jìn)行表查詢，表錄入
server = "DESKTOP-XXX"# 連接自己數(shù)據(jù)庫的服務(wù)器地址
user = "sa"# 連接帳號
password = "123"# 連接密碼
conn = py.connect(server, user, password, "student_message") #獲取連接
cursor = conn.cursor() # 獲取光標(biāo)
 
# 創(chuàng)建表
cursor.execute("""
IF OBJECT_ID('students', 'U') IS NOT NULL
 DROP TABLE students
CREATE TABLE students (
 ID INT NOT NULL,
 name VARCHAR(100),
 StudentID INT,
 Sex VARCHAR(100)
 )
""")
conn.commit()
#第一次運(yùn)行時建立表，之后再運(yùn)行無需再建
 
def insert(Name, studentID, Sex):
  count_students = 0
  try:
   conn = py.connect(server, user, password, "student_message") # 獲取連接
   cursor =conn.cursor()
   cursor.execute(' select count(ID) from students')
   for row in cursor:
    count_students = row[0]
    print(row[0])
   cursor.executemany(
    "INSERT INTO students VALUES (%d, %s, %d,%s)",
    [(count_students+1, Name, studentID, Sex)])
  # 你必須調(diào)用 commit() 來保持你數(shù)據(jù)的提交如果你沒有將自動提交設(shè)置為true
   conn.commit()
   conn.close()
  except py.InterfaceError:
   print("數(shù)據(jù)庫連接出錯")
  except py.ProgrammingError:
   print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
  except py.OperationalError:
   print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 
 
# 導(dǎo)出學(xué)生信息表
def find_student_all():
 try:
  conn = py.connect(server, user, password, "student_message") # 獲取連接
  cursor =conn.cursor()
  sql = "select * from students"
  df = pd.read_sql(sql, conn)
  df.to_excel('all.xlsx',index=False)
  print('ok')
  conn.commit()
  conn.close()
 except py.InterfaceError:
  print("數(shù)據(jù)庫連接出錯")
 except py.ProgrammingError:
  print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 
#find_student_all()
 
def readName(idnum):
 Name = -1
 try:
  conn = py.connect(server, user, password, "student_message") # 獲取連接
  cursor =conn.cursor()
  cursor.execute(' select Name from students where ID='+str(idnum))
  for row in cursor:
   if row[0]!=[]:
    Name = row[0]
  conn.commit()
  conn.close()
 except py.InterfaceError:
  print("數(shù)據(jù)庫連接出錯")
 except py.ProgrammingError:
  print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 return Name
 
def readIDbaseStudentID(StudentID):
 ID = -1
 try:
  conn = py.connect(server, user, password, "student_message") # 獲取連接
  cursor =conn.cursor()
  cursor.execute(' select ID from students where StudentID='+str(StudentID))
  for row in cursor:
   if row[0]!=[]:
    ID = row[0]
  conn.commit()
  conn.close()
 except py.InterfaceError:
  print("數(shù)據(jù)庫連接出錯")
 except py.ProgrammingError:
  print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 return ID
 
def readSex(idnum):
 Sex = -1
 try:
  conn = py.connect(server, user, password, "student_message") # 獲取連接
  cursor =conn.cursor()
  cursor.execute(' select Sex from students where ID='+str(idnum))
  for row in cursor:
    if row[0]!=[]:
     Sex = row[0]
  conn.commit()
  conn.close()
 except py.InterfaceError:
  print("數(shù)據(jù)庫連接出錯")
 except py.ProgrammingError:
  print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 return Sex
 
 
def readID(name):
 # 多個id
 ID = -1
 try:
  conn = py.connect(server, user, password, "student_message") # 獲取連接
  cursor =conn.cursor()
  cursor.execute(' select ID from students where name='+'\''+str(name)+'\'')
  for row in cursor:
   if row[0]!=[]:
    ID = row[0]
  conn.commit()
  conn.close()
 except py.InterfaceError:
  print("數(shù)據(jù)庫連接出錯")
 except py.ProgrammingError:
  print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 return ID
 
 
def readStudentID(idnum):
 StudentID = -1
 try:
  conn = py.connect(server, user, password, "student_message") # 獲取連接
  cursor =conn.cursor()
  cursor.execute(' select StudentID from students where ID='+str(idnum))
  for row in cursor:
   if row[0]!=[]:
    StudentID = row[0]
  conn.commit()
  conn.close()
 except py.InterfaceError:
  print("數(shù)據(jù)庫連接出錯")
 except py.ProgrammingError:
  print("數(shù)據(jù)錯誤，請檢查輸入的數(shù)據(jù)")
 return StudentID
 
 
# 關(guān)閉連接
# conn.close()
#
# # 注：在任何時候，在一個連接下，一次正在執(zhí)行的數(shù)據(jù)庫操作只會出現(xiàn)一個cursor對象

import pymssql as py
import time
import pandas as pd
 
server = "DESKTOP-XXXX"# 連接服務(wù)器地址
user = "sa" # 連接帳號
password = "123" # 連接密碼
conn = py.connect(server, user, password, "student_message") #獲取連接
cursor = conn.cursor() # 獲取光標(biāo)
 
def insertd(idnum,Name,StudentID,Sex): # 簽到
 conn = py.connect(server, user, password, "student_message") # 獲取連接
 timenow = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
 cursor = conn.cursor()
 cursor.execute("INSERT INTO qiandao VALUES (%d, %s,%d,%s, %s, %s, %s,%d )", (idnum,Name,StudentID,Sex,timenow, '0', '0', 0))
 conn.commit()
 # 必須調(diào)用 commit() 來保持?jǐn)?shù)據(jù)的提交
def insertt(idnum,Name,StudentID,Sex): # 簽退
 conn = py.connect(server, user, password, "student_message") # 獲取連接
 cursor = conn.cursor()
 cursor.execute("SELECT starttime FROM qiandao WHERE ID=%s and flag=%d",(idnum,0))
 starttimeget = cursor.fetchone()
 sat = str(tuple(starttimeget))
 timenow = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
 timeArray = time.strptime(sat, "('%Y-%m-%d %H:%M:%S',)")
 timeStamp = int(time.mktime(timeArray))
 timecout = time.time() - timeStamp
 m, s = divmod(timecout, 60)
 h, m = divmod(m, 60)
 timepass = str(h) + '小時 ' + str(m) + '分鐘 ' + str(s) + '秒'
 print(timepass)
 cursor.executemany("INSERT INTO qiandao VALUES (%d, %s,%d,%s, %s, %s, %s,%d )",
  [(idnum,Name,StudentID,Sex,'0', timenow, timepass, 1)])
 conn.commit()
 
def peoson_sign(StudentID):# 導(dǎo)出學(xué)生信息表_按照學(xué)號
 conn = py.connect(server, user, password, "student_message") # 獲取連接
 cursor = conn.cursor()
 sql = "select * from qiandao where StudentID=" + str(StudentID)
 df = pd.read_sql(sql, conn)
 df.to_excel(r'E:\01STUDY\20190701\work\openVersion\excel\studentID_sign.xlsx',index=False)
 print('ok')
 conn.commit()
 
#peoson_sign(2016002105)
 
def sign():# 導(dǎo)出簽到表
 conn = py.connect(server, user, password, "student_message") # 獲取連接
 cursor = conn.cursor()
 sql = "select * from qiandao"
 df = pd.read_sql(sql, conn)
 df.to_excel(r'E:\01STUDY\20190701\work\openVersion\excel\sign_all.xlsx', index=False)
 print('ok')
 conn.commit()
#sign()
 
def total_time():# 導(dǎo)出時長表#sign()
 conn = py.connect(server, user, password, "student_message") # 獲取連接
 cursor = conn.cursor()
 sql = "select * from qiandao where convert(nvarchar(max),count) != convert(nvarchar(max),0)"
 df = pd.read_sql(sql, conn)
 df.to_excel(r'E:\01STUDY\20190701\work\openVersion\excel\total_time.xlsx', index=False)
 print('ok')
 conn.commit()
 
#
# if __name__=='__main':
#  sign()
#  #peoson_sign(2016002105)
 
conn.close()