private void BuildFundsSearchTable(int EmailRequestID)
{
//get Query parameters from tblHistory by EmailRequestID:
var history = Report.GetHistoryRecordByEmailRequest(EmailRequestID);
if (!history.Any())
{
throw new Exception("There is no enough data to complete search. Please try again to request another report. Thank you.");
}
//var criteria_fields = (string)Utility.GetNotNullValue(history.Tables[0].Rows[0]["CustomField01"], "String");
var criteria_fields = history.First().CustomField01;
var criteria_values = history.First().CustomField02;
var business_line = "";
var organization = "";
var fiscal_year = "";
var book_month = "";
var budget_activity = "";
var group_cd = "";
var summary_function = "";
var oc_code = "";
var CostElem = "";
var doc_number = "";
var obl_income_view = 1; //default value
var arr_fields = criteria_fields.Split(new char[] { '|' });
var arr_values = criteria_values.Split(new char[] { '|' });
for (var i = 0; i < arr_fields.Length; i++)
{
switch (arr_fields[i])
{
case HT_KEY_ORG:
organization = arr_values[i];
break;
case HT_KEY_YEAR:
fiscal_year = arr_values[i];
break;
case HT_KEY_BOOK_MONTH:
book_month = arr_values[i];
break;
case HT_KEY_VIEW:
obl_income_view = Int32.Parse(arr_values[i]);
break;
case HT_KEY_SUM_FUNC:
summary_function = arr_values[i];
break;
case HT_KEY_OC:
oc_code = arr_values[i];
break;
case HT_KEY_CE:
CostElem = arr_values[i];
break;
case HT_KEY_DOC_NUM:
doc_number = arr_values[i];
break;
case HT_KEY_BA:
budget_activity = arr_values[i];
break;
case HT_KEY_BL:
business_line = arr_values[i];
break;
case HT_KEY_GROUP:
group_cd = arr_values[i];
break;
}
}
var title_text = "";
switch (obl_income_view)
{
case (int)FundsReviewViewMode.fvObligations:
title_text = "Obligations";
break;
case (int)FundsReviewViewMode.fvIncome:
title_text = "Income";
break;
case (int)FundsReviewViewMode.fvOneTimeAdjustments:
title_text = "One Time Adjustments";
break;
}
//start Excel headers:
var tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Funds Search Results", "title", 14);
Sheet.Rows.Add(tr);
tr = new HtmlTableRow();
tr.Cells.AddCellLeft(title_text, "title2", 14);
Sheet.Rows.Add(tr);
//display query parameters in Excel:
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Organization:", "reportCaption2", 2);
tr.Cells.AddCellLeft(organization, "title2", 12);
Sheet.Rows.Add(tr);
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Fiscal Year:", "reportCaption2", 2);
tr.Cells.AddCellLeft(fiscal_year, "title2", 12);
Sheet.Rows.Add(tr);
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Book Month:", "reportCaption2", 2);
if (book_month == "")
{
tr.Cells.AddCellLeft("All Available", "title2", 12);
}
else
{
var s = "";
var ss = book_month.Split(new char[] { ',' });
foreach (var m in ss)
{
s += String.Format("{0:MMMM}", DateTime.Parse(m + "/" + fiscal_year)) + ", ";
}
s = s.Substring(0, s.Length - 2);
tr.Cells.AddCellLeft(s, "title2", 12);
}
Sheet.Rows.Add(tr);
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Budget Activity:", "reportCaption2", 2);
tr.Cells.AddCellLeft(budget_activity, "title2", 12);
Sheet.Rows.Add(tr);
if (group_cd != null && group_cd != "")
{
var ds_g = FundStatus.GetReportFunctionGroupList();
if (ds_g != null && ds_g.Tables[0] != null && ds_g.Tables[0].Rows.Count > 0)
{
var dr_col = ds_g.Tables[0].Select("GROUP_CD=" + group_cd);
if (dr_col.Length > 0)
{
var group_name = dr_col[0]["Name"].ToString();
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Filter by Function Report Group:", "reportCaption2", 2);
tr.Cells.AddCellLeft(group_name, "tableBold", 12);
Sheet.Rows.Add(tr);
}
}
}
if (summary_function != null && summary_function != "")
{
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Filter by Functions:", "reportCaption2", 2);
tr.Cells.AddCellLeft(summary_function, "tableBold", 12);
Sheet.Rows.Add(tr);
}
if (oc_code != null && oc_code != "")
{
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Filter by OC Code:", "reportCaption2", 2);
tr.Cells.AddCellLeft(oc_code, "tableBold", 12);
Sheet.Rows.Add(tr);
}
if (CostElem != null && CostElem != "")
{
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Filter by Cost Elements:", "reportCaption2", 2);
tr.Cells.AddCellLeft(CostElem, "tableBold", 12);
Sheet.Rows.Add(tr);
}
if (doc_number != "")
{
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("DocNumber like:", "reportCaption2", 2);
tr.Cells.AddCellLeft(doc_number, "tableBold", 12);
Sheet.Rows.Add(tr);
}
tr = new HtmlTableRow();
tr.Cells.AddCell("", "", 14);
Sheet.Rows.Add(tr);
p_iHeaderLines = Sheet.Rows.Count + 1;
//get the data:
var records = 0;
decimal total_amount = 0;
object obj_org_code = (organization != "") ? organization : null;
object obj_book_month = (book_month != "") ? book_month : null;
object obj_sum_func = (summary_function != "") ? summary_function : null;
object obj_oc_code = (oc_code != "") ? oc_code : null;
object obj_CE = (CostElem != "") ? CostElem : null;
object obj_doc_num = (doc_number != "") ? doc_number : null;
object obj_group_cd = (group_cd != "") ? group_cd : null;
var ds = FundStatus.GetSearchResults(obl_income_view, fiscal_year, budget_activity, out records, out total_amount,
obj_org_code, obj_book_month, obj_group_cd, obj_sum_func, obj_oc_code, obj_CE, obj_doc_num, Settings.Default.QueryResultMaxRecords, true);
if (ds == null || records == 0)
{
//no records:
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("No records found", "reportCaption", 14);
Sheet.Rows.Add(tr);
p_iHeaderLines = 0;
}
else
{
var dt = ds.Tables[0].Clone();
dt.Columns["Amount"].DataType = typeof(int);
foreach (DataRow r in ds.Tables[0].Rows)
{
dt.ImportRow(r);
}
dt.Columns["Amount"].ColumnName = "$ Amount";
foreach (DataRow r in dt.Rows)
{
if (r["book month"].ToString() != "")
{
r["book month"] = String.Format("{0:MMMM}", DateTime.Parse(r["book month"].ToString() + "/2000"));
}
r["$ Amount"] = Convert.ToInt32(r["$ Amount"]);
}
BuildGeneralExcelOutput(dt);
}
}
