/// <summary>
/// 查询列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public FuncResult Select(FactPMIXModel model)
{
var data = GetData(model);
return(new FuncResult()
{
IsSuccess = true, Content = new { data }
});
}
public IActionResult Export(DateTime?StartTime, DateTime?EndTime, string stRegion, string stCity, string stDM, string stStore)
{
var model = new FactPMIXModel()
{
stCity = stCity,
StartTime = StartTime,
EndTime = EndTime,
stDM = stDM,
stRegion = stRegion,
storeCode = stStore
};
var result = factPMIXBLL.GetPMIXListBytes(model);
return(File(result, "application/ms-excel", $"PMIX.xls"));
}
public FuncResult Select([FromBody] FactPMIXModel model)
{
//接受传递过来的参数处理.
return(factPMIXBLL.Select(model));
}
private IEnumerable <FactPMIXResultModel> GetData(FactPMIXModel model)
{
//total = 0;
//根据页码取出 当前页要展示的交易记录
DateTime _start, _end;
_start = model.StartTime != null ? (DateTime)
model.StartTime : Convert.ToDateTime((DateTime.Now.AddDays(-10)).ToShortDateString());
_end = model.EndTime != null ? (DateTime)
model.EndTime : Convert.ToDateTime((DateTime.Now.AddDays(-1)).ToShortDateString());
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
//先去主表里面查店铺信息
var stcodes = _context.OdsStoreMasters.Where(b => (string.IsNullOrWhiteSpace(model.stBrand) || b.Stbrand.Contains(model.stBrand)) &&
(string.IsNullOrWhiteSpace(model.stRegion) || b.Stregion.Contains(model.stRegion)) &&
(string.IsNullOrWhiteSpace(model.stCity) || b.Stcity.Contains(b.Stcity)) &&
(string.IsNullOrWhiteSpace(model.stDM) || b.Stdm.Contains(model.stDM)) &&
(string.IsNullOrWhiteSpace(model.storeCode) || b.Stcode.Contains(model.storeCode))).Select(e => e.Stcode).ToList();
//取数据
var query2 = (from a in _context.FactPMIXes
where a.trade_Date >= _start && a.trade_Date <= _end &&
(stcodes.Count <= 0 || stcodes.Contains(a.store_Code))
group a by new { a.product_Code, a.product_Name, a.product_Dishcolor, a.product_Type, a.product_Price, a.product_Cost_Price }
//, a.trade_Price
into g
select new
{
productCode = g.Key.product_Code,
product_Name = g.Key.product_Name,
product_Dishcolor = g.Key.product_Dishcolor,
product_Type = g.Key.product_Type,
product_Price = g.Key.product_Price,
product_Cost_Price = g.Key.product_Cost_Price,
ProductTotalTradeAmount = g.Sum(su => su.trade_Amount),
ProductTotalTradeAmountBelt = g.Sum(su => su.trade_Amount_Belt),
ProductTotalTradeAmountAlacart = g.Sum(su => su.trade_Amount_Alacart),
ProductTradeMoney = g.Sum(su => su.trade_Money),
ProductTradeMoneyBelt = g.Sum(su => su.trade_Money_Belt),
ProductTradeMoneyAlacart = g.Sum(su => su.trade_Money_Alacart),
CostMoney = g.Sum(su => su.cost_Money),
}).ToList();
decimal TotalTradeAmount = query2.Sum(c => c.ProductTotalTradeAmount);
decimal TotalTradeAmountBelt = query2.Sum(c => c.ProductTotalTradeAmountBelt);
decimal TotalTradeAmountAlacart = query2.Sum(c => c.ProductTotalTradeAmountAlacart);
decimal TotalTradeMoney = query2.Sum(c => c.ProductTradeMoney);
decimal TotalTradeMoneyBelt = query2.Sum(c => c.ProductTradeMoneyBelt);
decimal TotalTradeMoneyAlacart = query2.Sum(c => c.ProductTradeMoneyAlacart);
//var product_query = (from a in _context.DimProducts
// join b in query2 on a.code equals b.productCode
// select new
// {
// a.price,
// a.cost,
// a.name,
// b.productCode,
// b.ProductTotalTradeAmount,
// b.ProductTotalTradeAmountAlacart,
// b.ProductTotalTradeAmountBelt,
// b.ProductTradeMoney,
// b.ProductTradeMoneyBelt,
// b.ProductTradeMoneyAlacart
// }).ToList();
var result = query2.Select(a => new FactPMIXResultModel
{
productCode = a.productCode,
productName = a.product_Name,
dish_Color = a.product_Dishcolor,
category = a.product_Type,
price = a.product_Price,
cost = a.product_Cost_Price,
tradeAmount = a.ProductTotalTradeAmount.ToString("N"),
tradeAmountAlacart = a.ProductTotalTradeAmountAlacart,
tradeAmountBelt = a.ProductTotalTradeAmountBelt,
tradeMoney = a.ProductTradeMoney,
tradeMoneyAlacart = a.ProductTradeMoneyAlacart,
tradeMoneyBelt = a.ProductTradeMoneyBelt,
tradePercent = TotalTradeAmount == 0 ? "0%" : (Convert.ToDecimal((a.ProductTotalTradeAmount / TotalTradeAmount)) * 100).ToString("#0.00") + "%",
tradePercentBelt = TotalTradeAmountBelt == 0 ? "0%" : (Convert.ToDecimal((a.ProductTotalTradeAmountBelt / TotalTradeAmountBelt)) * 100).ToString("#0.00") + "%",
tradePercentAlacart = TotalTradeAmountAlacart == 0 ? "0%" : (Convert.ToDecimal((a.ProductTotalTradeAmountAlacart / TotalTradeAmountAlacart)) * 100).ToString("#0.00") + "%",
tradeMoneyPercent = TotalTradeMoney == 0 ? "0%" : (Convert.ToDecimal((a.ProductTradeMoney / TotalTradeMoney)) * 100).ToString("#0.00") + "%",
costMoney = a.CostMoney,
//【成本%】：计算公式：成本% =（该产品成本单价 /（产品销售单价/1.06））* 100
costPercent = a.product_Price == 0 ? "0%" : Convert.ToDecimal(((a.product_Cost_Price / (a.product_Price / Convert.ToDecimal(1.06))) * 100)).ToString("#0.00") + "%"
}).ToList();
stopwatch.Stop();
Console.WriteLine($"耗时{stopwatch.ElapsedMilliseconds}毫秒");
var addCount = new FactPMIXResultModel
{
productCode = "汇总：",
tradeAmount = TotalTradeAmount.ToString("N"),
tradeMoney = TotalTradeMoney,
tradeAmountBelt = TotalTradeAmountBelt,
tradeMoneyBelt = TotalTradeMoneyBelt,
tradeAmountAlacart = TotalTradeAmountAlacart,
tradeMoneyAlacart = TotalTradeMoneyAlacart,
//costMoney = ,
};
result.Add(addCount);
return(result);
}
/// <summary>
/// 导出按钮事件
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public byte[] GetPMIXListBytes(FactPMIXModel model)
{
var comlumHeadrs = new[] { "产品编号", "产品名称", "色碟", "类别", "价格", "成本单价", "售出数", "售出数%", "金额", "金额%", "Belt售出", "Belt售出数%", "Belt金额", "A-La-Cart售出", "A-La-Cart售出数%", "A-La-Cart金额", "成本金额", "成本" };
byte[] result;
//使用查询得出结果
var data = GetData(model);
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet1"); //Worksheet name
//First add the headers
worksheet.Cells["A1:J1"].Merge = true;
worksheet.Cells[1, 1].Value = "条件";
worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["K1:M1"].Merge = true;
worksheet.Cells[1, 11].Value = "Belt";
worksheet.Cells[1, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var fill1 = worksheet.Cells[1, 11].Style.Fill;
fill1.PatternType = ExcelFillStyle.Solid;
fill1.BackgroundColor.SetColor(Color.Yellow);
worksheet.Cells["N1:P1"].Merge = true;
worksheet.Cells[1, 14].Value = "A-La-Cart";
worksheet.Cells[1, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var fill = worksheet.Cells[1, 14].Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.Orange);
worksheet.Cells["Q1:R1"].Merge = true;
worksheet.Cells[1, 17].Value = "";
worksheet.Cells[1, 17].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
for (var i = 0; i < comlumHeadrs.Count(); i++)
{
worksheet.Cells[2, i + 1].Value = comlumHeadrs[i];
worksheet.Cells[2, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
}
//Add values
var j = 3;
// var chars = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
foreach (var obj in data)
{
var rt = obj.GetType();
var rp = rt.GetProperties();
worksheet.Cells["A" + j].Value = obj.productCode;
//worksheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
worksheet.Cells["A" + j].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells["B" + j].Value = obj.productName;
worksheet.Cells["C" + j].Value = obj.dish_Color;
worksheet.Cells["D" + j].Value = obj.category;
worksheet.Cells["E" + j].Value = obj.price;
worksheet.Cells["F" + j].Value = obj.cost;//成本单价
worksheet.Cells["G" + j].Value = obj.tradeAmount;
worksheet.Cells["H" + j].Value = obj.tradePercent;
worksheet.Cells["I" + j].Value = obj.tradeMoney;
//售出金额
worksheet.Cells["J" + j].Value = obj.tradeAmountBelt;
worksheet.Cells["K" + j].Value = obj.tradeAmountBelt;
worksheet.Cells["L" + j].Value = obj.tradePercentBelt;
worksheet.Cells["M" + j].Value = obj.tradeMoneyBelt;
worksheet.Cells["N" + j].Value = obj.tradeAmountAlacart;
worksheet.Cells["O" + j].Value = obj.tradePercentAlacart;
worksheet.Cells["P" + j].Value = obj.tradeMoneyAlacart;
worksheet.Cells["Q" + j].Value = obj.costMoney;
worksheet.Cells["R" + j].Value = obj.costPercent;
j++;
}
result = package.GetAsByteArray();
}
return(result);
}