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

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

namespace Estsh.Core.Repositories
{
    public class StockDetailQuery:BaseApp
    {
        /***************************************************************************************************
         * 
         * 作者：张茂忠
         * 创建时间：2013.04.27
         * 描述：库存明细查询模块Dal层
         * 修改日志：
         * 
         * 
         * *************************************************************************************************/
        public StockDetailQuery(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }
        public ArrayList GetAll(string _strwhere,ref DataTable dt)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT  a.ruid , ");
                SqlStringBuilder.Append("        a.order_no ,h.enum_desc, ");
                SqlStringBuilder.Append("        a.carton_no , ");
                SqlStringBuilder.Append("        a.create_hms , ");
                SqlStringBuilder.Append("        a.create_ymd , ");
                SqlStringBuilder.Append("        a.update_ymd , ");
                SqlStringBuilder.Append("        a.update_hms , ");
                SqlStringBuilder.Append("        a.date_code , ");
                SqlStringBuilder.Append("        a.guid , ");
                SqlStringBuilder.Append("        a.qty , ");
                SqlStringBuilder.Append("        b.locate_name ,b.locate_desc, ");
                SqlStringBuilder.Append("        c.part_no , ");
                SqlStringBuilder.Append("        c.part_spec , ");
                SqlStringBuilder.Append("        d.vendor_name , ");
                SqlStringBuilder.Append("        f.emp_name AS update_name , ");
                SqlStringBuilder.Append("        e.emp_name AS create_name , ");
                SqlStringBuilder.Append("        g.factory_name ");
                SqlStringBuilder.Append("FROM    dbo.sys_stock a ");
                SqlStringBuilder.Append("        LEFT JOIN sys_locate b ON a.locate_id = b.locate_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part c ON a.part_id = c.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_vendor d ON a.vendor_id = d.vendor_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_emp e ON a.create_userid = e.emp_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_emp f ON a.update_userid = f.emp_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_factory g ON a.factory_id = g.factory_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_enum h ON a.status = h.enum_value and h.enum_type='sys_stock_status' ");
                SqlStringBuilder.Append("       where 1=1 ");

                if (!string.IsNullOrEmpty(_strwhere))
                {
                    SqlStringBuilder.Append(_strwhere);
                    SqlStringBuilder.Append("ORDER BY a.part_no , ");
                    SqlStringBuilder.Append("        a.date_code ");
                }
                else
                {
                    SqlStringBuilder.Append("ORDER BY a.part_no , ");
                    SqlStringBuilder.Append("        a.date_code ");
                }


                dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }
        public ArrayList GetHistAll(string _strwhere, ref DataTable dt)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.ruid ,          ");
                SqlStringBuilder.Append("a.order_no , ");
                SqlStringBuilder.Append("h.enum_desc,          ");
                SqlStringBuilder.Append("a.carton_no ,         ");
                SqlStringBuilder.Append(" a.create_hms ,          ");
                SqlStringBuilder.Append(" a.create_ymd ,          ");
                SqlStringBuilder.Append(" a.update_ymd ,          ");
                SqlStringBuilder.Append(" a.update_hms ,          ");
                SqlStringBuilder.Append(" a.date_code ,          ");
                SqlStringBuilder.Append(" a.guid ,          ");
                SqlStringBuilder.Append(" a.qty ,          ");
                SqlStringBuilder.Append(" b.locate_name , ");
                SqlStringBuilder.Append(" b.locate_desc ,       ");
                SqlStringBuilder.Append(" c.part_no ,          ");
                SqlStringBuilder.Append(" c.part_spec,          ");
                SqlStringBuilder.Append(" d.vendor_name ,          ");
                SqlStringBuilder.Append(" f.emp_name AS update_name ,          ");
                SqlStringBuilder.Append(" e.emp_name AS create_name ,          ");
                SqlStringBuilder.Append(" g.factory_name  ");
                SqlStringBuilder.Append(" FROM     ");
                SqlStringBuilder.Append(" dbo.sys_ht_stock a         ");
                SqlStringBuilder.Append("  left JOIN sys_locate b ON a.locate_id = b.locate_id          ");
                SqlStringBuilder.Append("  left JOIN dbo.sys_part c ON a.part_id = c.part_id          ");
                SqlStringBuilder.Append("  LEFT JOIN dbo.sys_vendor d ON a.vendor_id = d.vendor_id          ");
                SqlStringBuilder.Append("  LEFT JOIN dbo.sys_emp e ON a.create_userid = e.emp_id          ");
                SqlStringBuilder.Append("  LEFT JOIN sys_emp f ON a.update_userid = f.emp_id          ");
                SqlStringBuilder.Append("  LEFT JOIN dbo.sys_factory g ON a.factory_id = g.factory_id          ");
                SqlStringBuilder.Append("  LEFT JOIN dbo.sys_enum h ON a.status = h.enum_value and h.enum_type='sys_stock_status' ");
                SqlStringBuilder.Append("        where 1=1  ");


                if (!string.IsNullOrEmpty(_strwhere))
                {
                    SqlStringBuilder.Append(_strwhere);
                    SqlStringBuilder.Append("ORDER BY a.part_no , ");
                    SqlStringBuilder.Append("        a.date_code ");
                }
                else
                {
                    SqlStringBuilder.Append("ORDER BY a.part_no , ");
                    SqlStringBuilder.Append("        a.date_code ");
                }
                dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
                return DataTypeConvert.NewObject.DataTableToArrayList(dt);
            }
        }
        /// <summary>
        /// 获取零件号
        /// </summary>
        /// <returns></returns>
        public DataTable GetPartNo()
        {
            lock (_remotingProxy)
            {
                string SqlString = "select part_no as [value],part_no as [key] from sys_part";

                return _remotingProxy.GetDataTable(SqlString);
            }
        }

        /// <summary>
        /// 获取库位名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetLocateName()
        {
            lock (_remotingProxy)
            {
                string SqlString = "select locate_name as [value],locate_name as [key] from sys_locate ";

                return _remotingProxy.GetDataTable(SqlString);
            }
        }

        /// <summary>
        /// 获取枚举值
        /// </summary>
        /// <param name="enumtype">枚举类型</param>
        /// <returns></returns>
        public DataTable GetSysEnum(string enumtype)
        {
            lock (_remotingProxy)
            {
                string Sqlstring = "select enum_desc as [key],enum_value as [value] from sys_enum where enum_type=@enum_type";

                Hashtable values = new Hashtable(1);

                values.Add("@enum_type", enumtype);

                return _remotingProxy.GetDataTable(Sqlstring, values);
            }
        }
    }
}