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