pyDWH

class SourceEmployee(object):
    """
    Represents a row data for src N.employee table:
    id, name, address, salary, manager, did, jid, gender, dob
    """
    def   init  (self, *args):
        assert len(args) == 9
        self.id = args[0]
        self.name = args[1]
        self.address = args[2]
        self.salary = args[3]
        self.manager = args[4]
        self.did = args[5]
        self.jid = args[6]
        self.gender = args[7]
        self.dob = args[8]

    def employees(self, source employees, cid):
        """
        Transforms source employees objects.
        """
        for employee in source employees:
            yield TargetEmployee(
                employee.id, # Reference use only, NOT INSERTED INTO DB
                employee.gender,
                employee.did,
                employee.jid,
                self.tid,
                cid,
                self. apply exchange rate(employee.salary),
                employee.manager,
                get age(employee.dob, self.today))

def get age(dob, current date):
"""
Returns person's age as of current date given the date of birth.
"""
    try:
        bday = dob.replace(year=current date.year)
    except ValueError:
        # This happens if dob falls on 29th Feb and the current year is
        # not a leap year.
        bday = dob.replace(year=current date.year, day=dob.day - 1)
    if bday > current date:
        age = current date.year - dob.year - 1
    else:
        age = current date.year - dob.year
    return age

class TargetEmployee(object):
    """
    Represents a row data for tgt N.fact employee table:
    id, gender, did, jid, tid, cid, salary, manager, age
    """
    def   init  (self, *args):
        assert len(args) == 9
        self.id = args[0]
        self.gender = args[1]
        self.did = args[2]
        self.jid = args[3]
        self.tid = args[4]
        self.cid = args[5]
        self.salary = args[6]
        self.manager = args[7]
        self.age = args[8]

class Query(object):
    """
    Performs database queries to fetch all the required lines data in order to
    generate a chart.
    """
    def   init  (self, database, chart):
        self.database = database
        self.chart = chart

    def  split line range(self, line range):
        """
        Splits a line range into a tuple representing start and end of the
        range. This is typically used for age range.
        """
        line range = line range.split("-")
        if len(line range) > 1:
            (start, end) = line range
        else:
            start = line range[0]
            end = start
        return (start, end)

    def  get tables(self):
        """
        Returns a list of tables that forms the FROM clause for a query given
        the data type.
        """
        if self.chart['data type'] == JOBROLE:
            return "fact employee e, dim time d, dim jobrole j"
        return "fact employee e, dim time d"

    def  get time limit(self):
        """
        Returns a time limit string that forms the WHERE clause for a query
        given the x-axis inputs.
        """
        x unit = self.chart['x']['unit'].upper()  # x-axis for time
        x limit = self.chart['x']['limit']  # (start, end) or None
        time limit = "e.tid = d.id AND d.time interval = '{}'".format(x unit)
        if x limit is not None:
            time limit += " AND d.date >= '{}' AND d.date <= '{}'".format(
                x limit[0], x limit[1])
        return time limit

    def  get data limit(self, line range):
        """
        Returns a data limit string that forms the WHERE clause for a query
        given the data label.
        """
        data limit = ""
        data type = self.chart['data type']
        if data type == AGE:
            (start, end) = self. split line range(line range)
            data limit = "e.age >= {} AND e.age <= {}".format(start, end)
        elif data type == COMPANY:
            data limit = "e.cid = {}".format(int(line range))
        elif data type == GENDER:
            data limit = "e.gender = '{}'".format(line range.upper())
        elif data type == JOBROLE:
            data limit = "e.jid = j.id AND j.name = '{}'".format(line range)
        elif data type == MANAGER:
            data limit = "e.manager = {}".format(int(line range))
        return data limit

    def  get data select(self, context):
        """
        Returns a select statement that forms a joinable query given the
        unit for y-axis, i.e. median or total.
        """
        query = ""
        if self.chart['y']['unit'] == MEDIAN:
            query = (
                "(SELECT date AS line{idx} date, "
                "AVG(salary) AS line{idx} value "
                "FROM ( "
                "SELECT t1.row, t1.salary, t1.date FROM ( "
                "SELECT IF(@prev != date, @rownum := 1, @rownum := @rownum + 1) AS row, @prev := date AS date, salary "
                "FROM ( "
                "SELECT d.date, e.salary "
                "FROM {tables} "
                "WHERE {data limit} AND {time limit} "
                "ORDER BY date, salary "
                ") ordered, (SELECT @rownum := 0, @prev := NULL) reset "
                ") AS t1 INNER JOIN "
                "( "
                "SELECT COUNT(*) AS total rows, d.date AS date "
                "FROM {tables} "
                "WHERE {data limit} AND {time limit} "
                "GROUP BY date "
                ") AS t2 "
                "ON t1.date = t2.date "
                "AND t1.row >= t2.total rows / 2 AND t1.row <= ((t2.total rows / 2) + 1) "
                ") median "
                "GROUP BY date ORDER BY date) line{idx} ")
        elif self.chart['y']['unit'] == TOTAL:
            query = (
                "(SELECT d.date AS line{idx} date, "
                "SUM(e.salary) AS line{idx} value "
                "FROM {tables} "
                "WHERE {data limit} AND {time limit} "
                "GROUP BY date ORDER BY date) line{idx} ")
        return query.format(idx=context['idx'],
                            tables=context['tables'],
                            data limit=context['data limit'],
                            time limit=context['time limit'])

    def get data rows(self, line ranges):
        """
        Returns data rows for use in rendering the chart given the line ranges.
        """
        left column = "COALESCE("  # Header for first column
        right columns = ""  # Headers for subsequent columns
        joins = " "
        tables = self. get tables()
        time limit = self. get time limit()

        for idx, line range in enumerate(line ranges, 1):
            data limit = self. get data limit(line range)

            joins += self. get data select({
                'idx': idx,
                'tables': tables,
                'data limit': data limit,
                'time limit': time limit,
            })

            if idx > 1:
                joins += "ON line{} date = line{} date ".format(idx - 1, idx)
            if idx != len(line ranges):
                joins += "LEFT JOIN "

            left column += "line{} date".format(idx)
            if idx < len(line ranges):
                left column += ", "
            else:
                left column += ") AS date,"

            right columns += "line{} value".format(idx)
            if idx < len(line ranges):
                right columns += ", "

        query = "SELECT {} {} FROM".format(left column, right columns) + joins
        logging.debug("query={}".format(query))

        try:
            self.database.cursor.execute(query)
        except ProgrammingError as err:
            raise QueryError(err)
        rows = self.database.cursor.fetchall()

        logging.debug("rows={}".format(rows))
        return rows