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 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 <Town> getTowns() { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"select * from [Town] where status = N'正常' order by sort asc "; return(dbContext.Database.SqlQuery <Town>(sql).ToList()); } }
public void add(OrderContactItem data) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.OrderContactItem.Add(data); dbContext.SaveChanges(); } }
public void addTenant(Tenant tenant) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.Tenant.Add(tenant); dbContext.SaveChanges(); } }
public void addTenantBillDetail(TenantBillDetail tenantBillDetail) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.TenantBillDetail.Add(tenantBillDetail); dbContext.SaveChanges(); } }
public void addOrderDetail(OrderDetail orderDetail) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.OrderDetail.Add(orderDetail); dbContext.SaveChanges(); } }
public void add(MailNotice mailNotice) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.MailNotice.Add(mailNotice); dbContext.SaveChanges(); } }
public void addTenantMember(TenantMember tenantMember) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.TenantMember.Add(tenantMember); dbContext.SaveChanges(); } }
public void addItem(ShopCart item) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.ShopCart.Add(item); dbContext.SaveChanges(); } }
// 單獨商品 public TenantPrd getTenandPrd(int prdId) { using (CrazyBuyDbContext dbContext = ContextInit()) { TenantPrd model = dbContext.TenantPrd.Where( m => m.id == prdId).SingleOrDefault(); return(model); } }
public void removeItem(Guid id) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @" DELETE FROM [ShopCart] WHERE id = '{0}' "; var query = String.Format(sql, id); dbContext.Database.ExecuteSqlCommand(query); } }
public Member getMember(int id) { using (CrazyBuyDbContext dbContext = ContextInit()) { Member model = dbContext.Member.Where( m => m.memberId == id).SingleOrDefault(); return(model); } }
public OrderMaster addOrderMaster(OrderMaster orderMaster) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.OrderMaster.Add(orderMaster); dbContext.SaveChanges(); return(orderMaster); } }
public int addMember(Member member) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.Member.Add(member); dbContext.SaveChanges(); return(member.memberId); } }
public List <TenantPrdCatRead> getCatReads(int catId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"select catId,type,tenantMemId,memLevelId from [TenantPrdCatRead] where catId = {0} and status = N'正常'"; string query = String.Format(sql, catId); return(dbContext.Database.SqlQuery <TenantPrdCatRead>(query).ToList()); } }
public int addTenantBill(TenantBill tenantBill) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.TenantBill.Add(tenantBill); dbContext.SaveChanges(); return(tenantBill.id); } }
public Tenant getTenantByOwner(int ownerId) { using (CrazyBuyDbContext dbContext = ContextInit()) { Tenant model = dbContext.Tenant.Where( m => m.createdMemberId == ownerId).FirstOrDefault(); return(model); } }
public List <TenantPrdCat> getPrdCatsByParentId(Guid tenantId, long parentId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"select * from [TenantPrdCat] where tenantId = '{0}' and parentId = {1} and status = N'正常'"; string query = String.Format(sql, tenantId, parentId); return(dbContext.Database.SqlQuery <TenantPrdCat>(query).ToList()); } }
public Tenant getTenantByTenantCode(string tenantCode) { using (CrazyBuyDbContext dbContext = ContextInit()) { Tenant model = dbContext.Tenant.Where( m => m.tenantCode == tenantCode).SingleOrDefault(); return(model); } }
public TenantGrade GetTenantGrade(Guid tenantId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"SELECT Top 1 * FROM dbo.TenantGrade where tenantId = '{0}' and status = N'正常' order by dtStart desc"; sql = string.Format(sql, tenantId); return(dbContext.Database.SqlQuery <TenantGrade>(sql).SingleOrDefault()); } }
public List <TenantSetting> GetDefaultTenantSettings() { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"SELECT * FROM dbo.TenantSetting where tenantId is null "; //sql = string.Format(sql, tenantId); return(dbContext.Database.SqlQuery <TenantSetting>(sql).ToList()); } }
public TenantSetting getTenantSetting(Guid tenantId, string title) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"SELECT * FROM dbo.TenantSetting where title = N'{0}' and tenantId = '{1}' "; sql = string.Format(sql, title, tenantId); return(dbContext.Database.SqlQuery <TenantSetting>(sql).SingleOrDefault()); } }
public List <OrderMaster> getOrderByMember(int memberId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @" SELECT * FROM [OrderMaster] WHERE memberId = {0} and status <> N'刪除' order by id desc "; var query = String.Format(sql, memberId); return((List <OrderMaster>)dbContext.Database.SqlQuery <OrderMaster>(query).ToList()); } }
public int getCountCatsByParentId(Guid tenantId, long parentId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"select count(*) as total from [TenantPrdCat] where tenantId = '{0}' and parentId = {1}"; string query = String.Format(sql, tenantId, parentId); return(dbContext.Database.SqlQuery <SqlQueryTotal>(query).SingleOrDefault().total); } }
public Tenant getTenant(Guid id) { using (CrazyBuyDbContext dbContext = ContextInit()) { Tenant model = dbContext.Tenant.Where( m => m.tenantId == id).SingleOrDefault(); return(model); } }
public int addTenantGrade(TenantGrade tenantGrade) { using (CrazyBuyDbContext dbContext = ContextInit()) { dbContext.TenantGrade.Add(tenantGrade); dbContext.SaveChanges(); return(tenantGrade.id); } }
public List <TenantSetting> getTenantSetting(Guid tenantId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"SELECT * FROM dbo.TenantSetting where title in (N'免運設定',N'常溫運費',N'低溫運費') and tenantId = '{0}' "; sql = string.Format(sql, tenantId); return(dbContext.Database.SqlQuery <TenantSetting>(sql).ToList()); } }
public List <TenantBulletin> getBulletin(Guid tenantId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @"SELECT * FROM [TenantBulletin] where tenantId = '{0}' and status = N'上架' order by isTop desc ,sort"; sql = String.Format(sql, tenantId); return(dbContext.Database.SqlQuery <TenantBulletin>(sql).ToList()); } }
public bool isParent(long catId) { using (CrazyBuyDbContext dbContext = ContextInit()) { var sql = @" select count(*) as total from TenantPrdCatAd a where a.ancestorId = {0} "; string query = String.Format(sql, catId); return(dbContext.Database.SqlQuery <SqlQueryTotal>(query).SingleOrDefault().total > 0); } }