Passing a Sorted List as a Series to be rendered in an IG Chart

here is the code thats not working. I have been working to change around the view rendering and the way in which the data gets passed. The sorted list contains the key which is date time and the there are two values attached to each key. the two values are rate and volume. I am trying to create a spline with igniteUI that displays date on the x axis and rate on the y axis. in addition there are controls that allow the user to change data being passed into the chart. In that sense it is dynamic.

I cannot zip this project because the solution is very large and proprietary. You can copy and paste this code into visual studio to run it.

here is the controller:

using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Web.Mvc;
using System.Web.UI.DataVisualization.Charting;
using EAGLEWebPages.Models;
using JBHunt.Eagle.EOneICS;
using JBHunt.Eagle.Utilities;
using System.Data.Entity;
using JBHunt.Eagle.Utilities.Client;
using System.Linq;
using System.Data.OleDb;
using EAGLEWebPages.Models.ICSPTE;

namespace EAGLEWebPages.Controllers
{
public class ICSPTEController : Controller
{
private static readonly int sModule = Logfile.GetModuleId(ModuleName);
private static string ModuleName { get { return System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name; } }

ICSPTEModel m = new ICSPTEModel();

[AuthorizeEagleUser]
[OutputCache(Duration = 24 * 60 * 60, VaryByParam = "*")]
public ActionResult chart(
string ORIGIN
, string DEST
, string EquipmentType
, bool transcore = false
, bool ratemate = false
, bool icshistory = false
, bool otrData = false
, bool otrhistory = false
, bool chainalytics = false
, int minDate = -1
, int maxDate = -1
, string windowStart = null
, string windowStop = null
, bool WindowMode = false
, bool defaultView = true
, double alpha = 1.0)

{
//Here is an example URL for hitting this
// http://localhost:52733/ICSPTE?oHub=60441IL&dHub=30302GA&eqp=JBHU53&oDesc=Chicago,IL&dDesc=Atlanta,GA&miles=641

Logfile.Log(Logfile.MessageType.Debug, sModule, "Accessing Index method on ICSPTEController");

// Eligible for OTR View?
bool otrView = (Security.Session.BusinessUnits.Contains(Ibu.OTR) && defaultView) || otrData;

////// PARSE INPUT PARAMETERS INTO VIEW MODEL //////
// Toggle Default View
m.defaultView = defaultView;
m.useOtrData = otrView;

// Set Origin and Destination
m.Origin = ORIGIN ?? m.Origin;
m.Destination = DEST ?? m.Destination;
m.oLoc = new Location(Convert.ToInt32(m.Origin));
m.dLoc = new Location(Convert.ToInt32(m.Destination));

// Set which data source(s) View should display.
m.winMode = WindowMode;
m.transcore = m.defaultView ? true : transcore;
m.ratemate = m.defaultView ? true : ratemate;
m.chainalytics = m.defaultView ? false : chainalytics;

if (Security.Session.BusinessUnits.Contains(Ibu.ICS))
{
m.icsHistory = true;
m.otrHistory = false;
}
else
{
m.icsHistory = m.defaultView ? false : icshistory;
m.otrHistory = m.defaultView ? true : otrhistory;
}

// Alpha changed?
m.alpha = alpha;

// Set Chart Parameters
DateTime defaultMinDate = DateTime.Today.AddMonths(-18);
DateTime defaultMaxDate = DateTime.Today.AddDays(7 - (int)DateTime.Today.DayOfWeek);
m.minDate = minDate == -1 ? (defaultMinDate.Date - new DateTime(1900, 1, 1)).TotalDays + 1 : minDate;
m.maxDate = maxDate == -1 ? (defaultMaxDate.Date - new DateTime(1900, 1, 1)).TotalDays + 1 : maxDate;

// Set Window Start/Stop Range
if (!String.IsNullOrEmpty(windowStart))
{
m.windowStart = Convert.ToDateTime(windowStart);
}
if (!String.IsNullOrEmpty(windowStop))
{
m.windowStop = Convert.ToDateTime(windowStop);
}

// Set Eqp. Type
if (!string.IsNullOrEmpty(EquipmentType)) { EnumUtils.TryParse(EquipmentType, out m.EquipmentType); }

if (m.oLoc.Valid && m.dLoc.Valid && ValidateAndBuildHubPair(m.oLoc.Hub, m.dLoc.Hub, m.EquipmentType.ToString(), out m.hubPair, out m.eqpTyp))
{
try
{
double finalPTE;
m.miles = m.hubPair.MilesH2H;
m.fuel = ICSCache.ICSFuel.DefaultFSCcpm;
m.pteAvg = 0;
m.listRate = 0;
m.otrPercentPTEmarkup = (double)MarketRatesModel.getOtrPteMarkup();
m.otrWindowMargin = MarketRatesModel.getOtrListRateMargin();
double dvnDPM;
double refDPM;
double fbdDPM;
decimal discardMinPte;
decimal discardMaxPte;

//refactoring opportunity, create a HubPairEqp or HubPairIbu class
switch (m.eqpTyp)
{
case JBHunt.Eagle.Utilities.EquipmentType.JBHU53:
HubPair.GetICSPTECost(m.oLoc.Proximity, m.dLoc.Proximity, m.miles, m.hubPair.ICSBlendedMktPteMeanDvn, ICSCache.ICSFuel.DefaultFSCcpm, out finalPTE);
finalPTE = (double)ICSCache.CheckPteLimits((decimal)finalPTE, (decimal)m.miles, JBHunt.Eagle.Utilities.EquipmentType.JBHU53, true, out discardMinPte, out discardMaxPte);
HubPair.CalculateFinalICSRates(m.hubPair.SalesRankIcsDvn, finalPTE, ICSCache.ICSFuel.DefaultFSCcpm, m.oLoc.Proximity + m.dLoc.Proximity + m.miles, out dvnDPM, out refDPM, out fbdDPM);
m.pteAvg = otrView ? m.hubPair.ICSBlendedMktPteMeanDvn * (m.otrPercentPTEmarkup) : finalPTE / (m.miles + m.oLoc.Proximity + m.dLoc.Proximity);
m.listRate = otrView ? (double)m.hubPair.OtrRpm : dvnDPM + ICSCache.ICSFuel.DefaultFSCcpm;
m.spotRate = m.hubPair.IcsBlendedMktTransPteDvn;
m.spotRateDesc = m.hubPair.IcsBlendedMktTransPteDvnDesc;
break;
case JBHunt.Eagle.Utilities.EquipmentType.REF:
HubPair.GetICSPTECost(m.oLoc.Proximity, m.dLoc.Proximity, m.miles, m.hubPair.ICSBlendedMktPteMeanRef, ICSCache.ICSFuel.DefaultFSCcpm, out finalPTE);
finalPTE = (double)ICSCache.CheckPteLimits((decimal)finalPTE, (decimal)m.miles, JBHunt.Eagle.Utilities.EquipmentType.REF, true, out discardMinPte, out discardMaxPte);
HubPair.CalculateFinalICSRates(m.hubPair.SalesRankIcsRef, finalPTE, m.hubPair.FuelCostPerMile, m.oLoc.Proximity + m.dLoc.Proximity + m.miles, out dvnDPM, out refDPM, out fbdDPM);
m.pteAvg = otrView ? m.hubPair.ICSBlendedMktPteMeanRef * (m.otrPercentPTEmarkup) : finalPTE / (m.miles + m.oLoc.Proximity + m.dLoc.Proximity);
m.listRate = otrView ? (double)m.hubPair.OtrRpm : refDPM + ICSCache.ICSFuel.DefaultFSCcpm;
m.spotRate = m.hubPair.IcsBlendedMktTransPteRef;
m.spotRateDesc = m.hubPair.IcsBlendedMktTransPteRefDesc;
break;
case JBHunt.Eagle.Utilities.EquipmentType.FBD:
HubPair.GetICSPTECost(m.oLoc.Proximity, m.dLoc.Proximity, m.miles, m.hubPair.ICSBlendedMktPteMeanFbd, ICSCache.ICSFuel.DefaultFSCcpm, out finalPTE);
finalPTE = (double)ICSCache.CheckPteLimits((decimal)finalPTE, (decimal)m.miles, JBHunt.Eagle.Utilities.EquipmentType.FBD, true, out discardMinPte, out discardMaxPte);
HubPair.CalculateFinalICSRates(m.hubPair.SalesRankIcsFbd, finalPTE, m.hubPair.FuelCostPerMile, m.oLoc.Proximity + m.dLoc.Proximity + m.miles, out dvnDPM, out refDPM, out fbdDPM);
m.pteAvg = otrView ? m.hubPair.ICSBlendedMktPteMeanFbd * (m.otrPercentPTEmarkup) : finalPTE / (m.miles + m.oLoc.Proximity + m.dLoc.Proximity);
m.listRate = otrView ? (double)m.hubPair.OtrRpm : fbdDPM + ICSCache.ICSFuel.DefaultFSCcpm;
m.spotRate = m.hubPair.IcsBlendedMktTransPteFbd;
m.spotRateDesc = m.hubPair.IcsBlendedMktTransPteFbdDesc;
break;
}

// Get data from 3rd Party sources.

HistICSPTEData = MarketRatesModel.GetHistICSPTE(m.hubPair.Origin.Name, m.hubPair.Destination.Name, m.eqpTyp, m.alpha, m.useOtrData, m.fuel);
TranscoreData = MarketRatesModel.GetTranscore(m.hubPair.Origin.Name, m.hubPair.Destination.Name, m.eqpTyp, m.alpha, m.useOtrData);
RatemateData = MarketRatesModel.GetRatemate(m.hubPair.Origin.Name, m.hubPair.Destination.Name, m.eqpTyp, m.alpha, m.useOtrData, m.fuel);

decimal margin = m.useOtrData ? m.otrWindowMargin : (decimal)ICSCache.ICSListRateMargin;
decimal listFuel = m.useOtrData ? 0 : (decimal)m.fuel;

// Windowed Data
if (TranscoreData != null)
{
m.windowTranscoreAvg = MarketRatesModel.getLocalWindowedAverage(m.windowStart, m.windowStop, TranscoreData);
m.windowTranscoreAvgListRate = ((m.windowTranscoreAvg - listFuel) / (1 - margin)) + listFuel;
}
else
{
m.transcore = false;
}

if (RatemateData != null)
{
m.windowRatemateAvg = MarketRatesModel.getLocalWindowedAverage(m.windowStart, m.windowStop, RatemateData);
m.windowRatemateAvgListRate = ((m.windowRatemateAvg - listFuel) / (1 - margin)) + listFuel;
}
else
{
m.ratemate = false;
}

if (HistICSPTEData != null)
{
m.windowIcsHistoryAvg = MarketRatesModel.getLocalWindowedAverage(m.windowStart, m.windowStop, HistICSPTEData);
m.windowIcsHistoryAvgListRate = ((m.windowIcsHistoryAvg - listFuel) / (1 - margin)) + listFuel;
}
else
{
m.icsHistory = false;
}

//m.picData = Chart();

m.HistICSPTEData = HistICSPTEData;
m.RatemateData = RatemateData;
m.TranscoreData = TranscoreData;

List<ICSPTEModel> dataForChart = new List<ICSPTEModel>
{
new ICSPTEModel { HistICSPTEData = m.HistICSPTEData },
new ICSPTEModel { RatemateData = m.RatemateData },
new ICSPTEModel { TranscoreData = m.TranscoreData }
};

//IQueryable<List<ICSPTEModel>> chartData = new IQueryable<List<ICSPTEModel>> { new ICSPTEModel {RatemateData = m.RatemateData}};

return View(dataForChart.AsQueryable());

}
catch (Exception ex)
{
Logfile.Log(Logfile.MessageType.Error, sModule, "An excpetion occured while accessing Index of ICSPTE\r\n{0}", Logfile.FormatException(ex));
throw;
}
}
else
{
Logfile.Log(Logfile.MessageType.Error, sModule, "Could not validate inputs Origin:{0}, Destination:{1}, Equipment:{2}",
m.Origin, m.Destination, m.EquipmentType);
//this could return a nicer error page but the main entrance to this page is via PQ
//parameters should be correct pretty much all the time
return new HttpNotFoundResult("Could not validate input parameters");
}
}

private static bool ValidateAndBuildHubPair(string oHub, string dHub, string eqp, out HubPair hubPair, out EquipmentType eqpTyp)
{
eqpTyp = EquipmentType.JBHU53;
hubPair = null;
bool ok;
try
{
Hub orgHub;
Hub dstHub = null;
ok = Hub.TryGetHub(oHub, out orgHub);
ok = ok && Hub.TryGetHub(dHub, out dstHub);
ok = ok && EnumUtils.TryParse(eqp, out eqpTyp);
ok = ok &&
(eqpTyp == EquipmentType.JBHU53 || eqpTyp == EquipmentType.REF || eqpTyp == EquipmentType.FBD);
if (ok)
{
hubPair = new HubPair(orgHub, dstHub);
}
}
catch (Exception ex)
{
Logfile.Log(Logfile.MessageType.Error, sModule, "An exception occured while validating hubs and equipment:\r\n{0}", Logfile.FormatException(ex));
ok = false;
}

return ok;

}

}
}

