Ejemplo n.º 1
0
        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();
        }
Ejemplo n.º 2
0
        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;
        }