using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using HH.WCS.Mobox3.Template.Entity;
using SqlSugar;

namespace HH.WCS.Mobox3.Template.Util.Helper
{
    /// <summary>
    /// 数据库连接帮助类
    /// </summary>
    public static class AdoSqlHelper<T> where T : class, new()
    {
        /// <summary>
        /// 根据条件查询一条数据
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public static T QueryFirst(Expression<Func<T, bool>> condition)
        {
            var db = new SqlHelper<object>().GetInstance();

            // 返回数据
            return db.Queryable<T>().First(condition);
        }

        /// <summary>
        /// 根据条件查询多条数据
        /// </summary>
        /// <param name="condition"></param>
        public static List<T> QueryList(Expression<Func<T, bool>> condition)
        {
            var db = new SqlHelper<object>().GetInstance();

            // 返回数据
            return db.Queryable<T>().Where(condition).ToList();
        }

        /// <summary>
        /// 根据条件查询对应数据数量
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public static int QueryCount(Expression<Func<T, bool>> condition)
        {
            var db = new SqlHelper<object>().GetInstance();

            // 返回数据
            return db.Queryable<T>().Count(condition);
        }

        /// <summary>
        /// 根据条件修改一条数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="model">需要进行修改的对象</param>
        /// <param name="condition">修改条件</param>
        /// <returns></returns>
        public static bool UpdateFirstTran(SqlSugarClient sqlSugarClient, T model, Expression<Func<T, object>> condition)
        {
            // 修改数据
            var executeCommand = sqlSugarClient.Updateable(model).UpdateColumns(condition).ExecuteCommand();

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据条件修改一条数据
        /// </summary>
        /// <param name="model">需要进行修改的对象</param>
        /// <param name="condition">修改条件</param>
        /// <returns></returns>
        public static bool UpdateFirst(T model, Expression<Func<T, object>> condition)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();

            // 修改数据
            var executeCommand = sqlSugarClient.Updateable(model).UpdateColumns(condition).ExecuteCommand();

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    
        /// <summary>
        /// 根据条件修改一条数据的状态(sql语句)
        /// </summary>
        /// <param name="model">需要进行修改的对象</param>
        /// <param name="sql">修改条件</param>
        /// <returns></returns>
        public static bool UpdateFirstOutBoundStart(T model, string sql)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();

            // 修改数据
            var executeCommand = sqlSugarClient.Ado.ExecuteCommand(sql, model);

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据条件修改多条数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="models"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static int UpdateListTran(SqlSugarClient sqlSugarClient, List<T> models,
            Expression<Func<T, object>> condition)
        {
            // 修改数据
            return sqlSugarClient.Updateable(models).UpdateColumns(condition).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件修改多条数据
        /// </summary>
        /// <param name="models"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static int UpdateList(List<T> models, Expression<Func<T, object>> condition)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();
            // 修改数据
            return sqlSugarClient.Updateable(models).UpdateColumns(condition).ExecuteCommand();
        }

        /// <summary>
        /// 新增一条数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="model">需要新增的数据</param>
        /// <returns></returns>
        public static bool AddFirstTran(SqlSugarClient sqlSugarClient, T model)
        {
            var executeCommand = sqlSugarClient.Insertable(model).ExecuteCommand();

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 新增一条数据（事务）
        /// </summary>
        /// <param name="model">需要新增的数据</param>
        /// <returns></returns>
        public static bool AddFirst(T model)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();

            var executeCommand = sqlSugarClient.Insertable(model).ExecuteCommand();

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 新增多条数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="models">需要新增的数据集合</param>
        /// <returns></returns>
        public static int AddListTran(SqlSugarClient sqlSugarClient, List<T> models)
        {
            return sqlSugarClient.Insertable(models).ExecuteCommand();
        }

        /// <summary>
        /// 新增多条数据
        /// </summary>
        /// <param name="models">需要新增的数据集合</param>
        /// <returns></returns>
        public static int AddList(List<T> models)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();
            return sqlSugarClient.Insertable(models).ExecuteCommand();
        }

        /// <summary>
        /// 删除一条数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="model">需要删除的对象</param>
        /// <returns></returns>
        public static bool DeleteFirstTran(SqlSugarClient sqlSugarClient, T model)
        {
            var executeCommand = sqlSugarClient.Deleteable(model).ExecuteCommand();

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据（事务）
        /// </summary>
        /// <param name="model">需要删除的对象</param>
        /// <returns></returns>
        public static bool DeleteFirst(T model)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();

            var executeCommand = sqlSugarClient.Deleteable(model).ExecuteCommand();

            if (executeCommand > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除多条数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="models">需要删除的对象集合</param>
        /// <returns></returns>
        public static int DeleteListTran(SqlSugarClient sqlSugarClient, List<T> models)
        {
            return sqlSugarClient.Deleteable(models).ExecuteCommand();
        }

        /// <summary>
        /// 删除多条数据（事务）
        /// </summary>
        /// <param name="models">需要删除的对象集合</param>
        /// <returns></returns>
        public static int DeleteList(List<T> models)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();
            return sqlSugarClient.Deleteable(models).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件删除数据（事务）
        /// </summary>
        /// <param name="sqlSugarClient">数据库上下文连接</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public static int DeleteListTran(SqlSugarClient sqlSugarClient, Expression<Func<T, bool>> condition)
        {
            return sqlSugarClient.Deleteable<T>().Where(condition).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件删除数据（事务）
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public static int DeleteList(Expression<Func<T, bool>> condition)
        {
            var sqlSugarClient = new SqlHelper<object>().GetInstance();
            return sqlSugarClient.Deleteable<T>().Where(condition).ExecuteCommand();
        }

        /// <summary>
        /// 获取数据库上下文连接
        /// </summary>
        /// <returns></returns>
        public static SqlSugarClient QuerySqlSugarClient()
        {
            return new SqlHelper<object>().GetInstance();
        }

        /// <summary>
        /// 按照时间倒序查询
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public static T QueryFirstByDecs(Expression<Func<T, bool>> condition, Expression<Func<T, object>> orderBy)
        {
            var db = new SqlHelper<object>().GetInstance();
            
            // 返回数据
            return db.Queryable<T>().OrderByDescending(orderBy).First(condition);
        }


        #region 特定查询

        /// <summary>
        /// 分配入库货位
        /// </summary>
        /// <returns></returns>
        public static Location QueryInputLocation(string itemCode)
        {
            var areaCodeList = new List<string>
            {
                "1","2","3","4"
            };   
            
            var db = new SqlHelper<object>().GetInstance();

            var listMaxCol = db.Queryable<Location>()
                .LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
                .LeftJoin<CntrItemDetail>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
                .Where((l, lc, ci) => l.N_CURRENT_NUM > 0
                                      && areaCodeList.Contains(l.S_GROUP)
                                      && ci.S_ITEM_CODE == itemCode)
                .OrderByDescending((l, lc, ci) => l.N_COL)
                .Take(1)
                .PartitionBy((l, lc, ci) => new { l.S_GROUP, l.N_ROW })
                .ToList();

            
            // 有同种物料所占据的排
            if (listMaxCol.Count > 0)
            {
                foreach (var item in listMaxCol)
                {
                    //排有锁也排除（锁表示后面可能要改）
                    if (db.Queryable<Location>().Count(a =>
                            a.S_GROUP == item.S_GROUP && a.N_ROW == item.N_ROW && a.N_LOCK_STATE != 0) > 0)
                    {
                        continue;
                    }
                    
                    // 没锁
                    var end = db.Queryable<Location>().OrderBy(a => a.N_COL).Where(a =>
                        a.S_GROUP == item.S_GROUP && a.N_ROW == item.N_ROW &&
                        a.N_CURRENT_NUM == 0 &&
                        a.N_COL >= item.N_COL).First();
                    
                    if (end != null)
                    {
                        return end;
                    }
                }
            }
            
            Location location = db.Queryable<Location>().OrderBy(a => a.S_GROUP).First(p => p.N_COL == 1 && p.N_LOCK_STATE == 0);

            if (location != null)
            {
                return location;
            }

            return null;
        }

        #endregion
    }
}