using System;
using System.Collections.Generic;
using System.Text;
using Model;
using Common;
using System.Data;
using System.Collections;
using Commom.Utility;

namespace BLL
{
    public class DALRoles : IDALRoles
    {
        public IList<Roles> GetList()
        {
            try
            {
                IList<Roles> ls = new List<Roles>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select guid,(RoleNum+'-'+RoleName) as RoleName from Roles where IsDel !=1;");

                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
                ls = ModelConvertHelper<Roles>.DataReaderToModel(dt);

                return ls;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public IList<Roles> GetList(AjaxRoleList Json, ref PageInfo page)
        {
            try
            {

                IList<Roles> list = new List<Roles>();
                StringBuilder strSql = new StringBuilder();
                List<SqlParam> para = new List<SqlParam>();
                strSql.Append("Select guid,RoleNum,RoleName,Demo,CreatUser,CreatTime,UpdateUser,UpdateTime,Ord,IsDel from View_GetRoles ");

                if (Json.RoleNum != null && Json.RoleNum != "")
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("RoleNum like '%' + @RoleNum + '%' ");
                    para.Add(new SqlParam("@RoleNum", Json.RoleNum));
                }

                if (Json.RoleName != null && Json.RoleName != "")
                {
                    if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
                    strSql.Append("RoleName like '%' + @RoleName + '%'  ");
                    para.Add(new SqlParam("@RoleName", Json.RoleName));
                }

                SqlParam[] param = null;
                if (para != null)
                    param = para.ToArray();

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "Ord", "DESC", ref page);
                list = ModelConvertHelper<Roles>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }

        }

        public Roles GetModel(string RoleNum)
        {
            try
            {
                Roles us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select RoleNum,RoleName,IsDel,Demo from View_GetRoles where ");
                strSql.Append("RoleNum = @RoleNum ");
                strSql.Append("and IsDel != 1");
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@RoleNum", RoleNum),
                };

                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);

                us = ModelConvertHelper<Roles>.ReaderToModel(dt);

