public Prod.Item ProdItemGet(int id, bool includeSkus = false)
{
if (includeSkus)
{
var sql = @"select * from item where id=@id;select * from sku where itemid=@id;";
var cmd = SqlBuilder.Raw(sql, new { id }).ToCommand();
using (var reader = StoreConn.QueryMultiple(cmd))
{
var o = reader.Read <Prod.Item>().FirstOrDefault();
if (o != null)
{
o.Skus = reader.Read <Prod.Sku>().ToList();
}
return(o);
}
}
else
{
var cmd = SqlBuilder
.Select("*").From("item")
.Where("id=@id", new { id })
.ToCommand();
return(StoreConn.QueryFirstOrDefault <Prod.Item>(cmd));
}
}
public IPagedList <Prod.Item> ProdItemSearch(ProdItemSearchParam param)
{
var sb = SqlBuilder
.Select("*")
.From("item");
if (param.UserId.HasValue)
{
sb.Where("userid=@userid", new { param.UserId });
}
if (param.Id > 0)
{
sb.Where("Id=@id", new { param.Id });
}
if (!string.IsNullOrEmpty(param.Name))
{
sb.Where("Name LIKE @Name", new { name = param.Name + "%" });
}
if (!string.IsNullOrEmpty(param.Code))
{
sb.Where("Code=@code", new { param.Code });
}
if (param.CRId > 0)
{
sb.Where("CatgRId=@CatgRId", new { CatgRId = param.CRId });
}
if (param.PriceFrom > 0)
{
sb.Where("Price>=@PriceFrom", new { param.PriceFrom });
}
if (param.PriceEnd > 0)
{
sb.Where("Price<=@PriceEnd", new { param.PriceEnd });
}
if (param.SaleFrom > 0)
{
sb.Where("SaleCount>=@SaleFrom", new { param.SaleFrom });
}
if (param.SaleEnd > 0)
{
sb.Where("SaleCount>=@SaleEnd", new { param.SaleEnd });
}
if (param.OrderBy != null && param.OrderBy.Count > 0)
{
var tmp = new StringBuilder();
foreach (var o in param.OrderBy)
{
tmp.AppendFormat("[{0}] {1},", o.Key, o.Value ? "ASC" : "DESC");
}
sb.OrderBy(sb.ToString().TrimEnd(','));
}
else
{
sb.OrderBy("Id DESC");
}
var cmd = sb.ToCommand(param.PageIndex, param.PageSize);
return(StoreConn.PagedList <Prod.Item>(param.PageIndex, param.PageSize, cmd));
}
public IList <Prod.Sku> ProdSkuList(int itemId)
{
var cmd = SqlBuilder
.Select("*").From("sku")
.Where("ItemId=@ItemId", new { itemId })
.ToCommand();
return(StoreConn.Query <Prod.Sku>(cmd).ToList());
}
public Prod.Sku ProdSkuGet(int id)
{
var cmd = SqlBuilder
.Select("*").From("sku")
.Where("id=@id", new { id })
.ToCommand();
return(StoreConn.QueryFirstOrDefault <Prod.Sku>(cmd));
}
public Prod.ItemDesc ProdItemDescGet(int id)
{
var cmd = SqlBuilder
.Select("*").From("itemdesc")
.Where("id=@id", new { id })
.ToCommand();
return(StoreConn.QueryFirstOrDefault <Prod.ItemDesc>(cmd));
}
public User UserGet(int id)
{
var cmd = SqlBuilder
.Select("*").From("user")
.Where("id=@id", new { id })
.ToCommand();
return(StoreConn.QueryFirstOrDefault <User>(cmd));
}
public Prod.Mini ProdMiniGet(int id)
{
var sql = @"select id,spuid,userid,catgid,catgrid,code,name,title,stock,price,appprice,retailprice,barcode,brandid,brandname,keyword,propid,propstr,inputid,inputstr,summary,picture,ItemImgStr,PropImgStr,status from item where id=@id;
select id,code,barcode,stock,price,propstr,appprice,propid,propstr,status from sku where itemid=@id;";
var cmd = SqlBuilder.Raw(sql, new { id }).ToCommand();
using (var reader = StoreConn.QueryMultiple(cmd))
{
var o = reader.Read <Prod.Mini>().FirstOrDefault();
if (o != null)
{
o.Skus = reader.Read <Prod.Mini.Sku>().ToList();
}
return(o);
}
}
/*
* /// <summary>
* /// 创建店铺优惠活动规则
* /// </summary>
* /// <param name="o">店铺优惠活动规则</param>
* /// <returns></returns>
* public bool PromotionDefaultRuleCreate(Promotion.Default.Rule o)
* {
* try
* {
* var cmd = SqlBuilder
* .Insert("[Default.Rule]")
* .Column("ParentId", o.ParentId)
* .Column("Upon", o.Upon)
* .Column("Value", o.Value)
* .Column("SendGift", o.SendGift)
* .Column("GiftJson", o.GiftData)
* .Column("SendCoupon", o.SendCoupon)
* .Column("CouponJson", o.CouponData)
* .Column("SellerId", o.SellerId)
* .Column("Status", o.Status)
* .Column("CreatedBy", o.CreatedBy)
* .Column("CreatedOn", o.CreatedOn)
* .ToCommand();
* return PromotionConn.Execute(cmd) > 0;
* }
* catch (Exception e)
* {
* OnException(e);
* }
* return false;
* }
*
* /// <summary>
* /// 更新店铺优惠活动规则
* /// </summary>
* /// <param name="o">店铺优惠活动规则</param>
* /// <returns></returns>
* public bool PromotionDefaultRuleUpdate(Promotion.Default.Rule o)
* {
* try
* {
* var cmd = SqlBuilder
* .Update("[Default.Rule]")
* .Column("Upon", o.Upon)
* .Column("Value", o.Value)
* .Column("SendGift", o.SendGift)
* .Column("GiftJson", o.GiftData)
* .Column("SendCoupon", o.SendCoupon)
* .Column("CouponJson", o.CouponData)
* .Column("Status", o.Status)
* .Where("Id=@Id", new { o.Id })
* .ToCommand();
* return PromotionConn.Execute(cmd) > 0;
* }
* catch (Exception e)
* {
* OnException(e);
* }
* return false;
* }
*
* /// <summary>
* /// 获取店铺优惠活动规则
* /// </summary>
* /// <param name="id">店铺优惠活动规则Id</param>
* /// <param name="sellerId">卖家Id</param>
* /// <returns></returns>
* public Promotion.Default.Rule PromotionDefaultRuleGet(int id, int? sellerId = null)
* {
* try
* {
* var cmd = SqlBuilder
* .Select("*")
* .From("[Default.Rule]")
* .Where("Id=@Id", new { id })
* .Where(sellerId.HasValue, "SellerId=@sellerId", new { sellerId })
* .ToCommand();
* return PromotionConn.Query<Promotion.Default.Rule>(cmd).FirstOrDefault();
* }
* catch (Exception e)
* {
* OnException(e);
* }
* return null;
* }
*
* /// <summary>
* /// 设置店铺优惠活动规则为不可用
* /// </summary>
* /// <param name="id">店铺优惠活动规则Id</param>
* /// <param name="sellerId">卖家Id</param>
* /// <returns></returns>
* public bool PromotionDefaultRuleDisable(int id, int? sellerId = null)
* {
* var sql = "update [Default.Rule] set [Status]=0 where Id=@Id";
* if (sellerId.HasValue)
* {
* sql = "update [Default.Rule] set [Status]=0 where Id=@Id and Seller=@SellerId";
* }
* var cmd = SqlBuilder.Raw(sql).Append(sellerId.HasValue, "", new { sellerId }).ToCommand();
* try
* {
* return PromotionConn.Execute(cmd) > 0;
* }
* catch (Exception e)
* {
* OnException(e);
* }
* return false;
* }
*/
#endregion
/// <summary>
/// 获取搭配组合套餐
/// </summary>
/// <param name="id">搭配组合套餐Id</param>
/// <param name="includeItems">是否包含套餐附属商品信息</param>
/// <returns></returns>
public Promotion.Package PromotionPackageGet(int id, bool includeItems = false)
{
if (includeItems)
{
const string sql = @"select * from Package where Id=@id;select * from PackageItem where ParentId=@id";
var cmd = SqlBuilder.Raw(sql, new { id }).ToCommand();
return
(StoreConn.Query <Promotion.Package, Promotion.Package.Item, int>(cmd, o => o.Id,
o => o.ParentId, (a, b) =>
{
a.Items = b.ToList();
}).FirstOrDefault());
}
else
{
var cmd = SqlBuilder.Select("*")
.From("Package")
.Where("Id=@id", new { id })
.ToCommand();
return(PromotionConn.Query <Promotion.Package>(cmd).FirstOrDefault());
}
}
