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); } }