#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():
            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[18],
                vNay = row[19],
                vRefrains = row[20],
                absences = row[21],
                excused = row[22]
            )
            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, dayStart.month, dayStart.day,
                                dayStart.year, dayStart.month, dayStart.day,
                                activeParm,
                                dayStart.year, dayStart.month, dayStart.day,
                                colName, order
                            )

        cursor = connection.cursor()
        cursor.execute(select)
        retVal = []
        for row in cursor.fetchall():
            mp = self.createPersonFromRow(row)
            mp.party = Group.objects.get(id = row[14])
            mp.accordance = Accordance(
                same = row[15],
                diff = row[16] - row[15],
                sDate = dayStart,
                eDate = dayEnd
            )
            
            #if MP was not present at all, then pretend
            #100% activeAccordance
            if row[17] == 0 and row[18] == 0:
                mp.activeAccordance = Accordance(
                    same = 10,
                    diff = 0,
                    sDate = dayStart,
                    eDate = dayEnd
                )
            else:
                mp.activeAccordance = Accordance(
                    same = row[17],
                    diff = row[18] - row[17],
                    sDate = dayStart,
                    eDate = dayEnd
                )
            retVal.append(mp)
        
        return retVal


class Person(AutoSlug):
    """
    Class Person(AutoSlug)

    Person instances represent MPs. The default manager for class is
    PersonManager. There are just few attributes fetched from
    person table in DB:
        mpId    -   MP's ID (primary key) at psp.cz
        name    -   MP's name
        surname -   MP's surname
        title   -   title in front of name
        titleLast   -   title after the person's name
        birthDate   -   birth date
        email
        homePage    -   URLField with link to MP's blog/homepage
        office  -   address to office
        regionOffice    -   region office address
        phone,
        slug    -   slug field is composed of name and surname on instance save
                    i.e. josef_novak
        nasiPoliticiUrl -   link to MP's profile at www.nasipolitici.cz

    The rest of attributes are volatile - they are computed by select and
    added to instance.
    """
    mpId = models.CharField(
        _("MP ID"), unique = True, max_length = 10, db_index = True
    )
    name = models.CharField(_("Name"), max_length = 30)
    surname = models.CharField(_("Surname"), max_length = 30)
    title = models.CharField(_("Title"), max_length = 10, blank = True)
    titleLast = models.CharField(
        _("Title after Name"), max_length = 10, blank = True
    )
    birthDate = models.DateField(_("Birthdate"))
    email = models.EmailField(_("Email"))
    homePage = models.URLField(
        _("Homepage"), blank = True, verify_exists = False
    )
    office = models.CharField(_("Office"), max_length = 40)
    regionOffice = models.CharField(
        _("Electoral Ward Office"), max_length = 60
    )
    phone = PhoneNumberField(_("Phone Number"))
    slug = models.SlugField(db_index = True, unique = True)
    nasiPoliticiUrl = models.CharField(
        _("Link to NasiPolitici site"), max_length = 120, blank = True
    )
    slugs = {
        'slug' : ('name', 'surname', 'title', 'titleLast')
    }
    votes = None

    objects = PersonManager()

    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 = _("MP")
        verbose_name_plural = _("MPs")

    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
    
    @property
    def sex(self):
        """
        Property sex

        There are two values possible:
            'F' -   for woman
            'M' -   for man
        The value is derrived from name.
        """
        retVal = ''
        #suppress 'no endswith member' warning
        #pylint: disable-msg=E1101
        try:
            retVal = self._sex
        except AttributeError:
            #suppress warning 'Attribute _sex defined outside of init'
            #pylint: disable-msg=W0201
            if self.name.endswith('a') and \
                not self.surname.endswith(u'ý'):
                #suppress warning 'Attribute _sex defined outside of init'
                #pylint: disable-msg=W0201
                self._sex = 'F'
            else:
                self._sex = 'M'

            retVal = self._sex

        return retVal
                

    def getTermLinks(self, currentTerm = None):
        """
        Method getTermLinks(currentTerm = None)

        Method renders a html elements with links, to MP's profiles for each term.  
        If optional parameter currentTerm is used then the matching term will
        be just listed, no link will be generated for it.

        Returns list of html elements - links to terms
        """
        retVal = []
        url = u''

        for parlMship in self.getParlMemberships():
            url = u''
            term = parlMship.group.term
            content = '%d - %d' % (term.getStart(), term.getEnd(),)

            if currentTerm != None and term == currentTerm:
                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('show_mp_term', args = [
                                            self.slug,
                                            str(term.getStart()),
                                            str(term.getEnd())
                                        ]),
                            content
                        )
            retVal.append(url)
                
        return retVal
        

    def getParlMemberships(self):
        """
        Method getParlMemberships()

        Returns a django.query.queryset instance with all MP's memberships in
        group type 'PARLAMENT'. This is the easiest way to find out all terms
        the given MP was working in PARLAMENT.

        Returns a queryset
        """
        #suppress 'no membership_set member' warning
        #pylint: disable-msg=E1101
        retVal = self.membership_set.filter(
                            group__type__iexact = 'PARLAMENT',
                            post__iregex = '.*POSLAN.*'
                        )
        return retVal


    def getGroups(self, term):
        """
        Method getGroups(term)

        Method returns a django.queryset instance, which retrieves all
        memberships except the membership in group 'PARLAMENT' for given MP in
        chosen term. The term is mandatory parameter.

        Returns a queryset
        """
        retVal = []
        #suppress 'no membership_set member' warning
        #pylint: disable-msg=E1101
        if term != None:
            retVal = self.membership_set.filter(
                            ~Q(group__type__iexact = 'PARLAMENT'),
                            group__term = term,
                            post__iregex = u'.len'
                        )
        else:
            retVal = self.membership_set.filter(
                            ~Q(group__type__iexact = 'PARLAMENT'),
                            post__iregex = u'.len'
                        )

        return retVal


    def getMshipsForDateRange(self, dateStart, dateEnd = None):
        """
        Method getMshipsForDateRange(dateStart, dateEnd = None)

        Method returns django.query.queryset instance, which will retrieve all
        MP's membership in groups except group PARLAMENT in given date range
        defined by <dateStart, dateEnd>. Both arguments are instances of
        datetime.date class. If no dateEnd is specified method will return MP's
        membership for given date specified by dateStart.

        Returns a django.query.queryset instance
        """
        
        retVal = None
        term = TermOfOffice.get_term_for_date(dateStart)
        if dateEnd == None:
            #suppress 'no membership_set member' warning
            #pylint: disable-msg=E1101
            try:
                retVal = self.membership_set.filter(
                    ~Q(group__type__iexact = 'PARLAMENT'),
                    Q(start__lte = dateStart, end__gte = dateStart)|
                    Q(start__lte = dateStart, end = None),
                    group__term = term
                )
            except FieldError:
                retVal = self.membership_set.filter(
                    ~Q(group__type__iexact = 'PARLAMENT'),
                    start__lte = dateStart,
                    end = None,
                    group__term = term
                )
        else:
            try:
                #suppress 'no membership_set member' warning
                retVal = self.membership_set.filter( #pylint: disable-msg=E1101
                    ~Q(group__type__iexact = 'PARLAMENT'),
                    Q(start__lt = dateEnd) |
                    Q(start__lte = dateStart, end__gte = dateEnd) |
                    Q(end__range = (dateStart, dateEnd)),
                    group__term = term
                )
            except FieldError:
                #suppress 'no membership_set member' warning
                retVal = self.membership_set.filter( #pylint: disable-msg=E1101
                    ~Q(group__type__iexact = 'PARLAMENT'),
                    Q(start__lt = dateEnd) |
                    Q(start__lte = dateStart, end = None),
                    group__term = term
                )

        return retVal


    def getPartyClub(self, date):
        """
        Method getPartyClub(date)

        Method returns the group instance of type 'KLUB' (which is a party club
        the MP is member of) for single date. The date is mandatory parameter -
        the instance of datetime.date class.

        Returns group instance of type 'KLUB'
        """
        t = TermOfOffice.get_term_for_date(date)
        retVal = None
        try:
            #suppress 'no membership_set member' warning
            #pylint: disable-msg=E1101
            partyMship = self.membership_set.get(
                            group__type__iexact = 'KLUB',
                            group__term = t,
                            post__iregex = u'.len',
                            start__lte = date,
                            end__gte = date
                        )
            retVal = partyMship.group
        except ObjectDoesNotExist:
            #suppress 'no membership_set member' warning
            try:
                partyMship = self.membership_set.get(#pylint: disable-msg=E1101
                                group__type__iexact = 'KLUB',
                                group__term = t,
                                post__iregex = u'.len',
                                end = None
                            )
                retVal = partyMship.group
            except MultipleObjectsReturned:
            #suppress 'no membership_set member' warning
                partyMship = \
                    self.membership_set.filter(#pylint: disable-msg=E1101
                                 group__type__iexact = 'KLUB',
                                 group__term = t,
                                 post__iregex = u'.len',
                                 end = None
                             ).order_by('-start')
                retVal = partyMship[0].group

        return retVal


    def getPartyMships(self, term):
        """
        Method getPartyMships(term)

        Method retrieves all memberships in party clubs for given MP in chosen term.
        The term is mandatory argument - instance of TermOfOffice class.


        Returns a django.query.queryset instance
        """
        #suppress 'no membership_set member' warning
        #pylint: disable-msg=E1101
        retVal = self.membership_set.filter(
                    group__term = term,
                    group__type__iexact = 'KLUB'
                ).order_by('start')

        return retVal


    def getAccordanceCharts(self, group, dateStart, dateEnd = None):
        """
        Method getAccordanceCharts(group, dateStart, dateEnd = None)
        
        Returns a tupple, which consists of two pieCharts describing MP's
        accordances with group. The first tupple member describes accordance
        with group - the date include all meetings in given period. The second
        chart in tupple does not cover polls, where particular MP was not
        present. The arguments are as follows:
            group   -   the group (either instance or id) we want to get
                        accordance chart for
            dateStart   -   instance of datetime.date class
            dateEnd     -   optional argument in case we want to
                            get accodance charts for period
                            <dateStart, dateEnd>

        Returns tupple (accordanceChart, activeAccordanceChart)
        """
        mp = self.getAccordanceWithGroup(
            group = group,
            dateStart = dateStart,
            dateEnd = dateEnd
        )
        if mp == None:
            return (None, None,)

        groupAccordance = mp.accordance.getChart()
        title = None
        if group.type == 'KLUB':
            title = _('%s. %s shoda s %s') % (
                mp.name[0], mp.surname, group.getPartyName()
            )
        else:
            title = _('%s. %s shoda s %s') % (
                mp.name[0], mp.surname, group.name
            )
        groupAccordance.title(title)
        groupAccordance.size(330, 80)

        activeGroupAccordance = mp.activeAccordance.getChart()
        title = None
        if group.type == 'KLUB':
            title = _('%s. %s - aktivní shoda s %s') % (
                mp.name[0], mp.surname, group.getPartyName()
            )
        else:
            title = _('%s. %s - acktiví shoda s %s') % (
                mp.name[0], mp.surname, group.name
            )
        activeGroupAccordance.title(title)
        activeGroupAccordance.size(330, 80)


        return (groupAccordance, activeGroupAccordance,)
    

    def getAccordanceWithGroup(self, group, dateStart = None, dateEnd = None):
        """
        Method getAccordanceWithGroup(group, dateStart, dateEnd = None)

        Retrieves a poll accordance data for MP with group.  Person object is
        extended by two 'accordance' attributes.  Accordance attribute is the
        instance of Accordance class. The arguments are as follows:
            group       - group to compute accordance with
            dayStart    - date object defining start day
            dayEnd      - date object defining end day, it is optional
                          parameter, if no dayEnd is specified, then accordance
                          for desired dayStart date is retreived only.
        The new two attributes added to self instance are:
            accordance  -   MP's poll accordance with group
            activeAccordance    -   MP's poll accordance with group (only
                                    polls, where MP is present are counted)

        Returns self extended by two new attributes - accordance and
        activeAccordance. Both attribute are instances of Accordance class.
        """
        groupId = group_to_id(group)
        select = None
        if dateEnd == None and dateStart == None:
            try:
                dateStart = Membership.objects.filter(
                                group_id = groupId
                            ).order_by('-start')[0].start
                dateEnd = Membership.objects.filter(
                                group_id = groupId,
                                end = None
                            )
                if len(dateEnd) == 0:
                    dateEnd = Membership.objects.filter(
                                    group_id = groupId
                                ).order_by('-end')[0].date
                else:
                    dateEnd = datetime.date.today()
            except IndexError:
                return None
                                                    
        if dateEnd != None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result <> vote.result THEN 1
                                ELSE 0
                            END
                        ) AS different,
                        SUM(
                            CASE
                                WHEN gs.result == vote.result THEN 1
                                ELSE 0
                            END
                        ) AS same,
                        SUM(
                            CASE
                                WHEN vote.result IN ('A', 'N', 'Z') AND
                                    gs.result != vote.result THEN 1
                                ELSE 0
                            END
                        ) AS activeDifferent,
                        SUM(
                            CASE
                                WHEN vote.result IN ('A', 'N', 'Z') AND
                                    gs.result == vote.result THEN 1
                                ELSE 0
                            END
                        ) AS activeSame
                        FROM psp_poll AS poll
                            INNER JOIN psp_mpvote AS vote ON
                                        poll.id = vote.poll_id AND
                                        vote.person_id = %d
                            INNER JOIN psp_groupstats AS gs ON
                                        poll.id = gs.poll_id AND
                                        gs.group_id = %d
                        WHERE poll.date BETWEEN '%d-%02d-%02d' AND
                            '%d-%02d-%02d';
            """
            #suppressing 'Person has no attribute id' warning
            select = select % ( self.id,#pylint: disable-msg=E1101
                                groupId,
                                dateStart.year, dateStart.month, dateStart.day,
                                dateEnd.year, dateEnd.month, dateEnd.day,
                            )
        else:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result <> vote.result THEN 1
                                ELSE 0
                            END
                        ) AS different,
                        SUM(
                            CASE
                                WHEN gs.result == vote.result THEN 1
                                ELSE 0
                            END
                        ) AS same,
                        SUM(
                            CASE
                                WHEN vote.result IN ('A', 'N', 'Z') AND
                                    gs.result != vote.result THEN 1
                                ELSE 0
                            END
                        ) AS activeDifferent,
                        SUM(
                            CASE
                                WHEN vote.result IN ('A', 'N', 'Z') AND
                                    gs.result == vote.result THEN 1
                                ELSE 0
                            END
                        ) AS activeSame
                        FROM psp_poll AS poll
                            INNER JOIN psp_mpvote AS vote ON
                                        poll.id = vote.poll_id AND
                                        vote.person_id = %d
                            INNER JOIN psp_groupstats AS gs ON
                                        poll.id = gs.poll_id AND
                                        gs.group_id = %d
                        WHERE poll.date = '%d-%02d-%02d';
            """
            #suppressing 'Person has no attribute id' warning
            select = select % ( self.id,#pylint: disable-msg=E1101
                                groupId,
                                dateStart.year, dateStart.month, dateStart.day,
                            )

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()

        if len(rows) != 1:
            return None

        row = rows[0]
        #suppress warning 'Attribute _sex defined outside of init'
        #pylint: disable-msg=W0201
        self.accordance = Accordance(
                                diff = row[0],
                                same = row[1],
                                sDate = dateStart,
                                eDate = dateEnd
                            )
        #if MP was not present at all, then pretend
        #100% activeAccordance
        if row[2] == 0 and row[3] == 0:
            #suppress warning 'Attribute _sex defined outside of init'
            #pylint: disable-msg=W0201
            self.activeAccordance = Accordance(
                diff = 0,
                same = 10,
                sDate = dateStart,
                eDate = dateEnd
            )
        else:
            #suppress warning 'Attribute _sex defined outside of init'
            #pylint: disable-msg=W0201
            self.activeAccordance = Accordance(
                                    diff = row[2],
                                    same = row[3],
                                    sDate = dateStart,
                                    eDate = dateEnd
                                )
        return self 


    def getPersonWithStats(self, dayStart, dayEnd = None):
        """
        Method getPersonWithStats(dayStart, dateEnd = None)

        Retrives statistics for person. The person will be extended by
        attribute votes, which is instance of Votes class. The arguments
        are as follows:
            dayStart    -   instance of datetime.date class with desired
                            day we want to gather stats for
            dayEnd      -   optional, if specified then <dateStart, dateEnd>
                            range is used instead of single day.

        Returns a self object extended by votes attribute (the instance
        of Votes class).
        """

        #save cycles in case the votes attribute already exists
        if self.votes != None and\
            self.votes.startDate == dayStart and self.votes.endDate == dayEnd:
            return self

        if dayEnd == None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN vote.result = 'M' THEN 1
                                ELSE 0
                            END
                        ) AS abs,
                        SUM(
                            CASE
                                WHEN vote.result = '0' OR
                                    vote.result = 'X' THEN 1
                                ELSE 0
                            END
                        ) AS excs,
                        SUM(
                            CASE
                                WHEN vote.result = 'N' THEN 1
                                ELSE 0
                            END
                        ) AS nay,
                        SUM (
                            CASE
                                WHEN vote.result = 'Z' THEN 1
                                ELSE 0
                            END
                        ) AS refrains,
                        SUM (
                            CASE
                                WHEN vote.result = 'A' THEN 1
                                ELSE 0
                            END
                        ) AS aye
                        FROM psp_poll AS poll
                            INNER JOIN psp_mpvote AS vote ON
                                        poll.id = vote.poll_id AND
                                        vote.person_id = %d
                        WHERE poll.date = '%d-%02d-%02d';
            """
            select = select % (
                #suppressing 'Person has no attribute id' warning
                self.id,#pylint: disable-msg=E1101
                dayStart.year, dayStart.month, dayStart.day
            )

        else:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN vote.result = 'M' THEN 1
                                ELSE 0
                            END
                        ) AS abs,
                        SUM(
                            CASE
                                WHEN vote.result = '0' OR
                                    vote.result = 'X' THEN 1
                                ELSE 0
                            END
                        ) AS excs,
                        SUM(
                            CASE
                                WHEN vote.result = 'N' THEN 1
                                ELSE 0
                            END
                        ) AS nay,
                        SUM (
                            CASE
                                WHEN vote.result = 'Z' THEN 1
                                ELSE 0
                            END
                        ) AS refrains,
                        SUM (
                            CASE
                                WHEN vote.result = 'A' THEN 1
                                ELSE 0
                            END
                        ) AS aye
                        FROM psp_poll AS poll
                            INNER JOIN psp_mpvote AS vote ON
                                        poll.id = vote.poll_id AND
                                        vote.person_id = %d
                        WHERE poll.date BETWEEN '%d-%02d-%02d' AND
                                                '%d-%02d-%02d';
            """
            select = select % (
                #suppressing 'Person has no attribute id' warning
                self.id,#pylint: disable-msg=E1101
                dayStart.year, dayStart.month, dayStart.day,
                dayEnd.year, dayEnd.month, dayEnd.day
            )

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()

        if len(rows) != 1:
            return None

        row = rows[0]
        retVal = Person(self)
        retVal.votes = Votes(
            absences = row[0],
            excused = row[1],
            vNay = row[2],
            vRefrains = row[3],
            vAye = row[4],
            sDate = dayStart,
            eDate = dayEnd
        )

        return retVal

    
