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

namespace DA
{
    public class DBUsersManages
    {
        /// <summary>
        /// DA查询用户表
        /// </summary>
        /// <returns></returns>
        public DataSet DBUserSearch()
        {
            string sql = "select u.uId,u.uName,r.rName,u.uPwd,u.rId from Users u inner join Roles r on u.rId=r.rId";
            return SqlDB.Query(sql);
        }
        /// <summary>
        /// DA添加用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool DBAddUser(Common.CUsers user)
        {
              SqlParameter[] parameters = new SqlParameter[] 
               {             
                 new SqlParameter("@uName", SqlDbType.VarChar,20),
                 new SqlParameter("@RealName", SqlDbType.VarChar,20),
                 new SqlParameter("@uPwd", SqlDbType.NVarChar,20),
                 new SqlParameter("@rId", SqlDbType.Int),
                };
             parameters[0].Value = user.CuName1;
             parameters[1].Value = user.RealName;
             parameters[2].Value = user.CuPwd1;
             parameters[3].Value = user.CrId1;
             int j = SqlHelper.ExecuteNonQuery(SqlHelper.strconn, CommandType.StoredProcedure, "Pro_InsertNewUser", parameters);
             if (j > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
            // string sql = "insert Users values('"+user.CuName1+"','"+user.CuPwd1+"','"+user.CrId1 +"')";
            //return SqlDB.ExcuteSql(sql);
        }
        /// <summary>
        /// DA修改用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool DBUpdateUser(Common.CUsers user)
        {
            string sql = "update Users set uName='" + user.CuName1 + "',uPwd='" + user.UnewPwd  + "',rId=" + user.CrId1 + " where uId=" + user.CuId1;
            return SqlDB.ExcuteSql(sql);
        }
        /// <summary>
        /// DA删除用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool DBDeleteUser(Common.CUsers user)
        {
            string sql = "delete from Users where uId=" + user.CuId1;
            return SqlDB.ExcuteSql(sql);
        }
        /// <summary>
        /// DA查询控件表
        /// </summary>
        /// <returns></returns>
        public DataSet DBMenusTiaoSearch()
        {
            string sql = "select * from menusTiao";
            return SqlDB.Query(sql);
        }
        /// <summary>
        /// DA添加角色表并返回角色ID
        /// </summary>
        /// <param name="roles"></param>
        /// <returns></returns>
        public DataSet DBAddRoles(Common.CRoles roles)
        {
            DataSet ds = null;
            string sql2 = "insert Roles values('"+roles.CrName1+"')";
            string sql3 = "select max(rId) rId from Roles";
            if(SqlDB.ExcuteSql(sql2))
            {
                ds = SqlDB.Query(sql3);
                return ds;
            }
            return ds;
        }
        /// <summary>
        /// DA添加角色与菜单表
        /// </summary>
        /// <returns></returns>
        public bool DBAddRolesMenus(Common.CRolesMenu menu)
        {
            string sql = "insert RolesMenu values(" + menu.CrId1 + "," + menu.CmId1 + ",null)";
         
            return SqlDB.ExcuteSql(sql);
        }
        /// <summary>
        /// DA根据角色ID查询角色与菜单表
        /// </summary>
        /// <returns></returns>
        public DataSet DBRolesMenusSeachByRid(Common.CRoles roles)
        {
            string sql = "select mId from RolesMenu where rId=" + roles.CrId1;
            return SqlDB.Query(sql);
        }
        /// <summary>
        /// DA根据角色ID删除角色与菜单表
        /// </summary>
        /// <returns></returns>
        public bool DBDelRolesMenusByRid(Common.CRolesMenu menu)
        {
            string sql = "delete from RolesMenu where rId=" + menu.CrId1;
            return SqlDB.ExcuteSql(sql);
        }
        /// <summary>
        /// DA删除角色、角色权限及该角色的用户
        /// </summary>
        /// <returns></returns>
        public bool DBDelRolesMenusAndUsersAndRolesByRid(Common.CRoles roles)
        {
            string sql2 = "delete from RolesMenu where rId=" + roles.CrId1;
            string sql3 = "delete from Users where rId=" + roles.CrId1;
            string sql4 = "delete from Roles where rId=" + roles.CrId1;
            SqlCommand com = new SqlCommand();
            com.Connection = SqlDB.con;
            SqlDB.con.Open();
            SqlTransaction st = SqlDB.con.BeginTransaction();
            com.Transaction = st;
            try
            {
                com.CommandText = sql2;
                com.ExecuteNonQuery();
                com.CommandText = sql3;
                com.ExecuteNonQuery();
                com.CommandText = sql4;
                com.ExecuteNonQuery();
                st.Commit();
                return true;
            }
            catch
            {
                st.Rollback();
                return false;
            }
            finally
            {
                SqlDB.con.Close();
            }
        }
    }
}