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

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

/***************************************************************************************************
 * 
 * 作者：贾文涛
 * 创建时间：2013.03.19
 * 描述：菜单模块数据库访问类
 * 修改日志：
 * 1、2013.03.27  贾文涛   变更后台的菜单表，同步变更获取菜单数据的一些方法
 * 
 * *************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理菜单模块的业务数据
    /// </summary>
    public class DetectionControlDal : BaseApp
    {
        public DetectionControlDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
        }
        #region  成员方法

        /// <summary>
        /// 根据传入条件获得菜单列表数据
        /// </summary>
        public DataTable getList(string strWhere)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT type_code,model_name,model_spec,part_no,part_spec,PF_DJC,QP_DJC,
                       QP_IMS,QP_JYF,QP_TLL,QP_SBR,QP_SJYX,QM_TLL,QM_DJC FROM  dbo.g_outPdline_detection  ");
                if (strWhere != null && !strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                //if (filedOrder != null && !filedOrder.Trim().Equals(""))
                //{
                //    strSql.Append(" order by " + filedOrder);
                //}
                return this._remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getDetectionControlByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                Hashtable result = 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", " g_outPdline_detection  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "ruid,type_code,model_name,model_spec,part_no,part_spec,PF_DJC,QP_DJC,QP_IMS,QP_JYF,QP_SBR,QP_SJYX,QP_TLL,QM_DJC,QM_TLL "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy));
                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", strWhere));

                Hashtable values = new Hashtable(2);

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

        /// <summary>
        /// 根据分页条件获取分页数据列表(导出时使用)
        /// </summary>
        public DataTable getListByPage2(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                Hashtable result = 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", "g_outPdline_detection  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "type_code,model_name,model_spec,part_no,part_spec,PF_DJC,QP_DJC,QP_IMS,QP_JYF,QP_SBR,QP_SJYX,QP_TLL,QM_DJC,QM_TLL"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy));
                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", strWhere));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                return dt;
            }
        }

        ///// <summary>
        ///// 获取下拉框菜单数据
        ///// </summary>
        ///// <returns></returns>
        //public DataTable getSelectModelPart()
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select  ModelPart_id as [value],name as [key] from dbo.sys_web_ModelPart where parent_id = 0 AND enabled = 'Y' ");
        //    return _remotingProxy.GetDataTable(strSql.ToString());
        //}

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveDetectionControl(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("select top 1 1 from dbo.g_outPdline_detection WHERE model_name = @model_name and part_no=@part_no");
                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(),htParams);

                if (dt == null || dt.Rows.Count < 1)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.g_outPdline_detection(type_code ");
                    SqlStringBuilder.Append("						, model_name ");
                    SqlStringBuilder.Append("						,model_spec ");
                    SqlStringBuilder.Append("						,part_no ");
                    SqlStringBuilder.Append("						,part_spec ");
                    SqlStringBuilder.Append("						,PF_DJC ");
                    SqlStringBuilder.Append("						,QP_DJC ");
                    SqlStringBuilder.Append("						,QP_IMS ");
                    SqlStringBuilder.Append("						,QP_JYF ");
                    SqlStringBuilder.Append("						,QP_TLL ");
                    SqlStringBuilder.Append("						,QP_SBR ");
                    SqlStringBuilder.Append("						,QP_SJYX ");
                    SqlStringBuilder.Append("						,QM_TLL ");
                    SqlStringBuilder.Append("						,QM_DJC ) ");
                    SqlStringBuilder.Append("					VALUES(@type_code ");
                    SqlStringBuilder.Append("						, @model_name ");
                    SqlStringBuilder.Append("						,@model_spec ");
                    SqlStringBuilder.Append("						,@part_no ");
                    SqlStringBuilder.Append("						,@part_spec ");
                    SqlStringBuilder.Append("						,@PF_DJC ");
                    SqlStringBuilder.Append("						,@QP_DJC ");
                    SqlStringBuilder.Append("						,@QP_IMS ");
                    SqlStringBuilder.Append("						,@QP_JYF ");
                    SqlStringBuilder.Append("						,@QP_TLL ");
                    SqlStringBuilder.Append("						,@QP_SBR ");
                    SqlStringBuilder.Append("						,@QP_SJYX ");
                    SqlStringBuilder.Append("						,@QM_TLL ");
                    SqlStringBuilder.Append("						,@QM_DJC )");
                }

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

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveModelPart_Sys_part(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("select top 1 1 from dbo.sys_part where part_no = @model_name ");
                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(),htParams);

                if (dt == null || dt.Rows.Count < 1)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO sys_part (part_no ");
                    SqlStringBuilder.Append("						, part_spec ");
                    SqlStringBuilder.Append("						,model_id ");
                    SqlStringBuilder.Append("						,part_type ");
                    SqlStringBuilder.Append("						,create_ymd");
                    SqlStringBuilder.Append("						,create_hms) ");
                    SqlStringBuilder.Append("					VALUES(@model_name ");
                    SqlStringBuilder.Append("						, @model_name ");
                    SqlStringBuilder.Append("						,(select model_id from dbo.sys_model where model_name=@model_name)");
                    SqlStringBuilder.Append("						,2");
                    SqlStringBuilder.Append("						,convert(varchar(10),getdate(),120)");
                    SqlStringBuilder.Append("						,convert(varchar(10),getdate(),108))");
                }

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

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int  updateModelPart(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update g_outPdline_detection  ");
                SqlStringBuilder.Append("SET PF_DJC=@PF_DJC ");
                SqlStringBuilder.Append("	, QP_DJC=@QP_DJC ");
                SqlStringBuilder.Append("	, QP_IMS=@QP_IMS ");
                SqlStringBuilder.Append("	, QP_JYF=@QP_JYF ");
                SqlStringBuilder.Append("	, QP_TLL=@QP_TLL ");
                SqlStringBuilder.Append("	, QP_SBR=@QP_SBR ");
                SqlStringBuilder.Append("	, QP_SJYX=@QP_SJYX ");
                SqlStringBuilder.Append("	, QM_TLL=@QM_TLL ");
                SqlStringBuilder.Append("	, QM_DJC=@QM_DJC ");
                SqlStringBuilder.Append("WHERE model_name=@model_name  and part_no=@part_no ");


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

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateModelPart_Sys_part(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select top 1 1 from dbo.sys_part where part_no = @model_name");
                DataTable dt = _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), htParams);
                if (dt == null || dt.Rows.Count < 1)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO sys_part (part_no ");
                    SqlStringBuilder.Append("						, part_spec ");
                    SqlStringBuilder.Append("						,model_id ");
                    SqlStringBuilder.Append("						,part_type ");
                    SqlStringBuilder.Append("						,create_ymd");
                    SqlStringBuilder.Append("						,create_hms) ");
                    SqlStringBuilder.Append("					VALUES(@model_name ");
                    SqlStringBuilder.Append("						, @model_name ");
                    SqlStringBuilder.Append("						,(select model_id from dbo.sys_model where model_name=@model_name)");
                    SqlStringBuilder.Append("						,2");
                    SqlStringBuilder.Append("						,convert(varchar(10),getdate(),120)");
                    SqlStringBuilder.Append("						,convert(varchar(10),getdate(),108))");
                }

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

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteDetectionControl(String ruid)
        {
            lock (_remotingProxy)
            {
                String delStr = "delete from dbo.g_outPdline_detection where ruid in (" + ruid + ")";
                return _remotingProxy.ExecuteNonQuery(delStr);
            }
        }

        /// <summary>
        /// 更改状态
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int onState(String model_id , String enabled)
        {
            lock (_remotingProxy)
            {
                if (enabled == "Y")
                {
                    enabled = "N";
                }
                else if (enabled == "N")
                {
                    enabled = "Y";
                }

                Hashtable htparams = new Hashtable();
                htparams.Add("@model_id", model_id);
                htparams.Add("@enabled", enabled);

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update sys_model  ");
                SqlStringBuilder.Append("SET enabled=@enabled ");
                SqlStringBuilder.Append("	, update_ymd=convert(varchar(10),getdate(),120) ");
                SqlStringBuilder.Append("	, update_hms=convert(varchar(10),getdate(),108) ");
                SqlStringBuilder.Append("WHERE  model_id=@model_id	 ");


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

        /// <summary>
        /// 变更状态
        /// </summary>
        /// <param name="model_id">变更状态</param>
        /// <returns></returns>
        public int onState(String model_id)
        {
            lock (_remotingProxy)
            {
                Hashtable htparams = new Hashtable();

                htparams.Add("@model_id", model_id);
                String delStr = "delete from dbo.sys_part where part_type=2 and model_id=@model_id";
                return _remotingProxy.ExecuteNonQuery(delStr, htparams);
            }
        }

        /// <summary>
        /// 获取下拉框菜单数据
        /// </summary>
        /// <returns></returns>
        public DataTable getSelectModelPart()
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select type_id as [value],type_name as [key] from dbo.sys_model_type where enabled='Y'");
                return _remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 导入EXCEL时插入数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool InsertData(DataTable dt)
        {
            lock (_remotingProxy)
            {
                List<String> SqlStrings = new List<string>(dt.Rows.Count);

                List<Hashtable> hs = new List<Hashtable>(dt.Rows.Count);

                List<string> ModelCache = new List<string>(dt.Rows.Count);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string ModelName = dt.Rows[i][2].ToString().Trim();

                    if (string.IsNullOrEmpty(ModelName))
                        continue;

                    // 去除重复数据
                    if (ModelCache.Contains(ModelName))
                    {
                        continue;
                    }
                    else
                    {
                        ModelCache.Add(ModelName);
                    }

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);

                    int ModelID = GetModelID(ModelName);
                    int ModelTypeID = GetModelTypeID(dt.Rows[i][5].ToString().Trim());

                    if (ModelID == 0)
                    {
                        SqlStringBuilder.Append("insert into sys_model(model_code,model_type,model_name,model_alias, ");
                        SqlStringBuilder.Append("model_desc, model_type_id, create_userid,create_ymd,create_hms) ");
                        SqlStringBuilder.Append(" values( ");
                        SqlStringBuilder.Append("@model_code,@model_type,@model_name,@model_alias, ");
                        SqlStringBuilder.Append("@model_desc, @model_type_id, @emp_id,convert(varchar(10),getdate(),120),convert(varchar(10),getdate(),108)); ");
                    }
                    else
                    {
                        SqlStringBuilder.Append("update dbo.sys_model set model_code=@model_code,model_type=@model_type,model_alias=@model_alias, ");
                        SqlStringBuilder.Append("model_desc=@model_desc,model_type_id=@model_type_id,update_userid=@emp_id, ");
                        SqlStringBuilder.Append("update_ymd=convert(varchar(10),getdate(),120),update_hms=convert(varchar(10),getdate(),108) ");
                        SqlStringBuilder.Append("where model_id=@model_id;");
                    }

                    // 如果配置零件号不存在，则重新插入
                    if (GetModelPartID(ModelName) == 0)
                    {
                        SqlStringBuilder.Append("insert into dbo.sys_part (part_no,part_spec,model_id,part_type,create_userid,create_ymd,create_hms) ");
                        SqlStringBuilder.Append("values(@model_name,@model_name,(select model_id from dbo.sys_model where model_name=@model_name),2,@emp_id,convert(varchar(10),getdate(),120),convert(varchar(10),getdate(),108));");
                    }


                    //删除A配置后再次新增A配置后会导致配置ID不一致；
                    //获取数据库中当前的配置ID,如果sys_part和sys_model两表不能对应，则更新model_id
                    string getSQLModelName = getModelName(ModelName);
                    ModelID = GetModelID(ModelName);
                    if (getSQLModelName == "0")
                    {
                        SqlStringBuilder.Append("UPDATE dbo.sys_part SET model_id=@model_id,update_userid=@emp_id, ");
                        SqlStringBuilder.Append("update_ymd=convert(varchar(10),getdate(),120),update_hms=convert(varchar(10),getdate(),108) ");
                        SqlStringBuilder.Append("WHERE part_no =@model_name ");
                    }


                    Hashtable values = new Hashtable(15);

                    values.Add("@emp_id", _remotingProxy.UserID);
                    values.Add("@model_id", ModelID);
                    values.Add("@model_type_id", ModelTypeID);
                    values.Add("@model_code", dt.Rows[i][0].ToString());
                    values.Add("@model_type", dt.Rows[i][1].ToString());
                    values.Add("@model_name", dt.Rows[i][2].ToString());
                    values.Add("@model_alias", dt.Rows[i][3].ToString());
                    values.Add("@model_desc", dt.Rows[i][4].ToString());

                    SqlStrings.Add(SqlStringBuilder.ToString());

                    hs.Add(values);
                }

                return _remotingProxy.ExecuteSqlTransaction(SqlStrings, hs);
            }
        }

        /// <summary>
        /// 获取配置编号
        /// </summary>
        /// <param name="modelName">配置名称</param>
        /// <returns>配置编号</returns>
        public int GetModelID(string modelName)
        {
            lock (_remotingProxy)
            {
                string SqlString = "select model_id,model_name from dbo.sys_model where model_name=@model_name order by model_name";

                Hashtable Params = new Hashtable(1);
                Params.Add("@model_name", modelName);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                if (obj == null)
                    return 0;

                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取车型编号
        /// </summary>
        /// <param name="modelTypeName">车型名称</param>
        /// <returns>车型编号</returns>
        public int GetModelTypeID(string modelTypeName)
        {
            lock (_remotingProxy)
            {
                string SqlString = "select type_id,type_name from dbo.sys_model_type where type_name=@type_name order by type_name";

                Hashtable Params = new Hashtable(1);

                Params.Add("type_name", modelTypeName);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                if (obj == null)
                    return 0;

                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取配置零件号
        /// </summary>
        /// <param name="modelName">配置名称</param>
        /// <returns>配置的零件编号</returns>
        public int GetModelPartID(string modelName)
        {
            lock (_remotingProxy)
            {
                string SqlString = "select part_id from dbo.sys_part where part_no=@part_no";

                Hashtable Params = new Hashtable(1);
                Params.Add("@part_no", modelName);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                if (obj == null)
                    return 0;

                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取配置名称
        /// </summary>
        /// <returns></returns>
        public string getModelName(string modelName)
        {
            lock (_remotingProxy)
            {
                StringBuilder querySen = new StringBuilder(1024);
                querySen.AppendLine("SELECT  model_name  ");
                querySen.AppendLine("FROM    dbo.sys_model  ");
                querySen.AppendLine("WHERE   model_id = ( SELECT model_id  ");
                querySen.AppendLine("                     FROM   dbo.sys_part  ");
                querySen.AppendLine("                     WHERE  part_no = @modelName  ");
                querySen.AppendLine("                   )  ");

                Hashtable ht = new Hashtable(1);
                ht.Add("@modelName",modelName);

                object obj = _remotingProxy.GetScalar(querySen.ToString(),ht);
                if (obj == null)
                    return "0";

                return obj.ToString();
            }
        }

        /// <summary>
        /// 获取配置编号
        /// </summary>
        /// <param name="modelName">配置名称</param>
        /// <returns>配置编号</returns>
        public int GetModelIDFromPart(string modelName)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT model_id FROM sys_part WHERE part_no=@model_name ";
                Hashtable Params = new Hashtable(1);
                Params.Add("@model_name", modelName);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                if (obj == null)
                    return 0;

                return Convert.ToInt32(obj);
            }
        }


        #endregion  成员方法
    }
}