This is a migrated thread and some comments may be shown as answers.

Dynamic creation of groupheader

17 Answers 655 Views

Grid

This is a migrated thread and some comments may be shown as answers.

17 Answers, 1 is accepted

answered on 08 May 2015, 02:52 PM

Hello Julian,

I'm afraid that it is not possible to dynamically create the group header this way without breaking other features. You can get something similar by injecting html in the data cell of the group header item like shown below:

protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)

{

GridGroupHeaderItem ghItem = e.Item as GridGroupHeaderItem;

if (ghItem != null)

{

string oldValue = ghItem.DataCell.Text;

ghItem.DataCell.Text = String.Format(

"<table style=\"width: 100%; table-layout: fixed; text-align: center;\"><tbody><tr><td colspan='2'>{0}</td></tr><tr><td>Plan</td><td>Actual</td></tr></tbody></table>"

, oldValue);

}

Regards,
Daniel
Telerik

See What's Next in App Development. Register for TelerikNEXT.

answered on 11 May 2015, 03:24 PM

Daniel,

I hope you know that I meant Column Group Header. At any rate I'm uncertain of how to add a group header dynamically. The code snippet you gave me assumes a group header exists.

I tried injecting HTML into the column's caption to create a stack panel look, where I placed a table with 2 rows inside the caption for the column. Of course this didn't work as it completely blows up the grid.

12/2013

Plan Actual

If you could provide me with code to create a group header and format it such that my database value of "period" sits above the column header text that is equal to Actual as show above that would do what I need.

Again the columns "Plan" and "Actual" report for each period. I just need text sitting over these paired columns showing what the period is.

This is crucial as I'm over due on my timeline on this report as this little thing has caused me serious issues.

Thanks for any help you can provide!

answered on 13 May 2015, 01:27 PM

project-tracking-ss-new.png

project-tracking-ss-original.png

Daniel,

That was my mistake. I saw that I had not typed "column" in front of the group headers in my original post and would have fixed it but it seems you cannot edit posts once you make them.

I'm attaching a screen shot of the Telerik grid (Project Tracking SS NEW.png) that is using auto generated columns. All of the data is pulled from SQL into DataTables. I then manually create the rows and columns. In this screen shot you will see that the columns have the period concatenated to them. Just to show you what the period value is. I need that period value to be a group header. So for each paired group of columns "Plan" and "Actual" they have a column group header above them with the correct period date (Ie. 10/2013 or whatever date is for that period). I'm also attaching a screenshot of the old report done completely in JavaScript (Project Tracking SS Original.png). This will definitely show you what I mean. I just want it to appear like the old report in respect to the column group header.

I just need to create this in the code behind.

ONE LAST THING!. I'm using an older version of the Telerik controls. I'm not happy about it but I just started this new job and they have a very large application that uses Telerik quite a bit. However I don't have time to implement the new controls and fix whatever issues that may cause (I was told the last time they updated a lot of reports had to be tweaked).

Current Telerik product version that I'm using is: 2012.3.1308.40.

Old I know. :(

answered on 18 May 2015, 02:37 PM

Hello Julian,

Now I understand what are your requirements, thank you.
Have you considered using our RadPivotGrid control? Your structure seems to be a great match for this control. It is available for the version you are using - UI for ASP.NET AJAX Q3 2012 SP2. You have all the elements in a pivot control - including but not limited to column and row headers, totals and grand totals and calculations.
RadPivotGrid Overview demo

To answer your question, the programmatic creation of the multicolumn headers should look like that:

GridColumnGroup group = new GridColumnGroup();

RadGrid1.MasterTableView.ColumnGroups.Add(group);

group.HeaderText = group.Name = "January";

Possible places to add this code are Page_Init and Page_Load events, but considering the level of customization you need for your scenario you might have problems with ViewState so the first option may be the better one.

Then you need to set these custom groups to the column objects:

e.Column.ColumnGroupName = "January";

In the above example I add this code in ColumnCreated which is suitable only for autogenerated columns. If you add columns dynamically it would be best to set the ColumnGroupName property where you create them.

Regards,
Daniel
Telerik

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items

answered on 01 Jun 2015, 02:40 PM

column-group-headers.png

Daniel,

I am not sure that creating column groups will work for my scenario unfortunately. At least not in the manner you are suggesting. During the page_init I won't know what project I'm running the report for. I have to call into SQL to return my data before I know what my periods are. Since the column group's group.Name has to be the period (Ie. "1/2013", "2/2013") returned from SQL.

I actually am able to get what I want by setting the Column.ColumnGroupName in the OnColumnCreated event as you suggest but since I had to create the GridColumnGroup's outside of the page_init the view state as you said.. completely breaks.

I"m attaching a screen shot to show you that my code does give me what I want but it breaks right away once you start sliding the horizontal slider over.

So at this point I'm not sure if, for my scenario, the creation of Column groups is feasible? Do you have any ideas of another way to do this?

Here is the code I used to create the column groups and to assign the columns to each group:

This piece is done in a private method within the code behind:

protected DataTable CreateReportTable(DataTable dtProjects)

{

DataTable reportTable = new DataTable("ProjectTrackingReport");

try

{

// Get distinct Sites from returned dataTable

var distinctSiteIDs = dtProjects.AsEnumerable()

.Select(s => new

{

id = s.Field<int>("siteID"),

})

.Distinct().ToList();

// Get distinct Periods from returned dataTable

List<string> distinctPeriods = dtProjects.AsEnumerable()

.Select(r => r.Field<string>("period"))

.Distinct()

.ToList();

// Store member variable for use in PreRender

listDistinctPeriods = distinctPeriods;

reportTable.Columns.Add(" ", typeof (string));

var currentPeriod = string.Empty;

var index = 0;

if (distinctPeriods.Count > 0)

{

foreach (var item in distinctPeriods)

{

DataColumn tempPlanColumn = new DataColumn();

tempPlanColumn.DataType = System.Type.GetType("System.String");

DataColumn tempActualColumn = new DataColumn();

tempActualColumn.DataType = System.Type.GetType("System.String");

currentPeriod = string.Format("{1}", index, item);

// Create ColumnGroup for the currentPeriod

GridColumnGroup group = new GridColumnGroup();

radGrid1.MasterTableView.ColumnGroups.Add(group);

group.HeaderText = group.Name = currentPeriod;

tempPlanColumn.Caption = "Plan";

tempPlanColumn.ColumnName = "Plan" + "-" + currentPeriod;

tempActualColumn.Caption = "Actual";

tempActualColumn.ColumnName = "Actual" + "-" + currentPeriod;

reportTable.Columns.Add(tempPlanColumn);

reportTable.Columns.Add(tempActualColumn);

//reportTable.Columns.Add(string.Format("{1}", index, item.id));

index++;

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Creating the report Table [" + ex.Message + "]";

lblError.Visible = true;

}

return null;

}

Here is the code in the ColumnCreated event that assigns each column to it's corresponding group:

var colHeaderText = e.Column.HeaderText;

if (colHeaderText.Length > 0)

{

if (colHeaderText.Contains("-"))

{

string[] tempColumn = colHeaderText.Split('-');

e.Column.ColumnGroupName = tempColumn[1].ToString();

e.Column.HeaderText = tempColumn[0].ToString();

}

answered on 01 Jun 2015, 08:00 PM

Actually I was able to create the column groups in the page_init saving the dropdown selections in session variables. Then using those values on the page init to care all my column groups with the correct periods. (Ie. "1/2013", "2/2013")

However I still have a view state issue when i scroll the grid horizontally. I'm doing quite a bit of column manipulation in code. but just formatting type of things. I'm including the entire ASPX and .CS files in a zip so you can look at it. If there is some way i can get the view state fixed or if maybe you see something i'm doing horribly wrong let me know.

Thanks!

answered on 01 Jun 2015, 08:07 PM

Cannot attach a zip so here is the entire .cs file and .aspx! See if you can find an issue in the .cs that causes the viewstate to get corrupted when you scroll the grid horizontally.

using System;

using System.Data;

using System.Collections.Generic;

using System.ComponentModel;

using System.Reflection;

using System.Data.SqlClient;

using System.Drawing;

using System.Globalization;

using System.Linq;

using System.Web;

using System.Web.UI.WebControls;

using System.Windows.Forms;

using JabilProject.Administration.FinanceSites;

using Telerik.Web.UI;

using Telerik.Web.UI.ExportInfrastructure;

using BorderStyle = System.Web.UI.WebControls.BorderStyle;

using Convert = System.Convert;

namespace JabilProject.Reports.ProjectReports

{

#region enums

public enum culture

{

[Description("English")]

en_US = 7,

[Description("English (Canada)")]

en_CA = 1,

[Description("German")]

de_DE = 4

}

#endregion

/// <summary>

/// Project Tracking Report Page

/// </summary>

public partial class ProjectTrackingReport : System.Web.UI.Page

{

// Sample log4net logger declaration

//private static readonly log4net.ILog log = log4net.LogManager.GetLogger

// (System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

private const bool REMOVE_SECURITY_CHECK = true;

#region constructors

protected void Page_Init(object sender, EventArgs e)

{

var currentPeriod = string.Empty;

var index = -1;

var site = Session["site"] != null ? Session["site"].ToString() : null ;

var projectID = Session["projectID"] != null ? Session["projectID"].ToString() : null;

var currency = Session["currency"] != null ? Session["currency"].ToString() : null;

// Holds the data for this report

var dtProjects = new DataTable();

try

{

if (!string.IsNullOrEmpty(site) && !string.IsNullOrEmpty(projectID) && !string.IsNullOrEmpty(currency))

{

// Gets the starting and ending date range of the project

GetProjectDateRange(Convert.ToInt32(projectID));

var startingMonth = StartDate.Month;

var startingYear = StartDate.Year;

var endingMonth = EndDate.Month;

var endingYear = EndDate.Year;

DataTable reportTable = new DataTable();

DataTable dtSelectedSites = new DataTable("SelectedSiteTable");

dtSelectedSites.Columns.Add("id");

var isFirstPass = true;

List<SqlParameter> parameters = new List<SqlParameter>();

parameters.Add(new SqlParameter("@site", site));

parameters.Add(new SqlParameter("@projectID", projectID));

parameters.Add(new SqlParameter("@currency", currency));

parameters.Add(new SqlParameter("@startingMonth", startingMonth));

parameters.Add(new SqlParameter("@startingYear", startingYear));

parameters.Add(new SqlParameter("@endingMonth", endingMonth));

parameters.Add(new SqlParameter("@endingYear", endingYear));

parameters.Add(new SqlParameter("@exportToXML", false));

dtProjects = JPDB3.GetDataTable(parameters, "Projects_GetProjectTrackingData",

"Capacity", "Error Getting Project Tracking Data:", CommandType.StoredProcedure, 6000);

// Get distinct Periods from returned dataTable

List<string> distinctPeriods = dtProjects.AsEnumerable()

.Select(r => r.Field<string>("period"))

.Distinct()

.ToList();

// Store member variable for use in PreRender

listDistinctPeriods = distinctPeriods;

if (listDistinctPeriods.Count > 0)

{

foreach (var item in listDistinctPeriods)

{

currentPeriod = string.Format("{1}", index, item);

// Create ColumnGroup for the currentPeriod

GridColumnGroup group = new GridColumnGroup();

radGrid1.MasterTableView.ColumnGroups.Add(group);

group.HeaderText = group.Name = currentPeriod;

index++;

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Handles the Load event of the Page control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void Page_Load(object sender, EventArgs e)

{

if (IsPostBack == false)

{

// Fresh load of page - Populate dropDowns

// We have not run any reports yet

FirstSite = true;

// Clear session variables to make UI experience the same

Session.Remove("CustomerId");

Session.Remove("ProjectId");

// Does not help our issue with column headers

//radGrid1.ClientSettings.Scrolling.UseStaticHeaders = true;

PopulateDropdowns();

}

#endregion

#region UI Methods

/// <summary>

/// Handles the OnDataBound event of the radGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void radGrid1_OnDataBound(object sender, EventArgs e)

{

// first lets format the grid properly - this is being overriden by the OnPreRender event!!!!!

GridColumn[] gcArray = radGrid1.MasterTableView.AutoGeneratedColumns;

for (int col = 0; col < gcArray.Length; col++)

{

if (col == 0)

{

gcArray[col].HeaderStyle.Width = 220;

gcArray[col].HeaderStyle.HorizontalAlign = HorizontalAlign.Left;

//gcArray[col].ItemStyle.HorizontalAlign = HorizontalAlign.Right;

}

else

{

gcArray[col].HeaderStyle.HorizontalAlign = HorizontalAlign.Right;

gcArray[col].ItemStyle.HorizontalAlign = HorizontalAlign.Right;

}

/// <summary>

/// Handles the OnItemDataBound event of the radGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="GridItemEventArgs"/> instance containing the event data.</param>

protected void radGrid1_OnItemDataBound(object sender, GridItemEventArgs e)

{

// Currently not used

// We need to make sure empty entries are intialized to 0.00 and are set to the correct currency symbol

//if (e.Item is GridDataItem)

//{

// GridDataItem item = (GridDataItem) e.Item;

// FormatItem(item);

//}

//GridGroupHeaderItem gridHeaderItem = e.Item as GridGroupHeaderItem;

//if (gridHeaderItem != null)

//{

// string oldValue = gridHeaderItem.DataCell.Text;

// gridHeaderItem.DataCell.Text =

// String.Format("<table style=\"width: 100%; table-layout: fixed; text-align: center;\"><tbody><tr><td colspan='2'>{0}</td></tr><tr><td>Plan</td><td>Actual</td></tr></tbody></table>", oldValue);

//}

// Example coloring the backcolor for an entire COLUMN

////foreach (GridDataItem item in radGrid1.Items)

////{

//// GridDataItem dataItem = item as GridDataItem;

//// GridDataItem row = item as GridDataItem;

//// if ((dataItem[" "].Text != "<b>Work Packages Total</b>") && (dataItem[" "].Text != "<b>Total Development Costs</b>")

//// && (dataItem[" "].Text != "<b>NRE TOTAL</b>"))

//// {

//// row[" "].BackColor = System.Drawing.ColorTranslator.FromHtml("#EFEFEF");

//// }

////}

if (e.Item is GridDataItem)

{

GridDataItem dataItem = e.Item as GridDataItem;

//HOWTO: Access dataRow value from dataItem

//((DataRowView)e.Item.DataItem)[" "].ToString()

if ((dataItem[" "].Text == "<b>Work Packages Total</b>") || (dataItem[" "].Text == "<b>Total Development Costs</b>")

|| (dataItem[" "].Text == "<b>NRE TOTAL</b>"))

{

dataItem.BackColor = System.Drawing.ColorTranslator.FromHtml("#F7E69E");

//dataItem[" "].Style.Add("font-style", "italic");

//foreach (var date in dates)

//{

// // Iterate columns in this row that contain data and set their font to Italic

// month = Convert.ToInt32(date.GetType().GetProperty("Month").GetValue(date, null));

// year = Convert.ToInt32(date.GetType().GetProperty("Year").GetValue(date, null));

// columnName = month + "/" + year;

// dataItem[columnName].Style.Add("font-style", "italic");

//}

}

//if (dataItem[" "].Text.Contains("Site:"))

//{

// dataItem.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFCC00");

//}

if (dataItem[" "].Text.Contains("Project:"))

{

dataItem.BackColor = System.Drawing.ColorTranslator.FromHtml("#EFEFEF");

}

if (dataItem[" "].Text.Contains("Site:") || dataItem[" "].Text.Contains("NRE T") || dataItem[" "].Text.Contains("Work Packages") ||

dataItem[" "].Text.Contains("Total Dev") || dataItem[" "].Text.Contains("Project:"))

{

dataItem[" "].HorizontalAlign = HorizontalAlign.Left;

}

/// <summary>

/// Handles the OnInfrastructureExporting event of the radGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="GridInfrastructureExportingEventArgs"/> instance containing the event data.</param>

//protected void radGrid1_OnInfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)

//{

// //TODO: method called from RadButton but not from automatically created button at that top right of the grid

// //foreach (Column column in e.ExportStructure.Tables[0].Columns)

// //{

// // var index = column.Index;

// foreach (Cell cell in e.ExportStructure.Tables[0].Columns[1].Cells)

// {

// var count = 0.00;

// count++;

// var numStyles = NumberStyles.AllowParentheses | NumberStyles.AllowThousands |

// NumberStyles.AllowDecimalPoint;

// var cellValue = string.Empty;

// cellValue = cell.Text;

// cellValue = cellValue.Replace("<b>", "").Replace("</b>", "").Replace("--", "").Replace(" ", "");

// cell.Value = cellValue;

// var outCellValue = 0.00;

// var isNumeric = double.TryParse("cellValue", out outCellValue);

// var isEmptyOrNull = string.IsNullOrEmpty(cellValue);

// var isText = !isNumeric && !string.IsNullOrEmpty(cellValue) ? true : false;

// //if (cellValue.Length >0 && !)

// if (!isText && !isEmptyOrNull)

// {

// cellValue = Double.Parse(cellValue, numStyles).ToString();

// cell.Value = GetLocalizedValueForExcelExport(Convert.ToDouble(cellValue));

// }

//}

/// <summary>

/// Handles the OnExportCellFormatting event of the radGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="ExportCellFormattingEventArgs"/> instance containing the event data.</param>

protected void radGrid1_OnExportCellFormatting(object sender, ExportCellFormattingEventArgs e)

{

var numStyles = NumberStyles.AllowParentheses | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands;

var cellValue = string.Empty;

var item = e.Cell.Parent as GridDataItem;

//TODO: this align does not work

e.FormattedColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Right;

if (e.FormattedColumn.UniqueName.Length > 3)

{

if ((e.FormattedColumn.UniqueName.Substring(0, 4) == "Plan") && (e.FormattedColumn.UniqueName.Substring(0, 6) != "Plan C")

|| (e.FormattedColumn.UniqueName.Substring(0,4) == "Actu"))

{

if (item != null)

{

cellValue = item[e.FormattedColumn.UniqueName].Text;

cellValue = cellValue.Replace("<b>", "").Replace("</b>", "").Replace("--", "").Replace(" ","");

if (item[e.FormattedColumn.UniqueName].Text != " ")

{

if (!string.IsNullOrEmpty(cellValue))

{

cellValue = Double.Parse(cellValue, numStyles).ToString();

}

else

{

cellValue = "0.00";

}

item[e.FormattedColumn.UniqueName].Text = GetLocalizedValueForExcelExport(Convert.ToDouble(cellValue));

}

if (e.FormattedColumn.UniqueName.Length > 3)

{

if (e.FormattedColumn.UniqueName.Substring(0, 4) == "Plan")

{

e.FormattedColumn.HeaderStyle.Width = Unit.Point(27);

}

// Alternate row colors

if (item.ItemType == Telerik.Web.UI.GridItemType.AlternatingItem)

item.Style["background-color"] = "#E1E1DF";

else

item.Style["background-color"] = "#FFFFFF";

var dataCell = e.Cell;

}

/// <summary>

/// Handles the OnNeedDataSource event of the radPivotGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="PivotGridNeedDataSourceEventArgs"/> instance containing the event data.</param>

protected void radGrid1_OnNeedDataSource(object sender, GridNeedDataSourceEventArgs e)

{

//var dtProjects = PopulateTheGrid();

//try

//{

// //Session.Remove("reportTable");

// if (Session["reportTable"] != null)

// {

// var dt = (DataTable)Session["reportTable"];

// (sender as RadGrid).DataSource = dt;

// }

//}

//catch (Exception ex)

//{

// lblError.Text = "The Following Error Occurred Creating The Report [" + ex.Message + "]";

// lblError.Visible = true;

//}

}

/// <summary>

/// Handles the OnPreRender event of the radGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void radGrid1_OnPreRender(object sender, EventArgs e)

{

foreach (GridColumn column in radGrid1.MasterTableView.AutoGeneratedColumns)

{

if (column.UniqueName == " ")

{

column.ItemStyle.Width = Unit.Pixel(1200);

}

//var planColumn = string.Empty;

//var actualColumn = string.Empty;

//// PreRender is called frequently do not execute logic unless we have created the session variable distinctPeriods

//if (listDistinctPeriods.Count != 0)

//{

// foreach (var period in listDistinctPeriods)

// {

// planColumn = "Plan-" + period;

// actualColumn = "Actual-" + period;

// foreach (GridDataItem dataItem in radGrid1.MasterTableView.Items)

// {

// if (dataItem[planColumn].Text == dataItem[actualColumn].Text)

// {

// dataItem[planColumn].ColumnSpan = 2;

// dataItem[actualColumn].Visible = false;

// break;

// }

// //if (dataItem["Plan-10/2013"].Text == dataItem["Actual-10/2013"].Text)

// //{

// // dataItem["Plan-10/2013"].ColumnSpan = 2;

// // dataItem["Actual-10/2013"].Visible = false;

// // break;

// //}

// int previousItemIndex = dataItem.ItemIndex - 1;

// if (previousItemIndex >= 0)

// {

//                if (dataItem["Plan-10/2013"].Text == dataItem.OwnerTableView.Items[previousItemIndex]["Plan-10/2013"].Text)

// {

// dataItem.OwnerTableView.Items[previousItemIndex]["Plan-10/2013"].RowSpan = 2;

// dataItem["Plan-10/2013"].Visible = false;

// }

//}

}

/// <summary>

/// Handles the Click event of the btnExport control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void excelExportButton_Click(object sender, EventArgs e)

{

// Grab reportTable from session

//var exportReportTable = ReportTable;

try

{

// WORKING

radGrid1.ExportSettings.Excel.Format =

(GridExcelExportFormat) Enum.Parse(typeof (GridExcelExportFormat), "Xlsx");

radGrid1.ExportSettings.ExportOnlyData = true;

radGrid1.ExportSettings.IgnorePaging = true;

radGrid1.MasterTableView.ExportToExcel();

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Handles the OnClick event of the findProjectButton control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

/// <exception cref="NotImplementedException"></exception>

protected void findProjectButton_OnClick(object sender, EventArgs e)

{

if (projectIDTextBox.Text.Length > 0)

{

try

{

var projectId = Convert.ToInt32(projectIDTextBox.Text);

// Auto populate dropdowns based

var dtProjectInfo =

jpdb.Projects.GetCustomerAndSectorForProjectAndDate(Convert.ToInt32(projectIDTextBox.Text),

DateTime.Now);

for (var i = 0; i < dtProjectInfo.Rows.Count; i++)

{

Session["SectorId"] = Convert.ToInt32(dtProjectInfo.Rows[i]["sectorID"]);

Session["CustomerId"] = Convert.ToInt32(dtProjectInfo.Rows[i]["customerID"]);

}

Session["ProjectId"] = projectId;

PopulateDropdowns();

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred [" + ex.Message + "]";

lblError.Visible = true;

}

else

{

lblError.Text = "You have to enter a value.";

lblError.Visible = true;

}

/// <summary>

/// Handles the OnClick event of the btnRunReport control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void runReportButton_OnClick(object sender, EventArgs e)

{

lblError.Visible = false;

FirstPass = false;

// Get culture based off of selected currency - Not used currently

selectedCurrencyCulture = (culture)Convert.ToInt32(radComboBoxCurrencies.SelectedValue);

try

{

var reportTable = PopulateTheGrid();

if (reportTable != null)

{

radGrid1.Style.Add("display", "block");

radGrid1.DataSource = reportTable;

radGrid1.DataBind();

}

// Store the table, needed for Excel Export

Session["reportTable"] = reportTable;

}

catch (Exception ex)

{

lblError.Text = "Error occurred running the report [" + ex.Message + "]";

lblError.Visible = true;

}

radGrid1.ExportSettings.FileName = "ProjectTrackingReport" + "_" + radComboBoxSectors.SelectedItem.Text +

"_" + radComboBoxCustomers.SelectedItem.Text + "_" + radComboBoxProjects.SelectedItem.Text;

//TODO: Not sure if this is needed??

//// Set up excel filename

//int period = 0;

//int year = 0;

//DateTime selectedDate = new DateTime(rmypPeriod.SelectedDate.Value.Year, rmypPeriod.SelectedDate.Value.Month, 1);

//DateUtility.JP2_GetPeriodAndYearForCalendarDate(selectedDate, out period, out year);

//rgGrid.ExportSettings.FileName = "TM1_" + rcbFinanceSite.Text.Replace(' ', '_') + "_" + period + "_" + year;

}

/// <summary>

/// Handles the OnSelectedIndexChanged event of the radComboBoxCurrencies control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs"/> instance containing the event data.</param>

protected void radComboBoxCurrencies_OnSelectedIndexChanged(object sender,Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)

{

Session["currency"] = radComboBoxCurrencies.SelectedValue.ToString();

}

/// <summary>

/// Handles the SelectedIndexChange event of the radComboBoxCustomers control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="RadComboBoxSelectedIndexChangedEventArgs"/> instance containing the event data.</param>

protected void radComboBoxCustomers_SelectedIndexChanged(object sender,

RadComboBoxSelectedIndexChangedEventArgs e)

{

ClearDropDowns(false, false, false, true, false);

var selectedCustomer = e.Value;

try

{

// Optional bool value

// false = all customers

// true = only active customers (projectStatus == '3')

GetProjectsForCustomer(Convert.ToInt32(selectedCustomer), false);

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred getting projects for the customer selected [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Handles the OnDataBound event of the radComboBoxCustomers control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void radComboBoxCustomers_OnDataBound(object sender, EventArgs e)

{

var radComboBox = (RadComboBox) sender;

if (!radComboBox.IsEmpty)

{

radComboBox.Items.Insert(0, new RadComboBoxItem("---- Select Customer ----", string.Empty));

}

/// <summary>

/// Handles the OnDataBound event of the radComboBoxProjects control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void radComboBoxProjects_OnDataBound(object sender, EventArgs e)

{

var radComboBox = (RadComboBox) sender;

if (!radComboBox.IsEmpty)

{

radComboBox.Items.Insert(0, new RadComboBoxItem("---- Select Project ----", string.Empty));

}

/// <summary>

/// Handles the OnDataBound event of the radComboBoxSectors control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void radComboBoxSectors_OnDataBound(object sender, EventArgs e)

{

var radComboBox = (RadComboBox) sender;

if (!radComboBox.IsEmpty)

{

radComboBox.Items.Insert(0, new RadComboBoxItem("---- Select Sector ----", string.Empty));

}

/// <summary>

/// Handles the OnDataBound event of the radComboBoxSites control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>

protected void radComboBoxSites_OnDataBound(object sender, EventArgs e)

{

var radComboBox = (RadComboBox) sender;

if (!radComboBox.IsEmpty)

{

radComboBox.Items.Insert(0, new RadComboBoxItem("---- Select Site ----", string.Empty));

}

/// <summary>

/// Handles the SelectedIndexChange event of the radComboBoxProjects control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="RadComboBoxSelectedIndexChangedEventArgs"/> instance containing the event data.</param>

protected void radComboBoxProjects_OnSelectedIndexChanged(object sender,

RadComboBoxSelectedIndexChangedEventArgs e)

{

ClearDropDowns(false, false, false, true, false);

Session["projectID"] = radComboBoxProjects.SelectedValue.ToString();

// Insert default value into projects drop down

radComboBoxSites.Items.Insert(0, new RadComboBoxItem("---- Select Site ----", string.Empty));

var selectedProject = e.Value;

// Get the sites for drop down

GetProjectTeamsForProject(Convert.ToInt32(selectedProject));

}

/// <summary>

/// Handles the SelectedIndexChanged event of the radComboBoxSectors control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs"/> instance containing the event data.</param>

protected void radComboBoxSectors_SelectedIndexChanged(object sender,

Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)

{

ClearDropDowns(false, false, true, true, false);

var selectedSector = e.Value;

// Insert default value into projects drop down

radComboBoxCustomers.Items.Insert(0, new RadComboBoxItem("---- Select Customer ----", string.Empty));

GetCustomersInProjectSectorForCurrentDate(Convert.ToInt32(selectedSector));

}

protected void radComboBoxSites_OnSelectedIndexChanged(object sender,Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs e)

{

Session["site"] = radComboBoxSites.SelectedValue.ToString();

}

/// <summary>

/// Handles the OnColumnCreated event of the radGrid1 control.

/// </summary>

/// <param name="sender">The source of the event.</param>

/// <param name="e">The <see cref="GridColumnCreatedEventArgs"/> instance containing the event data.</param>

protected void radGrid1_OnColumnCreated(object sender, GridColumnCreatedEventArgs e)

{

//GridColumnGroup group = new GridColumnGroup();

//radGrid1.MasterTableView.ColumnGroups.Add(group);

//group.HeaderText = group.Name = "ColumnGroup";

var colHeaderText = e.Column.HeaderText;

if (colHeaderText.Length > 0)

{

if (colHeaderText.Contains("-"))

{

string[] tempColumn = colHeaderText.Split('-');

e.Column.ColumnGroupName = tempColumn[1].ToString();

//e.Column.HeaderText = tempColumn[0].ToString();

}

// RadGrid's created dynamically only use the columnName as "headerText"

// Use this code to override that and tell it to use the column's "caption"

//if (e.Column is IGridDataColumn)

//{

// IGridDataColumn column = e.Column as IGridDataColumn;

// var columnName = column.GetActiveDataField();

// var grid = sender as RadGrid;

// if (grid.DataSourceObject != null)

// {

// DataTable table = grid.DataSource as DataTable;

// foreach (DataColumn dColumn in table.Columns)

// {

// if (dColumn.ColumnName == columnName)

// {

// e.Column.HeaderText = dColumn.Caption;

// }

//}

}

#endregion

#region Private Methods

/// <summary>

/// Adds the report data for nre.

/// </summary>

/// <param name="nreTable">The nre table.</param>

/// <param name="site">The site.</param>

/// <param name="reportTable">The report table.</param>

private void AddReportDataForNRE(DataTable nreTable, string site, ref DataTable reportTable)

{

var distinctPeriods = nreTable.AsEnumerable()

.Select(s => new

{

id = s.Field<string>("period"),

})

.Distinct().ToList();

var currentPeriod = string.Empty;

var index = 0;

foreach (var item in distinctPeriods)

{

currentPeriod = string.Format("{1}", index, item.id);

// Get rows

// IF the data row's discipline is NA exclude

// for each data row populate Plan and Actual for that row's discipline. ALso populate

// Work packages TOTAL values and NRE Total.

DataRow[] siteDataRow =

nreTable.Select("period='" + currentPeriod + "'" + "AND disciplineName <> '" + "NA" + "'");

// Grab working Column

var planColumnIndex = reportTable.Columns.IndexOf("Plan" + "-" + item.id);

var actualColumnIndex = reportTable.Columns.IndexOf("Actual" + "-" + item.id);

var totalPlanColumnIndex = reportTable.Columns.IndexOf("Total Plan");

var totalActualColumnIndex = reportTable.Columns.IndexOf("Total Actual");

var planConformanceTotal = reportTable.Columns.IndexOf("Plan Conformance");

foreach (DataRow row in siteDataRow)

{

var siteRow = row["disciplineName"].ToString() != "Work Packages Total"

? row["disciplineName"] + "-" + "NRE" + site

: "NRE TOTAL" + "-" + site;

var planValue = String.Format("{0:N}", row["plannedValue"]);

var actualValue = String.Format("{0:N}", row["actualValue"]);

var totalPlanValue = String.Format("{0:N}", row["totalPlanValue"]);

var totalActValue = String.Format("{0:N}", row["totalActValue"]);

var totalConformance = String.Format("{0:N}", row["totalConformance"]);

totalConformance = totalConformance + " %";

// Populating Period for our fake Column Header row (rowid = "Work Packages")

//reportTable.Rows[0][planColumnIndex] = item.id;

//reportTable.Rows[0][actualColumnIndex] = item.id;

// Find correct Row and set the value (row,col)

var rowIndex = ReportRows[siteRow];

reportTable.Rows[rowIndex][planColumnIndex] = planValue;

reportTable.Rows[rowIndex][actualColumnIndex] = actualValue;

reportTable.Rows[rowIndex][totalPlanColumnIndex] = totalPlanValue;

reportTable.Rows[rowIndex][totalActualColumnIndex] = totalActValue;

reportTable.Rows[rowIndex][planConformanceTotal] = totalConformance;

}

index++;

}

/// <summary>

/// Adds the report data for non nre.

/// </summary>

/// <param name="disciplineTable">The discipline table.</param>

/// <param name="site">The site.</param>

/// <param name="reportTable">The report table.</param>

private void AddReportDataForNonNRE(DataTable disciplineTable, string site, ref DataTable reportTable)

{

var distinctPeriods = disciplineTable.AsEnumerable()

.Select(s => new

{

id = s.Field<string>("period"),

})

.Distinct().ToList();

var currentPeriod = string.Empty;

var index = 0;

foreach (var item in distinctPeriods)

{

currentPeriod = string.Format("{1}", index, item.id);

DataRow[] siteDataRow =

disciplineTable.Select("period='" + currentPeriod + "'" + "AND disciplineName <> '" + "NA" + "'");

// IF the data row's discipline is NA exclude

// for each data row populate Plan and Actual for that row's discipline. ALso populate

// Work packages TOTAL values and NRE Total.

// Grab working Column

var planColumnIndex = reportTable.Columns.IndexOf("Plan" + "-" + item.id);

var actualColumnIndex = reportTable.Columns.IndexOf("Actual" + "-" + item.id);

var totalPlanColumnIndex = reportTable.Columns.IndexOf("Total Plan");

var totalActualColumnIndex = reportTable.Columns.IndexOf("Total Actual");

var planConformanceTotal = reportTable.Columns.IndexOf("Plan Conformance");

foreach (DataRow row in siteDataRow)

{

if (row["disciplineName"].ToString() == "Work Packages Total")

{

// if work packages total then set the outputted value to BOLD "<b></b>"

workPackagesTotalRow = true;

}

var siteRow = row["disciplineName"] + "-" + site;

var planValue = String.Format("{0:N}", row["plannedValue"]);

var actualValue = String.Format("{0:N}", row["actualValue"]);

var totalPlanValue = String.Format("{0:N}", row["totalPlanValue"]);

var totalActValue = String.Format("{0:N}", row["totalActValue"]);

var totalConformance = String.Format("{0:N}", row["totalConformance"]);

totalConformance = totalConformance + " %";

// For display purposes this can cause issues. If the system doesn't support the culture you are trying to display

// Convert values to selected currency type that was selected in the currency drop down box

//planValue = GetLocalizedValueForGridDisplay(Convert.ToDouble(planValue), selectedCurrencyCulture);

//actualValue = GetLocalizedValueForGridDisplay(Convert.ToDouble(actualValue), selectedCurrencyCulture);

//totalPlanValue = GetLocalizedValueForGridDisplay(Convert.ToDouble(totalPlanValue), selectedCurrencyCulture);

//totalActValue = GetLocalizedValueForGridDisplay(Convert.ToDouble(totalActValue), selectedCurrencyCulture);

// Find correct Row

var rowIndex = ReportRows[siteRow];

//// Populating Period for our fake Column Header row (rowid = "Work Packages")

//reportTable.Rows[0][planColumnIndex] = item.id;

//reportTable.Rows[0][actualColumnIndex] = item.id;

reportTable.Rows[rowIndex][planColumnIndex] = FormatValue(Convert.ToDouble(planValue), workPackagesTotalRow);

reportTable.Rows[rowIndex][actualColumnIndex] = FormatValue(Convert.ToDouble(actualValue), workPackagesTotalRow);

reportTable.Rows[rowIndex][totalPlanColumnIndex] = FormatValue(Convert.ToDouble(totalPlanValue), workPackagesTotalRow);

reportTable.Rows[rowIndex][totalActualColumnIndex] = FormatValue(Convert.ToDouble(totalActValue), workPackagesTotalRow);

reportTable.Rows[rowIndex][planConformanceTotal] = FormatPercent(String.Format("{0:P}", totalConformance), workPackagesTotalRow);

workPackagesTotalRow = false;

}

index++;

}

/// <summary>

/// Adds the styles to data cells.

/// </summary>

/// <param name="modelDataCell">The model data cell.</param>

/// <param name="e">The e.</param>

//private static void AddStylesToDataCells(PivotGridBaseModelCell modelDataCell, PivotGridCellExportingArgs e)

//{

// if (modelDataCell.Data != null && modelDataCell.Data.GetType() == typeof (decimal))

// {

// var value = Convert.ToDecimal(modelDataCell.Data);

// if (value > 100000)

// {

// e.ExportedCell.Style.BackColor = Color.FromArgb(51, 204, 204);

// AddBorders(e);

// }

// e.ExportedCell.Format = "$0.0";

// }

//}

/// <summary>

/// Adds the styles to column header cells.

/// </summary>

/// <param name="modelDataCell">The model data cell.</param>

/// <param name="e">The e.</param>

//private static void AddStylesToColumnHeaderCells(PivotGridBaseModelCell modelDataCell,

// PivotGridCellExportingArgs e)

//{

// if (e.ExportedCell.Table.Columns[e.ExportedCell.ColIndex].Width == 0)

// {

// e.ExportedCell.Table.Columns[e.ExportedCell.ColIndex].Width = 120D;

// }

// if (modelDataCell.IsTotalCell)

// {

// //e.ExportedCell.Style.BackColor = Color.FromArgb(150, 150, 150);

// e.ExportedCell.Style.BackColor = Color.FromArgb(138, 228, 107);

// e.ExportedCell.Style.Font.Bold = true;

// }

// else

// {

// //e.ExportedCell.Style.BackColor = Color.FromArgb(192, 192, 192);

// e.ExportedCell.Style.BackColor = Color.FromArgb(171, 236, 150);

// }

// AddBorders(e);

//}

/// <summary>

/// Adds the styles to row header cells.

/// </summary>

/// <param name="modelDataCell">The model data cell.</param>

/// <param name="e">The e.</param>

//private void AddStylesToRowHeaderCells(PivotGridBaseModelCell modelDataCell, PivotGridCellExportingArgs e)

//{

// // Set the column width of all row headers to 230

// if (e.ExportedCell.Table.Columns[e.ExportedCell.ColIndex].Width == 0)

// {

// e.ExportedCell.Table.Columns[e.ExportedCell.ColIndex].Width = 230D;

// }

// if (modelDataCell.IsTotalCell)

// {

// //e.ExportedCell.Style.BackColor = Color.FromArgb(150, 150, 150);

// e.ExportedCell.Style.BackColor = Color.FromArgb(138, 228, 107);

// e.ExportedCell.Style.Font.Bold = true;

// }

// else if (modelDataCell.IsGrandTotalCell)

// {

// //e.ExportedCell.Style.BackColor = Color.FromArgb(192, 192, 192);

// e.ExportedCell.Style.BackColor = Color.FromArgb(138, 228, 107);

// }

// else

// {

// e.ExportedCell.Style.BackColor = Color.FromArgb(192, 192, 192);

// //e.ExportedCell.Style.BackColor = Color.FromArgb(171, 236, 150);

// }

// AddBorders(e);

//}

///<summary>

/// Clears the drop downs.

/// </summary>

/// <param name="sectors">if set to <c>true</c> [sectors].</param>

/// <param name="customers">if set to <c>true</c> [customers].</param>

/// <param name="projects">if set to <c>true</c> [projects].</param>

/// <param name="sites">if set to <c>true</c> [sites].</param>

/// <param name="currency">if set to <c>true</c> [currency].</param>

private void ClearDropDowns(bool sectors, bool customers, bool projects, bool sites, bool currency)

{

if (sectors)

{

radComboBoxSectors.Items.Clear();

}

if (customers)

{

radComboBoxCustomers.Items.Clear();

}

if (projects)

{

radComboBoxProjects.Items.Clear();

}

if (sites)

{

radComboBoxSites.Items.Clear();

}

if (currency)

{

radComboBoxCurrencies.Items.Clear();

}

/// <summary>

/// Converts the value.

/// </summary>

/// <param name="value">The value.</param>

/// <returns></returns>

private double ConvertValue(double value)

{

double currencyRatio = 0;

currencyRatio = 1;

return Math.Round(value, 2);

}

/// <summary>

/// Ceates the report rows for a site.

/// </summary>

/// <param name="siteTable">The site table.</param>

/// <param name="reportTable">The report table.</param>

private void CreateReportRowsForASite(DataTable siteTable, string siteNumber, ref DataTable reportTable)

{

//Get siteName for this site

var aRow = siteTable.Rows[0];

var currentSiteName = aRow["siteName"].ToString();

var siteID = siteNumber;

// Get distinct periods for this site

var distinctPeriods = siteTable.AsEnumerable()

.Select(s => new

{

id = s.Field<string>("period"),

})

.Distinct().ToList();

var firstPeriod = distinctPeriods.First();

// Get the discipline rows into a table

IEnumerable<DataRow> queryGetDisciplineRows = from myRow in siteTable.AsEnumerable()

where myRow.Field<string>("type") == "Disciplines"

select myRow;

var disciplineTable = new DataTable();

// Do we have any "type = discipline" data for this site?

var disciplineRowCount = queryGetDisciplineRows.Count();

disciplineTable = (disciplineRowCount > 0) ? queryGetDisciplineRows.CopyToDataTable<DataRow>() : null;

var diciplineDistinceDisciplineNames = disciplineTable != null

? disciplineTable.AsEnumerable()

.OrderBy(f => f.Field<string>("disciplineName"))

.Select(s => new

{

id = s.Field<string>("disciplineName"),

})

.Distinct().ToList()

: null;

var nreTable = new DataTable();

// Get the NRE Rows into a table

IEnumerable<DataRow> queryGetNRERows = from myRow in siteTable.AsEnumerable()

where myRow.Field<string>("type") == "NRE"

select myRow;

var nreRowCount = queryGetNRERows.Count();

nreTable = (nreRowCount > 0) ? queryGetNRERows.CopyToDataTable<DataRow>() : null;

// Get distinct NRE Discipline names if we have NRE

var nreDistincDisciplineNames = nreTable != null

? nreTable.AsEnumerable()

.OrderBy(f => f.Field<string>("disciplineName"))

.Select(s => new

{

id = s.Field<string>("disciplineName"),

})

.Distinct().ToList()

: null;

// Create Site Row

rowSite = CreateReportRow(ref reportTable, "<b>Site: </b>" + currentSiteName);

// If you want to add a blank row after site use this code.

// if it's the first site for the report do not add blank row otherwise insert a blank row

// if DisciplineName = NA do not create row

//if (!FirstSite)

//{

//CreateReportRow(ref reportTable, "");

//}

if (disciplineTable != null)

{

var index = 0;

// Create report rows for "Type = Discipline" items - Work Packages Total row is created here as well

foreach (var item in diciplineDistinceDisciplineNames)

{

if (item.id != "NA")

{

if (item.id == "Work Packages Total")

{

CreateReportRow(ref reportTable, "<b>" + item.id + "</b>" + "-" + siteID);

}

else

{

CreateReportRow(ref reportTable, item.id + "-" + siteID);

}

index++;

}

else

{

// This row is always created even if we have no discipline data for the site for Non NRE

CreateReportRow(ref reportTable, "<b>" + "Work Packages Total" + "</b>" + "-" + siteID);

}

// This row is always shown

CreateReportRow(ref reportTable, "<b>NRE</b>" + "-" + siteID);

var doWeHaveNRE = false;

if (nreTable != null)

{

var index2 = 0;

// Create report rows for "Type = NRE" items

foreach (var item in nreDistincDisciplineNames)

{

if (item.id != "NA" && item.id != "Work Packages Total")

{

CreateReportRow(ref reportTable, item.id + "-" + "NRE" + siteID);

doWeHaveNRE = true;

}

index2++;

}

if (doWeHaveNRE)

{

// We had NRE so show NRE TOTAL row

CreateReportRow(ref reportTable, "<b>NRE TOTAL</b>" + "-" + siteID);

}

// Populate report Data for non NRE Rows

if (disciplineTable != null)

{

AddReportDataForNonNRE(disciplineTable, siteNumber, ref reportTable);

}

// Populate report Data for NRE rows

if (nreTable != null && doWeHaveNRE)

{

AddReportDataForNRE(nreTable, siteNumber, ref reportTable);

}

// --- GET TOTALS --- //

// Create Site totals row and it's values

CreateReportRow(ref reportTable, "<b>Total Development Costs</b>" + "-" + siteID);

CreateReportRow(ref reportTable, "");

// Loop distinct periods for entire dataTable and calculate totals for Non-NRE + NRE

var dpIndex = 0;

var currentPeriod = string.Empty;

foreach (var item in distinctPeriods)

{

// Initialize total Plan and Actual values which will be used to calculate Site totals

var totalNonNreTablePlanValue = 0.00;

var totalNonNreTableActualValue = 0.00;

var totalNreTablePlanValue = 0.00;

var totalNreTableActualValue = 0.00;

var siteTotalPlanValue = 0.00;

var siteTotalActualValue = 0.00;

var planColumnIndex = -1;

var actualColumnIndex = -1;

var rowIndex = -1;

currentPeriod = string.Format("{1}", dpIndex, item.id);

if (disciplineTable != null)

{

// Get Discipline Data for Site Totals row for the current period we are dealing with "currentPeriod"

DataRow[] disciplineSiteDataRow =

disciplineTable.Select("period='" + currentPeriod + "'" + "AND disciplineName <> '" + "NA" + "'" +

"AND disciplineName <> '" + "Work Packages Total" + "'");

foreach (DataRow row in disciplineSiteDataRow)

{

//string siteRow = row["disciplineName"].ToString() + "-" + siteID;

var planValue = String.Format("{0:N}", row["plannedValue"]);

var actualValue = String.Format("{0:N}", row["actualValue"]);

totalNonNreTablePlanValue += Convert.ToDouble(planValue);

totalNonNreTableActualValue += Convert.ToDouble(actualValue);

}

if (nreTable != null && doWeHaveNRE)

{

// Get NRE Data for Site Totals row for the current period we are dealing with "currentPeriod"

DataRow[] nreSiteDataRow =

nreTable.Select("period='" + currentPeriod + "'" + "AND disciplineName <> '" + "NA" + "'" +

"AND disciplineName <> '" + "Work Packages Total" + "'");

foreach (DataRow row in nreSiteDataRow)

{

//string siteRow = row["disciplineName"].ToString() + "-" + siteID;

var planValue = String.Format("{0:N}", row["plannedValue"]);

var actualValue = String.Format("{0:N}", row["actualValue"]);

totalNreTablePlanValue = totalNreTablePlanValue + Convert.ToDouble(planValue);

totalNreTableActualValue = totalNreTableActualValue + Convert.ToDouble(actualValue);

}

// Push Plan and Actual Totals for the Total Development Costs row

siteTotalPlanValue = totalNonNreTablePlanValue + totalNreTablePlanValue;

siteTotalActualValue = totalNonNreTableActualValue + totalNreTableActualValue;

actualColumnIndex = reportTable.Columns.IndexOf("Actual" + "-" + item.id);

planColumnIndex = reportTable.Columns.IndexOf("Plan" + "-" + item.id);

rowIndex = ReportRows["Total Development Costs" + "-" + siteID];

reportTable.Rows[rowIndex][planColumnIndex] = FormatValue(siteTotalPlanValue, true);

reportTable.Rows[rowIndex][actualColumnIndex] = FormatValue(siteTotalActualValue, true);

dpIndex++;

}

// Get Plan Total, Actual Total, and Plan Conformance for Workpackages total row and Site Totals Row

// Sloppy code, need a better way to get site totals for Total Plan and Total Actual & Plan Conformance

if (disciplineTable != null)

{

// Get Discipline Data for Site Totals row

DataRow[] disciplineSiteTotalsDataRow =

disciplineTable.Select("disciplineName <> '" + "NA" + "'" +

"AND disciplineName <> '" + "Work Packages Total" + "'");

foreach (DataRow row in disciplineSiteTotalsDataRow)

{

// Check to see if we already grabbed the totals for this discipline.

if (!disciplineTotalsAlreadyGathered.Contains(row["disciplineName"].ToString()))

{

var totalPlanValue = String.Format("{0:N}", row["totalPlanValue"]);

var totalActValue = String.Format("{0:N}", row["totalActValue"]);

totalNonNreTableTotalPlanValue += Convert.ToDouble(totalPlanValue);

totalNonNreTableTotalActValue += Convert.ToDouble(totalActValue);

}

disciplineTotalsAlreadyGathered = disciplineTotalsAlreadyGathered + "~" + row["disciplineName"].ToString();

}

if (nreTable != null && doWeHaveNRE)

{

// Get NRE Data for Total Plan and Total Actual columns

DataRow[] nreSiteTotalsDataRow =

nreTable.Select("disciplineName <> '" + "NA" + "'" +

"AND disciplineName <> '" + "Work Packages Total" + "'");

foreach (DataRow row in nreSiteTotalsDataRow)

{

// Check to see if we already grabbed the totals for this discipline(NRE in this case).

if (!nreTotalsAlreadyGathered.Contains(row["disciplineName"].ToString()))

{

var totalPlanValue = String.Format("{0:N}", row["totalPlanValue"]);

var totalActValue = String.Format("{0:N}", row["totalActValue"]);

totalNreTableTotalPlanValue += Convert.ToDouble(totalPlanValue);

totalNreTableTotalActValue += Convert.ToDouble(totalActValue);

}

nreTotalsAlreadyGathered = nreTotalsAlreadyGathered + "~" + row["disciplineName"];

}

// Store in session varialbe; Use this for Excel Export

//ReportTable = reportTable;

siteTotalTotalsForPlan = totalNreTableTotalPlanValue + totalNonNreTableTotalPlanValue;

siteTotalTotalsForActual = totalNreTableTotalActValue + totalNonNreTableTotalActValue;

totalPlanColumnIndex = reportTable.Columns.IndexOf("Total Plan");

totalActualColumnIndex = reportTable.Columns.IndexOf("Total Actual");

totalPlanConformanceIndex = reportTable.Columns.IndexOf("Plan Conformance");

workPackagesTotalRowIndex = ReportRows["Work Packages Total" + "-" + siteID];

totalDevelopmentCostsRowIndex = ReportRows["Total Development Costs" + "-" + siteID];

planConformanceForWorkPackageTotalRow = totalNonNreTableTotalActValue / totalNonNreTableTotalPlanValue;

// Set report totals for "Work Packages Total" row

reportTable.Rows[workPackagesTotalRowIndex][totalPlanColumnIndex] = FormatValue(totalNonNreTableTotalPlanValue, true);

reportTable.Rows[workPackagesTotalRowIndex][totalActualColumnIndex] = FormatValue(totalNonNreTableTotalActValue, true);

reportTable.Rows[workPackagesTotalRowIndex][totalPlanConformanceIndex] = FormatPercent(String.Format("{0:P}", planConformanceForWorkPackageTotalRow), true);

var planConformanceForTotalDevCostsRow = siteTotalTotalsForActual/siteTotalTotalsForPlan;

// Set report totals for "Total Development Costs" row

reportTable.Rows[totalDevelopmentCostsRowIndex][totalPlanColumnIndex] = FormatValue(siteTotalTotalsForPlan, true);

reportTable.Rows[totalDevelopmentCostsRowIndex][totalActualColumnIndex] = FormatValue(siteTotalTotalsForActual, true);

reportTable.Rows[totalDevelopmentCostsRowIndex][totalPlanConformanceIndex] = FormatPercent(String.Format("{0:P}",planConformanceForTotalDevCostsRow),true);

FixEmptyValueRowsWithDashes(ref reportTable, siteID);

}

/// <summary>

/// Creates the report row.

/// </summary>

/// <param name="reportTable">The report table.</param>

/// <param name="text">The text.</param>

/// <returns></returns>

private int CreateReportRow(ref DataTable reportTable, string text)

{

string[] type = text.Split('-');

var row = reportTable.NewRow();

row[0] = type[0];

reportTable.Rows.Add(row);

var index = reportTable.Rows.Count - 1;

// add the row indexes.. and make sure it's not a blank row

if (text != "")

{

ReportRows.Add(text.Replace("<b>", "").Replace("</b>", ""), index);

}

return index;

}

/// <summary>

/// Creates the report table.

/// </summary>

/// <param name="startDate">The start date.</param>

/// <param name="endDate">The end date.</param>

/// <param name="costPcs">The cost PCS.</param>

/// <returns></returns>

protected DataTable CreateReportTable(DataTable dtProjects)

{

DataTable reportTable = new DataTable("ProjectTrackingReport");

try

{

// Get distinct Sites from returned dataTable

var distinctSiteIDs = dtProjects.AsEnumerable()

.Select(s => new

{

id = s.Field<int>("siteID"),

})

.Distinct().ToList();

// Store how many sites the report was run for

numberOfSitesForReport = distinctSiteIDs.Count;

// Get distinct Periods from returned dataTable

List<string> distinctPeriods = dtProjects.AsEnumerable()

.Select(r => r.Field<string>("period"))

.Distinct()

.ToList();

// Store member variable for use in PreRender

listDistinctPeriods = distinctPeriods;

reportTable.Columns.Add(" ", typeof (string));

var currentPeriod = string.Empty;

var index = 0;

if (distinctPeriods.Count > 0)

{

foreach (var item in distinctPeriods)

{

DataColumn tempPlanColumn = new DataColumn();

tempPlanColumn.DataType = System.Type.GetType("System.String");

DataColumn tempActualColumn = new DataColumn();

tempActualColumn.DataType = System.Type.GetType("System.String");

currentPeriod = string.Format("{1}", index, item);

// Create ColumnGroup for the currentPeriod

//GridColumnGroup group = new GridColumnGroup();

//radGrid1.MasterTableView.ColumnGroups.Add(group);

//group.HeaderText = group.Name = currentPeriod;

tempPlanColumn.Caption = "Plan";

tempPlanColumn.ColumnName = "Plan" + "-" + currentPeriod;

tempActualColumn.Caption = "Actual";

tempActualColumn.ColumnName = "Actual" + "-" + currentPeriod;

reportTable.Columns.Add(tempPlanColumn);

reportTable.Columns.Add(tempActualColumn);

//reportTable.Columns.Add(string.Format("{1}", index, item.id));

index++;

}

reportTable.Columns.Add("Total Plan", typeof (string));

reportTable.Columns.Add("Total Actual", typeof (string));

reportTable.Columns.Add("Plan Conformance", typeof (string));

//// Create Fake Header - totally crappy coding

//rowSite = CreateReportRow(ref reportTable, "<b>Work Packages</b>");

// Create Project Row

rowSite = CreateReportRow(ref reportTable, "<b>Project: </b>" + radComboBoxProjects.SelectedItem.Text);

foreach (var item in distinctSiteIDs)

{

var site = item.id;

IEnumerable<DataRow> query = from myRow in dtProjects.AsEnumerable()

where myRow.Field<int>("siteID") == site

select myRow;

DataTable siteTable = query.CopyToDataTable<DataRow>();

// For each site generate appropriate rows

CreateReportRowsForASite(siteTable, site.ToString(), ref reportTable);

FirstSite = false;

index++;

}

return reportTable;

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Creating the report Table [" + ex.Message + "]";

lblError.Visible = true;

}

return null;

}

/// <summary>

/// Fixes the empty value rows with dashes.

/// </summary>

/// <param name="reportTable">The report table.</param>

private static void FixEmptyValueRowsWithDashes(ref DataTable reportTable, string siteID)

{

foreach (DataRow row in reportTable.Rows)

{

if (row[0].ToString() != String.Empty && row[0] != null && !row[0].ToString().Contains("Site:")

&& row[0].ToString() != "NRE" && row[0].ToString() != "<b>NRE</b>" && row[0].ToString() != "<b>Work Packages</b>"

&& !row[0].ToString().Contains("Project"))

{

foreach (DataColumn col in reportTable.Columns)

{

if (row[col] == null || row[col].ToString() == String.Empty)

{

row[col] = "0.00";

}

/// <summary>

/// Formats the value.

/// </summary>

/// <param name="value">The value.</param>

/// <param name="bold">if set to <c>true</c> [bold].</param>

/// <returns></returns>

private static string FormatValue(double value, bool bold)

{

//double currencyRatio = 0;

//if (double.TryParse(lblExchangeRatio.Text, out currencyRatio) == true)

//{

// value = value * currencyRatio;

//}

//else

//{

// currencyRatio = 1;

//}

value = Math.Round(value, 2);

if (value != 0)

{

if (bold)

return "<b>" + String.Format("{0:0,0.00;(0,0.00)}", value) + "</b>";

else

return String.Format("{0:0,0.00;(0,0.00)}", value);

}

else

{

if (bold)

return "<b>0.00</b>";

else

return "0.00";

}

/// <summary>

/// Formats the item.

/// </summary>

/// <param name="item">The item.</param>

private void FormatItem(GridDataItem item)

{

var i = 0;

foreach (TableCell tableCell in item.Cells)

{

i++;

var temp = 0.00;

if (tableCell.Text == "" || tableCell.Text == "0.00" || tableCell.Text == "0"

|| tableCell.Text == null || tableCell.Text == String.Empty)

{

tableCell.Text = temp.ToString(numberFormat);

}

var numStyles = NumberStyles.AllowLeadingSign | NumberStyles.AllowCurrencySymbol |

NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands |

NumberStyles.Currency | NumberStyles.AllowLeadingWhite |

NumberStyles.AllowTrailingWhite;

var amount = 0.00;

if (double.TryParse(tableCell.Text, numStyles, null, out amount))

{

tableCell.Text = ConvertValue(amount).ToString(numberFormat);

}

/// <summary>

/// Formats the percent.

/// </summary>

/// <param name="value">The value.</param>

/// <returns></returns>

private string FormatPercent(string value, bool boldText)

{

var temp = 0.00;

if (value == "" || value == "0.00" || value == "0" || value == " "

|| String.IsNullOrEmpty(value))

{

value = temp.ToString(NumberFormat);

}

// NaN is returned when we divide 0 by 0

else if (value == "NaN")

{

value = "";

value = temp.ToString(NumberFormat) + "%";

}

else if (value == "Infinity")

{

value = "N/A";

}

var numStyles = NumberStyles.AllowLeadingSign | NumberStyles.AllowCurrencySymbol |

NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands |

NumberStyles.Currency | NumberStyles.AllowLeadingWhite |

NumberStyles.AllowTrailingWhite;

if (value != "N/A")

{

var amount = 0.00;

if (double.TryParse(value, numStyles, null, out amount))

{

value = this.ConvertValue(amount).ToString(NumberFormat) + "%";

}

if (boldText)

{

return "<b>" + value + "</b>";

}

else

{

return value;

}

/// <summary>

/// Formats the percent.

/// </summary>

/// <param name="cell">The cell.</param>

private void FormatPercent(PivotGridDataCell cell)

{

var temp = 0.00;

if (cell.Text == "" || cell.Text == "0.00" || cell.Text == "0" || cell.Text == " "

|| String.IsNullOrEmpty(cell.Text))

{

cell.Text = temp.ToString(NumberFormat);

}

// NaN is returned when we divide 0 by 0

else if (cell.Text == "NaN")

{

cell.Text = "";

cell.Text = temp.ToString(NumberFormat) + "%";

}

else if (cell.Text == "Infinity")

{

cell.Text = "N/A";

}

var numStyles = NumberStyles.AllowLeadingSign | NumberStyles.AllowCurrencySymbol |

NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands |

NumberStyles.Currency | NumberStyles.AllowLeadingWhite |

NumberStyles.AllowTrailingWhite;

if (cell.Text != "N/A")

{

var amount = 0.00;

if (double.TryParse(cell.Text, numStyles, null, out amount))

{

cell.Text = this.ConvertValue(amount).ToString(NumberFormat) + "%";

}

/// <summary>

/// Gets the active project sectors.

/// </summary>

/// <param name="activeOnly">if set to <c>true</c> [active only].</param>

private void GetActiveProjectSectors(bool activeOnly)

{

ClearDropDowns(true, true, true, true, false);

// Insert default value into projects drop down

radComboBoxSectors.Items.Insert(0, new RadComboBoxItem("---- Select Sector ----", string.Empty));

try

{

// Load Sectors dropDown

var dtSectors = jpdb.ProjectSectors.GetActiveProjectSectors(false);

radComboBoxSectors.DataSource = dtSectors;

radComboBoxSectors.DataTextField = "sectorname";

radComboBoxSectors.DataValueField = "id";

radComboBoxSectors.DataBind();

if (Session["SectorId"] != null)

{

radComboBoxSectors.SelectedValue = Session["SectorId"].ToString();

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred getting the list of project sectors [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Gets the currency types.

/// </summary>

/// <param name="activeOnly">if set to <c>true</c> [active only].</param>

private void GetCurrencyTypes(bool activeOnly)

{

try

{

// Load currencies dropdown

var dtCurrencies =

jpdb.Currency.GetCurrencyTypes(activeOnly);

radComboBoxCurrencies.DataSource = dtCurrencies;

radComboBoxCurrencies.DataTextField = "currencytype";

radComboBoxCurrencies.DataValueField = "id";

radComboBoxCurrencies.DataBind();

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred getting the currency types [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Gets the localized value for grid display.

/// </summary>

/// <param name="value">The value.</param>

/// <param name="selectedCurrencyType">Type of the selected currency.</param>

/// <returns></returns>

private string GetLocalizedValueForGridDisplay(Double value, culture selectedCurrencyType)

{

try

{

var countryLocalizationName = "";

// This isn't perfect but we need to determine the culture off the currency type

if (Enum.IsDefined(typeof(culture), selectedCurrencyType))

countryLocalizationName = ((culture)selectedCurrencyType).ToString();

else

countryLocalizationName = "Invalid";

var cellValue = value;

// Get current systems localization name

// fr-FR = French (France)

// fr-CA = French (Canada)

// en-US = English (United States)

// de-DE = German (Germany)

// NOTE!!!!!!!!! for display purposes probably best to display local culture

var currentCulture = new CultureInfo(countryLocalizationName);

// Test code to test german formatting of the data

//var german = new CultureInfo("de-DE");

//currentCulture = german;

var resultString = cellValue.ToString("n", currentCulture);

return resultString;

}

catch (Exception ex)

{

// Need to add loggging

//log.Debug(ex);

lblError.Text = "The Following Error Occurred getting the localized data values [" + ex.Message + "]";

lblError.Visible = true;

return null;

}

/// <summary>

/// Gets the localized value for excel export.

/// </summary>

/// <param name="value">The value.</param>

/// <returns></returns>

private string GetLocalizedValueForExcelExport(Double value)

{

try

{

var cellValue = value;

// Get current systems localization name

// fr-FR = French (France)

// fr-CA = French (Canada)

// en-US = English (United States)

// de-DE = German (Germany)

var currentCulture = new CultureInfo(CultureInfo.CurrentCulture.Name);

// Test code to test german formatting of the data

//var german = new CultureInfo("de-DE");

//currentCulture = german;

var resultString = cellValue.ToString("n", currentCulture);

return resultString;

}

catch (Exception ex)

{

// Need to add loggging

//log.Debug(ex);

lblError.Text = "The Following Error Occurred getting the localized data values [" + ex.Message + "]";

lblError.Visible = true;

return null;

}

/// <summary>

/// Gets the customers in project sector for current date.

/// </summary>

/// <param name="sectorId">The sector identifier.</param>

private void GetCustomersInProjectSectorForCurrentDate(int sectorId)

{

try

{

// Load Customers dropdown for the sector that is selected

var dtCustomers =

jpdb.ProjectSectors.GetCustomersInProjectSectorForCurrentDate(Convert.ToInt32(sectorId));

radComboBoxCustomers.DataSource = dtCustomers;

radComboBoxCustomers.DataTextField = "name";

radComboBoxCustomers.DataValueField = "id";

radComboBoxCustomers.DataBind();

if ((Session["CustomerId"] != null))

{

radComboBoxCustomers.SelectedValue = Session["CustomerId"].ToString();

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred getting the customer list [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Gets the project date range.

/// </summary>

/// <param name="projectId">The project identifier.</param>

private void GetProjectDateRange(int projectId)

{

try

{

var projectStartAndEndDates = jpdb.Projects.GetEVMReportStartingAndEndingDatesForProject(projectId);

if (projectStartAndEndDates.Rows.Count == 0)

{

// no dates

StartDate = Convert.ToDateTime(DateTime.Now.ToString());

EndDate = Convert.ToDateTime(DateTime.Now.ToString());

}

else

{

StartDate = Convert.ToDateTime(projectStartAndEndDates.Rows[0]["firstdate"].ToString());

EndDate = Convert.ToDateTime(projectStartAndEndDates.Rows[0]["lastdate"].ToString());

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Getting the project start and end dates [" + ex.Message +

"]";

lblError.Visible = true;

}

/// <summary>

/// Gets the projects for customer.

/// </summary>

/// <param name="customerId">The customer identifier.</param>

/// <param name="isActive">if set to <c>true</c> [is active].</param>

private void GetProjectsForCustomer(int customerId, bool isActive)

{

// Clear comboBox to ensure correct items in the collection

radComboBoxProjects.Items.Clear();

// Insert default value into projects drop down

radComboBoxProjects.Items.Insert(0, new RadComboBoxItem("---- Select Project ----", string.Empty));

// Load projects for the customer selected

var dtProjects =

jpdb.Customers.GetProjectsForCustomer(customerId, false);

radComboBoxProjects.DataSource = dtProjects;

radComboBoxProjects.DataTextField = "projname";

radComboBoxProjects.DataValueField = "id";

radComboBoxProjects.DataBind();

if (!String.IsNullOrEmpty(projectIDTextBox.Text))

{

radComboBoxProjects.SelectedValue = projectIDTextBox.Text;

// Insert default value into projects drop down

radComboBoxSites.Items.Insert(0, new RadComboBoxItem("---- Select Site ----", string.Empty));

// Get the sites for drop down

GetProjectTeamsForProject(Convert.ToInt32(projectIDTextBox.Text));

}

/// <summary>

/// Gets the project teams for project.

/// </summary>

/// <param name="projectId">The project identifier.</param>

private void GetProjectTeamsForProject(int projectId)

{

// Clear comboBox to ensure correct items in the collection

radComboBoxSites.Items.Clear();

// Insert default value into customers drop down

radComboBoxSites.Items.Insert(0, new RadComboBoxItem("---- Select Sites ----", string.Empty));

try

{

// Load Sites for the specific project selected

var dtSites =

jpdb.ProjectTeams.GetProjectTeamsForProject(projectId);

// insert entry for all

var dr = dtSites.NewRow();

dr["teamname"] = "All Sites";

dr["site"] = -1;

dtSites.Rows.InsertAt(dr, 0);

radComboBoxSites.DataSource = dtSites;

radComboBoxSites.DataTextField = "teamname";

radComboBoxSites.DataValueField = "site";

radComboBoxSites.DataBind();

if (radComboBoxSites.Items.Count > 0)

{

// get the person's site

int site = jpdb.Employees.GetCurrentFinanceSiteForEmployee(SecurityControl1.GetCurrentUserID());

radComboBoxSites.SelectedValue = site.ToString();

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Creating The Report [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Determines whether [is local currency selected].

/// </summary>

/// <returns></returns>

private bool isLocalCurrencySelected()

{

if (Session["dtCurrenciesPBR"] != null)

{

DataTable dtCurrencies = (DataTable)Session["dtCurrenciesPBR"];

if (dtCurrencies.Rows.Count > 0)

{

int outputCurrencyID = int.Parse(radComboBoxCurrencies.SelectedValue);

DataRow[] rows = dtCurrencies.Select("outputCurrency='" + outputCurrencyID + "'");

if (rows.Length > 0)

{

int inputCurrencyID = int.Parse(rows[0]["inputCurrency"].ToString());

return inputCurrencyID == outputCurrencyID;

}

else

{

return false;

}

else

{

return false;

}

else

{

return false;

}

/// <summary>

/// Populate dropDowns

/// </summary>

private void PopulateDropdowns()

{

try

{

// If queryString parameters are populated populate all dropdowns from QueryString Params

if (Session["SectorId"] != null)

{

// Get all sectors

GetActiveProjectSectors(false);

// Get Customers

GetCustomersInProjectSectorForCurrentDate(Convert.ToInt32(Session["SectorId"]));

// Get Projects

GetProjectsForCustomer(Convert.ToInt32(Session["CustomerId"]), false);

// Load Currency Types (always loaded)

GetCurrencyTypes(true);

}

else // No QueryString parameters exist, load Sectors & Currencies only

{

// Get all sectors - the rest of the dropdowns get loaded sequentially when the user selects each successive / needed

// item. Loading occurs on the SelectedIndexChanged event.

GetActiveProjectSectors(false);

// Get Currencies

GetCurrencyTypes(true);

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Creating The Report [" + ex.Message + "]";

lblError.Visible = true;

}

/// <summary>

/// Populates the grid.

/// </summary>

/// <returns></returns>

protected DataTable PopulateTheGrid()

{

//TODO: Add error handling to this section

// Gets the starting and ending date range of the project

GetProjectDateRange(Convert.ToInt32(radComboBoxProjects.SelectedValue));

var startingMonth = StartDate.Month;

var startingYear = StartDate.Year;

var endingMonth = EndDate.Month;

var endingYear = EndDate.Year;

// Holds the data for this report

var dtProjects = new DataTable();

DataTable reportTable = new DataTable();

DataTable dtSelectedSites = new DataTable("SelectedSiteTable");

dtSelectedSites.Columns.Add("id");

var isFirstPass = true;

List<SqlParameter> parameters = new List<SqlParameter>();

parameters.Add(new SqlParameter("@site", radComboBoxSites.SelectedValue));

parameters.Add(new SqlParameter("@projectID", radComboBoxProjects.SelectedValue));

parameters.Add(new SqlParameter("@currency", radComboBoxCurrencies.SelectedValue));

parameters.Add(new SqlParameter("@startingMonth", startingMonth));

parameters.Add(new SqlParameter("@startingYear", startingYear));

parameters.Add(new SqlParameter("@endingMonth", endingMonth));

parameters.Add(new SqlParameter("@endingYear", endingYear));

parameters.Add(new SqlParameter("@exportToXML", false));

try

{

dtProjects = JPDB3.GetDataTable(parameters, "Projects_GetProjectTrackingData",

"Capacity", "Error Getting Project Tracking Data:", CommandType.StoredProcedure, 6000);

// Create report table

return CreateReportTable(dtProjects);

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Creating The Report [" + ex.Message + "]";

lblError.Visible = true;

return null;

}

//var firstSite = distinctSiteIDs[0].id;

//rowSite = CreateReportRow(ref reportTable, "Site: " + firstSite);

// THis doesn't work?? damn you M$

//// Fix column names

//DataGridView dataGridView = new DataGridView();

//dataGridView.DataSource = reportTable;

//foreach (DataGridViewColumn col in dataGridView.Columns)

//{

// col.HeaderText = reportTable.Columns[col.HeaderText].Caption;

//}

//DataRow[] result = dtProjects.Select("site = ")

}

#endregion

#region Properties

// usage: var cultureDescription = GetDescriptionFromEnumValue(culture.de_DE);

public static string GetDescriptionFromEnumValue(Enum value)

{

DescriptionAttribute attribute = value.GetType()

.GetField(value.ToString())

.GetCustomAttributes(typeof(DescriptionAttribute), false)

.SingleOrDefault() as DescriptionAttribute;

return attribute == null ? value.ToString() : attribute.Description;

}

// usage var whatCulture = GetEnumValueFromDescription<culture>("English");

public static T GetEnumValueFromDescription<T>(string description)

{

var type = typeof(T);

if (!type.IsEnum)

throw new ArgumentException();

FieldInfo[] fields = type.GetFields();

var field = fields

.SelectMany(f => f.GetCustomAttributes(

typeof(DescriptionAttribute), false), (

f, a) => new { Field = f, Att = a })

.Where(a => ((DescriptionAttribute)a.Att)

.Description == description).SingleOrDefault();

return field == null ? default(T) : (T)field.Field.GetRawConstantValue();

}

public static DataTable ReportTable

{

get

{

var value = HttpContext.Current.Session["ReportTable"];

return value == null ? null : (DataTable) value;

}

set

{

HttpContext.Current.Session["ReportTable"] = value;

}

public List<string> listDistinctPeriods = new List<string>();

public DateTime StartDate { get; set; }

public DateTime EndDate { get; set; }

#endregion

#region Fields

private culture selectedCurrencyCulture;

private int numberOfSitesForReport;

private static string numberFormat = "#,##0.00;(#,##0.00)";

private int rowSite;

private Dictionary<string, int> ReportRows = new Dictionary<string, int>();

private readonly JPDB3 jpdb = new JPDB3();

private static string NumberFormat = "#,##0.00;(#,##0.00)";

// First load of page? Report button not clicked yet?

// If so do not attempt to build the grid within the OnNeedDataSource event

public bool FirstPass = true;

public bool FirstSite = true;

private bool workPackagesTotalRow = false;

private double totalNonNreTableTotalPlanValue = 0.00;

private double totalNonNreTableTotalActValue = 0.00;

private double totalNreTableTotalActValue = 0.00;

private double totalNreTableTotalPlanValue = 0.00;

private int totalPlanColumnIndex = -1;

private int totalActualColumnIndex = -1;

private double siteTotalTotalsForPlan = 0.00;

private double siteTotalTotalsForActual = 0.00;

private int totalPlanConformanceIndex = -1;

private int totalDevelopmentCostsRowIndex = -1;

private int workPackagesTotalRowIndex = -1;

private double planConformanceForWorkPackageTotalRow = 0.00;

private string disciplineTotalsAlreadyGathered = string.Empty;

private string nreTotalsAlreadyGathered = string.Empty;

#endregion

}

.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/JabilProjectWithMenu.Master" AutoEventWireup="True" CodeBehind="ProjectTrackingReport.aspx.cs"

Inherits="JabilProject.Reports.ProjectReports.ProjectTrackingReport" %>

<%@ Register Src="../../Security/SecurityControl.ascx" TagName="SecurityControl"

TagPrefix="uc1" %>

<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" runat="server">

<script type="text/javascript">

function onRequestStart(sender, args) {

if (args.get_eventTarget().indexOf("btnExport") >= 0 ||

args.get_eventTarget().indexOf("excelExportButton") >= 0 ||

args.get_eventTarget().indexOf("ExportToExcelButton") >= 0 ||

args.get_eventTarget().indexOf("ExportToWordButton") >= 0 ||

args.get_eventTarget().indexOf("ExportToCsvButton") >= 0) {

args.set_enableAjax(false);

}

</script>

<%# Server.ScriptTimeout = 240 %>

<style type="text/css">

.UpdateLabel { white-space: nowrap; }

</style>

<uc1:SecurityControl ID="SecurityControl1" runat="server"/>

<telerik:RadAjaxLoadingPanel ID="AjaxLoadingPanel1" runat="server" BackgroundPosition="Top"

Skin="Default">

</telerik:RadAjaxLoadingPanel>

<telerik:RadAjaxPanel runat="server" ID="RadAjaxPanel1" Width="100%" LoadingPanelID="AjaxLoadingPanel1" ClientEvents-OnRequestStart="onRequestStart">

<br/>

<div style="text-align: left; margin-left: 250px;">

<asp:Label ID="Label1" runat="server" CssClass="pageLabel">Project Tracking Report</asp:Label>

</div>

<br/>

<table class="jpTable" style="width: 200px;">

<tr>

<td><asp:TextBox ID="projectIDTextBox" runat="server" Width="75"></asp:TextBox></td>

<td><asp:Button ID="findProjectButton" OnClick="findProjectButton_OnClick" runat="server" Text="Find Project by ID" /></td>

</tr>

</table>

<br/>

<table class="jpTable" style="width: 800px;">

<tr>

<td>Sectors:</td>

<td>

<telerik:RadComboBox ID="radComboBoxSectors" runat="server" Width="300" AutoPostBack="true"

onselectedindexchanged="radComboBoxSectors_SelectedIndexChanged"

OnDataBound="radComboBoxSectors_OnDataBound">

<CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>

</telerik:RadComboBox>

</td>

</tr>

<tr>

<td>Customers:</td>

<td>

<telerik:RadComboBox ID="radComboBoxCustomers" runat="server" Width="300" AutoPostBack="true"

onselectedindexchanged="radComboBoxCustomers_SelectedIndexChanged"

OnDataBound="radComboBoxCustomers_OnDataBound">

</telerik:RadComboBox>

</td>

</tr>

<tr>

<td>Projects:</td>

<td>

<telerik:RadComboBox ID="radComboBoxProjects" runat="server" Width="300" AutoPostBack="true"

OnSelectedIndexChanged="radComboBoxProjects_OnSelectedIndexChanged"

OnDataBound="radComboBoxProjects_OnDataBound">

</telerik:RadComboBox>

</td>

</tr>

<tr>

<td>Sites:</td>

<td>

<telerik:RadComboBox ID="radComboBoxSites" runat="server" Width="300" AutoPostBack="true"

OnDataBound="radComboBoxSites_OnDataBound"

OnSelectedIndexChanged="radComboBoxSites_OnSelectedIndexChanged">

</telerik:RadComboBox>

</td>

</tr>

<tr>

<td>Currency:</td>

<td>

<telerik:RadComboBox ID="radComboBoxCurrencies" runat="server" Width="300" AutoPostBack="true"

OnSelectedIndexChanged="radComboBoxCurrencies_OnSelectedIndexChanged">

</telerik:RadComboBox>

</td>

</tr>

<tr>

<td></td>

</tr>

<tr>

<td>

<asp:Button ID="runReportButton" runat="server" OnClick="runReportButton_OnClick"

Text="Run Report"/>

<asp:Label ID="lblError" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>

</td>

<%--<td>

<div style="float: right">

<telerik:RadButton runat="server" AutoPostBack="true" ID="excelExportButton" Text="Export to Excel" Visible="true" OnClick="excelExportButton_Click" >

<Icon PrimaryIconUrl="~/Images/Excel-icon.png"/>

</telerik:RadButton>

</div>

</td>--%>

</tr>

</table>

<br/>

<telerik:RadGrid ID="radGrid1" runat="server"

GridLines="Vertical" Skin="Telerik" Width="1200" Height="650"

AllowSorting="false" ShowGroupPanel="true" ShowFooter="true"

OnColumnCreated="radGrid1_OnColumnCreated"

OnDataBound="radGrid1_OnDataBound"

OnExportCellFormatting="radGrid1_OnExportCellFormatting"

OnItemDataBound="radGrid1_OnItemDataBound"

OnNeedDatasource="radGrid1_OnNeedDataSource"

OnPreRender="radGrid1_OnPreRender">

<ClientSettings AllowDragToGroup="false">

<Scrolling AllowScroll="true" FrozenColumnsCount="1" SaveScrollPosition="true" UseStaticHeaders="true" />

</ClientSettings>

<ExportSettings Excel-Format="Html" ExportOnlyData="true" Excel-FileExtension="xls" ></ExportSettings>

<MasterTableView CommandItemDisplay="Top" ShowGroupFooter="false">

<CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false" ShowRefreshButton="False"/>

<HeaderStyle Font-Bold="true" Width="100" />

<Columns/>

</MasterTableView>

<ClientSettings AllowDragToGroup="false" AllowColumnsReorder="true" />

<GroupingSettings />

</telerik:RadGrid>

<br/><br/>

</telerik:RadAjaxPanel>

</asp:Content>

answered on 02 Jun 2015, 08:25 PM

Additional information:

I modified the OnNeedDataSource code. This was needed as page refreshes were not working correctly if the user manually refreshed the page. Or if the page encountered an error and was re-run (essentially re-building the grid).

protected void radGrid1_OnNeedDataSource(object sender, GridNeedDataSourceEventArgs e)

{

try

{

//Session.Remove("reportTable");

if (Session["reportTable"] != null)

{

var dt = (DataTable)Session["reportTable"];

(sender as RadGrid).DataSource = dt;

}

catch (Exception ex)

{

lblError.Text = "The Following Error Occurred Creating The Report [" + ex.Message + "]";

lblError.Visible = true;

}

This seemed to resolve page refresh issues.

Looking at my Grid what I am seeing is that i have 39 Periods for my report. That means there will be 78 columns in total. Each distinct period has a paired "Plan" and "Actual" column. What I'm getting in the resultant table is 156 columns. The first 78 are correctly rendered group'd with the correct period they should be with and the next 78 have no Column Group Header. The text is empty. So for some reason the grid is rendering a second set of columns.

If I export the grid to excel the file looks absolutely perfect. 78 columns and everything looks great. Maybe this additional info would be helpful.

Thanks,

answered on 04 Jun 2015, 04:30 PM

Hello Julian,

I examined the code but apart from the simple data-binding approach which you have corrected in the last post I don't see anything wrong. Truth is that it would be very hard to debug this code even if I had it runnable and attached to our source code.
Here are some general suggestions which may apply to your scenario:
- always use advanced data-binding with NeedDataSource and avoid calling DataBind in all cases
- if you need to refresh the control after adding new data use the Rebind method
- if you feel that there may be a ViewState problem try adding/creating the corresponding structure on an earlier event

I hope this helps.

Regards,
Daniel
Telerik