PageRenderTime 64ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 1ms

/psp/models.py

http://parliament-poll-stats.googlecode.com/
Python | 4721 lines | 4699 code | 1 blank | 21 comment | 6 complexity | 4ed341d9c73fe68daafddd46b79d14cc MD5 | raw file
Possible License(s): BSD-3-Clause

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

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