Estsh-Yiri/Estsh.Core.Services/Repositories/SwapSNInfoDal.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
{
    public class SwapSNInfoDal : BaseApp
    {
        private RemotingProxy remotingProxy;


        public SwapSNInfoDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
            this.TABLE_NAME = "sys_defect";
            this.PK_NAME = "defect_id";
        }

        /// <summary>
        /// 判断库存合格证条码是否存在
        /// </summary>
        /// <param name="serialNumber">库存合格证条码</param>
        /// <returns></returns>
        public bool ExistSn(string serialNumber)
        {
            lock (_remotingProxy)
            {
                bool result = false;
                string sql = "SELECT * FROM dbo.g_sn_status WHERE (serial_number=@serial_number)";
                Hashtable values = new Hashtable(1);
                values.Add("@serial_number", serialNumber);
                DataTable dt = _remotingProxy.GetDataTable(sql, values);
                if (dt.Rows.Count > 0)
                {
                    result = true;
                }
                return result;
            }
        }

        /// <summary>
        /// 依次更新g_sn_status、g_sn_keyparts、g_sn_keydata、NewTest、g_sn_travel表
        /// </summary>
        /// <param name="oldSn">原合格证条码</param>
        /// <param name="newSn">库存合格证条码</param>
        /// <returns></returns>
        public string UpdateTable(string oldSN, string newSN, string flag , int  empId)
        {
            lock (_remotingProxy)
            {
                flag = flag.ToUpper();

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

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@oldSN", oldSN));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@newSN", newSN));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@rMsg", 50));
                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@empId", empId));


                //Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@flag", flag));
                // use default value.

                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.g_swip_sn_audit", Parameters);

                return ht["@rMsg"].ToString();
            }
        }



        /// <summary>
        /// 从 sys_base 表中读取Audit交换数据时，写入的到g_sn_repair_replace_kp.remark中值，
        /// 以此区分是返工返修交换的数据还是Audit交换的数据
        /// </summary>
        /// <returns>异常以及没有取到值时返回默认值"AUDIT", 若能读取到返回读到的值。</returns>
        public string GetAuditSwapFlag()
        {
            lock (_remotingProxy)
            {
                string flag = "AUDIT"; //this is the default value.
                try
                {
                    string querySen = "SELECT param_value FROM sys_base WHERE enabled = 'Y' AND param_name = 'AuditSwapFlag' AND enabled = 'Y' ";

                    object obj = _remotingProxy.GetScalar(querySen);
                    if (null == obj)
                        return flag;
                    else
                        return obj.ToString();
                }
                catch
                {
                    return flag;
                }
            }
        }

        /// <summary>
        /// 查询库存合格证条码的状态
        /// </summary>
        /// <param name="serialNumber">原合格证条码</param>
        /// <returns></returns>
        public int GetStatus(string serialNumber)
        {
            lock (_remotingProxy)
            {
                string sql = "SELECT current_status FROM dbo.g_sn_status  WHERE serial_number=@serial_number";
                Hashtable values = new Hashtable(1);
                values.Add("@serial_number", serialNumber);
                return Convert.ToInt32(_remotingProxy.GetScalar(sql, values));
            }
        }

        /// <summary>
        /// 获取合格证条码的零件ID
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public int GetSnPartID(string serialNumber)
        {
            lock (_remotingProxy)
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select b.part_id from dbo.g_workorder_sn a, dbo.g_workorder_detail b ");
                sql.Append("where a.wo_detail_id=b.ruid and a.serial_number=@serial_number ");
                Hashtable values = new Hashtable(1);
                values.Add("@serial_number", serialNumber);

                object obj = _remotingProxy.GetScalar(sql.ToString(), values);

                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 查询所有在Audit时交换的信息。
        /// </summary>
        /// <param name="sn">要查询的条码（可为新的，可为旧的）</param>
        /// <param name="remarkFlag">AUDIT 交换数据的标记字符串</param>
        /// <param name="fuzzyQuery">是否启用模糊查询方式（以输入的字符串开头的都列出来）</param>
        /// <returns>返回输入条码的交换记录</returns>
        public DataTable GetSwapDataList(Hashtable para, bool fuzzyQuery)
        {
            lock (_remotingProxy)
            {
                StringBuilder querySen = new StringBuilder(1024);
                querySen.Append("SELECT  old_kpsn , ");
                querySen.Append("        new_kpsn , ");
                querySen.Append("        remark , ");
                querySen.Append("        enabled , ");
                querySen.Append("        create_ymd , ");
                querySen.Append("        create_hms, ");
                querySen.Append("        ht.serial_number, ");
                querySen.Append("        ht.new_part_id, ");
                querySen.Append("        ht.old_part_id ");
                querySen.Append("FROM    dbo.g_sn_repair_replace_kp ht ");
                querySen.Append("WHERE   (new_kpsn LIKE @newSN ");
                querySen.Append("        OR old_kpsn LIKE @oldSN) ");
                querySen.Append("        AND remark = @remarkFlag ");
                querySen.Append("ORDER BY ruid DESC ");
                string sn = para["newSN"].ToString();
                if (para["newSN"].ToString().Length <= 0)   //没有输入条码时，查询全部。
                {
                    sn = "%";
                }
                else if (fuzzyQuery) //有启用模糊查询 
                {
                    sn += "%";
                }


                try
                {
                    return _remotingProxy.GetDataTable(querySen.ToString(), para);
                }
                catch (System.Exception ex)
                {
                    lastErrMsg = ex.Message;
                    return null;
                }
            }
        }

        public string lastErrMsg { get; set; }
    }
}