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