public bool AddDefaultCategoryAndInheritancePrivilages(int CategoryId, int ParentCategoryId = -1)
{
try
{
int userid = UserManager.defaultInstance.UserInfo.UserID;
DataSources.dsDataCenter.CategoryRoleDataTable CatRole = new DataSources.dsDataCenter.CategoryRoleDataTable();
DataSources.dsDataCenter.CategoryUserDataTable CatUser = new DataSources.dsDataCenter.CategoryUserDataTable();
adpCategoryRole.FillByInheritances(CatRole, ParentCategoryId);
adpCategoryUser.FillByInheritances(CatUser, ParentCategoryId);
//add parent roles
foreach (DataSources.dsDataCenter.CategoryRoleRow item in CatRole)
{
item.CategoryId = CategoryId;
item.AcceptChanges();
item.SetAdded();
}
adpCategoryRole.Update(CatRole);
//add parent users
foreach (DataSources.dsDataCenter.CategoryUserRow item in CatUser)
{
item.CategoryId = CategoryId;
item.AcceptChanges();
item.SetAdded();
}
adpCategoryUser.Update(CatUser);
//add me as default user
if ((int)adpCategoryUser.ScalarQueryExistsUserID_CategoryId(userid, CategoryId) == 0)
{
DataSources.dsDataCenter.CategoryUserRow row = CatUser.NewCategoryUserRow();
row.UserID = userid; row.CategoryId = CategoryId;
row.CanRead = row.CanWrite = row.CanInsert = row.CanDelete = true;
row.UserLevelId = (int)nsLib.Utilities.Types.UserLevelType.Administrator;
row.GrantId = (int)nsLib.Utilities.Types.GrantType.allow;
row.Inheritance = false;
adpCategoryUser.Insert(row.UserID, row.CategoryId, row.CanRead, row.CanWrite, row.CanInsert,
row.CanDelete, row.Inheritance, null, row.UserLevelId, row.GrantId);
}
DataCenterX.LogMessage("Add Default Category And InheritancePrivilages ... Done", typeof(DataManager), nsLib.Utilities.Types.MessageType.Debug);
return true;
}
catch (SqlException ex)
{
DataCenterX.LogMessage("Add Default Category And InheritancePrivilages ... Error", typeof(DataManager), nsLib.Utilities.Types.MessageType.Error, ex);
return false;
}
}
public bool MoveCategoryInheritancePrivilages(int CategoryId, int NewParentId)
{
SqlConnection con = new SqlConnection(Properties.Settings.Default.DataCenterConnectionString);
SqlDataAdapter adp = new SqlDataAdapter() { SelectCommand = new SqlCommand("", con) };
SqlCommand cmd = new SqlCommand("", con);
con.Open();
//All Sub Category
DataSources.dsDataCenter.CategoryDataTable Categories = new DataSources.dsDataCenter.CategoryDataTable();
adp.SelectCommand.CommandText = string.Format(@";WITH ret (CategoryId, ParentID, CategoryName) AS(
SELECT CategoryId, ParentID, '' FROM Category WHERE CategoryId = {0}
UNION ALL
SELECT t.CategoryId, t.ParentID, '' FROM Category t INNER JOIN ret r ON t.ParentID = r.CategoryId )
select * from ret", CategoryId);
adp.Fill(Categories);
//All Role Should Delete
DataSources.dsDataCenter.CategoryRoleDataTable ROLES = new DataSources.dsDataCenter.CategoryRoleDataTable();
adp.SelectCommand.CommandText = string.Format(@"Select * FROM CategoryRole WHERE CategoryId = {0} AND Inheritance = 1 AND InheritanceCategoryId <> CategoryId", CategoryId);
adp.Fill(ROLES);
//All User Should Delete
DataSources.dsDataCenter.CategoryUserDataTable USERS = new DataSources.dsDataCenter.CategoryUserDataTable();
adp.SelectCommand.CommandText = string.Format(@"SELECT * FROM CategoryUser WHERE CategoryId = {0} AND Inheritance = 1 AND InheritanceCategoryId <> CategoryId", CategoryId);
adp.Fill(USERS);
foreach (DataSources.dsDataCenter.CategoryRow cat in Categories)
{
foreach (DataSources.dsDataCenter.CategoryRoleRow catRole in ROLES)
{
cmd.CommandText = string.Format(@"DELETE FROM CategoryRole WHERE CategoryId = {0} AND RoleID = {1} AND GrantId = {2}", cat.CategoryId, catRole.RoleID, catRole.GrantId);
cmd.ExecuteNonQuery();
}
foreach (DataSources.dsDataCenter.CategoryUserRow catUser in USERS)
{
cmd.CommandText = string.Format(@"DELETE FROM CategoryUser WHERE CategoryId = {0} AND UserID = {1} AND GrantId = {2}", cat.CategoryId, catUser.UserID, catUser.GrantId);
cmd.ExecuteNonQuery();
}
}
//All Role Should Add
adp.SelectCommand.CommandText = string.Format(@"Select * FROM CategoryRole WHERE CategoryId = {0} AND Inheritance = 1", NewParentId);
ROLES.Clear();
adp.Fill(ROLES);
//All User Should Add
adp.SelectCommand.CommandText = string.Format(@"SELECT * FROM CategoryUser WHERE CategoryId = {0} AND Inheritance = 1", NewParentId);
USERS.Clear();
adp.Fill(USERS);
cmd.CommandText = @"if exists(select * from CategoryRole where CategoryId = @CategoryId and RoleID = @RoleID and GrantId = @GrantId)
begin
UPDATE CategoryRole SET CanRead = @CanRead, CanWrite = @CanWrite, CanInsert = @CanInsert, CanDelete = @CanDelete, Inheritance = @Inheritance,
InheritanceCategoryId = @InheritanceCategoryId, UserLevelId = @UserLevelId
WHERE CategoryId = @CategoryId and RoleID = @RoleID and GrantId = @GrantId
end
else
begin
INSERT INTO CategoryRole (CategoryId, RoleID, CanRead, CanWrite, CanInsert, CanDelete, Inheritance, InheritanceCategoryId, UserLevelId, GrantId)
VALUES (@CategoryId, @RoleID, @CanRead, @CanWrite, @CanInsert, @CanDelete, @Inheritance, @InheritanceCategoryId, @UserLevelId, @GrantId)
end";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@CategoryId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@CanRead", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@CanWrite", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@CanInsert", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@CanDelete", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@Inheritance", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@InheritanceCategoryId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@UserLevelId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@GrantId", SqlDbType.Int));
foreach (DataSources.dsDataCenter.CategoryRow cat in Categories)
{
foreach (DataSources.dsDataCenter.CategoryRoleRow catRole in ROLES)
{
cmd.Parameters["@CategoryId"].Value = cat.CategoryId;
cmd.Parameters["@RoleID"].Value = catRole.RoleID;
cmd.Parameters["@CanRead"].Value = catRole.CanRead;
cmd.Parameters["@CanWrite"].Value = catRole.CanWrite;
cmd.Parameters["@CanInsert"].Value = catRole.CanInsert;
cmd.Parameters["@CanDelete"].Value = catRole.CanDelete;
cmd.Parameters["@Inheritance"].Value = catRole.Inheritance;
cmd.Parameters["@InheritanceCategoryId"].Value = catRole.InheritanceCategoryId;
cmd.Parameters["@UserLevelId"].Value = catRole.UserLevelId;
cmd.Parameters["@GrantId"].Value = catRole.GrantId;
cmd.ExecuteNonQuery();
}
}
cmd.CommandText = @"if exists(select * from CategoryUser where CategoryId = @CategoryId and UserID = @UserID and GrantId = @GrantId)
begin
UPDATE CategoryUser SET CanRead = @CanRead, CanWrite = @CanWrite, CanInsert = @CanInsert, CanDelete = @CanDelete, Inheritance = @Inheritance,
InheritanceCategoryId = @InheritanceCategoryId, UserLevelId = @UserLevelId
WHERE CategoryId = @CategoryId and UserID = @UserID and GrantId = @GrantId
end
else
begin
INSERT INTO CategoryUser (CategoryId, UserID, CanRead, CanWrite, CanInsert, CanDelete, Inheritance, InheritanceCategoryId, UserLevelId, GrantId)
VALUES (@CategoryId, @UserID, @CanRead, @CanWrite, @CanInsert, @CanDelete, @Inheritance, @InheritanceCategoryId, @UserLevelId, @GrantId)
end";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@CategoryId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@CanRead", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@CanWrite", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@CanInsert", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@CanDelete", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@Inheritance", SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@InheritanceCategoryId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@UserLevelId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@GrantId", SqlDbType.Int));
foreach (DataSources.dsDataCenter.CategoryRow cat in Categories)
{
foreach (DataSources.dsDataCenter.CategoryUserRow catUser in USERS)
{
cmd.Parameters["@CategoryId"].Value = cat.CategoryId;
cmd.Parameters["@UserID"].Value = catUser.UserID;
cmd.Parameters["@CanRead"].Value = catUser.CanRead;
cmd.Parameters["@CanWrite"].Value = catUser.CanWrite;
cmd.Parameters["@CanInsert"].Value = catUser.CanInsert;
cmd.Parameters["@CanDelete"].Value = catUser.CanDelete;
cmd.Parameters["@Inheritance"].Value = catUser.Inheritance;
cmd.Parameters["@InheritanceCategoryId"].Value = catUser.InheritanceCategoryId;
cmd.Parameters["@UserLevelId"].Value = catUser.UserLevelId;
cmd.Parameters["@GrantId"].Value = catUser.GrantId;
cmd.ExecuteNonQuery();
}
}
con.Close(); con.Dispose(); cmd.Dispose(); adp.Dispose();
return true;
}