pre_save.connect(makeSlug, Person)


class PersonDetail(models.Model):
    """
    Class PersonDetail(models.Model)

    PersonDetail class holds data for MP and specific term.
    These attributes are fetched from DB:
        region  -   foreign key, which referes to division
        person  -   foreign key, which referes to person
        term    -   foreign key, which referes to term, the record is bound to 
        photo   -   of given MP
    """
    region = models.ForeignKey(Division, blank = True, null = True)
    person = models.ForeignKey(Person)
    term = models.ForeignKey(TermOfOffice)
    photo = models.ImageField(  _("Photo"),
                                upload_to = settings.MP_PHOTOS
                            )
    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 = _("Person's Additional Info")
        verbose_name_plural = _("Person's Additional Info")

    
class GroupManager(models.Manager):
    """
    Class GroupManager(models.Manager)

    The GroupManager is a default custom manager for class Group.
    """
    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def getGroupWithStats(self, term, qType, poll):
        """
        Method getGroupWithStats(term, qType, poll)

        Method retrives a list of groups extended by stat attribute,
        which is an instance of GroupStats class. The arguments are:
            term    -   TermOfOffice instance
            qType   -   django.db.models.Q object - it is used to perform
                        custom filtering by group type
            poll    -   instance of Poll class, it presents the particular
                        poll we want to gather data for

        Returns the list of groups extended by stat attribute (instance of
        GroupStats). The groups are ordered by name.
        """
        retVal = []
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        for g in Group.objects.filter(
                            Q(term = term),
                            qType).order_by('name'):
            try:
                #suppress 'no objects member' warning
                #pylint: disable-msg=E1101
                g.stat = GroupStats.objects.get(group = g, poll = poll)
            except ObjectDoesNotExist:
                g.stat = None

            subGroups = []

            for sg in g.children.all():
                try:
                    #suppress 'no objects member' warning
                    #pylint: disable-msg=E1101
                    sg.stat = GroupStats.objects.get(
                        group = sg,
                        poll = poll
                    )
                except ObjectDoesNotExist:
                    g.stat = None

                subGroups.append(sg)

            g.subGroups = subGroups
            retVal.append(g)

        return retVal


    def getGroupWithStatsArg(self, poll, qObjs):
        """
        Method getGroupWithStatsArg(poll, qObjs)

        Method retrieves the list of groups. Each Group instance in the list
        will be extended by attribute stat (instance of GroupStats class).  The
        method allows caller to finely define the desired querset parameter by
        using qObjs argument. The arguments are as follows:
            poll    -   the poll we want to gather stats for
            qObjs   -   the args for filter() method from queryset,
                        it's basically tupple of django.db.models.Q instances

        Returns list of groups extended by stat attribute, which is an instance
        of GroupStats class.
        """
        retVal = []
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        #suppress used * or ** magic
        #pylint: disable-msg=W0142
        for g in Group.objects.filter(*qObjs).order_by('name'):
            try:
                #suppress 'no objects member' warning
                #pylint: disable-msg=E1101
                g.stat = GroupStats.objects.get(group = g, poll = poll)
            except ObjectDoesNotExist:
                g.stat = None

            subGroups = []

            for sg in g.children.all():
                try:
                    #suppress 'no objects member' warning
                    #pylint: disable-msg=E1101
                    sg.stat = GroupStats.objects.get(
                        group = sg,
                        poll = poll
                    )
                except ObjectDoesNotExist:
                    sg.stat = None

                subGroups.append(sg)

            g.subGroups = subGroups
            retVal.append(g)

        return retVal

            
