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


        /// <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);
            }
        }

        public DataTable getList(string strWhere, string filedOrder)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT a.ruid,a.order_no,a.part_no,b.part_spec,a.qty,a.piqty,c.emp_name,a.update_ymd,a.update_hms,a.create_ymd,a.create_hms,a.enabled FROM g_misco AS a LEFT JOIN dbo.sys_part AS b ON a.part_no=b.part_no LEFT JOIN dbo.sys_emp AS c ON a.update_userid=c.emp_id ");
                if (!strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                return this._remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        public int saveModelType(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.sys_model_type(type_code,type_name,type_alias,type_desc,cust_pdline_id,create_userid,create_ymd,create_hms) values");
                SqlStringBuilder.Append("(@type_code,@type_name,@type_alias,@type_desc,@cust_pdline_id,@create_userid,Convert(varchar(10),getdate(),23),Convert(varchar(10),getdate(),108))");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateShippingList(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE dbo.g_misco SET qty=@qty  ");
                SqlStringBuilder.Append("WHERE ruid=@ruid ");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        // <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getShippingListByPage(int PageSize, int PageIndex, string strWhere)
        {
            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", @" g_misco AS a LEFT JOIN dbo.sys_part AS b ON a.part_no=b.part_no LEFT JOIN dbo.sys_emp AS c ON a.update_userid=c.emp_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"a.ruid,a.order_no,a.part_no,b.part_spec,a.qty,a.piqty,c.emp_name,a.update_ymd,a.update_hms,a.create_ymd,a.create_hms,case　when a.qty=a.piqty   THEN '已配完' when  a.qty>a.piqty    then '未配完'  END AS enabled "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.update_ymd,a.update_hms desc "));
                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", "1=1 " + strWhere));

                Hashtable result = new Hashtable();
                Hashtable values = new Hashtable(2);
                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());
                return result;
            }
        }

        // <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getShippingListDetailByPage(int PageSize, int PageIndex, string strWhere)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();
                Hashtable values = new Hashtable(2);

                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();

                if (!string.IsNullOrEmpty(strWhere))
                { 
                   strWhere=strWhere.Replace("update","create");
                
                }
                   
                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_misco_sn a LEFT JOIN dbo.g_misco b ON a.order_no=b.order_no AND a.part_no=b.part_no LEFT JOIN sys_part d ON a.part_no=d.part_no AND d.part_type=1 "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " a.order_no,a.serial_number,a.part_no,d.part_spec,emp_name,a.create_ymd,a.create_hms  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.create_ymd,a.create_hms desc  "));
                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", " 1=1 " + strWhere));

                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());

                return result;
            }
        }

        public DataTable GetCountShipping(string whereStr)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT order_no,werks,a.part_no,b.part_spec, qty,piqty,case  when  piqty=qty   then '已完成'when  piqty<qty   then  '未完成'else '其他'   END  AS flag,a.update_ymd,a.update_hms,a.create_ymd,a.create_hms   ");
                SqlStringBuilder.Append("FROM   dbo.g_misco a LEFT JOIN sys_part b ON a.part_no=b.part_no ");
                SqlStringBuilder.Append("where 1=1 " + whereStr);
                SqlStringBuilder.Append("		ORDER BY  a.update_ymd,a.update_hms desc  ");


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

        public DataTable GetCountShippingDetail(string whereStr)
        {
            lock (_remotingProxy)
            {
                if (!string.IsNullOrEmpty(whereStr))
                {
                    whereStr = whereStr.Replace("update", "create");

                }
                
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT   a.order_no,a.serial_number,a.part_no,d.part_spec,emp_name,a.create_ymd,a.create_hms ");
                SqlStringBuilder.Append("FROM    dbo.g_misco_sn a LEFT JOIN dbo.g_misco b ON a.order_no=b.order_no AND a.part_no=b.part_no LEFT JOIN sys_part d ON a.part_no=d.part_no AND d.part_type=1  ");
                SqlStringBuilder.Append("where 1=1 " + whereStr);
                SqlStringBuilder.Append(" ORDER BY  a.create_ymd,a.create_hms desc ");


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


    }
}