                return us;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// 获取根菜单
        /// </summary>
        /// <param name="RoleNum"></param>
        /// <returns></returns>
        public IList<ResMenu> GetParentMenu(string RoleNum)
        {
            try
            {
                IList<ResMenu> list = new List<ResMenu>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select tb1.ReNum as ResNum,tb2.ResName from[Relation] as tb1 ");
                strSql.Append("left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum ");
                strSql.Append("where tb1.RoleId = '" + RoleNum + "' and ParentNum= '' order by Ord; ");


                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
                list = ModelConvertHelper<ResMenu>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// 获取角色可分配页面
        /// </summary>
        /// <param name="RoleNum">角色编号</param>
        /// <param name="ParentNum">根菜单编号</param>
        /// <returns></returns>
        public IList<ResMenu> GetResMenus(string RoleNum, string Role, string ParentNum)
        {
            try
            {
                IList<ResMenu> list = new List<ResMenu>();
                StringBuilder strSql = new StringBuilder();

                strSql.Append("select tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime,");
                strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb2.ResNum and RoleNum ='" + Role + "'),'0')!='0' then '1' else '0' end as Sel ");
                strSql.Append("from [ResMenu] as tb2 ");
                strSql.Append("where tb2.ParentNum= '" + ParentNum + "' order by ord; ");

                // liudl Eidt 不考虑多级分权限
                //strSql.Append("select tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime,");
                //strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb1.ReNum and RoleNum ='" + Role + "'),'0')!='0' then '1' else '0' end as Sel ");
                //strSql.Append("from[Relation] as tb1 left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum  ");
                //strSql.Append("where tb1.RoleNum = '" + RoleNum + "' and tb2.ParentNum= '" + ParentNum + "'; ");

                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
                list = ModelConvertHelper<ResMenu>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// 获取视图内按钮权限
        /// </summary>
        /// <param name="RoleNum"></param>
        /// <param name="RoleNums"></param>
        /// <param name="ParentNum"></param>
        /// <returns></returns>
        public IList<ResBtnItem> GetResBtnItems(string RoleNum, string RoleNums, string ParentNum)
        {
            try
            {
                IList<ResBtnItem> list = new List<ResBtnItem>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select '" + RoleNum + "' as RoleNum,'"+ RoleNums + "' as Guid,tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime, ");
                strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb2.ResNum and RoleNum ='" + RoleNum + "'),'0')!='0' then '1' else '0' end as Sel ");
                strSql.Append("from [ResMenu] as tb2 ");
                strSql.Append("where tb2.ParentNum= '" + ParentNum + "' order by ord; ");

                // liudl Edit 不考虑多级分权限
                //strSql.Append("select '" + RoleNum + "' as RoleNum,tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime, ");
                //strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb1.ReNum and RoleNum ='" + RoleNum + "'),'0')!='0' then '1' else '0' end as Sel ");
                //strSql.Append("from[Relation] as tb1 ");
                //strSql.Append("left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum  ");
                //strSql.Append("where tb1.RoleNum = '" + RoleNums + "' and tb2.ParentNum= '" + ParentNum + "' ");

                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
                list = ModelConvertHelper<ResBtnItem>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// 获取用户所属角色编码
        /// </summary>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public string GetResCode(string usercode)
        {
            string roleNum = "";
            StringBuilder sbstr = new StringBuilder();
            sbstr.Append("select RoleNum from userinfo where userCode='" + usercode + "'");
            DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbstr);
            if (dt != null)
            {
                roleNum = dt.Rows[0][0].ToString();
            }

            return roleNum;
        }

        /// <summary>
        /// 保存菜按钮权限单权限
        /// </summary>
        /// <param name="resBtnItem"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public bool SetRelationData(ResBtnItem resBtnItem, string userCode)
        {
            bool bl = false;
            if (resBtnItem != null)
            {
                StringBuilder sbStr = new StringBuilder();
                if (resBtnItem.Sel == "1")
                {
                    sbStr.Append("Delete from Relation where ReNum = '" + resBtnItem.ResNum + "' and RoleNum = '" + resBtnItem.RoleNum + "';");
                    if (!DoesItExist(resBtnItem))
                    {
                        sbStr.Append("Delete from Relation where ReNum = '" + resBtnItem.ParentNum + "' and RoleNum = '" + resBtnItem.RoleNum + "';");
                    }
                }
                else
                {
                    sbStr.Append("INSERT INTO Relation values ('" + resBtnItem.RoleNum + "','" + resBtnItem.Guid+ "','" + resBtnItem.ResNum + "',GetDate(),'" + userCode + "');");
                    if (!DoesItExist(resBtnItem))
                    {
                        sbStr.Append("INSERT INTO Relation values ('" + resBtnItem.RoleNum + "','" + resBtnItem.Guid + "','" + resBtnItem.ParentNum + "',GetDate(),'" + userCode + "');");
                    }
                }

                int rowNum = DataFactory.SqlDataBase().ExecuteBySql(sbStr);
                if (rowNum > 0)
                {
                    bl = true;
                }
            }

            return bl;
        }

        /// <summary>
        /// 判断页面权限是否已存在
        /// </summary>
        /// <param name="resBtnItem"></param>
        /// <returns>felse:不存在 true:已存在</returns>
        private bool DoesItExist(ResBtnItem resBtnItem)
        {
            bool bl = false;
            StringBuilder sbstr = new StringBuilder();
            if (resBtnItem.Sel == "1")
            {
                sbstr.Append("select count(*) as RowNum from Relation as tb1 ");
                sbstr.Append("left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum ");
                sbstr.Append("where tb1.ReNum != '" + resBtnItem.ResNum + "' ");
                sbstr.Append("and tb1.RoleNum = '" + resBtnItem.RoleNum + "' and tb2.ParentNum = '" + resBtnItem.ParentNum + "'; ");
            }
            else
            {
                sbstr.Append("select count(*) as RowNum from Relation ");
                sbstr.Append("where RoleNum = '" + resBtnItem.RoleNum + "' and ReNum = '" + resBtnItem.ParentNum + "';");
            }

            DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbstr);
            if (dt.Rows[0]["RowNum"].ToString() != "0")
            {
                bl = true;
            }

            return bl;
        }

