public static IEnumerable <Dictionary <string, object> > getPrdListByCat(PrdPageQuery query, int userId) { List <TenantPrd> data = DataManager.tenantPrdDao.getTenandPrdByCatId(query, userId); IEnumerable <Dictionary <string, object> > result = getPrdList(data, query.userType, userId); return(result); }
public ActionResult getPrdByCatId(long id, [FromQuery] SortTypeRequest request) { ReturnMessage rm = new ReturnMessage(); try { string tenantId = User.Claims.FirstOrDefault(p => p.Type == "tenantId").Value; string type = User.Claims.FirstOrDefault(p => p.Type == "type").Value; int memberId = -1; if (!UserType.GUEST.Equals(type)) { memberId = int.Parse(User.Claims.FirstOrDefault(p => p.Type == "jti").Value); } PrdPageQuery query = new PrdPageQuery(request); query.tnenatId = Guid.Parse(tenantId); query.catId = id; query.userType = type; int totalCount = CTenantPrdManager.getTenantPrdCount(Guid.Parse(tenantId), id, memberId); int maxPage = totalCount % request.count == 0 ? totalCount / request.count : totalCount / request.count + 1; maxPage = totalCount <= request.count ? 1 : maxPage; PrdPageResponse response = new PrdPageResponse(); response.page = request.page; response.maxPage = maxPage; response.result = CTenantPrdManager.getPrdListByCat(query, memberId); rm.code = MessageCode.SUCCESS; rm.data = response; } catch (Exception e) { rm.code = MessageCode.ERROR; rm.data = e.Message; } return(Ok(rm)); }
public List <TenantPrd> getTenandPrdByCatId(PrdPageQuery pageQuery, int userId) { using (CrazyBuyDbContext dbContext = ContextInit()) { int top = pageQuery.count; int pageCount = top * pageQuery.page; string tenantId = pageQuery.tnenatId.ToString(); long catId = pageQuery.catId; var keyWordSql = ""; if (pageQuery.keyword != null) { keyWordSql = @" and (p.name like N'%" + pageQuery.keyword + "%' "; keyWordSql += @" or p.summary like N'%" + pageQuery.keyword + "%' "; keyWordSql += @" or p.[desc] like N'%" + pageQuery.keyword + "%' ) "; } else { if (isParent(catId)) { keyWordSql += @" and a.ancestorId = " + catId; } else { keyWordSql += @" and r.catId = " + catId; } } var sql = ""; var notInsql = ""; if (isParent(catId)) { notInsql = @" select TOP {0} p.id from dbo.TenantPrd p "; notInsql += @" left join dbo.TenantPrdCatRel r on r.prdId = p.id "; notInsql += @" left join dbo.TenantPrdCatAd a on a.descendantId = r.catId "; notInsql += @" left join dbo.TenantPrdRead pr on pr.prdId = p.id "; notInsql += @" left join dbo.TenantMember mr on mr.memberId = " + userId; //notInsql += @" where p.tenantId = '{1}' and a.ancestorId = {2} and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) "; notInsql += @" where p.tenantId = '{1}' and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) "; notInsql += keyWordSql; notInsql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {2} or pr.memLevelId = mr.levelId)"; //notInsql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {3} or pr.memLevelId = mr.levelId)"; notInsql += SortType.getOrderBy(pageQuery.sortType); //notInsql = String.Format(notInsql, pageCount, tenantId, catId, userId); notInsql = String.Format(notInsql, pageCount, tenantId, userId); sql = @" select distinct tb.* from ( select TOP {0} p.* from dbo.TenantPrd p "; sql += @" left join dbo.TenantPrdCatRel r on r.prdId = p.id "; sql += @" left join dbo.TenantPrdCatAd a on a.descendantId = r.catId "; sql += @" left join dbo.TenantPrdRead pr on pr.prdId = p.id "; sql += @" left join dbo.TenantMember mr on mr.memberId = " + userId; //sql += @" where p.tenantId = '{1}' and a.ancestorId = {2} and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) "; sql += @" where p.tenantId = '{1}' and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) "; sql += keyWordSql; sql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {2} or pr.memLevelId = mr.levelId) "; sql += @" and p.id not in ( {3} ) ) tb"; sql += SortType.getOrderDistBy(pageQuery.sortType); } else { notInsql = @" select TOP {0} p.id from dbo.TenantPrd p "; notInsql += @" left join dbo.TenantPrdCatRel r on r.prdId = p.id "; notInsql += @" left join dbo.TenantPrdRead pr on pr.prdId = p.id "; notInsql += @" left join dbo.TenantMember mr on mr.memberId = " + userId; notInsql += @" where p.tenantId = '{1}' and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) "; notInsql += keyWordSql; notInsql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {2} or pr.memLevelId = mr.levelId) "; notInsql += SortType.getOrderBy(pageQuery.sortType); notInsql = String.Format(notInsql, pageCount, tenantId, userId); sql = @" select TOP {0} p.* from dbo.TenantPrd p "; sql += @" left join dbo.TenantPrdCatRel r on r.prdId = p.id "; sql += @" left join dbo.TenantPrdRead pr on pr.prdId = p.id "; sql += @" left join dbo.TenantMember mr on mr.memberId = " + userId; sql += @" where p.tenantId = '{1}' and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) "; sql += keyWordSql; sql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {2} or pr.memLevelId = mr.levelId) "; sql += @" and p.id not in ( {3} ) "; sql += SortType.getOrderBy(pageQuery.sortType); } sql = String.Format(sql, top, tenantId, userId, notInsql); MDebugLog.debug("[getTenandPrdByCatId] >" + sql); return(dbContext.Database.SqlQuery <TenantPrd>(sql).ToList()); } }