using System;
using System.Data;
using System.Data.SqlClient;
using Business.System.Op.Vo;
using Business.System.User.Entity;
using Common;

namespace Business.System.User.Vo
{
    public class UserVo
    {
        /// <summary>
        /// 参数赋值
        /// </summary>
        /// <param name="user">实体类UserEty</param>
        /// <returns>参数数组</returns>
        private SqlParameter[] SetPara(UserEty user)
        {
            return new[]
                {
                    new SqlParameter("@userid", user.Userid),
                    new SqlParameter("@username", user.Username),
                    new SqlParameter("@delflag", user.Delflag),
                    new SqlParameter("@adduserid", user.Adduserid),
                    new SqlParameter("@addtime", user.Addtime),
                    new SqlParameter("@depid",user.Depid),
                    new SqlParameter("@mobile",user.Mobile),
                    new SqlParameter("@useraccount",user.Useraccount),
                    new SqlParameter("@userpassword",user.Userpassword)
                };
        }

        /// <summary>
        /// 新增user记录
        /// </summary>
        /// <param name="user">实体类UserEty</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string InsertUser(UserEty user)
        {
            try
            {
                //获取全部程序操作
                DataTable op = new OpVo().AllOp();
                string ops = string.Empty;
                foreach (DataRow row in op.Rows)
                {
                    ops += row["code"] + ",";
                }
                //保存用户权限
                string lmtSql = "insert into SysLmt values(@userid,@ops)";

                //保存用户
                var sql =
                    "insert into SysUser(userid,username,delflag,adduserid,addtime,depid,mobile,useraccount,userpassword) values(@userid,@username,@delflag,@adduserid,@addtime,@depid,@mobile,@useraccount,@userpassword)";
                
                //建立事务所需二维数组
                var sqlT = new object[2, 2];
                sqlT[0, 0] = lmtSql;
                sqlT[0, 1] = new[] { new SqlParameter("@userid", user.Userid), new SqlParameter("@ops", ops.TrimEnd(new[] { ',' })) };

                sqlT[1, 0] = sql;
                sqlT[1, 1] = SetPara(user);

                return SqlHelper.ExecSqlT(sqlT);
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user">实体类UserEty</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string UpdateUser(UserEty user)
        {
            var sq1 =
                "update SysUser set username=@username,depid=@depid,mobile=@mobile,useraccount=@useraccount where userid=@userid";
            return SqlHelper.ExecSql(sq1, SetPara(user));
        }

        /// <summary>
        /// 根据userid删除用户信息
        /// </summary>
        /// <param name="userid">用户userid</param>
        /// <returns>成功返回空字符串,失败返回错误信息</returns>
        public string DelUser(string userid)
        {
            var sql = "update SysUser set delflag='true' where userid in (" + userid + ")";
            return SqlHelper.ExecSql(sql);
        }

        /// <summary>
        /// 根据部门ID获取该部门所有用户
        /// </summary>
        /// <param name="searchText">搜索字段</param>
        /// <param name="depid">部门ID</param>
        /// <returns>数据表</returns>
        public DataTable GetUserByDepid(string searchText,string depid)
        {
            var sql =
                "SELECT  p.userid, p.username, p.mobile, p.useraccount,  p.addtime, vp.username FROM  SysUser p left join SysUser vp on p.adduserid=vp.userid where p.depid=@depid and p.delflag ='false'";
            if (!string.IsNullOrEmpty(searchText))
            {
                //根据查询字段 进行查询
                sql +=
                    string.Format(
                        "and( p.username like '%{0}%' or p.useraccount like '%{0}%' or p.mobile like '%{0}%' or vp.username like '%{0}%') ",
                        searchText);
            }
            sql += " order by p.addtime DESC";

            return SqlHelper.ExecSqlDateTable(sql,new SqlParameter("@depid",depid));    
        }

        //用户修改密码
        public string Upwd(string userid,string oPwd,string nPwd)
        {
            string check = "select count(*) from SysUser where userid=@userid and userpassword=@userpassword";
            string result = SqlHelper.ExecSqlSc(check, new SqlParameter("@userid", userid), new SqlParameter("@userpassword", SecurityHelper.Md5Encrypt(oPwd)));
            if (!SqlHelper.CheckSc(result))
            {
                return result.Split(':')[1];
            }
            try
            {
                int count = Convert.ToInt32(result);
                if (count <= 0)
                {
                    return "用户旧密码错误";
                }
                string Up = "update SysUser set userpassword=@userpassword where userid=@userid";
                return SqlHelper.ExecSql(Up, new SqlParameter("@userid", userid), new SqlParameter("@userpassword", SecurityHelper.Md5Encrypt(nPwd)));
            }
            catch (Exception ex)
            {
                return ex.Message; 
            }
        }

        /// <summary>
        /// 根据用户ID获取用户名
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <returns></returns>
        public string UserName(string userid)
        {
            string sql = "select username from SysUser where userid=@userid";
            return SqlHelper.ExecSqlSc(sql, new SqlParameter("@userid", userid));
        }
    }
}