using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.ExcelModel;
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
{
    public class ProdunctFinishedRepository : BaseRepository<GSnMisc>, IProdunctFinishedRepository
    {
        public ProdunctFinishedRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 判断条码是否存在
        /// </summary>
        /// <param name="carton_no"></param>
        /// <returns></returns>
        public bool IsCartonNoExists(string carton_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string strsql = "select count(1) from g_sn_status where serial_number=@serial_number ";
                DynamicParameters ht = new DynamicParameters();
                ht.Add("@serial_number", carton_no);
                object obj = dbConn.ExecuteScalar(strsql, ht);
                int Result = Convert.ToInt32(obj);
                if (Result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 成品领用主要存储过程
        /// </summary>
        /// <param name="serial_number"></param>
        /// <param name="remark"></param>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public string ProductShipping(string serial_number, string remark, string finished, int UserID, string doorNO, string reason)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters Parameters = new DynamicParameters();
                Parameters.Add( "@serial_number", serial_number);
                Parameters.Add( "@finished", finished);
                Parameters.Add( "@remark", remark);
                Parameters.Add( "@doorNO", doorNO);
                Parameters.Add( "@reason", reason);
                Parameters.Add("@UserID", UserID);
                Parameters.Add("@tres",null, DbType.String, ParameterDirection.Output, 255);

                List<dynamic> depts = dbConn.Query<dynamic>("ProductShipping", Parameters, commandType: CommandType.StoredProcedure).ToList();
                if (!string.IsNullOrEmpty(Parameters.Get<string>("@tres")))
                {
                    return Parameters.Get<string>("@tres");
                }
                else
                {
                    return "Fail";
                }
            }
        }


        public List<GSnMisc> GetAll(string where, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable ht = 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_sn_misc a 
                                JOIN dbo.g_sn_status b ON a.serial_number = b.serial_number AND b.work_flag IN( 1,3))
                                LEFT JOIN sys_part c ON b.part_id = c.part_id
                                LEFT JOIN dbo.sys_emp d ON a.create_userid = d.emp_id 
                                LEFT JOIN dbo.sys_model e ON e.model_id = b.model_id
                                LEFT JOIN dbo.sys_model_type f ON f.type_id = e.model_type_id ");

                parameters.Add( "@Column", @" '0' AS lsn ,
                            f.type_name ,e.model_name ,a.serial_number , c.part_no ,c.cust_part_no ,c.part_spec , a.create_time AS ctime , 
                            d.emp_name ,a.emp_name AS uname , a.reason ,a.out_going_no ,a.remark , 1 AS qty  ");
                parameters.Add( "@orderColumn", @"a.create_time DESC ");
                parameters.Add( "@GroupColumn", @"");
                parameters.Add( "@PageSize", pager.pageSize);
                parameters.Add( "@CurrentPage", pager.pageNo);
                parameters.Add( "@Group", 0);

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" 1=1 AND (LEN(a.reason) > 0 OR LEN(a.remark) > 0)  " + where);
                parameters.Add("@Condition", SqlStringBuilder.ToString());

                List<GSnMisc> depts = dbConn.Query<GSnMisc>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount= parameters.Get<int>("@TotalCount");
                return depts;
            }
        }
        public List<ProdunctFinished> getTableListByPage(string where, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable ht = 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_sn_misc a 
                                JOIN dbo.g_sn_status b ON a.serial_number = b.serial_number AND b.work_flag IN( 1,3))
                                LEFT JOIN sys_part c ON b.part_id = c.part_id
                                LEFT JOIN dbo.sys_emp d ON a.create_userid = d.emp_id 
                                LEFT JOIN dbo.sys_model e ON e.model_id = b.model_id
                                LEFT JOIN dbo.sys_model_type f ON f.type_id = e.model_type_id ");

                parameters.Add("@Column", @" '0' AS lsn ,
                            f.type_name ,e.model_name ,a.serial_number , c.part_no ,c.cust_part_no ,c.part_spec , a.create_time AS ctime , 
                            d.emp_name ,a.emp_name AS uname , a.reason ,a.out_going_no ,a.remark , 1 AS qty  ");
                parameters.Add("@orderColumn", @"a.create_time DESC ");
                parameters.Add("@GroupColumn", @"");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" 1=1 AND (LEN(a.reason) > 0 OR LEN(a.remark) > 0)  " + where);
                parameters.Add("@Condition", SqlStringBuilder.ToString());

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