using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;
namespace FaceAttendance
{
    class DBHelper
    {
        public SQLiteConnection conn;
        public DBHelper()
        {
            ConnectDatabase();
        }
        private void ConnectDatabase()
        {
            conn = new SQLiteConnection("Data Source=..\\..\\..\\faceinfo.db3; verson = 3;"); //创建数据库实例，指定文件位置  
            conn.Open(); //打开数据库，若文件不存在会自动创建
            if (conn == null) throw new Exception("数据库连接失败。请检查数据库文件是否存在。");
        }
        public void insertperson(int Id,string name,string sex,string position,string faceinfo,string des)
        {
            string sql = null;
            if (des ==null)
            {
                sql = "insert into info values(" + 14061143 + ",‘" + name + "‘,‘" + sex + "‘,‘" + position + "‘," + des + ");";
            }
            else
            {
                sql = "insert into info values(" + 14061143 + ",‘" + name + "‘,‘" + sex + "‘,‘" + position + "‘,‘" + des + "‘);";
            }
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
            command.Dispose();
        }
        public void insertAttendance(int Id,string time)
        {
            string sql = "insert into signinfo values("+Id+",‘"+time+"‘);";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
            command.Dispose();
        }
        public List<peopleinfo> getpeopleinfo()
        {
            List<peopleinfo> faceRlt = new List<peopleinfo>();
            string sql = "SELECT * FROM info;";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            int j = 0;
            while (reader.Read())
            {
                j++;
                peopleinfo people = new peopleinfo();
                people.Id = reader.GetInt32(0);
                people.name = (string)reader["NAME"];
                people.sex = (string)reader["SEX"];
                people.position = (string)reader["JOB"];
                string fs = (string)reader["faceinfo"];
                string[] fsa = fs.Split(‘;‘);
                for (int i = 0; i < 128; i++)
                    people.faceinfo[i] = (float)Convert.ToDouble(fsa[i]);
                people.des = (string)reader["MSG"];
                faceRlt.Add(people);
            }
            command.Dispose();
            reader.Dispose();
            return faceRlt;
        }
        public void insertPersonRecord(string name,string time)
        {
            string sql = "insert into personRecord values(‘ " + name + "‘,‘" + time + "‘);";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
            command.Dispose();
        }
        public peopleinfo getPersonInfo(int Id)
        {
            string sql = "SELECT * FROM info WHERE Id = "+Id;
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            reader.Read();
            peopleinfo people = new peopleinfo();
            people.Id = reader.GetInt32(0);
            people.name = (string)reader["NAME"];
            people.sex = (string)reader["SEX"];
            people.position = (string)reader["JOB"];
            people.des = (string)reader["MSG"];
            return people;
        }
    }
}