285 lines
11 KiB
C#
285 lines
11 KiB
C#
using InnovEnergy.App.Backend.Database;
|
||
using InnovEnergy.App.Backend.DataTypes;
|
||
|
||
namespace InnovEnergy.App.Backend.Services;
|
||
|
||
/// <summary>
|
||
/// Ingests daily energy totals into the DailyEnergyRecord SQLite table.
|
||
/// Data source priority: JSON (S3) → xlsx fallback.
|
||
/// Runs automatically at 01:00 UTC daily. Can also be triggered manually via the
|
||
/// IngestDailyData API endpoint.
|
||
/// </summary>
|
||
public static class DailyIngestionService
|
||
{
|
||
private static readonly String TmpReportDir =
|
||
Environment.CurrentDirectory + "/tmp_report/";
|
||
|
||
private static Timer? _dailyTimer;
|
||
|
||
/// <summary>
|
||
/// Starts the daily scheduler. Call once on app startup.
|
||
/// Ingests xlsx data at 01:00 UTC every day.
|
||
/// </summary>
|
||
public static void StartScheduler()
|
||
{
|
||
var now = DateTime.UtcNow;
|
||
var next = now.Date.AddDays(1).AddHours(1); // 01:00 UTC tomorrow
|
||
|
||
_dailyTimer = new Timer(
|
||
_ =>
|
||
{
|
||
try
|
||
{
|
||
IngestAllInstallationsAsync().GetAwaiter().GetResult();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Console.Error.WriteLine($"[DailyIngestion] Scheduler error: {ex.Message}");
|
||
}
|
||
},
|
||
null, next - now, TimeSpan.FromDays(1));
|
||
|
||
Console.WriteLine($"[DailyIngestion] Scheduler started. Next run: {next:yyyy-MM-dd HH:mm} UTC");
|
||
}
|
||
|
||
/// <summary>
|
||
/// Ingests xlsx data for all SodioHome installations. Safe to call manually.
|
||
/// </summary>
|
||
public static async Task IngestAllInstallationsAsync()
|
||
{
|
||
Console.WriteLine($"[DailyIngestion] Starting ingestion for all SodioHome installations...");
|
||
|
||
var installations = Db.Installations
|
||
.Where(i => i.Product == (Int32)ProductType.SodioHome && i.Device != 3) // Skip Growatt (device=3)
|
||
.ToList();
|
||
|
||
foreach (var installation in installations)
|
||
{
|
||
try
|
||
{
|
||
await IngestInstallationAsync(installation.Id);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Console.Error.WriteLine($"[DailyIngestion] Failed for installation {installation.Id}: {ex.Message}");
|
||
}
|
||
}
|
||
|
||
Console.WriteLine($"[DailyIngestion] Ingestion complete.");
|
||
}
|
||
|
||
/// <summary>
|
||
/// Ingests data for one installation. Tries JSON (S3) and xlsx.
|
||
/// Both sources are tried — idempotency checks prevent duplicates.
|
||
/// JSON provides recent data; xlsx provides historical data.
|
||
/// </summary>
|
||
public static async Task IngestInstallationAsync(Int64 installationId)
|
||
{
|
||
await TryIngestFromJson(installationId);
|
||
IngestFromXlsx(installationId);
|
||
}
|
||
|
||
/// <summary>
|
||
/// Ingests S3 JSON data for a specific date range. Used by report services
|
||
/// as a fallback when SQLite has no records for the requested period.
|
||
/// Idempotent — skips dates already in DB.
|
||
/// </summary>
|
||
public static async Task IngestDateRangeAsync(Int64 installationId, DateOnly fromDate, DateOnly toDate)
|
||
{
|
||
var installation = Db.GetInstallationById(installationId);
|
||
if (installation is null) return;
|
||
|
||
var newDaily = 0;
|
||
var newHourly = 0;
|
||
|
||
for (var date = fromDate; date <= toDate; date = date.AddDays(1))
|
||
{
|
||
var isoDate = date.ToString("yyyy-MM-dd");
|
||
if (Db.DailyRecordExists(installationId, isoDate))
|
||
continue;
|
||
|
||
var content = await AggregatedJsonParser.TryReadFromS3(installation, isoDate);
|
||
if (content is null) continue;
|
||
|
||
var (d, h) = IngestJsonContent(installationId, content);
|
||
newDaily += d;
|
||
newHourly += h;
|
||
}
|
||
|
||
if (newDaily > 0 || newHourly > 0)
|
||
Console.WriteLine($"[DailyIngestion] Installation {installationId} (S3 date-range {fromDate:yyyy-MM-dd}–{toDate:yyyy-MM-dd}): {newDaily} day(s), {newHourly} hour(s) ingested.");
|
||
}
|
||
|
||
private static async Task<Boolean> TryIngestFromJson(Int64 installationId)
|
||
{
|
||
var newDaily = 0;
|
||
var newHourly = 0;
|
||
|
||
var installation = Db.GetInstallationById(installationId);
|
||
if (installation is null) return false;
|
||
|
||
// Try S3 for recent days (yesterday + today), skip if already in DB
|
||
for (var daysBack = 0; daysBack <= 1; daysBack++)
|
||
{
|
||
var date = DateOnly.FromDateTime(DateTime.UtcNow.AddDays(-daysBack));
|
||
var isoDate = date.ToString("yyyy-MM-dd");
|
||
|
||
if (Db.DailyRecordExists(installationId, isoDate))
|
||
continue;
|
||
|
||
var content = await AggregatedJsonParser.TryReadFromS3(installation, isoDate);
|
||
if (content is null) continue;
|
||
|
||
var (d, h) = IngestJsonContent(installationId, content);
|
||
newDaily += d;
|
||
newHourly += h;
|
||
}
|
||
|
||
if (newDaily > 0 || newHourly > 0)
|
||
Console.WriteLine($"[DailyIngestion] Installation {installationId} (JSON): {newDaily} day(s), {newHourly} hour(s) ingested.");
|
||
|
||
return newDaily > 0 || newHourly > 0;
|
||
}
|
||
|
||
public static (Int32 daily, Int32 hourly) IngestJsonContent(Int64 installationId, String content)
|
||
{
|
||
var newDaily = 0;
|
||
var newHourly = 0;
|
||
|
||
foreach (var day in AggregatedJsonParser.ParseDaily(content))
|
||
{
|
||
if (Db.DailyRecordExists(installationId, day.Date))
|
||
continue;
|
||
|
||
Db.Create(new DailyEnergyRecord
|
||
{
|
||
InstallationId = installationId,
|
||
Date = day.Date,
|
||
PvProduction = day.PvProduction,
|
||
LoadConsumption = day.LoadConsumption,
|
||
GridImport = day.GridImport,
|
||
GridExport = day.GridExport,
|
||
BatteryCharged = day.BatteryCharged,
|
||
BatteryDischarged = day.BatteryDischarged,
|
||
CreatedAt = DateTime.UtcNow.ToString("o"),
|
||
});
|
||
newDaily++;
|
||
}
|
||
|
||
foreach (var hour in AggregatedJsonParser.ParseHourly(content))
|
||
{
|
||
var dateHour = $"{hour.DateTime:yyyy-MM-dd HH}";
|
||
if (Db.HourlyRecordExists(installationId, dateHour))
|
||
continue;
|
||
|
||
Db.Create(new HourlyEnergyRecord
|
||
{
|
||
InstallationId = installationId,
|
||
Date = hour.DateTime.ToString("yyyy-MM-dd"),
|
||
Hour = hour.Hour,
|
||
DateHour = dateHour,
|
||
DayOfWeek = hour.DayOfWeek,
|
||
IsWeekend = hour.IsWeekend,
|
||
PvKwh = hour.PvKwh,
|
||
LoadKwh = hour.LoadKwh,
|
||
GridImportKwh = hour.GridImportKwh,
|
||
BatteryChargedKwh = hour.BatteryChargedKwh,
|
||
BatteryDischargedKwh = hour.BatteryDischargedKwh,
|
||
BattSoC = 0,
|
||
CreatedAt = DateTime.UtcNow.ToString("o"),
|
||
});
|
||
newHourly++;
|
||
}
|
||
|
||
return (newDaily, newHourly);
|
||
}
|
||
|
||
private static void IngestFromXlsx(Int64 installationId)
|
||
{
|
||
if (!Directory.Exists(TmpReportDir))
|
||
{
|
||
Console.WriteLine($"[DailyIngestion] tmp_report directory not found, skipping.");
|
||
return;
|
||
}
|
||
|
||
var xlsxFiles = Directory.GetFiles(TmpReportDir, $"{installationId}*.xlsx");
|
||
if (xlsxFiles.Length == 0)
|
||
{
|
||
Console.WriteLine($"[DailyIngestion] No xlsx found for installation {installationId}, skipping.");
|
||
return;
|
||
}
|
||
|
||
var newDailyCount = 0;
|
||
var newHourlyCount = 0;
|
||
var totalParsed = 0;
|
||
|
||
foreach (var xlsxPath in xlsxFiles.OrderBy(f => f))
|
||
{
|
||
List<DailyEnergyData> days;
|
||
try { days = ExcelDataParser.Parse(xlsxPath); }
|
||
catch (Exception ex)
|
||
{
|
||
Console.Error.WriteLine($"[DailyIngestion] Failed to parse daily {Path.GetFileName(xlsxPath)}: {ex.Message}");
|
||
continue;
|
||
}
|
||
|
||
totalParsed += days.Count;
|
||
|
||
foreach (var day in days)
|
||
{
|
||
if (Db.DailyRecordExists(installationId, day.Date))
|
||
continue;
|
||
|
||
Db.Create(new DailyEnergyRecord
|
||
{
|
||
InstallationId = installationId,
|
||
Date = day.Date,
|
||
PvProduction = day.PvProduction,
|
||
LoadConsumption = day.LoadConsumption,
|
||
GridImport = day.GridImport,
|
||
GridExport = day.GridExport,
|
||
BatteryCharged = day.BatteryCharged,
|
||
BatteryDischarged = day.BatteryDischarged,
|
||
CreatedAt = DateTime.UtcNow.ToString("o"),
|
||
});
|
||
newDailyCount++;
|
||
}
|
||
|
||
List<HourlyEnergyData> hours;
|
||
try { hours = ExcelDataParser.ParseHourly(xlsxPath); }
|
||
catch (Exception ex)
|
||
{
|
||
Console.Error.WriteLine($"[DailyIngestion] Failed to parse hourly {Path.GetFileName(xlsxPath)}: {ex.Message}");
|
||
continue;
|
||
}
|
||
|
||
foreach (var hour in hours)
|
||
{
|
||
var dateHour = $"{hour.DateTime:yyyy-MM-dd HH}";
|
||
if (Db.HourlyRecordExists(installationId, dateHour))
|
||
continue;
|
||
|
||
Db.Create(new HourlyEnergyRecord
|
||
{
|
||
InstallationId = installationId,
|
||
Date = hour.DateTime.ToString("yyyy-MM-dd"),
|
||
Hour = hour.Hour,
|
||
DateHour = dateHour,
|
||
DayOfWeek = hour.DayOfWeek,
|
||
IsWeekend = hour.IsWeekend,
|
||
PvKwh = hour.PvKwh,
|
||
LoadKwh = hour.LoadKwh,
|
||
GridImportKwh = hour.GridImportKwh,
|
||
BatteryChargedKwh = hour.BatteryChargedKwh,
|
||
BatteryDischargedKwh = hour.BatteryDischargedKwh,
|
||
BattSoC = hour.BattSoC,
|
||
CreatedAt = DateTime.UtcNow.ToString("o"),
|
||
});
|
||
newHourlyCount++;
|
||
}
|
||
}
|
||
|
||
Console.WriteLine($"[DailyIngestion] Installation {installationId} (xlsx): {newDailyCount} new day(s), {newHourlyCount} new hour(s) ingested ({totalParsed} days across {xlsxFiles.Length} file(s)).");
|
||
}
|
||
}
|