'''
Created on 11. 06. 2016

@author: wn
'''

from __future__ import print_function

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


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


PIPELINE_DISHWASHER = [
  {'$match':
    {
      'metadata.device':'MeterbusHub', 
      'metadata.timestamp': 
        {
          '$gt': datetime.datetime(2016, 6, 11, 19, 20, 0), 
          '$lt': datetime.datetime(2016, 6, 11, 21, 15, 0)
        },  
      'data.decodedTelegram.frame.comment':'Dishwasher'}
  },
  {'$project':
    {
      '_id':0,
      'timestamp': '$metadata.timestamp',
      'energy': '$data.energy',
      'power': '$data.power'
    }
  },
  {'$sort':
    {'metadata.timestamp':1}
  }
]

PIPELINE_LAUNDRY = [
  {'$match':
    {
      'metadata.device':'MeterbusHub', 
      'metadata.timestamp': 
        {
          '$gt': datetime.datetime(2016, 6, 12, 15, 0, 0), 
          '$lt': datetime.datetime(2016, 6, 12, 18, 0, 0)
        },  
      'data.decodedTelegram.frame.comment':'Laundry'}
  },
  {'$project':
    {
      '_id':0,
      'timestamp': '$metadata.timestamp',
      'energy': '$data.energy',
      'power': '$data.power'
    }
  },
  {'$sort':
    {'metadata.timestamp':1}
  }
]

PIPELINE_FREEZER_POWER = [
  {'$match':
    {
      'metadata.device':'MeterbusHub', 
      'metadata.timestamp': 
        {
          '$gt': datetime.datetime(2016, 6, 12, 0, 0, 0), 
          '$lt': datetime.datetime(2016, 6, 12, 23, 59, 59)
        },  
      'data.decodedTelegram.frame.comment':'Freezer'}
  },
  {'$project':
    {
      '_id':0,
      'timestamp': '$metadata.timestamp',
      'energy': '$data.energy',
      'power': '$data.power'
    }
  },
  {'$sort':
    {'metadata.timestamp':1}
  }
]

PIPELINE_LIGHT = [
  {'$match':
    {
      'metadata.device':'MeterbusHub', 
      'metadata.timestamp': 
        {
          '$gt': datetime.datetime(2016, 6, 23, 0, 0, 0), 
          '$lt': datetime.datetime(2016, 6, 23, 23, 59, 59)
        },  
      'data.decodedTelegram.frame.comment':'Light'}
  },
  {'$project':
    {
      '_id':0,
      'timestamp': '$metadata.timestamp',
      'energy': '$data.energy',
      'power': '$data.power'
    }
  },
  {'$sort':
    {'metadata.timestamp':1}
  }
]

PIPELINE_FREEZER_TEMPERATURE = [
  {'$match':
    {
      'metadata.device':'ModbusHub', 
      'metadata.Slave':'Thermometer',
      'metadata.timestamp': 
        {
          '$gt': datetime.datetime(2016, 6, 18, 0, 0, 0), 
          '$lt': datetime.datetime(2016, 6, 18, 23, 59, 59)
        }
    }  
  },
  {'$project':
    {
      '_id':0,
      'timestamp':'$metadata.timestamp',
      'temperature': '$data.t2'
    }
  },
  {'$sort':
    {'metadata.timestamp':1}
  }
]

PIPELINE_OUTDOOR_TEMPERATURE = [
  {'$match':
    {
      'metadata.device':'MeterbusHub', 
      'metadata.consumer': 'Thermometer',
      'metadata.timestamp': 
        {
          '$gt': datetime.datetime(2016, 6, 23, 0, 0, 0), 
          '$lt': datetime.datetime(2016, 6, 24, 23, 59, 59)
        }  
    }
  },
  {'$project':
    {
      '_id':0,
      'timestamp': '$metadata.timestamp',
      'temperature1': '$data.temperature1',
      'temperature2': '$data.temperature2',
      'temperature3': '$data.temperature3',
      'temperature4': '$data.temperature4'
    }
  },
  {'$sort':
    {'metadata.timestamp':1}
  }
]



PIPELINE = PIPELINE_OUTDOOR_TEMPERATURE

class Stopwatch():
  def millis(self):
    return time.time() * 1000
  def __init__(self, msg):
    self.msg = msg
  def __enter__(self):
    self.start = self.millis()
    print("{} ... ".format(self.msg), end="")
  def __exit__(self, type, value, traceback):
    duration = self.millis() - self.start
    print("{}".format(duration))
    

class DataAdder(object):
    def __init__(self, dbhost, dbname, dbcoll, filename, pipeline):
        self.dbhost = dbhost
        self.dbname = dbname
        self.dbcoll = dbcoll
        self.filename = filename
        self.pipeline = pipeline
        
        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.excelW = xlwt.Workbook(encoding='ascii', style_compression=0)
        self.sheetW = self.excelW.add_sheet('data')

        
    def collectData(self):
        cursor = self.coll.aggregate(
            self.pipeline,
            useCursor = True, allowDiskUse = True
        )    
        return cursor

    def run(self):
        with Stopwatch("stage 1: collecting data"):
          cursor = self.collectData()
        with Stopwatch("stage 2: opening excel"):
          self.openExcel()
        with Stopwatch("stage 3: writing excel"):
          for rownum, row in enumerate(cursor):
            if (rownum == 0):
              for pos, key in enumerate(row.iterkeys()):
                self.sheetW.write(rownum, pos, key)
            else:
              for pos, value in enumerate(row.itervalues()):
                if (type(value) not in [int, float]):
                  value = str(value)
                self.sheetW.write(rownum, pos, value)
        with Stopwatch("stage 4: saving excel"):
          self.excelW.save(self.filename)
        

    
    
dataAdder = DataAdder(DBHOST, DBNAME, DBCOLL, FILENAME, PIPELINE)
dataAdder.run()