#coding=utf-8
#suppress 'too many' lines warning
#pylint: disable-msg=C0302

"""
copyright (c) 2009, paketka@gmail.com et. al
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice,
      this list of conditions and the following disclaimer.

    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.

    * Neither the name of the <ORGANIZATION> nor the names of its contributors
      may be used to endorse or promote products derived from this software
      without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
"""

import datetime
import re

from django.shortcuts import get_object_or_404
from django.db import models
from django.db.models import Q
from django.http import Http404
from django.utils.translation import ugettext as _
from django.db.models.signals import pre_save
from django.contrib.localflavor.us.models import PhoneNumberField
from django.core.exceptions import ObjectDoesNotExist, MultipleObjectsReturned
from django.core.exceptions import FieldError
from django.db import connection
from common.models import AutoSlug, makeSlug
import settings
from django.core.urlresolvers import reverse

from GChartWrapper import Pie3D
from utils.charts import Votes, Accordance, get_percents

def term_to_id(term):
    """
    Function term_to_id(term)

    Retrieves a primary key from term instance of TermOfOffice class.
    If term is integer function just returns the term value.

    Returns term's instance key
    """
    if type(term) == type(0):
        term = get_object_or_404(TermOfOffice, id = term)
        return term.id
    else:
        return term.id


def person_to_id(person):
    """
    Function person_to_id(person)

    Retrieves a primary key from person instance of Person class.
    If person is integer function just returns the person value.

    Returns person's instance primary key
    """
    if type(person) == type(0):
        return person
    else:
        return person.id


def group_to_id(group):
    """
    Function group_to_id(group)

    Retrieves a primary key from person instance of Group class.
    If group is integer function just returns the group value.

    Returns group's instance primary key
    """
    if type(group) == type(0):
        return group
    else:
        return group.id


def poll_to_id(poll):
    """
    Function poll_to_id(poll)

    Retrieves a primary key from poll instance of Poll class.
    If poll is integer function just returns the poll value.

    Returns poll's instance primary key
    """
    if type(poll) == type(0):
        return poll
    else:
        return poll.id