here is the model:

using System;
using System.Linq;
using System.Collections.Generic;
using JBHunt.Eagle.Utilities;
using EAGLEWebPages.Controllers;

namespace EAGLEWebPages.Models
{

public class ICSPTEModel
{

public IQueryable<ICSPTEModel>chart{ get; set; }
//EquipmentType... but not all equipment types...so...
public enum Equipment
{
JBHU53,
FBD,
REF
}

//default oprigin
public string Origin = "217176";

//creates an actual name
//for a city state
//based on the number
//this is displayed in the dropdown
//where users configure the chart
public string OriginDesc
{
get
{
return oLoc != null && oLoc.Valid ? oLoc.City + ", " + oLoc.State : string.Empty;
}
}

public Location oLoc;

//repeat of the above with destination instead
public string Destination = "330948";
public string DestinationDesc
{
get
{
return dLoc != null && dLoc.Valid ? dLoc.City + ", " + dLoc.State : string.Empty;
}
}
public Location dLoc;

//sets initial equipment type
public Equipment EquipmentType = Equipment.JBHU53;

//changes equipment type for passthrough
public EquipmentType eqpTyp;
public HubPair hubPair;

// Window Components
public bool defaultView = true;
public bool transcore;
public bool ratemate;
public bool icsHistory;
public bool otrHistory;
public bool useOtrData;
public bool chainalytics;
public string windowType
{
get
{
return (transcore ? " TS " : "") + (ratemate ? " RM " : "") + (icsHistory ? " ICS " : "");
}
}

public double otrPercentPTEmarkup;
public double pteAvg;
public double listRate;
public decimal spotRate;
public string spotRateDesc;
public double fuel;
public double miles;

//got ride of tuple
//CI was no good
//-michael rosenberg

public SortedList<DateTime, MarketRatesModel.RateWithVolume> HistICSPTEData;
public SortedList<DateTime, MarketRatesModel.RateWithVolume> TranscoreData;
public SortedList<DateTime, MarketRatesModel.RateWithVolume> RatemateData;
// Windowed Data
public bool winMode;
public decimal windowRatemateAvg;
public decimal windowRatemateAvgListRate;
public decimal windowTranscoreAvg;
public decimal windowTranscoreAvgListRate;
public decimal windowIcsHistoryAvg;
public decimal windowIcsHistoryAvgListRate;
public DateTime windowStart = DateTime.Now.AddDays(-90);
public DateTime windowStop = DateTime.Now;
public double windowStartInt
{
get
{
return (windowStart.Date - new DateTime(1900, 1, 1)).TotalDays;
}
}
public double windowStopInt
{
get { return (windowStop.Date - new DateTime(1900, 1, 1)).TotalDays; }
}

public decimal wPteAvg
{
get
{
decimal num = (ratemate ? windowRatemateAvg : 0) + (transcore ? windowTranscoreAvg : 0) + (icsHistory ? windowIcsHistoryAvg : 0);
decimal den = (ratemate ? 1 : 0) + (transcore ? 1 : 0) + (icsHistory ? 1 : 0);
decimal avg = den > 0 ? num / den : 0;

return avg;
}
}

public decimal wListRate
{
get
{
decimal num = (ratemate ? windowRatemateAvgListRate : 0) + (transcore ? windowTranscoreAvgListRate : 0) + (icsHistory ? windowIcsHistoryAvgListRate : 0);
decimal den = (ratemate ? 1 : 0) + (transcore ? 1 : 0) + (icsHistory ? 1 : 0);
decimal avg = den > 0 ? num / den : 0;

return avg;
}
}

public decimal otrWindowMargin;

// Chart Control
// this will be used with infragistics
//believe it or not
//even though it was originally intended
//for rendering a windows chart
//it will give us information we need
// for the rendering of the infragistis chart
// we keep it
public double alpha;
public DateTime minRange;
public DateTime maxRange;
public double minRangeDouble;
public double maxRangeDouble;
public int startDateInt
{
get { return (int)(minRange.Date - new DateTime(1900, 1, 1)).TotalDays + 1; }
}

public int stopDateInt
{
get { return (int)(maxRange.Date - new DateTime(1900, 1, 1)).TotalDays + 1; }
}

public double minDate;
public double maxDate;

public string MinDate
{
get { return minDate.ToString(); }
}

public string MaxDate
{
get { return maxDate.ToString(); }
}

public byte[] picData;
//this is the one part of the viewer that we clevarly shouldget rid of
//we do not want to mess up dependencies, and have an infragistics issue
//but what we can do is just render the chart invisible in the views file
//in html
//this is highly inefficient, but it will be what we do in this first version
//the version after this will be much cleaner, and slowly remove dependencies
//as infragistics testing becomes viable
//public string ImageSource
//{
// get
// {
// string mimeType = "image/png";
// string base64 = Convert.ToBase64String(picData);
// return string.Format("data:{0};base64,{1}", mimeType, base64);
// }
//}

public double xMinPix;
public double xMaxPix;
public double yMinPix;
public double yMaxPix;

}
}

