search

mysociety / pombola

GNU Affero General Public License v3.0
65 stars 41 forks source link

KE: Party memberships with wrong Title #1986

Open tmtmtmtm opened 8 years ago

tmtmtmtm commented 8 years ago

Most party memberships have a positiontitle of 'Member':

               role                | howmany 
-----------------------------------+---------
 Member                            |   13560
 party leader                      |      20
 Member of Parliament              |      17
 Chairman                          |      12
 Secretary                         |       5
 Aspirant MP                       |       4
 Secretary General                 |       4
 Founding member                   |       4
                                   |       3
 Chair                             |       3
 Founder Member                    |       3
 National Youth Coordinator        |       2
 Deputy Chairperson                |       2
 Vice Chair                        |       2
 Executive Director                |       1
 Youth leader                      |       1
 Founding Secretary General        |       1
 Senator                           |       1
 Deputy National Executive Officer |       1
 Secretary - Nairobi Branch        |       1
 Chief whip                        |       1
 First National Chairman           |       1
 Organizing Secretary              |       1
 Chair - Nyeri Branch              |       1
 Officer                           |       1
 Deputy Secretary General          |       1
 Coalition Member                  |       1

Most of the rest of these are OK (though it would be useful to be able to conceptually separate out special positions within a party from the on_behalf_of relationships on other memberships), but a few are almost certainly mistakes — particularly "Member of Parliament", "Aspirant MP", "Senator" and "Coalition Member". The ones with no title should probably be fixed too.

via 

SELECT t.name AS role, COUNT(*) AS howmany
  FROM core_person mp
  LEFT JOIN core_position p ON mp.id = p.person_id
  LEFT JOIN core_positiontitle t ON p.title_id = t.id
  LEFT JOIN core_place a ON a.id = p.place_id
  LEFT JOIN core_placekind pk ON a.kind_id = pk.id
  LEFT JOIN core_organisation o ON p.organisation_id = o.id
 WHERE o.kind_id = 1
 GROUP BY t.name
 ORDER BY howmany DESC
tmtmtmtm commented 8 years ago

The ones to fix:

  id   |         legal_name         |                  organisation                  |         role         |        area         | start_date |  end_date  
-------+----------------------------+------------------------------------------------+----------------------+---------------------+------------+------------
   259 | Dahir Sheikh Abdullahi     | Kenya National Congress                        | Aspirant MP          | Dadaab              |            | 2013-02-18
  1987 | Francis Mwanzia Nyenze     | Ford Asili                                     | Aspirant MP          | Kitui West          |            | 1992-00-00
    76 | Gideon Mung'aro Maitha     | Orange Democratic Movement                     | Aspirant MP          | Kilifi North        |            | 2013-03-04
   128 | Richard Momoima Onyonka    | Orange Democratic Movement                     | Aspirant MP          | Kitutu Chache South |            | 2013-03-04
 13152 | Fatuma Ibrahim Ali         | Orange Democratic Movement                     | Coalition Member     | Wajir               | 2013-01-18 | future
   255 | Boniface Mganga            | Kenya African National Union                   | Member of Parliament | Voi                 | 2002-00-00 | 2011-00-00
   147 | Ferdinand Ndung'u Waititu  | The National Alliance (TNA)                    | Member of Parliament | Kabete              | 2015-05-04 | 
   180 | James Omingo Magara        | Forum for Restoration and Democracy- People    | Member of Parliament | South Mugirango     | 2003-00-00 | 2007-00-00
   180 | James Omingo Magara        | Forum For The Restoration Of Democracy - Kenya | Member of Parliament | South Mugirango     | 2001-00-00 | 2002-00-00
   281 | John Mutua Katuku          | Kenya African National Union                   | Member of Parliament | Mwala               | 2002-00-00 | 2007-00-00
    93 | Kiraitu Murungi            | Forum For The Restoration Of Democracy         | Member of Parliament | South Imenti        | 1992-00-00 | 2006-00-00
   247 | Morris Mwachondo Dzoro     | National Rainbow Coalition                     | Member of Parliament | Kaloleni            | 2002-00-00 | 2007-00-00
   312 | Musa Cherutich Sirma       | Kenya African National Union                   | Member of Parliament | Eldama Ravine       | 1997-00-00 | 2007-00-00
   223 | Mwangi Kirika Waithaka     | Forum for Restoration and Democracy- People    | Member of Parliament | Kinangop            | 1997-00-00 | 2002-00-00
     9 | Najib Mohamed Balala       | National Rainbow Coalition                     | Member of Parliament | Mvita               | 2002-00-00 | 2007-00-00
   194 | Oburu Ngona Odinga         | National Rainbow Coalition                     | Member of Parliament | Bondo               | 2002-00-00 | 2007-00-00
   194 | Oburu Ngona Odinga         | National Development Party (NDP)               | Member of Parliament | Bondo               | 1995-00-00 | 2002-00-00
   347 | Ochilo George Mbogo Ayacko | National Development Party (NDP)               | Member of Parliament | Rongo               | 1997-00-00 | 2002-00-00
   240 | Onesmus Kihara Mwangi      | Democratic Party                               | Member of Parliament | Kigumo              | 1997-00-00 | 2002-00-00
   284 | Richard Maoka Maore        | Democratic Party                               | Member of Parliament | Igembe South        | 1992-00-00 | 2002-00-00
   342 | Simeon Nyachae             | Kenya African National Union                   | Member of Parliament | Nyaribari Chache    | 1992-00-00 | 2002-00-00
   196 | Sospeter Odeke Ojaamongson | National Rainbow Coalition                     | Member of Parliament | Amagoro             | 2002-00-00 | 2007-00-00
 13162 | Moses Otieno Kajwang'      | Orange Democratic Movement                     | Senator              | Homa Bay            | 2015-02-00 | future

via 

SELECT mp.id, mp.legal_name, o.name AS organisation, t.name AS role, a.name AS area, p.start_date, p.end_date
  FROM core_person mp
  LEFT JOIN core_position p ON mp.id = p.person_id
  LEFT JOIN core_positiontitle t ON p.title_id = t.id
  LEFT JOIN core_place a ON a.id = p.place_id
  LEFT JOIN core_placekind pk ON a.kind_id = pk.id
  LEFT JOIN core_organisation o ON p.organisation_id = o.id
 WHERE o.kind_id = 1
   AND t.name IN ('Member of Parliament', 'Aspirant MP', '', 'Senator', 'Coalition Member')
 ORDER BY t.name, mp.legal_name;
mhl commented 8 years ago

We thought these are probably best fixed in the admin interface rather than programmatically, although we can use the query as an automatic check for regressions.