public void FillControls()
{
try
{
Search.AddOnClick("return checkOrg();");
DataRow dr;
var dt = FundStatus.GetBusinessLineList().ToDataSet().Tables[0];
dt.Columns[0].ReadOnly = true;
ddgBLine.Table = dt;
//init Organizations list:
var ds = FundStatus.GetOrganizationList().ToDataSet();
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Application error. Organization list is missing.");
}
dt = ds.Tables[0];
while (dt.Columns.Count != 2)
{
dt.Columns.RemoveAt(2);
}
dt.Columns[1].ReadOnly = true;
ddlOrganization.Table = dt;//"ORG_CD";
//init fiscal year list:
var doc = new XmlDocument();
doc.Load(Request.PhysicalApplicationPath + "FundStatus\\include\\FS_SupportedFiscalYears.xml");
dt = new DataTable();
dt.Columns.Add("Year");
foreach (XmlNode node in doc.DocumentElement.ChildNodes)
{
dr = dt.NewRow();
dr[0] = node.InnerText;
dt.Rows.Add(dr);
}
var s = FiscalYear;
ddlFiscalYear.Table = dt;
if (s == "")
{
FiscalYear = DateTime.Now.Year.ToString();
}
ds = FundStatus.GetBAList().ToDataSet();
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Application error. No Budget Activity found.");
}
s = BudgetActivity;
ddgBA.Table = ds.Tables[0];
if (s == "")
{
BudgetActivity = Settings.Default.Default_BUDGET_ACTIVITY;
}
//init book month list for selected fiscal year:
dt = new DataTable();
dt.Columns.Add("BOOK_MONTH");
for (var i = 0; i < 12; i++)
{
dr = dt.NewRow();
dr[0] = Month[i];
dt.Rows.Add(dr);
}
ddlBookMonth.Table = dt;
if (VMode)
{
//init view mode:
dt = new DataTable();
dt.Columns.Add("Mode");
dr = dt.NewRow();
dr[0] = "Obligations";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "Income";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "One Time Adjustments";
dt.Rows.Add(dr);
ddlView.Table = dt;
}
if (GroupFunc)
{
//init Report Function Group Code list:
dt = FundStatus.GetReportFunctionGroupList().Tables[0];
dt.Columns[0].ReadOnly = true;
lstGroupCD.Table = dt;
}
if (Function)
{ //init Summary Functions list:
lstSumFunc.Table = FundStatus.GetSummaryFunctionsList().ToDataSet().Tables[0];
}
if (OCC)
{ //init all object class codes:
dt = FundStatus.GetObjectClassCodeList().ToDataSet().Tables[0];
dt.Columns[0].ReadOnly = true;
lstOCCode.Table = dt;
}
if (CostElem)
{
dt = FundStatus.GetCostElemList().ToDataSet().Tables[0];
dt.Columns[0].ReadOnly = true;
mgCostElem.Table = dt;
}
}
catch (Exception ex)
{
lblCriteriaMsg.CssClass = "errorsum";
lblCriteriaMsg.Text = ex.Message;
}
}
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);
}
}
private void BuildFundsSearchTable()
{
//get Query parameters:
var ht = (new PageBase()).FundsSearchSelectedValues;
var business_line = (string)ht[HT_KEY_BL];
var organization = (string)ht[HT_KEY_ORG];
var fiscal_year = (string)ht[HT_KEY_YEAR];
var BudgetActivity = (string)ht[HT_KEY_BA];
var book_month = (string)ht[HT_KEY_BOOK_MONTH];
var group_cd = (string)ht[HT_KEY_GROUP_CD];
var summary_function = (string)ht[HT_KEY_SUM_FUNC];
var oc_code = (string)ht[HT_KEY_OC];
var CostElem = (string)ht[HT_KEY_CE];
var doc_number = (string)ht[HT_KEY_DOC_NUM];
var obl_income_view = Int32.Parse((string)ht[HT_KEY_VIEW]);
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:
if (business_line != "")
{
var ds_t = FundStatus.GetBusinessLineList().ToDataSet();
if (ds_t != null && ds_t.Tables[0] != null && ds_t.Tables[0].Rows.Count > 0)
{
var dr_col = ds_t.Tables[0].Select("BL_CD='" + business_line + "'");
if (dr_col.Length > 0)
{
var bl_name = dr_col[0]["BLDesc"].ToString();
tr = new HtmlTableRow();
tr.Cells.AddCellLeft("Business Line:", "reportCaption2", 2);
tr.Cells.AddCellLeft(bl_name, "title2", 12);
Sheet.Rows.Add(tr);
}
}
}
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(BudgetActivity, "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, BudgetActivity, 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, false);
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);
}
}