here is the view:

@using Infragistics.Web.Mvc
@using JBHunt.Eagle.Utilities.Server
@using JBHunt.Eagle.Utilities
@using JBHunt.Eagle.Utilities.WebPage
@using System.Web.Mvc
@using System.Data
@using System.Data.Entity
@using System.Data.OleDb
@using System.Linq
@using EAGLEWebPages.Models
@model IQueryable<EAGLEWebPages.Models.ICSPTEModel>
@{
ViewBag.Title = "chart";
}

<!DOCTYPE html>
<html xmlns ="http://www.w3.org/1999/xhtml"
<head>

<script src="@Url.Content("~/Scripts/infragistics.core.js")" type = "text/javascript"></script>
<script src="@Url.Content("~/Scripts/infragistics.loader.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/infragistics.dv.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/infragistics.lob.js")" type="text/javascript"></script>

</head>
@*this should render a spline that changes thickness depending on the volume*@
<body>
<div>

</td>
</div>
<div style="align-items">
@(Html.Infragistics().DataChart(Model)
.ID("chart")
.Width("1000px")
.Height("500px")
.AutoMarginHeight(0)
.VerticalZoomable(true)
.HorizontalZoomable(true)
.Title("ICS PTE Analysis (IPA)")
.Legend(legend => legend.ID("legend").Width("1000px"))
.DefaultInteraction(InteractionState.DragPan)
.CrosshairVisibility(Visibility.Visible)
.Axes(axes => {
axes.CategoryX("xAxis").Label(item => item.RatemateData.Keys).Stroke("rgba(0, 0, 0, 0.1)").StrokeThickness(5).Interval(1);
axes.NumericY("rate").Stroke("rgba(0,0,0,0.5)").StrokeThickness(5);
})
.Series(series =>{
series.Line("RateMateSeries").Title("RateMate").XAxis("xAxis").YAxis("rateAxis")
.ValueMemberPath(item => item.RatemateData.Values.SelectMany(x => x.rate).ToList()).MarkerType(MarkerType.Circle).Brush("rgba(255,0,0,1)").MarkerBrush("rgba(255,0,0,1)")
.TrendLineType(TrendLineType.ModifiedAverage).TrendLineBrush("rgba(0, 100, 0, 1)").TrendLineThickness(3).Legend(legend => legend.ID("legend"))
.ShowTooltip(true);
})
.DataBind()
.Render()

)

