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