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, date, dateRange, channel=None):

        query =  self.t.lt.query.with_entities(

            func.sum(self.t.lt.billsec).label('sum')).filter(

                self.t.lt.dcontext == "callback",

                self.t.lt.disposition == "ANSWERED",

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

        if channel is not None:

            query = query.filter(self.t.lt.channel.like(channel+'%'))

        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.billsec).label('average')).filter(

                self.t.lt.dcontext == "callback",

                self.t.lt.disposition == "ANSWERED",

                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.billsec).label('sum')).filter(

    #             self.t.lt.dcontext == "callback",

    #             self.t.lt.disposition == "ANSWERED",

    #             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 unAccountedCalls(self, date, dateRange):

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

                                      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.disposition == 'ANSWERED',

                                      self.t.lt.calldate.between(date,

                                                                 dateRange)).count()

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

        return self.t.lt.query.filter(

            self.t.lt.dcontext == 'callback', self.t.lt.billsec < billsec,

            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.disposition == 'NO ANSWER',

                                      self.t.lt.dcontext == 'callback',

                                      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