using HH.WMS.Entitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using HH.WMS.Common;
using HH.WMS.Entitys.Common;
using HH.WMS.Common.External;
using Newtonsoft.Json;

namespace HH.WMS.DAL
{
    public class ErpDapperDAL<T> : ErpDapperBaseDAL where T : new()
    {
        #region 表名
        /// <summary>
        /// 表名
        /// </summary>
        private string TableName
        {
            get
            {
                var attribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
                string tableName = string.Empty;
                if (attribute != null)
                {
                    tableName = ((TableAttribute)attribute).Table;
                }
                return tableName;
            }
        }
        #endregion

        #region 新增sql语句
        /// <summary>
        /// 新增sql语句
        /// </summary>
        /// <returns></returns>
        public string AddSql()
        {
            PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO " + TableName);
            string fieldsSql = string.Empty;
            string _fieldsSql = string.Empty;
            foreach (var f in fields)
            {
                var columnData = f.GetCustomAttribute(typeof(ColumnAttribute));
                if (columnData == null) continue;
                fieldsSql += string.Format("{0},", f.Name);
                _fieldsSql += string.Format("@{0},", f.Name);
            }
            sqlBuilder.AppendLine("(");
            sqlBuilder.AppendLine(fieldsSql.Trim(','));
            sqlBuilder.AppendLine(")VALUES(");
            sqlBuilder.AppendLine(_fieldsSql.Trim(','));
            sqlBuilder.AppendLine(")");
            return sqlBuilder.ToString();
        }
        #endregion

        #region 修改sql语句，存在条件给实体赋值条件
        /// <summary>
        /// 修改sql语句，存在条件给实体赋值条件
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string UpdateSql(T entity, object where)
        {
            PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
            StringBuilder sqlBuilder = new StringBuilder("UPDATE " + TableName + " SET ");
            string updateSql = string.Empty;
            foreach (var f in fields)
            {
                var columnData = f.GetCustomAttribute(typeof(ColumnAttribute));
                if (columnData == null) continue;
                updateSql += string.Format("{0}=@{1},", f.Name, f.Name);
            }
            sqlBuilder.AppendLine(updateSql.Trim(',')).AppendLine("WHERE 1=1 ");
            string whereSql = string.Empty;
            if (where != null)
            {
                PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                foreach (var f in whereFields)
                {
                    whereSql += string.Format(" AND {0}=@{1} ", f.Name, f.Name);
                    var _f = fields.FirstOrDefault(p => p.Name.Equals(f.Name));
                    if (_f != null)
                        _f.SetValue(entity, f.GetValue(where, null));
                }
            }
            sqlBuilder.AppendLine(whereSql.Trim(','));
            return sqlBuilder.ToString();
        }
        #endregion

        #region 修改sql语句，动态修改
        /// <summary>
        /// 修改sql语句，动态修改
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="where"></param>
        /// <param name="mapEntity"></param>
        /// <returns></returns>
        public string UpdateSql(object entity, object where, T mapEntity)
        {
            PropertyInfo[] fields = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
            PropertyInfo[] entityFields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
            StringBuilder sqlBuilder = new StringBuilder("UPDATE " + TableName + " SET ");
            string updateSql = string.Empty;
            foreach (var f in fields)
            {
                if (entity.GetType() == typeof(T))
                {
                    var columnData = f.GetCustomAttribute(typeof(ColumnAttribute));
                    if (columnData == null) continue;
                }
                updateSql += string.Format("{0}=@{1},", f.Name, f.Name);
                //将更新字段给mapEntity 赋值 
                var _f = entityFields.FirstOrDefault(p => p.Name.Equals(f.Name));
                if (_f != null)
                    _f.SetValue(mapEntity, f.GetValue(entity, null));
            }
            sqlBuilder.AppendLine(updateSql.Trim(',')).AppendLine(" WHERE 1=1 ");
            string whereSql = string.Empty;
            if (where != null)
            {
                PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                foreach (var f in whereFields)
                {
                    whereSql += string.Format(" AND {0}=@{1} ", f.Name, f.Name);
                    //将条件字段给mapEntity 赋值 
                    var _f = entityFields.FirstOrDefault(p => p.Name.Equals(f.Name));
                    if (_f != null)
                        _f.SetValue(mapEntity, f.GetValue(where, null));
                }
            }
            sqlBuilder.AppendLine(whereSql.Trim(','));
            return sqlBuilder.ToString();
        }
        #endregion

