public bool Insert(Category category)
{
try
{
return(_category.Insert(category));
}
catch (Exception exception)
{
throw new Exception("Insert Faild...\nPlease try again.");
}
}
public IActionResult Add(int pid, string caname)
{
caname = Tool.GetSafeSQL(caname);
string pnum = "0";
if (pid != 0)
{
Category pca = dal.GetModel(pid);
if (pca != null)
{
if (pca.ParNum != "0")
{
return(Json(new { status = "n", info = "Can only have 2 tier of category!" }));
}
pnum = pca.Num;
if (dal.CalcCount($"parnum='{pca.Num}' and caname='{caname}'") > 0)
{
return(Json(new { status = "n", info = "Cannot have same name with other category!" }));
}
}
}
else
{
if (dal.CalcCount($"parnum='0' and caname='{caname}'") > 0)
{
return(Json(new { status = "n", info = "Cannot have same name with other category!" }));
}
}
string num = dal.GenBH(pnum, 2);
dal.Insert(new Model.Category()
{
CaName = caname, ParNum = pnum, Num = num,
});
return(Json(new { status = "y", info = "Create new Category success!" }));
}
public void ChooseActionForCategories()
{
CategoryDal categoryDal = new CategoryDal();
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\n");
Console.WriteLine("What action you want to choose?\n" +
"1. View all categories\n" +
"2. Get category by Id\n" +
"3. Get category by name of column\n" +
"4. Add category\n" +
"5. Edit info about category\n" +
"6. Delete category by Id\n" +
"7. Delete category by name of column\n" +
"8. Back to start menu");
Console.Write("\nYour selection: ");
string ch = Console.ReadLine();
switch (ch)
{
case "1":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\n");
categoryDal.PrintListOfCategories(categoryDal.GetAll());
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "2":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\nId: ");
int id = Convert.ToInt32(Console.ReadLine());
categoryDal.PrintCategory(categoryDal.GetById(id));
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "3":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\n");
Console.WriteLine("Name of column: ");
string fieldName = Console.ReadLine();
Console.WriteLine("\nValue: ");
string text = Console.ReadLine();
categoryDal.PrintListOfCategories(categoryDal.GetByFieldName(fieldName, text));
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "4":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\n");
Console.WriteLine("NAME: ");
string name = Console.ReadLine();
Console.WriteLine("DESCRIPTION: ");
string desc = Console.ReadLine();
Category category = new Category(name, desc);
categoryDal.Insert(category);
Console.WriteLine("Category succesfully inserted :)");
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "5":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\n");
Console.WriteLine("Name of column (set): ");
string fieldName = Console.ReadLine();
Console.WriteLine("Value (set): ");
string text = Console.ReadLine();
Console.WriteLine("Name of column (condition): ");
string fieldCondition = Console.ReadLine();
Console.WriteLine("Value (condition): ");
string textCondition = Console.ReadLine();
categoryDal.UpdateByFieldName(fieldName, text, fieldCondition, textCondition);
Console.WriteLine("Category succesfully updated :)");
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "6":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\nId: ");
int id = Convert.ToInt32(Console.ReadLine());
categoryDal.DeleteById(id);
Console.WriteLine("Category succesfully deleted :)");
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "7":
{
try
{
Console.Clear();
Console.WriteLine("TABLE: CATEGORIES\n\n ");
Console.WriteLine("Name of column (condition): ");
string fieldCondition = Console.ReadLine();
Console.WriteLine("Value (condition): ");
string textCondition = Console.ReadLine();
categoryDal.DeleteByFieldName(fieldCondition, textCondition);
Console.WriteLine("Category succesfully deleted :)");
Console.ReadKey();
break;
}
finally
{
Menu menu = new Menu();
menu.ChooseActionForCategories();
}
}
case "8":
{
Menu menu = new Menu();
menu.ChooseTable();
break;
}
default:
Console.WriteLine("Invalid selection. Please select 1, 2, 3, 4, 5, 6, 7 or 8.");
break;
}
}
public int SaveCategory(ICategory category)
{
ICategory parentCategory = category.Parent;
//* 树状结构,只有一个根节点(默认Left为1)
int parentLft = 1;
int siteId = category.Site.Id;
if (parentCategory != null)
{
parentLft = parentCategory.Lft;
}
if (category.Id <= 0)
{
#region SQL
/*
* 添加同一层次的节点的方法如下：
*
* Sql代码
* LOCK TABLE nested_category WRITE;
* SELECT @myRight := rgt FROM nested_category
* WHERE name = 'Cherry';
* UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
* UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
* INSERT INTO nested_category(name, lft, rgt) VALUES('Strawberry', @myRight + 1, @myRight + 2);
* UNLOCK TABLES;
*
* 添加树的子节点的方法如下：
*
* Sql代码
* LOCK TABLE nested_category WRITE;
* SELECT @myLeft := lft FROM nested_category
* WHERE name = 'Beef';
* UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
* UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
* INSERT INTO nested_category(name, lft, rgt) VALUES('charqui', @myLeft + 1, @myLeft + 2);
* UNLOCK TABLES;
*
*
* 每次插入节点之后都可以用以下SQL进行查看验证：
* SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
* FROM nested_category AS node,
* nested_category AS parent
* WHERE node.lft BETWEEN parent.lft AND parent.rgt
* GROUP BY node.name
* ORDER BY node.lft;
*
*/
#endregion
/*
* //添加树的子节点方法
* string parentName = category.Parent.Name;
* if (String.IsNullOrEmpty(parentName))
* {
* parentName = this.GetCategories()[0].Name;
* }*/
//更新
categoryDal.UpdateInsertLftRgt(siteId, parentLft);
int categoryId = this.GetNewCategoryId(siteId);
category.Id = categoryDal.Insert(siteId,
categoryId,
parentLft + 1, parentLft + 2,
category.Name,
category.Tag,
category.Icon,
category.PageTitle,
category.Keywords,
category.Description,
category.Location,
category.SortNumber
);
}
else
{
#region 算法
/*
* refence url: http://wizardmin.com/2012/08/left-right-code-tree/
*
* 移动操作
* 主要是改变父节点和同层节点调换操作
* 移动操作基本基于一个公式：任何树所占的数字数目 = 根的右值 – 根的左值 + 1。
* 1.改变父节点
* 先看下移动PHP到客户端，即PHP的父节点换成客户端，这种情况下树节点上的变化
* 以PHP为根的树（虽然只有一个节点）的节点值变化是以新父节点的原右值为依据，
* 如上图，新父节点的原右值为6，那PHP新的左值就是6，这样重新遍历PHP为根的树，
* 就相当于这棵子树上的每个节点更新为：原右值 – (原左值 – 新左值)，
*
* 所以PHP的右值就等于7 = 9 – (8 – 6)。
*
* 而除了PHP为根的树，其他节点更新的有一定范围，范围就是新父节点的右值和
* PHP原先的左值（或旧父节点的左值），因为PHP是向前移，范围内的节点值是往后移，
*
* 需要加上PHP树的所占的数字数目，见上面公式，这里可以总结一个定律：
* 新父节点_right <= ([left, right] + (移动_right – 移动_left + 1)) < 移动_left
*
* 再看下PHP向后移的情况，与前移类似，也是更新一定范围的节点，变成减定律：
* 移动_right < ([left, right] – (移动_right – 移动_left + 1)) < 新父节点_left
*
*/
#endregion
bool leftRightChanged = false;
//ICategory oldCategory = this.GetCategoryById(category.Site.ID, category.ID);
//Category newCategory = null;
//更新
categoryDal.Update(category.Id, siteId,
category.Name,
category.Tag,
category.Icon,
category.PageTitle,
category.Keywords,
category.Description,
category.Location,
category.SortNumber);
#region 修改父类(暂时没有涉及到,需要从DB中获取旧数据来判断是否更改了父类
if (parentLft > 0)
{
//获取父类
/* SELECT TOP 1 * FROM 'tree' WHERE lft<@lft AND rgt>@rgt ORDER BY lft DESC */
/*
* leftRightChanged = parentCategory.Lft != parentLft;
*
*
*
*
* //维护左右值
* if (leftRightChanged)
* {
* int lft,
* rgt,
* parentRgt;
*
* //要移动到的新类
* newCategory = this.Get(a => a.Lft == parentLft);
*
* //原左值
* lft = entity.Lft;
* rgt = entity.Rgt;
* parentRgt = newCategory.Rgt;
*
*
#region 左右值语句
*
* //栏目的左值为新父节点的右值
* // entity.Lft = newCategory.Rgt;
*
* //栏目的右值为原右值-(原左值-新左值)
* // entity.Rgt = entity.Rgt - (orgialLft - entity.Lft);
*
*
*/
/*
* 其子节点的数目为$count = ($right_node - $left_node -1 )/2 , 节点A左值为$A_left_node ,
* UPDATE `tree` SET `left_node`=`left_node`-$right_node-$left_node-1 WHERE `left_node`>$right_node
* AND `left_node`<=$A_left_node
*
* UPDATE `tree` SET `right_node`=`right_node`-$right_node-$left_node-1 WHERE `right_node`>$right_node
* AND `right_node`<$A_left_node
*
* UPDATE `tree` SET `left_node`=`left_node`+$A_left_node-$right_node ,
* `right_node`=`right_node`+$A_left_node-$right_node WHERE `left_node`>=$left_node
* AND `right_node`<=$right_node
*
*/
/*
* public string Category_ChangeUpdateTreeLeft =
* "UPDATE $PREFIX_category SET lft=lft-@rgt-@lft-1 WHERE lft>@rgt AND lft<=@tolft";
*
* public string Category_ChangeUpdateTreeRight=
* "UPDATE $PREFIX_category SET rgt=rgt-@rgt-@lft-1 WHERE rgt>@rgt AND rgt<@tolft";
*
* public string Category_ChangeUpdateTreeBettown =
* "UPDATE $PREFIX_category SET lft=lft+@tolft-@rgt,
* rgt=rgt+@tolft-@rgt WHERE lft>=@lft AND rgt<=@rgt"
*/
/*
* Array ( [lft] => 5 [rgt] => 10 [width] => 6 )
* Array2 ( [id] => 5 [lft] => 2 [rgt] => 17 [width] => 16 )
* Array ( [id] => 5 [lft] => 2 [rgt] => 17 [width] => 16 )
*
*
* mysql_query("UPDATE entryCategory SET rgt = rgt + %d - %d, lft = lft + %d - %d
* WHERE rgt <= %d and lft >= %d;",$row2["rgt"],$row["lft"],
* $row2["rgt"],$row["lft"],$row["rgt"],$row["lft"]);
*
*
* mysql_query("UPDATE entryCategory SET rgt = rgt + %d
* WHERE id=%d;",$row["width"],$row2["id"]);
*
* mysql_query("UPDATE entryCategory SET rgt = rgt - %d,
* lft = lft - %d WHERE rgt > %d and lft > %d;",
* $row["width"],$row["width"],$row["rgt"],$row["rgt"]);
*
*/
/*
* this.dal.UpdateMoveLftRgt(parentLft, entity.Lft, entity.Rgt);
*/
/*
* Array ( [lft] => 17 [rgt] => 22 [width] => 6 )
* Array2 ( [id] => 5 [lft] => 3 [rgt] => 18 [width] => 16 )
* Array ( [id] => 5 [lft] => 2 [rgt] => 23 [width] => 16 )
*
*/
/*
* var _dal = new CategoryDAL();
* int width = rgt - lft + 1;
*
* _dal.db.ExecuteNonQuery("UPDATE O_Categories SET rgt = rgt + " + (parentRgt - lft)
+ ", lft = lft +" + (parentRgt - lft) + " WHERE rgt <= " + rgt + " and lft >= " + lft);
+
+ _dal.db.ExecuteNonQuery("UPDATE O_Categories SET rgt = rgt + " + width
+ " WHERE id=" + newCategory.ID);
+
+
+ _dal.db.ExecuteNonQuery("UPDATE O_Categories SET rgt = rgt - " + width
+ ", lft = lft -" + (width) + " WHERE rgt > " + rgt + " and lft > " + rgt);
*/
/*
* if (parentRgt > rgt)
* {
* categoryDal.UpdateMoveLftRgt(parentRgt, entity.Lft, entity.Rgt);
* }
* else
* {
* categoryDal.UpdateMoveLftRgt2(parentRgt, entity.Lft, entity.Rgt);
* }
*
*
* }*/
#endregion
#region 修改父类
/*
* goto planover;
*
*
* plan2:
*
* //维护左右值
* if (leftRightChanged)
* {
* newCategory = this.Get(a => a.Lft == parentLft);
*
* //原左值
* int orgialLft = entity.Lft;
*
* //栏目的左值为新父节点的右值
* entity.Lft = newCategory.Rgt;
*
* //栏目的右值为原右值-(原左值-新左值)
* entity.Rgt = entity.Rgt - (orgialLft - entity.Lft);
*
* /*
* 其子节点的数目为$count = ($right_node - $left_node -1 )/2 , 节点A左值为$A_left_node ,
* UPDATE `tree` SET `left_node`=`left_node`-$right_node-$left_node-1 WHERE `left_node`>$right_node
* AND `left_node`<=$A_left_node
*
* UPDATE `tree` SET `right_node`=`right_node`-$right_node-$left_node-1 WHERE `right_node`>$right_node
* AND `right_node`<$A_left_node
*
* UPDATE `tree` SET `left_node`=`left_node`+$A_left_node-$right_node ,
* `right_node`=`right_node`+$A_left_node-$right_node WHERE `left_node`>=$left_node
* AND `right_node`<=$right_node
*
*/
/*
* public string Category_ChangeUpdateTreeLeft =
* "UPDATE $PREFIX_category SET lft=lft-@rgt-@lft-1 WHERE lft>@rgt AND lft<=@tolft";
*
* public string Category_ChangeUpdateTreeRight=
* "UPDATE $PREFIX_category SET rgt=rgt-@rgt-@lft-1 WHERE rgt>@rgt AND rgt<@tolft";
*
* public string Category_ChangeUpdateTreeBettown =
* "UPDATE $PREFIX_category SET lft=lft+@tolft-@rgt,
* rgt=rgt+@tolft-@rgt WHERE lft>=@lft AND rgt<=@rgt"
*/
//this.dal.ChangeUpdateTreeLeft(parentLft, entity.Lft, entity.Rgt);
//this.dal.ChangeUpdateTreeRight(parentLft, entity.Lft, entity.Rgt);
//this.dal.ChangeUpdateTreeBettown(parentLft, entity.Lft, entity.Rgt);
// }
//更新
//this.dal.Update(entity.ID, entity.Lft, entity.Rgt, entity.ModuleID, entity.Name, entity.Tag, entity.Keywords, entity.Description, entity.SortNumber);
//清除缓存
//planover:
#endregion
}
}
RepositoryDataCache._categories = null;
return(category.Id);
}