public List <TenantPrd> getSearchTenandPrdByCatId(PrdSearchQuery searchQuery, int userId)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
int top = searchQuery.count;
int pageCount = top * searchQuery.page;
string tenantId = searchQuery.tnenatId.ToString();
long catId = searchQuery.catId;
var notInsql = @" select TOP {0} p.id from [TenantPrd] p ";
notInsql += @" left join [TenantPrdCatRel] r on r.prdId = p.id ";
notInsql += @" left join [TenantPrdRead] pr on pr.prdId = p.id ";
notInsql += @" left join [TenantMember] mr on mr.memberId = " + userId;
notInsql += @" where p.tenantId = '{1}' and r.catId = {2} and p.status = N'上架' and p.name like N'%{3}%' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) ";
notInsql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {3} or pr.memLevelId = mr.levelId) ";
notInsql += SortType.getOrderBy(searchQuery.sortType);
notInsql = String.Format(notInsql, pageCount, tenantId, catId, searchQuery.name, userId);
var sql = @" select TOP {0} p.* from [TenantPrd] p ";
sql += @" left join [TenantPrdCatRel] r on r.prdId = p.id ";
sql += @" left join [TenantPrdRead] pr on pr.prdId = p.id ";
sql += @" left join [TenantMember] mr on mr.memberId = " + userId;
sql += @" where p.tenantId = '{1}' and r.catId = {2} and p.status = N'上架' and p.name like N'%{3}%' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) ";
sql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {3} or pr.memLevelId = mr.levelId) ";
sql += @" and p.id not in ( {4} ) ";
sql += SortType.getOrderBy(searchQuery.sortType);
sql = String.Format(sql, top, tenantId, catId, searchQuery.name, userId, notInsql);
MDebugLog.debug("[getSearchTenandPrdByCatId] >" + sql);
return(dbContext.Database.SqlQuery <TenantPrd>(sql).ToList());
}
}
public ActionResult getPrdSearchByCatId([FromBody] SearchRequest request)
{
ReturnMessage rm = new ReturnMessage();
try
{
int userId = -1;
string tenantId = User.Claims.FirstOrDefault(p => p.Type == "tenantId").Value;
string type = User.Claims.FirstOrDefault(p => p.Type == "type").Value;
if (!UserType.GUEST.Equals(type))
{
userId = int.Parse(User.Claims.FirstOrDefault(p => p.Type == "jti").Value);
}
PrdSearchQuery query = new PrdSearchQuery(request);
query.tnenatId = Guid.Parse(tenantId);
query.catId = request.catId;
query.userType = type;
int totalCount = CTenantPrdManager.getTenantPrdCount(Guid.Parse(tenantId), request.catId, userId);
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.getPrdSearchListByCat(query, userId);
rm.code = MessageCode.SUCCESS;
rm.data = response;
}
catch (Exception e)
{
rm.code = MessageCode.ERROR;
rm.data = e.Message;
}
return(Ok(rm));
}
public static IEnumerable <Dictionary <string, object> > getPrdSearchListByCat(PrdSearchQuery query, int userId)
{
List <TenantPrd> data = DataManager.tenantPrdDao.getSearchTenandPrdByCatId(query, userId);
IEnumerable <Dictionary <string, object> > result = getPrdList(data, query.userType, userId);
return(result);
}