class TermOfOffice(AutoSlug):
    """
    Class TermOfOffice(AutoSlug)

    The TermOfOffice represents a period of parliament between two pools.
    There are several attributes:
        yearStart   -   the first electoral year (string)
        yearEnd     -   the last electoral year (string)
        slug        -   string (yearStart_yearEnd), autogenerated from
                        yearStart, yearEnd
        termId      -   term ID assigned at psp.cz site
    """
    yearStart = models.CharField(_("Start Year"), max_length = 4)
    yearEnd = models.CharField(_("End Year"), max_length = 4)
    slug = models.SlugField(db_index = True, unique = True)
    termId = models.CharField(_("Term Id"), max_length = 4)
    slugs = {
        'slug' : ('yearStart', 'yearEnd', ),
    }
                            
    class Meta:
        """
        See http://docs.djangoproject.com/en/dev/ref/models/options/
        for details
        """
        #suppress class has no __init__ method
        #pylint: disable-msg=W0232
        #suppress too few public methods warning
        #pylint: disable-msg=R0903
        verbose_name = _("Term of Office")
        verbose_name_plural = _("Terms of Office")
    
    class Admin:
        """
        See http://docs.djangoproject.com/en/dev/intro/tutorial02
        for details
        """
        #suppress class has no __init__ method
        #pylint: disable-msg=W0232
        #suppress too few public methods warning
        #pylint: disable-msg=R0903
        pass


    def isComplete(self):
        """
        Method isComplete()

        Method returns true if the given term instance is complete.
        The complete term instance has yearEnd attribute filled in. It
        actually means the given electoral term is over.

        Returns True if term is complete (over)
        """
        return self.yearEnd != ''


    def getStart(self):
        """
        Method getStart()

        Returns yearStart attribute as integer.

        Returns yearEnd attribute as integer.
        """
        return int(self.yearStart)


    def getEnd(self):
        """
        Method getEnd()

        Method returns yearEnd attribute as integer. If there is no
        yearEnd set yet, which happens for current terms, the method
        will return the current year instead.

        Returns yearEnd attribute as integer, if there is no yearEnd
        attribute set, function will return a current year.
        """
        if self.yearEnd == '':
            return datetime.datetime.today().year
        else:
            return int(self.yearEnd)


    def getStartDate(self):
        """
        Method getStartDate()

        We have to create faked start date of given term, it will be Jan 1 of
        the yearStart.

        Returns datetime.date(int(yearEnd), day = 1, month = 1)
        """
        retVal = datetime.date(
                        day = 1,
                        month = 1,
                        year = self.getStart()
                    )
        return retVal
    

    def getEndDate(self):
        """
        Method getEndDate()

        We have to create faked end date of given term, it will be Dec 31
        of the yearEnd.

        Returns datetime.date(int(yearStart), day = 31, month = 12)
        """
        retVal = datetime.date(
                        day = 31,
                        month = 12,
                        year = self.getEnd()
                    )
        return retVal


    def getRange(self):
        """
        Method getRange()

        Method returns a date range represeting given term instance
        as a tupple of two datetime.date instances, represeting
        (lowerBound, upperBound)

        Returns a tupple (self.getStartDate(), self.getEndDate())
        """

        return (self.getStartDate(), self.getEndDate(),)

    def getTermsList(self):
        """
        Method getTermsList()

        Returns list of terms rendered to .html tags. It's optimized for views
        which are using using ui-jquery .css.  It renders links to terms, which
        are other than 'self'.
        
        Method is used in templates. It follows DRY principle, but violates
        MVC model - the presentation logic is implemented here.

        Returns list of strings with .html tags.
        """
        retVal = []
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        for t in TermOfOffice.objects.all():
            url = u''
            content = '%d - %d' % (t.getStart(), t.getEnd())
            if t == self:
                url = u'<span>%s</span>' % content
                url += u'<span class="ui-icon ui-icon-triangle-1-w"></span>'
            else:
                url = u'<a href="%s">%s</a>' % (
                            reverse('term_tab',
                                        args = [ 
                                            str(t.getStart()),
                                            str(t.getEnd())
                                        ]
                            ),
                            content,
                        )
            retVal.append(url)

        return retVal

    @staticmethod
    def get_term_or_404(start, end):
        """
        Static method get_term_or_404(start, end)

        Returns a TermOfOffice instance for given start, end year range. If
        there is no such term in DB, function (method) will raise Http404
        exception.

        The mandatory arguments start, end can be either strings or
        integer numbers:
            start   -   start year for term
            end     -   end year fo term

        Returns matching TermOfOffice instance or raises Http404
        """
        retVal = None

        if type(start) != type(''):
            start = str(start)

        if type(end) != type(''):
            end = str(end)

        try:
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            retVal = TermOfOffice.objects.get(yearStart = start, yearEnd = end)
        except ObjectDoesNotExist:
            if (int(start) <= int(end)):
                try:
                    #suppress 'no objects member' warning
                    #pylint: disable-msg=E1101
                    retVal = TermOfOffice.objects.get(
                                    yearStart = start,
                                    yearEnd = ''
                                )
                except ObjectDoesNotExist:
                    raise Http404
            else:
                raise Http404

        return retVal

    @staticmethod
    def get_term_for_date(date):
        """
        Static method get_term_for_date(date)

        Returns the term for date. The mandatory argument date is a
        datetime.date object.

        Returns instance of TermOfOffice class.
        """
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        for t in TermOfOffice.objects.all().order_by('-id'):
            if date >= t.getStartDate() and date <= t.getEndDate():
                return t

        return None
                

pre_save.connect(makeSlug, TermOfOffice)


class Division(AutoSlug):
    """
    Class Division(AutoSlug)

    Division class represents an electoral area. The contains these attributes:
        term    -   link (ForeignKey) to term instance the particular division
                    is bound to
        name    -   the division name (i.e. 'Karlovarsky kraj')
        icon    -   image icon (currently unused)
        slug    -   slug field (autogenerated from name)
    """
    term = models.ForeignKey(TermOfOffice)
    name = models.CharField(_("Division Name"), max_length = 20)
    icon = models.ImageField(_("Division Symbol"), 
                                upload_to = settings.SYMBOLS,
                                blank = True
                            )
    divId = models.CharField(_("Division Id"), max_length = 10)
    slug = models.SlugField(db_index = True, unique = True)
    slugs = {
        'slug' : ('name', )
    }

    class Meta:
        """
        See http://docs.djangoproject.com/en/dev/ref/models/options/
        for details
        """
        #suppress class has no __init__ method
        #pylint: disable-msg=W0232
        #suppress too few public methods warning
        #pylint: disable-msg=R0903
        verbose_name = _("Division")
        verbose_name_plural = _("Divisions")
        unique_together = (('term', 'divId',),)

    def getName(self):
        """
        Method getName()

        Method strips 'Volebni kraj' string from data, which are stored in DB.
        
        Returns the division name.
        """
        pattern = re.compile(u'.*Volební\s*kraj(?P<name>.*)', re.U)
        match = pattern.match(self.name)
        retVal = u''
        if match:
            retVal += match.group('name')
        else:
            retVal = self.name
        
        return retVal.strip()
        

pre_save.connect(makeSlug, Division)