class Group(AutoSlug):
    """
    Class Group(AutoSlug)

    Group represents any group of MP's, which participates in parliament
    in given term. These attributes are stored into DB:
        term    -   ForeignKey to term
        members -   ManyToManyField links person through Membership
                    table
        groupId -   group ID (key) from psp.cz
        name    -   group name used in jakhlasovali.cz site
        origName -  group name used in psp.cz site.
        type    -   type of group can be: party, board, subboard, ...
        coulor  -   colour assigned to group when generating charts,
                    currently is used just for party clubs
        icon    -   icon assigned to group (currently unused)
        slug    -   slug field to generate human friendly urls
    
    Certain queries will compute extra attributes:
        group consistency, votes, ...

    The Group class is using GroupManager as a default manager.
    """
    term = models.ForeignKey(TermOfOffice)
    members = models.ManyToManyField(   Person,
                                        through='Membership',
                                        blank = True,
                                        null = True
                                    )
    parentGroup = models.ForeignKey(    'self',
                                    related_name = 'children',
                                    blank = True,
                                    null = True
                                )
    groupId = models.CharField(_("Group Id"), max_length = 20)
    name = models.CharField(_("Name"), max_length = 512)
    origName = models.CharField(_("Name"), max_length = 1024)
    type = models.CharField(_("Club Type"), max_length = 50, blank = True)
    color = models.CharField(_("Colour"), max_length = 6, blank = True)
    icon = models.ImageField(_("Club Icon"),
                                upload_to = settings.GROUP_ICONS,
                                blank = True
                            )
    slug = models.SlugField(db_index = True)
    slugs = {
        'slug' : ('name', )
    }
    consistency = None
    activeConsistency = None
    votes = None

    objects = GroupManager()

    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 = _("MP Organ (party/board/...)")
        verbose_name_plural = _("MP Organs (parties/boards/...)")
        unique_together = (('term', 'groupId',),)

    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 getMembersCommon(self,
                            dateStart = None,
                            dateEnd = None,
                            orderArg = None,
                            distinct = True):
        """
        Method getMembersCommon(dateStart = None, dateEnd = None,
            orderArg = None, distinct = True)

        Returns list of person objects - members of group.  If no date is
        specified returns summary for complete term, otherwise returns member
        list valid for particular date range. If dateStart only is specified,
        then actual list for given date is returned.

        Each person object in list returned is extended by these attributes:
            mship,
            days,
        and:
            age,
            partyClub,
            division,
            ... 
            [ all attributes inserted by PersonManager.commonSelect ]
        """

        mships = None
        if dateStart == None and dateEnd == None:
            #suppress 'no membership_set member' warning
            #pylint: disable-msg=E1101
            mships = self.membership_set.all()
        elif dateStart != None and dateEnd == None:
            #suppress 'no membership_set member' warning
            #pylint: disable-msg=E1101
            mships = self.membership_set.filter(
                            Q(end = None) | Q(end__gte = dateStart),
                            start__lte = dateStart
                        )
        elif dateStart != None and dateEnd != None:
            #suppress 'no membership_set member' warning
            #pylint: disable-msg=E1101
            mships = self.membership_set.filter(
                            ~Q(end = None),
                            Q(end__gte = dateEnd),
                            start__lte = dateStart
                        )
        else:
            return []

        if orderArg:
            mships = mships.order_by(orderArg)

        #
        # we have to fake distinct by person, if we are asked to do so
        #
        if distinct:
            tmp = {}
            for m in mships:
                tmp[m.person.id] = m
            
            mships = []
            for k in tmp.keys():
                mships.append(tmp[k])

        retVal = []

        for m in mships:
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            person = Person.objects.getPerson(m.person, self.term)
            person.mship = m
            person.start = m.start
            person.end = m.end
            person.post = m.post
            if m.end == None:
                person.days = (datetime.date.today() - m.start).days
            else:
                person.days = (m.end - m.start).days

            retVal.append(person)

        return retVal


    def getMembersByAge(self, desc = True, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByAge(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByAge() to retrieve list of persons (members of group)
        ordered by age. The arguments are as follows:
            desc    -   if true the members will be ordered from the oldest
                        to youngest
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = self.getMembersCommon(dateStart, dateEnd,
                                        distinct = distinct
                                    )
        if desc:
            retVal.sort(lambda a, b: a.age - b.age)
        else:
            retVal.sort(lambda a, b: b.age - a.age)

        return retVal


    #suppress warning 'Unused argument distinct'
    #pylint: disable-msg=W0613
    def getMembersByParty(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByParty(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByParty() to retrieve list of persons (members of group)
        ordered by party they member of. The arguments are as follows:
            desc    -   if false the members will be ordered by party name in
                        alphabetical order
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = self.getMembersCommon(dateStart, dateEnd,
                                        distinct = True
                                    )
        if desc:
            retVal.sort(lambda a, b: a.partyName > b.partyName)
        else:
            retVal.sort(lambda a, b: a.partyName < b.partyName)

        return retVal


    def getMembersByName(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByName(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByName() to retrieve list of persons (members of group)
        ordered by their names. The arguments are as follows:
            desc    -   if false the members will be ordered by name in
                        alphabetical order.
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = None
        if desc:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            '-person__name',
                                            distinct = distinct
                                        )
        else:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            'person__name',
                                            distinct = distinct
                                        )
        return retVal


    def getMembersBySurname(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersBySurname(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersBySurname() to retrieve list of persons (members of
        group) ordered by their surname. The arguments are as follows:
            desc    -   if false the members will be ordered by surname in
                        alphabetical order.
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = None
        if desc:
            retVal = self.getMembersCommon( 
                                            dateStart,
                                            dateEnd,
                                            '-person__surname',
                                            distinct = distinct
                                        )
        else:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            'person__surname',
                                            distinct = distinct
                                        )
            
        return retVal
    

    def getMembersByDivision(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByDivision(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByDivision() to retrieve list of persons (members of
        group) ordered by the electoral division. The arguments are as follows:
            desc    -   if false the members will be ordered by division name
                        in alphabetical order.
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = self.getMembersCommon(dateStart, dateEnd, distinct = distinct)
        if desc:
            retVal.sort(lambda a, b: a.divisionName > b.divisionName)
        else:
            retVal.sort(lambda a, b: a.divisionName < b.divisionName)

        return retVal


    def getMembersByDays(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByDays(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByDays() to retrieve list of persons (members of group)
        ordered by the number of days their membership lasts.  The arguments
        are as follows:
            desc    -   if true the members will be ordered from the longest
                        membership to the shortest one.
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = self.getMembersCommon(dateStart, dateEnd, distinct = distinct)
        if desc:
            retVal.sort(lambda a, b: a.days > b.days)
        else:
            retVal.sort(lambda a, b: a.days < b.days)

        return retVal


    def getMembersByStart(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByStart(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByStart() to retrieve list of persons (members of group)
        ordered by the start of their membership.  The arguments are as
        follows:
            desc    -   if false the members will be ordered from the oldest date
                        to recent ones
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = None
        if desc:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            '-start',
                                            distinct = distinct
                                        )
        else:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            'start',
                                            distinct = distinct
                                        )
        return retVal


    def getMembersByEnd(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByEnd(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByEnd() to retrieve list of persons (members of group)
        ordered by the end date of their membership.  The arguments are as
        follows:
            desc    -   if false the members will be ordered from the oldest
                        end dates to recent ones
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = None
        if desc:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            '-end',
                                            distinct = distinct
                                        )
        else:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            'end',
                                            distinct = distinct
                                        )
        return retVal


    def getMembersByPost(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByPost(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByPost() to retrieve list of persons (members of group)
        ordered by their membership post (member (člen), head (předseda)).  The
        arguments are as follows:
            desc    -   if false the members will be ordered from the oldest
                        end dates to recent ones
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = None
        if desc:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            '-post',
                                            distinct = distinct 
                                        )
        else:
            retVal = self.getMembersCommon(
                                            dateStart,
                                            dateEnd,
                                            'post',
                                            distinct = distinct
                                        )
        return retVal


    def getMembersByAbsences(self, desc = False, dateStart = None,
                            dateEnd = None, distinct = True):
        """
        Method getMembersByAbsences(desc = True, dateStart = None
            dateEnd = None, distinct = True)
        
        Uses getMembersByAbsences() to retrieve list of persons (members of group)
        ordered by their absences.  The arguments are as follows:
            desc    -   if false the members will be ordered from the highest
                        absences to the lowest absences number.
            dateStart   -   date we want to retrieve members for
            dateEnd     -   if date end is specified then method wil retrieve
                            persons for <dateStart, dateEnd> range.
                            if no date is specified members for complete term
                            are returned
            distinct    -   only one membership will be retreived for person,
                            person might have several memberships, which can
                            overlap (member, prime member, ...)
        Returns list of persons, it uses commonSelect to construct list.
        """
        retVal = self.getMembersCommon(dateStart, dateEnd, distinct = distinct)

        if desc:
            retVal.sort(
                lambda a, b: int(a.votes.totalAbsences - b.votes.totalAbsences)
            )
        else:
            retVal.sort(
                lambda a, b: int(b.votes.totalAbsences - a.votes.totalAbsences)
            )
        

        return retVal


    def getGroupPartyProfile(   self,
                                dateStart = None,
                                dateEnd = None):
        """
        Method getGroupPartyProfile(dateStart = None, dateEnd = None)

        Returns list of parties the members of group are coming from. Each party
        group in list is extended by these parameters
            mpCount
            group - which is the self (the group we are braking down to parties rep)
            totalCount - number of members in self
            percent - relative percent of party members in self
        The arguments are:
            dateStart   -   retrieves data for particular date
            dateEnd     -   if specified retrieves data for range
                            <dateStart, dateEnd>
        Returns list of party groups extended by mpCount, group, totalCount, percent
        """
        #suppress warning too many local variables
        #pylint: disable-msg=R0914
        if self.type == 'KLUB':
            return None

        select = None
        if dateStart == None and dateEnd == None:
            select = """
                SELECT groupId, SUM(num) FROM (
                    SELECT  partyMship.group_id AS groupId,
                            COUNT(DISTINCT mp.id) AS num
                        FROM    psp_person AS mp,
                                psp_membership AS mship
                        JOIN psp_group AS party ON
                                party.type LIKE 'KLUB' AND
                                party.term_id = %d
                        JOIN psp_membership AS partyMship ON
                                mp.id = partyMship.person_id AND
                                partyMship.group_id = party.id
                        WHERE mship.group_id = %d AND mship.person_id = mp.id
                        GROUP BY mp.id ORDER BY partyMship.group_id
                    )
                    GROUP BY groupId;
            """
            #suppress warning ForeignKey has no attribute id
            #pylint: disable-msg=E1101
            #suppress warning Group has no attribute id
            #pylint: disable-msg=E1101
            select = select % (self.term.id, self.id)
        elif dateStart != None and dateEnd == None:
            date = '%d-%02d-%02d' % (   dateStart.year,
                                        dateStart.month,
                                        dateStart.day
                                    )
            select = """
                SELECT groupId, SUM(num) FROM (
                    SELECT  partyMship.group_id AS groupId,
                            COUNT(DISTINCT mp.id) AS num
                        FROM    psp_person AS mp,
                                psp_membership AS mship
                        JOIN    psp_group AS party ON
                                party.type LIKE 'KLUB' AND party.term_id = %d
                        JOIN    psp_membership AS partyMship ON
                                mp.id = partyMship.person_id AND
                                partyMship.group_id = party.id AND
                                (   (partyMship.end IS NULL AND
                                    partyMship.start <= '%s') OR
                                    ('%s' BETWEEN partyMship.start AND
                                    partyMship.end)
                                )
                        WHERE mship.group_id = %d AND
                                mship.person_id = mp.id AND
                                (   (mship.end IS NULL AND
                                    mship.start <= '%s') OR
                                    ('%s' BETWEEN mship.start AND mship.end)
                                )
                        GROUP BY mp.id ORDER BY partyMship.group_id
                    )
                    GROUP BY groupId;
            """
            #suppress warning ForeignKey has no attribute id
            #pylint: disable-msg=E1101
            #suppress warning Group has no attribute id
            #pylint: disable-msg=E1101
            select = select % ( self.term.id,
                                date, date,
                                self.id,
                                date, date
                            )
        elif dateStart != None and dateEnd != None:
            start = '%d-%02d-%02d' % (  dateStart.year,
                                        dateStart.month,
                                        dateStart.day
                                )
            end = '%d-%02d-%02d' % (    dateEnd.year,
                                        dateEnd.month,
                                        dateEnd.day
                                )
            select = """
                SELECT groupId, SUM(num) FROM (
                    SELECT  partyMship.group_id AS groupId,
                            COUNT(DISTINCT mp.id) AS num
                        FROM psp_person AS mp, psp_membership AS mship
                        JOIN    psp_group AS party ON
                                party.type LIKE 'KLUB' AND
                                party.term_id = %d
                        JOIN    psp_membership AS partyMship ON
                                mp.id = partyMship.person_id AND
                                partyMship.group_id = party.id AND
                                ((partyMship.end IS NULL AND
                                    (partyMship.start BETWEEN '%s' AND '%s' OR
                                        partyMship.start <= '%s'))
                                    OR
                                    (partyMship.start BETWEEN '%s' AND '%s' OR
                                    partyMship.end BETWEEN '%s' AND '%s') OR
                                (partyMship.start <= '%s'))
                        WHERE   mship.group_id = %d AND
                                mship.person_id = mp.id AND
                                ((mship.end IS NULL AND
                                    (mship.start BETWEEN '%s' AND '%s' OR
                                    mship.start <= '%s'))
                                OR
                                    (mship.start BETWEEN '%s' AND '%s' OR
                                        mship.end BETWEEN '%s' AND '%s') OR
                                        (mship.start <= '%s')
                                )
                        GROUP BY mp.id ORDER BY partyMship.group_id
                    )
                    GROUP BY groupId;
            """
            #suppress warning ForeignKey has no attribute id
            #pylint: disable-msg=E1101
            #suppress warning Group has no attribute id
            #pylint: disable-msg=E1101
            select = select % ( self.term.id,
                                start, end, start,
                                start, end, start, end,
                                start,
                                self.id,
                                start, end, start,
                                start, end, start, end,
                                start,
                            )
        else:
            raise ObjectDoesNotExist

        
        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()
        retVal = []
        total = 0

        for row in rows:
            groupId = row[0]
            mpCount = row[1]
            total += mpCount
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            group = Group.objects.get(id = groupId)
            group.mpCount = mpCount
            group.group = self
            retVal.append(group)

        for g in retVal:
            g.totalCount = total
            g.percent = get_percents(g.mpCount, total)
            
        return retVal
            

    def getMfRatio(self):
        """
        Method getMfRatio()

        Returns the ratio between men and women in the group.
        """
        males = 0
        females = 0
        members = None
        #suppress 'no membership_set member' warning
        #pylint: disable-msg=E1101
        members = self.membership_set.all().select_related().distinct('person')

        for member in members:
            if member.person.sex == 'F':
                females += 1
            else:
                males += 1

        return males, females

    def getMfPie(self):
        """
        Method getMfPie()

        Returns the GChart.Pie3D instance - chart representing ratio between
        men and women in the group.
        """
        male, female = self.getMfRatio()
        pm = get_percents(male, male + female)
        pf = get_percents(female, male + female)
        retVal = Pie3D([pm, pf])
        retVal.label(
                _(u'Muži') + ' %d' % male,
                _(u'Ženy') + ' %d' % female
            )
        retVal.color('000077', '770000') 
        #suppress warning foreign key has no member getStart()
        #pylint: disable-msg=E1101
        #suppress warning foreign key has no meber getEnd()
        #pylint: disable-msg=E1101
        retVal.title(_(u'Poměr mužů a žen') + '(%d - %d)' % (
                                        self.term.getStart(),
                                        self.term.getEnd())
                                    )
        retVal.size(330, 130)

        return retVal
        
    def getPartyName(self):
        """
        Method getPartyName()

        Method converts long name format to acronum.  The mpClub.name usually
        looks like
            Poslanecký klub Občasnské demokratické strany
        the method will turn it into abbreviation:
            ODS

        Returns string with party name acronum if group is party.
        """
        #work around Křesťansko demokratická strana-českosloven...
        #suppress warning foreign key has no meber replace
        #pylint: disable-msg=E1101
        name = self.origName.replace('-', ' - ')
        pattern = re.compile(u'.*Poslanecký klub(?P<name>.*)', re.U)
        match = pattern.match(name)
        retVal = u''
        if match:
            name = match.group('name')
        
        for i in name.split():
            if i == 'a' or i == 'A':
                continue
            retVal += i[0].upper()

        if retVal == 'N':
            retVal = _(u'Nezávislí')

        return retVal.strip()


    def getAvgAge(self):
        """
        Method getAvgAge()

        Returns the average age of group members.
        """
        retVal = 0
        totalAge = 0
        count = 0

        #suppress 'no membership_set member' warning
        #pylint: disable-msg=E1101
        members = self.membership_set.all().distinct('person')
        for member in members:
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            mp = Person.objects.get(id = member.person.id)
            age = self.term.getStart() - mp.birthDate.year
            totalAge += age
            count += 1

        if count != 0:
            retVal = totalAge / count

        return retVal
        

    def getPartyPie(self, dateStart = None, dateEnd = None):
        """
        Method getPartyPie(dateStart = None, dateEnd = None)

        Method returns party profile for group. It generates party profile
        either for single date, if dateStart only is specified or for
        <dateStart, dateEnd> range if both arguments are specified.
        If no argument is passed, then chart for complete term is
        returned.
        """
        partyProfile = self.getGroupPartyProfile(dateStart, dateEnd)
    
        if partyProfile == None:
            return None

        pieData = [ party.mpCount for party in partyProfile ]
        retVal = Pie3D(pieData)

        label = [ '%s (%.2f%%)' % ( party.getPartyName(),
                                    party.percent
                                )  for party in partyProfile 
                ]
        #suppress used * or ** magic
        #pylint: disable-msg=W0142
        retVal.label(*label)

        colors = ''
        for party in partyProfile:
            colors += party.color + '|'
        retVal.color(colors.strip('|'))

        if dateStart == None and dateEnd == None:
            #suppress warning foreign key has no member getStart()
            #pylint: disable-msg=E1101
            #suppress warning foreign key has no meber getEnd()
            #pylint: disable-msg=E1101
            title = u'%s (%d - %d)' % (
                            self.name,
                            self.term.getStart(),
                            self.term.getEnd()
                        )
        elif dateStart != None and dateEnd == None:
            title = u'%s (%s)' % (
                            self.name,
                            dateStart.strftime("%d. %m. %Y")
                        )
        else:
            title = u'%s (%s - %s)' % (
                            self.name,
                            dateStart.strftime("%d. %m. %Y"),
                            dateEnd.strftime("%d. %m. %Y")
                        )

        retVal.title(title)
        retVal.size(400, 130)

        return retVal


    def getAccordanceWithGroup( self,
                                otherGroup,
                                dateStart = None,
                                dateEnd = None
                            ):
        """
        Method getAccordanceWithGroup(otherGroup,
            dateStart = None, dateEnd = None)

        Method generates accordance chart with 'otherGroup'.
        The arguments are:
            otherGroup  -   the group to be compared for accordance
            dateStart   -   dateStart if not specified the accordance for
                            complete term is computed, otherwise for signle
                            dateStart day only is computed.
            dateEnd     -   if specified then accordance for <dateStart,
                            dateEnd> range is returned.
        Returns accordance with group chart.
        """
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        groupAccord = GroupStats.objects.getAccordanceWithGroup(
                            self, otherGroup,
                            dateStart, dateEnd
                        )
        retVal = groupAccord.accordance.getChart()

        title = ''
        if self.type == 'KLUB':
            title = _(u'%s - shoda s ') % (self.getPartyName())
        else:
            title = _(u'%s - shoda s ') % (self.name)

        if otherGroup.type == 'KLUB':
            title += otherGroup.getPartyName()
        else:
            title += otherGroup.name
        retVal.title(title)
        retVal.size(330, 80)
        #suppress warning 'Attribute accordance defined outside of init'
        #pylint: disable-msg=W0201
        self.accordance = retVal
        
        return retVal


    def getPresencePieChart(self, dateStart = None, dateEnd = None):
        """
        Method getPresencePieChart(dateStart = None, dateEnd = None)

        Extends self by votes attribute, which is an instance of Votes class.
        If dateStart, dateEnd are None then presence pie chart for complete
        term is computed. If both arguments are specified then presence
        pie for <dateStart, dateEnd> range is returned. 

        Returns Pie3D presence chart for given group.
        """
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        stats = GroupStats.objects.getStats(
            group = self,
            dateStart = dateStart,
            dateEnd = dateEnd
        )
        #suppress warning 'Attribute stats defined outside of init'
        #pylint: disable-msg=W0201
        self.stats = stats

        retVal = stats.votes.getPresenceChart()
        
        if self.type == 'KLUB':
            retVal.title(_(u'%s - absence %d - %d') % (
                                    self.getPartyName(),
                                    self.term.getStart(),
                                    self.term.getEnd()
                                )
                            )
        else:
            retVal.title(_(u'%s Absence %d - %d') % (
                                    self.name,
                                    self.term.getStart(),
                                    self.term.getEnd()
                                )
                            )
        retVal.size(330, 80)

        return retVal


    def getGroupConsistency(self, dateStart, dateEnd = None):
        """
        Method getGroupConsistency(dateStart, dateEnd = None)
        Returns self extended by two attributes:
            consistency
            activeConsistency
        both attributes are instances of Accordance class.  Both attributes
        describe poll conistency inside the given group - group consistency.
        Group consistency is reflects the ratio between MPs, who voted with
        group majority vs. MPs who voted against the final group result.

        The activeConsistency does not account MP's absences.

        The date range we want to gather consistency for is defined as
        <dayStart, dayEnd> if no upper bound (dayEnd == None) is defined,
        then consistency data for single date (dayStart) are retreived.

        Method returns self on success, None on error.
        """
        def none2zero(x):
            """
            Internal function none2zero()
            Accepts argument 'x' if x is equal to None returns zero, otherwise
            returns x.
            """
            if x == None:
                return 0
            else:
                return x


        select = ''
        if dateEnd == None:
            select = """
                SELECT  SUM (CASE
                                WHEN gstats.result = vote.result THEN 1
                                ELSE 0
                             END
                        ) AS same,
                        SUM (CASE
                                WHEN gstats.result != vote.result THEN 1
                                ELSE 0
                            END
                        ) AS diff,
                        SUM (CASE
                                WHEN vote.result IN ('A', 'N', 'Z') THEN
                                    CASE
                                        WHEN gstats.result = vote.result THEN 1
                                        ELSE 0
                                    END
                                ELSE 0
                            END
                        ) AS sameNoAbs,
                        SUM (CASE
                                WHEN vote.result IN ('A', 'N', 'Z') THEN
                                    CASE
                                        WHEN gstats.result != vote.result THEN 1
                                        ELSE 0
                                    END
                                ELSE 0
                            END
                        ) AS diffNoAbs,
                        SUM (1) AS total
                        FROM    psp_poll AS poll,
                                psp_membership AS mship
                        JOIN psp_groupstats AS gstats ON gstats.poll_id = poll.id AND
                            gstats.group_id = %d
                        JOIN psp_mpvote AS vote ON vote.person_id = mship.person_id AND
                            mship.group_id = %d AND vote.poll_id = poll.id
                        WHERE   poll.date = '%d-%02d-%02d' AND mship.group_id = %d AND
                                mship.post LIKE '_len' AND
                                ((poll.date BETWEEN mship.start AND mship.end) OR
                                (mship.start <= poll.date AND mship.end IS NULL))
            """
            #suppress 'Group has no id member' warning
            #pylint: disable-msg=E1101
            select = select % ( self.id,
                                self.id,
                                dateStart.year, dateStart.month, dateStart.day,
                                self.id,
                            )
        else:
            select = """
                SELECT  SUM (CASE
                                WHEN gstats.result = vote.result THEN 1
                                ELSE 0
                             END
                        ) AS same,
                        SUM (CASE
                                WHEN gstats.result != vote.result THEN 1
                                ELSE 0
                            END
                        ) AS diff,
                        SUM (CASE
                                WHEN vote.result IN ('A', 'N', 'Z') THEN
                                    CASE
                                        WHEN gstats.result = vote.result THEN 1
                                        ELSE 0
                                    END
                                ELSE 0
                            END
                        ) AS sameNoAbs,
                        SUM (CASE
                                WHEN vote.result IN ('A', 'N', 'Z') THEN
                                    CASE
                                        WHEN gstats.result != vote.result THEN 1
                                        ELSE 0
                                    END
                                ELSE 0
                            END
                        ) AS diffNoAbs,
                        SUM (1) AS total
                        FROM    psp_poll AS poll,
                                psp_membership AS mship
                        JOIN psp_groupstats AS gstats ON gstats.poll_id = poll.id AND
                            gstats.group_id = %d
                        JOIN psp_mpvote AS vote ON vote.person_id = mship.person_id AND
                            mship.group_id = %d AND vote.poll_id = poll.id
                        WHERE   poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d' AND
                                mship.group_id = %d AND mship.post LIKE '_len' AND
                                ((poll.date BETWEEN mship.start AND mship.end) OR
                                (mship.start <= poll.date AND mship.end IS NULL));
            """
            #suppress 'Group has no id member' warning
            #pylint: disable-msg=E1101
            select = select % ( self.id,
                                self.id,
                                dateStart.year, dateStart.month, dateStart.day,
                                dateEnd.year, dateEnd.month, dateEnd.day,
                                self.id
                            )   

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()
        
        if len(rows) != 1:
            return None

        row = rows[0]
        #suppress used builtin function 'map' warning
        #pylint: disable-msg=W0141
        row = map(none2zero, row)
        self.consistency = Accordance(
                                    same = row[0],
                                    diff = row[1],
                                    sDate = dateStart,
                                    eDate = dateEnd 
                                )
        self.activeConsistency = Accordance(
                                    same = row[2],
                                    diff = row[3],
                                    sDate = dateStart,
                                    eDate = dateEnd 
                                )
            
        return self


pre_save.connect(makeSlug, Group)


class Membership(models.Model):
    """
    Class Membership(models.Model)

    Class represents a membership of person in group.
    These attributes are fetched from DB:
        group   -   foreign key refering to group
        person  -   foreign key refering to person
        post    -   post bound to membership (member, head, ...)
        start   -   start date of membership
        end     -   end date of membership. if None then membership
                    is still in progress.
    """
    group = models.ForeignKey(Group)
    person = models.ForeignKey(Person)
    post = models.CharField(_("Post"), max_length = 30)
    start = models.DateField(_("Start Date"))
    end = models.DateField(_("End Date"), blank = True, null = True)

    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 = _("MP Organ Membership")
        verbose_name_plural = _("MP Organs Membership")

    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 getStart(self):
        """
        Method getStart() returns self.start
        """
        return self.start

    def getEnd(self):
        """
        Method getEnd() returns self.end.
        If self.end is None then datetime.datetime.today()
        is returned.
        """
        if self.end == None:
            return datetime.datetime.today()

        return self.end
        
    def getDuration(self):
        """
        Method getDuration() returns time delta between start and date.
        """
        if self.end == None:
            today = datetime.datetime.today()
            return today - self.start

        return self.end - self.start


class Meeting(models.Model):
    """
    Class Meeting(models.Model)

    Class meeting represents single meeting. These fields are fetched from DB:
        term    -   ForeignKey to TermOfOffice, when particular team happened
        meetingNo   -   the kind of meeting name (string) (i.e. '50', '51'...)
        start   -   datetime.date instance - start date of meeting
        end     -   datetime.date instance - end date of meeting, some meetings
                    can last more than one day
        name    -   meeting name (i.e. '51. schuze')
        url     -   link to meeting data at psp.cz
    """
    term = models.ForeignKey(TermOfOffice)
    meetingNo = models.CharField(_("Meeting No."), max_length = 8)
    start = models.DateField(_("Start Date"))
    end = models.DateField(_("End Date"))
    name = models.CharField(_("Session Name"), max_length = 80)
    url = models.URLField(_("Link To Meeting Agenda"), verify_exists = False)

    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 = _("Meeting")
        verbose_name_plural = _("Meetings")
    
    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


class PollManager(models.Manager):
    """
    Class PollManager(models.Manager)

    Class is default manager for Polls.
    """
    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def getMeetingDates(self):
        """
        Method getMeetingDates()

        Returns list of Poll instances grouped by date. Method is used in
        updateDB.py so far.
        """
        select = """
            SELECT date FROM psp_poll GROUP BY date ORDER BY date ASC;
        """
        retVal = []
        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()
        for row in rows:
            retVal.append(row[0])

        return retVal


class Poll(AutoSlug):
    """
    Class Poll(AutoSlug)

    Class represents single poll. These attributes are fetched from DB:
        meeting -   meeting is ForeignKey referring to particular Meeting
                    instance
        name    -   poll title
        pollId  -   an ID assigned to poll at psp.cz
        verdict -   can be one of follows:
                        'přijato'
                        'zamítnuto'
        votesAye    -   number of votes Aye
        votesNay    -   number of votes Nay
        votesRefrain    -   number of refrains
        absences    -   number of absences
        excused -   number of excused absences
        slug    -   slug referring to poll
        resultsLink -   link to results at psp.cz
        stenoLink   -   link to stenographic record
        date        -   datetime.date instance with poll date
    """
    meeting = models.ForeignKey(Meeting)
    name = models.CharField(_("Poll Title"), max_length = 80)
    pollId = models.CharField(_("Poll Id"), max_length = 30, db_index = True)
    verdict = models.CharField(_("Verdict"), max_length = 20)
    votesAye = models.IntegerField( _("Votes For"),
                                    db_column = 'vote_aye'
                                )
    votesNay = models.IntegerField( _("Votes Against"),
                                    db_column = 'vote_nay'
                                )
    votesRefrain = models.IntegerField( _("Votes Refrained"),
                                    db_column = 'refrain'
                                )
    absences = models.IntegerField( _("Absentees"),
                                    db_column = 'absentees'
                                )
    excused = models.IntegerField(_("Excused"))
    slug = models.SlugField(db_index = True)
    resultsLink = models.URLField(  _("Link to Poll Result"),
                                    verify_exists = False,
                                    blank = True
                                )
    stenoLink = models.URLField(    _("Link to steno record"),
                                    verify_exists = False,
                                    blank = True
                            )
    date = models.DateField(_("Poll date"), blank = True)
    slugs = {
        'slug' : ('name', )
    }

    objects = PollManager()

    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 = _("Poll")
        verbose_name_plural = _("Polls")

    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 resultPie(self):
        """
        Method resultPie()

        Method generates a pie chart representing results.
        
        Returns instance GChart.Pie3D instance.
        """
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        mpVotes = MpVote.objects.filter(poll = self)
        votes = Votes(
            vAye = mpVotes.filter(result__iexact = 'A').count(),
            vNay = mpVotes.filter(result__iexact = 'N').count(),
            vRefrains = mpVotes.filter(result__iexact = 'Z').count(),
            absences = mpVotes.filter(result__iregex = '0|X|M').count(),
            sDate = self.date
        )

        retVal = votes.getVotesChart()
        retVal.size(370, 120)
        
        return retVal


    def bumpPoll(self, result):
        """
        Method bumpPoll(result)

        Bumps poll - result is code for result for particular MP.
        The update will bump votesAye, votesNay ... by result code:
            'A' - adds one vote to votesAye
            'N' - adds one vote to votesNay
            'Z' - adds one vote to votesRefrain
            '0' - adds one vote to absences
            'X' 
            'M' - adds one vote for excused

        Returns void
        """
        if result == 'A':
            self.votesAye += 1
        elif result == 'N':
            self.votesNay += 1
        elif result == 'Z':
            self.votesRefrain += 1
        elif result == '0' or result == 'X':
            self.absences += 1
        elif result == 'M':
            self.excused += 1

        return


    def getRebels(self, active = False):
        """
        Method getRebels(active = False)

        Method returns list persons. The list is ordered by accordance with
        MP's clubs for given poll. The person instances (MPs) are ordered from
        the least accordance. The argument active determines whether
        all results shoudl be counted or only those, which are result of
        active presence (results code 'A', 'N', 'Z').
    
        Returns the list of persons ordered by accordance with party club
        for given poll.
        """
        extraArg = ''
        if active:
            extraArg = """
                AND mpvote.result IN ('A', 'N', 'Z')
            """

        select = """
            SELECT  mship.person_id
                FROM    psp_mpvote AS mpvote,
                        psp_group AS party
                    JOIN    psp_groupstats AS gs ON gs.group_id = party.id AND gs.poll_id = %d
                    JOIN    psp_membership AS mship ON mship.person_id = mpvote.person_id AND
                            mship.group_id = party.id AND mship.post LIKE '_len' AND
                            gs.group_id = mship.group_id AND gs.result != mpvote.result %s
                WHERE   mpvote.poll_id = %d AND party.type = 'KLUB'
                GROUP BY mship.person_id;
        """
        #suppress warning Poll has no attribute id
        #pylint: disable-msg=E1101
        select = select % ( self.id,
                            extraArg,
                            self.id
                        )

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()
        
        retVal = []
        for row in rows:
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            mp = Person.objects.get(id = row[0])
            retVal.append(mp)

        return retVal

    
class MpVoteManager(models.Manager):
    """
    Class MpVoteManager(models.Manager)

    MpVoteManager is default manager for MpVote class.
    """

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

        Retrieves stats for any arbitrary period.  dateStart is mandatory
        argument, dateEnd is optional. When both arguments are specified
        function retrieves stats for range <dateStart, dateEnd>

        Returns VoteStats object. It's kind of surprise (one would expect it
        will be returned by by VoteStats manager). The think is the
        VoteStats aggregate records for whole term. Therefore I've decided
        to put this method here.
        """
        if dateEnd != None:
            select = """
                SELECT  SUM(    CASE
                                    WHEN mpvote.result = 'A' THEN 1
                                    ELSE 0
                                END
                        ) AS votes_aye,
                        SUM(    CASE
                                    WHEN mpvote.result = 'N' THEN 1
                                    ELSE 0
                                END
                        ) AS votes_nay,
                        SUM(    CASE
                                    WHEN mpvote.result = 'Z' THEN 1
                                    ELSE 0
                                END
                        ) AS votes_refrains,
                        SUM(    CASE
                                    WHEN mpvote.result = '0' OR
                                        result = 'X' THEN 1
                                    ELSE 0
                                END
                        ) AS absences,
                        SUM(    CASE
                                    WHEN mpvote.result = 'M' THEN 1
                                    ELSE 0
                                END
                        ) AS absences_excused
                    FROM psp_poll AS poll
                    INNER JOIN psp_mpvote AS mpvote ON mpvote.poll_id = poll.id
                    WHERE poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
            """ % ( dateStart.year, dateStart.month, dateStart.day,
                    dateEnd.year, dateEnd.month, dateEnd.day
                )
        else:
            select = """
                SELECT  SUM(    CASE
                                    WHEN mpvote.result = 'A' THEN 1
                                    ELSE 0
                                END
                        ) AS votes_aye,
                        SUM(    CASE
                                    WHEN mpvote.result = 'N' THEN 1
                                    ELSE 0
                                END
                        ) AS votes_nay,
                        SUM(    CASE
                                    WHEN mpvote.result = 'Z' THEN 1
                                    ELSE 0
                                END
                        ) AS votes_refrains,
                        SUM(    CASE
                                    WHEN mpvote.result = '0' OR
                                        result = 'X' THEN 1
                                    ELSE 0
                                END
                        ) AS absences,
                        SUM(    CASE
                                    WHEN mpvote.result = 'M' THEN 1
                                    ELSE 0
                                END
                        ) AS absences_excused
                    FROM psp_poll AS poll
                    INNER JOIN psp_mpvote AS mpvote ON mpvote.poll_id = poll.id
                    WHERE poll.date = '%d-%02d-%02d';
            """ % (dateStart.year, dateStart.month, dateStart.day)

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()

        if len(rows) != 1:
            return None

        row = rows[0]
        retVal = VoteStats()
        retVal.votesAye = row[0]
        retVal.votesNay = row[1]
        retVal.votesRefrain = row[2]
        retVal.absences = row[3]
        retVal.excused = row[4]
        #suppress warning 'Attribute totalAbsences defined outside of init'
        #pylint: disable-msg=W0201
        retVal.totalAbsences = sum(row[3:])
        #suppress warning 'Attribute totalPolls defined outside of init'
        #pylint: disable-msg=W0201
        retVal.totalPolls = sum(row)
        retVal.dateStart = dateStart
        retVal.dateEnd = dateEnd

        return retVal


class MpVote(AutoSlug):
    """
    Class MpVote(AutoSlug)

    MpVote stores a poll result for single MP. The attributes fetched
    from database are as follows:
        poll    -   ForeignKey referring to particular poll
        person  -   ForeignKey referring to MP, who polled
        result  -   single char, valid values are as follows:
                        A   votes aye
                        N   votes nay
                        Z   refrain
                        M   excused
                        A,X absence
    """
    poll = models.ForeignKey(Poll, db_index = True)
    person = models.ForeignKey(Person, db_index = True)
    result = models.CharField(_("Result"), max_length = 20)

    objects = MpVoteManager()

    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 = _("Vote")
        verbose_name_plural = _("Votes")
        unique_together = (('poll', 'person',),)

    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


    #suppress warning 'Argument differs from overridden method'
    #pylint: disable-msg=W0221
    def save(self):
        """
        Method save()

        The saving a vote for particular person must update corresponding
        VoteStats record. If there is no such record exists yet, then it
        is created.
        """
        
        super(MpVote, self).save()

        mpStats = None
        try:
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            mpStats = VoteStats.objects.get(
                person = self.person,
                term = self.poll.meeting.term
            )
        except ObjectDoesNotExist:
            print "creating stats for: %s %s" % (
                #suppress 'ForeignKey has no member name' warning
                #pylint: disable-msg=E1101
                self.person.name,
                #suppress 'ForeignKey has no member surname' warning
                #pylint: disable-msg=E1101
                self.person.surname
            )
            mpStats = VoteStats()
            mpStats.votesAye = 0
            mpStats.votesNay = 0
            mpStats.votesRefrain = 0
            mpStats.excused = 0
            mpStats.absences = 0
            #suppress 'ForeignKey has no member meeting
            #pylint: disable-msg=E1101
            mpStats.term = self.poll.meeting.term
            mpStats.person = self.person
        
        if self.result == 'A':
            mpStats.votesAye += 1
        elif self.result == 'N':
            mpStats.votesNay += 1
        elif self.result == 'Z':
            mpStats.votesRefrain += 1
        elif self.result == 'M':
            mpStats.excused += 1
        else:
            mpStats.absences += 1

        mpStats.save()

        return

    def delete(self):
        """
        Method delete()

        When vote is being deleted, then corresponding VoteStats record
        must be also updated.
        """
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        mpStats = VoteStats.objects.get(    person = self.person,
                                            term = self.poll.meeting.term
                                    )

        super(MpVote, self).delete()

        if self.result == 'A':
            mpStats.votesAye -= 1
        elif self.result == 'N':
            mpStats.votesNay -= 1
        elif self.result == 'Z':
            mpStats.votesRefrain -= 1
        elif self.result == 'M':
            mpStats.excused -= 1
        else:
            mpStats.absences -= 1

        mpStats.save()

        return



    def getResult(self):
        """
        Method getResult()

        Returns poll result in human readable form.
        """
        if self.result == 'A':
            return _(u'Pro')
        elif self.result == 'N':
            return _(u'Proti')
        elif self.result == 'Z':
            return _(u'Zdržel')
        elif self.result == '0':
            return _(u'Chybí')
        else:
            return _(u'Omluven')


    @staticmethod
    def get_presence_pie(dateStart, dateEnd = None):
        """
        Static method get_presence_pie(dateStart, dateEnd)

        Renders a pie chart showing MP's presence for given <dateStart,
        dateEnd> range. If no dateEnd is specified, then pie chart for
        single day is rendered.

        Returns GChart.Pie3D instance.
        """
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        vs = MpVote.objects.getDayStats(dateStart, dateEnd)

        presences = vs.votesAye + vs.votesNay + vs.votesRefrain
        presences = get_percents(presences, vs.totalPolls)
        excused = get_percents(vs.excused, vs.totalPolls)
        absences = get_percents(vs.absences, vs.totalPolls)

        retVal = Pie3D([ presences, excused, absences ])
        retVal.label(   _(u'Přítomno') + ' %.2f%%' % presences,
                        _(u'Omluveno') + ' %.2f%%' % excused,
                        _(u'Chybí') + ' %.2f%%' % absences,
                    )
        retVal.color('00dd00', 'dd7700', 'dd0000') 
        retVal.title(_(u'Nepřítomnost při hlasování'))
        retVal.size(330, 80)

        return retVal


class VoteStatsManager(models.Manager):
    """
    Class VoteStatsManager(models.Manager)

    Class provides default manager for VoteStats class.
    """
    def getStatsForTerm(self, term = None):
        """
        Aggregates stats for all MPs in given term. If no term is passed
        returns aggregation for all terms. Function returns VoteStat object
        without any reference to particular person. Thus the returned VoteStat
        object can not be saved.
        """
        retVal = None
        cursor = None
        if term == None:
            select = """
                SELECT  SUM(votes_aye),
                        SUM(votes_nay),
                        SUM(votes_refrains),
                        SUM(absences),
                        SUM(excused),
                        SUM(absences + excused),
                        SUM(votes_aye + votes_nay + votes_refrains)
                        FROM psp_votestats;
            """
        else:
            termId = term_to_id(term)
            select = """
                SELECT  SUM(votes_aye),
                        SUM(votes_nay),
                        SUM(votes_refrains),
                        SUM(absences),
                        SUM(excused),
                        SUM(absences + excused),
                        SUM(votes_aye + votes_nay + votes_refrains)
                        FROM psp_votestats
                        WHERE term_id = %d;
            """
            select = select % termId

        cursor = connection.cursor()
        cursor.execute(select)

        for row in cursor.fetchall()[:1]:
            retVal = self.model(
                        votesAye = row[0],
                        votesNay = row[1],
                        votesRefrain = row[2],
                        absences = row[3],
                        excused = row[4]
                    )
            retVal.totalAbsences = row[5]
            retVal.presences = row[6]
            retVal.term = term
        
        return retVal


class VoteStats(models.Model):
    """
    Class VoteStats(models.Model)

    Class encapsulates a DB table with poll results aggregation for particular
    person and term. These attributes are being fetched from DB:
        person  -   ForeignKey - referrence to Person instance
        term    -   ForeignKey - referrence to TermOfOffice instance
        votesAye    -   votes aye   (result == 'A')
        votesNay    -   votes nay   (result == 'N')
        votesRefrain    -   refrains    (result == 'Z')
        absences    -   result == 'X' or result == '0'
        excused - result == 'M'
    """
    #suppress 'too many instance attributes' warning
    #pylint: disable-msg=R0902
    person = models.ForeignKey(Person, db_index = True)
    term = models.ForeignKey(TermOfOffice)
    votesAye = models.IntegerField(_("Votes For"), db_column = 'votes_aye')
    votesNay = models.IntegerField(_("Votes Against"), db_column = 'votes_nay')
    votesRefrain = models.IntegerField( _("Votes Refraiend"),
                                         db_column = 'votes_refrains'
                                    )
    absences = models.IntegerField(_("Absences"))
    excused = models.IntegerField(_("Excused Absences"))
    dateStart = None
    dateEnd = None
    _votes = None

    objects = VoteStatsManager()

    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 = _("Aggregated stats for MP")
        verbose_name_plural = _("Aggregated stats for MPs")
        unique_together = (('person', 'term',),)

    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


    @property
    def votes(self):
        """
        Property votes

        returns a Votes instance for given stat.
        """
        if self._votes == None:
            self._votes =  Votes(
                    vAye = self.votesAye,
                    vNay = self.votesNay,
                    vRefrains = self.votesRefrain,
                    absences = self.absences,
                    excused = self.excused,
                    sDate = self.dateStart,
                    eDate = self.dateEnd
            )
        return self._votes
                

class MpDayStatsManager(models.Manager):
    """
    Class MpDayStatsManager(models.Manager)

    Default manager for MpDayStats class.
    """
    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def createStatsForDay(self, date):
        """
        Method createStatsForDay(date)

        Method computes a day stats for all MPs in given day.

        Returns void
        """
        select = """
            INSERT OR IGNORE INTO psp_mpdaystats (
                    person_id,
                    date,
                    votes_aye,
                    votes_nay,
                    votes_refrain,
                    absences,
                    excused
                )
                SELECT  
                    id,
                    '%d-%02d-%02d',
                    aye,
                    nay,
                    refrains,
                    abs,
                    excs
                    FROM (
                SELECT  person.id AS id,
                        person.mpId AS mpId,
                        person.name AS name,
                        person.surname AS surname,
                        person.title AS title,
                        person.titleLast AS titleLast,
                        person.birthDate AS birthDate,
                        person.email AS email,
                        person.office AS office,
                        person.regionOffice AS regionOffice,
                        person.phone AS phone,
                        person.slug AS slug,
                        person.homePage AS homePage,
                        person.nasiPoliticiUrl AS nasiPoliticiUrl,
                        SUM(
                            CASE
                                WHEN vote.result = 'M' THEN 1
                                ELSE 0
                            END
                        ) AS abs,
                        SUM(
                            CASE
                                WHEN vote.result = '0' OR
                                    vote.result = 'X' THEN 1
                                ELSE 0
                            END
                        ) AS excs,
                        SUM(
                            CASE
                                WHEN vote.result = 'N' THEN 1
                                ELSE 0
                            END
                        ) AS nay,
                        SUM (
                            CASE
                                WHEN vote.result = 'Z' THEN 1
                                ELSE 0
                            END
                        ) AS refrains,
                        SUM (
                            CASE
                                WHEN vote.result = 'A' THEN 1
                                ELSE 0
                            END
                        ) AS aye
                        FROM psp_poll AS poll
                            INNER JOIN psp_mpvote AS vote ON
                                poll.id = vote.poll_id
                            INNER JOIN psp_person AS person ON
                                vote.person_id = person.id
                        WHERE poll.date = '%d-%02d-%02d'
                        GROUP BY person.id
            ) GROUP BY id;
        """
        select = select % ( date.year, date.month, date.day,
                            date.year, date.month, date.day
                        )
        cursor = connection.cursor()
        cursor.execute(select)

        return


    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def getStatsForRange(self, mp, dateStart, dateEnd = None):
        """
        Method getStatsForRange(mp, dateStart, dateEnd = None)

        Method returns MpDayStats instance for given MP and date range (if both
        arguments dateStart, dateEnd are specified). The arguments are as
        follows:
            mp  -   Person ID or person instance,
            dateStart   -   datetime.date instance representing date instance
            dateEnd     -   datetime.date instance if specified then
                            stats for <dateStart, dateEnd> range are
                            retreived. if no dateEnd is specified then
                            stats for single date are retrieved.

        Returns agregated stats for single MP and chosen date range.
        """
        mpId = None
        person = None
        if type(mp) == type(0):
            mpId = mp
            #suppress 'no objects member' warning
            #pylint: disable-msg=E1101
            person = Person.objects.get(id = mpId)
        else:
            mpId = mp.id
            person = mp

        if dateEnd == None:
            select = """
                SELECT  SUM(ds.votes_aye),
                        SUM(ds.votes_nay),
                        SUM(ds.votes_refrain),
                        SUM(ds.absences),
                        SUM(ds.excused)
                FROM psp_mpdaystats AS ds
                WHERE ds.person_id = %d AND ds.date = '%d-%02d-%02d';
            """
            select = select % ( mpId,
                                dateStart.year, dateStart.month, dateStart.day
                            )
        else:
            select = """
                SELECT  SUM(ds.votes_aye),
                        SUM(ds.votes_nay),
                        SUM(ds.votes_refrain),
                        SUM(ds.absences),
                        SUM(ds.excused)
                FROM psp_mpdaystats AS ds
                WHERE ds.person_id = %d AND
                    ds.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d';
            """
            select = select % ( mpId,
                                dateStart.year, dateStart.month, dateStart.day,
                                dateEnd.year, dateEnd.month, dateEnd.day
                            )
        

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()

        if len(rows) != 1:
            return None
        
        retVal = MpDayStats()

        row = rows[0]
        retVal.votesAye = row[0]
        retVal.votesNay = row[1]
        retVal.refrains = row[2]
        retVal.absences = row[3]
        retVal.excused = row[4]
        retVal.dateStart = dateStart
        retVal.dateEnd = dateEnd
        retVal.person = person 

        return  retVal
        

class MpDayStats(models.Model):
    """
    Class MpDayStats(models.Model)

    MpDayStats provides statistic aggregation for day and MP (Person).
    These attributes are fetched from DB:
        person  -   is ForeignKey referres to Person
        date    -   datetime.date instance
        votesAye    -   votes aye
        votesNay    -   votes nay
        refrains    -   refrains
        absences    -   absences
        excused     -   excused absences
    """
    #suppress 'too many instance attributes' warning
    #pylint: disable-msg=R0902
    person = models.ForeignKey(Person, db_index = True)
    date = models.DateField(_("Date"))
    votesAye = models.IntegerField(_("Votes For"), db_column = 'votes_aye')
    votesNay = models.IntegerField(_("Votes For"), db_column = 'votes_nay')
    refrains = models.IntegerField(_("Refrains"), db_column = 'votes_refrain')
    absences = models.IntegerField(_("Absences"))
    excused = models.IntegerField(_("Excused Absences"))
    dateStart = None
    dateEnd = None
    _votes = None

    objects = MpDayStatsManager()

    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 = _("Aggregated stats for MP and day")
        verbose_name_plural = _("Aggregated stats for MPs and day")
        unique_together = (('person', 'date',),)

    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

    @property
    def votes(self):
        """
        Property votes returns instance of Votes class.
        """
        if self._votes == None:
            self._votes = Votes(
                vAye = self.votesAye,
                vNay = self.votesNay,
                vRefrains = self.refrains,
                absences = self.absences,
                excused = self.excused,
                sDate = self.dateStart,
                eDate = self.dateEnd
            )
        return self._votes


class GroupStatsManager(models.Manager):
    """
    Class GroupStatsManager(models.Manager)

    GroupStatsManager provides default manager for GroupStats class.
    """
    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def getStats(self, group, dateStart = None, dateEnd = None):
        """
        Method getStats(group, dateStart = None, dateEnd = None)

        Method returns stats for particular group for complete term (each group
        is bound to term by dafault). The arguments are as follows:
            group   -   group id or Group instance
            dateStart   -   if None, then stats for complete term are
                            retrievved, otherwise just for particular
                            day
            dateEnd     -   if specified then stats for <dateStart, dateEnd>
                            range are retrieved.

        Returns a GroupStats instance.
        """
        groupId = group_to_id(group)
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        g = Group.objects.get(id = groupId)
        retVal = GroupStats()
        select = None

        if dateStart == None and dateEnd == None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result = 'A' THEN 1
                                ELSE 0
                            END
                        ) AS aye,
                        SUM(
                            CASE
                                WHEN gs.result = 'N' THEN 1
                                ELSE 0
                            END
                        ) AS nay,
                        SUM(
                            CASE
                                WHEN gs.result = 'Z' THEN 1
                                ELSE 0
                            END
                        ) AS refrains,
                        SUM(
                            CASE
                                WHEN gs.result NOT IN ('A', 'N', 'Z') THEN 1
                                ELSE 0
                            END
                        ) AS absences,
                        SUM(1) AS total
                    FROM    psp_groupstats AS gs, psp_poll AS poll,
                            psp_meeting AS meeting
                    WHERE   meeting.term_id = %d AND
                            poll.meeting_id = meeting.id AND
                            gs.group_id = %d AND gs.poll_id = poll.id;
            """
            select = select % (g.term.id, g.id)
        elif dateStart != None and dateEnd == None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result = 'A' THEN 1
                                ELSE 0
                            END
                        ) AS aye,
                        SUM(
                            CASE
                                WHEN gs.result = 'N' THEN 1
                                ELSE 0
                            END
                        ) AS nay,
                        SUM(
                            CASE
                                WHEN gs.result = 'Z' THEN 1
                                ELSE 0
                            END
                        ) AS refrains,
                        SUM(
                            CASE
                                WHEN gs.result NOT IN ('A', 'N', 'Z') THEN 1
                                ELSE 0
                            END
                        ) AS absences,
                        SUM(1) AS total
                    FROM    psp_groupstats AS gs, psp_poll AS poll
                    WHERE   poll.date = '%d-%02d-%02d' AND
                            gs.poll_id = poll.id AND gs.group_id = %d
            """
            select = select % ( dateStart.year, dateStart.month, dateStart.day,
                                groupId
                            )
        elif dateStart != None and dateEnd != None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result = 'A' THEN 1
                                ELSE 0
                            END
                        ) AS aye,
                        SUM(
                            CASE
                                WHEN gs.result = 'N' THEN 1
                                ELSE 0
                            END
                        ) AS nay,
                        SUM(
                            CASE
                                WHEN gs.result = 'Z' THEN 1
                                ELSE 0
                            END
                        ) AS refrains,
                        SUM(
                            CASE
                                WHEN gs.result NOT IN ('A', 'N', 'Z') THEN 1
                                ELSE 0
                            END
                        ) AS absences,
                        SUM(1) AS total
                    FROM    psp_groupstats AS gs, psp_poll AS poll
                    WHERE   poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
                            AND gs.poll_id = poll.id AND gs.group_id = %d
            """
            select = select % ( dateStart.year, dateStart.month, dateStart.day,
                                dateEnd.year, dateEnd.month, dateEnd.day,   
                                g.id
                            )
        else:
            return None

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()

        if len(rows) != 1:
            return None
        
        row = rows[0]
        retVal.votesAye = row[0]
        retVal.votesNay = row[1]
        retVal.votesRefrain = row[2]
        retVal.absences = row[3]
        #suppress warning 'Attribute totalVotes defined outside of init'
        #pylint: disable-msg=W0201
        retVal.totalVotes = row[4]
        retVal.dateStart = dateStart
        retVal.dateEnd = dateEnd

        return retVal


    #suppress 'method could be a function' refactor warning
    #pylint: disable-msg=R0201
    def getAccordanceWithGroup(self, group, accordGroup, dateStart, dateEnd):
        """
        Method getAccordanceWithGroup(group, accordGroup, dateStart, dateEnd)

        Method computes group accordance with accordGroup. Returns an instance
        of Group (group), which is extended by these attributes:
            accordance  -   an instance of Accordance class
            accordGroup -   the argument accordGroup
        The arguments are as follows:
            group               -   is ID of group or Group instance,
            accordGroup         -   is ID of group or Group instance,
            dateStart, dateEnd  -   define date range we want to gather
                                    stats for. If both are None, then stats
                                    for complete term are computed, if
                                    dateStart only is defined, then method
                                    will compute stats for particular single
                                    'dateStart' day. Both arguments are
                                    datetime.date instances.
        Returns an instance of Group class, which is group itself.
        """
        select = ''
        groupId = group_to_id(group)
        accordGroupId = group_to_id(accordGroup)
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        retVal = Group.objects.get(id = groupId)

        if dateStart == None and dateEnd == None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result = accordGs.result THEN 1
                                ELSE 0
                            END
                        ) AS same,
                        SUM(
                            CASE
                                WHEN gs.result != accordGs.result THEN 1
                                ELSE 0
                            END
                        ) AS diff,
                        SUM(1) AS total
                    FROM    psp_groupstats AS gs, psp_groupstats AS accordGs,
                            psp_meeting AS m, psp_poll AS poll
                    WHERE   m.term_id = %d AND poll.meeting_id = m.id AND
                            gs.group_id = %d AND accordGs.group_id = %d AND
                            gs.poll_id = poll.id AND accordGs.poll_id = poll.id;
            """
            select = select % (retVal.term.id, groupId, accordGroupId)
        elif dateStart != None and dateEnd == None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result = accordGs.result THEN 1
                                ELSE 0
                            END
                        ) AS same,
                        SUM(
                            CASE
                                WHEN gs.result != accordGs.result THEN 1
                                ELSE 0
                            END
                        ) AS diff,
                        SUM(1) AS total
                    FROM    psp_groupstats AS gs, psp_groupstats AS accordGs,
                            psp_poll AS poll
                    WHERE   poll.date = '%d-%02d-%02d' AND
                            gs.group_id = %d AND accordGs.group_id = %d AND
                            gs.poll_id = poll.id AND accordGs.poll_id = poll.id;
            """
            select = select % ( dateStart.year, dateStart.month, dateStart.day,
                                groupId, accordGroupId
                            )
        elif dateStart != None and dateEnd != None:
            select = """
                SELECT  SUM(
                            CASE
                                WHEN gs.result = accordGs.result THEN 1
                                ELSE 0
                            END
                        ) AS same,
                        SUM(
                            CASE
                                WHEN gs.result != accordGs.result THEN 1
                                ELSE 0
                            END
                        ) AS diff,
                        SUM(1) AS total
                    FROM    psp_groupstats AS gs, psp_groupstats AS accordGs,
                            psp_poll AS poll
                    WHERE   poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d' AND
                            gs.group_id = %d AND accordGs.group_id = %d AND
                            gs.poll_id = poll.id AND accordGs.poll_id = poll.id;
            """
            select = select % ( dateStart.year, dateStart.month, dateStart.day,
                                dateEnd.year, dateEnd.month, dateEnd.day,
                                groupId, accordGroupId
                            )
        else:
            return None

        cursor = connection.cursor()
        cursor.execute(select)
        rows = cursor.fetchall()

        if len(rows) != 1:
            return None
        
        row = rows[0]
        #suppress 'no objects member' warning
        #pylint: disable-msg=E1101
        retVal.accordanceGroup = Group.objects.get(id = accordGroupId)
        retVal.accordance = Accordance(
            same = row[0],
            diff = row[1],
            sDate = dateStart,
            eDate = dateEnd
        )

        return retVal


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

        Method createStats(poll) computes GroupStats for given poll and all
        groups.

        Returns None
        """
        pollId = poll_to_id(poll)
        insert = """
            INSERT OR IGNORE INTO psp_groupStats (
                    poll_id,
                    group_id,
                    aye,
                    nay,
                    refrains,
                    absences,
                    result
                )
            SELECT  
                    poll.id,
                    membership.group_id,
                    SUM(
                        CASE
                            WHEN vote.result = 'A' THEN 1
                            ELSE 0
                        END
                    ) AS aye,
                    SUM(
                        CASE
                            WHEN vote.result = 'N' THEN 1
                            ELSE 0
                        END
                    ) AS nay,
                    SUM(
                        CASE
                            WHEN vote.result = 'Z' THEN 1
                            ELSE 0
                        END
                    ) AS refrains,
                    SUM(
                        CASE
                            WHEN vote.result IN ('0', 'X', 'M') THEN 1
                            ELSE 0
                        END
                    ) AS absences,
                    ' ' as result
                FROM psp_poll AS poll
                    INNER JOIN psp_mpVote AS vote ON poll.id = vote.poll_id
                    INNER JOIN psp_membership AS membership ON
                            membership.person_id = vote.person_id
                    INNER JOIN psp_group AS groupa ON
                            membership.group_id = groupa.id
                    INNER JOIN psp_meeting AS meeting ON
                            meeting.term_id = groupa.term_id AND
                            poll.meeting_id = meeting.id
                WHERE (
                        membership.post = 'člen' OR
                        membership.post = 'poslanec'
                    ) AND poll.id = %d
                GROUP BY poll.id, membership.group_id
            ;
        """
        insert = insert % pollId
        cursor = connection.cursor()
        cursor.execute(insert)
        
        return


class GroupStats(models.Model):
    """
    Class GroupStats(models.Model)

    GroupStats aggregates poll results for group and poll.
    These attributes are fetched from database:
        poll    -   ForeignKey, which referres to poll
        group   -   ForeignKey, which referres to group
        votesAye    -   number of Ayes in group for given poll
        votesNay    -   number of Nays in group for given poll
        votesRefrain    -   number of refrains in group for poll
        absences    -   number of absences
        result  -   if poll was supported or denied by group
    """
    #suppress 'too many instance attributes' warning
    #pylint: disable-msg=R0902
    poll = models.ForeignKey(Poll, db_index = True)
    group = models.ForeignKey(Group, db_index = True)
    votesAye = models.IntegerField(_("Group Aye"), db_column = 'aye')
    votesNay = models.IntegerField(_("Group Nay"), db_column = 'nay')
    votesRefrain = models.IntegerField( _("Group Refrains"),
                                        db_column = 'refrains'
                                    )
    absences = models.IntegerField(_("Group Absences"))
    result = models.CharField(_("Result"), max_length = 20)
    dateStart = None
    dateEnd = None
    _votes = None
    _accordance = None

    objects = GroupStatsManager()

    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 = _("Group Statistic")
        verbose_name_plural = _("Group Statistics")
        unique_together = (('poll', 'group',),)

    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 getResult(self):
        """
        Method getResult()

        Method converts the stats to result code, if result code is still not
        defined (self.result == ' ' or self.result == '').
        The result codes are same as for MpVote.result
            A   for
            N   against
            Z   refrain
            0   absent (no difference is being made between excused and absent
                in case of GroupStats)
        Returns result code
        """
        if self.result == ' ' or self.result == '':
            #suppress redefining built-in max
            #pylint: disable-msg=W0622
            max = {}
            max['value'] = 0
            max['key'] = ''
            for k in (  'votesAye',
                        'votesNay',
                        'votesRefrain',
                        'absences'
                    ):
                if max['value'] < self.__getattribute__(k):
                    max['value'] = self.__getattribute__(k)
                    max['key'] = k
                
            if max['key'] == 'votesAye':
                self.result = 'A'
            elif max['key'] == 'votesNay':
                self.result = 'N'
            elif max['key'] == 'votesRefrain':
                self.result = 'Z'
            else:
                self.result = '0'

            self.save()

        if self.result == 'A':
            return _(u'Pro')
        elif self.result == 'N':
            return _(u'Proti')
        elif self.result == 'Z':
            return _(u'Zdrželi')
        else:
            return _(u'Chyběli')


    @property
    def votes(self):
        """
        Property votes returns Votes instance
        """
        if self._votes == None:
            self._votes = Votes(
                vAye = self.votesAye,
                vNay = self.votesNay,
                vRefrains = self.votesRefrain,
                absences = self.absences
            )

        return self._votes


    def getResultChart(self):
        """
        Method getResultChart()
        
        Method returns pie showing the result.  Returns a pie chart for a group
        stat. Pie chart shows percent of votes
            for,
            against,
            reefrains,
            absences
        the default size is 400x80

        Returns GChart.Pie3D instance
        """
        retVal = None
    
        if self.votes.totalVotes != 0:
            retVal = self.votes.getVotesChart(group = True)
            retVal.size(400, 80)

        return retVal


class SiteStateData(models.Model):
    """
    Class SiteStateData
    
    class is a container for various run-time data for site.
    These attributes are stored into DB:
        recentUpdate    -   datetime.date - the last update date from psp.cz
        recentMeeting   -   ForeignKey referring to last poll processed.
    """
    recentUpdate = models.DateField(_("Recent site update"), blank = True)
    recentMeeting = models.ForeignKey(Meeting)