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 static ReturnMessage addOrder(OrderMaster orderMaster, UserInfo userInfo)
{
ReturnMessage rm = new ReturnMessage();
int orderResult = MessageCode.ERROR;
try
{
DateTime now = DateTime.Now;
orderMaster.tenantId = userInfo.tnenatId;
orderMaster.memberId = userInfo.memberId;
orderMaster.dtOrder = now;
orderMaster.dtInStock = now;
orderMaster.createTime = now;
orderMaster.updateTime = now;
int total = 0;
int taxAmount = 0;
DataManager.shopCartDao.deleteTimeOutItem(userInfo.memberId); //防止購入超時商品
List <ShopCartPrd> shopCartPrds = DataManager.shopCartDao.getItemsByMember(userInfo.memberId);
List <OrderDetail> detailList = new List <OrderDetail>();
Dictionary <int, TenantPrd> prdMap = new Dictionary <int, TenantPrd>();
TenantMemLevel tenantMemLevel = DataManager.tenantMemberDao.getMemberLevel(userInfo.memberId);
//是否有高級會員折扣
double dis = 1;
int memberLevelId = MessageCode.ERROR;
if (tenantMemLevel != null)
{
memberLevelId = tenantMemLevel.id;
dis = tenantMemLevel.discount * 0.01;
}
//結算購物車價錢及規格數量
foreach (ShopCartPrd item in shopCartPrds)
{
TenantPrd prdItem = DataManager.tenantPrdDao.getTenandPrd(item.productId);
prdItem = isPrdSPecEnought(prdItem, item.prdSepc, item.count);
if (prdItem != null)
{
OrderDetail detail = new OrderDetail();
detail.prdId = item.productId;
detail.qty = item.count;
detail.amount = item.amount;
if (item.SpecialRule == null || !item.SpecialRule.Contains(UserDisType.NO_DIS))
{
detail.amount = (int)(detail.amount * dis);
}
detail.unitPrice = detail.amount / detail.qty;
detail.status = "正常";
detail.createTime = now;
detail.updateTime = now;
detail.prdSpec = item.prdSepc;
detail.prdCustPriceId = item.prdCustPriceId;
detail.priceGradeType = item.priceGradeType;
total += detail.amount;
detailList.Add(detail);
if (!prdMap.ContainsKey(prdItem.id))
{
prdMap.Add(prdItem.id, prdItem);
}
}
}
//檢查是否數量足夠
foreach (OrderDetail item in detailList)
{
TenantPrd prdItem = prdMap.GetValueOrDefault(item.prdId);
prdItem.stockNum = prdItem.stockNum - item.qty;
if (!string.IsNullOrEmpty(prdItem.zeroStockMessage) && prdItem.dtSellEnd > DateTime.Now)
{
if (prdItem.stockNum < 0)
{
Debug.WriteLine(prdItem.name);
rm.code = MessageCode.PRD_NOT_ENOUGHT;
return(rm);
}
}
}
//開始寫入價格
if (total != 0)
{
orderMaster.orderAmount = total;
if (orderMaster.isNeedInvoice || orderMaster.invoiceType.Equals("捐贈"))
{
taxAmount = Convert.ToInt32(total * 0.05);
}
total += taxAmount;
total += orderMaster.shippingAmount;
orderMaster.totalAmount = total;
// 5/14有需求說先將等待貨款拿掉
//orderMaster.payStatus = "等待貨款";
orderMaster.shippingStatus = "未出貨";
orderMaster.status = "新訂單";
orderMaster.taxAmount = taxAmount;
if (MessageCode.ERROR != memberLevelId)
{
orderMaster.levelId = memberLevelId;
}
OrderMaster master = DataManager.orderDao.addOrderMaster(orderMaster);
orderResult = master.id;
rm.data = master;
foreach (OrderDetail item in detailList)
{
TenantPrd prdItem = prdMap.GetValueOrDefault(item.prdId);
DataManager.tenantPrdDao.updateTenandPrd(prdItem);
item.orderId = master.id;
DataManager.orderDao.addOrderDetail(item);
}
DataManager.shopCartDao.removeItemsByMember(userInfo.memberId);
OrderAmountHistory histroy = new OrderAmountHistory();
histroy.orderId = master.id;
histroy.changeDesc = "購物完成";
histroy.changeAmount = total;
histroy.cumulativeAmount = 0;
histroy.status = "正常";
histroy.createTime = DateTime.Now;
histroy.creator = userInfo.memberId;
DataManager.orderAmountHistoryDao.add(histroy);
}
}
catch (Exception e)
{
MDebugLog.error("[COrderManager-addOrder] error: " + e);
}
rm.code = orderResult;
return(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());
}
}
