EvalMeasurement/addData.py

'''
Created on 12.07.2015

@author: wn
'''


import xlrd
import xlwt
from xlutils.copy import copy
import pymongo
from bson.son import SON
import datetime

DBHOST = '172.16.2.18'
DBNAME = 'iot'
DBCOLL = 'iot'
FILENAME = 'measurementData.xls'

TOTALIZERS = ['electricity', 'light', 'laundry', 'dryer', 'dishwasher', 'computer', 'freezer']

class DataAdder(object):
    def __init__(self, dbhost, dbname, dbcoll, filename, totalizers):
        self.dbhost = dbhost
        self.dbname = dbname
        self.dbcoll = dbcoll
        self.filename = filename
        self.totalizers = totalizers
        
        self.client = pymongo.MongoClient(dbhost)
        self.db = self.client[self.dbname]
        self.coll = self.db[self.dbcoll]
        
        self.date_format = xlwt.XFStyle()
        self.date_format.num_format_str = 'dd.mm.yyyy'

    def openExcel(self):
        self.excelR = None
        while not self.excelR:
            try:
                print('Trying to open Excel file')
                self.excelR = xlrd.open_workbook(self.filename, formatting_info=True)
                self.sheetR = self.excelR.sheet_by_index(0) 
                self.numOfRows = self.sheetR.nrows
                self.excelW = copy(self.excelR) 
                self.sheetW = self.excelW.get_sheet(0) 
                print('Done.')
            except IOError:
                print('Excel file does not exist, create one')
                excel = xlwt.Workbook(encoding='ascii', style_compression=0)
                sheet = excel.add_sheet('data')
                sheet.write(0, 0, 'Date')
                for i, t in enumerate(self.totalizers):
                    sheet.write(0, i+1, t)
                excel.save(self.filename)

    def findStartDate(self):
        if self.numOfRows == 1:
            startDate = datetime.datetime(1900, 1, 1)
        else:
            print('numOfRows: %s' % self.numOfRows)
            lastDateCell = self.sheetR.cell(self.numOfRows-1, 0)
            print('Date: %s' % lastDateCell)
            startDate = xlrd.xldate.xldate_as_datetime(lastDateCell.value, self.excelR.datemode)
            startDate += datetime.timedelta(days=1)
            print('Date: %s' % startDate)
        return startDate
        
    def collectData(self, startDate):
        cursor = self.coll.aggregate(
            [
                {"$match":{"metadata.device":"MeterbusHub", "metadata.name": {"$in": self.totalizers}, "metadata.day": {"$gte": startDate}}},
                {"$sort":SON([("metadata.day",1), ("metadata.seconds",1)])},
                {"$group":{"_id":{"device":"$metadata.name","day":"$metadata.day"}, "first":{"$first":"$data.energy"},"last":{"$last":"$data.energy"}}},
                {"$sort":{"_id":1}},
                {"$project": {"energy":{"$subtract":["$last","$first"]}}}
            ],
            useCursor = True, allowDiskUse = True
        )    
        return cursor

    def run(self):
        print("stage 1")
        self.openExcel()
        print("stage 2")
        startDate = self.findStartDate()
        print("stage 3")
        cursor = self.collectData(startDate)
        print("stage 4")
        
        m = {}
        oldDay = None
        startDate = None
        for c in cursor:
            day = c['_id']['day']
            if startDate is None:
                startDate = day
            device = c['_id']['device']
            if not m.has_key(day):
                m[day] = {}
            m[day][device] = c['energy']
        print("stage 5")
        print(m)
        
        endDate = datetime.datetime.now()
        endDate -= datetime.timedelta(days=1)  # yesterday
        itemDate = startDate
        rowsWritten = 0
        while (itemDate < endDate):
            print(itemDate)
            self.sheetW.write(self.numOfRows, 0, itemDate, self.date_format)
            rowsWritten += 1
            for pos, totalizer in enumerate(self.totalizers):
                pos += 1
                try:
                    energy = m[itemDate][totalizer]
                except KeyError:
                    energy = None
                print("%s, %s: %s" % (pos, totalizer, energy))
                self.sheetW.write(self.numOfRows, pos, energy)
                self.excelW.save(self.filename)
            self.numOfRows += 1
            itemDate += datetime.timedelta(days=1)
        
        print("Done, rows written %s" % rowsWritten)
        

def saveWorkSpace(fields):
    rb = xlrd.open_workbook('accounts.xls',formatting_info=True)
    r_sheet = rb.sheet_by_index(0) 
    r = r_sheet.nrows
    wb = copy(rb) 
    sheet = wb.get_sheet(0) 
    sheet.write(r,0,fields['name'])
    sheet.write(r,1,fields['phone'])
    sheet.write(r,2,fields['email'])
    wb.save('accounts.xls')
    print 'Wrote accounts.xls'
    
    
dataAdder = DataAdder(DBHOST, DBNAME, DBCOLL, FILENAME, TOTALIZERS)
dataAdder.run()