Estsh-Yiri/Estsh.Core.Services/Repositories/TerminalKPSNDal.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 TerminalKPSNDal : BaseApp
    {
        public TerminalKPSNDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }

        public ArrayList GetTerminalName()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select terminal_id as [value],terminal_name as [key] from dbo.sys_terminal where enabled='Y' ");

                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }
        /// <summary>
        /// 客户产线集合
        /// </summary>
        /// <returns>数据集</returns>
        //public ArrayList GetCustPDLineName()
        //{
        //    lock (_remotingProxy)
        //    {
        //        StringBuilder SqlStringBuilder = new StringBuilder(1024);
        //        SqlStringBuilder.Append("select cust_pdline_name as [value],cust_pdline_name as [key] from sys_cust_pdline where enabled='Y' ");

        //        DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
        //        return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        //    }
        //}

        // <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getTerKPSNByPage(Pager pager, string strWhere)
        //public Hashtable getShippingListByPage(String cust_pdline_name, int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                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", "g_sn_keyparts(nolock) a  left join g_sn_status(nolock) b on a.serial_number=b.serial_number left join sys_part(nolock) c  on b.part_id =c.part_id and c.part_type=1 left join sys_part(nolock) d  on a.item_part_id=d.part_id and d.part_type=0 left join sys_model(nolock) e on b.model_id=e.model_id left join sys_model_type(nolock) f on e.model_type_id=f.type_id  left join sys_terminal(nolock) g on a.terminal_id=g.terminal_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "g.terminal_name,f.type_name,e.model_name,c.part_no,c.part_spec,a.serial_number,a.create_ymd,a.create_hms,d.part_no AS k_part_no,d.part_spec as k_part_spec,a.kpsn "));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy.Replace("desc", "")));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", @"a.create_ymd,a.create_hms"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                //parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                //parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Inssput, "@Condition", strWhere + " and f.cust_pdline_name='" + cust_pdline_name + "'"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
               
                 ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                 result.Add("dataList", dataList);
                 result.Add("totalCount", values["@TotalCount"].ToString());
               // resulttotalRows = Convert.ToInt32(values["@TotalCount"]);
                 return result;
               
            }
        }



        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable getTerKPSN(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {



                Hashtable result = new Hashtable();

                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", "g_sn_keyparts(nolock) a  left join g_sn_status(nolock) b on a.serial_number=b.serial_number left join sys_part(nolock) c  on b.part_id =c.part_id and c.part_type=1 left join sys_part(nolock) d  on a.item_part_id=d.part_id and d.part_type=0 left join sys_model(nolock) e on b.model_id=e.model_id left join sys_model_type(nolock) f on e.model_type_id=f.type_id  left join sys_terminal(nolock) g on a.terminal_id=g.terminal_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "g.terminal_name,f.type_name,e.model_name,c.part_no,c.part_spec,a.serial_number,a.create_ymd,a.create_hms,d.part_no AS k_part_no,d.part_spec as k_part_spec,a.kpsn "));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy.Replace("desc", "")));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", @"a.create_ymd,a.create_hms"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                //parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                //parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Inssput, "@Condition", strWhere + " and f.cust_pdline_name='" + cust_pdline_name + "'"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", aWhere));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);

                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                result.Add("dataList", dataList);
                result.Add("totalCount", values["@TotalCount"].ToString());
                // resulttotalRows = Convert.ToInt32(values["@TotalCount"]);
                return dt;



            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表(导出时使用)
        /// </summary>
        public DataTable getShippingExport(int PageSize, int PageIndex, string strWhere)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                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", "g_sn_print_detail AS a  LEFT JOIN dbo.sys_terminal AS b ON b.terminal_id = a.terminal_name  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "a.serial_number,b.terminal_name,a.create_ymd,a.create_hms "));
                //parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy.Replace("desc", "")));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", @"a.serial_number"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", strWhere));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                return dt;
            }
        }

        //public bool getSnStatus(string sn)
        //{
        //    lock (_remotingProxy)
        //    {
        //        StringBuilder SqlStringBuilder = new StringBuilder(1024);
        //        SqlStringBuilder.Append("SELECT TOP 100 * FROM dbo.i_hx_testdata WHERE 条码 = '" + sn + "' ");

        //        object obj = _remotingProxy.GetScalar(SqlStringBuilder.ToString());
        //        return obj == null ? false : true;
        //    }
        //}

        //public string getVinStatus(string input)
        //{
        //    lock (_remotingProxy)
        //    {
        //        StringBuilder SqlStringBuilder = new StringBuilder(1024);
        //        SqlStringBuilder.Append("SELECT  a.vin ");
        //        SqlStringBuilder.Append("FROM    dbo.g_order a ");
        //        SqlStringBuilder.Append("        LEFT JOIN g_order_items b ON a.order_id = b.order_id ");
        //        SqlStringBuilder.Append("WHERE   b.shipping_sn ='" + input + "' ");

        //        object obj = _remotingProxy.GetScalar(SqlStringBuilder.ToString());
        //        return obj == null ? null : obj.ToString();
        //    }
        //}


    }
}