/// <summary>
/// 删除GoodsId和Model满足对应的购物车
/// </summary>
/// <returns></returns>
public bool DeleteByGoodsIdModel(MemberCart mc)
{
string sql = "";
if (mc.Id > 0 && mc.GoodsId > 0 && mc.MemberId > 0)
{
sql += string.Format(" delete dbo.MemberCart where Id ={0} and memberId={1} and GoodsId ={2} and model ='{3}' ", mc.Id, mc.MemberId, mc.GoodsId, mc.Model);
}
return(m_dbo.ExecuteNonQuery(sql));
}
/// <summary>
/// 获取购物车中是否存在已添加过的商品(GoodsId和Model) quxiaoshan 2015-5-27
/// </summary>
/// <returns></returns>
public DataSet ReadCartByGoodsId(MemberCart memberCart)
{
string sql = string.Format("select ID, Num from dbo.MemberCart where 1=1 ");
if (memberCart.MemberId > 0)
{
sql += string.Format(" and MemberId={0} ", memberCart.MemberId);
}
if (memberCart.GoodsId > 0)
{
sql += string.Format(" and GoodsId ={0} ", memberCart.GoodsId);
}
//if (memberCart.Model != "")
//{
// sql += string.Format(" and Model= '{0}' ",memberCart.Model);
//}
sql += " order by Id desc ";
return(m_dbo.GetDataSet(sql));
}
/// <summary>
/// 更新商品数量,并且返回当期更新的购物车商品信息，cart.cshtml的加减数量操作 quxiaoshan 2015-5-29
/// **********************千万不要随便修改update的sql，不然数据库就都错了************************
/// </summary>
/// <returns></returns>
public DataRow UpdateCartGoodsNumById(MemberCart mc, int isvip)
{
string sql = "";
if (mc.Id > 0 && mc.GoodsId > 0 && mc.MemberId > 0)
{
sql = string.Format(@" update dbo.MemberCart set num={0} where 1=1 and Id ={1} and memberId={2} and GoodsId ={3} and model ='{4}' ;", mc.Num, mc.Id, mc.MemberId, mc.GoodsId, mc.Model);
if (isvip == 0)
{
sql += string.Format(" select dbo.Goods.Price as Price ,dbo.Goods.Rate as Rate,dbo.MemberCart.Id as MemberCartId ,* from MemberCart join dbo.Goods on dbo.MemberCart.GoodsId=dbo.Goods.ID where dbo.MemberCart.Id={0} and GoodsId={1} ", mc.Id, mc.GoodsId);
}
else if (isvip == 1)//专柜商品只插入了母商品，使用with子句关联到自己商品后，在和MemberCart中的购物车信息匹配
{
sql += string.Format(@" declare @discount decimal(18,2)= 1;
declare @counterId int =0;
select @discount=discount, @CounterId= CounterId from dbo.VIPCounterCompany where ComId= (select ComId from dbo.Member where Id ={0});
with CounterDetailContainParent as(
select dbo.Goods.ID as GoodsId,CounterId,VIPPrice from dbo.VIPCounterDetail
left join dbo.Goods on
dbo.VIPCounterDetail.GoodsId=dbo.Goods.ID or
dbo.VIPCounterDetail.GoodsId=dbo.Goods.ParentId
where dbo.VIPCounterDetail.CounterId =@CounterId
)
select dbo.MemberCart.*,dbo.MemberCart.Id as MemberCartId,CONVERT(decimal(18,2),@discount*VIPPrice) as VIPPrice,dbo.Goods.* from dbo.MemberCart
left join CounterDetailContainParent on dbo.MemberCart.GoodsId=CounterDetailContainParent.GoodsId
left join dbo.Goods on CounterDetailContainParent.GoodsId=dbo.Goods.ID
where dbo.MemberCart.Id={1} and dbo.MemberCart.GoodsId= {2} and MemberId={0} and
CounterId = @counterId
order by dbo.MemberCart.Id Desc ", mc.MemberId, mc.Id, mc.GoodsId);
}
}
DataSet ds = m_dbo.GetDataSet(sql);
DataRow row = null;
if (ds.Tables[0].Rows.Count > 0)
{
row = ds.Tables[0].Rows[0];
}
return(row);
}