Ejemplo n.º 1
0
        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);
            }
        }
Ejemplo n.º 2
0
        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;
        }
Ejemplo n.º 3
0
        // 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;
        }
Ejemplo n.º 4
0
        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;
        }
Ejemplo n.º 5
0
        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;
        }
Ejemplo n.º 6
0
        /// <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);
        }