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; } }
public void InitControls() { DataRow dr; DataSet ds; //init Business Line list: ds = FundStatus.GetBusinessLineList().ToDataSet(); if (ds == null || ds.Tables[0].Rows.Count == 0) { throw new Exception("Application error. Business Line list is missing."); } ddlBusinessLine.DataSource = ds.Tables[0]; ddlBusinessLine.DataTextField = "BLDesc"; ddlBusinessLine.DataValueField = "BL_CD"; ddlBusinessLine.DataBind(); //init Organizations list: DataTable dt; if (ScreenType == (int)FundsStatusScreenType.stFundsSearch) { dt = OrganizationsDT.Copy(); dr = dt.NewRow(); dr["ORG_CD"] = ""; dt.Rows.InsertAt(dr, 0); } else { dt = OrganizationsDT; } ddlOrganization.DataSource = dt; ddlOrganization.DataTextField = "ORG_CD"; ddlOrganization.DataValueField = "ORG_CD"; ddlOrganization.DataBind(); //init fiscal year list: ddlFiscalYear.DataSource = LoadFiscalYearList(); ddlFiscalYear.DataTextField = "Text"; ddlFiscalYear.DataValueField = "Value"; ddlFiscalYear.DataBind(); ddlFiscalYear.SelectedValue = DateTime.Now.Year.ToString(); ArrayList al; ListItem li; if (ScreenType != (int)FundsStatusScreenType.stFundSummaryReport) { //init book month list for selected fiscal year: al = new ArrayList(); if (ScreenType == (int)FundsStatusScreenType.stFundsSearch) { li = new ListItem("", ""); al.Add(li); } if (ScreenType == (int)FundsStatusScreenType.stFundsReview) { li = new ListItem("All Available", "00"); al.Add(li); } var str_month = ""; for (var i = 1; i < 13; i++) { str_month = String.Format("{0:MMMM}", DateTime.Parse((i < 10 ? "0" + i.ToString() : i.ToString()) + "/2000")); li = new ListItem(str_month, i < 10 ? "0" + i.ToString() : i.ToString()); al.Add(li); } ddlBookMonth.DataSource = al; ddlBookMonth.DataTextField = "Text"; ddlBookMonth.DataValueField = "Value"; ddlBookMonth.DataBind(); } else { lblBookMonth.Visible = false; ddlBookMonth.Visible = false; } if (ScreenType == (int)FundsStatusScreenType.stFundsReview || ScreenType == (int)FundsStatusScreenType.stFundsSearch) { //init view mode: al = new ArrayList(); li = new ListItem("Obligations", ((int)FundsReviewViewMode.fvObligations).ToString()); al.Add(li); li = new ListItem("Income", ((int)FundsReviewViewMode.fvIncome).ToString()); al.Add(li); li = new ListItem("One Time Adjustments", ((int)FundsReviewViewMode.fvOneTimeAdjustments).ToString()); al.Add(li); ddlView.DataSource = al; ddlView.DataTextField = "Text"; ddlView.DataValueField = "Value"; ddlView.DataBind(); } else { trViewSelection.Visible = false; } if (ScreenType == (int)FundsStatusScreenType.stFundsSearch) { //init Summary Functions list: ds = FundStatus.GetSummaryFunctionsList().ToDataSet(); dr = ds.Tables[0].NewRow(); dr["FUNC_CD"] = ""; ds.Tables[0].Rows.InsertAt(dr, 0); lstSumFunc.DataSource = ds.Tables[0]; lstSumFunc.DataTextField = "FUNC_CD"; lstSumFunc.DataValueField = "FUNC_CD"; lstSumFunc.DataBind(); //init all object class codes: ds = FundStatus.GetObjectClassCodeList().ToDataSet(); dr = ds.Tables[0].NewRow(); dr["OBJ_CLASS_CD"] = ""; dr["OC_DESC"] = ""; ds.Tables[0].Rows.InsertAt(dr, 0); lstOCCode.DataSource = ds.Tables[0]; lstOCCode.DataTextField = "OC_DESC"; lstOCCode.DataValueField = "OBJ_CLASS_CD"; lstOCCode.DataBind(); btnClear.Visible = true; btnEmail.Visible = true; } else { trSearchCriteria.Visible = false; } if (ScreenType == (int)FundsStatusScreenType.stFundsReview) { btnExpand.Visible = true; } if (ScreenType == (int)FundsStatusScreenType.stFundStatusReport || ScreenType == (int)FundsStatusScreenType.stFundSummaryReport) { btnSubmit.Text = "View Report"; btnSubmit.ToolTip = "View Report"; } else { btnSubmit.Text = "Search"; btnSubmit.ToolTip = "Search"; } //add client side functions: btnExcel.AddOnClick("javascript:return funds_data_to_excel(" + ScreenType.ToString() + ");"); }
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); } }