using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;

using System.Data.Entity;

namespace iWare_SCADA_BusinessLogical.DLA.Dao
{
    /// <summary>
    /// 公用方法的抽象类
    /// 张展
    /// </summary>
    /// <typeparam name="T">自定义的实体类</typeparam>
    /// <typeparam name="S">ORM中的实体类</typeparam>
    public abstract class CommonDao<T, S> where T : class, ICommonEntity<S>  where S : class
    {

        #region 需要在子类中实现的抽象方法
        /// <summary>
        /// 该方法获取在自定义实体和ORM自动生成实体之间字段的对应关系
        /// </summary>
        protected abstract string GetColumnName(string name);
        
        /// <summary>
        /// 获取T中属性名列表
        /// </summary>
        /// <returns>T中属性名列表</returns>
        protected abstract List<string> GetColumnNames();

        /// <summary>
        ///  该方法将Orm的S对象转换为自定义类型T的对象
        /// </summary>
        /// <param name="s">Orm中的S实体的实例</param>
        /// <returns>自定义的T类型实例</returns>
        protected abstract T ToEntity(S s);

        /// <summary>
        /// 该方法将定义类型T的S对象转换为自Orm的对象
        /// </summary>
        /// <param name="t">自定义T类型的对象</param>
        /// <returns>Orm中S的对象</returns>
        protected abstract S ToOrm(T t);

        /// <summary>
        /// 该方法获取在自定义实体和ORM自动生成实体之间字段的对应关系
        /// </summary>
        protected abstract string GetTableName();
        #endregion

        #region 根据条件查询