        #region 删除 sql语句
        /// <summary>
        /// 删除 sql语句
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public string DeleteSql(object param)
        {
            StringBuilder sqlBuilder = new StringBuilder("DELETE FROM " + TableName + " WHERE 1=1 ");
            PropertyInfo[] fields = param.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
            string whereSql = string.Empty;
            foreach (var f in fields)
            {
                whereSql += string.Format(" AND {0}=@{1} ", f.Name, f.Name);
            }
            sqlBuilder.AppendLine(whereSql.Trim(','));
            return sqlBuilder.ToString();
        }
        #endregion

        //#region 查询sql语句
        ///// <summary>
        ///// 查询sql语句
        ///// </summary>
        ///// <param name="where"></param>
        ///// <returns></returns>
        //public string QuerySql(object where = null, bool filterEmpty = true)
        //{
        //    var sqlBuilder = new StringBuilder("SELECT * FROM " + TableName + " WHERE 1=1 ");
        //    if (where != null)
        //    {
        //        PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
        //        foreach (var f in whereFields)
        //        {
        //            Type t = f.PropertyType;
        //            if (t == typeof(List<string>))
        //            {
        //                List<string> list = (List<string>)f.GetValue(where, null);
        //                if (list == null || !list.Any()) continue;
        //                sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
        //                //list.ForEach(p =>
        //                //{
        //                //    sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
        //                //});
        //            }
        //            else
        //            {
        //                //}
        //                //if (t == typeof(string) ||
        //                //    t == typeof(Object) ||
        //                //    t == typeof(bool))
        //                //{
        //                string currentValue = Util.ToString(f.GetValue(where, null));
        //                if (string.IsNullOrEmpty(currentValue) && filterEmpty) continue;
        //                if (currentValue.Trim('%') != currentValue)
        //                {
        //                    currentValue = Util.ToStringInput(currentValue);

        //                    sqlBuilder.AppendLine(string.Format(" AND {0} LIKE '{1}' ", f.Name, currentValue));
        //                }
        //                else
        //                {
        //                    sqlBuilder.AppendLine(string.Format(" AND {0}=@{1} ", f.Name, f.Name));
        //                }
        //            }

        //        }
        //    }
        //    return sqlBuilder.ToString();
        //}
        //#endregion

        #region 查询sql语句
        /// <summary>
        /// 查询sql语句
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public string QuerySql(object where = null, bool filterEmpty = true, int top = 0)
        {
            var sqlBuilder = new StringBuilder();
            if (top > 0)
            {
                sqlBuilder.Append("SELECT TOP " + top + " * FROM " + TableName + " WHERE 1=1 ");
            }
            else
            {
                sqlBuilder.Append("SELECT * FROM " + TableName + " WHERE 1=1 ");
            }
            if (where != null)
            {
                PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                foreach (var f in whereFields)
                {
                    Type t = f.PropertyType;
                    if (t == typeof(List<string>))
                    {
                        List<string> list = (List<string>)f.GetValue(where, null);
                        if (list == null || !list.Any()) continue;
                        sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
                    }
                    else
                    {
                        string currentValue = Util.ToString(f.GetValue(where, null));
                        if (string.IsNullOrEmpty(currentValue) && filterEmpty) continue;
                        if (currentValue.Trim('%') != currentValue)
                        {
                            currentValue = Util.ToStringInput(currentValue);

                            sqlBuilder.AppendLine(string.Format(" AND {0} LIKE '{1}' ", f.Name, currentValue));
                        }
                        else
                        {
                            sqlBuilder.AppendLine(string.Format(" AND {0}=@{1} ", f.Name, f.Name));
                        }
                    }

                }
            }
            return sqlBuilder.ToString();
        }

