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

namespace IOM_cs.irisDb.service.sqliteImpl
{
    public class RecognitionService_SqliteImpl : IRecognitionService
    {
        /// <summary>
        /// 获取所有虹膜编码
        /// </summary>
        public DataTable getAllIrisCodeLocal()
        {
            DataTable dt = new DataTable();
            SQLiteTransaction tx = null;
            try
            {                
                tx = _SqliteHelper.sqliteConnection.BeginTransaction();
                string sQry =
                    "SELECT PERSON_ID, IRIS_L1, IRIS_L2, IRIS_R1, IRIS_R2 FROM iris_data";
                SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.CommandTimeout = 0;
                //绑定事务
                aCommand.Transaction = tx;               
                
                aCommand.ExecuteNonQuery();
                tx.Commit();
               
                
                SQLiteDataAdapter adapter = new SQLiteDataAdapter();
                adapter.SelectCommand = aCommand;
                              
                
                adapter.Fill(dt); 
            }
            catch (SQLiteException ex)
            {
                tx.Rollback();                
                if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return null;
                }
                MessageBox.Show("EXCEPTION: " + ex.Message);
            }
            finally
            {
                tx.Dispose();
            }
            return dt;
        }

        /// <summary>
        /// 获取人员基本信息
        /// </summary>
        public int GetInfoById(Int64 personId,ref string deptName, ref Person person)
        {
            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();

                // 获取部门名称
                sQry = "SELECT SIMPLENAME FROM sys_dept WHERE ID=@Id";
                aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
                aCommand.Parameters.AddWithValue("@Id", person.deptId);

                SQLiteDataReader aReader = aCommand.ExecuteReader(CommandBehavior.Default);
                if (aReader.Read())
                {
                    deptName = aReader["SIMPLENAME"].ToString();
                }
                aReader.Close();
                aCommand.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>
        /// 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;
        }


    }
}