dXqmYJbk

1import sys, time
2import glob
3import logging
4import logging.handlers
5import sqlite3
6from datetime import datetime
7from sbLog import *
8
9daySecs = 24*3600
10
11def midNight(ofDay):
12    tup = time.localtime(ofDay and ofDay or time.time())
13    return time.mktime((tup.tm_year, tup.tm_mon, tup.tm_mday, 0, 0, 0, tup.tm_wday,tup.tm_yday, tup.tm_isdst))
14
15def strTime(etime):
16    return time.asctime(time.localtime(etime))
17
18class InverterDb(object):
19    def __init__(self, db):
20        global logger
21        self.invList = dict()
22        self.conn = sqlite3.connect(db)
23        self.lastMode = None
24    def saveData(self, data):
25        global logger
26        if data[5] != 'Mpp' and self.lastMode == data[5]: 
27            print"skipping save for mode", data[5]
28            return
29        self.lastMode = data[5]
30        tries = 3
31        while tries > 0:
32            tries = tries - 1
33            try:
34                self.conn.execute('insert into pvData values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', data)
35                self.conn.commit()
36                return
37            except:
38                logger.info( 'db insert failed: ' + sys.exc_info()[1].message)
39                time.sleep(1)
40    def buildStats(self, field, fromT, toT):
41        #print(time.asctime(time.localtime(toT)))
42        sql = 'select MAX(%s) - MIN(%s) from pvData where time > %d and  time < %d' %\
43            (field, field, fromT, toT)
44        #print('bldStats', strTime(fromT), strTime(toT),sql)
45        cur = self.conn.cursor()
46        cur.execute(sql)
47        return cur.fetchone()
48    def firstDate(self):
49        cur = self.conn.cursor()
50        cur.execute('select time from pvData limit 1')
51        return cur.fetchone()[0]
52
53class InverterSummaryDb(object):
54    def __init__(self, dbSumary, dataDb):
55        self.conn = sqlite3.connect(dbSumary)
56        #self.create()
57        self.dataDb = dataDb
58        self.baseFields = { 'E_Total': 'E_Day', 'h_Total':'H_Day', 'Pac':'PmaxDay'}
59        self.nextSumDueAt = self.lastSummaryAt() or self.dataDb.firstDate()
60    def buildSummaryForDay(self, date):
61        global logger
62        toT = date+daySecs
63        for liveFld, sumFld in self.baseFields.iteritems():
64            summary = self.dataDb.buildStats(liveFld, date, toT)
65            #print liveFld, summary[0]
66            #continue
67            try:
68                sql = "insert into pvSummary values (?, ?, ?)"
69                self.conn.execute(sql, (date, sumFld, summary[0]))
70            except sqlite3.IntegrityError:
71                sql = "update pvSummary set value = (?) where (time = ? and field = ?)"
72                self.conn.execute(sql, (summary[0], date, sumFld))
73            except:
74                print( 'db Summary insert failed: ' + sys.exc_info()[1].message)
75                logger.info( 'db Summary insert failed: ' + sys.exc_info()[1].message)
76        self.conn.commit()
77        return toT
78    def buildSummaryAll(self):
79        while self.nextSumDueAt < time.time() - daySecs:
80            self.nextSumDueAt = midNight(self.buildSummaryForDay(self.nextSumDueAt))
81            #print('Next Summary due', strTime(self.nextSumDueAt))
82        return self.nextSumDueAt
83    def lastSummaryAt(self):
84        c = self.conn.cursor()
85        c.execute('select time from pvSummary order by time desc limit 1')
86        rslt = c.fetchone()
87        return rslt and rslt[0] or midNight(None)
88    def summaryAt(self, day):
89        c = self.conn.cursor()
90        fromT = midNight(day)
91        c.execute('select * from pvSummary where (time >= ? and time <= ?)', (fromT, fromT + daySecs))
92        rslt = {'day': strTime(fromT)}
93        data = c.fetchall()
94        for row in data:
95            rslt[row[1]] = row[2]
96        return rslt
97    def create(self):
98        self.conn.execute(''' drop table pvSummary ''')
99        self.conn.execute(''' create table if not exists pvSummary (time numeric KEY, field text, value real) ''')
100        self.conn.execute(''' create unique index if not exists idx_time_fld on pvSummary (time, field) ''')
101
102testSummary = None
103if testSummary:
104    setupLogger('sblog.txt')
105    db = InverterDb('k:/sb/sb.db')
106    dbSummary = 'k:/sb/sbSummary.db'
107    sumDb = InverterSummaryDb(dbSummary, db)
108    midnight = midNight(time.time())
109    last = sumDb.lastSummaryAt()
110    print(time.asctime(time.localtime(last)))
111    #
112    sumDb.buildSummaryAll()
113    print sumDb.summaryAt(midnight-daySecs)