using IOM_cs.irisDb.model;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Windows.Forms;

namespace IOM_cs.irisDb.service.sqliteImpl
{
    public class PersonService_SqliteImpl:IPersonService
    {
        public int deleteAllData()
        {
            int iRetval = -1;
            try
            {
                //iris_data表
                string sQry = "delete from iris_data";               
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                sQry = "update sqlite_sequence SET seq = 0 where name = 'iris_data'";//自增长ID为0";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();
                
                //sys_perosn
                sQry = "delete from sys_person";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                sQry = "update sqlite_sequence SET seq = 0 where name = 'sys_person'";//自增长ID为0";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();               

                iRetval = 0;
            }
            catch(Exception ex)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("EXCEPTION: " + ex.Message);
                iRetval = -1;
            }

            return iRetval;
        }

        #region person
        /// <summary>
        /// 获取数据库中的注册人员总数
        /// </summary>
        /// <returns></returns>
        public int GetCountOfPerson()
        {
            int count = 0;
            try
            {
                SQLiteDataReader aReader = null;

                string sQry = "SELECT COUNT(*) FROM sys_person";
                SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    count = aReader.GetInt32(0);
                }
                aReader.Close();
            }
            catch (SqlException s)
            { MessageBox.Show(s.Message.ToString()); }