</div>

</body>
</html>

the data is queried in this file:

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Reflection;
using JBHunt.Eagle.Utilities;

namespace EAGLEWebPages.Models
{
public class MarketRatesModel
{
private static readonly int sModule = Logfile.GetModuleId(ModuleName);
private static string ModuleName { get { return System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name; } }

public struct RateWithVolume
{
public double rate;
public int volume;

public RateWithVolume(double rate, int volume)
{
this.rate = rate;
this.volume = volume;

}
}

public static decimal getOtrPteMarkup()
{
decimal otrPteMarkup = -1;

using (OleDbConnection conn = EGLConn.EAGLEConn)
{
string sql = "SELECT OTR_PTE_PCT_INC FROM EGL.OTR_PRC_COMPONENTS";
OleDbCommand cmd = new OleDbCommand(sql, conn);
otrPteMarkup = Convert.ToDecimal(cmd.ExecuteScalar());
}

return otrPteMarkup;
}

public static decimal getOtrListRateMargin()
{
decimal otrListRateMargin = -1;

using (OleDbConnection conn = EGLConn.EAGLEConn)
{
string sql = "SELECT LIST_RATE_MARGIN FROM EGL.OTR_PRC_COMPONENTS";
OleDbCommand cmd = new OleDbCommand(sql, conn);
otrListRateMargin = Convert.ToDecimal(cmd.ExecuteScalar());
}

return otrListRateMargin;
}

// Get Historical ICS PTE expense -> when fuel is not included, best we can do is estimate the cost using market fuel... this is only used for the OTR
// data option.
public static SortedList<DateTime, RateWithVolume> GetHistICSPTE(string oHub, string dHub, EquipmentType eqp, double alpha, bool fuelExcl, double fuel)
{
string sqlRemoveFuel = " - " + fuel;

SortedList<DateTime, RateWithVolume> unfiltered = GetMarketRate(
"SELECT IHPD.PTE" + (fuelExcl ? sqlRemoveFuel : "") +", IHPD.SMPL_SZ, IHPD.END_T" +
" FROM EGL.ICS_HIST_PTE_DATA IHPD" +
" WHERE IHPD.ORIG = ?" +
" AND IHPD.DEST = ?" +
" AND IHPD.EQP_TYP = ?" +
" AND IHPD.END_T BETWEEN CURRENT DATE - 18 MONTHS" +
" AND CURRENT DATE" +
" - (DAYOFWEEK (CURRENT DATE) - 7) DAYS", oHub, dHub, eqp);

if (unfiltered.Count > 1)
{
SortedList<DateTime, RateWithVolume> filtered = FilterRateData(unfiltered, alpha);
// double CI = getConfidenceInterval(filtered, unfiltered);

return new SortedList<DateTime, RateWithVolume>(filtered);
}
else
{
return null;
}
}

public static SortedList<DateTime, RateWithVolume> GetTranscore(string oHub, string dHub, EquipmentType eqp, double alpha, bool fuelExcl)
{
string sqlSelect = "AVG_RATE + AVG_FSC";
if(fuelExcl)
{
sqlSelect = "AVG_RATE";
}

string sql = "SELECT " + sqlSelect + " AS PTE, SAMPLE, DATE (CRT_S) AS DATE" +
" FROM EGL.TRANSCORE_SPOT_HST HST" +
" WHERE OHUB = ?" +
" AND DHUB = ?" +
" AND EQUIPMENT = ?" +
" AND DATE (HST.CRT_S) BETWEEN CURRENT DATE - 18 MONTHS" +
" AND CURRENT DATE" +
" - (DAYOFWEEK (CURRENT DATE) - 7) DAYS" +
" ORDER BY CRT_S ASC";

SortedList<DateTime, RateWithVolume> unfiltered = GetMarketRate(sql, oHub, dHub, eqp);

if (unfiltered.Count > 1)
{
SortedList<DateTime, RateWithVolume> filtered = FilterRateData(unfiltered, alpha);
//double CI = getConfidenceInterval(filtered, unfiltered);

return new SortedList<DateTime, RateWithVolume>(filtered);
}
else
{
return null;
}
}

public static SortedList<DateTime, RateWithVolume> GetRatemate(string oHub, string dHub, EquipmentType eqp, double alpha, bool fuelExcl, double fuel)
{
string sqlRemoveFuel = " - " + fuel;

SortedList<DateTime, RateWithVolume> unfiltered = GetMarketRate(
"SELECT HST.AVG_7 " + (fuelExcl ? sqlRemoveFuel : "") + " AS RATE, HST.SAMPLE_7 AS SAMPLE, DATE (HST.UPDATED) AS DATE" +
" FROM EGL.RATE_MATE_HST HST" +
" WHERE HST.OHUB = ?" +
" AND HST.DHUB = ?" +
" AND HST.EQUIPMENT = ?" +
" AND HST.RATE_TO = 'TRUCKER'" +
" AND DATE (HST.UPDATED) BETWEEN CURRENT DATE - 18 MONTHS" +
" AND CURRENT DATE" +
" - (DAYOFWEEK (CURRENT DATE) - 7) DAYS", oHub, dHub, eqp);

if (unfiltered.Count > 1)
{
SortedList<DateTime, RateWithVolume> filtered = FilterRateData(unfiltered, alpha);
// double CI = getConfidenceInterval(filtered, unfiltered);

return new SortedList<DateTime, RateWithVolume>(filtered);
}
else
{
return null;
}
}

public static SortedList<DateTime, RateWithVolume> GetOTRHistRev(string oHub, string dHub, bool fuelExcl)
{
//cannot use GetMarketRate because this query does not take an equipment type parameter
SortedList<DateTime, RateWithVolume> ratesByDate = new SortedList<DateTime, RateWithVolume>();

string sqlSelect = "TOTAL_REVENUE";

if(fuelExcl)
sqlSelect = "TOTAL_REVENUE - REV_FUEL";

string sql = "SELECT (SUM ("+ sqlSelect +") / COUNT (TOTAL_REVENUE))" +
" / (SUM (LOADED_MILES) / COUNT (LOADED_MILES))" +
" AVG_RPM," +
" COUNT (ORDER_NBR) AS VOL," +
" FIRST_DISP_DATE - (DAYOFWEEK (FIRST_DISP_DATE) - 1) DAYS AS DATE" +
" FROM EGL.JBHT_ORDERS" +
" WHERE OHUB = ? AND DHUB = ? AND LOADED_MILES > 0 AND TARIFF_REV > 0" +
" GROUP BY FIRST_DISP_DATE - (DAYOFWEEK (FIRST_DISP_DATE) - 1) DAYS";

using (OleDbConnection conn = EGLConn.EAGLEConn)
{
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
cmd.Parameters.AddWithValue("OHUB", oHub);
cmd.Parameters.AddWithValue("DHUB", dHub);

using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
double rate = reader.GetDouble(0);
int volume = reader.GetInt32(1);
DateTime date = reader.GetDateTime(2);

if (!ratesByDate.ContainsKey(date))
ratesByDate.Add(date, new RateWithVolume(rate, volume));
}
}
}
}

