using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.ExcelModel;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using Estsh.Core.Util;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：料架信息管理数据库访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理料架信息模块的业务数据
    /// </summary>
    public class TrayRepository : BaseRepository<GTray>, ITrayRepository
    {
        public TrayRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        #region 料架信息管理

        /// <summary>
        /// 根据分页条件获取角色列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="OrderBy"></param>
        /// <returns></returns>
        public Hashtable getTrayByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append(" a.*, b.type_name, c.enum_desc ");

                StringBuilder table = new StringBuilder(1024);
                table.Append(" dbo.g_tray a LEFT JOIN dbo.g_tray_type b ON a.tray_type_id = b.type_id ");
                table.Append(" LEFT JOIN dbo.sys_enum c ON a.tray_seq = c.enum_value ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<GTray> depts = dbConn.Query<GTray>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dt", depts);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }

        /// <summary>
        /// 根据分页条件获取角色列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="OrderBy"></param>
        /// <returns></returns>
        public Hashtable getTrayByPageToList(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append(" a.*, b.type_name, c.enum_desc ");

                StringBuilder table = new StringBuilder(1024);
                table.Append(" dbo.g_tray a LEFT JOIN dbo.g_tray_type b ON a.tray_type_id = b.type_id ");
                table.Append(" LEFT JOIN dbo.sys_enum c ON a.tray_seq = c.enum_value ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<GTray> dataList = dbConn.Query<GTray>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }
        public Hashtable getTableListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append(" a.*, b.type_name, c.enum_desc ");

                StringBuilder table = new StringBuilder(1024);
                table.Append(" dbo.g_tray a LEFT JOIN dbo.g_tray_type b ON a.tray_type_id = b.type_id ");
                table.Append(" LEFT JOIN dbo.sys_enum c ON a.tray_seq = c.enum_value ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<TrayDefine> dataList = dbConn.Query<TrayDefine>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }

        /// <summary>
        /// 获取料架类型列表
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getTrayType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "SELECT type_id as [value],type_name as [key] from g_tray_type where enabled = 'Y'";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 插入料架信息信息
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveTrayInfo(GTray htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String tray_code = htParams.TrayCode;
                if (existTray(tray_code))
                {
                    return -1;
                }

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO g_tray (tray_code ");
                SqlStringBuilder.Append("				, tray_name ");
                SqlStringBuilder.Append("				,tray_type_id ");
                SqlStringBuilder.Append("				,tray_seq ");
                SqlStringBuilder.Append("				,enabled ");
                SqlStringBuilder.Append("				, create_time ");
                SqlStringBuilder.Append("				,create_userid) ");
                SqlStringBuilder.Append("			VALUES(@trayCode ");
                SqlStringBuilder.Append("				, @trayName ");
                SqlStringBuilder.Append("				,@trayTypeId");
                SqlStringBuilder.Append("				,@traySeq");
                SqlStringBuilder.Append("				,'Y' ");
                SqlStringBuilder.Append("				, CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("				,@CreateUserid)");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 验证料架代号在料架信息表中是否存在
        /// </summary>
        /// <param name="aTrayCode">料架代号</param>
        /// <returns>true</returns>
        public bool existTray(string tray_code)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT * FROM g_tray WHERE tray_code=@tray_code ");

                DynamicParameters values = new DynamicParameters();
                values.Add("@tray_code", tray_code);
                object obj = dbConn.ExecuteScalar(SqlStringBuilder.ToString(), values);
                return obj != null;
            }
        }

        /// <summary>
        /// 根据料架ID获取料架数据
        /// </summary>
        /// <param name="type_id">料架ID</param>
        /// <returns>true</returns>
        public List<GTray> GetTrayById(string tray_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "SELECT * FROM g_tray WHERE tray_id=@tray_id ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@tray_id", tray_id);
                List<GTray> result = dbConn.Query<GTray>(strSql, values).ToList();
                return result;

            }
        }

        /// <summary>
        /// 更新料架信息信息
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateTrayInfo(GTray htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update g_tray  ");
                SqlStringBuilder.Append("SET tray_code=@trayCode ");
                SqlStringBuilder.Append(" , tray_name=@trayName ");
                SqlStringBuilder.Append(" , tray_type_id=@trayTypeId ");
                SqlStringBuilder.Append(" , tray_seq=@traySeq ");
                SqlStringBuilder.Append(" ,update_userid = @updateUserId ");
                SqlStringBuilder.Append(" ,update_time = CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("WHERE tray_id=@trayId	 ");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 删除料架信息数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteTray(String tray_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_tray set Enabled='N'  where tray_id = @tray_id";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@tray_id", tray_id);
                int result = dbConn.Execute(delStr, htparams);
                return result;

            }
        }

        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_tray set Enabled='Y'  WHERE tray_id in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_tray set Enabled='N'  WHERE tray_id in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }
        /// <summary>
        /// 根据分页条件获取角色列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="OrderBy"></param>
        /// <returns></returns>
        public Hashtable getTrayDetailOfPager(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append(" a.*, b.tray_name , c.enum_desc AS tray_loc1, d.enum_desc AS place_part_location1 ");

                StringBuilder table = new StringBuilder(1024);
                table.Append(" dbo.g_tray_detail a ");
                table.Append(" LEFT JOIN g_tray b ON a.tray_id = b.tray_id ");
                table.Append(" LEFT JOIN dbo.sys_enum c ON a.tray_loc = c.enum_value AND c.enum_type = 'sys_tray_loc' ");
                table.Append(" LEFT JOIN dbo.sys_enum d ON a.place_part_location = d.enum_value AND d.enum_type = 'sys_part_location' ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<GTrayDetail> depts = dbConn.Query<GTrayDetail>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dt", depts);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }

        public Hashtable getTrayDetailToList(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append(" a.*, b.tray_name , c.enum_desc AS tray_loc1, d.enum_desc AS place_part_location1 ");

                StringBuilder table = new StringBuilder(1024);
                table.Append(" dbo.g_tray_detail a ");
                table.Append(" LEFT JOIN g_tray b ON a.tray_id = b.tray_id ");
                table.Append(" LEFT JOIN dbo.sys_enum c ON a.tray_loc = c.enum_value AND c.enum_type = 'sys_tray_loc' ");
                table.Append(" LEFT JOIN dbo.sys_enum d ON a.place_part_location = d.enum_value AND d.enum_type = 'sys_part_location' ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<TrayDefineDetail> dataList = dbConn.Query<TrayDefineDetail>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }

        }
        /// <summary>
        /// 根据guid,获取Tray明细数据
        /// </summary>
        /// <returns>true</returns>
        public List<GTrayDetail> GetTrayDetailByGuid(string guid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "SELECT * FROM g_tray_detail WHERE guid=@guid ";
                DynamicParameters values = new DynamicParameters();
                values.Add("@guid", guid);
                List<GTrayDetail> result = dbConn.Query<GTrayDetail>(strSql.ToString(), values).ToList();
                return result;
            }
        }

        /// <summary>
        /// 插入料架明细信息信息
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveTrayDetailInfo(GTrayDetail htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO g_tray_detail (tray_loc ");
                SqlStringBuilder.Append("				,place_part_location ");
                SqlStringBuilder.Append("				,enabled ");
                SqlStringBuilder.Append("				, create_time ");
                SqlStringBuilder.Append("				,create_userid) ");
                SqlStringBuilder.Append("			VALUES(@trayLoc ");
                SqlStringBuilder.Append("				,@placePartLocation");
                SqlStringBuilder.Append("				,'Y' ");
                SqlStringBuilder.Append("				, CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("				,@CreateUserid)");
                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 更新料架明细信息信息
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateTrayDetailInfo(GTrayDetail htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update g_tray_detail  ");
                SqlStringBuilder.Append("SET tray_loc=@trayLoc ");
                SqlStringBuilder.Append(" , place_part_location=@placePartLocation ");
                SqlStringBuilder.Append(" ,update_userid = @updateUserId ");
                SqlStringBuilder.Append(" ,update_time = CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("WHERE guid=@guid ");
                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 删除料架明细信息数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteTrayDetail(String guid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_tray_detail set Enabled='N'  where guid = @guid";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@guid", guid);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }
        #endregion
    }
}