Пример #1
0
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
Пример #2
0
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
Пример #3
0
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
Пример #4
0
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
Пример #5
0
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
Пример #6
0
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
Пример #7
0
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
Пример #8
0
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
Пример #9
0
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
Пример #10
0
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
Пример #11
0
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
Пример #12
0
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
Пример #13
0
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