def _get_age_data(election_instance_id):
query = """
SELECT eip.id, p.age - (p.age %% 10) as label, COUNT(p.age - (p.age %% 10)) AS count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE p.age IS NOT NULL AND eip.election_instance_id = %s
GROUP BY eip.id, (p.age - (p.age %% 10))
ORDER BY eip.id, p.age - (p.age %% 10)
"""
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for (key, title) in [(x, "%s-%s" % (x, x + 9))
for x in range(10, 100, 10)]:
chart_titles.append(title)
chart_data.append(grouped_data.get(key, 0))
chart_titles[0] = '<19'
chart_titles[-1] = '90>'
query_dict = dict(
cht='bvs',
chd='t:%s' % (','.join(map(str, chart_data))),
chs='350x250',
chxt='x,y',
chxr='1,0,100',
chds='0,%s' % (sum(chart_data)),
chxl='0:|%s|' % ('|'.join(chart_titles)),
chbh=30,
chco=','.join(CHART_COLORS[0:len(chart_data)]),
chg='0,10,1,0',
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_politicaltype_data(election_instance_id):
query = """
SELECT eip.id, pt.type AS label, COUNT(*) AS count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
INNER JOIN political_profiles_politicalexperience ppe ON ppe.politician_id = p.id
INNER JOIN political_profiles_politicalexperiencetype pt ON pt.id = ppe.type_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id, pt.type
ORDER BY eip.id, pt.id
"""
political_type = PoliticalExperienceType.objects.order_by(
'type').values_list('type', flat=True)
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for title in political_type:
key = title
chart_titles.append("%s (%s)" %
(title, grouped_data.get(key, 0)))
chart_data.append(grouped_data.get(key, 0))
query_dict = dict(
cht='p3',
chd='t:%s' % (','.join(map(str, chart_data))),
chs='400x240',
chdl='%s' % ('|'.join(chart_titles)),
chco=','.join(CHART_COLORS[0:len(chart_data)]),
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_age_data(election_instance_id):
query = """
SELECT eip.id, p.age - (p.age %% 10) as label, COUNT(p.age - (p.age %% 10)) AS count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE p.age IS NOT NULL AND eip.election_instance_id = %s
GROUP BY eip.id, (p.age - (p.age %% 10))
ORDER BY eip.id, p.age - (p.age %% 10)
"""
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for (key, title) in [(x, "%s-%s" % (x, x+9)) for x in range(10, 100, 10)]:
chart_titles.append(title)
chart_data.append(grouped_data.get(key, 0))
chart_titles[0] = '<19'
chart_titles[-1] = '90>'
query_dict = dict(
cht='bvs',
chd='t:%s' % (','.join(map(str,chart_data))),
chs='350x250',
chxt='x,y',
chxr='1,0,100' ,
chds='0,%s' % (sum(chart_data)),
chxl='0:|%s|' % ('|'.join(chart_titles)),
chbh=30,
chco=','.join(CHART_COLORS[0:len(chart_data)]),
chg='0,10,1,0',
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_politicaltype_data(election_instance_id):
query = """
SELECT eip.id, pt.type AS label, COUNT(*) AS count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
INNER JOIN political_profiles_politicalexperience ppe ON ppe.politician_id = p.id
INNER JOIN political_profiles_politicalexperiencetype pt ON pt.id = ppe.type_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id, pt.type
ORDER BY eip.id, pt.id
"""
political_type = PoliticalExperienceType.objects.order_by('type').values_list('type', flat=True)
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for title in political_type:
key = title
chart_titles.append("%s (%s)" % (title, grouped_data.get(key, 0)))
chart_data.append(grouped_data.get(key, 0))
query_dict = dict(
cht='p3',
chd='t:%s' % (','.join(map(str,chart_data))),
chs='400x240',
chdl='%s' % ('|'.join(chart_titles)),
chco=','.join(CHART_COLORS[0:len(chart_data)]),
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_religion_data(election_instance_id):
query = """
SELECT eip.id, p.religion as label, COUNT(*) as count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id, p.religion
ORDER BY eip.id, p.religion
"""
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for (key, title) in RELIGION:
chart_titles.append("%s" % (title))
chart_data.append(grouped_data.get(key, 0))
query_dict = dict(
cht='bhs',
chd='t:%s' % (','.join(map(str, reversed(chart_data)))),
chs='400x280',
chxt='y,x',
chds='0,%s' % (sum(chart_data)),
chxl='0:|%s' % ('|'.join(chart_titles)),
chbh=20,
chco=','.join(CHART_COLORS[0:len(chart_data)]),
chg='10,0,1,1',
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_religion_data(election_instance_id):
query = """
SELECT eip.id, p.religion as label, COUNT(*) as count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id, p.religion
ORDER BY eip.id, p.religion
"""
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for (key, title) in RELIGION:
chart_titles.append("%s" % (title))
chart_data.append(grouped_data.get(key, 0))
query_dict = dict(
cht='bhs',
chd='t:%s' % (','.join(map(str, reversed(chart_data)))),
chs='400x280',
chxt='y,x',
chds='0,%s' % (sum(chart_data)),
chxl='0:|%s' % ('|'.join(chart_titles)),
chbh=20,
chco=','.join(CHART_COLORS[0:len(chart_data)]),
chg='10,0,1,1',
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_maritalstatus_data(election_instance_id):
query = """
SELECT eip.id, p.marital_status AS label, COUNT(*) AS count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id, p.marital_status
ORDER BY eip.id, p.marital_status
"""
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for (key, title) in MARITAL_STATUS:
chart_titles.append("%s (%s)" %
(title, grouped_data.get(key, 0)))
chart_data.append(grouped_data.get(key, 0))
query_dict = dict(
cht='p3',
chd='t:%s' % (','.join(map(str, chart_data))),
chs='280x100',
chdl='%s' % ('|'.join(chart_titles)),
chco=','.join(CHART_COLORS[0:len(chart_data)]),
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_maritalstatus_data(election_instance_id):
query = """
SELECT eip.id, p.marital_status AS label, COUNT(*) AS count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id, p.marital_status
ORDER BY eip.id, p.marital_status
"""
result = {}
grouped_data = {}
current_id = None
generator = query_to_dict(query, election_instance_id)
while True:
try:
row = generator.next()
except StopIteration:
row = None
if row is None or (row['id'] != current_id and current_id is not None):
chart_data = []
chart_titles = []
for (key, title) in MARITAL_STATUS:
chart_titles.append("%s (%s)" % (title, grouped_data.get(key, 0)))
chart_data.append(grouped_data.get(key, 0))
query_dict = dict(
cht='p3',
chd='t:%s' % (','.join(map(str,chart_data))),
chs='280x100',
chdl='%s' % ('|'.join(chart_titles)),
chco=','.join(CHART_COLORS[0:len(chart_data)]),
)
result.update({current_id: query_dict})
grouped_data = {}
if row is None:
break
grouped_data.update({row['label']: row['count']})
current_id = row['id']
return result
def _get_gender_data(election_instance_id):
query = """
SELECT eip.id, SUM(CASE p.gender WHEN 'Male' THEN 1 ELSE 0 END) AS male_count, SUM(CASE p.gender WHEN 'Female' THEN 1 ELSE 0 END) AS female_count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id
"""
result = {}
for row in query_to_dict(query, election_instance_id):
query_dict = dict(
cht='p3',
chd='t:%s,%s' % (row['male_count'], row['female_count']),
chs='280x100',
chdl='Mannen+(%s)|Vrouwen+(%s)' % (row['male_count'], row['female_count']),
chco=','.join(CHART_COLORS[0:2]),
)
result.update({row['id']: query_dict})
return result
def _get_gender_data(election_instance_id):
query = """
SELECT eip.id, SUM(CASE p.gender WHEN 'Male' THEN 1 ELSE 0 END) AS male_count, SUM(CASE p.gender WHEN 'Female' THEN 1 ELSE 0 END) AS female_count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id
"""
result = {}
for row in query_to_dict(query, election_instance_id):
query_dict = dict(
cht='p3',
chd='t:%s,%s' % (row['male_count'], row['female_count']),
chs='280x100',
chdl='Mannen+(%s)|Vrouwen+(%s)' %
(row['male_count'], row['female_count']),
chco=','.join(CHART_COLORS[0:2]),
)
result.update({row['id']: query_dict})
return result
def _get_vegetarian_data(election_instance_id):
query = """
SELECT eip.id, SUM(CASE p.diet WHEN 'ALL' THEN 1 ELSE 0 END) AS nonveggie_count, SUM(CASE p.diet WHEN 'VEG' THEN 1 ELSE 0 END) AS veggie_count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id
"""
result = {}
for row in query_to_dict(query, election_instance_id):
query_dict = dict(
cht='p3',
chd='t:%s,%s' % (row['nonveggie_count'], row['veggie_count']),
chs='280x100',
chdl='Niet+vegetarisch+(%s)|Vegetarisch+(%s)' % (row['nonveggie_count'], row['veggie_count']),
chco=','.join(CHART_COLORS[0:2]),
)
result.update({row['id']: query_dict})
return result
def _get_vegetarian_data(election_instance_id):
query = """
SELECT eip.id, SUM(CASE p.diet WHEN 'ALL' THEN 1 ELSE 0 END) AS nonveggie_count, SUM(CASE p.diet WHEN 'VEG' THEN 1 ELSE 0 END) AS veggie_count
FROM elections_electioninstanceparty eip
INNER JOIN elections_candidacy ec ON eip.id = ec.election_party_instance_id
INNER JOIN political_profiles_politicianprofile p ON p.user_id = ec.candidate_id
WHERE eip.election_instance_id = %s
GROUP BY eip.id
"""
result = {}
for row in query_to_dict(query, election_instance_id):
query_dict = dict(
cht='p3',
chd='t:%s,%s' % (row['nonveggie_count'], row['veggie_count']),
chs='280x100',
chdl='Niet+vegetarisch+(%s)|Vegetarisch+(%s)' %
(row['nonveggie_count'], row['veggie_count']),
chco=','.join(CHART_COLORS[0:2]),
)
result.update({row['id']: query_dict})
return result
def get_popularity(election_instance_id):
key = 'popu-%s' % (election_instance_id)
result = cache.get(key)
if result is None:
from political_profiles.models import UserStatistics
from django.conf import settings as stts
import sys
winsec = 24*60*60 * UserStatistics.view_interval.days + UserStatistics.view_interval.seconds
if stts.DATABASES['default']['ENGINE'].find('sqlite') != -1:
pop = """COALESCE((({0} / ({0} + (strftime('%%s', 'now') - strftime('%%s', us.profile_hits_up)))) * us.profile_hits), 0) as pop""".format(winsec)
elif stts.DATABASES['default']['ENGINE'].find('mysql') != -1:
pop = """COALESCE((({0} / ({0} + time_to_sec(timediff(now(), us.profile_hits_up)))) * us.profile_hits), 0) as pop""".format(winsec)
else:
raise Exception('Unsupported database engine. Please add DB specific date-time manipulation code');
#[FIXME: by taking sum of scores we give to high penalties for people that
# where not in top 5, but have almost no difference in score with top 5]
query = """
SELECT ec.id, p.id as party_id, COUNT(ca.candidates_score) as sum, {0}
FROM elections_candidacy ec
JOIN elections_electioninstanceparty p ON p.id = ec.election_party_instance_id
LEFT JOIN frontoffice_candidateanswers ca ON ca.candidate_id = ec.candidate_id
LEFT JOIN political_profiles_userstatistics us ON ec.candidate_id = us.user_id
WHERE p.election_instance_id = %s
GROUP BY ec.id, p.id
""".format(pop)
canresult = []
pmin, pmax = float(sys.maxint), 0
smin, smax = sys.maxint, 0
for row in query_to_dict(query, election_instance_id):
pop = float(row['pop'])
pmin, pmax = min(pmin, pop), max(pmax, pop)
sum = float(row['sum'])
smin, smax = min(smin, sum), max(smax, sum)
canresult.append((row['id'], row['party_id'], (sum, pop)))
# normalized [0..100], floats
presult = {}
cand_result = {}
# with 1 candidate smax == smin
smin, stot = (smin, smax - smin) if smax > smin else (smin - 1, 1)
pmin, ptot = (pmin, pmax - pmin) if pmax > pmin else (pmin - 1, 1)
for (cid, pid, (sum, pop)) in canresult:
sum = ((sum - smin) / stot) * 100
pop = ((pop - pmin) / ptot) * 100
pp = calc_popularity(sum, pop)
cand_result[cid] = pp
pcur, pcount = presult.get(pid, (0.0, 0))
presult[pid] = (pcur + pp, pcount + 1)
#normalize parties
#[FIXME: average is really bad statistics! will flat out all peaks (leaders
# of the parties). Better to compensate it with all values with Z-score > 0.x
presult = dict(map(lambda (pid, (popsum, count)): (pid, popsum / count), presult.iteritems()))
pmax, pmin = max(presult.itervalues()), min(presult.itervalues())
pmin, ptot = (pmin, pmax - pmin) if pmax > pmin else (pmin - 1, 1)
presult = dict(map(lambda (pid, pop): (pid, ((pop - pmin) / ptot) * 100), presult.iteritems()))
# cache data
result = (cand_result, presult)
cache.set(key, result, 60*60*24) #24 hour cache
return result