/// <summary>
/// 修改商品
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public bool Update(UpdateGoodsDto dto)
{
using (var commodityDbContext = new CommodityDbContext())
{
dto.Price = System.Decimal.Round(System.Decimal.Floor(dto.Price * 100) / 100, 2);
//判断标签的id是否存在
if (commodityDbContext.LabelRepos.Where(o => dto.Tags.Contains(o.Id)).Count() != dto.Tags.Length)
{
throw new Exception("有标签不存在");
}
//修改Goods表
var entity = commodityDbContext.GoodsRepos.Where(o => o.Id == dto.Id).FirstOrDefault();
if (entity == null)
{
throw new Exception("商品不存在");
}
entity.Name = dto.Name;
entity.Pinyin = PinYin.ConvertCh(dto.Name).ToLower();
entity.Price = dto.Price;
entity.Describe = dto.Describe;
//删除label_goods表中所有与该商品有关的记录
commodityDbContext.Database.ExecuteSqlCommand(@"delete from label_goods where goods_id = @p0", dto.Id);
//往label_goods表中添加该商品的标签
foreach (var Tag in dto.Tags)
{
commodityDbContext.LabelGoodsRepos.Add(new LabelGoodsRepo()
{
Goods_id = entity.Id,
Label_id = Tag
});
}
return(commodityDbContext.SaveChanges() > 0);
}
}
/// <summary>
/// 搜索商品
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public List <GoodsRepo> GetListForSelect(SearchGoodsDto dto)
{
using (var commodityDbContext = new CommodityDbContext())
{
var result = new List <GoodsRepo>();
var name = new MySqlParameter();
var sel = "select goods.id,goods.number,goods.name,goods.pinyin,goods.price,goods.describe,goods.state,goods.createtime,goods.updatetime";
var from = " from goods";
var where = " where 1=1 ";
var order = $" order by goods.updatetime desc limit {(dto.PageIndex - 1) * dto.PageSize},{dto.PageSize}";
if (dto.Label_id != null)
{
from = from + @" inner join label_goods on goods.id = label_goods.goods_id ";
where = where + $" and label_goods.label_id={dto.Label_id}";
}
if (Enum.IsDefined(typeof(StateType), dto.State))
{
where = where + $" and goods.state={(int)dto.State}";
}
if (dto.Name != null)
{
dto.Name = dto.Name.Split(' ').Join("");//去空格
name = new MySqlParameter("@name", "%" + dto.Name + "%");
where = where + $" and (goods.number = @name or goods.name like @name or goods.pinyin like @name)";
}
//ef core
result = commodityDbContext.GoodsRepos.FromSql(sel + from + where + order, name).ToList();
//ef 6
//result = commodityDbContext.Database.SqlQuery<InquiryGoodsDto>(sel + from + where + order, name).ToList();
return(result);
}
}
/// <summary>
/// 删除商品标签
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete(int id)
{
using (var commodityDbContext = new CommodityDbContext())
{
var entity = commodityDbContext.LabelRepos.Where(o => o.Id == id).FirstOrDefault();
if (entity == null)
{
throw new Exception("标签不存在");
}
entity.IsDeleted = true;
return(commodityDbContext.SaveChanges() > 0);
}
}
/// <summary>
/// 查询商品标签
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public List <InquiryLabelDto> GetList()
{
using (var commodityDbContext = new CommodityDbContext())
{
//查询未被删除的商品标签
var result = commodityDbContext.LabelRepos.Where(o => o.IsDeleted == false)
.Select(o => new InquiryLabelDto()
{
Id = o.Id,
Name = o.Name,
}).ToList();
return(result);
}
}
/// <summary>
/// 新增商品标签
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public bool Create(String name)
{
using (var commodityDbContext = new CommodityDbContext())
{
//若重名，抛出异常
if (commodityDbContext.LabelRepos.Any(o => o.Name == name))
{
throw new Exception("标签已存在");
}
commodityDbContext.LabelRepos.Add(new LabelRepo()
{
Name = name,
});
return(commodityDbContext.SaveChanges() > 0);
}
}
/// <summary>
/// 删除商品
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete(int id)
{
using (var commodityDbContext = new CommodityDbContext())
{
//判断商品是否存在
if (!commodityDbContext.GoodsRepos.Any(o => o.Id == id))
{
throw new Exception("商品不存在");
}
//在Goods表里删除商品
commodityDbContext.Database.ExecuteSqlCommand(@"delete from goods where id = @p0", id);
//在label_goods表里删除记录
commodityDbContext.Database.ExecuteSqlCommand(@"delete from label_goods where goods_id = @p0", id);
return(true);
}
}
/// <summary>
/// 添加商品
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public bool Create(CreateGoodsDto dto)
{
using (var commodityDbContext = new CommodityDbContext())
{
var tran = commodityDbContext.Database.BeginTransaction();
try
{
dto.Price = System.Decimal.Round(System.Decimal.Floor(dto.Price * 100) / 100, 2);
//若商品编号重复，返回false
if (commodityDbContext.GoodsRepos.Any(o => o.Number == dto.Number))
{
throw new Exception("商品编号重复");
}
//判断标签的id是否存在
if (commodityDbContext.LabelRepos.Where(o => dto.Tags.Contains(o.Id)).Count() != dto.Tags.Length)
{
throw new Exception("有标签不存在");
}
GoodsRepo goods = new GoodsRepo()
{
Number = dto.Number,
Name = dto.Name,
Pinyin = PinYin.ConvertCh(dto.Name).ToLower(),
Price = dto.Price,
Describe = dto.Describe,
Updatetime = DateTime.Now
};
commodityDbContext.GoodsRepos.Add(goods);
commodityDbContext.SaveChanges();
//往label_goods表中添加该商品的标签
foreach (var Tag in dto.Tags)
{
commodityDbContext.LabelGoodsRepos.Add(new LabelGoodsRepo()
{
Goods_id = goods.Id,
Label_id = Tag
});
}
return(commodityDbContext.SaveChanges() > 0);
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
}
/// <summary>
/// 修改商品标签
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public bool Update(UpdateLabelDto dto)
{
using (var commodityDbContext = new CommodityDbContext())
{
//若重名，抛出异常
if (commodityDbContext.LabelRepos.Any(o => o.Name == dto.Name))
{
throw new Exception("标签名重复");
}
var entity = commodityDbContext.LabelRepos.Where(o => o.Id == dto.Id).FirstOrDefault();
if (entity == null)
{
throw new Exception("未选择标签");
}
entity.Name = dto.Name;
return(commodityDbContext.SaveChanges() > 0);
}
}
/// <summary>
/// 上架商品
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public bool UpStack(int[] ids)
{
using (var commodityDbContext = new CommodityDbContext())
{
var entitysCount = commodityDbContext.GoodsRepos.Where(o => ids.Contains(o.Id)).Count();
if (entitysCount != ids.Length)
{
throw new Exception("商品不存在");
}
var update = $"UPDATE goods SET state= {StateType.Putaway} WHERE id IN(0";
foreach (var id in ids)
{
update = update + $",{id}";
}
update = update + ")";
commodityDbContext.Database.ExecuteSqlCommand(update);
return(true);
}
}
/// <summary>
/// 查询商品详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public GoodsDetailDto GetDetailById(int id)
{
using (var commodityDbContext = new CommodityDbContext())
{
//获取商品信息
var entity = commodityDbContext.GoodsRepos.Where(o => o.Id == id).Select(o => new GoodsDetailDto()
{
Id = o.Id,
Number = o.Number,
Name = o.Name,
Price = o.Price,
Describe = o.Describe,
State = o.State,
Createtime = o.Createtime,
Updatetime = o.Updatetime
}).FirstOrDefault();
if (entity == null)
{
throw new Exception("未选择商品");
}
//获取该商品的标签id
var results = commodityDbContext.LabelGoodsRepos.Where(o => o.Goods_id == id).Select(o => new LabelGoodsDto()
{
Goods_id = o.Goods_id,
Label_id = o.Label_id
}).ToList();
//获取标签名
var str = new List <string>();
foreach (var result in results)
{
var temp = commodityDbContext.LabelRepos.Where(o => o.Id == result.Label_id).Select(o => new InquiryLabelDto()
{
Id = o.Id,
Name = o.Name
}).FirstOrDefault();
str.Add(temp.Name);
}
entity.Tags = str;
return(entity);
}
}
