Estsh-Yiri/Estsh.Core.Services/Repositories/TerminalEmpDefineDal.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;

namespace Estsh.Core.Repositories
{

    /**
     * 工位帐号管理
     * 
     * NOAH
     * 
     */
    public class TerminalEmpDefineDal : BaseApp
	{
        public TerminalEmpDefineDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { 
        }
		#region  成员方法
		/// <summary>
		/// 获得菜单列表数据
		/// </summary>
		public DataTable getList(string strWhere,string filedOrder)
		{
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select a.*,b.terminal_name,c.emp_no from dbo.sys_emp_matrix AS a  LEFT JOIN dbo.sys_terminal AS b ON a.terminal_id=b.terminal_id LEFT JOIN dbo.sys_emp AS c ON a.emp_id=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());
            }
		}

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getTerminalEmpListByPage(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", @" dbo.sys_emp_matrix AS a  LEFT JOIN dbo.sys_terminal AS b ON a.terminal_id=b.terminal_id LEFT JOIN dbo.sys_emp AS c ON a.emp_id=c.emp_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " a.*,b.terminal_name,c.emp_name "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy.Replace("a.ruid", "")));
                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 getTerminalEmpExport(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", @" dbo.sys_emp_matrix AS a  LEFT JOIN dbo.sys_terminal AS b ON a.terminal_id=b.terminal_id LEFT JOIN dbo.sys_emp AS c ON a.emp_id=c.emp_id "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " a.*,b.terminal_name,c.emp_name"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy.Replace("a.ruid", "")));
                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>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveTerminalEmp(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.sys_emp_matrix( emp_id ,emp_no ,terminal_id,create_userid,create_ymd,create_hms) values");
                SqlStringBuilder.Append("(@emp_id,@emp_no,@terminal_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 updateTerminalEmp(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_emp_matrix SET  ");
                SqlStringBuilder.Append("emp_id=@emp_id,emp_no=@emp_no,terminal_id=@terminal_id,update_userid=@update_userid,update_ymd=Convert(varchar(10),getdate(),23),update_hms=Convert(varchar(10),getdate(),108) ");
                SqlStringBuilder.Append("WHERE ruid=@ruid ");

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

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

        /// <summary>
        /// 根据帐号名称查找对应编号
        /// </summary>
        /// <param name="custPDLineDesc">客户产线描述</param>
        /// <returns>客户产线编号</returns>
        public int GetEmpno(string emp_no)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT emp_id FROM dbo.sys_emp WHERE emp_no=@emp_no";

                Hashtable Params = new Hashtable(1);

                Params.Add("@emp_no", emp_no);

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

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

        /// <summary>
        /// 根据帐号名称查找对应编号
        /// </summary>
        /// <param name="custPDLineDesc">客户产线描述</param>
        /// <returns>客户产线编号</returns>
        public int GetEmpId(string emp_no)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT emp_id FROM dbo.sys_emp WHERE emp_no=@emp_no";

                Hashtable Params = new Hashtable(1);

                Params.Add("@emp_no", emp_no);

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

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

        public int GetTerminalName(string terminal_name)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT terminal_id FROM dbo.sys_terminal WHERE terminal_name=@terminal_name";

                Hashtable Params = new Hashtable(1);

                Params.Add("@terminal_name", terminal_name);

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

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

        public bool GetEmpnoTerminalId(string terminal_name, string emp_no)
        {
            lock (_remotingProxy)
            {
                int emp_id = GetEmpno(emp_no);
                int terminal_id = GetTerminalName(terminal_name);
                string SqlString = "SELECT  top 1 1  FROM dbo.sys_emp_matrix WHERE emp_id=@emp_id AND terminal_id=@terminal_id";

                Hashtable Params = new Hashtable(2);

                Params.Add("@emp_id", emp_id);
                Params.Add("@terminal_id", terminal_id);


                return _remotingProxy.Exists(SqlString, Params);
            }
        }

        /// <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<String> ModelCache = new List<string>(dt.Rows.Count);

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

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int emp_id = GetEmpno(dt.Rows[i][1].ToString());
                    int terminal_id = GetTerminalName(dt.Rows[i][0].ToString().Trim());

                    //// 跳过空行
                    //if (string.IsNullOrEmpty(emp_no.Trim()))
                    //{
                    //    continue;
                    //}

                    //// 跳过重复的行
                    //if (ModelCache.Contains(ruid))
                    //{
                    //    continue;
                    //}
                    //else
                    //{
                    //    ModelCache.Add(dt.Rows[i][0].ToString());
                    //}

                    

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("insert into sys_emp_matrix(emp_id,emp_no,terminal_id");
                    SqlStringBuilder.Append(" ) values( ");
                    SqlStringBuilder.Append("@emp_id,@emp_no,@terminal_id) ");


                    Hashtable values = new Hashtable(3);
                    values.Add("@emp_id", emp_id);
                    values.Add("@emp_no", dt.Rows[i][1].ToString());
                    values.Add("@terminal_id", terminal_id);
          
                    SqlStrings.Add(SqlStringBuilder.ToString());

                    hs.Add(values);
                }

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

        /// <summary>
        /// 判断车型是否存在
        /// </summary>
        /// <param name="modelName"></param>
        /// <returns></returns>
        public bool ExistsTerminalName(string terminal_name)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT top 1 1 FROM dbo.sys_terminal where terminal_name=@terminal_name ";

                Hashtable Params = new Hashtable(1);

                Params.Add("@terminal_name", terminal_name);
                //Params.Add("@terminal_id", terminal_id);

                return _remotingProxy.Exists(SqlString, Params);
            }
        }

        public bool ExistsEmpNo(string emp_no)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT top 1 1 FROM dbo.sys_emp where emp_no=@emp_no"; 

                Hashtable Params = new Hashtable(1);

                Params.Add("@emp_no", emp_no);

                return _remotingProxy.Exists(SqlString, Params);
            }
        }

		#endregion  成员方法
        
	}
}