return ratesByDate;
}

public static SortedList<DateTime, RateWithVolume> GetChainalytics(string oHub, string dHub, EquipmentType eqp, bool fuelExcl)
{
string sqlSelect = "C.PUB_FL_RPM_TOT_CST";
if (fuelExcl)
sqlSelect = "C.PUB_FL_RPM_TOT_CST - C.PUB_FL_RPM_FL_CST";

return GetMarketRate(
"SELECT DOUBLE (" + sqlSelect + ") AS RATE," +
" INT (" +
" CEIL (" +
" ( DOUBLE (C.ORG_VOL)" +
" * DOUBLE (C.DST_VOL)" +
" / DOUBLE (ORG_VOL.TOT_ORG_VOL)" +
" + DOUBLE (C.ORG_VOL)" +
" * DOUBLE (C.DST_VOL)" +
" / DOUBLE (DST_VOL.TOT_DST_VOL))" +
" / 2.0))" +
" AS SAMPLE," +
" C.MDL_PERIOD AS DATE" +
" FROM EGL.CHAINALYTICS_HST C" +
" INNER JOIN" +
" (SELECT SUM (DST.VOL) AS TOT_DST_VOL, DST.MDL_TYP" +
" FROM (SELECT DISTINCT" +
" C.DST_HUB, C.DST_VOL AS VOL, C.MDL_TYP AS MDL_TYP" +
" FROM EGL.CHAINALYTICS_HST C" +
" WHERE C.ERR_DSC = 'OK'" +
" AND C.MDL_PERIOD BETWEEN CURRENT DATE - 18 MONTHS" +
" AND CURRENT DATE) AS DST" +
" GROUP BY MDL_TYP) AS DST_VOL" +
" ON DST_VOL.MDL_TYP = C.MDL_TYP" +
" INNER JOIN" +
" (SELECT SUM (ORG.VOL) AS TOT_ORG_VOL, ORG.MDL_TYP" +
" FROM (SELECT DISTINCT" +
" C.ORG_HUB, C.ORG_VOL AS VOL, C.MDL_TYP AS MDL_TYP" +
" FROM EGL.CHAINALYTICS_HST C" +
" WHERE C.ERR_DSC = 'OK'" +
" AND C.MDL_PERIOD BETWEEN CURRENT DATE - 18 MONTHS" +
" AND CURRENT DATE) AS ORG" +
" GROUP BY MDL_TYP) AS ORG_VOL" +
" ON C.MDL_TYP = ORG_VOL.MDL_TYP" +
" WHERE C.ORG_HUB = ? AND C.DST_HUB = ? AND C.EQP_TYP = ?"
, oHub, dHub, eqp);
}

