/psp/models.py
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