//TODO: BuildSeries2 does the series building for a non bubble chart. Refactor so that its modular for all chart types.
private IEnumerable <VfChartSeries> BuildSeriesArrayForBubbleChart()
{
var aggregate = EpmChartAggregateType.Sum;
var hasZAxis = !PropChartZaxisField.Contains("None Selected");
var hasZAxisColor = !PropBubbleChartColorField.Contains("None Selected");
var zAxisFieldName = hasZAxis ? PropChartZaxisField : "";
var bubbleChartColumnMapping = GetBubbleChartColumnMappings();
var currentList = SPContext.Current.Web.Lists[PropChartSelectedList];
SPField xAxisField = null;
SPField yAxisField = null;
SPField zAxisField = null;
if (currentList != null)
{
xAxisField = currentList.Fields.GetFieldByInternalName(PropChartXaxisField);
yAxisField = currentList.Fields.GetFieldByInternalName(PropChartYaxisField);
zAxisField = currentList.Fields.GetFieldByInternalName(PropChartZaxisField);
}
var epmChartDataSeriesList = new EpmChartDataSeriesList(dtSPSiteDataQueryData, aggregate, xAxisField, yAxisField, zAxisField, hasZAxis, hasZAxisColor, bubbleChartColumnMapping);
return(EpmChartSeriesToVisifireChartSeriesMapper.GetVisifireChartSeries(epmChartDataSeriesList));
}
private string GetZaxisFieldValue(string sListName, SPField zAxisField, int zAxisFieldIndex, DataRow dr)
{
var zAxisFieldValue = "";
if (!String.IsNullOrEmpty(PropChartZaxisField.Trim()) && !PropChartZaxisField.Contains("None Selected") &&
dr.ItemArray.Length > zAxisFieldIndex)
{
zAxisFieldValue = dr.ItemArray[zAxisFieldIndex].ToString();
if (zAxisField != null && zAxisField.InternalName.ToUpper() == "LIST")
{
zAxisFieldValue = sListName;
}
else if (zAxisFieldValue.Length > 0 && zAxisFieldValue != "No Value")
{
if (zAxisField != null)
{
if (zAxisFieldValue.Contains(";#"))
{
zAxisFieldValue = zAxisFieldValue.Substring(zAxisFieldValue.IndexOf(";#") + 2);
if (IsBubbleChart())
{
zAxisFieldValue = GetCleanNumberValue(zAxisFieldValue).ToString();
}
}
//zAxisFieldValue = zAxisField.GetFieldValueForEdit(zAxisFieldValue);
}
}
//zAxisFieldValue = GetCleanNumberValue(zAxisFieldValue).ToString();
}
return(zAxisFieldValue);
}
private BubbleChartAxisToColumnMapping GetBubbleChartColumnMappings()
{
//TODO: I know this is a disgusting algorithm. It was done in haste to accomodate the bubble chart needing 4 axis when all 4 may not be present in the chart data (i.e. several axis use the same column)
var mappings = new BubbleChartAxisToColumnMapping();
var numOfColumns = 1;
mappings.XaxisColumnIndex = 0;
if (PropChartYaxisField.ToLower().Trim() == PropChartXaxisField.ToLower().Trim())
{
mappings.YaxisColumnIndex = mappings.XaxisColumnIndex;
}
else
{
mappings.YaxisColumnIndex = 1;
numOfColumns++;
}
if (PropChartZaxisField.ToLower().Trim() == PropChartXaxisField.ToLower().Trim())
{
mappings.ZaxisColumnIndex = mappings.XaxisColumnIndex;
}
else if (PropChartZaxisField.ToLower().Trim() == PropChartYaxisField.ToLower().Trim())
{
mappings.ZaxisColumnIndex = mappings.YaxisColumnIndex;
}
else
{
mappings.ZaxisColumnIndex = numOfColumns;
numOfColumns++;
}
if (PropBubbleChartColorField.ToLower().Trim() == PropChartXaxisField.ToLower().Trim())
{
mappings.ZaxisColorColumnIndex = mappings.XaxisColumnIndex;
}
else if (PropBubbleChartColorField.ToLower().Trim() == PropChartYaxisField.ToLower().Trim())
{
mappings.ZaxisColorColumnIndex = mappings.YaxisColumnIndex;
}
else if (PropBubbleChartColorField.ToLower().Trim() == PropChartZaxisField.ToLower().Trim())
{
mappings.ZaxisColorColumnIndex = mappings.ZaxisColumnIndex;
}
else
{
mappings.ZaxisColorColumnIndex = numOfColumns;
}
return(mappings);
}
private void AddZAxisRowToChartDataTable(int xAxisColumnIndex, SPField xAxisField)
{
if ((String.IsNullOrEmpty(PropChartZaxisField.Trim()) || PropChartZaxisField.Contains("None Selected")) &&
PropChartAggregationType.ToUpper() == "COUNT")
{
if (xAxisField.InternalName.ToUpper() == "LIST")
{
if (!SeriesExistsInChartDataTable(xAxisField.Title))
{
AddNewRowToChartDataTable(xAxisField.Title);
}
}
else
{
AddNewRowToChartDataTable(dtSPSiteDataQueryData.Columns[xAxisColumnIndex].ColumnName);
}
_numberOfRowsInChartDataTable++;
}
}
private void ConvertEmptySiteQueryDataToNoValue()
{
try
{
for (int x = 0; x < dtSPSiteDataQueryData.Rows.Count; x++)
{
if (String.IsNullOrEmpty(dtSPSiteDataQueryData.Rows[x][3].ToString().Trim()))
{
dtSPSiteDataQueryData.Rows[x][3] = "No Value";
}
if (!PropChartZaxisField.Contains("None Selected") &&
String.IsNullOrEmpty(dtSPSiteDataQueryData.Rows[x][4].ToString().Trim()))
{
dtSPSiteDataQueryData.Rows[x][4] = "No Value";
}
}
}
catch (Exception ex)
{
HandleException(ex);
}
}
public void DataBindChart()
{
LoadChartDataUsingSPSiteDataQuery();
TraceHeader();
try
{
if (_numberOfColumnsInChartDataTable > 0 && _numberOfRowsInChartDataTable > 0) // has data points
{
if (IsBubbleChart())
{
Series.AddRange(BuildSeriesArrayForBubbleChart());
}
else
{
int iCol = 0;
var x = new String[_numberOfColumnsInChartDataTable];
foreach (DataColumn col in ChartDataTable.Columns)
{
if (col.ColumnName != "SeriesName")
{
x[iCol] = col.ColumnName;
iCol++;
}
}
DataView v = ChartDataTable.DefaultView;
v.Sort = "[" + ChartDataTable.Columns[0].ColumnName + "] desc";
ChartDataTable = v.ToTable();
switch (PropChartAggregationType.ToUpper())
{
case "COUNT":
if ((PropChartZaxisField.Trim() == "" || PropChartZaxisField.Contains("None Selected")) &&
_numberOfRowsInChartDataTable > 1)
{
foreach (DataRow dr in ChartDataTable.Rows)
{
AddChartSeries(dr["SeriesName"].ToString(), PropChartAggregationType, ChartDataTable.Rows.Count, 0, false, false);
}
}
else
{
foreach (DataRow dr in ChartDataTable.Rows)
{
AddChartSeries(dr["SeriesName"].ToString(), PropChartAggregationType, ChartDataTable.Rows.Count, 0, false, false);
}
}
break;
case "SUM":
case "AVG":
if ((String.IsNullOrEmpty(PropChartZaxisField.Trim()) ||
PropChartZaxisField.Contains("None Selected")) && _numberOfRowsInChartDataTable > 1)
{
foreach (DataRow dr in ChartDataTable.Rows)
{
AddChartSeries(dr["SeriesName"].ToString(), PropChartAggregationType, ChartDataTable.Rows.Count, 0, false, true);
}
}
else
{
foreach (DataRow dr in ChartDataTable.Rows)
{
AddChartSeries(dr["SeriesName"].ToString(), PropChartAggregationType, ChartDataTable.Rows.Count, 0, false, true);
}
}
break;
default:
break;
}
}
}
else // no data points - show msg
{
PropChartTitle = "There are no results to show in this chart.";
}
}
catch (Exception ex)
{
HandleException(ex);
}
}
private void SetGraphData(int idx, DataRow dataRow, string zAxisFieldValue, string xAxisFieldValue)
{
var fieldName = dtSPSiteDataQueryData.Columns[idx].ColumnName;
var yAxis = oTopList.Fields.GetFieldByInternalName(fieldName);
var seriesName = yAxis.Title;
var yAxisFieldValue = dataRow.ItemArray[idx].ToString();
if (yAxisFieldValue.Length > 0)
{
if (yAxisFieldValue.Contains(";#"))
{
yAxisFieldValue = GetCleanNumberValue(yAxisFieldValue.Substring(yAxisFieldValue.IndexOf(";#") + 2))
.ToString();
}
if (string.Equals(
GetFieldSchemaAttribValue(yAxis.SchemaXml, "Percentage"),
bool.TrueString,
StringComparison.OrdinalIgnoreCase))
{
try
{
var flTempVal = double.Parse(yAxisFieldValue, providerEn);
flTempVal = flTempVal * 100;
yAxisFieldValue = flTempVal.ToString();
}
catch (Exception ex)
{
HandleException(ex);
}
}
}
if (PropChartZaxisField.Trim().Length != 0 && !PropChartZaxisField.Contains("None Selected"))
{
if (zAxisFieldValue.Trim().Length == 0)
{
zAxisFieldValue = "No Value";
}
if (SeriesExistsInChartDataTable(zAxisFieldValue))
{
UpdateCellValue(zAxisFieldValue, xAxisFieldValue, yAxisFieldValue, PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable(zAxisFieldValue);
_numberOfRowsInChartDataTable++;
UpdateCellValue(zAxisFieldValue, xAxisFieldValue, yAxisFieldValue, PropChartAggregationType);
}
}
else
{
if (SeriesExistsInChartDataTable(seriesName))
{
UpdateCellValue(seriesName, xAxisFieldValue, yAxisFieldValue, PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable(seriesName);
_numberOfRowsInChartDataTable++;
UpdateCellValue(seriesName, xAxisFieldValue, yAxisFieldValue, PropChartAggregationType);
}
}
}
private void ProcessSiteQueryData(string sListName)
{
const int xAxisColumnIndex = 3;
const int zAxisFieldIndex = 4;
try
{
var xAxisField = oTopList.Fields.GetFieldByInternalName(PropChartXaxisField);
SPField zAxisField = null;
if (!PropChartZaxisField.Contains("None Selected"))
{
zAxisField = oTopList.Fields.GetFieldByInternalName(PropChartZaxisField);
}
AddZAxisRowToChartDataTable(xAxisColumnIndex, xAxisField);
foreach (DataRow dr in dtSPSiteDataQueryData.Rows)
{
var xAxisFieldValue = GetXaxisFieldValue(sListName, xAxisField, xAxisColumnIndex, dr);
var zAxisFieldValue = GetZaxisFieldValue(sListName, zAxisField, zAxisFieldIndex, dr);
var sYaxisFldVal = "";
AddXAxisColumnToChartDataTableIfDoesntExist(xAxisFieldValue);
switch (PropChartAggregationType.ToUpper())
{
case "COUNT":
if (PropChartZaxisField.Trim().Length != 0 && !PropChartZaxisField.Contains("None Selected"))
{
if (zAxisFieldValue.Trim() == "")
{
zAxisFieldValue = "No Value";
}
if (SeriesExistsInChartDataTable(zAxisFieldValue))
{
UpdateCellValue(zAxisFieldValue, xAxisFieldValue, zAxisFieldValue, PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable(zAxisFieldValue);
_numberOfRowsInChartDataTable++;
UpdateCellValue(zAxisFieldValue, xAxisFieldValue, zAxisFieldValue, PropChartAggregationType);
}
}
else
{
if (xAxisField.InternalName.ToUpper() == "LIST")
{
UpdateCellValue(xAxisField.Title, xAxisFieldValue, "1", "COUNT");
}
else
{
UpdateCellValue(dtSPSiteDataQueryData.Columns[xAxisColumnIndex].ColumnName, xAxisFieldValue, "1", "COUNT");
}
_numberOfRowsInChartDataTable++;
}
break;
case "SUM":
case "AVG":
if (PropChartAggregationType.ToUpper() != "SUM" && (PropChartZaxisField.Trim() != "" && !PropChartZaxisField.Contains("None Selected")))
{
if (zAxisFieldValue.Trim().Length == 0)
{
zAxisFieldValue = "No Value";
}
if (GetYFields().Length > 0)
{
if (SeriesExistsInChartDataTable(zAxisFieldValue))
{
UpdateCellValue(zAxisFieldValue, xAxisFieldValue, sYaxisFldVal,
PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable(zAxisFieldValue);
_numberOfRowsInChartDataTable++;
UpdateCellValue(zAxisFieldValue, xAxisFieldValue, sYaxisFldVal,
PropChartAggregationType);
}
}
else
{
if (dtSPSiteDataQueryData.Columns.Count > 5)
{
if (
SeriesExistsInChartDataTable(zAxisField.GetFieldValueAsText(dtSPSiteDataQueryData.Columns[5].ColumnName)))
{
UpdateCellValue(dtSPSiteDataQueryData.Columns[5].ColumnName, xAxisFieldValue,
zAxisFieldValue, PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable(dtSPSiteDataQueryData.Columns[5].ColumnName);
_numberOfRowsInChartDataTable++;
UpdateCellValue(dtSPSiteDataQueryData.Columns[5].ColumnName, xAxisFieldValue,
zAxisFieldValue, PropChartAggregationType);
}
}
else
{
if (dtSPSiteDataQueryData.Columns[zAxisFieldIndex].ColumnName.ToUpper() == "LIST")
{
if (zAxisField != null)
{
if (SeriesExistsInChartDataTable(zAxisField.Title))
{
UpdateCellValue(zAxisField.Title, xAxisFieldValue, zAxisFieldValue,
PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable(zAxisField.Title);
_numberOfRowsInChartDataTable++;
UpdateCellValue(zAxisField.Title, xAxisFieldValue, zAxisFieldValue,
PropChartAggregationType);
}
}
else
{
if (SeriesExistsInChartDataTable("Work Type"))
{
UpdateCellValue("Work Type", xAxisFieldValue, zAxisFieldValue,
PropChartAggregationType);
_numberOfRowsInChartDataTable++;
}
else
{
AddNewRowToChartDataTable("Work Type");
_numberOfRowsInChartDataTable++;
UpdateCellValue("Work Type", xAxisFieldValue, zAxisFieldValue,
PropChartAggregationType);
}
}
}
}
}
}
if (GetYFields().Length > 0)
{
string sSeriesName;
int iStartCol;
if (!PropChartZaxisField.Contains("None Selected") && !IsBubbleChart())
{
iStartCol = 5;
}
else
{
iStartCol = 4;
}
SPField fldYaxis;
if (!IsBubbleChart())
{
for (int iSeries = iStartCol; iSeries < dtSPSiteDataQueryData.Columns.Count; iSeries++)
{
SetGraphData(iSeries, dr, zAxisFieldValue, xAxisFieldValue);
}
}
else
{
SetGraphData(iStartCol, dr, zAxisFieldValue, xAxisFieldValue);
}
}
break;
default:
break;
}
}
}
catch (Exception ex)
{
HandleException(ex);
}
}
/// <summary>
/// GetSiteDataUsingSPSiteDataQuery - RUN WITH ELEVATED PRIVILEDGES
/// </summary>
/// <param name="web"></param>
private void GetSiteDataUsingSPSiteDataQuery(SPWeb web)
{
SPSite s = SPContext.Current.Site;
SPWeb w = SPContext.Current.Web;
try
{
string dbCon =
s.ContentDatabase.DatabaseConnectionString;
var cn = new SqlConnection(dbCon);
cn.Open();
string siteurl = w.ServerRelativeUrl.Substring(1);
string[] rolluplists = PropChartRollupLists.ToArray();
//TODO: This little section is for when they didn't elect to have rollup lists. This will stick the one selected list into the array to use. Refactor this.
bool hasRollupLists = true;
var selectedList = w.Lists[PropChartSelectedList];
if (rolluplists[0] == "")
{
hasRollupLists = false;
rolluplists[0] = selectedList.Title;
}
string lists = "";
foreach (string list in rolluplists)
{
if (hasRollupLists)
{
string query = "";
if (siteurl == "")
{
query =
"SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE webs.siteid='" +
web.Site.ID +
"' AND (dbo.AllLists.tp_Title like '" +
list.Replace("'", "''") + "')";
}
else
{
query =
"SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '" +
siteurl + "/%' OR dbo.Webs.FullUrl = '" +
siteurl +
"') AND (dbo.AllLists.tp_Title like '" +
list.Replace("'", "''") + "')";
}
var cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@list", list);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lists += "<List ID='" + dr.GetGuid(0) + "'/>";
}
dr.Close();
}
else
{
lists = "<List ID='" + selectedList.ID + "'/>";
}
var dq = new SPSiteDataQuery();
dq.Lists = "<Lists>" + lists + "</Lists>";
dq.Query = oTopQuery.Query.Replace("<Query>", "").Replace("</Query>", ""); //get from current view
dq.Webs = "<Webs Scope='Recursive'/>";
string sYFields = "";
for (int iSeries = 0; iSeries < GetYFields().Length; iSeries++)
{
string sSeriesName = GetYFields()[iSeries];
sYFields += "<FieldRef Name='" + sSeriesName + "' Nullable='TRUE'/>";
}
if (IsBubbleChart())
{
dq.ViewFields = "<FieldRef Name='" + PropChartXaxisField + "' Nullable='TRUE'/>" +
sYFields +
"<FieldRef Name='" + PropChartZaxisField + "' Nullable='TRUE'/>" +
"<FieldRef Name='" + PropBubbleChartColorField + "' Nullable='TRUE'/>" +
"<FieldRef Name='Title' Nullable='TRUE'/>";
}
else
{
if (!PropChartZaxisField.Contains("None Selected"))
{
dq.ViewFields = "<FieldRef Name='" +
PropChartXaxisField +
"' Nullable='TRUE'/>" +
"<FieldRef Name='" +
PropChartZaxisField +
"' Nullable='TRUE'/>" +
sYFields;
}
else
{
dq.ViewFields = "<FieldRef Name='" + PropChartXaxisField + "' Nullable='TRUE'/>" +
sYFields;
}
}
dtSPSiteDataQueryData = new DataTable("SiteDataQueryData");
AppendLookupQueryIfApplicable(dq);
dtSPSiteDataQueryData = web.GetSiteData(dq);
ConvertEmptySiteQueryDataToNoValue();
ProcessSiteQueryData(list);
}
cn.Close();
}
catch (Exception ex)
{
HandleException(ex);
}
}