/// <summary>
/// Helper method to reduce code duplication when running market rate queries.
/// </summary>
/// <param name="query">Must have parameters in the order of oHub, dHub, equipment and must select data in the order of rate, volume, date</param>
/// <param name="oHub"></param>
/// <param name="dHub"></param>
/// <param name="eqp"></param>
/// <returns></returns>
private static SortedList<DateTime, RateWithVolume> GetMarketRate(string query, string oHub, string dHub, EquipmentType eqp)
{
SortedList<DateTime, RateWithVolume> ratesByDate = new SortedList<DateTime, RateWithVolume>();

using (OleDbConnection conn = EGLConn.EAGLEConn)
{
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{

cmd.Parameters.AddWithValue("ORIG", oHub);
cmd.Parameters.AddWithValue("DEST", dHub);
cmd.Parameters.AddWithValue("EQP_TYP", eqp.ToString());

using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
double rate = reader.GetDouble(0);
int volume = reader.GetInt32(1);
DateTime date = reader.GetDateTime(2);

if (!ratesByDate.ContainsKey(date))
ratesByDate.Add(date, new RateWithVolume(rate, volume));
}
}
}
}

Logfile.Log(Logfile.MessageType.Debug, sModule, "GetMarketRate fetched {0} rows", ratesByDate.Count);

