public ModuleShopProductValueItem GetListValueByRequest(HttpRequestBase httpRequest, int agecncy, out decimal?total, out decimal?quantity)
{
Request = new ParramRequest(httpRequest);
var isValue = httpRequest["IsValue"] ?? "1";
var query = (from o in FDIDB.sp_GetListInventoryValue(int.Parse(isValue), agecncy)
orderby o.ID descending
select new ShopProductValueItem
{
ID = o.ID,
Name = o.Name,
UnitName = o.Unitname,
Quantity = o.slNhap,
QuantityOut = o.slXuat,
QuantityInv = o.tonkho
}).ToList();
var model = new ModuleShopProductValueItem
{
ListItems = query.ToList(),
};
//query = query.SelectByRequest(Request);
//total = query.Sum(m => (m.Quantity - (m.QuantityOut ?? 0)) * m.Price);
total = 0;
quantity = query.Sum(m => m.QuantityInv);
//query = query.SelectPageByRequest(Request, ref TotalRecord);
return(model);
}
public static void ExportListValueDetail(string filePath, ModuleShopProductValueItem lst, string date)
{
var newFile = new FileInfo(filePath);
using (var xlPackage = new ExcelPackage(newFile))
{
var worksheet = xlPackage.Workbook.Worksheets.Add("LSGD");
xlPackage.Workbook.CalcMode = ExcelCalcMode.Manual;
var properties = new[]
{
"STT",
"Tên nguyên liệu",
"Đơn vị",
"Số lượng",
"Đã Xuất",
};
worksheet.Cells["A1:H1"].Value = "DANH SÁCH NGUYÊN LIỆU SỬ DỤNG TRONG NGÀY " + DateTime.Parse(date).ToString("dd/MM/yyyy");
worksheet.Cells["A1:H1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A1:H1"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells["A1:H1"].Style.Font.Bold = true;
worksheet.Cells["A1:H1"].Style.Font.Size = 16;
worksheet.Cells["A1:H1"].Merge = true;
worksheet.Cells["A1:H1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var row = 6;
for (var i = 0; i < properties.Length; i++)
{
worksheet.Cells[row, i + 1].Value = properties[i];
worksheet.Cells[row, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, i + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
worksheet.Cells[row, i + 1].Style.Font.Bold = true;
worksheet.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
worksheet.Cells[row, 1, row, properties.Length].AutoFilter = true;
row++;
var stt = 1;
foreach (var item in lst.ListItems)
{
var ex = lst.LstImportItem.Where(c => c.ValueId == item.ID).ToList();
var col = 1;
worksheet.Cells[row, col].Value = stt++;
worksheet.Cells[row, col].Style.Numberformat.Format = "0";
worksheet.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells[row, col++].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[row, col].Value = item.Name;
worksheet.Cells[row, col].Style.Numberformat.Format = "@";
worksheet.Cells[row, col++].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells[row, col].Value = item.UnitName;
worksheet.Cells[row, col].Style.Numberformat.Format = "@";
worksheet.Cells[row, col++].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells[row, col].Value = item.Quantity.Quantity("0:0.####");
worksheet.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells[row, col++].Style.Numberformat.Format = "@";
worksheet.Cells[row, col].Value = ex.Sum(c => c.Quantity).Quantity("0:0.####");
worksheet.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells[row, col].Style.Numberformat.Format = "@";
row++;
}
var nameexcel = "Danh sách nguyên liệu sử dụng ngày" + DateTime.Parse(date).ToString("dd/MM/yyyy");
xlPackage.Workbook.Properties.Title = string.Format("{0} reports", nameexcel);
xlPackage.Workbook.Properties.Author = "Admin-IT";
xlPackage.Workbook.Properties.Subject = "reports";
xlPackage.Workbook.Properties.Category = "Report";
xlPackage.Workbook.Properties.Company = "SSC";
xlPackage.Save();
}
}