} // 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
