private void DrawTable()
{
var cell_link_onclick = String.Format("javascript:get_link('FundSearch.aspx?org={0}&fy={1}&bm={2}&bl={3}&ba={4}", Organization, FiscalYear, GetBookMonthList(BookMonth), BusinessLine, DEFAULT_VALUE_BUDGET_ACTIVITY);
cell_link_onclick = cell_link_onclick + "&vm={0}&gcd={1}');";
//before drawing the table:
//if the Organization value is empty - take the BusinessLine value,
//otherwise - ignore BusinessLine value and build the report on Organization level:
if (Organization != "")
{
BusinessLine = "";
}
//link to user data entry screen:
var cell_popup_onclick = String.Format("open_popup('org={0}&fy={1}&bm={2}&bl={3}", Organization, FiscalYear, BookMonth, BusinessLine);
cell_popup_onclick = cell_popup_onclick + "&t={0}&gcd={1}&adj={2}');";
HtmlTableRow tr;
HtmlTableCell td;
var dsState = FSSummaryReport.GetMonthState(FiscalYear, BookMonth, Organization, BusinessLine);
var dsConfig = FSDataServices.GetFSReportConfiguration();
/*
* in case of report by Business Line the following should be recalculated and not taken from the database (State table):
* - projection
* - rwa
* - total obligations, etc
* because in calulation we use reference to paid days
* */
var dtState = dsState.Tables[0].Copy();
if (BusinessLine != "")
{
dtState = FSSummaryReport.RecalculateValuesForBusinessLine(dtState, BusinessLine, FiscalYear, BookMonth);
}
decimal obligations = 0;
decimal income = 0;
decimal rwa = 0;
decimal adjustments = 0;
decimal totalobligations = 0;
decimal overunderaccrued = 0;
decimal projection = 0;
decimal allowance = 0;
decimal balance = 0;
decimal onetimeadj = 0;
decimal expbyend = 0;
decimal variance = 0;
var overunder_desc = "";
var onetimeadj_desc = "";
var expbyend_desc = "";
decimal sum_obligations = 0;
decimal sum_income = 0;
decimal sum_adjustments = 0;
decimal sum_totalobligations = 0;
decimal sum_projection = 0;
decimal sum_allowance = 0;
decimal sum_balance = 0;
decimal sum_variance = 0;
decimal total_obligations = 0;
decimal total_income = 0;
decimal total_adjustments = 0;
decimal total_totalobligations = 0;
decimal total_projection = 0;
decimal total_allowance = 0;
decimal total_balance = 0;
var total_variance = 0;
decimal q_projection = 0;
decimal q_allowance = 0;
decimal q_balance = 0;
decimal q_adjustments = 0;
decimal sum_q_projection = 0;
decimal sum_q_allowance = 0;
decimal sum_q_balance = 0;
decimal total_q_projection = 0;
decimal total_q_allowance = 0;
decimal total_q_balance = 0;
DataRow[] dr_col;
//first part of Report:
/*****************************************************************************************/
tr = new HtmlTableRow();
//1 column
td = new HtmlTableCell();
td.AddReportHeaderBlue();
//td.Width = "300";
tr.Cells.Add(td);
//2,3,4,5 columns
td = new HtmlTableCell();
td.ColSpan = 4;
td.InnerText = String.Format("{0:MMMM}", DateTime.Parse(BookMonth + "/01/2010"));
td.Align = "center";
td.AddReportHeaderBlue();
tr.Cells.Add(td);
//6,7,8 columns
td = new HtmlTableCell();
td.ColSpan = 3;
td.InnerText = "Quarterly Projection";
td.Align = "center";
td.AddReportHeaderBlue();
tr.Cells.Add(td);
//9,10,11,12 columns
td = new HtmlTableCell();
td.ColSpan = 4;
td.InnerText = "End-of-Year Projection";
td.Align = "center";
td.AddReportHeaderBlue();
tr.Cells.Add(td);
TableToDraw.Rows.Add(tr);
//headers row:
tr = new HtmlTableRow();
tr.Cells.AddHeaderCell("FUNCTIONS:", "reportHeaderBlueNAlign", "left");
tr.Cells.AddHeaderCellWithTooltip(CAPTION_OBLIGATIONS, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_OBLIGATIONS));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_INCOME, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_INCOME));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_ADJUSTMENTS, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_ADJUSTMENTS));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_TOTAL_OBLIGATIONS, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_TOTAL_OBLIGATIONS));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_Q_PROJECTION, "reportHeaderYellow", BuildReportForExcel, AddTooltipPopup(CAPTION_Q_PROJECTION));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_Q_ALLOWANCE, "reportHeaderYellow", BuildReportForExcel, AddTooltipPopup(CAPTION_Q_ALLOWANCE));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_Q_BALANCE, "reportHeaderYellow", BuildReportForExcel, AddTooltipPopup(CAPTION_Q_BALANCE));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_PROJECTION, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_PROJECTION));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_ALLOWANCE, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_ALLOWANCE));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_BALANCE, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_BALANCE));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_VARIANCE, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_VARIANCE));
TableToDraw.Rows.Add(tr);
/*****************************************************************************************/
foreach (DataRow drConfig in dsConfig.Tables[0].Rows)
{
tr = new HtmlTableRow();
tr.AddReportRow();
tr.Cells.AddHeaderCell(drConfig["Name"].ToString(), drConfig["CaptionStyle"].ToString(), "left");
if ((int)drConfig["OrderNumber"] == 0)
{
//complete empty row:
td = new HtmlTableCell();
td.ColSpan = 11;
td.InnerText = "";
tr.Cells.Add(td);
}
else if ((int)drConfig["OrderNumber"] == 99)
{
//complete row with summary numbers:
tr.Cells.AddDisplayedMoneyCell(sum_obligations, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_income, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_adjustments, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_totalobligations, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_q_projection, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_q_allowance, "reportTotal");
sum_q_balance = sum_q_allowance - sum_q_projection;
tr.Cells.AddDisplayedMoneyCell(sum_q_balance, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_projection, "reportTotal");
tr.Cells.AddDisplayedMoneyCell(sum_allowance, "reportTotal");
sum_balance = sum_allowance - sum_projection;
tr.Cells.AddDisplayedMoneyCell(sum_balance, "reportTotal");
sum_variance = (sum_allowance == 0) ? 0 : sum_balance / sum_allowance * 100;
tr.Cells.AddTextCell(string.Format("{0:0}%", sum_variance), "reportTotal");
sum_obligations = 0;
sum_income = 0;
sum_adjustments = 0;
sum_totalobligations = 0;
sum_q_projection = 0;
sum_q_allowance = 0;
sum_q_balance = 0;
sum_projection = 0;
sum_allowance = 0;
sum_balance = 0;
sum_variance = 0;
}
else
{
dr_col = dtState.Select(String.Format("GROUP_CD={0}", (int)drConfig["GROUP_CD"]));
if (dr_col.Length == 0)
{
obligations = 0;
income = 0;
adjustments = 0;
totalobligations = 0;
projection = 0;
allowance = 0;
balance = 0;
variance = 0;
q_projection = 0;
q_allowance = 0;
q_balance = 0;
}
else
{
obligations = (decimal)dr_col[0]["Obligations"];
income = (decimal)dr_col[0]["Income"];
adjustments = (decimal)dr_col[0]["Adjustments"];
totalobligations = (decimal)dr_col[0]["TotalObligations"];
projection = (decimal)dr_col[0]["Projection"];
allowance = (decimal)dr_col[0]["Allowance"];
balance = allowance - projection;
variance = (allowance == 0) ? 0 : balance / allowance * 100;
q_projection = (decimal)dr_col[0]["Projection_Q"];
q_allowance = (decimal)dr_col[0]["Allowance_Q"];
q_balance = q_allowance - q_projection;
}
sum_obligations += obligations;
total_obligations += obligations;
sum_income += income;
total_income += income;
sum_adjustments += adjustments;
total_adjustments += adjustments;
sum_totalobligations += totalobligations;
total_totalobligations += totalobligations;
sum_projection += projection;
total_projection += projection;
sum_allowance += allowance;
total_allowance += allowance;
sum_q_projection += q_projection;
sum_q_allowance += q_allowance;
total_q_projection += q_projection;
total_q_allowance += q_allowance;
//complete the row:
if (!BuildReportForExcel)
{
td = DisplayedMoneyCell(obligations, "reportAmountLink");
td.AddOnClick(String.Format(cell_link_onclick, (int)FundsReviewViewMode.fvObligations, (int)drConfig["GROUP_CD"]));
tr.Cells.Add(td);
}
else
{
tr.Cells.AddDisplayedMoneyCell(obligations, "reportAmount");
}
if (!BuildReportForExcel)
{
td = DisplayedMoneyCell(income, "reportAmountLink");
td.AddOnClick(String.Format(cell_link_onclick, (int)FundsReviewViewMode.fvIncome, (int)drConfig["GROUP_CD"]));
tr.Cells.Add(td);
}
else
{
tr.Cells.AddDisplayedMoneyCell(income, "reportAmount");
}
tr.Cells.AddDisplayedMoneyCell(adjustments, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(totalobligations, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(q_projection, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(q_allowance, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(q_balance, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(projection, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(allowance, "reportAmount");
tr.Cells.AddDisplayedMoneyCell(balance, "reportAmount");
tr.Cells.AddTextCell(String.Format("{0:0}%", variance), "reportAmount");
}
TableToDraw.Rows.Add(tr);
}
/*****************************************************************************************/
//add Grand Total row:
tr = new HtmlTableRow();
tr.AddReportRow();
//display sum totals for the whole report:
tr.Cells.AddHeaderCell("Grand Total", "reportHeaderBlueNAlign", "left");
tr.Cells.AddDisplayedMoneyCell(total_obligations, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_income, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_adjustments, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_totalobligations, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_q_projection, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_q_allowance, "reportHeaderBlueNAlign", "right");
total_q_balance = total_q_allowance - total_q_projection;
tr.Cells.AddDisplayedMoneyCell(total_q_balance, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_projection, "reportHeaderBlueNAlign", "right");
tr.Cells.AddDisplayedMoneyCell(total_allowance, "reportHeaderBlueNAlign", "right");
total_balance = total_allowance - total_projection;
tr.Cells.AddDisplayedMoneyCell(total_balance, "reportHeaderBlueNAlign", "right");
total_variance = (int)((total_allowance == 0) ? 0 : total_balance / total_allowance * 100);
tr.Cells.AddHeaderCell(String.Format("{0:0}%", total_variance), "reportHeaderBlueNAlign", "right");
TableToDraw.Rows.Add(tr);
/*****************************************************************************************/
tr = new HtmlTableRow();
tr.AddReportRow();
//complete empty row:
td = new HtmlTableCell();
td.ColSpan = 12;
td.Height = "8";
td.InnerText = "";
tr.Cells.Add(td);
TableToDraw.Rows.Add(tr);
//second part of Report:
/*****************************************************************************************/
tr = new HtmlTableRow();
//1 column
td = new HtmlTableCell();
td.AddReportHeaderBlue();
tr.Cells.Add(td);
//2,3,4,5 columns
td = new HtmlTableCell();
td.ColSpan = 4;
td.InnerText = "RWA and Over/Under Accrued Entries";
td.Align = "center";
td.AddReportHeaderBlue();
tr.Cells.Add(td);
//6,7,8,9 columns
td = new HtmlTableCell();
td.ColSpan = 4;
td.InnerText = "One-Time Adjustments";
td.Align = "center";
td.AddReportHeaderBlue();
tr.Cells.Add(td);
//10,11,12 columns
td = new HtmlTableCell();
td.ColSpan = 3;
td.InnerText = "Expected by Year End";
td.Align = "center";
td.AddReportHeaderBlue();
tr.Cells.Add(td);
TableToDraw.Rows.Add(tr);
//headers row:
tr = new HtmlTableRow();
tr.Cells.AddHeaderCell("Adjustments:", "reportHeaderBlueNAlign", "left");
td = HeaderCellWithTooltip(CAPTION_RWA, "reportHeaderGreen");
if (!BuildReportForExcel)
{
td.AddOnClick(String.Format("open_rwa_popup('org={0}&fy={1}&bm={2}&bl={3}');", Organization, FiscalYear, BookMonth, BusinessLine));
}
tr.Cells.Add(td);
tr.Cells.AddHeaderCellWithTooltip(CAPTION_OVERUNDERACCRUED, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_OVERUNDERACCRUED));
td = new HtmlTableCell();
td.ColSpan = 2;
td.InnerText = CAPTION_EXPLANATION;
td.Align = "center";
td.AddReporHeaderGreen();
tr.Cells.Add(td);
tr.Cells.AddHeaderCellWithTooltip(CAPTION_ONETIMEADJ, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_ONETIMEADJ));
td = new HtmlTableCell();
td.ColSpan = 3;
td.InnerText = CAPTION_EXPLANATION;
td.Align = "center";
td.AddReporHeaderGreen();
tr.Cells.Add(td);
//tr.Cells.Add(HeaderCellWithTooltip(CAPTION_EXPLANATION, "reportHeaderGreen"));
tr.Cells.AddHeaderCellWithTooltip(CAPTION_EXPBYYEAREND, "reportHeaderGreen", BuildReportForExcel, AddTooltipPopup(CAPTION_EXPBYYEAREND));
td = new HtmlTableCell();
td.ColSpan = 2;
td.InnerText = CAPTION_EXPLANATION;
td.Align = "center";
td.AddReporHeaderGreen();
tr.Cells.Add(td);
//tr.Cells.Add(HeaderCellWithTooltip(CAPTION_EXPLANATION, "reportHeaderGreen"));
TableToDraw.Rows.Add(tr);
/*****************************************************************************************/
foreach (DataRow drConfig in dsConfig.Tables[0].Rows)
{
if ((int)drConfig["OrderNumber"] == 0)
{
tr = new HtmlTableRow();
tr.AddReportRow();
tr.Cells.AddHeaderCell(drConfig["Name"].ToString(), drConfig["CaptionStyle"].ToString());
//complete empty row:
td = new HtmlTableCell();
td.AddReportTable();
td.ColSpan = 11;
td.InnerText = "";
tr.Cells.Add(td);
TableToDraw.Rows.Add(tr);
}
else if ((int)drConfig["OrderNumber"] != 99)
//skip the 'Totals' row in this part!
{
tr = new HtmlTableRow();
tr.AddReportRow();
tr.Cells.AddHeaderCell(drConfig["Name"].ToString(), drConfig["CaptionStyle"].ToString());
dr_col = dtState.Select(String.Format("GROUP_CD={0}", (int)drConfig["GROUP_CD"]));
if (dr_col.Length == 0)
{
rwa = 0;
overunderaccrued = 0;
onetimeadj_desc = "";
onetimeadj = 0;
onetimeadj_desc = "";
expbyend = 0;
expbyend_desc = "";
q_adjustments = 0;
}
else
{
rwa = (decimal)dr_col[0]["RWA"];
overunderaccrued = (decimal)dr_col[0]["OverUnderAccrued"];
onetimeadj = (decimal)dr_col[0]["OneTimeAdjustments"];
expbyend = (decimal)dr_col[0]["ExpByYearEnd"];
q_adjustments = (decimal)dr_col[0]["Adjustments_Q"];
//no description for BusinessLine
if (BusinessLine == "")
{
overunder_desc = (string)Utility.GetNotNullValue(dr_col[0]["OverUnderAccrDesc"], "String");
onetimeadj_desc = (string)Utility.GetNotNullValue(dr_col[0]["OneTimeAdjDesc"], "String");
expbyend_desc = (string)Utility.GetNotNullValue(dr_col[0]["ExpByYearEndDesc"], "String");
}
else
{
overunder_desc = "";
onetimeadj_desc = "";
expbyend_desc = "";
}
}
//complete the row:
tr.Cells.AddDisplayedMoneyCell(rwa, "reportAmount");
td = DisplayedMoneyCell(overunderaccrued, "reportAmountLink");
if (!BuildReportForExcel)
{
td.AddOnClick(String.Format(cell_popup_onclick, (int)FundsStatusUserEntryType.ueOverUnderAccrued, (int)drConfig["GROUP_CD"], ""));
}
tr.Cells.Add(td);
td = new HtmlTableCell();
td.ColSpan = 2;
td.InnerText = overunder_desc;
tr.Cells.Add(td);
td = DisplayedMoneyCell(onetimeadj, "reportAmountLink");
if (!BuildReportForExcel)
{
td.AddOnClick(String.Format(cell_popup_onclick, (int)FundsStatusUserEntryType.ueOneTimeAdjustment, (int)drConfig["GROUP_CD"], (string)Utility.GetNotNullValue(drConfig["AddAdjustment"], "String")));
}
tr.Cells.Add(td);
td = new HtmlTableCell();
td.ColSpan = 3;
td.InnerText = onetimeadj_desc;
tr.Cells.Add(td);
td = DisplayedMoneyCell(expbyend, "reportAmountLink");
if (!BuildReportForExcel)
{
td.AddOnClick(String.Format(cell_popup_onclick, (int)FundsStatusUserEntryType.ueExpectedByYearEnd, (int)drConfig["GROUP_CD"], ""));
}
tr.Cells.Add(td);
td = new HtmlTableCell();
td.ColSpan = 2;
td.InnerText = expbyend_desc;
tr.Cells.Add(td);
TableToDraw.Rows.Add(tr);
}
}
tr = new HtmlTableRow();
tr.AddReportHeaderBlue();
//complete empty row:
td = new HtmlTableCell();
td.ColSpan = 12;
td.Height = "8";
td.InnerText = "";
tr.Cells.Add(td);
TableToDraw.Rows.Add(tr);
/*****************************************************************************************/
}
