Itext Sharp Report is not able to generate the Report in For more than 37000 records

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Itext Sharp Report is not able to generate the Report in For more than 37000 records

Pankaj Tiwari
Hi I have tried to build the pdf For more than 37000 records but I am getting system out of memory exception and I have also tried after deploying the same in Staging server but getting time out ,I also increased the time out in the Web.config but not able to produce the report.Can Any one help on this.

Controller.
[HttpPost]
public async Task<FileContentResult> GeneralLedgerPostReport(DRS.Model.DTO.GLReportSearchCriteria model)
        {
            return File(await Task.Run(() => _reportService.CreateGeneralLedgerReports(model)), "application/pdf", "General Ledger.PDF");
        }


Method to generate the file.
 
public byte[] CreateGeneralLedgerReports(GLReportSearchCriteria reportcriteria)
        {
            byte[] CoreData = this.GenerateLedgerCorePDF(reportcriteria);
            List<PdfReader> pdfcollection = new List<PdfReader>();
            pdfcollection.Add(new PdfReader(CoreData));
            byte[] combineoutput = this.MergLedgerDocuments(pdfcollection, reportcriteria);
            return combineoutput;
        }



private byte[] GenerateLedgerCorePDF(GLReportSearchCriteria reportcriteria)
        {
            Document document = new Document(new Rectangle(842f, 595f), 25, 25, 60, 25);
            var output = new MemoryStream();
            var writer = PdfWriter.GetInstance(document, output);
            writer.CloseStream = false;        
            document.Open();
            Chapter ChapterforSection = new Chapter(new Paragraph("", _titleFont), i);
            ChapterforSection.NumberDepth = 0;
            Paragraph pp = new Paragraph("", _ColorFont);
            pp.Add(this.AddGenerateLedgerSection(reportcriteria));
            ChapterforSection.AddSection(pp, 0);
            document.Add(ChapterforSection);
            document.Close();
            return output.ToArray();
        }


