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)); }