public DataTable GetAllTags(TagsSortTypeEnum tagsSortTypeEnum)
{
var sql = new SqlQuickBuild();
string command = @"
select
ISNULL(b.ItemCount,0) ItemCount
,a.* from Tag a
{1} join (
select _b.TagId,count(*) ItemCount from MenuBelongTag _b
left join Question qq on qq.QuestionId=_b.MainId and _b.MainType=1
left join Article aa on aa.ArticleId=_b.MainId and _b.MainType=2
where (qq.IsDelete=0 or aa.IsDelete=0) {0}
group by _b.TagId
) b on a.TagId=b.TagId
where a.IsDelete=0 order by a.CreateTime desc;" ;
string conditionJoin = " left ";
string conditionWhere = string.Empty;
switch (tagsSortTypeEnum)
{
case TagsSortTypeEnum.Sort_Hot:
// conditionJoin = @"
//left join MenuBelongTag b on b.TagId=a.TagId and b.MainType=1
//left join Question c on c.QuestionId=b.MainId";
// conditionWhere = " and c.IsRemen=1 and c.IsDelete=0 ";
conditionJoin = " right ";
conditionWhere = @" and (qq.IsRemen=1)";
break;
case TagsSortTypeEnum.Sort_JingHua:
// conditionJoin = @"left join MenuBelongTag b on b.TagId=a.TagId and b.MainType=1
//left join Question c on c.QuestionId=b.MainId";
// conditionWhere = " and c.IsJinghua=1 and c.IsDelete=0 ";
conditionJoin = " right ";
conditionWhere = @" and (qq.IsJinghua=1)";
break;
case TagsSortTypeEnum.Sort_30:
// conditionJoin = @"left join MenuBelongTag b on b.TagId=a.TagId and b.MainType=1
//left join Question c on c.QuestionId=b.MainId";
DateTime start = DateTime.Now.AddDays(-DateTime.Now.Day + 1).Date;
// conditionWhere = " and c.IsDelete=0 and c.CreateTime between @startTime and getdate()";
conditionJoin = " right ";
conditionWhere = " and (qq.CreateTime between @startTime and getdate() or aa.CreateTime between @startTime and getdate())";
sql.AddParams("@startTime", SqlDbType.DateTime, start);
break;
}
sql.Cmd = command.FormatWith(conditionWhere, conditionJoin);
return(sql.GetTable());
}
public List <HomeTagsViewModel> GetAllTags(TagsSortTypeEnum tagsSortTypeEnum)
{
return(ModelConvertHelper <HomeTagsViewModel> .ConvertToList(dal.GetAllTags(tagsSortTypeEnum)));
}
private List <HomeTagsViewModel> GetTags(TagsSortTypeEnum tagsSortTypeEnum)
{
return(TagBLL.Instance.GetAllTags(tagsSortTypeEnum));
}