private PdfPTable AddGenerateLedgerSection(GLReportSearchCriteria reportcriteria)
        {
            int? a = reportcriteria.Period + reportcriteria.BalanceFrwd;

            List<GLGeneralLedgerReport> result = (from o in _repo.GLAccounts
                                                  join b in _repo.GLAccountTotals
                                                  on o.AccountID equals b.AccountID
                                                  where (o.Corporation.Equals(reportcriteria.Corporation)
                                                  && b.Year == reportcriteria.FiscalYear
                                               && (b.Period >= reportcriteria.Period && b.Period <= a))
                                                  select new GLGeneralLedgerReport
                                                  {
                                                      AccountNumber = o.AccountNumber,
                                                      AccountDescription = o.AccountDescription,
                                                      AccountID = o.AccountID,
                                                      YTDBalance = o.YTDBalance,
                                                      ClassCode = o.ClassCode,
                                                      NormalBalance = o.NormalBalance
                                                  }).AsNoTracking().ToList();

            List<Guid?> AccountiDs = result.Select(b => b.AccountID).ToList();
            // var result2 = _financeService.GetGeneralLedgerIDDetails(item.AccountID).AsNoTracking().ToList();
            var result2 = _repo.GLAccountJournals.Where(b => AccountiDs.Contains(b.AccountID)).ToList();
            //from r in _repo.GLAccountTotals where r.AccountID == AccountId && r.Period == BalanceFrwd select r;
            //var result3 = _financeService.GetGeneralLedgerTotalDetails(item.AccountID, reportcriteria.BalanceFrwd).AsNoTracking().ToList();
            var result3 = _repo.GLAccountTotals.Where(b => AccountiDs.Contains(b.AccountID) && b.Period == reportcriteria.BalanceFrwd).ToList(); ;
            //var result3 = _financeService.GetGeneralLedgerTotalDetails(item.AccountID, reportcriteria.BalanceFrwd).AsNoTracking().ToList();

            PdfPTable maintable = ReportStyleHelper.GetTable(1);
            PdfPTable headertable = ReportStyleHelper.GetTable(1);

            headertable.AddCell(ReportStyleHelper.GetChapterHeader(reportcriteria.Corporation, 7));
            headertable.AddCell(ReportStyleHelper.GetChapterHeader("General Ledger Report", 7));
            headertable.AddCell(ReportStyleHelper.GetChapterHeader("From " + ReportStyleHelper.PeriodToDate(reportcriteria.Period, reportcriteria.FiscalYear), 7));
            headertable.AddCell(ReportStyleHelper.GetChapterHeader("To " + ReportStyleHelper.PeriodToDate(Convert.ToInt32(a), reportcriteria.FiscalYear), 7));
            maintable.AddCell(headertable);

            PdfPTable table = ReportStyleHelper.GetTable(8, new float[] { 1f, 1f, 1f, 1f, 1f, 1f, 1f, 1f });
            foreach (var item in result)
            {
                table.AddCell(ReportStyleHelper.GetSectionLabel("Account", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel(item.AccountNumber));
                table.AddCell(ReportStyleHelper.GetSectionLabel(item.AccountDescription));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Balance frw ", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel(Convert.ToString(a - reportcriteria.Period)));

                if (result.Count > 0)
                {
                    foreach (var datas in result.Where(u => u.Period == reportcriteria.Period))
                    {
                        table.AddCell(ReportStyleHelper.GetSectionLabel(Convert.ToDecimal(datas.YTDBalance).ToString("#,##0.00")));
                    }
                }
                else
                {
                    table.AddCell(ReportStyleHelper.GetEmptyCell(1));
                }
                table.AddCell(ReportStyleHelper.GetEmptyCell(3));
                table.AddCell(ReportStyleHelper.GetParagraphSeparater(8));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Period", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Journal Id", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("System", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Source Description", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Posting Date", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Debit", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Credit", PdfPCell.ALIGN_LEFT));
                table.AddCell(ReportStyleHelper.GetSectionLabel("Balance", PdfPCell.ALIGN_LEFT));
                //table5.AddCell(ReportStyleHelper.GetParagraphSeparater(8));



                //var data = result2.Where(u => u.Period == Convert.ToString(reportcriteria.Period)).ToList();
                foreach (var item2 in result2)
                {
                    table.AddCell(ReportStyleHelper.GetSectionText((reportcriteria.BalanceFrwd).ToString()));
                    table.AddCell(ReportStyleHelper.GetSectionText(item2.JournalID.ToString()));
                    table.AddCell(ReportStyleHelper.GetSectionText(item2.System));
                    table.AddCell(ReportStyleHelper.GetSectionText(item2.SourceDescription));
                    table.AddCell(ReportStyleHelper.GetSectionText(item2.PostDate.ToString("MM/dd/yyyy")));
                    table.AddCell(ReportStyleHelper.GetSectionText(Convert.ToDecimal(item2.DebitAmount).ToString("#,##0.00")));
                    table.AddCell(ReportStyleHelper.GetSectionText(Convert.ToDecimal(item2.CreditAmount).ToString("#,##0.00")));
                    table.AddCell(ReportStyleHelper.GetEmptyCell(1));

                }


                var data3 = result3.Where(u => u.Year == reportcriteria.FiscalYear && (u.Period >= reportcriteria.Period && u.Period <= a)).ToList();
                foreach (var item3 in data3)
                {
                    table.AddCell(ReportStyleHelper.GetEmptyCell(4));
                    table.AddCell(ReportStyleHelper.GetSectionLabel("Net change", PdfPCell.ALIGN_LEFT));
                    table.AddCell(ReportStyleHelper.GetSectionText(Convert.ToDecimal(item3.NetChange).ToString("#,##0.00")));
                    table.AddCell(ReportStyleHelper.GetSectionLabel("Ending balance", PdfPCell.ALIGN_LEFT));
                    table.AddCell(ReportStyleHelper.GetSectionText(Convert.ToDecimal(item3.YTDBalance).ToString("#,##0.00")));
                }
                maintable.AddCell(table);
            }

            PdfPTable tablelast = ReportStyleHelper.GetTable(3, new float[] { 1f, 1f, 1f });
            var aggregateResult = result.GroupBy(x => x.ClassCode).Select(
                 x => new
                 {
                     ClassCode = x.Key,
                     NormalBalance = x.Sum(y => Convert.ToDecimal(y.NormalBalance)),
                     YTDBalance = x.Sum(y => y.YTDBalance),
                 }
                 );
            tablelast.AddCell(ReportStyleHelper.GetEmptyCell(1));
            tablelast.AddCell(ReportStyleHelper.GetSectionLabel("Current"));
            tablelast.AddCell(ReportStyleHelper.GetSectionLabel("Year to Date "));
            foreach (var aggregate in aggregateResult)
            {

                tablelast.AddCell(ReportStyleHelper.GetDetailSectionLabel("Totals By " + aggregate.ClassCode, 1));
                tablelast.AddCell(ReportStyleHelper.GetSectionText(Convert.ToDecimal(aggregate.NormalBalance).ToString("#,##0.00")));
                tablelast.AddCell(ReportStyleHelper.GetSectionText(Convert.ToDecimal(aggregate.YTDBalance).ToString("#,##0.00")));
                // tablelast.AddCell(ReportStyleHelper.GetParagraphSeparater(7));
            }
            maintable.AddCell(tablelast);
            return maintable;
        }