Estsh-Yiri/Estsh.Core.Services/Repositories/TestDataDal.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text; 
using System.Collections;
using ApServerProvider;
using Estsh.Web.Util;
using DbCommon;

namespace Estsh.Core.Repositories
{
    public class TestDataDal : BaseApp
    {
        public TestDataDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }




        /// <summary>
        /// 电检主表
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetDjc(string whereStr2, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @" dbo.i_djc AS a 
                                                                                                     LEFT JOIN dbo.g_sn_status AS b ON a.条码=b.serial_number "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"a.设备名称,a.条码,a.零件号,a.类型,a.[测试总结果PASS/FAIL] AS  测试总结果,a.[测试时间(秒)] AS 测试时间,a.测试完成日期时间,a.记录保存日期时间 "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.测试完成日期时间 desc "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition","1=1" +whereStr2));


                Hashtable values = new Hashtable(2);
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        /// <summary>
        /// 电检明细表
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetDjcDeatil(string whereStr2, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @"   dbo.i_djc_testdata AS a    LEFT JOIN dbo.g_sn_status AS b ON a.条码=b.serial_number "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.设备名称, a.条码, a.检测项目名称, a.下限值,a.[上限值/模块信息] AS 上限值,a.测试值,a.[测试结果OK/NG] as 测试结果,a.测试完成日期时间,a.记录保存日期时间  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.测试完成日期时间 desc"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition","1=1"+ whereStr2));

                Hashtable values = new Hashtable(2);
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        public DataTable GetSumDjc(string whereStr)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.设备名称 , ");
                SqlStringBuilder.Append("        a.条码 , ");
                SqlStringBuilder.Append("        a.零件号 , ");
                SqlStringBuilder.Append("        a.类型 , ");
                SqlStringBuilder.Append("        a.[测试总结果PASS/FAIL] AS 测试总结果 , ");
                SqlStringBuilder.Append("        a.[测试时间(秒)] AS 测试时间 , ");
                SqlStringBuilder.Append("        a.测试完成日期时间 , ");
                SqlStringBuilder.Append("        a.记录保存日期时间 ");
                SqlStringBuilder.Append("FROM    dbo.i_djc AS a ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.g_sn_status AS b ON a.条码 = b.serial_number ");
                SqlStringBuilder.Append( "where 1=1 " +whereStr);
                SqlStringBuilder.Append("		ORDER BY  a.测试完成日期时间 desc  ");


                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                return dt;
            }
        }

        public DataTable GetSumDjcDeatil(string whereStr)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.设备名称 , ");
                SqlStringBuilder.Append("        a.条码 , ");
                SqlStringBuilder.Append("        a.检测项目名称 , ");
                SqlStringBuilder.Append("        a.下限值 , ");
                SqlStringBuilder.Append("        a.[上限值/模块信息] AS 上限值 , ");
                SqlStringBuilder.Append("        a.测试值 , ");
                SqlStringBuilder.Append("        a.[测试结果OK/NG] AS 测试结果 , ");
                SqlStringBuilder.Append("        a.测试完成日期时间 , ");
                SqlStringBuilder.Append("        a.记录保存日期时间 ");
                SqlStringBuilder.Append("FROM    dbo.i_djc_testdata AS a     LEFT JOIN dbo.g_sn_status AS b ON a.条码=b.serial_number   ");
                SqlStringBuilder.Append("where 1=1 " + whereStr);
                SqlStringBuilder.Append("		ORDER BY  a.测试完成日期时间 desc ");


                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                return dt;
            }
        }






        /// <summary>
        /// 二排推拉力
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="pager"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable GetImpetus(string whereStr1, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @"  dbo.i_b_hdl_testdata AS a 
                LEFT JOIN dbo.g_sn_status AS b ON a.型号条码=b.serial_number  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.*  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", "a.ruid"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", whereStr1));


                Hashtable values = new Hashtable(2);
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
 
            }
        }

        /// <summary>
        /// 获取二排电检测信息
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetSNCurrentData(string whereStr, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @"dbo.i_b_djc_testdata AS a 
LEFT JOIN dbo.g_sn_status AS b ON a.型号条码=b.serial_number "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.* "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.ruid "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", whereStr));

                Hashtable values = new Hashtable(2);
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        /// <summary>
        /// 前排SBR及视觉影像
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetSBR(string whereStr2, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @" dbo.i_func_testdata AS a 
LEFT JOIN dbo.g_sn_status AS b ON a.条码=b.serial_number "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.* "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", "a.条码"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", whereStr2));

               
                Hashtable values = new Hashtable(2);

               
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        /// <summary>
        /// 前排推拉力
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetFrontImpetus(string whereStr3, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @" dbo.i_hdl_testdata AS a 
                LEFT JOIN dbo.g_sn_status AS b ON a.条码=b.serial_number "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.* "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.条码 "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", whereStr3));
                Hashtable values = new Hashtable(2);
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        /// <summary>
        /// 前排静音房
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetFrontRoom(string whereStr4, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @" dbo.i_noise_testdata AS a 
LEFT JOIN dbo.g_sn_status AS b ON a.条码=b.serial_number  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.* "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", "a.条码"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", whereStr4));
                Hashtable values = new Hashtable(2);
                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        /// <summary>
        /// QE电检测
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetRSBR(string whereStr5, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", @" dbo.i_qe_djc_testdata AS a 
LEFT JOIN dbo.g_sn_status AS b ON a.座椅条码=b.serial_number "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @" a.* "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", "a.座椅条码"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", whereStr5));


                Hashtable values = new Hashtable(2);


                DataTable dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

    }
}