public List <TenantPrdCatCount> getAllPrdCats(Guid tenantId, int memberId)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
var sql = @" select c.* , ";
sql += @" (select count(id) from TenantPrdCatAd tpca where tpca.ancestorId = c.id) as count , ";
sql += @" ( select count(cr.id) from TenantPrdCatRel cr ";
sql += @" left join TenantPrd prd on prd.id = cr.prdId ";
sql += @" left join TenantPrdRead pr on pr.prdId = prd.id ";
sql += @" left join TenantMember mr on mr.memberId = " + memberId;
sql += @" where cr.catId = c.id and cr.status = N'正常' ";
sql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {0} or pr.memLevelId = mr.levelId) ";
sql += @" and prd.status = N'上架' and (prd.dtSellEnd is null or prd.dtSellEnd >= getdate() or (prd.dtSellEnd <= getdate() and prd.takeOffMethod = N'隱藏訂購鈕'))) as pcount ,";
sql += @" (select count(distinct p.id) as total from dbo.TenantPrd p ";
sql += @" inner join TenantPrdCatRel r on r.prdId = p.id ";
sql += @" inner join TenantPrdRead pr on pr.prdId = p.id ";
sql += @" inner join TenantMember mr on mr.memberId = " + memberId;
sql += @" inner join TenantPrdCatAd a on r.catId = a.descendantId ";
sql += @" where a.ancestorId = c.id ";
sql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {1} or pr.memLevelId = mr.levelId) ";
sql += @" and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd is null or p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕'))) as ccount ";
sql += @" from [TenantPrdCat] c where tenantId = '{2}' and status = N'{3}' order by parentId asc, c.sort asc ";
string query = String.Format(sql, memberId, memberId, tenantId.ToString(), "正常");
MDebugLog.debug("@" + query);
return(dbContext.Database.SqlQuery <TenantPrdCatCount>(query).ToList());
}
}
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 Member getMemberByCellPhone(Guid tenantId, string phone, string pwd)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
var sql = @" select m.* from [Member] m ";
sql += @" left join [TenantMember] t on t.memberId = m.memberId ";
sql += @" where t.tenantId = '{0}' and m.cellphone = '{1}' and m.password = '******' and t.status = N'啟用' ";
string query = String.Format(sql, tenantId.ToString(), phone, pwd);
MDebugLog.debug("[getMemberByCellPhone]>" + query);
return(dbContext.Database.SqlQuery <Member>(query).SingleOrDefault());
}
}
public Member VerifyMemberIsExist(Guid tenantId, string phone, string email)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
var sql = @"SELECT * FROM dbo.Member m ";
sql += @" inner join TenantMember tm on m.memberId = tm.memberId ";
sql += @" WHERE m.cellphone = N'{0}' and m.email = N'{1}' and tenantId = '{2}'";
sql = string.Format(sql, phone, email, tenantId);
MDebugLog.debug("[VerifyMemberIsExist] >" + sql);
return(dbContext.Database.SqlQuery <Member>(sql).SingleOrDefault());
}
}
public ShopCart getShopCartPrd(Guid tenantId, int memberId, int productId, string sepc)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
var sql = @" SELECT TOP 1 * FROM [ShopCart] c ";
sql += @" where c.tenantId = '{0}' and c.memberId = {1} and c.productId = {2} ";
if (!string.IsNullOrEmpty(sepc))
{
sql += string.Format(" and c.prdSepc like '%{0}%' ", sepc);
}
var query = String.Format(sql, tenantId, memberId, productId);
MDebugLog.debug(query);
return(dbContext.Database.SqlQuery <ShopCart>(query).FirstOrDefault());
}
}
// 首頁商品
public List <TenantPrd> getHomePrds(Guid tenantId, int userId)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
var sql = @"SELECT tp.* FROM [TenantPrd] tp";
sql += @" left join [TenantHomePrd] hp on hp.prdId = tp.id";
sql += @" left join [TenantPrdRead] pr on pr.prdId = tp.id ";
sql += @" left join [TenantMember] mr on mr.memberId = " + userId;
sql += @" where tp.[isOpenOrder] = 1 and tp.status = N'上架' and hp.dtStart <= getdate() and (hp.dtEnd is null or hp.dtEnd >= getdate()) ";
sql += @" and hp.[tenantId] ='{0}' ";
sql += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {1} or pr.memLevelId = mr.levelId )";
string query = String.Format(sql, tenantId.ToString(), userId);
MDebugLog.debug("[getHomePrds] > " + query);
return(dbContext.Database.SqlQuery <TenantPrd>(query).ToList());
}
}
public int getCountByCatId(Guid tenantId, long catId, int memberId)
{
using (CrazyBuyDbContext dbContext = ContextInit())
{
var sql = "";
if (isParent(catId))
{
//sql = @" select count(*) as total 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 += @" where a.ancestorId = {0} and ";
//sql += @" p.tenantId = '{1}' and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) ";
sql = @" select count(distinct p.Id) 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 = " + memberId;
sql += @" where p.tenantId = '{0}' and a.ancestorId = {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 += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {2} or pr.memLevelId = mr.levelId) ";
}
else
{
// sql = @" select count(*) as total from dbo.TenantPrd p ";
// sql += @" left join dbo.TenantPrdCatRel r on r.prdId = p.id ";
// sql += @" where r.catId = {0} and ";
// sql += @" p.tenantId = '{1}' and r.status = N'正常' and p.status = N'上架' and (p.dtSellEnd >= getdate() or (p.dtSellEnd <= getdate() and takeOffMethod = N'隱藏訂購鈕')) ";
sql = @" select count(distinct p.Id) 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 = " + memberId;
sql += @" where p.tenantId = '{0}' and r.catId = {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 += @" and pr.status = N'正常' and (pr.type = N'所有會員' or pr.tenantMemId = {2} or pr.memLevelId = mr.levelId )";
}
//sql = String.Format(sql, top, tenantId, catId, notInsql);
string query = String.Format(sql, tenantId, catId, memberId);
MDebugLog.debug("[getCountByCatId] > " + query);
return(dbContext.Database.SqlQuery <SqlQueryTotal>(query).SingleOrDefault().total);
}
}
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());
}
}