//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 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); } }