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
 * 描述：报表模块条码查询DAL
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class SerialNumberQuery2Repository : BaseRepository<GSnKeyparts>, ISerialNumberQuery2Repository
    {
        public SerialNumberQuery2Repository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 查找总成零件号
        /// </summary>
        /// <returns>数据集</returns>
        public List<KeyValueResult> GetPartNo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" select part_no as [value],part_no as [key] from sys_part WHERE part_type= 1 AND part_location IN ('01','02')  Group by part_no ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
        /// <summary>
        /// 获得不绑定条码信息
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public List<GSnKeyparts> GetSNInfo(string whereStr, Pager pager, ref int totalCount)
        {
            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_sn_keyparts(NOLOCK) a
                                  JOIN sys_part b(NOLOCK) ON b.part_id = a.item_part_id
                                  JOIN sys_emp(NOLOCK) c on a.create_userid = c.emp_id
                                  JOIN sys_model(NOLOCK) d on b.model_id = d.model_id ");
                parameters.Add( "@Column", @" a.serial_number, 
                                  a.kpsn, b.part_no, b.part_spec,c.emp_name,a.create_time AS ctime,a.update_time AS utime,d.model_alias ");
                parameters.Add( "@OrderColumn", " ruid DESC ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" a.version IN ( 'SubFG' ) " + whereStr);
                parameters.Add( "@Condition", SqlStringBuilder.ToString());
                List<GSnKeyparts> depts = dbConn.Query<GSnKeyparts>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount= parameters.Get<int>("@TotalCount");
                return depts;
            }
        }
        public List<SerialNumberQuery2SN> GetSNInfoByList(string whereStr, Pager pager, ref int totalCount)
        {
            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_sn_keyparts(NOLOCK) a
                                  JOIN sys_part b(NOLOCK) ON b.part_id = a.item_part_id
                                  JOIN sys_emp(NOLOCK) c on a.create_userid = c.emp_id
                                  JOIN sys_model(NOLOCK) d on b.model_id = d.model_id ");
                parameters.Add("@Column", @" a.serial_number, 
                                  a.kpsn, b.part_no, b.part_spec,c.emp_name,a.create_time AS ctime,a.update_time AS utime,d.model_alias ");
                parameters.Add("@OrderColumn", " ruid DESC ");
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" a.version IN ( 'SubFG' ) " + whereStr);
                parameters.Add("@Condition", SqlStringBuilder.ToString());
                List<SerialNumberQuery2SN> depts = dbConn.Query<SerialNumberQuery2SN>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return depts;
            }
        }
        /// <summary>
        /// 获得条码当前信息
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public List<GSnStatus> GetSNCurrentData(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                DynamicParameters parameter = new DynamicParameters();
                parameter.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameter.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameter.Add( "@Table", @"  dbo.g_sn_status(NOLOCK) a
                                 LEFT JOIN sys_part(NOLOCK) b ON a.part_id = b.part_id
                                 LEFT JOIN dbo.sys_pdline(NOLOCK) c ON a.pdline_id = c.pdline_id
                                 LEFT JOIN sys_model(NOLOCK) d ON d.model_id = a.model_id
                                 LEFT JOIN dbo.sys_model_type(NOLOCK) e ON e.type_id = d.model_type_id
                                 LEFT JOIN dbo.sys_emp(NOLOCK) f ON f.emp_id = a.update_userid ");
                parameter.Add( "@Column", @" c.pdline_name ,
                                 e.type_name ,d.model_name ,a.serial_number,b.part_no ,b.part_spec ,b.cust_part_no ,a.remark ,        
                                 CASE a.current_status WHEN 0 THEN '良品' WHEN 1 THEN '不良品' WHEN -1 THEN '报废品' ELSE '其他' END AS current_status,
                                 a.prod_type ,a.update_time as utime ,f.emp_name,CONVERT(NVARCHAR,a.out_pdline_time,120) AS out_pdline_time ,CONVERT(NVARCHAR,a.in_pdline_time,120) AS in_pdline_time ,CONVERT(NVARCHAR,a.shipping_time,120) AS shipping_time ");
                parameter.Add( "@OrderColumn", " a.serial_number ASC ");
                parameter.Add( "@GroupColumn", "");
                parameter.Add("@PageSize", pager.pageSize);
                parameter.Add("@CurrentPage", pager.pageNo);
                parameter.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
                parameter.Add( "@Condition", SqlStringBuilder.ToString());

                List<GSnStatus> depts = dbConn.Query<GSnStatus>("Com_Pagination", parameter, commandType: CommandType.StoredProcedure).ToList();


                if ((depts == null || depts.Count < 1) && this.IsSerialNumberQuery2History())
                {
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                    parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                    parameters.Add( "@Table", @"  dbo.vw_g_sn_status_union(NOLOCK) a
                                      LEFT JOIN sys_part(NOLOCK) b ON a.part_id = b.part_id
                                      LEFT JOIN dbo.sys_pdline(NOLOCK) c ON a.pdline_id = c.pdline_id
                                      LEFT JOIN sys_model(NOLOCK) d ON d.model_id = a.model_id
                                      LEFT JOIN dbo.sys_model_type(NOLOCK) e ON e.type_id = d.model_type_id
                                      LEFT JOIN dbo.sys_emp(NOLOCK) f ON f.emp_id = a.update_userid ");
                    parameters.Add( "@Column", @" c.pdline_name ,
                                      e.type_name ,d.model_name ,a.serial_number ,a.car_no ,b.part_no ,b.part_spec ,b.cust_part_no ,a.remark ,        
                                      CASE a.current_status WHEN 0 THEN '良品' WHEN 1 THEN '不良品' WHEN -1 THEN '报废品' ELSE '其他' END AS current_status,
                                      a.prod_type,a.update_time as utime ,f.emp_name");
                    parameters.Add( "@OrderColumn", " a.serial_number ASC ");
                    parameters.Add( "@GroupColumn", "");
                    parameters.Add("@PageSize", pager.pageSize);
                    parameters.Add("@CurrentPage", pager.pageNo);
                    parameters.Add("@Group", 0);

                    parameters.Add( "@Condition", SqlStringBuilder.ToString());


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

        /// <summary>
        /// 关键数据查询是否查询历史表数据
        /// </summary>
        /// <returns></returns>
        public bool IsSerialNumberQuery2History()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 param_value FROM dbo.sys_base(NOLOCK) WHERE param_name='IsSerialNumberQuery2History'";
                object obj = dbConn.ExecuteScalar(sql);

                if (obj == null)
                {
                    return false;
                }
                if (obj.ToString().ToUpper() != "Y")
                {
                    return false;
                }
                return true;
            }
        }

        /// <summary>
        /// 获得历史信息
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public List<GSnTravel> GetSNTravelData(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                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_travel(NOLOCK) a
                                 LEFT JOIN sys_part(NOLOCK) b ON a.part_id = b.part_id
                                 LEFT JOIN dbo.sys_pdline(NOLOCK) c ON a.pdline_id = c.pdline_id
                                 LEFT JOIN sys_model(NOLOCK) d ON d.model_id = a.model_id
                                 LEFT JOIN dbo.sys_model_type(NOLOCK) e ON e.type_id = d.model_type_id
                                 LEFT JOIN dbo.sys_emp(NOLOCK) f ON f.emp_id = a.update_userid
                                 LEFT JOIN dbo.sys_terminal(NOLOCK) g ON a.terminal_id = g.terminal_id
                                 LEFT JOIN dbo.g_sn_repair(NOLOCK) h ON a.serial_number = h.serial_number
                                 LEFT JOIN dbo.g_sn_defect(NOLOCK) i ON a.serial_number = i.serial_number
                                 LEFT JOIN dbo.sys_defect(NOLOCK) j ON i.defect_id = j.defect_id ");
                parameters.Add( "@Column", @" c.pdline_name ,
                                 e.type_name ,d.model_name ,a.serial_number ,a.car_no ,b.part_no ,b.part_spec ,b.cust_part_no ,a.remark ,        
                                 CASE a.current_status WHEN 0 THEN '良品' WHEN 1 THEN '不良品' WHEN -1 THEN '报废品' ELSE '其他' END AS current_status,
                                 a.prod_type ,g.terminal_name,CONVERT(VARCHAR(22),out_process_time,120) AS ctime ,j.defect_code ,j.defect_desc ,f.emp_name ");
                parameters.Add( "@OrderColumn", " a.out_process_time ASC ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
                parameters.Add( "@Condition", SqlStringBuilder.ToString());
                List<GSnTravel> result = dbConn.Query<GSnTravel>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        /// <summary>
        /// 关键数据
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public List<GSnKeydata> GetKeyData(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@Table", @" g_sn_keydata(NOLOCK) a 
                                  LEFT JOIN dbo.g_sn_status (NOLOCK) b ON a.serial_number=b.serial_number 
                                  LEFT JOIN dbo.sys_pdline (NOLOCK) c ON b.pdline_id =c.pdline_id
                                  LEFT JOIN dbo.sys_part (NOLOCK) d ON b.part_id =d.part_id ");
                parameters.Add( "@Column", @" a.serial_number,a.keydata_desc,a.keydata_value ,a.create_time, c.pdline_name, d.part_spec,a.keydata_angle  ");
                parameters.Add( "@OrderColumn", " a.create_time ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
                parameters.Add( "@Condition", SqlStringBuilder.ToString());
                List<GSnKeydata> result = dbConn.Query<GSnKeydata>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        public List<SerialNumberQuery2> getTableListByPage(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", @" g_sn_keydata(NOLOCK) a 
                                  LEFT JOIN dbo.g_sn_status (NOLOCK) b ON a.serial_number=b.serial_number 
                                  LEFT JOIN dbo.sys_pdline (NOLOCK) c ON b.pdline_id =c.pdline_id
                                  LEFT JOIN dbo.sys_part (NOLOCK) d ON b.part_id =d.part_id ");
                parameters.Add("@Column", @" a.serial_number,a.keydata_desc,a.keydata_value ,a.create_time, c.pdline_name, d.part_spec,a.keydata_angle  ");
                parameters.Add("@OrderColumn", " a.create_time ");
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
                parameters.Add("@Condition", SqlStringBuilder.ToString());
                List<SerialNumberQuery2> result = dbConn.Query<SerialNumberQuery2>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return result;
            }
        }


        // <summary>
        /// 绑定信息
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public List<GSnKeyparts> GetKeyParts(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@Table", @" g_sn_keyparts(NOLOCK) a 
                                  LEFT JOIN sys_part(NOLOCK) b ON a.item_part_id=b.part_id  
                                  LEFT JOIN dbo.g_sn_status c ON a.serial_number=c.serial_number 
                                  LEFT JOIN dbo.sys_pdline d ON c.pdline_id=d.pdline_id 
                                  LEFT JOIN dbo.g_sn_status e ON a.serial_number =e.serial_number
                                  LEFT JOIN sys_part f ON e.part_id=f.part_id where  1=1 and a." + whereStr + "");
                parameters.Add( "@Column", " d.pdline_name,a.serial_number,f.part_spec  AS ps , a.kpsn  ,b.part_no,b.part_spec, a.create_time ");
                parameters.Add( "@OrderColumn", " a.create_time ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                //StringBuilder SqlStringBuilder = new StringBuilder(1024);
                //SqlStringBuilder.AppendLine(" and 1=1 and a." + whereStr + " ) t ");
                parameters.Add( "@Condition", "");
                List<GSnKeyparts> result = dbConn.Query<GSnKeyparts>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        /// <summary>
        /// 电功能检测
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public List<GSnStatus> GetCheck(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@Table", @"  dbo.i_func_testdata a
                                 LEFT JOIN dbo.g_sn_status b ON REPLACE(REPLACE(a.座椅条形码,CHAR(10),''),CHAR(13),'') = b.serial_number
                                 LEFT JOIN dbo.sys_pdline c ON b.pdline_id=c.pdline_id ");
                parameters.Add( "@Column", @" c.pdline_name AS '生产线',CONVERT(VARCHAR,a.测试完成日期+' '+a.测试完成时间,120) AS '时间',a.* ");
                parameters.Add( "@OrderColumn", " a.测试完成日期,a.测试完成时间 ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
                parameters.Add( "@Condition", SqlStringBuilder.ToString());
                List<GSnStatus> result = dbConn.Query<GSnStatus>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return result;
            }
        }
        /// <summary>
        /// 不良信息
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns></returns>
        public List<GSnDefect> GetRepairDetials(string whereStr, Pager pager, ref int totalCount)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@Table", @"  g_sn_defect(NOLOCK) a
                                 left JOIN g_sn_repair(NOLOCK) b ON a.serial_number=b.serial_number  and a.ruid =b.sn_defect_id 
                                 left JOIN sys_defect(NOLOCK) c ON a.defect_id=c.defect_id
                                 left JOIN sys_reason(NOLOCK) d ON b.reason_id=d.reason_id 
                                 left JOIN sys_duty(NOLOCK) e ON b.duty_id =e.duty_id 
                                 left JOIN sys_emp(NOLOCK) f on b.repair_emp_id=f.emp_id ");
                parameters.Add( "@Column", @" a.serial_number,c.defect_code,c.defect_desc,d.reason_code,d.reason_desc
                                 ,e.duty_code,e.duty_desc,f.emp_name ,a.memo,b.remark,isnull(b.create_time,a.create_time) create_time
                                 ");
                parameters.Add( "@OrderColumn", " a.serial_number  ASC ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
                parameters.Add( "@Condition", SqlStringBuilder.ToString());
                List<GSnDefect> result = dbConn.Query<GSnDefect>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                totalCount = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        public List<GSnKeyparts> GetKPSN(string srcSN)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT    b.kpsn FROM    dbo.g_sn_status(NOLOCK) a        ");
                SqlStringBuilder.Append("  LEFT JOIN dbo.g_sn_keyparts(NOLOCK) b ON a.serial_number = b.serial_number  ");
                SqlStringBuilder.Append("  WHERE   a.serial_number = @srcSN");
                DynamicParameters ht = new DynamicParameters();
                ht.Add("@srcSN", srcSN);
                try
                {
                    List<GSnKeyparts> dt = dbConn.Query<GSnKeyparts>(SqlStringBuilder.ToString(), ht).ToList();
                    if ((dt == null || dt.Count < 1) && this.IsSerialNumberQuery2History())
                    {
                        SqlStringBuilder.Remove(0, SqlStringBuilder.Length);
                        SqlStringBuilder.Append("SELECT    b.kpsn FROM    dbo.vw_g_sn_status_union a        ");
                        SqlStringBuilder.Append("  LEFT JOIN dbo.g_sn_keyparts(NOLOCK) b ON a.serial_number = b.serial_number  ");
                        SqlStringBuilder.Append("  WHERE   a.serial_number = @srcSN");

                        return dbConn.Query<GSnKeyparts>(SqlStringBuilder.ToString(), ht).ToList();
                    }
                    return dt;
                }
                catch (Exception ex)
                {
                    //ex.Message;
                    return null;
                }
            }
        }
        public List<GSnKeydata> valiDationKPSN(string strKPSN)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sqlString = "select * from g_sn_keydata(NOLOCK) where serial_number = '" + strKPSN + "'";
                List<GSnKeydata> result = dbConn.Query<GSnKeydata>(sqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 通过任意有绑定信息的条码取回过程条码 g_sn_status.serial_number
        /// </summary>
        /// <param name="srcSN">已绑定的条码</param>
        /// <returns>若返回值为空，则没有查到信息</returns>
        public string GetPSN(string srcSN)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder querySen = new StringBuilder(1024);
                querySen.Append("SELECT TOP 1 ");
                querySen.Append("      a.serial_number ");
                querySen.Append("FROM  dbo.g_sn_status(NOLOCK) a ");
                querySen.Append("      LEFT JOIN dbo.g_sn_keyparts(NOLOCK) b ON a.serial_number = b.serial_number ");
                querySen.Append("WHERE b.serial_number = @srcSN ");
                querySen.Append("      OR b.kpsn = @srcSN ");
                querySen.Append("      OR a.serial_number = @srcSN ");

                DynamicParameters para = new DynamicParameters();
                para.Add("@srcSN", srcSN);

                try
                {
                    object obj = dbConn.ExecuteScalar(querySen.ToString(), para);

                    if (obj == null && this.IsSerialNumberQuery2History())
                    {
                        querySen.Remove(0, querySen.Length);
                        querySen.Append("SELECT TOP 1 ");
                        querySen.Append("        a.serial_number ");
                        querySen.Append("FROM    dbo.vw_g_sn_status_union a ");
                        querySen.Append("        LEFT JOIN dbo.g_sn_keyparts b ON a.serial_number = b.serial_number ");
                        querySen.Append("WHERE   b.serial_number = @srcSN ");
                        querySen.Append("        OR b.kpsn = @srcSN ");
                        querySen.Append("        OR a.serial_number = @srcSN ");

                        obj = dbConn.ExecuteScalar(querySen.ToString(), para);
                        return obj == null ? "" : obj.ToString();
                    }
                    else
                    {
                        return obj == null ? "" : obj.ToString();
                    }
                }
                catch (Exception ex)
                {
                    return "";
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sn"></param>
        /// <returns></returns>
        public List<SysModel> GetSNModelTypeInfo(string sn)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder querySen = new StringBuilder(1024);
                querySen.Append("SELECT  b.model_name , ");
                querySen.Append("       c.type_name ");
                querySen.Append("FROM   dbo.g_sn_status(NOLOCK) a ");
                querySen.Append("       LEFT JOIN dbo.sys_model(NOLOCK) b ON a.model_id = b.model_id ");
                querySen.Append("       JOIN dbo.sys_model_type(NOLOCK) c ON c.type_id = b.model_type_id ");
                querySen.Append("WHERE  a.serial_number = @SN ");
                DynamicParameters para = new DynamicParameters();
                para.Add("@SN", sn);
                List<SysModel> tmpDT = new List<SysModel>();
                try
                {
                    tmpDT = dbConn.Query<SysModel>(querySen.ToString(), para).ToList(); 
                    if ((tmpDT == null || tmpDT.Count < 1) && this.IsSerialNumberQuery2History())
                    {
                        querySen.Remove(0, querySen.Length);
                        querySen.Append("SELECT  b.model_name , ");
                        querySen.Append("      c.type_name ");
                        querySen.Append("FROM  dbo.vw_g_sn_status_union(NOLOCK) a ");
                        querySen.Append("      LEFT JOIN dbo.sys_model(NOLOCK) b ON a.model_id = b.model_id ");
                        querySen.Append("      JOIN dbo.sys_model_type(NOLOCK) c ON c.type_id = b.model_type_id ");
                        querySen.Append("WHERE a.serial_number = @SN ");
                        tmpDT = dbConn.Query<SysModel>(querySen.ToString(), para).ToList();
                    }
                }
                catch (Exception ex)
                {
                }
                return tmpDT;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sn"></param>
        /// <param name="pnInfo"></param>
        /// <returns></returns>
        public List<SysPart> GetSNPartInfo(string sn)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder querySen = new StringBuilder(1024);
                querySen.AppendLine("SELECT  a.serial_number ,  ");
                querySen.AppendLine("      b.part_no ,  ");
                querySen.AppendLine("      b.cust_part_no ,  ");
                querySen.AppendLine("      b.part_spec ,  ");
                querySen.AppendLine("      c.enum_desc  ");
                querySen.AppendLine("FROM  dbo.g_sn_status(NOLOCK) a  ");
                querySen.AppendLine("      JOIN dbo.sys_part(NOLOCK) b ON a.part_id = b.part_id  ");
                querySen.AppendLine("      LEFT JOIN dbo.sys_enum(NOLOCK) c ON c.enum_value = b.part_location  ");
                querySen.AppendLine("     AND c.enum_type = 'sys_part_location'  ");
                querySen.AppendLine("WHERE a.serial_number = @sn ");

                DynamicParameters para = new DynamicParameters();
                para.Add("@SN", sn);

                List<SysPart> tmpDT = new List<SysPart>();
                try
                {
                    tmpDT = dbConn.Query<SysPart>(querySen.ToString(), para).ToList();
                    if ((tmpDT == null || tmpDT.Count < 1) && this.IsSerialNumberQuery2History())
                    {
                        querySen.Remove(0, querySen.Length);
                        querySen.AppendLine("SELECT  a.serial_number ,  ");
                        querySen.AppendLine("      b.part_no ,  ");
                        querySen.AppendLine("      b.cust_part_no ,  ");
                        querySen.AppendLine("      b.part_spec ,  ");
                        querySen.AppendLine("      c.enum_desc  ");
                        querySen.AppendLine("FROM  dbo.vw_g_sn_status_union(NOLOCK) a  ");
                        querySen.AppendLine("      JOIN dbo.sys_part(NOLOCK) b ON a.part_id = b.part_id  ");
                        querySen.AppendLine("      LEFT JOIN dbo.sys_enum(NOLOCK) c ON c.enum_value = b.part_location  ");
                        querySen.AppendLine("                                  AND c.enum_type = 'sys_part_location'  ");
                        querySen.AppendLine("WHERE a.serial_number = @sn ");
                        tmpDT = dbConn.Query<SysPart>(querySen.ToString(), para).ToList();
                    }
                }
                catch (Exception ex)
                {

                }
                return tmpDT;
            }
        }


        public List<GSnStatus> GetSNBasicInfo(string sn)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder querySen = new StringBuilder(1024);
                querySen.Append("SELECT a.serial_number , ");
                querySen.Append("       a.create_time AS ctime , ");
                querySen.Append("       CONVERT(varchar(100),a.in_pdline_time,120) as in_pdline_time , ");
                querySen.Append("       CONVERT(varchar(100),a.out_pdline_time,120) as out_pdline_time , ");
                querySen.Append("       CONVERT(varchar(100),a.shipping_time,120) as shipping_time , ");
                querySen.Append("       a.prod_type , ");
                querySen.Append("       a.car_no AS shpCarNO , ");
                querySen.Append("       a.work_flag , ");
                querySen.Append("       a.current_status , ");
                querySen.Append("       a.remark , ");
                querySen.Append("       c.type as stype , ");
                querySen.Append("       d.car_no , ");
                querySen.Append("       a.workorder , ");
                querySen.Append("       a.slot_no , ");
                querySen.Append("       e.pdline_name ");
                querySen.Append("FROM   ( dbo.g_sn_status(NOLOCK) a ");
                querySen.Append("         JOIN dbo.g_workorder_sn(NOLOCK) b ON a.serial_number = b.serial_number ");
                querySen.Append("         JOIN dbo.g_workorder_detail(NOLOCK) c ON c.ruid = b.wo_detail_id ");
                querySen.Append("         JOIN dbo.g_workorder(NOLOCK) d ON d.ruid = c.workorder_id ");
                querySen.Append("       ) ");
                querySen.Append("        LEFT JOIN dbo.sys_pdline(NOLOCK) e ON a.pdline_id = e.pdline_id ");
                querySen.Append("WHERE   a.serial_number = @SN ");

                DynamicParameters para = new DynamicParameters();
                para.Add("@SN", sn);

                List<GSnStatus> tmpDT = new List<GSnStatus>();
                try
                {
                    tmpDT = dbConn.Query<GSnStatus>(querySen.ToString(), para).ToList();
                    if ((tmpDT == null || tmpDT.Count < 1) && this.IsSerialNumberQuery2History())
                    {
                        querySen.Remove(0, querySen.Length);
                        querySen.Append("SELECT a.serial_number , ");
                        querySen.Append("       a.create_time AS ctime , ");
                        querySen.Append("       CONVERT(varchar(100),a.in_pdline_time,120) as in_pdline_time , ");
                        querySen.Append("       CONVERT(varchar(100),a.out_pdline_time,120) as out_pdline_time , ");
                        querySen.Append("       CONVERT(varchar(100),a.shipping_time,120) as shipping_time , ");
                        querySen.Append("       a.prod_type , ");
                        querySen.Append("       a.car_no AS shpCarNO , ");
                        querySen.Append("       a.work_flag , ");
                        querySen.Append("       a.current_status , ");
                        querySen.Append("       a.remark , ");
                        querySen.Append("       c.type as stype , ");
                        querySen.Append("       d.car_no , ");
                        querySen.Append("       a.workorder , ");
                        querySen.Append("       a.slot_no , ");
                        querySen.Append("       e.pdline_name ");
                        querySen.Append("FROM   ( dbo.vw_g_sn_status_union a ");
                        querySen.Append("         JOIN dbo.vw_g_workorder_sn_union b ON a.serial_number = b.serial_number ");
                        querySen.Append("         JOIN dbo.vw_g_workorder_detail_union c ON c.ruid = b.wo_detail_id ");
                        querySen.Append("         JOIN dbo.vw_g_workorder_union d ON d.ruid = c.workorder_id ");
                        querySen.Append("       ) ");
                        querySen.Append("       LEFT JOIN dbo.sys_pdline(NOLOCK) e ON a.pdline_id = e.pdline_id ");
                        querySen.Append("WHERE   a.serial_number = @SN ");

                        tmpDT = dbConn.Query<GSnStatus>(querySen.ToString(), para).ToList();
                    }
                }
                catch (Exception ex)
                {
                }
                return tmpDT;
            }
        }
        public List<KeyValueResult> GetPDlineInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string Sqlstring = " select pdline_name as [key],pdline_id as [value] from sys_pdline ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(Sqlstring).ToList();
                return result;
            }
        }

        public List<SysPartSnRule> getPartSnRule()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.part_id , ");
                SqlStringBuilder.Append("       a.from1 , ");
                SqlStringBuilder.Append("       a.to1 , ");
                SqlStringBuilder.Append("       a.fix1 , ");
                SqlStringBuilder.Append("       b.part_no , ");
                SqlStringBuilder.Append("       b.part_spec ");
                SqlStringBuilder.Append("FROM   dbo.sys_part_sn_rule a ");
                SqlStringBuilder.Append("       LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");

                List<SysPartSnRule> result = dbConn.Query<SysPartSnRule>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
    }
}