public System.Data.DataSet GetUserModules(string Account) { SqlParameterProvider p1 = new GZFrameworkDemo.Business.SqlParameterProvider(); p1.AddParameter("@Account", SqlDbType.VarChar, 20, Account); DataSet ds = null; DBServices.DB.ExecuteTransaction(db => { string sql = "SELECT IsSysAdmain FROM dt_MyUser WHERE Account=@Account"; string isAdmin = db.ExecuteScalar <string>(sql, p1); if ("Y".Equals(isAdmin)) { string sql2 = "SELECT * FROM sys_Modules ORDER BY Sort;" + "SELECT *,1073741823 AS UserAuthority FROM sys_ModulesFunction ORDER BY Sort "; ds = dal.DBHelper.GetDataSet(sql2, null); } else { string sql2 = @"SELECT * FROM sys_Modules ORDER BY Sort; SELECT *,0 AS UserAuthority FROM sys_ModulesFunction ORDER BY Sort; SELECT * FROM dt_MyRoleAuthority WHERE RoleID IN( SELECT RoleID FROM dt_MyUserRole WHERE Account = @Account )"; ds = dal.DBHelper.GetDataSet(sql2, p1); //获得总权限, DataTable dtAuthority = ds.Tables[2]; var query = from t in dtAuthority.AsEnumerable() group t by new { FunctionID = t.Field <string>("FunctionID") } into m select new { FunctionID = m.Key.FunctionID, Authority = m.Aggregate(0, (d, n) => { return(d | Common.ConvertLib.ToInt(n["Authority"])); }) }; query.ToList().ForEach(p => { ds.Tables[1].Select($"FunctionID='{p.FunctionID}'").ToList().ForEach(row => { row["UserAuthority"] = p.Authority; }); }); ds.Tables[1].Select("UserAuthority=0").ToList().ForEach(row => { ds.Tables[1].Rows.Remove(row); }); var ModuleNo = from module in ds.Tables[0].AsEnumerable() where !ds.Tables[1].AsEnumerable().Any(y => y.Field <string>(sys_ModulesFunction.ModuleID) == module.Field <string>(sys_Modules.ModuleID)) select new { row = module, ModuleID = module.Field <string>(sys_Modules.ModuleID) }; ModuleNo.ToList().ForEach(r => { ds.Tables[0].Rows.Remove(r.row); }); ds.Tables.RemoveAt(2); ds.AcceptChanges(); } }); ds.Tables[0].TableName = sys_Modules._TableName; ds.Tables[1].TableName = sys_ModulesFunction._TableName; return(ds); }
public System.Data.DataSet GetUserModules(string Account) { SqlParameterProvider p1 = new GZFrameworkDemo.Business.SqlParameterProvider(); p1.AddParameter("@Account", SqlDbType.VarChar, 20, Account); DataSet ds = null; DBServices.DB.ExecuteTransaction(db => { string sql = "SELECT IsSysAdmain FROM dt_MyUser WHERE Account=@Account"; string isAdmin = db.ExecuteScalar <string>(sql, p1); string sqlAllModuleFunction = "SELECT * FROM sys_Modules ORDER BY Sort;" + "SELECT *,0 AS UserAuthority FROM sys_ModulesFunction ORDER BY Sort "; ds = dal.DBHelper.GetDataSet(sqlAllModuleFunction, null); if ("Y".Equals(isAdmin)) { foreach (DataRow dr in ds.Tables[1].Rows) { dr["UserAuthority"] = 1073741823; } return; } else { DataTable dtAuthority = null; string sqlDBAdmin = "SELECT IsDbAdmin FROM dbo.dt_MyUserDBs WHERE Account=@Account AND DBCode=@DBCode"; SqlParameterProvider pDBAdmin = new SqlParameterProvider(); pDBAdmin.AddParameter("@Account", SqlDbType.VarChar, 20, Account); pDBAdmin.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode); string isDBAdmin = db.ExecuteScalar <string>(sqlDBAdmin, pDBAdmin); if ("Y".Equals(isDBAdmin)) { string sql2 = "SELECT FunctionID,Authority FROM dbo.sys_DataBaseListAuthority where DBCode=@DBCode"; SqlParameterProvider p3 = new SqlParameterProvider(); p3.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode); dtAuthority = db.GetTable(sql2, "tmp", p3); } else { string sql2 = @"SELECT B.FunctionID, A.Authority & B.Authority AS Authority FROM ( SELECT FunctionID, Authority FROM dt_MyRoleAuthority WHERE RoleID IN ( SELECT RoleID FROM dt_MyUserRole WHERE Account = @Account ) ) AS A INNER JOIN ( SELECT FunctionID, Authority FROM dbo.sys_DataBaseListAuthority WHERE DBCode = @DBCode ) AS B ON A.FunctionID = B.FunctionID;"; SqlParameterProvider p3 = new SqlParameterProvider(); p3.AddParameter("@Account", SqlDbType.VarChar, 20, Account); p3.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode); dtAuthority = db.GetTable(sql2, "tmp", p3); } //获得总权限, //DataTable dtAuthority = ds.Tables[2]; var query = (from t in dtAuthority.AsEnumerable() group t by new { FunctionID = t.Field <string>("FunctionID") } into m select new { FunctionID = m.Key.FunctionID, Authority = m.Aggregate(0, (d, n) => { return(d | Common.ConvertLib.ToInt(n["Authority"])); }) }).ToList(); query.ToList().ForEach(p => { ds.Tables[1].Select($"FunctionID='{p.FunctionID}'").ToList().ForEach(row => { row["UserAuthority"] = p.Authority; }); }); ds.Tables[1].Select("UserAuthority=0").ToList().ForEach(row => { ds.Tables[1].Rows.Remove(row); }); var ModuleNo = from module in ds.Tables[0].AsEnumerable() where !ds.Tables[1].AsEnumerable().Any(y => y.Field <string>(sys_ModulesFunction.ModuleID) == module.Field <string>(sys_Modules.ModuleID)) select new { row = module, ModuleID = module.Field <string>(sys_Modules.ModuleID) }; ModuleNo.ToList().ForEach(r => { ds.Tables[0].Rows.Remove(r.row); }); //ds.Tables.RemoveAt(2); ds.AcceptChanges(); } }); ds.Tables[0].TableName = sys_Modules._TableName; ds.Tables[1].TableName = sys_ModulesFunction._TableName; return(ds); }