Estsh-Yiri/Estsh.Core.Services/Repositories/YYEdiShipDal.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 YYEdiShipDal : BaseApp
    {
        public YYEdiShipDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
            this.TABLE_NAME = "";
            this.PK_NAME = "";
        }

        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable GetYYEDIShipData(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                //lvf 20210510 加入vin码显示
                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_edi_ship_detail(NOLOCK) a 
                                     LEFT JOIN  dbo.magne_edi_detail(NOLOCK) m ON  a.csn=m.csn and m.materialId=SUBSTRING(a.serial_number,2,10)
                                     LEFT JOIN  sys_part(NOLOCK) c ON m.materialId =c.part_no_3c  and c.part_type=1 and  c.enabled='Y'
                                     LEFT JOIN  dbo.sys_model(NOLOCK) d ON m.model_id=d.model_id 
                                     LEFT JOIN dbo.sys_model_type(NOLOCK) e ON d.model_type_id=e.type_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"a.order_no,a.csn, m.vin,e.type_name,d.model_name,c.part_no,c.part_no_3c,c.part_spec,a.serial_number,
                                    CASE a.status WHEN '1' THEN '正常发运' WHEN '3' THEN '解锁发运' ELSE '其他' END AS status,
                                    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);
            }
        }
    }
}