private void DisplayRevenueExpenseSummaryReport()
{
ProjectionPercentages percentages = new ProjectionPercentages();
percentages = (ProjectionPercentages)ViewState["percentages"];
if (percentages == null)
{
percentages = CalculatePercentage();
}
String fiscalYear = (String)Session["MostRecentFY"];
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);
StoredProcedures sp = new StoredProcedures();
DataTable tbl = sp.DisplayProjections(fiscalYear);
ProjectionsLabel.Text = "Projections " + fiscalYear;
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));
tbl.Columns.Add("Percentage", typeof(System.Double));
tbl.Rows[0]["Year1"] = (Double.Parse(tbl.Rows[0][1].ToString()) * percentages.grossCHG) + Double.Parse(tbl.Rows[0][1].ToString());
tbl.Rows[0]["Year2"] = (Double.Parse(tbl.Rows[0][3].ToString()) * percentages.grossCHG) + Double.Parse(tbl.Rows[0][3].ToString());
tbl.Rows[0]["Year3"] = (Double.Parse(tbl.Rows[0][4].ToString()) * percentages.grossCHG) + Double.Parse(tbl.Rows[0][4].ToString());
tbl.Rows[0]["Year4"] = (Double.Parse(tbl.Rows[0][5].ToString()) * percentages.grossCHG) + Double.Parse(tbl.Rows[0][5].ToString());
tbl.Rows[0]["Percentage"] = percentages.grossCHG;
tbl.Rows[1]["Year1"] = (Double.Parse(tbl.Rows[1][1].ToString()) * percentages.exportedCHG) + Double.Parse(tbl.Rows[1][1].ToString());
tbl.Rows[1]["Year2"] = (Double.Parse(tbl.Rows[1][3].ToString()) * percentages.exportedCHG) + Double.Parse(tbl.Rows[1][3].ToString());
tbl.Rows[1]["Year3"] = (Double.Parse(tbl.Rows[1][4].ToString()) * percentages.exportedCHG) + Double.Parse(tbl.Rows[1][4].ToString());
tbl.Rows[1]["Year4"] = (Double.Parse(tbl.Rows[1][5].ToString()) * percentages.exportedCHG) + Double.Parse(tbl.Rows[1][5].ToString());
tbl.Rows[1]["Percentage"] = percentages.exportedCHG;
tbl.Rows[2]["Year1"] = (Double.Parse(tbl.Rows[2][1].ToString()) * percentages.netCHG) + Double.Parse(tbl.Rows[2][1].ToString());
tbl.Rows[2]["Year2"] = (Double.Parse(tbl.Rows[2][3].ToString()) * percentages.netCHG) + Double.Parse(tbl.Rows[2][3].ToString());
tbl.Rows[2]["Year3"] = (Double.Parse(tbl.Rows[2][4].ToString()) * percentages.netCHG) + Double.Parse(tbl.Rows[2][4].ToString());
tbl.Rows[2]["Year4"] = (Double.Parse(tbl.Rows[2][5].ToString()) * percentages.netCHG) + Double.Parse(tbl.Rows[2][5].ToString());
tbl.Rows[2]["Percentage"] = percentages.netCHG;
tbl.Rows[3]["Year1"] = (Double.Parse(tbl.Rows[3][1].ToString()) * percentages.grossRevenue) + Double.Parse(tbl.Rows[3][1].ToString());
tbl.Rows[3]["Year2"] = (Double.Parse(tbl.Rows[3][3].ToString()) * percentages.grossRevenue) + Double.Parse(tbl.Rows[3][3].ToString());
tbl.Rows[3]["Year3"] = (Double.Parse(tbl.Rows[3][4].ToString()) * percentages.grossRevenue) + Double.Parse(tbl.Rows[3][4].ToString());
tbl.Rows[3]["Year4"] = (Double.Parse(tbl.Rows[3][5].ToString()) * percentages.grossRevenue) + Double.Parse(tbl.Rows[3][5].ToString());
tbl.Rows[3]["Percentage"] = percentages.grossRevenue;
tbl.Rows[4]["Year1"] = (Double.Parse(tbl.Rows[4][1].ToString()) * percentages.exportedRevenue) + Double.Parse(tbl.Rows[4][1].ToString());
tbl.Rows[4]["Year2"] = (Double.Parse(tbl.Rows[4][3].ToString()) * percentages.exportedRevenue) + Double.Parse(tbl.Rows[4][3].ToString());
tbl.Rows[4]["Year3"] = (Double.Parse(tbl.Rows[4][4].ToString()) * percentages.exportedRevenue) + Double.Parse(tbl.Rows[4][4].ToString());
tbl.Rows[4]["Year4"] = (Double.Parse(tbl.Rows[4][5].ToString()) * percentages.exportedRevenue) + Double.Parse(tbl.Rows[4][5].ToString());
tbl.Rows[4]["Percentage"] = percentages.exportedRevenue;
tbl.Rows[5]["Year1"] = (Double.Parse(tbl.Rows[5][1].ToString()) * percentages.netRevenue) + Double.Parse(tbl.Rows[5][1].ToString());
tbl.Rows[5]["Year2"] = (Double.Parse(tbl.Rows[5][3].ToString()) * percentages.netRevenue) + Double.Parse(tbl.Rows[5][3].ToString());
tbl.Rows[5]["Year3"] = (Double.Parse(tbl.Rows[5][4].ToString()) * percentages.netRevenue) + Double.Parse(tbl.Rows[5][4].ToString());
tbl.Rows[5]["Year4"] = (Double.Parse(tbl.Rows[5][5].ToString()) * percentages.netRevenue) + Double.Parse(tbl.Rows[5][5].ToString());
tbl.Rows[5]["Percentage"] = percentages.netRevenue;
int columnIndex = 0;
DataRow row = tbl.Rows[6];
foreach (DataColumn column in tbl.Columns)
{
if (int.Parse(year) == 2015 && columnIndex == 3)
{
row[column] = 3712000;
}
else if (int.Parse(year) == 2015 && columnIndex == 4)
{
row[column] = 4800000;
}
else if (int.Parse(year) > 2015 && columnIndex >= 3)
{
row[column] = 4800000;
}
columnIndex++;
//tbl.Rows[6]["Year2"] = (Double.Parse(tbl.Rows[6][3].ToString()) * percentages.differentialRevenue) + Double.Parse(tbl.Rows[6][3].ToString());
//tbl.Rows[6]["Year3"] = (Double.Parse(tbl.Rows[6][4].ToString()) * percentages.differentialRevenue) + Double.Parse(tbl.Rows[6][4].ToString());
//tbl.Rows[6]["Year4"] = (Double.Parse(tbl.Rows[6][5].ToString()) * percentages.differentialRevenue) + Double.Parse(tbl.Rows[6][5].ToString());
//tbl.Rows[6]["Percentage"] = percentages.differentialRevenue;
}
tbl.Rows[7]["Year1"] = (Double.Parse(tbl.Rows[7][1].ToString()) * percentages.assessments) + Double.Parse(tbl.Rows[7][1].ToString());
tbl.Rows[7]["Year2"] = (Double.Parse(tbl.Rows[7][3].ToString()) * percentages.assessments) + Double.Parse(tbl.Rows[7][3].ToString());
tbl.Rows[7]["Year3"] = (Double.Parse(tbl.Rows[7][4].ToString()) * percentages.assessments) + Double.Parse(tbl.Rows[7][4].ToString());
tbl.Rows[7]["Year4"] = (Double.Parse(tbl.Rows[7][5].ToString()) * percentages.assessments) + Double.Parse(tbl.Rows[7][5].ToString());
tbl.Rows[7]["Percentage"] = percentages.assessments;
tbl.Rows[8]["Year1"] = (Double.Parse(tbl.Rows[8][1].ToString()) * percentages.costDrivers) + Double.Parse(tbl.Rows[8][1].ToString());
tbl.Rows[8]["Year2"] = (Double.Parse(tbl.Rows[8][3].ToString()) * percentages.costDrivers) + Double.Parse(tbl.Rows[8][3].ToString());
tbl.Rows[8]["Year3"] = (Double.Parse(tbl.Rows[8][4].ToString()) * percentages.costDrivers) + Double.Parse(tbl.Rows[8][4].ToString());
tbl.Rows[8]["Year4"] = (Double.Parse(tbl.Rows[8][5].ToString()) * percentages.costDrivers) + Double.Parse(tbl.Rows[8][5].ToString());
tbl.Rows[8]["Percentage"] = percentages.costDrivers;
tbl.Rows[9]["Year1"] = (Double.Parse(tbl.Rows[9][1].ToString()) * percentages.expendetures) + Double.Parse(tbl.Rows[9][1].ToString());
tbl.Rows[9]["Year2"] = (Double.Parse(tbl.Rows[9][3].ToString()) * percentages.expendetures) + Double.Parse(tbl.Rows[9][3].ToString());
tbl.Rows[9]["Year3"] = (Double.Parse(tbl.Rows[9][4].ToString()) * percentages.expendetures) + Double.Parse(tbl.Rows[9][4].ToString());
tbl.Rows[9]["Year4"] = (Double.Parse(tbl.Rows[9][5].ToString()) * percentages.expendetures) + Double.Parse(tbl.Rows[9][5].ToString());
tbl.Rows[9]["Percentage"] = percentages.expendetures;
tbl.Rows[10]["Year1"] = (Double.Parse(tbl.Rows[10][1].ToString()) * percentages.surplus) + Double.Parse(tbl.Rows[10][1].ToString());
tbl.Rows[10]["Year2"] = (Double.Parse(tbl.Rows[10][3].ToString()) * percentages.surplus) + Double.Parse(tbl.Rows[10][3].ToString());
tbl.Rows[10]["Year3"] = (Double.Parse(tbl.Rows[10][4].ToString()) * percentages.surplus) + Double.Parse(tbl.Rows[10][4].ToString());
tbl.Rows[10]["Year4"] = (Double.Parse(tbl.Rows[10][5].ToString()) * percentages.surplus) + Double.Parse(tbl.Rows[10][5].ToString());
tbl.Rows[10]["Percentage"] = percentages.surplus;
ProjectionsGridView.Columns[2].HeaderText = fiscalYear;
ProjectionsGridView.Columns[3].HeaderText = fiscalYear1;
ProjectionsGridView.Columns[4].HeaderText = fiscalYear2;
ProjectionsGridView.Columns[5].HeaderText = fiscalYear3;
ProjectionsGridView.Columns[6].HeaderText = fiscalYear4;
ProjectionsPanel.Visible = true;
ProjectionsGridView.DataSource = tbl;
ProjectionsGridView.DataBind();
}
protected void ProjectionsGridView_RowUpdating(Object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
{
ProjectionPercentages percentages = new ProjectionPercentages();
TextBox TBox;
int index = e.RowIndex;
TBox = (TextBox)ProjectionsGridView.Rows[e.RowIndex].Cells[1].Controls[0];
Double percent = Double.Parse(TBox.Text)/100;
if (index == 0)
{
percentages.grossCHG = percent;
}
else if (index == 1)
{
percentages.exportedCHG = percent;
}
else if (index == 2)
{
percentages.netCHG = percent;
}
else if (index == 3)
{
percentages.grossRevenue = percent;
}
else if (index == 4)
{
percentages.exportedRevenue = percent;
}
else if (index == 5)
{
percentages.netRevenue = percent;
}
//else if (index == 6)
//{
// percentages.differentialRevenue = percent;
//}
else if (index == 7)
{
percentages.assessments = percent;
}
else if (index == 8)
{
percentages.costDrivers = percent;
}
else if (index == 9)
{
percentages.expendetures = percent;
}
else if (index == 10)
{
percentages.surplus = percent;
}
int rowIndex = 0;
foreach (GridViewRow row in ProjectionsGridView.Rows)
{
if (rowIndex == 0 && rowIndex != e.RowIndex)
{
percentages.grossCHG = Double.Parse(row.Cells[1].Text.Replace("%",""))/100;
}
else if (rowIndex == 1 && rowIndex != e.RowIndex)
{
percentages.exportedCHG = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 2 && rowIndex != e.RowIndex)
{
percentages.netCHG = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 3 && rowIndex != e.RowIndex)
{
percentages.grossRevenue = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 4 && rowIndex != e.RowIndex)
{
percentages.exportedRevenue = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 5 && rowIndex != e.RowIndex)
{
percentages.netRevenue = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
//else if (rowIndex == 6 && rowIndex != e.RowIndex)
//{
// percentages.differentialRevenue = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
//}
else if (rowIndex == 7 && rowIndex != e.RowIndex)
{
percentages.assessments = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 8 && rowIndex != e.RowIndex)
{
percentages.costDrivers = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 9 && rowIndex != e.RowIndex)
{
percentages.expendetures = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
else if (rowIndex == 10 && rowIndex != e.RowIndex)
{
percentages.surplus = Double.Parse(row.Cells[1].Text.Replace("%", "")) / 100;
}
rowIndex++;
}
ViewState["percentages"] = percentages;
ProjectionsGridView.EditIndex = -1;
DisplayRevenueExpenseSummaryReport();
}
private ProjectionPercentages CalculatePercentage()
{
Double percentage = 0;
Double amount = 0;
ProjectionPercentages percentages = new ProjectionPercentages();
List<Double> list = new List<Double>();
StoredProcedures sp = new StoredProcedures();
DataSet ds = sp.GetNetRevenueAndNetCHGByYear();
int index = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.netRevenue = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[1].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.grossRevenue = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[2].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.exportedRevenue = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[3].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.assessments = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[4].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.grossCHG = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[5].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.exportedCHG = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[6].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.netCHG = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[7].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.expendetures = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[8].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.costDrivers = percentage / list.Count;
//list.RemoveRange(0, list.Count);
//percentage = 0;
//amount = 0;
//index = 0;
//foreach (DataRow row in ds.Tables[9].Rows)
//{
// if (index == 0)
// {
// amount = Double.Parse(row[1].ToString());
// }
// else
// {
// list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
// amount = Double.Parse(row[1].ToString());
// }
// index++;
//}
//foreach (Double iteam in list)
//{
// percentage = percentage + iteam;
//}
//percentages.differentialRevenue = percentage / list.Count;
list.RemoveRange(0, list.Count);
percentage = 0;
amount = 0;
index = 0;
foreach (DataRow row in ds.Tables[10].Rows)
{
if (index == 0)
{
amount = Double.Parse(row[1].ToString());
}
else
{
list.Add((Double.Parse(row[1].ToString()) - amount) / amount);
amount = Double.Parse(row[1].ToString());
}
index++;
}
foreach (Double iteam in list)
{
percentage = percentage + iteam;
}
percentages.surplus = percentage / list.Count;
ViewState["percentages"] = percentages;
return percentages;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string userType = (string)Session["UserType"];
if (userType == null)
{
Response.Redirect("Login.aspx?category=User", true);
}
else
{
Session["supportUnit"] = null;
StoredProcedures sp = new StoredProcedures();
ProjectionPercentages percentages = new ProjectionPercentages();
DataSet fiscalYears = sp.GetFiscalYears();
int lastRecord = fiscalYears.Tables[0].Rows.Count - 1;
String mostRecentFY = fiscalYears.Tables[0].Rows[lastRecord][0].ToString();
Session["MostRecentFY"] = mostRecentFY;
DisplayRevenueExpenseSummaryReport();
}
}
}
