1
import logger
2
from sqlalchemy.sql import func
3
4
5
class Query:
6
    """Objects of query class can be used to run specific queries."""
7
    def __init__(self, table):
8
        self.t = logger.Logger(table)
9
10
11
    def posts(self, date, dateRange):
12
        return self.t.lt.query.filter(self.t.lt.status == 3,
13
                                 self.t.lt.posted.between(date, dateRange)).count()
14
15
    def recordings(self, date, dateRange):
16
        return self.t.lt.query.filter(self.t.lt.posted.between(date,
17
                                                            dateRange)).count()
18
    def filter_by_title(self, title, date, dateRange):
19
        return self.t.lt.query.filter(self.t.lt.title.like(title+'%'),
20
                            self.t.lt.posted.between(date, dateRange)).count()
21
22
    def load(self, channel, date, dateRange):
23
        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))
24
        sum = 0
25
        for res in query:
26
           sum = res.sum/60
27
        return sum
28
29
    def average(self, date, dateRange):
30
        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))
31
        average = 0
32
        for res in query:
33
            average = res.average/60
34
        return average
35
36
    def sum(self, date, dateRange):
37
        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))
38
        sum = 0
39
        for res in query:
40
            sum = res.sum/60
41
        return sum
42
43
    def missedCalls(self, date, dateRange):
44
        return self.t.lt.query.filter(((self.t.lt.dcontext == 'mobilink') |
45
                                     (self.t.lt.dcontext == 'mobilinktata')) &
46
                                    (self.t.lt.calldate.between(date, dateRange))).count()
47
48
    def answeredCalls(self, date, dateRange):
49
        return self.t.lt.query.filter(self.t.lt.dcontext == 'callback',
50
                                    self.t.lt.calldate.between(date, dateRange)).count()