return ratesByDate;

}

private static SortedList<DateTime, RateWithVolume> FilterRateData(SortedList<DateTime, RateWithVolume> dataIn, double alpha)
{
// Function to process time-series rate and volume data.
// Sorting by date ascending should be handled in SQL before passing data to this function.

// Instantiate output and pull out list of available dates.
SortedList<DateTime, RateWithVolume> dataOut = new SortedList<DateTime, RateWithVolume>();
List<DateTime> dates = new List<DateTime>(dataIn.Keys);

// Initialize filter: Use first value in dataIn.
dataOut.Add(dates[1], dataIn[dates[0]]);

// Expontential smoothing filter for rates (and volume?)
for (int i = 1; i < dates.Count; i++)
{
// Applies weighted average to estimate and measured data point at each time-step.
RateWithVolume previousEstimate = dataOut[dates[i]];
RateWithVolume currentMeasured = dataIn[dates[i]];

// Exponential Smoothing Forecast (simple way to create a roaming average)
double rateEstNext = alpha * currentMeasured.rate + (1 - alpha) * previousEstimate.rate;

// Volume of forecast is assumed to be the measured volume (but does this accurately portray the "weight" of the estimate)
int volumeEstNext = currentMeasured.volume;

// Append forecast.
DateTime date;
if (i + 1 >= dates.Count)
{
date = dates[i].AddDays(7);
}
else
{
date = dates[i + 1];
}
dataOut.Add(date, new RateWithVolume(rateEstNext, volumeEstNext));
}

return dataOut;
}

