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 CustPreQtyRepository : BaseRepository<SysCustPdlineProdinfo>, ICustPreQtyRepository
    {
        public CustPreQtyRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        /// <param name="PageSize">一页显示多少条数据</param>
        /// <param name="PageIndex">当前第几页</param>
        /// <param name="strWhere">条件</param>
        /// <param name="OrderBy">排序字段</param>
        /// <returns></returns>
        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.sys_cust_pdline_prodinfo a inner join dbo.sys_cust_pdline b on a.cust_pdline_id=b.cust_pdline_id ");
                parameters.Add("@Column", "b.cust_pdline_id,b.cust_pdline_name,a.cust_pdline_seq,a.cycle_time,a.pdline_seq,a.pre_qty");
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

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

        /// <summary>
        /// 根据客户产线名称查询已下线的最大车号
        /// </summary>
        /// <param name="custPDLineName">客户产线名称</param>
        /// <returns>已下线的最大车号</returns>
        public string GetOutPDLineMaxCarNo(string custPDLineName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select max(car_no) as car_no ");
                SqlStringBuilder.Append("from dbo.g_workorder a, dbo.g_pdline_relation b, dbo.sys_cust_pdline c ");
                SqlStringBuilder.Append("where a.type='N' and a.pdline_id=b.pdline_id and b.cust_pdline_id=c.cust_pdline_id ");
                SqlStringBuilder.Append(" and c.cust_pdline_name=@cust_pdline_name and out_pdline_ymd is not null and enabled='Y'  ");

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@cust_pdline_name", custPDLineName);

                object obj = dbConn.ExecuteScalar(SqlStringBuilder.ToString(), Params);
                if (obj == null || obj == DBNull.Value)
                    return "0";

                return obj.ToString();
            }
        }
        /// <summary>
        /// 根据客户产线查询对应数据
        /// </summary>
        /// <param name="custPDLineID">客户产线编号</param>
        /// <returns>是否存在</returns>
        public List<SysCustPdlineProdinfo> ExistsCustPDLine(int custPDLineID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT b.cust_pdline_name ,a.*FROM sys_cust_pdline_prodinfo AS a INNER JOIN sys_cust_pdline AS b ON a.cust_pdline_id=b.cust_pdline_id where a.cust_pdline_id=@cust_pdline_id and a.enabled='Y' ";

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@cust_pdline_id", custPDLineID);

                List<SysCustPdlineProdinfo> result = dbConn.Query<SysCustPdlineProdinfo>(SqlString, Params).ToList();
                return result;
            }
        }
        public List<KeyValueResult> getCust_pdline()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT cust_pdline_id AS [value],cust_pdline_name AS [key] FROM sys_cust_pdline where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="custPDLineID">产线ID</param>
        /// <param name="cust_pdline_seq">客户流水号</param>
        /// <param name="cycle_time">标准工时</param>
        /// <param name="update_userid">更新用户</param>
        /// <returns></returns>
        public bool edit(int custPDLineID, string cust_pdline_seq, string cycle_time, int update_userid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "UPDATE sys_cust_pdline_prodinfo SET cust_pdline_seq=@cust_pdline_seq,cycle_time=@cycle_time,update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 21) WHERE cust_pdline_id=@cust_pdline_id";
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@cust_pdline_id", custPDLineID);
                Params.Add("@cust_pdline_seq", cust_pdline_seq);
                Params.Add("@cycle_time", cycle_time);
                Params.Add("@update_userid", update_userid);
                int result = dbConn.Execute(sql, Params);
                if (result > 0)
                    return true;
                else
                    return false;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="custPDLineID">产线ID</param> 
        /// <returns></returns>
        public int delete(int custPDLineID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_cust_pdline_prodinfo set Enabled='N'  where cust_pdline_id = @cust_pdline_id";
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@cust_pdline_id", custPDLineID);
                int result = dbConn.Execute(delStr, Params);
                return result;
            }
        }

        //启用
        public int EnableData(String id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_cust_pdline_prodinfo set Enabled='Y'  WHERE cust_pdline_id = @cust_pdline_id";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@cust_pdline_id", id);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        //禁用
        public int DisableData(String id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_cust_pdline_prodinfo set Enabled='N'  WHERE cust_pdline_id = @cust_pdline_id";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@cust_pdline_id", id);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }
        public int Save(SysCustPdlineProdinfo htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_cust_pdline_prodinfo(cust_pdline_id,cust_pdline_seq,pdline_seq,pre_qty,cycle_time,enabled,create_time,guid,create_userid) ");
                SqlStringBuilder.Append("VALUES(@custPdlineId,@custPdlineSeq,@pdlineSeq,@preQty,@cycleTime,'Y',CONVERT(varchar(50), GETDATE(), 21),NEWID(),@createUserid) ");
                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }
    }
}