        public string QuerySql(List<HH.WMS.Entitys.Common.SearchWhere> where, bool isParamQuery)
        {
            string sql = "SELECT * FROM " + TableName;
            StringBuilder sb = new StringBuilder();
            foreach (HH.WMS.Entitys.Common.SearchWhere m in where)
            {
                if (m.value == null || string.IsNullOrEmpty(m.value.ToString()))
                    continue;
                Type t = m.value.GetType();
                switch (m.sign)
                {
                    case Entitys.Common.WhereSign.Eq:
                        if (sb.Length > 0)
                            sb.Append(" and ");
                        if (t == typeof(string))
                            sb.Append(m.key + "=" + (isParamQuery ? "@" + m.key : "'" + m.value + "'"));
                        else if (t == typeof(int) || t == typeof(decimal))
                            sb.Append(m.key + "=" + (isParamQuery ? "@" + m.key : m.value));
                        break;
                    case Entitys.Common.WhereSign.TEq:
                        if (sb.Length > 0)
                            sb.Append(" and ");
                        sb.Append("CONVERT(date," + m.key + ")= " + (isParamQuery ? "@" + m.key : "CONVERT(date,'" + m.value + "')"));
                        break;
                    case Entitys.Common.WhereSign.TRange:
                        string[] dates = (string[])m.value;
                        if (!string.IsNullOrEmpty(dates[0]))
                        {
                            if (sb.Length > 0)
                                sb.Append(" and ");
                            sb.Append(" CONVERT(date," + m.key + ") >= " + (isParamQuery ? "@START" : "CONVERT(date,'" + dates[0] + "')"));
                        }
                        if (!string.IsNullOrEmpty(dates[1]))
                        {
                            if (sb.Length > 0)
                                sb.Append(" and ");
                            sb.Append(" CONVERT(date," + m.key + ") <= " + (isParamQuery ? "@END" : "CONVERT(date,'" + dates[1] + "')"));
                        }
                        break;
                    case Entitys.Common.WhereSign.Like:
                        if (sb.Length > 0)
                            sb.Append(" and ");
                        sb.Append(m.key + " like " + (isParamQuery ? "@" + m.key : "'%" + m.value + "%'"));
                        break;
                    case Entitys.Common.WhereSign.Gt:
                        if (sb.Length > 0)
                            sb.Append(" and ");
                        sb.Append(m.key + " > " + (isParamQuery ? "@" + m.key : m.value));
                        break;
                    case Entitys.Common.WhereSign.Lt:
                        if (sb.Length > 0)
                            sb.Append(" and ");
                        sb.Append(m.key + " < " + (isParamQuery ? "@" + m.key : m.value));
                        break;
                    case Entitys.Common.WhereSign.Include:
                        List<string> list = (List<string>)m.value;
                        if (list == null || !list.Any())
                            continue;
                        if (sb.Length > 0)
                            sb.Append(" and ");
                        if (isParamQuery)
                        {
                            sb.Append(string.Format(" {0} IN @{1} ", m.key, m.key));
                        }
                        else
                        {
                            sb.Append(string.Format(" {0} IN ('{1}') ", m.key, string.Join("','", list)));
                        }
                        break;
                        //List<string> list = (List<string>)t.GetValue(where, null);
                        //sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
                        //list.ForEach(p =>
                        //{
                        //    sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
                        //});
                }
            }
            if (sb.Length > 0)
                return sql += " where " + sb.ToString();
            return sql;
        }
        #endregion

        #region 获取top条
        /// <summary>
        /// 获取top条
        /// </summary>
        /// <param name="top"></param>
        /// <param name="where"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public List<T> GetTopList(int top, object where = null, string orderBy = "")
        {
            return ExecuteQuery<T>(QuerySql(where, true, top) + orderBy, where);
        }
        #endregion

