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

using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using ApServerProvider;
using DbCommon;
using System.Collections;

namespace Estsh.Core.Repositories
{
	/// <summary>
	/// 数据访问类SysWebWorkorderInfo。
	/// </summary>
	public class WorkOrderDefineDal : BaseApp
	{
        public WorkOrderDefineDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { 
        }
		#region  成员方法
		/// <summary>
		/// 获得菜单列表数据
		/// </summary>
		public DataTable getList(string strWhere,string filedOrder)
		{

            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.* , ");
                SqlStringBuilder.Append("        b.part_no , ");
                SqlStringBuilder.Append("        b.part_spec , ");
                SqlStringBuilder.Append("        c.route_name , ");
                SqlStringBuilder.Append("        d.customer_code ,g.ruid, ");
                SqlStringBuilder.Append("        d.customer_name , ");
                SqlStringBuilder.Append("        e.process_name , ");
                SqlStringBuilder.Append("        f.process_name ,h.model_id, ");
                SqlStringBuilder.Append("        g.spec_name , ");
                SqlStringBuilder.Append("        h.model_name , ");
                SqlStringBuilder.Append("        i.factory_code , ");
                SqlStringBuilder.Append("        i.factory_name ");
                SqlStringBuilder.Append("FROM    g_workorder_base a ");
                SqlStringBuilder.Append("        LEFT JOIN sys_part b ON a.part_id = b.part_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_route c ON a.route_id = c.route_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_customer d ON a.customer_id = d.customer_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_process e ON a.start_process_id = e.process_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_process f ON a.end_process_id = f.process_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_pdline g ON a.default_ruid = g.ruid ");
                SqlStringBuilder.Append("        LEFT JOIN sys_model h ON a.model_id = h.model_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_factory i ON a.ruid = i.ruid ");

                if (!strWhere.Trim().Equals(""))
                {
                    SqlStringBuilder.Append(" where a." + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    SqlStringBuilder.Append(" order by " + filedOrder);
                }
                return this._remotingProxy.GetDataTable(SqlStringBuilder.ToString());
            }
		}

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public DataTable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                if (strWhere.Trim() != "")
                {
                    strWhere = " a." + strWhere;  //查询条件
                }
                if (OrderBy.Trim() != "")
                {
                    OrderBy = "  " + OrderBy;  //排序
                }
                Hashtable Params = new Hashtable(6);

                Params.Add("@TotalCount", 100);
                Params.Add("@TotalPage", 100);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", "g_workorder_base a 	left join sys_part b on a.part_id=b.part_id 	left join sys_route c on a.route_id=c.route_id 	left join sys_customer d on a.customer_id=d.customer_id 	left join sys_process e on a.start_process_id=e.process_id 	left join sys_process f on a.end_process_id=f.process_id 	left join sys_pdline g on a.default_ruid=g.ruid 	left join sys_model h on a.model_id=h.model_id 	left join sys_factory i on a.ruid=i.ruid");
                Params.Add("@Column", "a.*,g.ruid,h.model_id,b.part_no,b.part_spec,c.route_name,d.customer_code, 	d.customer_name,e.process_name,f.process_name,g.spec_name, 	h.model_name,i.factory_code,i.factory_name");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

                String strProcedure = "EXEC Com_Pagination @TotalCount, @TotalPage, @Table, @Column,@OrderColumn,@GroupColumn,@PageSize, @CurrentPage,@Group,@Condition";
                return _remotingProxy.GetDataTable(strProcedure, Params);
            }
        }

        /// <summary>
        /// 获取分页记录总数
        /// </summary>
        public int getCountWhere(string strWhere)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(*) as Total from g_workorder_base a 	left join sys_part b on a.part_id=b.part_id 	left join sys_route c on a.route_id=c.route_id 	left join sys_customer d on a.customer_id=d.customer_id 	left join sys_process e on a.start_process_id=e.process_id 	left join sys_process f on a.end_process_id=f.process_id 	left join sys_pdline g on a.default_ruid=g.ruid 	left join sys_model h on a.model_id=h.model_id 	left join sys_factory i on a.ruid=i.ruid");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where a." + strWhere);
                }
                int i = 0;
                DataTable dt = _remotingProxy.GetDataTable(strSql.ToString());
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["Total"].ToString() != "")
                    {
                        i = int.Parse(dt.Rows[0]["Total"].ToString());
                    }
                }
                return i;
            }
        }

        /// <summary>
        /// 获取  工单状态getSelectPart_no
        /// </summary>
        /// <returns></returns>
        public DataTable getSelectWO_status()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT wo_status  as [key], ruid as [value] from g_workorder_base");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取  零件号
        /// </summary>
        /// <returns></returns>
        public DataTable getSelectPart_no()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT DISTINCT part_no  as [key], part_id as [value] FROM dbo.sys_part WHERE enabled = 'Y'");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取  产线信息
        /// </summary>
        /// <returns></returns>
        public DataTable getSelectPackSpec()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT DISTINCT spec_name  as [key], ruid as [value] FROM dbo.sys_pdline");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取  流程信息
        /// </summary>
        /// <returns></returns>
        public DataTable getSelectRoute()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT route_name  as [key], route_id as [value] FROM sys_route WHERE enabled = 'Y'");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取  流程信息
        /// </summary>
        /// <returns></returns>
        public DataTable getSelectModel()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT model_name  as [key], model_id as [value] FROM sys_model WHERE enabled = 'Y'");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveWorkorder(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.g_workorder_base( workorder_no ,part_id ,wo_status ,target_qty,default_ruid,route_id,model_id,remark,enabled )VALUES ");
                SqlStringBuilder.Append("(@workorder_no,@part_id,@wo_status,@target_qty,@ruid,@route_id,@model_id,@remark,@enabled) ");

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

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateWorkorder(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_pdline SET  ");
                SqlStringBuilder.Append("workorder_no=@workorder_no,pdline_desc=@pdline_desc,ruid=@ruid,enabled=@enabled ");
                SqlStringBuilder.Append("WHERE ruid=@ruid ");

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

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteWorkorder(String ruid )
        {
            lock (_remotingProxy)
            {
                String delStr = "DELETE FROM g_workorder_base WHERE ruid = @ruid";
                Hashtable htparams = new Hashtable();
                htparams.Add("@ruid", ruid);
                return _remotingProxy.ExecuteNonQuery(delStr, htparams);
            }
        }

		#endregion  成员方法
























        /// <summary>
        /// 获取下拉框菜单数据   查询出工单条码详细信息
        /// BY NOAH
        /// </summary>
        /// <returns></returns>
        public DataTable getWorkOrder_sn_meaage(String workorder_no)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.workorder,a.serial_number,b.process_name ");
                SqlStringBuilder.Append("from g_sn_status a ");
                SqlStringBuilder.Append("	left join sys_process b on a.process_id=b.process_id ");
                SqlStringBuilder.Append("where a.workorder=@workorder ");

                Hashtable Params = new Hashtable(1);

                Params.Add("@workorder", workorder_no);

                return _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), Params);
            }
        }

        /// <summary>
        /// 判断工单是否已经存在
        /// </summary>
        /// <param name="workorder">工单号码</param>
        /// <returns>是否存在</returns>
        public bool ExistsWorkOrder(string workorder)
        {
            lock (_remotingProxy)
            {
                string SqlString =
                    "select top 1 1 from g_workorder_base where workorder_no=@workorder_no";

                Hashtable Params = new Hashtable(1);
                Params.Add("@workorder_no", workorder);

                return _remotingProxy.GetScalar(SqlString, Params) != null;
            }
        }
	}
}