        /// <summary>
        /// 根据条件查询自定义实体T的列表
        /// </summary>
        /// <param name="param">查询条件</param>
        /// <param name="msg">错误信息</param>
        /// <returns>自定义实体T的记录列表</returns>
        public virtual List<T> QueryByParam(QueryParam param, DbContext dbModel)
        {
            var res = new List<T>();
            var filter = param.Filter == null || param.Filter.Count <= 0 ? new Dictionary<string, object>() : param.Filter;
            var notfilter = param.NotFilter == null || param.NotFilter.Count <= 0 ? new Dictionary<string, object>() : param.NotFilter;
            var search = param.Search == null || param.Search.Count <= 0 ? new Dictionary<string, object>() : param.Search;

            var leqd = param.Leqd == null || param.Leqd.Count <= 0 ? new Dictionary<string, object>() : param.Leqd;
            var geqd = param.Geqd == null || param.Geqd.Count <= 0 ? new Dictionary<string, object>() : param.Geqd;
            var ltd = param.Ltd == null || param.Ltd.Count <= 0 ? new Dictionary<string, object>() : param.Ltd;
            var gtd = param.Gtd == null || param.Gtd.Count <= 0 ? new Dictionary<string, object>() : param.Gtd;
            var leq = param.Leq == null || param.Leq.Count <= 0 ? new Dictionary<string, object>() : param.Leq;
            var geq = param.Geq == null || param.Geq.Count <= 0 ? new Dictionary<string, object>() : param.Geq;
            var lt = param.Lt == null || param.Lt.Count <= 0 ? new Dictionary<string, object>() : param.Lt;
            var gt = param.Gt == null || param.Gt.Count <= 0 ? new Dictionary<string, object>() : param.Gt;
            var neq = param.Neq == null || param.Neq.Count <= 0 ? new Dictionary<string, object>() : param.Neq;
            var order = param.Order == null || param.Order.Count <= 0 ? new Dictionary<string, object>() { { "Id", "DESC" } } : param.Order;

            var sql = string.Format("SELECT * FROM {0} WHERE 1=1", GetTableName());

            var sqlParams = new List<SqlParameter>();

            foreach (var key in filter.Keys)
            {
                if (filter[key] != null && !string.IsNullOrEmpty(filter[key] + ""))
                {

                    sqlParams.Add(new SqlParameter(("@F_" + key), filter[key]));
                    sql += string.Format(" AND {0} = {1}", GetColumnName(key), "@F_" + key);
                }
            }
            foreach (var key in notfilter.Keys)
            {
                if (notfilter[key] != null && !string.IsNullOrEmpty(notfilter[key] + ""))
                {

                    sqlParams.Add(new SqlParameter(("@F_" + key), notfilter[key]));
                    sql += string.Format(" AND {0} != {1}", GetColumnName(key), "@F_" + key);
                }
            }

            foreach (var key in search.Keys)
            {
                if (search[key] != null && !string.IsNullOrEmpty(search[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@S_" + key, "%" + search[key] + "%"));
                    sql += string.Format(" AND {0} LIKE {1}", GetColumnName(key), "@S_" + key);
                }
            }

            foreach (var key in leqd.Keys)
            {
                if (leqd[key] != null && !string.IsNullOrEmpty(leqd[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@LEQD_" + key, leqd[key]));
                    sql += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQD_" + key);
                }
            }


            foreach (var key in geqd.Keys)
            {
                if (geqd[key] != null && !string.IsNullOrEmpty(geqd[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@GEQD_" + key, geqd[key]));
                    sql += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQD_" + key);
                }
            }

            foreach (var key in ltd.Keys)
            {
                if (ltd[key] != null && !string.IsNullOrEmpty(ltd[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@LTD_" + key, ltd[key]));
                    sql += string.Format(" AND {0} < {1}", GetColumnName(key), "@LTD_" + key);
                }
            }
            
            foreach (var key in gtd.Keys)
            {
                if (gtd[key] != null && !string.IsNullOrEmpty(gtd[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@GTD_" + key, gtd[key]));
                    sql += string.Format(" AND {0} > {1}", GetColumnName(key), "@GTD_" + key);
                }
            }
            
            foreach (var key in leq.Keys)
            {
                if (leq[key] != null && !string.IsNullOrEmpty(leq[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@LEQ_" + key, leq[key]));
                    sql += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQ_" + key);
                }
            }
            
            foreach (var key in geq.Keys)
            {
                if (geq[key] != null && !string.IsNullOrEmpty(geq[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@GEQ_" + key, geq[key]));
                    sql += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQ_" + key);
                }
            }
            
            foreach (var key in lt.Keys)
            {
                if (lt[key] != null && !string.IsNullOrEmpty(lt[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@LT_" + key, lt[key]));
                    sql += string.Format(" AND {0} < {1}", GetColumnName(key), "@LT_" + key);
                }
            }
            
            foreach (var key in gt.Keys)
            {
                if (gt[key] != null && !string.IsNullOrEmpty(gt[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@GT_" + key, gt[key]));
                    sql += string.Format(" AND {0} > {1}", GetColumnName(key), "@GT_" + key);
                }
            }

            foreach (var key in neq.Keys)
            {
                if (neq[key] != null && !string.IsNullOrEmpty(neq[key] + ""))
                {
                    sqlParams.Add(new SqlParameter("@NEQ_" + key, neq[key]));
                    sql += string.Format(" AND {0} <> {1}", GetColumnName(key), "@NEQ_" + key);
                }
            }

            sql += " ORDER BY ";

            foreach (var key in order.Keys)
            {

                var value = string.IsNullOrEmpty(order[key].ToString()) ? "DESC" : order[key].ToString().ToUpper();

                sqlParams.Add(new SqlParameter("@O_" + key, order[key]));
                sql += string.Format("{0} {1},", GetColumnName(key), value);

            }

            if (sql.EndsWith(","))
            {
                sql = sql.Substring(0, sql.Length - 1);
            }
            var datas = dbModel.Database.SqlQuery<S>(sql, sqlParams.ToArray()).ToList();
            datas.ForEach(x => res.Add(ToEntity(x)));

            
            return res;         
        }

        #endregion

        #region 根据条件分页查询
        /// <summary>
        /// 根据条件分页查询自定义实体T的列表，同时返回记录的总条数及当前所在的页数
        /// </summary>
        /// <param name="param">查询条件</param>
        /// <param name="msg">异常错误消息</param>
        /// <param name="totalNum">记录的总条数</param>
        /// <param name="currentPage">当前页面数</param>
        /// <returns>自定义实体T的记录列表</returns>
        public virtual List<T> QueryByParam(QueryParam param, DbContext dbModel, out int totalNum, out int currentPage)
        {
         
            totalNum = 0;
            currentPage = 1;

            var res = new List<T>();

            var filter = param.Filter == null || param.Filter.Count <= 0 ? new Dictionary<string, object>() : param.Filter;
            var search = param.Search == null || param.Search.Count <= 0 ? new Dictionary<string, object>() : param.Search;

            var leqd = param.Leqd == null || param.Leqd.Count <= 0 ? new Dictionary<string, object>() : param.Leqd;
            var geqd = param.Geqd == null || param.Geqd.Count <= 0 ? new Dictionary<string, object>() : param.Geqd;
            var ltd = param.Ltd == null || param.Ltd.Count <= 0 ? new Dictionary<string, object>() : param.Ltd;
            var gtd = param.Gtd == null || param.Gtd.Count <= 0 ? new Dictionary<string, object>() : param.Gtd;
            var leq = param.Leq == null || param.Leq.Count <= 0 ? new Dictionary<string, object>() : param.Leq;
            var geq = param.Geq == null || param.Geq.Count <= 0 ? new Dictionary<string, object>() : param.Geq;
            var lt = param.Lt == null || param.Lt.Count <= 0 ? new Dictionary<string, object>() : param.Lt;
            var gt = param.Gt == null || param.Gt.Count <= 0 ? new Dictionary<string, object>() : param.Gt;
            var neq = param.Neq == null || param.Neq.Count <= 0 ? new Dictionary<string, object>() : param.Neq;
            var pageIndex = param.PageIndex < 1 ? 1 : param.PageIndex;
            var pageSize = param.PageSize < 1 ? 5 : param.PageSize;
            var order = param.Order == null || param.Order.Count <= 0 ? new Dictionary<string, object>() { { "Id", "DESC" } } : param.Order;

            //查询数量的sql语句
            var sqlCount = string.Format("SELECT COUNT(*) FROM {0} WHERE 1=1", GetTableName());

            //查询数量使用的sql参数列表
            var sqlParamsCount = new List<SqlParameter>();

            var orderStr = "";


            foreach (var key in order.Keys)
            {
                var value = string.IsNullOrEmpty(order[key].ToString()) ? "DESC" : order[key].ToString().ToUpper();
                sqlParamsCount.Add(new SqlParameter("@O_" + key, order[key]));
                orderStr += string.Format("{0} {1},", GetColumnName(key), value);
            }

            if (orderStr.EndsWith(","))
            {
                orderStr = orderStr.Substring(0, orderStr.Length - 1);
            }

            //查询记录列表的sql语句
            var sqlData = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY {0} ) AS ROWNUM from {1} where 1=1", orderStr, GetTableName());
            
            //查询记录列表使用的sql参数列表
            var sqlParamsData = new List<SqlParameter>();

            foreach (var key in filter.Keys)
            {
                if (filter[key] != null && !string.IsNullOrEmpty(filter[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@F_" + key, filter[key]));
                    sqlParamsData.Add(new SqlParameter("@F_" + key, filter[key]));
                    sqlCount += string.Format(" AND {0} = {1}", GetColumnName(key), "@F_" + key);
                    sqlData += string.Format(" AND {0} = {1}", GetColumnName(key), "@F_" + key);
                }
            }

            foreach (var key in search.Keys)
            {
                if (search[key] != null && !string.IsNullOrEmpty(search[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@S_" + key, "%" + search[key] + "%"));
                    sqlParamsData.Add(new SqlParameter("@S_" + key, "%" + search[key] + "%"));
                    sqlCount += string.Format(" AND {0} LIKE {1}", GetColumnName(key), "@S_" + key);
                    sqlData += string.Format(" AND {0} LIKE {1}", GetColumnName(key), "@S_" + key);
                }
            }

            foreach (var key in leqd.Keys)
            {
                if (leqd[key] != null && !string.IsNullOrEmpty(leqd[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@LEQD_" + key, leqd[key]));
                    sqlParamsData.Add(new SqlParameter("@LEQD_" + key, leqd[key]));
                    sqlCount += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQD_" + key);
                    sqlData += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQD_" + key);
                }
            }

            foreach (var key in geqd.Keys)
            {
                if (geqd[key] != null && !string.IsNullOrEmpty(geqd[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@GEQD_" + key, geqd[key]));
                    sqlParamsData.Add(new SqlParameter("@GEQD_" + key, geqd[key]));
                    sqlCount += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQD_" + key);
                    sqlData += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQD_" + key);
                }
            }

            foreach (var key in ltd.Keys)
            {
                if (ltd[key] != null && !string.IsNullOrEmpty(ltd[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@LTD_" + key, ltd[key]));
                    sqlParamsData.Add(new SqlParameter("@LTD_" + key, ltd[key]));
                    sqlCount += string.Format(" AND {0} < {1}", GetColumnName(key), "@LTD_" + key);
                    sqlData += string.Format(" AND {0} < {1}", GetColumnName(key), "@LTD_" + key);
                }
            }

            foreach (var key in gtd.Keys)
            {
                if (gtd[key] != null && !string.IsNullOrEmpty(gtd[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@GTD_" + key, gtd[key]));
                    sqlParamsData.Add(new SqlParameter("@GTD_" + key, gtd[key]));
                    sqlCount += string.Format(" AND {0} > {1}", GetColumnName(key), "@GTD_" + key);
                    sqlData += string.Format(" AND {0} > {1}", GetColumnName(key), "@GTD_" + key);
                }
            }

            foreach (var key in leq.Keys)
            {
                if (leq[key] != null && !string.IsNullOrEmpty(leq[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@LEQ_" + key, leq[key]));
                    sqlParamsData.Add(new SqlParameter("@LEQ_" + key, leq[key]));
                    sqlCount += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQ_" + key);
                    sqlData += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQ_" + key);
                }
            }

            foreach (var key in geq.Keys)
            {
                if (geq[key] != null && !string.IsNullOrEmpty(geq[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@GEQ_" + key, geq[key]));
                    sqlParamsData.Add(new SqlParameter("@GEQ_" + key, geq[key]));
                    sqlCount += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQ_" + key);
                    sqlData += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQ_" + key);
                }
            }

            foreach (var key in lt.Keys)
            {
                if (lt[key] != null && !string.IsNullOrEmpty(lt[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@LT_" + key, lt[key]));
                    sqlParamsData.Add(new SqlParameter("@LT_" + key, lt[key]));
                    sqlCount += string.Format(" AND {0} < {1}", GetColumnName(key), "@LT_" + key);
                    sqlData += string.Format(" AND {0} < {1}", GetColumnName(key), "@LT_" + key);
                }
            }

            foreach (var key in gt.Keys)
            {
                if (gt[key] != null && !string.IsNullOrEmpty(gt[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@GT_" + key, gt[key]));
                    sqlParamsData.Add(new SqlParameter("@GT_" + key, gt[key]));
                    sqlCount += string.Format(" AND {0} > {1}", GetColumnName(key), "@GT_" + key);
                    sqlData += string.Format(" AND {0} > {1}", GetColumnName(key), "@GT_" + key);
                }
            }

            foreach (var key in neq.Keys)
            {
                if (neq[key] != null && !string.IsNullOrEmpty(neq[key] + ""))
                {
                    sqlParamsCount.Add(new SqlParameter("@NEQ_" + key, neq[key]));
                    sqlParamsData.Add(new SqlParameter("@NEQ_" + key, neq[key]));
                    sqlCount += string.Format(" AND {0} <> {1}", GetColumnName(key), "@NEQ_" + key);
                    sqlData += string.Format(" AND {0} <> {1}", GetColumnName(key), "@NEQ_" + key);
                }
            }

            totalNum = dbModel.Database.SqlQuery<int>(sqlCount, sqlParamsCount.ToArray()).ToList()[0];

            int totalPage = Convert.ToInt32(totalNum / pageSize);

            if ((totalNum % pageSize) > 0)
            {
                totalPage += 1;
            }

            currentPage = pageIndex > totalPage ? totalPage : pageIndex;
            currentPage = Math.Max(currentPage,1);
            sqlParamsData.Add(new SqlParameter("@PI_Start", (currentPage - 1) * pageSize + 1));
            sqlParamsData.Add(new SqlParameter("@PI_End", currentPage * pageSize));
            sqlData += string.Format(") t Where t.ROWNUM   between {0} and {1} ", "@PI_Start", "@PI_End");

            var datas = dbModel.Database.SqlQuery<S>(sqlData, sqlParamsData.ToArray()).ToList();

           

            datas.ForEach(x => res.Add(ToEntity(x)));
            return res;
        }

        #endregion

        #region 插入新的对象
        
        /// <summary>
        /// 将自定义的实体T的实例列表批量保存到数据库
        /// </summary>
        /// <param name="ts">自定义类型T的实例列表</param>
        /// <param name="msg">异常错误消息</param>
        /// <returns>保存的数量</returns>
        public virtual int Save(List<T> ts, DbContext dbModel)
        {
            if (ts == null)
            {
                return 0;
            }
            ts.ForEach(x => dbModel.Set<S>().Add(ToOrm(x)));
            dbModel.SaveChanges();
            return ts.Count;
        }

        /// <summary>
        /// 将自定义的实体T的实例保存到数据库
        /// </summary>
        /// <param name="t">自定义类型T的实例</param>
        /// <param name="msg">异常错误消息</param>
        /// <returns>新添加T对象的id</returns>
        public virtual int Save(T t, DbContext dbModel)
        {
            var s = ToOrm(t);
            dbModel.Set<S>().Add(s);
            dbModel.SaveChanges();
            return ToEntity(s).Id;
        }

        #endregion

        #region 修改对象
        /// <summary>
        /// 修改自定义类型T的实体并保存到数据库
        /// </summary>
        /// <param name="t">自定义类型T的实例</param>
        /// <param name="msg">异常错误消息</param>
        /// <returns>需改实体的id</returns>
        public virtual int Update(T t, DbContext dbModel)
        {       
            var entry = dbModel.Entry<S>(ToOrm(t));
            entry.State = System.Data.Entity.EntityState.Unchanged;
            var propStrs = GetColumnNames();
            propStrs.ForEach(x => entry.Property(GetColumnName(x)).IsModified = true);
            dbModel.SaveChanges();
            return t.Id;
        }

        /// <summary>
        /// 修改自定义类型T的实体并保存到数据库
        /// </summary>
        /// <param name="t">自定义类型T的实例</param>
        /// <param name="msg">异常错误消息</param>
        /// <returns>需改实体的id</returns>
        /// 
        public virtual int Update(List<T> ts, DbContext dbModel)
        {
            foreach (var t in ts)
            {
                var entry = dbModel.Entry<S>(ToOrm(t));
                entry.State = System.Data.Entity.EntityState.Unchanged;
                var propStrs = GetColumnNames();
                propStrs.ForEach(x => entry.Property(GetColumnName(x)).IsModified = true);
            }
            dbModel.SaveChanges();
            return ts.Count;
        }
        
        #endregion

        #region 删除对象 
        
        /// <summary>
        /// 从数据库中删除主键为id的记录
        /// </summary>
        /// <param name="id">需要删除对象的id</param>
        /// <param name="msg">异常错误信息</param>
        /// <returns>被删除对象的主键</returns>
        public virtual int Delete(int id, DbContext dbModel)
        {
            var count = Delete(new List<int> { id }, dbModel);
            return id;
        }

        /// <summary>
        /// 从数据库中批量删除主键列表为ids的记录
        /// </summary>
        /// <param name="id">需要删除对象的id列表</param>
        /// <param name="msg">异常错误信息</param>
        /// <returns>被删除对象的数量</returns>
        public virtual int Delete(List<int> ids, DbContext dbModel)
        {
            foreach (var id in ids)
            {
                var t = Activator.CreateInstance(typeof(T));
                ((T)t).Id = id;
                var s = ToOrm((T)t);
                dbModel.Set<S>().Attach(s);
                dbModel.Set<S>().Remove(s);
            }


            dbModel.SaveChanges();
            return ids.Count;   
        }
        #endregion


        #region 拼装字典
        /// <summary>
        /// 将满足条件的T类型拼装成以id为键，类型本身为值的字典
        /// </summary>
        /// <param name="param">查询条件</param>
        /// <param name="msg">异常错误消息</param>
        /// <returns>以id为键，类型本身为值的字典</returns>
        public virtual Dictionary<int, T> ToDictionary(QueryParam param, DbContext dbModel)
        {
            var res = new Dictionary<int, T>();
            var datas = QueryByParam(param, dbModel);
            datas.ForEach(x => res.Add(x.Id,x));
            return res;
        }

        #endregion
    }

}