public void DisplayTotals(string fiscalYear)
{
RetrieveFromDB rtrv = new RetrieveFromDB();
DataSet ds = rtrv.RetriveExpenduturesTotals(fiscalYear);
if (ds.Tables[0].Rows.Count != 0)
{
ExpendutersTotalsPanel.Visible = true;
ExpendutersTotalsGridView.DataSource = ds;
ExpendutersTotalsGridView.DataBind();
}
}
public Boolean CheckFiscalYear(String category, String fiscalYear)
{
RetrieveFromDB rtrvDB = new RetrieveFromDB();
// DataSet fiscalYears = rtrvDB.RetriveFiscalYears(category);
Boolean val = false;
//foreach (DataRow row in fiscalYears.Tables[0].Rows)
//{
// if (row[0].ToString() == fiscalYear)
// {
// val = true;
// }
//}
return val;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["supportUnit"] = null;
RetrieveFromDB rtrvDB = new RetrieveFromDB();
DataSet fiscalYears = rtrvDB.GetFiscalYears();
int lastRecord = fiscalYears.Tables[0].Rows.Count - 1;
String mostRecentFY = fiscalYears.Tables[0].Rows[lastRecord][0].ToString();
Session["MostRecentFY"] = mostRecentFY;
// CategoryDropDownList.Items.Insert(0, new ListItem("Historical Percent Change", "0"));
//CategoryDropDownList.Items[0].Attributes.Add("style", "color: silver");
}
}
protected void ExpendutersTotalsGridView_OnRowDataBound(Object sender, GridViewRowEventArgs e)
{
String fiscalYear = (String)Session["FiscalYear"];
RetrieveFromDB rtrv = new RetrieveFromDB();
DataSet ds = rtrv.RetriveExpenduturesTotals(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 Page_Load(object sender, EventArgs e)
{
RetrieveFromDB rtrvDB = new RetrieveFromDB();
if (!IsPostBack)
{
String fiscalYear = (String)Session["FiscalYear"];
DataTable costDriverstbl = DTable();
Session["CostDriversTbl"] = costDriverstbl;
DataTable costDriversAdded = CostDriversRightPanel();
Session["CostDriversAdded"] = costDriversAdded;
DataSet ds = rtrvDB.GetCostDriversTypes();
SelectCostDriverDropDownList.DataSource = ds.Tables[0];
SelectCostDriverDropDownList.DataTextField = "costDriver";
SelectCostDriverDropDownList.DataValueField = "costDriver";
SelectCostDriverDropDownList.DataBind();
SelectCostDriverLabel.Text = "Select Cost Driver " + fiscalYear;
AddedCostDrivers();
}
}
private void PopulateGridView()
{
String fiscalYear = (String)Session["FiscalYear"];
RetrieveFromDB rtrvDB = new RetrieveFromDB();
DataTable dt = rtrvDB.DisplayAssessments(fiscalYear);
TextBox tb = new TextBox();
tb = (TextBox)AssessmentRatesGridView.Rows[0].FindControl("UndergradRateTextBox");
if (dt.Rows[0][1].ToString() != "")
{
tb.Text = (double.Parse(dt.Rows[0][1].ToString()) * 100).ToString();
}
tb = (TextBox)AssessmentRatesGridView.Rows[0].FindControl("GradRateTextBox");
if (dt.Rows[0][3].ToString() != "")
{
tb.Text = (double.Parse(dt.Rows[0][3].ToString()) * 100).ToString();
}
tb = (TextBox)AssessmentRatesGridView.Rows[1].FindControl("UndergradRateTextBox");
if (dt.Rows[1][1].ToString() != "")
{
tb.Text = (double.Parse(dt.Rows[1][1].ToString()) * 100).ToString();
}
tb = (TextBox)AssessmentRatesGridView.Rows[1].FindControl("GradRateTextBox");
if (dt.Rows[1][3].ToString() != "")
{
tb.Text = (double.Parse(dt.Rows[1][3].ToString()) * 100).ToString();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable years = new DataTable();
years.Columns.Add("Text", typeof(String));
years.Columns.Add("Value", typeof(String));
DataRow row = years.NewRow();
row["Text"] = "Select";
row["Value"] = "Select";
years.Rows.Add(row);
for (int i = 0; i <= 10; i++)
{
int year = 2010;
DataRow row2 = years.NewRow();
row2["Text"] = (year + i).ToString();
row2["Value"] = (year + i).ToString();
years.Rows.Add(row2);
}
YearDropDownList.DataSource = years;
YearDropDownList.DataTextField = "Text";
YearDropDownList.DataValueField = "Value";
YearDropDownList.DataBind();
RetrieveFromDB rtrvDB = new RetrieveFromDB();
DataSet fiscalYears = rtrvDB.GetFiscalYears(); //RetriveFiscalYears("All");
// DataSet fiscalYearsByCategory = rtrvDB.RetriveFiscalYearsAllCategories();
for (int i = 0; i < YearDropDownList.Items.Count; i++)
{
foreach (DataRow row2 in fiscalYears.Tables[0].Rows)
{
String fiscalYear = row2[0].ToString();
if (fiscalYear.GetLast(4) == YearDropDownList.Items[i].Value.ToString())
{
// YearDropDownList.Items[i].Text = fiscalYear.GetLast(4) + " (draft)";
YearDropDownList.Items[i].Attributes.Add("style", "color: silver");
}
}
//foreach (DataRow row3 in fiscalYearsByCategory.Tables[0].Rows)
//{
// String fiscalYear = row3[0].ToString();
// if (fiscalYear.GetLast(4) == YearDropDownList.Items[i].Value.ToString())
// {
// // YearDropDownList.Items[i].Text = fiscalYear.GetLast(4) + " (completed)";
// YearDropDownList.Items[i].Attributes.Add("style", "color: silver");
// }
//}
}
}
}
protected void SelectButton_Click(object sender, EventArgs e)
{
Alert.Visible = false;
Error.Visible = false;
CostToAllocateTextBox.Text = "";
SubmitCostDriverPanel.Visible = true;
SubmitCostDriverLabel.Text = SelectCostDriverDropDownList.SelectedValue;
RetrieveFromDB rtrvDB = new RetrieveFromDB();
TextBox tbox = new TextBox();
String fiscalYear = (String)Session["FiscalYear"];
DataSet ds1 = rtrvDB.DisplaySupportUnitMetricsEdit(fiscalYear, SelectCostDriverDropDownList.SelectedValue);
DataSet ds2 = rtrvDB.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 = rtrvDB.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;
}
private void AddedCostDrivers()
{
String fiscalYear = (String)Session["FiscalYear"];
RetrieveFromDB rtrvDB = new RetrieveFromDB();
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");
}
}
}
}
private void PopulateGridView()
{
String fiscalYear = (String)Session["FiscalYear"];
RetrieveFromDB rtrvDB = new RetrieveFromDB();
DataSet ds = rtrvDB.DisplayCompensation(fiscalYear, "Instractional");
DataSet ds2 = rtrvDB.DisplayCompensation(fiscalYear, "NonInstractional");
DataSet ds3 = rtrvDB.DisplayNonCompensationExpenses(fiscalYear);
InstractionalCompensationGridView.DataSource = ds;
InstractionalCompensationGridView.DataBind();
NonInstractionalCompensationGridView.DataSource = ds2;
NonInstractionalCompensationGridView.DataBind();
NonCompensationGridView.DataSource = ds3;
NonCompensationGridView.DataBind();
TextBox tb = new TextBox();
for (int row = 0; row < InstractionalCompensationGridView.Rows.Count; row++)
{
tb = (TextBox)InstractionalCompensationGridView.Rows[row].FindControl("BudgetTextBox");
tb.Text = ds.Tables[0].Rows[row][3].ToString();
if (ds.Tables[0].Rows[row][4].ToString() != "")
{
double rate = double.Parse(ds.Tables[0].Rows[row][4].ToString()) * 100;
tb = (TextBox)InstractionalCompensationGridView.Rows[row].FindControl("FringeBenefitRateTextBox");
tb.Text = ds.Tables[0].Rows[row][4].ToString();
rate.ToString();
}
}
for (int row = 0; row < NonInstractionalCompensationGridView.Rows.Count; row++)
{
tb = (TextBox)NonInstractionalCompensationGridView.Rows[row].FindControl("BudgetTextBox");
tb.Text = ds2.Tables[0].Rows[row][3].ToString();
if (ds2.Tables[0].Rows[row][4].ToString() != "")
{
double rate = double.Parse(ds2.Tables[0].Rows[row][4].ToString())*100;
tb = (TextBox)NonInstractionalCompensationGridView.Rows[row].FindControl("FringeBenefitRateTextBox");
tb.Text = rate.ToString();
}
}
for (int row = 0; row < NonCompensationGridView.Rows.Count; row++)
{
tb = (TextBox)NonCompensationGridView.Rows[row].FindControl("BudgetTextBox");
tb.Text = ds3.Tables[0].Rows[row][3].ToString();
}
}
protected void SaveButton_Click(object sender, EventArgs e)
{
Alert.Visible = false;
Error.Visible = false;
String fiscalYear = (String)Session["FiscalYear"];
Utils utils = new Utils();
UpdateDatabase updtDB = new UpdateDatabase();
RetrieveFromDB rtrv = new RetrieveFromDB();
Boolean val = utils.CheckFiscalYear("Expendutures", fiscalYear);
TextBox tb = new TextBox();
ArrayList instractCompExpendures = new ArrayList();
ArrayList nonInstractCompExpendures = new ArrayList();
ArrayList nonCompExpendures = new ArrayList();
for (int row = 0; row < InstractionalCompensationGridView.Rows.Count; row++)
{
ExpendutersObject instractComp = new ExpendutersObject();
String expenseType = InstractionalCompensationGridView.Rows[row].Cells[0].Text;
String accountNumber = InstractionalCompensationGridView.Rows[row].Cells[1].Text;
tb = (TextBox)InstractionalCompensationGridView.Rows[row].FindControl("BudgetTextBox");
String budget = tb.Text;
tb = (TextBox)InstractionalCompensationGridView.Rows[row].FindControl("FringeBenefitRateTextBox");
String fringeBenefitRate = String.Empty;
if (tb.Text != "")
{
fringeBenefitRate = (double.Parse(tb.Text) / 100).ToString();
}
else
{
fringeBenefitRate = tb.Text;
}
if (val != true)
{
updtDB.InsertCompensationExpenduters("Instractional", fiscalYear, expenseType, accountNumber, budget, fringeBenefitRate);
}
else
{
updtDB.UpdateCompensationExpenduters("Instractional", fiscalYear, expenseType, accountNumber, budget, fringeBenefitRate);
}
}
for (int row = 0; row < NonInstractionalCompensationGridView.Rows.Count; row++)
{
String expenseType = NonInstractionalCompensationGridView.Rows[row].Cells[0].Text;
String accountNumber = NonInstractionalCompensationGridView.Rows[row].Cells[1].Text;
tb = (TextBox)NonInstractionalCompensationGridView.Rows[row].FindControl("BudgetTextBox");
String budget = tb.Text;
tb = (TextBox)NonInstractionalCompensationGridView.Rows[row].FindControl("FringeBenefitRateTextBox");
String fringeBenefitRate = "";
if (tb.Text != "")
{
fringeBenefitRate = (double.Parse(tb.Text) / 100).ToString();
}
else
{
fringeBenefitRate = tb.Text;
}
if (val != true)
{
updtDB.InsertCompensationExpenduters("NonInstractional", fiscalYear, expenseType, accountNumber, budget, fringeBenefitRate);
}
else
{
updtDB.UpdateCompensationExpenduters("NonInstractional", fiscalYear, expenseType, accountNumber, budget, fringeBenefitRate);
}
}
for (int row = 0; row < NonCompensationGridView.Rows.Count; row++)
{
String expenseType = NonCompensationGridView.Rows[row].Cells[0].Text;
String accountNumber = NonCompensationGridView.Rows[row].Cells[1].Text;
tb = (TextBox)NonCompensationGridView.Rows[row].FindControl("BudgetTextBox");
String budget = tb.Text;
if (val != true)
{
updtDB.InsertNonCompensationExpenduters(fiscalYear, expenseType, accountNumber, budget);
}
else
{
updtDB.UpdateNonCompensationExpenduters(fiscalYear, expenseType, accountNumber, budget);
}
}
DisplayTotals(fiscalYear);
}
public String CreateNewFiscalYear(String fiscalYear)
{
String message = String.Empty;
RetrieveFromDB rtrv = new RetrieveFromDB();
UpdateDatabase updDB = new UpdateDatabase();
DataSet ds = rtrv.CheckCategoriesBeforeNewFiscalYear(fiscalYear);
if (int.Parse(ds.Tables[0].Rows[4][1].ToString()) != 0)
{
message = "Fiscal year " + fiscalYear + " already exists.";
}
else if (int.Parse(ds.Tables[0].Rows[3][1].ToString()) != 0 & int.Parse(ds.Tables[0].Rows[2][1].ToString()) != 0 && int.Parse(ds.Tables[0].Rows[1][1].ToString()) != 0 && int.Parse(ds.Tables[0].Rows[0][1].ToString())!= 0 )
{
message = "1";
updDB.UpdateNulls(fiscalYear);
updDB.RevenueSummaryInsert(fiscalYear);
}
else
{
if (int.Parse(ds.Tables[0].Rows[3][1].ToString()) == 0)
{
message = "CHG";
}
if (int.Parse(ds.Tables[0].Rows[1][1].ToString()) == 0)
{
if (message == String.Empty)
{
message = "Assessments";
}
else
{
message = message + ", Assessments";
}
}
if (int.Parse(ds.Tables[0].Rows[2][1].ToString()) == 0)
{
if (message == String.Empty)
{
message = "Expendutries";
}
else
{
message = message + ", Expendutries";
}
}
if (int.Parse(ds.Tables[0].Rows[0][1].ToString()) == 0)
{
if (message == String.Empty)
{
message = "Allocated Costs";
}
else
{
message = message + ", Allocated Costs";
}
}
message = "Fiscal year " + fiscalYear + " has not been created. In order to create the fiscal year please submit " + message + ".";
}
return message;
}
private void PopulateGrossExported()
{
String fiscalYear = (String)Session["FiscalYear"];
RetrieveFromDB rtrvDB = new RetrieveFromDB();
DataSet ds = rtrvDB.DisplayCHG(fiscalYear);
DataSet ds2 = rtrvDB.DisplayTuitionDifferentialRevenueEdit(fiscalYear);
TextBox tb = new TextBox();
for (int row = 0; row < GrossCHGGridView.Rows.Count; row++)
{
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row + 8][3].ToString();
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[0].Rows[row + 8][4].ToString();
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("GradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row + 12][3].ToString();
tb = (TextBox)GrossCHGGridView.Rows[row].FindControl("GradRateTextBox");
tb.Text = ds.Tables[0].Rows[row + 12][4].ToString();
}
for (int row = 0; row < NetCHGGridView.Rows.Count; row++)
{
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row][3].ToString();
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[0].Rows[row][4].ToString();
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("GradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row + 4][3].ToString();
tb = (TextBox)NetCHGGridView.Rows[row].FindControl("GradRateTextBox");
tb.Text = ds.Tables[0].Rows[row + 4][4].ToString();
}
for (int row = 0; row < ExportedCHGGridView.Rows.Count; row++)
{
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("UndergradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row + 16][3].ToString();
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("UndergradRateTextBox");
tb.Text = ds.Tables[0].Rows[row + 16][4].ToString();
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("GradCHGTextBox");
tb.Text = ds.Tables[0].Rows[row + 20][3].ToString();
tb = (TextBox)ExportedCHGGridView.Rows[row].FindControl("GradRateTextBox");
tb.Text = ds.Tables[0].Rows[row + 20][4].ToString();
}
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("UndergradCHGTextBox");
tb.Text = ds2.Tables[0].Rows[0][1].ToString();
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("UndergradRateTextBox");
tb.Text = ds2.Tables[0].Rows[0][2].ToString();
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("NumberGradStudentsTextBox");
tb.Text = ds2.Tables[0].Rows[1][1].ToString();
tb = (TextBox)DifferentialRevenueGridView.Rows[0].FindControl("GradRateTextBox");
tb.Text = ds2.Tables[0].Rows[1][2].ToString();
}
private void PopulateDifferentialRevTotal()
{
RetrieveFromDB rtrvdb = new RetrieveFromDB();
String fiscalYear = (String)Session["FiscalYear"];
DataSet ds = rtrvdb.GetDifferentialRevTotal(fiscalYear);
Session["RevTotalsDs"] = ds;
if (ds.Tables[0].Rows.Count > 0)
{
CHGTotalsTotalsPanel.Visible = true;
CHGTotalsGridView.DataSource = ds;
CHGTotalsGridView.DataBind();
}
}
private void DisplayRevenueExpenseSummaryReport()
{
Double percentage = 0;
String fiscalYear = "FY2014";//(String)Session["MostRecentFY"];
RetrieveFromDB rtrvDB = new RetrieveFromDB();
DataTable tbl = rtrvDB.DisplayRevenueExpenseSummaryReport(fiscalYear);
if (Percentage.Text != String.Empty)
{
percentage = Double.Parse(Percentage.Text) / 100;
}
else
{
percentage = 0;
}
tbl.Columns.Add("Year1", typeof(System.Double));
tbl.Columns.Add("Year2", typeof(System.Double));
tbl.Columns.Add("Year3", typeof(System.Double));
tbl.Columns.Add("Year4", typeof(System.Double));
foreach (DataRow dr in tbl.Rows)
{
dr["Year1"] = (Double.Parse(dr[1].ToString()) * percentage) + Double.Parse(dr[1].ToString());
dr["Year2"] = (Double.Parse(dr[3].ToString()) * percentage) + Double.Parse(dr[3].ToString());
dr["Year3"] = (Double.Parse(dr[4].ToString()) * percentage) + Double.Parse(dr[4].ToString());
dr["Year4"] = (Double.Parse(dr[5].ToString()) * percentage) + Double.Parse(dr[5].ToString());
}
string year = fiscalYear.GetLast(4);
String fiscalYear1 = "FY" + (int.Parse(year)+ 1);
String fiscalYear2 = "FY" + (int.Parse(year) + 2);
String fiscalYear3 = "FY" + (int.Parse(year) + 3);
String fiscalYear4 = "FY" + (int.Parse(year) + 4);
SummaryReportGridView.Columns[1].HeaderText = fiscalYear;
SummaryReportGridView.Columns[2].HeaderText = fiscalYear1;
SummaryReportGridView.Columns[3].HeaderText = fiscalYear2;
SummaryReportGridView.Columns[4].HeaderText = fiscalYear3;
SummaryReportGridView.Columns[5].HeaderText = fiscalYear4;
SummaryReportPanel.Visible = true;
SummaryReportGridView.DataSource = tbl;
SummaryReportGridView.DataBind();
}