private static double getConfidenceInterval(SortedList<DateTime, RateWithVolume> meanData, SortedList<DateTime, RateWithVolume> measData)
{
double residual = 0;
double volume = 0;
double sigma = 0;

foreach (KeyValuePair<DateTime, RateWithVolume> dataPoint in meanData)
{
if (measData.ContainsKey(dataPoint.Key))
{
residual += Math.Pow(measData[dataPoint.Key].rate - dataPoint.Value.rate, 2) * dataPoint.Value.volume;
volume += dataPoint.Value.volume;
}
}

if (volume > 0)
{
sigma = Math.Sqrt(residual / volume);
}

return sigma;
}

public static decimal getLocalWindowedAverage(DateTime start, DateTime stop, SortedList<DateTime, RateWithVolume> measData)
{
decimal localAvg = 0;
double numerator = 0;
int denominator = 0;

foreach (DateTime key in measData.Keys)
{
if (key >= start && key <= stop)
{
numerator += measData[key].rate;
denominator++;
}
}

if (denominator > 0)
{
localAvg = (decimal)numerator / denominator;
}

return localAvg;
}
}
}

the current error I get has to do with this line in the view:

.ValueMemberPath(item => item.RatemateData.Values.SelectMany(x => x.rate).ToList()).MarkerType(MarkerType.Circle).Brush("rgba(255,0,0,1)").MarkerBrush("rgba(255,0,0,1)")

the error states :

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1660: Cannot convert lambda expression to type 'string' because it is not a delegate type