/// <summary>
/// 界面按钮权限
/// </summary>
/// <param name="vm"></param>
/// <returns></returns>
public List <FormRoleMenuAuthoritiesVM> GetFormRoleMenuAuthorities(FormRoleMenuAuthoritiesVM vm)
{
StringBuilder sb = new StringBuilder();
try
{
string sql = @"
select u.ID,u.Account,a.Name AuthorityName,m.FormName from Users u
join UserRoles ur on u.ID=ur.UserID
join RoleMenus rm on ur.RoleID=rm.RoleID
join Menus m on rm.MenuID=m.ID
join RoleMenuAuthorities rma on m.ID=rma.MenuID and rma.RoleID=ur.RoleID
join Authorities a on rma.AuthorityID=a.ID
where u.Account=@Account and m.FormName=@FormName and m.[Status]=1
";
List <SqlParameter> paramList = new List <SqlParameter>();
SqlParameter paramAccount = new SqlParameter("@Account", vm.Account);
SqlParameter paramFormName = new SqlParameter("@FormName", vm.FormName);
paramList.Add(paramAccount);
paramList.Add(paramFormName);
return(DataTableToList.DataSetToList <FormRoleMenuAuthoritiesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramList.ToArray())));
}
catch (Exception ex)
{
return(null);
}
}
public List <MenusVM> GetAllMenus(MenusVM vm)
{
try
{
StringBuilder selectDataCommand = new StringBuilder(@"
SELECT [ID] ,[ParentID] ,[FormName] ,[TabHeaderText]
,[SortCode] ,[Remark]
FROM [RABC].[dbo].[Menus]
where [Status]=1
");
List <SqlParameter> parameters = new List <SqlParameter>();
if (!string.IsNullOrEmpty(vm.FormName))
{
selectDataCommand.Append(" and [FormName] like @FormName");
parameters.Add(new SqlParameter("@FormName", string.Format("%{0}%", vm.FormName)));
}
if (!string.IsNullOrEmpty(vm.TabHeaderText))
{
selectDataCommand.Append(" and [TabHeaderText] like @TabHeaderText");
parameters.Add(new SqlParameter("@TabHeaderText", string.Format("%{0}%", vm.TabHeaderText)));
}
return(DataTableToList.DataSetToList <MenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray())));
}
catch (Exception ex)
{
return(null);
}
}
public List <RoleMenuAuthoritiesVM> GetRoleMenuAuthorities(RoleMenuAuthoritiesVM vm)
{
StringBuilder sb = new StringBuilder();
try
{
StringBuilder sql = new StringBuilder(@"
select rma.ID,m.TabHeaderText MenuName,r.Name RoleName ,a.Name AuthorityName
from [dbo].[RoleMenuAuthorities] rma
join [dbo].[Menus] m on rma.[MenuID]=m.ID
join [dbo].[Roles] r on rma.RoleID=r.ID
join [dbo].[Authorities] a on a.ID=rma.AuthorityID
");
List <SqlParameter> paramList = new List <SqlParameter>();
StringBuilder where = new StringBuilder(" where 1=1 and m.[Status]=1 ");
if (!string.IsNullOrEmpty(vm.MenuName))
{
where.Append(" and m.TabHeaderText like @MenuName");
SqlParameter paramMenuName = new SqlParameter("@MenuName", string.Format("%{0}%", vm.MenuName));
paramList.Add(paramMenuName);
}
if (!string.IsNullOrEmpty(vm.RoleName))
{
where.Append(" and r.Name like @RoleName");
SqlParameter paramRoleName = new SqlParameter("@RoleName", string.Format("%{0}%", vm.RoleName));
paramList.Add(paramRoleName);
}
sql.Append(where);
return(DataTableToList.DataSetToList <RoleMenuAuthoritiesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql.ToString(), paramList.ToArray())));
}
catch (Exception ex)
{
return(null);
}
}
//private static long SqlBulkCopyInsert()
//{
// Stopwatch stopwatch = new Stopwatch();
// stopwatch.Start();
// DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, "select * from FROM [RABC].[dbo].[UserRoles] where ID<0").Tables[0];
// string passportKey;
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// DataRow dataRow = dt.NewRow();
// dataRow[""] = passportKey;
// dataTable.Rows.Add(dataRow);
// }
// SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
// sqlBulkCopy.DestinationTableName = "Passport";
// sqlBulkCopy.BatchSize = dataTable.Rows.Count;
// SqlConnection sqlConnection = new SqlConnection(connectionString);
// sqlConnection.Open();
// if (dataTable != null && dataTable.Rows.Count != 0)
// {
// sqlBulkCopy.WriteToServer(dataTable);
// }
// sqlBulkCopy.Close(); sqlConnection.Close();
// stopwatch.Stop();
// return stopwatch.ElapsedMilliseconds;
//}
public List <UserRolesVM> GetUserRoles(UserRolesVM vm)
{
try
{
// DataPageRequestInfo info = JsonHelper.DeserializeJsonToObject<DataPageRequestInfo>(strData);
// StringBuilder where = new StringBuilder("where 1 = 1");
// var qm = JsonConvert.DeserializeAnonymousType(info.Conditions, new { Name = "" });
//List<SqlParameter> parameters = new List<SqlParameter>();
//if (!string.IsNullOrEmpty(qm.Name))
//{
// SqlParameter param = new SqlParameter("@Name", string.Format("%{0}%", qm.Name));
// where.Append(" and Name like @Name");
// parameters.Add(param);
//}
StringBuilder selectDataCommand = new StringBuilder(@"
SELECT ur.ID ,[UserID] ,[RoleID],r.Name RoleName ,u.Account UserName FROM [RABC].[dbo].[UserRoles] ur
join [dbo].[Users] u on ur.UserID=u.[ID]
join [dbo].[Roles] r on r.ID=ur.RoleID
");
List <SqlParameter> paramList = new List <SqlParameter>();
if (vm.UserID != 0)
{
selectDataCommand.Append(" where u.ID=@UserID");
SqlParameter paramID = new SqlParameter("@UserID", vm.UserID);
paramList.Add(paramID);
}
// selectDataCommand.Append(where.ToString());
return(DataTableToList.DataSetToList <UserRolesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramList.ToArray())));
}
catch (Exception ex)
{
return(null);
}
}
public List <RolesVM> GetUserNotHaveRoles(UserRolesVM vm)
{
try
{
//DataPageRequestInfo info = JsonHelper.DeserializeJsonToObject<DataPageRequestInfo>(strData);
//StringBuilder where = new StringBuilder("where 1 = 1");
//var qm = JsonConvert.DeserializeAnonymousType(info.Conditions, new { Name = "" });
// RolesVM qm = JsonConvert.DeserializeObject<RolesVM>(info.Conditions);
// List<SqlParameter> parameters = new List<SqlParameter>();
StringBuilder selectDataCommand = new StringBuilder(@"
select [ID] ,[Name] ,[CreateTime],[Remark] from [dbo].[Roles] r
where not exists
(
select ur.ID from [dbo].[UserRoles] ur where ur.RoleID=r.ID and ur.UserID=@UserID
)
");
SqlParameter paramUserID = new SqlParameter("@UserID", vm.UserID);
return(DataTableToList.DataSetToList <RolesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramUserID)));
}
catch (Exception ex)
{
return(null);
}
}
public List <RolesVM> GetRoles(RolesVM vm)
{
try
{
// DataPageRequestInfo info = JsonHelper.DeserializeJsonToObject<DataPageRequestInfo>(strData);
StringBuilder where = new StringBuilder("where 1 = 1");
//var qm = JsonConvert.DeserializeAnonymousType(info.Conditions, new { Name = "" });
// RolesVM qm = JsonConvert.DeserializeObject<RolesVM>(info.Conditions);
List <SqlParameter> parameters = new List <SqlParameter>();
if (!string.IsNullOrEmpty(vm.Name))
{
SqlParameter param = new SqlParameter("@Name", string.Format("%{0}%", vm.Name));
where.Append(" and Name like @Name");
parameters.Add(param);
}
StringBuilder selectDataCommand = new StringBuilder(" SELECT [ID],[Name] ,[CreateTime] ,[Remark] FROM [RABC].[dbo].[Roles]");
selectDataCommand.Append(where.ToString());
return(DataTableToList.DataSetToList <RolesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray())));
// retJson = JsonHelper.SerializeObject(list);
}
catch (Exception ex)
{
return(null);
}
}
public UserVM Login(UserVM vm)
{
string retJson = "error";
string sql = @"
SELECT [ID] ,[Account] ,[Password] ,[Status]
,[CreateTime] ,[ModityTime] ,[TimeStamp]
FROM [RABC].[dbo].[Users]
where [Account]=@Account ;
";
SqlParameter paramAccount = new SqlParameter("@Account", vm.Account);
return(DataTableToList.DataSetToList <UserVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramAccount)).FirstOrDefault());
}
public List <MenusVM> GetMenus(RoleMenusVM vm)
{
try
{
// StringBuilder selectDataCommand = new StringBuilder(@"
// SELECT m.[ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark]
// FROM [RABC].[dbo].[Menus] m
// where not exists
// (
// select * from [dbo].[RoleMenus] rm where rm.MenuID=m.ID and rm.RoleID=@RoleID
// )
//");
string sql = @"
select * into #NoParents from (
SELECT m.[ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark],[Status]
FROM [RABC].[dbo].[Menus] m
where not exists
(
select rm.ID from [dbo].[RoleMenus] rm where rm.MenuID=m.ID and rm.RoleID=@RoleID
) and m.[Status]=1
) t
select * into #parents from
(
select [ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark],[Status] from Menus where ID in
(
--查找所有有孩子的父节点
select ParentID from #NoParents
where ParentID<>0
group by ParentID
) and [Status]=1
)t1
select * from #NoParents
union
select * from #parents
drop table #NoParents
drop table #parents
";
SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID);
// selectDataCommand.Append(where.ToString());
return(DataTableToList.DataSetToList <MenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramRoleID)));
}
catch (Exception ex)
{
return(null);
}
}
public List <UserMenusVM> GetUserMenus(string account)
{
string sql = string.Format(@"
select distinct m.ID, m.[ParentID],m.[FormName] ,m.[TabHeaderText] ,m.SortCode from Users u
join UserRoles ur on u.ID=ur.UserID
join RoleMenus rm on ur.RoleID=rm.RoleID
join Menus m on rm.MenuID=m.ID
where u.Account='{0}' and m.[Status]=1
order by m.SortCode
", account);
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql);
var list = DataTableToList.DataSetToList <UserMenusVM>(ds);
return(list);
}
public (int Count, List <UserVM> UsersList) GetUsers(UsersQM qm)
{
try
{
StringBuilder selectCountCommand = new StringBuilder(@" SELECT Count([ID]) FROM [RABC].[dbo].[Users] ");
StringBuilder where = new StringBuilder("where 1 = 1");
List <SqlParameter> parameters = new List <SqlParameter>();
if (!string.IsNullOrEmpty(qm.Account))
{
SqlParameter param = new SqlParameter("@Account", string.Format("%{0}%", qm.Account));
where.Append(" and Account like @Account");
parameters.Add(param);
}
selectCountCommand.Append(where.ToString());
StringBuilder selectDataCommand = new StringBuilder(string.Format(@"
select * from
(
select row_number() over (order by a.ID ) as rowNum, a.*
from (
SELECT TOP ({0})
[ID],[Account] ,[Password] FROM [RABC].[dbo].[Users]
{1}
) as a
)c
where c.rowNum>{2}", qm.Take + qm.Skip, where.ToString(), qm.Skip));
// DataPagingInfo dataPagingInfo = new DataPagingInfo();
var list = DataTableToList.DataSetToList <UserVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray()));
// dataPagingInfo.DataSoure = JsonConvert.SerializeObject(list);
// dataPagingInfo.SearchAllFlg = info.SearchAllFlg;
// dataPagingInfo.PageCount = pageCount;
int count = (int)SqlHelper.ExecuteScalar(SqlHelper.GetConnSting(), CommandType.Text, selectCountCommand.ToString(), parameters.ToArray());;
// retJson = JsonHelper.SerializeObject(dataPagingInfo);
return(count, list);
}
catch (Exception ex)
{
return(0, null);
}
}
public int AddRoleMenus(List <MenusVM> list)
{
StringBuilder sb = new StringBuilder();
int i = 0;
using (SqlConnection con = new SqlConnection(SqlHelper.GetConnSting()))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
StringBuilder sqlSB = new StringBuilder();
try
{
//父节点单独拿出来处理
var parents = list.Where(p => p.ParentID == 0).ToList();
string existsParentIDS = "SELECT distinct [MenuID] FROM [RABC].[dbo].[RoleMenus] where RoleID= @RoleID";
var parentMenuIDS = DataTableToList.DataSetToList <RoleMenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, existsParentIDS, new SqlParameter("@RoleID", list.First().RoleID))).Select(p => p.MenuID).ToList();
var notEixtParent = parents.Where(p => !parentMenuIDS.Contains(p.ID)).ToList();
var dhildren = list.Where(p => p.ParentID != 0).ToList();
dhildren.AddRange(notEixtParent);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tran;
for (int j = 0; j < dhildren.Count; j++)
{
string insertCommand = String.Format(@"INSERT INTO [dbo].[RoleMenus] ([RoleID] ,[MenuID]) VALUES (@RoleID{0} ,@MenuID{0});", j);
cmd.Parameters.AddWithValue(string.Format("@RoleID{0}", j), dhildren[j].RoleID);
cmd.Parameters.AddWithValue(string.Format("@MenuID{0}", j), dhildren[j].ID);
sqlSB.Append(insertCommand);
}
cmd.CommandText = sqlSB.ToString();
i = cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
i = 0;
}
}
return(i);
}
public List <FormRoleMenuAuthoritiesVM> GetParentMenus()
{
StringBuilder sb = new StringBuilder();
try
{
string sql = @"
SELECT [ID] ,[ParentID]
,[FormName],[TabHeaderText]
,[SortCode] ,[Remark],[Status]
FROM [RABC].[dbo].[Menus]
where ParentID=0 and [Status]=1
";
return(DataTableToList.DataSetToList <FormRoleMenuAuthoritiesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql)));
}
catch (Exception ex)
{
return(null);
}
}
public List <RoleMenusVM> GetRoleMenus(RoleMenusVM vm)
{
try
{
StringBuilder selectDataCommand = new StringBuilder(@"
select rm.[ID],rm.[RoleID], rm.[MenuID],r.Name RoleName,m.TabHeaderText MenuName ,m.ParentID from [dbo].[RoleMenus] rm
join [dbo].[Roles] r on rm.RoleID=r.ID
join [dbo].[Menus] m on rm.MenuID =m.ID
");
StringBuilder where = new StringBuilder(" where 1=1 and m.[Status]=1 ");
List <SqlParameter> paramList = new List <SqlParameter>();
if (!string.IsNullOrEmpty(vm.RoleName))
{
where.Append(" and r.Name like @RoleName");
SqlParameter paramRoleName = new SqlParameter("@RoleName", string.Format("%{0}%", vm.RoleName));
paramList.Add(paramRoleName);
}
if (!string.IsNullOrEmpty(vm.MenuName))
{
where.Append(" and m.TabHeaderText like @MenuName");
SqlParameter paramMenuName = new SqlParameter("@MenuName", string.Format("%{0}%", vm.MenuName));
paramList.Add(paramMenuName);
}
if (vm.RoleID != 0)
{
where.Append(" and r.ID = @RoleID");
SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID);
paramList.Add(paramRoleID);
}
selectDataCommand.Append(where.ToString());
return(DataTableToList.DataSetToList <RoleMenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramList.ToArray())));
}
catch (Exception ex)
{
return(null);
}
}
