using IOM_cs.irisDb.model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;

namespace IOM_cs.irisDb.service.mySqlImpl
{
    public class IrisCollViewService_MysqlImpl:IIrisCollViewService
    {

        public DataTable GetColl(int baseNum, int i, string searchStr)
        {
            String strSql = "select IR_ID,S_ID,NAME, CARD_TYPE, ID_CARD_NO, COLUMN_3, COLL_TIME," +
                " COLL_REASON,FLG_UPLOAD" +
                " from iris_coll_view where 1=1 ";

            if (searchStr != "")
                strSql += searchStr;

            strSql += " order by coll_time asc limit " + baseNum * (i - 1) + "," + baseNum;

            MySqlCommand aCommand = new MySqlCommand(strSql, _MySqlHelper.mySqlConnection);
            aCommand.CommandTimeout = 0;
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = aCommand;
            DataTable dt = new DataTable();

            try
            {
                adapter.Fill(dt);
                for (int k = 0; k < dt.Rows.Count; k++)
                {
                    dt.Rows[k][3] = Enum.GetName(typeof(cardType), Convert.ToInt16(dt.Rows[k][3].ToString()));
                    dt.Rows[k][7] = Enum.GetName(typeof(collReason), Convert.ToInt16(dt.Rows[k][7].ToString()));
                    if (dt.Rows[k][8].ToString() == "0")
                        dt.Rows[k][8] = "未上传";
                    else if (dt.Rows[k][8].ToString() == "1")
                        dt.Rows[k][8] = "已上传";
                    else if (dt.Rows[k][8].ToString() == "2")
                        dt.Rows[k][8] = "上传失败允许继续上传";
                    else if (dt.Rows[k][8].ToString() == "3")
                        dt.Rows[k][8] = "上传失败不允许继续上传";
                }
            }
            catch (SqlException exSqlDb)
            {
                if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return dt;
                }
                MessageBox.Show("GetUsersEXCEPTION: " + exSqlDb.Message);
            }
            catch (Exception ex)
            {
                if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return dt;
                }
                MessageBox.Show("GetUsersEXCEPTION: " + ex.Message);
            }

            return dt;
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <returns></returns>
        public int GetCollCount(string searchStr)
        {
            int count = 0;
            try
            {
                MySqlDataReader aReader = null;

                string sQry = "SELECT COUNT(*) FROM iris_coll_view where 1=1 ";

                if (searchStr != "")
                    sQry += searchStr;

                MySqlCommand cmd = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
                cmd.CommandTimeout = 0;
                aReader = cmd.ExecuteReader(CommandBehavior.Default);

                if (aReader.Read())
                {
                    count = aReader.GetInt32(0);
                }
                aReader.Close();
            }
            catch (SqlException s)
            {
                if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return count;
                }
                MessageBox.Show(s.Message.ToString());
            }

            return count;

        }

        /// <summary>
        /// 获取所有未上传数据的irid
        /// </summary>
        /// <returns></returns>
        public List<String> GetIrIdList()
        {
            //iris_coll_view
            List<String> iridList = new List<String>();

            try
            {

                // sys_person表
                string sQry = "SELECT IR_ID FROM iris_coll_view WHERE FLG_UPLOAD = 0 or FLG_UPLOAD = 2";
                MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
                aCommand.CommandTimeout = 0;
                MySqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.Default);
                int mun = 0;
                if (aReader.Read())
                {
                    String iridStr = aReader["IR_ID"].ToString();
                    iridList.Add(iridStr);
                }
                aReader.Close();
                aCommand.Dispose();

            }
            catch (MySqlException e)
            {
                if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return iridList;
                }
                MessageBox.Show("GetIrIdList Exception:" + e.Message);
            }
            return iridList;
        }

        public IrisCollView GetIrisCollViewByIrid(long irid)
        {
            IrisCollView irisCollView = new IrisCollView();
            try
            {
                string sQry = "SELECT * FROM iris_coll_view WHERE IR_ID=@irid";
                MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
                aCommand.CommandTimeout = 0;
                aCommand.Parameters.AddWithValue("@irid", irid);

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

                if (dt != null && dt.Rows.Count != 0)
                {
                    irisCollView = ConvertToEntity<IrisCollView>(dt, dt.Rows[0]);
                }
                aCommand.Dispose();
                adapter.Dispose();
            }
            catch (Exception ex)
            {
                if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
                {
                    MessageBox.Show("数据库连接断开，请重启软件！");
                    return irisCollView;
                }
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "获取上传视图失败：" + ex.Message);
            }
            return irisCollView;
        }

        /// <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;
        }
    }
}