public static void GenerateContractorSalesReport(List<Document> documents, ExcelReportInfo reportInfo)
{
SheetStyle style = InitializeWorkbookAndGenerateStandartHeader(reportInfo);
foreach (Document document in documents)
{
GenerateDocumentInfoRow(document, style);
CreateProductLineTable(style, document.ProductsFilteredList, false);
string pathToFile = Path.Combine(reportInfo.FilePath, reportInfo.FileName);
WriteToFile(style.Workbook, pathToFile);
}
}
public string GetRefundsReportFileLink(DateTime refundsReportDate, int contractorId, int managerId)
{
string relativePath = "../" + Constants.RefundsReportPath;
string rootPath = Server.MapPath(relativePath);
string reportFile = String.Empty;
// Check if file already exists.
// Than check Modified date of the file in file name
string docPath = Path.Combine(rootPath, contractorId.ToString(), refundsReportDate.ToString("yyyy-MM-dd"));
if(!Directory.Exists(docPath))
Directory.CreateDirectory(docPath);
string dateSeparator = "-";
string extension = ".xls";
string mask = String.Format("{0}*{1}", Constants.RefundsReportPrefix, extension);
var directory = new DirectoryInfo(docPath);
// get last created file in directory
var existingFile = directory.GetFiles(mask).OrderByDescending(f => f.LastWriteTime).FirstOrDefault();
// check if file is actual upon document modified date
if (existingFile != null)
{
// Cache 1 minute
if (existingFile.CreationTime.AddSeconds(10) > DateTime.Now)
{
// return cached file
return String.Format("{0}/{1}/{2}/{3}", relativePath, contractorId.ToString(), refundsReportDate.ToString("yyyy-MM-dd"),
existingFile.Name);
}
else
{
// delete outdate file
existingFile.Delete();
}
}
var context = new SkladDataContext();
// Get refunds products by day
var products = GetRefundsProductLines(context, refundsReportDate, contractorId, managerId);
if (products.Count() > 0)
{
string fileName = String.Format("{0}{1}{4}{2}{4}{3}{5}", Constants.RefundsReportPrefix,
refundsReportDate.Year, refundsReportDate.ToString("MM"), refundsReportDate.ToString("dd"),
dateSeparator, extension);
string clientName = String.Empty;
if (contractorId > 0)
{
var contractor = context.Contractors.Where(x => x.ContractorId == contractorId).FirstOrDefault();
if (contractor != null)
clientName = contractor.Code;
}
else
clientName = "По всем клиентам";
string titleRight = "Возврат от " + refundsReportDate.ToString("dd.MM.yyyy");
if (managerId > 0)
{
var user = context.UserProfiles.Where(x => x.UserId == managerId).FirstOrDefault();
if (user != null)
titleRight += ". Менеджер: " + user.DisplayName;
}
// create report
ExcelReportInfo reportInfo = new ExcelReportInfo
{
CreatedOf = refundsReportDate,
FileName = fileName,
FilePath = docPath,
DocumentSubject = "Возврат от " + refundsReportDate.ToString("dd.MM.yyyy"),
SheetName = Constants.RefundsReportPrefix + refundsReportDate.ToString("dd.MM.yyyy"),
TitleLeft = clientName,
TitleCenter = refundsReportDate.ToString("dd.MM.yyyy"),
TitleRight = titleRight
};
ReportHelper.GenerateProductLinesReport(products.ToList(), reportInfo);
// ../Reports/Document/1093/e4fmt/Report-2014-10-09.xls
// /Reports/Refunds/RefundsReport-2014-10-09.xls
reportFile = String.Format("{0}/{1}/{2}/{3}", relativePath, contractorId.ToString(), refundsReportDate.ToString("yyyy-MM-dd"),
fileName);
}
return reportFile;
}
// TODO: create ajax method which creates datacontextModel
private string GetReportFile(SkladDataContext datacontextModel,
Document document,
EntityEnum.ReportType type)
{
if (document == null)
{
logger.ErrorFormat("GetReportFile. Document is null");
throw new ArgumentException("GetReportFile. Document is null");
}
bool saveChanges = false;
// check for folder names
if (String.IsNullOrEmpty(document.SecureFolderName))
{
document.SecureFolderName = GenerateFolderName(document.CommonFolderName);
saveChanges = true;
}
if (String.IsNullOrEmpty(document.CommonFolderName))
{
document.CommonFolderName = GenerateFolderName(document.SecureFolderName);
saveChanges = true;
}
if (saveChanges)
datacontextModel.SaveChanges();
string relativePath = "../" + Constants.DocumentReportPath;
string rootPath = Server.MapPath(relativePath);
string reportFile = String.Empty;
if (type == EntityEnum.ReportType.SaleReport)
{
// Check if file already exists.
// Than check Modified date of the file in file name
string docPath = Path.Combine(rootPath, document.DocumentId.ToString(), document.SecureFolderName);
if(!Directory.Exists(docPath))
Directory.CreateDirectory(docPath);
string dateSeparator = "-";
string extension = ".xls";
string mask = String.Format("{0}*{1}", Constants.DocumentReportPrefix, extension);
var directory = new DirectoryInfo(docPath);
// get last created file in directory
var existingFile = directory.GetFiles(mask).OrderByDescending(f => f.LastWriteTime).FirstOrDefault();
if (existingFile != null && !document.IsReportOutdated)
{
// check if file is actual upon document modified date
reportFile = String.Format("{0}/{1}/{2}/{3}", relativePath,
document.DocumentId.ToString(), document.SecureFolderName, existingFile.Name);
}
else
{
string fileName = String.Format("{0}{1}{4}{2}{4}{3}{5}", Constants.DocumentReportPrefix,
DateTimeOffset.Now.Year, DateTimeOffset.Now.ToString("MM"), DateTimeOffset.Now.ToString("dd"),
dateSeparator, extension);
// create report
ExcelReportInfo reportInfo = new ExcelReportInfo
{
CreatedOf = document.CreatedOf,
FileName = fileName,
FilePath = docPath,
DocumentSubject = "Отчёт №" + document.Number + " от " + document.CreatedOf,
SheetName = "Report-" + document.CreatedOf.ToString("dd.MM.yyyy"),
TitleLeft = (document.Contractor != null) ? document.Contractor.Code : String.Empty,
TitleCenter = document.CreatedOf.ToString("dd.MM.yyyy"),
TitleRight = "Док. №" + document.Number + " от " + document.CreatedOf.ToString("dd.MM.yyyy")
};
ReportHelper.GenerateProductLinesReport(document.Products, reportInfo);
document.IsReportOutdated = false;
datacontextModel.SaveChanges();
reportFile = String.Format("{0}/{1}/{2}/{3}", relativePath,
document.DocumentId.ToString(), document.SecureFolderName, fileName);
}
}
return reportFile;
}
private string GetSaleReportByContractorFile(Contractor contractor,
List<Document> documents,
int grandTotalQuantity, DateTime? periodFrom, DateTime? periodTo)
{
string relativePath = "../" + Constants.SalesByContractorReportPath;
string rootPath = Server.MapPath(relativePath);
string reportFile = String.Empty;
// Check if file already exists.
// Than check Modified date of the file in file name
string docPath = Path.Combine(rootPath, contractor.ContractorId.ToString());
if (!Directory.Exists(docPath))
Directory.CreateDirectory(docPath);
// string dateSeparator = "-";
string extension = ".xls";
string mask = String.Format("{0}*{1}", Constants.SalesByContractorReportPrefix, extension);
var directory = new DirectoryInfo(docPath);
// get last created file in directory
var existingFile = directory.GetFiles(mask).OrderByDescending(f => f.LastWriteTime).FirstOrDefault();
// check if file is actual upon document modified date
if (existingFile != null)
{
// Cache 1 minute
if (existingFile.CreationTime.AddSeconds(10) > DateTime.Now)
{
// return cached file
return String.Format("{0}/{1}/{2}", relativePath, contractor.ContractorId.ToString(),
existingFile.Name);
}
else
{
// delete outdate file
existingFile.Delete();
}
}
string fileName = String.Format("{0}{1}", Constants.SalesByContractorReportPrefix,
extension);
// create report
ExcelReportInfo reportInfo = new ExcelReportInfo
{
CreatedOf = DateTime.Now,
FileName = fileName,
FilePath = docPath,
DocumentSubject = "Список документов для " + contractor.Name + "(" + contractor.Code + ")",
SheetName = Constants.SalesByContractorReportPrefix,
TitleLeft = contractor.Code,
TitleCenter = (periodFrom != null ? "За период "+periodFrom.Value.ToString("dd.MM.yyyy") : "")
+ (periodTo != null ? "-" + periodTo.Value.ToString("dd.MM.yyyy") : "") + " Общее кол-во: " + grandTotalQuantity,
TitleRight = ""
};
ReportHelper.GenerateContractorSalesReport(documents, reportInfo);
// ../Reports/Document/1093/e4fmt/Report-2014-10-09.xls
// /Reports/Refunds/RefundsReport-2014-10-09.xls
reportFile = String.Format("{0}/{1}/{2}", relativePath, contractor.ContractorId.ToString(),
fileName);
return reportFile;
}
private static SheetStyle InitializeWorkbookAndGenerateStandartHeader(ExcelReportInfo reportInfo)
{
SheetStyle result = new SheetStyle();
result.Workbook = InitializeWorkbook(reportInfo.DocumentSubject);
result.Sheet = result.Workbook.CreateSheet(reportInfo.SheetName);
result.Sheet.SetColumnWidth(0, 5000);
result.StyleBold = result.Workbook.CreateCellStyle();
result.Font = result.Workbook.CreateFont();
result.Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
result.StyleBold.SetFont(result.Font);
result.StyleAlignCenter = CreateTableStyle(result.Workbook, true, false);
result.StyleTableBold = CreateTableStyle(result.Workbook, true, true);
result.StyleTable = CreateTableStyle(result.Workbook, true, false);
result.StyleLeftBottom = CreateLeftBottomBorderStyle(result.Workbook);
result.FirstRow = result.Sheet.CreateRow(0);
if (!String.IsNullOrEmpty(reportInfo.TitleLeft))
{
ICell contractorCodeCell = result.FirstRow.CreateCell(0);
contractorCodeCell.CellStyle = result.StyleBold;
contractorCodeCell.SetCellValue(reportInfo.TitleLeft);
contractorCodeCell.RichStringCellValue.ApplyFont(0, reportInfo.TitleLeft.Length, result.Font);
contractorCodeCell.CellStyle = result.StyleBold;
}
if (!String.IsNullOrEmpty(reportInfo.TitleCenter))
{
ICell dateCreatedCell = result.FirstRow.CreateCell(1);
dateCreatedCell.SetCellValue(reportInfo.TitleCenter);
}
if (!String.IsNullOrEmpty(reportInfo.TitleRight))
result.FirstRow.CreateCell(3).SetCellValue(reportInfo.TitleRight);
CreateHeader(result.Workbook, result.Sheet, 2);
result.CurrentRowNum = 3;
return result;
}
/// <summary>
/// Отчёт по товарам
/// </summary>
/// <param name="products"></param>
/// <param name="createdOf"></param>
/// <param name="subject"></param>
/// <param name="path"></param>
/// <param name="fileName"></param>
public static void GenerateProductLinesReport(ICollection<ProductLine> products, ExcelReportInfo reportInfo)
{
SheetStyle style = InitializeWorkbookAndGenerateStandartHeader(reportInfo);
//InitializeWorkbook(reportInfo.DocumentSubject);
//ISheet sheet1 = hssfworkbook.CreateSheet(reportInfo.SheetName);
//sheet1.SetColumnWidth(0, 5000);
//ICellStyle styleBold = hssfworkbook.CreateCellStyle();
////create a font style
//IFont font = hssfworkbook.CreateFont();
//font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
//styleBold.SetFont(font);
//ICellStyle styleAlignCenter = CreateTableStyle(true, false);
//ICellStyle styleTableBold = CreateTableStyle(false, true);
//ICellStyle styleTable = CreateTableStyle(false, false);
//ICellStyle styleLeftBottom = CreateLeftBottomBorderStyle();
//IRow row1 = sheet1.CreateRow(0);
//if (!String.IsNullOrEmpty(reportInfo.TitleLeft))
//{
// ICell contractorCodeCell = row1.CreateCell(0);
// contractorCodeCell.CellStyle = styleBold;
// contractorCodeCell.SetCellValue(reportInfo.TitleLeft);
// contractorCodeCell.RichStringCellValue.ApplyFont(0, reportInfo.TitleLeft.Length, font);
// contractorCodeCell.CellStyle = styleBold;
//}
//if (!String.IsNullOrEmpty(reportInfo.TitleCenter))
//{
// ICell dateCreatedCell = row1.CreateCell(1);
// dateCreatedCell.SetCellValue(reportInfo.TitleCenter);
//}
//if (!String.IsNullOrEmpty(reportInfo.TitleRight))
// row1.CreateCell(3).SetCellValue(reportInfo.TitleRight);
//CreateHeader(sheet1, 2);
CreateProductLineTable(style, products);
//var priceQuery =
// from prod in products
// group prod by prod.SupplierId into grouping
// select new
// {
// grouping.Key,
// grouping,
// Count = grouping.Count(),
// TotalQuantity = grouping.Sum(p => p.Quantity),
// TotalPurchasePrice = grouping.Sum(p => p.PurchasePrice),
// TotalSalePrice = grouping.Sum(p => p.SalePrice),
// TotalSum = grouping.Sum(p => p.Sum),
// TotalSaleSum = grouping.Sum(p => p.SaleSum)
// };
////int rowCounter = 3;
//double grandTotalSum = 0, grandTotalSaleSum = 0;
//int grandTotalQuantity = 0;
//foreach (var grp in priceQuery)
//{
// bool firstRow = true;
// int supplierRowId = rowCounter;
// foreach (var gr in grp.grouping)
// {
// //supplierId;
// IRow rowData = style.Sheet.CreateRow(rowCounter);
// if (firstRow)
// {
// string supplierCode = "Фабрика не известна";
// if (grp.Key != null)
// supplierCode = products.Where(x => x.SupplierId == grp.Key).FirstOrDefault().SupplierCode;
// ICell supplierCell = rowData.CreateCell(0);
// supplierCell.SetCellValue(supplierCode);
// supplierCell.CellStyle = style.StyleAlignCenter;
// // sheet1.AddMergedRegion(new CellRangeAddress(rowCounter, grp.Count, 0, 0));
// }
// else
// {
// var cell = rowData.GetCell(0) ?? rowData.CreateCell(0);
// cell.CellStyle = style.StyleAlignCenter;
// }
// ICell productArticleCell = rowData.CreateCell(1);
// productArticleCell.SetCellValue(gr.ProductArticle);
// productArticleCell.CellStyle = style.StyleTable;
// ICell quantityCell = rowData.CreateCell(2);
// quantityCell.SetCellValue(gr.Quantity);
// quantityCell.CellStyle = style.StyleTable;
// ICell purchasePriceCell = rowData.CreateCell(3);
// purchasePriceCell.SetCellValue((double)gr.PurchasePrice);
// purchasePriceCell.CellStyle = style.StyleTable;
// ICell salePriceCell = rowData.CreateCell(4);
// salePriceCell.SetCellValue((double)gr.SalePrice);
// salePriceCell.CellStyle = style.StyleTable;
// ICell sumCell = rowData.CreateCell(5);
// sumCell.SetCellValue((double)gr.Sum);
// sumCell.CellStyle = style.StyleTable;
// ICell saleSumCell = rowData.CreateCell(6);
// saleSumCell.SetCellValue((double)gr.SaleSum);
// saleSumCell.CellStyle = style.StyleTable;
// rowData.CreateCell(7).SetCellValue(gr.Comment);
// rowCounter++;
// firstRow = false;
// }
// CellRangeAddress region = new CellRangeAddress(supplierRowId, grp.Count + supplierRowId, 0, 0);
// style.Sheet.AddMergedRegion(region);
// IRow rowTotal = style.Sheet.CreateRow(rowCounter);
// ICell totalCell0 = rowTotal.GetCell(0) ?? rowTotal.CreateCell(0);
// totalCell0.CellStyle = style.StyleLeftBottom;
// ICell totalCell2 = rowTotal.CreateCell(1);
// totalCell2.CellStyle = style.StyleTableBold;
// grandTotalQuantity += grp.TotalQuantity;
// ICell quantityTotalCell = rowTotal.CreateCell(2);
// quantityTotalCell.SetCellValue(grp.TotalQuantity);
// quantityTotalCell.CellStyle = style.StyleTableBold;
// ICell totalCell3 = rowTotal.CreateCell(3);
// totalCell3.CellStyle = style.StyleTableBold;
// ICell totalCell4 = rowTotal.CreateCell(4);
// totalCell4.CellStyle = style.StyleTableBold;
// grandTotalSum += (double)grp.TotalSum;
// ICell sumTotalCell = rowTotal.CreateCell(5);
// sumTotalCell.SetCellValue((double)grp.TotalSum);
// sumTotalCell.CellStyle = style.StyleTableBold;
// grandTotalSaleSum += (double)grp.TotalSaleSum;
// ICell sumSaleTotalCell = rowTotal.CreateCell(6);
// sumSaleTotalCell.SetCellValue((double)grp.TotalSaleSum);
// sumSaleTotalCell.CellStyle = style.StyleTableBold;
// // итоги
// rowCounter = rowCounter + 2;
//}
//// итоговые суммы
//IRow rowGrandTotal = style.Sheet.CreateRow(rowCounter);
//ICell granTotalCell0 = rowGrandTotal.GetCell(0) ?? rowGrandTotal.CreateCell(0);
//granTotalCell0.SetCellValue("Итого:");
//granTotalCell0.CellStyle = style.StyleTableBold;
//ICell quantityGrandTotalCell = rowGrandTotal.CreateCell(2);
//quantityGrandTotalCell.SetCellValue(grandTotalQuantity);
//quantityGrandTotalCell.CellStyle = style.StyleTableBold;
//ICell sumGrandTotalCell = rowGrandTotal.CreateCell(5);
//sumGrandTotalCell.SetCellValue(grandTotalSum);
//sumGrandTotalCell.CellStyle = style.StyleTableBold;
//ICell sumGrandSaleTotalCell = rowGrandTotal.CreateCell(6);
//sumGrandSaleTotalCell.SetCellValue(grandTotalSaleSum);
//sumGrandSaleTotalCell.CellStyle = style.StyleTableBold;
//for (int i = 0; i <= rowCounter; i++)
//{
// IRow row = sheet1.GetRow(i) ?? sheet1.CreateRow(i);
// var cell = row.GetCell(0) ?? row.CreateCell(0);
// cell.CellStyle = styleAlignCenter;
//}
string pathToFile = Path.Combine(reportInfo.FilePath, reportInfo.FileName);
WriteToFile(style.Workbook,pathToFile);
}