        /// <summary>
        /// 设置菜单页权限
        /// </summary>
        /// <param name="roles"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public bool SetResMenus(ResBtnItem roles , string userCode) 
        {
            bool bl = false;
            if (roles != null)
            {
                StringBuilder sbStr = new StringBuilder();
                if (roles.Sel == "1")
                {
                    // 删除按钮权限
                    sbStr.Append("delete from Relation where ReNum in ");
                    sbStr.Append("(select ResNum from ResMenu where ParentNum = '"+ roles.ResNum+ "') and RoleNum = '"+roles.RoleNum+"';");

                    // 删除菜单权限
                    sbStr.Append("delete from Relation where ReNum = '"+ roles.ResNum + "' and RoleNum  ='" + roles.RoleNum + "';");

                    if (!DoesItExist(roles))   // 判断是否需要删除功能模块权限
                    {
                        sbStr.Append("delete from Relation where ReNum in ");
                        sbStr.Append("(select ParentNum from ResMenu where ResNum ='" + roles.ResNum + "') and RoleNum = '" + roles.RoleNum + "';");
                    }
                }
                else
                {
                    StringBuilder sbSql = new StringBuilder();
                    sbSql.Append("select ResNum from ResMenu ");
                    sbSql.Append("where ParentNum = '" + roles.ResNum + "'  or ResNum = '"+ roles.ResNum + "';");
                    DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbSql);
                    foreach (DataRow row in dt.Rows) 
                    {
                        StringBuilder sbIsExist = new StringBuilder();
                        sbIsExist.Append("select count(*) from Relation where RoleNum = '"+ roles.RoleNum + "'");
                        sbIsExist.Append(" and ReNum = '" + row["ResNum"] + "';");
                        DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(sbIsExist);
                        if (dtt.Rows[0][0].ToString() == "0") 
                        {
                            // 新增菜单及按钮权限
                            sbStr.Append("Insert Into Relation values (");
                            sbStr.Append("'" + roles.RoleNum + "','" + roles.Guid + "','"+ row["ResNum"] + "',GetDate(),'" + userCode + "');");
                        }
                    }

                    if (!DoesItExist(roles))  // 判断是否需新增功能模块权限
                    {
                        sbStr.Append("Insert Into Relation ");
                        sbStr.Append("select '" + roles.RoleNum + "','" + roles.Guid + "',ParentNum,GetDate(),'" + userCode + "' from ResMenu ");
                        sbStr.Append("where ResNum = '" + roles.ResNum + "';");
                    }
                }

                if (sbStr.Length > 0)
                {
                    int rowNum = DataFactory.SqlDataBase().ExecuteBySql(sbStr);
                    if (rowNum > 0)
                    {
                        bl = true;
                    }
                }
                else 
                {
                    bl = true;
                }

            }

            return bl;
        }

        public bool Add(Roles model)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();

                ht["RoleNum"] = "'" + model.RoleNum + "'";
                ht["RoleName"] = "'" + model.RoleName + "'";
               
                ht["Ord"] = "(select Max(Ord) from Roles) + 1";
                ht["IsDel"] = 0;
                ht["CreatUser"] = "'" + model.CreatUser + "'";
                ht["UpdateTime"] = "GetDate()";
                ht["CreatTime"] = "GetDate()";
                ht["Demo"] = "'" + model.Demo + "'";
                ht["UpdateUser"] = "''";

                int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("Roles", ht);

                if (_ret == 1) result = true;
                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool Update(Roles model)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();

                ht["RoleName"] = string.IsNullOrEmpty(model.RoleName) ? "''" : "'" + model.RoleName + "'";
                ht["RoleNum"] = string.IsNullOrEmpty(model.RoleName) ? "''" : "'" + model.RoleNum + "'";
               
