/// <summary>
/// 移除某一个类型(关键字，一级类型，二级类型)
/// </summary>
/// <param name="rmId">移除项目的ID</param>
/// <param name="queryString">移除项目的类型（keyword,firstLevel,secondLevel）</param>
/// <returns>是否移除成功</returns>
public static bool removeItem(int rmId, string queryString)
{
try
{
using (var db = new PaperDbEntities())
{
switch (queryString)
{
case "keyword":
KeyWords keyword = db.KeyWords.Single(a => a.id == rmId);
db.KeyWords.Remove(keyword);
break;
case "firstLevel":
Category category = db.Category.Single(a => a.id == rmId);
db.Category.Remove(category);
break;
case "secondLevel":
Option option = db.Option.Single(a => a.id == rmId);
db.Option.Remove(option);
break;
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 添加各种选项
/// </summary>
/// <param name="articleId">文章ID</param>
/// <param name="ops">一级选项名字，二级选项名字</param>
/// <returns></returns>
public static bool addOptions(int articleId, Dictionary <string, string> ops)
{
List <int> opsls = new List <int>();
foreach (KeyValuePair <string, string> s in ops)
{
opsls.Add(LabelHelper.getOptionId(s.Key, s.Value));
}
try
{
using (var db = new PaperDbEntities())
{
//增加所有选项
foreach (int id in opsls)
{
TypeConnection tc = new TypeConnection();
tc.ArticleId = articleId;
tc.OptionId = id;
db.TypeConnection.Add(tc);
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 查询需要绑定的数据，并且绑定数据源到给出的下拉列表框中(Enum("keyword","firstLevel","secondLevel"))
/// </summary>
/// <param name="rpt">数据容器</param>
/// <param name="queryString">查询那个表</param>
/// <param name="ca">为了查二级选项，需要提供一级选项名字</param>
public static void bindLables(ref GridView rpt, string queryString, string ca = null)
{
try
{
using (var db = new PaperDbEntities())
{
switch (queryString)
{
case "keyword":
List <KeyWords> keywordList = (from it in db.KeyWords select it).ToList();
rpt.DataSource = keywordList;
break;
case "firstLevel":
List <Category> categoryList = (from it in db.Category select it).ToList();
rpt.DataSource = categoryList;
break;
case "secondLevel":
Category cid = db.Category.Single(a => a.Name == ca);
List <Option> optionList = (from it in db.Option where it.CategoryId == cid.id select it).ToList();
rpt.DataSource = optionList;
break;
}
}
}
catch (Exception ex)
{
rpt.DataSource = null;
}
}
/// <summary>
/// 获取文章的各种选项
/// </summary>
/// <param name="articleId">文章ID</param>
/// <returns>一级选项名，二级选项名的键值对</returns>
public static Dictionary <string, string> getOptions(int articleId)
{
Dictionary <string, string> opsDict = new Dictionary <string, string>();
try
{
using (var db = new PaperDbEntities())
{
var query = from it in db.TypeConnection
where it.ArticleId == articleId
join op in db.Option on it.OptionId equals op.id
join ca in db.Category on op.CategoryId equals ca.id
select new { firstLevel = ca.Name, secondLevel = op.Name };
//转换到字典中
opsDict.Clear();
foreach (var k in query)
{
opsDict.Add(k.firstLevel, k.secondLevel);
}
}
return(opsDict);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取文章的所有关键字
/// </summary>
/// <param name="articleId">文章ID</param>
/// <returns>关键字对象字典</returns>
public static Dictionary <int, string> getKeyowrd(int articleId)
{
Dictionary <int, string> keywordDict = new Dictionary <int, string>();
try
{
List <KeyWords> keywordLs = new List <KeyWords>();
using (var db = new PaperDbEntities())
{
keywordLs = (from it in db.KeyWordConnection
where it.ArticleId == articleId
join key in db.KeyWords on it.KeyWordId equals key.id
select key).ToList();
}
//转换成字典
keywordDict.Clear();
foreach (KeyWords k in keywordLs)
{
keywordDict.Add(k.id, k.Name);
}
return(keywordDict);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 根据一级分类的名字获取一级分类ID
/// </summary>
/// <param name="name">一级分类名字</param>
/// <returns>一级分类ID,失败返回 -1</returns>
public static int getCategoryIdByName(string name)
{
try
{
using (var db = new PaperDbEntities())
{
Category caID = db.Category.Single(a => a.Name == name);
return(caID.id);
}
}
catch (Exception ex)
{
return(-1);
}
}
/// <summary>
/// 根据一级分类返回本类下面的二级分类
/// </summary>
/// <param name="firstLevel">一级分了</param>
/// <returns>本类下面的二级分类</returns>
public static List <Option> bindSecondLevel(string firstLevel)
{
try
{
using (var db = new PaperDbEntities())
{
Category caID = db.Category.Single(a => a.Name == firstLevel);
List <Option> opLi = (from it in db.Option where it.CategoryId == caID.id select it).ToList();
return(opLi);
}
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 获取文章的所有选项记录
/// </summary>
/// <param name="articleId"></param>
/// <returns></returns>
public static List <TypeConnection> getArticleTypeConn(int articleId)
{
try
{
List <TypeConnection> kcLs = new List <TypeConnection>();
using (var db = new PaperDbEntities())
{
kcLs = (from it in db.TypeConnection where it.ArticleId == articleId select it).ToList();
}
return(kcLs);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 得到二级选项的id，建立文章和二级选项的关系
/// </summary>
/// <param name="firstLevel">一级选项名字</param>
/// <param name="secondLevel">二级选项名字</param>
/// <returns>二级选项ID</returns>
public static int getOptionId(string firstLevel, string secondLevel)
{
try
{
using (PaperDbEntities db = new PaperDbEntities())
{
Category c = db.Category.Single(a => a.Name == firstLevel);
Option op = db.Option.Single(a => a.CategoryId == c.id && a.Name == secondLevel);
return(op.id);
}
}
catch (Exception ex)
{
return(-1);
}
}
/// <summary>
/// 根据查询多关键字字符串返回关键字ID
/// </summary>
/// <param name="queryKeyword">关键字字符串(分隔符;)</param>
/// <returns>关键词ID列表</returns>
public static List <int> getKeywordIdByStringKeyword(string queryKeyword)
{
List <int> ids = new List <int>();
try
{
using (var db = new PaperDbEntities())
{
ids = db.KeyWords.Where(a => queryKeyword.Contains(a.Name)).Select(a => a.id).ToList();
}
return(ids);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 根据ID返回一片文章
/// </summary>
/// <param name="articleId">文章ID</param>
/// <returns>文章对象</returns>
public static Article getArticleById(int articleId)
{
Article ar = new Article();
try
{
using (var db = new PaperDbEntities())
{
ar = db.Article.Single(a => a.id == articleId);
return(ar);
}
}
catch (Exception ex)
{
return(ar);
}
}
/// <summary>
/// 通过作者返回文章
/// </summary>
/// <param name="author">作者，模糊搜索</param>
/// <returns></returns>
public static List <Article> getArticleByAuthor(string author)
{
List <Article> arLs = new List <Article>();
try
{
using (var db = new PaperDbEntities())
{
arLs = (from it in db.Article where it.Author.Contains(author) orderby it.Title ascending select it).Distinct().ToList();
return(arLs);
}
}
catch (Exception ex)
{
return(arLs);
}
}
/// <summary>
/// 更新文章
/// </summary>
/// <param name="newAr">待更新文章对象</param>
/// <returns>是否添加成功</returns>
public static bool updateArticle(Article newAr)
{
try
{
using (var db = new PaperDbEntities())
{
Article oldAr = db.Article.Single(a => a.id == newAr.id);
db.Entry(oldAr).CurrentValues.SetValues(newAr);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 通过高级选项返回文章
/// </summary>
/// <param name="opIds">高级选项的ID</param>
/// <returns></returns>
public static List <Article> getArticleByOptions(List <int> opIds)
{
try
{
List <Article> arls = new List <Article>();
using (var db = new PaperDbEntities())
{
List <int> arIds = db.TypeConnection.Where(a => opIds.Contains(a.OptionId)).Select(a => a.ArticleId).Distinct().ToList();
arls = db.Article.Where(a => arIds.Contains(a.id)).ToList();
}
return(arls);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 返回所有文章
/// </summary>
/// <returns></returns>
public static List <Article> getAllArticle()
{
List <Article> arLs = new List <Article>();
try
{
using (var db = new PaperDbEntities())
{
arLs = (from it in db.Article orderby it.Title ascending select it).ToList();
return(arLs);
}
}
catch (Exception ex)
{
return(arLs);
}
}
/// <summary>
/// 添加新文章
/// </summary>
/// <param name="ar">待添加文章对象</param>
/// <returns>添加成功返回文章ID,否则返回-1</returns>
public static int addArticle(Article ar)
{
try
{
using (var db = new PaperDbEntities())
{
db.Article.Add(ar);
db.SaveChanges();
Article tmp = db.Article.Single(a => a.Title == ar.Title && a.Author == ar.Author);
return(tmp.id);
}
}
catch (Exception ex)
{
return(-1);
}
}
/// <summary>
/// 删除文章
/// </summary>
/// <param name="articleID">文章ID</param>
/// <returns></returns>
public static bool delArticle(int articleID)
{
try
{
using (var db = new PaperDbEntities())
{
Article ar = db.Article.SingleOrDefault(a => a.id == articleID);
db.Article.Remove(ar);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 获取所有关键字
/// </summary>
/// <returns>关键字对象列表</returns>
public static List <KeyWords> getAllKeywords()
{
List <KeyWords> keywordList = new List <KeyWords>();
try
{
using (var db = new PaperDbEntities())
{
keywordList = (from it in db.KeyWords select it).ToList();
}
return(keywordList);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 通过关键词返回文章
/// </summary>
/// <param name="keywordIds">关键词ID列表，模糊搜索</param>
/// <returns></returns>
public static List <Article> getArticleByKeyword(List <int> keywordIds)
{
List <int> keyConn = new List <int>();
List <Article> arLs = new List <Article>();
try
{
using (var db = new PaperDbEntities())
{
keyConn = db.KeyWordConnection.Where(a => keywordIds.Contains(a.KeyWordId)).OrderBy(a => a.ArticleId).Select(a => a.ArticleId).ToList();
arLs = db.Article.Where(a => keyConn.Contains(a.id)).OrderBy(a => a.Title).Distinct().ToList();
return(arLs);
}
}
catch (Exception ex)
{
return(arLs);
}
}
/// <summary>
/// 删除文章类型选项记录
/// </summary>
/// <param name="typeConnId"></param>
/// <returns></returns>
public static bool delTypeConnection(List <int> typeConnId)
{
try
{
using (var db = new PaperDbEntities())
{
foreach (int id in typeConnId)
{
TypeConnection kc = db.TypeConnection.Single(a => a.id == id);
db.TypeConnection.Remove(kc);
}
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 通过高级选项返回文章
/// </summary>
/// <param name="opIds">高级选项的ID</param>
/// <returns></returns>
public static List <Article> getArticleByOptions(List <int> opIds)
{
try
{
List <Article> arls = new List <Article>();
using (var db = new PaperDbEntities())
{
/***
*
* 这个是是要包含就能查出来，不太好
* */
/*
* List<int> arIds = db.TypeConnection.Where(a => opIds.Contains(a.OptionId)).Select(a => a.ArticleId).Distinct().ToList();
* arls = db.Article.Where(a => arIds.Contains(a.id)).ToList();
*/
//查询每一个 optionID 可以获得一个 set
//求所有 set 的广义交，结果就是多选项查询
//set 访问速度O(1),但是求交的时候也需要遍历，速度取决于 optionId 的个数
//目前没想到什么好的办法改进，可能程序可以优化，也可能数据库结构不好，导致现在这种查询尴尬的境地
int opid0 = opIds[0];
int opidNow = 0;
HashSet <int> setId = new HashSet <int>(db.TypeConnection.Where(a => a.OptionId == opid0).Select(a => a.ArticleId));// new HashSet<int>();
HashSet <int> setTmp = null;
for (int i = 1; i < opIds.Count; i++)
{
//linq 不可以直接使用数组下标访问，需要用变量存一下
opidNow = opIds[i];
setTmp = new HashSet <int>(db.TypeConnection.Where(a => a.OptionId == opidNow).Select(a => a.ArticleId));
setId.IntersectWith(setTmp);
}
arls = db.Article.Where(a => setId.Contains(a.id)).ToList();
}
return(arls);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 通过时间返回文章
/// </summary>
/// <param name="low">时间下限</param>
/// <param name="high">时间上限</param>
/// <returns></returns>
public static List <Article> getArticleByTime(int low, int high)
{
DateTime lowYear = new DateTime(low, 1, 1);
DateTime highYear = new DateTime(high, 1, 1);
List <Article> arls = new List <Article>();
try
{
using (var db = new PaperDbEntities())
{
arls = (from it in db.Article
where it.UpateTime <= highYear && it.UpateTime >= lowYear
select it).Distinct().ToList();
}
return(arls);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 添加各种选项，通过给出optionId
/// </summary>
/// <param name="articleId"></param>
/// <param name="opsId"></param>
/// <returns></returns>
public static bool addOptions(int articleId, List <int> opsId)
{
try
{
using (var db = new PaperDbEntities())
{
foreach (int i in opsId)
{
TypeConnection tc = new TypeConnection();
tc.ArticleId = articleId;
tc.OptionId = i;
db.TypeConnection.Add(tc);
}
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
/// <summary>
/// 混合条件查询中，从但条件查询中过滤出符合各种选项的文章
/// </summary>
/// <param name="ll"></param>
/// <param name="opIds"></param>
/// <returns></returns>
public static List <Article> filterArticleByOptions(ref List <Article> ll, ref List <int> opIds)
{
try
{
using (var db = new PaperDbEntities())
{
List <int> arids = new List <int>();
arids.Clear();
foreach (Article ar in ll)
{
arids.Add(ar.id);
}
//获取待过滤文章的所有 TypeConnection
List <TypeConnection> tyls = db.TypeConnection.Where(a => arids.Contains(a.ArticleId)).ToList();
List <Article> arls = new List <Article>();
int opid0 = opIds[0];
int opidNow = 0;
HashSet <int> setId = new HashSet <int>(tyls.Where(a => a.OptionId == opid0).Select(a => a.ArticleId));// new HashSet<int>();
HashSet <int> setTmp = null;
for (int i = 1; i < opIds.Count; i++)
{
//linq 不可以直接使用数组下标访问，需要用变量存一下
opidNow = opIds[i];
setTmp = new HashSet <int>(tyls.Where(a => a.OptionId == opidNow).Select(a => a.ArticleId));
setId.IntersectWith(setTmp);
}
arls = ll.Where(a => setId.Contains(a.id)).ToList();
return(arls);
}
}
catch (Exception ex)
{
return(null);
}
}
protected void btnSecondLevel_Click(object sender, EventArgs e)
{
string secondLevelVal = txtSecondLevel.Text.Trim();
if (secondLevelVal.Equals(string.Empty))
{
Response.Write(JSHelper.alert("二级分类不能为空，请重新输入！"));
}
else
{
try
{
using (var db = new PaperDbEntities())
{
Category c = db.Category.Single(a => a.Name == dplFirstLevel.SelectedValue);
Option tmp = db.Option.SingleOrDefault(a => a.Name == secondLevelVal && a.CategoryId == c.id);
if (tmp == null)
{
Option secondLevel = new Option();
secondLevel.Name = secondLevelVal;
secondLevel.CategoryId = LabelHelper.getCategoryIdByName(dplFirstLevel.SelectedValue);
db.Option.Add(secondLevel);
db.SaveChanges();
Response.Write(JSHelper.alert("添加成功!", "manageLabel.aspx"));
}
else
{
Response.Write(JSHelper.alert("二级分类重复，请检查！"));
}
}
}
catch (Exception ex)
{
//ex.Message;
Response.Write(JSHelper.alert("添加失败，请重新尝试!"));
}
}
}
protected void dplManageLabel_SelectedIndexChanged(object sender, EventArgs e)
{
if (dplManageLabel.SelectedValue.Equals("关键字管理"))
{
panel_keywords.Visible = true;
LabelHelper.bindLables(ref gdvKeywords, "keyword");
gdvKeywords.DataBind();
panel_firstLevel.Visible = panel_secondLevel.Visible = false;
}
else if (dplManageLabel.SelectedValue.Equals("一级分类管理"))
{
panel_firstLevel.Visible = true;
LabelHelper.bindLables(ref gdvFirstLevel, "firstLevel");
gdvFirstLevel.DataBind();
panel_keywords.Visible = panel_secondLevel.Visible = false;
}
else
{
try
{
//绑定一级分类
using (var db = new PaperDbEntities())
{
List <string> caLi = (from it in db.Category select it.Name).ToList();
dplFirstLevel.DataSource = caLi;
dplFirstLevel.DataBind();
}
}
catch (Exception ex)
{
throw;
}
panel_secondLevel.Visible = true;
LabelHelper.bindLables(ref gdvSecondLevel, "secondLevel", dplFirstLevel.SelectedValue);
gdvSecondLevel.DataBind();
panel_keywords.Visible = panel_firstLevel.Visible = false;
}
}
/// <summary>
/// 添加关键字
/// </summary>
/// <param name="articleId">文章id</param>
/// <param name="keywordId">关键字id 列表</param>
/// <returns>是否成功</returns>
public static bool addKeywords(int articleId, List <int> keywordId)
{
try
{
using (var db = new PaperDbEntities())
{
//增加所有关键字
foreach (int id in keywordId)
{
KeyWordConnection kc = new KeyWordConnection();
kc.ArticleId = articleId;
kc.KeyWordId = id;
db.KeyWordConnection.Add(kc);
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
protected void btnKeyword_Click(object sender, EventArgs e)
{
string keywordvalu = txtKeyword.Text.Trim();
if (keywordvalu.Equals(string.Empty))
{
Response.Write(JSHelper.alert("关键词不能为空，请重新输入！"));
}
else
{
try
{
using (var db = new PaperDbEntities())
{
KeyWords tmp = db.KeyWords.SingleOrDefault(a => a.Name == keywordvalu);
if (tmp == null)
{
KeyWords keyword = new KeyWords();
keyword.Name = keywordvalu;
db.KeyWords.Add(keyword);
db.SaveChanges();
Response.Write(JSHelper.alert("添加成功!", "manageLabel.aspx"));
}
else
{
Response.Write(JSHelper.alert("关键词重复，请检查！"));
}
}
}
catch (Exception ex)
{
//ex.Message;
Response.Write(JSHelper.alert("添加失败，请重新尝试!"));
}
}
}
protected void btnFirstLevel_Click(object sender, EventArgs e)
{
string firstLevelVal = txtFirstLevel.Text.Trim();
if (firstLevelVal.Equals(string.Empty))
{
Response.Write(JSHelper.alert("一级分类不能为空，请重新输入！"));
}
else
{
try
{
using (var db = new PaperDbEntities())
{
Category tmp = db.Category.SingleOrDefault(a => a.Name == firstLevelVal);
if (tmp == null)
{
Category firstLevel = new Category();
firstLevel.Name = firstLevelVal;
db.Category.Add(firstLevel);
db.SaveChanges();
Response.Write(JSHelper.alert("添加成功!", "manageLabel.aspx"));
}
else
{
Response.Write(JSHelper.alert("一级分类重复，请检查！"));
}
}
}
catch (Exception ex)
{
//ex.Message;
Response.Write(JSHelper.alert("添加失败，请重新尝试!"));
}
}
}
