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

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

namespace Estsh.Core.Repositories
{
    /***************************************************************************************************
* 
* 作者：任华松
* 创建时间：2013.07.08
* 描述：产品下线未发运查询DAL层
* 修改日志：
* 
* 
* *************************************************************************************************/
    public class YYRankQueryDal : BaseApp
    {
        public YYRankQueryDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
            this.TABLE_NAME = "";
            this.PK_NAME = "";
        }

        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable GetYYRankData(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                Hashtable ht = 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", @" dbo.g_workorder_print_detail_SN a
                                LEFT JOIN  SYS_PART b ON a.part_no=b.part_no
                                LEFT JOIN dbo.sys_emp c ON c.emp_id=a.create_userid
                                LEFT JOIN dbo.sys_emp d ON d.emp_id=a.update_userid "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"a.order_no,a.part_no,b.part_spec,a.serial_number,
                                CASE a.status   WHEN '20' THEN '条码扫描'  WHEN '10' THEN '条码跳过'  ELSE '其他' END  AS  status, 
                                CASE a.enabled  WHEN 'Y'  THEN '正常'   WHEN 'N'  THEN '删除'   ELSE '其他' END  AS  enabled, 
                                d.emp_name as update_name ,a.update_ymd,a.update_hms, c.emp_name as create_name,a.create_ymd,a.create_hms  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderColumn", " a.create_ymd desc ,a.create_hms desc "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                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.String, ParameterDirection.Input, "@Group", 0));

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" 1=1 " + aWhere);
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", SqlStringBuilder.ToString()));
                Hashtable values = new Hashtable(2);
                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                ht.Add("dataList", dataList);
                ht.Add("totalCount", values["@TotalCount"].ToString());
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }

        /// <summary>
        /// 配置集合
        /// </summary>
        /// <returns>数据集</returns>
        public ArrayList GetModelName(string typeName)
        {
            lock (_remotingProxy)
            {
                string quernSen = "";
                if (string.IsNullOrEmpty(typeName))
                {
                    quernSen = @" SELECT DISTINCT model_name  as [value],model_name as [key] FROM dbo.sys_model WHERE enabled = 'Y' ";
                }
                else
                {
                    quernSen = @" SELECT DISTINCT model_name as [value],model_name as [key] FROM dbo.sys_model a JOIN dbo.sys_model_type b ON a.model_type_id = b.type_id WHERE a.enabled = 'Y' AND b.type_name = '" + typeName + "' ";
                }
                DataTable dt = _remotingProxy.GetDataTable(quernSen);
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }

        /// <summary>
        ///车型集合
        /// </summary>
        /// <returns>数据集</returns>
        public ArrayList GetTypeName()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select type_name as [value],type_name as [key] from sys_model_type where enabled='Y' ");

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