public RoleCollection GetRolesNotBelongTo()
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
RoleCollection result = new RoleCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = database.GetCommand(connection);
#region SubDomainId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@SubDomainId", _id);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT
a.[RoleId],
[RoleName],
[RoleDescription]
FROM
dbo.[Role] a
inner join SubDomainInRole b on a.RoleId = b.RoleId
WHERE
SubDomainId!=@SubDomainId" ;
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Role item = new Role(reader);
result.Add(item);
}
}
return(result);
}
public ArticleCollection GetArticlesBelongTo()
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
ArticleCollection result = new ArticleCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = database.GetCommand(connection);
#region SubDomainId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@SubDomainId", _id);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT
a.[ArticleId],
[ArticleName],
[ArticleTitle],
b.[IsCheck],
s.[Name] as SubDomainFromName
FROM
dbo.[Article] a
inner join SubDomainInArticle b on a.ArticleId = b.ArticleNewId
inner join SubDomain s on b.SubDomainFromId = s.Id
WHERE
b.SubDomainToId = @SubDomainId AND b.IsCheck = 'false'" ;
command.CommandType = System.Data.CommandType.Text;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Article item = new Article();
if (!reader.IsDBNull(reader.GetOrdinal("ArticleId")))
{
item.Id = new Guid(reader["ArticleId"].ToString());
}
if (!reader.IsDBNull(reader.GetOrdinal("ArticleName")))
{
item.Name = (string)reader["ArticleName"].ToString();
}
if (!reader.IsDBNull(reader.GetOrdinal("ArticleTitle")))
{
item.Title = (string)reader["ArticleTitle"].ToString();
}
if (!reader.IsDBNull(reader.GetOrdinal("SubDomainFromName")))
{
item.SubDomainFromName = (string)reader["SubDomainFromName"];
}
if (!reader.IsDBNull(reader.GetOrdinal("IsCheck")))
{
item.IsCheck = (bool)reader["IsCheck"];
}
result.Add(item);
}
}
return(result);
}
protected DbCommand GetUsersNotInByRoleIDCommand(PSCPortal.Framework.Database database, Guid roleID)
{
DbCommand command = database.GetCommand();
DbParameter prRoleID = database.GetParameter(System.Data.DbType.Guid, "@RoleID", roleID);
command.Parameters.Add(prRoleID);
command.CommandText = "User_GetUsersNotInRole";
command.CommandType = System.Data.CommandType.StoredProcedure;
return(command);
}
protected DbCommand GetRolesByUserIDCommand(PSCPortal.Framework.Database database, Guid userID)
{
DbCommand command = database.GetCommand();
DbParameter prUserID = database.GetParameter(System.Data.DbType.Guid, "@UserID", userID);
command.Parameters.Add(prUserID);
command.CommandText = "UserInRole_FilterByEqualingUserId";
command.CommandType = System.Data.CommandType.StoredProcedure;
return(command);
}
protected DbCommand GetRolesNotBelongToCommand(PSCPortal.Framework.Database database)
{
DbCommand command = database.GetCommand();
#region UserId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@UserId", _id);
command.Parameters.Add(prId);
#endregion
command.CommandText = "Role_GetRolesNotBelongToUser";
command.CommandType = System.Data.CommandType.StoredProcedure;
return(command);
}
public UserCollection GetUsersNotInRole()
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
UserCollection result = new UserCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = GetUsersNotInByRoleIDCommand(database, this._id);
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User item = new User(reader);
result.Add(item);
}
}
return(result);
}
public static UserInRoleCollection GetRolesByUserID(Guid userID)
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
UserInRoleCollection result = new UserInRoleCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = result.GetRolesByUserIDCommand(database, userID);
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
UserInRole item = new UserInRole(reader);
result.Add(item);
}
}
return(result);
}
public RoleCollection GetRolesNotBelongTo()
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
RoleCollection result = new RoleCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = GetRolesNotBelongToCommand(database);
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Role item = new Role(reader);
result.Add(item);
}
}
return(result);
}
public static SubDomainCollection GetSubDomainByUser(User user)
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
SubDomainCollection result = new SubDomainCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = database.GetCommand(connection);
#region RoleId
DbParameter prId = database.GetParameter(System.Data.DbType.String, "@UserName", user.Name);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT b.* FROM UserInSubDomain
a LEFT JOIN SubDomain b
ON a.SubDomainId = b.Id
INNER JOIN [User] c
ON a.UserId = c.UserId
WHERE c.UserName = @UserName and b.IsDelete = 0";//Ngọc - 18122015
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
SubDomain item = new SubDomain();
if (!reader.IsDBNull(reader.GetOrdinal("Id")))
{
item.Id = new Guid(reader["Id"].ToString());
}
if (!reader.IsDBNull(reader.GetOrdinal("Name")))
{
item.Name = reader["Name"].ToString();
}
if (!reader.IsDBNull(reader.GetOrdinal("OldVisitors")))
{
item.OldVisitors = (int)reader["OldVisitors"];
}
result.Add(item);
}
}
return(result);
}
protected DbCommand GetTopicBelongToCommand(PSCPortal.Framework.Database database)
{
DbCommand command = database.GetCommand();
#region SubDomainId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@SubDomainId", _id);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT
a.[TopicId]
FROM
dbo.[Topic] a
inner join SubDomainInTopic b on a.TopicId = b.TopicId
WHERE
SubDomainId=@SubDomainId" ;
command.CommandType = System.Data.CommandType.Text;
return(command);
}
public Topic GetTopic()
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
Topic result = new Topic();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = GetTopicBelongToCommand(database);
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
result = new Topic {
Id = new Guid(reader["TopicId"].ToString())
};
}
}
return(result);
}
public static SubDomainCollection GetSubDomainCollection(Role role)
{
PSCPortal.Framework.Database database = new PSCPortal.Framework.Database("PSCPortalConnectionString");
SubDomainCollection result = new SubDomainCollection();
using (DbConnection connection = database.GetConnection())
{
DbCommand command = database.GetCommand(connection);
#region RoleId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@RoleId", role.Id);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT
a.[Id],
[Name]
FROM
dbo.[SubDomain] a
inner join SubDomainInRole b on a.Id = b.SubDomainId
WHERE
RoleId=@RoleId and a.IsDelete = 0" ;//Ngọc - 18122015
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
SubDomain item = new SubDomain();
if (!reader.IsDBNull(reader.GetOrdinal("Id")))
{
item.Id = new Guid(reader["Id"].ToString());
}
if (!reader.IsDBNull(reader.GetOrdinal("Name")))
{
item.Name = reader["Name"].ToString();
}
result.Add(item);
}
}
return(result);
}
protected DbCommand GetMenuMastersBelongToCommand(PSCPortal.Framework.Database database)
{
DbCommand command = database.GetCommand();
#region SubDomainId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@SubDomainId", _id);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT
a.[MenuMasterId],
[MenuMasterName],
[MenuMasterDescription]
FROM
dbo.[MenuMaster] a
inner join SubDomainInMenuMaster b on a.MenuMasterId = b.MenuMasterId
WHERE
SubDomainId=@SubDomainId" ;
command.CommandType = System.Data.CommandType.Text;
return(command);
}
protected DbCommand GetPagesBelongToCommand(PSCPortal.Framework.Database database)
{
DbCommand command = database.GetCommand();
#region SubDomainId
DbParameter prId = database.GetParameter(System.Data.DbType.Guid, "@SubDomainId", _id);
command.Parameters.Add(prId);
#endregion
command.CommandText = @"SELECT
a.[PageId],
[PageName],
[PageTitle],
[PageTemplate],
[PageLanguage]
FROM
dbo.[Page] a
inner join SubDomainInPage b on a.PageId = b.PageId
WHERE
SubDomainId=@SubDomainId" ;
command.CommandType = System.Data.CommandType.Text;
return(command);
}