        #region 获取数量sql
        /// <summary>
        /// 获取数量sql
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public string CountSql(object where = null)
        {
            var sqlBuilder = new StringBuilder("SELECT COUNT(1) FROM " + TableName + " WHERE 1=1 ");
            if (where != null)
            {
                PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                foreach (var f in whereFields)
                {
                    sqlBuilder.AppendLine(string.Format("AND {0}=@{1}", f.Name, f.Name));
                }
            }
            return sqlBuilder.ToString();
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult Add(T entity, IDbTransaction trans = null)
        {
            return ExecuteTranSql(AddSql(), entity, trans);
        }

        public OperateResult Add(T entity, LogPara logPara, IDbTransaction trans = null)
        {
            var sqlStr = AddSql();
            Log.Detail(logPara, "AddSql(): " + sqlStr);
            return ExecuteTranSql(sqlStr, entity, trans);
        }
        #endregion

        #region 批量新增
        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult AddRange(List<T> list, IDbTransaction trans = null)
        {
            return ExecuteTranSql(AddSql(), list, trans);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="where"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateAll(T entity, object where, IDbTransaction trans = null)
        {
            return ExecuteTranSql(UpdateSql(entity, where), entity, trans);
        }
        #endregion

        #region 获取数量
        /// <summary>
        /// 获取数量
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public long Count(object where = null)
        {
            return ExecuteCountSql(CountSql(where), where);
        }
        #endregion

        #region 单表分页
        /// <summary>
        /// 单表分页
        /// </summary>
        /// <param name="where"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public OperateResult GetPagingResult(int pageIndex, int pageSize, object where = null, string orderBy = "")
        {
            string tableName = "(" + QuerySql(where) + ") t";
            return ExecutePagingResult(tableName, pageIndex, pageSize, orderBy, where);
        }
        public OperateResult GetPagingResult(List<SearchWhere> whereColumn, int pageIndex, int pageSize, bool isParamQuery, object where = null, string orderBy = "")
        {
            string tableName = "(" + QuerySql(whereColumn, isParamQuery) + ") t ";
            return ExecutePagingResult(tableName, pageIndex, pageSize, orderBy, where);
        }
        #endregion

        #region 分页
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <param name="orderBy"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable GetPagingData(int pageIndex, int pageSize, out long total, object where = null, string orderBy = "")
        {
            string tableName = "(" + QuerySql(where) + ") t";
            return ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy, where);
        }
        #endregion

        public DataTable GetDataTable(object where = null, string orderBy = "")
        {
            string tableName = "(" + QuerySql(where) + " " + orderBy + ") t";
            return ExecuteSqlDataTable(tableName, where);
        }

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="param"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult Delete(object param, IDbTransaction trans = null)
        {
            return ExecuteTranSql(DeleteSql(param), param, trans);
        }
        #endregion

        #region 动态修改单表
        /// <summary>
        /// 动态修改
        /// </summary>
        /// <param name="updateFields"></param>
        /// <param name="where"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult Update(object updateFields, object where, IDbTransaction trans = null)
        {
            T t = new T();
            string updateSql = UpdateSql(updateFields, where, t);
            return ExecuteTranSql(updateSql, t, trans);
        }
        #endregion

        #region 批量修改
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <param name="list"></param>
        /// <param name="where"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public OperateResult UpdateRange(IList<T> list, object where, IDbTransaction trans = null)
        {
            string updateSql = string.Empty;
            foreach (var entity in list)
            {
                updateSql = UpdateSql(entity, where);
            }
            return ExecuteTranSql(updateSql, list, trans);
        }
        #endregion

        #region 查询-返回List
        /// <summary>
        ///  查询-返回List
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<T> GetList(object where = null, string orderBy = "")
        {
            return ExecuteQuery<T>(QuerySql(where) + orderBy, where);
        }
        #endregion

        #region 查询-返回DataTable
        /// <summary>
        /// 查询-返回DataTable
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetDataTable(object where = null)
        {
            return ExecuteDataTable(QuerySql(where), where);
        }
        #endregion

        #region 查询-返回entity
        /// <summary>
        /// 查询-返回entity
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public T GetSingleEntity(object where = null, string orderBy = "")
        {
            string querySql = QuerySql(where, false) + orderBy;
            return ExecuteSingleEntity<T>(querySql, where);
        }
        #endregion

        /// <summary>
        /// 支持模糊查询
        /// </summary>
        /// <param name="whereColumn"></param>
        /// <param name="where"></param>
        /// <param name="isParamQuery">是否通过参数化查询</param>
        /// <returns></returns>
        /// <History>[Hanhe(dbs)] created by 2018-12-19</History>
        public List<T> GetList(List<SearchWhere> whereColumn, object where, bool isParamQuery)
        {
            if (!isParamQuery)
                return ExecuteQuery<T>(QuerySql(whereColumn, isParamQuery));
            else
                return ExecuteQuery<T>(QuerySql(whereColumn, isParamQuery), where);
        }

        /// <summary>
        /// 支持模糊分页查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <param name="whereColumn"></param>
        /// <param name="isParamQuery">是否通过参数化查询</param>
        /// <param name="where"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        /// <History>[Hanhe(dbs)] created by 2018-12-19</History>
        public DataTable GetPagingData(int pageIndex, int pageSize, out long total, List<SearchWhere> whereColumn, bool isParamQuery, object where = null, string orderBy = "")
        {
            string tableName = "(" + QuerySql(whereColumn, isParamQuery) + ") t";

            Log.Info("DapperDAL", tableName);
            return ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy, where);
        }

        public DataTable GetData(List<SearchWhere> whereColumn, bool isParamQuery, object where = null, string orderBy = "")
        {
            string tableName = QuerySql(whereColumn, isParamQuery) + " " + orderBy;
            return ExecuteDataTable(tableName, where);
        }
    }
}