/*
* Input: string plant number, SolarContext database
* Output: None
* Desc: Reads in plan numbers into database, called from ReadInWeatherSensorData()
*/
static void AddPlantNumber(string value, SolarContext context)
{
int num = int.Parse(value);
//Get how many facilities exist with PlantNumber
int?exists = context.Facilities
.Where(f => f.PlantNumber == num)
.Count();
/*
* Facility {
* PK int FacilityID, auto increment
* int PlantNumber }
*/
if (!(exists > 0))
{
Facility f = new Facility()
{
PlantNumber = num,
};
context.Facilities.Add(f);
context.SaveChanges();
System.Diagnostics.Debug.WriteLine("Added Facility " + num + " to Database.");
}
}
public static void Init(IServiceProvider serviceProvider)
{
using (var context = new SolarContext(
serviceProvider.GetRequiredService <DbContextOptions <SolarContext> >()
))
{
if (context.WeatherReadings.Any())
{
//If any readings exist, then database is already seeded and missing records added
stampFixed = true;
return;
}
/* Seed the database
*
* Seeds using data from 4 CSV files which can be found in the Data folder
*
* Plant_1_Generation_Data.csv
* Plant_1_Weather_Sensor_Data.csv
*
* Plant_2_Generation_Data.csv
* Plant_1_Weather_Sensor_Data.csv
*/
//ReadInWeatherSensorData("Plant_1_Weather_Sensor_Data.csv", context);
ReadInGenerationData("Plant_1_Generation_Data.csv", context);
ReadInGenerationData("Plant_2_Generation_Data.csv", context);
ReadInWeatherSensorData("Plant_1_Weather_Sensor_Data.csv", context);
ReadInWeatherSensorData("Plant_2_Weather_Sensor_Data.csv", context);
}
}
/*
* Input: String file name, SolarContext database
* Output: None
* Desc: Reads weather sensor data from file_name into database
*/
static void ReadInWeatherSensorData(string file_in, SolarContext context)
{
try
{
using (var reader = new StreamReader("Data/" + file_in))
{
string line;
bool firstRun = true;
System.Diagnostics.Debug.WriteLine("Seeding Database with " + file_in);
reader.ReadLine(); //First line is human stuff
while ((line = reader.ReadLine()) != null)
{
var values = line.Split(',');
if (firstRun)
{
AddPlantNumber(values[1], context);
firstRun = false;
}
/*
* WeatherReading {
* PK int WeatherReadingID, auto increment
* DateTime DateAndTime,
* double AmbientTemp,
* double ModuleTemp,
* double Irradiation }
*/
DateTime dateTime = DateTime.Parse(values[0]);
int plantNum = int.Parse(values[1]);
double aTemp = double.Parse(values[3]);
double mTemp = double.Parse(values[4]);
double irrid = double.Parse(values[5]);
WeatherReading r = new WeatherReading()
{
DateAndTime = dateTime,
PlantNumber = plantNum,
AmbientTemp = aTemp,
ModuleTemp = mTemp,
Irradiation = irrid
};
context.WeatherReadings.Add(r);
}
context.SaveChanges();
System.Diagnostics.Debug.WriteLine("Finished successfully.");
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Error reading from file '" + file_in + "': " + e.Message);
}
}
/*
* Input: IServiceProvider - to get database context
* Output: None
* Desc: Original CSV files are missing large chunks of data. This function is called after database seed to add data where its datestamp is missing
* This function is a bandaid and is very inefficient, modifications needs to be made to Weather/Power reading functions to more efficiently
* fill in this data. On the other hand, this function is only ever ran once so that will probably never happen.
*/
public static void AddMissingDateStamps(IServiceProvider serviceProvider)
{
if (!stampFixed)
{
System.Diagnostics.Debug.WriteLine("Searching for and adding missing date values to Database.");
using (var context = new SolarContext(
serviceProvider.GetRequiredService <DbContextOptions <SolarContext> >()
))
{
//Invoking these takes a long time. It scans the entire database
addMissingWeather(context);
addMissingPower(context);
}
}
stampFixed = true;
}
/*
* Input: SolarContext database
* Output: None
* Desc: Called by AddMissingDatestamps to inefficiently add in missing power readings with -1 values
*/
static void addMissingPower(SolarContext context)
{
int plant1 = 4135001;
int plant2 = 4136001;
DateTime firstDate = DateTime.Parse("05-15-2020 00:00");
DateTime lastDate = DateTime.Parse("06-17-2020 23:45");
int count = 0;
int recordsAdded = 0;
//Each power source array is treated seperately
Dictionary <string, List <PowerReading> > sources5001 = new Dictionary <string, List <PowerReading> >();
Dictionary <string, List <PowerReading> > sources6001 = new Dictionary <string, List <PowerReading> >();
List <PowerSource> src = context.PowerSources.Where(ps => ps.PlantNumber == plant1).ToList();
//Populate dictionary with source keys and power readings for that source ordered by date
foreach (PowerSource ps in src)
{
sources5001.Add(ps.SourceKey, context.PowerReadings.Where(pr => pr.SourceKey == ps.SourceKey).OrderBy(pr => pr.DateAndTime).ToList());
count += sources5001[ps.SourceKey].Count();
}
src = context.PowerSources.Where(ps => ps.PlantNumber == plant2).ToList();
foreach (PowerSource ps in src)
{
sources6001.Add(ps.SourceKey, context.PowerReadings.Where(pr => pr.SourceKey == ps.SourceKey).OrderBy(pr => pr.DateAndTime).ToList());
count += sources6001[ps.SourceKey].Count();
}
System.Diagnostics.Debug.WriteLine(count + " power records found. Scanning for missing records.");
//Iterate over all the sourceKeys in each sources list comparing dates
foreach (KeyValuePair <string, List <PowerReading> > s in sources5001)
{
recordsAdded += addMissingArray(firstDate, lastDate, s, context);
}
foreach (KeyValuePair <string, List <PowerReading> > s in sources6001)
{
recordsAdded += addMissingArray(firstDate, lastDate, s, context);
}
count = context.PowerReadings.Count();
System.Diagnostics.Debug.WriteLine(recordsAdded + " total records were added. " + count + " total records");
}
/*
* Input: SolarContext database
* Output: None
* Desc: Scans all weather readings in the database looking for missing records based on date
*/
static void addMissingWeather(SolarContext context)
{
int plant1 = 4135001;
int plant2 = 4136001;
DateTime firstDate = DateTime.Parse("05-15-2020 00:00");
DateTime lastDate = DateTime.Parse("06-17-2020 23:45");
int recordsAdded = 0;
//Search for missing date values in weather table
List <WeatherReading> readings5001 = context.WeatherReadings.Where(wr => wr.PlantNumber == 4135001).OrderBy(wr => wr.DateAndTime).ToList();
List <WeatherReading> readings6001 = context.WeatherReadings.Where(wr => wr.PlantNumber == 4136001).OrderBy(wr => wr.DateAndTime).ToList();
System.Diagnostics.Debug.WriteLine((readings5001.Count + readings6001.Count) + " weather records found. Scanning for missing records.");
DateTime currDate = firstDate;
int index5001 = 0;
int index6001 = 0;
while (currDate != lastDate)
{
if (index5001 > readings5001.Count) //List doesnt include readings to end of date
{
WeatherReading r = new WeatherReading()
{
DateAndTime = currDate,
PlantNumber = plant1,
AmbientTemp = -1,
ModuleTemp = -1,
Irradiation = -1
};
context.WeatherReadings.Add(r);
recordsAdded++;
}
else if (currDate != readings5001[index5001].DateAndTime) //List missing readings in middle
{
WeatherReading r = new WeatherReading()
{
DateAndTime = currDate,
PlantNumber = plant1,
AmbientTemp = -1,
ModuleTemp = -1,
Irradiation = -1
};
context.WeatherReadings.Add(r);
recordsAdded++;
}
else
{
index5001++; //Continue
}
if (index6001 > readings6001.Count) //List doesnt include readings to end of date
{
WeatherReading r = new WeatherReading()
{
DateAndTime = currDate,
PlantNumber = plant2,
AmbientTemp = -1,
ModuleTemp = -1,
Irradiation = -1
};
context.WeatherReadings.Add(r);
recordsAdded++;
}
else if (currDate != readings6001[index6001].DateAndTime) //List missing readings in middle
{
WeatherReading r = new WeatherReading()
{
DateAndTime = currDate,
PlantNumber = plant2,
AmbientTemp = -1,
ModuleTemp = -1,
Irradiation = -1
};
context.WeatherReadings.Add(r);
recordsAdded++;
}
else
{
index6001++; //Continue
}
currDate = currDate.AddMinutes(15); //I don't know why this has to return a DateTime
}
//Search for missing date values in weather table
context.SaveChanges();
int count = context.WeatherReadings.Count();
System.Diagnostics.Debug.WriteLine(recordsAdded + " records were added. " + count + " total records");
}
/*
* Input: DateTime, DateTime, KeyValuePair<string, List>, SolarContext
* DateTime fd - firstDate in CSV files
* DateTime ld - lastDate in CSV files
* KVP<string, List> - List to scan
* SolarContext - database
* Output: int - Number of missing records found
* Desc: Scans a List found in KVP<string, list>, adds missing values to database
*/
static int addMissingArray(DateTime fd, DateTime ld, KeyValuePair <string, List <PowerReading> > s, SolarContext context)
{
DateTime currDate = fd;
int index = 0;
int records = 0;
while (currDate != ld)
{
if (index > s.Value.Count) //List doesnt include readings to end of date
{
PowerReading r = new PowerReading()
{
DateAndTime = currDate,
SourceKey = s.Key,
DC_Power = -1.0,
AC_Power = -1.0,
DailyYield = -1.0,
TotalYield = -1.0
};
context.PowerReadings.Add(r);
records++;
}
else if (currDate != s.Value[index].DateAndTime) //List missing readings in middle
{
PowerReading r = new PowerReading()
{
DateAndTime = currDate,
SourceKey = s.Key,
DC_Power = -1.0,
AC_Power = -1.0,
DailyYield = -1.0,
TotalYield = -1.0
};
context.PowerReadings.Add(r);
records++;
}
else
{
index++; //Continue
}
currDate = currDate.AddMinutes(15);
}
//Search for missing date values in power table
context.SaveChanges();
System.Diagnostics.Debug.WriteLine("Finished power array " + s.Key);
return(records);
}
/*
* Input: String file_name, SolarContext database
* Output: None
* Desc: Reads power reading data from file into database
*/
static void ReadInGenerationData(string file_in, SolarContext context)
{
try
{
using (var reader = new StreamReader("Data/" + file_in))
{
string line;
System.Diagnostics.Debug.WriteLine("Seeding Database with " + file_in);
reader.ReadLine(); //First line is human stuff
//Quick lookup for already entered Source_Key's
List <string> sourceKeys = new List <string>();
while ((line = reader.ReadLine()) != null)
{
var values = line.Split(',');
/*
* PowerSource {
* PK int PowerSourceID, auto increment
* string SourceKey,
* int PlantNumber }
*
* PowerReading {
* PK int PowerReadingID, auto increment
* DateTime DateAndTime,
* string SourceKey,
* double DC_Power,
* double AC_Power,
* double Irradiation }
*/
DateTime dateTime = DateTime.Parse(values[0]);
int plantNum = int.Parse(values[1]);
string sourceKey = values[2];
double dc_power = double.Parse(values[3]);
double ac_power = double.Parse(values[4]);
double daily_y = double.Parse(values[5]);
double total_y = double.Parse(values[6]);
//Add a new PowerSource to database if it doesnt exist already
if (sourceKeys.FirstOrDefault(s => s.Equals(sourceKey)) == null)
{
PowerSource s = new PowerSource()
{
SourceKey = sourceKey,
PlantNumber = plantNum
};
context.PowerSources.Add(s);
sourceKeys.Add(sourceKey);
}
PowerReading r = new PowerReading()
{
DateAndTime = dateTime,
SourceKey = sourceKey,
DC_Power = dc_power,
AC_Power = ac_power,
DailyYield = daily_y,
TotalYield = total_y
};
context.PowerReadings.Add(r);
}
context.SaveChanges();
System.Diagnostics.Debug.WriteLine("Finished successfully.");
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Error reading from file '" + file_in + "': " + e.Message);
}
}
public SolarController(SolarContext context)
{
_context = context;
}