protected void Page_Load(object sender, EventArgs e)
{
StoredProcedures sp = new StoredProcedures();
if (!IsPostBack)
{
string userType = (string)Session["UserType"];
if (userType == null)
{
Response.Redirect("Login.aspx?category=User", true);
}
else
{
String fiscalYear = (String)Session["FiscalYear"];
DataTable costDriverstbl = DTable();
Session["CostDriversTbl"] = costDriverstbl;
DataTable costDriversAdded = CostDriversRightPanel();
Session["CostDriversAdded"] = costDriversAdded;
DataSet ds = sp.GetCostDriversTypes();
SelectCostDriverDropDownList.DataSource = ds.Tables[0];
SelectCostDriverDropDownList.DataTextField = "costDriver";
SelectCostDriverDropDownList.DataValueField = "costDriver";
SelectCostDriverDropDownList.DataBind();
SelectCostDriverLabel.Text = "Select Cost Driver " + fiscalYear;
AddedCostDrivers();
}
}
}
protected void CreateFYButton_Click(object sender, EventArgs e)
{
StoredProcedures sp = new StoredProcedures();
String fiscalYear = String.Concat("FY", YearDropDownList.SelectedValue);
Session["FiscalYear"] = fiscalYear;
sp.CreateFiscalYear(fiscalYear);
Response.Redirect("AddCHG.aspx");
}
protected void AddExpense_Click(object sender, EventArgs e)
{
InfoLabel.Visible = false;
String fiscalYear = (String)Session["FiscalYear"];
StoredProcedures sp = new StoredProcedures();
sp.MiscellaneousExpenseInsert(fiscalYear, MiscellaneousExpenseTextBox.Text, MiscellaneousAccountTextBox.Text);
PopulateMiscellaneous();
// PopulateGridView();
//InstractionalCompensationPanel.Visible = false;
}
public void DisplayAssesments()
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.GetAssessmentsGenerateReport(FinancialYearDropDownList.SelectedValue);
DataTable tbl = ds.Tables[0];
AssessmentsGridView.DataSource = tbl;
AssessmentsGridView.DataBind();
AssessmentsPanel.Visible = true;
AssessmentsLabel.Text = "Assessments " + FinancialYearDropDownList.SelectedValue;
}
public void DisplayAssesments()
{
string fiscalYear = (string)ViewState["MostRecentFiscalYear"];
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.GetAssessmentsGenerateReport(fiscalYear);
DataTable tbl = ds.Tables[0];
//AssessmentsGridView.DataSource = tbl;
//AssessmentsGridView.DataBind();
//AssessmentsGridviewPanel.Visible = true;
//AssessmentsLabel.Text = "Assessments " + fiscalYear;
}
//protected void CreateFiscalYearButton_Click(object sender, EventArgs e)
//{
// ArrayList fiscalYearData = (ArrayList)Session["FiscalYearData"];
// String fiscalYear = fiscalYearData[0].ToString();
// ArrayList chg = (ArrayList)fiscalYearData[1];
// StoredProcedures sp = new StoredProcedures();
// DataTable tbl = (DataTable)Session["CostDriversTbl"];
// foreach (DataRow row in tbl.Rows)
// {
// sp.InsertAllocatedCosts(fiscalYear, row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString());
// }
// if (Session["FiscalYear"] == null)
// {
// sp.RevenueSummaryInsert(fiscalYear);
// }
// else
// {
// sp.UpdateRevenueExpenseSummary(fiscalYear);
// }
//}
protected void SaveCostDriverButton_Click(object sender, EventArgs e)
{
Alert.Visible = false;
Error.Visible = false;
DataTable costDriversTbl = (DataTable)Session["CostDriversTbl"];
TextBox tbox = new TextBox();
String fiscalYear = (String)Session["FiscalYear"];
for (int row = 0; row < SubmitCostDriverGridView.Rows.Count; row++)
{
StoredProcedures sp = new StoredProcedures();
String costDriver = SelectCostDriverDropDownList.SelectedValue;
String metricType = SubmitCostDriverGridView.Rows[row].Cells[0].Text;
tbox = (TextBox)SubmitCostDriverGridView.Rows[row].Cells[1].FindControl("PercentageTextBox");
String rate = String.Empty;
if (tbox.Text != "")
{
rate = (Double.Parse(tbox.Text) / 100).ToString();
}
else
{
rate = tbox.Text;
}
tbox = (TextBox)SubmitCostDriverGridView.Rows[row].Cells[2].FindControl("TotalMetricTextBox");
String totalMetric = tbox.Text;
tbox = (TextBox)SubmitCostDriverGridView.Rows[row].Cells[3].FindControl("MetricsTextBox");
String metrics = tbox.Text;
String costToAllocate = CostToAllocateTextBox.Text;
sp.InsertAllocatedCosts(fiscalYear, costDriver, metricType, rate, totalMetric, metrics, costToAllocate);
}
StoredProcedures sp2 = new StoredProcedures();
sp2.UpdateRevenueExpenseSummary(fiscalYear);
AddedCostDrivers();
}
private void HighlightExistedFiscalYears()
{
StoredProcedures sp = new StoredProcedures();
DataSet fiscalYears = sp.GetExistedFiscalYears();
for (int i = 0; i < YearDropDownList.Items.Count; i++)
{
foreach (DataRow row2 in fiscalYears.Tables[0].Rows)
{
String fiscalYear = row2[0].ToString();
if (fiscalYear.Substring(fiscalYear.Length - 4) == YearDropDownList.Items[i].Value.ToString())
{
YearDropDownList.Items[i].Attributes.Add("style", "color: silver");
}
}
}
}
public void DisplayTotals(string fiscalYear)
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.RetriveExpendituresTotals(fiscalYear);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][1].ToString() == String.Empty)
{
ds.Tables[0].Rows[i].Delete();
}
}
if (ds.Tables[0].Rows.Count != 0)
{
ExpendutersTotalsPanel.Visible = true;
ExpendutersTotalsGridView.DataSource = ds;
ExpendutersTotalsGridView.DataBind();
}
}
protected void ExpendutersTotalsGridView_OnRowDataBound(Object sender, GridViewRowEventArgs e)
{
String fiscalYear = (String)Session["FiscalYear"];
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.RetriveExpendituresTotals(fiscalYear);
Double total = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row[1].ToString() != "")
{
total = Double.Parse(row[1].ToString()) + total;
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[0].Text = "Total";
e.Row.Cells[1].Text = String.Format("{0:C}", total);
}
}
protected void SelectButton_Click(object sender, EventArgs e)
{
Alert.Visible = false;
Error.Visible = false;
CostToAllocateTextBox.Text = "";
SubmitCostDriverPanel.Visible = true;
SubmitCostDriverLabel.Text = SelectCostDriverDropDownList.SelectedValue;
StoredProcedures sp = new StoredProcedures();
TextBox tbox = new TextBox();
String fiscalYear = (String)Session["FiscalYear"];
DataSet ds1 = sp.DisplaySupportUnitMetricsEdit(fiscalYear, SelectCostDriverDropDownList.SelectedValue);
DataSet ds2 = sp.GetCostToAllocate(fiscalYear, SelectCostDriverDropDownList.SelectedValue);
if (ds2.Tables[0].Rows.Count > 0)
{
SubmitCostDriverGridView.DataSource = ds1;
SubmitCostDriverGridView.DataBind();
CostToAllocateTextBox.Text = ds2.Tables[0].Rows[0][0].ToString();
for (int row = 0; row < SubmitCostDriverGridView.Rows.Count; row++)
{
tbox = (TextBox)SubmitCostDriverGridView.Rows[row].FindControl("PercentageTextBox");
if (ds1.Tables[0].Rows[row][3].ToString() != "")
{
tbox.Text = (double.Parse(ds1.Tables[0].Rows[row][3].ToString()) * 100).ToString();
}
tbox = (TextBox)SubmitCostDriverGridView.Rows[row].FindControl("TotalMetricTextBox");
tbox.Text = ds1.Tables[0].Rows[row][4].ToString();
tbox = (TextBox)SubmitCostDriverGridView.Rows[row].FindControl("MetricsTextBox");
tbox.Text = ds1.Tables[0].Rows[row][5].ToString();
}
}
else
{
DataSet ds = sp.GetCostDriversTypes(SelectCostDriverDropDownList.SelectedValue);
SubmitCostDriverGridView.DataSource = ds;
SubmitCostDriverGridView.DataBind();
}
DataTable costDriversTbl = (DataTable)Session["CostDriversTbl"];
int index = 0;
for (int i = 0; i < costDriversTbl.Rows.Count; i++)
{
if (costDriversTbl.Rows[i][0].ToString() == SelectCostDriverDropDownList.SelectedValue)
{
tbox = (TextBox)SubmitCostDriverGridView.Rows[index].FindControl("PercentageTextBox");
if (costDriversTbl.Rows[i][2].ToString() != "")
{
tbox.Text = (Double.Parse(costDriversTbl.Rows[i][2].ToString()) * 100).ToString();
}
tbox = (TextBox)SubmitCostDriverGridView.Rows[index].FindControl("TotalMetricTextBox");
tbox.Text = costDriversTbl.Rows[i][3].ToString();
tbox = (TextBox)SubmitCostDriverGridView.Rows[index].FindControl("MetricsTextBox");
tbox.Text = costDriversTbl.Rows[i][4].ToString();
CostToAllocateTextBox.Text = costDriversTbl.Rows[i][5].ToString();
index = index + 1;
}
}
index = 0;
}
protected void InstractionalCompenstationGridView_RowUpdating(Object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
{
TextBox TBox;
Double budget;
Double rate;
String expenseType = InstractionalCompenstationGridView.Rows[e.RowIndex].Cells[0].Text;
TBox = (TextBox)InstractionalCompenstationGridView.Rows[e.RowIndex].Cells[1].Controls[0];
budget = Double.Parse(TBox.Text);
TBox = (TextBox)InstractionalCompenstationGridView.Rows[e.RowIndex].Cells[2].Controls[0];
rate = Double.Parse(TBox.Text)/100;
StoredProcedures sp = new StoredProcedures();
sp.UpdateCompensationExpenduters("Instractional", FinancialYearDropDownList.SelectedValue, expenseType, budget, rate);
sp.UpdateRevenueExpenseSummary(FinancialYearDropDownList.SelectedValue);
DisplayRevenueExpenseSummaryReport();
DisplayDirectExpenses();
InstractionalCompenstationGridView.EditIndex = -1;
DisplayInstructionalCompensation();
FocusAnchor.Focus();
}
protected void ExportDataButton_Click(object sender, EventArgs e)
{
string fiscalYear = FinancialYearDropDownList.SelectedValue;
StoredProcedures sp = new StoredProcedures();
DataTable summaryTbl = sp.DisplayRevenueExpenseSummaryReport(FinancialYearDropDownList.SelectedValue);
DataTable grossRevenueTbl = sp.DisplayGrossTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
DataTable exportedRevenueTbl = sp.DisplayExportedTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
DataTable netRevenueTbl = sp.DisplayNetTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
DataSet differentialRevenueTbl = sp.DisplayTuitionDifferentialRevenue(FinancialYearDropDownList.SelectedValue);
DataSet tuitionRevenueGrossUndergrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Gross/Undergraduate");
DataSet tuitionRevenueGrossGrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Gross/Graduate");
DataSet tuitionRevenueExportedUndergrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Exported/Undergraduate");
DataSet tuitionRevenueExportedgrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Exported/Graduate");
DataSet tuitionRevenueNetUndergrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Net/Undergraduate");
DataSet tuitionRevenueNetGrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Net/Graduate");
DataSet assessments = sp.GetAssessmentsGenerateReport(FinancialYearDropDownList.SelectedValue);
DataTable displayDirectExpenses = sp.DisplayDirectExpenses(FinancialYearDropDownList.SelectedValue);
DataSet instarctionalCompensation = sp.DisplayCompensation(FinancialYearDropDownList.SelectedValue, "Instractional");
DataSet noninstarctionalCompensation = sp.DisplayCompensation(FinancialYearDropDownList.SelectedValue, "NonInstractional");
DataSet noncompensationExpenses = sp.DisplayNonCompensationExpenses(FinancialYearDropDownList.SelectedValue);
DataSet miscellaneous = sp.GetMiscellaneousExpenses(FinancialYearDropDownList.SelectedValue);
DataTable allocatedCostsSummary = sp.DisplayAllocatedCostsSummary(FinancialYearDropDownList.SelectedValue);
string path = Server.MapPath("exportedfiles\\");
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
File.Delete(path + fiscalYear + "RCMModel.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
if (summaryTbl.Rows.Count > 0)
{
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Excel2.Application workbook = new Excel2.Application();
workbook.Workbooks.Add("");
// ADD A WORKSHEET.
Excel2.Worksheet sheet = default(Excel2.Worksheet);
sheet = (Excel2.Worksheet)workbook.Sheets["Sheet1"];
sheet.Name = "Summary Report";
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 5;
// SHOW THE HEADER.
sheet.Cells[1, 1] = "RCM Model " + fiscalYear;
Excel2.Range range = sheet.Cells[1, 1] as Excel2.Range;
range.EntireRow.Font.Bold = true;
range.EntireRow.Font.Size = 20;
sheet.Cells[2, 1] = "Summary Report";
Excel2.Range range2 = sheet.Cells[2, 1] as Excel2.Range;
range2.EntireRow.Font.Bold = true;
range2.EntireRow.Font.Size = 18;
sheet.Range["A1:B1"].MergeCells = true; // MERGE CELLS OF THE HEADER.
sheet.Range["A2:B2"].MergeCells = true;
// SHOW COLUMNS ON THE TOP.
sheet.Cells[iRowCnt - 1, 1] = "Revenue / Expense";
sheet.Cells[iRowCnt - 1, 2] = "Amount";
for (int i = 0; i <= summaryTbl.Rows.Count - 1; i++)
{
sheet.Cells[iRowCnt, 1] = summaryTbl.Rows[i][2];
sheet.Cells[iRowCnt, 2] = summaryTbl.Rows[i][1];
iRowCnt = iRowCnt + 1;
}
Excel2.Worksheet sheet2;
sheet2 = (Excel2.Worksheet)workbook.Worksheets.Add();
sheet2.Name = "Revenue";
sheet2.Cells[1, 1] = "RCM Model " + fiscalYear;
Excel2.Range range4 = sheet2.Cells[1, 1] as Excel2.Range;
range4.EntireRow.Font.Bold = true;
range4.EntireRow.Font.Size = 20;
sheet2.Cells[2, 1] = "Revenue Report";
Excel2.Range range5 = sheet2.Cells[2, 1] as Excel2.Range;
range5.EntireRow.Font.Bold = true;
range5.EntireRow.Font.Size = 18;
sheet2.Range["A1:E1"].MergeCells = true;
sheet2.Range["A2:E2"].MergeCells = true;
iRowCnt = 6;
sheet2.Cells[iRowCnt - 2, 1] = "Gross Revenue";
sheet2.Cells[iRowCnt - 1, 1] = "Category";
sheet2.Cells[iRowCnt - 1, 2] = "Amount";
for (int i = 0; i <= grossRevenueTbl.Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = grossRevenueTbl.Rows[i][0];
sheet2.Cells[iRowCnt, 2] = grossRevenueTbl.Rows[i][1];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = "Exported Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Category";
sheet2.Cells[iRowCnt + 2, 2] = "Amount";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= exportedRevenueTbl.Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = exportedRevenueTbl.Rows[i][0];
sheet2.Cells[iRowCnt, 2] = exportedRevenueTbl.Rows[i][1];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = "Net Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Category";
sheet2.Cells[iRowCnt + 2, 2] = "Amount";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= netRevenueTbl.Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = netRevenueTbl.Rows[i][0];
sheet2.Cells[iRowCnt, 2] = netRevenueTbl.Rows[i][1];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = "Differential Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Category";
sheet2.Cells[iRowCnt + 2, 2] = "Students/CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= differentialRevenueTbl.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = differentialRevenueTbl.Tables[0].Rows[i][4];
sheet2.Cells[iRowCnt, 2] = differentialRevenueTbl.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = differentialRevenueTbl.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = differentialRevenueTbl.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = "Gross Undergraduate Tuition Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Term";
sheet2.Cells[iRowCnt + 2, 2] = "CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= tuitionRevenueGrossUndergrad.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][0];
sheet2.Cells[iRowCnt, 2] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = "Gross Graduate Tuition Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Term";
sheet2.Cells[iRowCnt + 2, 2] = "CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= tuitionRevenueGrossGrad.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = tuitionRevenueGrossGrad.Tables[0].Rows[i][0];
sheet2.Cells[iRowCnt, 2] = tuitionRevenueGrossGrad.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = tuitionRevenueGrossGrad.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = tuitionRevenueGrossGrad.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = " Exported Undergraduate Tuition Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Term";
sheet2.Cells[iRowCnt + 2, 2] = "CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= tuitionRevenueExportedUndergrad.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][0];
sheet2.Cells[iRowCnt, 2] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = " Exported Graduate Tuition Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Term";
sheet2.Cells[iRowCnt + 2, 2] = "CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= tuitionRevenueExportedgrad.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = tuitionRevenueExportedgrad.Tables[0].Rows[i][0];
sheet2.Cells[iRowCnt, 2] = tuitionRevenueExportedgrad.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = tuitionRevenueExportedgrad.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = tuitionRevenueExportedgrad.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = " Net Undergraduate Tuition Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Term";
sheet2.Cells[iRowCnt + 2, 2] = "CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= tuitionRevenueNetUndergrad.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][0];
sheet2.Cells[iRowCnt, 2] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet2.Cells[iRowCnt + 1, 1] = " Net Graduate Tuition Revenue";
sheet2.Cells[iRowCnt + 2, 1] = "Term";
sheet2.Cells[iRowCnt + 2, 2] = "CHG";
sheet2.Cells[iRowCnt + 2, 3] = "Rate";
sheet2.Cells[iRowCnt + 2, 4] = "Total";
iRowCnt = iRowCnt + 3;
for (int i = 0; i <= tuitionRevenueNetGrad.Tables[0].Rows.Count - 1; i++)
{
sheet2.Cells[iRowCnt, 1] = tuitionRevenueNetGrad.Tables[0].Rows[i][0];
sheet2.Cells[iRowCnt, 2] = tuitionRevenueNetGrad.Tables[0].Rows[i][1];
sheet2.Cells[iRowCnt, 3] = tuitionRevenueNetGrad.Tables[0].Rows[i][2];
sheet2.Cells[iRowCnt, 4] = tuitionRevenueNetGrad.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
Excel2.Worksheet sheet3;
sheet3 = (Excel2.Worksheet)workbook.Worksheets.Add();
sheet3.Name = "Assessments";
sheet3.Cells[1, 1] = "RCM Model " + fiscalYear;
Excel2.Range range17 = sheet3.Cells[1, 1] as Excel2.Range;
range17.EntireRow.Font.Bold = true;
range17.EntireRow.Font.Size = 20;
sheet3.Cells[2, 1] = "Assessments Report";
Excel2.Range range18 = sheet3.Cells[2, 1] as Excel2.Range;
range18.EntireRow.Font.Bold = true;
range18.EntireRow.Font.Size = 18;
sheet3.Range["A1:B1"].MergeCells = true;
sheet3.Range["A2:B2"].MergeCells = true;
iRowCnt = 5;
sheet3.Cells[iRowCnt - 1, 1] = "Assessments";
sheet3.Cells[iRowCnt - 1, 2] = "Undergarduate Rate";
sheet3.Cells[iRowCnt - 1, 3] = "Undergraduate Amount";
sheet3.Cells[iRowCnt - 1, 4] = "Graduate Rate";
sheet3.Cells[iRowCnt - 1, 5] = "Garduate Amount";
for (int i = 0; i <= assessments.Tables[0].Rows.Count - 1; i++)
{
sheet3.Cells[iRowCnt, 1] = assessments.Tables[0].Rows[i][0];
sheet3.Cells[iRowCnt, 2] = assessments.Tables[0].Rows[i][1];
sheet3.Cells[iRowCnt, 3] = assessments.Tables[0].Rows[i][2];
sheet3.Cells[iRowCnt, 4] = assessments.Tables[0].Rows[i][3];
sheet3.Cells[iRowCnt, 5] = assessments.Tables[0].Rows[i][4];
iRowCnt = iRowCnt + 1;
}
Excel2.Worksheet sheet4;
sheet4 = (Excel2.Worksheet)workbook.Worksheets.Add();
sheet4.Name = "Direct Expenses";
sheet4.Cells[1, 1] = "RCM Model " + fiscalYear;
Excel2.Range range20 = sheet4.Cells[1, 1] as Excel2.Range;
range20.EntireRow.Font.Bold = true;
range20.EntireRow.Font.Size = 20;
sheet4.Cells[2, 1] = "Direct Expenses Report";
Excel2.Range range21 = sheet4.Cells[2, 1] as Excel2.Range;
range21.EntireRow.Font.Bold = true;
range21.EntireRow.Font.Size = 18;
sheet4.Range["A1:B1"].MergeCells = true;
sheet4.Range["A2:B2"].MergeCells = true;
iRowCnt = 5;
sheet4.Cells[iRowCnt - 1, 1] = "Expense Type";
sheet4.Cells[iRowCnt - 1, 2] = "Amount";
for (int i = 0; i <= displayDirectExpenses.Rows.Count - 1; i++)
{
sheet4.Cells[iRowCnt, 1] = displayDirectExpenses.Rows[i][0];
sheet4.Cells[iRowCnt, 2] = displayDirectExpenses.Rows[i][1];
iRowCnt = iRowCnt + 1;
}
sheet4.Cells[iRowCnt + 1, 1] = "Instractional Compensation Expenses";
sheet4.Cells[iRowCnt + 2, 1] = "Expense";
sheet4.Cells[iRowCnt + 2, 2] = "Budget";
sheet4.Cells[iRowCnt + 2, 3] = "Fringe Benefit Rate";
sheet4.Cells[iRowCnt + 2, 4] = "Benefit";
iRowCnt = iRowCnt + 3;
int instracCompStartRow = iRowCnt;
for (int i = 0; i <= instarctionalCompensation.Tables[0].Rows.Count - 1; i++)
{
sheet4.Cells[iRowCnt, 1] = instarctionalCompensation.Tables[0].Rows[i][1];
sheet4.Cells[iRowCnt, 2] = instarctionalCompensation.Tables[0].Rows[i][3];
sheet4.Cells[iRowCnt, 3] = instarctionalCompensation.Tables[0].Rows[i][4];
sheet4.Cells[iRowCnt, 4] = instarctionalCompensation.Tables[0].Rows[i][5];
iRowCnt = iRowCnt + 1;
}
sheet4.Cells[iRowCnt + 1, 1] = "Non-Instractional Compensation Expenses";
sheet4.Cells[iRowCnt + 2, 1] = "Expense";
sheet4.Cells[iRowCnt + 2, 2] = "Budget";
sheet4.Cells[iRowCnt + 2, 3] = "Fringe Benefit Rate";
sheet4.Cells[iRowCnt + 2, 4] = "Benefit";
iRowCnt = iRowCnt + 3;
int noninstracCompStartRow = iRowCnt;
for (int i = 0; i <= noninstarctionalCompensation.Tables[0].Rows.Count - 1; i++)
{
sheet4.Cells[iRowCnt, 1] = noninstarctionalCompensation.Tables[0].Rows[i][1];
sheet4.Cells[iRowCnt, 2] = noninstarctionalCompensation.Tables[0].Rows[i][3];
sheet4.Cells[iRowCnt, 3] = noninstarctionalCompensation.Tables[0].Rows[i][4];
sheet4.Cells[iRowCnt, 4] = noninstarctionalCompensation.Tables[0].Rows[i][5];
iRowCnt = iRowCnt + 1;
}
sheet4.Cells[iRowCnt + 1, 1] = "Non-Compensation Expenses";
sheet4.Cells[iRowCnt + 2, 1] = "Expense";
sheet4.Cells[iRowCnt + 2, 2] = "Budget";
iRowCnt = iRowCnt + 3;
int nonCompStartRow = iRowCnt;
for (int i = 0; i <= noncompensationExpenses.Tables[0].Rows.Count - 1; i++)
{
sheet4.Cells[iRowCnt, 1] = noncompensationExpenses.Tables[0].Rows[i][1];
sheet4.Cells[iRowCnt, 2] = noncompensationExpenses.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
sheet4.Cells[iRowCnt + 1, 1] = "Miscellaneous";
sheet4.Cells[iRowCnt + 2, 1] = "Expense";
sheet4.Cells[iRowCnt + 2, 2] = "Account";
sheet4.Cells[iRowCnt + 2, 3] = "Budget";
iRowCnt = iRowCnt + 3;
int miscellaneousStartRow = iRowCnt;
for (int i = 0; i <= miscellaneous.Tables[0].Rows.Count - 1; i++)
{
sheet4.Cells[iRowCnt, 1] = miscellaneous.Tables[0].Rows[i][1];
sheet4.Cells[iRowCnt, 2] = miscellaneous.Tables[0].Rows[i][2];
sheet4.Cells[iRowCnt, 3] = miscellaneous.Tables[0].Rows[i][3];
iRowCnt = iRowCnt + 1;
}
Excel2.Worksheet sheet5;
sheet5 = (Excel2.Worksheet)workbook.Worksheets.Add();
sheet5.Name = "Allocated Costs";
sheet5.Cells[1, 1] = "RCM Model " + fiscalYear;
Excel2.Range range27 = sheet5.Cells[1, 1] as Excel2.Range;
range27.EntireRow.Font.Bold = true;
range27.EntireRow.Font.Size = 20;
sheet5.Cells[2, 1] = "Allocated Costs Report";
Excel2.Range range28 = sheet5.Cells[2, 1] as Excel2.Range;
range28.EntireRow.Font.Bold = true;
range28.EntireRow.Font.Size = 18;
sheet5.Range["A1:B1"].MergeCells = true;
sheet5.Range["A2:B2"].MergeCells = true;
iRowCnt = 5;
sheet5.Cells[iRowCnt - 1, 1] = "Allocated Cost";
sheet5.Cells[iRowCnt - 1, 2] = "Total";
for (int i = 0; i <= allocatedCostsSummary.Rows.Count - 1; i++)
{
sheet5.Cells[iRowCnt, 1] = allocatedCostsSummary.Rows[i][0];
sheet5.Cells[iRowCnt, 2] = allocatedCostsSummary.Rows[i]["Amount"];
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Excel2.Range range1 = sheet.Cells[4, 1] as Excel2.Range;
range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range6 = sheet2.Cells[5, 1] as Excel2.Range;
range6.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range8 = sheet2.Cells[10, 1] as Excel2.Range;
range8.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range9 = sheet2.Cells[16, 1] as Excel2.Range;
range9.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range10 = sheet2.Cells[22, 1] as Excel2.Range;
range10.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range11 = sheet2.Cells[27, 1] as Excel2.Range;
range11.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range12 = sheet2.Cells[34, 1] as Excel2.Range;
range12.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range13 = sheet2.Cells[41, 1] as Excel2.Range;
range13.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range14 = sheet2.Cells[48, 1] as Excel2.Range;
range14.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range15 = sheet2.Cells[55, 1] as Excel2.Range;
range15.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range16 = sheet2.Cells[62, 1] as Excel2.Range;
range16.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
range16.ColumnWidth = 35;
Excel2.Range range16a = sheet2.Cells[62, 2] as Excel2.Range;
range16a.ColumnWidth = 15;
Excel2.Range range19 = sheet3.Cells[5, 1] as Excel2.Range;
range19.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range22 = sheet4.Cells[5, 1] as Excel2.Range;
range22.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range23 = sheet4.Cells[instracCompStartRow, 1] as Excel2.Range;
range23.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range24 = sheet4.Cells[noninstracCompStartRow, 1] as Excel2.Range;
range24.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range25 = sheet4.Cells[nonCompStartRow, 1] as Excel2.Range;
range25.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
Excel2.Range range26 = sheet4.Cells[miscellaneousStartRow, 1] as Excel2.Range;
range26.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
range26.ColumnWidth = 35;
Excel2.Range range26a = sheet4.Cells[miscellaneousStartRow, 2] as Excel2.Range;
range26a.ColumnWidth = 15;
Excel2.Range range29 = sheet5.Cells[5, 1] as Excel2.Range;
range29.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
range29.ColumnWidth = 35;
// SAVE THE FILE IN A FOLDER.
sheet.SaveAs(path + fiscalYear + "RCMModel.xlsx");
// CLEAR.
workbook.Workbooks.Close();
workbook.Quit();
workbook = null;
sheet = null;
sheet2 = null;
sheet3 = null;
sheet4 = null;
sheet5 = null;
}
DownLoadFile();
int a = 0;
}
protected void DisplayTuitionRevenueBreakdown(String category)
{
StoredProcedures sp = new StoredProcedures();
DataTable tbl = null;
if (category == "Gross")
{
tbl = sp.DisplayGrossTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
TuitionBreakdownByLevelLabel.Text = "Gross Revenue Breakdown by Level " + FinancialYearDropDownList.SelectedValue;
Session["chkRevenueTbl"] = 1;
}
else if (category == "Exported")
{
tbl = sp.DisplayExportedTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
TuitionBreakdownByLevelLabel.Text = "Exported Revenue Breakdown by Level " + FinancialYearDropDownList.SelectedValue;
Session["chkRevenueTbl"] = 2;
}
else if (category == "Net")
{
tbl = sp.DisplayNetTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
TuitionBreakdownByLevelLabel.Text = "Net Revenue Breakdown by Level " + FinancialYearDropDownList.SelectedValue;
Session["chkRevenueTbl"] = 3;
}
TuitionBreakdownByLevelPanel.Visible = true;
TuitionBreakdownByLevelGridView.DataSource = tbl;
TuitionBreakdownByLevelGridView.DataBind();
}
protected void DisplayTuitionDifferentialRevenue()
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.DisplayTuitionDifferentialRevenue(FinancialYearDropDownList.SelectedValue);
TuitionDifferentialRevenueGridView.DataSource = ds;
TuitionDifferentialRevenueGridView.DataBind();
DirectExpensesLabel.Text = "Direct Expenditures " + FinancialYearDropDownList.SelectedValue;
}
private void DisplayAllocatedCostsSummaryReport()
{
StoredProcedures sp = new StoredProcedures();
DataTable tbl = sp.DisplayAllocatedCostsSummary(FinancialYearDropDownList.SelectedValue);
if (tbl.Rows.Count == 0)
{
AllocatedCostEmptyDSLabel.Visible = true;
}
else
{
AllocatedCostEmptyDSLabel.Visible = false;
}
AllocatedCostsPanel.Visible = true;
AllocatedCostsGridView.DataSource = tbl;
AllocatedCostsGridView.DataBind();
AllocatedCostsLabel.Text = "Allocated Costs " + FinancialYearDropDownList.SelectedValue;
}
/* Opens Add Admin Modal on Add Button click
* Opens Edit Admin Modal on Edit Button click
* Deletes Admin on Delete Button click
*/
protected void AdminGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
// Int32 userTuid = Int32.Parse((string)Session["AuthorizedUserTUID"]);
int index = Convert.ToInt32(e.CommandArgument);
if (e.CommandName == "AddAdmin")
{
AddAdminModalError.Visible = false;
AddAdminModalHeader.InnerText = "Add Administrator";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "addAdmin", "$('#addAdmin').modal();", true);
AddAdminFirstNameTextBox.Text = String.Empty;
AddAdminLastNameTextBox.Text = String.Empty;
AddAdminTuidTextBox.Text = String.Empty;
//AddCandidatePositionDropDownList.SelectedIndex = 0;
//AddCandidatePartyDropDownList.SelectedIndex = 0;
}
else if (e.CommandName == "RemoveAdmin")
{
StoredProcedures sp = new StoredProcedures();
int id = Int32.Parse(AdministratorsGridView.Rows[index].Cells[0].Text);
sp.DeleteAdmin(id);
DisplayAdmins();
//sp.CreateLog(userTuid, "Deleted admin. (" + id + " )");
}
else
{
//AddCandidateModalError.Visible = false;
//CandidateInfoError.Visible = false;
StoredProcedures sp = new StoredProcedures();
// AdminObject admin = new AdminObject();
int id = Int32.Parse(AdministratorsGridView.Rows[index].Cells[0].Text);
DataSet ds = sp.GetAdminById(id);
AddAdminTuidLabel.Visible = false;
AddAdminTuidTextBox.Visible = false;
EditAdminTuidLabel.Visible = true;
EditAdminTuidHeaderLabel.Visible = true;
EditAdminTuidLabel.Text = ds.Tables[0].Rows[0][0].ToString();
AddAdminFirstNameTextBox.Text = ds.Tables[0].Rows[0][1].ToString();
AddAdminLastNameTextBox.Text = ds.Tables[0].Rows[0][2].ToString();
AddAdminDeptDropDownList.SelectedValue = ds.Tables[0].Rows[0][4].ToString();
AddAdminAccessLevelDropDownList.SelectedValue = ds.Tables[0].Rows[0][3].ToString();
AddAdminModalHeader.InnerText = "Edit Administrator";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "addAdmin", "$('#addAdmin').modal();", true);
}
}
private void PopulateGrossExported()
{
StoredProcedures sp = new StoredProcedures();
TextBox tb = new TextBox();
String fiscalYear = (String)Session["FiscalYear"];
DataSet ds = sp.GetCHG(fiscalYear);
for (int row = 0; row < GrossCHGGridView.Rows.Count; row++)
{
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row + 4][4].ToString();
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[0].Rows[row + 4][5].ToString();
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("GradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row][4].ToString();
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("GradRateTextBox");
tb.Text = ds.Tables[0].Rows[row][5].ToString();
}
for (int row = 0; row < NetCHGGridView.Rows.Count; row++)
{
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[1].Rows[row + 4][4].ToString();
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[1].Rows[row + 4][5].ToString();
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("GradCHGTextBox");
tb.Text = ds.Tables[1].Rows[row][4].ToString();
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("GradRateTextBox");
tb.Text = ds.Tables[1].Rows[row][5].ToString();
}
for (int row = 0; row < ExportedCHGGridView.Rows.Count; row++)
{
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[2].Rows[row + 4][4].ToString();
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[2].Rows[row + 4][5].ToString();
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("GradCHGTextBox");
tb.Text = ds.Tables[2].Rows[row][4].ToString();
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("GradRateTextBox");
tb.Text = ds.Tables[2].Rows[row][5].ToString();
}
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[3].Rows[1][2].ToString();
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[3].Rows[1][3].ToString();
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("NumberGradStudentsTextBox");
tb.Text = ds.Tables[3].Rows[0][2].ToString();
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("GradRateTextBox");
tb.Text = ds.Tables[3].Rows[0][3].ToString();
}
protected void SaveButton_Click(object sender, EventArgs e)
{
int tagFlag = (int)Session["TagFlag"];
String fiscalYear = (String)Session["FiscalYear"];
StoredProcedures sp = new StoredProcedures();
TextBox tb = new TextBox();
ArrayList CHG = new ArrayList();
for (int row = 0; row < GrossCHGGridView.Rows.Count; row++)
{
String term = GrossCHGGridView.Rows[row].Cells[0].Text;
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
String undergradCHG = tb.Text;
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
String undergradRate = tb.Text;
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("GradCHGTextBox");
String gradCHG = tb.Text;
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("GradRateTextBox");
String gradRate = tb.Text;
sp.UpdateCHG(fiscalYear, term, "Undergraduate", "Gross", double.Parse(undergradCHG),double.Parse(undergradRate));
sp.UpdateCHG(fiscalYear, term, "Graduate", "Gross", double.Parse(gradCHG), double.Parse(gradRate));
}
for (int row = 0; row < NetCHGGridView.Rows.Count; row++)
{
String term = NetCHGGridView.Rows[row].Cells[0].Text;
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
String undergradCHG = tb.Text;
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
String undergradRate = tb.Text;
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("GradCHGTextBox");
String gradCHG = tb.Text;
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("GradRateTextBox");
String gradRate = tb.Text;
sp.UpdateCHG(fiscalYear, term, "Undergraduate", "Net", double.Parse(undergradCHG), double.Parse(undergradRate));
sp.UpdateCHG(fiscalYear, term, "Graduate", "Net", double.Parse(gradCHG), double.Parse(gradRate));
}
for (int row = 0; row < ExportedCHGGridView.Rows.Count; row++)
{
String term = ExportedCHGGridView.Rows[row].Cells[0].Text;
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
String undergradCHG = tb.Text;
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
String undergradRate = tb.Text;
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("GradCHGTextBox");
String gradCHG = tb.Text;
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("GradRateTextBox");
String gradRate = tb.Text;
sp.UpdateCHG(fiscalYear, term, "Undergraduate", "Exported", double.Parse(undergradCHG), double.Parse(undergradRate));
sp.UpdateCHG(fiscalYear, term, "Graduate", "Exported", double.Parse(gradCHG), double.Parse(gradRate));
}
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("UndergradCHGTextBox");
String differentialUndergradCHG = tb.Text;
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("UndergradRateTextBox");
String differentialUndergradRate = tb.Text;
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("NumberGradStudentsTextBox");
String gradStudentsCHG = tb.Text;
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("GradRateTextBox");
String gradStudentsRate = tb.Text;
sp.UpdateDifferentialRevenue(fiscalYear, "Undergraduate", double.Parse(differentialUndergradCHG), double.Parse(differentialUndergradRate));
sp.UpdateDifferentialRevenue(fiscalYear, "Graduate", double.Parse(gradStudentsCHG), double.Parse(gradStudentsRate));
sp.UpdateRevenueExpenseSummary(fiscalYear);
if (tagFlag == 1)
{
PopulateCHGTotals("Gross");
}
if (tagFlag == 2)
{
PopulateCHGTotals("Net");
}
if (tagFlag == 3)
{
PopulateCHGTotals("Exported");
}
if (tagFlag == 4)
{
PopulateDifferentialRevTotal();
}
}
private void DisplaySupportUnitMetrics(String supportUnit, String metricType)
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.DisplaySupportUnitMetricsByMetricType(FinancialYearDropDownList.SelectedValue, supportUnit, metricType);
SupportUnitMetricsPanel.Visible = true;
SupportUnitMetricsGridView.DataSource = ds;
SupportUnitMetricsGridView.DataBind();
}
private void DisplaySupportUnit(String supportUnit)
{
StoredProcedures sp = new StoredProcedures();
DataSet tbl = sp.DisplaySupportUnit2(FinancialYearDropDownList.SelectedValue, supportUnit);
SupportUnitPanel.Visible = true;
SupportUnitBreakDownGridView.DataSource = tbl;
SupportUnitBreakDownGridView.DataBind();
SupportUnitLabel.Text = supportUnit + " Breakdown " + FinancialYearDropDownList.SelectedValue;
}
private void DisplayRevenueExpenseSummaryReport()
{
StoredProcedures sp = new StoredProcedures();
DataTable tbl = sp.DisplayRevenueExpenseSummaryReport(FinancialYearDropDownList.SelectedValue);
SummaryReportPanel.Visible = true;
SummaryReportGridView.DataSource = tbl;
SummaryReportGridView.DataBind();
}
private void AddedCostDrivers()
{
String fiscalYear = (String)Session["FiscalYear"];
StoredProcedures rtrvDB = new StoredProcedures();
DataTable tbl = rtrvDB.DisplayAllocatedCostsSummary(fiscalYear);
if (tbl.Rows.Count != 0)
{
AddedCostDriversPanel.Visible = true;
AddedCostDriversGridView.DataSource = tbl;
AddedCostDriversGridView.DataBind();
}
for (int i = 0; i < SelectCostDriverDropDownList.Items.Count; i++)
{
foreach (DataRow row in tbl.Rows)
{
if (row[0].ToString() == SelectCostDriverDropDownList.Items[i].Value.ToString())
{
SelectCostDriverDropDownList.Items[i].Attributes.Add("style", "color: silver");
}
}
}
}
protected void DisplayDirectExpenses()
{
StoredProcedures sp = new StoredProcedures();
DataTable tbl = sp.DisplayDirectExpenses(FinancialYearDropDownList.SelectedValue);
DirectEspensesPanel.Visible = true;
DirectExpensesGridView.DataSource = tbl;
DirectExpensesGridView.DataBind();
DirectExpensesLabel.Text = "Direct Expenditures " + FinancialYearDropDownList.SelectedValue;
}
protected void TuitionRevenueGridView_RowUpdating(Object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
{
String grossExported = (String)Session["RevenueCategory"];
String category = (String)Session["category"];
String gradUndergrad = (String)Session["gradUndergrad"];
TextBox TBox;
String term;
String rate;
String CHG;
term = TuitionRevenueGridView.Rows[e.RowIndex].Cells[0].Text;
TBox = (TextBox)TuitionRevenueGridView.Rows[e.RowIndex].Cells[1].Controls[0];
CHG = TBox.Text;
TBox = (TextBox)TuitionRevenueGridView.Rows[e.RowIndex].Cells[2].Controls[0];
rate = TBox.Text;
StoredProcedures sp = new StoredProcedures();
sp.UpdateRevenue(FinancialYearDropDownList.SelectedValue, term, gradUndergrad, grossExported, rate, CHG);
sp.UpdateRevenueExpenseSummary(FinancialYearDropDownList.SelectedValue);
DisplayRevenueExpenseSummaryReport();
DisplayTuitionRevenueBreakdown(grossExported);
TuitionRevenueGridView.EditIndex = -1;
DisplayTuition(category);
FocusAnchor.Focus();
}
private void PopulateDifferentialRevTotal()
{
StoredProcedures sp = new StoredProcedures();
String fiscalYear = (String)Session["FiscalYear"];
DataSet ds = sp.GetDifferentialRevTotal(fiscalYear);
Session["RevTotalsDs"] = ds;
if (ds.Tables[0].Rows.Count > 0)
{
CHGTotalsTotalsPanel.Visible = true;
CHGTotalsGridView.DataSource = ds;
CHGTotalsGridView.DataBind();
}
}
protected void DisplayMiscellaneous()
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.GetMiscellaneousExpenses(FinancialYearDropDownList.SelectedValue);
MiscellaneousGridView.DataSource = ds;
MiscellaneousGridView.DataBind();
MiscellaneousLabel.Text = "Miscellaneous " + FinancialYearDropDownList.SelectedValue;
MiscellaneousPanel.Visible = true;
}
protected void AddAdminSubmit_Click(object sender, EventArgs e)
{
String logMessage = String.Empty;
Int32 userTuid = Int32.Parse((string)Session["AuthorizedUserTUID"]);//User TUID is used for existed Admin record editing
AddAdminModalError.Visible = false;
EditAdminTuidHeaderLabel.Visible = false;
EditAdminTuidLabel.Visible = false;
AddAdminTuidLabel.Visible = true;
AddAdminTuidTextBox.Visible = true;
String notValidTuidMessage = String.Empty;
Int32 tuid = 0;
Boolean result = false;
AdminObject admin = new AdminObject();
StoredProcedures sp = new StoredProcedures();
List<String> EmptyControls = new List<string>();
if (AddAdminModalHeader.InnerText == "Add Administrator" && AddAdminTuidTextBox.Text != String.Empty)
{
result = Int32.TryParse(AddAdminTuidTextBox.Text, out tuid);
if (result)
{
if (AddAdminTuidTextBox.Text.Length > 9 || AddAdminTuidTextBox.Text.Length < 9)
{
notValidTuidMessage = "Please enter a valid 9 digits TUID.</br>";
}
}
else
{
notValidTuidMessage = "Please enter a valid 9 digits TUID.</br>";
}
}
if (result == false)
{
AddAdminModalError.Visible = true;
AddAdminModalError.InnerHtml = notValidTuidMessage;
}
if (AddAdminTuidTextBox.Text == String.Empty)
{
EmptyControls.Add("TUID");
}
if (AddAdminFirstNameTextBox.Text == String.Empty)
{
EmptyControls.Add("First Name");
}
if (AddAdminLastNameTextBox.Text == String.Empty)
{
EmptyControls.Add("Last Name");
}
if (AddAdminDeptDropDownList.SelectedValue == "Select")
{
EmptyControls.Add("Department");
}
if (AddAdminAccessLevelDropDownList.SelectedValue == "Select")
{
EmptyControls.Add("Access Level");
}
if (AddAdminFirstNameTextBox.Text == string.Empty || AddAdminLastNameTextBox.Text == string.Empty || AddAdminDeptDropDownList.SelectedValue == "Select" || AddAdminAccessLevelDropDownList.SelectedValue == "Select")
{
String errorFields = String.Empty;
foreach (String field in EmptyControls)
{
if (errorFields == String.Empty)
{
errorFields = field;
}
else
{
errorFields += ", " + field;
}
}
AddAdminModalError.Visible = true;
AddAdminModalError.InnerHtml = notValidTuidMessage + "Please complete the following filelds: " + errorFields + ".";
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "addAdmin", "$('#addAdmin').modal();", true);
}
else
{
try
{
admin.tuid = Int32.Parse(AddAdminTuidTextBox.Text);
logMessage = "Added new administrator. ";
}
catch
{
admin.tuid = Int32.Parse(EditAdminTuidLabel.Text);
logMessage = "Edited administrator info. ";
}
admin.firstName = AddAdminFirstNameTextBox.Text;
admin.lastName = AddAdminLastNameTextBox.Text;
admin.department = AddAdminDeptDropDownList.SelectedValue;
admin.accessLevel = AddAdminAccessLevelDropDownList.SelectedValue;
sp.AddAdmin(admin);
AddAdminTuidTextBox.Text = String.Empty;
AddAdminFirstNameTextBox.Text = String.Empty;
AddAdminLastNameTextBox.Text = String.Empty;
AddAdminDeptDropDownList.SelectedValue = "Select";
AddAdminAccessLevelDropDownList.SelectedValue = "Select";
DisplayAdmins();
}
}
protected void DisplayNonInstructionalCompensation()
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.DisplayCompensation(FinancialYearDropDownList.SelectedValue, "NonInstractional");
if (ds.Tables[0].Rows.Count == 0)
{
NonInstractionalCompenstationEmptyDSLabel.Visible = true;
}
else
{
NonInstractionalCompenstationEmptyDSLabel.Visible = false;
}
NonInstractionalCompensationGridView.DataSource = ds;
NonInstractionalCompensationGridView.DataBind();
NonInstractionalCompensationLabel.Text = "Non-instractional Compensation Expenses " + FinancialYearDropDownList.SelectedValue;
NonInstractionalCompensationPanel.Visible = true;
}
private void DisplayAdmins()
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.GetAdmins();
ViewState["Admins"] = ds;
AdministratorsGridView.DataSource = ds;
AdministratorsGridView.DataBind();
//Needed for correct representation of GriedView headers using Bootstrap css
try
{
AdministratorsGridView.HeaderRow.TableSection = TableRowSection.TableHeader;
}
catch { }
}
protected void DisplayTuition(String category)
{
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, category);
TuitionRevenuePanel.Visible = true;
TuitionRevenueGridView.DataSource = ds;
TuitionRevenueGridView.DataBind();
if (category == "Gross/Undergraduate")
{
TuitionRevenueLabel.Text = "Gross Undergraduate Tuition Revenue " + FinancialYearDropDownList.SelectedValue;
}
if (category == "Gross/Graduate")
{
TuitionRevenueLabel.Text = "Gross Graduate Tuition Revenue " + FinancialYearDropDownList.SelectedValue;
}
if (category == "Exported/Undergraduate")
{
TuitionRevenueLabel.Text = "Exported Undergraduate Tuition Revenue " + FinancialYearDropDownList.SelectedValue;
}
if (category == "Exported/Graduate")
{
TuitionRevenueLabel.Text = "Exported Graduate Tuition Revenue " + FinancialYearDropDownList.SelectedValue;
}
if (category == "Net/Undergraduate")
{
TuitionRevenueLabel.Text = "Net Undergraduate Tuition Revenue " + FinancialYearDropDownList.SelectedValue;
}
if (category == "Net/Graduate")
{
TuitionRevenueLabel.Text = "Net Graduate Tuition Revenue " + FinancialYearDropDownList.SelectedValue;
}
}