#suppress warning too many public methods
#pylint: disable-msg=R0904
class PersonManager(models.Manager):
    """
    Class PersonManager

    PersonManager provides predefined select to retrieve Person instances from DB.
    Depending on method used the Person instance might be extended by extra attributes
    (see method descriptions further down).

    The PersonManager is a default manager for Person class.
    """
    def createPersonFromRow(self, row):
        """
        Method createPersonFromRow

        Method creates an instance of Person class from row, fetched from DB.
        The row is list, which represents the row retrieved. The method expects
        row argument to contain at least 14 elements. The order of elements
        in array is as follows:
            0   -   integer representinf Person's instance primary key
            1   -   mpId string representing person's key at psp.cz
            2   -   name (unicode string)
            3   -   surname (unicode string)
            4   -   title prepended before first name (unicode string)
            5   -   title, which follows sutname (unicode string)
            6   -   birthDate (datetime.date instance)
            7   -   email (string)
            8   -   office address (string)
            9   -   regionOffice address (office located at MP's home town)
            10  -   MP's phone (string)
            11  -   slug, which is a parameter to construct link (string)
            12  -   homePage/blog link to blog (string)
            13  -   link to profile at http://nasipolitici.cz

        Returns instance of Person class
        """ 
        person = self.model(
                    id = row[0],    #primary Key
                    mpId = row[1], #mpId
                    name = row[2], #name
                    surname = row[3], #surname
                    title = row[4], #title
                    titleLast = row[5], #titleLast
                    birthDate = row[6], #birthDate
                    email = row[7], #email
                    office = row[8], #office
                    regionOffice = row[9], #regionOffice
                    phone = row[10], #phone number
                    slug = row[11], # slug field
                    homePage = row[12], # homePage/blogField
                    nasiPoliticiUrl = row[13]
                )
                    
        return person


    def commonSelect(self, where, orderBy, desc, term = None):
        """
        Method commonSelect(where, orderBy, desc, term = None)

        Retrieves a list of Person objects matching the query. The application
        should never use commonSelect() directly. It should use commonSelect()
        wrappers instead:
            getPersons(), getPersonsOrderByName(),getPersonsOrderByAge(),
            getPersonsOrderByDivision(), getPersonsOrderByParty(),
            getPersonsOrderByAbsences(), getPerson()
        All these methods are explained further in the text.

        The arguments are parts of SELECT command:
            where   -   is optional WHERE clause (it can be an empty string)
            order   -   by is order by statement
            desc    -   is boolean flag if true the order will be descendant
            term    -   optional argument, if no term is passed, all MPs are
                        selected.

        Besides standard attribute the Person instance is extended by some
        more:
            detail  -   an instance of PersonDetail class
            divisionName    -   the name of electoral division the MP comes
                                from
            partyName   -   the name of party the MP is member of
            votes   -   is an instance of Votes class. It wraps up the
                        vote stats for given MP in term.
            terms   -   in case no term was specified, this attribute contains
                        list of all terms the MP was member of parliament.

        Returns list of Person objects extended by few attributes (see above).
        """
        #suppress too many branches refactore warning
        #pylint: disable-msg=R0912
        retVal = []
        select = ''
        termId = None

        if orderBy != None and orderBy != '':
            if desc:
                orderBy += ' DESC'
            else:
                orderBy += ' ASC'

        if term != None:
            termId = term_to_id(term)
            select = u"""
                SELECT DISTINCT person.id,
                    person.mpId,
                    person.name,
                    person.surname,
                    person.title,
                    person.titleLast,
                    person.birthDate,
                    person.email,
                    person.office,
                    person.regionOffice,
                    person.phone,
                    person.slug,
                    person.homePage,
                    person.nasiPoliticiUrl,
                    detail.id,
                    division.id,
                    party.id,
                    SUM(votes.absences) + SUM(votes.excused) AS absences,
                    SUM(votes.absences) + SUM(votes.excused) +
                            SUM(votes.votes_refrains) +
                            SUM(votes.votes_aye) +
                            SUM(votes.votes_nay) AS total,
                    SUM(votes.votes_aye) AS votesAye,
                    SUM(votes.votes_nay) AS votesNay,
                    SUM(votes.votes_refrains) AS refrains,
                    SUM(votes.absences) AS unexcAbs,
                    SUM(votes.excused) AS excsdAbs
                    FROM psp_person AS person
                    INNER JOIN psp_group AS parlament ON parlament.type LIKE 'PARLAMENT' AND
                                parlament.term_id = %d
                    INNER JOIN psp_group AS party ON party.type LIKE 'KLUB' AND
                                party.term_id = %d
                    INNER JOIN psp_membership AS partymship ON
                                partymship.group_id = party.id AND
                                partymship.post LIKE '%%%%len' AND
                                partymship.person_id = person.id
                    INNER JOIN psp_membership AS parlmship ON
                                parlmship.group_id = parlament.id AND
                                parlmship.post LIKE 'poslan%%%%' AND
                                parlmship.person_id = person.id
                    INNER JOIN psp_persondetail AS detail ON
                                detail.person_id = person.id AND
                                detail.term_id = %d
                    INNER JOIN psp_division AS division ON
                                division.id = detail.region_id
                    INNER JOIN psp_votestats AS votes ON
                                votes.person_id = person.id AND
                                votes.term_id = %d
                    %s
                    GROUP BY person.id %s;
            """
            select = select % (termId, termId, termId, termId, where, orderBy)
        else:
            select = u"""
                SELECT person.id,
                    person.mpId,
                    person.name,
                    person.surname,
                    person.title,
                    person.titleLast,
                    person.birthDate,
                    person.email,
                    person.office,
                    person.regionOffice,
                    person.phone,
                    person.slug,
                    person.homePage,
                    person.nasiPoliticiUrl,
                    detail.id,
                    division.id,
                    party.id,
                    SUM(votes.absences) + SUM(votes.excused) AS absences,
                    SUM(votes.absences) + SUM(votes.excused) +
                            SUM(votes.votes_refrains) +
                            SUM(votes.votes_aye) +
                            SUM(votes.votes_nay) AS total,
                    SUM(votes.votes_aye) AS votesAye,
                    SUM(votes.votes_nay) AS votesNay,
                    SUM(votes.votes_refrains) AS refrains,
                    SUM(votes.absences) AS unexcAbs,
                    SUM(votes.excused) AS excsdAbs
                    FROM psp_person AS person
                    INNER JOIN psp_group AS party ON party.type LIKE 'KLUB'
                    INNER JOIN psp_membership AS partymship ON
                                partymship.group_id = party.id AND
                                partymship.post LIKE '_len' AND
                                partymship.person_id = person.id
                    INNER JOIN psp_persondetail AS detail ON
                                detail.person_id = person.id
                    INNER JOIN psp_division AS division ON
                                division.id = detail.region_id
                    INNER JOIN psp_votestats AS votes ON
                                votes.person_id = person.id
                    %s
                    GROUP BY person.id %s;
                """
            select = select % (where, orderBy)

        cursor = connection.cursor()
        cursor.execute(select)
        for row in cursor.fetchall():
            mp = self.createPersonFromRow(row)

            # row[14] - missed polls
            # row[15] - total number of all polls
            mp.votes = Votes(
                vAye = row[19],
                vNay = row[20],
                vRefrains = row[21],
                absences = row[22],
                excused = row[23]
            )
            mp.detail = None
            #suppress 'Exception does not do anything' warning
            #pylint: disable-msg=W0704
            try:
                #suppress 'no objects member' warning
                #pylint: disable-msg=E1101
                mp.detail = PersonDetail.objects.get(id = row[14])
            except ObjectDoesNotExist:
                pass

            mp.divisionName = ''
            #suppress 'Exception does not do anything' warning
            #pylint: disable-msg=W0704
            try:
                # row[12] division ID
                #suppress 'no objects member' warning
                #pylint: disable-msg=E1101
                mp.divisionName = Division.objects.get(id = row[15]).getName()
            except ObjectDoesNotExist:
                pass

            mp.partyName = ''
            #suppress 'Exception does not do anything' warning
            #pylint: disable-msg=W0704
            try:
                # row[13] party club id
                #suppress 'no objects member' warning
                #pylint: disable-msg=E1101
                mp.partyGroup = Group.objects.get(id = row[16])
                mp.partyName = mp.partyGroup.getPartyName() 
            except ObjectDoesNotExist:
                pass

            mp.terms = []
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            for mship in Membership.objects.filter(
                                        person = mp,
                                        group__type = 'PARLAMENT'
                                    ).order_by('group__term__id'):
                mp.terms.append(mship.group.term)
            
            mp.term = term

            if term != None:
                mp.terms.append(term)
                mp.age =  term.getStart() - mp.birthDate.year
            else:
                try:
                    t = mp.terms[len(mp.terms) - 1]
                    mp.age = t.getStart() - mp.birthDate.year
                except IndexError:
                    mp.age = 0

            retVal.append(mp)

        return retVal


    def getPersons(self, desc = False, term = None):
        """
        Method getPersons(desc = False, term = None)

        Method uses commonSelect() to retrieve list of persons. The persons are
        ordered by surname, name in ascendant order by default. If no term
        argument is used function retrieves list of all MPs, otherwise it
        fetches list of MPs for particular term only. All arguments are optional:
            desc    -   false on default (the MPs will be ordered in ascendant
                        order by surname, name)
            term    -   if no term is specified then all MPs are retrieved,
                        each MP will have extra attribute terms, which
                        a list of all terms he/she was working as MP.

        Returns list of MPs ordered by surname, name
        """
        orderBy = 'ORDER BY person.surname, person.name'
        return self.commonSelect(   where = '',
                                    orderBy = orderBy,
                                    desc = desc,
                                    term = term
                                )


    def getPersonsOrderByName(self, desc = False, term = None):
        """
        Method getPersonsOrderByName(desc = False, term = None)

        Method uses commonSelect() to retrieve list of persons. The persons are
        ordered by name, surname in ascendant order by default. If no term
        argument is used function retrieves list of all MPs, otherwise it
        fetches list of MPs for particular term only. All arguments are optional:
            desc    -   false on default (the MPs will be ordered in ascendant
                        order by surname, name)
            term    -   if no term is specified then all MPs are retrieved,
                        each MP will have extra attribute terms, which
                        a list of all terms he/she was working as MP.

        Returns list of MPs ordered by name, surname
        """
        orderBy = 'ORDER BY person.name, person.surname'
        return self.commonSelect(   where = '',
                                    orderBy = orderBy,
                                    desc = desc,
                                    term = term
                                )


    def getPersonsOrderByAge(self, desc = False, term = None):
        """
        Method getPersonsOrderByAge(desc = False, term = None)

        Uses commonSelect() to retreive list of person objects. Objects are
        ordered by age. The age is computed with respect to the begining of
        given term. If no term is given then the recent term the MP was in
        parliament is used. By default the MPs are ordered in ascendant
        order. The optional arguments are as follows:
            desc    -   default False, (descendant/ascendant) order
            term    -   if no term is specified then all MPs are retrieved,
                        each MP will have extra attribute terms, which
                        a list of all terms he/she was working as MP.

        Returns list of MPs ordered by age in ascendant order by default.
        """
        orderBy = 'ORDER BY person.birthDate, person.surname, person.name'
        retVal = self.commonSelect( where = '',
                                    orderBy = orderBy,
                                    desc = desc,
                                    term = term
                                )
        #We need to sort by age here, age is computed with respect to the
        #start of parliementar membership.
        if desc:
            retVal.sort(lambda a, b: a.age - b.age)
        else:
            retVal.sort(lambda a, b: b.age - a.age)
            
        return retVal


    def getPersonsOrderByDivision(self, desc = False, term = None):
        """
        Method getPersonsOrderByAge(desc = False, term = None)

        Uses commonSelect() to retrieve list of MPs (person objects) ordered by
        division name. The defaut sort order is ascendant. If no term is
        specified MPs for all terms are retrieved. The optional arguments are:
            desc    -   default False, (descendant/ascendant) order
            term    -   if no term is specified then all MPs are retrieved,
                        each MP will have extra attribute terms, which
                        a list of all terms he/she was working as MP.

        Returns list of MPs ordered by division in ascendant order by default.
        """
        orderBy = 'ORDER BY division.name, person.surname, person.name'
        return self.commonSelect(   where = '',
                                    orderBy = orderBy,
                                    desc = desc,
                                    term = term
                                )


    def getPersonsOrderByParty(self, desc = False, term = None):
        """
        Method getPersonsOrderByParty(desc = False, term = None)

        Uses commonSelect() to retrieve the list of MPs (person objects) ordered
        by the name of party club they are member of. The default sort order
        is ascendant. If no term is specified MPs for all terms are retreived.
        The optional arguments are as follows:
            desc    -   default False, (descendant/ascendant) order
            term    -   if no term is specified then all MPs are retrieved,
                        each MP will have extra attribute terms, which
                        a list of all terms he/she was working as MP.

        Returns list of MPs ordered by their party name in ascendant order.
        """
        orderBy = 'ORDER BY party.name, person.surname, person.name'
        return self.commonSelect(   where = '',
                                    orderBy = orderBy,
                                    desc = desc,
                                    term = term
                                )

    
    def getPersonsOrderByAbsences(self, desc = False, term = None):
        """
        Method getPersonsOrderByAbsences(desc, term)

        Uses commonSelect() to retreive list of MPs (person objects) ordered
        by absences. The default sort order is ascendant (from the least
        absences to the most absences). If no term is specified MPs for all
        terms are retrieved. The optional arguments are as follows:
            desc    -   default False, (descendant/ascendant) order
            term    -   if no term is specified then all MPs are retrieved,
                        each MP will have extra attribute terms, which
                        a list of all terms he/she was working as MP.

        Returns list of MPs ordered by absences in ascendant order.
        """
        orderBy = 'ORDER BY absences'
        retVal = self.commonSelect( where = '',
                                    orderBy = orderBy,
                                    desc = desc,
                                    term = term
                                )
        #we need to sort by computed absences, which are in relative values,
        #the DB select sorts by absolute values, which are misleading
        #we need to keep 2 decimal places presition, therefore we mult by 100
        if desc:
            retVal.sort(
                lambda a, b: int(a.votes.totalAbsencesPerc * 100) - \
                int(b.votes.totalAbsencesPerc * 100)
            )
        else:
            retVal.sort(
                lambda a, b: int(b.votes.totalAbsencesPerc * 100) - \
                int(a.votes.totalAbsencesPerc * 100)
            )
        
        return retVal


    def getPerson(self, person, term = None):
        """
        Method getPerson(person, term)

        Uses commonSelect() to retrieve a single MP (Person object). If no term
        is specified all data from all terms the particular person was member
        of parlament are retrieved. The mandatory person argument can be any of
        these below:
            integer -   primary key for Person object
            string  -   slug defining MP
            person  -   instance of Person class
        If no term is specified the person will be extended by attribute terms,
        which is a list of all terms the person was active MP.

        Returns Person object or raises ObjectDoesNotExist exception, if no
        such MP can be found.
        """
        where = None
        if type(person) == type(0):
            where = 'WHERE person.id = %d' % person
        elif type(person) == type(u'') or type(person) == type(''):
            where = 'WHERE person.slug = "%s"' % person
        else:
            where = 'WHERE person.id = %d' % person.id

        #suppress 'redefining built-in list' warning
        #pylint: disable-msg=W0622
        list = self.commonSelect(   where = where,
                                    orderBy = '',
                                    desc = False,
                                    term = term
                                )

        if len(list) != 1:
            raise ObjectDoesNotExist

        return list[0]
            

    def getSlackers(self, dayStart, dayEnd = None, desc = True,
        colName = 'totalAbsences'):
        """
        Method getSlackers(dayStart, dayEnd = None, desc = True,
                colName = 'totalAbsences')

        Returns list of Person objects (MPs) ordered by their absences at
        polls for given date (date range). The only mandatory argument is
        dayStart, which defines desired day we want to retrieve list for.
        The rest of arguments is optional:
            dayEnd  -   if used, then dayStart, dayEnd range is defined
            desc    -   default value is true, which means the persons
                        will be ordered in descendant order (from the
                        biggest slackers toward the biggest hardworkers,
                        with no absences)
            colName -   the default value is 'totalAbsences' which means
                        the MPs will be ordered by totalAbsences column.
                        In fact you can use any column name you like:
                            absences    -   unexcused absences
                            excused     -   excused absences
                            votesRefrain
                            votesAye
                            votesNay

        Returns list of MPs ordered by their absences.
        """
        select = None
        order = None

        if desc:
            order = 'DESC'
        else:
            order = 'ASC'

        if dayEnd == None:
            select = """
                SELECT  id personId,
                        mpId AS personMpId,
                        name AS personName,
                        surname AS personSurname,
                        title AS personTitle,
                        titleLast AS personTitleLast,
                        birthDate AS personBirthDate,
                        email AS personEmail,
                        office AS personOffice,
                        regionOffice AS personRegionOffice,
                        phone AS personPhone,
                        slug AS personSlug,
                        homePage AS personHomePage,
                        nasiPoliticiUrl AS personNasiPoliticiUrl,
                        absences,
                        excused,
                        votesAye,
                        votesNay,
                        votesRefrain,
                        (absences + excused) AS totalAbsences FROM (
                    SELECT  mp.id AS id,
                            mp.mpId AS mpId,
                            mp.name AS name,
                            mp.surname AS surname,
                            mp.title AS title,
                            mp.titleLast AS titleLast,
                            mp.birthDate AS birthDate,
                            mp.email AS email,
                            mp.office AS office,
                            mp.regionOffice AS regionOffice,
                            mp.phone AS phone,
                            mp.slug AS slug,
                            mp.homePage AS homePage,
                            mp.nasiPoliticiUrl AS nasiPoliticiUrl,
                            SUM(mpds.absences) AS absences,
                            SUM(mpds.excused) AS excused,
                            SUM(mpds.votes_aye) AS votesAye,
                            SUM(mpds.votes_nay) AS votesNay,
                            SUM(mpds.votes_refrain) AS votesRefrain
                            FROM    psp_mpdaystats AS mpds
                                INNER JOIN psp_person AS mp ON
                                    mp.id = mpds.person_id
                            WHERE mpds.date = '%d-%02d-%02d'
                            GROUP BY id
                ) GROUP BY id ORDER BY %s %s;
            """
            select = select % ( dayStart.year, dayStart.month, dayStart.day,
                                colName, order
                            )
        else:
            select = """
                SELECT  id personId,
                        mpId AS personMpId,
                        name AS personName,
                        surname AS personSurname,
                        title AS personTitle,
                        titleLast AS personTitleLast,
                        birthDate AS personBirthDate,
                        email AS personEmail,
                        office AS personOffice,
                        regionOffice AS personRegionOffice,
                        phone AS personPhone,
                        slug AS personSlug,
                        homePage AS personHomePage,
                        nasiPoliticiUrl AS personNasiPoliticiUrl,
                        absences,
                        excused,
                        votesAye,
                        votesNay,
                        votesRefrain,
                        (absences + excused) AS totalAbsences FROM (
                    SELECT  mp.id AS id,
                            mp.mpId AS mpId,
                            mp.name AS name,
                            mp.surname AS surname,
                            mp.title AS title,
                            mp.titleLast AS titleLast,
                            mp.birthDate AS birthDate,
                            mp.email AS email,
                            mp.office AS office,
                            mp.regionOffice AS regionOffice,
                            mp.phone AS phone,
                            mp.slug AS slug,
                            mp.homePage AS homePage,
                            mp.nasiPoliticiUrl AS nasiPoliticiUrl,
                            SUM(mpds.absences) AS absences,
                            SUM(mpds.excused) AS excused,
                            SUM(mpds.votes_aye) AS votesAye,
                            SUM(mpds.votes_nay) AS votesNay,
                            SUM(mpds.votes_refrain) AS votesRefrain
                            FROM    psp_mpdaystats AS mpds
                                INNER JOIN psp_person AS mp ON
                                    mp.id = mpds.person_id
                            WHERE mpds.date
                                BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
                            GROUP BY id
                ) GROUP BY id ORDER BY %s %s;
            """
            select = select % ( dayStart.year, dayStart.month, dayStart.day,
                                dayEnd.year, dayEnd.month, dayEnd.day,
                                colName, order
                            )

        cursor = connection.cursor()
        cursor.execute(select)
        retVal = []
        for row in cursor.fetchall():
            mp = self.createPersonFromRow(row)
            mp.votes = Votes(
                    vAye = row[17],
                    vNay = row[16],
                    vRefrains = row[18],
                    absences = row[14],
                    excused = row[15],
                    sDate = dayStart,
                    eDate = dayEnd
            ) 

            retVal.append(mp)
        
        return retVal


    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def countPersonsForDay(self, date):
        """
        Method countPersonsForDay(date)

        Requires date argument, which is an instance of datetime.date object.
        It defines the day we want to count all MPs. The method is used to
        render charts.

        Returns the number of MPs for particular day.
        """
        select = """
            SELECT DISTINCT person.id
                FROM psp_person AS person
                INNER JOIN psp_group AS party ON party.type LIKE 'KLUB'
                INNER JOIN psp_membership AS partymship ON
                            partymship.group_id = party.id AND
                            partymship.post LIKE 'člen' AND
                            partymship.person_id = person.id AND
                            ((partymship.end IS NULL AND
                                partymship.start <= '%d-%02d-%02d'
                            ) OR (
                            '%d-%02d-%02d' BETWEEN partymship.start AND
                                     partymship.end 
                            ))
                GROUP BY person.id;
        """
        select = select % ( date.year, date.month, date.day,
                            date.year, date.month, date.day
                        )
        cursor = connection.cursor()
        cursor.execute(select)
        return len(cursor.fetchall())


    def getRebels(self, dayStart, dayEnd = None, desc = True, colName = 'same'):
        """
        Method getRebels(dayStart, dayEnd = None, desc = True, colName = 'same')

        Retrieves the list of MPs ordered by poll divergence. Rebels are MPs
        who are voting against majority of their own party club. The higher
        divergence in poll results between particular MP and his/her club the
        bigger rebel MP is. The only mandatory argument is dayStart, which is
        an instance of datetime.date class. The other arguments are optional:
            dayEnd  -   if defined then the list of MPs is retrieved for
                        day range <dayStart, dayEnd>
            orderDesc   -   by default the rebels (those who diverge most)
                            will be at the begining of the list.
            colName -   by default we are ordering by 'same' column, other
                        possible (reasonable values are):
                            activeSame
        Same vs. Active - in case of accordance of MP's we need to distinguish
        between rebels and active rebels. Rebels are all MP's whose results
        differ with majority vote of theri party club including absences - MP's
        absences are also included into calculation. The activeSame column,
        ignore does not include polls, where particular MP was not present,
        only polls, where given MP was actively participating in, these will
        be included, thus we call them active - actively voting against
        majority of party club.

        Returns list of MPs ordered by result divergence between them and their
        party club.
        """

        select = None
        order = None
        if desc:
            order = 'DESC'
        else:
            order = 'ASC'

        activeParm = '\n'

        if dayEnd != None:
            select = """
                SELECT
                    person.id,
                    person.mpId,
                    person.name,
                    person.surname,
                    person.title,
                    person.titleLast,
                    person.birthDate,
                    person.email,
                    person.office,
                    person.regionOffice,
                    person.phone,
                    person.slug,
                    person.homePage,
                    person.nasiPoliticiUrl,
                    pgroup.id AS id,
                    SUM(    CASE
                                WHEN gstats.result = mpvote.result THEN 1
                                ELSE 0
                            END
                    ) AS same,
                    SUM(1) AS total,
                    SUM(    CASE
                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN
                                    CASE
                                        WHEN gstats.result = mpvote.result THEN 1
                                        ELSE 0
                                    END
                                ELSE 0
                            END
                    ) AS activeSame,
                    SUM(    CASE
                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN 1
                                ELSE 0
                            END
                    ) AS activeTotal
                    FROM    psp_person AS person,
                            psp_membership AS mship,
                            psp_group AS pgroup,
                            psp_groupstats AS gstats,
                            psp_mpvote AS mpvote,
                            psp_poll AS poll
                    WHERE   pgroup.type LIKE 'KLUB' AND
                            mship.group_id = pgroup.id AND
                            mship.person_id = person.id AND
                            mship.post LIKE '%%%%len' AND
                            --we will handle lower bound first
                            (('%d-%02d-%02d' BETWEEN  mship.start AND mship.end) OR  
                            ('%d-%02d-%02d' > mship.start AND mship.end IS NULL)) AND
                            --we will handle upper bound next 
                            (('%d-%02d-%02d' BETWEEN  mship.start AND mship.end) OR  
                            ('%d-%02d-%02d' > mship.start AND mship.end IS NULL)) AND
                            mpvote.poll_id = poll.id AND mpvote.person_id = person.id AND %s
                            gstats.poll_id = poll.id AND gstats.group_id = pgroup.id AND
                            poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
                    GROUP BY person.id
                    ORDER BY %s %s;
            """
            select = select % ( dayStart.year, dayStart.month, dayStart.day,
                                dayStart.year, dayStart.month, dayStart.day,
                                dayEnd.year, dayEnd.month, dayEnd.day,
                                dayEnd.year, dayEnd.month, dayEnd.day,
                                activeParm,
                                dayStart.year, dayStart.month, dayStart.day,
                                dayEnd.year, dayEnd.month, dayEnd.day,
                                colName, order
                            )
        else:
            select = """
                SELECT
                    person.id,
                    person.mpId,
                    person.name,
                    person.surname,
                    person.title,
                    person.titleLast,
                    person.birthDate,
                    person.email,
                    person.office,
                    person.regionOffice,
                    person.phone,
                    person.slug,
                    person.homePage,
                    person.nasiPoliticiUrl,
                    pgroup.id,
                    SUM(    CASE
                                WHEN gstats.result = mpvote.result THEN 1
                                ELSE 0
                            END
                    ) AS same,
                    SUM(1) AS total,
                    SUM(    CASE
                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN
                                    CASE
                                        WHEN gstats.result = mpvote.result THEN 1
                                        ELSE 0
                                    END
                                ELSE 0
                            END
                    ) AS activeSame,
                    SUM(    CASE
                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN 1
                                ELSE 0
                            END
                    ) AS activeTotal
                    FROM    psp_person AS person,
                            psp_membership AS mship,
                            psp_group AS pgroup,
                            psp_groupstats AS gstats,
                            psp_mpvote AS mpvote,
                            psp_poll AS poll
                    WHERE   pgroup.type LIKE 'KLUB' AND
                            mship.group_id = pgroup.id AND
                            mship.person_id = person.id AND
                            mship.post LIKE '%%%%len' AND
                            (('%d-%02d-%02d' BETWEEN  mship.start AND mship.end) OR  
                            ('%d-%02d-%02d' > mship.start AND mship.end IS NULL)) AND
                            mpvote.poll_id = poll.id AND mpvote.person_id = person.id AND %s
                            gstats.poll_id = poll.id AND gstats.group_id = pgroup.id AND
                            poll.date = '%d-%02d-%02d'
                    GROUP BY person.id
                    ORDER BY %s %s;
            """
            select = select % ( dayStart.year,…