public async Task <InventoryReportDto> GetReport(DateTime fromDate, DateTime toDate, ICollection <Guid> storageIds)
{
var inventoryReport = new InventoryReportDto
{
ReturnStorageInventoryList = new List <ReturnStorageInventoryDto>(),
StorageNameList = new List <string>(),
FromDate = fromDate,
ToDate = toDate
};
foreach (var storageId in storageIds)
{
var storage = await _context.Storages.SingleAsync(s => s.Id == storageId);
inventoryReport.StorageNameList.Add(storage.Name);
var inventoryList = await _entity.Where(b => b.StorageId == storageId &&
b.CreatedDateTime.Date >= fromDate.Date &&
b.CreatedDateTime.Date <= toDate.Date &&
b.Status != CONSTANT.INVENTORY_DESTROY
).Include(i => i.Storage).Include(i => i.BalanceUser).ToListAsync();
inventoryReport.InventoryAmount += inventoryList.Count;
foreach (var inventory in inventoryList)
{
inventoryReport.TotalDeviation += inventory.TotalDeviation;
inventoryReport.IncreaseDeviation += inventory.IncreaseDeviation;
inventoryReport.DecreaseDeviation += inventory.DecreaseDeviation;
if (inventory.Status == CONSTANT.INVENTORY_BANLANCED)
{
inventoryReport.BalanceInventoryAmount++;
}
else if (inventory.Status == CONSTANT.INVENTORY_INVENTORIED)
{
inventoryReport.InventoriedInventoryAmount++;
}
}
ReturnStorageInventoryDto storageWarehousingDto = new ReturnStorageInventoryDto
{
StorageName = storage.Name,
InventoryList = Mapper.Map <List <InventoryDto> >(inventoryList)
};
inventoryReport.ReturnStorageInventoryList.Add(storageWarehousingDto);
}
return(inventoryReport);
}
public static string InsertInventoryReport(InventoryReportDto report)
{
return($@"INSERT INTO INVENTORY_RAPORT (`ID`, `INVENTORY_ID`, `RAPORT`) VALUES (NULL, {report.InventoryId}, '{report.Raport}')");
}
public void InsertReport(InventoryReportDto raport)
{
NonResultQuerry(InventorySql.InsertInventoryReport(raport));
}
public static ExcelPackage Export(this InventoryReportDto inventoryReport, string worksheetName)
{
var excelPackage = new ExcelPackage();
var worksheet = excelPackage.Workbook.Worksheets.Add(worksheetName);
var headerRows = new List <string[]>
{
new string[] { "Код", "Наименование", "Кол-во, шт." }
};
var locations = inventoryReport.LocationGoods.Keys.OrderBy(x => x).ToList();
var multipleLocations = locations.Count > 1;
if (multipleLocations)
{
var headerRow = new List <string> {
"", "", "Всего"
};
locations.ForEach(l => headerRow.Add(l));
headerRows.Add(headerRow.ToArray());
}
var rangeEndLetter = multipleLocations
? char.ConvertFromUtf32(headerRows[1].Length + 64)
: "C";
var headerRange = $"A1:{rangeEndLetter}{(multipleLocations ? "2" : "1")}";
worksheet.Cells[headerRange].LoadFromArrays(headerRows);
worksheet.Cells[headerRange].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var totalRow = new List <string> {
"Итого:", ""
};
if (!multipleLocations)
{
var total = inventoryReport.LocationGoods.FirstOrDefault().Value.Sum(x => x.Quantity);
totalRow.Add(total.ToString());
}
else
{
var totals = inventoryReport.LocationGoods
.OrderBy(x => x.Key)
.Select(x => x.Value.Sum(y => y.Quantity))
.ToList();
totalRow.Add(totals.Sum().ToString());
totals.ForEach(x => totalRow.Add(x.ToString()));
}
var currentRow = multipleLocations ? 3 : 2;
worksheet.Cells[currentRow, 1].LoadFromArrays(new List <string[]> {
totalRow.ToArray()
});
currentRow += 1;
var articleTypes = inventoryReport.LocationGoods
.SelectMany(x => x.Value)
.GroupBy(x => x.ArticleType)
.Select(x => x.Key)
.OrderBy(x => x)
.ToList();
articleTypes.ForEach(type =>
{
worksheet.Cells[currentRow, 1].LoadFromArrays(new List <string[]> {
new string[] { type }
});
worksheet.Cells[currentRow, 1].Style.Font.Bold = true;
var rowRange = $"A{currentRow}:{rangeEndLetter}{currentRow}";
worksheet.Cells[rowRange].Merge = true;
worksheet.Cells[rowRange].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[rowRange].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(177, 213, 227));
currentRow += 1;
var articles = inventoryReport.LocationGoods
.SelectMany(x => x.Value)
.Where(x => x.ArticleType.Equals(type))
.GroupBy(x => new { x.ArticleId, x.ArticleCode, x.ArticleName })
.Select(x => x.Key)
.OrderBy(x => x.ArticleName)
.ToList();
articles.ForEach(article =>
{
var result = inventoryReport.LocationGoods
.OrderBy(x => x.Key)
.Select(x => x.Value.FirstOrDefault(v => v.ArticleId.Equals(article.ArticleId))?.Quantity ?? null)
.ToList();
var cells = new List <string> {
article.ArticleCode, article.ArticleName
};
if (!multipleLocations)
{
cells.Add(result.FirstOrDefault()?.ToString() ?? string.Empty);
}
else
{
cells.Add(result.Sum().ToString());
result.ForEach(r => cells.Add(r.ToString()));
}
worksheet.Cells[currentRow, 1].LoadFromArrays(new List <string[]> {
cells.ToArray()
});
currentRow += 1;
});
});
var totalRange = $"A1:{rangeEndLetter}{currentRow - 1}";
worksheet.Cells[totalRange].AutoFitColumns();
if (multipleLocations)
{
worksheet.Cells["A1:A2"].Merge = true;
worksheet.Cells["B1:B2"].Merge = true;
worksheet.Cells[$"C1:{rangeEndLetter}1"].Merge = true;
}
var totalRowNumber = multipleLocations ? 3 : 2;
var totalRowRange = $"A{totalRowNumber}:B{totalRowNumber}";
worksheet.Cells[totalRowRange].Merge = true;
worksheet.Cells[totalRowRange].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[$"A{totalRowNumber}:{rangeEndLetter}{totalRowNumber}"].Style.Font.Italic = true;
worksheet.Cells[totalRange].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[totalRange].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[totalRange].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[totalRange].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
return(excelPackage);
}
