protected void ShowResults()
{
try
{
btns.Visible = mgRezult.Visible = true;
SearchCriteria.Visible = false;
var records = 0;
decimal total_amount = 0;
object business_line = (SearchCriteria.BusinessLine != "") ? SearchCriteria.BusinessLine : null;
object org_code = (SearchCriteria.Organization != "") ? SearchCriteria.Organization : null;
org_code = ClearDoubles(org_code.ToString());
object book_month = (SearchCriteria.BookMonth != "") ? SearchCriteria.BookMonth : null;
object group_cd = (SearchCriteria.GroupCD != "") ? SearchCriteria.GroupCD : null;
object sum_func = (SearchCriteria.SummaryFunction != "") ? SearchCriteria.SummaryFunction : null;
object oc_code = (SearchCriteria.ObjClassCode != "") ? SearchCriteria.ObjClassCode : null;
object CostElem = (SearchCriteria.CostElement != "") ? SearchCriteria.CostElement : null;
object doc_num = (SearchCriteria.DocNumber != "") ? SearchCriteria.DocNumber : null;
var Message = "<font style='color:Navy'>Search Criteria:</font><font style='color:black'><br /> ";
if (SearchCriteria.ViewMode == (int)FundsReviewViewMode.fvObligations)
{
Message += "Obligations,";
}
else if (SearchCriteria.ViewMode == (int)FundsReviewViewMode.fvIncome)
{
Message += "Income,";
}
else
{
Message += "One Time Adjustments,";
}
Message += " Fiscal Year: " + SearchCriteria.FiscalYear;
if (book_month != null)
{
Message += ", Book Month: " + SearchCriteria.BookMonthNames.ToString().Replace(",", ", ");
}
Message += "<br />Budget Activity: " + SearchCriteria.BudgetActivity;
if (business_line != null)
{
Message += ", Business Line: " + SearchCriteria.BusinessLineName;
}
if (org_code != null)
{
Message += "<br />Organizations: " + org_code.ToString().Replace(",", ", ");
}
if (doc_num != null)
{
Message += "<br />Document: " + doc_num;
}
if (group_cd != null)
{
Message += "<br/>Function Report Group: " + SearchCriteria.GroupCDName;
}
if (sum_func != null)
{
Message += "<br/>Summary Functions: " + sum_func.ToString().Replace(",", ", ");
}
if (oc_code != null)
{
Message += "<br/>OCC Functions: " + oc_code.ToString().Replace(",", ", ");
}
if (CostElem != null)
{
Message += "<br/>Cost Elements: " + CostElem.ToString().Replace(",", ", ");
}
Message += "</font><br/>";
var ds = FundStatus.GetSearchResults(SearchCriteria.ViewMode, SearchCriteria.FiscalYear, SearchCriteria.BudgetActivity, out records, out total_amount,
org_code, book_month, group_cd, sum_func, oc_code, CostElem, doc_num, Settings.Default.QueryResultMaxRecords, false);
if (ds == null || records == 0)
{
Message += "No records found.";
}
else
{
if (records > Settings.Default.QueryResultMaxRecords)
{
Message += String.Format("{0:0,0} records found. Total Amount {1:$0,0}. <br />Only first {2:0,0} records are displayed. Please narrow your search criteria or request results by email.",
records, total_amount, Settings.Default.QueryResultMaxRecords);
}
else if (records > 9)
{
Message += String.Format("{0:0,0} records found. Total Amount {1:$0,0}.", records, total_amount);
}
else
{
Message += String.Format("{0} records found. Total Amount {1:$0,0}.", records, total_amount);
}
var dt = ds.Tables[0].Clone();
dt.Columns["Amount"].DataType = typeof(string);
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"] = String.Format("{0:0,0}", Convert.ToInt64(Convert.ToDecimal(r["$ Amount"])));
if (r["$ Amount"].ToString() == "00")
{
r["$ Amount"] = "0";
}
}
mgRezult.Table = dt;
}
lblMessages.Text = Message;
}
catch (System.Data.SqlClient.SqlException ex)
{
//"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
if (ex.Number == -2)
{
//give a message to the user:
throw new Exception("Search results contain too many records or the server is not responding. You can try again or narrow your seach criteria. Thank you.");
}
else
{
throw ex;
}
}
}
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);
}
}
