public ChartsModel(device _device)
{
DateTime lastRecordTime = Startup.db.Database.SqlQuery <DateTime>(
@"SELECT TOP 1 stamp " +
"FROM record WHERE id_dev = " + _device.id +
" ORDER BY stamp DESC"
).Single(); //data + godzina ostatniego odczytu
this.x10m = new ArrayList();
this.y10m = new ArrayList();
this.x1h = new ArrayList();
this.y1h = new ArrayList();
this.x6h = new ArrayList();
this.y6h = new ArrayList();
this.x1d = new ArrayList();
this.y1d = new ArrayList();
this.x7d = new ArrayList();
this.y7d = new ArrayList();
for (int i = 0; i < 10; i++) // i - dzielnik wykresu
{
Decimal tempConsumption10m = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
" AND stamp BETWEEN DATEADD(MINUTE," + (i * 10 + 10) * -1 + ", getdate()) AND DATEADD(MINUTE," + -i * 10 + ", getdate())").DefaultIfEmpty(0).Single() ?? 0; //zuzycie prądu 10 min
this.y10m.Add(tempConsumption10m);
this.x10m.Add(lastRecordTime.AddMinutes(-i * 10).ToShortTimeString());
Decimal tempConsumption1h = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
" AND stamp BETWEEN DATEADD(HOUR," + (i + 1) * -1 + ", getdate()) AND DATEADD(HOUR," + -i + ", getdate())").DefaultIfEmpty(0).Single() ?? 0; //zuzycie prądu 1h
this.y1h.Add(tempConsumption1h);
this.x1h.Add(lastRecordTime.AddHours(-i).ToShortTimeString());
Decimal tempConsumption6h = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
" AND stamp BETWEEN DATEADD(HOUR," + (i * 6 + 1) * -1 + ", getdate()) AND DATEADD(HOUR," + -i * 6 + ", getdate())").DefaultIfEmpty(0).Single() ?? 0; //zuzycie prądu 6h
this.y6h.Add(tempConsumption6h);
this.x6h.Add(lastRecordTime.AddHours(-i * 6).ToString("HH:mm dd/MM"));
Decimal tempConsumption1d = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
" AND stamp BETWEEN DATEADD(DAY," + (i + 1) * -1 + ", getdate()) AND DATEADD(DAY," + -i + ", getdate())").DefaultIfEmpty(0).Single() ?? 0; //zuzycie prądu 6h
this.y1d.Add(tempConsumption1d / 1000);
this.x1d.Add(lastRecordTime.AddDays(-i).ToString("dd/MM"));
}
x10m.Reverse();
y10m.Reverse();
x1h.Reverse();
y1h.Reverse();
x6h.Reverse();
y6h.Reverse();
x1d.Reverse();
y1d.Reverse();
}
public StatsViewModel(device _device, double kwhprice)
{
DateTime lastRecordTime = Startup.db.Database.SqlQuery <DateTime>(
@"SELECT TOP 1 stamp " +
"FROM record WHERE id_dev = " + _device.id +
" ORDER BY stamp DESC"
).Single(); //data + godzina ostatniego odczytu
Decimal lastVoltage = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT TOP 1 voltage
FROM record WHERE id_dev = " + _device.id +
" ORDER BY stamp DESC"
).Single() ?? 0; //ostatni odzyt napiecia
Decimal lastCurrentL1 = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT TOP 1 current_l1
FROM record WHERE id_dev = " + _device.id +
" ORDER BY stamp DESC"
).Single() ?? 0; //ostatni odzyt amperazu L1
Decimal lastCurrentL2 = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT TOP 1 current_l2
FROM record WHERE id_dev = " + _device.id +
" ORDER BY stamp DESC"
).Single() ?? 0; //ostatni odzyt amperazu L2
Decimal lastCurrentL3 = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT TOP 1 current_l3
FROM record WHERE id_dev = " + _device.id +
" ORDER BY stamp DESC"
).Single() ?? 0; //ostatni odzyt amperazu L3
Decimal last1hAvgVoltage = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT AVG(voltage)
AS avgVoltage
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(hh, -1, getdate()) AND GETDATE()").Single() ?? 0; //średnie napiecie 1h
Decimal last1hConsumption = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(hh, -1, getdate()) AND GETDATE()").Single() ?? 0; //zuzycie prądu 1h
Decimal last24hAvgVoltage = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT AVG(voltage)
AS avgVoltage
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(hh, -24, getdate()) AND GETDATE()").Single() ?? 0; //średnie napiecie 24h
Decimal last24hConsumption = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(hh, -24, getdate()) AND GETDATE()").Single() ?? 0; //zuzycie prądu 24h
Decimal last7dAvgVoltage = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT AVG(voltage)
AS avgVoltage
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(dd, -7, getdate()) AND GETDATE()").Single() ?? 0; //średnie napiecie 7d
Decimal last7dConsumption = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(dd, -7, getdate()) AND GETDATE()").Single() ?? 0; //zuzycie prądu 7d
Decimal last30dAvgVoltage = Startup.db.Database.SqlQuery <Decimal?>(
@"SELECT AVG(voltage)
AS avgVoltage
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(dd, -30, getdate()) AND GETDATE()").Single() ?? 0; //średnie napiecie 30d
Decimal last30dConsumption = Startup.db.Database.SqlQuery <int?>(
@"SELECT SUM(wh)
AS totalwh
FROM record
WHERE id_dev = " + _device.id +
"AND stamp BETWEEN DATEADD(dd, -30, getdate()) AND GETDATE()").Single() ?? 0; //zuzycie prądu 30d
this.lastRecordTime = lastRecordTime;
this.lastVoltage = Math.Round(lastVoltage);
this.currentPowerL1 = Math.Round((lastCurrentL1 * lastVoltage) / 1000, 2);
this.currentPowerL2 = Math.Round((lastCurrentL2 * lastVoltage) / 1000, 2);
this.currentPowerL3 = Math.Round((lastCurrentL3 * lastVoltage) / 1000, 2);
this.currentTotalPower = Math.Round((this.currentPowerL1 + this.currentPowerL2 + this.currentPowerL3), 2);
this.last1hVoltage = Math.Round(last1hAvgVoltage);
this.last1hConsumption = Math.Round(last1hConsumption / 1000, 2);
this.last1hCost = Math.Round(this.last1hConsumption * (Decimal)kwhprice, 2);
this.last24hVoltage = Math.Round(last24hAvgVoltage);
this.last24hConsumption = Math.Round(last24hConsumption / 1000, 2);
this.last24hCost = Math.Round(this.last24hConsumption * (Decimal)kwhprice, 2);
this.last7dVoltage = Math.Round(last7dAvgVoltage);
this.last7dConsumption = Math.Round(last7dConsumption / 1000, 2);
this.last7dCost = Math.Round(this.last7dConsumption * (Decimal)kwhprice, 2);
this.last30dVoltage = Math.Round(last30dAvgVoltage);
this.last30dConsumption = Math.Round(last30dConsumption / 1000, 2);
this.last30dCost = Math.Round(this.last30dConsumption * (Decimal)kwhprice, 2);
this.kwhPrice = (Decimal)kwhprice;
}