            return count;

        }


        /// <summary>
        /// 判断此证件号码是否已存在
        /// </summary>
        public int IsIdCardNoPresent(string idCardNo)
        {
            //加密
            //sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);
            int mun = 0;
            try
            {
                string sQry = "SELECT COUNT(*) FROM sys_person WHERE ID_CARD_NO=@staffNo";
                SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                cmd.Parameters.Add
                    (
                    "@staffNo",
                   DbType.AnsiString,
                    100
                    ).Value = idCardNo.Trim();
                SQLiteDataReader aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    if (!aReader.IsDBNull(0)) mun = aReader.GetInt16(0);
                }
                aReader.Close();
                cmd.Dispose();
            }
            catch (SQLiteException e)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");

                }
                MessageBox.Show("IsIDCardNoPresentException:" + e.Message);
            }
            return mun;

        }

        /// <summary>
        /// 判断此员工编号是否已存在
        /// </summary>
        public int IsStaffNoPresent(string staffNo)
        {
            //加密
            //sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);
            int mun = 0;
            try
            {
                string sQry = "SELECT COUNT(*) FROM sys_person WHERE STAFF_NO=@staffNo";
                SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                cmd.Parameters.Add
                    (
                    "@staffNo",
                   DbType.AnsiString,
                    100
                    ).Value = staffNo.Trim();
                SQLiteDataReader aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    if (!aReader.IsDBNull(0)) mun = aReader.GetInt16(0);
                }
                aReader.Close();
                cmd.Dispose();
            }
            catch (SQLiteException e)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");

                }
                MessageBox.Show("IsIDCardNoPresentException:" + e.Message);
            }
            return mun;

        }

        /// <summary>
        /// sys_person,sys_person_ext新增人员
        /// </summary>
        public int AddPerson(Person person)
        {
            int re = -1;
            SQLiteTransaction tx = null;
            try
            {
                //sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);//加密

                tx = _SqliteHelper.sqliteConnection.BeginTransaction();


                string sQry =
                    "INSERT INTO sys_person (ID,CREATETIME,UPDATETIME,NAME,SEX,DEPTID,ID_CARD_NO,REMARKS,NATION,BIRTHDAY,PHOTO_DATA,STAFF_NO,ADDR,COLL_TYPE,CER_AUTH,COLL_TIME)" +
                                    "values (@var0,@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11, @var12, @var13, @var14, @var15)";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);


                aCommand.Transaction = tx; //绑定事务             

                aCommand.Parameters.Add(new SQLiteParameter("@var0", DbType.Int64));
                aCommand.Parameters.Add(new SQLiteParameter("@var1", DbType.DateTime));
                aCommand.Parameters.Add(new SQLiteParameter("@var2", DbType.DateTime));
                aCommand.Parameters.Add(new SQLiteParameter("@var3", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var4", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var5", DbType.Int64));
                aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.Date));
                aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var11", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var12", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var13", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var14", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var15", DbType.DateTime));

                aCommand.Parameters[0].Value = person.id;
                aCommand.Parameters[1].Value = DateTime.Now;
                aCommand.Parameters[2].Value = DateTime.Now;
                aCommand.Parameters[3].Value = person.name;
                aCommand.Parameters[4].Value = person.sex;
                aCommand.Parameters[5].Value = person.deptId;
                aCommand.Parameters[6].Value = person.idCardNo;
                aCommand.Parameters[7].Value = person.remarks;
                aCommand.Parameters[8].Value = person.nation;
                aCommand.Parameters[9].Value = person.birthday;
                aCommand.Parameters[10].Value = person.photoData;
                aCommand.Parameters[11].Value = person.staffNo;
                aCommand.Parameters[12].Value = person.addr; 
                aCommand.Parameters[13].Value = person.collType;
                aCommand.Parameters[14].Value = person.cerAuth;
                aCommand.Parameters[15].Value = DateTime.Now;

                aCommand.ExecuteNonQuery();
                tx.Commit();
                aCommand.Dispose();
                
                re = 0;

            }
            catch (SQLiteException ex)
            {
                tx.Rollback();
                re = -1;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("AddPerson EXCEPTION: " + ex.Message);
            }
            finally
            {
                tx.Dispose();
            }
            return re;
        }
        /// <summary>
        /// 删除人员，sys_person、iris_data、iris_data_coll物理删除
        /// </summary>
        public int DeletePerson(Int64 personId)
        {
            int iRetval = -1;
            try
            {
                string sQry = "delete FROM sys_person WHERE ID=@Id";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.AddWithValue("@Id", personId);


                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                sQry = "DELETE FROM iris_data WHERE PERSON_ID=@Id";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.AddWithValue("@Id", personId);

                iRetval = aCommand.ExecuteNonQuery();
                aCommand.Dispose();
                
                iRetval = 0;

            }
            catch (Exception ex)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("EXCEPTION: " + ex.Message);
                iRetval = -1;
            }

            return iRetval;

        }
        /// <summary>
        /// 获取人员详情,返回Person,IrisData
        /// </summary>
        public int GetDetail(Int64 personId, ref Person person, ref IrisData irisData)
        {
            int iRetval = 0;
            try
            {
                // sys_person表
                string sQry = "SELECT * FROM sys_person WHERE ID=@Id";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.AddWithValue("@Id", personId);

                SQLiteDataAdapter adapter = new SQLiteDataAdapter();
                adapter.SelectCommand = aCommand;
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                if (dt != null && dt.Rows.Count != 0)
                {
                    person = ConvertToEntity<Person>(dt, dt.Rows[0]);
                }
                aCommand.Dispose();
                adapter.Dispose();

                
                // iris_data表
                sQry = "SELECT * FROM iris_data WHERE PERSON_ID=@Id";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.AddWithValue("@Id", personId);
                adapter = new SQLiteDataAdapter();
                adapter.SelectCommand = aCommand;
                dt = null;
                dt = new DataTable();
                adapter.Fill(dt);
                if (dt != null && dt.Rows.Count != 0)
                {
                    irisData = ConvertToEntity<IrisData>(dt, dt.Rows[0]);
                }
                aCommand.Dispose();
                adapter.Dispose();

            }
            catch (SqlException exSqlDb)
            {
                iRetval = exSqlDb.ErrorCode;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("GetDetail EXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show("GetDetail EXCEPTION: " + ex.Message);
            }

            return iRetval;
        }
        /// <summary>
        /// 保存编辑，更新sys_person表
        /// </summary>
        public int Update(Person person)
        {
            int iRetval = -1;
            try
            {
                // sys_person表
                string sQry = "UPDATE sys_person set STAFF_NO=@var0,NAME=@var1,SEX=@var2,ID_CARD_NO=@var3, REMARKS = @var4,NATION=@var5, CER_AUTH=@var6,ADDR=@var7,BIRTHDAY=@var8,DEPTID=@var9,UPDATETIME=@var10 WHERE ID=@Id";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                
                aCommand.Parameters.Add(new SQLiteParameter("@var0", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var1", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var2", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var3", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var4", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var5", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.Date));
                aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.Int64));
                aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.DateTime));
                aCommand.Parameters.Add(new SQLiteParameter("@Id", DbType.Int64));

                aCommand.Parameters[0].Value = person.staffNo;
                aCommand.Parameters[1].Value = person.name;
                aCommand.Parameters[2].Value = person.sex;
                aCommand.Parameters[3].Value = person.idCardNo;
                aCommand.Parameters[4].Value = person.remarks;
                aCommand.Parameters[5].Value = person.nation;
                aCommand.Parameters[6].Value = person.cerAuth;
                aCommand.Parameters[7].Value = person.addr;
                aCommand.Parameters[8].Value = person.birthday;
                aCommand.Parameters[9].Value = person.deptId;
                aCommand.Parameters[10].Value = DateTime.Now;
                aCommand.Parameters[11].Value = person.id;

                aCommand.ExecuteNonQuery();
                aCommand.Dispose();
                
                iRetval = 0;
            }
            catch (SqlException exSqlDb)
            {
                iRetval = exSqlDb.ErrorCode;
                iRetval = -1;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                iRetval = -1;
                MessageBox.Show("Update EXCEPTION: " + ex.Message);
            }

            return iRetval;
        }

        /// <summary>
        /// 更新数据上传状态
        /// </summary>
        public int UpdateFlagUpload(long id, string flagUpload)
        {
            int iRetval = -1;
            try
            {
                // sys_person表
                string sQry = "UPDATE sys_person set FLAG_UPLOAD=@var0 WHERE ID=@Id";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);

                aCommand.Parameters.Add(new SQLiteParameter("@var0", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@Id", DbType.Int64));

                aCommand.Parameters[0].Value = flagUpload;
                aCommand.Parameters[1].Value = id;

                aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                iRetval = 0;
            }
            catch (SqlException exSqlDb)
            {
                iRetval = exSqlDb.ErrorCode;
                iRetval = -1;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                iRetval = -1;
                MessageBox.Show("Update EXCEPTION: " + ex.Message);
            }

            return iRetval;
        }

        public int deletePersonAndIrisById(long id)
        {
            int re = -1;
            try
            {
                // sys_person表
                string sQry = "delete from sys_person WHERE ID=@Id";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.Add(new SQLiteParameter("@Id", DbType.Int64));
                aCommand.Parameters[0].Value = id;
                aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                //iris_data
                sQry = "delete from iris_data WHERE PERSON_ID=@Id";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.Clear();
                aCommand.Parameters.Add(new SQLiteParameter("@Id", DbType.Int64));
                aCommand.Parameters[0].Value = id;
                aCommand.ExecuteNonQuery();
                aCommand.Dispose();

                re = 0;
            }
            catch (SqlException exSqlDb)
            {
                re = -1;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("deletePersonAndIrisById EXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                re = -1;
                MessageBox.Show("deletePersonAndIrisById EXCEPTION: " + ex.Message);
            }


            return re;
        }

        #endregion

       

        #region iris_data


        public int GetAllIrisCode(ref DataTable ds)
        {
            int re = -1;

            try
            {
                string sQry = "SELECT ID,PERSON_ID, IRIS_L1, IRIS_L2, IRIS_R1, IRIS_R2 FROM iris_data";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                SQLiteDataAdapter aAdapter = new SQLiteDataAdapter();
                aAdapter.SelectCommand = aCommand;

                aAdapter.Fill(ds);

                aAdapter.Dispose();
                 
                re = 0;
            }           
            catch (Exception ex)
            {
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！" + "EXCEPTION: " + ex.Message);
                    return -1;
                }                
            }


            return re;
        }

        /// <summary>
        /// iris_data新增虹膜数据
        /// </summary>
        public int AddIrisData(IrisData irisData)
        {
            int re = -1;
            SQLiteTransaction tx = null;
            try
            {
                tx = _SqliteHelper.sqliteConnection.BeginTransaction();
                string sQry =
                    "INSERT INTO iris_data(ID,PERSON_ID,IRIS_L1,IRIS_R1,IRIS_L2,IRIS_R2,IMAGE_L1_DATA,IMAGE_R1_DATA,IMAGE_L2_DATA,IMAGE_R2_DATA,Q_L1,Q_R1,Q_L2,Q_R2)" +
                    "values(@var0,@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11, @var12, @var13)";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);

                //绑定事务
                aCommand.Transaction = tx;
                aCommand.Parameters.Add(new SQLiteParameter("@var0", DbType.Int64));
                aCommand.Parameters.Add(new SQLiteParameter("@var1", DbType.Int64));
                aCommand.Parameters.Add(new SQLiteParameter("@var2", DbType.Binary));
                aCommand.Parameters.Add(new SQLiteParameter("@var3", DbType.Binary));
                aCommand.Parameters.Add(new SQLiteParameter("@var4", DbType.Binary));
                aCommand.Parameters.Add(new SQLiteParameter("@var5", DbType.Binary));
                aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var11", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var12", DbType.String));
                aCommand.Parameters.Add(new SQLiteParameter("@var13", DbType.String));

                aCommand.Parameters[0].Value = irisData.id;
                aCommand.Parameters[1].Value = irisData.personId;
                aCommand.Parameters[2].Value = irisData.irisL1;
                aCommand.Parameters[3].Value = irisData.irisR1;
                aCommand.Parameters[4].Value = irisData.irisL2;
                aCommand.Parameters[5].Value = irisData.irisR2;
                aCommand.Parameters[6].Value = irisData.imageL1Data;
                aCommand.Parameters[7].Value = irisData.imageR1Data;
                aCommand.Parameters[8].Value = irisData.imageL2Data;
                aCommand.Parameters[9].Value = irisData.imageR2Data;
                aCommand.Parameters[10].Value = irisData.qL1;
                aCommand.Parameters[11].Value = irisData.qR1;
                aCommand.Parameters[12].Value = irisData.qL2;
                aCommand.Parameters[13].Value = irisData.qR2;

                aCommand.ExecuteNonQuery();
                tx.Commit();
                aCommand.Dispose();

                re = 0;
            }
            catch (Exception ex)
            {
                tx.Rollback();
                re = -1;
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return -1;
                }
                MessageBox.Show("EXCEPTION: " + ex.Message);
            }
            finally
            {
                tx.Dispose();
            }

            return re;
        }
        #endregion

        /// <summary>
        /// DataTable转换为实体对象   
        /// </summary>
        private T ConvertToEntity<T>(DataTable dt, DataRow row) where T : new()
        {
            T t = new T();
            try
            {
                System.Data.DataColumnCollection columns = dt.Columns;
                int iColumnCount = columns.Count;
                int i;
                int j;

                Type elementType;
                elementType = t.GetType();
                System.Reflection.PropertyInfo[] publicProperties = elementType.GetProperties();
                //if (!(publicProperties.Length > iColumnCount))
                //{
                for (i = 0; i < iColumnCount; i++)
                {
                    for (j = 0; j < publicProperties.Length; j++)
                    {
                        if (columns[i].ColumnName.ToLower().Replace("_", "") == publicProperties[j].Name.ToLower().Replace("_", ""))
                        {
                            if (publicProperties[j].PropertyType == typeof(int))
                            {
                                int num = 0;
                                try
                                {
                                    num = Convert.ToInt32(row[i]);
                                }
                                catch
                                {
                                }
                                publicProperties[j].SetValue(t, num, null);
                            }
                            else
                                if (publicProperties[j].PropertyType == typeof(string) && row[i] == System.DBNull.Value)
                            {
                                publicProperties[j].SetValue(t, "", null);
                            }
                            else
                            {
                                object value = row[i] == System.DBNull.Value ? null : row[i];
                                publicProperties[j].SetValue(t, value, null);
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            return t;
        }      

    }
}