I am making a custom report using Google Visualization API. It will have 6 sections with each section having tables on either side and a chart in the middle.
Since the formats differ slightly I was spending a lot of time defining classes for each one-off case.
I decided to try Google.DataTable.Net.Wrapper 3.1.0.0.
I created a stored procedure that returns a DataSet and then walk through the DataSet in my Controller and pass each table that I need.
The Data looks something like this
rownum charttypeid charttypename
----------- ----------- ------------------
1 1 Membership Sales
rownum chartareaid chartareaname
----------- ----------- -------------------------
1 1 Membership Sales Overview
2 2 Membership Sales Chart
title value display
------------------------- ----------- ----------
# of Walk-ins 25 25
# of Tours 17 17
# of New Members 35 35
Tour Conversion 78 78%
Percent to Goal 87 87%
Month value display goalvalue goaldisplay
----- ----------- ---------- ----------- -----------
Sep 3125 $3,125.00 1500 $1,500.00
Oct 4500 $4,500.00 1500 $1,500.00
Sometimes the charts will have money formats or other display formats, sometimes dates etc. I can't figure out how to add/modify the "f" part of the cell which provides a string format for display. My Controller code looks like this
[ResponseType(typeof(List<ChartPanel>))]
public IHttpActionResult GetChart(int gym, string dateCategory, string iso8601date, int id = -1)
{
if (!String.IsNullOrWhiteSpace(dateCategory))
{
dateCategory = dateCategory.ToLower();
string strConnString = ConfigurationManager.ConnectionStrings["PrimaryDBConnection"].ConnectionString;
// return DataSet From USP
DataSet dashBoardDataSet = GetDataSQL(strConnString, gym, dateCategory, iso8601date, 0);
if (dashBoardDataSet != null)
{
int chartPanelCount = dashBoardDataSet.Tables[0].Rows.Count;
List<ChartPanel> chartTypeList = new List<ChartPanel>(); // list for all the panels
// first table describes the Chart Panels
int tableCount = 0;
for (int chartPanelLoop = 0; chartPanelLoop < chartPanelCount; chartPanelLoop++)
{ // for every panel
tableCount++;
ChartPanel chartPanel = new ChartPanel();
chartPanel.name = dashBoardDataSet.Tables[0].Rows[chartPanelLoop][2].ToString();
// second table describes the following chart areas for the panel
int panelAreaCount = dashBoardDataSet.Tables[1].Rows.Count;
List<ChartArea> chartAreaList = new List<ChartArea>();
int areaTableCount = tableCount;
for (int panelAreaLoop = 0; panelAreaLoop < panelAreaCount; panelAreaLoop++)
{ // for every area
int areaTable = areaTableCount;
ChartArea chartArea = new ChartArea();
chartArea.name = dashBoardDataSet.Tables[areaTable].Rows[panelAreaLoop][2].ToString();
int chartAreaRowNum = panelAreaLoop + 1;
System.Data.DataTable systDT = new System.Data.DataTable();
systDT = dashBoardDataSet.Tables[areaTable + chartAreaRowNum];
var dt = systDT.ToGoogleDataTable(); //convert with wrapper
//issue ==> //dt = RemoveColumnsWithTitleLikeDisplayAndPassCellContentsAsFormattedStringToPreviousCell(dt);
chartArea.table = JsonConvert.DeserializeObject(dt.GetJson());
chartAreaList.Add(chartArea);
//}
if (chartAreaList.Count() > 0) chartPanel.areas = chartAreaList;
tableCount++;
}
if (chartPanel.areas != null && chartPanel.areas.Count() > 0) chartTypeList.Add(chartPanel);
}
return Ok(chartTypeList);
}
else { return NotFound(); }
}
else { return NotFound(); }
}
Is there a better way to do this?
Figured it out. Here is my working code with a hack to look for any column where (colName.Contains("_display")) and make it be the formatted ("f") data for the previous column.
To map the column to the formatting column I made a custom class.
class ColumnDisplayMap
{
public int columnToFormat { get; set; }
public int formatColumn { get; set; }
}
[ResponseType(typeof(List<ChartPanel>))]
public IHttpActionResult GetChart(int gym, string dateCategory, string iso8601date, int id = -1)
{
if (!String.IsNullOrWhiteSpace(dateCategory))
{
dateCategory = dateCategory.ToLower();
string strConnString = ConfigurationManager.ConnectionStrings["PrimaryDBConnection"].ConnectionString;
// return DataSet From USP
DataSet dashBoardDataSet = GetDataSQL(strConnString, gym, dateCategory, iso8601date, 0);
if (dashBoardDataSet != null)
{
int chartPanelCount = dashBoardDataSet.Tables[0].Rows.Count;
List<ChartPanel> chartTypeList = new List<ChartPanel>(); // list for all the panels
// first table describes the Chart Panels
int tableCount = 0;
for (int chartPanelLoop = 0; chartPanelLoop < chartPanelCount; chartPanelLoop++)
{ // for every panel
ChartPanel chartPanel = new ChartPanel();
chartPanel.name = dashBoardDataSet.Tables[0].Rows[chartPanelLoop][2].ToString();
// second table describes the following chart areas for the panel
DataRow[] areaTableRows = dashBoardDataSet.Tables[1].Select("charttype = " + (chartPanelLoop + 1).ToString());
int panelAreaCount = areaTableRows.Count();
List<ChartArea> chartAreaList = new List<ChartArea>();
for (int panelAreaLoop = 0; panelAreaLoop < panelAreaCount; panelAreaLoop++)
{ // for every area
int areaTable = 1;
ChartArea chartArea = new ChartArea();
chartArea.name = areaTableRows[panelAreaLoop][3].ToString(); // dashBoardDataSet.Tables[areaTable].Rows[panelAreaLoop][3].ToString();
DataColumnCollection columns = dashBoardDataSet.Tables[areaTable + tableCount + 1].Columns;
DataRowCollection rows = dashBoardDataSet.Tables[areaTable + tableCount + 1].Rows;
Google.DataTable.Net.Wrapper.DataTable gdt = new Google.DataTable.Net.Wrapper.DataTable();
List<ColumnDisplayMap> cMap = new List<ColumnDisplayMap>();
foreach (DataColumn col in columns)
{
string colName = col.ToString();
if (!colName.Contains("_display"))
{
ColumnType type = ColumnType.Number;
if (!col.IsNumeric()) type = ColumnType.String;
gdt.AddColumn(new Column(type, col.ToString(), col.ToString()));
}else
{
ColumnDisplayMap cdm = new ColumnDisplayMap(){columnToFormat = col.Ordinal - 1, formatColumn = col.Ordinal};
cMap.Add(cdm);
}
}
foreach (DataRow row in rows)
{
var r = gdt.NewRow();
for (int cellItem = 0; cellItem < row.ItemArray.Count(); cellItem++)
{
if (cMap.Any(c => c.columnToFormat.Equals(cellItem)))
{
r.AddCell(new Cell(row.ItemArray[cellItem], row.ItemArray[cellItem + 1].ToString()));
}
else if (cMap.Any(c => c.formatColumn.Equals(cellItem)))
{
// do nothing
}
else
{
r.AddCell(new Cell(row.ItemArray[cellItem], row.ItemArray[cellItem].ToString()));
}
}
gdt.AddRow(r);
}
chartArea.table = JsonConvert.DeserializeObject(gdt.GetJson());
chartAreaList.Add(chartArea);
//}
if (chartAreaList.Count() > 0) chartPanel.areas = chartAreaList;
tableCount++;
}
if (chartPanel.areas != null && chartPanel.areas.Count() > 0) chartTypeList.Add(chartPanel);
}
return Ok(chartTypeList);
}
else { return NotFound(); }
}
else { return NotFound(); }
}