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.Threading.Tasks;

namespace Face.Services.DBUtility.Common
{
    public class DALHelper<TEntity> where TEntity : class, new()
    {
        private string databasetablename; //数据库表名前缀

        //private DbConfigsInfo confInfi;

        public DALHelper()
        {
            databasetablename = "TBL_UTS_Manage";
        }
        public DALHelper(string _databaseprefix)
        {
            databasetablename = _databaseprefix;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(TEntity entity, string strField, object keyValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databasetablename);
            strSql.Append(" where " + strField + "=@keyValue ");
            MySqlParameter[] parameters = {
                 new MySqlParameter("@keyValue",keyValue)
            };
            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId(TEntity entity, string strField)
        {
            return DbHelperMySQL.GetMaxID(strField, databasetablename);
        }

        //public bool Add(TEntity entity)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("insert into " + databasetablename + "(");

        //    PropertyInfo[] propertys = entity.GetType().GetProperties();// 获得此模型的公共属性

        //    List<SqlParameter> parameters = new List<SqlParameter>();
        //    foreach (PropertyInfo pi in propertys)
        //    {
        //        if (!pi.CanWrite) continue;
        //        strSql.Append(pi.Name + ",");
        //        strSql.Append(" values (");
        //    }
        //    strSql.Append(" ) values (");
        //    foreach (PropertyInfo pi in propertys)
        //    {
        //        if (!pi.CanWrite) continue;
        //        strSql.Append(pi.Name + "@,");
        //        parameters.Add(new SqlParameter("@" + pi.Name, pi.GetValue(entity)));
        //    }
        //    strSql.Append(");select @@IDENTITY");
        //    int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters.ToArray());
        //    if (rows > 0)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        ///// <summary>
        ///// 修改一个实体数据
        ///// </summary>
        ///// <param name="entity"></param>
        ///// <param name="strField"></param>
        ///// <param name="keyValue"></param>
        ///// <returns></returns>
        //public bool Update(TEntity entity, string strField, object keyValue)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("update " + databasetablename + " set ");

        //    PropertyInfo[] propertys = entity.GetType().GetProperties();// 获得此模型的公共属性

        //    List<SqlParameter> parameters = new List<SqlParameter>();
        //    foreach (PropertyInfo pi in propertys)
        //    {
        //        if (!pi.CanWrite) continue;
        //        strSql.Append(pi.Name + "=@" + pi.Name + ",");
        //        parameters.Add(new SqlParameter("@" + pi.Name, pi.GetValue(entity)));
        //    }
        //    strSql.Append(" where " + strField + "=@strValue");
        //    parameters.Add(new MySqlParameter("@strValue", keyValue));
        //    int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters.ToArray());
        //    if (rows > 0)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public int UpdateField(TEntity entity, string strField, object strValue, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databasetablename);
            strSql.Append(" set " + strField + "=@strValue");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            SqlParameter[] parameters = {
                 new SqlParameter("@strValue",strValue)
            };

            return DbHelperMySQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(TEntity entity, string strField, object keyValue)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + databasetablename);
            strSql.Append(" where " + strField + "=@keyValue ");
            MySqlParameter[] parameters = {
                 new MySqlParameter("@keyValue",keyValue)
            };

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>MySqlDataReader</returns>
        //public static void ExecuteReader(string strSQL, Action<MySqlDataReader> func)
        //{
        //    try
        //    {
        //        using (MySqlConnection connection = new MySqlConnection(connectionString))
        //        {
        //            using (MySqlCommand cmd = new MySqlCommand(strSQL, connection))
        //            {
        //                connection.Open();

        //                using (MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        //                {
        //                    func(myReader);

        //                }
        //            }
        //        }

        //    }
        //    catch (MySqlException e)
        //    {
        //        throw e;
        //    }

        //}


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public TEntity GetModel(TEntity entity, string strField, string keyValue)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from " + databasetablename);
            strSql.Append(" where " + strField + "=@keyValue ");
            strSql.Append(" LIMIT 0,1;");
            MySqlParameter[] parameters = {
                 new MySqlParameter("@keyValue",keyValue)
            };

            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(entity, ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        public List<TEntity> GetList(string strWhere = null)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from " + databasetablename);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            //strSql.Append(" LIMIT 0,1;");
            TEntity entity = new TEntity();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModels(entity, ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 将DataTable转换得到一个对象实体
        /// </summary>
        /// <param name="model"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public TEntity DataRowToModel(TEntity model, DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {
                TEntity t = new TEntity();
                PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
                foreach (PropertyInfo pi in propertys)
                {
                    if (dt.Columns.Contains(pi.Name))
                    {
                        if (!pi.CanWrite) continue;
                        var value = dt.Rows[0][pi.Name];
                        if (value != DBNull.Value)
                        {
                            switch (pi.PropertyType.FullName)
                            {
                                case "System.Decimal":
                                    pi.SetValue(t, decimal.Parse(value.ToString()), null);
                                    break;
                                case "System.String":
                                    pi.SetValue(t, value.ToString(), null);
                                    break;
                                case "System.Single":
                                    pi.SetValue(t, float.Parse(value.ToString()), null);
                                    break;
                                case "System.Double":
                                    pi.SetValue(t, double.Parse(value.ToString()), null);
                                    break;
                                case "System.Int32":
                                    pi.SetValue(t, int.Parse(value.ToString()), null);
                                    break;
                                case "System.DateTime":
                                    pi.SetValue(t, DateTime.Parse(value.ToString()), null);
                                    break;
                                case "System.Boolean":
                                    pi.SetValue(t, bool.Parse(value.ToString()), null);
                                    break;
                                default:
                                    pi.SetValue(t, value, null);
                                    break;
                            }
                        }
                    }
                }
                return t;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 将DataTable转换得到一个对象实体集合
        /// </summary>
        /// <param name="model"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public List<TEntity> DataRowToModels(TEntity model, DataTable dt)
        {
            List<TEntity> ts = new List<TEntity>();// 定义集合
            foreach (DataRow dr in dt.Rows)
            {
                TEntity t = new TEntity();
                PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
                foreach (PropertyInfo pi in propertys)
                {
                    if (dt.Columns.Contains(pi.Name))
                    {
                        if (!pi.CanWrite) continue;
                        var value = dr[pi.Name];
                        if (value != DBNull.Value)
                        {
                            switch (pi.PropertyType.FullName)
                            {
                                case "System.Decimal":
                                    pi.SetValue(t, decimal.Parse(value.ToString()), null);
                                    break;
                                case "System.String":
                                    pi.SetValue(t, value.ToString(), null);
                                    break;
                                case "System.Single":
                                    pi.SetValue(t, float.Parse(value.ToString()), null);
                                    break;
                                case "System.Double":
                                    pi.SetValue(t, double.Parse(value.ToString()), null);
                                    break;
                                case "System.Int32":
                                    pi.SetValue(t, int.Parse(value.ToString()), null);
                                    break;
                                case "System.DateTime":
                                    pi.SetValue(t, DateTime.Parse(value.ToString()), null);
                                    break;
                                case "System.Boolean":
                                    pi.SetValue(t, bool.Parse(value.ToString()), null);
                                    break;
                                default:
                                    pi.SetValue(t, value, null);
                                    break;
                            }
                        }
                    }
                }
                ts.Add(t);
            }
            return ts;
        }


    }
}