/// <summary>
/// 商品查询显示分页
/// </summary>
/// <param name="goodsName"></param>
/// <param name="goodsDegree"></param>
/// <param name="priceNow"></param>
/// <param name="brandName"></param>
/// <param name="placeName"></param>
/// <param name="aromaName"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public GoodsAllListPage GetGoodsAll(string goodsName, string goodsDegree, int priceNow, string brandName, string placeName, string aromaName, int typeId, int brandId, int pageIndex, int pageSize)
{
GoodsAllListPage goodsAllListPage = new GoodsAllListPage();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
var orderByPriceSql = "";
string sql = "select Goods.Id,Brand.Id as BrandId,GoodsType.Id as GoodsTypeId,GoodsName,PriceNow,GoodsImg ,BrandName, PlaceName,AromaName ,TypeName ,SpecificationsName from Goods inner join Price on Goods.Id=Price.GoodsId inner join Brand on Brand.GoodsId = Goods.Id inner join Place on Place.GoodsId = Goods.Id inner join Aroma on Aroma.GoodsId = Goods.Id inner join GoodsType on Place.GoodsTypeId = GoodsType.Id inner join specifications on Specifications.GoodsId=Goods.Id where 1 =1";
string sqlCount =
"select count(Goods.Id) from Goods inner join Price on Goods.Id=Price.GoodsId inner join Brand on Goods.Id = Brand.GoodsId inner join Place on Goods.Id = Place.GoodsId inner join Aroma on Goods.Id = Aroma.GoodsId inner join GoodsType on Place.GoodsTypeId = GoodsType.Id inner join specifications on Specifications.GoodsId=Goods.Id where 1=1";
if (!string.IsNullOrWhiteSpace(goodsName))
{
sql = sql + $" and GoodsName like '%{goodsName}%'";
sqlCount = sqlCount + $" and GoodsName like '%{goodsName}%'";
}
if (!string.IsNullOrWhiteSpace(goodsDegree))
{
sql = sql + $" and GoodsDegree like '%{goodsDegree}%'";
sqlCount = sqlCount + $" and GoodsDegree like '%{goodsDegree}%'";
}
if (!string.IsNullOrWhiteSpace(brandName))
{
sql = sql + $" and BrandName like '%{brandName}%'";
sqlCount = sqlCount + $" and BrandName like '%{brandName}%'";
}
if (!string.IsNullOrWhiteSpace(placeName))
{
sql = sql + $" and PlaceName like '%{placeName}%'";
sqlCount = sqlCount + $" and PlaceName like '%{placeName}%'";
}
if (brandId != 0)
{
sql = sql + $" and Brand.Id={brandId}";
sqlCount = sqlCount + $" and Brand.Id={brandId}";
}
if (typeId != 0)
{
sql = sql + $" and GoodsType.Id={typeId}";
sqlCount = sqlCount + $" and GoodsType.Id={typeId}";
}
switch (priceNow)
{
case 123: orderByPriceSql += "select * from(" + sql + ") t order by PriceNow desc"; break;
case 456: orderByPriceSql += "select * from(" + sql + ") t order by PriceNow"; break;
case 789: orderByPriceSql += sql; break;
}
orderByPriceSql = orderByPriceSql + $" limit {(pageIndex - 1) * pageSize},{pageSize}";
var result = conn.Query <GoodsAll>(orderByPriceSql).ToList();
goodsAllListPage.GoodsAll = result;
goodsAllListPage.Total = conn.ExecuteScalar <int>(sqlCount);
return(goodsAllListPage);
}
}
/// <summary>
/// 数据删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DeleteGoods(int id)
{
GoodsAllListPage goodsAllListPage = new GoodsAllListPage();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
string sql = "delete from Goods where Id=" + id;
int i = conn.Execute(sql, null);
return(i);
}
}
