/// <summary>
/// The GetProductExpendRate.
/// </summary>
/// <param name="category">The category<see cref="int?"/>.</param>
/// <param name="time">The time<see cref="DateTime?"/>.</param>
/// <returns>The <see cref="List{ProductExpendModel}"/>.</returns>
private static List <ProductExpendModel> GetProductExpendRate(int?category = null, DateTime?time = null)
{
List <ProductExpendModel> list = new List <ProductExpendModel>();
try
{
using (ERPDBEntities db = new ERPDBEntities())
{
if (category.HasValue)
{
var query = (from a in db.Product join b in db.Sell_Record on a.product_id equals b.product_id where a.product_category == category.Value select new { a.product_id, a.product_name, a.stock_count, b.sell_count, b.create_time }).ToList();
if (time.HasValue)
{
query = query.Where(a => a.create_time.ToString("yyyy-MM-dd") == time.Value.ToString("yyyy-MM-dd")).Select(a => a).ToList();
}
foreach (var item in query)
{
ProductExpendModel pro = new ProductExpendModel();
pro.product_id = item.product_id;
pro.product_name = item.product_name;
pro.stock_count = item.stock_count;
pro.sell_count = item.sell_count;
pro.expend_rate = (((double)item.sell_count / (double)item.stock_count) * 100).ToString("f2") + "%";
list.Add(pro);
}
List <ProductExpendModel> temp = new List <ProductExpendModel>();
temp.AddRange(list.OrderBy(a => a.sell_count).Take(5));
temp.AddRange(list.OrderByDescending(a => a.sell_count).Take(5));
return(temp.OrderBy(a => a.sell_count).ToList());
}
else
{
var query = (from a in db.Product join b in db.Sell_Record on a.product_id equals b.product_id select new { a.product_id, a.product_name, a.stock_count, b.sell_count, b.create_time }).ToList();
if (time.HasValue)
{
query = query.Where(a => a.create_time.ToString("yyyy-MM-dd") == time.Value.ToString("yyyy-MM-dd")).Select(a => a).ToList();
}
//var query = (from a in db.Product join b in db.Sell_Record on a.product_id equals b.product_id select new { a.product_id, a.product_name, a.stock_count, b.sell_count }).ToList();
foreach (var item in query)
{
ProductExpendModel pro = new ProductExpendModel();
pro.product_id = item.product_id;
pro.product_name = item.product_name;
pro.stock_count = item.stock_count;
pro.sell_count = item.sell_count;
pro.expend_rate = (((double)item.sell_count / (double)item.stock_count) * 100).ToString("f2") + "%";
list.Add(pro);
}
return(list.OrderBy(a => a.sell_count).ToList());
}
}
}
catch (Exception ex)
{
}
return(null);;
}
/// <summary>
/// The GetProductExpendRate.
/// </summary>
/// <param name="time">The time<see cref="DateTime?"/>.</param>
/// <returns>The <see cref="List{ProductExpendModel}"/>.</returns>
private static List <ProductExpendModel> GetProductExpendRate(DateTime?time = null)
{
List <ProductExpendModel> list = new List <ProductExpendModel>();
try
{
DateTime start_time = time.HasValue ? time.Value.Date : DateTime.Now.Date;
DateTime end_time = start_time.AddDays(1);
using (ERPDBEntities db = new ERPDBEntities())
{
var query = (from a in db.Product
join b in db.Sell_Record on a.product_id equals b.product_id
where b.create_time >= start_time && b.create_time < end_time
select new { a.product_id, a.product_name, a.stock_count, b.sell_count, b.create_time }).AsQueryable();
if (query.Any())
{
var product = query.GroupBy(a => new { a.product_id, a.product_name });
foreach (var item in product)
{
List <double> expend_rate = new List <double>();
foreach (var info in item)
{
double rate = ((double)info.sell_count / (double)info.stock_count) * 100;
expend_rate.Add(rate);
}
double average_expend_rate = expend_rate.Average();
ProductExpendModel pro = new ProductExpendModel();
pro.product_id = item.Key.product_id;
pro.product_name = item.Key.product_name;
pro.average_expend_rate = average_expend_rate;
pro.expend_rate = average_expend_rate.ToString("f2") + "%";
list.Add(pro);
}
}
return(list.OrderBy(a => a.average_expend_rate).ToList());
}
}
catch (Exception ex)
{
}
return(null);;
}
/// <summary>
/// 获取商品消耗率过高和过低的5个产品.
/// </summary>
/// <param name="category">.</param>
/// <param name="time">.</param>
/// <returns>.</returns>
private static ExpendModel GetProductExpendRate(int category, DateTime?time = null)
{
ExpendModel model = new ExpendModel();
try
{
DateTime start_time = time.HasValue ? time.Value.Date : DateTime.Now.Date;
DateTime end_time = start_time.AddDays(1);
using (ERPDBEntities db = new ERPDBEntities())
{
var query = (from a in db.Product
join b in db.Sell_Record on a.product_id equals b.product_id
where a.product_category == category && b.create_time >= start_time && b.create_time < end_time
select new { a.product_id, a.product_name, a.stock_count, b.sell_count, b.create_time }).AsQueryable();
if (query.Any())
{
var product = query.GroupBy(a => new { a.product_id, a.product_name });
foreach (var item in product)
{
List <double> expend_rate = new List <double>();
foreach (var info in item)
{
double rate = ((double)info.sell_count / (double)info.stock_count) * 100;
expend_rate.Add(rate);
}
double average_expend_rate = expend_rate.Average();
if (category == (int)Product_Category_Enum.RiPei)
{
//日配商品消化率过高，消化率90%，上下两个点
if (average_expend_rate >= SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.RiPei, (int)Enum_Product_Expend_Rate_Type.High_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.High_Rate.Add(high_model);
}
//日配商品消化率过低，消化率50%
if (average_expend_rate < SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.RiPei, (int)Enum_Product_Expend_Rate_Type.Low_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.Low_Rate.Add(high_model);
}
}
else if (category == (int)Product_Category_Enum.NoRiPei)
{
//非日配商品消化率过高，消化率30%，上下五个点
if (average_expend_rate >= SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.NoRiPei, (int)Enum_Product_Expend_Rate_Type.High_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.High_Rate.Add(high_model);
}
//非日配商品消化率过低，消化率10%
if (average_expend_rate < SystemCommon.GetExpendRate(item.Key.product_id, (int)Product_Category_Enum.NoRiPei, (int)Enum_Product_Expend_Rate_Type.Low_Expend_Rate))
{
ProductExpendModel high_model = new ProductExpendModel();
high_model.product_id = item.Key.product_id;
high_model.product_name = item.Key.product_name;
high_model.average_expend_rate = average_expend_rate;
high_model.expend_rate = average_expend_rate.ToString("f2") + "%";
model.Low_Rate.Add(high_model);
}
}
}
if (model.High_Rate.Any() && model.High_Rate.Count > 5)
{
model.High_Rate = model.High_Rate.OrderByDescending(a => a.average_expend_rate).Take(5).ToList();
}
if (model.Low_Rate.Any() && model.Low_Rate.Count > 5)
{
model.Low_Rate = model.Low_Rate.OrderByDescending(a => a.average_expend_rate).Take(5).ToList();
}
}
return(model);
}
}
catch (Exception ex)
{
}
return(null);
}
