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; }