                ht["UpdateTime"] = "GetDate()";
                ht["UpdateUser"] = string.IsNullOrEmpty(model.UpdateUser) ? "''" : "'" + model.UpdateUser + "'";
                ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";

                string RoleNum = "'" + model.RoleNum + "'";

                int _ret = DataFactory.SqlDataBase().UpdateByHashtable("Roles", nameof(model.RoleNum), RoleNum, ht);

                if (_ret == 1) result = true;

                return result;

            }
            catch
            {
                return result;
            }
        }
        public bool IsExist(string name, string value)
        {
            bool result = false;
            try
            {
                string[] para = new string[] { value };
                int dt = DataFactory.SqlDataBase().IsExist("Roles", name, para);
                if (dt > 0) result = true;
                return result;
            }
            catch
            {
                return result;
            }

        }

        public bool IsExist(AjaxRoleInfo model) 
        {
            bool result = false;
            try
            {
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append("select count(id) from Roles");
                sbStr.Append(" where  RoleName  = " + model.RoleName.AddQuotes());
                sbStr.Append(" and RoleNum != " + model.RoleNum.AddQuotes());

                DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
                if (row[0].ToString() != "0")
                {
                    result = true;
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        public DataTable GetDataTable(string[] strWhere)
        {
            DataTable dt = null;
            try
            {
                int index = 0;
                string str = "@RoleNum" + index;
                SqlParam[] param = new SqlParam[strWhere.Length];
                StringBuilder sql = new StringBuilder();
                sql.Append("Select RoleNum as 角色编号, RoleName as 角色名,CreatUser as 创建人,CreatTime as 创建时间,UpdateUser as 更新人,UpdateTime as 更新时间,Demo as 备注 FROM View_GetRoles where RoleNum in (");

                for (int i = 0; i < param.Length - 1; i++)
                {
                    string obj2 = strWhere[i];
                    str = "@RoleNum" + index;
                    sql.Append(str).Append(",");
                    param[index] = new SqlParam(str, obj2);
                    index++;
                }
                str = "@RoleNum" + index;
                sql.Append(str);
                param[index] = new SqlParam(str, strWhere[index]);
                sql.Append(")");

                dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql, param, "");

                return dt;

            }
            catch
            {
                throw new NotImplementedException();
            }

        }
        public bool BatchDelete(string[] RoleNum)
        {
            bool result = false;
            try
            {
                int dt = DataFactory.SqlDataBase().IsExist("Roles", "RoleNum", RoleNum);
                if (dt >= RoleNum.Length)
                {
                    int _ret = 0;
                    foreach (string rolestr in RoleNum) 
                    {
                        int rowCount = DataFactory.SqlDataBase().BatchDeleteData("Roles", "RoleNum", RoleNum);
                        if (rowCount > 0) 
                        {
                            // 删除权限信息，防止沉余数据
                            StringBuilder sbstr = new StringBuilder();
                            sbstr.Append("Delete From Relation where RoleNum = '" + rolestr + "';");
                            DataFactory.SqlDataBase().ExecuteBySql(sbstr);
                        }

                        _ret += rowCount;
                    }

                    if (_ret >= RoleNum.Length) 
                    {
                        result = true;
                    } 
                }
                return result;
            }
            catch
            {
                return result;
            }
        }

        /// <summary>
        /// 验证该部门是否包含员工
        /// </summary>
        /// <param name="DepartNum"></param>
        /// <returns></returns>
        public string IsChecks(string[] ResNum)
        {
            try
            {
                string result = "";
                foreach (string roleNum in ResNum)
                {
                    StringBuilder sql = new StringBuilder();
                    sql.Append("select count(*) from userinfo where roleNum='" + roleNum + "' and isdel = '0';");
                    DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(sql);
                    if (row[0].ToString() != "0")
                    {
                        result += roleNum + ":请先停用所属部门员工。";
                    }
                }

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}