} // BuildFinancialStatsXls private KeyValuePair <ReportQuery, DataTable> CreateFinancialStatsReport(Report report, DateTime today, DateTime tomorrow) { var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; var ea = new EarnedInterest.EarnedInterest(DB, EarnedInterest.EarnedInterest.WorkingMode.ForPeriod, true, today, tomorrow, this); SortedDictionary <int, decimal> earned = ea.Run(); DataTable oOutput = new DataTable(); oOutput.Columns.Add("SortOrder", typeof(int)); oOutput.Columns.Add("Caption", typeof(string)); oOutput.Columns.Add("Value", typeof(decimal)); rpt.Execute(DB, (sr, bRowsetStart) => { oOutput.Rows.Add((int)sr[0], (string)sr[1], (decimal)sr[2]); return(ActionResult.Continue); }); oOutput.Rows.Add(0, "Earned interest", earned.Sum(pair => pair.Value)); return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateFinancialStatsReport
} // BuildStrategyRunningTimeXls private void CreateNewUnmatchedPaymentsData(Report report, DateTime today, DateTime tomorrow) { if (this.newUnmatchedPaymentsData != null) { return; } var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow, }; DataTable tbl = NewUnmatchedPayment.ToTable(); DB.ForEachResult <NewUnmatchedPayment>( row => { row.ToRow(tbl); if (this.timestampsToSave == null) { this.timestampsToSave = new List <PaymentTimestamp>(); } this.timestampsToSave.Add(new PaymentTimestamp { PaymentID = row.PaymentID, LastKnownTimestamp = row.PaymentTimestamp, }); }, "FindNewUnmatchedPayments", CommandSpecies.StoredProcedure ); this.newUnmatchedPaymentsData = new KeyValuePair <ReportQuery, DataTable>(rpt, tbl); } // CreateNewUnmatchedPaymentsData
} // BuildEarnedInterestXls private KeyValuePair <ReportQuery, DataTable> CreateEarnedInterestReport(Report report, bool bAccountingMode, DateTime today, DateTime tomorrow) { var ea = new EarnedInterest.EarnedInterest(DB, EarnedInterest.EarnedInterest.WorkingMode.ForPeriod, bAccountingMode, today, tomorrow, this); SortedDictionary <int, decimal> earned = ea.Run(); var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; var oTotal = new EarnedInterestRow(true, CustomerStatus.Enabled, CustomerStatus.Enabled); var oRows = new List <EarnedInterestRow>(); rpt.Execute(DB, (sr, bRowsetStart) => { int nLoanID = sr["LoanID"]; if (!earned.ContainsKey(nLoanID)) { return(ActionResult.Continue); } int nClientID = sr["ClientID"]; var oNewRow = new EarnedInterestRow(false, ea.CustomerStatusHistory.Data.GetLast(nClientID).NewStatus, ea.CustomerStatusHistory.GetCurrent(nClientID).NewStatus) { IssueDate = sr["IssueDate"], ClientID = nClientID, LoanID = nLoanID, ClientName = sr["ClientName"], ClientEmail = sr["ClientEmail"], EarnedInterest = earned[nLoanID], LoanAmount = sr["LoanAmount"], TotalRepaid = sr["TotalRepaid"], PrincipalRepaid = sr["PrincipalRepaid"], SetupFees = sr["SetupFees"], OtherFees = sr["OtherFees"], Rollover = sr["Rollover"], }; oTotal.Update(oNewRow); oRows.Add(oNewRow); return(ActionResult.Continue); }); // for each earned interest oRows.Sort(EarnedInterestRow.Compare); DataTable oOutput = oTotal.ToTable(); oRows.ForEach(r => r.ToRow(oOutput)); return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateEarnedInterestReport
} // BuildLoansIssuedXls public ExcelPackage XlsReport(ReportQuery rptDef, string sRptTitle = "") { try { return(AddSheetToExcel(rptDef, sRptTitle)); } catch (Exception e) { Alert(e, "Failed to generate Excel report."); } // try return(ErrorXlsReport("Failed to generate report.")); } // XlsReport
protected ATag GetHtml(ReportQuery rptDef, List <string> oColumnTypes, Report report) { switch (report.Type) { case ReportType.RPT_NOT_AUTO_APPROVED: return(BuildNotAutoApprovedReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_EARNED_INTEREST: return(BuildEarnedInterestReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_EARNED_INTEREST_ALL_CUSTOMERS: return(BuildEarnedInterestAllCustomersReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_FINANCIAL_STATS: return(BuildFinancialStatsReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_LOAN_INTEGRITY: return(BuildLoanIntegrityReport(report, oColumnTypes)); case ReportType.RPT_LOANS_GIVEN: return(BuildLoansIssuedReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_CCI: return(BuildCciReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_UI_REPORT: return(BuildUiReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_UI_EXT_REPORT: return(BuildUiExtReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_ACCOUNTING_LOAN_BALANCE: return(BuildAccountingLoanBalanceReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_TRAFFIC_REPORT: return(BuildTrafficReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_MARKETING_CHANNELS_SUMMARY: return(BuildMarketingChannelsSummaryReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); case ReportType.RPT_STRATEGY_RUNNING_TIME: return(BuildStrategyRunningTimeReport(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd, oColumnTypes)); default: string sReportTitle = report.GetTitle((DateTime)rptDef.DateStart, " ", report.IsDaily ? (DateTime?)null : (DateTime)rptDef.DateEnd); return(new Div() .Append(new H1().Append(new Text(sReportTitle))) .Append(TableReport(rptDef, oColumnTypes: oColumnTypes))); } // switch } // GetHtml
} // CreateUiReport private KeyValuePair <ReportQuery, DataTable> CreateUiExtReport(Report report, DateTime today, DateTime tomorrow) { var cc = new UiReportExt(DB, today, tomorrow, this); var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; Tuple <DataTable, ColumnInfo[]> oOutput = cc.Run(); rpt.Columns = oOutput.Item2; return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput.Item1)); } // CreateUiExtReport
} // CreateLoansIssuedReport private KeyValuePair <ReportQuery, DataTable> CreateCciReport(Report report, DateTime today, DateTime tomorrow) { var cc = new CciReport(DB, this); List <CciReportItem> oItems = cc.Run(); var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; DataTable oOutput = CciReportItem.CreateTable(); oItems.ForEach(r => r.ToRow(oOutput)); return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateCciReport
} // GetHtml protected ExcelPackage GetXls(ReportQuery rptDef, Report report) { switch (report.Type) { case ReportType.RPT_NOT_AUTO_APPROVED: return(BuildNotAutoApprovedXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_EARNED_INTEREST: return(BuildEarnedInterestXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_EARNED_INTEREST_ALL_CUSTOMERS: return(BuildEarnedInterestAllCustomersXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_FINANCIAL_STATS: return(BuildFinancialStatsXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_LOANS_GIVEN: return(BuildLoansIssuedXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_CCI: return(BuildCciXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_UI_REPORT: return(BuildUiXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_UI_EXT_REPORT: return(BuildUiExtXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_ACCOUNTING_LOAN_BALANCE: return(BuildAccountingLoanBalanceXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_TRAFFIC_REPORT: return(BuildTrafficReportXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_MARKETING_CHANNELS_SUMMARY: return(BuildMarketingChannelsSummaryXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); case ReportType.RPT_STRATEGY_RUNNING_TIME: return(BuildStrategyRunningTimeXls(report, (DateTime)rptDef.DateStart, (DateTime)rptDef.DateEnd)); default: var xlsTitle = report.GetTitle((DateTime)rptDef.DateStart, " ", report.IsDaily ? (DateTime?)null : (DateTime)rptDef.DateEnd); return(XlsReport(rptDef, xlsTitle)); } // switch } // GetXls
} // CreateCciReport private KeyValuePair <ReportQuery, DataTable> CreateUiReport(Report report, DateTime today, DateTime tomorrow) { var cc = new UiReport(DB, today, tomorrow, this); List <UiReportItem> oItems = cc.Run(); var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; DataTable oOutput = UiReportItem.CreateTable(); foreach (UiReportItem oItem in oItems) { oItem.ToRow(oOutput); } return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateUiReport
} // class LoansIssuedRow private KeyValuePair <ReportQuery, DataTable> CreateLoansIssuedReport(Report report, DateTime today, DateTime tomorrow) { var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; var ea = new EarnedInterest.EarnedInterest(DB, EarnedInterest.EarnedInterest.WorkingMode.ByIssuedLoans, false, today, tomorrow, this); SortedDictionary <int, decimal> earned = ea.Run(); var oRows = new List <LoansIssuedRow>(); var oTotal = new LoansIssuedRow(null); int nRowCount = 0; rpt.Execute(DB, (sr, bRowsetStart) => { nRowCount++; var lir = new LoansIssuedRow(sr); oRows.Add(lir); lir.SetInterests(earned); oTotal.AddClient(lir); oTotal.AccumulateTotals(lir); return(ActionResult.Continue); }); oTotal.SetLoanCount(nRowCount); DataTable oOutput = oTotal.ToTable(); oRows.ForEach(lir => lir.ToRow(oOutput)); return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateLoansIssuedReport
} // BuildNotAutoApprovedXls private KeyValuePair <ReportQuery, DataTable> CreateNotAutoApprovedReport( Report report, DateTime today, DateTime tomorrow ) { var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; var reportData = new NotAutoApprovedReportData(); rpt.Execute(DB, (sr, rowsetStart) => { reportData.Add(sr); return(ActionResult.Continue); }); var oOutput = reportData.ToTable(); return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateNotAutoApprovedReport
} // class AccountingLoanBalanceRow private KeyValuePair <ReportQuery, DataTable> CreateAccountingLoanBalanceReport( Report report, DateTime today, DateTime tomorrow ) { Debug("Creating accounting loan balance report..."); Debug("Creating accounting loan balance report: loading earned interest..."); var ea = new EarnedInterest.EarnedInterest( DB, EarnedInterest.EarnedInterest.WorkingMode.ForPeriod, true, today, tomorrow, this ); SortedDictionary <int, decimal> earned = ea.Run(); Debug("Creating accounting loan balance report: loading earned interest complete."); var rpt = new ReportQuery(report) { DateStart = today, DateEnd = tomorrow }; var oRows = new SortedDictionary <int, AccountingLoanBalanceRow>(); Debug("Creating accounting loan balance report: loading report data..."); rpt.Execute(DB, (sr, bRowsetStart) => { int nLoanID = sr["LoanID"]; decimal nEarnedInterest = earned.ContainsKey(nLoanID) ? earned[nLoanID] : 0; if (oRows.ContainsKey(nLoanID)) { oRows[nLoanID].Update(sr); } else { int nClientID = sr["ClientID"]; oRows[nLoanID] = new AccountingLoanBalanceRow( sr, nEarnedInterest, ea.CustomerStatusHistory.Data.GetLast(nClientID), ea.CustomerStatusHistory.GetCurrent(nClientID), ea.CustomerStatusHistory.Data.GetWriteOffDate(nClientID) ?? tomorrow ); } // if return(ActionResult.Continue); }); Debug("Creating accounting loan balance report: loading report data complete."); Debug("Creating accounting loan balance report: creating an output..."); DataTable oOutput = AccountingLoanBalanceRow.ToTable(); Debug("Creating accounting loan balance report: table is ready, filling it..."); var oTotal = new AccountingLoanBalanceRow(); foreach (KeyValuePair <int, AccountingLoanBalanceRow> pair in oRows) { oTotal.UpdateTotal(pair.Value); } oTotal.ToRow(oOutput); foreach (KeyValuePair <int, AccountingLoanBalanceRow> pair in oRows) { pair.Value.ToRow(oOutput); } Debug("Creating accounting loan balance report complete."); return(new KeyValuePair <ReportQuery, DataTable>(rpt, oOutput)); } // CreateAccountingLoanBalanceReport
} // AddSheetToExcel private ExcelPackage AddSheetToExcel(ReportQuery rptDef, string title, ExcelPackage wb = null) { if (wb == null) // first initialization, if we will use it multiple times. { wb = new ExcelPackage(); } var sheet = wb.Workbook.Worksheets.Add(rptDef.StoredProcedure); const int nTitleRow = 1; // first row for title const int nFirstColumn = 1; // first column int nHeaderRow = nTitleRow + 1; int nFirstDataRow = nHeaderRow + 1; ExcelRange oTitle = sheet.Cells[nTitleRow, nFirstColumn]; oTitle.Value = title.Replace("<h1>", "").Replace("</h1>", ""); oTitle.Style.Font.Bold = true; oTitle.Style.Font.Size = 16; int nDataRowIdx = 0; rptDef.Execute(DB, (sr, bRowsetStart) => { for (int j = 0; j < sr.Count; j++) { if (nDataRowIdx == 0) { sheet.Cells[nHeaderRow, nFirstColumn + j].Value = sr.GetName(j); if (sr.Count > 1) { sheet.Cells[nTitleRow, nFirstColumn, nTitleRow, nFirstColumn + sr.Count - 1].Merge = true; } } // if ExcelRange oCell = sheet.Cells[nFirstDataRow + nDataRowIdx, nFirstColumn + j]; object oValue = sr.ColumnOrDefault(j); if (IsInt(oValue)) { oCell.Style.Numberformat.Format = "#,##0"; } else if (IsFloat(oValue)) { oCell.Style.Numberformat.Format = "#,##0.00"; } else if (oValue is DateTime) { oCell.Style.Numberformat.Format = "dd-mmm-yyyy hh:mm:ss"; } else { oCell.Style.Numberformat.Format = "@"; } oCell.Value = oCell.Style.Numberformat.Format == "@" ? oValue.ToString().Replace(" ", " ") : oValue; } // for each column nDataRowIdx++; return(ActionResult.Continue); }); sheet.Cells.AutoFitColumns(); sheet.Row(nHeaderRow).Style.Font.Bold = true; return(wb); } // AddSheetToExcel
} // CreateUiExtReport private void ProcessTableReportRow(ReportQuery rptDef, SafeReader sr, Tbody oTbody, int lineCounter, List <string> oColumnTypes) { var oTr = new Tr().Add <Class>(lineCounter % 2 == 0 ? "Odd" : "Even"); oTbody.Append(oTr); var oClassesToApply = new List <string>(); for (int columnIndex = 0; columnIndex < rptDef.Columns.Length; columnIndex++) { ColumnInfo col = rptDef.Columns[columnIndex]; var oValue = sr.ColumnOrDefault(col.FieldName); if (col.IsVisible) { var oTd = new Td(); oTr.Append(oTd); if (IsNumber(oValue)) { ATag oInnerTag = new Text(NumStr(oValue, col.Format(IsInt(oValue) ? 0 : 2))); if (col.ValueType == ValueType.UserID || col.ValueType == ValueType.BrokerID) { var oLink = new A(); oLink.Append(oInnerTag); oLink.Target.Append("_blank"); var titleText = "Open this customer in underwriter."; if (col.ValueType == ValueType.UserID) { oLink.Href.Append("https://" + UnderwriterSite + "/UnderWriter/Customers?customerid=" + oValue); } else { oLink.Href.Append("https://" + UnderwriterSite + "/UnderWriter/Customers#broker/" + oValue); titleText = "Open this broker in underwriter."; } oLink.Alt.Append(titleText); oLink.Title.Append(titleText); oInnerTag = oLink; if (oColumnTypes != null) { oColumnTypes[columnIndex] = "user-id"; } } else { if (oColumnTypes != null) { oColumnTypes[columnIndex] = "formatted-num"; } } // if user id oTd.Add <Class>("R").Append(oInnerTag); } else { oTd.Add <Class>("L").Append(new Text(oValue.ToString())); if ((oColumnTypes != null) && (oValue is DateTime)) { oColumnTypes[columnIndex] = "date"; } } // if } else { if (col.ValueType == ValueType.CssClass) { oClassesToApply.Add(oValue.ToString()); } } // if } // for each column if (oClassesToApply.Count > 0) { oTr.ApplyToChildren <Class>(string.Join(" ", oClassesToApply.ToArray())); } } // ProcessTableReportRow
} // TableReport public ATag TableReport(ReportQuery rptDef, DataTable oReportData, bool isSharones = false, string sRptTitle = "", List <string> oColumnTypes = null) { int lineCounter = 0; var tbl = new Table().Add <Class>("Report"); Tbody oTbody; try { if (!isSharones) { tbl.Add <ID>("tableReportData"); } } catch (Exception e) { Alert(e, "Failed to add HTML id to report table."); return(tbl); } // try try { var tr = new Tr().Add <Class>("HR"); for (int columnIndex = 0; columnIndex < rptDef.Columns.Length; columnIndex++) { if (rptDef.Columns[columnIndex].IsVisible) { tr.Append(new Th().Add <Class>("H").Append(new Text(rptDef.Columns[columnIndex].Caption))); } } tbl.Append(new Thead().Append(tr)); } catch (Exception e) { Alert(e, "Failed to initialise table header row."); return(tbl); } // try try { oTbody = new Tbody(); tbl.Append(oTbody); if (oColumnTypes != null) { oColumnTypes.Clear(); for (int columnIndex = 0; columnIndex < rptDef.Columns.Length; columnIndex++) { oColumnTypes.Add("string"); } } // if } catch (Exception e) { Alert(e, "Failed to initialise report table column types."); return(tbl); } // try if (oReportData == null) { try { rptDef.Execute(DB, (sr, bRowsetStart) => { ProcessTableReportRow(rptDef, sr, oTbody, lineCounter, oColumnTypes); lineCounter++; return(ActionResult.Continue); }); // for each data row } catch (Exception e) { Alert(e, "Failed to fetch data from DB or create report table body."); return(tbl); } // try } else { try { foreach (DataRow row in oReportData.Rows) { ProcessTableReportRow(rptDef, new SafeReader(row), oTbody, lineCounter, oColumnTypes); lineCounter++; } // for each data row } catch (Exception e) { Alert(e, "Failed to create report table body."); return(tbl); } // try } // if try { if (oColumnTypes != null) { for (int columnIndex = rptDef.Columns.Length - 1; columnIndex >= 0; columnIndex--) { if (!rptDef.Columns[columnIndex].IsVisible) { oColumnTypes.RemoveAt(columnIndex); } } } // if } catch (Exception e) { Alert(e, "Failed to finalise report table column types."); } // try return(tbl); } // TableReport
} // constructor public ATag TableReport(ReportQuery rptDef, bool isSharones = false, string sRptTitle = "", List <string> oColumnTypes = null) { return(TableReport(rptDef, null, isSharones, sRptTitle, oColumnTypes)); } // TableReport