PageRenderTime 267ms CodeModel.GetById 141ms app.highlight 76ms RepoModel.GetById 24ms app.codeStats 2ms

/psp/models.py

http://parliament-poll-stats.googlecode.com/
Python | 4721 lines | 4699 code | 1 blank | 21 comment | 6 complexity | 4ed341d9c73fe68daafddd46b79d14cc MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1#coding=utf-8
   2#suppress 'too many' lines warning
   3#pylint: disable-msg=C0302
   4
   5"""
   6copyright (c) 2009, paketka@gmail.com et. al
   7All rights reserved.
   8
   9Redistribution and use in source and binary forms, with or without
  10modification, are permitted provided that the following conditions are met:
  11
  12    * Redistributions of source code must retain the above copyright notice,
  13      this list of conditions and the following disclaimer.
  14
  15    * Redistributions in binary form must reproduce the above copyright notice,
  16      this list of conditions and the following disclaimer in the documentation
  17      and/or other materials provided with the distribution.
  18
  19    * Neither the name of the <ORGANIZATION> nor the names of its contributors
  20      may be used to endorse or promote products derived from this software
  21      without specific prior written permission.
  22
  23THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
  24ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  25WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  26DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
  27FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
  28DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
  29SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
  30CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
  31OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  32OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  33"""
  34
  35import datetime
  36import re
  37
  38from django.shortcuts import get_object_or_404
  39from django.db import models
  40from django.db.models import Q
  41from django.http import Http404
  42from django.utils.translation import ugettext as _
  43from django.db.models.signals import pre_save
  44from django.contrib.localflavor.us.models import PhoneNumberField
  45from django.core.exceptions import ObjectDoesNotExist, MultipleObjectsReturned
  46from django.core.exceptions import FieldError
  47from django.db import connection
  48from common.models import AutoSlug, makeSlug
  49import settings
  50from django.core.urlresolvers import reverse
  51
  52from GChartWrapper import Pie3D
  53from utils.charts import Votes, Accordance, get_percents
  54
  55def term_to_id(term):
  56    """
  57    Function term_to_id(term)
  58
  59    Retrieves a primary key from term instance of TermOfOffice class.
  60    If term is integer function just returns the term value.
  61
  62    Returns term's instance key
  63    """
  64    if type(term) == type(0):
  65        term = get_object_or_404(TermOfOffice, id = term)
  66        return term.id
  67    else:
  68        return term.id
  69
  70
  71def person_to_id(person):
  72    """
  73    Function person_to_id(person)
  74
  75    Retrieves a primary key from person instance of Person class.
  76    If person is integer function just returns the person value.
  77
  78    Returns person's instance primary key
  79    """
  80    if type(person) == type(0):
  81        return person
  82    else:
  83        return person.id
  84
  85
  86def group_to_id(group):
  87    """
  88    Function group_to_id(group)
  89
  90    Retrieves a primary key from person instance of Group class.
  91    If group is integer function just returns the group value.
  92
  93    Returns group's instance primary key
  94    """
  95    if type(group) == type(0):
  96        return group
  97    else:
  98        return group.id
  99
 100
 101def poll_to_id(poll):
 102    """
 103    Function poll_to_id(poll)
 104
 105    Retrieves a primary key from poll instance of Poll class.
 106    If poll is integer function just returns the poll value.
 107
 108    Returns poll's instance primary key
 109    """
 110    if type(poll) == type(0):
 111        return poll
 112    else:
 113        return poll.id
 114
 115class TermOfOffice(AutoSlug):
 116    """
 117    Class TermOfOffice(AutoSlug)
 118
 119    The TermOfOffice represents a period of parliament between two pools.
 120    There are several attributes:
 121        yearStart   -   the first electoral year (string)
 122        yearEnd     -   the last electoral year (string)
 123        slug        -   string (yearStart_yearEnd), autogenerated from
 124                        yearStart, yearEnd
 125        termId      -   term ID assigned at psp.cz site
 126    """
 127    yearStart = models.CharField(_("Start Year"), max_length = 4)
 128    yearEnd = models.CharField(_("End Year"), max_length = 4)
 129    slug = models.SlugField(db_index = True, unique = True)
 130    termId = models.CharField(_("Term Id"), max_length = 4)
 131    slugs = {
 132        'slug' : ('yearStart', 'yearEnd', ),
 133    }
 134                            
 135    class Meta:
 136        """
 137        See http://docs.djangoproject.com/en/dev/ref/models/options/
 138        for details
 139        """
 140        #suppress class has no __init__ method
 141        #pylint: disable-msg=W0232
 142        #suppress too few public methods warning
 143        #pylint: disable-msg=R0903
 144        verbose_name = _("Term of Office")
 145        verbose_name_plural = _("Terms of Office")
 146    
 147    class Admin:
 148        """
 149        See http://docs.djangoproject.com/en/dev/intro/tutorial02
 150        for details
 151        """
 152        #suppress class has no __init__ method
 153        #pylint: disable-msg=W0232
 154        #suppress too few public methods warning
 155        #pylint: disable-msg=R0903
 156        pass
 157
 158
 159    def isComplete(self):
 160        """
 161        Method isComplete()
 162
 163        Method returns true if the given term instance is complete.
 164        The complete term instance has yearEnd attribute filled in. It
 165        actually means the given electoral term is over.
 166
 167        Returns True if term is complete (over)
 168        """
 169        return self.yearEnd != ''
 170
 171
 172    def getStart(self):
 173        """
 174        Method getStart()
 175
 176        Returns yearStart attribute as integer.
 177
 178        Returns yearEnd attribute as integer.
 179        """
 180        return int(self.yearStart)
 181
 182
 183    def getEnd(self):
 184        """
 185        Method getEnd()
 186
 187        Method returns yearEnd attribute as integer. If there is no
 188        yearEnd set yet, which happens for current terms, the method
 189        will return the current year instead.
 190
 191        Returns yearEnd attribute as integer, if there is no yearEnd
 192        attribute set, function will return a current year.
 193        """
 194        if self.yearEnd == '':
 195            return datetime.datetime.today().year
 196        else:
 197            return int(self.yearEnd)
 198
 199
 200    def getStartDate(self):
 201        """
 202        Method getStartDate()
 203
 204        We have to create faked start date of given term, it will be Jan 1 of
 205        the yearStart.
 206
 207        Returns datetime.date(int(yearEnd), day = 1, month = 1)
 208        """
 209        retVal = datetime.date(
 210                        day = 1,
 211                        month = 1,
 212                        year = self.getStart()
 213                    )
 214        return retVal
 215    
 216
 217    def getEndDate(self):
 218        """
 219        Method getEndDate()
 220
 221        We have to create faked end date of given term, it will be Dec 31
 222        of the yearEnd.
 223
 224        Returns datetime.date(int(yearStart), day = 31, month = 12)
 225        """
 226        retVal = datetime.date(
 227                        day = 31,
 228                        month = 12,
 229                        year = self.getEnd()
 230                    )
 231        return retVal
 232
 233
 234    def getRange(self):
 235        """
 236        Method getRange()
 237
 238        Method returns a date range represeting given term instance
 239        as a tupple of two datetime.date instances, represeting
 240        (lowerBound, upperBound)
 241
 242        Returns a tupple (self.getStartDate(), self.getEndDate())
 243        """
 244
 245        return (self.getStartDate(), self.getEndDate(),)
 246
 247    def getTermsList(self):
 248        """
 249        Method getTermsList()
 250
 251        Returns list of terms rendered to .html tags. It's optimized for views
 252        which are using using ui-jquery .css.  It renders links to terms, which
 253        are other than 'self'.
 254        
 255        Method is used in templates. It follows DRY principle, but violates
 256        MVC model - the presentation logic is implemented here.
 257
 258        Returns list of strings with .html tags.
 259        """
 260        retVal = []
 261        #suppress 'no objects member' warning
 262        #pylint: disable-msg=E1101
 263        for t in TermOfOffice.objects.all():
 264            url = u''
 265            content = '%d - %d' % (t.getStart(), t.getEnd())
 266            if t == self:
 267                url = u'<span>%s</span>' % content
 268                url += u'<span class="ui-icon ui-icon-triangle-1-w"></span>'
 269            else:
 270                url = u'<a href="%s">%s</a>' % (
 271                            reverse('term_tab',
 272                                        args = [ 
 273                                            str(t.getStart()),
 274                                            str(t.getEnd())
 275                                        ]
 276                            ),
 277                            content,
 278                        )
 279            retVal.append(url)
 280
 281        return retVal
 282
 283    @staticmethod
 284    def get_term_or_404(start, end):
 285        """
 286        Static method get_term_or_404(start, end)
 287
 288        Returns a TermOfOffice instance for given start, end year range. If
 289        there is no such term in DB, function (method) will raise Http404
 290        exception.
 291
 292        The mandatory arguments start, end can be either strings or
 293        integer numbers:
 294            start   -   start year for term
 295            end     -   end year fo term
 296
 297        Returns matching TermOfOffice instance or raises Http404
 298        """
 299        retVal = None
 300
 301        if type(start) != type(''):
 302            start = str(start)
 303
 304        if type(end) != type(''):
 305            end = str(end)
 306
 307        try:
 308            #suppress 'no objects member' warning
 309            #pylint: disable-msg=E1101
 310            retVal = TermOfOffice.objects.get(yearStart = start, yearEnd = end)
 311        except ObjectDoesNotExist:
 312            if (int(start) <= int(end)):
 313                try:
 314                    #suppress 'no objects member' warning
 315                    #pylint: disable-msg=E1101
 316                    retVal = TermOfOffice.objects.get(
 317                                    yearStart = start,
 318                                    yearEnd = ''
 319                                )
 320                except ObjectDoesNotExist:
 321                    raise Http404
 322            else:
 323                raise Http404
 324
 325        return retVal
 326
 327    @staticmethod
 328    def get_term_for_date(date):
 329        """
 330        Static method get_term_for_date(date)
 331
 332        Returns the term for date. The mandatory argument date is a
 333        datetime.date object.
 334
 335        Returns instance of TermOfOffice class.
 336        """
 337        #suppress 'no objects member' warning
 338        #pylint: disable-msg=E1101
 339        for t in TermOfOffice.objects.all().order_by('-id'):
 340            if date >= t.getStartDate() and date <= t.getEndDate():
 341                return t
 342
 343        return None
 344                
 345
 346pre_save.connect(makeSlug, TermOfOffice)
 347
 348
 349class Division(AutoSlug):
 350    """
 351    Class Division(AutoSlug)
 352
 353    Division class represents an electoral area. The contains these attributes:
 354        term    -   link (ForeignKey) to term instance the particular division
 355                    is bound to
 356        name    -   the division name (i.e. 'Karlovarsky kraj')
 357        icon    -   image icon (currently unused)
 358        slug    -   slug field (autogenerated from name)
 359    """
 360    term = models.ForeignKey(TermOfOffice)
 361    name = models.CharField(_("Division Name"), max_length = 20)
 362    icon = models.ImageField(_("Division Symbol"), 
 363                                upload_to = settings.SYMBOLS,
 364                                blank = True
 365                            )
 366    divId = models.CharField(_("Division Id"), max_length = 10)
 367    slug = models.SlugField(db_index = True, unique = True)
 368    slugs = {
 369        'slug' : ('name', )
 370    }
 371
 372    class Meta:
 373        """
 374        See http://docs.djangoproject.com/en/dev/ref/models/options/
 375        for details
 376        """
 377        #suppress class has no __init__ method
 378        #pylint: disable-msg=W0232
 379        #suppress too few public methods warning
 380        #pylint: disable-msg=R0903
 381        verbose_name = _("Division")
 382        verbose_name_plural = _("Divisions")
 383        unique_together = (('term', 'divId',),)
 384
 385    def getName(self):
 386        """
 387        Method getName()
 388
 389        Method strips 'Volebni kraj' string from data, which are stored in DB.
 390        
 391        Returns the division name.
 392        """
 393        pattern = re.compile(u'.*Volební\s*kraj(?P<name>.*)', re.U)
 394        match = pattern.match(self.name)
 395        retVal = u''
 396        if match:
 397            retVal += match.group('name')
 398        else:
 399            retVal = self.name
 400        
 401        return retVal.strip()
 402        
 403
 404pre_save.connect(makeSlug, Division)
 405
 406#suppress warning too many public methods
 407#pylint: disable-msg=R0904
 408class PersonManager(models.Manager):
 409    """
 410    Class PersonManager
 411
 412    PersonManager provides predefined select to retrieve Person instances from DB.
 413    Depending on method used the Person instance might be extended by extra attributes
 414    (see method descriptions further down).
 415
 416    The PersonManager is a default manager for Person class.
 417    """
 418    def createPersonFromRow(self, row):
 419        """
 420        Method createPersonFromRow
 421
 422        Method creates an instance of Person class from row, fetched from DB.
 423        The row is list, which represents the row retrieved. The method expects
 424        row argument to contain at least 14 elements. The order of elements
 425        in array is as follows:
 426            0   -   integer representinf Person's instance primary key
 427            1   -   mpId string representing person's key at psp.cz
 428            2   -   name (unicode string)
 429            3   -   surname (unicode string)
 430            4   -   title prepended before first name (unicode string)
 431            5   -   title, which follows sutname (unicode string)
 432            6   -   birthDate (datetime.date instance)
 433            7   -   email (string)
 434            8   -   office address (string)
 435            9   -   regionOffice address (office located at MP's home town)
 436            10  -   MP's phone (string)
 437            11  -   slug, which is a parameter to construct link (string)
 438            12  -   homePage/blog link to blog (string)
 439            13  -   link to profile at http://nasipolitici.cz
 440
 441        Returns instance of Person class
 442        """ 
 443        person = self.model(
 444                    id = row[0],    #primary Key
 445                    mpId = row[1], #mpId
 446                    name = row[2], #name
 447                    surname = row[3], #surname
 448                    title = row[4], #title
 449                    titleLast = row[5], #titleLast
 450                    birthDate = row[6], #birthDate
 451                    email = row[7], #email
 452                    office = row[8], #office
 453                    regionOffice = row[9], #regionOffice
 454                    phone = row[10], #phone number
 455                    slug = row[11], # slug field
 456                    homePage = row[12], # homePage/blogField
 457                    nasiPoliticiUrl = row[13]
 458                )
 459                    
 460        return person
 461
 462
 463    def commonSelect(self, where, orderBy, desc, term = None):
 464        """
 465        Method commonSelect(where, orderBy, desc, term = None)
 466
 467        Retrieves a list of Person objects matching the query. The application
 468        should never use commonSelect() directly. It should use commonSelect()
 469        wrappers instead:
 470            getPersons(), getPersonsOrderByName(),getPersonsOrderByAge(),
 471            getPersonsOrderByDivision(), getPersonsOrderByParty(),
 472            getPersonsOrderByAbsences(), getPerson()
 473        All these methods are explained further in the text.
 474
 475        The arguments are parts of SELECT command:
 476            where   -   is optional WHERE clause (it can be an empty string)
 477            order   -   by is order by statement
 478            desc    -   is boolean flag if true the order will be descendant
 479            term    -   optional argument, if no term is passed, all MPs are
 480                        selected.
 481
 482        Besides standard attribute the Person instance is extended by some
 483        more:
 484            detail  -   an instance of PersonDetail class
 485            divisionName    -   the name of electoral division the MP comes
 486                                from
 487            partyName   -   the name of party the MP is member of
 488            votes   -   is an instance of Votes class. It wraps up the
 489                        vote stats for given MP in term.
 490            terms   -   in case no term was specified, this attribute contains
 491                        list of all terms the MP was member of parliament.
 492
 493        Returns list of Person objects extended by few attributes (see above).
 494        """
 495        #suppress too many branches refactore warning
 496        #pylint: disable-msg=R0912
 497        retVal = []
 498        select = ''
 499        termId = None
 500
 501        if orderBy != None and orderBy != '':
 502            if desc:
 503                orderBy += ' DESC'
 504            else:
 505                orderBy += ' ASC'
 506
 507        if term != None:
 508            termId = term_to_id(term)
 509            select = u"""
 510                SELECT DISTINCT person.id,
 511                    person.mpId,
 512                    person.name,
 513                    person.surname,
 514                    person.title,
 515                    person.titleLast,
 516                    person.birthDate,
 517                    person.email,
 518                    person.office,
 519                    person.regionOffice,
 520                    person.phone,
 521                    person.slug,
 522                    person.homePage,
 523                    person.nasiPoliticiUrl,
 524                    detail.id,
 525                    division.id,
 526                    party.id,
 527                    SUM(votes.absences) + SUM(votes.excused) AS absences,
 528                    SUM(votes.absences) + SUM(votes.excused) +
 529                            SUM(votes.votes_refrains) +
 530                            SUM(votes.votes_aye) +
 531                            SUM(votes.votes_nay) AS total,
 532                    SUM(votes.votes_aye) AS votesAye,
 533                    SUM(votes.votes_nay) AS votesNay,
 534                    SUM(votes.votes_refrains) AS refrains,
 535                    SUM(votes.absences) AS unexcAbs,
 536                    SUM(votes.excused) AS excsdAbs
 537                    FROM psp_person AS person
 538                    INNER JOIN psp_group AS parlament ON parlament.type LIKE 'PARLAMENT' AND
 539                                parlament.term_id = %d
 540                    INNER JOIN psp_group AS party ON party.type LIKE 'KLUB' AND
 541                                party.term_id = %d
 542                    INNER JOIN psp_membership AS partymship ON
 543                                partymship.group_id = party.id AND
 544                                partymship.post LIKE '%%%%len' AND
 545                                partymship.person_id = person.id
 546                    INNER JOIN psp_membership AS parlmship ON
 547                                parlmship.group_id = parlament.id AND
 548                                parlmship.post LIKE 'poslan%%%%' AND
 549                                parlmship.person_id = person.id
 550                    INNER JOIN psp_persondetail AS detail ON
 551                                detail.person_id = person.id AND
 552                                detail.term_id = %d
 553                    INNER JOIN psp_division AS division ON
 554                                division.id = detail.region_id
 555                    INNER JOIN psp_votestats AS votes ON
 556                                votes.person_id = person.id AND
 557                                votes.term_id = %d
 558                    %s
 559                    GROUP BY person.id %s;
 560            """
 561            select = select % (termId, termId, termId, termId, where, orderBy)
 562        else:
 563            select = u"""
 564                SELECT person.id,
 565                    person.mpId,
 566                    person.name,
 567                    person.surname,
 568                    person.title,
 569                    person.titleLast,
 570                    person.birthDate,
 571                    person.email,
 572                    person.office,
 573                    person.regionOffice,
 574                    person.phone,
 575                    person.slug,
 576                    person.homePage,
 577                    person.nasiPoliticiUrl,
 578                    detail.id,
 579                    division.id,
 580                    party.id,
 581                    SUM(votes.absences) + SUM(votes.excused) AS absences,
 582                    SUM(votes.absences) + SUM(votes.excused) +
 583                            SUM(votes.votes_refrains) +
 584                            SUM(votes.votes_aye) +
 585                            SUM(votes.votes_nay) AS total,
 586                    SUM(votes.votes_aye) AS votesAye,
 587                    SUM(votes.votes_nay) AS votesNay,
 588                    SUM(votes.votes_refrains) AS refrains,
 589                    SUM(votes.absences) AS unexcAbs,
 590                    SUM(votes.excused) AS excsdAbs
 591                    FROM psp_person AS person
 592                    INNER JOIN psp_group AS party ON party.type LIKE 'KLUB'
 593                    INNER JOIN psp_membership AS partymship ON
 594                                partymship.group_id = party.id AND
 595                                partymship.post LIKE '_len' AND
 596                                partymship.person_id = person.id
 597                    INNER JOIN psp_persondetail AS detail ON
 598                                detail.person_id = person.id
 599                    INNER JOIN psp_division AS division ON
 600                                division.id = detail.region_id
 601                    INNER JOIN psp_votestats AS votes ON
 602                                votes.person_id = person.id
 603                    %s
 604                    GROUP BY person.id %s;
 605                """
 606            select = select % (where, orderBy)
 607
 608        cursor = connection.cursor()
 609        cursor.execute(select)
 610        for row in cursor.fetchall():
 611            mp = self.createPersonFromRow(row)
 612
 613            # row[14] - missed polls
 614            # row[15] - total number of all polls
 615            mp.votes = Votes(
 616                vAye = row[19],
 617                vNay = row[20],
 618                vRefrains = row[21],
 619                absences = row[22],
 620                excused = row[23]
 621            )
 622            mp.detail = None
 623            #suppress 'Exception does not do anything' warning
 624            #pylint: disable-msg=W0704
 625            try:
 626                #suppress 'no objects member' warning
 627                #pylint: disable-msg=E1101
 628                mp.detail = PersonDetail.objects.get(id = row[14])
 629            except ObjectDoesNotExist:
 630                pass
 631
 632            mp.divisionName = ''
 633            #suppress 'Exception does not do anything' warning
 634            #pylint: disable-msg=W0704
 635            try:
 636                # row[12] division ID
 637                #suppress 'no objects member' warning
 638                #pylint: disable-msg=E1101
 639                mp.divisionName = Division.objects.get(id = row[15]).getName()
 640            except ObjectDoesNotExist:
 641                pass
 642
 643            mp.partyName = ''
 644            #suppress 'Exception does not do anything' warning
 645            #pylint: disable-msg=W0704
 646            try:
 647                # row[13] party club id
 648                #suppress 'no objects member' warning
 649                #pylint: disable-msg=E1101
 650                mp.partyGroup = Group.objects.get(id = row[16])
 651                mp.partyName = mp.partyGroup.getPartyName() 
 652            except ObjectDoesNotExist:
 653                pass
 654
 655            mp.terms = []
 656            #suppress 'no objects member' warning
 657            #pylint: disable-msg=E1101
 658            for mship in Membership.objects.filter(
 659                                        person = mp,
 660                                        group__type = 'PARLAMENT'
 661                                    ).order_by('group__term__id'):
 662                mp.terms.append(mship.group.term)
 663            
 664            mp.term = term
 665
 666            if term != None:
 667                mp.terms.append(term)
 668                mp.age =  term.getStart() - mp.birthDate.year
 669            else:
 670                try:
 671                    t = mp.terms[len(mp.terms) - 1]
 672                    mp.age = t.getStart() - mp.birthDate.year
 673                except IndexError:
 674                    mp.age = 0
 675
 676            retVal.append(mp)
 677
 678        return retVal
 679
 680
 681    def getPersons(self, desc = False, term = None):
 682        """
 683        Method getPersons(desc = False, term = None)
 684
 685        Method uses commonSelect() to retrieve list of persons. The persons are
 686        ordered by surname, name in ascendant order by default. If no term
 687        argument is used function retrieves list of all MPs, otherwise it
 688        fetches list of MPs for particular term only. All arguments are optional:
 689            desc    -   false on default (the MPs will be ordered in ascendant
 690                        order by surname, name)
 691            term    -   if no term is specified then all MPs are retrieved,
 692                        each MP will have extra attribute terms, which
 693                        a list of all terms he/she was working as MP.
 694
 695        Returns list of MPs ordered by surname, name
 696        """
 697        orderBy = 'ORDER BY person.surname, person.name'
 698        return self.commonSelect(   where = '',
 699                                    orderBy = orderBy,
 700                                    desc = desc,
 701                                    term = term
 702                                )
 703
 704
 705    def getPersonsOrderByName(self, desc = False, term = None):
 706        """
 707        Method getPersonsOrderByName(desc = False, term = None)
 708
 709        Method uses commonSelect() to retrieve list of persons. The persons are
 710        ordered by name, surname in ascendant order by default. If no term
 711        argument is used function retrieves list of all MPs, otherwise it
 712        fetches list of MPs for particular term only. All arguments are optional:
 713            desc    -   false on default (the MPs will be ordered in ascendant
 714                        order by surname, name)
 715            term    -   if no term is specified then all MPs are retrieved,
 716                        each MP will have extra attribute terms, which
 717                        a list of all terms he/she was working as MP.
 718
 719        Returns list of MPs ordered by name, surname
 720        """
 721        orderBy = 'ORDER BY person.name, person.surname'
 722        return self.commonSelect(   where = '',
 723                                    orderBy = orderBy,
 724                                    desc = desc,
 725                                    term = term
 726                                )
 727
 728
 729    def getPersonsOrderByAge(self, desc = False, term = None):
 730        """
 731        Method getPersonsOrderByAge(desc = False, term = None)
 732
 733        Uses commonSelect() to retreive list of person objects. Objects are
 734        ordered by age. The age is computed with respect to the begining of
 735        given term. If no term is given then the recent term the MP was in
 736        parliament is used. By default the MPs are ordered in ascendant
 737        order. The optional arguments are as follows:
 738            desc    -   default False, (descendant/ascendant) order
 739            term    -   if no term is specified then all MPs are retrieved,
 740                        each MP will have extra attribute terms, which
 741                        a list of all terms he/she was working as MP.
 742
 743        Returns list of MPs ordered by age in ascendant order by default.
 744        """
 745        orderBy = 'ORDER BY person.birthDate, person.surname, person.name'
 746        retVal = self.commonSelect( where = '',
 747                                    orderBy = orderBy,
 748                                    desc = desc,
 749                                    term = term
 750                                )
 751        #We need to sort by age here, age is computed with respect to the
 752        #start of parliementar membership.
 753        if desc:
 754            retVal.sort(lambda a, b: a.age - b.age)
 755        else:
 756            retVal.sort(lambda a, b: b.age - a.age)
 757            
 758        return retVal
 759
 760
 761    def getPersonsOrderByDivision(self, desc = False, term = None):
 762        """
 763        Method getPersonsOrderByAge(desc = False, term = None)
 764
 765        Uses commonSelect() to retrieve list of MPs (person objects) ordered by
 766        division name. The defaut sort order is ascendant. If no term is
 767        specified MPs for all terms are retrieved. The optional arguments are:
 768            desc    -   default False, (descendant/ascendant) order
 769            term    -   if no term is specified then all MPs are retrieved,
 770                        each MP will have extra attribute terms, which
 771                        a list of all terms he/she was working as MP.
 772
 773        Returns list of MPs ordered by division in ascendant order by default.
 774        """
 775        orderBy = 'ORDER BY division.name, person.surname, person.name'
 776        return self.commonSelect(   where = '',
 777                                    orderBy = orderBy,
 778                                    desc = desc,
 779                                    term = term
 780                                )
 781
 782
 783    def getPersonsOrderByParty(self, desc = False, term = None):
 784        """
 785        Method getPersonsOrderByParty(desc = False, term = None)
 786
 787        Uses commonSelect() to retrieve the list of MPs (person objects) ordered
 788        by the name of party club they are member of. The default sort order
 789        is ascendant. If no term is specified MPs for all terms are retreived.
 790        The optional arguments are as follows:
 791            desc    -   default False, (descendant/ascendant) order
 792            term    -   if no term is specified then all MPs are retrieved,
 793                        each MP will have extra attribute terms, which
 794                        a list of all terms he/she was working as MP.
 795
 796        Returns list of MPs ordered by their party name in ascendant order.
 797        """
 798        orderBy = 'ORDER BY party.name, person.surname, person.name'
 799        return self.commonSelect(   where = '',
 800                                    orderBy = orderBy,
 801                                    desc = desc,
 802                                    term = term
 803                                )
 804
 805    
 806    def getPersonsOrderByAbsences(self, desc = False, term = None):
 807        """
 808        Method getPersonsOrderByAbsences(desc, term)
 809
 810        Uses commonSelect() to retreive list of MPs (person objects) ordered
 811        by absences. The default sort order is ascendant (from the least
 812        absences to the most absences). If no term is specified MPs for all
 813        terms are retrieved. The optional arguments are as follows:
 814            desc    -   default False, (descendant/ascendant) order
 815            term    -   if no term is specified then all MPs are retrieved,
 816                        each MP will have extra attribute terms, which
 817                        a list of all terms he/she was working as MP.
 818
 819        Returns list of MPs ordered by absences in ascendant order.
 820        """
 821        orderBy = 'ORDER BY absences'
 822        retVal = self.commonSelect( where = '',
 823                                    orderBy = orderBy,
 824                                    desc = desc,
 825                                    term = term
 826                                )
 827        #we need to sort by computed absences, which are in relative values,
 828        #the DB select sorts by absolute values, which are misleading
 829        #we need to keep 2 decimal places presition, therefore we mult by 100
 830        if desc:
 831            retVal.sort(
 832                lambda a, b: int(a.votes.totalAbsencesPerc * 100) - \
 833                int(b.votes.totalAbsencesPerc * 100)
 834            )
 835        else:
 836            retVal.sort(
 837                lambda a, b: int(b.votes.totalAbsencesPerc * 100) - \
 838                int(a.votes.totalAbsencesPerc * 100)
 839            )
 840        
 841        return retVal
 842
 843
 844    def getPerson(self, person, term = None):
 845        """
 846        Method getPerson(person, term)
 847
 848        Uses commonSelect() to retrieve a single MP (Person object). If no term
 849        is specified all data from all terms the particular person was member
 850        of parlament are retrieved. The mandatory person argument can be any of
 851        these below:
 852            integer -   primary key for Person object
 853            string  -   slug defining MP
 854            person  -   instance of Person class
 855        If no term is specified the person will be extended by attribute terms,
 856        which is a list of all terms the person was active MP.
 857
 858        Returns Person object or raises ObjectDoesNotExist exception, if no
 859        such MP can be found.
 860        """
 861        where = None
 862        if type(person) == type(0):
 863            where = 'WHERE person.id = %d' % person
 864        elif type(person) == type(u'') or type(person) == type(''):
 865            where = 'WHERE person.slug = "%s"' % person
 866        else:
 867            where = 'WHERE person.id = %d' % person.id
 868
 869        #suppress 'redefining built-in list' warning
 870        #pylint: disable-msg=W0622
 871        list = self.commonSelect(   where = where,
 872                                    orderBy = '',
 873                                    desc = False,
 874                                    term = term
 875                                )
 876
 877        if len(list) != 1:
 878            raise ObjectDoesNotExist
 879
 880        return list[0]
 881            
 882
 883    def getSlackers(self, dayStart, dayEnd = None, desc = True,
 884        colName = 'totalAbsences'):
 885        """
 886        Method getSlackers(dayStart, dayEnd = None, desc = True,
 887                colName = 'totalAbsences')
 888
 889        Returns list of Person objects (MPs) ordered by their absences at
 890        polls for given date (date range). The only mandatory argument is
 891        dayStart, which defines desired day we want to retrieve list for.
 892        The rest of arguments is optional:
 893            dayEnd  -   if used, then dayStart, dayEnd range is defined
 894            desc    -   default value is true, which means the persons
 895                        will be ordered in descendant order (from the
 896                        biggest slackers toward the biggest hardworkers,
 897                        with no absences)
 898            colName -   the default value is 'totalAbsences' which means
 899                        the MPs will be ordered by totalAbsences column.
 900                        In fact you can use any column name you like:
 901                            absences    -   unexcused absences
 902                            excused     -   excused absences
 903                            votesRefrain
 904                            votesAye
 905                            votesNay
 906
 907        Returns list of MPs ordered by their absences.
 908        """
 909        select = None
 910        order = None
 911
 912        if desc:
 913            order = 'DESC'
 914        else:
 915            order = 'ASC'
 916
 917        if dayEnd == None:
 918            select = """
 919                SELECT  id personId,
 920                        mpId AS personMpId,
 921                        name AS personName,
 922                        surname AS personSurname,
 923                        title AS personTitle,
 924                        titleLast AS personTitleLast,
 925                        birthDate AS personBirthDate,
 926                        email AS personEmail,
 927                        office AS personOffice,
 928                        regionOffice AS personRegionOffice,
 929                        phone AS personPhone,
 930                        slug AS personSlug,
 931                        homePage AS personHomePage,
 932                        nasiPoliticiUrl AS personNasiPoliticiUrl,
 933                        absences,
 934                        excused,
 935                        votesAye,
 936                        votesNay,
 937                        votesRefrain,
 938                        (absences + excused) AS totalAbsences FROM (
 939                    SELECT  mp.id AS id,
 940                            mp.mpId AS mpId,
 941                            mp.name AS name,
 942                            mp.surname AS surname,
 943                            mp.title AS title,
 944                            mp.titleLast AS titleLast,
 945                            mp.birthDate AS birthDate,
 946                            mp.email AS email,
 947                            mp.office AS office,
 948                            mp.regionOffice AS regionOffice,
 949                            mp.phone AS phone,
 950                            mp.slug AS slug,
 951                            mp.homePage AS homePage,
 952                            mp.nasiPoliticiUrl AS nasiPoliticiUrl,
 953                            SUM(mpds.absences) AS absences,
 954                            SUM(mpds.excused) AS excused,
 955                            SUM(mpds.votes_aye) AS votesAye,
 956                            SUM(mpds.votes_nay) AS votesNay,
 957                            SUM(mpds.votes_refrain) AS votesRefrain
 958                            FROM    psp_mpdaystats AS mpds
 959                                INNER JOIN psp_person AS mp ON
 960                                    mp.id = mpds.person_id
 961                            WHERE mpds.date = '%d-%02d-%02d'
 962                            GROUP BY id
 963                ) GROUP BY id ORDER BY %s %s;
 964            """
 965            select = select % ( dayStart.year, dayStart.month, dayStart.day,
 966                                colName, order
 967                            )
 968        else:
 969            select = """
 970                SELECT  id personId,
 971                        mpId AS personMpId,
 972                        name AS personName,
 973                        surname AS personSurname,
 974                        title AS personTitle,
 975                        titleLast AS personTitleLast,
 976                        birthDate AS personBirthDate,
 977                        email AS personEmail,
 978                        office AS personOffice,
 979                        regionOffice AS personRegionOffice,
 980                        phone AS personPhone,
 981                        slug AS personSlug,
 982                        homePage AS personHomePage,
 983                        nasiPoliticiUrl AS personNasiPoliticiUrl,
 984                        absences,
 985                        excused,
 986                        votesAye,
 987                        votesNay,
 988                        votesRefrain,
 989                        (absences + excused) AS totalAbsences FROM (
 990                    SELECT  mp.id AS id,
 991                            mp.mpId AS mpId,
 992                            mp.name AS name,
 993                            mp.surname AS surname,
 994                            mp.title AS title,
 995                            mp.titleLast AS titleLast,
 996                            mp.birthDate AS birthDate,
 997                            mp.email AS email,
 998                            mp.office AS office,
 999                            mp.regionOffice AS regionOffice,
1000                            mp.phone AS phone,
1001                            mp.slug AS slug,
1002                            mp.homePage AS homePage,
1003                            mp.nasiPoliticiUrl AS nasiPoliticiUrl,
1004                            SUM(mpds.absences) AS absences,
1005                            SUM(mpds.excused) AS excused,
1006                            SUM(mpds.votes_aye) AS votesAye,
1007                            SUM(mpds.votes_nay) AS votesNay,
1008                            SUM(mpds.votes_refrain) AS votesRefrain
1009                            FROM    psp_mpdaystats AS mpds
1010                                INNER JOIN psp_person AS mp ON
1011                                    mp.id = mpds.person_id
1012                            WHERE mpds.date
1013                                BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
1014                            GROUP BY id
1015                ) GROUP BY id ORDER BY %s %s;
1016            """
1017            select = select % ( dayStart.year, dayStart.month, dayStart.day,
1018                                dayEnd.year, dayEnd.month, dayEnd.day,
1019                                colName, order
1020                            )
1021
1022        cursor = connection.cursor()
1023        cursor.execute(select)
1024        retVal = []
1025        for row in cursor.fetchall():
1026            mp = self.createPersonFromRow(row)
1027            mp.votes = Votes(
1028                    vAye = row[17],
1029                    vNay = row[16],
1030                    vRefrains = row[18],
1031                    absences = row[14],
1032                    excused = row[15],
1033                    sDate = dayStart,
1034                    eDate = dayEnd
1035            ) 
1036
1037            retVal.append(mp)
1038        
1039        return retVal
1040
1041
1042    #suppress 'method could be a function' refactor warning
1043    #pylint: disable-msg=R0201
1044    def countPersonsForDay(self, date):
1045        """
1046        Method countPersonsForDay(date)
1047
1048        Requires date argument, which is an instance of datetime.date object.
1049        It defines the day we want to count all MPs. The method is used to
1050        render charts.
1051
1052        Returns the number of MPs for particular day.
1053        """
1054        select = """
1055            SELECT DISTINCT person.id
1056                FROM psp_person AS person
1057                INNER JOIN psp_group AS party ON party.type LIKE 'KLUB'
1058                INNER JOIN psp_membership AS partymship ON
1059                            partymship.group_id = party.id AND
1060                            partymship.post LIKE 'člen' AND
1061                            partymship.person_id = person.id AND
1062                            ((partymship.end IS NULL AND
1063                                partymship.start <= '%d-%02d-%02d'
1064                            ) OR (
1065                            '%d-%02d-%02d' BETWEEN partymship.start AND
1066                                     partymship.end 
1067                            ))
1068                GROUP BY person.id;
1069        """
1070        select = select % ( date.year, date.month, date.day,
1071                            date.year, date.month, date.day
1072                        )
1073        cursor = connection.cursor()
1074        cursor.execute(select)
1075        return len(cursor.fetchall())
1076
1077
1078    def getRebels(self, dayStart, dayEnd = None, desc = True, colName = 'same'):
1079        """
1080        Method getRebels(dayStart, dayEnd = None, desc = True, colName = 'same')
1081
1082        Retrieves the list of MPs ordered by poll divergence. Rebels are MPs
1083        who are voting against majority of their own party club. The higher
1084        divergence in poll results between particular MP and his/her club the
1085        bigger rebel MP is. The only mandatory argument is dayStart, which is
1086        an instance of datetime.date class. The other arguments are optional:
1087            dayEnd  -   if defined then the list of MPs is retrieved for
1088                        day range <dayStart, dayEnd>
1089            orderDesc   -   by default the rebels (those who diverge most)
1090                            will be at the begining of the list.
1091            colName -   by default we are ordering by 'same' column, other
1092                        possible (reasonable values are):
1093                            activeSame
1094        Same vs. Active - in case of accordance of MP's we need to distinguish
1095        between rebels and active rebels. Rebels are all MP's whose results
1096        differ with majority vote of theri party club including absences - MP's
1097        absences are also included into calculation. The activeSame column,
1098        ignore does not include polls, where particular MP was not present,
1099        only polls, where given MP was actively participating in, these will
1100        be included, thus we call them active - actively voting against
1101        majority of party club.
1102
1103        Returns list of MPs ordered by result divergence between them and their
1104        party club.
1105        """
1106
1107        select = None
1108        order = None
1109        if desc:
1110            order = 'DESC'
1111        else:
1112            order = 'ASC'
1113
1114        activeParm = '\n'
1115
1116        if dayEnd != None:
1117            select = """
1118                SELECT
1119                    person.id,
1120                    person.mpId,
1121                    person.name,
1122                    person.surname,
1123                    person.title,
1124                    person.titleLast,
1125                    person.birthDate,
1126                    person.email,
1127                    person.office,
1128                    person.regionOffice,
1129                    person.phone,
1130                    person.slug,
1131                    person.homePage,
1132                    person.nasiPoliticiUrl,
1133                    pgroup.id AS id,
1134                    SUM(    CASE
1135                                WHEN gstats.result = mpvote.result THEN 1
1136                                ELSE 0
1137                            END
1138                    ) AS same,
1139                    SUM(1) AS total,
1140                    SUM(    CASE
1141                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN
1142                                    CASE
1143                                        WHEN gstats.result = mpvote.result THEN 1
1144                                        ELSE 0
1145                                    END
1146                                ELSE 0
1147                            END
1148                    ) AS activeSame,
1149                    SUM(    CASE
1150                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN 1
1151                                ELSE 0
1152                            END
1153                    ) AS activeTotal
1154                    FROM    psp_person AS person,
1155                            psp_membership AS mship,
1156                            psp_group AS pgroup,
1157                            psp_groupstats AS gstats,
1158                            psp_mpvote AS mpvote,
1159                            psp_poll AS poll
1160                    WHERE   pgroup.type LIKE 'KLUB' AND
1161                            mship.group_id = pgroup.id AND
1162                            mship.person_id = person.id AND
1163                            mship.post LIKE '%%%%len' AND
1164                            --we will handle lower bound first
1165                            (('%d-%02d-%02d' BETWEEN  mship.start AND mship.end) OR  
1166                            ('%d-%02d-%02d' > mship.start AND mship.end IS NULL)) AND
1167                            --we will handle upper bound next 
1168                            (('%d-%02d-%02d' BETWEEN  mship.start AND mship.end) OR  
1169                            ('%d-%02d-%02d' > mship.start AND mship.end IS NULL)) AND
1170                            mpvote.poll_id = poll.id AND mpvote.person_id = person.id AND %s
1171                            gstats.poll_id = poll.id AND gstats.group_id = pgroup.id AND
1172                            poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
1173                    GROUP BY person.id
1174                    ORDER BY %s %s;
1175            """
1176            select = select % ( dayStart.year, dayStart.month, dayStart.day,
1177                                dayStart.year, dayStart.month, dayStart.day,
1178                                dayEnd.year, dayEnd.month, dayEnd.day,
1179                                dayEnd.year, dayEnd.month, dayEnd.day,
1180                                activeParm,
1181                                dayStart.year, dayStart.month, dayStart.day,
1182                                dayEnd.year, dayEnd.month, dayEnd.day,
1183                                colName, order
1184                            )
1185        else:
1186            select = """
1187                SELECT
1188                    person.id,
1189                    person.mpId,
1190                    person.name,
1191                    person.surname,
1192                    person.title,
1193                    person.titleLast,
1194                    person.birthDate,
1195                    person.email,
1196                    person.office,
1197                    person.regionOffice,
1198                    person.phone,
1199                    person.slug,
1200                    person.homePage,
1201                    person.nasiPoliticiUrl,
1202                    pgroup.id,
1203                    SUM(    CASE
1204                                WHEN gstats.result = mpvote.result THEN 1
1205                                ELSE 0
1206                            END
1207                    ) AS same,
1208                    SUM(1) AS total,
1209                    SUM(    CASE
1210                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN
1211                                    CASE
1212                                        WHEN gstats.result = mpvote.result THEN 1
1213                                        ELSE 0
1214                                    END
1215                                ELSE 0
1216                            END
1217                    ) AS activeSame,
1218                    SUM(    CASE
1219                                WHEN mpvote.result IN ('A', 'N', 'Z') THEN 1
1220                                ELSE 0
1221                            END
1222                    ) AS activeTotal
1223                    FROM    psp_person AS person,
1224                            psp_membership AS mship,
1225                            psp_group AS pgroup,
1226                            psp_groupstats AS gstats,
1227                            psp_mpvote AS mpvote,
1228                            psp_poll AS poll
1229                    WHERE   pgroup.type LIKE 'KLUB' AND
1230                            mship.group_id = pgroup.id AND
1231                            mship.person_id = person.id AND
1232                            mship.post LIKE '%%%%len' AND
1233                            (('%d-%02d-%02d' BETWEEN  mship.start AND mship.end) OR  
1234                            ('%d-%02d-%02d' > mship.start AND mship.end IS NULL)) AND
1235                            mpvote.poll_id = poll.id AND mpvote.person_id = person.id AND %s
1236                            gstats.poll_id = poll.id AND gstats.group_id = pgroup.id AND
1237                            poll.date = '%d-%02d-%02d'
1238                    GROUP BY person.id
1239                    ORDER BY %s %s;
1240            """
1241            select = select % ( dayStart.year,

Large files files are truncated, but you can click here to view the full file