368 lines
14 KiB
C#
368 lines
14 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using Microsoft.SharePoint;
|
|
using System.Collections;
|
|
|
|
namespace FusionCharts.WebParts
|
|
{
|
|
/// <summary>
|
|
/// Converts a SharePoint List (SPList) into a Plots dataset
|
|
/// </summary>
|
|
public class ListReader
|
|
{
|
|
#region PrivateMembers
|
|
private ITrace _tracer; // printf style debugging stream (into comment in page html) for developers
|
|
private string _listName; // The name of the SPList to transform
|
|
private string _viewName; // The name of the SPView to use
|
|
private SPList _list; //The SPList to transform
|
|
private SPView _view; // The SPView to use
|
|
private SPListItemCollection _items; // Items in the SPList
|
|
#endregion
|
|
|
|
#region Constructors
|
|
/// <summary>
|
|
/// Constructor
|
|
/// </summary>
|
|
/// <param name="list">The SharePoint list to tranform into XML for Fusion Charts</param>
|
|
/// <param name="view">The SharePoint view to use with the list</param>
|
|
public ListReader(ITrace tracer, string listName, string viewName)
|
|
{
|
|
// We initialize the private members
|
|
_tracer = tracer;
|
|
_listName = listName;
|
|
_viewName = viewName;
|
|
}
|
|
#endregion
|
|
|
|
#region PrivateMethods
|
|
|
|
/// <summary>
|
|
/// Retrieves a SPList from its name
|
|
/// </summary>
|
|
private void GetListAndView()
|
|
{
|
|
if (string.IsNullOrEmpty(_listName))
|
|
{
|
|
throw (new Exception("List name cannot be empty"));
|
|
}
|
|
// We open the SPList
|
|
Guid siteID = SPContext.Current.Site.ID;
|
|
Guid webID = SPContext.Current.Web.ID;
|
|
using (SPSite site = new SPSite(siteID))
|
|
{
|
|
using (SPWeb web = site.OpenWeb(webID))
|
|
{
|
|
// We get the list from its name
|
|
try
|
|
{
|
|
_list = web.Lists[_listName];
|
|
}
|
|
catch
|
|
{
|
|
throw (new Exception("Cannot find specified list name: " + _listName));
|
|
}
|
|
|
|
// We get the view from its name or get the default one
|
|
if (!string.IsNullOrEmpty(_viewName))
|
|
{
|
|
try
|
|
{
|
|
_view = _list.Views[_viewName];
|
|
}
|
|
catch
|
|
{
|
|
throw (new Exception("Cannot find specified view name: " + _viewName));
|
|
}
|
|
}
|
|
else
|
|
{
|
|
_view = _list.DefaultView;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// Check that requested fields are in list (or else throw exception)
|
|
/// </summary>
|
|
private void ValidateFields(string xFieldName, List<string> yFieldNames, GroupAction action)
|
|
{
|
|
ValidateFieldNameInList(xFieldName);
|
|
if (action == GroupAction.COUNT)
|
|
{
|
|
// does not use y field
|
|
return;
|
|
}
|
|
foreach (string fieldName in yFieldNames)
|
|
{
|
|
ValidateFieldNameInList(fieldName);
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// Check that specified nameis in list (or else throw exception)
|
|
/// </summary>
|
|
private void ValidateFieldNameInList(string fieldName)
|
|
{
|
|
if (!_list.Fields.ContainsField(fieldName))
|
|
{
|
|
string message = "The column \""
|
|
+ fieldName + "\" does not exist in the list \""
|
|
+ _list.Title + "\" or in the specified view \""
|
|
+ _view.Title + "\".<br /><br />Check the view you specified and make sure that the column exists.";
|
|
message += ListValidColumns();
|
|
throw (new Exception(message));
|
|
}
|
|
}
|
|
private string FieldRefNullable(string fieldname)
|
|
{
|
|
// fieldname should be internal name
|
|
string intname = fieldname; // passing in internal name
|
|
return String.Format("<FieldRef Name='{0}' Nullable='TRUE'/>", intname);
|
|
}
|
|
/// <summary>
|
|
/// Get the list of items in the list based on the view
|
|
/// </summary>
|
|
private void QueryList(string xFieldName, List<string> yFieldNames)
|
|
{
|
|
SPQuery query = new SPQuery(_view);
|
|
string viewFields = FieldRefNullable(xFieldName);
|
|
foreach (string fieldName in yFieldNames)
|
|
{
|
|
if (!string.IsNullOrEmpty(fieldName))
|
|
{
|
|
viewFields += FieldRefNullable(fieldName);
|
|
}
|
|
}
|
|
query.ViewFields = viewFields;
|
|
_items = _list.GetItems(query);
|
|
}
|
|
/// <summary>
|
|
/// Returns a list of valids column name that can be used.
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
private string ListValidColumns()
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("<br /><br />Valid columns are:<br />");
|
|
foreach (string field in _view.ViewFields)
|
|
{
|
|
sb.Append(" - " + field + "<br />");
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
private void TraceLine(string msg)
|
|
{
|
|
if (_tracer != null) _tracer.TraceLine(msg);
|
|
}
|
|
#endregion
|
|
|
|
#region PublicProperties
|
|
public SPList ParsedList { get { return _list; } }
|
|
public SPView ParsedView { get { return _view; } }
|
|
#endregion
|
|
|
|
#region PublicMethods
|
|
|
|
public void FindListView()
|
|
{
|
|
// We perform startup tasks
|
|
GetListAndView();
|
|
}
|
|
/// <summary>
|
|
/// Transforms the list of items into something easily usable (a Plots object).
|
|
/// Performs also operations such as SUM, COUNT over GROUPBY
|
|
/// </summary>
|
|
/// <param name="xFieldName">The name of the column to use as xValue</param>
|
|
/// <param name="yFieldNames">Nonempty list of column names for y values</param>
|
|
/// <returns></returns>
|
|
public Plots GetPlots(string xFieldName, List<string> yFieldNames, GroupAction action)
|
|
{
|
|
if (yFieldNames.Count == 0) throw new Exception("Empty yFieldNames list passed to GetPlots");
|
|
|
|
ValidateFields(xFieldName, yFieldNames, action);
|
|
QueryList(xFieldName, yFieldNames);
|
|
|
|
int nseries = yFieldNames.Count;
|
|
Plots plots = new Plots(nseries);
|
|
|
|
// We loop through our SPList and get all the x & y values
|
|
foreach (SPListItem item in _items)
|
|
{
|
|
// We get the X and Y values from the list via ItemValueReader
|
|
// which contains code to handle lookup fields, currency fields, and user fields
|
|
|
|
string x = ItemValueReader.GetItemValue(item, xFieldName);
|
|
if (x == null)
|
|
continue;
|
|
|
|
List<double> yvalues = new List<double>(nseries);
|
|
List<double> yctvalues = null; // only assign it if we need it (GroupAction.Average)
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
string fieldvalue = ItemValueReader.GetItemValue(item, yFieldNames[i]);
|
|
double y = Double.NaN;
|
|
if (!ReadNumericValue(fieldvalue, out y))
|
|
{
|
|
y = Double.NaN;
|
|
}
|
|
yvalues.Add(y);
|
|
}
|
|
|
|
// The value is already in our array
|
|
if (plots.Contains(x))
|
|
{
|
|
int pos = plots.XIndexOf(x);
|
|
|
|
if (action == GroupAction.NOTHING)
|
|
{
|
|
// Add new point at same x value
|
|
plots.Add(x, yvalues);
|
|
}
|
|
else
|
|
{
|
|
// modify existing point at x value
|
|
List<double> oldvalues = plots.GetY(pos);
|
|
|
|
if (action == GroupAction.COUNT)
|
|
{
|
|
// Count, we add +1 to the existing series point for each series point we have
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
if (newvalue != Double.NaN)
|
|
{
|
|
oldvalues[i] += 1;
|
|
}
|
|
}
|
|
}
|
|
else if (action == GroupAction.SUM)
|
|
{
|
|
// Sum, we add the value to the existing value
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
if (newvalue != Double.NaN)
|
|
{
|
|
oldvalues[i] += newvalue;
|
|
}
|
|
}
|
|
}
|
|
else if (action == GroupAction.AVERAGE)
|
|
{
|
|
// Sum yvalues, we add the value to the existing value
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
if (newvalue != Double.NaN)
|
|
{
|
|
oldvalues[i] += newvalue;
|
|
}
|
|
}
|
|
List<double> oldyctvalues = plots.GetYct(pos);
|
|
// Count them in yctvalues, we add +1 to the existing series point for each series point we have
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
if (newvalue != Double.NaN)
|
|
{
|
|
oldyctvalues[i] += 1;
|
|
}
|
|
}
|
|
}
|
|
// we modified the existing y data (oldvalues) in place
|
|
}
|
|
}
|
|
else // The value is a new one in our array
|
|
{
|
|
if (action == GroupAction.COUNT)
|
|
{
|
|
// Count, the first yValue is 1 (if we have it, else 0)
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
yvalues[i] = (newvalue != Double.NaN ? 1 : 0);
|
|
}
|
|
}
|
|
else if (action == GroupAction.SUM)
|
|
{
|
|
// Sum, the first yValue is y (if we have it, else 0)
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
yvalues[i] = (newvalue != Double.NaN ? newvalue : 0);
|
|
}
|
|
}
|
|
else if (action == GroupAction.AVERAGE)
|
|
{
|
|
yctvalues = new List<double>(nseries);
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
double newvalue = yvalues[i];
|
|
if (newvalue == Double.NaN)
|
|
{
|
|
yvalues[i] = 0;
|
|
yctvalues.Add(0);
|
|
}
|
|
else
|
|
{
|
|
yvalues[i] = newvalue;
|
|
yctvalues.Add(1);
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
// anything else than Count, we just add the plot
|
|
// which is already populated in yvalues
|
|
}
|
|
if (yctvalues != null)
|
|
{
|
|
plots.AddWithCounts(x, yvalues, yctvalues);
|
|
}
|
|
else
|
|
{
|
|
plots.Add(x, yvalues);
|
|
}
|
|
}
|
|
}
|
|
if (action == GroupAction.AVERAGE)
|
|
{
|
|
// Update yvalues in place - calculate arithmetic mean
|
|
for (int pos = 0; pos < plots.Count(); ++pos)
|
|
{
|
|
List<double> yvalues = plots.GetY(pos);
|
|
List<double> yctvalues = plots.GetYct(pos);
|
|
for (int i = 0; i < nseries; ++i)
|
|
{
|
|
if (yctvalues[i] > 0)
|
|
{
|
|
double newvalue = yvalues[i] / (double)yctvalues[i];
|
|
yvalues[i] = newvalue;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return plots;
|
|
}
|
|
/// <summary>
|
|
/// Parse numeric value from string, allowing for currency marks (e.g., $10,000)
|
|
/// </summary>
|
|
private bool ReadNumericValue(string value, out double numeric)
|
|
{
|
|
return Double.TryParse(value, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CurrentCulture.NumberFormat, out numeric);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
|
|
public enum GroupAction
|
|
{
|
|
NOTHING = 0,
|
|
SUM,
|
|
COUNT,
|
|
AVERAGE
|
|
}
|
|
|
|
}
|