import logger

from sqlalchemy.sql import func

import datetime

class Query:

    """Objects of query class can be used to run specific queries."""

    def __init__(self, table):

        self.t = logger.Logger(table)

    def posts(self, date, dateRange):

        return self.t.lt.query.filter(self.t.lt.status == 3,

                                 self.t.lt.posted.between(date, dateRange)).count()

    def recordings(self, date, dateRange):

        return self.t.lt.query.filter(self.t.lt.posted.between(date,

                                                            dateRange)).count()

    def filter_by_title(self, title, date, dateRange):

        return self.t.lt.query.filter(self.t.lt.title.like(title+'%'),

                            self.t.lt.posted.between(date, dateRange)).count()

    def totalMinutes(self, channel, date, dateRange):

        query =  self.t.lt.query.with_entities(func.sum(self.t.lt.duration).label('sum')).filter(self.t.lt.dcontext == "callback", self.t.lt.channel.like(channel+'%'), self.t.lt.calldate.between(date, dateRange))

        sum = 0

        for res in query:

            if res.sum is not None:

                sum = res.sum/60

        return round(sum, 4)

    def average(self, date, dateRange):

        query = self.t.lt.query.with_entities(func.avg(self.t.lt.duration).label('average')).filter(self.t.lt.dcontext == "callback", self.t.lt.calldate.between(date, dateRange))

        average = 0

        for res in query:

            if res.average is not None:

                average = res.average/60

        return round(average, 4)

    def sum(self, date, dateRange):

        query = self.t.lt.query.with_entities(func.sum(self.t.lt.duration).label('sum')).filter(self.t.lt.dcontext == "callback", self.t.lt.calldate.between(date, dateRange))

        sum = 0

        for res in query:

            if res.sum is not None:

                sum = res.sum/60

        return round(sum, 4)

    def missedCalls(self, date, dateRange, modem=None):

        if modem is None:

            return self.t.lt.query.filter(((self.t.lt.dcontext == 'mobilink') |

                                     (self.t.lt.dcontext == 'mobilinktata')) &

                                    (self.t.lt.calldate.between(date, dateRange))).count()

        else:

            return self.t.lt.query.filter(self.t.lt.dcontext == modem, self.t.lt.calldate.between(date, dateRange)).count()

    def answeredCalls(self, date, dateRange):

        return self.t.lt.query.filter(self.t.lt.dcontext == 'callback',

                                    self.t.lt.calldate.between(date, dateRange)).count()

    def filter_calls_by_duration(self, date, dateRange, duration):

        return self.t.lt.query.filter(self.t.lt.dcontext == 'callback', self.t.lt.duration < duration, self.t.lt.calldate.between(date, dateRange)).count()

    def call_distribution(self, date, dateRange, dcontext):

        startTimeStamp = datetime.datetime.strptime(date, '%Y-%m-%d %H:%M:%S')

        endTimeStamp = datetime.datetime.strptime(dateRange, '%Y-%m-%d %H:%M:%S')

        result = self.t.lt.query.filter(self.t.lt.dcontext == dcontext, self.t.lt.calldate.between(date, dateRange))

        slots = {}

        while(startTimeStamp - endTimeStamp <= datetime.timedelta(0)):

            date1 = startTimeStamp

            date2 = startTimeStamp + datetime.timedelta(seconds=3600)

            startTimeStamp = date2

            if slots.has_key('{0}-{1}'.format(date1.strftime('%H'), date2.strftime('%H'))):

                slots['{0}-{1}'.format(date1.strftime('%H'), date2.strftime('%H'))] += result.filter(self.t.lt.calldate.between(date1, date2)).count()

            else:

                slots['{0}-{1}'.format(date1.strftime('%H'), date2.strftime('%H'))] = result.filter(self.t.lt.calldate.between(date1, date2)).count()

        maxLoad = max(slots, key = lambda x: slots.get(x) )

        minLoad = []

        for slot in slots:

            if min(slots, slots[slot]) == 0:

                minLoad.append(slot)

        return {"maxLoad": maxLoad, "maxCalls": slots[maxLoad], "minLoad": repr(minLoad)}

    def calls_unanswered(self, date, dateRange):

        return self.t.lt.query.filter(self.t.lt.dcontext=='default', self.t.lt.calldate.between(date,dateRange)).count()

    def max_duration_UC(self, date, dateRange):

        query = self.t.lt.query.with_entities(func.max(self.t.lt.duration).label('duration')).filter(self.t.lt.dcontext=='default', self.t.lt.calldate.between(date,dateRange))

        duration = 0

        for result in query:

            if result.duration is not None:

                duration = result.duration

        return duration