public List <Tuple <string, int> > GetOrderInformation(int selectedID, WindowGraph.DisplayMode displayMode, bool isProduct)
{
List <Tuple <string, int> > studentChoices = new List <Tuple <string, int> >();
//switches out the group by sql statement to group the sum of the days by the display mode
string returnMode = "%Y-%m-%d";
switch (displayMode)
{
case WindowGraph.DisplayMode.Day:
returnMode = "%Y-%m-%d";
break;
case WindowGraph.DisplayMode.Month:
returnMode = "%Y-%m";
break;
case WindowGraph.DisplayMode.Year:
returnMode = "%Y";
break;
}
dbconn.Open();
dbcmd = dbconn.CreateCommand();
//switch between grabbing product and lunchchoice information
if (!isProduct)
{
dbcmd.CommandText = "SELECT * FROM (( SELECT strftime('" + returnMode + "', date), SUM(amount_of_lunches), lunch_choice_id " +
"FROM studentlunchchoices WHERE lunch_choice_id = " + selectedID + " GROUP BY strftime('" + returnMode + "', date)) INNER JOIN ( SELECT " +
"lunch_choice_id, lunch_choice_name FROM lunchchoices WHERE lunch_choice_id = " + selectedID +
") USING(lunch_choice_id));";
}
else
{
dbcmd.CommandText = "SELECT date, lunches, product_id FROM(SELECT * " +
"FROM products INNER JOIN lunchchoices_products USING (product_id) " +
"WHERE product_id = " + selectedID + ") INNER JOIN (SELECT strftime('" + returnMode + "', date) " +
"date, lunch_choice_id, SUM(amount_of_lunches) lunches FROM studentlunchchoices " +
"GROUP BY strftime('" + returnMode + "', date), lunch_choice_id) USING (lunch_choice_id) " +
"INNER JOIN products USING(product_id)";
}
Debug.Log(dbcmd.CommandText);
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
string date = reader.GetString(0);
int amount = reader.GetInt32(1);
studentChoices.Add(Tuple.Create(date, amount));
}
dbconn.Close();
dbcmd.Dispose();
reader.Dispose();
return(studentChoices);
}
public void OnDisplayModeDropdownChanged(TMP_Dropdown change)
{
switch (change.value)
{
case 0:
currentDisplayMode = WindowGraph.DisplayMode.Day;
break;
case 1:
currentDisplayMode = WindowGraph.DisplayMode.Month;
break;
case 2:
currentDisplayMode = WindowGraph.DisplayMode.Year;
break;
}
PopulateGraph();
}
public List <Tuple <string, int> > GetForecastedChoices(List <Tuple <string, int> > inputData, WindowGraph.DisplayMode displayMode)
{
dateStrings = new List <string>();
var inputDataAsTimeSeriesData = new List <TimeSeriesData>();
foreach (Tuple <string, int> tuple in inputData)
{
if (displayMode != WindowGraph.DisplayMode.Year)
{
inputDataAsTimeSeriesData.Add(new TimeSeriesData(DateTime.Parse(tuple.Item1), tuple.Item2));
}
else
{
inputDataAsTimeSeriesData.Add(new TimeSeriesData(DateTime.ParseExact(tuple.Item1, "yyyy", System.Globalization.CultureInfo.InvariantCulture), tuple.Item2));
}
}
if (inputDataAsTimeSeriesData.Count < 7)
{
return(new List <Tuple <string, int> >());
}
MLContext context = new MLContext(); // defines a new ML context
//IDataView dataView = context.Data.LoadFromTextFile<ChoiceRecord>(path: dataPath, hasHeader: true, separatorChar: ','); // loads data from csv into the dataView
IDataView dataView = context.Data.LoadFromEnumerable(inputDataAsTimeSeriesData);
List <Tuple <string, int> > newTuples = new List <Tuple <string, int> >();
// build a training pipeline for forecasting data
var pipeline = context.Forecasting.ForecastBySsa(
"Forecast",
nameof(TimeSeriesData.amountOfChoices),
windowSize: (inputDataAsTimeSeriesData.Count > 365) ? 365 : inputDataAsTimeSeriesData.Count / 4,
seriesLength: inputDataAsTimeSeriesData.Count + 1,
trainSize: (((inputDataAsTimeSeriesData.Count > 365) ? 365 : inputDataAsTimeSeriesData.Count / 4)) * 2 + 1,
horizon: (inputDataAsTimeSeriesData.Count > 365) ? 365 : inputDataAsTimeSeriesData.Count
);
// train the model
var model = pipeline.Fit(dataView);
var forecastingEngine = model.CreateTimeSeriesEngine <TimeSeriesData, TimeSeriesForecast>(context);
var forecasts = forecastingEngine.Predict();
int index = 0;
DateTime currentDate = inputDataAsTimeSeriesData.Last().date;
//make sure next prediction date starts in the school year
int schoolStartMonth = 8;
int schoolStartDay = 24;
int schoolEndMonth = 5;
int schoolEndDay = 15;
if (displayMode == WindowGraph.DisplayMode.Day)
{
currentDate.AddDays(1); // stops it from forecasting current day
//makes sure date falls within school year
if ((currentDate.Month < schoolStartMonth && currentDate.Month > schoolEndMonth) ^
(currentDate.Month == schoolStartMonth && currentDate.Day < schoolStartDay) ^
(currentDate.Month == schoolEndMonth && currentDate.Day >= schoolEndDay))
{
currentDate = new DateTime(currentDate.AddYears(1).Year, schoolStartMonth, schoolStartDay);
}
}
else if (displayMode == WindowGraph.DisplayMode.Month)
{
currentDate = currentDate.AddMonths(1); // stops it from forecasting current day
//makes sure date falls within school year
if ((currentDate.Month <= schoolStartMonth && currentDate.Month >= schoolEndMonth))
{
currentDate = new DateTime(currentDate.AddYears(1).Year, schoolStartMonth - 1, 1);
}
}
foreach (var forecast in forecasts.Forecast)
{
string currentDateString;
//increments day properly
if (displayMode == WindowGraph.DisplayMode.Day)
{
currentDate = currentDate.AddDays(1);
}
else if (displayMode == WindowGraph.DisplayMode.Month)
{
currentDate = currentDate.AddMonths(1);
}
else
{
currentDate = currentDate.AddYears(1);
}
currentDateString = currentDate.ToString("yyyy-MM-dd");
//Debug.Log(currentDateString + ", " + Mathf.RoundToInt(forecast));
newTuples.Add(Tuple.Create(currentDateString, Mathf.RoundToInt(forecast)));
index++;
}
return(newTuples);
}
