55128.cn_kjh.55128.cn_m.55128.cn/CP55128/CB.Data.SqlServer/System/SysUserManage.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using CB.Common;
using CB.Entity;
using CB.Interface.Infrastructure;

namespace CB.Data.SqlServer
{
    public class SysUserManage : Repository<SysUserInfo>, ISysUserService
    {
        public SysUserManage(string interfaceId)
            : base(interfaceId)
        {
        }

        public override bool Save(SysUserInfo entity)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,250,@"INSERT INTO [SYS_SysUser]([UserName],[PassWord],[NickName],[GroupId],[Status],[Email],[TelPhone],[RegIP])VALUES(@UserName,@PassWord,@NickName,@GroupId,@Status,@Email,@TelPhone,@RegIP)"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,150,@"@UserName nchar(20),@PassWord char(32),@NickName nchar(20),@GroupId int,@Status bit,@Email nvarchar(50),@TelPhone nchar(20),@RegIP nchar(50)"),
                DbHelper.MakeInParam(InterfaceId,"@UserName",(DbType)SqlDbType.NChar,20,entity.UserName),
                DbHelper.MakeInParam(InterfaceId,"@PassWord",(DbType)SqlDbType.Char,32,entity.PassWord),
                DbHelper.MakeInParam(InterfaceId,"@NickName",(DbType)SqlDbType.NChar,20,entity.NickName),
                DbHelper.MakeInParam(InterfaceId,"@GroupId",(DbType)SqlDbType.Int,4,entity.GroupId),
                DbHelper.MakeInParam(InterfaceId,"@Status",(DbType)SqlDbType.Bit,1,entity.Status),
                DbHelper.MakeInParam(InterfaceId,"@Email",(DbType)SqlDbType.NVarChar,50,entity.Email),
                DbHelper.MakeInParam(InterfaceId,"@TelPhone",(DbType)SqlDbType.NChar,20,entity.TelPhone),
                DbHelper.MakeInParam(InterfaceId,"@RegIP",(DbType)SqlDbType.NChar,50,entity.RegIP)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId,CommandType.StoredProcedure, "dbo.sp_executesql", pars) > 0 ? true : false;
        }

        public override bool Update(SysUserInfo entity)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,170,@"UPDATE [SYS_SysUser] SET [NickName]=@NickName,[GroupId]=@GroupId,[Status]=@Status,[Email]=@Email,[TelPhone]=@TelPhone WHERE [Uid]=@Uid"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,150,@"@NickName nchar(20),@GroupId int,@Status bit,@Email nvarchar(50),@TelPhone nchar(20),@Uid int"),
                DbHelper.MakeInParam(InterfaceId,"@NickName",(DbType)SqlDbType.NChar,20,entity.NickName),
                DbHelper.MakeInParam(InterfaceId,"@GroupId",(DbType)SqlDbType.Int,4,entity.GroupId),
                DbHelper.MakeInParam(InterfaceId,"@Status",(DbType)SqlDbType.Bit,1,entity.Status),
                DbHelper.MakeInParam(InterfaceId,"@Email",(DbType)SqlDbType.NVarChar,50,entity.Email),
                DbHelper.MakeInParam(InterfaceId,"@TelPhone",(DbType)SqlDbType.NChar,20,entity.TelPhone),
                DbHelper.MakeInParam(InterfaceId,"@Uid",(DbType)SqlDbType.Int,4,entity.Uid)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId,CommandType.StoredProcedure, "dbo.sp_executesql", pars) > 0 ? true : false;
        }

        public override bool Delete(int Id)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,50,"DELETE FROM [SYS_SysUser] WHERE [Uid]=@Uid"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@Uid int"),
                DbHelper.MakeInParam(InterfaceId,"@Uid",(DbType)SqlDbType.Int,4,Id)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId,CommandType.StoredProcedure, "dbo.sp_executesql", pars) > 0 ? true : false;
        }

        public override SysUserInfo Get<TKey>(TKey key)
        {
            DbParameter[] pars ={
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,200,"SELECT [Uid],[UserName],[PassWord],[NickName],[GroupId],[Status],[SuperAdmin],[Email],[TelPhone],[LoginTimes],[RegIP],[RegDate],[LastLoginIP],[LastLoginDate] FROM [SYS_SysUser] WHERE [Uid]=@uid"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@uid int"),
                DbHelper.MakeInParam(InterfaceId,"@uid",(DbType)SqlDbType.Int,4,key)
                                };
            SysUserInfo entity = null;
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.StoredProcedure, "[dbo].[sp_executesql]", pars))
            {
                if (reader.Read())
                {
                    entity = LoadEntity(reader);
                }
                reader.Dispose();
            }
            return entity;
        }

        public override IList<SysUserInfo> ToList()
        {
            throw new NotImplementedException();
        }

        public override IList<SysUserInfo> ToList(SysUserInfo entity)
        {
            throw new NotImplementedException();
        }

        public override IList<SysUserInfo> ToPaging(SysUserInfo entity, int pageSize, int pageIndex, out int recordCount)
        {
            recordCount = 0;

            string where = " 1=1 ";
            if (!string.IsNullOrEmpty(entity.UserName))
            { where = string.Format("{0} AND [UserName] LIKE '%{1}%'", where, entity.UserName); }
            if (0 < entity.GroupId)
            { where = string.Format("{0} AND [GroupId]={1}", where, entity.GroupId); }

            DbParameter[] pars ={
                DbHelper.MakeInParam(InterfaceId,"@pageSize",(DbType)SqlDbType.Int,4,pageSize),
                DbHelper.MakeInParam(InterfaceId,"@page",(DbType)SqlDbType.Int,4,pageIndex),
                DbHelper.MakeInParam(InterfaceId,"@tableName",(DbType)SqlDbType.NVarChar,500,"[SYS_SysUser]"),
                DbHelper.MakeInParam(InterfaceId,"@field",(DbType)SqlDbType.NVarChar,1000,
                    "[Uid],[UserName],[PassWord],[NickName],[GroupId],[Status],[SuperAdmin],[Email],[TelPhone],[LoginTimes],[RegIP],[RegDate],[LastLoginIP],[LastLoginDate]"),
                DbHelper.MakeInParam(InterfaceId,"@orderField",(DbType)SqlDbType.NVarChar,50,"[Uid] ASC"),
                DbHelper.MakeInParam(InterfaceId,"@where",(DbType)SqlDbType.NVarChar,2000,where)
                                };

            IList<SysUserInfo> list = new List<SysUserInfo>();
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.StoredProcedure, "usp_st_page", pars))
            {
                while (reader.Read())
                {
                    list.Add(LoadEntity(reader));
                }
                if (reader.NextResult() && reader.Read())
                {
                    recordCount = reader.GetInt32(0);
                }
                reader.Dispose();
            }
            return list;
        }

        public SysShortUserInfo GetShortSysUser(string userName, string passWord, string IP)
        {
            DbParameter[] pars ={
                DbHelper.MakeInParam(InterfaceId,"@username",(DbType)SqlDbType.NChar,20,userName),
                DbHelper.MakeInParam(InterfaceId,"@userpwd",(DbType)SqlDbType.Char,32,passWord),
                DbHelper.MakeInParam(InterfaceId,"@ip",(DbType)SqlDbType.NChar,50,IP)
                                };
            SysShortUserInfo entity = null;
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.StoredProcedure, "usp_SysUser_login", pars))
            {
                if (reader.Read())
                {
                    entity = new SysShortUserInfo()
                    {
                        Uid = TypeConverter.ObjectToInt(reader["Uid"]),
                        UserName = reader["UserName"].ToString().Trim(),
                        PassWord = reader["PassWord"].ToString().Trim(),
                        NickName = reader["NickName"].ToString().Trim(),
                        GroupId = TypeConverter.ObjectToInt(reader["GroupId"]),
                        Status = TypeConverter.ObjectToBool(reader["Status"], false),
                        SuperAdmin = TypeConverter.ObjectToBool(reader["SuperAdmin"], false),
                    };
                }
                reader.Dispose();
            }
            return entity;
        }

        public bool UpdateUserStatus(int uid)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,0,"UPDATE [SYS_SysUser] SET [Status]=~[Status] WHERE [Uid]=@uid"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@uid int"),
                DbHelper.MakeInParam(InterfaceId,"@uid",(DbType)SqlDbType.Int,4,uid)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId,CommandType.StoredProcedure, "dbo.sp_executesql", pars) > 0 ? true : false;
        }

        public bool UpdateUserPassWord(int uid, string oldPassWord, string newPassWord)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,100,"UPDATE [SYS_SysUser] SET [PassWord]=@newpwd WHERE [Uid]=@uid AND [PassWord]=@oldpwd"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,50,"@newpwd char(32),@uid int,@oldpwd char(32)"),
                DbHelper.MakeInParam(InterfaceId,"@uid",(DbType)SqlDbType.Int,4,uid),
                DbHelper.MakeInParam(InterfaceId,"@newpwd",(DbType)SqlDbType.Char,32,newPassWord),
                DbHelper.MakeInParam(InterfaceId,"@oldpwd",(DbType)SqlDbType.Char,32,oldPassWord)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId,CommandType.StoredProcedure, "dbo.sp_executesql", pars) > 0 ? true : false;
        }

        protected override SysUserInfo LoadEntity(IDataReader reader)
        {
            return new SysUserInfo
            {
                Uid = TypeConverter.ObjectToInt(reader["Uid"]),
                UserName = reader["UserName"].ToString().Trim(),
                PassWord = reader["PassWord"].ToString().Trim(),
                NickName = reader["NickName"].ToString().Trim(),
                GroupId = TypeConverter.ObjectToInt(reader["GroupId"]),
                Status = TypeConverter.ObjectToBool(reader["Status"], false),
                SuperAdmin = TypeConverter.ObjectToBool(reader["SuperAdmin"], false),
                Email = reader["Email"].ToString(),
                TelPhone = reader["TelPhone"].ToString().Trim(),
                LoginTimes = TypeConverter.ObjectToInt(reader["LoginTimes"]),
                RegIP = reader["RegIP"].ToString().Trim(),
                RegDate = TypeConverter.ObjectToDateTime(reader["RegDate"], DateTime.MinValue),
                LastLoginIP = reader["LastLoginIP"].ToString().Trim(),
                LastLoginDate = TypeConverter.ObjectToDateTime(reader["LastLoginDate"], DateTime.MinValue)
            };
        }

        protected override SysUserInfo LoadEntity(DataRow dr)
        {
            return new SysUserInfo
            {
                Uid = TypeConverter.ObjectToInt(dr["Uid"]),
                UserName = dr["UserName"].ToString().Trim(),
                PassWord = dr["PassWord"].ToString().Trim(),
                NickName = dr["NickName"].ToString().Trim(),
                GroupId = TypeConverter.ObjectToInt(dr["GroupId"]),
                Status = TypeConverter.ObjectToBool(dr["Status"], false),
                SuperAdmin = TypeConverter.ObjectToBool(dr["SuperAdmin"], false),
                Email = dr["Email"].ToString(),
                TelPhone = dr["TelPhone"].ToString().Trim(),
                LoginTimes = TypeConverter.ObjectToInt(dr["LoginTimes"]),
                RegIP = dr["RegIP"].ToString().Trim(),
                RegDate = TypeConverter.ObjectToDateTime(dr["RegDate"], DateTime.MinValue),
                LastLoginIP = dr["LastLoginIP"].ToString().Trim(),
                LastLoginDate = TypeConverter.ObjectToDateTime(dr["LastLoginDate"], DateTime.MinValue)
            };
        }
    }
}