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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：发运通道维护
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class RollDefineRepository : BaseRepository<GRoll>, IRollDefineRepository
    {
        public RollDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 根据查询条件得出结果集
        /// </summary>
        /// <param name="awhere"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        //public List<GRoll> GetQuery(string awhere, ref DataTable dt)
        public List<GRoll> GetQuery(string awhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sqlStringBuilder = new StringBuilder();
                sqlStringBuilder.Append(" SELECT roll_no,roll_name,roll_type,tray_type,order_seq,enabled FROM dbo.g_roll ");

                if (!string.IsNullOrEmpty(awhere))
                {
                    sqlStringBuilder.Append(awhere);
                }
                List<GRoll> result = dbConn.Query<GRoll>(sqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得菜单列表数据
        /// </summary>
        public List<GRoll> getList(string strWhere, string filedOrder)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select   a.*,b.model_name,b.model_desc,(CASE  a.roll_type WHEN '0' THEN '库存道' WHEN '1' THEN '排序道' ELSE '未知' END) AS as_roll_type,(CASE tray_type WHEN 'F' THEN '前排托盘' WHEN 'R' THEN '后排托盘' WHEN 'A' THEN '前后排托盘' ELSE '未知' END) AS as_tray_type,type_id  from g_roll AS a  ");
                strSql.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id ");
                strSql.Append(" LEFT JOIN dbo.sys_model_type AS c ON b.model_type_id=c.type_id ");
                if (!strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }

                List<GRoll> result = dbConn.Query<GRoll>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取辊道编号
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetRollNO()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sqlStringBuilder = new StringBuilder(1024);

                sqlStringBuilder.Append(" select roll_no as [value],roll_name as [key] from g_roll ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取辊道名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetRollName()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sqlStringBuilder = new StringBuilder(1024);
                sqlStringBuilder.Append(" select roll_no as [value],roll_name as [key] from g_roll");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sqlStringBuilder.ToString()).ToList();
                return result;
            }
        }


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

        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_roll set Enabled='Y'  WHERE ruid 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_roll set Enabled='N'  WHERE ruid in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int insertRoll(GRoll htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sqlStringBuilder = new StringBuilder(1024);
                sqlStringBuilder.Append("INSERT  INTO dbo.g_roll");
                sqlStringBuilder.Append("(roll_no ,");
                sqlStringBuilder.Append("roll_name ,");
                sqlStringBuilder.Append("roll_type ,");
                sqlStringBuilder.Append("max_qty ,");
                sqlStringBuilder.Append("min_qty ,");
                sqlStringBuilder.Append("qty ,");
                sqlStringBuilder.Append("cust_pdline_id,");
                sqlStringBuilder.Append(", create_userid ");
                sqlStringBuilder.Append(", create_time ");
                sqlStringBuilder.Append("model_id");
                sqlStringBuilder.Append("enabled");
                sqlStringBuilder.Append(")");
                sqlStringBuilder.Append("VALUES  ( @rollNo ,");
                sqlStringBuilder.Append("@rollName ,");
                sqlStringBuilder.Append("@rollType ,");
                sqlStringBuilder.Append("@maxQty ,");
                sqlStringBuilder.Append("@minQty ,");
                sqlStringBuilder.Append("@qty ,");
                sqlStringBuilder.Append("@custPdlineId,");
                sqlStringBuilder.Append(", @createUserid ");
                sqlStringBuilder.Append(", CONVERT(varchar(50), GETDATE(), 21)");
                sqlStringBuilder.Append("@modelId");
                sqlStringBuilder.Append("'Y'");
                sqlStringBuilder.Append(" ) ");

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

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateRoll(GRoll htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE  g_roll ");
                SqlStringBuilder.Append("SET roll_no = @rollNo , ");
                SqlStringBuilder.Append("    roll_name = @rollName , ");
                SqlStringBuilder.Append("    roll_type = @rollType , ");
                SqlStringBuilder.Append("    max_qty = @maxQty , ");
                SqlStringBuilder.Append("    min_qty = @minQty , ");
                SqlStringBuilder.Append("    qty = @qty , ");
                SqlStringBuilder.Append("    model_id = @modelId , ");
                SqlStringBuilder.Append("    cust_pdline_id=@custPdlineId, ");
                SqlStringBuilder.Append("	update_userid = @updateUserId ,");
                SqlStringBuilder.Append("	update_time = CONVERT(varchar(50), GETDATE(), 21) ");
                SqlStringBuilder.Append("WHERE   ruid = @ruid  ");
                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                DynamicParameters parameters = new DynamicParameters();

                parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@Table", " dbo.g_roll AS a LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id ");
                parameters.Add( "@Column", " a.*,b.model_name,b.model_desc,(CASE  a.roll_type WHEN '0' THEN '库存道' WHEN '1' THEN '排序道' ELSE '未知' END) AS as_roll_type,(CASE tray_type WHEN 'F' THEN '前排托盘' WHEN 'R' THEN '后排托盘' WHEN 'A' THEN '前后排托盘' ELSE '未知' END) AS as_tray_type ");
                parameters.Add( "@OrderColumn", OrderBy);
                parameters.Add( "@GroupColumn", "");
                parameters.Add( "@PageSize", PageSize);
                parameters.Add( "@CurrentPage", PageIndex);
                parameters.Add( "@Group", 0);
                parameters.Add( "@Condition", strWhere);

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

        /// <summary>
        /// 获取客户产线列表
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetCustPDLine()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  cust_pdline_id as [value], ");
                SqlStringBuilder.Append("cust_pdline_name + ' : ' + cust_pdline_desc as [key] ");
                SqlStringBuilder.Append("FROM    dbo.sys_cust_pdline ");
                SqlStringBuilder.Append("WHERE   enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY cust_pdline_name ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据客户产线查询车型
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetMode_type(string CustPDLine)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select type_id as [value], type_name as [key] from dbo.sys_model_type WHERE enabled='Y' and cust_pdline_id= (select cust_pdline_id from sys_cust_pdline where cust_pdline_id='" + CustPDLine + "')";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据车型编号查询座椅配置
        /// </summary>
        /// <param name="modelTypeID">车型编号</param>
        /// <returns>配置</returns>
        public List<KeyValueResult> GetModelList(int ModelType)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT model_id as [value],model_name as [key] FROM dbo.sys_model WHERE enabled='Y' and model_type_id=@type_id order by model_name";
                DynamicParameters Params = new DynamicParameters(1);
                Params.Add("@type_id", ModelType);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString, Params).ToList();
                return result;
            }
        }


        /// <summary>
        /// 查询重复信息
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<GRoll> getRollNoList(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT roll_no,roll_name,roll_type,alarm_addr,model_id,tray_type,order_seq,layer,enabled FROM dbo.g_roll where 0=0 " + strWhere);
                List<GRoll> result = dbConn.Query<GRoll>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取客户产线编号
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetCustID()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT  DISTINCT a.cust_pdline_id as [value],b.cust_pdline_name as [key] FROM dbo.g_roll a LEFT JOIN dbo.sys_cust_pdline b ON a.cust_pdline_id=b.cust_pdline_id WHERE a.enabled='Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }
    }

}